I bet every DBA must have encountered this situation at least once. When I am in this situation, I normally use "shutdown abort" to stop the database and then proceed with normal startup.
After receiving the email, I thought of reproducing the same. My database (TGTDB) is 11.2.0.3 running on RHEL-5.5. The goal is to kill all Oracle background process and try to connect to the database.
Of course you don't want to test this in your production databases.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL>
Below is the list of background processes for my test database "TGTDB":
[oracle@ogg2 ~]$ ps -ef|grep TGTDB
oracle 8249 1 0 01:35 ? 00:00:00 ora_pmon_TGTDB
oracle 8251 1 0 01:35 ? 00:00:00 ora_psp0_TGTDB
oracle 8253 1 0 01:35 ? 00:00:00 ora_vktm_TGTDB
oracle 8257 1 0 01:35 ? 00:00:00 ora_gen0_TGTDB
oracle 8259 1 0 01:35 ? 00:00:00 ora_diag_TGTDB
oracle 8261 1 0 01:35 ? 00:00:00 ora_dbrm_TGTDB
oracle 8263 1 0 01:35 ? 00:00:00 ora_dia0_TGTDB
oracle 8265 1 6 01:35 ? 00:00:02 ora_mman_TGTDB
oracle 8267 1 0 01:35 ? 00:00:00 ora_dbw0_TGTDB
oracle 8269 1 1 01:35 ? 00:00:00 ora_lgwr_TGTDB
oracle 8271 1 0 01:36 ? 00:00:00 ora_ckpt_TGTDB
oracle 8273 1 0 01:36 ? 00:00:00 ora_smon_TGTDB
oracle 8275 1 0 01:36 ? 00:00:00 ora_reco_TGTDB
oracle 8277 1 1 01:36 ? 00:00:00 ora_mmon_TGTDB
oracle 8279 1 0 01:36 ? 00:00:00 ora_mmnl_TGTDB
oracle 8281 1 0 01:36 ? 00:00:00 ora_d000_TGTDB
oracle 8283 1 0 01:36 ? 00:00:00 ora_s000_TGTDB
oracle 8319 1 0 01:36 ? 00:00:00 ora_p000_TGTDB
oracle 8321 1 0 01:36 ? 00:00:00 ora_p001_TGTDB
oracle 8333 1 0 01:36 ? 00:00:00 ora_arc0_TGTDB
oracle 8344 1 1 01:36 ? 00:00:00 ora_arc1_TGTDB
oracle 8346 1 0 01:36 ? 00:00:00 ora_arc2_TGTDB
oracle 8348 1 0 01:36 ? 00:00:00 ora_arc3_TGTDB
oracle 8351 1 0 01:36 ? 00:00:00 ora_qmnc_TGTDB
oracle 8366 1 0 01:36 ? 00:00:00 ora_cjq0_TGTDB
oracle 8368 1 0 01:36 ? 00:00:00 ora_vkrm_TGTDB
oracle 8370 1 0 01:36 ? 00:00:00 ora_j000_TGTDB
oracle 8376 1 0 01:36 ? 00:00:00 ora_q000_TGTDB
oracle 8378 1 0 01:36 ? 00:00:00 ora_q001_TGTDB
oracle 8402 4494 0 01:36 pts/1 00:00:00 grep TGTDB
[oracle@ogg2 ~]$
Let us kill all these processes at once as shown below:
[oracle@ogg2 ~]$ kill -9 `ps -ef|grep TGTDB | awk '{print ($2)}'`
bash: kill: (8476) - No such process
[oracle@ogg2 ~]$
Make sure no processes are running for our database:
[oracle@ogg2 ~]$ ps -ef|grep TGTDB
oracle 8520 4494 0 01:37 pts/1 00:00:00 grep TGTDB
[oracle@ogg2 ~]$
Now, try to connect to the database using SQL*Plus:
[oracle@ogg2 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 13 01:38:12 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Voila, I am connected. Not only you get connected to the database but you can query V$*, DBA* and other application schema views/tables. Let's give a try:
SQL> select name from v$database;
Voila, I am connected. Not only you get connected to the database but you can query V$*, DBA* and other application schema views/tables. Let's give a try:
SQL> select name from v$database;
NAME
---------
TGTDB
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
TEST_TS
6 rows selected.
SQL>
SQL> select count(*) from dba_tables;
COUNT(*)
----------
2787
SQL>
SQL> select count(*) from test.emp;
COUNT(*)
----------
3333
SQL>
Let us try to update a record.
SQL>
SQL> update test.emp set ename = 'test' where eno = 2;
1 row updated.
SQL>
SQL>
SQL> commit;
commit
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8917
Session ID: 87 Serial number: 7
SQL>
Following is the error message recorded in the database alert log:
Wed Mar 13 01:41:44 2013
USER (ospid: 8917): terminating the instance due to error 472
Instance terminated by USER, pid = 8917
The user (client) session was able to retrieve data from the database as the shared memory was still available and the client session does not need background processes for this task.
Below mentioned MOS article discusses on how to identify and kill the shared memory segment(s) allocated to "oracle" user through UNIX/Linux commands.
References:
- Successfully Connect to Database Even if Background Processes are Killed [ID 166409.1]
I think it would be more accurate to write "Connecting to Oracle INSTANCE..."
ReplyDeleteWell, technically that's correct but in real world, database and instance are used interchangeably when it is a single instance database.
ReplyDelete