Forgot your password?    
+ Reply to Thread
Results 1 to 3 of 3

Thread: Clone a Database Using a Hot Backup

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    426

    Clone a Database Using a Hot Backup

    A) Take a hot backup of the SOURCE Instance

    To quickly take a hot backup , Refer to : Script-to-create-Script-to-take-Hot-Backup-of-Database

    B) Prepare the TARGET

    SHUTDOWN the target DATABASE and LISTENER
    Take backup and Remove the TARGET ORACLE_HOME and database

    C) Use tar to copy the ORACLE_HOME and the database (from the hot backup) to the TARGET system to replace the target system.

    On the Source : Create the tar files

    tar -cvf /tmp/ohome.tar <ORACLE_HOME location>
    tar -cvf /tmp/dbbkp.tar <Database Hot backup location>


    Copy or scp the tar files

    scp /tmp/ohome.tar oracle@192.168.2.131:/tmp/ohome.tar
    scp /tmp/dbbkp.tar oracle@192.168.2.131:/tmp/dbbkp.tar


    On the Target Intance :

    create the directories for the ORACLE_HOME and datafiles if it does not exist
    Extract the tar files

    cd <ORACLE_HOME location>
    tar -xvf /tmp/ohome.tar

    cd <datafile location>
    tar -xvf /tmp/dbbkp.tar

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    426
    ON the TARGET

    1 Login as target user user and Verify .profile and *.env file under $ORACLE_HOME
    Make sure that the environment is set for the Target System.

    cd $ORACLE_HOME
    pwd

    2 Check all oracle related file systems for user and group ownership

    cd <TARGET file systems>
    ls -l

    Check user and group ownership : It should be proper


    3 Modify the entries in the tnsnames.ora and listener.ora to have the Listner for the target instance

    eg : My listener.ora - where target is dev
    ==================================

    dev =
    (ADDRESS_LIST =
    (ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCdev))
    (ADDRESS= (PROTOCOL= TCP)(Host= dbalounge)(Port= 1525))
    )

    SID_LIST_dev =
    (SID_LIST =
    (SID_DESC =
    (ORACLE_HOME= /u03/oracle/devdb/10.2.0)
    (SID_NAME = dev)
    )
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /u03/oracle/devdb/10.2.0)
    (PROGRAM = extproc)
    )
    )

    STARTUP_WAIT_TIME_dev = 0
    CONNECT_TIMEOUT_dev = 10
    TRACE_LEVEL_dev = OFF

    LOG_DIRECTORY_dev = /u03/oracle/devdb/10.2.0/network/admin
    LOG_FILE_dev = dev
    TRACE_DIRECTORY_dev = /u03/oracle/devdb/10.2.0/network/admin
    TRACE_FILE_dev = dev
    ADMIN_RESTRICTIONS_dev = OFF


    eg : My TNS entry - where target is dev
    ==================================

    dev=
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=DBALOUNGE.com)(PORT=1 525))
    (CONNECT_DATA=
    (SERVICE_NAME=dev)
    (INSTANCE_NAME=dev)
    )
    )


    4 In config.c, make sure the entries are pointing to target .

    cd $ORACLE_HOME/rdbms/lib
    ls -ltr config*
    vi config.c
    Take care of Group. It should be the group the target user belongs to :
    #define SS_DBA_GRP "dbTARGET"
    #define SS_OPER_GRP "dbTARGET"



    5 Move config.o

    cd $ORACLE_HOME/rdbms/lib
    mv config.o config.o.old_18jul10


    6 If this is RAC to NON-RAC Refresh, relink Oracle with rac_off

    cd $ORACLE_HOME/rdbms/lib
    make -f ins_rdbms.mk rac_off

    If this step did not fail with fatal errors, proceed with :
    make -f ins_rdbms.mk ioracle


    7 Relink all the executables.

    cd $ORACLE_HOME/bin
    ./relink all

    8 Verify the Timestamp
    ls -l $ORACLE_HOME/bin/oracle



    9 Verify that you can run sqlplus “/ as sysdba” without prompting for any password.

    sqlplus "/ as sysdba"
    connected to idle instance


    10 Set the init.ora paramters and create the destination directory location
    To see the directories to be created and corresponding parameters to be modified, Refer to :
    Create-a-Database-Manually-using-Create-Database-Script

    11 Prepare the Control file script from the trace bacup taken on production.

    vi TARGET_control.sql

    a)  Consider the resetlog portion of this script . Delete the noresetlogs section of the script.

    b) Old Entry - CREATE CONTROLFILE REUSE DATABASE "SOURCE" NORESETLOGS ARCHIVELOG FORCE LOGGING

     New Entry - CREATE CONTROLFILE REUSE SET DATABASE "SOURCE" RESETLOGS ARCHIVELOG

    c) Change the location of Datafiles to point to the Target,
    Replace all occurances of : SOURCE replaced by TARGET
    Replace all occurances of : SOURCE replaced by TARGET

    d) Remove the TEMP file entries ( keep a safe copy, for later use).
    Keep a safe backup of Temporary creation part and redo logfile creation statements.
    After this , you can remove all lines after characterset and semicolon.

    e) Inspect the control file script
    The number of Datafiles should match that in the source.
    select count(*) from v$datafile at the source.

    Similarily, number of copied Datafiles at the Target should match the definition in the script.

    Example – of what you will be left with in TARGET_control.sql
    STARTUP NOMOUNT;
    CREATE CONTROLFILE REUSE SET DATABASE "TARGET" RESETLOGS ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 5
    MAXDATAFILES 1024
    MAXINSTANCES 8
    MAXLOGHISTORY 1361
    LOGFILE
    GROUP 1 (
    '/TARGET/..../log01a.dbf',
    '/TARGET/..../log01b.dbf'
    ) SIZE 200M,
    GROUP 2 (
    '/TARGET/..../log02a.dbf',
    '/TARGET/..../log02b.dbf'
    ) SIZE 200M,
    GROUP 3 (
    '/TARGET/..../log03a.dbf',
    '/TARGET/..../log03b.dbf'
    ) SIZE 200M,
    ….
    DATAFILE
    '/TARGET/..../system01.dbf',
    '/TARGET/..../system02.dbf',
    ……….
    ……….. (representing other datafiles)

    CHARACTER SET UTF8
    ;


    12 Create the Controlfile

    $sqlplus "/ as sysdba"
    @TARGET_control.sql

    13 See archive log mode

    SQL>ARCHIVE LOG LIST
    Should show automatic archiving “Enabled” and “Archivelog” Mode

    14 Recover the database

    SQL> recover database using backup controlfile until cancel;

    Enter the archive file location, when prompted.

    Use the following query on SOURCE to get the list of archives needed to be recovered -
    select THREAD#, SEQUENCE# from v$log_history where &1 between FIRST_CHANGE# and NEXT_CHANGE#;

    Here – For the value for $1 to be given will come from the sequence returned by the command – “recover database using backup controlfile until cancel;” which you have given on TARGET


    15 Open the DB with resetlogs
    SQL> alter database open resetlogs;

    When the DB opens, check for any missing files or files needed for recovery

    SQL> select * from v$recover_file;
    SQL> select * from v$datafile where name like '%MISS%';
    Should not return any rows for these queries.

    16 Shutdown the Database

    17 Check for the Default Temporary file –

    select * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

    Confirm that this TEMP.
    If the default is not TEMP,
    SQL>alter database default temporary tablespace TEMP

    18. Create the Temp datafiles , using the commands taken from the control file.

    ALTER TABLESPACE TEMP ADD TEMPFILE __________

    Example - The commands will be similar to below
    ALTER TABLESPACE TEMP ADD TEMPFILE '/TARGET/..../temp20.dbf'
    SIZE 134217728 REUSE AUTOEXTEND ON NEXT 52428800 MAXSIZE 1800M;
    ALTER TABLESPACE TEMP ADD TEMPFILE '/TARGET/..../temp19.dbf'
    SIZE 134217728 REUSE AUTOEXTEND ON NEXT 52428800 MAXSIZE 1800M;
    ALTER TABLESPACE TEMP ADD TEMPFILE '/TARGET/..../temp18.dbf'
    SIZE 134217728 REUSE AUTOEXTEND ON NEXT 52428800 MAXSIZE 1800M;
    ALTER TABLESPACE TEMP ADD TEMPFILE '/TARGET/..../temp17.dbf'
    SIZE 134217728 REUSE AUTOEXTEND ON NEXT 52428800 MAXSIZE 1800M;

    Verify dba_temp_files, for all the temp file entries added to TEMP

    SQL> select file_name from dba_temp_files;

    19 Update global_name

    select * from global_name;
    update global_name set global_name='TARGET.ORACLEOUTSOURCING.COM';
    commit;

    SQL> select * from global_name;

    GLOBAL_NAME
    --------------------------------------------------------------------------------
    TARGET

  3. #3
    Oracle Administrator
    Join Date
    Dec 2011
    Posts
    92

    Different Approach As Per Oracle Documentation.

    Assumptions:

    Hot backups of the production server are there, including binary backup of controlfiles.

    Production Database Name: orcl
    Cloned Database Name: pub

    --Create pfile from the production servers spfile.

    SQL> create pfile='/u01/initpub.ora' from spfile;

    -- Make appropriate folder structures.

    [oracle@canada ~]$ mkdir -p /u01/app/oracle/admin/pub/adump
    [oracle@canada ~]$ mkdir -p /u01/app/oracle/oradata/pub
    [oracle@canada ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/pub
    [oracle@canada ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/PUB/archivelog


    -- Edit the initpub.ora file and change the location of all the files for
    -- the "pub" database.

    Also add the following parameters to the pfile:

    DB_UNIQUE_NAME=pub

    -- above parameter is necessary if database is created on the same machine.

    DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)

    -- above parameter is necessary if database is created on the same machine,
    -- or if the directory structure is different.

    LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)

    -- above parameter is mandatory.

    LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/app/oracle/flash_recovery_area/PUB/archivelog/'

    Sample Pfile


    pub.__db_cache_size=62914560
    pub.__java_pool_size=4194304
    pub.__large_pool_size=4194304
    pub.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    pub.__pga_aggregate_target=113246208
    pub.__sga_target=150994944
    pub.__shared_io_pool_size=0
    pub.__shared_pool_size=75497472
    pub.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/pub/adump'
    *.audit_trail='db'
    *.compatible='11.2.0.0.0'
    *.control_files='/u01/app/oracle/oradata/pub/control01.ctl',
    '/u01/app/oracle/flash_recovery_area/pub/control02.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='orcl'
    *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
    *.db_recovery_file_dest_size=4039114752
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=pubXDB)'
    *.log_archive_format='%t_%s_%r.dbf'
    *.memory_target=262144000
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.undo_tablespace='UNDOTBS1'
    DB_UNIQUE_NAME=pub
    DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)
    LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)
    LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/app/oracle/flash_recovery_area/PUB/archivelog/'

    --Copy the backup datafiles, archived log files, and control files to folders created
    --earlier.


    [oracle@canada ~]$ export ORACLE_SID=pub
    [oracle@canada ~]$ sqlplus

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 16 19:02:27 2012

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

    Enter user-name: / as sysdba
    Connected to an idle instance.

    SQL> startup nomount pfile='/u01/initpub.ora';
    ORACLE instance started.

    Total System Global Area 263639040 bytes
    Fixed Size 1335892 bytes
    Variable Size 197135788 bytes
    Database Buffers 62914560 bytes
    Redo Buffers 2252800 bytes

    SQL> alter database mount clone database;

    Database altered.

    SQL> set pagesize 100
    SQL> set linesize 130
    SQL> select name,file#,status from v$datafile;

    NAME FILE# STATUS
    ------------------------------ ---------- -------
    /u01/app/oracle/oradata/pub/sy 1 SYSOFF
    stem01.dbf

    /u01/app/oracle/oradata/pub/sy 2 OFFLINE
    saux01.dbf

    /u01/app/oracle/oradata/pub/un 3 OFFLINE
    dotbs01.dbf

    /u01/app/oracle/oradata/pub/us 4 OFFLINE
    ers01.dbf


    SQL> alter database datafile 1 online;

    Database altered.

    SQL> alter database datafile 2 online;

    Database altered.

    SQL> alter database datafile 3 online;

    Database altered.

    SQL> alter database datafile 4 online;

    Database altered.


    SQL> select name,file#,status from v$datafile;

    NAME FILE# STATUS
    -------------------------------------------------- ---------- -------
    /u01/app/oracle/oradata/pub/system01.dbf 1 SYSTEM
    /u01/app/oracle/oradata/pub/sysaux01.dbf 2 ONLINE
    /u01/app/oracle/oradata/pub/undotbs01.dbf 3 ONLINE
    /u01/app/oracle/oradata/pub/users01.dbf 4 ONLINE


    SQL>recover database until cancel using backup controlfile;

    --manually apply all the suggested archived log files and finish
    --the incomplete recovery.

    SQL>alter database open resetlogs;

+ Reply to Thread

Similar Threads

  1. Dynamic Script to take Hot Backup of Oracle Database
    By Hemant in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 10
    Last Post: 03-14-2012, 11:40 PM
  2. Oracle Database Manual Backup and Recovery.
    By ajaychandi in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 01-24-2012, 06:51 PM
  3. What happens when we put either database or tablespace in begin backup mode
    By nemat in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 01-13-2012, 02:16 PM
  4. Replies: 0
    Last Post: 01-01-2012, 04:59 PM
  5. Clone Database using RMAN Duplicate Database Feature
    By Hemant in forum Database Installation,Upgrades,Migration,Patching and Cloning
    Replies: 0
    Last Post: 11-23-2011, 11:38 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics