The Momen Blog

Connecting to Oracle Database Even if Background Processes are Killed

noreply@blogger.com (Asif Momen) — Wed, 13 Mar 2013 11:28:00 +0000

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

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:

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

Oracle Linux 6.4 Announced

noreply@blogger.com (Asif Momen) — Thu, 07 Mar 2013 16:15:00 +0000

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

Exporting Multiple Tables on a Common Filter

noreply@blogger.com (Asif Momen) — Mon, 04 Mar 2013 11:59:00 +0000

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

RACcheck for Single Instance Databases

noreply@blogger.com (Asif Momen) — Tue, 12 Feb 2013 07:21:00 +0000

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]$ ./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]

Bug: ORA-00979: not a GROUP BY expression

noreply@blogger.com (Asif Momen) — Wed, 30 Jan 2013 19:51:00 +0000

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]

Online Free Courses from World's Best Universities

noreply@blogger.com (Asif Momen) — Wed, 10 Oct 2012 08:56:00 +0000

Coursera is offering courses free online. The courses are delivered from faculty members of top universities in the world. You may find more information on the courses at:

https://www.coursera.org/

Happy learning !!!

OOW-2012: Oracle 12c New Features

noreply@blogger.com (Asif Momen) — Thu, 04 Oct 2012 19:15:00 +0000

Blogging right from Tom's session "database new features". Here are the new features as they are discussed:

1. Identity Columns

Remember I blogged on this topic earlier.

2. Default on null

A default value is inserted into the column When it's null.

3. Improved defaults - metadata only defaults

Now add new default columns to a table on the fly. 12c does that for you.

4. Increased size limit for Varchar2 and NVarchar2

The column types can be upto 32k.

5. Row Pattern Matching

Efficient SQL functionality for reporting row patterns like identifying V and W patterns for your stock

6. Partitioning Improvements

Online partition movement without using DBMS_REDEFINITION. Efficient global index management during DROP and TRUNCATE partition operation.

7. Hybrid Histograms

Jonathan Lewis spoke on this topic. I think more information on this is available on his blog.

8. Session private statistics for GTTs

Table and index statistics are held private for each session.

9. Temporary UNDO
Undo for temporary tables can now be managed in TEMP. Reduces the amount of undo generated in undo tablespace and redo generation.

10. Pluggable databases

Read Roels blog for this one. He has a good blog post on this

http://roelhartman.blogspot.com/

Oracle RAC 12c: New Features

noreply@blogger.com (Asif Momen) — Wed, 03 Oct 2012 19:32:00 +0000

Blogging live from OOW session on "Enhancements in Oracle RAC". Following are few Oracle RAC 12c new features being discussed in this session as I post:

1. Application Continuity

2. Oracle Flex ASM
With this feature, database instances use remote ASM instances.

3. Oracle ASM Disk Scrubbing
Checks for logical data corruptions and repair them automatically.

4. Enhancements to Policy-based Databases
Actively utilizes different sized servers

5. What - if analysis for server pool management

6. Standardized deployment and patching
Introducing GHS, rapid home provisioning and gold images

7. A new "ghctl" command for better patching

8. Oracle Utility Cluster

9. Dynamic IP Management and name resolution made easy

10. IPv6 Based IP Addresses Support for client connectivity

11. Multi-purpose Installation

12. Oracle installer will run Fix-up scripts & "root.sh" scripts across nodes. You don't have to run the scripts manually on RAC nodes.

Exciting new features !!!

OOW-2012: Very Large Databases (VLDB): Challenges and Opportunities

noreply@blogger.com (Asif Momen) — Tue, 02 Oct 2012 07:56:00 +0000

I would like to thank all the attendees for attending my session "Very Large Databases (VLDB): Challenges and Opportunities". I am glad, the hall was full to it's capacity. :)

You may download the presentation by logging into OOW-2012 schedule builder.

I request all the attendees to kindly rate my session by logging into OOW: Schedule Builder.

See you all !!!

PARALLEL_MIN_TIME_THRESHOLD Contradictions

noreply@blogger.com (Asif Momen) — Tue, 25 Sep 2012 11:39:00 +0000

PARALLEL_MIN_TIME_THRESHOLD initialization parameter controls which statements are candidate for parallelism.

As per MOS, this parameter is set to 10 seconds while Oracle documentation claims that it is set to 30 seconds. Now who's correct ??? Seems like it's a documentation bug.

