Two Best Practice
First let’s see how to change the DB name using manual assumption (By recreating the control file)
ASSUMPTİON 1: RE-CREATING CONTROLFILE
STEP1:- CREATE CONTROLFILE CREATATION SCRIPT.
[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 24 16:29:40 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database backup controlfile to trace;
Database altered.
STEP2:- EDIT/MODIFY CONTROLFILE CREATION SCRIPT.
[oracle@oralinux trace]$ ls -lrt
total 16
-rw-r—–. 1 oracle oinstall 1.3K May 24 11:26 COPYDATABASE_dbrm_41909.trc
-rw-r—–. 1 oracle oinstall 73 May 24 14:27 COPYDATABASE_ora_42007.trm
-rw-r—–. 1 oracle oinstall 6.8K May 24 14:27 COPYDATABASE_ora_42007.trc
-rw-r—–. 1 oracle oinstall 494M May 24 14:27 alert_COPYDATABASE.log
[oracle@oralinux trace]$ cp COPYDATABASE_ora_42007.trc control_COPYDATABASE.sql
[oracle@oralinux trace]$ vi control_COPYDATABASE.sql
This will generate script in above location
[oracle@dbaserver1 ~ ]$ vi control_COPYDATABASE.sql
Here change the database name and replace word REUSE with SET and make sure it is having RESETLOGS
CREATE CONTROLFILE SET DATABASE “COPYDATABASEN” RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/data/oracle/app/oracle/oradata/COPYDATABASE/redo1.log’ SIZE 10M BLOCKSIZE 512,
GROUP 2 ‘/data/oracle/app/oracle/oradata/COPYDATABASE/redo2.log’ SIZE 10M BLOCKSIZE 512,
GROUP 3 ‘/data/oracle/app/oracle/oradata/COPYDATABASE/redo3.log’ SIZE 10M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/data/oracle/app/oracle/oradata/COPYDATABASE/system.dbf’,
‘/data/oracle/app/oracle/oradata/COPYDATABASE/user04.dbf’,
‘/data/oracle/app/oracle/oradata/COPYDATABASE/sysaux.dbf’,
‘/data/oracle/app/oracle/oradata/COPYDATABASE/undo.dbf’,
‘/data/oracle/app/oracle/oradata/COPYDATABASE/test1_tmp.dbf’,
‘/data/oracle/app/oracle/oradata/COPYDATABASE/test_corrupt.dbf’
CHARACTER SET UTF8;
STEP3: CHANGE DB_NAME PARAMETER IN SPFILE/PFILE.
SQL> sho parameter control
NAME TYPE VALUE
control_file_record_keep_time integer 7
control_files string /data/oracle/app/oracle/oradat
a/COPYDATABASE/control01.ctl, /data
/oracle/app/oracle/oradata/CLO
NEDB/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> shutdown immediate;
ORA-01013: user requested cancel of current operation
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 601272320 bytes
Fixed Size 2230712 bytes
Variable Size 276825672 bytes
Database Buffers 310378496 bytes
Redo Buffers 11837440 bytes
SQL> alter system set db_name=COPYDATABASEN scope=spfile;
System altered.
STEP4:- NOW REMOVE OR MOVE OLD CONTROL FILES.
Before removing the old control file to make a backup of all control files.
[oracle@NVMBD1BZY150D00 dbs]$cd
/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@NVMBD1BZY150D00 COPYDATABASE]$ mv control01.ctl control01.ctl_bkp
[oracle@NVMBD1BZY150D00 COPYDATABASE]$ mv control02.ctl control02.ctl_bkp
STEP5: SHUT DOWN THE DATABASE AND STARTUP IN NOMOUNT MODE TO REFLECT DB_NAME PARAMETER CHANGES.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 14 16:34:38 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 601272320 bytes
Fixed Size 2230712 bytes
Variable Size 276825672 bytes
Database Buffers 310378496 bytes
Redo Buffers 11837440 bytes
STEP6: NOW EXECUTE NEWLY CREATED CONTROLFILE SCRIPT.
SQL> @control_COPYDATABASE.sql
Control file created.
STEP7: OPEN DATABASE WITH RESETLOGS OPTION.
SQL> alter database open resetlogs;
Database altered.
SQL> select database_name from v$database;
COPYDATABASEN
ASSUMPTİON 2: DBNEWID UTILITY
THE DB NEWID is a database utility that can change the internal database identifier (DBID) and database name (DBNAME) for an operational database.
The DB NEWID utility solves this problem by letting you change any of the following:
• Only the DBID of a database
• Only the DBNAME of a database
• Both the DBNAME and DBID of a database
I prefer to change both DBNAME and DBID at the same time as a best practice during creation of test environments.
Step-1. We will change both db_name to NEWNAMEDB and dbid belongs to NEWNAMEDBd database.
Check db_id and db_name for new environment before operation.
SQL> select dbid,name from v$database;
DBID NAME
1536075532;; COPYDATABASEN
Step-2. Startup instance with mount state.
export ORACLE_SID=COPYDATABASE
sqlplus / as sysdba
shutdown immediate;
startup mount;
Step-3. Execute nid command and check the log file “/tmp/nid.log”:
oracle@NVMBD1BZY150D00 dbs]$ nid target=/ dbname=NEWNAMEDB logfile=/tmp/nid.log
[oracle@NVMBD1BZY150D00 dbs]$ cat /tmp/nid.log
DBNEWID: Release 11.2.0.3.0 – Production on Thu May 25 12:07:16 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database COPYDATABASEN (DBID=1536075532)
Connected to server version 11.2.0
Control Files in database:
/data/oracle/app/oracle/oradata/COPYDATABASE/control01.ctl /data/oracle/app/oracle/oradata/COPYDATABASE/control02.ctl
Changing database ID from 1536075532 to 2136075214
Changing database name from COPYDATABASEN to NEWNAMEDB
Control File /data/oracle/app/oracle/oradata/COPYDATABASE/control01.ctl – modified Control File /data/oracle/app/oracle/oradata/COPYDATABASE/control02.ctl – modified Datafile /data/oracle/app/oracle/oradata/COPYDATABASE/system.db – dbid changed, wrote new name Datafile /data/oracle/app/oracle/oradata/COPYDATABASE/user04.db – dbid changed, wrote new name Datafile /data/oracle/app/oracle/oradata/COPYDATABASE/sysaux.db – dbid changed, wrote new name Datafile /data/oracle/app/oracle/oradata/COPYDATABASE/undo.db – dbid changed, wrote new name Datafile /data/oracle/app/oracle/oradata/COPYDATABASE/test1_tmp.db – dbid changed, wrote new name Datafile /data/oracle/app/oracle/oradata/COPYDATABASE/test_corrupt.db – dbid changed, wrote new name Control File /data/oracle/app/oracle/oradata/COPYDATABASE/control01.ctl – dbid changed, wrote new name Control File /data/oracle/app/oracle/oradata/COPYDATABASE/control02.ctl – dbid changed, wrote new name Instance shut down
Database name changed to NEWNAMEDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database NEWNAMEDB changed to 2136075214.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.
After DBNEWID completed successful, instance has been also closed automatically.
Step-4. Startup instance with nomount option and change the db_name to NEWNAMEDB.
Then shutdown and startup mount instance again for activate new db_name. At last, open database with resetlogs option.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 601272320 bytes
Fixed Size 2230712 bytes
Variable Size 276825672 bytes
Database Buffers 310378496 bytes
Redo Buffers 11837440 bytes
SQL> show parameter db_name
NAME TYPE VALUE
db_name string NEWNAMEDB
SQL> alter system set db_name=testdb5 scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1663012104 bytes
Fixed Size 744712 bytes
Variable Size 805306368 bytes
Database Buffers 855638016 bytes
Redo Buffers 1323008 bytes
SQL> show parameter db_name
NAME TYPE VALUE
db_name string NEWNAMEDB
SQL> alter database mount;
Database altered.
SQL> alter database open resetlogs;
Database altered.
Step-5. Control the value of dbid and name of the new database.
SQL> select dbid, name from v$database;
DBID NAME
2136075214 NEWNAMEDB
Step-6. You should create new password file for the new environment if you need
cd $ORACLE_HOME/dbs
orapwd file=orapwNEWNAMEDB password=NEWNAMEDB entries=3
Also you can check out my other post : Oracle Migrating Table Columns from Longs to CLOB
Congrats, have a nice day.
Tags: