HOW TO CHANGE DBID of NON-RAC DATABASE
Y.MORAN
11.01.2011
1. First, take a backup of database.
2. Mount the database.
C:\Documents and Settings\dbadmin>set ORACLE_SID=PRODDB
C:\Documents and Settings\dbadmin>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 11 09:53:57 2011
Copyright (c) 1982, 2005, Oracle.
All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select instance_name from v$instance;
INSTANCE_NAME
---------------PRODDB
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
HOW TO CHANGE DBID of NON-RAC DATABASE
Y.MORAN
11.01.2011
SQL> startup mount;
ORACLE instance started.
Total System Global Area
Fixed Size
805306368 bytes
1251532 bytes
Variable Size
171968308 bytes
Database Buffers
624951296 bytes
Redo Buffers
7135232 bytes
Database mounted.
3. Set ORACLE HOME
C:\Documents and Settings\dbadmin>set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
(for unix,linux based systems, use export ORACLE_HOME=...)
C:\Documents and Settings\dbadmin>cd %ORACLE_HOME%
(for unix,linux based systems, cd $ORACLE_HOME)
C:\oracle\product\10.2.0\db_1>cd bin
C:\oracle\product\10.2.0\db_1\BIN>
HOW TO CHANGE DBID of NON-RAC DATABASE
Y.MORAN
11.01.2011
4. check Tnsnames.ora entry for the db.
TNS entry of the PRODDB
PRODDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tstsrv)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRODDB)
)
)
5. check ifyou able to connect database. (also, you can use tnsping.)
C:\oracle\product\10.2.0\db_1\BIN>sqlplus sys/oracle@PRODDB as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 11 09:59:30 2011
Copyright (c) 1982, 2005, Oracle.
All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
HOW TO CHANGE DBID of NON-RAC DATABASE
Y.MORAN
11.01.2011
SQL> select instance_name from v$instance;
INSTANCE_NAME
---------------PRODDB
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
6. Usage of NID
C:\oracle\product\10.2.0\db_1\BIN>nid TARGET=SYS/oracle@PRODDB
DBNEWID: Release 10.2.0.1.0 - Production on Tue Jan 11 10:03:16 2011
Copyright (c) 1982, 2005, Oracle.
All rights reserved.
Connected to database PRODDB (DBID=985788221)
Connected to server version 10.2.0
Control Files in database:
D:\PRODDB\CONTROL01.CTL
D:\PRODDB\CONTROL02.CTL
D:\PRODDB\CONTROL03.CTL
HOW TO CHANGE DBID of NON-RAC DATABASE
Y.MORAN
11.01.2011
Change database ID of database PRODDB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 985788221 to 1071734893
Control File D:\PRODDB\CONTROL01.CTL - modified
Control File D:\PRODDB\CONTROL02.CTL - modified
Control File D:\PRODDB\CONTROL03.CTL - modified
Datafile D:\PRODDB\SYSTEM01.DBF - dbid changed
Datafile D:\PRODDB\UNDOTBS01.DBF - dbid changed
Datafile D:\PRODDB\SYSAUX01.DBF - dbid changed
Datafile D:\PRODDB\USERS01.DBF - dbid changed
Datafile D:\PRODDB\INDX01.DBF - dbid changed
Datafile D:\PRODDB\UNDOTBS.DBF - dbid changed
Datafile D:\PRODDB\USERS02.DBF - dbid changed
Datafile D:\PRODDB\USER03.DBF - dbid changed
Datafile C:\PRODDB\INDX02.DBF - dbid changed
Datafile C:\PRODDB\USERS04.DBF - dbid changed
Datafile D:\PRODDB\TEMP01.DBF - dbid changed
HOW TO CHANGE DBID of NON-RAC DATABASE
Y.MORAN
11.01.2011
Control File D:\PRODDB\CONTROL01.CTL - dbid changed
Control File D:\PRODDB\CONTROL02.CTL - dbid changed
Control File D:\PRODDB\CONTROL03.CTL - dbid changed
Instance shut down
Database ID for database PRODDB changed to 1071734893.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
C:\oracle\product\10.2.0\db_1\BIN>sqlplus sys/oracle@PRODDB as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 11 10:04:53 2011
Copyright (c) 1982, 2005, Oracle.
All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area
Fixed Size
805306368 bytes
1251532 bytes
HOW TO CHANGE DBID of NON-RAC DATABASE
Y.MORAN
11.01.2011
Variable Size
171968308 bytes
Database Buffers
624951296 bytes
Redo Buffers
7135232 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> select instance_name from v$instance;
INSTANCE_NAME
---------------PRODDB
SQL> select dbid from v$database;
DBID
---------1071734893
HOW TO CHANGE DBID of NON-RAC DATABASE
Y.MORAN
11.01.2011
“Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database
become unusable. This is similar to creating a database except that the data is already in the datafiles. After you change the DBID, backups and archive logs
that were created prior to the change can no longer be used because they still have the original DBID, which does not match the current DBID. You must open
the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1 (see the Oracle Database Administrator's
Guide). Consequently, you should make a backup of the whole database immediately after changing the DBID.”
Reference:
How to Change the DBID, DBNAME Using NID Utility [ID 863800.1]