NoCOUG internal magazine

NoCOUG internal magazine at http://www.nocoug.org/Journal/NoCOUG_Journal_YYYYMM.pdf, where YYYY - year, MM - one of 02,05,08,11

Duplicating for standby

On the start I have a target database configured with OMF and specified unique db name. On the standby side I created an instance, started it and that's it. Oh, yeah - I copied password file from primary side to be able to connect to standby and configured listener with GLOBAL_DBNAME, which allows for registering a service for use in tnsnames.ora on both hosts. Of course I specified proper entries in tnsnames.ora as well.
There are possible many configurations for this duplication - combinations of a target database, a standby database and a catalog - of course a standby one is a must, but the rest is optional and may be avoided, although it complicates the matter a little. I choose to use additionally to standby database also active target database. This avoids the trouble of applying all the redo logs from last backup.

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

First failure: the command failed with ORA-17628: Oracle error 19505 returned by remote Oracle server on primary and ORA-19505: failed to identify file "/oracle/oradata/ZABBIX/controlfile/o1_mf_855ys7j8_.ctl"
ORA-27040: file create error, unable to create file as I did not create path for controlfile (so now mkdir -p /oracle/oradata/ZABBIX/controlfile/o1_mf_855ys7j8_.ctl).

Second start and failure:

RMAN-03002: failure of Duplicate Db command at 11/13/2012 10:14:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-06550: line 1, column 17:
PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared
ORA-06550: line 1, column 7:

I found at http://blog.csdn.net/tianlesoftware/article/details/6232292 that it is due to not specifying nocatalog when connecting with rman. And that really helped.
Third try and this time I moved much ahead - I was wrong with assuming the online logs will be created according to the db_create_file_dest (so I should set db_create_online_log_dest_n at least to overcome this). However this time duplication is going forward - the data files are created according to the plan.
Because copy is done with backup as copy reuse datafile X auxiliary format new then no useless copying to storage local to primary and all bytes goes directly to the standby files, which is nice

To finish the standby I need now to add standby log files (though this is optional) and set log_archive_dest_state_2 to enable. The managed standby is already run by the duplicate command. One may want to switch this to recovery using current logfile.

Distributed transactions holding locks on some resources

One of the main problems with XA transactions seems to be locking of rows by distributed transaction. While this is perfectly normal, when locking lasts a short time, it becomes a nightmare when it lasts forever. There are few scenarios, which may end up with such long-lasting lock.
Many comprehensive articles may be found on Metalink:

1. Solving locking problems in a XA environment [ID 1248848.1] - short, thorough and I like it
2. Manually Resolving In-Doubt Transactions: Different Scenarios [ID 126069.1] - long and boring

In many scenarios the issue should resolve automatically. If this is not the case then theoretically the distributed transaction should be recognized by the resource manager as 'in-doubt' and show up in the dataset from the dba_2pc_pending view. If so then a DBA should settle with a transaction manager administrator if the transaction 'in-doubt' should be commited or rollbacked and then on the resource manager side the DBA should call appropriately commit or rollback with force option and local transaction id as an argument. Sometimes additionally one needs to call DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('[LOCAL_TRAN_ID]');
Even easier such situation may be resolved from XA client side - we may prepare a fake client, which binds to the database as a side of the chosen distributed transaction and commit or rollback it according to the needs, while whole the cleaning will be done by standard mechanisms (which is recommended way of dealing with such issues) - such example client code may be obtained from the first mentioned article.
Yet another way of resolving the issue is the database reboot (but this is not always possible).

The distributed transaction goes through few phases.
When in 1st phase - not yet prepared - the locks are seen in v$lock view and diagnostic information is easy to obtain.
After prepare call and response those locks are no longer seen in v$lock and then the diagnostics is more hard. Yet another possibility here is the transaction process on the transaction manager side was killed by the system - then we end up with an active transaction still blocking some resources, but there is no locks seen (there is however entry in the v$locked_object view, so at least we may find objects on which there are some locked rows) and no session attached.
In this scenario we may use the excellent view from Tanel Poder changing the equal join to a left join between sys.x$k2gte2 and v$session - from this we may see that some transactions in x$k2gte2 have no corresponding row from the v$session view - these are our candidates for forced transaction finish - in x$k2gte2 we may find the global id of the transaction 'in-doubt' and the local id can be constructed from the join with sys.x$ktcxb (x$k2gte2.k2gtdxcb=x$ktcxb.ktcxbxba) on columns kxidusn, kxidslt, kxidsqn (it seems no need for left join here) so we may do something about it.
Once there was a workaround published on Metalink, which involved an insert into pending_trans$ and pending_session$, but I could not find it lately - so I suppose they recommend rather the use of fake client here. However one may still read about it after some google search - voila http://tazlambert.wordpress.com/2011/11/24/ora-01591-lock-held-by-in-doubt-distributed-transaction-string/ (look at the end of the article).
As a kind of curiosity I may add that I found even a x$k2gte2 entry, which had no corresponding entry in the sys.x$ktuxe structure or the v$transaction view. In sys.x$ktcxb there still was an entry however(that is why I assumed no left join needed here), but all the parts of local transaction id were equal to 0, so I suppose this trash has no impact on the database activity and the only way to clean it is a reboot.

XA on Oracle database

The Oracle RDBMS is XA compliant from scratch - no adjustments needed - http://www.orafaq.com/wiki/XA_FAQ is a good starting point on this subject.

One may create additional views for dealing with troublesome situations. The Oracle views v$XATRANS$ and V$PENDING_XATRANS$ may be created in order to help with diagnostics (they are created by running @?/rdbms/admin/xaview.sql).
Another very good view is accessible at http://blog.tanelpoder.com/files/scripts/gtrans.sql. I usually change the join between sys.x$k2gte2 and v$session into LEFT JOIN as still it is possible to have on a database active distributed transactions not connected to any session.

Update from 6 May 2014

Lately we have got a request to fix the cooperation between the Oracle RDBMS and the Tuxedo.
The errors as following:

080307.tux24int!TMS_ORA.18176.1.0: 05-06-2014: Tuxedo Version 11.1.1.2.0, 32-bit
080307.tux24int!TMS_ORA.18176.1.0: LIBTUX_CAT:262: INFO: Standard main starting
080307.tux24int!TMS_ORA.18176.1.0: CMDTUX_CAT:409: ERROR: xa_recover() returned -3 for group ON1LT
080307.tux24int!TMS_ORA.18176.1.0: LIBTUX_CAT:250: ERROR: tpsvrinit() failed
080307.tux24int!tmboot.18147.1.-2: CMDTUX_CAT:825: ERROR: Process TMS_ORA at tux24int failed with /T tperrno (TPESYSTEM - internal system error)
080307.tux24int!TMS_ORA.18177.1.0: 05-06-2014: Tuxedo Version 11.1.1.2.0, 32-bit
080307.tux24int!TMS_ORA.18177.1.0: LIBTUX_CAT:262: INFO: Standard main starting
080308.tux24int!TMS_ORA.18177.1.0: CMDTUX_CAT:409: ERROR: xa_recover() returned -3 for group ON1LT
080308.tux24int!TMS_ORA.18177.1.0: LIBTUX_CAT:250: ERROR: tpsvrinit() failed
080308.tux24int!tmboot.18147.1.-2: CMDTUX_CAT:825: ERROR: Process TMS_ORA at tux24int failed with /T tperrno (TPESYSTEM - internal system error)

Well, it seems that one needs some adjustments - of course this is possible it is our configuration, where the devil hides. Anyway despite the running the xaview script I also granted the privileges below to the user, which the Tuxedo connects to

grant SELECT on SYS.DBA_PENDING_TRANSACTIONS to {schema};
grant EXECUTE on SYS.DBMS_SYSTEM to {schema};
-- this one is to a view created by xaview.sql
grant SELECT on SYS.V$XATRANS$ to {schema}; 

It seems it solved the issue.

IMPDP and lacking internal import structures

There are number of problems with importing data with impdp depending on the content parameter setting during export. According to few articles on Metalink this is mostly due to the fact, that during import there are created implicitly some structures and this creation failed. Today I just hit something like this. I exported some schemas with content=data_only. When importing I hit:

ORA-39034: Table TABLE_DATA:"TEST"."SCHEDULER$_JOB_ARG" does not exist.
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [62] 
TABLE_DATA:"TEST"."SCHEDULER$_JOB_ARG"
ORA-31603: object "SCHEDULER$_JOB_ARG" of type TABLE not found in schema "TEST"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8364

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xb7f251c0     19208  package body SYS.KUPW$WORKER
0xb7f251c0      8385  package body SYS.KUPW$WORKER
0xb7f251c0     18770  package body SYS.KUPW$WORKER
0xb7f251c0      4226  package body SYS.KUPW$WORKER
0xb7f251c0      9082  package body SYS.KUPW$WORKER
0xb7f251c0      1688  package body SYS.KUPW$WORKER
0xb791dd40         2  anonymous block

To workaround this I assumed if I create this lacking table I'll be able to go further. I run as sys

create table "TEST"."SCHEDULER$_JOB_ARG" as select * from SCHEDULER$_JOB_ARGUMENT where rownum<1 pre="">
and run import again.
One step forward.
I hit then:
ORA-39034: Table TABLE_DATA:"TEST"."SCHEDULER$_PROGRAM_ARG" does not exist.
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [62] 
TABLE_DATA:"TEST"."SCHEDULER$_PROGRAM_ARG"
ORA-31603: object "SCHEDULER$_PROGRAM_ARG" of type TABLE not found in schema "TEST"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8364

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xb7f251c0     19208  package body SYS.KUPW$WORKER
0xb7f251c0      8385  package body SYS.KUPW$WORKER
0xb7f251c0     18770  package body SYS.KUPW$WORKER
0xb7f251c0      4226  package body SYS.KUPW$WORKER
0xb7f251c0      9082  package body SYS.KUPW$WORKER
0xb7f251c0      1688  package body SYS.KUPW$WORKER
0xa9d3b2b8         2  anonymous block

Per analogiam I run:
create table "TEST"."SCHEDULER$_PROGRAM_ARG" as select * from SCHEDULER$_PROGRAM_ARGUMENT where rownum<1 pre="">
Next step forward.
Now I hit
ORA-39034: Table TABLE_DATA:"TEST2"."RE$ACTION_IMP_TAB" does not exist.
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [62] 
TABLE_DATA:"TEST2"."RE$ACTION_IMP_TAB"
ORA-31603: object "RE$ACTION_IMP_TAB" of type TABLE not found in schema "TEST2"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8364

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xb7f251c0     19208  package body SYS.KUPW$WORKER
0xb7f251c0      8385  package body SYS.KUPW$WORKER
0xb7f251c0     18770  package body SYS.KUPW$WORKER
0xb7f251c0      4226  package body SYS.KUPW$WORKER
0xb7f251c0      9082  package body SYS.KUPW$WORKER
0xb7f251c0      1688  package body SYS.KUPW$WORKER
0xb2aae570         2  anonymous block

Now I have no idea where I may find RE$ACTION_IMP_TAB structure, so I wished myself the structure is not important and it is enough to get the properly named object, so I simply created whatever.
create table "TEST2"."RE$ACTION_IMP_TAB" as select * from dual where rownum<1;
The load goes forward - I assume it should be ok. If I import jobs or chains, it would fail, but jobs or chains are a part of metadata, so problem solved.

