EXPLORING ORACLE

ORA-27546: Oracle compiled against IPC interface version 3.2 found version 3.3

2011-05-09T18:22:00.000-07:00

Background:

Customer upgraded 10204 CRS and ASM to 11202 Grid Infrastructure.
Customer upgraded his 10204 Databases to 11202 Database.
Customer verified all the jobs and application to be working fine.
Customer uninstalled OLD 10204 CRS, ASM & Database home.

Symptoms:

Their nightly export job failed with following error.

UDE-27546: operation generated ORACLE error 27546
ORA-27546: Oracle compiled against IPC interface version 3.2 found version 3.3

Further investigation revealed that even normal connections to 11202 database was failing with following error.

ORA-27546: Oracle compiled against IPC interface version 3.2 found version 3.3

Cause:
Further research revealed that the error was caused due to library mismatch their RAC Nodes for libskgxp10.so in $ORACLE_HOME/lib.

Apparently, this library was found to have different timestamp and size between node 1 and rest of the nodes in cluster which is caused by Bug: 5474623 - DEINSTALL OF ONE ORACLE HOME AFFECTS ANOTHER ORACLE HOME.

As per the bug, OUI picks up the environment ORACLE_HOME variable and cleans up the library file libskgxp10.so from that particular ORACLE_HOME irrespective of which ORACLE_HOME you are uninstalling.

Customer uninstalled 10204 CRS, ASM and DB home while ORACLE_HOME environment variable was set to 11202 Database home.

Solution:
Copy the good copy of libskgxp10.so from any other node (11202 ORACLE_HOME/lib) in the cluster to the problematic node (11202 ORACLE_HOME/lib) from where uninstall was started.

/etc/oratab & # line added by Agent

2011-02-09T15:15:00.000-08:00

We upgraded our Oracle technology stack from 10204 to 11202. We are running Oracle Grid infrastructure 11202 on 4 node RAC, which has many 11202 database running it.

We use /etc/oratab extensively on all our scripts. For example: backup scripts, application scripts which needs to connect to database etc..

In a RAC environment /etc/oratab only contains the DB_UNIQUE_NAME for cluster database instead of ORACLE_SID And most of our scripts use ORACLE_SID instead of DB_UNIQUE_NAME. As result of this we modifed /etc/oratab to point to ORACLE_SID rather than DB_UNIQUE_NAME. Thiw worked well in 10g.

Since we upgraded to Oracle 11202, we started having new entry in our /etc/oratab with DB_UNIQUE_NAME like below.

TEST:/app/oracle/db/11.2/db_1:N # line added by Agent
PROD:/app/oracle/db/11.2/db_1:N # line added by Agent
DR:/app/oracle/db/11.2/db_1:N # line added by Agent

This cause most of our scripts to fail and as result I modified the /etc/oratab manually and removed those entries and surprisingly in couple of days those entries will come back and our script will fail all over again.

I looked up in google and metalink and found couple of bugs which said that its an expected behaviour but no one explained why this is happening or who updates the /etc/oratab at regular intervals.

Further research revealed that ORACLE agent updates the /etc/oratab everytime you restart the database.

Logfile /app/oracle/grid/11.2/grid_1/log/node01/agent/crsd/oraagent_oracle has the following entries to confirm that.

oraagent_oracle.l01:2011-02-10 08:35:52.745: [ora.test.db][1346505024] {1:35347:18516} [start] sUpdateOratab oratab is /etc/oratab
oraagent_oracle.l01:2011-02-10 08:35:52.760: [ora.test.db][1346505024] {1:35347:18516} [start] ConfigFile::updateInPlace file /etc/oratab is updated
oraagent_oracle.l01:2011-02-10 08:37:06.316: [ora.test.db][3187673408] {1:35347:18560} [start] sUpdateOratab oratab is /etc/oratab
oraagent_oracle.l01:2011-02-10 08:37:06.329: [ora.test.db][3187673408] {1:35347:18560} [start] ConfigFile::updateInPlace file /etc/oratab is updated

Based on that, we can say that,

The oratab file entry is also updated automatically by the oraagent - New process introduced in 11g (not to be confused with Oracle EM agent) when a database started or shutdown.

Oracle creates an entry for each Oracle RAC database in the oratab configuration file Database name or DB_UNIQUE_NAME and not the ORACLE_SID. The oratab file is created by the root.sh script during installation, and it is updated by the Database Configuration Assistant when creating or deleting a database.

ORA-15186: ASMLIB error function = [asm_open], error = [1], mesg = [Operation not permitted]

2011-02-07T20:39:00.000-08:00

Recently, I was involved in OS rolling upgrade for 3-node RAC running on 10204 with ASMLIB. I had performed quite a few OS rolling upgrades in past so I had my checklist or documentation handy on steps to re-configure ASMLIB.

The upgrade worked fine on node 1 and ASM re-configuration worked fine. However on node 2, after patching OS and re-configuring ASMLIB, asm disk group failed mount with folowing error.

ORA-15186: ASMLIB error function = [asm_open], error = [1], mesg = [Operation not permitted]

Based on my previous experience, I was positive that this error is related multipathing. Most probably the root cause is ORACLE_SCANORDER parameter in /etc/sysconfig/oracleasm file.

I verified the value and it was set correctly as below.

# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER="dm"

My asm commands, querydisk, listdisk, scandisk were working fine.

However, ASM diskgroup failed to mount with above errors.

To further investigate, I checked /dev/oracleasm/disks and /proc/partitions

ls -l /dev/oracleasm/disks
total 0
brw-rw---- 1 oracle dba 65, 176 Feb 6 17:04 TESTORA2_ASM1
brw-rw---- 1 oracle dba 65, 192 Feb 6 17:04 TESTORA2_ASM2
brw-rw---- 1 oracle dba 65, 208 Feb 6 17:04 TESTORA2_ASM3
brw-rw---- 1 oracle dba 65, 224 Feb 6 17:04 TESTORA2_ASM4

cat /proc/partitions
....
....
....
65 176 209715200 sdab
65 192 209715200 sdac
65 208 209715200 sdad
65 224 209715200 sdae
....
....
....
253 9 209715200 dm-9
253 10 209715200 dm-10
253 11 209715200 dm-11
253 12 209715200 dm-12
....
....
....

If you compare above to output you can see that my ls -l /dev/oracleasm/disks output (major, minor) matches to cat /proc/paritions outpu (major, minor) sd* disks and not dm-* disks. Even though my ORACLEASM_SCANORDER="dm" set correctly, ASM is not looking at the multipath disks but a single disks.

I tried restarting ASM service couple of times and it didn't help. I tried changing the ORACLEASM_SCANORDER="sd" as well and still no luck. Based on this, I thought for some reason ASMLIB was not reading the /etc/sysconfig/oracleasm file at all.

I decided to raise the SR with Oracle support and luckily I got a call from very knowlegeble senior support analyst and he confirmed that using the strace on /etc/init.d/oracleasm start command.

In strace it was visible that instead of reading the /etc/sysconfig/oracleasm file it was reading the file called oracleasm-_dev_oracleasm.

We ran ls command on other sever to compare with this server.

On working server
------------------

ls -l /etc/sysconfig/oracleasm*

lrwxrwxrwx 1 root root 24 Feb 6 16:57 oracleasm -> oracleasm-_dev_oracleasm
-rw-r--r-- 1 root root 776 Feb 6 17:00 oracleasm-_dev_oracleasm
-rw-r--r-- 1 root root 863 May 12 2010 oracleasm.rpmsave

On a Problematic Server
------------------------

ls -l oracleasm*
-rw-r--r-- 1 root root 24 Feb 6 16:57 oracleasm
-rw-r--r-- 1 root root 776 Feb 6 17:00 oracleasm-_dev_oracleasm
-rw-r--r-- 1 root root 863 May 12 2010 oracleasm.rpmsave

You can see that a on a problematic server, oracleasm is a text file instead of symbolic link. Further investigation revealed that while cleanup of OLD ASMLIB, file oracleasm-_dev_oracleasm was left out and due to this new installtion failed to create the symbolic link.

I recreated the symbolic link manually and changed the ORACLEASM_SCANORDER="dm" and restarted the ASM service and all started working properly.

Hope this helps...

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS Failed with ORA-01408: such column list already indexed

2011-01-17T21:28:00.000-08:00

Recently I was engaged in exercise to partition a table in a database. I was using a DBMS_REDEFINITION package to partition the online.

As per documentation,

There may be times when you want to override the actions of DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( ). For example, an index must be moved to a different tablespace. This can be achieved by making the relevant changes on the interim table, and then using DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT() to register the new version of the object. Then when DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( ) is executed, it will ignore that object, in this case the index, which has already been defined.

However, during my test, I realized that this is not entirely true. It complained about my index which was supporting my primary key constraint.

Let's see that in following example:

Creating Source Table
---------------------
SQL> create table t1 as select * from all_users;
Table created.

SQL> alter table t1 add constraint t1_pk primary key(user_id);
Table altered.

SQL> select index_name from user_indexes;
INDEX_NAME
-----------------------------
T1_PK

Creating Interim Partitioned Table
----------------------------------
SQL> create table t2 partition by hash(user_id) partitions 8 as select * from t1 where 1=2;
Table created.

Moving the tablespace of INDEX on interim Table
-----------------------------------------------
SQL> create index t2_Pk on t2 (user_id) tablespace system;
Index created.

Start online re-definition
---------------------------
SQL> exec dbms_redefinition.can_redef_table( user, 'T1' );
PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.start_redef_table( user, 'T1', 'T2' );
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('TEST', 'T1', 'T2', DBMS_REDEFINITION.CONS_INDEX, 'TEST', 'T1_PK', 'T2_PK');
PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON;
VARIABLE NERRORS NUMBER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ( 'TEST', 'T1', 'T2', 1, TRUE, TRUE, TRUE , FALSE, NUM_ERRORS => :NERRORS );
END;
/

