LATEST TOPICS

Oracle Database: You can not shut me down !!

Today, I have come across a strange behavior of Oracle Database.

I was trying to SHUTDOWN my database with the IMMEDIATE option and the database came back to me saying I can not shut it down.

Here is the error that it was showing.

SQL> shutdown immediate
ORA-01097: cannot shutdown while in a transaction - commit or rollback first

I was bit confused about the error at the first look.

So, basically Oracle is trying to tell me that there is a ongoing uncommitted transaction in the database. To be able to shutdown the database either commit or rollback the ongoing uncommitted transaction and then issue the SHUTDOWN command.

But that’s not my job. I have asked you (database) to rollback any ongoing uncommitted transaction and shut yourself (database) down. Why are you (database) not doing your job and instead asking me to do your (database) job.

Here is what official doc says about SHUTDOWN IMMEDIATE command.

Shutting Down with the IMMEDIATE Clause

Use immediate database shutdown only in the following situations:

To initiate an automated and unattended backup

When a power shutdown is going to occur soon

When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off

To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE clause:

SHUTDOWN IMMEDIATE
Immediate database shutdown proceeds with the following conditions:

No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.

Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)

Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.

The next startup of the database will not require any instance recovery procedures.

As per the documentation, any uncommitted transactions are rolled back as part of SHUTDOWN IMMEDIATE command. Then, why am I still getting this error.

Now lets find out why…..

What if I am trying to initiate the SHUTDOWN from the session to which the uncommitted transaction belongs to??

Yes, you are guessing it correct…. Oracle can’t help you (including me) there if you initiate the SHUTDOWN IMMEDIATE command within a session to which a uncommitted database transaction belongs to.

In my case, the uncommitted transaction belongs to the session initiating SHUTDOWN command.

SQL> show user
USER is "SYS"

---- My Current session that initiated SHUTDOWN IMMEDIATE
----
SQL> SELECT sys_context('USERENV', 'SID') SID  FROM DUAL;

SID
--------------------
22


----  uncommitted transaction belonging to my current session.
----
SQL> select t.start_time,s.sid,s.serial#,s.username,s.status,
to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by start_time;  2    3    4    5

START_TIME                  SID    SERIAL# USERNAME             STATUS   LOGON_TIME
-------------------- ---------- ---------- -------------------- -------- ---------------------------
10/27/14 12:43:14            22          5 SYS                  INACTIVE 27/OCT/14 12:41:39

SQL> shutdown immediate
ORA-01097: cannot shutdown while in a transaction - commit or rollback first

Here is what Oracle does for SHUTDOWN IMMEDIATE operation with respect to uncommitted transactions.

Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.

How the rollback of the transaction happens is basically Oracle just kill the server processes belonging to the sessions, which in turn rollback the associated uncommitted transactions. You can see trace entries similar to the following one, when Oracle internally kills and rollback uncommitted transaction as part of SHUTDOWN IMMEDIATE command.

*** 2014-10-27 13:05:17.748
ksukia: Starting kill, flags = 1
ksukia: Attempt 1 to kill process oracle@labserver.home.com (TNS V1-V3), OS id=19746
ksukia: killed 1 out of 1 processes.

Now, when the active transaction belongs to the session that initiated the SHUTDOWN command, it doesn’t rollback them as Oracle can’t disconnect (kill) that session (since that is the session which initiated shutdown operation).

Since Oracle was not able to rollback uncommitted transaction for the session that initiated the SHUTDOWN IMMEDIATE command, it asked you (including me) to do that manually by means of the error ORA-01097.

Lets validate it by a simple simulation.

I will login to database and issue an INSERT statement without commit. From the same session, I will try to shutdown the database.

SQL>  show user
USER is "SYS"

SQL> SELECT sys_context('USERENV', 'SID') SID  FROM DUAL;

SID
--------------------
22

SQL> create table abbas.test_shut (dummy varchar(10));

Table created.

SQL> insert into abbas.test_shut values ('junk');

1 row created.

SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction - commit or rollback first
SQL>

As I did not commit the transaction, Oracle was preventing my to shutdown the database from same session.

However, If I initiate the SHUTDOWN IMMEDIATE from another session, it will allow me to shutdown the database by rolling back the above uncommitted transaction itself.

SQL> show user
USER is "SYS"

---- My new session that initiated SHUTDOWN IMMEDIATE
----
SQL> SELECT sys_context('USERENV', 'SID') SID  FROM DUAL;

SID
--------------------
58


---- Session info of uncommitted transaction.
----
SQL> select t.start_time,s.sid,s.serial#,s.username,s.status,
to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by start_time;  2    3    4    5

START_TIME                  SID    SERIAL# USERNAME             STATUS   LOGON_TIME
-------------------- ---------- ---------- -------------------- -------- ---------------------------
10/27/14 12:43:14            22          5 SYS                  INACTIVE 27/OCT/14 12:41:39

--- Lets issue SHUTDOWN command from the new session
---
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

When, I look at the trace file of the session that initiated SHUTDOWN IMMEDIATE command; I could see it killed the server process belonging to the session with uncommitted transaction and which in turn rolled back the transaction.

*** 2014-10-27 13:05:15.347
*** SESSION ID:(58.145) 2014-10-27 13:05:15.347
*** CLIENT ID:() 2014-10-27 13:05:15.347
*** SERVICE NAME:(SYS$USERS) 2014-10-27 13:05:15.347
*** MODULE NAME:(sqlplus@labserver.home.com (TNS V1-V3)) 2014-10-27 13:05:15.347
*** ACTION NAME:() 2014-10-27 13:05:15.347

Stopping background process SMCO

*** 2014-10-27 13:05:15.348
Stopping background process FBDA

*** 2014-10-27 13:05:16.505
kwsbgshms: Shutdown all masters complete

*** 2014-10-27 13:05:16.505
Stopping background process MMNL

*** 2014-10-27 13:05:16.540
Stopping background process MMON

*** 2014-10-27 13:05:17.748
ksukia: Starting kill, flags = 1
ksukia: Attempt 1 to kill process oracle@labserver.home.com (TNS V1-V3), OS id=19746
ksukia: killed 1 out of 1 processes.

Observation:

Though SHUTDOWN IMMEDIATE is meant for performing a clean shutdown by rolling back uncommitted transaction, it can not rollback uncommitted transactions belonging to the session initiating the SHUTDOWN command. Hence, it would be a good option to always initiate SHUTDOWN command from a fresh database session.

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed