Oracle 10g RAC RMAN recovery Example

Oracle 10g RAC RMAN backup Example 

Closed database and delete datafile

[oracle@racnode1 backup]$ srvctl stop database -d racdb[oracle@racnode1 backup]$ asmcmdASMCMD> lsDATADG/FLASHDG/ASMCMD> cd datadgASMCMD> lsRACDB/ASMCMD> cd racdbASMCMD> lsCONTROLFILE/DATAFILE/ONLINELOG/PARAMETERFILE/TEMPFILE/spfileracdb.oraASMCMD> cd PARAMETERFILEASMCMD> lsspfile.268.802541131ASMCMD> cd ..ASMCMD> cd datafileASMCMD> lsEXAMPLE.264.802540751SYSAUX.257.802540615SYSTEM.256.802540615UNDOTBS1.258.802540615UNDOTBS2.265.802540981UNDOTBS3.269.802888323USERS.259.802540615ASMCMD> rm EXAMPLE.264.802540751ASMCMD> rm *You may delete multiple files and/or directories. Are you sure? (y/n) yASMCMD> lsasmcmd: entry 'datafile' does not exist in directory '+datadg/racdb/'ASMCMD> 

Start racnode1, racnode2, racnode3 instance to mount state

[oracle@racnode1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 25 15:47:31 2012Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  599785472 bytesFixed Size    2098112 bytesVariable Size  201329728 bytesDatabase Buffers  390070272 bytesRedo Buffers    6287360 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1: '+DATADG/racdb/datafile/system.256.802540615'SQL> 

[oracle@racnode2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 25 15:47:55 2012Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  599785472 bytesFixed Size    2098112 bytesVariable Size  197135424 bytesDatabase Buffers  394264576 bytesRedo Buffers    6287360 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1: '+DATADG/racdb/datafile/system.256.802540615'SQL> 

[oracle@racnode3 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 25 15:48:11 2012Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  599785472 bytesFixed Size    2098112 bytesVariable Size  163580992 bytesDatabase Buffers  427819008 bytesRedo Buffers    6287360 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1: '+DATADG/racdb/datafile/system.256.802540615'SQL> 

recovery database

[oracle@racnode1 backup]$ rman target /Recovery Manager: Release 10.2.0.5.0 - Production on Tue Dec 25 16:01:30 2012Copyright (c) 1982, 2007, Oracle.  All rights reserved.connected to target database: RACDB (DBID=800157471, not open)RMAN> run{2> restore database;3> recover database;4> alter database open;5> }Starting restore at 2012-12-25 16:01:47using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=861 instance=racdb1 devtype=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: sid=868 instance=racdb2 devtype=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: sid=861 instance=racdb3 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to +DATADG/racdb/datafile/system.269.802972261channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/RACDB_LVL0_20121225_01ntomb3_s1_p1channel ORA_DISK_2: starting datafile backupset restorechannel ORA_DISK_2: specifying datafile(s) to restore from backup setrestoring datafile 00003 to +DATADG/racdb/datafile/sysaux.265.802972263restoring datafile 00004 to +DATADG/racdb/datafile/users.257.802972267restoring datafile 00006 to +DATADG/racdb/datafile/undotbs2.259.802972265channel ORA_DISK_2: reading from backup piece /u01/app/oracle/backup/RACDB_LVL0_20121225_02ntomb3_s2_p1channel ORA_DISK_3: starting datafile backupset restorechannel ORA_DISK_3: specifying datafile(s) to restore from backup setrestoring datafile 00002 to +DATADG/racdb/datafile/undotbs1.256.802972267restoring datafile 00005 to +DATADG/racdb/datafile/example.258.802972265restoring datafile 00007 to +DATADG/racdb/datafile/undotbs3.264.802972269channel ORA_DISK_3: reading from backup piece /u01/app/oracle/backup/RACDB_LVL0_20121225_03ntomb3_s3_p1channel ORA_DISK_3: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_LVL0_20121225_03ntomb3_s3_p1 tag=DB_INC0channel ORA_DISK_3: restore complete, elapsed time: 00:01:24channel ORA_DISK_1: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_LVL0_20121225_01ntomb3_s1_p1 tag=DB_INC0channel ORA_DISK_1: restore complete, elapsed time: 00:01:50channel ORA_DISK_2: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_LVL0_20121225_02ntomb3_s2_p1 tag=DB_INC0channel ORA_DISK_2: restore complete, elapsed time: 00:01:49Finished restore at 2012-12-25 16:03:45Starting recover at 2012-12-25 16:03:45using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3channel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: +DATADG/racdb/datafile/system.269.802972261channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/RACDB_LVL1_20121225_0anton25_s10_p1channel ORA_DISK_2: starting incremental datafile backupset restorechannel ORA_DISK_2: specifying datafile(s) to restore from backup setdestination for restore of datafile 00003: +DATADG/racdb/datafile/sysaux.265.802972263destination for restore of datafile 00004: +DATADG/racdb/datafile/users.257.802972267destination for restore of datafile 00006: +DATADG/racdb/datafile/undotbs2.259.802972265channel ORA_DISK_2: reading from backup piece /u01/app/oracle/backup/RACDB_LVL1_20121225_0bnton25_s11_p1channel ORA_DISK_3: starting incremental datafile backupset restorechannel ORA_DISK_3: specifying datafile(s) to restore from backup setdestination for restore of datafile 00002: +DATADG/racdb/datafile/undotbs1.256.802972267destination for restore of datafile 00005: +DATADG/racdb/datafile/example.258.802972265destination for restore of datafile 00007: +DATADG/racdb/datafile/undotbs3.264.802972269channel ORA_DISK_3: reading from backup piece /u01/app/oracle/backup/RACDB_LVL1_20121225_0cnton25_s12_p1channel ORA_DISK_1: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_LVL1_20121225_0anton25_s10_p1 tag=DB_INC1channel ORA_DISK_1: restore complete, elapsed time: 00:00:00channel ORA_DISK_2: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_LVL1_20121225_0bnton25_s11_p1 tag=DB_INC1channel ORA_DISK_2: restore complete, elapsed time: 00:00:02channel ORA_DISK_3: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_LVL1_20121225_0cnton25_s12_p1 tag=DB_INC1channel ORA_DISK_3: restore complete, elapsed time: 00:00:02starting media recoveryarchive log thread 1 sequence 20 is already on disk as file /u01/app/oracle/arch/1_20_802540708.dbfchannel ORA_DISK_1: starting archive log restore to default destinationchannel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=19channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/RACDB_ARCH_20121225_0dnton3c_s13_p1channel ORA_DISK_2: starting archive log restore to default destinationchannel ORA_DISK_3: starting archive log restore to default destinationchannel ORA_DISK_2: restoring archive logarchive log thread=2 sequence=11channel ORA_DISK_2: reading from backup piece /u01/app/oracle/backup/RACDB_ARCH_20121225_0enton3c_s14_p1channel ORA_DISK_3: restoring archive logarchive log thread=3 sequence=4channel ORA_DISK_3: reading from backup piece /u01/app/oracle/backup/RACDB_ARCH_20121225_0fnton3c_s15_p1channel ORA_DISK_1: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_ARCH_20121225_0dnton3c_s13_p1 tag=ARCH_INC1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01archive log filename=/u01/app/oracle/arch/1_19_802540708.dbf thread=1 sequence=19media recovery complete, elapsed time: 00:00:05channel ORA_DISK_2: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_ARCH_20121225_0enton3c_s14_p1 tag=ARCH_INC1channel ORA_DISK_2: restore complete, elapsed time: 00:00:06channel ORA_DISK_3: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_ARCH_20121225_0fnton3c_s15_p1 tag=ARCH_INC1channel ORA_DISK_3: restore complete, elapsed time: 00:00:06Finished recover at 2012-12-25 16:03:58database openedRMAN> 

validate

[oracle@racnode1 ~]$ srvctl start service -d racdb -s zwc[oracle@racnode1 ~]$ sqlplus /nologSQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 25 16:12:08 2012Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.SQL> conn hr/[e-mail]> conn hr/[e-mail]> conn hr/[e-mail]> conn hr/[e-mail]> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') dt from dual;DT-------------------2012-12-25 16:12:36SQL> 

SQL> !crs_stat -t -vName           Type           R/RA   F/FT   Target    State     Host        ----------------------------------------------------------------------ora.racdb.db   application    0/0    0/1    ONLINE    ONLINE    racnode2    ora....b1.inst application    0/5    0/0    ONLINE    ONLINE    racnode1    ora....b2.inst application    0/5    0/0    ONLINE    ONLINE    racnode2    ora....b3.inst application    0/5    0/0    ONLINE    ONLINE    racnode3    ora.....zwc.cs application    0/0    0/1    ONLINE    ONLINE    racnode1    ora....db1.srv application    0/0    0/0    ONLINE    ONLINE    racnode1    ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    racnode1    ora....E1.lsnr application    0/5    0/0    ONLINE    ONLINE    racnode1    ora....de1.gsd application    0/5    0/0    ONLINE    ONLINE    racnode1    ora....de1.ons application    0/3    0/0    ONLINE    ONLINE    racnode1    ora....de1.vip application    0/0    0/0    ONLINE    ONLINE    racnode1    ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    racnode2    ora....E2.lsnr application    0/5    0/0    ONLINE    ONLINE    racnode2    ora....de2.gsd application    0/5    0/0    ONLINE    ONLINE    racnode2    ora....de2.ons application    0/3    0/0    ONLINE    ONLINE    racnode2    ora....de2.vip application    0/0    0/0    ONLINE    ONLINE    racnode2    ora....SM3.asm application    0/5    0/0    ONLINE    ONLINE    racnode3    ora....E3.lsnr application    0/5    0/0    ONLINE    ONLINE    racnode3    ora....de3.gsd application    0/5    0/0    ONLINE    ONLINE    racnode3    ora....de3.ons application    0/3    0/0    ONLINE    ONLINE    racnode3    ora....de3.vip application    0/0    0/0    ONLINE    ONLINE    racnode3    

SQL> select INSTANCE_NAME,HOST_NAME,VERSION,TO_CHAR(STARTUP_TIME,'YYYY-MM-DD HH24:MI:SS') DT,STATUS,ACTIVE_STATE,INSTANCE_ROLE,DATABASE_STATUS from gv$INSTANCE;INSTANCE_NAME HOST_NAME  VERSION      DT  STATUS       ACTIVE_ST INSTANCE_ROLE    DATABASE_STATUS---------------- ---------- ----------------- ------------------- ------------ --------- ------------------ -----------------racdb2 racnode2   10.2.0.5.0      2012-12-25 16:08:08 OPEN       NORMAL PRIMARY_INSTANCE   ACTIVEracdb1 racnode1   10.2.0.5.0      2012-12-25 16:08:07 OPEN       NORMAL PRIMARY_INSTANCE   ACTIVEracdb3 racnode3   10.2.0.5.0      2012-12-25 16:08:08 OPEN       NORMAL PRIMARY_INSTANCE   ACTIVE