BEGIN
*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at "SYS.DBMS_REDEFINITION", line 984
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1727
ORA-06512: at line 2

Following query confirms that
------------------------------
SQL> select * From dba_redefinition_errors;
OBJECT_TYP OBJECT_OWNER OBJECT_NAME
---------- ------------------------------ ------------------------------
BASE_TABLE_OWNER BASE_TABLE_NAME
------------------------------ ------------------------------
DDL_TXT
--------------------------------------------------------------------------------

CONSTRAINT TEST T1_PK
TEST T1
ALTER TABLE "TEST"."T2" ADD CONSTRAINT "TMP$$_T1_PK0" PRIMARY KEY ("USER_ID")

Removing the Index
------------------
SQL> BEGIN
DBMS_REDEFINITION.unregister_dependent_object ( 'TEST', 'T1', 'T2', DBMS_REDEFINITION.CONS_INDEX, 'TEST', 'T1_PK', 'T2_PK');
END;
/
PL/SQL procedure successfully completed.

SQL> DROP INDEX T2_PK;
Index dropped.

No re-try the copy constraints
------------------------------
SQL> SET SERVEROUTPUT ON;
VARIABLE NERRORS NUMBER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ( 'TEST', 'T1', 'T2', 1, TRUE, TRUE, TRUE , FALSE, NUM_ERRORS => :NERRORS );
END;
/
PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.finish_redef_table( user, 'T1', 'T2' );
PL/SQL procedure successfully completed.

Hope this helps...

Drop tablespace waiting on 'reliable message'

2011-01-13T17:14:00.000-08:00

Lats week, I droped a tablespace from production database.

SQL> select count(*) from dba_segments where tablespace_name='TEST';
COUNT(*)
----------
0
SQL> drop tablespace TEST including contents and datafiles;

Its just hung there for more than 2 hours.

I looked at the alert log...

Wed Jan 12 11:10:39 2011
drop tablespace test including contents and datafiles
Wed Jan 12 11:11:52 2011
Deleted Oracle managed file +DGROUP1/PROD/datafile/test.339.666449433
Wed Jan 12 11:12:12 2011
Deleted Oracle managed file +DGROUP1/PROD/datafile/test.1104.672507107
Wed Jan 12 11:12:29 2011
Deleted Oracle managed file +DGROUP1/PROD/datafile/test.1838.684692483
Wed Jan 12 11:12:45 2011
Deleted Oracle managed file +DGROUP1/PROD/datafile/test.1955.691083671
Wed Jan 12 11:39:48 2011
Thread 1 advanced to log sequence 94797 (LGWR switch)
Current log# 7 seq# 94797 mem# 0: +DGROUP2/PROD/redo07a.dbf
Wed Jan 12 11:39:50 2011
LNS: Standby redo logfile selected for thread 1 sequence 94797 for destination LOG_ARCHIVE_DEST_2
Wed Jan 12 11:42:35 2011

Looks like all the files have been dropped physically from ASM.

Checked the database...

SQL> select * from v$tablespace where name='TEST';

no rows selected

SQL> select file_name from dba_data_files where tablespace_name='TEST';

no rows selected

In spite of all this, my initial sqlplus session with drop tablespace was still hung...

Looking V$session_wait it was waiting for 'reliable message' for last 2 hours.

SQL> select event, p1, p2, state from v$session_wait where sid=836;
EVENT P1 P2 STATE
---------------------------------------------------------------- ----------
reliable message 6852580008 6968627176 WAITING
SQL> /
reliable message 6852580008 6968627176 WAITING
SQL> /
reliable message 6852580008 6968627176 WAITING
SQL> /
reliable message 6852580008 6968627176 WAITING

I gogled a bit, couldn't find anything. Metalink search got me few hits though...

Bug 6148054 RAC hang waiting for "reliable message"
Bug 4635062 drop tablespace session waiting for reliable message

Though, this confirmed that I was hitting either of the oracle bug, none of them had a solution on what to do for exisitng hung session. One off the bug did had a suggestion to kill the session from other session using alter system command. However, there wasn't enough information on if that resolved the issue or not.

AS I had no other option, I killed my drop tablespace session from other session using alter system command and drop tablespace worked.

Watch out when using RMAN custom backup piece naming convention via FORMAT

2011-01-13T16:03:00.000-08:00

Yesterday, I was asked to investigate the backup restore issue of production database for one off my client. Customer was taking incremental level 0 backups on Sunday and cumulative incremental level 1 backups on Monday-Saturday. Customer had successful backup for all one month, no errors in the backup log files, RMAN Catalog list valid backups available for last one month.

Customer was trying to restore and recover a backup as of Monday morning. In spite successful incremental level 0 backup on Sunday night, restore as of Monday morning was failing. It was complaining about backup being not found for certain files in backup set of Sunday and it was trying to read the backup piece of previous Sunday and for some files 4 to 6 weeks old backup piece.
Customer had no clue on why RMAN was able to restore few files from last night (Sunday) incremental level 0 backup however, trying to read a backup piece 3 to 4 weeks old for other files.

Customer was using the following script to perform the backup.

run
{
allocate channel tdp1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

allocate channel tdp2 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

BACKUP INCREMENTAL LEVEL 0 DATABASE FORMAT='DB_LVL0_%d_%T%t_%p_%c' PLUS ARCHIVELOG NOT BACKED UP 2 TIMES FORMAT='LG_%d_%T%t_%p_%c';
}

Then I looked at the backup log files…

Starting backup at 10-JAN-2011 05:18:50
channel tdp1: starting incremental level 0 datafile backupset
channel tdp1: specifying datafile(s) in backupset
input datafile fno=00006 name=+DGROUP1/prod/datafile/PROD_data.1138.704640745
input datafile fno=00007 name=+DGROUP1/prod/datafile/PROD_index.1147.704640829
input datafile fno=00014 name=+DGROUP1/prod/datafile/PROD_data.2100.725724753
input datafile fno=00002 name=+DGROUP1/prod/datafile/undotbs1.1067.704635111
input datafile fno=00010 name=+DGROUP1/prod/datafile/PROD_data.1111.719594721
input datafile fno=00013 name=+DGROUP1/prod/datafile/PROD_data.1278.721149489
input datafile fno=00005 name=+DGROUP1/prod/datafile/users.1210.704635119
input datafile fno=00016 name=+DGROUP1/prod/datafile/PROD_index.1435.726672149
channel tdp1: starting piece 1 at 10-JAN-2011 05:18:51
channel tdp2: starting incremental level 0 datafile backupset
channel tdp2: specifying datafile(s) in backupset
input datafile fno=00004 name=+DGROUP1/prod/datafile/undotbs2.2251.704635119
input datafile fno=00015 name=+DGROUP1/prod/datafile/PROD_data.1080.725725677
input datafile fno=00017 name=+DGROUP1/prod/datafile/PROD_index.1090.734796557
input datafile fno=00012 name=+DGROUP1/prod/datafile/PROD_data.1951.721149479
input datafile fno=00009 name=+DGROUP1/prod/datafile/PROD_data.395.717544159
input datafile fno=00011 name=+DGROUP1/prod/datafile/PROD_data.1162.719594733
input datafile fno=00003 name=+DGROUP1/prod/datafile/sysaux.1922.704635113
input datafile fno=00001 name=+DGROUP1/prod/datafile/system.1752.704635103
input datafile fno=00008 name=+DGROUP1/prod/datafile/drsys.770.712926249
channel tdp2: starting piece 1 at 10-JAN-2011 05:18:51
channel tdp2: finished piece 1 at 10-JAN-2011 05:49:39
piece handle=DB_LVL0_prod_20110110740035131_1_1 tag=TAG20110110T051851 comment=API Version 2.0,MMS Version 5.5.1.0
channel tdp2: backup set complete, elapsed time: 00:30:48
channel tdp1: finished piece 1 at 10-JAN-2011 05:56:45
piece handle=DB_LVL0_prod_20110110740035131_1_1 tag=TAG20110110T051851 comment=API Version 2.0,MMS Version 5.5.1.0
channel tdp1: backup set complete, elapsed time: 00:37:54
Finished backup at 10-JAN-2011 05:56:45

If you look at the log files very carefully, it allocated 2 channels and distributes the all the datafiles in 2 sets and starts the backups. However, if you look at the backup piece name for both channels, it’s exactly same. As a result, when which ever channel finished lasts it overwrites the backup taken by earlier channel. So if you look at the above output, channel 1 td1 and channel 2 tdp2 started the backup but the channel 2 finished backup first with the name DB_LVL0_PROD_20110110740035131_1_1. Later on when channel 1 finished the backup it reused the same backup piece name DB_LVL0_PROD_20110110740035131_1_1. Due to this all the files backed by channel 2 tdp2 are completely lost. And the surprisingly, RMAN doesn’t complain at all about this. RMAN Backup log file looks clear with return code 0.
However, when it comes to restore, RMAN can only restore the set of files backup by channel 1 tdp1 which finished last and its has no backup available for files backed by channel 2 tdp2.

Let’s confirm this by looking at RMAN catalog output. We already saw RMAN backup log it says it backed up all datafiles without any error.

Let’s run Report Schema to list the datafiles in database.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 530 SYSTEM *** +DGROUP1/prod/datafile/system.1752.704635103
2 13720 UNDOTBS1 *** +DGROUP1/prod/datafile/undotbs1.1067.704635111
3 970 SYSAUX *** +DGROUP1/prod/datafile/sysaux.1922.704635113
4 23395 UNDOTBS2 *** +DGROUP1/prod/datafile/undotbs2.2251.704635119
5 5 USERS *** +DGROUP1/prod/datafile/users.1210.704635119
6 20480 PROD_DATA *** +DGROUP1/prod/datafile/PROD_data.1138.704640745
7 18960 PROD_INDEX *** +DGROUP1/prod/datafile/PROD_index.1147.704640829
8 20 DRSYS *** +DGROUP1/prod/datafile/drsys.770.712926249
9 8192 PROD_DATA *** +DGROUP1/prod/datafile/PROD_data.395.717544159
10 8192 PROD_DATA *** +DGROUP1/prod/datafile/PROD_data.1111.719594721
11 8192 PROD_DATA *** +DGROUP1/prod/datafile/PROD_data.1162.719594733
12 8196 PROD_DATA *** +DGROUP1/prod/datafile/PROD_data.1951.721149479
13 8192 PROD_DATA *** +DGROUP1/prod/datafile/PROD_data.1278.721149489
14 18692 PROD_DATA *** +DGROUP1/prod/datafile/PROD_data.2100.725724753
15 18992 PROD_DATA *** +DGROUP1/prod/datafile/PROD_data.1080.725725677
16 8192 PROD_INDEX *** +DGROUP1/prod/datafile/PROD_index.1435.726672149
17 16384 PROD_INDEX *** +DGROUP1/prod/datafile/PROD_index.1090.734796557

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 15360 TEMP 15360 +DGROUP1/prod/tempfile/temp.309.704635115
2 8192 TEMP 8192 +DGROUP1/prod/tempfile/temp.1141.735345399

Now let’s grab the backup tag from backup log file and list the backup piece and datafiles in that backup tag.

RMAN> list backup tag TAG20110110T051851;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
5169802 Incr 0 93.70G SBT_TAPE 00:37:50 10-JAN-2011 05:56:41
BP Key: 5169806 Status: AVAILABLE Compressed: NO Tag: TAG20110110T051851
Handle: DB_LVL0_prod_20110110740035131_1_1 Media:
List of Datafiles in backup set 5169802
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
2 0 Incr 7310305942 10-JAN-2011 05:18:51 +DGROUP1/prod/datafile/undotbs1.1067.704635111
5 0 Incr 7310305942 10-JAN-2011 05:18:51 +DGROUP1/prod/datafile/users.1210.704635119
6 0 Incr 7310305942 10-JAN-2011 05:18:51 +DGROUP1/prod/datafile/norkom_data.1138.704640745
7 0 Incr 7310305942 10-JAN-2011 05:18:51 +DGROUP1/prod/datafile/norkom_index.1147.704640829
10 0 Incr 7310305942 10-JAN-2011 05:18:51 +DGROUP1/prod/datafile/norkom_data.1111.719594721
13 0 Incr 7310305942 10-JAN-2011 05:18:51 +DGROUP1/prod/datafile/norkom_data.1278.721149489
14 0 Incr 7310305942 10-JAN-2011 05:18:51 +DGROUP1/prod/datafile/norkom_data.2100.725724753
16 0 Incr 7310305942 10-JAN-2011 05:18:51 +DGROUP1/prod/datafile/norkom_index.1435.726672149

RMAN>

From the above output you can clearly see that there set of datafiles missing in the backup piece. That’s why customer was able to restore set of files from recent backup and for rest of the files RMAN was looking for backup piece which 3 to 4 weeks old.

So be cautious when you are using customized RMAN backup piece name using format keyword. Make sure you use %u which guarantees unique name for your backup piece.

ORA-31619: invalid dump file for IMPDP

2010-08-17T16:23:00.000-07:00

We had a EXPDP script to export the application schema on daily basis.

We were writing data pump files to WINDOWS files system mounted to LINUX system with CIFS option. We used parallel=2 and generated 2 files in parallel.

EXPDP always worked fine without any issues.

Last night I had to restore the EXPORT in DEV invironment and the IMPDP failed with following error.

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "/app/edws/fileload/expdp/SYSMAN_201.dmp"

After breaking my head for a while, I found that if EXPDP has parallel set > 1 and if you write to 2 multiple files on CIFS mounted file system, it corrupts the file.

Your IMPDP throws above error. I got mixed resulst from Metalink.
Bug # 8313127 says CIFS file system is not supported for data pump.
Note: 444809.1 says its supported but not certified.

Based on my test, you write to one file, you are still safe. But if you use parallel option, you are files are corrupted and can't be used for IMPORT.

So for those who are using CIFS mount please be aware of that.

How to Rollback failed Patchset via Flashback Database

2010-07-28T22:27:00.001-07:00

Pre-Patch

Enable Flashback

-- SQL> alter database flashback on;

Create Restore Point

-- SQL> create restore point before_patch guarantee flashback database;

Patch 1020n

Shutdown DB

-- SQL> shutdown immediate

Startup from 10204 HOME

-- SQL> startup upgrade

Run the Upgrade Scripts

-- SQL> catupgrd.sql

Rollback Patch

If you are not happy with the patch, it failed or it had some error which you can't fix it and decided to rollback, Please follow below steps.

Start the Database

-- SQL> startup force mount

Flashback Database

-- SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_PATCH;

Shutdown Database

-- SQL> shutdown immediate

Start the database from OLD 10203 HOME

-- SQL> startup mount

Open the Database

-- SQL> alter database open resetlogs;

Turn off Flashback

-- SQL> alter database flashback off;

Drop restore point

-- SQL> drop restore point BEFORE_PATCH;

That's all you are ready to go back in business with 10203.

Failed DB Patch Set - No need to Restore, Use Flashback Instead

2010-07-28T21:49:00.000-07:00

Since last couple of weeks I have been working on a project to patch more than 50+ databases from 10203 to 10204.

I never follwed the traditional approach of backing up of ORACLE_HOME and the database as Rollback Strategy. Last night I had to patch a Data warehouse database (3TB in size) and it failed. My Rollback Strategy saved me hour n hours of backup restore and I thought it will be worth sharing with you all.

Patching ORACLE_HOME from 10203 to 10204

-- Create new 10201 Home & Patch it to 101024

-- This will help you to minimize the downtime during the patch as well as if rollback is required you can use the existing home without a need of restore

Patching Database from 10203 to 10204

-- Enable a flashback on database before patching, this avoid the need have a database backup prior to patch

-- In case of failure, you dont need to go through database restore, use flashback database to rollback the patch. This will drastically redice your rollback time, you will back in business within minutes. My DW database was 3TB in size imagines the time it would have taken to restore vs it took less than 5 minutes to restore.

P.S. In my next blog, I have documented the high level steps on how to use flashback to rollback the patch.

After 10204 patch SRVCTL fails with srvctl: line 187: /bin/java: No such file or directory

2010-07-25T16:09:00.000-07:00

Last night I patched 10203 2-node RAC database to 102042. There were no issues with patching and all went well. However after patching, my srvctl command on node 2 started failing with

"srvctl: line 187: /bin/java: No such file or directory".

I checked all the environment variables and other libabry paths and all seemed OK to me. For node 1, everything was working fine and I was able to manage node to resources using the SRVCTL on node 1. It was the node 2 SRVCTL which was causing the trouble.

To further troubleshoot, I had to enable to tracing for SRVCTL and that revealed that SRVCTL file on node to had following environment variables set to NULL.

Node 1 - $ORACLE_HOME/bin/srvctl

# External Directory Variables set by the Installer
JREDIR=/app/oracle/product/10.2/db_3/jdk/jre
JLIBDIR=/app/oracle/product/10.2/db_3/jlib

Node 2 - $ORACLE_HOME/bin/srvctl

# External Directory Variables set by the Installer
JREDIR=
JLIBDIR=

I had to modify the Node 2 srvctl file similar to node 1 and the issue got resolved. Not sure of teh root cause at this point in time. However, sharing the fix so that if anyone of you come across this problem it will be a quick fix.

Calculating Network Bandwidth Required for Data Guard Log shipping

2010-02-23T20:03:00.000-08:00

Calculating Network Bandwidth Required for Data Guard Log shipping

I am currently working with a customer designing Oracle Data Guard. As part of the design process, one my objective was to calculate the additional network bandwidth required ship the logs from primary site to standby site.

As usual, I did some googling and found some intresting links and formulas. Based on all that I have written a simple SQL query as below which shows the network bandwidth required in Mbps to ship the logs. It shows total Mbps required to ship the logs for a day, max,min and avg Mbps required to ship the logs for an hour. The formula used in query is quite simple (just the conversion of an hour into seconds and bytes into bits and 30% network overhead).

SELECT DT,
SUM(RB*8/3600000000*1.3) TOTAL_Mbps_REQ_FOR_A_DAY,
MIN(RB*8/3600000000*1.3) MIN_Mbps_REQ_FOR_AN_HOUR,
MAX(RB*8/3600000000*1.3) MAX_Mbps_REQ_FOR_AN_HOUR ,
AVG(RB*8/3600000000*1.3) AVG_Mbps_REQ_FOR_AN_HOUR
FROM
(
SELECT TRUNC (COMPLETION_TIME) DT,
TO_CHAR (COMPLETION_TIME,'HH24') HH,
SUM(BLOCKS*BLOCK_SIZE) RB
FROM
V$ARCHIVED_LOG
WHERE COMPLETION_TIME > SYSDATE-5
AND DEST_ID=1
GROUP BY TRUNC(COMPLETION_TIME),
TO_CHAR (COMPLETION_TIME, 'HH24')
)
GROUP BY DT;

Hope this helps you.

ORA-16069: Archive Log standby database activation identifier mismatch

2009-07-25T01:03:00.000-07:00

ORA-16069: Archive Log standby database activation identifier mismatch

Last week, I was testing "Using a Physical Standby Database for Read/Write Testing and Reporting" documented at "http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIABDH".

I was able to activate standby database perform read-write operations, test my application, flashback the database back to standby mode and start the managed recovery once again.

However, my primary database started complaining while shipping the logs to standby.
ORA-16401: ARCHIVELOG REJECTED BY RFS

At the same time I could see the following error message in standby.
ORA-16069: Archive Log standby database activation identifier mismatch

I was running 10.2.0.2, so when I contacted oracle, they asked me apply the latest 10.2.0.4 patchset. So I will not talk about root case and I will talk on how I fixed ORA-16069 problem.

On Primary
----------
SQL> alter database create standby controlfile as 'C:\standby.ctl';

On Standby
-----------
SQL> shutdown immediate

Replace your standby controlfile with new standby controlfile created in previous controlfile.

SQL> startup mount
SQL> recover managed standby database disconnect;

The above steps will fix ORA-16069 and enable log shipping as well as GAP resolution from primary to standby.

DBUA fails with LRM-00116 & ORA-01078

2009-07-24T22:45:00.000-07:00

Recently I was upgrading the 10.2.0.2 database to 10.2.0.4 on Windows 2003 server. DBUA failed with "ORA-01078: failure in processing system parameters" error while doing the pre upgrade checks. I looked up in the C:\Oracle\product\10.2.4\cfgtoollogs\dbua\DBNAME\upgrade0\PreUpgrade.log and found the following.

LRM-00101: unknown parameter name 'e:\WEBCT64DATA\datafiledb_name'
LRM-00116: syntax error at 'E:\MYDBdb_uni' following '='
ORA-01078: failure in processing system parameters

It didn't make any sense to me, as I had no parameter with values as 'e:\MYDB\datafiledb_name' as this. However, when I closely looked at this I realised that I had parameter called db_file_name_convert which had value set to 'd:\dbname\datafile\', 'E:\MYDB\datafile\' and it was followed by the parameter db_name.

Now I knew something is wrong here which oracle DBUA doesn't like. As usual I checked up in metalink and found a note. 397802.1 "DBUA Reports ORA-01078 While Upgrading A Data Guard Database".

Yes, this note confirms that DBUA doesn't like this parameter value and as solution recommends it to set to null values. The metalink article doesn't talk about why DBUA doesn't like this and as per the note this can happen only with this data guard related parameters and hence issues associated with upgrading data giard database.

I did some further dig up on this issue and found it to be something else.

Its seems to me on windows DBUA treats any trailing "\" in pfile/spfile as an escape character and hence and trailing backspace which are part of values for any init params will be ignored and nect parameter name will be appended to the value.

For e.g.
In my case the param file looks like...
....
....
db_file_name_convert='D:\MYDB\DATAFILE\', 'E:\MYDB\DATAFILE\'
DB_NAME=MYDB

So as there is a "\" in end it escapes it and reads the file as "db_file_name_convert='D:\MYDB\DATAFILE\', 'E:\MYDB\DATAFILEDB_NAME=MYDB" which is invalid and it doesn't like and complains. So this error has nothing to do with data guard instances.

This error can be caused by any parameter in init file which has values with "/" at the end no matter you are run data guard or single instance.

For e.g.

user_dump_Dest="c:\oracle\admin\udump\"
background_dump_Dest="c:\oracle\admin\bdump\"
core_dump_Dest="c:\oracle\admin\cdump\"

It seems to be windows specific issue. So if DBUA fails with LRM-00116 & ORA-01078 on windows please check the init file for values which has a "\" at the end.

Frequently used commands in Data Guard using Broker

2009-06-18T21:24:00.000-07:00

· How to upgrade/downgrade the protection mode?
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

· How to change the init parameter setting related to data guard?
DGMGRL> EDIT DATABASE 'MYDBdr' SET PROPERTY 'StandbyFileManagement'='AUTO';

· How to change the redo shipping process or mode?
DGMGRL> EDIT DATABASE 'MYDB’ PROPERTY 'LogXptMode'='SYNC';

· How to open the standby database in read-only mode?
DGMGRL> EDIT DATABASE 'MYDBdr' SET STATE='READ-ONLY';

· How to stop the recovery process on standby database?
DGMGRL> EDIT DATABASE 'MYDBdr' SET STATE='LOG-APPLY-OFF’;

· How to put the standby database back in recovery mode?
DGMGRL> EDIT DATABASE 'MYDBdr' SET STATE='ONLINE’;

· How to stop the redo shipping on primary database?
DGMGRL> EDIT DATABASE 'MYDB' SET STATE='LOG-TRANSPORT-OFF’;

· How to start the redo shipping on primary database?
DGMGRL> EDIT DATABASE 'MYDB' SET STATE='ONLINE’;

· How to check the status of broker configuration?
DGMGRL> SHOW CONFIGURATION;

Best Practices for Change Data Capture (CDC) Asynchronous Auto Log Archive

2009-03-11T15:32:00.000-07:00

Currently I am working with a customer to implement CDC and as a part of this engagement, I was looking for resources on best practices / guidelines to consider while implementing CDC. Even though there is much on Google and Oracle documentation, I thought it will be good idea to consolidate them and publish it here.

Following are the guidelines / best practices to consider while implementing CDC in Asynchronous Auto Log Archive mode.

1. Supplemental Logging

· The asynchronous modes of Change Data Capture work best with appropriate supplemental logging on the source database. Oracle recommends that the source database supplementally log all source table columns that are part of a primary key or function to uniquely identify a row.
· Create an unconditional log group for all source table columns that are captured by any asynchronous change table. This should be done before any change tables are created on a source table. If an unconditional log group is not created for all source table columns to be captured, then when an update DML operation occurs, some unchanged user column values in change tables will be null instead of reflecting the actual source table value.
· If you intend to capture all the column values in a row whenever a column in that row is updated, you can use ALL COLUMN option instead of listing each column one-by-one in the ALTER TABLE statement for supplemental logging. However, do not use this form of the ALTER TABLE statement if all columns are not needed. Logging all columns incurs more overhead than logging selected columns.
· Use force-logging option only if you want to capture the changes out of direct load insert or inserts with nologging. Use this force logging with caution as it may introduce performance overhead on the source database.
· To minimize performance impact in case you want capture even the changes out of direct load inserts or inserts with nologging, consider moving the source tables which are part of such operation to a dedicated tablespace and turn on force-logging at tablespace level instead of database level.

2. Defining Tables and Columns

· Performance, storage & network overhead of the Change Data Capture configuration is proportional to the amount of table and columns, which are part of the configurations.
· Ensure that you include only required and relevant tables and their columns while creating change tables.
· Capture only selective and relevant control columns on the change tables.
· By default, Change Data Capture process, records both (old & new) values in change table. If old values are not required, ensure to capture only new values (parameter CAPTURE_VALUES=>’NEW’) to minimize the performance overhead.
· Oracle recommends that change tables not be created in system tablespaces. This can be accomplished if the publisher's default tablespace is not the system tablespace or if the publisher specifies a tablespace in the options_string parameter.
· Do not specify any constrains on change table as it adds performance overhead during the time of capture. Perform all your data validations in target database.

3. Change Source, Change Sets & Subscriptions

· Keep the number of change sources per source database to minimum. Ideally create one change source in your staging database for each source database. Bear in mind that a change set cannot span across the change sources.
· Keep the number of change sets within a given change source to minimum. Bear in mind that a change set is a set of change data that is guaranteed to be transactionally consistent and subscription cannot span across change sets. If your subscriber needs set of changed data, which spans across various tables in one subscription, then you should group those tables in one change set.
· Keep the number of subscription equal or more than number of subscribers. Bear in mind that given subscription cannot be shared by 2 subscribers. If you have a subscriber who is interested in change set but he is interested in few change tables on daily basis and few change tables in weekly basis, then consider creating two subscriptions for the give subscriber. If you have two subscribers interested in same set of change tables, the create two different subscriptions for a given change set instead of duplicating the source tables in two change sets.

4. Publisher & Subscriber

· It’s highly recommended to create a dedicated publisher user on staging database for publication purpose.
· Oracle recommends that when creating the publisher account on a staging database, the DBA specify a default tablespace for the publisher; the publisher should use this tablespace for any change tables he or she creates.
· Generally, one publisher is sufficient enough to manage multiple change sources/ source databases on given staging database. However, if you have centralized staging databases, which caters multiple source databases and you don’t want to share the access to change source for security reasons, then it makes more sense to have dedicated publisher for each source database.
· If you have multiple source databases and centralized staging databases and only one publisher, then make sure you use options_string while creating change tables and specify appropriate tablespace name to group the change tables from various source databases to their respective tablespaces.
· It’s highly recommended that end application don’t share the subscribers. Each application interested in changed data must have their own dedicated subscribers.
· The publisher must grant the SELECT privilege before a subscriber can subscribe to the change table. The publisher must not grant any DML access (use of INSERT, UPDATE, or DELETE statements) to the subscribers on the change tables because a subscriber might inadvertently change the data in the change table, making it inconsistent with its source. Furthermore, the publisher should avoid creating change tables in schemas to which subscribers have DML access.

5. ARCHIVE_LAG_TARGET

With the AutoLog archive option, Change sets are populated as new archived redo log files arrive on the staging database. The degree of latency depends on the frequency of redo log file switches on the source database. The AutoLog archive option has a higher degree of latency than any other option. However, ARCHIVE_LAG_TARGET parameter can be used to efficiently control this latency by forcing log switches at regular interval. A 0 value disables the time-based thread advance feature; otherwise, the value represents the number of seconds. The typical, or recommended value is 1800 (30 minutes). Extremely low values can result in frequent log switches, which could degrade performance; such values can also make the archiver process too busy to archive the continuously generated logs.

