DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, April 26, 2017

To change the database name after database creation

Method 1:

1) Using NID utility

a) alter database close;
b) nid target=sys as sysdba dbname=clone(this will change the control files and headers of the datafiles with the correction of new name)

c)cd $ORACLE_HOME/dbs

d) cp initprod.ora initclone.ora

e)vi initclone.ora

find db_name parameter and change it to clone
f)vi /etc/oratab
change the name prod to clone

g) . oraenv
set the variable ORACLE_SID=clone

f) startup the database
SQL> startup (but the database open will error out since the database should open with resetlogs)

h) alter database open resetlogs;

i) select dbid, name from v$database;


Method 2:

By changing the control file

a)alter database backup control file to trace;

b)go to trace directory and edit the control file trace

c)In control file trace update database name prod to clone,

CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oracle/app/oracle/oradata/CLONE/redo1.log'  SIZE 10M BLOCKSIZE 512,
  GROUP 2 '/u01/oracle/app/oracle/oradata/CLONE/redo2.log'  SIZE 10M BLOCKSIZE 512,
  GROUP 3 '/u01/oracle/app/oracle/oradata/CLONE/redo3.log'  SIZE 10M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/oracle/app/oracle/oradata/CLONE/system.dbf',
  '/u01/oracle/app/oracle/oradata/CLONE/user04.dbf',
  '/u01/oracle/app/oracle/oradata/CLONE/sysaux.dbf',
  '/u01/oracle/app/oracle/oradata/CLONE/undo.dbf',
CHARACTER SET UTF8; 

d)shut down the database

e)Backup the existing control file.

f)vi initclone.ora file and change the dbname prod to clone and change the control file location also.

g)startup nomount

h)execute the control file trace which we modified earlier(this will create a new control file in the location with the  new dbname )
@control.sql

i) alter database open resetlogs;

j) select database_name  from v$database

No comments: