changing DBID in oracle database

While cloning the database using RMAN. We usually face the problem in DBID.After cloning a database it change only the name of the database but  DBID remains as same before.It reflects in various concern. I have faced this issue while  exporting table from cloned database to another database.In order to change the dbname along with DBID we have option.We can use oracle utilities called as NID.Using NID we can change database name without changing the DBID

otherwise we can change only the database name. But there is no option to change only the DBID using the NID utility.

If we want to change only the DBID. Follow these steps which is given below

NOTE: Before any major or significant changes to a database, you should perform a cold (closed) backup.

Steps to change the DBID.
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;

Output:
DBID                    NAME

——————   ————-

578456994  HR9DEMO

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.

7. Get another clean shutdown.

shutdown normal

8. Delete the controlfiles .

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;

Now you can find the different DBID in the database.

Advertisements

About PARTHASARATHY.G
I am working as an Associate DBA in fugo consulting.

One Response to changing DBID in oracle database

  1. Marijn says:

    Partha,
    Do you use duplicate command in RMAN ? This will change your DBID in the clone database automatically. So your script is not necessary.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: