Block Media Recovery - DATA RECOVERY ADVISOR

RCATPDB è il pdb che contiene Rman Cataolog e ha come db registrato il container db CKULAB01 

Creiamo sul pdb PKULAB01 del container CKULAB01 una tabella sotto l'utente "bat".

[oracle@coslab05 PKULAB01]$ sqlplus bat/man@PKULAB01

SQL> create tablespace tbs_bat datafile '/u01/app/oracle/oradata/CKULAB01/PKULAB01/tbs_bat01.dbf' size

 1M autoextend on next 5M maxsize 3M;

SQL> select count(*) from bat.archam;

COUNT(*)
--------------
113664

Creare un data block corruction: aprire il data file "tbs_bat01.df" ed inserire delle stringhe.

# vi /u01/app/oracle/oradata/CKULAB01/PKULAB01/tbs_bat01.dbf

[oracle@coslab05 PKULAB01]$ sqlplus bat/man@PKULAB01
SQL> select count(*) from bat.archam;
select count(*) from bat.archam
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 12, block # 10)
ORA-01110: data file 12:
'/u01/app/oracle/oradata/CKULAB01/PKULAB01/tbs_bat01.dbf'

Ricolleghiamoci al Catalog.

[oracle@coslab05 admin]$ rman target sys/so12mee0@CKULAB01 catalog rcatown/rcatown@RCATPDB
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Mar 26 14:59:02 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
conected to target database: CKULAB01 (DBID=2499174279)
connected to recovery catalog database
recovery catalog schema release 19.03.00.00. is newer than RMAN release

RMAN> list failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
90882 HIGH OPEN 26-MAR-21 One or more non-system datafiles are corrupt

RMAN> validate datafile 12;

Starting validate at 26-MAR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
RMAN-06169: could not read file header for datafile 12 error reason 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 03/26/2021 15:01:00
RMAN-06056: could not access datafile 12

RMAN> recover datafile 12 block 10;
Starting recover at 26-MAR-21using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-MAR-21

Verifica:

RMAN> list failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
90882 HIGH OPEN 26-MAR-21 One or more non-system datafiles are corrupt

Riprovo a fare la query:

SQL> select count(*) from bat.archam;
ORA-01578: ORACLE data block corrupted (file # 12, block # 10)
ORA-01110: data file 12:
'/u01/app/oracle/oradata/CKULAB01/PKULAB01/tbs_bat01.dbf'

Il file è ancora corrotto, quindi conviene usare una delle 2 strategie seguenti:

1)  DATA RECOVERY ADVISOR che è un metodo automatico.

RMAN> ADVISE FAILURE;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
90882 HIGH OPEN 26-MAR-21 One or more non-system datafiles are corrupt
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 12
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ckulab01/CKULAB01/hm/reco_1489665355.hm

RMAN> REPAIR FAILURE;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ckulab01/CKULAB01/hm/reco_1489665355.hm
contents of repair script:
# restore and recover datafile
sql 'PKULAB01' 'alter database datafile 12 offline';
restore ( datafile 12 );
recover datafile 12;
sql 'PKULAB01' 'alter database datafile 12 online';
Do you really want to execute the above repair (enter YES or NO)?

Scrivere yes.

Verifiche:
RMAN> list failure;
Database Role: PRIMARY
no failures found that match specification

SQL> select count(*) from archam;
COUNT(*)
----------
113664

2) Metodo manuale:

SQL> alter session set container=PKULAB01;

SQL> alter database datafile 12 offline;

RMAN> restore datafile 12;
Starting restore at 26-MAR-21
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 00012 to u01/app/oracle/oradata/CKULAB01/PKULAB01/tbs_bat01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CKULAB01/CKULAB01/9D6F617A162965B5E053A4F5A00AF674/backupset/2021_03_26/o1_mf_nnndf_TAG20210326T124112_j5vl3x9f_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CKULAB01/CKULAB01/9D6F617A162965B5E053A4F5A00AF674/backupset/2021_03_26/o1_mf_nnndf_TAG20210326T124112_j5vl3x9f_.bkp tag=TAG20210326T124112
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-MAR-21

RMAN> recover datafile 12;
Starting recover at 26-MAR-21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-MAR-21

SQL> alter database datafile 12 online;
Database altered.

Verifiche:

SQL> select count(*) from bat.archam;
COUNT(*)
----------
113664

RMAN> list failure;
Database Role: PRIMARY
no failures found that match specification