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

Logminer utility

Sometimes we need to see redo information about executed sql in the database.In order to do this, Oracle offers the Logminer utility.
This native tool analyzes the content of redo and archived redologs.

Note: If it’s configured and it’s not too late, it’s easier to use Flashback query.

Here is a simple example to use Oracle Logminer
— Prerequisite
EXECUTE_CATALOG_ROLE and select on V$LOGMNR_CONTENTS

— Specify a LogMiner dictionary.
EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

— Specify a list of redo log files for analysis.
— to find which log file: search first_time field (the hour should be the last before the hour we need the logminer)
select * from v$archived_log where trunc(first_time) = to_date(sysdate)
and name like ‘%specific-file-system%’

— Add required archives (here 2)
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>’+archives/my-db/archivelog/2017_10_28/thread_2_seq_366089.4743.958543215′, OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>’+archives/my-db/archivelog/2017_10_28/thread_1_seq_367742.3394.958545027′, OPTIONS => DBMS_LOGMNR.NEW);
[…]

— Start LogMiner.
— to retreive only commited transactions
EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => TO_DATE(’28-10-2017 05:56:00′,’DD-MM-YYYY HH24:MI:SS’),ENDTIME => TO_DATE(’28-10-2017 06:10:00′,’DD-MM-YYYY HH24:MI:SS’), OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

— to retreive both committed and uncommitted transactions
EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => TO_DATE(’28-10-2017 05:56:00′,’DD-MM-YYYY HH24:MI:SS’),ENDTIME => TO_DATE(’28-10-2017 05:56:00′,’DD-MM-YYYY HH24:MI:SS’), OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

— Mine…
select count(*) from v$logmnr_contents a where a.TABLE_NAME = ‘MY_TABLE’;

create table my-schema.logmnr-result as
select * from v$logmnr_contents a where a.TABLE_NAME = ‘MY_TABLE’;

— end of the session
EXECUTE DBMS_LOGMNR.END_LOGMNR();

— Analyze my-schema.logmnr-result Table