Chris Slattery oracle and Miscellaneous tech Blog: January 2013

Monday, January 21, 2013

Oracle Recovery Advisor.

Quite frequently with RMAN you must enter a fairly abstruse set of commands to perform a given action. RMAN's syntax is not easy to remember, and in a stressful time like having to perform a recovery it can be difficult to remember the correct sequence of commands.

Step forward the Recovery Advisor, which can make this job very easy, of diagnosing a problem and then performing the correct action.

Let's assume someone has by accident removed users01.dbf and we try and start the database.

Let's further assume someone has shutdown the database abort;

( mumble mumble some one reminded me that this only works if you're probably archivelog mode and you have a proper backup. )
[oracle@oel6u3m1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 22 00:42:28 2013

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

connected to target database (not started)

RMAN> startup

Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 01/22/2013 00:42:39
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oradata/orcl/users01.dbf'

RMAN>

As expected, of course.

Normally here we would go through some sort of custom restore/recover - is there a less specific fix ?

RMAN> list failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

262 HIGH OPEN 22-JAN-13 One or more non-system datafiles are missing

RMAN>

This is the data recovery advisor. It's correctly figured out that a file is missing.

Does it know how to fix the problem ?

RMAN> advise failure

2> ;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

262 HIGH OPEN 22-JAN-13 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=134 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /home/oracle/oradata/orcl/users01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

------ ------------------

1 Restore and recover datafile 4

Strategy: The repair includes complete media recovery with no data loss

Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2553734102.hm

RMAN>

Yes it does !

we can run the generated script - OR - we can ask RMAN to fix the problem without prompting ...

RMAN> repair failure noprompt

2> ;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2553734102.hm

contents of repair script:

# restore and recover datafile

restore datafile 4;

recover datafile 4;

sql 'alter database datafile 4 online';

executing repair script

Starting restore at 22-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00004

input datafile copy RECID=12 STAMP=805336584 file name=/home/oracle/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_users_8hvq94p6_.dbf

destination for restore of datafile 00004: /home/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00004

output file name=/home/oracle/oradata/orcl/users01.dbf RECID=0 STAMP=0

Finished restore at 22-JAN-13

Starting recover at 22-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00004: /home/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnnd1_INCR_UPDATE_8hvqr9y j_.bkp

channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnnd1_INCR_UPDATE_8hvqr9yj_.bkp tag=IN CR_UPDATE

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 22-JAN-13

sql statement: alter database datafile 4 online

repair failure complete

database opened

RMAN>

In a lot of cases this could be faster than going through tedious steps to find the problem, remember the goal is uptime..

Remember - Data Recovery Advisor - may help you out.

Regards

Chris.

Thursday, January 17, 2013

Cloning an Oracle RDBMS Home

In this case I want to clone just an Oracle 11.2.0.3 Home back to the UAT machine, and then I can run databases against it.

we are following

http://docs.oracle.com/cd/E11882_01/install.112/e24321/app_cloning.htm#sthref782

Step 1. Zip/Tar up the existing home and transfer and unzip it to the destination location.

In this case this is

/u11/app/oracle11t/product/11.2.0.3/dbhome_1/network/admin

So this is our starting point. If we wanted to we could start databases now, but the clone is not finished as the inventory is not done properly, and a load of other steps.

Remove all the .ora files in $OH/network/admin on the cloned home.

Let's establish some Parameters ( needed before proceeding )

ORACLE_BASE=/u11/app/oracle11t
ORACLE_HOME=/u11/app/oracle11t/product/11.2.0.3/dbhome_1/
OSDBA_GROUP=dba
OSOPER_GROUP=
ORACLE_HOME_NAME="CLONED11203"

[oracle11t@vostok bin]$ perl clone.pl ORACLE_HOME=/u11/app/oracle11t/product/11.2.0.3/dbhome_1/ ORACLE_BASE=/u11/app/oracle11t OSDBA_GROUP=dba ORACLE_HOME_NAME="CLONED11203"

./runInstaller -clone -waitForCompletion "ORACLE_HOME=/u11/app/oracle11t/product/11.2.0.3/dbhome_1/" "ORACLE_BASE=/u11/app/oracle11t" "oracle_install_OSDBA=dba" "ORACLE_HOME_NAME=CLONED11203" -silent -noConfig -nowait

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 39687 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-01-17_02-52-40PM. Please wait ...Oracle Universal Installer, Version 11.2.0.2.0 Production

You can find the log of this install session at:

/oracle/oraInventory/logs/cloneActions2013-01-17_02-52-40PM.log

.................................................................................................... 100% Done.

Installation in progress (Thursday, January 17, 2013 2:52:53 PM GMT)

.............................................................................. 78% Done.

Install successful

Linking in progress (Thursday, January 17, 2013 2:52:59 PM GMT)

Link successful

Setup in progress (Thursday, January 17, 2013 2:53:55 PM GMT)

Setup successful

End of install phases.(Thursday, January 17, 2013 2:53:59 PM GMT)

WARNING:

The following configuration scripts need to be executed as the "root" user.

/u11/app/oracle11t/product/11.2.0.3/dbhome_1//root.sh

To execute the configuration scripts:

1. Open a terminal window

2. Log in as "root"

3. Run the scripts

The cloning of CLONED11203 was successful.

Please check '/oracle/oraInventory/logs/cloneActions2013-01-17_02-52-40PM.log' for more details.

dbhome_1]# ./root.sh

Check /u11/app/oracle11t/product/11.2.0.3/dbhome_1//install/root__2013-01-17_14-55-11.log for the output of root script

And that's all there is; the Oracle Home should now be usable, registered, and patched.

Note there is a whole other set of work to clone the database, but it is not that difficult with the current toolses.

Regards

Chris.

How to determine open TCP/IP files/sockets of a Linux/Unix Process

Using lsof, a standard linux tool.

Let's assume we want to see the open TCPV4 sockets ( normal, remote connections ) for a linux process.

We assume we know the PID.

Relevant lsof options :

-i4 ( displays TCPV4 sockets )

-p ( displays files for a particular process )

-a ( uses AND to filter the results. Be careful. If you do not use this then lsof uses OR in the filters and you will get the wrong answer )

OK lets try it out and then refine.

let's look at the CUPS Daemon for something nice and easy

[root]# ps -ef|grep cups

root 5160 1 0 2012 ? 00:00:46 cupsd

OK so now we have something to look at , pid 5160

[root]# lsof -a -i4 -p 5160

COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME

cupsd 5160 root 4u IPv4 14303 TCP localhost.localdomain:ipp (LISTEN)

cupsd 5160 root 7u IPv4 14307 UDP *:ipp

OK so we are seeing hostnames and service names.

We use -n to convert hostnames to IP addresses

[root]# lsof -a -n -i4 -p 5160

COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME

cupsd 5160 root 4u IPv4 14303 TCP 127.0.0.1:ipp (LISTEN)

cupsd 5160 root 7u IPv4 14307 UDP *:ipp

And we use -P to convert network service names to numbers , which for most Oracle applications is much more relevant.

[root]# lsof -a -n -P -i4 -p 5160

COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME

cupsd 5160 root 4u IPv4 14303 TCP 127.0.0.1:631 (LISTEN)

cupsd 5160 root 7u IPv4 14307 UDP *:631

So we can see that CUPS is listening locally on port 631.

Regards,

Chris Slattery.

Wednesday, January 16, 2013

Item invalidation in the Oracle result set cache ( Part 1 )

SQL> select id,type,status,namespace,name from v$result_cache_objects;

3 Dependency Published SH.CUSTOMERS

110 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=104489

109 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=103379

108 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL

2 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL

1 Result Published SQL SELECT DECODE('A','A','1','2') FROM DUAL

0 Result Published SQL SELECT USER FROM DUAL

4 Result Invalid SQL select cust_id from customers

OK all good so what happens if we update the customers table ?

SQL> select cust_first_name,cust_last_name from customers where cust_id=104489;

Branden Kennedy

SQL> update customers set cust_first_name='Brandon' where cust_id=104489;

1 row updated.

OK so now what has happened to the cache ?

SQL> select id,type,status,namespace,name from v$result_cache_objects;

3 Dependency Published SH.CUSTOMERS

110 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=104489

109 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=103379

108 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL

2 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL

1 Result Published SQL SELECT DECODE('A','A','1','2') FROM DUAL

0 Result Published SQL SELECT USER FROM DUAL

4 Result Invalid SQL select cust_id from customers

STILL SHOWING AS CACHE !

Why ?

Let's COMMIT !

SQL> commit;

Commit complete.

SQL> select id,type,status,namespace,name from v$result_cache_objects;

3 Dependency Published SH.CUSTOMERS

108 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL

2 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL

1 Result Published SQL SELECT DECODE('A','A','1','2') FROM DUAL

0 Result Published SQL SELECT USER FROM DUAL

4 Result Invalid SQL select cust_id from customers

109 Result Invalid SQL select cust_first_name,cust_last_name from customers where cust_id=103379

110 Result Invalid SQL select cust_first_name,cust_last_name from customers where cust_id=104489

8 rows selected.

So now committing has caused the dependency on CUSTOMERS to force a flush on those cached items.

Now if we reissue the SQL we can see

SQL> select cust_first_name,cust_last_name from customers where cust_id=104489;

Brandon Kennedy

Execution Plan

----------------------------------------------------------

Plan hash value: 4238351645

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 20 | 2 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("CUST_ID"=104489)

Result Cache Information (identified by operation id):

------------------------------------------------------

1 - column-count=2; dependencies=(SH.CUSTOMERS); attributes=(single-row); name="select cust_first_name,cust_last_name from customers where cust_id=104489"

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

622 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select cust_first_name,cust_last_name from customers where cust_id=104489;

Brandon Kennedy

Execution Plan

----------------------------------------------------------

Plan hash value: 4238351645

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 20 | 2 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("CUST_ID"=104489)

Result Cache Information (identified by operation id):

------------------------------------------------------

1 - column-count=2; dependencies=(SH.CUSTOMERS); attributes=(single-row); name="select cust_first_name,cust_last_name from customers where cust_id=104489"

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

622 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

We have as expected the 3 consistent gets, so it is bypassing the cache.

If we reissue

SQL> /

Brandon Kennedy

Execution Plan

----------------------------------------------------------

Plan hash value: 4238351645

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 20 | 2 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("CUST_ID"=104489)

Result Cache Information (identified by operation id):

------------------------------------------------------

1 - column-count=2; dependencies=(SH.CUSTOMERS); attributes=(single-row); name="select cust_first_name,cust_last_name from customers where cust_id=104489"

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

622 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

So now it's caching again.

Let's look at the result cache info again

SQL> select id,type,status,namespace,name from v$result_cache_objects
2 ;
3 Dependency Published SH.CUSTOMERS
112 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=104489
111 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
108 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
2 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
1 Result Published SQL SELECT DECODE('A','A','1','2') FROM DUAL
0 Result Published SQL SELECT USER FROM DUAL
4 Result Invalid SQL select cust_id from customers
109 Result Invalid SQL select cust_first_name,cust_last_name from customers where cust_id=103379
110 Result Invalid SQL select cust_first_name,cust_last_name from customers where cust_id=104489

10 rows selected.

A new Cache entry ! Note there are now TWO entries for the 104489 lookup, One is invalid and the other is Published ( available for use ! )

So be very careful about selecting from this table and using the information.

This brings me to the last item in this post... dependency checks

V$RESULT_CACHE_DEPENDENCY will tell us what object dependencies are in the cache.

SQL> select * from V$RESULT_CACHE_DEPENDENCY;

112 3 76289

That's not very useful

let's enhance a little

select d.result_id,o.owner,o.object_name,o.object_type from V$RESULT_CACHE_DEPENDENCY D, dba_objects o where d.object_no=o.object_id

SQL> /

RESULT_ID OWNER OBJECT_NAME OBJECT_TYPE

---------- ------------------------------ ------------------------------ -------------------

112 SH CUSTOMERS TABLE

That's a little better... so we can see that our 112 cache from above, depends on the customers table. Which explains why if we commit a change to the customers table the cached results are invalid.

That's enough for now.... More soon

What other things can we look at in the result set cache ?

Well what items are in the result set cache ?

All docs are at

http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_2141.htm#sthref3469

select id,type,status,namespace,name from v$result_cache_objects

ID TYPE STATUS NAMES NAME
---------- ---------- --------- ----- --------------------------------------------------------------------------------------------------------------------------------
3 Dependency Published SH.CUSTOMERS
110 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=104489
109 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=103379
108 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
2 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
1 Result Published SQL SELECT DECODE('A','A','1','2') FROM DUAL
0 Result Published SQL SELECT USER FROM DUAL
4 Result Invalid SQL select cust_id from customers

So we can see some information. We can also change that slightly to see how many times it has been used

1* select id,type,pin_count,scan_count,block_count from v$result_cache_objects

SQL> /

ID TYPE PIN_COUNT SCAN_COUNT BLOCK_COUNT

---------- ---------- ---------- ---------- -----------

3 Dependency 0 0 1

110 Result 0 0 1

109 Result 0 1 1

108 Result 0 0 1

2 Result 0 0 1

1 Result 0 3 1

0 Result 0 1 1

4 Result 0 0 104

8 rows selected.

And this lets us look a little more detail into the usage of the cache.

What if we want more summary information ?

select * from V$RESULT_CACHE_STATISTICS

1* select * from V$RESULT_CACHE_STATISTICS

SQL> /

1 Block Size (Bytes) 1024

2 Block Count Maximum 2080

3 Block Count Current 128

4 Result Size Maximum (Blocks) 104

5 Create Count Success 7

6 Create Count Failure 0

7 Find Count 5

8 Invalidation Count 0

9 Delete Count Invalid 0

10 Delete Count Valid 0

11 Hash Chain Length 1

12 Find Copy Count 5

12 rows selected.

OK All great.

Wednesday, January 9, 2013

Looking at Result Set Cache.

Based on our previous post we can now look at execution plans.
( we are using stock SH.CUSTOMERS table )

( Well actually I've found out there's a bug in DBMS_XPLAN.DISPLAY_CURSORs, it doesn't display result set caching ! back to Autotrace, hopefully that'll be OK !

Annnyway

set long 32000
set linesize 32000
set pagesize 0
set autotrace on
alter system set result_cache_mode=manual;
select cust_first_name,cust_last_name from customers where cust_id=103379;
set autotrace off
-- select cust_first_name,cust_last_name from customers where cust_id=103379;
-- select * from table(dbms_xplan.display_cursor(format=>'ADVANCED'));
quit;

System altered.

Alana Yoon

Execution Plan
----------------------------------------------------------
Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CUST_ID"=103379)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
617 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

[oracle@oel6u3m1 xplain]$

So our nice little plan costs us 3 gets. if we were doing this a bazillion times we would have to probe the BC for the blocks every time. Why not just cache the results of the query ?

et long 32000
set linesize 32000
set pagesize 0
set autotrace on
alter system set result_cache_mode=force;
select cust_first_name,cust_last_name from customers where cust_id=103379;
set autotrace off
quit;

System altered.

Alana Yoon

Execution Plan
----------------------------------------------------------
Plan hash value: 4238351645

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 3d41f60nry3b51vp54prrk2m38 | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("CUST_ID"=103379)

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=2; dependencies=(SH.CUSTOMERS); attributes=(single-row); name="select cust_first_name,cust_last_name from customers where cust_id=103379"

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
617 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

... If we look we can see that as soon as we turn caching on for Results the consistent gets goes to Zero !

so instead of a bazillion hits we have zero.

Now again this is still only server side caching, not front-end... let's look at that in a few blog posts.

Relevant Parameters are

result_cache_max_result integer 5
result_cache_max_size big integer 2080K
result_cache_mode string FORCE
result_cache_remote_expiration integer 0

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams207.htm

Check into these variables; each should be considered carefully

Friday, January 4, 2013

Undocumented parameter to DBMS_XPLAN.DISPLAY_CURSOR

The documentation
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xplan.htm#i996786

tops out at

format=>'ALL'

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id           IN  VARCHAR2  DEFAULT  NULL,
   cursor_child_no  IN  NUMBER    DEFAULT  0, 
   format           IN  VARCHAR2  DEFAULT  'TYPICAL');

What if we wanted MORE ...

First let's see what we get with 'ALL'

set long 32000
set linesize 32000
set pagesize 0
-- set autotrace on
alter system set result_cache_mode=manual;
--select cust_first_name,cust_last_name from customers where cust_id=103379;
-- set autotrace off
select cust_first_name,cust_last_name from customers where cust_id=103379;
select * from table(dbms_xplan.display_cursor(format=>'ALL'));
quit;

System altered.

Alana                Yoon

SQL_ID  dh88td3czp639, child number 0
-------------------------------------
select cust_first_name,cust_last_name from customers where
cust_id=103379

Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / CUSTOMERS@SEL$1
   2 - SEL$1 / CUSTOMERS@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUST_ID"=103379)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "CUST_FIRST_NAME"[VARCHAR2,20], "CUST_LAST_NAME"[VARCHAR2,40]
   2 - "CUSTOMERS".ROWID[ROWID,10]


32 rows selected.

OK all as expected.

What if we changed 'ALL' to 'ADVANCED' ?

set long 32000
set linesize 32000
set pagesize 0
-- set autotrace on
alter system set result_cache_mode=manual;
--select cust_first_name,cust_last_name from customers where cust_id=103379;
-- set autotrace off
select cust_first_name,cust_last_name from customers where cust_id=103379;
select * from table(dbms_xplan.display_cursor(format=>'ADVANCED'));
quit;

System altered.

Alana                Yoon

SQL_ID  dh88td3czp639, child number 0
-------------------------------------
select cust_first_name,cust_last_name from customers where
cust_id=103379

Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / CUSTOMERS@SEL$1
   2 - SEL$1 / CUSTOMERS@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "CUSTOMERS"@"SEL$1" ("CUSTOMERS"."CUST_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUST_ID"=103379)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "CUST_FIRST_NAME"[VARCHAR2,20], "CUST_LAST_NAME"[VARCHAR2,40]
   2 - "CUSTOMERS".ROWID[ROWID,10]


46 rows selected.

! Look at the extra information provided.    Have a good one !

Finding the actual execution plan for a SQL Statement.

Issuing Explain plan does just that - doesn't actually give you what actually happened.

Can we get better ?

set long 32000
set linesize 32000
set autotrace on
select cust_first_name,cust_last_name from customers where cust_id=103379;
quit;

CUST_FIRST_NAME CUST_LAST_NAME
-------------------- ----------------------------------------
Alana Yoon

Execution Plan
----------------------------------------------------------
Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CUST_ID"=103379)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
617 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

This is just autotrace. Lets move up the stack a bit.

Using DBMS_XPLAN

...

If we don't suppply any parameters to DISPLAY_CURSOR then " in which case the plan of the last cursor executed by the session is displayed."

( see http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xplan.htm#i996786)

set long 32000

set linesize 32000

set pagesize 0

-- set autotrace on

select cust_first_name,cust_last_name from customers where cust_id=103379;

-- set autotrace off

select * from table(dbms_xplan.display_cursor);

quit;

Alana Yoon

SQL_ID dh88td3czp639, child number 0

-------------------------------------

select cust_first_name,cust_last_name from customers where

cust_id=103379

Plan hash value: 4238351645

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 20 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("CUST_ID"=103379)

20 rows selected.

This is very useful because it takes the work out of guessing with Autotrace etc.

Thursday, January 3, 2013

Finding the location of the Oracle Alert Log 9i/10g/11g - updated for 12c

In this short post I'll try and see if we can provide a sensible way of establishing the location of the alert log for an oracle database, one that works for 9i, 10g, and 11g.

The Oracle Alert log tells us what major events have happened to the Oracle database.

In times gone by this was a nice text formatted file that most Oracle DBAs knew how to read. In 11g this was changed to an XML formatted file, with the advent of the ADR which is a first stab at automating the management of the large quantity of detailed trace file that Oracle manages.

We won't go into the detail of the ADR here except to note that this is where the alert log hangs out in recent versions of Oracle.

To cope with the backlash of complaints of people who didn't want to read an XML formatted alert log, Oracle continue to write the traditional form of the log as well simultaneously. This is perfect for our purposes.

So this post will try and establish some ways that you can find the alert log.

Some initial problems you may run into

1: The database is down - if it is down, then obviously Oracle cannot tell you what the location is as SQLPLUS is down.
2. There are multiple alert logs - in a RAC database for example there are several alert logs, one for each instance
3. you don't have access to the file location - being a database user is no guarantee of being able to access the alert log file location (even being DBA may not be sufficient ! - depends on the sandbox the sysadmins have you in)

However it breaks down into two basic cases
Database is Up or Database is Down

Database is Up
If the database is Up we can ask it where it is writing the alert Log

Assumptions

User has access to SQLPLUS
User has access to v$parameter
user can issue SHOW PARAMETER

The alert log location we want to see can be found in a variety of ways.

*** UPDATED - FOR 12c this doesn't work any more !!! ***

Read to the end for 12c answer.....

Show Parameter Background_DUMP_DEST

SQL> show parameter background_dump_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

background_dump_dest string /u01/app/oracle/diag/rdbms/rep

o/REPO/trace

SQL>

So that is a location of the oracle alert log, works for all recent versions, 8i,9i,10g,11g

Of course this isn't easy to capture into a variable so why not

SQL> set linesize 300

SQL> column value format a300

SQL> select value from v$parameter where name='background_dump_dest';

VALUE

------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/repo/REPO/trace

SQL>

So that is a location of the oracle alert log, works for all recent versions, 8i,9i,10g,11g

Which is a little easier.

Another way of getting pretty much the same thing is

( This one actually probes the ADR for the trace directory rather than looking at the legacy BDD - but only works in ADR databases - 11g and later )

SQL> select value from v$diag_info where name='Diag Trace';

VALUE

------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/repo/REPO/trace

SQL>

So that is a location of the oracle alert log, works for 11g

These techniques work when the database is started NOMOUNT,MOUNT or OPEN .. but they do not work, of course, when the database is down, as mentioned above.

Database is Down

OK if the db is down how do we find the alert log ?

There are a number of ways to find the alert log.

Simple : search the operating system directories for it.

The alert log will be named alert_SID.log where SID is the SID of the instance ( thus a rac DB may have many alert logs, and a Data Guard pair will have two

if we assume our SID=REPO then by the above convention the alert log will be named alert_REPO.log

We will not go into how to find the SID here, we assume this is well known.

so we know the name of the file how do we search for it.

Linux/Unix

[oracle@oracloud3 /]$ find / -name alert_REPO.log 2>/dev/null

/u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log

[oracle@oracloud3 /]$

We redirect STDERR to null because we don't want to read about all the stuff our Oracle account doesn't have access to.

Windows

Rather than do command line ( dir alert_repo.log /s ) just use the GUI to find the file, alternatively if that's not an option download and use something like Agent Ransack

OK so that's the simplistic method

What about a little more intelligence than that

If we were using an older database we used to be obliged to enter the BACKGROUND_DUMP_DEST into the initialization parameter file ( PFILE )... so we could go and find that and see if it's there.

For this we need ORACLE_HOME envar set.

On Linux/Unix

cd $ORACLE_HOME/dbs

on Windows

cd %ORACLE_HOME%/database

if a pfile is in use we will see it as initSID.ora, so following the above example it will be called initREPO.ora

we can then edit this using our favourite text editor to find the above variable and then we have the value.

OK ... but the current 11g, this doesn't work because

a) newer databases default to using an SPFILE ( binary version of the spfile )

b) The background dump dest variable is defaulted in 11g by the ADR so it does not need to be specified.

So in order to find the alert log we can try and put together a "where Oracle thinks it might put it " from analyzing the SPFILE.

In order to do this we take into account the following assumptions/axioms.

ADR Top level Directory is usually ORACLE_BASE/diag

RDBMS Logs are usually stored in ADR_base/rdbms

This is then broken down into DB_NAME/sid to take account of the fact that there may be a shared ADR directory tree across multiple instances of the same database ( i.e. RAC in some situations )

so we can complete the location of the alert log in 11g (Only )
as

ORACLE_BASE/diag/rdbms/DB_NAME/SID/trace

or
/u01/app/oracle/diag/rdbms/repo/REPO/trace

This assumes a normally put-together database.

If you haven't bothered following an OFA architecture then you will probably be defaulted as per the manual
"Derived from the value of the $ORACLE_BASE environment variable. If $ORACLE_BASE is not set, then derived fromORACLE_BASE as set by the Oracle Universal Installer. If ORACLE_BASE is not set, then $ORACLE_HOME/rdbms/log is the default location "

PS to find DB_NAME and ORACLE_BASE we may need to look at the spfile

this is difficult to view as it is a binary file so let's look at 'strings spfileREPO.ora' to get what we want.

Hope that helps .... several different ways to get what you need.

12c (12.1.0.2) answer : Background_dump_dest no longer useful as they have changed what that parameter is doing

$ORACLE_BASE/diag/rdbms/DB_NAME/SID/trace is still useful to find the alert log

core_dump_dest can be used to get close

core_dump_dest = /u01/app/oracle/diag/rdbms/repo/REPO/cdump and then cd ../trace

It is an ongoing mystery why this is made difficult to find.

Here's a SQL script I wrote to find the alert log on Linux for 11g and 12c databases

set heading off pages 0 trimspool on lines 120 feedback off echo off
-- Chris Slattery November 2015
-- find the Alert Log Location in recent versions of Oracle 11g onwards
with DD (DDV) as (SELECT VALUE DDV FROM V$parameter WHERE NAME='diagnostic_dest'),
LCD (LCDV)as (select LOWER(value) LCDV FROM V$parameter WHERE NAME='db_name'),
INST (INSTV) as (select UPPER(value) INSTV FROM V$parameter WHERE NAME='instance_name')
select * from (select DDV||'/diag/rdbms/'||LCDV||'/'||INSTV||'/trace' from DD,LCD,INST ) where rownum= 1;
quit;

/

to run it

sqlplus -S "/ as sysdba" @get_alert_loc.sql
/u01/app/oracle/diag/rdbms/repo/REPO/trace

ls -lrt /u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log

-rw-r-----. 1 oracle dba 164844 Nov 26 10:09 /u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log

so that's OK

Note will be different on Windows etc but this should cover the majority of cases. Yes I know I am making assumptions with Lower and Upper

Hope this helps people ..