Cloning a Standalone Database without RMAN

This post covers how to clone/duplicate a database the old-fashioned way, without RMAN, using manual scripts, etc.

Backup the Source Database

In my example, I backup a 10.2.0.4 database called 'cubs' on a host called 'rac1', using a series of customizable scripts. Feel free to download and customize these 3 scripts to suit your needs.

Once the scripts have been edited, run the run_hotbkp.bsh script.

The hot backup copies the datafiles, etc. to the backup location of your choosing.

ls -lart /u01/app/oracle/orabackup/cubs
total 779072
drwxr-xr-x  4 oracle dba      4096 Sep 21 12:15 ../
-rw-r-----  1 oracle dba   5251072 Sep 21 14:27 users01.dbf
-rw-r-----  1 oracle dba  26222592 Sep 21 14:27 undotbs01.dbf
-rw-r-----  1 oracle dba 503324672 Sep 21 14:27 system01.dbf
-rw-r-----  1 oracle dba 262152192 Sep 21 14:28 sysaux01.dbf
-rw-r--r--  1 oracle dba      5602 Sep 21 14:28 CUBS.ctlbkp

Prepare the Target Host

In my example, I will be cloning the 'cubs' database to the 'rac2' host, renaming it to 'colts.'

Create any necessary directories

$ mkdir -p $ORACLE_BASE/admin/colts/adump
$ mkdir -p $ORACLE_BASE/admin/colts/bdump
$ mkdir -p $ORACLE_BASE/admin/colts/cdump
$ mkdir -p $ORACLE_BASE/admin/colts/udump
$ mkdir -p $ORACLE_BASE/oradata/colts
$ mkdir -p $ORACLE_BASE/oradata/colts/arch

Update /etc/oratab

echo "colts:/u01/app/oracle/product/10.2:N" >> /etc/oratab

Create a Passwordfile for the Target Database

$ export ORACLE_SID=colts
$ . oraenv
$ orapwd file=$ORACLE_HOME/dbs/orapwcolts password=password;

Create a PFILE for the Target Database

Create a PFILE on the Source host, transfer it to the Target, and modify it as appropriate.

$ export ORACLE_SID=cubs
$ . oraenv

$ sqlplus "/ as sysdba"
SQL> create pfile='/tmp/initcolts.ora' from spfile;

$ scp /tmp/initcolts.ora oracle@rac2:/u01/app/oracle/product/10.2/dbs/.

Edit the necessary values; for example:

*.audit_file_dest='/u01/app/oracle/admin/colts/adump'
*.background_dump_dest='/u01/app/oracle/admin/colts/bdump'
*.control_files='/u01/app/oracle/oradata/colts/control01.ctl','/u01/app/oracle/oradata/colts/control02.ctl','/u01/app/oracle/oradata/colts/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/colts/cdump'
*.db_file_name_convert='/cubs/','/colts/'
*.db_name='colts'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=coltsXDB)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/colts/arch'
*.log_archive_format='colts_%t_%s_%r.dbf'
*.user_dump_dest='/u01/app/oracle/admin/colts/udump'

Transfer the Hot Backup

Move the Hot Backup to the desired location on the Target Host.

