Changing Database ID and Name is just so easy as 1,2 and 3...
Here it goes:
NID --> is the command that is used to change the database name.
Let's say I need to change the database name from SAN1 to SAN2.
Steps to follow:
01. No need to say Backup is important correct !!!
02. Shutdown the database that needs a database name change
03. Startup in MOUNT mode
04. Run NID command:
nid TARGET=sys/sys_password_here@SAN1 DBNAME=SAN2
In the above command:
nid --> Oracle Command Tool DBNEWID
Target --> The database connection information for the source database that needs a name change.
DBNAME --> Its the New name for your database
05. From the NID command, it will prompt you to make up your mind before renaming the database. Respond according to your mind set to that prompt and continue.
06. Once its done, you need to set your db_name parameter in the spfile.
07. Make sure to set your oraenv is still pointing to the OLD DB Name (SAN1)
08. So, Start the database in MOUNT mode. (dont worry about ORA-01103 here)
09. change the spfile:
SQL> alter system set db_name=SAN2 scope=spfile;
10. Come out from sqlplus and create a password file using ORAPWD utility
11. Copy/Change your initfile (that refers to your spfile) name to reflect the NEW DB Name
12. Connect to sqlplus and start the database in MOUNT mode
13. Now Open with resetlogs:
SQL> alter database open resetlogs;
14. Last but least, reload the listener:
lsnrctl reload
15. Now the database name is SAN2 and Listener status also shows its active and you are good to go.
Following is the run thru of the above steps:
/oracle/product/10.2.0/db_1:(+ASM)$ . oraenv
ORACLE_SID = [+ASM] ? SAN1
/oracle/product/10.2.0/db_1:(SAN1)$ s
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 26 11:34:31 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2083656 bytes
Variable Size 704644280 bytes
Database Buffers 536870912 bytes
Redo Buffers 14692352 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
/oracle/product/10.2.0/db_1:(SAN1)$ nid TARGET=sys/lmao_sorry@SAN1 DBNAME=SAN2
DBNEWID: Release 10.2.0.4.0 - Production on Wed Aug 26 11:36:05 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database SAN1 (DBID=3609722023)
Connected to server version 10.2.0
Control Files in database:
+DGROUP1/SAN1/control01.ctl
+DGROUP1/SAN1/control02.ctl
+DGROUP1/SAN1/control03.ctl
Change database ID and database name SAN1 to SAN2? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1234567890 to 0987654321
Changing database name from SAN1 to SAN2
Control File +DGROUP1/SAN1/control01.ctl - modified
Control File +DGROUP1/SAN1/control02.ctl - modified
Control File +DGROUP1/SAN1/control03.ctl - modified
Datafile +DGROUP1/SAN1/datafile/system.350.695906669 - dbid changed, wrote new name
Datafile +DGROUP1/SAN1/datafile/undots1.1361.695906675 - dbid changed, wrote new name
Datafile +DGROUP1/SAN1/datafile/sysaux.348.695906679 - dbid changed, wrote new name
Datafile +DGROUP1/SAN1/datafile/users.1201.695906683 - dbid changed, wrote new name
Datafile +DGROUP1/SAN1/datafile/tools.345.695906685 - dbid changed, wrote new name
Datafile +DGROUP1/SAN1/tempfile/temp.1205.695906681 - dbid changed, wrote new name
Control File +DGROUP1/SAN1/control01.ctl - dbid changed, wrote new name
Control File +DGROUP1/SAN1/control02.ctl - dbid changed, wrote new name
Control File +DGROUP1/SAN1/control03.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to SAN2.
Modify parameter file and generate a new password file before restarting.
Database ID for database SAN2 changed to 169189862.
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.
/oracle/product/10.2.0/db_1:(SAN1)$ . oraenv
ORACLE_SID = [SAN1] ? SAN1
/oracle/product/10.2.0/db_1:(SAN1)$ s
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 26 11:41:33 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2083656 bytes
Variable Size 704644280 bytes
Database Buffers 536870912 bytes
Redo Buffers 14692352 bytes
ORA-01103: database name 'SAN2' in control file is not 'SAN1'
SQL> alter system set db_name=SAN2 scope=spfile;
System altered.
SQL>
$> orapwd file=/oracle/product/10.2.0/db_1/dbs/pwdSAN2 password=lmao_sorry entries=10
$> Copied existing initSAN1.ora to initSAN2.ora
/oracle/product/10.2.0/db_1/dbs:(hmm9test)$ . oraenv
ORACLE_SID = [hmm9test] ? SAN2
/oracle/product/10.2.0/db_1/dbs:(SAN2)$ s
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 26 11:52:46 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2083656 bytes
Variable Size 704644280 bytes
Database Buffers 536870912 bytes
Redo Buffers 14692352 bytes
Database mounted.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>
/oracle/product/10.2.0/db_1/dbs:(SAN2)$ lsnrctl reload
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 26-AUG-2009 11:54:29
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
/oracle/product/10.2.0/db_1/dbs:(SAN2)$
No comments:
Post a Comment