Changing DBID and DB name  

Posted by Mawahid

1.       First take backup of full database.

2.       Then login as SYSDBA to check out the DB ID and DB Name

select dbid, name from v$database;

3.       Make sure the following things should addressed.

a)      Open_cursors=500 (increase it to some value, later revert back to original)
b)      Crosscheck all the db files are present at OS level i.e, datafiles,tempfiles etc. Make sure none of the tablespace is offline.

4.       Shutdown the database and startup in mount state.

SQL> shutdown immediate
SQL> startup mount

5.       Use the DBNEWID command to change the DB ID and db name, you need to login as user with privilege SYSDBA

$ nid target=username/password@servicename dbname=newdbname


which changes the DBID to a new DBID (You can't set DBID though as your wish). In this case the follow operations are performed is below.
a)      The DBNEWID utility performs validations in the headers of the datafiles and control files before attempting I/O to the files.
b)      If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt)
c)       Then changes the DBID and the DBNAME for each datafile, including offline normal and read-only datafiles
d)      Shuts down the database, and then exits.
                If you want to change only dbid then
$ nid target=username/password@servicename
                If you want only db name to rename then
                                $ nid target=username/password@servicename dbname=newdbname SETNAME=y
If NID fails, with some errors and for revert,

$ nid target=username/password@servicename REVERT=Y

6.       Shutdown database again.

SQL> shutdown immediate

7.       Change db_name initialization parameter in the initialization file.

8.       Create a new password file using ORAPWD utility with the new SID.

$ orapwd file=orapwSID password=password entries=

9.       Start database in mount state

SQL> startup mount;

10.   Open database with RESETLOGS option

SQL> alter database open resetlogs;

11.   Verify the database id and name using following statement

select dbid, name from v$database;

12.   Don’t forget to change ORACLE_SID parameter in .bash_profile and database name in /etc/oratab
Note:
All the previous backup, archivelogs become unusable.

Restore and Recover database in Noarchivelog Mode  

Posted by Mawahid in


Before proceed we need to know while restoring data in noarchivelog mode that,

1)Only cold backups (that is, backups created when the database was shut down normally) can be used in restoring a database in NOARCHIVELOG mode.

2)Media recovery is not possible, because there are no archived logs.

3)If we exclude any tablespace except read-only or offline from taking database backup then those tablespaces will be lost.

In this scenario I have lost all the data files, control files, redo log file and spfile. I have also forgot DBID of the database. The procedure of restore and recovery of database in noarchivelog mode in as below.


A) Find the DBID of the Database from Backup Piece.

bash-3.00$ strings /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp |grep MAXVALUE,

3386862614, MAXVALUE,

We got DBID here 3386862614.


B) Connect to target database with RMAN

bash-3.00$ rman target /


C) Set the DBID.

RMAN> set dbid 3386862614


D) Start the database in nomount stage with dummy Parameter.

RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'


starting Oracle instance without parameter file for retrival of spfile


Oracle instance started

E) Restore spfile

RMAN> restore spfile from '/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_653998009_420shstm_.bkp';


F) Mount the Database.

RMAN> startup force nomount;


G) Restore controlfile.
 
RMAN> restore controlfile from autobackup;


RMAN> alter database mount;

I) Restore Database. 

J) Recover Database: 

RMAN> recover database; 

RMAN> alter database open resetlogs;
 
database opened.

What happens during oracle database hot backup  

Posted by Mawahid in

According to oracle documentation you already heard/aware that during an Oracle tablespace hot backup, a script or program or command puts a tablespace into backup mode, then copies the datafiles to disk or tape, then takes the tablespace out of backup mode. We can check the backup mode from V$BACKUP view. In case of user managed backup, backup process starts after issuing ALTER TABLESPACE tbs_name BEGIN BACKUP; or ALTER DATABASE BEGIN BACKUP; command and backup process ends by ALTER TABLESPACE tbs_name END BACKUP; or ALTER DATABASE END BACKUP; command.

Although the process is very clear and well understood but there are many misconception around hot backup. The misconception start what is actually done during hot backup, is data file opens writeable during backup process? or changes are stored somewhere in the SGA, the redologs, the rollback/undo segments or some combination thereof, and then written back into the datafile when the tablespace is taken out of backup mode?

Well, around the writeable issue inside datafile there is other misconception like "During hot backup process there is generated huge amount of redo data which in fact slows down the database dramatically if the database is in archivelog mode."

Now let's know what actually happens during hot backup. The hot backup steps are,

1)The corresponding tablespace is checkpointed.

2)The checkpoint SCN marker in the datafile headers cease to increment with checkpoints.

3)Full images of changed DB blocks are written to the redologs.

Whenever you issue,

ALTER TABLESPACE tbs_name BEGIN BACKUP;

command, at that point a checkpoint is performed against the target tablespace and the datafile header is frozen, so no more updates are allowed on it (the datafile header), this is for the database to know which was the last time the tablespace had a consistent image of the data.

But during backup process, the corresponding datafiles in the tablespace allow just normal read/write operations, that is I/O activity is not frozen.

In case of redo log generation, each block will be recorded into the redo log files, the first time it the block is changed. So if a row is modified for the first time inside date block since hot backup started the complete block image is recorded in the redo log files but subsequent transactions on the block will only record the transaction just as normal.

Above three steps are required to guarantee consistency during the file is restored and recovery. By freezing the checkpoint SCN in the file headers, any subsequent recovery on that backup copy of the file will know that it must commence at that SCN. Having an old SCN in the file header tells recovery that the file is an old one, and that it should look for the redolog file containing that SCN, and apply recovery starting there. Note that checkpoints to datafiles in hot backup mode are not suppressed during the backup, only the incrementing of the main checkpoint SCN flag. A "hot backup checkpoint" SCN marker in the file header continues to increment as periodic or incremental checkpoints progress normally.

By initially checkpointing the datafiles that comprise the tablespace and logging full block images to redo, Oracle guarantees that any blocks changed in the datafile while in hot backup mode will also be available in the redologs in case they are ever used for a recovery.

Now many one claims that during hot backup process there is excessive redo log generation than in normal mode. It actually depends on the amount of blocks changes during hot backup process. Because the first time a block is changed logging of full images of changed blocks in these tablespaces are recorded to the redo logs. Normally, Oracle logs an entry in the redologs for every change in the database, but it does not log the whole image of the database block. But during the hot backup process by logging full images of changed DB blocks to the redologs, Oracle eliminates the possibility of the backup containing irresolvable split blocks. To understand this reasoning, you must first understand what a split block is.

Typically, Oracle database blocks are a multiple of O/S blocks. For instance, most windows filesystems have a default block size of 512 bytes and unix filesystems have a default blocksize 2k, while Oracle’s default block size is 8k. This means that the filesystem stores data in 512 byte chunks, while Oracle performs reads and writes in 2k chunks, or multiples thereof. While backing up a datafile, your backup script makes a copy of the datafile from the filesystem, using O/S utilities such as copy, dd, cpio, or OCOPY. As it is making this copy, it is reading in O/S-block sized increments. If the database writer happens to be writing a DB block into the datafile at the same time that your script is reading that block’s constituent O/S blocks, your backup copy of the DB block could contain some O/S blocks from before the database performed the write, and some from after. This would be a split block.

By logging the full block image of the changed block to the redologs, it guarantees that in the event of a recovery, any split blocks that might be in the backup copy of the datafile will be resolved by overlaying them with the full legitimate image of the block from the redologs. Upon completion of a recovery, any blocks that got copied in a split state into the backup will have been resolved through application of full block images from the redologs.
 
 

Database Duplication Fails Missing Log RMAN-06053 RMAN-06025  

Posted by Mawahid in


Error Stuck:
--------------------

 
While running duplicating database the following error returns.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 5 lowscn 977621 found to restore


Cause of The problem:
------------------------


The problem is that RMAN is not able to apply all the archived logs needed for complete recovery. For example, if you only backed up logs through sequence 4, but the most recent archived log is sequence 5, then DUPLICATE fails.

 
Solution of The Problem:
------------------------------


