Cloning using hot backup

Source database=TEST

New database to be created=LIVE

-Identify and copy the database files

With the source database started, identify all of the database's files.

Copy all tablespaces to the desired location.

  
SQL> startup

  
SQL> select name from v$tablespace;

  
SQL> alter system switch logfile;(do it thrice=no. of redo log groups)

  
SQL> alter tablespace SYSTEM begin backup;

Tablespace altered.

  
SQL> alter tablespace SYSTEM end backup;

Tablespace altered.

  
SQL> alter tablespace SYSAUX begin backup;

Tablespace altered.

  
SQL> alter tablespace SYSAUX end backup;

Tablespace altered.

  
SQL> alter tablespace UNDOTBS1 begin backup;

Tablespace altered.

  
SQL> alter tablespace UNDOTBS1 end backup;

Tablespace altered.

Copy archive logs::::

It is only necessary to copy archive logs created during the time the source database was in backup mode. Begin by archiving the current redo:

  
SQL> alter system archive log current;

System altered.

-Creating controlfile script

Create a control file for the new database

  
SQL> alter database backup controlfile to trace as 'C:\cr_live.sql';

Database altered.

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.
• Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
• Remove the line 'ALTER DATABASE OPEN RESETLOGS;'
• Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file. Make sure that you hang onto the command, it will be used later.
• 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.

IT WILL LOOK LIKE THIS::::

CREATE CONTROLFILE SET DATABASE "LIVE" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 32

    MAXLOGMEMBERS 2

    MAXDATAFILES 32

    MAXINSTANCES 16

    MAXLOGHISTORY 1752

LOGFILE

  GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\LIVE\REDO1.LOG'  SIZE 10M,

  GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\LIVE\REDO2.LOG'  SIZE 10M,

  GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\LIVE\REDO3.LOG'  SIZE 10M

DATAFILE

  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\LIVE\SYSTEM.DBF',

  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\LIVE\UNDOTBS1.DBF',

  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\LIVE\SYSAUX.DBF'

CHARACTER SET AL32UTF8

;

-Produce a pfile for the new database

SQL> create pfile='D:\oracle\product\10.2.0\db_1\database\initlive.ora' from spfile;

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.

(copy all archives from source db i.e from C:\test_archives to C:\live_archives)

Ensure that the archive log destination in pfile is pointing to the destination(C:\live_archives) i.e where log archives are being saved

-Specifying Instance Sid


D:\oracle\product\10.1.0\Db_1>oradim -new -sid live -intpwd sagar -startmode auto

File created.


 -Starting up the database

    
D:\oracle\product\10.2.0\db_1>set oracle_sid=LIVE

  
D:\oracle\product\10.2.0\db_1>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 16

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

  SQL> connect sys/sagar as sysdba

Connected to an idle instance.

  SQL> startup nomount;

ORACLE instance started.

Total System Global Area  289406976 bytes

Fixed Size                  1248576 bytes

Variable Size             104858304 bytes

Database Buffers          176160768 bytes

Redo Buffers                7139328 bytes

  SQL> @C:\CR_LIVE.sql (running the script to create controlfile)

 

  SQL> recover database using backup controlfile until cancel;

ORA-00279: change 376028 generated at 09/16/2011 17:17:22 needed for thread 1

ORA-00289: suggestion : C:\LIVE_ARCHIVES\ARC00097_0761913994.001

ORA-00280: change 376028 for thread 1 is in sequence #97

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

 

  AUTO

ORA-00279: change 376119 generated at 09/16/2011 17:20:02 needed for thread 1

ORA-00289: suggestion : C:\LIVE_ARCHIVES\ARC00098_0761913994.001

ORA-00280: change 376119 for thread 1 is in sequence #98

ORA-00278: log file 'C:\LIVE_ARCHIVES\ARC00097_0761913994.001' no longer needed

for this recovery

ORA-00308: cannot open archived log 'C:\LIVE_ARCHIVES\ARC00098_0761913994.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

  SQL> alter database open resetlogs;

Database altered.

  SQL> ALTER TABLESPACE TEMP ADD TEMPFILE   

       'D:\ORACLE\PRODUCT\10.2.0\ORADATA\LIVE\T 

       EMP.DBF' SIZE 10485760  REUSE AUTOEXTEND OFF;

Tablespace altered.

  SQL> select status from v$instance;

STATUS

------------

OPEN

  SQL> select distinct status from v$datafile;

STATUS

-------

ONLINE

SYSTEM

  SQL> create spfile from pfile;

File created.

-Configure TNS


Add entries for new database in the listener.ora and tnsnames.ora as necessary.