Of course the best way is to dump with content=ALL (ie. default value), but sometimes one forgets or has no such choice - then the workaround would be useful.

And short update - the manually created objects have to be dropped manually as well.

Zabbix on Oracle - few notes

We migrated Zabbix from MySQL to Oracle.
At the same time we moved to partitioning, here excellent article how to do it on MySQL. On Oracle we partitioned the same tables, created the same indexes, but go with interval partitioning (it is nice to not have to create new partitions manually or program it) based upon weeks (the partitioning key is of NUMBER type and we need an equal interval value for every period, so month is out due to variable number of days).

Almost no problems with upgrade to 2.0.2. We had to change the database however due to national character set - must be UTF-8, while we had previously fixed UTF16 - zabbix on Oracle is based mainly upon NVARCHAR2 type, and upgrade sets some columns to width bigger than 2000.
It is worth to enable CURSOR_SHARING to FORCE (actually I would say this is inevitable).
At the same time I would not recommend to use any automatic memory management (even ASMM). May be this is only our case, but frequently we run into ORA-4031 problems, which eventually ended with instance hangover or crash. As soon as I disabled any automatic memory management completely, the problems gone.

In addition to partition maintenance here there is an action for job, which will drop the HISTORY% tables' old partitions:

 begin
  for cmd in (
    with 
    conf as (select 9+1 part_num_left from dual)
    select 'alter table '||p.table_name||' drop partition '||p.partition_name||' update global indexes'  ddl1 
    from 
      user_tab_partitions p, 
      (select tp.table_name, count(*) l_pnum 
        from user_tab_partitions tp 
        where tp.table_name like 'HISTORY%' 
        group by tp.table_name 
        having count(*)>(select part_num_left from conf)) t
    where 1=1
      and p.table_name=t.table_name 
      and p.table_name like 'HISTORY%' 
      and p.partition_name<>'DONT_DELETE' 
      and p.partition_position between 2 and (t.l_pnum-(select part_num_left from conf)) 
    order by p.table_name, p.partition_position)
  loop
    execute immediate cmd.ddl1;
  end loop;
end;

The partitions labeled DONT_DELETE are partitions on the first position of relevant tables. The label is due to the fact one can not drop the first partition from a table partitioned by interval. It is comfortable to create those partitions before the time we want to store in table in order to keep it empty. The code above drops partitions leaving last n ones (here 9 + DONT_DELETE). The line with condition on DONT_DELETE partition name is actually redundant as the code starts dropping from second partition.

Moving subpartitions

In Oracle database MOVE operations are done on the segment level actually. Thus while simple table has its own segment, the partitioned one has none all the segments are bound with partitions. In case of subpartitions the same rule apply. So we move:

ALTER TABLE simple_t MOVE TABLESPACE tblspace1;
ALTER TABLE partitioned_t MOVE PARTITION p1  TABLESPACE tblspace1;
ALTER TABLE subpartitioned_t MOVE SUBPARTITION s1 TABLESPACE tblspace1;

When we move all the segments with must however change the default tablespace value on the table level and possibly on the partition level if it has a different value.

ALTER TABLE partitioned_t MODIFY DEFAULT ATTRIBUTES TABLESPACE tblspace1;
ALTER TABLE subpartitioned_t MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE tblspace1;

ALTER TABLE t1 MODIFY DEFAULT ATTRIBUTES... is less known, but can be quite useful allowing for switching on and off Advanced Compression and some other segment parameters.

This applies at least to the version 11.2 (do not check other docs).

Problems with connection

From time to time there happens different problems with connections to Oracle database. Below little report about those I have met.

• problems on client

  Usually they are trivial and bound with tnsnames.ora. Wrong PATH variable (for example few ORACLE_HOME/bin entries), wrong LD_LIBRARY_PATH, entries in the tnsnames.ora with some unusual blank character, wrong syntax in this file, wrong service_name. For example today 2 different tnsnames.ora files, while part of programs uses the right one, and some the wrong one.
  While trivial, sometimes hard to diagnose, especially by phone.
  
  Fortunately it is easy to get very detailed trace. We need in sqlnet.ora few entries:

  TRACE_LEVEL_CLIENT=16 #or SUPPORT
  TRACE_FILE_CLIENT=mytrace #useful for identification
  TRACE_DIRECTORY_CLIENT=traces #useful for location
  

  The output is quite detailed and usually helpful in the diagnosis.

• problems on cman

  Of course if one uses it (we do). I have met 3 major problems with it (and this is relevant for 11g version).
  
  The first one was with the raise of 11g version. The cman needs one rule to connect to itself. Till the 10g it was for example:

  (RULE=(SRC=10.0.50.10)(DST=127.0.0.1)(SRV=cmon)(ACT=accept))
  

  With 11g it must be (at least on Linux):

  (RULE=(SRC=10.0.50.10)(DST=::1)(SRV=cmon)(ACT=accept))
  

  The Metalink (ID 298916.1) still provides address 127.0.0.1 as a right one, and indeed the cman starts, but one can not connect to it with cmctl. Possibly it works, but one can not control it. I always needed to kill the cman process with kill utility to change anything (and no, we did not use it on production before the problem was solved).
  
  The second problem was with some older RDBMS versions (ie. before 10g) - we needed to add to simple rule some settings, for example:

  (RULE=(SRC=10.0.50.12)(DST=10.0.60.11)(SRV=*)(ACT=accept)(ACTION_LIST=(mct=0)))
  

  Usually it is enough to end with ACT attribute. This particular case with RDBMS servers on Windows kind os.
  
  The third one was with error in syntax. Once we loose somewhere in the middle of cman.ora a parenthesis. The cman kept all the rules from the time before error emergence, thus in the rules there were rules located before the point of error as well as those after it. When we added rules before that point and reloaded, everything was fine, if we added after, the rules were ignored. Because we always reloaded, the cman worked and for some time we were unaware of the problem at all.
  The true incident we met, when were trying to restart the cman. It went down and we can not start it up. IIRC the returned error was about problem in the configuration file in the end of it (which of course was due to mess in parentheses). The solution here was to display the config with VIM and color the syntax. Eventually we found the place for lacking parenthesis. The problem was trivial, but not so obvious.
  
  To those three I add yet another - in our case it was lack of mapping IP->hostname in /etc/hosts, possibly similar situation if no proper name in DNS service available (if used instead of /etc/hosts mapping). In result the cman service returned ORA-12529, even though the cman.ora configuration does not use host names at all (all rules with IP).
  The same happens when the entry in /etc/hosts differs from DST hostname.

• problems on server

  Both problems on server had something to do with firewalls.
  Once it was MTU parameter, which default value appeared too large after some firewall software update.
  The other problem was with inactivity timeout. Some of our firewalls disconnect connections, on which there is no traffic during specified time. A solution here seems to be enabling dead connection detection (DCD) feature. This feature implementation is based upon sending for time to time some bytes as a heartbeat - thus there is some traffic on a connection, so it is not broken.
  

Automation while suspending the db work

From some time the Oracle RDBMS supports a situation when due to some work lacks the space within db tablespaces. The AFTER SUSPEND trigger is called, so there is a place for kind of callback, one may set the RESUMABLE_TIMEOUT parameter or there is the DBA_RESUMABLE table with entries inserted after supend events.

If I would imagine itself the best way to solve the suspend event it would be to run within the AFTER SUSPEND trigger a code which would add a new file to the tablespace, on which there is a lack of place. There are was even examples with such solution for 9i version, but it is not now at least with 11.2 version (not sure about 10g). So what one may do?

I meet this problem mostly on test/dev environments, so the solution do not need to be a complex one and I do not need to think about any special cases - all added files are of UNLIMITED size, so always the solution is to add a new file (unless the storage is fully used).

1. I have met with a solution based upon the AFTER SUSPEND trigger. In 11.2 we get ORA-30511 if trying to run some DDL (and adding a file is of such type). The practical usage now I see in the following - setting RESUMABLE_TIMEOUT to some higher value in order to allow a DBA to make a room for the operation in suspend state or/and inform such DBA by SMS or an alert generation
2. use DBMS_SCHEDULER job to run from time to time a piece of code, which would add a new file

In my case the second solution has much more sense and is a real automation. Because we mostly use Data Pump utilities to load test/dev databases, it usually enables 2h long suspend (or so) on the operation. In such case the best way is to create a procedure like this:

CREATE OR REPLACE
PROCEDURE expand_resumable_tblspace
AS
  l_tblspace VARCHAR2(80);
  l_count pls_integer := 0;
BEGIN
  SELECT
    COUNT(*)
  INTO
    l_count
  FROM
    dba_resumable
  WHERE
    1               =1
  AND SUSPEND_TIME IS NOT NULL
  AND resume_time  IS NULL
  AND status        ='SUSPENDED' ;
  IF l_count        > 0 THEN
    SELECT
      SUBSTR(regexp_substr(ERROR_MSG, 'tablespace [^[:blank:]]*', 1, 1, 'im'),
      12)
    INTO
      l_tblspace
    FROM
      dba_resumable
    WHERE
      1                       =1
    AND SUSPEND_TIME         IS NOT NULL
    AND resume_time          IS NULL
    AND status                ='SUSPENDED'
    AND rownum                <2 ;
    IF SUBSTR(l_tblspace,1,4)!='TEMP' THEN
      EXECUTE immediate 'alter tablespace '||l_tblspace||
      ' add datafile size 1m autoextend on next 8m';
    ELSE
      EXECUTE immediate 'alter tablespace '||l_tblspace||
      ' add tempfile size 128m autoextend on next 128m';
    END IF;
  END IF;
EXCEPTION
WHEN OTHERS THEN
  SYS.DBMS_SYSTEM.KSDDDT;
  SYS.DBMS_SYSTEM.KSDWRT(2, SQLERRM);
  SYS.DBMS_SYSTEM.KSDFLS;
END;
/

and to run a job every let's say 5 minutes, which would call such procedure.

Why take only one row from DBA_RESUMABLE? I assume there is not too much such events at a moment and the second one would be serviced with next job run.

Why cut a tablespace name from ERROR_MSG and not from ERROR_PARAMETERx? I found that in some circumstances (different db version for example) the ERROR_PARAMETERx are set differently or not at all. For 11.2 IIRC the tablespace name was set in ERROR_PARAMETER4.

Recycle bin and dropping tablespaces

Today I hit an interesting issue. First the instance have not shut down within an hour. Second I could not drop a schema. The reason seems to be an entry in the recycle bin left after some table, which was bound with a tablespace, which in turn has been dropped apparently some time ago. So no tablespace, while the entry left and dropping the schema or purging recycle bin have failed with error in recursive SQL.

The solution quite simple - creating a new tablespace with the old name, then purging the recycle bin again .

Log rotation in 11g

Till version 11g the log rotation may been easily done by lsnrctl and set log_file to new name. With advent of ADR if used it blocks this way.
Excellent article on the theme IMHO at http://msutic.blogspot.com/2009/06/truncating-rotating-flushing.html.

Su-like technology for Oracle db

From time to time I meet with the situation when it comes to perform many changes on many db schemas provided as a set of scripts.

• The simplest way to do this is to logon sequentially to chosen schema and perform there all the necessary changes. This attitude has some drawbacks:
  
  1. one needs to know a password to every schema visited
  2. one needs to perform authentication to db many times
  3. one needs to run many scripts
