How to Rename a Database Prior to 9.2

Background & Overview

This document provides a step by step guide to renaming a database. For a database whose version is 9.2 or higher, the following guide is more appropriate.

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
  • Where commands continue on the next line, the continuation characters will be —>
  • The example commands will be for a Sun Solaris operating system.

Useful Information

None Currently.

Known Issues

None Currently.

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.


This work is reproduced under licence and is the copyright of abcdba.com


Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License