When creating the duplication script, use the SET UNTIL command to specify a log sequence number for incomplete recovery. For example, to terminate recovery after applying log sequence 5, enter:


RUN
{
SET UNTIL SEQUENCE 5 THREAD 1; # recovers up to but not including log 5
DUPLICATE TARGET DATABASE TO DUPBASE NOFILENAMECHECK;

Restore and Recover database to a new host  

Posted by Mawahid in


In this case DBID of the database will be the same as of original database. But whenever your purpose is to create a new copy of the database then use RMAN DUPLICATE; this will change the DBID of the new database.

In this example my database name dbase1 and it is running on host1, I will be taking backup on host1, transfer them to host2 and restore-recovery.


1)  On Host1 (dbase1)

RMAN> backup database; 

piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONE

Starting Control File and SPFILE Autobackup 
piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654016132_421c64vl_.bkp
Finished Control File and SPFILE Autobackup



2) Now transfer these files to host2 

3) Determine the DBID of source machine(Dbase1)

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


4) Now perform task on target machine(host2 here).

First set ORACLE_SID,


-bash-3.00$export ORACLE_SID=dbase1

 
Then connect to rman,


-bash-3.00$ rman target /


5) Set DBID and restore spfile to pfile.
 
RMAN> set dbid 3386862614


RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 bytes

RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora' from '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';


6) start the instance with pfile.
 
RMAN> STARTUP FORCE NOMOUNT PFILE='/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora';
 

7) Restore controlfile and mount the database.

RMAN> RESTORE CONTROLFILE FROM '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
 
RMAN> ALTER DATABASE MOUNT; 

8) From SQL*Plus determine the data file and redo log file name.

SQL> COLUMN NAME FORMAT a70
SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE UNION SELECT GROUP#,MEMBER FROM V$LOGFILE;


9) Catalog your backuppiece.

RMAN> catalog backuppiece '/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp';
 
RMAN> list backup; 

Here we get dbfiles and scn information, which will be useful in next step. 

10) Make a script by issuing SET NEWNAME if you want different file name other than source.

In the script issue SET UNTIL clause and restore and recover database.

RMAN> run{
set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
set newname for datafile 2 to '/oradata2/DBase1/undotbs01.dbf';
set newname for datafile 3 to '/oradata2/DBase1/sysaux01.dbf';
set newname for datafile 4 to '/oradata2/DBase1/users01.dbf';
set newname for datafile 5 to '/oradata2/DBase1/tbs201.dbf';
set newname for datafile 6 to '/oradata2/DBase1/after_01.dbf';
set newname for datafile 7 to '/oradata2/DBase1/after_02.dbf';
set newname for datafile 8 to '/oradata2/DBase1/after_03.dbf';
set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';

SET UNTIL SCN 745212;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}


11) Open the Database resetlogs option.
 
RMAN> alter database open resetlogs;

database opened.


 

ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace  

Posted by Mawahid


Problem Description:


Drop undo tablespace fails with error Ora-01548.


SQL> drop Tablespace UNDOTBS;
drop Tablespace UNDOTBS
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace

Cause of The Problem:


An attempt was made to drop a tablespace that contains active rollback segments.

Solution of The Problem:


In order to get rid of this error you have to follow following steps.


1)Create pfile if you started with database with spfile.


SQL>Create PFILE from SPFILE;

2)Edit pfile and set undo management to manual.


undo_management = manual

3)Put the entries of the undo segments in the pfile by using the following statement in the pfile:

_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,.....)

4)Mount the database using new pfile.


Startup mount pfile='fullpath to pfile'

5)Drop the datafiles.


sql>Alter Database datafile '&filename' offline drop;

6)Open the database.


sql>Alter Database Open;

7)Drop the undo segments,

sql>Drop Rollback Segment "_SYSSMU1$";
......
8)Drop Old undo tablespace.


sql>Drop Tablespace old_tablespace_name Including Contents;

9)Add the new undo tablespace.

10) Shutdown Immediate;

11) Change the pfile parameters back to Undo_management = AUTO and modify the parameter Undo_tablespace=new_undo_tablespace_name and remove the _offline_rollback_segments parameter.

12) Startup the Database.

User Managed hot backup of oracle database  

Posted by Mawahid in


Used managed backup means you take backup without any oracle feature. Suppose if you take backup by Operating system then it is called user managed backup. And the term hot backup means taking your backup whenever your database is at open state.

To take full database backup follow the following steps.

1) Before proceed remember you can take online/hot backup whenever your database is in Archivelog mode. If your database run on noarchivelog mode then you must take consistent backup that is after cleanly shutdown.


2) Determine the files that you need to take backup.

Whenever you decide to take your database backup then take backup of data files , online redo log files ,control files, spfile.

In order to decide which files you need to backup issue the following query.


SQL>SELECT NAME "File Need Backup" FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';


3) Take the tablespace in backup mode rather than offline and read-only tablespace. In case of offline and read only tablespace you do not have to place the tablespace in backup mode because the database is not permitting changes to the datafiles.

You can check the status, tablespace_name and it's associated data file name with the following query


SELECT t.STATUS,t.TABLESPACE_NAME "Tablespace", f.FILE_NAME "Datafile" FROM DBA_TABLESPACES t, DBA_DATA_FILES f
WHERE t.TABLESPACE_NAME = f.TABLESPACE_NAME;
ORDER BY t.NAME;


Take the tablespace in backup mode rather than offline and read-only tablespace.

You can easily make a script of taking the online tablespace in backup mode by following query.


SQL>SELECT 'ALTER TABLESPACE ' ||TABLESPACE_NAME ||' BEGIN BACKUP;' "Script" FROM DBA_TABLESPACES WHERE STATUS NOT IN ('READ ONLY','OFFLINE');
 

Alternatively, you can issue

SQL>ALTER DATABASE BEGIN BACKUP;


4) Copy the datafile to backup location.

After making a tablespace in backup mode take backup/copy of the associated datafiles.
 

For online tablespace you must at first take it backup mode. You can check whether backup mode now active or not by issuing following query,

SQL>SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';


Here you can also make a script in order to copy datafiles to another location. 

SQL> SELECT 'host scp '|| FILE_NAME || ' &backup_location ' "Backup Command" FROM DBA_DATA_FILES;
Enter value for backup_location: /backup
 

5) Whenever your copy is finished make the tablespace out of backup mode.

SQL>ALTER DATABASE END BACKUP; 
 

Shutdown fails with ORA-24324, ORA-24323, ORA-01090  

Posted by Mawahid


Problem Description:

 
Shut down oracle database (shutdown normal/shutdown immediate) fails with following errors.


ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted

Cause of the Problem:

 
The problem happened because background processes are hanging/not stared correctly during the previous startup of this database. Hence the smeaphores and shared memory segements are not getting detached properly now during shutdown.

Solution of the Problem:

 
1) Verify that there are no background processes owned by "oracle"


$ ps -ef | grep ora_ | grep $ORACLE_SID

If background processes exist, remove them by using the Unix "kill" command.
For example to kill a process ID number 1200 issue,


$ kill -9 1200

2) Verify that no shared memory segments and semaphores that are owned by "oracle" still exist.


If there are shared memory segments and semaphores owned by "oracle", remove the shared memory segments and semaphores.


A) checking and removing shared memory.


 Verify the shared memory segment by,

$ ipcs -mt

To remove shared memory segment issue,


$ ipcrm -m Shared_Memory_ID_Number 


where Shared_Memory_ID_Number must be replace by shared memory id number.
B) checking and removing semaphores
 
Check the semaphores by,


$ipcs -sbt

To remove the semaphores issue,


$ ipcrm -s Semaphore_ID_Number 


where Semaphore_ID_Number must be replaced by your Semaphore ID Number.

3) Verify that file $ORACLE_HOME/dbs/lk{db_name} does not exist where db_name is your actual database name.


$ cd $ORACLE_HOME/dbs
$ rm lk{db_name}


4) Verify that file "$ORACLE_HOME/dbs/sgadef{sid}.dbf" does not exist where sid is your actual database SID.


$ cd $ORACLE_HOME/dbs
$ rm sgadef{SID}.dbf


5) Exit sql*plus window, re login to sql*plus and try to bring up oracle one step at a time.


$ sqlplus /nolog
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;


Note that while performing above steps it is recommended to first bring down all the other instances running on the server and then perform the above steps one by one. Re-booting the server will also fix this problem.

How undo Space Allocation Works  

Posted by Mawahid


In two different scenarios two different mechanisms is used to allocate undo space.

 
A)Whenever a new transaction needs undo space:
-------------------------------------------------


1)First , oracle tries to find a undo segment which has no active transaction and allocate the extent within the undo segment.

2)If no such undo segment found then oracle tries to make online of and off-line undo segment and use it.

3)If no such off-line undo segment found then create a new undo segment and use it.

4)If there is no sufficient space to create a new undo segment then it try to reuse an expired extent from the existing undo segment.


B)Whenever a running transaction needs more undo space:
------------------------------------------------------------

 
1)If current extent has more free blocks? If it has then allocate the next free block within the extent.

2)If current extent does not have free blocks then check the next extent within the segment. If the next extent is expired then wrap the next extent and allocate the first block of the next extent.

3)If the next extent is not expired then get space from UNDO tablespace.If a free extent is available then allocate it to the undo segment and return the first block in the new extent.

4)If there is no free space available then take space from offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment. Return the first free block of the extent.

5)If there is no offline undo segment then deallocate the extent from the online undo segment and add it to the current undo segment. Return the first free block of the extent.

6)If it fails then extend the datafile of the undo tablespace.If the file can be extended then add an extent to the current undo segment then return the block.

7)If the extend datafile fails then try to reuse extents from its own undo segments. If it is not busy then warp into the next extent.

8)If reuse extents from its own undo segment fails i.e they contain uncommitted transaction then take unexpired extents from it's own offline undo segments.

9)If it fails then take unexpired extents from it's own online undo segments.

10)If all above fails then ORA-30036 (unable to extend segment by %s in undo tablespace '%s' ) arises.

How to restore an Spfile from autobackup older than 7 days  

Posted by Mawahid in


Whenever we issue in RMAN, RMAN> restore spfile from autobackup; then restoring from autobackup stops at seven days and then it arises message channel ORA_DISK_1: no autobackup in 7 days found. For example,

RMAN> restore spfile from autobackup;

 
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20080831
channel ORA_DISK_1: looking for autobackup on day: 20080830
channel ORA_DISK_1: looking for autobackup on day: 20080829
channel ORA_DISK_1: looking for autobackup on day: 20080828
channel ORA_DISK_1: looking for autobackup on day: 20080827
channel ORA_DISK_1: looking for autobackup on day: 20080826
channel ORA_DISK_1: looking for autobackup on day: 20080825
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Whenever you give RMAN to RESTORE SPFILE FROM AUTOBACKUP or RESTORE CONTROLFILE FROM AUTOBACKUP, RMAN begins the search on the current day or on the day specified with the SET UNTIL caluse. If no autobackup is found in the current or SET UNTIL day, RMAN checks the previous day. In this way RMAN by default checks for 7 days autobackup from the current or SET UNTIL day.

However if you want to extend your searching of autobackup more than 7 then you have to use MAXDAYS option with the RESTORE command.

For example,


RMAN>restore spfile from autobackup maxdays 30;
or
RMAN>restore controlfile from autobackup maxdays 30;

In these cases autobackup searches will be performed up to 30 days from the current or SET UNTIL day.

How to skip a tablespace from restore operation  

Posted by Mawahid in


Sometimes we want to restore full database except a certain tablespace. That is we need to skip a tablespace for restore operation. May be that the tablespace only contains tempoarary data and hence not useful for us. Or we are performing some database point in time recovery operation and we want to omit a big tablespace as the tablespace does not contain data needed for our requirement. In this way we can minimize our restore operation time.

With the RESTORE DATABASE command we perform all datafiles restore operation except those that are offline or read-only.

Note that RESTORE DATABASE does not work same as BACKUP DATABASE. With command BACKUP DATABASE, RMAN backs up datafiles along with controlfiles and spfile. But with RESTORE COMMAND operation, RMAN only restores datafiles.

To omit a certain tablespace for restore operation use RESTORE DATABASE SKIP TABLESPACE tablespace_name. Suppose I want to omit restore of indexed tablespace INDX01,INDX02 and INDX01_16K . Then my restore command will be,
 
RMAN>RESTORE DATABASE SKIP TABLESPACE INDX01, INDX02, INDX01_16K;

If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP option of ALTER DATABASE DATAFILE ... OFFLINE when taking the datafiles that belong to the tablespace offline before the restore. The DROP option indicates that RMAN does not intend to recover these files and intends to drop their tablespaces from the database after the database is opened again. In other words, FOREVER indicates that RMAN never intends to do anything with the skipped tablespaces again.

Suppose you want to skip forever to restore tablespace EXAMPLE,INDX01 and INDX02 then your command will be,

RMAN>RESTORE DATABASE SKIP FOREVER TABLESPACE EXAMPLE, INDX01, INDX02;

How to Discover DBID  

Posted by Mawahid


We need DBID whenever we lost our all database files, or whenever we need to restore spfile or control file.

After some analysis on discovering DBID I got several ways to find DBID. I will try to demonstrate the procedure.

A) If the database is up: You can query V$database and get the DBID and record it in somewhere.

or,if the database is down and you have control file then you can mount the database and query from V$SATABASE.

SQL> SELECT DBID FROM V$DATABASE;
DBID
----------
2869417476


B) If you log the RMAN backup or if you preserve output of RMAN session then you can get DBID from that output.
$rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 01:25:48 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ARJU (DBID=2869417476)


C) If you have configured AUTOBACKUP ON,
RMAN> SHOW CONTROLFILE AUTOBACKUP;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;


Now we take BACKUP DATAFILE 4
RMAN>BACKUP DATAFILE 4;
.
.
Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oracle/app/oracle/product/10.2.0/db_1/dbs/c-2869417476-20080506-04


The autobackup format is by default c-IIIIIIIIII-YYYYMMDD-QQ,(when it is %F) where:

IIIIIIIIII stands for the DBID.
YYYYMMDD is a time stamp of the day the backup is generated
QQ is the hex sequence that starts with 00 and has a maximum of FF

I have seen that this format works when we set specifically/explicitly configure as CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';

Now in the file from c-2869417476-20080506-04 we get DBID is 2869417476.


D) If you did not set Autobackup on which is by default. After many analysis I found that DBID can be seen from backup piece or any image copy that holds either SYSTEM or SYSAUX or UNDO datafiles.


Though if you backup your database as backup as compressed then with this method you will not be able to discover DBID.


If you have SYSTEM datafile or UNDO datafile either as image copy or as backup piece then you can use,


strings file_name |grep MAXVALUE, (In case of SYSTEM datafile)
strings file_name |grep MAXVALUE (In case of UNDO datafile)


to find DBID.

If you have SYSAUX datafile either as image copy or as backup piece then you can use,


strings file_name |grep DBID= to find DBID.

Examples:
-----------------
Both of these example is based on UNIX scenario.

1)
RMAN> backup datafile 1;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata1/datafiles/system01.dbf
piece handle=
/oradata2/flash_recovery_area/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T014702_41zw6p8j_.bkp

.
.
RMAN> exit;
Recovery Manager complete.


bash-3.00$
strings /oradata2/flash_recovery_area/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T014702_41zw6p8j_.bkp |grep MAXVALUE

.
.

2869417476, MAXVALUE,

So here 2869417476 is the DBID.


2)
RMAN> backup as copy datafile 2;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oradata1/datafiles/undotbs01.dbf
output filename=/oradata2/flash_recovery_area/datafile/o1_mf_undotbs1_41zwjtx6_.dbf

bash-3.00$ strings /oradata2/flash_recovery_area/datafile/o1_mf_undotbs1_41zwjtx6_.dbf |grep MAXVALUE

2869417476, MAXVALUE

