Thursday, July 21, 2011

Oracle: Rename single/RAC Database

Method 1: Recreate control file with the new DB name, the old tried and tested method.
Method 2: Use new utility called NID
Steps to rename Databases using the new NID utility:
1.) Stop database

[oracle@testing1]~% srvctl status database -d test
Instance test1 is running on node testing1
Instance test2 is running on node testing2

srvctl stop database -d test


2.) startup mount the database:

[oracle@testing1]~% sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 21 00:50:44 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.2527E+10 bytes
Fixed Size 2238104 bytes
Variable Size 8120174952 bytes
Database Buffers 4294967296 bytes
Redo Buffers 109346816 bytes
Database mounted.

3.) Exit and run the "nid" utility

nid sys/oracle@test DBNAME=new


[oracle@testing1]~% nid sys/m4gent4 DBNAME=new

DBNEWID: Release 11.2.0.2.0 - Production on Thu Jul 21 00:55:12 2011

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

NID-00002: Parse error: LRM-00108: invalid positional parameter value 'sys/m4gent4'

Change of database ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.



Correct the syntax:
nid target=sys/oracle@test DBNAME=new



[oracle@testing1]~% nid target=sys/m4gent4 DBNAME=new

DBNEWID: Release 11.2.0.2.0 - Production on Thu Jul 21 00:56:04 2011

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

Connected to database test (DBID=3952648861)

NID-00120: Database should be mounted exclusively


Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.


Incase of RAC, we need to set cluster_database=FALSE to change DB name. Then stop and mount the DB again.


SQL> alter system set cluster_database=FALSE scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open


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

Total System Global Area 1.2527E+10 bytes
Fixed Size 2238104 bytes
Variable Size 8120174952 bytes
Database Buffers 4294967296 bytes
Redo Buffers 109346816 bytes
Database mounted.



nid target=sys/oracle@test DBNAME=new

[oracle@testing1]~% nid target=sys/m4gent4 DBNAME=new

DBNEWID: Release 11.2.0.2.0 - Production on Thu Jul 21 00:59:54 2011

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

Connected to database test (DBID=3952648861)

Connected to server version 11.2.0

Control Files in database:
+DG_SYS01/test/control01.ctl
+DG_DATA01/test/control02.ctl

Change database ID and database name test to NEW? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3952648861 to 388598347
Changing database name from test to NEW
Control File +DG_SYS01/test/control01.ctl - modified
Control File +DG_DATA01/test/control02.ctl - modified
Datafile +DG_SYS01/test/datafile/system.261.75517911 - dbid changed, wrote new name
Datafile +DG_SYS01/test/datafile/sysaux.262.75517911 - dbid changed, wrote new name
Datafile +DG_DATA01/test/datafile/undotbs1.258.75518469 - dbid changed, wrote new name
Datafile +DG_DATA01/test/datafile/undotbs2.260.75518577 - dbid changed, wrote new name
Datafile +DG_DATA01/test/datafile/users.257.75518565 - dbid changed, wrote new name
Control File +DG_SYS01/test/control01.ctl - dbid changed, wrote new name
Control File +DG_DATA01/test/control02.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to NEW.
Modify parameter file and generate a new password file before restarting.
Database ID for database NEW changed to 388598347.
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.


4.) Copy the old inittest1.ora as initnew1.ora, update the DB_NAME, instance_name parameter to reflect the new name.
Then startup mount


SQL> startup mount pfile='/home/oracle/software/dbcreate/test/inittest1.ora'
ORACLE instance started.

Total System Global Area 1.2527E+10 bytes
Fixed Size 2238104 bytes
Variable Size 8120174952 bytes
Database Buffers 4294967296 bytes
Redo Buffers 109346816 bytes
Database mounted.


5.) Resetlog open the database.

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE



6.) Create spfile

SQL> create spfile='+DG_SYS01/NEW/spfileNEW.ora' from pfile='/home/oracle/software/dbcreate/test/inittest1.ora';

File created.

NOTE: For single node DB, steps are over. Step 7 for RAC only.

7.) Add DB to the cluster

[oracle@testing1]~/software/dbcreate/test% srvctl add database -d NEW -o /home/oracle/product/11.2 -r primary -s OPEN -p +DG_SYS01/NEW/spfileNEW.ora
[oracle@testing1]~/software/dbcreate/test% srvctl add instance -d NEW -i NEW1 -n testing1
[oracle@testing1]~/software/dbcreate/test% srvctl add instance -d NEW -i NEW2 -n testing2
[oracle@testing1]~/software/dbcreate/test% srvctl config database -d NEW -a
Database unique name: NEW
Database name:
Oracle home: /home/oracle/product/11.2
Oracle user: oracle
Spfile: +DG_SYS01/NEW/spfileNEW.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: NEW
Database instances: NEW1,NEW2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
[oracle@testing1]~/software/dbcreate/test% vi inittest1.ora
[oracle@testing1]~/software/dbcreate/test% srvctl start database -d NEW
[oracle@testing1]~/software/dbcreate/test% srvctl status database -d NEW
Instance NEW1 is running on node testing1
Instance NEW2 is running on node testing2

INST_ID INSTANCE_NAM HOST_NAME STATUS STARTED VERSION
-------- ------------------------- ----------------------------------- ----------
1 new1 testing1 OPEN 20 Jul 18:25:20 11.2.0.2.0
2 new2 testing2 OPEN 20 Jul 18:25:22 11.2.0.2.0

6 Comments:

sal said...

Thanks for the instructions!

sal said...

Thank you! Worked great!

I ran into an issue and had to...

when changing CLUSTER_DATABASE to FALSE, the value would not stick unless I included the SID='*' clause.

sal said...

Thank you! Worked great!

I ran into an issue and had to...

when changing CLUSTER_DATABASE to FALSE, the value would not stick unless I included the SID='*' clause.

sal said...

Thank you! Worked great!

I ran into an issue and had to...

when changing CLUSTER_DATABASE to FALSE, the value would not stick unless I included the SID='*' clause.

Anonymous said...

Thank you! Very helpful!

Anonymous said...

Good Blog!
Will datafile diskgroup be renamed automatically and all datafiles be moved to new directory (in this case new) automatically?