Asif Momen

Yesterday, I received an email update from MOS Hot Topics Email alert regarding a knowledge article which discusses how to connect to an Oracle database whose background processes are killed.

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;
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>        

Wow, one record was updated. But when you try to commit/rollback, the instance gets terminated. And it makes sense as the background processes responsible for carrying out the change have all died.

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:

1. Successfully Connect to Database Even if Background Processes are Killed [ID 166409.1]

The Oracle Linux team has announced the availability of Oracle Enterprise Linux (OL) 6.4. You can download OEL-6.4 from Oracle's EDelivery website (the link is below):

https://edelivery.oracle.com/EPD/Search/handle_go

To learn more about OL-6.4 click on the below link.

http://docs.oracle.com/cd/E37670_01/E39522/html/

Happy downloading!!! 

To be frank, I consider myself novice when it comes to advanced export/import requirements. This is because I don’t deal with these utilities on a day-to-day basis.
A simple requirement came across my desk to export selected tables from a schema based on a common filter.
Requirement: Say, you have 5 tables T1, T2, T3, T4, and T5. All have “ID” as the primary key column and you have to export data from these tables only if it is found in COMMON_TABLE. The COMMON_TABLE stores “ID” to be exported.
Solution: The first place that I look for solution is “Oracle Documentation”. I knew we can filter a table using “QUERY” parameter of Data Pump Export but did not know how to apply it on multiple tables.
The syntax of the QUERY parameter is:
QUERY = [schema.][table_name:] query_clause
If you omit [schema.][table_name:] then the query is applied to all the tables in the export job.
So, here’s my export command:
expdp test/test DIRECTORY=data_pump_dir TABLES=t1,t2,t3,t4,t5 DUMPFILE=test.dmp QUERY=\"WHERE id IN \(SELECT common_table.id FROM common_table\)\"
You may click here to read more about the QUERY parameter of Data Pump Export.
Thanks for reading!!!

Starting with RACcheck 2.2.0, RACcheck support is extended to Oracle Single Instance Databases, Oracle Restart and RAC One Node configurations. 


I downloaded the latest version of the tool and tested it against my play database. Below is a sample RACcheck output from a single instance database:



[oracle@localhost raccheck]$ ./raccheck -v
RACCHECK  VERSION: 2.2.0_20121109 [oracle@localhost raccheck]$  [oracle@localhost raccheck]$  [oracle@localhost raccheck]$ ./raccheck -a
List of running databases 1. testdb 2. None of above
Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1]. . .

Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS
. . . . . . . . . . . . . . . -------------------------------------------------------------------------------------------------------                                                  Oracle Stack Status                            ------------------------------------------------------------------------------------------------------- Host Name  CRS Installed  ASM HOME       RDBMS Installed  CRS UP    ASM UP    RDBMS UP  DB Instance Name ------------------------------------------------------------------------------------------------------- localhost   No              No              Yes             No         No       Yes      testdb   -------------------------------------------------------------------------------------------------------


Refer to the MOS document mentioned below to learn more about RACcheck tool. You may download the latest version of RACcheck from My Oracle Support (MOS).

References: 

RACcheck - RAC Configuration Audit Tool [ID 1268927.1]

Bugs and performance degradation are part of database upgrade stories and we have witnessed yet another post-upgrade bug after upgrading our database from Oracle 10gR2 (10.2.0.5) to Oracle 11gR2 (11.2.0.2). 

Following query (I have simplified the query for the demonstration purpose) was running happily within Oracle 10gR2:


SQL>
SQL> select * from ( select TRUNC(dt,'MM')
  2          from test
  3          group by TRUNC(dt,'mm'));

TRUNC(DT,
---------
01-JAN-13

SQL>


However, the same query started to throws an error (ORA-00979) when executed in Oracle 11gR2 (11.2.0.2): 


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> 


SQL> select * from ( select TRUNC(dt,'MM')
          from test
          group by TRUNC(dt,'mm'));
  2    3  select * from ( select TRUNC(dt,'MM')
                             *
ERROR at line 1:
ORA-00979: not a GROUP BY expression


SQL> 



At this point I normally do a search on My Oracle Support (MOS) to see if I get  some hits pertaining to this problem and found the following bug information:


Bug 11067251 - False ORA-979 with TRUNC(date, format) or ROUND(date, format) - superceded [ID 11067251.8]


Apparently, you hit the bug when you use either TRUNC or ROUND functions in an inline view. Executing the same query with little modification (removing inline view) in 11.2.0.2 was however successful.


SQL> select TRUNC(dt,'MM')
 from test
group by TRUNC(dt,'mm');
  2    3  
TRUNC(DT,
---------
01-JAN-13

SQL> 

The above bug confirms that 11.2.0.2 is affected and proposes following two workarounds: 

1) Use NO_MERGE hint or
2) Disable view merging "_simple_view_merging=false" 

As it was not possible to rewrite the application at this point, so we disabled view merging at the system level. Well, disabling view merging at the system level might appear as a bad choice but I think it is a right decision at this time. We will soon be upgrading this database to 11.2.0.3. This will kill two birds with one stone, a) bug fix and b) upgrading to the latest patch level (who knows what new bugs are waiting for us ???).

References: 

• My Oracle Support:  Bug 11067251 - False ORA-979 with TRUNC(date, format) or ROUND(date, format) - superceded [ID 11067251.8]