Syed DBA: June 2012

Sunday, June 17, 2012

ORA-16047: DGID mismatch between destination setting and target database

ORA-16047: DGID mismatch between destination setting and target database

Primary database omrdb
Dataguard omrdg

1- Check db_name and db_unique_name parameter on Primary and standby database
On Primary

SQL> show parameter db_name

NAME TYPE VALUE
----------------------- ----------- ------------------------------
db_name string omrdb

SQL> show parameter db_unique
NAME TYPE VALUE
----------------------- ----------- ------------------------------
db_unique_name string omrdb

On Dataguard
SQL> show parameter db_name
NAME TYPE VALUE
--------------------- ----------- ------------------------------
db_name string OMRDB

SQL> show parameter db_unique
NAME TYPE VALUE
--------------------- ----------- ------------------------------
db_unique_name string omrdg

2- Check log_archive_config parameter
SQL>show parameter log_archive_config

NAME TYPE VALUE
------------------------- ----------- ------------------------------
log_archive_config string dg_config=(omrdb,omrdg)

3- Check log_archive_dest_2 parameter on primary database
SQL> show parameter log_archive_dest_2

NAME TYPE VALUE
------------------------ ----------- ------------------------------
log_archive_dest_2 string service=omrdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=omrdb

4- Set the db_unique_name value correct to omrdg dataguard database
SQl>alter system set log_archive_dest_2='service=omrdg lgwr async valid_for=
(online_logfiles,primary_role) db_unique_name=omrdg' scope=spfile

5- Bounce the primary database and check alert log of standby database, it should show recovery like:

Sun Jun 17 07:47:54 2012
Media Recovery Log /u01/app/oracle/flash_recovery_area/OMRDG/archivelog/2012_06_17/o1_mf_1_67_7xtrccnj_.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/OMRDG/archivelog/2012_06_17/o1_mf_1_68_7xtrc0t8_.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/OMRDG/archivelog/2012_06_17/o1_mf_1_69_7xtrc0cr_.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/OMRDG/archivelog/2012_06_17/o1_mf_1_70_7xtrc0hc_.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/OMRDG/archivelog/2012_06_17/o1_mf_1_71_7xtrc0yn_.arc

6- Check the v$archived_log view on dataguard
1* select sequence#,applied from v$archived_log order by sequence#
SQL> /

SEQUENCE# APPLIED
---------- ---------
67 YES
68 YES
69 YES
70 YES
71 YES
72 YES
73 YES
74 YES
75 YES
76 YES
77 YES
78 YES
79 YES
80 YES

14 rows selected.

Friday, June 15, 2012

PL/SQL package RMAN.DBMS_RCVCAT version 10.02.00.00 in RCVCAT database is not current

[oracle@omr ~]$ rman catalog rman/rman@omrdb

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jun 15 07:44:22 2012

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

connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 10.02.00.00 in RCVCAT database is not current
PL/SQL package RMAN.DBMS_RCVMAN version 10.02.00.00 in RCVCAT database is not current

RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 10.02.00.04
DBMS_RCVMAN package upgraded to version 10.02.00.00
DBMS_RCVCAT package upgraded to version 10.02.00.00

RMAN>

[oracle@omr ~]$ rman catalog rman/rman@omrdb

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jun 15 07:48:02 2012

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

connected to recovery catalog database

RMAN>

Wednesday, June 6, 2012

How to recreate Oracle Inventory

How to re-create Oracle Inventory

Step 1. [oracle@omr oraInventory]$ export PATH=$PATH:$ORACLE_HOME/OPatch
[oracle@omr oraInventory]$ opatch lsinv
Invoking OPatch 11.1.0.6.6
Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.2.0.1.0
OUI location : /u01/app/oracle/product/11.2.0/db_1/oui
Log file location :
/u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2012-06-06_19-08-51PM.log

Patch history file:
/u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location :
/u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2012-06-06_19-08-51PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g
11.2.0.1.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.

Step 2. Move OraInventory direcotry to oraInventory.old to introduce Oracle Inventory corruption:
[oracle@omr oraInventory]$ mv oraInventory oraInventory.old

Step 3. Try opatch lsinventory again

[oracle@omr oraInventory]$ opatch lsinv
Invoking OPatch 11.1.0.6.6
Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.2.0.1.0
OUI location : /u01/app/oracle/product/11.2.0/db_1/oui
Log file location :
/u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2012-06-06_19-09-54PM.log
Patch history file:
/u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt
OPatch failed to locate Central Inventory.
Possible causes are:
The Central Inventory is corrupted
The oraInst.loc file specified is not valid.
LsInventorySession failed: OPatch failed to locate Central Inventory.
Possible causes are:
The Central Inventory is corrupted
The oraInst.loc file specified is not valid.

OPatch failed with error code 73

Step 3. Check the Oracle Home Name from old Oracle Inventory Location.

[oracle@omr app]$ cat ./oraInventory.old/ContentsXML/inventory.xml
<?xml version="1.0" standalone="yes" ?>


<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.1.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/db_1"
TYPE="O" IDX="1"/>
</HOME_LIST>
</INVENTORY>

Step 4. Re-create the Oracle Inventory.
[oracle@omr oui]$ ./runInstaller -silent -attachHome ORACLE_HOME="/u01/app/oracle/product/11.2.0/db_1" ORACLE_HOME_NAME="OraDb11g_home1"

Step 5. Verify Oracle Inventory

[oracle@omr bin]$ opatch lsinventory

Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6

Oracle Home : /u01/app/oracle/product/11.2.0/db_1

Central Inventory : /u01/app/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.1.0.6.6

OUI version : 11.2.0.1.0

OUI location : /u01/app/oracle/product/11.2.0/db_1/oui

Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2012-06-06_19-15-54PM.log

Patch history file: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2012-06-06_19-15-54PM.txt

--------------------------------------------------------------------------------

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.1.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------

OPatch succeeded.

Tuesday, June 5, 2012

Timestamp column as part of composite Primary key

The precision of systimestamp is platform dependent.

On most Unix platforms it's microseconds (10-6), while on Windows this is Milliseconds (10-3).

For example:

On Linux Environment:

SQL> create table test(c1 timestamp(6));

Table created.

SQL> begin

2 for i in 1..10 loop

3 insert into test values(systimestamp);

4 end loop;

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from test;

---------------------------------------------------------------------------

04-JUN-12 07.04.42.198435 PM

04-JUN-12 07.04.42.276114 PM

04-JUN-12 07.04.42.276165 PM

04-JUN-12 07.04.42.276193 PM

04-JUN-12 07.04.42.276221 PM

04-JUN-12 07.04.42.276248 PM

04-JUN-12 07.04.42.276274 PM

04-JUN-12 07.04.42.276302 PM

04-JUN-12 07.04.42.276329 PM

04-JUN-12 07.04.42.276355 PM

10 rows selected.

On Windows Environment:

The milliseconds value in timestamp is same in all the rows:

SQL> select * from test;

----------------------------

04-JUN-12 07.02.38.981000 PM

04-JUN-12 07.02.38.982000 PM

10 rows selected.

Due the same value in timestamp column, only the first row will be inserted in the table, and the rest would be non-unique and gets rejected. The problem is due to Windows limitations.

The workaround could be to add one more column to the composite primary key, and link the new column with a sequence to make it unique.