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.0Note: 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-14Now 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 optionsAs 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.logWe 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 WRITEConsolation:
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
Regards
