Oracle Database 12c – What happens during CDB startup if one the PDB has a media problem?

Oracle Database 12c – What happens during CDB startup if one the PDB has a media problem?

What this article about:

Prior to Oracle Database 12c Oracle strongly recommended: only one database store on one server. After 12c we can use multiple Pluggable Databases (PDB) in a Container Database (CDB) on one server. Also we can use one PDB for one application. Because Pluggable Databases using  for users data and working as “independent” database. In this article I want answer questions: What happens during CDB startup if one the PDB has a media problem? How to affect of a PDB’s media problem to other PDBs  and CDB( in other words to other applications)?

What situation this can be applied?

If you are using Oracle Database 12c Release 1 (12.1.0.1 without apply PSU1) then your CDB is not open normally after startup, when one of PDB has a media problem.

Overview of Multitenant Architecture

Oracle Multitenant is a new architecture for Oracle Database 12c Enterprise Edition that helps customers to reduce IT costs by simplifying consolidation, provisioning, upgrades, and more. It is supported by a new architecture that allows a container database (CDB) to hold many pluggable databases (PDBs). The many PDBs in a single CDB share its memory and background processes, letting you operate many more PDBs on a particular platform than you can single databases that use the old architecture.  This is the same benefit that schema-based consolidation brings. But there are significant barriers to adopting schema-based consolidation, and it causes ongoing operating problems. The new architecture removes these adoption barriers and operating problems.

Multitenant Architecture fully complements other options, including Oracle Real Application Clusters (RAC) and Oracle Active Data Guard. An existing database can be simply adopted, with no change, as a pluggable database; and no changes are needed in the other tiers of the application.

A  PDB can be unplugged from one container database and plugged into another. Alternatively, you can clone one, within the same container database, or from one container database to another. These operations, together with creating a pluggable database, are done with new SQL commands and take just seconds. By consolidating existing databases as pluggable databases, administrators can manage many databases as one.

The investment of time and effort to patch one container database results in patching all of its many pluggable databases. To patch a single pluggable database, you simply unplug/plug to a container database at a different Oracle Database software version.

Oracle Database 12c Resource Manager is extended with specific functionality to instantly control the competition between the pluggable databases within a container database.

Many databases on one server

Prior to Oracle Database 12c Oracle strongly recommended: only one database store on server. After 12c we can use multiple pluggable databases in a container database on one server.

Now emerges questions: What happens on startup of CDB when one of PDB has a media problem?  How to affect of a PDB’s media problem to other PDBs or CDB?

In this article our test environment prmcdb is a Container Database and have two Pluggable Databases in CBD: prmpdb01, prmpdb02

[oracle@oel62-ora12c /]$ export ORACLE_SID=prmcdb 
[oracle@oel62-ora12c /]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Feb 12 12:53:16 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select con_id, cdb, name, open_mode from v$database;

    CON_ID CDB NAME  OPEN_MODE
---------- --- --------- --------------------
  0 YES PRMCDB  READ WRITE

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

    CON_ID NAME      OPEN_MODE
---------- ------------------------------ ----------
  2 PDB$SEED     READ ONLY
  3 PRMPDB01     READ WRITE
  4 PRMPDB02     READ WRITE

SQL> select file#, name from v$datafile;

     FILE#      NAME
---------------------------------------------------------------
  1  /u01/app/oracle/oradata/prmcdb/system01.dbf
  3  /u01/app/oracle/oradata/prmcdb/sysaux01.dbf
  4  /u01/app/oracle/oradata/prmcdb/undotbs01.dbf
  5  /u01/app/oracle/oradata/prmcdb/pdbseed/system01.dbf
  6  /u01/app/oracle/oradata/prmcdb/users01.dbf
  7  /u01/app/oracle/oradata/prmcdb/pdbseed/sysaux01.dbf
  8  /u01/app/oracle/oradata/prmcdb/prmpdb01/system01.dbf
  9  /u01/app/oracle/oradata/prmcdb/prmpdb01/sysaux01.dbf
 10  /u01/app/oracle/oradata/prmcdb/prmpdb01/prmpdb01_users01.dbf
 11  /u01/app/oracle/oradata/prmcdb/prmpdb02/system01.dbf
 12  /u01/app/oracle/oradata/prmcdb/prmpdb02/sysaux01.dbf
 13  /u01/app/oracle/oradata/prmcdb/prmpdb02/prmpdb02_users01.dbf

SQL> select file#, status, error from v$datafile_header;

     FILE# STATUS     ERROR
-----------------------------------------------------------------
  1 ONLINE
  3 ONLINE
  4 ONLINE
  5 ONLINE
  6 ONLINE
  7 ONLINE
  8 ONLINE
  9 ONLINE
 10 ONLINE
 11 ONLINE
 12 ONLINE
 13 ONLINE

