ADRCI – Automatic Diagnostic Repository Command Interpreter

Notes on using ADRCI to package incidents for Oracle support.

From OS command line export Oracle SID and run adrci to launch the command line tool.

export ORACLE_SID=<SID>

adrci

Some useful commands once in the tool

help
show home
show incident
show problem
ips pack problem <problem_number>

Some example output:

ADRCI: Release 11.2.0.3.0 – Production on Fri Dec 4 11:46:46 2015

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

ADR base = "/u01/app/oracle"

adrci>
adrci> show home

ADR Homes:
diag/rdbms/mydb/INST22

adrci>
adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/mydb/INST22:

*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                                            CREATE_TIME
——————– ———————————————————–                            —————————————-

327230               ORA 7445 [qmcxdAddNmspcAttrFromPfxId()+216]                 2015-12-03 09:33:35.465000 +00:00
330918               ORA 7445 [qmcxdAddNmspcAttrFromPfxId()+216]                 2015-12-04 10:38:12.696000 +00:00

2 rows fetched

adrci>
adrci> show problem

ADR Home = /u01/app/oracle/diag/rdbms/mydb/INST22:

*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
——————– ———————————————————– ——————– —————————————-

1                    ORA 7445 [kolarsAssign()+1408]                              35914                2013-07-03 02:21:55.866000 +01:00

1 rows fetched

adrci>
adrci> ips pack problem 1

Generated package 3 in file /u01/app/oracle/diag/rdbms/mydb/INST22/trace/ORA7445qm_20151204120758_COM_1.zip, mode complete

adrci>

Full Credit to a colleague who wrote this up!

ORA 600s after applying PSU7 for 11.2.0.3

We were seeing the following two ORA 600s after applying PSU7. Technically the issue wasnt related to PSU 7 but more the switchover involved when patching…

SR 3-8646935731 : ORA 600 [ktbdchk1: bad dscn]
SR 3-8664869401 : ORA 600-[FILE:jsks.c LINE:2388 FUNCTION:jsksStartOCICall() ID:OCIKCallPus]

Caused by Bug 8895202 : ITL HAS HIGHER COMMIT SCN THAN BLOCK SCN

The solution was to set the following dynamic parameter on both the primary and standby database:

ALTER SYSTEM SET "_ktb_debug_flags"=8 SCOPE=BOTH;

This parameter is designed to heal blocks having invalid dependent scn’s on switchover operations.

From the traces provided to oracle, I was told that the affected object for this issue was ID 12152331.

Running the following SQL determines the object:

select owner,object_name,object_type,subobject_name,object_id,data_object_id
  from   dba_objects
  where  object_id in (12152331)
  or     data_object_id in (12152331);

The affected blocks were on index SYS.I_SCHEDULER_JOB_RUN_DETAILS on table SCHEDULER$_JOB_RUN_DETAILS.

As well as the dynamic parameter, I also did the following:

ANALYZE TABLE SCHEDULER$_JOB_RUN_DETAILS VALIDATE STRUCTURE online;

ALTER INDEX SYS.I_SCHEDULER_JOB_RUN_DETAILS REBUILD online;

Retrieve Oracle SPID from unix OS PID

The Unix/Linux PID that you see in ‘top’ is visible as SPID in V$PROCESS. Then, you’d have to join V$PROCESS to V$SESSION to get the SID. (Note : PID in V$PROCESS is the Oracle PID, not the OS PID).

--Replace bind variable with OS PID List
select s.sid, s.serial#, s.username,
       to_char(s.logon_time,'DD-MON HH24:MI:SS') logon_time,
       p.pid oraclepid, p.spid "ServerPID", s.process "ClientPID",
       s.program clientprogram, s.module, s.machine, s.osuser,
       s.status, s.last_call_et
from  gv$session s, gv$process p
where p.spid=nvl('&unix_process',' ')
and s.paddr=p.addr
order by s.sidÔ…

Unregister Mview Snapshot Cleanly

Useful SQL to dynamically create unregister mview script for all mviews for a certain site or schema or both!

--Dynamic SQL - Unregister Snapshot info for a particular schema and database
select OWNER, NAME, SNAPSHOT_SITE, 
'execute dbms_snapshot.unregister_snapshot(''' || OWNER || ''',''' || NAME || ''',''' || SNAPSHOT_SITE || ''');' 
from dba_registered_snapshots rs
WHERE rs.OWNER = 'MY_SCHEMA'
and SNAPSHOT_SITE = 'MY_SNAPSHOT_SITE';

This links to my previous posts on this topic:

Find Site holding up mlog & Register Orphaned Mviews

Investigation SQL for Mview and Mlog growth

Also see metalink note “How to Manually Register/Unregister Snapshots” [ID 67371.1]

Investigating high number of sessions/scraping

Some queries to help investigate scraping or max session errors on the database:

--Total Session counts on database per schema
select username, Service_name, count(*) from gv$session
group by username, Service_name
HAVING count(*) > 100
order by 3 desc;ì*
--Total Session counts split by specific instance 
-- Use if only certain instances recieving max session error
select username, inst_id, Service_name, count(*) from gv$session
where inst_id in (1,2)
group by username, inst_id, Service_name
HAVING count(*) > 100
order by 4 desc;

Out machine names have Data Centre references so we are able to further split:

--Split Sessions by Data Centre
SELECT USERNAME, 
CASE MACHINE 
    WHEN '%dc1%' THEN 'DC1'
    ELSE 'DC2'
END AS DC
, count(*)
FROM gv$session
where SERVICE_NAME = 'MYSERVICE_1' and username = 'MYSCHEMA_2'
GROUP BY USERNAME, 
CASE MACHINE 
    WHEN '%dc1%' THEN 'DC1'
    ELSE 'DC2'
END 
ORDER BY 3

Trace Session for Schema on Login

The following trigger starts a session trace whenever a session for a particular schema logs into the database:

CREATE OR REPLACE TRIGGER ON_MY_SCHEMA_LOGIN
AFTER LOGON ON DATABASE
WHEN ( USER = 'MySCHEMA' )
BEGIN
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

Level definitions:

For methods that require tracing levels, the following are valid values:

  • 0 – No trace. Like switching sql_trace off.

  • 2 – The equivalent of regular sql_trace.

  • 4 – The same as 2, but with the addition of bind variable values.

  • 8 – The same as 2, but with the addition of wait events.

  • 12 – The same as 2, but with both bind variable values and wait events.

  • To turn off session level traces that are running:

    ALTER SESSION SET EVENTS '10046 trace name context off';

EXCEPT & INTERSECT in SQL Server – Same as MINUS & UNION in Oracle

Need to run a query to find the distinct different rows in an oracle table compared to a SQL server table. These tables are comparable because the source SQL Server table is replicated to the Oracle Schema.

Note: This example shows the use of Except in a more complex scenario but it can be used in its simplest form to return results from 2 SQL server tables (with the proviso that they are comparable tables)

MSDN – EXCEPT and INTERSECT

This SQL requires a link server to be in place linking to the oracle schema with select permissions.

SELECT Person_ID, Misc_Data_ID
FROM OPENQUERY(ORA_TEMP, 'select * from MYSchema.misc_data')
EXCEPT
SELECT Person_ID, Misc_Data_ID 
FROM misc_data
  WHERE (Person_ID IN 
    (SELECT Person_ID FROM Person 
    WHERE (Customer_ID IN 
        (SELECT Customer_ID FROM Customer WHERE Business_Subset_ID = 123456))))

In the above SQL, EXCEPT finds distinct rows which are in the oracle schema which are NOT in the SQL table. To make sure that we are returning exact rows, i use the primary key on the oracle table for the except comparison.

INTERSECT works in the same way, but finds all common rows between the 2 tables.

SELECT Person_ID, Misc_Data_ID
FROM OPENQUERY(ORA_TEMP, 'select * from MYSchema.misc_data')
INTERSECT
SELECT Person_ID, Misc_Data_ID 
FROM misc_data
  WHERE (Person_ID IN 
    (SELECT Person_ID FROM Person 
    WHERE (Customer_ID IN 
        (SELECT Customer_ID FROM Customer WHERE Business_Subset_ID = 123456))))

EXCEPT is doing the same functionality as the MINUS operation in Oracle, whereas INTERSECT is the same as the UNION operation.