• first improvement is to create master script, use CONNECT <<user>> command of sqlplus - but this still do not resolve providing passwords
  
• so we may create a more powerful account, gathering all the needed privileges and use
  ALTER SESSION SET CURRENT_SCHEMA=<<user>>; instead of CONNECT - this is a step in right direction and as long as scripts use simple objects and functionalities this way addresses all the inconveniences. Another ability is to prefix all the used objects.
  
• still yet we are far from perfectness - there are some functionalities which work wrong - the simplest example here is usage of private db links. Here we may use so called proxy users. Starting from 10gR2 these are available through sqlplus. We don't need a powerful user. We return back to use CONNECT, this time slightly modified. First we need an account with CREATE SESSION privilege. We need to alter all schemas, which we plan to make available:
  ALTER USER <<user>> GRANT CONNECT THROUGH <<proxy user>> (we may revoke it with REVOKE CONNECT THROUGH). This time we connect to any so configured user through
  CONNECT <<proxy user>>[<<user>>]/<<proxy user password>>. With this addressing all the problems is easy - we need to authenticate as only one user, so even though reconnecting many times, may use sqlplus variable substitution in order to provide a password only once.

There exists the special role BECOME USER, which apparently allows for similar behavior, however it is used for internal processing bound with Data Pump technology and rather not for use for "e;end users"e;.

Enabling Real Time Apply on open standby (with VPD)

Actually nothing special. One needs to stop the recovery, open standby in READ-ONLY mode and one again start the recovery.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

There is however one trick - one needs to do this rather quick. It seems (at least for me) that even one archivelog behind the most actual means the managed replication will not start, waiting apparently on some flag indicating the end of previously written log. Thus copying to standby needed logs does not mean, the standby will apply them. It seems a condition is here that archivelog on standby should be "in transition" and the same log being current on primary.

UPDATE from 2013-06-20
It seems I missed some apparent (and specific for this configuration) facts. First of all the database in question despite of having its standby as Active Data Guard has also implemented some VPD policy, which corresponds to a trigger triggered on logon.

The starting position is the following:

• primary database up and running, log_archive_dest_state_2 equals to defer
• standby database shut down, few archivelogs behind the primary, FAL server indicated

When the standby is mounted or started it tries to connect to FAL server. In my case this connection fails due to the trigger mentioned above as it throws an exception for some reason. If the standby is mounted then enabling log_archive_dest_2 (ie setting log_archive_dest_state_2 do enable) on the primary makes the primary to connect to the standby. Those connections succeed. Now no matter what I will do (open the standby in READ ONLY mode and then start the Real Time Apply or start it on the mounted standby) the managed recovery will succeed - after recovery start the ARCH processes provide not yet transferred archivelogs, the recovery process (MRP0) applies them, and then (assuming USING CURRENT LOGFILE clause) switches to communication with a primary LNS process in order to apply directly from memory not waiting for the logfile switch.
But if I would open the standby directly or do not enable log_archive_dest_2 when the standby stays in MOUNTED mode then all the connections from the primary would fail as the FAL connection from the standby to the primary. In such case there is impossible to open the communication and enable successfully the replication - the MRP0 process will start without problems, yet the archivelogs will not be transferred to the standby.
Then the only solution I've found is to shut the standby down and then mount it, ensure the communication with the primary is enabled (simple switching log_archive_dest_state_2 to defer, then to enabled again resets the connections and the messages in the standby alert log will tell if it works (no messages or error messages signal the problem)) and now (optionally) open, then start the managed recovery.

All my previous hypotheses are apparently wrong. There is no need to be at the same sequence on the primary and the standby, so the time after which the recovery is started is irrelevant in this context. The only thing important is to make connections during mount phase (as they are impossible to acquire when any database is in open mode).

Error ORA-01031/ORA-00604 on communication with standby

When I have hit the ORA-01031 error on version 10g, the cause was usually boring - badly configured connection primary<->standby, lack of password file, improper permissions on such file, etc. With 11g version the trivial error become more enigmatic. I checked connections, copied the password file from the primary and still got ORA-01031 on connection from the primary to the standby (and ORA-00604 on the connection from the standby to the primary). Finally under the link https://forums.oracle.com/forums/thread.jspa?messageID=10350931 I have found quite good explanation to my problem.
With the advent of 11g version DEFAULT profile has got some limitations - among others expired passwords. Due to this passwords go through grace time period, when "normal" user sees a message about grace time period. But automatically controlled connection to/from a standby gets lost with this behavior, while returned errors do not indicate the right solution, which is simple of course. One may

• change a password (to solve temporarily)
• or change the DEFAULT profile
• or change SYS profile to less restrictive

One must remember that after any of those operations the current logfile needs to be applied on a standby in order to see a positive change. After that automatic redo shipping started to work properly.

UPDATE on 2013-06-20
It seems that despite possible problems with passwords (which may arise with similar symptoms) in my particular case the true reason behind the ORA-1031/ORA-604 errors is the local coniguration - on the database is enabled VPD technology and an ON LOGON trigger to enforce the required context settings. The connections are successful only if done from a primary to a mounted standby. More on the subject in another article.

Installation of Oracle RDBMS on Oracle Linux 6

We install usually 2 Oracle products on database environments - RDBMS itself and Grid Control agent. The RDBMS binaries installation actually brings no problems - of course one needs X installation, which may be more than cautious sysadmin would like to install. The Grid agent is other thing - there is number of additional packages to install in order to make the installation process smooth. The best way on Oracle Linux 5 was to install package oracle-validated, which depends on few other packages, which fully cover the agent needs. With Oracle Linux 6 the package, which seems to be needed for that purpose is named oracle-rdbms-server-11gR2-preinstall (R must be in uppercase). However it seems it is not enough - additionally:

• yum install libXp.so.6
• yum install libXt.so.6
• yum install libXtst.so.6

Despite that another problem is I try to install the agent in 10.2.0.5 version, so must have used -ignoreSysPrereqs option. And now failure - some errors in linking. I will switch to 11.1.0.1.

Speeding up a standby recovery with incremental backup

Lately I have found a description for recovering using incremental backup. Despite speeding up a standby recovery (assuming the number of archivelogs to apply is much beyond the amount of changed data blocks) this method is useful for example for recovery from lost archivelog. I found some good articles on the topic above:

1. http://dbakerber.wordpress.com/2011/12/20/incremental-recovery-of-standby-asm-and-rman/
2. http://msutic.blogspot.com/2010/11/recover-from-incrementally-updated.html - this one is especially very precise

My personal goal was to speed up a standby recovery. I did it on the 11.2.0.2 version. I assume here disk channel, but tape is the same or even simpler as one do not need to transfer backup files from the primary to the standby host assuming centralized backup facility. The receipt is as follows:

1. make an incremental backup from SCN, at which our standby stopped (or actually it is better to backup slightly more then is needed, thus SCN should be a little lower then the really needed). This could be as simple as this:
   

   BACKUP INCREMENTAL FROM SCN  60839640548 DATABASE TAG='SCN_BEFORE_60839650548';
   

   Here a warning - this has to be a disk backup. In docs (at least for 10.2) there is stated that

   RMAN does not consider the incremental backup as part of a backup strategy at the source database.

   It vanished in later versions of docs (or at least I can not find it, but after perfoming an incremental backup to tape we were not able to list it).
2. make a copy of a current controlfile for standby:
   

   # from rman
   BACKUP CURRENT CONTROLFILE FOR STANDBY;
   # or from sqlplus
   ALTER DATABASE CREATE PHYSICAL STANDBY CONTROLFILE AS '/tmp/control01.ctl';
   

3. transfer both backups to the standby - starting from here all
4. restore control files with rman

   RESTORE STANDBY CONTROLFILE FROM '[controlfile path]';
   

   or simply replace existing control files if using sqlplus

   cp [controlfile path] [CONTROL_FILES parameter value] # for every position in CONTROL_FILES parameter of course
   

5. now mount the standby instance

   STARTUP NOMOUNT
   ALTER DATABASE MOUNT STANDBY DATABASE;
   

6. set STANDBY_FILE_MANAGEMENT to MANUAL (if set to AUTO)
7. catalog all the already existing on standby datafiles to control file

   CATALOG START WITH '[datafile path]'; # for every datafile path 
   

8. add all non-existent datafiles with sqlplus or with help of command SQL of rman

   ALTER DATABASE CREATE DATAFILE [file_id]; 
   

9. now is the time for switching the database to cataloged copies of datafiles. The simplest way is to:

   SWITCH DATABASE TO COPY;
   

   but if there were some non-existent files, which had to be added in the previous step, this will do not work. The source 2 provides a way to workaround it:

   CHANGE COPY OF DATAFILE [file_id] UNCATALOG;
   

   My way is to produce a script with a call to v$datafile:

   spool switch_files.rmn
   SELECT 'SWITCH DATAFILE '||file_id||' TO COPY;' rmn1 FROM v$datafile ORDER BY file#;
   spool off
   

   then execute it and edit the spooled content to remove unnecessary entries - among those also tries to switch lately created datafile stubs (stubs are already seen by the instance, so actually no need to switch to them).
10. now almost finish - recover database with rman - NOREDO forces rman to not use archivelogs at all

    RECOVER DATABASE NOREDO;
    

11. the rest is possibly to fix some small issues with redologs, which means usually clear logfiles and to start managed standby recovery
    • it is interesting that in order to clear standby logfiles one needs to put at work the parameter log_file_name_convert
    • it seems enabling the standby recovery may be performed at any SCN - we simply switch the recovery process from incremental backup to archived logs again. Now onwards we need to apply the logs requested by standby - assuming the managed standby, the log sequences are to be found in an alert log as entries about archive gaps.

      ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
      

And now we are with automatically running standby back again :-)

Data Pump and db links

The environment I work here with heavily relies on db links due to integration efforts. At the same time we create numerous test/dev environments for projects' needs. Usually for test/dev environment we are not able to provide all the connections or data, while trying to provide 1 to 1 metadata structure. Of course part of it becomes INVALID, but that is ok. The point is to do an import at the lowest cost (in terms of time and writing), thus keep with a good principle, the IT staff should be lazy. The problem is that during Data Pump imports the worker hangs on views built upon non-existing db links, possibly due to firewalling connections.

1. one may exclude schemas with db link (EXCLUDE=SCHEMA:" IN ('[username]')"
2. one may define TNS aliases - in our case due to firewalls there was a need to indicate true databases (no matter that there was no proper schemas on them)

Automatic TSPITR and dealing with it

The automatic TSPITR is very useful when we have an independent schema, which was logically corrupted by some of our actions. Let's say we started a batch processing, which is not a single transaction able to rollback, but rather hundreds of small changes to many tables and this processing failed being inside the run.
In the 11g database it is highly automated - theoretically one needs to run one RMAN command like below:

recover tablespace until time "to_date('2012-04-16 08:15:00','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/rman/aux';

Lately we use it for exactly the same reason I mentioned above - we restored a schema from a time before processing.

In the first place of course it is best not to recover at all, for example by making UNDO tablespace bigger (and I mean here a defense against ORA-01555 rather than making FLASHBACK TABLE operations possible).
However let's assume one is already in a trouble.

The automatic instance
According to our experience the most important thing is to tune the automatically run instance by providing additional settings.
The automatic instance by default runs on the following parameters:

System parameters with non-default values:
processes = 50
sga_target = 280M
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_dest_1 = "location=/rman/aux"
db_files = 200
db_create_file_dest = "/rman/aux"
db_name = "TEST"
db_unique_name = "xgFy_tspitr_TEST"

For us 2 things did not work well. The processes parameter was set too low - at the end of the whole TSPITR process the automatic instance failed due to exceeding the processes limit. This was probably due to some additional job processes, not sure why there were so many of them. Thus the processes value at 150 is much better setting.
Another thing to tune is the SGA_TARGET - for small transactions the default setting is probably ok - our process run smoothly through the files restore phase and majority of the recover phase, but not the whole. In our case doubling this value with setting 200M exclusively for shared pool helped, but this may vary.

What if it failed anyway
Failing the automatic/auxiliary instance is really a disaster to the TSPITR process, but we are able to run it again reusing partially the work done in the previous run. Unfortunately when failing the auxiliary instance removes all the files in auxiliary destination, which means we loose auxiliary control file and datafiles for UNDO, SYSTEM and SYSAUX.
The datafiles for recovered tablespaces are usually restored in the final production destination and they are left as of the moment of the auxiliary instance crash and this is what we could reuse, assuming the TSPITR process did not crash after open resetlogs operation. To use them we need to add to the RMAN RUN block CONFIGURE AUXNAME commands.
Depending on the TSPITR phase the whole process may fail leaving also files in the auxiliary destination, thus we are able to continue the TSPITR process manually. In such case first thing is to remove causes of the failure. One needs to create also its own pfile, where especially important is to know the value of the db_name parameter - those parameters may be taken from alert.log file of the auxiliary instance. After that we start the auxiliary instance.
There is a clone control file in use, so we need to mount database as clone database (ALTER DATABASE MOUNT CLONE DATABASE;).
Open it was a little tricky - we had to use RMAN (and not SQLPLUS) and connect as to auxiliary instance (rman auxiliary /), then simply open with resetlogs. After this moment the rest is quite simple - the best way is to follow a log from some previous TSPITR (such log may be found on Metalink for example). One needs to set the recovered files as READ-ONLY and create an alias for a dictionary to use by Data Pump (it is good to set the same dictionary as in the primary database). Then goes an expdp (expdp dumpfile= transport_tablespaces=, possibly TRANSPORT_FULL_CHECK, if needed).
Afterwards one may close the auxiliary instance, as this is not needed anymore. Then impdp to the primary database, setting tablespace to READ-WRITE again and voila - the thing is done.

Kudos to Pawel Smolarz :-)

Partitioning by interval

Few notes:

• interval needs to be a constant - we had here a time recorded as UNIX timestamp, thus in the database it was a NUMBER column to store those values - in this case to use interval we may partition by days or weeks
  
• interval constant type needs to be compatible with partition key data type (ie. number with number, date with time interval) - which is somewhat obvious
  
• at least one partition needs to be created in a "static" way (with the definition of the table) - so a database will have a starting point for next partitions, despite that the last "statically" added partition can not be removed - such activity finish with error ORA-14758.
• in order to archive/delete older partitions one may:
  
  • truncate the first "static" partition (ALTER TABLE blabla TRUNCATE PARTITION dont_delete DROP STORAGE UPDATE INDEXES;)
  • drop chosen partitions despite of the first one (ALTER TABLE blabla TRUNCATE PARTITION sys_536 UPDATE INDEXES;)
  • it is easy to automate dropping old partitions by dropping after every period the partition with partition_position=2 in dba_tab_partitions (or possibly an equivalent for subpartitions)

Cloning Grid Control agent binaries

Lastly we created a 2-node system in configuration active-standby. The environment for the second node was simply a copy of the first one. As all the paths were preserved then we successfully used the same Oracle Database binaries.
However when came to agents, when we run them on both nodes we get only one agent on the OMS. The other agent was seen as the same possibly - for example agents have a generated at the installation time agent seed, which is seen in several places - for example in emd.properties (as AgentSeed property) and in targets.xml (hashed/encrypted to AGENT_TOKEN). I have got an error about duplicating the agent. Possibly there are other such settings.

The solution is to clone the agent:
- copy binaries to the destination location
- run $ORACLE_HOME/oui/bin/runInstaller -clone -forceClone ORACLE_HOME= ORACLE_HOME_NAME= -noconfig -silent
- run $ORACLE_HOME/bin/agentca -f to run Agent Configuration Assistant
- run $ORACLE_HOME/root.sh as root (assuming this is not a first Oracle product on this machine, else run /oraInventory/orainstRoot.sh)

Location in Oracle docs here.

Error 1017 received logging on to the standby

I have got frequently the error "Error 1017 received logging on to the standby", while trying to enable the archivelog transfer between primary and standby.
The majority of tips on the net and the entry in the alert.log would tell You:
- check Your remote_login_passwordfile - should be SHARED or EXCLUSIVE
- check Your password file - it should exist and a password for SYS should be the same.
I have done as suggested, still no progress.

The solution was actually trivial - just copy the password file from the primary to the standby. Not sure why previously it did not work - I am sure the SYS password was the same and I checked connection in both directions (i.e. PRIMARY->STANDBY, STANDBY->PRIMARY) using the same entries in tnsnames.ora as specified in the archivelog transfer configuration.

It seems, there is a huge difference between versions 10g and 11g in this case - previously it was enough to create new password file with the same password. Now it must be the same file (ie. copy from the original on the primary).
And here is a very good article on the password files in Data Guard environment.

BIND_MISMATCH reason for not sharing cursor

BIND_MISMATCH reason is another one from the long list of possible reasons why not to share a cursor. General reason here is that bind variables between two executions differ too much for the database. The details are stored in the REASON column of the V$SQL_SHARED_CURSOR view. Here is how it looks like:


