12c ORA-63999: data file suffered media failure

After a datafile missing in a PDB, the instance is terminated.
alert.log file:


Wed Jul 01 11:07:43 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ckpt_7972.trc:
ORA-63999: data file suffered media failure
ORA-01110: data file 10: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE01.DBF'
ORA-01115: IO error reading block from file 10 (block # 1)
ORA-27072: File I/O error
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 11:07:43 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ckpt_7972.trc:
ORA-63999: data file suffered media failure
ORA-01110: data file 10: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE01.DBF'
ORA-01115: IO error reading block from file 10 (block # 1)
ORA-27072: File I/O error
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 11:07:43 2015
System state dump requested by (instance=1, osid=7972 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_diag_9924_20150701110743.trc
Wed Jul 01 11:07:43 2015
USER (ospid: 7972): terminating the instance due to error 63999

C:\Users\oracle12c>rman target /

RMAN> restore tablespace pdb1:example;

RMAN> recover tablespace pdb1:example;


After the CBD restart, some other tablespaces were reported requiring media recovery:

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-01113: file 34 needs media recovery
ORA-01110: data file 34: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT.DBF'

RMAN> recover pluggable database pdb1;

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

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

12c recover from one redolog file lost

SQL> select group#, member, status from v$logfile;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
         3
E:\APP\ORACLE12C\ORADATA\CDB\REDO03.LOG


         2
E:\APP\ORACLE12C\ORADATA\CDB\REDO02.LOG


    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------

         1
E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG


         1
E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
INVALID

         2
E:\APP\ORACLE12C\ORADATA\CDB\REDO02A.LOG
INVALID

         3

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
E:\APP\ORACLE12C\ORADATA\CDB\REDO03A.LOG
INVALID


6 rows selected.
 SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 INACTIVE
         2          2 INACTIVE
         3          2 CURRENT
 

Delete one redolog file from group 1 with SO commands.

SQL> alter system switch logfile;

System altered.

1. Verify what logfile is missing

alert.log content:

Wed Jul 01 08:53:12 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00321: log 1 of thread 1, cannot update log file header
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
ORA-27070: async read/write failed
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 08:53:12 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00313: open failed for members of log group 1 of thread 1
Wed Jul 01 08:53:12 2015
Thread 1 advanced to log sequence 1264 (LGWR switch)
  Current log# 1 seq# 1264 mem# 0: E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG


SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 CURRENT
         2          2 INACTIVE
         3          2 ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 ACTIVE
         2          2 CURRENT
         3          2 ACTIVE

2. Archive the redo log group’s contents if it's the case (database in ARCHIVELOG mode); if you clear this redo log group before archiving it, you must back up the full database to ensure maximum recoverability of the database in case of the loss of a data file.

SQL> alter system archive log group 1;
alter system archive log group 1
*
ERROR at line 1:
ORA-16013: log 1 sequence# 1264 does not need archiving

3. Make the log group INNACTIVE, with a checkpoint. If you try to CLEAR LOGFILE when the logfile group is ACTIVE or CURRENT, following error will be raised:

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance cdb (thread 1)
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG'
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'

Make the log group INNACTIVE, with a checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 INACTIVE
         2          2 CURRENT
         3          2 INACTIVE
 

4. Run command CLEAR LOGFILE GROUP to recreate the missing logfile from the group:

SQL> alter database clear logfile group 1;

Database altered.

After the recreation of redo log member, you might see the following error in alert.log:

Wed Jul 01 09:56:29 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00320: cannot read file header from log 1 of thread 1
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 09:56:29 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00321: log 1 of thread 1, cannot update log file header
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'

In this case you have to recreate the log member:

SQL> alter database drop logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LO
G' ;

Database altered.

SQL> alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG
' to group 1;

Database altered.

Before running the ADD LOGFILE MEMBER command, be sure to delete the dropped logfile member from filesystem with SO commands. If not you will receive following error:

SQL> alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG
' to group 1;
alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG' to
group 1
*
ERROR at line 1:
ORA-00301: error in adding log file 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
- file cannot be created
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

12c - Change PDB global name

12c - PDB to Use Global CDB (ROOT) Temporary Tablespace Functionality is Missing

Oracle 12c documentation:

http://docs.oracle.com/database/121/ADMIN/cdb_plug.htm#ADMIN13556

Storage Limits

The optional STORAGE clause of the CREATE PLUGGABLE DATABASE statement specifies the following limits:

• The amount of storage that can be used by all tablespaces that belong to the PDB
  Use MAXSIZE and a size clause to specify a limit, or set MAXSIZE to UNLIMITED to indicate no limit.
  
• The amount of storage in the default temporary tablespace shared by all PDBs that can be used by sessions connected to the PDB
  Use MAX_SHARED_TEMP_SIZE and a size clause to specify a limit, or set MAX_SHARED_TEMP_SIZE to UNLIMITED to indicate no limit.
  
  
  Although it supposed to be the option to have a PDB with no TEMP tablespace that uses the CDB TEMP tablespace, currently this option is not implemented. 
  
  Available on MOS ID 2004595.1
  

12c bug - PDB spfile parameter showing into v$spparameter with wrong con_id

When changing a parameter in spfile of a pluggable database, the change is visible in V$spparameter view for PDB con_id (5), but with wrong con_id (0). This is a known bug:

Bug 16682595  SPFILE / V$SPPARAMETER problems with PDB

SQL> show con_name

CON_NAME
------------------------------
PDB3 

SQL> alter system set ddl_lock_timeout=30 scope=both;

SQL> select con_id, name, value from v$spparameter where name='ddl_lock_timeout' ;

    CON_ID 
----------
NAME
-----------------------------
VALUE
-----------------------------
         0
ddl_lock_timeout
30

Connected to the PDB we check the value of the parameter and the con_id from v$parameter view: 

SQL> show parameter ddl_lock_timeout;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
ddl_lock_timeout                     integer     30

SQL> select con_id, name, value from v$parameter where name='ddl_lock_timeout';

    CON_ID
----------
NAME
----------------------------
VALUE
----------------------------
         5
ddl_lock_timeout
30

12c - Operation 227 succeeded.

C:\Users\oracle12c>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 27 18:59:36 2015

Copyright (c) 1982, 2013, 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 opt
ions

SQL>
SQL>
SQL>
SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           MOUNTED
PDB3                           MOUNTED

SQL> alter pluggable database all open;

Operation 227 succeeded.

SQL> alter pluggable database pdb1 save state;

Operation 227 succeeded.

SQL> alter pluggable database pdb3 close;

Operation 227 succeeded.

This issue is generated by the client I used (11.2.0.4) to connect to database (12.1.0.2)