Supplemental logging (external link)

I have just planned to prepare an article on the supplemental logging subject, but a colleague of mine sent me a link to excellent and thorough work by Julian Dyke.

Zabbix on Oracle RDBMS and ORA-00001

Scenario

We performed today a small experiment with Zabbix - fast database restart with hope to keep the application alive based on the memory buffer and data kept in it during the database outage. This approach has failed even if sometimes we see Zabbix working through some time even with the database disabled.
Just after immediate database restart we observed a whole bunch of errors like the one below:

18264:20140512:134056.833 [Z3005] query failed: [-1] ORA-00001: unique constraint (ZABBIX.EVENTS_PK) violated [insert into 
events (eventid,source,object,objectid,clock,ns,value,value_changed) values (610764154,1,1,157,1399894856,0,1,0)]

Diagnosis

I set the diagnosis completely based on the symptoms, so it may seem not exact, but I suppose it is proper enough. In general it is bound with the mechanism for creating the ids for rows in Zabbix tables. The mechanism looks like the following:

• an id is built from the value selected from the ids table for a row designated for a particular table increased by 1
• the new value is used with a new row
• the nextid value in the ids table is increased by one

Thus the ids table is used as an equivalent of sequence functionality. It seems however, that the values in this table not necessarily are chosen and updated within one transaction. Thus in the scenario like the one below we end up with the nextid value lower than the maximal value in tables to which Zabbix tries to insert new rows and those inserts fail with the error presented above.
In particular the frequent table with which we have trouble is the events table. Nothing strange though

Solution

UPDATE ZABBIX.IDS SET NEXTID=(select max(eventid) from events)+1 WHERE TABLE_NAME='events' AND FIELD_NAME='eventid';
commit;

Compatibility on active-passive configuration and ORA-600

Today we experienced some small issues on one of the production environments. And btw. the following story I would like to share with.

Situation overview

The database in question works in active-passive configuration with managed standby recovery enabled. From time to time it has some problems with memory settings. As this issue repeats for some time we are already quite experienced with it and act appropriately. And because of nature of this database traffic we are able to run some experiments. And today was also such day.

Compatible parameter

Among other adjustments and settings we've changed the compatible parameter from 11.2.0.0.0 to 11.2.0.3.0. But the change was done only on the active node.

Output

The first what we've got was the ORA-600 error.

[.. from alert.log ..]
Mon May 12 08:48:10 2014
RFS[13]: Assigned to RFS process 7418
RFS[13]: Selected log 7 for thread 1 sequence 140861 dbid -1879016249 branch 793971529
Errors in file /oracle/diag/rdbms/zabbix_node2/zabbix/trace/zabbix_rfs_7412.trc  (incident=192281):
ORA-00600: internal error code, arguments: [2730], [331], [1], [5], [140862], [140862], [512], [512], [], [], [], []

[.. from one of trace files ..]
*** ACTION NAME:() 2014-05-12 08:48:09.667
 
Identified standby redo log 5 for implicit mid-log reconnect
DDE: Problem Key 'ORA 600 [2730]' was flood controlled (0x2) (incident: 192281)
ORA-00600: internal error code, arguments: [2730], [331], [1], [5], [140862], [140862], [512], [512], [], [], [], []
Exception 600 received while writing lno 5 thread 1 seq 140862
*** 2014-05-12 08:48:10.517 4638 krsb.c
krsb_stream_write: Error 600 while attempting to write buffer
krsv_dsga: Dispatching RFS shutdown notification

Of course as all the ORA-600 quite misterious.
As I was not sure at which archivelog the problem appeared and was too lazy to look through the alert log, I simply run in SQL*Plus (nota bene 5th and 6th arguments of the ORA-600 indicate the archivelog sequence on which the problem emerges, we had already few such archivelogs, so one had to find the first occurence of the problem anyway)

recover standby database until cancel

. Now things were much more clear - at one of the archivelogs I have simply got an info, that the server can not process the archivelog with compatibility set to higher than currently set. It was enough to set the compatibility accordingly and restart the standby.