$ scp /u01/app/oracle/orabackup/cubs/* oracle@rac2:/u01/app/oracle/oradata/colts/.
oracle@rac2's password:
CUBS.ctlbkp                                                               100% 5602     5.5KB/s   00:00
sysaux01.dbf                                                              100%  250MB  27.8MB/s   00:09
system01.dbf                                                              100%  480MB  25.3MB/s   00:19
undotbs01.dbf                                                             100%   25MB  25.0MB/s   00:01
users01.dbf                                                               100% 5128KB   5.0MB/s   00:00

Transfer Needed Archive Logs to the Target

$ scp -p /u02/oracle/arch/* oracle@rac2:/u01/app/oracle/oradata/colts/arch/.

Edit the Controlfile Trace

$ vi /u01/app/oracle/oradata/colts/CUBS.ctlbkp

Edit the text-based controlfile script until it resembles the following

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "COLTS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/colts/redo01.log'  SIZE 50M REUSE,
  GROUP 2 '/u01/app/oracle/oradata/colts/redo02.log'  SIZE 50M REUSE,
  GROUP 3 '/u01/app/oracle/oradata/colts/redo03.log'  SIZE 50M REUSE
DATAFILE
  '/u01/app/oracle/oradata/colts/system01.dbf',
  '/u01/app/oracle/oradata/colts/undotbs01.dbf',
  '/u01/app/oracle/oradata/colts/sysaux01.dbf',
  '/u01/app/oracle/oradata/colts/users01.dbf'
CHARACTER SET AL32UTF8
;
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;

I normally recover until cancel because user intervention is often required. This was the case for my recovery, because a datafile was added after the backup.

Run the modified script

$ sqlplus "/ as sysdba"
SQL> @CUBS.ctlbkp

ORACLE instance started.

Total System Global Area  176160768 bytes
Fixed Size                  1266416 bytes
Variable Size              62917904 bytes
Database Buffers          104857600 bytes
Redo Buffers                7118848 bytes

Control file created.

ORA-00279: change 485386 generated at 09/21/2008 15:46:08 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_42_666013624.dbf
ORA-00280: change 485386 for thread 1 is in sequence #42

Respond to the recovery:

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/cubs/tools01.dbf'

ORA-01112: media recovery not started

Remedy the file situtaion via the following commands

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/colts/system01.dbf
/u01/app/oracle/oradata/colts/undotbs01.dbf
/u01/app/oracle/oradata/colts/sysaux01.dbf
/u01/app/oracle/oradata/colts/users01.dbf
/u01/app/oracle/product/10.2/dbs/UNNAMED00005

SQL> alter database create datafile '/u01/app/oracle/product/10.2/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/colts/tools01.dbf';

Database altered.

Resume the recovery

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 485384 generated at 09/21/2008 15:46:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_42_666013624.dbf
ORA-00280: change 485384 for thread 1 is in sequence #42


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 485411 generated at 09/21/2008 15:46:20 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_43_666013624.dbf
ORA-00280: change 485411 for thread 1 is in sequence #43
ORA-00278: log file
'/u01/app/oracle/oradata/colts/arch/cubs_1_42_666013624.dbf' no longer needed
for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 485415 generated at 09/21/2008 15:46:25 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_44_666013624.dbf
ORA-00280: change 485415 for thread 1 is in sequence #44
ORA-00278: log file
'/u01/app/oracle/oradata/colts/arch/cubs_1_43_666013624.dbf' no longer needed
for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

Open the Database RESETLOGS:

SQL> alter database open resetlogs;

Complete Post-Cloning Steps

Add back tempfiles

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/colts/temp01.dbf' size 250M;

Change the DBID (Database ID) of the Database via nid

SQL> shutdown immediate;
SQL> startup mount;

$ nid target=/

DBNEWID: Release 10.2.0.4.0 - Production on Sun Sep 21 16:55:20 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to database COLTS (DBID=2153372661)

Connected to server version 10.2.0

Control Files in database:
    /u01/app/oracle/oradata/colts/control01.ctl
    /u01/app/oracle/oradata/colts/control02.ctl
    /u01/app/oracle/oradata/colts/control03.ctl

Change database ID of database COLTS? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2153372661 to 2820171320
    Control File /u01/app/oracle/oradata/colts/control01.ctl - modified
    Control File /u01/app/oracle/oradata/colts/control02.ctl - modified
    Control File /u01/app/oracle/oradata/colts/control03.ctl - modified
    Datafile /u01/app/oracle/oradata/colts/system01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/colts/undotbs01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/colts/sysaux01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/colts/users01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/colts/tools01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/colts/temp01.dbf - dbid changed
    Control File /u01/app/oracle/oradata/colts/control01.ctl - dbid changed
    Control File /u01/app/oracle/oradata/colts/control02.ctl - dbid changed
    Control File /u01/app/oracle/oradata/colts/control03.ctl - dbid changed
    Instance shut down

Database ID for database COLTS changed to 2820171320.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

SQL> startup mount;
SQL> alter database open resetlogs;

Optionally, create a SPFILE

SQL> create spfile from pfile;

Change the log_archive_format parameter if necessary

SQL> alter system set log_archive_format='colts_%t_%s_%r.dbf' scope=spfile;
SQL> shutdown immediate;
SQL> startup

Labels: Backup and Recovery