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

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 Update

My 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
Location: Baku, Azerbaijan

Comments

Post a Comment

Popular posts from this blog

Oracle Data Guard 11g - How to safely remove a Data Guard Broker configuration?

Image
When you need safely remove broker-managed Data Guard Configuration then, oracle recommend following steps… You know dg_broker_start parameter is TRUE and data guard configuration information contains in broker configuration files dg_broker_config_file1, dg_broker_config_file2 with broker-managed Data Guard Configuration. Following steps I use on my test machines witch is running on Oracle Virtual Box. I captures video on this machines, too. Primary and Standby Database running on Oracle Linux Server 6.2 and installed Oracle Database 11g Release 2 (11.2.0.3). DG – is my breoker-managed Data Guard configuration. admdb is primary database, stldb – is standby database. DGMGRL> show configuration Configuration - dg Protection Mode: MaxPerformance Databases: admdb - Primary database stldb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS Archive log destinations like as SQL> show parameter log_archive_dest_ NAME
Read more

Oracle Database 18c - Install On-Premises for Linux

Image
Oracle Database 18c was available on Oracle Cloud. Now everybody can download Oracle Database 18c On-Premises for Linux ( LINUX.X64_180000_db_home.zip ) Oracle change a little on installation file. OUI does not copy files to Oracle Home You just extract the zip file to Oracle Home directly and OUI only linking binaries. I installed Oracle Database 18c on Linux 7.1. Before installation, I set Oracle environments such as (ORACLE_HOME, ORACLE_BASE). You can add these settings in oracle user .bash_profile. You can watch my little installation video: You can also read all about the new features in the 18c documentation Best Regards Mahir M. Quluzade Oracle ACE
Read more

Cluvfy error : Framework setup check failed on all the nodes

Hi As you know Oracle recommends before installation Oracle Real Application Clusters 11g Release 2 run Cluster Verification utility with stage previous and post installation cluster ware or database. Cluster verification utility help you for getting error before and after installation Oracle Grid Infrastructure and Real Application Clusters Database. I getting following error : [grid@oel62-rac1 grid]$ ./runcluvfy.sh stage -pre crsinst -n oel62-rac1,oel62-rac2 –verbose ….. Checking user equivalence... Check: User equivalence for user "grid"   Node Name                             Status                    ------------------------------------  ------------------------   oel62-rac2                            passed                    oel62-rac1                            passed               Result: User equivalence check passed for user "grid" Verifying node connectivity Version of exectask could not be retrieved from node &q
Read more