How to change DBID in Oracle 11g ?

Oracle version :-  11.2.0.1.0
Database Sid : prim


While we clone the database, the DB ID remains same as like the source database, so if we need to change it to the different DB ID, then use the follwing
method.Also when we change DBID of the database all previous backups will become unusable and we must open the database with RESETLOGS option.
Once you change the DBID make sure you take database backup immediately.

Step 1.
Find out current dbid :-
SQL> select dbid from v$database;

      DBID
----------
4130009889

Step 2.
Shutdown & start database in mount stage.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes
Database mounted.
SQL>

Step 3.Now change the dbid using NID Utility.
[oracle@server1 ~]$ nid target=/

DBNEWID: Release 11.2.0.1.0 - Production on Thu Nov 19 09:11:21 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database PRIM (DBID=4130009889)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/prim/control01.ctl
    /u01/app/oracle/flash_recovery_area/prim/control02.ctl

Change database ID of database PRIM? (Y/[N]) => y

Proceeding with operation
Changing database ID from 4130009889 to 4188450681
    Control File /u01/app/oracle/oradata/prim/control01.ctl - modified
    Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/prim/system01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/sysaux01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/undotbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/users01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/example01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/temp01.db - dbid changed
    Control File /u01/app/oracle/oradata/prim/control01.ctl - dbid changed
    Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - dbid changed
    Instance shut down

Database ID for database PRIM changed to 4188450681.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

Step 4.Start the database in mount stage and open it using RESETLOGS option.
[oracle@server1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 19 09:13:39 2015

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select dbid from v$database;

      DBID
----------
4188450681

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
prim

So we see the dbname has been changed into new one. Done..

P.S.After changing the DBID make sure you take database backup immediately.Since all the previous backups are nolonger can be used.

Nenhum comentário:

Postar um comentário

Related Posts Plugin for WordPress, Blogger...