Monday 3 June 2013

How to do Super fast Database Copying/Cloning


Super fast Database Copying/Cloning:

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another.  It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

    STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
    NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 240
    MAXINSTANCES 1
    MAXLOGHISTORY 113
    LOGFILE
    GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
    '/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
    GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
    '/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
    DATAFILE
    '/u01/oradata/oldlsq/system01.dbf',
    '/u01/oradata/oldlsq/mydatabase.dbf'
    ;
    # Recovery is required if any of the datafiles are restored
    # backups, or if the last shutdown was not normal or immediate.
    RECOVER DATABASE
    # Database can now be opened normally.
    ALTER DATABASE OPEN;
     

STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

    rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
    rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
    rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
    rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
     

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

    CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

New:

    CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax

    # Recovery is required if any of the datafiles are restored
    # backups, or if the last shutdown was not normal or immediate.
    RECOVER DATABASE
    # Database can now be opened normally.
    ALTER DATABASE OPEN;
     

STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

Old:

    DATAFILE
    '/u01/oradata/oldlsq/system01.dbf',
    '/u01/oradata/oldlsq/mydatabase.dbf'

New:

    DATAFILE
    '/u01/oradata/newlsq/system01.dbf',
    '/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

    cd $DBA/admin
    mkdir newlsq
    cd newlsq
    mkdir bdump
    mkdir udump
    mkdir cdump
    mkdir pfile


STEP 8: Copy-over the old init.ora file

    rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9: Start the new database

    @db_create_controlfile.sql

STEP 10: Place the new database in archivelog model




1 Put your source database in backup mode
2 Force a checkpoint in your source database
3 FTP all datafiles to your target box
4 Take your source database out of backup mode
5 Force a new checkpoint in your source database
6 FTP all relevant archive logs to target box
7 Create a control file on your source box
8 Move your newly created control file to the target box
9 On target box Edit your new control file as needed
10 Create/recover your new database using CREATE CONTROLFILE REUSE SET DATABASE "newname" RESETLOGS ARCHIVELOG until cancel.

Hope this helps. 

Thank you -Mr George Picoaga.

No comments:

Post a Comment