Thursday, September 20, 2012

Rename DB and DBID after refreshing on another server

Changing DB name & DBID using NID utility

[oracle@lnx-122 dbs]$ nid target=sys/abc123 dbname=cric
DBNEWID: Release 11.2.0.2.0 - Production on Thu Sep 20 09:40:32 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database MAZE (DBID=1370075061)
Connected to server version 11.2.0
Control Files in database:
/u01/app/oracle/oradata1/cric/control01.ctl
/u01/app/oracle/oradata2/cric/control02.ctl
Change database ID and database name MAZE to CRIC? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1370075061 to 566499152
Changing database name from MAZE to CRIC
Control File /u01/app/oracle/oradata1/cric/control01.ctl - modified
Control File /u01/app/oracle/oradata2/cric/control02.ctl - modified
Datafile /u01/app/oracle/oradata2/cric/system01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata2/cric/sysaux01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata1/cric/undotbs01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata1/cric/users01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata1/cric/example01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata2/cric/appstbs_01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata1/cric/appstbs_01.db - dbid changed, wrote new name
Control File /u01/app/oracle/oradata1/cric/control01.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/oradata2/cric/control02.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to CRIC.
Modify parameter file and generate a new password file before restarting.
Database ID for database CRIC changed to 566499152.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 20 09:41:20 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 331352984 bytes
Database Buffers 83886080 bytes
Redo Buffers 6086656 bytes
ORA-01103: database name 'CRIC' in control file is not 'MAZE'
 
SQL> shut immediate;
ORA-01507: database not mounted
 
ORACLE instance shut down.
SQL> exit

Since I didnt' make the changes to pfile & orapwd file the above error popped out. Make the changes in pfile & then set the right ORACLE_SID.  
[oracle@lnx-122 dbs]$ export ORACLE_SID=cric
[oracle@lnx-122 dbs]$ sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 20 09:51:39 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 260049816 bytes
Database Buffers 155189248 bytes
Redo Buffers 6086656 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
 --> start datbase with open resetlogs
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$database;
NAME
---------
CRIC
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oraredo/cric/redo03.log
/u01/app/oracle/oraredo/cric/redo02.log
/u01/app/oracle/oraredo/cric/redo01.log
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata1/cric/control01.ctl
/u01/app/oracle/oradata2/cric/control02.ctl
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata2/cric/system01.dbf
/u01/app/oracle/oradata2/cric/sysaux01.dbf
/u01/app/oracle/oradata1/cric/undotbs01.dbf
/u01/app/oracle/oradata1/cric/users01.dbf
/u01/app/oracle/oradata1/cric/example01.dbf
/u01/app/oracle/oradata2/cric/appstbs_01.dbf
/u01/app/oracle/oradata1/cric/appstbs_01.dbf
7 rows selected.

Note: Refer to oracle documention before using nid utility.

No comments:

Post a Comment