Some notes on materialized views

Few loosely notes on materialized views.

It is possible to build a mview on a pre-built table. I like this feature mainly because one is able to drop such view and recreate it with slightly changed definition, while the data is preserved, which is very valuable especially when dealing with large source tables.

Another observation is that sometimes the casting to right types is necessary. We met several times the situation when on Linux some mview definition worked properly while on AIX it failed due to not strong type casting, when the solution was to use CAST with precision to type and length (usually the problem core was longer VARCHAR2 than expected).

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.