Pavan DBA's Blog

The DBA Knowledge Store

Database cloning using cold backup

Posted by Pavan DBA on November 13, 2009


The following are the steps for performing database cloning using cold backup

Assumptions : You are using Linux flavour OS and following same directory structure

1. Take the cold backup of source database

2. Take controlfile trace and pfile or spfile (that was using by the source database)

3. Install Oracle software on another machine (choose “Install only” option in OUI). Don’t create any database

4. Copy all the files (including trace file and pfile or spfile) from source server to target server either using FTP or rcp

5. Place pfile or spfile in “dbs” directory on target

6. Copy the remaining files to their respective locations (If any directories are missing, do create them)

7. Open bash_profile file and set ORACLE_HOME and ORACLE_SID

8. Connect as sysdba and Startup the database

2 Responses to “Database cloning using cold backup”

  1. chandu said

    I posted similar topic but the oracle version is 11g using RMAN DUPLICATE command:

    http://chandu208.blogspot.com/2011/12/11g-rman-cloning-using-duplicate.html

  2. jagat mohanty said

    Steps for DEV refresh from PRD

    (Try to log in all your actions wherever possible to either using script command at unix level or spooling all your actions wherever you use sqlplus)

    • Export DEV with no data. This would be useful as will explained in later steps.

    Following are the steps.

    1) Preserve a copy of current pfile .
    2) cp $ORACLE_HOME/dbs/initDEV.ora $ORACLE_HOME/dbs/initDEV.ora.start

    3) Create pfile from spfile.
    a. This will create a file initDEV.ora in the $ORACLE_HOME/dbs directory.
    b. Preserve this as initDEV.ora initDEV.ora.text
    4) Create a backup control file trace.
    5) Alter database backup controlfile to trace.
    6) ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/oracle/app/dev/dba/restore/dev/dev_ctl_file_backup.txt’;
    7) Save existing user details and dblinks in as /oracle/app/dev/dba/restore/dev/SaveUsers.sql
    8) Shutdown both the instances(If in RAC) of dev.
    cd /oracle/app/crs/product/10.2.0/bin
    ./srvctl stop database -d dev
    9) Use either srvctl or shutdown each instance individually. Try to do a clean shutdown with immediate option.
    10) Drop the datafiles,controlfiles and logfiles using ASMCMD.

    11) Change initdev.ora (created in Step 2) to have cluster_databases=FALSE
    12) CLUSTER_DATABASES=FALSE
    13) DB_FILE_NAME_CONVERT=’+PRD_DATAFILES_DG’, ’+ DEV_DATAFILES_DG’.
    14) LOG_FILE_NAME_CONVERT=’+PRD_DATAFILES_DG’,’+DEV_DATAFILES_DG’
    15) Controlfiles=’+DEV_DATAFILES_DG’,’+DEV_DATAFILES_DG’
    16) Startup DEV in non-cluster mode using STARTUP NOMOUNT.
    17) Check PRD backup logs on prod server and for the most recent backup completion times an SCN’s.
    18) On dev server change the script /oracle/app/dev/dba/restore/dev/ rest_PRD_to_dev.rcv as below.

    19) Based on the completion time of PRD backups , as found in step 15, change the SET UNTIL TIME.

    20) If you prefer to have SET UNTIL SEQUENCE THREAD or SET UNTIL SCN it is your call. The whole idea is to make sure that your restore is going to use the most recent backup of PRD.

    21) If you have any issues or problems check steps(15,19)
    22) Run the restore script in the background.
    23) nohup ./ rest_PRD_to_DEV.sh > rest_PRD_to_DEV.sh.log 2>&1 & This will take almost 10-11 to complete. As soon as you start check the restore logs created in the directory /oracle/app/dev/dba/restorelogs. Make sure that the log shows that the restore is being done from the latest backup. If not kill the job, check your restore_PRD_to_DEV.rcv and set the UNTIL TIME correctly.

    24) After the restore is done RMAN opens the database with resetlogs option. Also control_files parameter in initDEV.ora would be updated with actual controlfile names.

    25) Just in case notedown the control file names. You can query v$controlfile parameter.
    26) If the controlfiles are not updated in initDEV.ora, update initDEV.ora with actual controlfile names.

    27) By recycling the instance change the archivelog mode of the instance from archivelog to nonarchivelog mode.

    ALTER DATABASE NOARCHIVELOG;

    28) Make following changes to initDEV.ora and restart the instance.
    29) CLUSTER_DATABASES=TRUE.

    30) Now create spfile as below and recycle the instance as below.
    31) Create spfile=’+DEV_DATAFILES_DG/DEV/spfileDEV.ora’ from pfile;
    32) Shutdown the instance.
    33) Cp $ORACLE_HOME/dbs/initDEV.ora.start initDEV.ora. Note this will have just one line spfile=+DEV_DATAFILES_DG/DEV/spfileDEV.ora.

    34) Use srvctl to startup DEV
    cd /oracle/app/crs/product/10.2.0/bin
    35) ./srvctl start database -d DEV
    36) Drop all db_links that pointing production DBs
    37) Create Dev dblinks (step 6 – CreateDblinks.sql )
    38) Reset all users (step 6 CreateUser.sql and AlterUser.sql )
    39) Look at the change log in our shared directory, re-apply those changes to DEV, that haven’t yet been moved to production.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: