Wednesday, November 3, 2010

How to write DBID to alert long on a regular basis? --Moid

Every Oracle database has an internal unique DBID that can be queried from V$DATABASE
as follows:

SQL> select dbid from v$database;DBID------------------------------1794272723

RMAN uses the DBID to uniquely identify databases. The DBID helps RMAN identify the
correct RMAN backup piece from which to restore the control file. If you don’t use a flash
recovery area or a recovery catalog, then you should record the DBID in a safe location and
have it available in the event you need to restore your control file.

Writing the DBID to the Alert.log File

Another way of recording the DBID is to make sure that it is written to the alert.log file on a
regular basis using the DBMS_SYSTEM package. For example, you could have this SQL code execute as part of your backup job

COL dbid NEW_VALUE hold_dbidSELECT dbid FROM v$database;exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));

After running the previous code, you should see a text message in your target database
alert.log file that looks like this:

==> alert_PrimeDG.log <==Wed Nov  3 03:06:47 2010DBID: 1794272723
You can easily put this in a cron job to write on a daily or weekly basis.Extracting of DBID from Redo Log fileAnother way of getting DBID is possible by getting the dump of the any availabe datafile, redolog or archvied log. I chosed to take a dumpfile of the online redolog and following are the steps I have taken.
SQL> ALTER SESSION SET sql_trace = true;Session altered.SQL> ALTER SESSION SET tracefile_identifier=Moid_Logfile_dump;Session altered.SQL> alter system dump logfile '/u15/oradata/Prime/redo01a.rdo';System altered.
At this point, I changed my directory to the user dump destination and dumpfile is available to be skinned.
Linux-223:(PrimeDG)$ pwd/u01/app/oracle/admin/PrimeDG/udumpLinux-223:(PrimeDG)$ ls -ltrhtotal 203M-rw-r-----  1 oracle oinstall  948 Nov  3 03:39 primedg_rfs_8676.trc-rw-r-----  1 oracle oinstall  947 Nov  3 03:44 primedg_rfs_8809.trc-rw-r-----  1 oracle oinstall 135M Nov  3 03:49 primedg_ora_8712.trc-rw-r-----  1 oracle oinstall  947 Nov  3 03:49 primedg_rfs_8979.trc-rw-r-----  1 oracle oinstall  68M Nov  3 03:49 primedg_ora_8712_MOID_LOGFILE_DUMP.trc
A simple cat of the file, provided me what I was looking for, DBID of my database.
Linux-223:(PrimeDG)$ cat primedg_ora_8712_MOID_LOGFILE_DUMP.trc |grep "Db ID"        Db ID=1794272723=0x6af26dd3, Db Name='PRIME'
The above instructions can also be found here. 

No comments:

Post a Comment