How to rename a database instance on the same host

Database, Oracle, ,

Something that I was working on and it turned to be very easy. Below you’ll find what I did:

MAIN3 = the name of the main database
CL3= the name of the clone database

Prepare the new database locations:
# Create the necessary folders:
 mkdir -p /u01/app/oracle/admin/CL3/adump
 mkdir -p /u02/oradata/CL3
 mkdir -p /u03/oradata/CL3
 mkdir -p /u01/app/oracle/fast_recovery_area/CL3
 mkdir -p /u02/fast_recovery_area/CL3
# add this to /etc/oratab
 CL3:/u01/app/oracle/product/11.2.0.4/db_1:N
# Put the database in backup mode and copy it in the folder where the new database will hold the datafiles:
SQL> alter database begin backup;
Database altered.
SQL> exit
# Get the location of the datafiles:
 SQL> select name from v$datafile;
NAME
 --------------------------------------------------------------------------------
 /u02/oradata/MAIN3/system01.dbf
 /u02/oradata/MAIN3/sysaux01.dbf
 /u02/oradata/MAIN3/undotbs01.dbf
 /u02/oradata/MAIN3/users01.dbf
 /u02/oradata/MAIN3/example01.dbf
cp /u02/oradata/MAIN3/* /u02/oradata/CL3
# Copy the controlfiles:
 cp /u02/fast_recovery_area/MAIN3 /u02/fast_recovery_area/CL3
#Copy the ORLs:
 SQL> select member from v$logfile;
MEMBER
 --------------------------------------------------------------------------------
 /u03/oradata/MAIN3/redo03.log
 /u03/oradata/MAIN3/redo02.log
 /u03/oradata/MAIN3/redo01.log
SQL>
cp /u03/oradata/MAIN3/* /u03/oradata/CL3
# End the backup mode of the database:
 SQL> alter database end backup;
Database altered.
SQL>
# Create the pfile with the new name:
SQL> create pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initCL3.ora' from spfile;
File created.
SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> exit
SQL>
# Adapt the init file to reflect the new location of the instance (so we are not messing up with the main instance):
 .audit_file_dest
 .control_files=
# Adapt the path of the datafiles within the clone CL3:
 [[email protected] dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 6 06:59:30 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
 ORACLE instance started.
Total System Global Area 1252663296 bytes
 Fixed Size 2252824 bytes
 Variable Size 402657256 bytes
 Database Buffers 838860800 bytes
 Redo Buffers 8892416 bytes
 Database mounted.
 SQL> set pagesize 900
 set lin 800SQL>
 SQL> select 'alter database rename file '''||name||''' to '''|| replace(name,'MAIN3','CL3')||''';' from v$datafile;
'ALTERDATABASERENAMEFILE'''||NAME||'''TO'''||REPLACE(NAME,'MAIN3','CL3')||''';'
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 alter database rename file '/u02/oradata/MAIN3/system01.dbf' to '/u02/oradata/CL3/system01.dbf';
 alter database rename file '/u02/oradata/MAIN3/sysaux01.dbf' to '/u02/oradata/CL3/sysaux01.dbf';
 alter database rename file '/u02/oradata/MAIN3/undotbs01.dbf' to '/u02/oradata/CL3/undotbs01.dbf';
 alter database rename file '/u02/oradata/MAIN3/users01.dbf' to '/u02/oradata/CL3/users01.dbf';
SQL> alter database rename file '/u02/oradata/MAIN3/system01.dbf' to '/u02/oradata/CL3/system01.dbf';
 alter database rename file '/u02/oradata/MAIN3/sysaux01.dbf' to '/u02/oradata/CL3/sysaux01.dbf';
 alter database rename file '/u02/oradata/MAIN3/undotbs01.dbf' to '/u02/oradata/CL3/undotbs01.dbf';
 alter database rename file '/u02/oradata/MAIN3/users01.dbf' to '/u02/oradata/CL3/users01.dbf';
Database altered.
SQL> Database altered.
SQL> Database altered.
SQL>
 Database altered.
SQL>
 SQL>
 SQL>
 SQL>
 SQL> select 'alter database rename file '''||member||''' to '''|| replace(member,'MAIN3','CL3')||''';' from v$logfile;
'ALTERDATABASERENAMEFILE'''||MEMBER||'''TO'''||REPLACE(MEMBER,'MAIN3','CL3')||''';'
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 alter database rename file '/u02/oradata/MAIN3/redo03.log' to '/u02/oradata/CL3/redo03.log';
 alter database rename file '/u02/oradata/MAIN3/redo02.log' to '/u02/oradata/CL3/redo02.log';
 alter database rename file '/u02/oradata/MAIN3/redo01.log' to '/u02/oradata/CL3/redo01.log';
SQL> alter database rename file '/u02/oradata/MAIN3/redo03.log' to '/u02/oradata/CL3/redo03.log';
 alter database rename file '/u02/oradata/MAIN3/redo02.log' to '/u02/oradata/CL3/redo02.log';
 alter database rename file '/u02/oradata/MAIN3/redo01.log' to '/u02/oradata/CL3/redo01.log';
Database altered.
SQL>
 Database altered.
SQL>
 Database altered.
SQL>
 SQL>
 SQL> select 'alter database rename file '''||name||''' to '''|| replace(name,'MAIN3','CL3')||''';' from v$tempfile;
'ALTERDATABASERENAMEFILE'''||NAME||'''TO'''||REPLACE(NAME,'MAIN3','CL3')||''';'
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 alter database rename file '/u02/oradata/MAIN3/temp01.dbf' to '/u02/oradata/CL3/temp01.dbf';
SQL> alter database rename file '/u02/oradata/MAIN3/temp01.dbf' to '/u02/oradata/CL3/temp01.dbf';
Database altered.
SQL> shutdown immediate
 ORA-01109: database not open
Database dismounted.
 ORACLE instance shut down.
 SQL> exit
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
# Tried to mount the database and to rename it and I got this:
 SQL> startup mount
 ORACLE instance started.
Total System Global Area 1252663296 bytes
 Fixed Size 2252824 bytes
 Variable Size 402657256 bytes
 Database Buffers 838860800 bytes
 Redo Buffers 8892416 bytes
 Database mounted.
 SQL> exit
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 [[email protected] dbs]$ nid target=sys dbname=CL3
DBNEWID: Release 11.2.0.4.0 - Production on Fri Nov 6 07:02:11 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
 Connected to database MAIN3 (DBID=2712190800)
NID-00135: There are 1 active threads
Change of database name failed during validation - database is intact.
 DBNEWID - Completed with validation errors.

# Tried again by opening the database and shutting down cleanly:
SQL> alter database open;
Database altered.
SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup mount
 ORACLE instance started.

Total System Global Area 1252663296 bytes
 Fixed Size 2252824 bytes
 Variable Size 402657256 bytes
 Database Buffers 838860800 bytes
 Redo Buffers 8892416 bytes
 Database mounted.
 SQL> exit
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 [[email protected] dbs]$ nid target=sys dbname=CL3
DBNEWID: Release 11.2.0.4.0 - Production on Fri Nov 6 07:03:25 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
 Connected to database MAIN3 (DBID=2712190800)
Connected to server version 11.2.0
Control Files in database:
 /u02/oradata/CL3/control01.ctl
 /u02/fast_recovery_area/CL3/control02.ctl
Change database ID and database name MAIN3 to CL3? (Y/[N]) => Y
Proceeding with operation
 Changing database ID from 2712190800 to 3512827391
 Changing database name from MAIN3 to CL3
 Control File /u02/oradata/CL3/control01.ctl - modified
 Control File /u02/fast_recovery_area/CL3/control02.ctl - modified
 Datafile /u02/oradata/CL3/system01.db - dbid changed, wrote new name
 Datafile /u02/oradata/CL3/sysaux01.db - dbid changed, wrote new name
 Datafile /u02/oradata/CL3/undotbs01.db - dbid changed, wrote new name
 Datafile /u02/oradata/CL3/users01.db - dbid changed, wrote new name
 Datafile /u02/oradata/CL3/temp01.db - dbid changed, wrote new name
 Control File /u02/oradata/CL3/control01.ctl - dbid changed, wrote new name
 Control File /u02/fast_recovery_area/CL3/control02.ctl - dbid changed, wrote new name
 Instance shut down
Database name changed to CL3.
 Modify parameter file and generate a new password file before restarting.
 Database ID for database CL3 changed to 3512827391.
 All previous backups and archived redo logs for this database are unusable.
 Database is not aware of previous backups and archived logs in Recovery Area.
 Database has been shutdown, open database with RESETLOGS option.
 Succesfully changed database name and ID.
 DBNEWID - Completed succesfully.
# Adapt the init file to fully reflect the name of the new database:
 [[email protected] dbs]$ vi initCL3.ora (do this on the prompt :%s/MAIN3/CL3/g)
# Create the spfile and mount the database:
[[email protected] dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 6 09:48:57 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup mount
 ORACLE instance started.

Total System Global Area 1252663296 bytes
 Fixed Size 2252824 bytes
 Variable Size 402657256 bytes
 Database Buffers 838860800 bytes
 Redo Buffers 8892416 bytes
 Database mounted.
 SQL> alter database open resetlogs;
Database altered.
SQL> exit

Leave Comment

Your email address will not be published. Required fields are marked *