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 !
Like this:
Like Loading...
Related
Data ... as usual 
I would say “sure it is a bug” because it’s otherwise documented as you showed
I seem that auto commit is enabled.
If auto commit is disable, I think It’s an important Bug.
Do you ask Oracle Support that this is issue?
auto commit was disabled. I’ll have to open an SR soon, but I am currently running out of time 🙂
SQL> select * from t1;
OBJECT_ID OBJECT_NAME
———- ——————————————————————————————————————————–
9 I_FILE#_BLOCK#
38 I_OBJ3
45 I_TS1
51 I_CON1
19 IND$
31 CDEF$
6 C_TS#
58 I_CCOL2
24 I_PROXY_DATA$
56 I_CDEF4
10 rows selected.
SQL> update t1 set object_id=object_id+100;
10 rows updated.
SQL> shutdown abort;
Pluggable Database closed.
SQL> alter pluggable database pdborcl open;
alter pluggable database pdborcl open
*
ERROR at line 1:
ORA-01113: file 10 needs media recovery
ORA-01110: data file 10: ‘/u01/app/oracle/oradata/orcl/pdborcl/users01.dbf’
SQL> alter session set container=pdborcl;
Session altered.
SQL> alter database recover;
Database altered.
SQL> alter pluggable database pdborcl open;
Pluggable database altered.
SQL> select * from t1;
OBJECT_ID OBJECT_NAME
———- ——————————————————————————————————————————–
9 I_FILE#_BLOCK#
38 I_OBJ3
45 I_TS1
51 I_CON1
19 IND$
31 CDEF$
6 C_TS#
58 I_CCOL2
24 I_PROXY_DATA$
56 I_CDEF4
10 rows selected.
SQL> select * from v$version;
BANNER CON_ID
——————————————————————————– ———-
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 – 64bit Beta 0
PL/SQL Release 12.2.0.0.1 – Beta 0
CORE 12.2.0.0.0 Beta 0
TNS for Linux: Version 12.2.0.0.0 – Beta 0
Hi Weejar,
Sorry, but I’m not in the beta program for 12.2.
Please refer to Oracle Support with your beta CSI … they would help you to resolve this issue.
hi lauret,
Your case should be a bug in 12.1.01, I test in 12.1.0.2 and 12.2 all had fixed.
ok. Thanks for the feedback … I will check this later