Clone a database from cold backup.

Procedure:


STEPS:

1 Backup the Source Database

Before attempting to clone the source database, it is necessary to perform a full backup of the database. This will ensure that no data will be lost if the copying of the database is unsuccessful.

2 Backup the Source Database's Control File to a Trace File

Connect to the source database PROD SQL*Plus using an account that has the SYSDBA, SYSOPER, or DBA role granted to it. Perform a backup of the control file to trace using the following command:

SQL> alter database backup controlfile to trace;

Examples:

On Windows Operating System:

C:\> set ORACLE_SID=prod
C:\> sqlplus “sys/password (please put sys password here) as sysdba”
SQL> alter database backup controlfile to trace;

On Unix Operating System:

$Export ORACLE_SID= prod
$ Sqlplus “sys/change_on_install as sysdba”
SQL> alter database backup controlfile to trace;

Note: This will create a text file that we'll use later. The text file has the format ora_.trc or _ora_.trc or ora.trc depending on the Operating System , where is the number of the process that created this file. The trace file is created in the location specified by the initialization parameter USER_DUMP_DEST. To find the value of this parameter, type the following query:


SQL> select name, value
From v$parameter
Where name='user_dump_dest';

Note: Go to the directory selected above, identify the most recent .trc file and rename it to c1.sql.

A listing of an example control file trace follows

Dump file e:\oracle\admin\prod\udump\ORA03552.TRC
Mon Aug 26 15:04:15 2002
ORACLE V8.1.7.2.1 - Production vsnsta=0
vsnsql=f vsnxtr=3
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.8.1 - Production
With the Partitioning option
Server Release 9.2.0.8.1 - Production
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Instance name: PROD
Redo thread mounted by this instance: 1
Oracle process number: 11
Windows thread id: 3552, image: ORACLE.EXE
*** SESSION ID:(12.554) 2002-08-26 15:04:15.796
*** 2002-08-26 15:04:15.796
# The following commands will create a new control file and use it
# to open the database.
# The contents of online logs will be lost and all backups will
# be invalidated. Use this only if online logs are damaged.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 96
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'E:\ORADATA\PROD\REDO01.LOG' SIZE 10M,
GROUP 2 'E:\ORADATA\PROD\REDO02.LOG' SIZE 10M
DATAFILE
'E:\ORADATA\PROD\SYSTEM01.DBF',
'E:\ORADATA\PROD\RBS01.DBF',
'E:\ORADATA\PROD\TEMP01.DBF',
'E:\ORADATA\PROD\USERS01.DBF',
'E:\ORADATA\PROD\TOOLS01.DBF',
'E:\ORADATA\PROD\OCL_TEMP1.DBF',
'E:\ORADATA\PROD\OCL_GLIB.DBF',
'E:\ORADATA\PROD\OCL_GLIB_IDX.DBF',
'E:\ORADATA\PROD\OCL_LI.DBF',
'E:\ORADATA\PROD\OCL_LI_IDX.DBF',
'E:\ORADATA\PROD\OCL_DCMQ.DBF',
'E:\ORADATA\PROD\OCL_DCMQ_IDX.DBF',
'E:\ORADATA\PROD\OCL_DCD.DBF',
'E:\ORADATA\PROD\OCL_DCD_IDX.DX1\OCL_RESP.DBF',
'E:\ORADATA\PROD\OCL_RESP_IDX.DBF',
'E:\ORADATA\PROD\OCL_DISC.DBF',
CHARACTER SET WE8ISO8859P1
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# No tempfile entries found to add.

You need to modify the trace file script for the new database “TEST” by doing the following:


Line #s 1 through 19: Delete the header information and the comments in the script file.
Line # 20: Modify the startup command statement to include the inittest.ora parameter file as follows:

STARTUP NOMOUNT PFILE=e:\oracle\admin\TEST\pfile\inittest.ora

Line # 21: Modify the control file command statement to change the database name to the new database, as follows:

CREATE CONTROLFILE SET DATABASE "test" RESETLOGS NOARCHIVELOG

Line #s 28 and 29: Modify the filenames to point to the names of the redo log files for test.

Line #s 31 through 62: Modify the names of the data files to point to the correct names of the data files for test.

Line #s 65, 66, and 67: Remove the lines containing the RECOVER DATABASE command and its associated comments.

Line #s 68, 70: Delete the lines # 68 and #70

The following shows the modified file listing:

STARTUP NOMOUNT PFILE=e:\oracle\admin\TEST\pfile\initTEST.ora
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 96
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'E:\ORADATA\TEST\REDO01.LOG' SIZE 10M,
GROUP 2 'E:\ORADATA\TEST\REDO02.LOG' SIZE 10M

