Resource is in UNKNOWN state and srvctl Cannot Start/Stop the Resource

Oracle RAC Service Unknow State

An Oracle RAC Service is in UNKNOWN state on instance 1 and it is mpossible to stop or start it with srvctl.

crs_stat -u ora.DBName.My-ServiceName.DBName1.srv

NAME=ora.DBName.My-ServiceName.DBName1.srv
TYPE=application
TARGET=ONLINE
STATE=UNKNOWN on Host1

 

Solution:

The UNKNOWN state can often be resolved by bringing the resource offline using crs_stop.

If the resource is not in Unknown state: Use srvctl to stop it and do not use crs_stop (as mentionned in note Oracle support note 845709.1 there’s a risk of corruption of the OCR).

Make sure that you have a recent backup of your OCR:
ocrconfig -showbackup

Restore the instance resource to OFFLINE with this command:
crs_stop ora.DBName.My-ServiceName.DBName1.srv

Verify the status is now OFFLINE:
crs_stat -u ora.DBName.My-ServiceName.DBName1.srv

NAME=ora.DBName.My-ServiceName.DBName1.srv
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE

Bring the service online with srvctl:
srvctl start service -d DBName -i DBName1 -s My-ServiceName

Verify that the service is ONLINE:
crs_stat -u ora.DBName.My-ServiceName.DBName1.srv

NAME=ora.DBName.My-ServiceName.DBName1.srv
TYPE=application
TARGET=ONLINE
STATE=ONLINE on Host1

Source : CRS: Resource in UNKNOWN state and srvctl Cannot Start/Stop the Resource (Doc ID 845709.1)

 

 

How to flashback a procedure, function or package?

If you accidentally dropped or modified a procedure, function or package. You can rollback to another version in a time.
If Flashback is disabled on your database, the rollback time has to be short in time because you will use your undo tablespace

Find the object:
select object_id from dba_objects
where object_name=’MY_OBJECT_NAME’ and owner=’OWNER_OF_THE_OBJECT’;
==> 1010952

Flashback table:
select SOURCE from sys.source$ as of timestamp
to_timestamp(’31-Mar-2017 10:00:52′,’DD-Mon-YYYY hh24:MI:SS’)
where obj#=1010952 ;

List of active transaction per users

If I have a lock problem (for example a select for update not committed), I can start with the list of active transactions per users:

select s.sid
      ,s.serial#
      ,s.username
      ,s.machine
      ,s.status
      ,s.lockwait
      ,t.used_ublk
      ,t.used_urec
      ,t.start_time
from gv$transaction t
inner join gv$session s on t.addr = s.taddr;

Trace a specific ORA- error

How to trace a specific ORA- error that is raised and can be reproduced?
We want to find the complete Oracle stack to understand the origin of this error.

For example, I want to understand why datapump expdp/impdp raises:
ORA-01422: exact fetch returns more than requested number of rows

This time, the alert file is not very helpful.

To determine the root cause, event 1422 can be set as follows:

connect /as sysdba 
alter system set events '1422 trace name ERRORSTACK level 3'; 

[…reproduce the issue…]

The event can be turned off again using:

connect /as sysdba

alter system set events '1422 trace name ERRORSTACK off';

In the event 1422 trace file, below sql query was causing error.

*** ACTION NAME:(Select sys_context into variable) 2017-01-27 14:15:24.257
*** MODULE NAME:(Trigger DDL_AUDIT) 2017-01-27 14:15:24.257
*** SERVICE NAME:(SYS$USERS) 2017-01-27 14:15:24.257
*** SESSION ID:(3265.22843) 2017-01-27 14:15:24.257
*** 2017-01-27 14:15:24.257
ksedmp: internal or fatal error
ORA-01422: exact fetch returns more than requested number of rows
Current SQL statement for this session:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’)

Of course, it’s easier to locate the target module if you have already instrumented your code with DBMS_APPLICATION_INFO.SET_MODULE and DBMS_APPLICATION_INFO.SET_ACTION;

In this case, the problem was due to SYS_CONTEXT(‘USERENV’,’SESSIONID’) returning 2 rows causing the ORA-01422 and subsequent datapump failure.

The workaround of this specific problem is to disable the Trigger DDL_AUDIT.
A solution is to modify the cursor or the statement in the trigger to retreive just one row, for example:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’) and rownum=1;
Another solution is to trap error with an excveption clause.

 
Author: Vincent Fenoll, Oracle DBA Montreal

ADRCI, How to create a package?

The goal is to upload a generated zip file from Montreal to the My Oracle Support website. This package will includes all the trace files, instance alert file and other diagnostic information for the critical error .

It is called a IPS package (Incident Packaging Services).

$adrci

adrci> show home

if you have multiple ORACLE_HOME:
adrci> set homepath database_home

adrci> show problem

PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
——– ———- ————- ————
2 ORA 4030 146025 2014-09-17 22:16:08.625000 -04:00
1 ORA 445 144125 2014-09-18 09:24:12.998000 -04:00

adrci> show incident -p “problem_key=’ORA 4030′”

ADR Home = /u01a/diag/rdbms/nhsmppr/NHsmPPr:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
—————- ————– —————————————-
145585 ORA 4030 2014-09-17 22:16:05.493000 -04:00
144217 ORA 4030 2014-09-17 22:16:05.523000 -04:00
145441 ORA 4030 2014-09-17 22:16:05.639000 -04:00
144577 ORA 4030 2014-09-17 22:16:05.782000 -04:00
145985 ORA 4030 2014-09-17 22:16:05.919000 -04:00
5 rows fetched

adrci> ips pack incident 145585 in /tmp
Generated package 1 in file /tmp/ORA4030_20140918102208_COM_1.zip, mode complete

If at this step the ADRCI utility complains that the incident was flood-controlled and that no package can be generated for it, then instead of choosing the most recent incident to be packaged, choose the first incident that occurred after an instance startup.

Vincent Fenoll – Oracle DBA in Montreal