Wednesday, April 22, 2009

Hot Backup and Cloning

Procedure for Hot Backup and Hot Cloning:

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/test/oracle/product/10.2.0/oradata/orcl/system01.dbf
/u01/test/oracle/product/10.2.0/oradata/orcl/undotbs01.dbf
/u01/test/oracle/product/10.2.0/oradata/orcl/sysaux01.dbf
/u01/test/oracle/product/10.2.0/oradata/orcl/users01.dbf
/u01/test/oracle/product/10.2.0/oradata/orcl/example01.dbf
/u01/test/oracle/product/10.2.0/oradata/orcl/oneapps01
/u01/test/oracle/product/10.2.0/oradata/orcl/sant_reorg0.dbf
***********************************************************************

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/test/oracle/product/10.2.0/oradata/orcl/control01.ctl
/u01/test/oracle/product/10.2.0/oradata/orcl/control02.ctl
/u01/test/oracle/product/10.2.0/oradata/orcl/control03.ctl
**************************************************************************

NAMESQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/test/oracle/product/10.2.0/oradata/orcl/redo03.log
/u01/test/oracle/product/10.2.0/oradata/orcl/redo02.log
/u01/test/oracle/product/10.2.0/oradata/orcl/redo01.log
************************************************************************

SQL> show parameter _dump;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/test/oracle/product/10.2.
0/db_1/admin/test/bdump
core_dump_dest string /u01/test/oracle/product/10.2.
0/db_1/admin/orcl/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /u01/test/oracle/product/10.2.
0/db_1/admin/test/udump
**************************************************************************

Taking Hot Backup

1, alter database begin backup;

2, Copy Archive log files created during hot backup to /backup directory
cp -r 2009_03_30/ /u01/bkup30/.

3 Copy the All Data files to /backup directory.
cp -r oradata/ /u01/bkup30/

4 Backup the control file to trace.
SQL> alter database backup controlfile to trace;

5 Copy this trace file to /backup directory
/u01/test/oracle/product/10.2.0/db_1/admin/test/udump
cp test_ora_9407.trc /u01/bkup30/


6 Copy the current init.ora file to /backup directory
/u01/test/oracle/product/10.2.0/db_1/dbs
cp inittest.ora /u01/bkup30/


7 End the Begin Backup Mode
Sql> alter database end backup.
*****************************************************
Hot Backup Cloning

1 Create the target database control file manually

2 Open the backed up control file

a. remove all lines before the startup nomount statement
b. Modify the REUSE to SET
c. Modify Source DB SID to Target SID (Here PROD to TEST)
d. Modify NORESETLOGS TO RESETLOGS
e. delete all lines after the CHARACTER SET statement
**********************************************************

Make the trace file as a sql format create.sql

STARTUP NOMOUNT pfile='/u01/test/oracle/product/10.2.0/db_1/dbs/initkish.ora'
CREATE CONTROLFILE SET DATABASE "KISH" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/bkup30/oradata/kish/redo01.log' SIZE 50M,
GROUP 2 '/u01/bkup30/oradata/kish/redo02.log' SIZE 50M,
GROUP 3 '/u01/bkup30/oradata/kish/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/bkup30/oradata/kish/system01.dbf',
'/u01/bkup30/oradata/kish/undotbs01.dbf',
'/u01/bkup30/oradata/kish/sysaux01.dbf',
'/u01/bkup30/oradata/kish/users01.dbf',
'/u01/bkup30/oradata/kish/example01.dbf',
'/u01/bkup30/oradata/kish/oneapps01',
'/u01/bkup30/oradata/kish/sant_reorg0.dbf'
CHARACTER SET WE8ISO8859P1

************************************************************

SQL> @create.sql
ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size 2085776 bytes
Variable Size 163581040 bytes
Database Buffers 427819008 bytes
Redo Buffers 6299648 bytes

Control file created.

**************************************************************
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 2830167 generated at 03/30/2009 11:51:47 needed for thread 1
ORA-00289: suggestion :
/u01/test/oracle/product/10.2.0/db_1/flash_recovery_area/KISH/archivelog/2009_03
_30/o1_mf_1_5_%u_.arc
ORA-00280: change 2830167 for thread 1 is in sequence #5


Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/bkup30/oradata/kish/redo02.log
Log applied.
Media recovery complete.

****************************************************************
SQL> alter database open resetlogs;

Database altered.

*****************************************************************