Monday, May 19, 2008

RMAN versus User Managed Backup

RMAN versus User Managed Backup-Part-I
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.

RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.

RMAN versus User Managed Backup-Part-I
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.

RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.

RMAN versus User Managed Backup-Part-I
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.

RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.

RMAN versus User Managed Backup-Part-I
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.

RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)