6. Archiving Strategy for Change tables

· When finished using change data, a subscriber must call the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure. This indicates to Change Data Capture and the publisher that the change data is no longer needed by this subscriber and allows auto scheduled DBMS_JOB to execute DBMS_CDC_PUBLISH.PURGE procedure to remove data that subscribers are no longer using from the change tables. This ensures that the size of the change tables does not grow without limit.
· If the subscriber fails to call PURGE_WINDOW after using the changed data, then DBMS_CDC_PUBLISH.PURGE procedure cannot delete unneeded rows from the change table. So, it’s important to ensure that subscriber runs the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure whenever finished using change data.
· In addition to DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure, it’s highly recommended that the subscriber should call the DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure to drop unneeded subscriptions.
· By default, auto scheduled DBMS_JOB executes DBMS_CDC_PUBLISH.PURGE every 24 hours. It’s recommended that the publisher who created the first change table should adjust this interval using the DBMS_JOB.CHANGE procedure based on the frequency of subscription (calls to extend_window procedure). The values for the JOB parameter for this procedure can be found by querying the USER_JOBS view for the job number that corresponds to the WHAT column containing the string 'SYS.DBMS_CDC_PUBLISH.PURGE ();'.
· It is possible that a subscriber could fail to call PURGE_WINDOW, with the result being that unneeded rows would not be deleted by the purge job. It’s highly recommended that the publisher should monitor the DBA_SUBSCRIPTIONS view at regular intervals to determine if this is happening. The publisher can use the DBMS_CDC_PUBLISH.DROP_SUBSCRIPTION procedure to drop active subscriptions where subscriber has failed to call PURGE_WINDOW.
· Publisher should consider performing purge operations at a finer granularity than the automatic purge operation performed by Change Data Capture as and when required. For e.g. after massive data load change capture, as a clean-up process etc. There are three purge operations available to the publisher:
§ – DBMS_CDC_PUBLISH.PURGE: Purges all change tables on the staging database. This is the same PURGE operation as is performed automatically by Change Data Capture.
§ – DBMS_CDC_PUBLISH.PURGE_CHANGE_SET: Purges all the change tables in a named change set.
§ – DBMS_CDC_PUBLISH.PURGE_CHANGE_TABLE: Purges a named changed table.

Recovering 3 TB standby when the archive log is deleted/corrupted before applying

2008-01-29T01:31:00.001-08:00

Yesterday, I had a challenging issue with one of my customer and its worth sharing.

They had 3 TB data warehouse system with physical standby in place. Its a critical banking reporting database and standby is mostly used in the evening to run the critical reports. They had a corruption in one of the archive log which was shipped to standby for applying. The same archive log on primary database was deleted without backup as the backups were taken on standby. Standby database was stuck for recovery requesting that particular archive log.

No other option but to take a fresh backup from primary and re-create the standby. But 3TB primary DB, 9 hours of backup time and around 14 hours of restore time. Not acceptable to customer.

Simple solution...

1. Check the SCN on standby

SQL>select current_scn from v$database;
CURRENT_SCN
-----------
47987632

2. Take incremental SCN backup on Primary

RMAN> BACKUP INCREMENTAL FROM SCN 47987632 DATABASE;

3. Recover the database

SQL>recover managed standby database cancel;
RMAN> RECOVER DATABASE NOREDO;
SQL> recover managed standby database nodelay disconnect;

Backup took 12 minutes and the size was 1.2 GB thats all. Recovery took 16 minutes.

Exploiting 11g ASM features against 10g database

2008-01-20T17:33:00.000-08:00

Exploiting 11g ASM features against 10g database

We all know that new Oracle 11g release has much more to offer. Its going to take some time for every one to adopt 11g in production. Lot of testing to be done. Management has to be convinced about its stability based on success stories of others customers. But somewhere, someone has to start.

For those who are using ASM as their storage can upgrade their ASM instance alone to 11g immediately and start exploiting 11g ASM features against their 10g database.

Following are the simple steps to follow and you are all set to go with ASM 11g features for your 1og database.

1. Install Oracle 11g RDBMS software for your new ASM 11g HOME.

2. Shutdown your 10g database & ASM instance

3. Reconfigure Oracle Cluster Synchronization Services to run from new 11g home

Set PATH and other environment variables(ORACLE_HOME, ORACLE_SID) to new ORACLE 11g executables.

Login as root and run following command.

[root@arul oracle]# localconfig reset
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized
Stale CSS daemon is running... killing it now
Cleaning up Network socket directories
Setting up Network socket directories
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
Cluster Synchronization Services is active on these nodes.
arul
Cluster Synchronization Services is active on all the nodes.
Oracle CSS service is installed and running under init(1M)
[root@arul oracle]#

4. Copy password file & S/Pfile to new 11g ORACLE_HOME/dbs folder

$ cp spfile+ASM.ora /u02/app/oracle/product/11g/db_1/dbs
$ cp orapw+ASM /u02/app/oracle/product/11g/db_1/dbs

5. Remove the obsolete initialization parameters
Parameters like background/user/core dump dest are obsolete in 11g, so remove those parameters.
Change the remote login passwordfile to exclusive.

6. Login as sysdba and start the ASM instance from 11g home and grant sysasm privilege.

$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 21 09:11:50 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area 284565504 bytes
Fixed Size 1299428 bytes
Variable Size 258100252 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted

SQL> grant sysasm to sys;
Grant succeeded.
SQL> exit

7. Start 10g RDBMS instance.
set PATH and other environment variables(ORACLE_HOME, ORACLE_SID) to ORACLE 10g RDBMS instance.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 21 09:13:46 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 71305364 bytes
Database Buffers 109051904 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>

8. All set to go.

Now you can use 11g ASM features for your 10g RDBMS instance.

ASMCMD has the few very handy new commands: cp, lsdsk, pwd...

Few examples..

ASMCMD [+data/mars] > cp spfilemars.ora +data/mars/test/spfilemars.ora
source +data/mars/spfilemars.ora
target +data/mars/test/spfilemars.ora
copying file(s)...
file, +DATA/mars/test/spfilemars.ora, copy committed.

ASMCMD [+data/mars] > cd test
ASMCMD [+data/mars/test] > ls
spfilemars.ora

$ asmcmd -v
asmcmd version 11.1.0.6.0

ASMCMD [+data/mars/test] > pwd
+data/mars/test

ASMCMD [+data/mars/test] > lsdsk
Path
/dev/raw/raw1
/dev/raw/raw2

ASMCMD [+data/mars/test] > lsct
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
mars CONNECTED 10.2.0.1.0 10.2.0.1.0 mars DATA
mars CONNECTED 10.2.0.1.0 10.2.0.1.0 mars FRA

Oracle11g Active Data Guard

2007-10-31T04:38:00.000-07:00

A physical standby database can be open for read-only access while Redo Apply is active if a license for the Oracle Active Data Guard option has been purchased. This capability is known as Real-time Query.

Let test this feature...

Oracle DATAGUARD Physical standby configuration steps remains same as 10g. So I have a physical standby database already running.

Make sure you have standby redo logs configured..

SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select count(*) from v$standby_log;
COUNT(*)
----------
3

Open the Physical standby database and start the recovery

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.

Now Lets perfrom some transaction on primary and redo will be shipped to standby and will be applied and we will be able to query them online when the apply is in progress.

SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY

SQL> select count(*) from test;
COUNT(*)
----------
68289

SQL> truncate table test;
Table truncated.

SQL> select count(*) from test;
COUNT(*)
----------
0

Now lets check on standby

SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select count(*) from test;
COUNT(*)
----------
0

ORA-01274 on Standby DB using ASM

2007-10-29T05:44:00.000-07:00

ORA-01274 is raised on standby database when Automated standby file management is disabled, so an added file on primary could not automatically be created on the standby. The control file file entry for the file is 'UNNAMEDnnnnn'.

Wehn you are using a normal file system, Use the ALTER DATABASE CREATE DATAFILE statement to create the file and set STANDBY_FILE_MANAGEMENT to AUTO and restart standby recovery.

For e.g.
ALTER DATABASE CREATE DATAFILE
'/u01/app/oracle/product/10.1.0/dbs/UNNAMED00007'
AS
'/u02/db1/oradata/test01.dbf';

But if you are using ASM along with oracle managed files then target datafile name is auto generated. We had ORA-1274 recently on standby using ASM file system and this how it was fixed.

ERROR

Media Recovery Log +DATA/genprd_js/arc00457_0631903938.002
File #6 added to control file as 'UNNAMED00006' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Errors with log +DATA/genprd_js/arc00457_0631903938.002
MRP0: Background Media Recovery terminated with error 1274
Fri Oct 26 16:36:05 2007
Errors in file d:\oracle\product\10.2.0\admin\genprd_js\bdump\genprd2_mrp0_4320.trc:
ORA-01274: cannot add datafile '+DATA/genprd_fc/datafile/deal_data_large.598.636716925' - file could not be created

FIX
SQL> alter database create datafile 6 as;
SQL> recover managed standby database disconnect;

How to backup Database & archivelog to two different area with two different format and TAG name

2007-07-30T05:38:00.000-07:00

Backup database format '/u01/db_backup/%d_%t_%s_%p' tag 'DB_daily_backup' plus archivelog format '/u02/arch_backup/%d_%U' tag 'Arch_daily_backup' delete input;

How to backup Archivelogs when stored on local disk in a RAC environment

2007-07-30T05:32:00.000-07:00

RMAN enables the autolocation feature whenever the allocated channels have different PARMS or CONNECT strings. RMAN automatically discovers which nodes of an Oracle Real Application Clusters configuration can access the files that you want to back up or restore. RMAN autolocates the following files:

·Backup pieces during backup or restore

