Duplicating Oracle databases without RMAN

Duplicating the database without RMAN is very useful if you are not using RMAN for backups or if the database is fairly small, since this method requires significantly less setup, then RMAN duplication method. This method will work for small to medium size databases up to perhaps 10 - 20 GB. If the database is larger then that, then probably RMAN duplication is a better alternative. For RMAN duplication see Metalink document 73912.1 for RMAN Duplication.  The potential problem with this approach is that the target database will have the same DBID (Database ID) as the source database. Threfore, if you are planning to use the same RMAN recovery catalog for the target database as you are using for the source database, you will have an issue with the current incarnation, and therefore it will not work. The steps below would be for replacing the existing database (target) with the source database. For example lets say you have a test database (target) which needs to refreshed with production (source) database data

DUPLICATING DATABASE STEPS:
• Shutdown the target database

• modify the PFILE, change database name to be the name of the source database.

• change the Environment variable, set ORACLE_SID to be the source database

• Disable archive log backup on the source database

• Begin backup on datafiles about to be brough over, copy over the datafiles and archive logs which are generated while the datafiles are being copied.

• Once all the datafiles have been brought over, alter all tablespaces (or database)- end backup.

• Several times switch the logs on the source database to get the empty archive logs

• Copy all the archive logs which were generated during the backup to the arch destination directory of the target database

• On source database, print control file to trace.

• Startup the target database: "STARTUP NOMOUNT PFILE=pfile" (otherwise or you can change the pfile file to match the SID name of the source database)

• Modify the control file printed from trace from the source to change the location of the datafiles and redo logs. :

• Example:

                    CREATE CONTROLFILE REUSE DATABASE "ISTP" RESETLOGS ARCHIVELOG

              MAXLOGFILES 32

              MAXLOGMEMBERS 2

              MAXDATAFILES 30

              MAXINSTANCES 8

              MAXLOGHISTORY 904

              LOGFILE

              GROUP 1 (

                  '/oradata08/oradata/ISTU/redo_logs/redo01a.log',

                  '/oradata07/oradata/ISTU/redo_logs_mirror/redo01b.log'

              ) SIZE 10M,

              GROUP 2 (

                  '/oradata08/oradata/ISTU/redo_logs/redo02a.log',

                  '/oradata07/oradata/ISTU/redo_logs_mirror/redo02b.log'

              ) SIZE 10M,

                GROUP 3 (

                  '/oradata08/oradata/ISTU/redo_logs/redo03a.log',

                  '/oradata07/oradata/ISTU/redo_logs_mirror/redo03b.log'

              ) SIZE 10M

                DATAFILE

              '/oradata09/oradata/ISTU/data_files/system01.dbf',

              '/oradata08/oradata/ISTU/data_files/istd0101.dbf',

              '/oradata09/oradata/ISTU/data_files/temp01.dbf',

              '/oradata08/oradata/ISTU/data_files/rbs0101.dbf',

              '/oradata09/oradata/ISTU/data_files/tools01.dbf',

              '/oradata09/oradata/ISTU/data_files/users01.dbf'

                CHARACTER SET US7ASCII

               /

  
  
• Recover database. You will be prompted for the archive logs, supply the archive logs brought over from source server. Either type the archive logs eith the full path or simply press ENTER for default. When all archive have been applied, type CANCEL

• alter database open resetlogs, if it doesn't work. shutdown the database. "open no mount pfile=...." recreate the control file with the stament from above. Alter database open resetlogs should work now.

• Rename the database. shutdown the database. "startup nomount pfile=..." change db name using new control file

• Example:

          CREATE CONTROLFILE REUSE SET DATABASE "ISTU" RESETLOGS ARCHIVELOG

            MAXLOGFILES 32

            MAXLOGMEMBERS 2

            MAXDATAFILES 30

            MAXINSTANCES 8

            MAXLOGHISTORY 904

        LOGFILE

          GROUP 1 (

            '/oradata08/oradata/ISTU/redo_logs/redo01a.log',

            '/oradata07/oradata/ISTU/redo_logs_mirror/redo01b.log'

          ) SIZE 10M,

          GROUP 2 (

            '/oradata08/oradata/ISTU/redo_logs/redo02a.log',

            '/oradata07/oradata/ISTU/redo_logs_mirror/redo02b.log'

          ) SIZE 10M,

          GROUP 3 (

            '/oradata08/oradata/ISTU/redo_logs/redo03a.log',

            '/oradata07/oradata/ISTU/redo_logs_mirror/redo03b.log'

          ) SIZE 10M

        DATAFILE

          '/oradata09/oradata/ISTU/data_files/system01.dbf',

          '/oradata08/oradata/ISTU/data_files/istd0101.dbf',

          '/oradata09/oradata/ISTU/data_files/temp01.dbf',

          '/oradata08/oradata/ISTU/data_files/rbs0101.dbf',

          '/oradata09/oradata/ISTU/data_files/tools01.dbf',

          '/oradata09/oradata/ISTU/data_files/users01.dbf'

        CHARACTER SET US7ASCII

        /

  
  
• alter database open resetlogs

• shutdown immediate

• Modify envronment variable ORACLE_SID back to what target database is suppose to be

• change pfile, modify DB_NAME to the old name

• startup the database normally