Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog
Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Wednesday, December 08, 2010

Restore With RMAN (Examples)

Steps for media recovery: 
1. Mount or open the database.
Mount the database when performing whole database recovery, or open the database when performing online tablespace recovery.

2. To perform incomplete recovery, use the SET UNTIL command to specify the time, SCN, or log sequence number at which recovery terminates. Alternatively, specify the UNTIL clause on the RESTORE and RECOVER commands.
3. Restore the necessary files with the RESTORE command.
4. Recover the datafiles with the RECOVER command.
5. Place the database in its normal state. For example, open it or bring recovered tablespaces online.

Restore and recover a tablespace
To check the scenerio do the following:
1-Take full DB backup
2- Offline the tablespace and datafile related to MYTS_ON_ASM, delete the datafile at OS level
ALTER DATABASE DATAFILE '+DB_DATA/asmdb/datafile/MYTS_ON_ASM.263.737202621' OFFLINE
RMAN> SQL 'ALTER TABLESPACE MYTS_ON_ASM OFFLINE';
sql statement: ALTER TABLESPACE MYTS_ON_ASM OFFLINE
RMAN> RESTORE TABLESPACE MYTS_ON_ASM;
Starting restore at 08-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +DB_DATA/asmdb/datafile/myts_on_asm.263.733412067
channel ORA_DISK_1: reading from backup piece D:\HOME_BACKUP\ASMRMAN\MYTS_ON_ASM_ASMDB_1MLV1IVK_54
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\HOME_BACKUP\ASMRMAN\MYTS_ON_ASM_ASMDB_1MLV1IVK_54 tag=TAG20101208T100540
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 08-DEC-10

RMAN> RECOVER TABLESPACE MYTS_ON_ASM;
Starting recover at 08-DEC-10
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 08-DEC-10
RMAN> SQL 'ALTER TABLESPACE MYTS_ON_ASM ONLINE';
sql statement: ALTER TABLESPACE MYTS_ON_ASM ONLINE

Observations: when restoring tablespace on ASM based DB, data filename is get changed.

Restore and recover a datafile
To check the scenerio do the following, assume database is running while this exercise
1- Take the full DB backup 
2- Make the datafile offline
ALTER DATABASE DATAFILE '+DB_DATA/asmdb/datafile/MYTS_ON_ASM.263.737203475' OFFLINE 
3- Delete the file on OS MYTS_ON_ASM.263.737203475


Hands On


Now as we have deleted the data file on OS, we check from the DB about tablespace and associated datafiles.




select tablespace_name,status from dba_tablespaces




TABLESPACE_NAME STATUS
SYSTEM         ONLINE
UNDOTBS1 ONLINE
SYSAUX         ONLINE
TEMP         ONLINE
MYTS_ON_ASM ONLINE
XDB         ONLINE
GGS_DATA ONLINE
USERS         ONLINE
EXAMPLE         ONLINE




select file_name,file_id,tablespace_name,status,online_status 
from dba_data_files 




FILE_NAME                                  FILE_ID  TABLESPACE_NAME STATUS ONLINE_STATUS
+DB_DATA/asmdb/datafile/system.259.733408841    1   SYSTEM   AVAILABLE SYSTEM
+DB_DATA/asmdb/datafile/undotbs1.260.733408851    2   UNDOTBS1   AVAILABLE ONLINE
+DB_DATA/asmdb/datafile/sysaux.261.733408851    3   SYSAUX   AVAILABLE ONLINE
+DB_DATA/asmdb/datafile/myts_on_asm.263.737203475  4   MYTS_ON_ASM   AVAILABLE RECOVER
+DB_DATA/asmdb/datafile/xdb.264.733412967    5   XDB           AVAILABLE ONLINE
+DB_DATA/asmdb/datafile/ggs_data.267.734093627    6   GGS_DATA   AVAILABLE ONLINE
+DB_DATA/asmdb/datafile/users.dbf            7   USERS           AVAILABLE ONLINE
+DB_DATA/asmdb/datafile/example.dbf            8   EXAMPLE   AVAILABLE ONLINE
Observe status is available but online_status is recover.


Now we user RMAN and do the following


RMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';
sql statement: ALTER DATABASE DATAFILE 4 OFFLINE


RMAN> RESTORE DATAFILE 4;
Starting restore at 08-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +DB_DATA/asmdb/datafile/myts_on_asm.263.737203475
channel ORA_DISK_1: reading from backup piece D:\HOME_BACKUP\ASMRMAN\DB_ASMDB_1OLV1KIL_56
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\HOME_BACKUP\ASMRMAN\DB_ASMDB_1OLV1KIL_56 tag=TAG20101208T103252
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 08-DEC-10


RMAN> RECOVER DATAFILE 4;
Starting recover at 08-DEC-10
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 08-DEC-10


 


At this point check the datafile status 


select file_name,file_id,tablespace_name,status,online_status 
from dba_data_files


FILE_NAME                                  FILE_ID  TABLESPACE_NAME STATUS ONLINE_STATUS
+DB_DATA/asmdb/datafile/system.259.733408841    1   SYSTEM   AVAILABLE SYSTEM
+DB_DATA/asmdb/datafile/undotbs1.260.733408851    2   UNDOTBS1   AVAILABLE ONLINE
+DB_DATA/asmdb/datafile/sysaux.261.733408851    3   SYSAUX   AVAILABLE ONLINE
+DB_DATA/asmdb/datafile/myts_on_asm.263.737203475  4   MYTS_ON_ASM   AVAILABLE OFFLINE
+DB_DATA/asmdb/datafile/xdb.264.733412967    5   XDB           AVAILABLE ONLINE
+DB_DATA/asmdb/datafile/ggs_data.267.734093627    6   GGS_DATA   AVAILABLE ONLINE
+DB_DATA/asmdb/datafile/users.dbf            7   USERS           AVAILABLE ONLINE
+DB_DATA/asmdb/datafile/example.dbf            8   EXAMPLE   AVAILABLE ONLINE




RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';
sql statement: ALTER DATABASE DATAFILE 4 ONLINE




Restore and recover the whole database


Do the following to check the scenerio


1- Take full DB backup and then Shutdown DB


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.  


2-  Delete any datafile on OS 


2.1- As I've ASM based DB I need to use the ASMCMD to delete the file


Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\inam>set ORACLE_HOME=D:\oracle\product\10.2.0\db_1
C:\Documents and Settings\inam>set ORACLE_SID=+ASM
C:\Documents and Settings\inam>asmcmd
ASMCMD>


ASMCMD> ls
DB_ARCHIVELOG/
DB_DATA/
ASMCMD> cd DB_DATA
ASMDB/
ASMCMD> cd ASMDB
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
ASMCMD> cd DATAFILE


ASMCMD> pwd
+DB_DATA/ASMDB/DATAFILE
ASMCMD> ls
EXAMPLE.269.735649055
EXAMPLE.dbf
GGS_DATA.267.734093627
MYTS_ON_ASM.263.737205055
SYSAUX.261.733408851
SYSTEM.259.733408841
UNDOTBS1.260.733408851
USERS.268.735646613
USERS.dbf
XDB.264.733412967
ASMCMD> rm MYTS_ON_ASM.263.737205055
ASMCMD> ls
EXAMPLE.269.735649055
EXAMPLE.dbf
GGS_DATA.267.734093627
SYSAUX.261.733408851
SYSTEM.259.733408841
UNDOTBS1.260.733408851
USERS.268.735646613
USERS.dbf
XDB.264.733412967
ASMCMD>


 


3- Try to start the instance


SQL> startup pfile=D:\ASMTEST\ASMDB\pfile\initASMDB.ora
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1253124 bytes
Variable Size             264241404 bytes
Database Buffers          801112064 bytes
Redo Buffers                7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DB_DATA/asmdb/datafile/myts_on_asm.263.737205055'
Check the alertlog (alert_asmdb.log) for more info, you will see the message like below


 


Wed Dec 08 11:38:09 2010
Errors in file d:\asmtest\asmdb\bdump\asmdb_dbw0_6112.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DB_DATA/asmdb/datafile/myts_on_asm.263.737205055'
ORA-17503: ksfdopn:2 Failed to open file +DB_DATA/asmdb/datafile/myts_on_asm.263.737205055
ORA-15012: ASM file '+DB_DATA/asmdb/datafile/myts_on_asm.263.737205055' does not exist





ORA-1157 signalled during: ALTER DATABASE OPEN...


 


4- Start Recovery process


 


















C:\Documents and Settings\inam>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 8 11:41:52 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ASMDB (DBID=477607595, not open)


 


RMAN> STARTUP FORCE MOUNT pfile=D:\ASMTEST\ASMDB\pfile\initASMDB.ora;

Oracle instance started
database mounted

Total System Global Area    1073741824 bytes

Fixed Size                     1253124 bytes
Variable Size                264241404 bytes
Database Buffers             801112064 bytes
Redo Buffers                   7135232 bytes


 










RMAN> RESTORE DATABASE;

Starting restore at 08-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 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 +DB_DATA/asmdb/datafile/system.259.733408841
restoring datafile 00002 to +DB_DATA/asmdb/datafile/undotbs1.260.733408851
restoring datafile 00003 to +DB_DATA/asmdb/datafile/sysaux.261.733408851
restoring datafile 00004 to +DB_DATA/asmdb/datafile/myts_on_asm.263.737205055
restoring datafile 00005 to +DB_DATA/asmdb/datafile/xdb.264.733412967
restoring datafile 00006 to +DB_DATA/asmdb/datafile/ggs_data.267.734093627
restoring datafile 00007 to +DB_DATA/asmdb/datafile/users.dbf
restoring datafile 00008 to +DB_DATA/asmdb/datafile/example.dbf
channel ORA_DISK_1: reading from backup piece D:\HOME_BACKUP\ASMRMAN\DB_ASMDB_1OLV1KIL_56
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\HOME_BACKUP\ASMRMAN\DB_ASMDB_1OLV1KIL_56 tag=TAG20101208T103252
channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 08-DEC-10
RMAN> RECOVER DATABASE;

Starting recover at 08-DEC-10
using channel ORA_DISK_1

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

Finished recover at 08-DEC-10


 


RMAN> ALTER DATABASE OPEN;

database opened 


 


 


Identify the backups required to complete a database restore



RMAN>  restore database preview;


 


 













Posted by



at


























Labels:
,

















No comments:











Post a Comment













        

      









Subscribe to:
Post Comments (Atom)