Below are the links to both Oracle documentation and MOS article:

PARALLEL_MIN_TIME_THRESHOLD from Oracle Documentation
Oracle 11g Release2: new parallel query parameters [ID 1264548.1]

Speaking at Oracle OpenWorld 2012

noreply@blogger.com (Asif Momen) — Mon, 24 Sep 2012 12:59:00 +0000

Well, it's time for Oracle OpenWorld again. This is the place to meet & learn from the industry experts. Glad I am making this year.

This year I will be speaking on my experiences managing Very Large Databases. Following are the details of my session.

Session ID: UGF3662
Session Title: Very Large Databases: Challenges and Opportunities
Venue / Room: Moscone West - 2000
Date and Time: 9/30/12, 10:00 - 11:00

These are the activities on my OOW to-do list:

1) Attend lots of sessions
2) Oracle ACE dinner
3) Blogger meetup
4) Oaktable World

I’m looking forward to seeing you in San Francisco!

Win A Free Copy of Packt's Oracle Database XE 11gR2 Jump Start Guide eBook

noreply@blogger.com (Asif Momen) — Mon, 13 Aug 2012 07:00:00 +0000

I am pleased to announce that Packt Publishing is organizing a giveaway especially for you. All you need to do is just comment below the post and win a free copy of Oracle Database XE 11gR2 Jump Start Guide. Two lucky winners stand a chance to win an e-copy of the book. Keep reading to find out how you can be one of the Lucky One.

Overview of Oracle Database XE 11gR2 Jump Start Guide eBook

Build and manage the Oracle Database 11gR2 XE environment with this fast paced, practical guide. The book helps beginners to install, administer, maintain, tune, backup and upgrade the Oracle Database Express Edition.

Read more about this book and download free Sample Chapter:

http://www.packtpub.com/oracle-database-xe-11gr2-jump-start-guide/book

How to Enter?

All you need to do is head on over to this page and look through the product description of this book and drop a line via the comments below to let us know what interests you the most about these books. It’s that simple.

DeadLine:

The contest will close on 26-AUG-2012. Winners will be contacted by email, so be sure to use your real email address when you comment!

All the best !!!

Book Released: "Oracle Database XE 11gR2 Jump Start Guide"

noreply@blogger.com (Asif Momen) — Thu, 02 Aug 2012 13:59:00 +0000

I am pleased to announce my first book "Oracle Database XE 11gR2 Jump Start Guide" published by Packt Publishers. The book is available in two formats "Print Book" and "ebook".

Please let your friends and colleagues know about the book. Have a look at the contents by following the below link:

http://www.packtpub.com/oracle-database-xe-11gr2-jump-start-guide/book

The book is available for purchase from the publishers website (www.packtpub.com) and other leading consumer websites like Amazon, Barnes and Nobles, Waterstones etc.

Thanks to all my readers who have encouraged me to write this book.

Oracle Database 12c - New Feature: Identity Columns

noreply@blogger.com (Asif Momen) — Wed, 25 Jul 2012 10:20:00 +0000

Well, Oracle Database 12c is not yet available but new features seems to be popping out in MOS. While troubleshooting Oracle Enterprise Manager Cloud Control 12c startup issues I came across Oracle Database 12c new feature called “Identity Columns”.

An Identity Columns is auto-incremented at the time of insertion just like in SQL Server. Going forward, I think you will not use Oracle Sequence anymore to generate unique values instead use Identity Columns.

For more information on this read:

HOW TO CREATE A TABLE WITH APRIMARY KEY USING AN IDENTITY CLAUSE [ID 1472346.1]

Happy reading!!!

Oracle Certified Master (OCM)

noreply@blogger.com (Asif Momen) — Tue, 17 Jul 2012 07:54:00 +0000

Today I received a congratulations email from “Oracle Certification Program” for successfully completing Oracle 10g OCM. Being an OCM is really an accomplishment in a DBA’s career. OCM is the most advanced database credential for an Oracle professional.

I still have to wait for another 2-3 weeks to receive OCM-Kit and to get my profile updated under “Oracle Certified Master Profiles”.

DBUA Fails with ORA-17502 Error

noreply@blogger.com (Asif Momen) — Sun, 24 Jun 2012 11:49:00 +0000

