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
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
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
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
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