DATAFILE
'E:\ORADATA\TEST\SYSTEM01.DBF',
'E:\ORADATA\TEST\RBS01.DBF',
'E:\ORADATA\TEST\TEMP01.DBF',
'E:\ORADATA\TEST\USERS01.DBF',
'E:\ORADATA\TEST\TOOLS01.DBF',
'E:\ORADATA\TEST\OCL_TEMP1.DBF',
'E:\ORADATA\TEST\OCL_APP.DBF',
'E:\ORADATA\TEST\OCL_APP_IDX.DBF',
'E:\ORADATA\TEST\OCL_DEF.DBF',
'E:\ORADATA\TEST\OCL_DEF_IDX.DBF',
'E:\ORADATA\TEST\OCL_GLIB.DBF',
'E:\ORADATA\TEST\OCL_GLIB_IDX.DBF',
'E:\ORADATA\TEST\OCL_LI.DBF',
'E:\ORADATA\TEST\OCL_LI_IDX.DBF',
CHARACTER SET WE8ISO8859P1
;

3 Make a List of All of your Data and Redo Log Files

SQL> select name from v$datafile;
SQL> select member from v$logfile;

4 Perform a Clean Shutdown of the Source Database

SQL> shutdown immediate;

You must connect as a user (i.e. SYS) who has privileges (i.e. SYSDBA) to shut down the database.

5 Make a Copy of the Database

Copy all data and redo log files noted in step “Make a List of All of your Data and Redo Log Files” to their new location making sure you preserve ownership and permissions. Do not copy any control file.

Note: You must first create the directories for the target database TEST, to save the administrative files, the data and log files. Issue the following commands:


On Windows Operating System:

Create administrative directories:

e:>mkdir e:\oracle\admin\TEST
e:>mkdir e:\oracle\admin\TEST\bdump
e:>mkdir e:\oracle\admin\TEST\udump
e:>mkdir e:\oracle\admin\TEST\cdump
e:>mkdir e:\oracle\admin\TEST\pfile





Create data file directory:

e:>mkdir e:\oradata\TEST

Copy Database Files

C:> copy e:\oradata\PROD\*.dbf e:\oradata\TEST\
C:> copy e:\oradata\PROD\*.log e:\oradata\TEST\
C:> copy e:\oracle\admin\PROD\pfile\initPROD.ora e:\oracle\admin\TEST\pfile\initTEST.ora

On Unix Operating System:

Create administrative directories:

$mkdir /u01/app/oracle/admin/TEST
$ mkdir /u01/app/oracle/admin/TEST/bdump
$mkdir /u01/app/oracle/admin/TEST/udump
$ mkdir /u01/app/oracle/admin/TEST/cdump
$ mkdir /u01/app/oracle/admin/TEST/pfile

Create data file directory:

$mkdir /u01/oradata/TEST

Copy Database Files