Here 2869417476 is the DBID

3)From physical data file you can also follow the same method.

From sysaux datafile,

bash-3.00$
strings /oradata1/datafiles/sysaux01.dbf |grep DBID=connected to target database: (DBID=2869417476, not open)

Here 2869417476 is the DBID.

4) From whole database backup you can also follow same method.
RMAN> BACKUP DATABASE;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
.
.
piece handle=
/oradata2/flash_recovery_area/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T015705_41zwskgv_.bkp


bash-3.00$
strings /oradata2/flash_recovery_area/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T015705_41zwskgv_.bkp |grep MAXVALUE


.
.
2869417476, MAXVALUE

Here 2869417476 is the DBID.
 

About Oracle Database Incarnations  

Posted by Mawahid


 Whenever you open the database with the command ALTER DATABASE OPEN RESETLOGS , a new incarnation is created. Performing an OPEN RESETLOGS do the following,

-Archives the current online redo logs,

-Incarnation resets the log sequence number to 1, and then

-Gives the online redo logs a new time stamp and SCN.

-Increments the incarnation number, which is used to uniquely tag and identify a stream of redo.

Parent, Ancestor and Sibling Database Incarnations
------------------------------------------------------
•The incarnation from which the current incarnation branched following an OPEN RESETLOGS operation is called the parent incarnation of the current incarnation.

•The parent incarnation and all of its parent incarnations are the ancestor incarnations of the current incarnation.

•Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.

•To view the incarnation history of a database use the RMAN>LIST INCARNATION; commnad.

•By default, when used command like FLASHBACK DATABASE or RECOVER... UNTIL, an SCN is assumed to refer to the current incarnation path, rather than sibling incarnations.

•However, you can use the RESET DATABASE TO INCARNATION command to specify that SCNs are to be interpreted in the frame of reference of another incarnation.

•For example my current database INCARNATION is 5 and now I have used
FLASHBACK DATABASE TO SCN 6666;
then SCN 6666 will be search in current incarnation which is 5. However if I want to get back to SCN 6666 of INCARNATION 4 then I have to use,

RMAN> RESET DATABASE TO INCARNATION 4;
RMAN> RECOVER DATABASE TO SCN 6666;


•When a database goes through multiple incarnations, some backups can become orphaned. Orphaned backups are backups that are created during incarnations of the database that are not ancestors of the current incarnation.

•For example, database incarnation is 5 and SCN is 7000. A DBPITR is done at SCN 7000 to SCN 6666 and then RESETLOGS is performed. Now in current incarnation 6 the backup taken between 6666 and 7000 SCN became orphaned.

•Orphaned backups are usable by RMAN in cases where you wish to restore the database to a point in time not in the current incarnation path.

RMAN duplicate fails with RMAN-10006, ORA-03113, ORA-01092  

Posted by Mawahid in

Problem Description:

While duplicating database using RMAN it fails with ORA-01092: "ORACLE instance terminated. Disconnection forced" and ora-03113 error reported.
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10039: error encountered while polling for RPC completion on channel clone_default
RMAN-10006: error running SQL statement: select act from x$ksusex where sid=:1 and serial=:2
RMAN-10002: ORACLE error: ORA-03113: end-of-file on communication channel
RMAN-03002: failure of Duplicate Db command at 02/30/2010 01:21:18
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database:
ORA-01092: ORACLE instance terminated. Disconnection forced
If we look for the auxiliary database alert.log file then it shows that Undo Tablespace 'UNDOTBS1' does not exist or of wrong type.

Cause of the Problem
The UNDO_TABLESPACE parameter of auxiliary database is not same as target database. In the source database undo tablespace is set to UNDOTBS2 while in target database undo tablespace is set to UNDOTBS1. The undo tablespace specified in the auxiliary database parameter file does not exist.

Solution of the Problem
Make sure that the undo tablespace for auxiliary database is same as of target database.
Edit the init.ora or alter the UNDO_TABLESPACE parameter of the auxiliary instance and set
undo_tablespace = UNDOTBS2
- restart the auxiliary instance in nomount mode and repeat the duplicate database command.