While upgrade four-node Oracle RAC environment from 11.2.0.1 to 11.2.0.3, DBUA started to throw ORA-17502 error while creating a new SPFILE for the upgraded database. Not only did DBUA err out but it also deleted the existing SPFILE.

Below is the screenshot of the error:

In order to complete the upgrade process, we have to manually perform the following:

Re-create the SPFILE,
Complete Post-installation steps (like executing “utlrp.sql”), and
Re-create the Cluster Services

I later found out on MOS that this situation can be avoided by pinning the nodes as shown below:

$GRID_HOME/bin/crsctl pin css -n

To find out whether node(s) is pinned or not:

$GRID_HOME/bin/olsnodes -t –n

So, make sure that you pin the nodes before running DBUA to avoid the above errors.

Modifying SCAN Listener Port

noreply@blogger.com (Asif Momen) — Wed, 30 May 2012 10:30:00 +0000

It’s been awhile since I’ve published a post on my blog. I have so many things to share but not able to find time to share. Hopefully, very soon I will get back to blogging. Here’s a quick post on modifying SCAN listener port.

We have been having archive log corruption issues since we setup Data Guard (sometime last week) on Oracle 11gR2 (11.2.0.2). The physical standby database is receiving the archive log files but unfortunately not able to perform media recovery as the archive logs are in malformed state.

The customer is using CISCO switches between primary and DR. These switches have SQL*Net packet deep packet scanning/inspection enabled by default on 1521 port and this seems to be resulting in failed or corrupt-packets.

Below is an excerpt from the physical standby database alert log:

CORRUPTION DETECTED: In redo blocks starting at block 387073count 2048 for thread 2 sequence 8776
Deleted Oracle managed file +FRA/qtprdb/archivelog/2012_05_30/thread_2_seq_8776.366.784682159

So, this was the motivation behind changing the SCAN listener port and here’s how you do it:

1) Log in as the “grid” user (if you have installed Grid & database under single user “oracle” then use “oracle” user)

2) Make a note of current SCAN listener configuration:

[grid@rac-prod01 ~]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
[grid@rac-prod01 ~]$

3) Modify SCAN listener port to "1528"

srvctl modify scan_listener -p 1528

4) Restart SCAN listener to make the new port effective

srvctl stop scan_listener
srvctl start scan_listener

5) Finally confirm the change

 [grid@rac-prod01 ~]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1528
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1528
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1528
[grid@rac-prod01 ~]$

Sangam 2011: Less than a month now

noreply@blogger.com (Asif Momen) — Wed, 09 Nov 2011 23:37:00 +0000

Sangam11, AIOUG's annual conference is starting in the first week of December-2011. To be precise it's on 9th and 10th of December.

The IT capital of India will be hosting 3rd annual meetup of AIOUG. World renowned experts like Murali Vallath, Arup Nanda, Hemant Chitale are leading the show.

If you are in India then grab this opportunity to learn and network. For agenda and registrations click here.

Results of the Second International NoCOUG SQL Challenge are Out

noreply@blogger.com (Asif Momen) — Sat, 29 Oct 2011 16:36:00 +0000

The Second International NoCOUG SQL Challenge was published on 2/13/11 in the February 2011 issue of the NoCOUG Journal. The challenge was to find the secret message hidden in a seemingly random collection of words.

Andre Araujo, Rob van Wijk, and Ilya Chuhnakov are the winners of this competition. Pythian was the sponsor of the competition and each winner will receive an Amazon Kindle.

The full announcement can be read in the 100th issue of the NoCOUG Journal.

Resolving “library cache pin” Waits

noreply@blogger.com (Asif Momen) — Sun, 14 Aug 2011 07:58:00 +0000

The old traditional export (exp) utility is hanging indefinitely at “exporting system procedural objects and actions” stage of the export.

 
[oracle@node1 ~]$ /orabackup/exp/prtdb/exp_prtdb.sh

Export: Release 11.2.0.1.0 - Production on Sun Aug 14 21:39:03 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit                                          Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,                                          OLAP,
Data Mining and Real Application Tes
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions

Following are the details from V$SESSION view of this session:

SID:				190
SERIAL#:			379
USERNAME:			EXPIMP
STATUS:				ACTIVE
PROGRAM:			exp@node1 (TNS V1-V3)
LAST_CALL_ET:			1363
BLOCKING SESSION STATUS:	NOT IN WAIT
EVENT#:				280
EVENT:				library cache pin
WAIT CLASS:			Concurrency
STATE:				WAITED SHORT TIME
BLOCKING SESSION:

As you can see the session is waiting on “library cache pin” wait event. This means there is at least one other session holding a lock on some stored procedure which “exp” is trying to pin. The “BLOCKSING SESSION” column does not report which session is holding a lock (or for that matter blocking).

I used “oradebug” utility to analyze the situation. Following is an excerpt from the log file generated by “oradebug”:

    Oracle session identified by:
    {
                instance: 1 (prtdb.prtdb1)
                   os id: 24885
              process id: 49, oracle@node1
              session id: 13
        session serial #: 839
    }
    is waiting for 'Streams AQ: waiting for messages in the queue' with wait info:

Below is the information about session “13” from V$SESSION view:

SID:				13
SERIAL#:			841
USERNAME:			DBSNMP
STATUS:				ACTIVE
PROGRAM:			emagent@node1 (TNS V1-V3)
LAST_CALL_ET:			5
BLOCKING SESSION STATUS:	UNKNOWN
EVENT#:				364
EVENT:			Streams AQ: waiting for messages in the queue
WAIT CLASS:		Idle
STATE:			WAITING
BLOCKING SESSION:

The culprit session belongs to “DBSNMP” user and it is waiting on an “Idle” wait event.

It was easy to kill this session as it did not belong to any application. After killing the session (13), export happily started working and things were all normal.

. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table                    DEF$_AQCALL
:
:
:

References:

Note:215858.1 - Interpreting HANGANALYZE trace files to diagnose hanging and performance problems

Note: 175006.1 Steps to generate HANGANALYZE trace files

Note: 402983.1 Master Note: How to diagnose Database Performance

Diagnosing Library Cache Latch Contention: A Real Case Study By Arup Nanda

Confused SRVCTL in Oracle 11g R2

noreply@blogger.com (Asif Momen) — Fri, 12 Aug 2011 08:54:00 +0000

OS: RHEL 5.5 64-bit

DB: Oracle Database 11gR2

Grid: Oracle Clusterware 11gR2

A database “adverten” is running on a Linux (64-bit) box. This database happens to be a two-node Oracle RAC database. The instance names of this database are “adverten1” and ““adverten2” respectively on the two nodes.

I ran the RACcheck tool but unfortunately it does not list this database. However other databases running on this server are listed.

Below is the excerpt from “raccheck” tool:

List of running databases registered in OCR

1. clonedb

2. frisdb

3. All

4. None

Believe me; the instance is there and running.

 
[oracle@node1 raccheck]$ ps -ef|grep pmon
grid      8585     1  0 Aug06 ?        00:00:17 asm_pmon_+ASM1
oracle   12351  8299  0 22:16 pts/7    00:00:00 grep pmon
oracle   21959     1  0 Aug09 ?        00:00:31 ora_pmon_clonedb1
oracle   24222     1  0 Aug09 ?        00:00:19 ora_pmon_adverten1
oracle   29006     1  0 Aug07 ?        00:00:32 ora_pmon_frisdb1
[oracle@node1 raccheck]$

It’s time to debug “raccheck” tool.

“is_rdbms_installed_crs ()” procedure of the “raccheck” script is of our interest for now and below are the statements from where it is actually fetching database names.

It grabs the list of databases using the following:

       $CRS/bin/srvctl config database > $db_list_fil

And checks for database status using the following:

 
crs_db_status=$($CRS/bin/srvctl status database -d $db_list|grep -i $loc	alnode|grep -ic "is running")

Okay, running the first command manually lists my database:

 
[grid@node1 ~]$ $CRS_HOME/bin/srvctl config database
advertence
clonedb
frisdb
[grid@node1 ~]$

If you notice, the database name listed above is “advertence” (10 letter word). The database name from V$DATABASE view is “adjudica” (8 letter word), also instances use “adverten”.

 
SQL> select name from v$database;

NAME
---------
ADJUDICA

SQL>

However, the second command could not locate the database instances:

[grid@node1 ~]$ $CRS_HOME/bin/srvctl status database -d advertence
Instance adverten1 is not running on node node1
Instance adverten2 is not running on node node2
[grid@node1 ~]$

Of course “adverten” is not registered resource. So the following will fail:

 
[grid@abis19 ~]$ $CRS_HOME/bin/srvctl status database -d adverten
PRCD-1120 : The resource for database adverten could not be found.
PRCR-1001 : Resource ora. adverten.db does not exist
[grid@abis19 ~]$

