Changing an Oracle DBID (Database Identifier) in Oracle 8i and 9i Release 2

Changing an Oracle DBID (Database Identifier) in Oracle 8i and 9i Release 2

Oracle 9i Release 2 (9.2.0.1)

In Oracle 9i Release 2 (9.2.0.1), Oracle provides the ability to change the DBID and/or DBNAME through a new feature, the DBNEWID utility, and works outside of RMAN. Complete documentation on this utility is in the Oracle 9i Database Utilities Release 2 (9.2) part number A96642-01 manual.

This paper does not cover Oracle 9i Release 1, as I did not try the following procedures against it. The rest of this paper covers Oracle 8i.

Oracle 8i

In Oracle 8i, Oracle provides the procedure “dbms_backup_recovery.zeroDbid” for this purpose. While Oracle does not formally support it, Oracle note 174625.1 covers its usage.

If you are using RMAN and choose to use the following method based on Note 174625.1, please keep in mind Notes 105832.6 and 1062736.6 Should RMAN already know about the original and clone databases, you will need to deregister and re-register the databases with RMAN.

The basic concept is:

1. With the database in mount but NOT open mode.
2. Use dbms_backup_recover.zeroDbid to zero the DBID
3. Recreate the control files to assign a new DBID to the database.

DISCLAIMER

Neither Oracle support nor I will provide support for the following procedure. I can only say it worked for me. You perform it at your own risk. According to Oracle Support it runs the risk of rendering the database unusable, and therefore exists for the purpose of testing in a non-production environment. Never use this procedure on a production system; it should only be run on clone copies.

As always, before any major or significant changes to a database, you should perform a cold (closed) backup.
Step by Step Instructions
To Change an Oracle Database Identifier (DBID)

1. Connect to the database in SYSDBA mode.
2. Perform a clean shutdown. Do NOT abort or force the instance. This shutdown is the same as when you are going to do a cold / closed database backup. You may wish to perform a cold / closed database backup at this time, as a measure of protection.

shutdown immediate

3. Startup the database in mount mode

statup mount

4. Check the present (soon to be old) database ID (DBID)

select dbid, name

from v$database;



DBID NAME

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

578456994 SNEAKERS



Elapsed: 00:00:00.20

5. Generate the create controlfile statement into a trace file.

alter database backup controlfile to trace;

6. Generate a new DBID using the zeroDbid function of dbms_backup_restore

execute dbms_backup_restore.zeroDbid(0)

File: DBMSBKRS.SQL

NOTE: The value zero (0) is a special case, which is the case we need.



PROCEDURE zeroDbid(fno IN binary_integer);



-- This procedure is used to update the header of a datafile so that the

-- a new dbid would be calculated later on with a create controlfile

-- statement. This is necessary for the cloning of a database

-- The file to update is identified by the fno input parameter, a special

-- case is allowed when fno == 0, such that all the headers of the datafiles

-- in the control file are zeroed out.

-- Three fields in the datafile header (stored in the first block)

-- are zeroed out:

-- The field that holds the database id

-- The checksum field

-- The bit that signals that the checksum is valid

--

-- There are no return values

--

-- Input parameters:

-- fno

-- Identifies the file number of the datafile that will be modified

-- If it is zero, all datafiles listed in the controlfile are updated.

-- Output parameters:

-- NONE



7. Get another clean shutdown.

shutdown normal

8. Delete the controlfiles (Unix: rm; Windows erase).



9. Start up the database in nomount mode.

startup nomount

10. Recreate the control files modifying it for your database and possible new name (if this is a new clone). You can only use the instructions for recreating the controlfile (create controlfile).

Change from:

CREATE CONTROLFILE REUSE DATABASE "SNEAKERS" NORESETLOGS NOARCHIVELOG

Change to:

CREATE CONTROLFILE SET DATABASE RESETLOGS NOARCHIVELOG

Use only the CREATE CONTROLFILE command from this trace file. Remove all the other statements (e.g. recover, etc.)

11. Open the database.

alter database open resetlogs;

12. Check the new DBID. It should be different.

select dbid, name

from v$database;



DBID NAME

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

689567005 SNEAKERS



Elapsed: 00:00:00.20