12 rows selected.

SQL> select action_time,action,version,bundle_series,comments from dba_registry_history;

ACTION_TIME                       ACTION   VERSION   BUNDLE_SERIES COMMENTS
--------------------------------  ------- ---------  ---------------  ---------------
24-MAY-13 01.20.05.485655000 PM   APPLY   12.1.0.1PSUPatchset 12.1.0.0.0
08-JUL-13 04.12.17.807394000 PM   APPLY   12.1.0.1PSUPatchset 12.1.0.0.0

Note:  As you see from dba_registery_history view our database not patched yet.

Scenario:

We will follow remove a data file of one pluggable database with operating system command and will restart container database. As you know, when we start container database pluggable databases is not opening automatically.  We must use alter pluggable database <pluggable database name> open; or alter pluggable database all open; SQL commands. Before everything we take a full backup of Container Database with Recover Manager (RMAN):

oracle@oel62-ora12c /]$ rman target / 

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 12 12:52:32 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PRMCDB (DBID=2504197888)
RMAN> backup database plus archivelog delete all input;
Starting backup at 12-FEB-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1

...

Finished backup at 12-FEB-14
Starting Control File and SPFILE Autobackup at 12-FEB-14
piece handle=/u01/app/oracle/fra/PRMCDB/autobackup/2014_02_12/o1_mf_s_839336238_9hpfvkm7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-FEB-14

Now I will remove a data file of Pluggable Database prmpdb02.

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

    CON_ID NAME      OPEN_MODE
---------- ------------------------------ ----------
  2 PDB$SEED     READ ONLY
  3 PRMPDB01     READ WRITE
  4 PRMPDB02     READ WRITE

SQL> select file#, name from v$datafile;

     FILE#      NAME
---------------------------------------------------------------
  1  /u01/app/oracle/oradata/prmcdb/system01.dbf
  3  /u01/app/oracle/oradata/prmcdb/sysaux01.dbf
  4  /u01/app/oracle/oradata/prmcdb/undotbs01.dbf
  5  /u01/app/oracle/oradata/prmcdb/pdbseed/system01.dbf
  6  /u01/app/oracle/oradata/prmcdb/users01.dbf
  7  /u01/app/oracle/oradata/prmcdb/pdbseed/sysaux01.dbf
  8  /u01/app/oracle/oradata/prmcdb/prmpdb01/system01.dbf
  9  /u01/app/oracle/oradata/prmcdb/prmpdb01/sysaux01.dbf
 10  /u01/app/oracle/oradata/prmcdb/prmpdb01/prmpdb01_users01.dbf
 11  /u01/app/oracle/oradata/prmcdb/prmpdb02/system01.dbf
 12  /u01/app/oracle/oradata/prmcdb/prmpdb02/sysaux01.dbf
 13  /u01/app/oracle/oradata/prmcdb/prmpdb02/prmpdb02_users01.dbf

SQL> ! rm –fr /u01/app/oracle/oradata/prmcdb/prmpdb02/prmpdb02_users01.dbf
SQL> shutdown immediate;

ORA-03113: end-of-file on communication channel

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

As you see, we need startup Container Database:

[oracle@oel62-ora12c /]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.1.0 Production on Wed Feb 12 13:52:32 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 801701888 bytes

Fixed Size      2293496 bytes
Variable Size    377487624 bytes
Database Buffers   419430400 bytes
Redo Buffers      2490368 bytes
Database mounted.

ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: '/u01/app/oracle/oradata/prmcdb/prmpdb02/prmpdb02_users01.dbf'

What happens? Container Database is not opened? No database is not opened, because data file 13 is don’t found.  Yes we can open container database with offline this data file, then we can open CDB and can open other PDBs. Otherwise we can restore and recover this data file before open databases. I restored removed data file and opened all PDBs again.

 If you apply Patch 17552800 (12.1.0.1.2 Database Patch Set Update released: January 14, 2014), you will not meet with such problem, CDB will open normally.  When you open all PDBs, CDB will skip opening problematic PDB and will open other PDBs.

You can download PSU1 from support.oracle.com.

We downloaded and applied this patch.

SQL> select action_time,action,version,bundle_series,comments from dba_registry_history;

ACTION_TIME                       ACTION   VERSION   BUNDLE_SERIES  COMMENTS
--------------------------------  ------- ---------  ------------- ---------------
24-MAY-13 01.20.05.485655000 PM   APPLY   12.1.0.1PSUPatchset 12.1.0.0.0
08-JUL-13 04.12.17.807394000 PM   APPLY   12.1.0.1PSUPatchset 12.1.0.0.0
12-FEB-14 09.48.05.383369000 AM   APPLY   12.1.0.1 PSUPSU 12.1.0.1.2