$cp –p /u01/oradata/PROD/*.dbf /u01/oradata/TEST/
$cp –p /u01/oradata/PROD/*.log /u01/oradata/TEST/
$cp –p /u01/app/oracle/admin/PROD/pfile/initPROD.ora /u01/app/oracle/admin/TEST/pfile/initTEST.ora

Create symbolic link for the parameter file:

$ln –s /u01/app/oracle/admin/TEST/pfile/initTEST.ora $ORACLE_HOME/dbs/initTEST.ora

Where $ORACLE_HOME is the full path of the RDBMS home installation for PROD (i.e. /u01/app/oracle/product/9.2.0.80)

6 Modify the Parameter File for the Cloned Database

Using a text editor, revise the initTEST.ora parameter file to reflect the new values for the target database.
On Windows Operating System:
e:\oracle\admin\TEST\pfile\initTEST.ora
On Unix Operating System:
/u01/app/oracle/admin/TEST/pfile/initTEST.ora
This will involve changing the paths indicated in parameters such as CONTROL_FILES, USER_DUMP_DEST, BACKGROUND_DUMP_DEST, and any other parameter that specifies a path/file location.

A listing of an example initTEST.ora follows.

db_name = PROD
db_domain = us.oracle.com
Control_files = ("e:\oradata\PROD\control01_PROD.ctl", "e:\oradata\PROD\control02_PROD.ctl")
db_block_buffers = 10000
shared_pool_size = 25000000
java_pool_size = 20000000
Processes = 50
log_buffer = 163840
audit_trail = false
timed_statistics = true
log_archive_start = true
log_archive_dest_1 = "location=e:\oracle\admin\PROD\arch"
log_archive_format = %t_%s.dbf
If using private rollback segments, place lines of the following
global_names = true
oracle_trace_enable = true
Background_dump_dest = e:\oracle\admin\PROD\bdump
Core_dump_dest = e:\oracle\admin\PROD\cdump
user_dump_dest = e:\oracle\admin\PROD\udump
db_block_size = 8192
remote_login_passwordfile = exclusive
job_queue_processes = 2
compatible = 8.1.7.2.0
open_cursors=200
db_files=96
nls_date_format=dd-mon-rrrr
sort_area_size=3000000

You need to modify the initTEST.ora file for the new database TEST by doing the following:

Replace PROD with TEST
db_name = TEST
db_domain = us.oracle.com
Control_files = ("e:\oradata\TEST\control01.ctl", "e:\oradata\TEST\control02.ctl")
db_block_buffers = 10000
shared_pool_size = 25000000
java_pool_size = 20000000
Processes = 50
log_buffer = 163840
audit_trail = false
timed_statistics = true
log_archive_start = true
log_archive_dest_1 = "location=e:\oracle\admin\TEST\arch"
log_archive_format = %t_%s.dbf
If using private rollback segments, place lines of the following
global_names = true
oracle_trace_enable = true
Background_dump_dest = e:\oracle\admin\TEST\bdump
Core_dump_dest = e:\oracle\admin\TEST\cdump
user_dump_dest = e:\oracle\admin\TEST\udump
db_block_size = 8192
remote_login_passwordfile = exclusive
job_queue_processes = 2
compatible = 8.1.7.2.0
open_cursors=200
db_files=96
nls_date_format=dd-mon-yyyy
sort_area_size=3000000


7 Modify the Trace Output Script File

You must modify the c1.sql trace script file from Step Backup the Source Database's Control File to a Trace File

The modified controlfile will look like this:

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 96
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'E:\ORADATA\TEST\REDO01.LOG' SIZE 10M,
GROUP 2 'E:\ORADATA\TEST\REDO02.LOG' SIZE 10M

DATAFILE
'E:\ORADATA\TEST\SYSTEM01.DBF',
'E:\ORADATA\TEST\RBS01.DBF',
'E:\ORADATA\TEST\TEMP01.DBF',
'E:\ORADATA\TEST\USERS01.DBF',
'E:\ORADATA\TEST\TOOLS01.DBF',
'E:\ORADATA\TEST\OCL_TEMP1.DBF',
'E:\ORADATA\TEST\OCL_APP.DBF',
'E:\ORADATA\TEST\OCL_APP_IDX.DBF',
'E:\ORADATA\TEST\OCL_DEF.DBF',
'E:\ORADATA\TEST\OCL_DEF_IDX.DBF',
'E:\ORADATA\TEST\OCL_GLIB.DBF',
'E:\ORADATA\TEST\OCL_GLIB_IDX.DBF',
'E:\ORADATA\TEST\OCL_LI.DBF',
'E:\ORADATA\TEST\OCL_LI_IDX.DBF',
CHARACTER SET WE8ISO8859P1
;


8 Create the Oracle Service for the New Database (Windows Operating System Only)

C:\> set ORACLE_SID=TEST

Check whether the initialization parameter REMOTE_LOGIN_PASSWORDFILE in the initTEST.ora file is set to exclusive or shared. If it is set, then the original database PROD was using a password file. Use the oradim command to create a new Oracle SID service for the new database TEST with a password file:

C:\> oradim -new -sid TEST –intpwd sys -startmode auto


9 Update Network Files and Restart the Listener

Edit the listener.ora and tnsnames.ora files to mention the newly created instance TEST. Stop and restart the listener.

For Unix only, add an entry for the TEST database in the oratab file. In Tru64 and HP-UX , oratab is located in the /etc directory. In Solaris, the oratab file is located in the /var/opt/oracle directory. The line will be similar to this:

TEST:/u01/app/oracle/product/9.2.0.8.0:N

NOTE: The instance name is case sensitive. Make sure to match the instance name with the one in the listener.ora, tnsnames.ora and oratab files.


10 Recreate the Control File, and Open the Database

Start the instance and recreate the control file:

On Unix Operating System:

Set the Oracle environment to the new database TEST in the Operating System. Define the following environment variables:

Example:

$export ORACLE_SID=TEST

$cd $ORACLE_HOME/dbs
$orapwd file=orapwTEST password= entries=5

Connect to the target database’s idle instance via SQL*Plus using an account that has the SYSDBA, SYSOPER, or DBA role granted to it.

Example:

$ Sqlplus “sys/sys as sysdba”

SQL> STARTUP NOMOUNT pfile=’/u01/app/oracle/admin/TEST/pfile/initTEST.ora’

SQL> @c1.sql
SQL>alter database open resetlogs;
SQL>create spfile from pfile=’/u01/app/oracle/admin/TEST/pfile/initTEST.ora’
SQL>shutdown immediate;
SQL>startup;


On Windows Operating System:


Example:

C:\> set ORACLE_SID=TEST

Connect to the target database’s idle instance via SQL*Plus using an account that has the SYSDBA, SYSOPER, or DBA role granted to it.

Example:

C:\> sqlplus “sys/sys as sysdba”

SQL> STARTUP NOMOUNT PFILE=’e:\oracle\admin\TEST\pfile\initTEST.ora’
SQL> @c1.sql
SQL>alter database open resetlogs;
SQL>create spfile from pfile=’e:\oracle\admin\TEST\pfile\initTEST.ora’
SQL>shutdown immediate;
SQL>startup;


11 Change the Global Name of the Target Database

SQL> alter database rename global_name to TEST.us.oracle.com;