Fast refresh on a mview based on pre-built table (external link)

Lately I faced the following problem - what is faster: manual load or mview refresh. I would not tell - while mview refresh is a direct copy from a source to a destination, the manual load offers full control over the whole process, so we may additionally cut it according to our needs. There is one obstacle though in this particular issue - the final result should be an mview, we may refresh incrementally.
I was not sure if this is possible to run a fast refresh without initial complete refresh - if it would be impossible then the whole concept with the manual load would be useless regarding the circumstances. And during the standard Google search I hit this article.

In summary it proofs the thing is really fairly easy. I played a little with it and it seems the refresh is really a dumb machanism in this case (which works in our favour). In short it does not check when one initially loaded the pre-built table or which change was last or any SCN or whatever the implementers thought of. It simply assumes a user knows what he/she does, which approach is one of my favorite ones. Thus assuming one have a source table with a primary key defined, a pre-built table initially loaded with (some) data shaped according to one's needs, a mview log on the source table and a mview defined on top of the pre-built table the call to refresh the materialized view looks into the mlog, collects operations stored there and applies them to the destination mview without worrying about the integrity of the data - exactly what I tried to achieve.

Copying partition statistics (external link)

I have found today a very fresh article on the subject of copying the partition statistics. When copying, one have to understand which statistics are actually copied. E.g. in case of partitions one of concerns is how would like like the copied low and high values. Here (http://orastory.wordpress.com/2012/06/14/copy_table_stats/) You may find some paper touching that issue.

And 2 more:

• http://dioncho.wordpress.com/2009/02/12/how-to-copy-partition-stats-we-got-easier-way/
• https://blogs.oracle.com/optimizer/entry/maintaining_statistics_on_large_partitioned_tables

DB2 backup howto

Disclaimer

I am absolute novice in the world of the DB2 database. Thus I incorporate both a complete ignorance and a fresh look at the same time ;-), so take it into consideration.

Preparations

# set log archives 
mkdir /db2arch
# the logarchmeth1 is a equivalent to the Oracle 
# log_archive_dest_1='LOCATION="/db2arch"'
db2 "update db cfg for BPMDB using logarchmeth1 disk:/db2arch/"
# this is about compression, but do not know the details
db2 "update db cfg for BPMDB using logarchcompr1 on"

# and here a check if all is set as expected
db2 "GET DATABASE CONFIGURATION FOR ${database}"
[..]
 First log archive method                 (LOGARCHMETH1) = DISK:/db2arch/
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = ON
 Options for logarchmeth1                  (LOGARCHOPT1) = 
 Second log archive method                (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) = 
[..]

# after the preparations one have to perform once more a full backup offline
db2 "backup database ${database} to /db2backup"

Online backup operation

# load the profile 
. $HOME/sqllib/db2profile

# list databases
db2 list database directory 

# get database configuration
db2 "GET DATABASE CONFIGURATION FOR ${database}"

# the important parameters are:
# TRACKMOD, LOGARCHMETH1, LOGARCHMETH2, LOGARCHOPT1, LOGARCHOPT2
# I simply get them from the above command for configuration
# Because I want few of them, I call for all parameters, then grep, 
# but one may call only those of interest

# finally backup command
cmd="BACKUP DATABASE ${database} ONLINE"
if [ ${opt_tracking##*=} = 'YES' ]; then
  cmd="$cmd INCREMENTAL " ## here one may add also DELTA
fi
cmd="$cmd TO \"${BKP_DIR}\" "
cmd="$cmd WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 INCLUDE LOGS "
cmd="$cmd WITHOUT PROMPTING"
db2 "$cmd"

DBMS_REDEFINITION howto (external link)

http://www.ora-solutions.net/papers/Online_Redefinition_Oracle_10gR2.pdf - under this link one may find quite concise and thorough example of the DBMS_REDEFINITION package. Though a little bit old (version 10g R2), still it provides the MUST-KNOW content.