CLONE ORACLE DATABASE ON THE SAME UNIX MACHINE

Print this post

Before copying the database to a new location, it is necessary to perform a full cold backup of the database,
whilst the database is shutdown.  This will ensure that no data will be lost if the copying of the database is
unsuccessful.

WARNING 
------------

Creating a copy of a database involves usage of the CREATE CONTROLFILE command (explained below). 
If this command is not executed correctly it could corrupt the production database. This will mean that
the original database will need to be restored from a backup.


1. OBTAIN DATABASE INFORMATION FROM CONTROLFILE
  -----------------------------------------------------------------------

  In order to move the database, it is necessary to create a script containing  information about the files
  of the database.

  This is done by executing the following commands.

  a.   sqlplus /nolog

  b.  SQL> connect / as sysdba

  c.  ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

      This will create a trace file in the trace file directory. The file will have the extension .trc, and is located
      either in the directory defined by the initialization parameter 'user_dump_dest', or, if this is not defined,
      in ORACLE_HOME/rdbms/log. The file should be copied to a name such as ccf<NEW_SID>.sql, where
      <NEW_SID> is to be the ORACLE_SID of the copied  database.

2.     IDENTIFY FILES TO BACKUP/COPY
      --------------------------------------------

 a.   Identify database and log files

      The CREATE CONTROLFILE command in the file ccf<NEW_SID>.sql can then be used to identify the
      various database files and redo log files that need to be backed up.  The file names will be in single quotes
      and separated by commas after the words LOGFILE and DATAFILE, e.g: 

      CREATE CONTROLFILE REUSE DATABASE "FAMY" RESETLOGS ARCHIVELOG
          MAXLOGFILES 6
          MAXLOGMEMBERS 2
          MAXDATAFILES 10
          MAXINSTANCES 1
          MAXLOGHISTORY 100
      LOGFILE
        GROUP 1 (
        '/oracle/tberryha/fritz/log_disk1/famylog1v713.dbf',
        '/oracle/tberryha/fritz/log_disk2/famylog1v713.dbf'
        ) SIZE 50K,
        GROUP 2 (
        '/oracle/tberryha/fritz/log_disk1/famylog2v713.dbf',
        '/oracle/tberryha/fritz/log_disk2/famylog2v713.dbf'
        ) SIZE 50K
      DATAFILE
        '/oracle/tberryha/fritz/fritz_system/famy_system01.dbf' SIZE 8M,
        '/oracle/tberryha/fritz/fritz_data/famy_data01.dbf' SIZE 20M,
        '/oracle/tberryha/fritz/fritz_rollback/famy_rollback01.dbf' SIZE 20M,
        '/oracle/tberryha/fritz/fritz_temp/famy_temp01.dbf' SIZE 20M,
        '/oracle/tberryha/fritz/fritz_data/famy_data02.dbf' SIZE 5M,
        '/oracle/d2/V7141/dbs/x' SIZE 1M
      ;
         It is also possible to obtain a listing of the files of the database by executing the following sql commands:

         SQLPLUS username/password
         Note: The user must have sufficient privileges to be able to see the dba views 'sys.dba_data_files',
         'sys.v$logfile' and in addition the database must be open.

         SPOOL files.log

         SQL> SELECT file_name FROM sys.dba_data_files ORDER BY tablespace_name;

         SQL> SELECT member FROM sys.v$logfile

         SPOOL OFF
         Note: This will create a spool file called 'files.log' which will record the results of the previous commands.

  b.  Identify controlfiles

      This can be done either by referring to the  init<SID>.ora  'control_files' parameter, or from 7.0.16
      onwards, the table sys.v$controlfile can be used to identify the controlfiles of the database via the following
      statement:

      SPOOL control.log
      SELECT name FROM v$controlfile;
      SPOOL OFF

      This will create a file called control.log in the current directory which will contain the names of the
      controlfiles for the database.

3. BACKUP EXISTING DATABASE
  --------------------------------------

  Shutdown instance via SHUTDOWN NORMAL, and then take full cold backup of:

  a. All the files identified in step 2 above.

  b. All parameter files.
     Note: the main parameter file will usually be called init<SID>.ora, in addition to which there may
     also be other parameter files. These will  be identified by 'ifile' (included file) parameters in the
     init<SID>.ora. These additional parameter files are usually called config<SID>.ora.

4. MAKE A COPY OF THE DATABASE
   -------------------------------------------
  Shutdown instance via  SQLPlus, SHUTDOWN NORMAL.

  Copy all parameter files, controlfiles, and all files noted in step 2 above to their new location taking care
  to preserve ownership and permissions. When the database has been copied, it will not be possible to use
  the same SID and therefore the ORACLE_SID environment variable must  be changed to a new SID, and the
  copied init<SID>.ora must be renamed to init<NEW_SID>.ora, and any parameter files pointed
  to by an 'ifile' parameter (e.g. parameter files such as config<SID>.ora) should be renamed to incorporate
  the NEW_SID (i.e. config<NEW_SID>.ora).


5. SET UP PARAMETER FILES FOR THE COPIED DATABASE
  ---------------------------------------------- -------------------------

  Edit the value of the control_files parameter in the init<NEW_SID>.ora to be the name and
  location that you want to use for the new control files. The controlfiles should be given a different
  name to distinguish them from the old database.  In addition, change the DB_NAME parameter in
  the init<NEW_SID>.ora to be an appropriate name for the new database.  Any 'ifile' parameters
  of the parameter file will need to be edited to point to the new name of the include file in the new
  location.Ensure that the parameter DB_BLOCK_SIZE is the same as the originating database, otherwise
  when creating the controlfile  you may receive:  ORA-01160 "file is not a %s"

6. PREPARE THE 'CREATE CONTROLFILE COMMAND' FOR THE COPIED DATABASE
  ------------------------------------------------------------------------------------------------------

  In order to establish the new database in the new location, the CREATE CONTROLFILE command in the
 file ccf<NEW_SID>.sql should be executed. The following steps illustrate how CREATE CONTROLFILE
 command is prepared.

 a.  The file ccf<NEW_SID>.sql must be edited before use.  The CREATE CONTROLFILE command will
      be preceded by a series of comments and a  STARTUP NOMOUNT command. These need to be stripped
      out of the file.  In addition, after the create controlfile command, there will be a number of comments
      and the commands RECOVER DATABASE and ALTER DATABASE OPEN, which should also be stripped out,
      leaving just the create controlfile 

       command itself. 

  b.  The CREATE CONTROLFILE command itself should also be edited.  Change the CREATE CONTROLFILE
       command in 'ccf<NEW_SID>.sql' to have the new database name, and add the word 'SET', e.g:

      SQL> CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS
      becomes
      SQL> CREATE CONTROLFILE REUSE set DATABASE "newdbname" RESETLOGS

  c.  The CREATE CONTROLFILE command also specifies the files which make up the database, and these
       must also be changed to name the files of the new database in the new location, e.g:

      LOGFILE
        GROUP 1 (
          '/old_path/old_logfile_name1',
          '/old_path/old_logfile_name2'
        ) SIZE 50k
      would become:
      LOGFILE
        GROUP 1 (
          '/new_path/new_logfile_name1',
          '/new_path/new_logfile_name2'
        ) SIZE 50k
      and
      DATAFILE
        '/old_path/old_file_name1' SIZE 5M,
        '/old_path/old_file_name2' SIZE 10M
      ;
      would become:
      DATAFILE
        '/new_path/new_file_name1' SIZE 5M,
        '/new_path/new_file_name2' SIZE 10M
      ;

  d.  After Recreating Controlfile

        o Tablespace TEMP is available
        o DBA_TEMP_FILES  does not show a tempfile

    If using ALTER DATABASE BACKUP CONTROLFILE TO TRACE, to get a CREATE CONTROLFILE SCRIPT -
   the Datafile for Tablespace TEMP (TEMPFILE) is not listed in

             "CREATE CONTROLFILE STATEMENT"

     You must add the TEMPFILE manaually after Creating the Controlfile

      > Alter database backup controlfile to trace ;

        You should check Tracefile: (in udump) to get Statement on how to Add the Tempfile to temp Tablespace
 
      --> see end of tracefile
 
        EXAMPLE:
        ========
        Create Controlfile .....
        ...
        ;
        ...
        # Commands to add tempfiles to temporary tablespaces.
        # Online tempfiles have complete space information.
        # Other tempfiles may require adjustment.
        ALTER TABLESPACE TEMP ADD TEMPFILE '/U01/ORADATA/T920/TEMP01.DBF'
             SIZE 10485760  REUSE AUTOEXTEND OFF;
        # End of tempfile additions.
        #

       Simply adjust this command (path/name)  to your requirements and do for example:

        > ALTER TABLESPACE TEMP ADD TEMPFILE '/U01/ORADATA/T920/TEMP01.DBF'
          SIZE 10485760  REUSE AUTOEXTEND OFF;


7. CHECK REMOTE_LOGIN_PASSWORDFILE PARAMETER
  ----------------------------------------------------------------------
  Prior to executing the create controlfile script, check whether the initialisation parameter
  REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared.  If this is set, then a valid
  passwordfile  should exist in ORACLE_HOME/dbs or created using orapwd, otherwise you will
  receive an ora-600 [kzsrsdn:1].

8. EXECUTE THE 'CREATE CONTROLFILE' COMMAND FOR THE COPIED DATABASE
  ------------------------------------------------------------------------------------------------------
  Having prepared the create controlfile script, it is now necessary to run the script from within
 the new instance.  This is done by executing the following:

  a.  at the operating system prompt, change the value of the environment  variable ORACLE_SID
       from OLD_SID to NEW_SID. 
  b.  SQLPlus
  c.  CONNECT / AS SYSDBA
      SQL> STARTUP NOMOUNT PFILE=/<full path>/init<NEW_SID>.ora     
  d.  SQL> @ccf<NEW_SID>

    Note: if any  files which should  be specified in the  CREATE CONTROLFILE   command are omitted,
    these files  cannot be  added to the new  database at a later  date.  In addition, if any of the files
    specified in the CREATE CONTROLFILE command are NOT changed from their original names, then
    the corresponding files of the original database will become part of the copied database, and it will
    not be possible to restore them to the original database.  If this happens, and if the files in question
    are important, this will mean that the original database will need to be restored from a backup. 

    When  recreating  the  controlfile, it  is possible  to encounter  ORA-1565,  ORA-1503  and  ORA-9782,
    "another instance  has this  database mounted", the ORACLE_SID of the  original database is still in the
    header of the first datafile of the copy and oracle checks if an sgadef<ORACLE_SID>.dbf still exists
    in $ORACLE_HOME/dbs, to workaround this problem shutdown the other database of which you made a
    copy prior to issueing the create controlfile command.

  NOTE:  If you are using online 'hot' backup at this point you should issue:

        recover database until cancel using backup controlfile;
  
         Apply all the archives you have

  e.  SQL> ALTER DATABASE OPEN RESETLOGS

9. MAKE A FULL COLD BACKUP OF THE COPIED DATABASE
  ----------------------------------------- -------------------------------

  SHUTDOWN and take a full cold backup of the database in the new location.  The full cold backup can be
  done as detailed in steps 2 and 3.