This article presents Renaming Database Name
So, Sometime we need to change Database name, Actually its uncommon issue however again its importat for us. There are two methots
Lets begin step by step.
1. First methot Renaming Database Name
First lets look at our database_name will be renamed.
SQL> select name from v$database;NAME
---------
ORCL
1.Close your database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2.Make database mounted
SQL> startup mount
ORACLE instance started.
Total System Global Area 1121554432 bytes
Fixed Size 1336008 bytes
Variable Size 889195832 bytes
Database Buffers 218103808 bytes
Redo Buffers 12918784 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Write new dbname
[oracle@mhm /]$ nid target=sys/Oracle_4u@ORCL dbname=TEST_ORCL
DBNEWID: Release 11.2.0.1.0 - Production on Sun Oct 23 22:34:28 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL (DBID=1272787738)
Connected to server version 11.2.0
Control Files in database:
+DATA/orcl/controlfile/current.260.745007003
+FRA/orcl/controlfile/current.256.745007003
Change database ID and database name ORCL to TEST_ORCL? (Y/[N]) => Y
Go to SQL*plus with SYS User
And set new database name
SQL> ALTER SYSTEM SET DB_NAME=test_orcl SCOPE=SPFILE;
Cloese database
SQL> shu abort;
Now create new password
[oracle@mhm /]$ orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/pwdtest_orcl.ora
password=password entries=10
Create pfile from spifle
SQL>sqlplus / as sysdba
SQL>create pfile=’inittest_orcl.ora’ from spfile;
Open the initnew.ora and change the DBNAME as new
Again cloese the database.
SQL> Shutdown immediate;
Now export new name
oracle@mhm /]$ ORACLE_SID=test_orcl
oracle@mhm /]$ export ORACLE_SID
Now for TNS and Listener to be know new database Lsnrtl reloaded
Lsnrctl reload
And now open Database at Resetlogs;
SQL> startup mount;
SQL> alter database open resetlogs;
You finished so check your new database name.
SQL> select name from v$database;
NAME
---------
TEST_ORCL
2.Second methot renaming database
---------
ORCL
Now backup control file as a trace.
SQL> Alter database backup controlfile to trace;
Save the trace as script .
[oracle@orcl ~]$ mv orcl_ora_2884.trc back_cntrl_script.sql
Now open the script and delete until.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “ORCL”
RESETLOGS NOARCHIVELOG
Now change “REUSE” to “SET” and write new database name.
CREATE CONTROLFILE SET DATABASE
“TEST_ORCL” RESETLOGS NOARCHIVELOG
And delete the
RECOVER DATABASE USING BACKUP
CONTROLFILE;
Your script should be like following
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "TEST_ORCL"
RESETLOGS NOARCHIVELOG
MAXLOGFILES
16
MAXLOGMEMBERS 3
MAXDATAFILES
100
MAXINSTANCES
8
MAXLOGHISTORY 292
LOGFILE
GROUP 1
'/oracle/ora11g/oradata/redo01.log' SIZE
50M BLOCKSIZE 512,
GROUP 2
'/oracle/ora11g/ oradata/redo02.log'
SIZE 50M BLOCKSIZE 512,
GROUP 3
'/oracle/ora11g/ oradata/redo03.log'
SIZE 50M BLOCKSIZE 512,
DATAFILE
'/oracle/ora11g/oradata/system01.dbf',
'/oracle/ora11g/oradata/sysaux01.dbf',
'/oracle/ora11g/oradata/undotbs01.dbf',
'/oracle/ora11g/oradata/users01.dbf',
'/oracle/ora11g/oradata/example01.dbf',
'/oracle/ora11g/oradata/myts01.dbf'
CHARACTER SET WE8ISO8859P9
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE
'/oracle/ora11g/oradata/temp01.dbf'
SIZE
441450496 REUSE AUTOEXTEND ON NEXT
655360 MAXSIZE 32767M;
Shutdown your database;
SQL> shutdown immediate;
Backup your init<SID>.ora and open it, change db_name, instance_name parameters as a new
database_name save it.
Now run the script (back_cntrl_script.sql)
SQL> @/oracle/ back_cntrl_script.sql
You finished it Now check your new
database name
