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=10000

DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008

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

DBVERIFY - Verification starting: FILE = system01.dbf
......

DBVERIFY - Verification complete

Total Pages Examined : 56320
Total Pages Processed (Data) : 33386
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 6077
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1524
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 15333
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

NOTE: we can check all files like above one by one

Now 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> run
2> {
3> allocate channel c1 device type disk format 'e:\backup\%U';
4> restore validate check logical database archivelog all;
5> }

allocated channel: c1
channel c1: sid=147 devtype=DISK

Starting restore at 10-MAY-08

channel c1: starting validation of datafile backupset
channel c1: restored backup piece 1
piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545
channel c1: validation complete
channel c1: starting validation of archive log backupset
channel c1: restored backup piece 1
piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615
channel c1: validation complete
Finished restore at 10-MAY-08
released channel: c1

NOTE: 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> run
2> {
3> backup validate check logical database archivelog all;
4> }

Starting backup at 10-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF

input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF

input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DB
F
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DB
F
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=9 recid=3 stamp=654372512
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 10-MAY-08

Read 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 oradim
C:\>oradim -new -sid ORCL -startmode a
Instance created.

3. Create password file if database authentication method is passwordfile
C:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y

4. Connect to sqlplus with sysdba privileges
C:\>sqlplus sys/oracle as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008

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

Connected to an idle instance.

5. Create spfile from backup of pfile
SQL> create spfile from pfile='e:\backup\init.ora';

File created.

6. startup database with nomount stage
SQL> startup nomount
ORACLE instance started.

Total System Global Area 293601280 bytes
Fixed Size 788572 bytes
Variable Size 98826148 bytes
Database Buffers 192937984 bytes
Redo Buffers 1048576 bytes

7. restore controlfile from backup and mount the database
SQL> alter database mount;

Database altered.

8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clause
SQL> set autorecovery on
SQL> recover database using backup controlfile until cancel;
open database with resetlogs
SQL> alter database open resetlogs;

RMAN restore and recover procedure from backup.
1. Create file directory structure for datafiles or trace file from alertlog

2. Create Oracle Service from oradim utility
C:\>oradim -new -sid ORCL
Instance created.

3. Create password file

4. Connect to RMAN with SYS user

C:\>rman target=sys

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (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 autobackup
NOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01

RMAN> set DBID=1182151585

executing command: SET DBID

RMAN> set DBID=1182151585

executing command: SET DBID

RMAN> startup nomount force;

startup failed: ORA-01078: failure in processing system parameters
LRM-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 started

Total System Global Area 113246208 bytes

Fixed Size 787708 bytes
Variable Size 61078276 bytes
Database Buffers 50331648 bytes
Redo Buffers 1048576 bytes

6. Restore SPFILE from backup

RMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';

Starting restore at 10-MAY-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=29 devtype=DISK

channel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 10-MAY-08

7. Now shutdown the database and startup and create spfile

RMAN> startup nomount pfile 'e:\backup\init.ora';

connected to target database (not started)
Oracle instance started

Total System Global Area 293601280 bytes

Fixed Size 788572 bytes
Variable Size 94631844 bytes
Database Buffers 197132288 bytes
Redo Buffers 1048576 bytes

8. Restore controlfile from backup and mount the database

RMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';

Starting restore at 10-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 10-MAY-08

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

9. 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> run
2> {
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\redo0
1.log'' to ''e:\orcl\oradata\redo01.log'' ";
8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo0
2.log'' to ''e:\orcl\oradata\redo02.log'' ";
9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo0
3.log'' to ''e:\orcl\oradata\redo03.log'' ";
10> restore database;
11> switch datafile all;
12> recover database;
13> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orc
l\redo01.log'' to ''e:\orcl\oradata\redo01.log''

sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orc
l\redo02.log'' to ''e:\orcl\oradata\redo02.log''

sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orc
l\redo03.log'' to ''e:\orcl\oradata\redo03.log''

Starting restore at 10-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBF
restoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBF
restoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBF
restoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008
channel ORA_DISK_1: restore complete
Finished restore at 10-MAY-08

datafile 1 switched to datafile copy
input datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBF

datafile 2 switched to datafile copy
input datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DB
F
datafile 3 switched to datafile copy
input datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBF

datafile 4 switched to datafile copy
input datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBF

Starting recover at 10-MAY-08
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/10/2008 20:33:39
RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780

RMAN> run
2> {
3> SQL 'alter database open resetlogs';
4> }

sql statement: alter database open resetlogs

11. All datafiles and redologs file switch perfectly :)

SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;

FILE# NAME
---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF
2 E:\ORCL\ORADATA\UNDOTBS01.DBF
3 E:\ORCL\ORADATA\SYSAUX01.DBF
4 E:\ORCL\ORADATA\USERS01.DBF
E:\ORCL\ORADATA\REDO01.LOG
E:\ORCL\ORADATA\REDO02.LOG
E:\ORCL\ORADATA\REDO03.LOG

7 rows selected.

12. Create SPFILE from PFILE

SQL> create spfile from pfile='e:\backup\init.ora';
File created.