During Oracle Day 2014 (February 05, 2014, which was held in Baku/Azerbaijan), I met with my friends and had a conversation about several topics.
I asked to my friend Teymur Hajiyev, what happens during CDB startup if one the PDB has a media problem? He said, CDB must open without a problem. But you need to open PDBs manually. In other words, by default PDBs in mount mode, you need call
alter pluggable databases all open.After Oracle Day, Teymur shared his tests in
here. and faced the same issue. But he is wrote :
If you apply PSU1 on 12c, you will not meet with such problem, CDB will skip opening problematic PDB and will open other PDBs.
I will share my tests on patched database. I applied PSU1 to my database server:
Installation and configuration Patch 17552800 - 12.1.0.1.2 Patch Set UpdateMy test environment as below:
[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 * from dba_registry_history;
ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS
24-MAY-13 01.20.05.485655000 PM APPLY SERVER 12.1.0.1 0 PSU Patchset 12.1.0.0.0
10-FEB-14 06.29.37.412212000 PM APPLY SERVER 12.1.0.1 0 PSU Patchset 12.1.0.0.0
12-FEB-14 09.48.05.383369000 AM APPLY SERVER 12.1.0.1 2 PSU PSU 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_2014Feb12_09_47_49.log
Before everything I take full backup of my database with 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
Backup finished, I follow our scenario in this database
SQL> ! rm -fr /u01/app/oracle/oradata/prmcdb/prmpdb02/prmpdb02_users01.dbf
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
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.
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
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. Trying to get it from Buffer Cache for process 11546.
It means, if we apply patch set update (12.1.0.1.2) on Oracle Database 12c, when media failure occurs on a PDB, after startup CDB will open normally. CDB skip opening crashed PDB and other PDBs will open normally, when we call
alter pluggable database all open. I tried open crashed pluggable database again.
SQL> alter pluggable database prmpdb02 open;
alter pluggable database prmpdb02 open
*
ERROR at line 1:
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'
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.
It mean we must restore Pluggable Database.
[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
Regards
Mahir M. Quluzade