·Archived redo logs during backup

·Datafile or control file copies during backup or restore

So run the script from any node, connect to any node, but make sure you have a channel connecting each instance. Sample…Script

run
{
ALLOCATE CHANNEL CH1 DEVICE TYPE sbt_tape connect '/@node2' PARMS "ENV=(tdpo_optfile=C:\Program Files\tivoli\tsm\AgentOBA\tdpo_LN1O.opt)";

ALLOCATE CHANNEL CH2 DEVICE TYPE sbt_tape connect '/@node1' PARMS "ENV=(tdpo_optfile=C:\Program Files\tivoli\tsm\AgentOBA\tdpo_LN1O.opt)";

Backup archivelog all;
}

Standby Database with a Time Lag

2007-07-30T05:24:00.000-07:00

By default, when the standby database is performing managed recovery, it automatically applies redo logs when they arrive from the primary database. But in some cases, you might not want the logs to be applied immediately, because you want to create a time lag between the archiving of a redo log at the primary site and the application of the log at the standby site. A time lag can protect against the transfer of corrupted or erroneous data from the primary site to the standby site.

For example, suppose you run a batch job every night on the primary database. Unfortunately, you accidentally ran the batch job twice and you did not realize the mistake until the batch job completed for the second time. Ideally, you need to roll back the database to the point in time before the batch job began. A primary database that has a standby database with a time lag could help you to recover. You could fail over the standby database with the time lag and use it as the new primary database.

To create a standby database with a time lag, use the DELAY attribute of the LOG_ARCHIVE_DEST_n initialization parameter in the primary database initialization parameter file. The following is an example of how to add a 4-hour delay to the LOG_ARCHIVE_DEST_n initialization parameter:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stdby DELAY=240';

The DELAY attribute indicates that the archived redo logs at the standby site are not available for recovery until the 4-hour time interval has expired. The time interval (expressed in minutes) starts when the archived redo logs are successfully transmitted to the standby site. The redo information is still sent to the standby database and written to the disk as normal without any delay.

Swithover Steps when stanby is running in Time lag.

Step 1 Current Primary

SQL> startup force;
SQL> alter database commit to switchover to standby with session shutdown;
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;

Step 2 Current Standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION THROUGH LAST SWITCHOVER;
SQL> alter database commit to switchover to primary with session shutdown;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open;

Step 3 Former Primary (Current Standby)

SQL> recover managed standby database disconnect;

REQUIRED_MIRROR_FREE_MB & USABLE_FILE_MB

2007-07-20T01:11:00.000-07:00

REQUIRED_MIRROR_FREE_MB & USABLE_FILE_MB

The V$ASM_DISKGROUP view contains columns that help you manage capacity:

REQUIRED_MIRROR_FREE_MB - indicates the amount of space that must be available in the disk group to restore full redundancy after the worst failure that can be tolerated by the disk group.

USABLE_FILE_MB - indicates the amount of free space, adjusted for mirroring, that is available for new files.

USABLE_FILE_MB is computed by subtracting REQUIRED_MIRROR_FREE_MB from total free space in the disk group and then adjusting for mirroring. For example, in a normal redundancy disk group, where by default mirrored files take up disk space equal to twice their size, if 4 GB of actual usable file space remains, USABLE_FILE_MB equals roughly 2 GB. You can then add up to a 2 GB file.

The following query shows capacity metrics for a normal redundancy disk group that consists of four 1 GB (1024 MB) disks, each in its own failure group:

SQL> select name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb from v$asm_diskgroup;

NAME TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---------------------------------------------------------------------------------------------------
DATA NORMAL 4096 3941 1024 1458

The REQUIRED_MIRROR_FREE_MB column shows that 1 GB of extra capacity must be available to restore full redundancy after one or more disks fail. Note that the first three numeric columns in the query results are raw numbers. That is, they do not take redundancy into account. Only the last column is adjusted for normal redundancy.

Notice that:

-- Created disk group worth 4GB so TOTAL_MB is 4096M
-- Metadata & header takes some place so you are left out with FREE_MB 3941M
-- Oracle has reserved 1024M to support disk failure so REQUIRED_MIRROR_FREE_MB is 1024M

So FREE_MB - REQUIRED_MIRROR_FREE_MB = USABLE_FILE_MB

So 3941 - 1024 = 2917M

But as we said earlier TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB are RAW figures they don't account the mirroring but USABLE_FILE_MB does. This means that out of 2917M due to normal redundancy the actual USABLE_FILE_MB turns out to be 2917/2 = 1458( what you see in output).

Now I go and create a database worth 1000M on this disk group so lets see how output will look like.

NAME TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---------------------------------------------------------------------------------------------------
DATA NORMAL 4096 2051 1024 513

So the equation is pretty clear, So you had 3941M free, you used 1000M for database so your FREE_MB (3941M-1000M ) becomes 2051( FREE_MB) now. Oracle has reserved 1024M to support disk failure so REQUIRED_MIRROR_FREE_MB is 1024M. So out of 2051(FREE_MB) - 1024 (REQUIRED_MIRROR_FREE_MB) = 1024 is the actual space left out. So you have 1024M but as you want normal redundancy for your data (additional duplicate copy of your data) the USABLE_FILE_MB becomes 1024/2 = 513M.

Now I go ahead and create a tablespace worth 756M even though USABLE_FILE_MB is 513M. It works without any errors.

SQL> create tablespace test datafile size 756m;
Tablespace created.

Now see how does the output look like.

NAME TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---------------------------------------------------------------------------------------------------
DATA NORMAL 4096 534 1024 -245

You had only 513M USABLE_FILE_MB but you wanted to create 756M of datafile. It works as you had a 1024M REQUIRED_MIRROR_FREE_MB reserved by ORACLE to protect any disk failure. But that was in case of disk failure. Now you wanted a additional disk space so it used the disk space which was reserved to protect disk failure.

Negative Values of USABLE_FILE_MB is not necessarily a critical situation, But it does mean that:

-- Depending on the value of FREE_MB, you may not be able to create new files. ( in this example files not more than 245M)