This is the reason “raccheck” is not able to list this database.

What might have happened is the question that needs to be answered.

At the time of database creation, “advertence” was keyed in as the database name. But Oracle created a database by trimming it to 8 characters and reserving 1 character for instance number. But for some reason “advertence” database resource was registered.

Apparently, the “ora. advertence.db” resource registered with the cluster is in “OFFLINE” state.

 
[grid@node1 ~]$ crs_stat  ora. advertence.db
NAME=ora. advertence.db
TYPE=ora.database.type
TARGET=OFFLINE
STATE=OFFLINE

[grid@node1 ~]$

It’s all messy here, but things are working.

I will have to create a Service Request with Oracle Support to resolve this issue for this client of mine.

VirtaThon: My Sessions Schedule

noreply@blogger.com (Asif Momen) — Sun, 17 Jul 2011 06:42:00 +0000

BrainSurface's VirtaThon conference has already started. Today is the second day of the virtual conference. These are a lot of interesting sessions from Oracle Expert's around the world.

I am speaking today and following are the details of my session:

RMAN: Recovery Procedures

Time slot: 17 July 14:30 - 15:25 EDT

Virtual Room: # 200

A detailed session schedule could be found here.

Attend4FREE: 6 Days of Expert+ Speakers & Sessions FOR Oracle, MySql and Java technologies

noreply@blogger.com (Asif Momen) — Thu, 14 Jul 2011 20:00:00 +0000

Attend4Free! 100% Virtual - No Travel Required!

What is VirtaThon?

Attend, participate & learn cutting edge knowledge from recognized domain experts from all over the world: All within the realm of your broadband connection at the largest Independent "Virtual Conference" for the Oracle, Java & MySQL Communities. Aimed at a global audience, VirtaThon was conceived to revolutionize Online Conferencing, focusing on the core goal of facilitating the inexpensive dissemination of expert knowledge to the masses.

Almost everyone of us would like to attend world-class physical conference events but, given these globally tough economic times that we live in, can we afford the costs of attendance, travel, time-off, lodging and such? How about attending a LIVE virtual conference from wherever you choose to be in the world?

The biggest prohibiting reasons in attending physical Professional Conferences translate into various inhibiting factors such as travel, time-off, lodging, not to mention the attendance fees that can easily run into the thousands of dollars. VirtaThon bridges this gap and makes it incredibly easy to attend a 6-day Conference event from the comfort of your PC. All you need is a broadband Internet connect and voilÃ , you are right there: Attending 6 days of cutting-edge expert-level sessions and interacting/benefiting from world renowned experts in the Oracle, Java & MySQL domains.

Keynotes:

With keynotes from Steven Feuerstein, Arup Nanda, Dr. Bert Scalzo, Eddie Awad, Tim Gorman, Jeremy Schneider, David Koelle, Guy Harrison, Tariq Farooq and a 4-hour Gold Star Deep-Dive session from Mike Ault, this a DO-NOT-MISS virtual learning event of a lifetime.

Session Schedule

For a detailed session schedule click here.

How to register for free?

Click here to register.

Hope to see you all there.

Master Notes from Oracle Support

noreply@blogger.com (Asif Momen) — Sat, 02 Jul 2011 14:42:00 +0000

My flight is delayed by 25 minutes, so thought of sharing this post with you all. Below is the list of master notes from Oracle Support. These are the topics that are on my to-do list. I am sure you too would like to read them. Search for “master note” on Oracle Support for a complete list.

11g New Features - Database Core [ID 1226873.1]

Master Note for Diagnosing ORA-4031 [ID 1088239.1]

Master Note for ORA-1555 Errors [ID 1307334.1]

Master Note for Oracle Backup and Recovery [ID 1199803.1]

Master Note for Automatic Storage Management (ASM) [ID 1187723.1]

Master Note of Linux OS Requirements for Database Server [ID 851598.1]

Master Note for Data Guard [ID 1101938.1]

Master Note for Oracle Database Client Installation [ID 1157463.1]

Master Note for the Oracle OLAP Option [ID 1107593.1]

Master Note for Streams Performance Recommendations [ID 335516.1]

Master Note for Handling Oracle Database Corruption Issues [ID 1088018.1]

Master Note for Transparent Data Encryption (TDE) [ID 1228046.1]

Master Note: SQL Query Performance Overview [ID 199083.1]

Master Note: How to diagnose Database Performance - FAQ [ID 402983.1]

Master Note for Query Rewrite [ID 1215173.1]

Master Note for Oracle Flashback Technologies [ID 1138253.1]

Master Note for Oracle Disk Manager [ID 1226653.1]

Master Note for Real Application Clusters (RAC) Oracle Clusterware and Oracle Grid Infrastructure [ID 1096952.1]

Master Note - RDBMS Large Objects (LOBs) [ID 1268771.1]

Master Note for Oracle Database Server Installation [ID 1156586.1]

Master Note for Oracle Database Downgrade [ID 1151427.1]

Master Note for Oracle Recovery Manager (RMAN) [ID 1116484.1]

Master Note for OLTP Compression [ID 1223705.1]

Master Note for Oracle Database Upgrades and Migrations [ID 1152016.1]

Master Note for Partitioning [ID 1312352.1]

Master Note for 11g Diagnosability - ADR and Packaging [ID 1283137.1]

Master Note for Oracle Database Machine and Exadata Storage Server [ID 1187674.1]

Happy reading!!!

Addressing Block corruption that is not part of any segment

noreply@blogger.com (Asif Momen) — Tue, 21 Jun 2011 12:52:00 +0000

Yesterday’s RMAN backup failed with ORA-19566 error reporting block corruption, here’s the error as reported by RMAN:

channel ORA_DISK_1: starting piece 1 at 20-JUN-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =========================================================== 
RMAN-03009: failure of backup command on c1 channel at 20/06/2011 11:55:11
ORA-19566: exceeded limit of 0 corrupt blocks for file xxxxxxxxxx

The next logical step is to run DB Verify (dbv) utility to find out what blocks are corrupt. Dbv confirmed block corruption as shown below:

Corrupt block relative dba: 0x0346c3cf (file 113, block 229845)
Bad check value found during backing up datafile
Data in bad block -
type: 6 format: 2 rdba: 0x0346c3cf

:
:

DBVERIFY - Verification complete

Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
:
:

Below are the OS and database details:

-bash-3.2$ cat /etc/*-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
-bash-3.2$
-bash-3.2$ uname -r
2.6.18-194.el5
-bash-3.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>

Note that Block 229845 is corrupt in datafile 113. The next step in the process is to identify the object by querying DBA_EXTENTS view

SQL> select segment_name, segment_type, owner
  2  from dba_extents
  3  where file_id = 113
  4  and 229845 between block_id and block_id + blocks -1;

no rows selected

SQL>

Apparently, this block is not associated with any of the segments in the database. The next question to ask is where this corrupt block lies in the data file. Is it in between the allocated blocks after the HWM?

SQL> select max(block_id) from dba_extents where file_id = 113;

MAX(BLOCK_ID)
-------------
       182520

SQL>

So, the corrupt block is beyond the maximum allocated block. Think of the data file as shown below (blue=used data, white=empty blocks, red=corrupt block).

The easiest solution is to shrink the datafile below the corrupt block, this way we drop the corrupt block off the datafile.

Identify the datafile size:

SQL> select bytes/1024/1024 size_mb  from dba_data_files where file_id = 113;

   SIZE_MB
----------
      2048

Identify the location of the maximum block in the datafile by multiplying it with the blocksize (8K):

SQL>  select max(BLOCK_ID) * 8/1024 size_mb from dba_extents where file_id = 113;

   SIZE_MB
----------
 1425.9375

SQL>

Also, find out the location of the corrupt block in the datafile:

SQL> select 229845 * 8/1024 size_mb from dual;

   SIZE_MB
----------
1795.66406

SQL>

Resize the datafile beyond the maximum allocated data block and below the corrupt block:

SQL> alter database datafile 113 resize 1450m;

Database altered.

SQL>

After resizing the datafile run DBV utility to check for corrupt blocks:

DBVERIFY - Verification complete

Total Pages Examined         : 188160
Total Pages Processed (Data) : 62277
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 71630
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 9337
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 21867
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 23049
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

There are no corrupt blocks in the datafile. Happy ending. 

But it wouldn’t have been this easy if the corrupt block were somewhere in between the allocated blocks. This solution will not be helpful in that case. Refer to following note from Oracle Support to address this issue:

How to Format Corrupted Block Not Part of Any Segment [ID 336133.1]