<childnode>
<childnumber>1</ChildNumber>
<id>40</ID>
<reason>Bind mismatch(22)</reason>
<size>4x4</size>
<bind_position>157</bind_position>
<original_oacflg>1</original_oacflg>
<original_oacmxl>32</original_oacmxl>
<upgradeable_new_oacmxl>128</upgradeable_new_oacmxl>
</ChildNode>




Whole thing is quite easy to decrypt. There is one node per an existing cursor child. The REASON tag provides one of reasons the database provides when it does not use an existing cursor child, but instead creates another one. The bind position indicates the position of the bind variable within a SQL text (every entry has a unique position per SQL even if we bind the same variable).
The ORIGINAL_OACFLG tag IMHO is the same entity as the OACFLG (or first FLG) in a bind section of a 10046 event raw trace and of course there is more equivalents.

The bind variables use some standard size buffers, which depend on data type. For varchar2 there are several sizes (due to varying nature of the type) and first size amounts to 32, so the minimum buffer length for bind variable of varchar2 is 32 bytes. The next values are 128 bytes, then 2000 bytes, and so on.

The change between different values of a bind variable may become a reason for not sharing a cursor . This happens when a buffer size of a variable changes - for example from smaller to larger.

Some details about bind section of the 10046 event trace

In general very good description of the 10046 event trace interpretation is contained on MOS as the article with 39817.1 id.
However with time there showed new entries not really mentioned in the article indicated above.
Let's see an example of the bind section:

Bind#2
oacdty=96 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=02 csi=2000 siz=0 off=56
kxsbbbfp=7fb04313fe80 bln=32 avl=02 flg=01
value=0 54

oac prefix I simply ignore and not sure what it stands for.

• dty is an id for data type
• mxl is a maximum length of bind variable (in parentheses there is provided a private maximum length - for me this is current bind variable length)
• mxlc is not sure for me, but I saw it always equal to 00
• mal is a maximum array length
• scl stands for scale
• pre stands for precision
• flg is a flag with bind options
• fl2 is a continuation of flag
• frm stays unknown
• csi is character set id, which can be decrypted with nls_charset_name function; the nls_charset_id function, working in an opposite way, is available as well
• siz is an amount of memory allocated for this chunk
• off is a memory offset of a buffer for this variable within the chunk
• kxsbbbfp is a bind addres
• bln is a bind buffer length and I suppose usually it is equal to mxl, unless data does not fit into one chunk
• avl is a current bind value length (or array length)
• flg is another flag (this time for bind status)
• value - finally current value

ROLL_INVALID_MISMATCH reason for not sharing cursor

Since 10g there was introduced a change in the way the cursors are invalidated. Now they are invalidated in a rolling fashion (i.e. not all at once), hence the reason ROLL_INVALID_MISMATCH.
The excellent article on this theme at The Dutch Prutser's Blog

Cursors with high VERSION_COUNT

Symptoms
Since upgrade to 11gR2 from time to time we experience problems with excessive number of cursor children per some cursors. Symptoms are common - many active sessions with waits on mutex X, mutex S, library cache latch, and few others, high VERSION_COUNT for those cursors.
What happens?
It seems those all waits are due to very slow work of shared pool for those cursors. My understanding of this issue is all the children of such cursor are sharing the same sql_id and hash_value, so eventually they end up in the same bucket of library cache and the more of them, the longer sessions need to hold the latch to library cache or mutexes in order to check all the children to choose the possibly matched for sharing.

Root causes
The root causes for multiplying the cursor children are various. Some reasons are provided by the V$SQL_SHARED_CURSOR, there is a plenty of bugs (search term high VERSION_COUNT on Metalink).
In our case the reasons are few and not sure the concretes, as few explanations are possible. The bug is one of them, however as probable is wrong use of bind variables. From V$SQL_SHARED_CURSOR we get as a reason mainly BIND_MISMATCH, with possible addition of BIND_LENGTH_UPGRADEABLE. Partially this is due to the change of a bind variable length (the buffer for bind value is allocated up to 32 bytes or up to 128 bytes or up to 2000 bytes - at least those were revealed by observing trace 10046, so I would not say how it is beyond 2000 bytes - are there other thresholds or the bind possible maximum is the limit).

Workarounds
Whatever the reasons workarounds are similar and I dare to say "officially" used. What is important this behavior is not seen on 10g (or I would rather say it is hidden). This is due to the obsoleting cursors with more than 1024 children, what is not a case with 11g. So the clue of a workaround is mimic this by:

1. workaround in the form of purging excessive children with use of dbms_shared_pool.purge
   
2. hidden parameter _cursor_obsolete_threshold and the MOS article 10187168.8
   

True solutions They depend on the root causes. This varies from applying patch to changing the SQL to changing bind variables (size or type) to impossible or better say independent of our doings - great example/article is the entry by Martin Klier. Update 2013.08.08 Few weeks ago we have another attack of mutexes' waits. It was another bug (11930680), where the workaround was to disable optimizer_secure_view_merging. The diagnosis method for such problems may be called generic - when one see a huge bunch of session waiting on "mutex X" or "mutex S" or "cursor pin S wait on X" and all of them try to perform the same query then:

• count the number of children for the query
• if high (few hundreds to thousands) look at V$SQL_SHARED_CURSOR in order to figure out why there are so many of them
• google Google (or any other favourite search engine) and MOS with the reasons found in V$SQL_SHARED_CURSOR
• as a symptom softening measure one may run the cursor purge as a job in short interval

On the grounds of short experience I may say that usually the very high cursor children count is brought by some bug. With poorly written code we hit ~200 children per cursor, with bugs it was up to 5.000 or more. But as I said the experience is short...
Here one may find that similar contention may arise from different than bug reasons.