Tuesday, 6 August 2019

Rman complete Recovery of datafile 12.1.0.2.0

Scenario Preview:

  •  The database is running in ARCHIVELOG mode
  • One or some non system datafiles  of a user tablespace are lost
  • Recovery Catalog is not being used
Solution :
  • Database will remain in open sate as non-system datafile is corrupted
  • Connect Rman on target database 
  • Validate database
  • Check restore database preview
  • Offline tablespace users
  • RESTORE TABLESPACE users( NOTE: FRA is default so it will automatically restore datafile from recent backup)
  • RECOVER TABLESPACE users
  • Make TABLESPACE users ONLINE
  • validate database
(Example b) - Restoration of datafile while taking backup from particular location.

[oracle@srv1 u01]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 6 17:13:56 2019

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

connected to target database: SMR1 (DBID=3409868754)

RMAN> BACKUP TABLESPACE users TAG 'FULL_USERS';

Starting backup at 06-AUG-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/smr1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-AUG-19
channel ORA_DISK_1: finished piece 1 at 06-AUG-19
piece handle=/u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_06/o1_mf_nnndf_FULL_USERS_gnlt03dj_.bkp tag=FULL_USERS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-AUG-19

RMAN> ALTER SYSTEM SWITCH LOGFILE;

Statement processed

 RMAN> ALTER SYSTEM SWITCH LOGFILE;

Statement processed

RMAN> ALTER SYSTEM SWITCH LOGFILE;

Statement processed

RMAN>

