Wednesday, July 1, 2015

12c ORA-01122: database file 8 failed verification check

This are the steps that I took to recover from an datafile error in a pluggable database.

SQL> alter session set container=pdb1;

Session altered.


SQL> startup
ORA-01122: database file 8 failed verification check
ORA-01110: data file 8: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSAUX01.DBF'
ORA-01200: actual file size of 138240 is smaller than correct size of 139520
blocks
 

SQL> select open_mode from v$pdbs;

OPEN_MODE
----------
MOUNTED
 

C:\Users\oracle12c>rman target /

RMAN> validate check logical pluggable database pdb1;

Starting validate at 01-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=270 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00034 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT.DBF

input datafile file number=00010 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE0
1.DBF
input datafile file number=00008 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSAUX01
.DBF
input datafile file number=00007 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSTEM01
.DBF
input datafile file number=00035 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_DEE
PDIVE.DBF
input datafile file number=00033 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_ECM
_DEPOT1.DBF
input datafile file number=00009 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SAMPLE_S
CHEMA_USERS01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:01:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              8136         67840           23430249
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSTEM01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              39856
  Index      0              17041
  Other      0              2807

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    FAILED 0              25182        139520          23429928
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSAUX01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              42004
  Index      0              21220
  Other      206            51114

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9    OK     0              481          640             2131817
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SAMPLE_SCHEMA_USERS01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              15
  Index      0              2
  Other      0              142

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10   OK     0              152794       166320          22539104
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              6849
  Index      0              1209
  Other      0              5468

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
33   OK     0              6108         7680            23427785
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_ECM_DEPOT1.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              578
  Index      0              258
  Other      0              736

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
34   OK     0              335452       454400          23427819
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              54775
  Index      0              21641
  Other      0              42532

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
35   OK     0              25401        25600           17792522
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_DEEPDIVE.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              30
  Index      0              6
  Other      0              163

validate found one or more corrupt blocks
See trace file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ora_5764.trc for de
tails
Finished validate at 01-JUL-15



In alert.log file:

Wed Jul 01 10:05:54 2015
Hex dump of (file 8, block 171) in trace file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ora_5764.trc

Corrupt block relative dba: 0x010000ab (file 8, block 171)
Bad header found during validation
Data in bad block:
 type: 1 format: 2 rdba: 0x00000ab0
 last change scn: 0x8044.000004f4 seq: 0x35 flg: 0x56
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x3
 computed block checksum: 0x0



Using DRA (Data Recovery Advisor) to list, advise and repair failure:

C:\Users\oracle12c>rman target /

connected to target database: CDB (DBID=1992057407)

RMAN> list failure ;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
7961       HIGH     OPEN      01-JUL-15     Datafile 8: 'E:\APP\ORACLE12C\ORADAT
A\CDB\PDB1\SYSAUX01.DBF' contains one or more corrupt blocks

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
7961       HIGH     OPEN      01-JUL-15     Datafile 8: 'E:\APP\ORACLE12C\ORADAT
A\CDB\PDB1\SYSAUX01.DBF' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=297 device type=DISK
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      Recover multiple corrupt blocks in datafile 8
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\hm\reco_3456085453.hm
 

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\hm\reco_3456085453.hm

contents of repair script:
   # block media recovery for multiple blocks
   recover datafile 8 block 171 to 376;

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting recover at 01-JUL-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00008
channel ORA_DISK_1: reading from backup piece E:\APP\ORACLE12C\FAST_RECOVERY_ARE
A\CDB\1FCD0926AC764930A9274C71DAB47DDC\BACKUPSET\2015_06_30\O1_MF_NNND0_20160630
_CDB_BKP_LVL_BS4H4RG8_.BKP
channel ORA_DISK_1: piece handle=E:\APP\ORACLE12C\FAST_RECOVERY_AREA\CDB\1FCD092
6AC764930A9274C71DAB47DDC\BACKUPSET\2015_06_30\O1_MF_NNND0_20160630_CDB_BKP_LVL_
BS4H4RG8_.BKP tag=20160630_CDB_BKP_LVL0
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:01:05

starting media recovery

archived log for thread 1 with sequence 1231 is already on disk as file E:\APP\O
RACLE12C\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2015_06_30\O1_MF_1_1231_BS4M4OD5_.ARC

...

archived log for thread 1 with sequence 1279 is already on disk as file E:\APP\O
RACLE12C\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2015_07_01\O1_MF_1_1279_BS746TMK_.ARC

media recovery complete, elapsed time: 00:00:47
Finished recover at 01-JUL-15
repair failure complete
 

Repair action failed. I restored and the recovery the tablespace.

RMAN> alter pluggable database pdb1 close;
RMAN> restore tablespace pdb1:sysaux;
RMAN> recover tablespace pdb1:sysaux;

RMAN> alter pluggable database pdb1 open;
RMAN> validate check logical pluggable database pdb1; -- check that are no corrupted database blocks in SYSAUX tablespace

No comments:

Post a Comment