SQL> select * from dba_registry_sqlpatch;

PATCH_ID ACTION STATUS ACTION_TIME DESCRIPTION LOGFILE

17552800 APPLY SUCCESS 12-FEB-14 09.49.00.559171000 AM bundle:PSU /u01/app/oracle/product/12.1.0/dbhome/sqlpatch/17552800/17552800_apply_PRMCDB_CDBROOT_2014 Feb12_09_47_49.log

We backed up my database and try my scenario again.

SQL> ! rm -fr /u01/app/oracle/oradata/prmcdb/prmpdb02/prmpdb02_users01.dbf

SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORACLE instance started.
Total System Global Area 801701888 bytes
Fixed Size      2293496 bytes
Variable Size    377487624 bytes
Database Buffers   419430400 bytes
Redo Buffers      2490368 bytes
Database mounted.
Database opened.

Now Container Database opened without any error. Now I will open all Pluggable Databases.

SQL> alter pluggable database all open;
alter pluggable database all open
*

ERROR at line 1:
ORA-01157: cannot identify/lock data file  - see DBWR trace file
SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------- ----------
PDB$SEED      READ ONLY
PRMPDB01      READ WRITE
PRMPDB02      MOUNTED

SQL> select file#, status, error from  v$datafile_header;

FILE# STATUS     ERROR
-----------------------------------------------------------------
  1 ONLINE
  3 ONLINE
  4 ONLINE
  5 ONLINE
  6 ONLINE
  7 ONLINE
  8 ONLINE
  9 ONLINE
 10 ONLINE
 11 ONLINE
 12 ONLINE
 13 ONLINE     FILE NOT FOUND

12 rows selected.

I get error, but other pluggable database opened and now alert log looks as below:

alter pluggable database all open

Wed Feb 12 15:11:15 2014

Errors in file /u01/app/oracle/diag/rdbms/prmcdb/prmcdb/trace/prmcdb_dbw0_11432.trc:

ORA-01157: cannot identify/lock data file 13 - see DBWR trace file

ORA-01110: data file 13: '/u01/app/oracle/oradata/prmcdb/prmpdb02/prmpdb02_users01.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Wed Feb 12 15:11:15 2014

Pdb PRMPDB02 hit error 1157 during open read write and will be closed.

ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local

Wed Feb 12 15:11:15 2014

Errors in file /u01/app/oracle/diag/rdbms/prmcdb/prmcdb/trace/prmcdb_p001_11482.trc:

ORA-01157: cannot identify/lock data file 13 - see DBWR trace file

ORA-01110: data file 13: '/u01/app/oracle/oradata/prmcdb/prmpdb02/prmpdb02_users01.dbf'

Wed Feb 12 15:11:18 2014

Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32

Opening pdb PRMPDB01 (3) with no Resource Manager plan active

Pluggable database PRMPDB01 opened read write

ORA-1157 signalled during: alter pluggable database all open...

Wed Feb 12 15:11:41 2014

Shared IO Pool defaulting to 24MB

Yes other Pluggable Databases opened normally. CDB skipped open prmdb02 (which is data file of this PDB is not found).

We can restore Pluggable Database with RMAN:

[oracle@oel62-ora12c ~]$ export ORACLE_SID=prmcdb
[oracle@oel62-ora12c ~]$ rman target / 

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 12 15:26:42 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PRMCDB (DBID=2504197888)

RMAN> restore pluggable database prmpdb02;

Starting restore at 12-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
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 00011 to /u01/app/oracle/oradata/prmcdb/prmpdb02/system01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/prmcdb/prmpdb02/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/prmcdb/prmpdb02/prmpdb02_users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fra/PRMCDB/F231EDEC22372112E043AA38A8C01F0B/backupset/2014_02_12/ o1_mf_nnndf_TAG20140212T125307_9hpfs0td_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fra/PRMCDB/F231EDEC22372112E043AA38A8C01F0B/backupset/2014_02_12/ o1_mf_nnndf_TAG20140212T125307_9hpfs0td_.bkp tag=TAG20140212T125307
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 12-FEB-14

RMAN> recover pluggable database prmpdb02;

Starting recover at 12-FEB-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:09

Finished recover at 12-FEB-14

RMAN> alter pluggable database prmpdb02 open;
Statement processed

RMAN> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE 
------------------------------ ----------
PDB$SEED                       READ ONLY 
PRMPDB01                       READ WRITE
PRMPDB02                       READ WRITE

Consolation:

As you see, after applied PSU1, don’t affect of media problem on one of PDB to other PDBs.  It means when we using Pluggable Databases for different applications, if any media problem on one of PDB not affect to other PDBs.

Reference

• CDB

Regards