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.