-- The next failure may result in files with reduced redundancy. ( As you have started using disk space which was reserved to protect disk failure REQUIRED_MIRROR_FREE_MB any disk failure in future will result in low redundancy for your files. It means your database will continue to run but with only one copy of extents the other mirrored copy can not be rebuilt as you have lost the disk and you don't have further spare disk or disk space in that failure group).

If USABLE_FILE_MB becomes negative, it is strongly recommended that you add more space to the disk group as soon as possible.

ARCHIVELOG HOUSE-KEEPING

2007-07-19T01:02:00.001-07:00

You should backup archivelogs and delete them regularly to prevent archive destination getting 100% full.

Many people use “backup archivelog all delete input”. This command will backup all archives and delete them from disk immediately. It has two major disadvantageous.

• Delay in point in time recovery
If you need to do a point in time recovery you need to restore archive logs from the backup and to avoid this restore it’s highly recommended to keep archivelog on disk for a day or so.
• When running in standby
If you are a running a standby database, you would want to keep archivelogs on primary database for some time to get it shipped across the standby. (Even though its real time most of the time, any network failure or maintenance on standby server can delay this shipping) And on standby you would always want delete the archives only when they are backed up and leave them on the disk for some time to get it applied as well.

So these are (and many more) scenarios when you want to delete the archivelogs from disk which are old and at the same time you want to ensure they are already backed up as well.

Simple way to achieve the same is running two separate commands/scripts regularly. One to backup regularly and other to delete the files which are old enough.

For e.g.

Backup archivelog all not backed up 1 times to sbt;

Delete archivelog all completed before ‘sysdate-1’;

But it very much possible that your backup command/script has been failing (for some reason) and your delete command/script was running without any issues. So you end up deleting files without backup.

So here we go…

RMAN syntax to delete archivelogs which are a day old and they are the one which are already backed up as well.

RMAN> delete archivelog all backed up 1 times to device type sbt completed before 'sysdate-1';

REBUILD OR MOVE ENTIRE SCHEMA WITH MINIMAL DOWNTIME (PRE 10G)

2007-07-11T05:42:00.000-07:00

There are scenarios where in you want rebuild the entire schema OR move it to different tablespace online.

Following script helps to do the same with minimal downtime.

NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.

CREATE OR REPLACE PROCEDURE schema_rebuild authid current_user IS
CURSOR obj_list IS
SELECT decode( segment_type, 'TABLE', segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) m_or_r,segment_type,segment_name
FROM user_segments, (SELECT table_name, index_name FROM user_indexes )
WHERE segment_type IN ( 'TABLE', 'INDEX' )
AND segment_name = index_name (+) ORDER BY 1, 2 ;

reb_str VARCHAR2(200) ;
username varchar2(20);

BEGIN
username:=user;
FOR i IN OBJ_LIST LOOP
IF i.segment_type = 'INDEX' THEN
reb_str := 'alter ' || i.segment_type || ' ' || i.segment_name ||' '|| i.m_or_r ||' online';
ELSE
reb_str := 'alter ' || i.segment_type || ' ' || i.segment_name ||' '|| i.m_or_r;
END IF;
EXECUTE IMMEDIATE reb_str;
reb_str := '';
END LOOP ;
dbms_stats.gather_schema_stats (ownname=>username, method_opt=>'FOR ALL INDEXED COLUMNS', cascade=>TRUE);
END ;
/

Reading & Interpreting Statspack

2007-07-03T22:37:00.000-07:00

In Oracle, Performance Tuning is based on the following formula:

Response Time = Service Time + Wait Time
Where
• Service Time is time spent on the CPU
• Wait Time is the sum of time spent on Wait Events i.e. non-idle time spent waiting for an event to complete or for a resource to become available.

Service Time is comprised of time spent on the CPU for Parsing, Recursive CPU usage (for PLSQL and recursive SQL) and CPU used for execution of SQL statements (CPU Other).

Service Time = CPU Parse + CPU Recursive + CPU Other

The above components of Service Time can be found from the following statistics:
• Service Time from CPU used by this session
• CPU Parse from parse time cpu
• CPU Recursive from recursive cpu usage

From these, CPU Other can be calculated as follows:

CPU other = CPU used by this session - parse time CPU - recursive CPU usage

Many performance-tuning tools (including Statspack) produce a list of the top wait events. For example, Statspack’s report contains the "Top 5 Wait Events" section.(Pre-Oracle9i Release 2).

It is a common mistake to start dealing with Wait Events first and not taking in consideration the corresponding response time. So always compare the time consumed by the top wait events to the 'CPU used by this session' and identify the biggest consumers.

Here is an example where CPU Other was found to be a significant component of total Response Time even though the report shows “direct path read” as top wait event:

From these figures we can obtain:
• Wait Time = 10,827 x 100% / 52,01% = 20,817 cs
• Service Time = 358,806 cs
• Response Time = 358,806 + 20,817 = 379,623 cs
• CPU Other = 358,806 - 38 - 186,636 = 172,132 cs

If we now calculate percentages for the top Response Time components:
• CPU Other = 45.34%
• CPU Recursive = 49.16%
• direct path read = 2.85%
• etc. etc.

So we can see the I/O-related Wait Events actually are not a significant component of the overall Response Time. For us it makes sense concentrate our tuning effort on the service time component.

CPU Other is a significant component of Response Time, so a possible next step is to look at the CPU intensive SQL and not at direct path read wait event.
Starting with Oracle9i Release 2, Statspack presents Service Time (obtained from the statistic CPU used by this session) together with the top Wait Events in a section called Top 5 Timed Events, which replaces the section Top 5 Wait Events of previous releases.
Here is an example:

These figures give us directly the percentages of the Wait Events against the total Response Time so no further calculations are necessary to assess the impact of Wait Events. Service Time is presented as CPU time in this section and corresponds to the total CPU utilisation. We can drill down to the various components of Service Time as follows:

• CPU Other = 3,211 - 59 - 232 = 2,920 cs
• CPU Other = 2,920 / 3,211 x 5.79% = 5.26%
• CPU Parse = 59 / 3,211 x 5.79% = 0.11%
• CPU Recursive = 232 / 3,211 x 5.79% = 0.42%

In this example, the main performance problem was an issue related to the Library Cache.
The second most important time consumer was waiting for physical I/O due to multiblock reads (db file scattered read).

Identifying problematic SQL’s from Statspack

From the above calculations you will get the significant components which caused the performance problem. Based on this components lets decide on the various Statspack section to identify the problematic SQL’s.

• Other CPU

If this shows CPU other as being significant the next step will be to look at the SQL performing most block accesses in the SQL by Gets section of the Statspack report. A better execution plan for this statement resulting in fewer Gets/Exec will reduce its CPU consumption.

• CPU Parse

If CPU Parse time is a significant component of Response Time, it can be because cursors are repeatedly opened and closed every time they are executed instead of being opened once, kept open for multiple executions and only closed when they are no longer required. The SQL ordered by Parse Calls can help find such cursors.

• Disk I/O related waits.

Identifying SQL statements responsible for most physical reads from the Statspack section SQL ordered by Reads has similar concepts as for SQL ordered by Gets.
% Total can be used to evaluate the impact of each statement. Reads per Exec together with Executions can be used as a hint of whether the statement has a suboptimal execution plan causing many physical reads or if it is there simply because it is executed often. Possible reasons for high Reads per Exec are use of unselective indexes require large numbers of blocks to be fetched where such blocks are not cached well in the buffer cache, index fragmentation, large Clustering Factor in index etc.

• Latch related waits.

Statspack has 2 sections to help find such unsharable statements, SQL ordered by Sharable Memory and SQL ordered by Version Count. This can help with Shared Pool and Library Cache/Shared Pool latch tuning. Statements with many versions (multiple child cursors with the same parent cursor i.e. identical SQL text but different properties such as owning schema of objects, optimizer session settings, types & lengths of bind variables etc.) are unsharable. This means they can consume excessive memory resources in the Shared Pool and cause performance problems related to parsing e.g. Library Cache and Shared Pool latch contention or lookup time e.g. Library Cache latch contention.

Testing New Application Release on Production database

2007-06-30T21:57:00.000-07:00

Recently we had a new application release from Vendor for one our critical (24X7) database. It was very important to test the application against the production equivalent database and the only option was to clone the real time production database and test it against the clone database. This indirectly means new hardware, storage(2TB in our case) and lot more things.

Being a critical database we had physical standby database in place, we decided to failover and open the standby database for testing upgrade of application. But in this, after application upgrade testing we need to restore a standby back from backup and bring it in sync with primary. 2TB database restore will take 12 hours and we can not afford to run a critical primary database without standby in place for such a long time. We decided to use 10g flashback database through resetlog feature to come over this problem. In 10gR2 You can flashback your database back in time through resetlogs as well which will avoid the restore of standby database and we can bring the standby database in SYNC with primary ASAP.

Lets see the generic steps involved and then walk through the actual syntax and implementing it.

1. Enable Flashback on Standby
2. Perform some transaction and verify Primary Standby
3. Stop the LISTENER for FAL SERVER/CLIENT for fake recovery
4. Perform FAKE failover on Standby
5. Use Primary & Standby as individual Standalone databases
6. Perform FLASHBACK on Standby to go back in time (STANDBY_ROLE)
7. Start listener on Primary and Standby for Log shipping
8. Verify Standby

Flashback database through failover/reset logs

1. Enable Flashback on Standby

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2G;
System altered.

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880;
System altered.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/ULTRADB01/ORACLE/ASMDB/';
System altered.

SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 759169024 bytes
Fixed Size 1263112 bytes
Variable Size 197134840 bytes
Database Buffers 553648128 bytes
Redo Buffers 7122944 bytes
Database mounted.

SQL> ALTER DATABASE FLASHBACK ON;
Database altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 759169024 bytes
Fixed Size 1263112 bytes
Variable Size 197134840 bytes
Database Buffers 553648128 bytes
Redo Buffers 7122944 bytes
Database mounted.

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Media recovery complete.
SQL>

2. Perform some transaction and verify Primary Standby

PRIMARY

SQL> CREATE TABLE TESTING AS SELECT * FROM DBA_OBJECTS;
Table created.

SQL> SELECT COUNT (*) FROM TESTING;
COUNT (*)
----------
46615

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

STANDBY
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
Media recovery complete.

SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.

SQL> SELECT COUNT (*) FROM TESTING;
COUNT (*)
----------
46615

3. Stop the LISTENER for FAL SERVER/CLIENT for fake recovery
PRIMARY

lsnrctl stop LOGSHIP
LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 07-SEP-2006 09:31:42
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hqlinux26.hq.emirates.com)(PORT=1640)))
The command completed successfully

STANDBY

lsnrctl stop LOGSHIP
LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 07-SEP-2006 09:47:31
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=itlinux16.hq.emirates.com)(PORT=1640)))
The command completed successfully

4. Perform FAKE failover on Standby
STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 759169024 bytes
Fixed Size 1263112 bytes
Variable Size 197134840 bytes
Database Buffers 553648128 bytes
Redo Buffers 7122944 bytes
Database mounted.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.

SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.
Total System Global Area 759169024 bytes
Fixed Size 1263112 bytes
Variable Size 197134840 bytes
Database Buffers 553648128 bytes
Redo Buffers 7122944 bytes
Database mounted.
Database opened.
SQL>

5.Use Primary & Standby as individual Standalone databases
STANDBY (Say your application upgrade is running DMLS against the database)

SQL> SELECT COUNT (*) FROM TESTING;
COUNT (*)
----------
46615

SQL> DELETE FROM TESTING;
46615 rows deleted.

SQL> COMMIT;
Commit complete.

SQL> SELECT COUNT (*) FROM TESTING;
COUNT (*)
----------
0

PRIMARY (Parallely your production database is up & running for business)

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PRIMARY

SQL> SELECT COUNT (*) FROM TESTING;
COUNT (*)
----------
46615

SQL> INSERT INTO TESTING SELECT * FROM TESTING;
46615 rows created.

SQL> INSERT INTO TESTING SELECT * FROM TESTING;
93230 rows created.

SQL> COMMIT;
Commit complete.

SQL> SELECT COUNT (*) FROM TESTING;
COUNT (*)
----------
186460

SQL> COMMIT;
Commit complete.

6. Perform FLASHBACK on Standby to go back in time (STANDBY_ROLE)
Your application upgrade is tested and you want bring your standby in Sync with primary

STANDBY

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 759169024 bytes
Fixed Size 1263112 bytes
Variable Size 197134840 bytes
Database Buffers 553648128 bytes
Redo Buffers 7122944 bytes
Database mounted.

SQL> FLASHBACK DATABASE TO TIMESTAMP TO_DATE ('09/07/06 07:32:09','MM/DD/YY HH:MI:SS');
Flashback complete.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

PRIMARY

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/TMP/STANDBY01.CTL';
Database altered.

Copy the same to Standby server and replace with existing controlfile.

STANDBY

SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 759169024 bytes
Fixed Size 1263112 bytes
Variable Size 197134840 bytes
Database Buffers 553648128 bytes
Redo Buffers 7122944 bytes
Database mounted.

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Media recovery complete.

7.Start listener on Primary and Standby for Log shipping
PRIMARY
lsnrctl start logship

STANDBY
lsnrctl start logship

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
Media recovery complete.

SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.

8. Verify Standby

