1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Clone a Database Using Hot Backup

Discussion in 'Server Administration and Options' started by simply_dba, Oct 31, 2008.

  1. simply_dba

    simply_dba Forum Advisor

    Likes Received:
    Trophy Points:
    Kolkata, India

    This document gives you a detailed flow of how to backup the database in a 24x7 operation and use it for cloning a Development or Test database. By using the System switching of Log files option, it reduces the amount of archive logs to be applied and reduces the time of recovery (the goal).Most sites running Oracle databases need the latest production databases to be cloned for the development and test teams to work on. These sites need to be refreshed periodically with the production database and Application. Most DBAs use Cold Backup procedures to clone a database. Although cold backups are the most common backup procedures for database cloning, there may be situations where no downtime is available. Hot Backups can be performed when the database is up and in Archive log mode.


    This document explains, in details, about how to perform user managed cloning of a active production database into test/development environment. It does not take into account the process of cloning using the Recovery Manager (RMAN) utility provided by Oracle.

    Definitions and Assumptions

    Source Database: The active production instance/database to be cloned.
    Target Database: The cloned database, typically a test/development database.

    Cloning steps

    Switching Logs​

    We need to make sure that all the present online logs are switched and archived, so that the time to do the media recovery to the cloned database is as small as possible.
    Query the number of log groups that exists in the database and switch as many times as there are log groups.

    Code (Text):
    Select group#,archived,status from v$log;

    1           YES       INACTIVE
    2           NO         CURRENT
    We can see from the example above, we need to switch logs to archive the online logs.

    Code (Text):
    alter system switch logfile;
    System altered.
    Make sure that no log group shows a “STALE” status.

    Destination Directory structure creation​

    Create the destination directory structures as you would like to move the data files and redo log files. Also create the dump directory to hold the target database dumps (udmp, bdump cdump, adump etc).
    Let’s note down the last archive log file from the archive log destination directory or from the alert log and then do another

    Alter system switch logfile;

    This would give us the current log that got archived by switching and also a
    checkpoint occurs. Remember that switching leads to check pointing and check pointing not necessarily need lead to a log switch. So the data files all get consistent since the current scn’s are stamped. We do the switch to the number of log groups we have. That means the first archive log file that was switched might be your full file size and the subsequent ones would have lesser size depending on the data.

    Copy source database files to the target​

    The next step is to copy all the data files of the source database to the destination database directories.
    Before starting to copy the files of a particular tablespace, we need to put that tablespace into hot backup mode.
    By putting a tablespace into hot backup mode, we will ensure two things.
    1) The first time a block is changed in a data file that is in hot backup mode, the entire block is written to the redo log files, not just the changed bytes. Normally only the changed bytes (a redo vector) are written. In hot backup mode, the entire block is logged the first time. This is because you can get into a situation where the process copying the data file and DBWR are working on the same block simultaneously.
    2) The data file headers which contain the SCN of the last completed checkpoint are not updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file.
    To limit the effect of this additional logging, you should ensure you only place one tablespace at a time in backup mode and bring the tablespace out of backup mode as soon as you have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible.
    List out all the tablespaces present in the system and their corresponding data files.

    Code (Text):
    select tablespace_name,status,contents from dba_tablespaces

    UNDOTBS1            ONLINE UNDO
    TEMP                   ONLINE TEMPORARY
    USERS                  ONLINE PERMANENT
    FLIRT                   ONLINE PERMANENT
    6 rows selected.

    select tablespace_name,file_name from dba_data_files order by tablespace_name;
    5 rows selected
    Now put each tablespace into hot backup mode, copy it’s corresponding data files to the destination directory, and then bring out the tablespace from the hot backup mode, one tablespace at a time.
    Alter tablespace FLIRT begin backup;
    Alter tablespace FLIRT end backup;
    It is better to do the above exercise using a script, so that manual intervention, and hence, chance of committing an error, is also less.
    Following is a script that you can use to generate a hot backup copy script from any database
  2. simply_dba

    simply_dba Forum Advisor

    Likes Received:
    Trophy Points:
    Kolkata, India
    Switch logs again​

    Once again, once the copy is complete, switch log groups the required number of times. In this way we need to copy over only those archive files starting with the ones that we noted down when we started the log switch to the ones we have when we did the log switch after the backup. These would be very few and hence the recovery would also be faster when we
    apply these archive files. We would also have a set of archive files that we are sure of and consistency can thus be assured.
    Backup the Control File.​

    Now we need a control file creation script for the target database. The best way to achieve this is to backup the source control file to trace with the following command.
    Code (Text):
    alter database backup controlf ile to trace;
    Database altered.
    Copy the appropriate archive logs and online redo logs to the target location.
    Prepare Initialization Parameter File​

    Make appropriate changes to the initialization parameter file of the target db to reflect the new file locations and new database. Create appropriate target dump locations (adump, bdump, udump etc).
    Creating Service and control files in target​

    Set the sid to the target database.
    If the target host is windows, then you will need to create database service control with the oradim command. Use the following syntax to create the service.
    Oradim –new –sid <target db name> -intpwd <sys password> -startmode manual
    start a sqlplus session as sysdba and start the instance in nomount mode with target init file.
    Open the control file trace backup. Towards the end, you’ll find controlfile creation statement with resetlogs option .Following is an example:
    Change the first line of the statement to look like this :
    Also change the file locations of the database mentioned in the statement to reflect the target file location. After all the changes to the statement, it should look like following :
    Go the sqlplus session and execute the above (changed) controlfile creation statement to create the controlfiles. Controlfile should be created without any errors.
    Apply Media Recovery​

    Now you need to apply media recovery to make the database consistent. Execute the following statement.
    You may need to apply the online redo logs to make the media recovery complete.
    After media recovery is complete open the database in restelogs mode.
    Code (Text):
    Alter database open resetlogs;
    Add tempfiles to the temporary tablespaces.
    Code (Text):
    Cloning is complete.
  3. Arju

    Arju Forum Expert

    Likes Received:
    Trophy Points:
    Good example. It is good to know cloning database in this way does not change the DBID of the cloned database in order with the source database.

    There are also other various ways to clone a database.

    1)Copy a full database cold backup and paste it in the same directory in the destination database and then create the service.
    However you can change the path. Then you have to create the control file again. This is user managed cloning.
    This does not change DBID.

    2)Take a Rman full database backup and restore it in the destination database. This also does not change DBID.

    3)RMAN DUPLICATE database. Clone a database using DUPLICATE DATABASE command. In this way you have to connect to RMAN in both source database and the database to be cloned. In this way DBID is changed of the database.
  4. simply_dba

    simply_dba Forum Advisor

    Likes Received:
    Trophy Points:
    Kolkata, India
    Good input Arju, Thanks !