Oracle … as usual

Oracle by Laurent Leturgez

Oracle 12cR1, Shutdown abort of a PDB seems to perform commit

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 !
Advertisements

7 responses to “Oracle 12cR1, Shutdown abort of a PDB seems to perform commit

  1. Matthias Rogel January 11, 2014 at 9:56 AM

    I would say “sure it is a bug” because it’s otherwise documented as you showed

  2. Youg January 31, 2014 at 4:52 AM

    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?

  3. weejar March 22, 2016 at 2:44 PM

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: