Monday, April 11, 2011

RMAN is Smart, But...


Restoration and recovery are the basic tasks which a DBA might perform regularly. The time of restoration and recovery is highly tensed and critical. Despite of good, available, and tested backups, it's easy to get carried away and mix up the things with RMAN. Trying different restores and recovers with SCN, and other option can lead to confusion or worst towards an un-successful recovery.

The best way is to cool down, and plan out as what exactly needs to be recovered, and then only ask that from RMAN. RMAN is smart, but its extra smart and we need to be sure to ask what exactly we need, avoiding ocean of information.

This short simple demo presents the full restore/recover of a database through RMAN to just show that its always clean and helpful to know that until how long
we can recover and then ordering just that to RMAN.

[oracle@stage dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 12 22:01:16 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@stage dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Feb 12 22:08:19 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1335192 bytes
Variable Size                 75497576 bytes
Database Buffers              79691776 bytes
Redo Buffers                   2494464 bytes

RMAN> restore spfile to pfile '/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora' from '/d01/backup/ORCL_1';

Starting restore at 12-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /d01/backup/ORCL_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/12/2011 22:17:57
ORA-19687: SPFILE not found in backup set

RMAN> restore spfile to pfile '/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora' from '/d01/backup/ORCL_2';

Starting restore at 12-FEB-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /d01/backup/ORCL_2
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 12-FEB-11

RMAN> startup force nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora';

Oracle instance started

Total System Global Area     564957184 bytes

Fixed Size                     1337916 bytes
Variable Size                360711620 bytes
Database Buffers             197132288 bytes
Redo Buffers                   5775360 bytes

RMAN> restore controlfile from '/d01/backup/ORCL_2';

Starting restore at 12-FEB-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 12-FEB-11

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    1.03G      DISK        00:01:17     12-FEB-11    
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20110212T215413
        Piece Name: /d01/backup/ORCL_1
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 806024     12-FEB-11 /u01/app/oracle/oradata/orcl/system01.dbf
  2       Full 806024     12-FEB-11 /u01/app/oracle/oradata/orcl/sysaux01.dbf
  3       Full 806024     12-FEB-11 /u01/app/oracle/oradata/orcl/undotbs01.dbf
  4       Full 806024     12-FEB-11 /u01/app/oracle/oradata/orcl/users01.dbf
  5       Full 806024     12-FEB-11 /u01/app/oracle/oradata/orcl/example01.dbf


RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  F  A DISK        12-FEB-11       1       1       NO         TAG20110212T215413

RMAN> restore database;

Starting restore at 12-FEB-11
Starting implicit crosscheck backup at 12-FEB-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 12-FEB-11

Starting implicit crosscheck copy at 12-FEB-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 12-FEB-11

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /d01/backup/ORCL_1
channel ORA_DISK_1: piece handle=/d01/backup/ORCL_1 tag=TAG20110212T215413
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 12-FEB-11

RMAN> recover database;

Starting recover at 12-FEB-11
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/12/2011 22:29:55
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 806024

RMAN> run
2> {
3>  set until scn 806023;
4>  recover database;
5> }

executing command: SET until clause

Starting recover at 12-FEB-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/12/2011 22:31:23
RMAN-06556: datafile 1 must be restored from backup older than SCN 806023

RMAN> run
2> {
3>  set until scn 806024;
4> recover database;
5> }

executing command: SET until clause

Starting recover at 12-FEB-11
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 12-FEB-11

RMAN> alter database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.
[oracle@stage dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 12 22:50:54 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl             OPEN

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@stage dbs]$

So, the better way is to fisrt get the SCN at the mount stage by following query, and then restore/recover together in RUN block after setting until that SCN:

select group#, first_change#, status, archived from v$log;

1 comment:

Elia Mtafya said...

Fine.What will be a solution to this before upgrade from 11.2.0.1 rman was working properly but after upgrade to 11.2.0.2 rman start to throw this ''RMAN-03009: failure of allocate command on t1 channel at 07/25/2011 19:46:18,
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library''please any help