SQL> SELECT COUNT (*) FROM TESTING;
COUNT (*)
----------
186460

EVEN THOUGH WE HAVE DELETED RECORDS FROM STANDBY AFTER FAKE FAILOVER, NOW WE ARE IN SYNC WITH PRIMARY AFTER FLASHBACK OF DATABASE. YOU HAD ZERO DOWNTIME AGAINST YOUR PRIMARY DATABASE; YOU TESTED YOUR APPLICATION CODE UPGRADE AGAINST THE REALTIME PRODUCTION DATABASE; YOU DIDN'T HAD TO CLONE THE DATABASE; YOU DIDN'T HAD TO RESTORE STANDBY DATABASE;

ALL YOU DID IS FLASHBACK THE STANDBY DATABASE.
.

Fixing the Block Corruption Online

2007-06-19T18:58:00.000-07:00

From 9i onwards you can use RMAN to recover only corrupted blocks while your database is up and running.

This could possibly save hours and hours of recovery time as a full database restore is not necessary.

Let's begin with the example...

1. Error reported by user pointing to block corruption.

An error encountered in Populate MACS DATA process.
The Error - Source : POPULATE_MACSDATA - ORA-01578: ORACLE data block corrupted (file # 48, block # 142713)
ORA-01110: data file 48: '/hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf'
ORA-02063: preceding 2 lines from MODSL_MACSL_LINK
File name : /hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf

2. Check if the there is only one( or few) blocks corrupted or most of the blocks are corrupted.

macsl:/opt/oracle/admin/macsl/bdump>

Issue command below at UNIX prompt.

dbv file=/hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf BLOCKSIZE=8192 LOGFILE=test.log

DBVERIFY: Release 10.2.0.1.0 - Production on Wed Oct 4 14:28:37 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBV-00200: Block, dba 201469305, already marked corrupted

vi test.log
DBVERIFY: Release 10.2.0.1.0 - Production on Wed Oct 4 14:28:37 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf
DBVERIFY - Verification complete
Total Pages Examined : 262144
Total Pages Processed (Data) : 218615
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 22422
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 21107
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 2550111754 (1040.2550111754)

See the value in 3rd last column which points to number of corruped blocks. (Only one block corrupted).

You can get the list of corrupted blocks from v$database_block_corruption as well.

Select * from v$database_block_corruption;

You will get block number corrupt.
Ex: block 142713.

As we know that we have only one corrupted block we can very much use RMAN to recover the block online.

3. LOGIN TO RMAN.

rman target / catalog rman10/rman10@rman10p
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 4 14:33:26 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MACSL (DBID=1125502194)
connected to recovery catalog database

RMAN> blockrecover datafile 48 block 142713;

Starting blockrecover at 04-OCT-06
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=119 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 devtype=DISK

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00048
channel ORA_SBT_TAPE_1: reading from backup piece MACSL.20061004.7379.1.1.602899473
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
piece handle=MACSL.20061004.7379.1.1.602899473 tag=TAG20061003T222108
channel ORA_SBT_TAPE_1: reading from backup piece MACSL.20061004.7379.2.1.602899473
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 2
piece handle=MACSL.20061004.7379.2.1.602899473 tag=TAG20061003T222108
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:04:01

starting media recovery
media recovery complete, elapsed time: 00:00:46

Finished blockrecover at 04-OCT-06

Block corruption has been fixed online while the database was up & running within minutes.

NOTE:
-- V$database_block_corruption is the view to check the list of corrupted blocks.
-- If you have multiple block list as corrupt, You can use single command to recover them.

RMAN> BLOCKRECOVER corruption list;

Implement & Explore Automatic Storage Management on your Laptop/Desktop

2007-06-19T17:58:00.000-07:00

ASM can be implemented without having expensive additional disks/logical volumes/partition for testing purpose on your laptop/desktop.

All you need is a free space on your hard disk to store your database.

It's much intresting to do in practice than to describe in theory, so let's begin!

1) Creating a dummy disks

In 10gR2 installation, Oracle ships an executable called asmtool, and it can be used to create 'solid' files -solid in the sense that they are filled with zeros, as opposed to being full of empty space. These are the sorts of files ASM demands if it is to treat them as hard disks. The utility is found in the ORACLE_HOME\bin.

C:\>mkdir asmdisks

C:\>cd asmdisks

C:\asmdisks>asmtool -create c:\asmdisks\disk1 1024

C:\asmdisks>asmtool -create c:\asmdisks\disk2 1024

C:\asmdisks>asmtool -create c:\asmdisks\disk3 1024

Now you have 3 disks(dummy) of 1 GB each which can be used to create a ASM disk group.

2) Create ASM instance

a) Configure Cluster Synchronization Servie

Before you can create an ASM instance on 10g Release 2, though, you must first run a script which instantiates the Oracle Cluster Registry and its associated processes & services. That's done by issuing the command:

C:\>c:\oracle\product\10.2.0\db_1\BIN\localconfig reset

Step 1: stopping local CSS stack
Step 2: deleting OCR repository
Step 3: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'ap\arogyaa', privgrp ''..
Operation successful.
Step 4: creating new CSS service
successfully created local CSS service
successfully reset location of CSS setup

b) Create Init pfile

Open notepad edit the following parameters and save file as "C:\oracle\product\10.2.0\db_1\database\init+ASM.ora"

INSTANCE_TYPE=ASM
DB_UNIQUE_NAME=+ASM
LARGE_POOL_SIZE=8M
ASM_DISKSTRING='c:\asmdisks\*'
_ASM_ALLOW_ONLY_RAW_DISKS=FALSE

c) Create service and password file

oradim will create an ASM instance and start it automatically.

c:\> orapwd file=C:\oracle\product\10.2.0\db_1\database\PWD+ASM.ora password=asm
c:\> oradim -NEW -ASMSID +ASM -STARTMODE auto

3) Create ASM disk group

a) Create asm disk group
SQL> select path, mount_status from v$asm_disk;

PATH MOUNT_S
--------------------------------
C:\ASMDISKS\DISK1 CLOSED

C:\ASMDISKS\DISK3 CLOSED

C:\ASMDISKS\DISK2 CLOSED

SQL> create diskgroup data external redundancy disk
2 'C:\ASMDISKS\DISK1',
3 'C:\ASMDISKS\DISK2',
4* 'C:\ASMDISKS\DISK3';

Diskgroup created.

b) Change PFILE to SPFILE, Add ASM Diskgroup parameter and your all set to go and use ASM.

SQL> create spfile from pfile;
SQL> startup force;
SQL> alter system set asm_diskgroups=data scope=spfile;
SQL> startup force;

SQL> startup force
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1247420 bytes
Variable Size 57472836 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>

Now you can go ahead and use your DBCA and create a database and on step 6 of 13, you can use Automatic Storage management as your Filesystem.

Moving 1.2TB database from 32bit to 64bit HOST in 12min.

2007-06-06T18:59:00.000-07:00

Recently we moved our production 9206 database (1.2TB) from 32bit to 64bit Host.

Moving 1.2TB of database using backup / restore mechanism was not acceptable due to the downtime required.

We followed a dataguard as an interim solution to migrate to 64bit and managed to do it successfully within 12minutes of outage.

Following were the series of steps followed:

1. Perform a production database backup. (32bit current/old Server).

2. Create a physical standby database on a new 64bit server. (Refer Metalink Note: 118409.1 Creating a 9i Data Guard Database with RMAN)

3. Bring standby database in Sync with primary and perform a failover.

4. Covert the Word Size (32bit to 64bit) of the new database.

• Shutdown immediate

• Startup migrate

• @?/rdbms/admin/utlirp

NOTE: This script first Invalidates & then recompiles PL/SQL modules viz., procedures, functions, packages, types, triggers, views in a database

• Shutdown immediate

• Startup

Database outage starts at step 3, and we finished step 3 and 4 within 12 minutes and database was released to users.

NOTE: YES, Dataguard works across 32bit & 64bit. (Refer Metalink Note: 413484.1 Data Guard Support for Heterogeneous Primary and Standby Systems in Same Data Guard Configuration)

ASM and RAW Devices on RHEL 4

2007-02-15T07:19:00.000-08:00

Recently I implemented ASM with RAW devices on RHEL 4. Things were running fine until
yesterday when the server was rebooted. ASM instance failed to start-up with following error.

SQL> startup
ASM instance started

Total System Global Area 130023424 bytes
Fixed Size 2027184 bytes
Variable Size 102830416 bytes
ASM Cache 25165824 bytes
ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"DGRP1"

Alert log had following:

ERROR: no PST quorum in group 1: required 2, found 0
NOTE: cache dismounting group 1/0x94F1292B (DGRP1)
NOTE: dbwr not being msg'd to dismount
ERROR: diskgroup DGRP1 was not mounted

While diagnosis I found that raw devices were owned by root where as they must be owned by Oracle.

ls -l /dev/raw/raw1
crw-rw---- 1 root disk 162, 1 Feb 14 19:55 /dev/raw/raw1

I fixed the issue by changing permissions.

# chown oracle:dba /dev/raw/raw1
# chmod 660 /dev/raw/raw1

But, It seems in RH4 raw devices are remapped as root on start-up. Every time I reboot the server, raw device's permissions were reset to root as "/etc/udev/permissions.d/50-udev.permissions" has a following entry:

# raw devices
raw/*:root:disk:0660

To make this permissions persistent across the reboot, you can do followings:

1. Add following in "/etc/rc.local"

chown oracle:dba /dev/raw/raw1
chmod 660 /dev/raw/raw1

OR

2. Add following in "/etc/udev/permissions.d/50-udev.permissions"

/raw/raw1:oracle:dba:0660

Note: Make sure you add the above line above "raw/*:root:disk:0660". It doesn't work if you add below this one.