This guide is tested on:
Oracle RDBMS Version: 10.2.0.5.0 and 11.2.0.1.0
Operating System: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
Old ORACLE_SID: ORCL10G
New ORACLE_SID: ORCL10G4
1. Perform full database backup. Ensure that is recoverable.
2. Restart database in mount stage.
SQL> shutdown immediate;
SQL> startup mount;SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
3A. To change DBNEWID only, Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege.SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 121635396 bytes
Database Buffers 159383552 bytes
Redo Buffers 2920448 bytes
Database mounted.
[oracle@oracle10g dbs]$ . oraenv
ORACLE_SID = [ORCL10G] ? ORCL10G
[oracle@oracle10g admin]$ nid TARGET=SYS/oracle@ORCL10G
[oracle@oracle10g dbs]$ sqlplus '/as sysdba'[oracle@oracle11g dbs]$ nid TARGET=SYS/oracle@ORCL10G
DBNEWID: Release 10.2.0.5.0 - Production on Thu May 3 11:36:14 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database ORCL10G (DBID=966597315)
Connected to server version 10.2.0
Control Files in database:
/u02/oradata/ORCL10G/controlfile/o1_mf_7t3z22qo_.ctl
Change database ID of database ORCL10G? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 966597315 to 966743407
Control File /u02/oradata/ORCL10G/controlfile/o1_mf_7t3z22qo_.ctl - modified
Datafile /u02/oradata/ORCL10G/datafile/o1_mf_system_7t3z4fqg_.dbf - dbid changed
Datafile /u02/oradata/ORCL10G/datafile/o1_mf_undotbs1_7t3z4ft2_.dbf - dbid changed
Datafile /u02/oradata/ORCL10G/datafile/o1_mf_sysaux_7t3z4fqr_.dbf - dbid changed
Datafile /u02/oradata/ORCL10G/datafile/o1_mf_users_7t3z4g4c_.dbf - dbid changed
Datafile /u02/oradata/ORCL10G/datafile/o1_mf_temp_7t3z73ms_.tmp - dbid changed
Control File /u02/oradata/ORCL10G/controlfile/o1_mf_7t3z22qo_.ctl - dbid changed
Instance shut down
Database ID for database ORCL10G changed to 966743407.
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.
SQL> startup mount;[oracle@oracle11g dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.5.0 - Production on Thu May 3 11:38:00 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> alter database open RESETLOGS;SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 121635396 bytes
Database Buffers 159383552 bytes
Redo Buffers 2920448 bytes
Database mounted.
3B. To change database name in addition to the DBID, specify the DBNAME parameter. Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege.SQL> alter database open RESETLOGS;
Database altered.
Export spfile.
SQL> create pfile from spfile;
[oracle@oracle10g dbs]$ nid TARGET=SYS/oracle@ORCL10G DBNAME=ORCL10G4
Modify oratab.[oracle@oracle11g dbs]$ nid TARGET=SYS/oracle@ORCL10G DBNAME=ORCL10G4
DBNEWID: Release 10.2.0.5.0 - Production on Thu May 3 11:42:23 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database ORCL10G (DBID=966743407)
Connected to server version 10.2.0
Control Files in database:
/u02/oradata/ORCL10G/controlfile/o1_mf_7t3z22qo_.ctl
Change database ID and database name ORCL10G to ORCL10G4? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 966743407 to 906946335
Changing database name from ORCL10G to ORCL10G4
Control File /u02/oradata/ORCL10G/controlfile/o1_mf_7t3z22qo_.ctl - modified
Datafile /u02/oradata/ORCL10G/datafile/o1_mf_system_7t3z4fqg_.dbf - dbid changed, wrote new name
Datafile /u02/oradata/ORCL10G/datafile/o1_mf_undotbs1_7t3z4ft2_.dbf - dbid changed, wrote new name
Datafile /u02/oradata/ORCL10G/datafile/o1_mf_sysaux_7t3z4fqr_.dbf - dbid changed, wrote new name
Datafile /u02/oradata/ORCL10G/datafile/o1_mf_users_7t3z4g4c_.dbf - dbid changed, wrote new name
Datafile /u02/oradata/ORCL10G/datafile/o1_mf_temp_7t3z73ms_.tmp - dbid changed, wrote new name
Control File /u02/oradata/ORCL10G/controlfile/o1_mf_7t3z22qo_.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to ORCL10G4.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL10G4 changed to 906946335.
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.
vi /etc/oratab
Modify the below line to reflect the new database name
ORCL10G4:/u01/app/oracle/product/10.2.0/db_1:N
Modify the initialization parameter file.
mv $ORACLE_HOME/dbs/initORCL10G.ora $ORACLE_HOME/dbs/initORCL10G4.ora
vi $ORACLE_HOME/dbs/initORCL10G4.ora
Change *.db_name='ORCL10G' to *.db_name='ORCL10G4'
Recreate password file.
[oracle@oracle11g dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwORCL10G4 password=oracle entries=5
Change ORACLE_SID to the new database name.
[oracle@oracle10g dbs]$ . oraenv
ORACLE_SID = [ORCL10G] ? ORCL10G4
[oracle@oracle10g dbs]$ sqlplus '/as sysdba'
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initORCL10G4.ora[oracle@oracle11g dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.5.0 - Production on Thu May 3 11:38:00 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
Recreate the new spfile.SQL> startup nomount pfile=$ORACLE_HOME/dbs/initORCL10G4.ora
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 92275268 bytes
Database Buffers 188743680 bytes
Redo Buffers 2920448 bytes
SQL> create spfile from pfile;
Restart database in mount stage.
SQL> startup force mount;
Open the database with RESETLOGS option.SQL> startup force mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 92275268 bytes
Database Buffers 188743680 bytes
Redo Buffers 2920448 bytes
Database mounted.
SQL> alter database open RESETLOGS;
Verify that the new database name is reflected.SQL> alter database open RESETLOGS;
Database altered.
SQL> select name from v$database;
4. Modify listener.SQL> select name from v$database;
NAME
---------
ORCL10G4
5. Modify tnsnames
-- DONE --