A rapid post to show you a little thing I detect today.
In an oracle pluggable database, syntaxes to control them are :
alter pluggable database open
alter pluggable database open read write
alter pluggable database open read only
alter pluggable database open restrict
alter pluggable database close
alter pluggable database close immediate
But if you are an experienced oracle dba, you usually use STARTUP and SHUTDOWN commands and these ones are still available is a PDB.
To close a PDB, SHUTDOWN and SHUTDOWN IMMEDIATE makes sense, but SHUTDOWN ABORT doesn’t because the transactional layer is managed by the root container. But SHUTDOWN ABORT seems to be functional in a PDB context with a strange behaviour.
[oracle@oel64-12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 10 20:47:08 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> alter session set container=ORCL_PDB;
Session altered.
SQL> alter session set current_schema=HR;
Session altered.
SQL> select * from regions;
REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
SQL> update regions set region_name=region_name||'*';
4 rows updated.
SQL> shutdown abort;
Pluggable Database closed.
So, if you are an Oracle DBA with a little bit of oracle knowledge and if I ask you what will be the content of the REGIONS table, you will answer me that each region_name will not have any * at the end.
But ….
SQL> connect / as sysdba
Connected.
SQL> alter session set container=ORCL_PDB;
Session altered.
SQL> startup
Pluggable Database opened.
SQL> show con_name
CON_NAME
------------------------------
ORCL_PDB
SQL> alter session set current_schema=HR;
Session altered.
SQL> select * from regions;
REGION_ID REGION_NAME
---------- -------------------------
1 Europe*
2 Americas*
3 Asia*
4 Middle East and Africa*
The transaction has been commited even with the shutdown abort command !
Stranger … if you try to do that with a SHUTDOWN IMMEDIATE, the transaction is committed too !
Ok, It’s my fault … I have to write correct statement with the official syntax … let’do it !
[oracle@oel64-12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 10 20:58:41 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> alter session set container=ORCL_PDB;
Session altered.
SQL> alter session set current_schema=HR;
Session altered.
SQL> select * from regions;
REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
SQL> update regions set region_name=region_name||'*';
4 rows updated.
SQL> alter pluggable database close immediate;
Pluggable database altered.
Now the result:
SQL> alter pluggable database orcl_pdb open;
Pluggable database altered.
SQL> alter session set container=orcl_pdb;
Session altered.
SQL> alter session set current_schema=HR;
Session altered.
SQL> select * from regions;
REGION_ID REGION_NAME
---------- -------------------------
1 Europe*
2 Americas*
3 Asia*
4 Middle East and Africa*
Same problem …
Let’s see what’s the official doc say:
IMMEDIATE If you specify the optional IMMEDIATE keyword, then this clause is the PDB equivalent of the SQL*Plus SHUTDOWN command with the immediate mode. Otherwise, the PDB is shut down with the normal mode.
So the pluggable database should have been closed with the immediate behaviour so with a rollback of my transaction, or throw an ORA-01097: cannot shutdown while in a transaction – commit or rollback first … but it’s not the case, worse it acts like an implicit commit.
Fortunately, if you perform the shutdown from another session, the transaction is correctly rolled back.
I don’t know if it’s a bug (I did’nt find anything in MOS) or a feature … but it’s weird !
Data ... as usual 