Clone a DB and ORACLE_HOME:
1) perform a cold backup of source DB, and copy the following to the target machine :
a) oracle software
b) oracle home and datafiles (datafiles, controlfiles, redo log files)
c) configuration files, environment variables, system files
2) on the target machine
a) edit the init ora file and keep the db_name and db_unique_name as in source DB
3) start-up and mount the database with the source db name unchanged
SQL> startup pfile='/u01/DEV3/apps/proddb/11.2.0/dbs/initDEV3.ora nomount;
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2213136 bytes
Variable Size 3489663728 bytes
Database Buffers 1811939328 bytes
Redo Buffers 40914944 bytes
SQL> alter database mount;
Database altered.
SQL> exit
4) backup the control file to trace as 'file_name' in order to edit the path of the datafiles
SQL> alter database backup controlfile to trace as '/u01/DEV3/apps/proddb/11.2.0/dbs/ctl_bck.sql';
Database altered.
SQL> exit
5) edit the control file sql script in order for the datafiles and redo log files to point to the correct location on the target machine
6) bring the database to nomount in order to run the modified control file script
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/u01/DEV3/apps/proddb/11.2.0/dbs/initDEV3.ora' nomount;
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2213136 bytes
Variable Size 3489663728 bytes
Database Buffers 1811939328 bytes
Redo Buffers 40914944 bytes
SQL> @ctl_bck.sql
Control file created.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
SQL> exit
The error is raised since the control file script already contains the statement to mount the database.
7) Open the database in order for the file header to synchronize and your new controlfile to become current, then shut it down:
SQL> alter database open;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
8) Startup mount - in order to change the DB name
SQL> startup pfile='/u01/DEV3/apps/proddb/11.2.0/dbs/initDEV3.ora' nomount;
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2213136 bytes
Variable Size 3489663728 bytes
Database Buffers 1811939328 bytes
Redo Buffers 40914944 bytes
SQL> alter database mount;
Database altered.
9) Change the DBID, using 'nid'
applprod@gvaebsdb3:/u01/DEV3/apps/proddb/11.2.0/dbs $ nid target=system/<password> DBNAME=DEV3
DBNEWID: Release 11.2.0.1.0 - Production on Mon Oct 10 12:38:44 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database PROD (DBID=141797709)
Connected to server version 11.2.0
Control Files in database:
/u02/DEV3/dbf/DEV3/control01.ctl
/u01/DEV3/apps/proddb/flash_recovery_area/PROD/control02.ctl
Change database ID and database name PROD to DEV3? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 141797709 to 3627549972
Changing database name from PROD to DEV3
Control File /u02/DEV3/dbf/DEV3/control01.ctl - modified
Control File /u01/DEV3/apps/proddb/flash_recovery_area/PROD/control02.ctl - modified
Datafile /u02/DEV3/dbf/DEV3/system01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/sysaux01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_MEDIA03.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/users01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/example01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/UNDO01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_ARCHIVE01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_NOLOGGING01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_QUEUES01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_SEED01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_SUMMARY01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TOOLS01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/DBA01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/DISCOVERER01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/INTERIM.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_INTERFACE01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_INTERFACE02.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_MEDIA01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_MEDIA02.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX02.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX03.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX04.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX05.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX06.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX07.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA01.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA02.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA03.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA04.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA05.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA06.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA07.db - dbid changed, wrote new name
Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TEM_MV01.db - dbid changed, wrote new name
Control File /u02/DEV3/dbf/DEV3/control01.ctl - dbid changed, wrote new name
Control File /u01/DEV3/apps/proddb/flash_recovery_area/PROD/control02.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to DEV3.
Modify parameter file and generate a new password file before restarting.
Database ID for database DEV3 changed to 3627549972.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
10) Edit the init<SID>.ora file and change the db_name and db_unique_name to the new DB name
11) Start the db using the new init.ora file and check the status and the name of the db:
applprod@gvaebsdb3:/u01/DEV3/apps/proddb/11.2.0/dbs $ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 10 12:41:56 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/u01/DEV3/apps/proddb/11.2.0/dbs/initDEV3.ora'
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2213136 bytes
Variable Size 3489663728 bytes
Database Buffers 1811939328 bytes
Redo Buffers 40914944 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select name,db_unique_name from v$database;
NAME DB_UNIQUE_NAME
--------- ------------------------------
DEV3 DEV3
12) Create a spfile from pfile in order for the database to start using a spfile on the next startup
SQL> create spfile from pfile;
File created.
References: MOS: [ID 28433.1]
No comments:
Post a Comment