Database cloning using cold backup DBACLASS

Cold database backup means while taking backup or doing cloning, we need to shutdown the source database. This method is usually used for test database when database is in noarchivelog mode.

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.Take backup of controlfile as trace:[SOURCE DB]

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

2. Note down the location of datafiles[SOURCE DB]

SQL> select file_name from dba_data_files;

FILE_NAME
---------------------------------------------------------------
/u03/oracle/oradata/SRCDB/system01.dbf
/u03/oracle/oradata/SRCDB/sysaux01.dbf
/u03/oracle/oradata/SRCDB/undotbs01.dbf
/u03/oracle/oradata/SRCDB/users01.dbf
/u03/oracle/oradata/SRCDB/CTLDATA_01.dbf
/u03/oracle/oradata/SRCDB/CTLIDX_01.dbf
/u03/oracle/oradata/SRCDB/catalog01.dbf
/u03/oracle/oradata/SRCDB/catalog_idx01.dbf
/u03/oracle/oradata/SRCDB/GGATE_01.dbf


SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------
/u03/oracle/oradata/SRCDB/temp01.dbf
/u03/oracle/oradata/SRCDB/catalog_temp01.dbf

3. Shutdown the database:[SOURCE DB]

SHUTDOWN IMMEDIATE;

4. Copy the datafiles and tempfiles to the target db server

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

5. Prepare the init file for target db:[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'

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

export ORACLE_SID=TRGDB

startup nomount pfile=initTRGDB.ora

7. 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;

8. Open the database in resetlog mode:

ALTER DATABASE OPEN RESETLOGS;

9. Add the temp files:

You can get this tempfile script in the end controlfile script .

-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oracle/oradata/SRCDB/temp01.dbf'
     SIZE 11529M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE CATALOG_TEMP ADD TEMPFILE '/u03/oracle/oradata/SRCDB/catalog_temp01.dbf'
     SIZE 524288000  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--

Replace the tempfile location as per target db and execute in target db:

ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oracle/oradata/TRGDB/temp01.dbf'
     SIZE 11529M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
	 
	 
ALTER TABLESPACE CATALOG_TEMP ADD TEMPFILE '/u03/oracle/oradata/TRGDB/catalog_temp01.dbf'
     SIZE 524288000  REUSE AUTOEXTEND OFF;

Database cloning using cold backup

1.Take backup of controlfile as trace:[SOURCE DB]

2. Note down the location of datafiles[SOURCE DB]

3. Shutdown the database:[SOURCE DB]

4. Copy the datafiles and tempfiles to the target db server

5. Prepare the init file for target db:[TARGET DB]

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

7. Re-recreate the controlfile [ TARGET DB ]

8. Open the database in resetlog mode:

9. Add the temp files:

About Admin

Leave a Reply Cancel reply

1.Take backup of controlfile as trace:[SOURCE DB]

2. Note down the location of datafiles[SOURCE DB]

3. Shutdown the database:[SOURCE DB]

4. Copy the datafiles and tempfiles to the target db server

5. Prepare the init file for target db:[TARGET DB]

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

7. Re-recreate the controlfile [ TARGET DB ]

8. Open the database in resetlog mode:

9. Add the temp files:

About Admin

Related Articles

Leave a Reply Cancel reply