Wednesday, September 28, 2011

Clone an Oracle database using a cold backup..

This procedure will clone a database using a cold copy of the source database files. If a cold backup of the database is available, restore it to the new location and jump to step 2.

  • 1. Identify and copy the database files
    With the source database started, identify all of the database's files. The following query will display all datafiles, tempfiles and redo logs:
    set lines 100 pages 999
    col name format a50
    select name, bytes
    from (select name, bytes
    from v$datafile
    union all
    select name, bytes
    from v$tempfile
    union all
    select lf.member "name", l.bytes
    from v$logfile lf
    , v$log l
    where = used
    , (select sum(bytes) as poo
    from dba_free_space) free
    Make sure that the clone databases file-system is large enough and has all necessary directories. If the source database has a complex file structure, you might want to consider modifying the above sql to produce a file copy script.

    Stop the source database with:
    shutdown immediate
    Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

    Start the source database up again
  • 2. Produce a pfile for the new database
    This step assumes that you are using a spfile. If you are not, just copy the existing pfile.

    From sqlplus:
    create pfile='init<new database sid>.ora' from spfile;
    This will create a new pfile in the $ORACLE_HOME/dbs directory.

    Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

    Note. Pay particular attention to the control locations.

  • 3. Create the clone controlfile
    Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:
    alter database backup controlfile to trace as '/home/oracle/cr_<new sid>.sql'
    The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

    • Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).
    • Remove any lines that start with --
    • Remove any lines that start with a #
    • Remove any blank lines in the 'CREATE CONTROLFILE' section.
    • Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.
    • If the file paths are being changed, alter the file to reflect the changes.

    Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:
    GROUP 1 '/u03/oradata/dg9a/redo01.log' SIZE 100M,
    GROUP 2 '/u03/oradata/dg9a/redo02.log' SIZE 100M,
    GROUP 3 '/u03/oradata/dg9a/redo03.log' SIZE 100M


    ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oradata/dg9a/temp01.dbf'
  • 4. Add a new entry to oratab and source the environment
    Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
    Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
    echo $ORACLE_SID
    If this doesn't output the new database sid go back and investigate.

  • 5. Create the a password file
    Use the following command to create a password file (add an appropriate password to the end of it):
    orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=<your password>
  • 5. Create the new control file(s)
    Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:
    sqlplus "/ as sysdba"
    @/home/oracle/cr_<new database sid>
    It is quite common to run into problems at this stage. Here are a couple of common errors and solutions:
    ORA-01113: file 1 needs media recovery
    You probably forgot to stop the source database before copying the files. Go back to step 1 and recopy the files.
    ORA-01503: CREATE CONTROLFILE failed
    ORA-00200: controlfile could not be created
    ORA-00202: controlfile: '/u03/oradata/dg9a/control01.ctl'
    ORA-27038: skgfrcre: file exists
    Double check the pfile created in step 2. Make sure the control_files setting is pointing at the correct location. If the control_file setting is ok, make sure that the control files were not copied with the rest of the database files. If they were, delete or rename them.

  • 6. Perform a few checks
    If the last step went smoothly, the database should be open. It is advisable to perform a few checks at this point:

    • Check that the database has opened with:
      select status from v$instance;
      The status should be 'OPEN'
    • Make sure that the datafiles are all ok:
      select distinct status from v$datafile;
      It should return only ONLINE and SYSTEM.
    • Take a quick look at the alert log too.

  • 7. Set the databases global name
    The new database will still have the source databases global name. Run the following to reset it:
    alter database rename global_name to <new database sid>
  • 8. Create a spfile
    From sqlplus:
    create spfile from pfile;
  • 9. Change the database ID
    If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.
    From sqlplus:
    shutdown immediate
    startup mount
    From unix:
    nid target=/
    NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:
    shutdown immediate
    startup mount
    alter database open resetlogs
  • 10. Configure TNS
    Add entries for new database in the listener.ora and tnsnames.ora as necessary.

  • 11. Finished

Sukhwinder Singh
Apps DBA

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

There was an error in this gadget