How To Rename A Database - All Versions

BACKGROUND & OVERVIEW

This document provides a step-by-step guide to renaming a database.

ASSUMPTIONS & PRE-REQUISITES

This document expects and assumes the following:

  • The instructions are carried out by a qualified DBA, fully conversant with Oracle.
  • All necessary client software, e.g. Telnet and X-Server is available.
  • You have a working Oracle database up and running;
  • Your file layouts conform to the Optimal Flexible Architecture (OFA) structure and, your top level directories are named u01, u02, etc;
  • Your control files are named CTRL1.CTL, CTRL2.CTL, etc;
  • Your Oracle database parameters are all contained in a single INIT.ORA file, i.e. there are no included PFILE's
  • You use the Oracle user-id SYSTEM to run DBA commands and,
  • You use the Oracle user-id '/ AS SYSDBA' to stop and start your database;

For the purpose of this document we will use the following names and values :

  • The current database name will be OLDD
  • The new database name will be NEWDB
  • The system password for OLDDB will be SYSTEMPWD
  • The database $ORACLE_HOME definitions location will be /var/opt/oracle/oratab
  • The example commands will be for a Sun Solaris operating system.

STEP-BY-STEP GUIDE

1. Ensure there is a complete cold backup of OLDDB.

2. Set the oracle environment to OLDDB.

. oraenv
OLDDB

3. Change to the temporary directory.

cd /tmp

4. Log on to OLDDB as SYSTEM using SQL*Plus.

sqlplus system/systempwd

5. Configure the SQL*Plus session.

SET PAGESIZE O;
SET LINESIZE 2000;
SET TRIMSPOOL ON;
SPOOL mvflle.sql;

5. Create dynamic SQL script to rename database files and log off.

SELECT ‘ALTER DATABASE RENAME FILE '''||name ||''' TO '''||name||''';' FROM V$DATAFILE;
SELECT ‘ALTER DATABASE RENAME FILE '''||member||''' TO '''||member||''';' FROM V$LOGFILE;
SPOOL OFF;
EXIT;

6. Using the operating system editor, tidy mvfile.sql and change the second occurrence of OLDDB in each line that contains it to NEWDB.

vi /tmp/mvfile.sql

An example of an edited line would now be as follows :

ALTER DATABASE RENAME FILE '/u01/…/OLDDB/f1.dbf' TO '/u01/…/NEWDB/f1.dbf';

7. Log on to OLDDB using SQL*Plus.

sqlplus /nolog
CONNECT / AS SYSDBA;

8. Shutdown and log off OLDDB.

SHUTDOWN IMMEDIATE;
EXIT;

9. Perform the following step for each /u xx /oradata directory - where xx is a number :

cd /uxx/oradata
mv OLDDB NEWDB

10. Using the operating system editor, amend the control file and archive destination entries in the INIT.ORA file.

vi $ORACLE_BASE/admin/OLDDB/pfile/initOLDDB.ora

11. Log on to OLDDB using SQL*Plus and mount the database.

sqlplus /nolog
CONNECT / AS SYSDBA;
STARTUP MOUNT;

12. Use the mvfile.sql script created in Step (7) to alter the database file locations.

@/tmp/mvfile.sql

13. Open the database and log off OLDDB.

ALTER DATABASE OPEN;
EXIT;

14. Change OLDDB user dump directory and ensure it is empty.

cd $ORACLE_BASE/admin/OLDDB/udump
rm *

15. Log on to OLDDB as SYSTEM using SQL*Plus to create an Oracle generated alter database script and the log off.

sqlplus system/systempwd
ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
EXIT;

16. Rename the Oracle generated script and move it to the temporary directory.

mv *.trc /tmp/rename.sql

17. Using the operating system editor, tidy rename.sql to enable it rename the database.

vi /tmp/rename.sql

Delete the first 21 lines of comment.

Delete the line containing the 'RECOVER DATABASE' command.

Change the string
'CREATE … REUSE DATABASE OLDDB …'
in the second command line to
'CREATE … REUSE SET DATABASE NEWDB …'

18. Using the operating system editor, change the 'db_name' setting in the INIT.ORA file from OLDDB to NEWDB.

vi $ORACLE_BASE/admin/OLDDB/pfile/initOLDDB.ora

19. Log on to OLDDB using SQL*Plus and shutdown the database.

sqlplus /nolog
CONNECT / AS SYSDBA;
SHUTDOWN IMMEDIATE;

20. Use the rename.sql script to rename the database.

@/tmp/rename.sqI

21. Shutdown and log off the database.

SHUTDOWN IMMEDIATE;
EXIT;

22. Rename the database admin directory.

mv $ORACLE_BASE/admin/OLDDB $ORACLE_BASE/admin/NEWDB

23.Rename the INIT.ORA file.

mv $ORACLE_BASE/admin/NEWDB/pfile/initOLDDB.ora $ORACLE_BASE/admin/NEWDB/pfile/initNEWDB.ora

24. Using the operating system editor, amend the INIT.ORA file to change all the remaining references of OLDDB to NEWDB.

vi $ORACLE_BASE/admin/NEWDB/pfile/initNEWDB.ora

25. Using the operating system editor, amend the ORATAB file entry for OLDDB to NEWDB.

vi /var/opt/oracle/oratab

26. Set the oracle environment to NEWDB.

. oraenv
NEWDB

27. Tidy the $ORACLE_HOME/dbs directory and ensure there is a link to the renamed INIT.ORA file.

cd $ORACLE_HOME/dbs
rm *OLDDB*
ln —s $ORACLE_BASE/NEWDB/pfile/initNEWDB.ora initNEWDB.ora
orapwd file=orapwNEWDB password=PASSWORD

28. Using the operating system editor, amend the SQL*NET configuration files, changing all OLDDB entries to NEWDB.

vi $ORACLE_HOME/network/admin
vi *.ora

29. Restart the database listener to incorporate the NEWDB name.

lsnrctl stop
lsnrctl start

30. Log on to NEWDB using SQL*Plus, start the database and log off.

sqlplus /nolog
CONNECT / AS SYSDBA;
STARTUP;
EXIT;

31. Log on to NEWDB as SYSTEM using SQL*Plus over SQL*NET - this tests the SQL*NET configuration file changes.

sqlplus system/systempwd@NEWDB

32. Update the database GLOBAL NAME setting and log off.

ALTER DATABASE RENAME global_name TO NEWDB;
EXIT;

33. Fix the temporary tablespace

create temporary tablespace temp1 tempfile '/tmp/temp1.dbf' size 100m;
alter database default temporary tablespace temp1;
drop tablespace temp including contents and datafiles;
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u05/oradata/NEWDB/temp/d1/temp01.dbf' size 5000m;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u05/oradata/NEWDB/temp/d1/temp02.dbf' size 5000m;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u05/oradata/NEWDB/temp/d1/temp03.dbf' size 5000m;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u05/oradata/NEWDB/temp/d1/temp04.dbf' size 5000m;
alter database default temporary tablespace temp;
drop tablespace temp1 including contents and datafiles;

34. Ensure a complete cold backup of NEWDB is taken before allowing users to log on to the database.

35. The database rename is now complete.

© copyright 2001-2014 ABCdba.com | all rights reserved