[oracle@srv1 u01]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 6 17:17:16 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT NAME FROM V$DATAFILE WHERE TS# = (SELECT TS# FROM V$TABLESPACE WHERE NAME='USERS');

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/smr1/users01.dbf

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@srv1 u01]$ cd /u01/app/oracle/oradata/smr1/
[oracle@srv1 smr1]$ ll
total 1826732
-rw-r-----. 1 oracle oinstall  10043392 Aug  6 17:17 control01.ctl
-rw-r-----. 1 oracle oinstall  52429312 Aug  6 17:14 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Aug  6 17:17 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Aug  6 17:14 redo03.log
-rw-r-----. 1 oracle oinstall 723525632 Aug  6 17:14 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 828383232 Aug  6 17:14 system01.dbf
-rw-r-----. 1 oracle oinstall 206577664 Aug  6 17:14 temp01.dbf
-rw-r-----. 1 oracle oinstall 141565952 Aug  6 17:14 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Aug  6 17:14 users01.dbf
[oracle@srv1 smr1]$ rm -f users01.dbf
[oracle@srv1 smr1]$ ll
total 1821604
-rw-r-----. 1 oracle oinstall  10043392 Aug  6 17:17 control01.ctl
-rw-r-----. 1 oracle oinstall  52429312 Aug  6 17:14 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Aug  6 17:17 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Aug  6 17:14 redo03.log
-rw-r-----. 1 oracle oinstall 723525632 Aug  6 17:14 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 828383232 Aug  6 17:14 system01.dbf
-rw-r-----. 1 oracle oinstall 206577664 Aug  6 17:14 temp01.dbf
-rw-r-----. 1 oracle oinstall 141565952 Aug  6 17:14 undotbs01.dbf
[oracle@srv1 smr1]$
[oracle@srv1 smr1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 6 17:20:38 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
SMR1      READ WRITE           PRIMARY

SQL> !ps -ef |grep pmon
oracle   11399  7034  0 17:21 pts/0    00:00:00 /bin/bash -c ps -ef |grep pmon
oracle   11402 11399  0 17:21 pts/0    00:00:00 grep pmon
oracle   24707     1  0 17:02 ?        00:00:00 ora_pmon_smr1

SQL>


[oracle@srv1 smr1]$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 6 17:22:34 2019

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

RMAN> connect target /

connected to target database: SMR1 (DBID=3409868754)

RMAN> VALIDATE DATABASE;

Starting validate at 06-AUG-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
RMAN-06169: could not read file header for datafile 6 error reason 5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 08/06/2019 17:22:47
RMAN-06056: could not access datafile 6

RMAN> SELECT NAME FROM V$TABLESPACE WHERE TS# = (SELECT D.TS# FROM V$DATAFILE D WHERE FILE#=6);

NAME
------------------------------
USERS


RMAN> restore database preview;

Starting restore at 06-AUG-19
using channel ORA_DISK_1


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    669.89M    DISK        00:00:38     06-AUG-19
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20190806T171015
        Piece Name: /u01/rmanbkp/SMR1_0au8hs3n_1_1.bckp
  List of Datafiles in backup set 10
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2117442    06-AUG-19 /u01/app/oracle/oradata/smr1/system01.dbf
  4       Full 2117442    06-AUG-19 /u01/app/oracle/oradata/smr1/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    521.73M    DISK        00:00:33     06-AUG-19
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20190806T171015
        Piece Name: /u01/rmanbkp/SMR1_0bu8hs3o_1_1.bckp
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3       Full 2117443    06-AUG-19 /u01/app/oracle/oradata/smr1/sysaux01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13      Full    1.61M      DISK        00:00:00     06-AUG-19
        BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: FULL_USERS
        Piece Name: /u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_06/o1_mf_nnndf_FULL_USERS_gnlt03dj_.bkp
  List of Datafiles in backup set 13
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  6       Full 2121439    06-AUG-19 /u01/app/oracle/oradata/smr1/users01.dbf

List of Archived Log Copies for database with db_unique_name SMR1
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
12      1    18      A 06-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_18_gnlst94b_.arc

13      1    19      A 06-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_19_gnlswqlc_.arc

14      1    20      A 06-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_20_gnlswyx3_.arc

15      1    21      A 06-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_21_gnlswzp7_.arc

16      1    22      A 06-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_22_gnlsx0b7_.arc

17      1    23      A 06-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_23_gnlt0cbd_.arc

18      1    24      A 06-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_24_gnlt0h5g_.arc

19      1    25      A 06-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_25_gnlt0n4p_.arc

20      1    26      A 06-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_26_gnltgrcc_.arc

21      1    27      A 06-AUG-19
        Name: /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_27_gnltgx1s_.arc

RMAN-05119: recovery can not be done to a consistent state.
Media recovery start SCN is 2117442
Recovery must be done beyond SCN 2121439 to clear datafile fuzziness
Finished restore at 06-AUG-19

RMAN> ALTER TABLESPACE users OFFLINE IMMEDIATE;

Statement processed
RMAN> restore tablespace users;

Starting restore at 06-AUG-19
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 00006 to /u01/app/oracle/oradata/smr1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_06/o1_mf_nnndf_FULL_USERS_gnlt03dj_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_06/o1_mf_nnndf_FULL_USERS_gnlt03dj_.bkp tag=FULL_USERS
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-AUG-19

 RMAN> recover tablespace users;

Starting recover at 06-AUG-19
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_23_gnlt0cbd_.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_24_gnlt0h5g_.arc
archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_25_gnlt0n4p_.arc
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_26_gnltgrcc_.arc
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_27_gnltgx1s_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_23_gnlt0cbd_.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_24_gnlt0h5g_.arc thread=1 sequence=24
archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_06/o1_mf_1_25_gnlt0n4p_.arc thread=1 sequence=25
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-AUG-19

RMAN> alter tablespace users online;

Statement processed
RMAN> validate database;

Starting validate at 06-AUG-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/app/oracle/oradata/smr1/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/smr1/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/smr1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/smr1/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:25
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              17831        101120          2122899
  File Name: /u01/app/oracle/oradata/smr1/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              65987
  Index      0              13344
  Other      0              3958

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              24399        88326           2122667
  File Name: /u01/app/oracle/oradata/smr1/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              15953
  Index      0              8310
  Other      0              39658

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              625          17280           2122903
  File Name: /u01/app/oracle/oradata/smr1/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              16655

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              17           642             1670940
  File Name: /u01/app/oracle/oradata/smr1/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              30
  Index      0              5
  Other      0              588

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              612
Finished validate at 06-AUG-19

RMAN>

(B) ######  Restoration  of users datafile while taking backup from articular location ###########

RMAN> BACKUP TABLESPACE users format '/u01/rmanbkp/users%u.bkp' TAG 'users_full_tb';

Starting backup at 07-AUG-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/smr1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-AUG-19
channel ORA_DISK_1: finished piece 1 at 07-AUG-19
piece handle=/u01/rmanbkp/users0lu8junn.bkp tag=USERS_FULL_TB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-AUG-19

RMAN> exit


[oracle@srv1 ~]$ rm -f /u01/app/oracle/oradata/smr1/users01.dbf

RMAN> ALTER TABLESPACE users OFFLINE IMMEDIATE;

Statement processed

RMAN> RESTORE TABLESPACE users;

Starting restore at 07-AUG-19
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 00006 to /u01/app/oracle/oradata/smr1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbkp/users0lu8junn.bkp
channel ORA_DISK_1: piece handle=/u01/rmanbkp/users0lu8junn.bkp tag=USERS_FULL_TB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 07-AUG-19

RMAN> RECOVER TABLESPACE users;

Starting recover at 07-AUG-19
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 36 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_36_gnnwgcho_.arc
archived log for thread 1 with sequence 37 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_37_gnnwgdfv_.arc
archived log for thread 1 with sequence 38 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_38_gnnwgfcn_.arc
archived log for thread 1 with sequence 39 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_39_gnnwggry_.arc
archived log for thread 1 with sequence 40 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_40_gnnwghf6_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_36_gnnwgcho_.arc thread=1 sequence=36
archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_37_gnnwgdfv_.arc thread=1 sequence=37
archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_38_gnnwgfcn_.arc thread=1 sequence=38
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-AUG-19

RMAN> ALTER TABLESPACE users ONLINE;

Statement processed

RMAN>

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)