Database cloning using hot backup DBACLASS

In this article We will explain the steps for cloning a database using hot backup method without using RMAN utility. During hot backup, the source database will be up and running.

NOTE – SOURCE DATABASE SHOULD BE IN ARCHIVELOG MODE FOR HOT BACKUP

Note- Both the source and target db server should be on same platform and the target db version will be that of the source db.So make sure oracle binary is already installed on target db server.

Here we will clone a database SRCDB to a new server with name TRGDB

SOURCE DB – SRCDB
TARGET DB – TRGDB

1. Make sure source database in ARCHIVE LOG MODE;[SOURCE DB]

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archive/oradata/SRCDB/arch
Oldest online log sequence     278
Next log sequence to archive   281
Current log sequence           281

2. Create required directories in target db server[TARGET DB]

mkdir -p /u03/oracle/oradata/TRGDB
mkdir -p /archive/oradata/TRGDB/arch

3. Take backup of controlfile to trace[SOURCE DB]

alter database backup controlfile to trace as '/export/home/oracle/ctrl_bkokp.sql';

4. Put source database in hot backup mode:[SOURCE DB]

SQL>ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER DATABASE BEGIN BACKUP;

SQL> select distinct status from v$backup;

STATUS
------------------
ACTIVE

5. Copy the datafile and tempfiles and the backup controlfile sql to target DB server

