How to rename Oracle Database? Recreate oracle controlfile: Steps to rename the database

advertisements

_____________________________________________________________________________________________________________________

Scenario: suppose we wanted to rename a database with name SOURCE to TARGET

Pre requisites

·         Take the backup of the database. This backup can be used to restore the database if something wrong happens to the rename procedure.

·         Prepare a new init.ora file for TARGET database with db_name as TARGET.

Steps for database renaming

• Take a control file backup to trace after connecting sys as sysdba. The trace file will get generated in user_dump_dest location and file will be with latest time stamp. Try ls –ltr |tail command to locate the trace file easily.

SQL> alter database backup controlfile to trace resetlogs;

Database altered.

• Edit the trace file to prepare the create control file script

a.       Remove all the lines upto the statement “STARTUP NOMOUNT” from trace file

b.      Remove all commented lines from the trace file

c.       Change the database name to TARGET

d.      Add SET keyword after the REUSE

e.      Rename the trace file to a file with .sql extension. (createctrl.sql)

The sample file looks like this.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE SET DATABASE "TARGET" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 256

    MAXINSTANCES 8

    MAXLOGHISTORY 14607

LOGFILE

  GROUP 1 '/data/oracle/oradata/SOURCE/redo1a.log'  SIZE 100M,

  GROUP 2 '/data/oracle/oradata/SOURCE/redo2a.log'  SIZE 100M,

  GROUP 3 '/data/oracle/oradata/SOURCE/redo3a.log'  SIZE 100M,

-- STANDBY LOGFILE

DATAFILE

  '/data/oracle/oradata/SOURCE/system01.dbf',

  '/data/oracle/oradata/SOURCE/sysaux01.dbf',

  '/data/oracle/oradata/SOURCE/users01.dbf',

  '/data/oracle/oradata/SOURCE/data-01.dbf',

  '/data/oracle/oradata/SOURCE/data-02.dbf',

  '/data/oracle/oradata/SOURCE/undo2_01.dbf',

CHARACTER SET WE8ISO8859P1;

• Shutdown the SOURCE database with normal or immediate option. Don’t use abort option
• Set the environment variables for TARGET database (export ORACLE_SID=TARGET)
• As mentioned in pre requisites create the new parameter file with initTARGET.ora in $ORACLE_HOME/dbs directory with db_name as TARGET
• Connect to sqlplus with sys as sysdba
• Run the createctrl.sql script in sql prompt to rename the database name to TARGET
• Open the database with RESETLOGS option. 

SQL> ALTER DATABASE OPEN RESETLOGS;

• Add temp file to the temp tablespace.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/data/oracle/oradata/SOURCE/temp01.dbf'

 SIZE 4500M REUSE AUTOEXTEND ON NEXT 524288000  MAXSIZE 32000M;

• Select db_name from v$database to confirm the changes.

SQL> select name from v$database;

• Update the tnsnames.ora, listener.ora files with new TARGET database details

Recommendation

• Take a full backup of new TARGET database immediately as the previous SOURCE databases are not valid.

Please comment your feedback.

_____________________________________________________________________________________________________________________

Website Stats