scp /u03/oracle/oradata/SRCDB/*dbf oracle@targret-host.dbclass.com:/u03/oracle/oradata/TRGDB/
scp /export/home/oracle/ctrl_bkokp.sql oracle@targret-host.dbclass.com:/u03/oracle/oradata/TRGDB/

6. Once copy is done, remove db from hot backup[SOURCE DB]

ALTER DATABASE END BACKUP;

ALTER SYSTEM ARCHIVE LOG CURRENT;

6. Now prepare the target db initfile[TARGET DB]

We can copy the pfile from source db and the change the required parameters like DB_NAME and control_file,audit_file_dest,diag location.

cat initTRGDB.ora
 
*.audit_file_dest='/u01/app/oracle/admin/TRGDB/adump'
*.audit_trail='D
*.compatible='12.1.0.2.0'
*.control_files='/u03/oracle/oradata/TRGDB/control01.ctl','/u03/oracle/oradata/TRGDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='TRGDB'
*.diagnostic_dest='/u01/app/oracle/'*.event=''
*.open_cursors=300
*.pga_aggregate_target=524288000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sessions=1536
*.sga_max_size=7373586432
*.sga_target=7373586432
*.undo_tablespace='UNDOTBS1'

7. Start the database in nomount stage:[TARGET DB]

export ORACLE_SID=TRGDB

startup nomount pfile=initTRGDB.ora

8. Re-recreate the controlfile [ TARGET DB ]

This is an important steps in cloning process. In step 1 , We had taken backup of the controlfile as trace, We will use that sql file to re-create the controlfile.

SNIPPET FROM controlfile sql script:

 
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
 
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
 
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TCOMDB01" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/u03/oracle/oradata/SRCDB/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u03/oracle/oradata/SRCDB/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u03/oracle/oradata/SRCDB/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u03/oracle/oradata/SRCDB/system01.dbf',
  '/u03/oracle/oradata/SRCDB/CTLDATA_01.dbf',
  '/u03/oracle/oradata/SRCDB/sysaux01.dbf',
  '/u03/oracle/oradata/SRCDB/undotbs01.dbf',
  '/u03/oracle/oradata/SRCDB/CTLIDX_01.dbf',
  '/u03/oracle/oradata/SRCDB/users01.dbf',
  '/u03/oracle/oradata/SRCDB/catalog01.dbf',
  '/u03/oracle/oradata/SRCDB/catalog_idx01.dbf',
  '/u03/oracle/oradata/SRCDB/GGATE_01.dbf'
CHARACTER SET AL32UTF8
;

Lets modify this sql by replacing the datafile location of source with that of target and the new db name as TRGDB.

After changing it will look as below:

CREATE CONTROLFILE SET DATABASE "TRGDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/u01/oracle/oradata/TRGDB/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/oracle/oradata/TRGDB/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/oracle/oradata/TRGDB/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/oracle/oradata/TRGDB/system01.dbf',
  '/u01/oracle/oradata/TRGDB/CTLDATA_01.dbf',
  '/u01/oracle/oradata/TRGDB/sysaux01.dbf',
  '/u01/oracle/oradata/TRGDB/undotbs01.dbf',
  '/u01/oracle/oradata/TRGDB/CTLIDX_01.dbf',
  '/u01/oracle/oradata/TRGDB/users01.dbf',
  '/u01/oracle/oradata/TRGDB/catalog01.dbf',
  '/u01/oracle/oradata/TRGDB/catalog_idx01.dbf',
  '/u01/oracle/oradata/TRGDB/GGATE_01.dbf'
CHARACTER SET AL32UTF8
;

SET DATABASE:

Here we have use SET DATABASE, Because we are cloning the database in target with a new db name.

CREATE CONTROLFILE SET DATABASE “SRCDB” RESETLOGS FORCE LOGGING ARCHIVELOG

If you wish to keep the database same as source, then you can use the command REUSE DATABASE command.

Run the create controlfile statement:

SQL>CREATE CONTROLFILE SET DATABASE "SRCDB" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/u01/oracle/oradata/TRGDB/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/oracle/oradata/TRGDB/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/oracle/oradata/TRGDB/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/oracle/oradata/TRGDB/system01.dbf',
  '/u01/oracle/oradata/TRGDB/CTLDATA_01.dbf',
  '/u01/oracle/oradata/TRGDB/sysaux01.dbf',
  '/u01/oracle/oradata/TRGDB/undotbs01.dbf',
  '/u01/oracle/oradata/TRGDB/CTLIDX_01.dbf',
  '/u01/oracle/oradata/TRGDB/users01.dbf',
  '/u01/oracle/oradata/TRGDB/catalog01.dbf',
  '/u01/oracle/oradata/TRGDB/catalog_idx01.dbf',
  '/u01/oracle/oradata/TRGDB/GGATE_01.dbf'
CHARACTER SET AL32UTF8
;
/
 
controlfile created;

Now if you try to open the database in resetlog mode it will fail with below errors.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/u01/oracle/oradata/TRGDB/system01.dbf’

To fix it , we need to RECOVER DATABASE by apply the archives generated during which db was in hot backup mode.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;


SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;


ORA-00279: change 11491098201897 generated at 05/17/2016 16:36:27 needed for
thread 1
ORA-00289: suggestion : /archive/oradata/TRGDB/arch/1_5642_907410560.arc ------- > required archive
ORA-00280: change 11491098201897 for thread 1 is in sequence #5642

It needs the archives starting from 1_5642_907410560.arc to recover the database.
Copy the available archives from the sequence #5642 from source db to target db archive location.

scp /archive/oradata/SRCDB/arch/1_5642_907410560.arc oracle@targret-host.dbclass.com:/archive/oradata/TRGDB/arch
scp /archive/oradata/SRCDB/arch/1_5643_907410560.arc oracle@targret-host.dbclass.com:/archive/oradata/TRGDB/arch

Once copied run the recovery command again: and select AUTO

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 11491098201897 generated at 05/17/2016 16:36:27 needed for
thread 1
ORA-00289: suggestion : /archive/oradata/TRGDB/arch/1_5642_907410560.arc
ORA-00280: change 11491098201897 for thread 1 is in sequence #5642
 
 
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 11491098210561 generated at 05/17/2016 16:55:09 needed for
thread 1
ORA-00289: suggestion : /archive/oradata/TRGDB/arch/1_5643_907410560.arc
ORA-00280: change 11491098210561 for thread 1 is in sequence #5643
ORA-00278: log file '/archive/oradata/TRGDB/arch/1_5642_907410560.arc' no longer
needed for this recovery

Once all are applied, try to open the database in resetlog:

SQL> alter database open resetlogs;
 
Database altered.

While opening the database , if you are still getting error like datafile need more recovery, they you might have missed few archive logs to apply.

Database cloning using hot backup

1. Make sure source database in ARCHIVE LOG MODE;[SOURCE DB]

2. Create required directories in target db server[TARGET DB]

3. Take backup of controlfile to trace[SOURCE DB]

4. Put source database in hot backup mode:[SOURCE DB]

5. Copy the datafile and tempfiles and the backup controlfile sql to target DB server

6. Once copy is done, remove db from hot backup[SOURCE DB]

6. Now prepare the target db initfile[TARGET DB]

7. Start the database in nomount stage:[TARGET DB]

8. Re-recreate the controlfile [ TARGET DB ]

About Admin

Leave a Reply Cancel reply

1. Make sure source database in ARCHIVE LOG MODE;[SOURCE DB]

2. Create required directories in target db server[TARGET DB]

3. Take backup of controlfile to trace[SOURCE DB]

4. Put source database in hot backup mode:[SOURCE DB]

5. Copy the datafile and tempfiles and the backup controlfile sql to target DB server

6. Once copy is done, remove db from hot backup[SOURCE DB]

6. Now prepare the target db initfile[TARGET DB]

7. Start the database in nomount stage:[TARGET DB]

8. Re-recreate the controlfile [ TARGET DB ]

About Admin

Related Articles

Leave a Reply Cancel reply