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

An Oracle RAC Service is in UNKNOWN state on instance 1 and it is impossible 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;

How to Enable or Disable the FRA?

Using a flash recovery area simplifies the ongoing administration of your database by automatically naming recovery-related files, retaining them as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose.

How to enable the Flash Recovery Area?

To enable FRA for the database:

ALTER DATABASE FLASHBACK ON;

ALTER SYSTEM SET db_recovery_file_dest=’my path’ (or ‘+FRA’ if ASM is used and FRA is your diskgroup)
ALTER SYSTEM SET db_recovery_file_dest_size=500G;

You can also set db_flashback_retention_target, default is 1440min or 1 day

To verify if FlashBack is enabled:
SELECT flashback_on, log_mode FROM v$database;

 

How to disable the Flash Recovery Area?

ALTER DATABASE FLASHBACK OFF;

To verify if FlashBack is disabled:
SELECT flashback_on, log_mode FROM v$database;

 

What files can be found in the Flash (Fast) Recovery Area:

Transient Files:
RMAN backups
archived redo logs
flashback logs

Permanent Files:
online redo log copies
control file
Guaranteed Restore Points

 

Flashback must be ON to put your database in ARCHIVELOG mode.

Author: Vincent Fenoll

How to see locks and blockers on table

I want to find oracle locked objects. How can I see lock on table ?

Sometimes, “select * from dba_blockers” does’nt return anything but somebody is blocking one or several row.

This SQL statement returns

select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
gv$locked_object a ,
gv$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;

OR

BEGIN
dbms_output.enable(1000000);
for do_loop in (select inst_id, session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
b.object_name object_name, b.object_type object_type
FROM gv$locked_object a, dba_objects b
WHERE xidsqn != 0
and b.object_id = a.object_id)
loop
dbms_output.put_line(‘.’);
dbms_output.put_line(‘Blocking Session : ‘||do_loop.inst_id||’-‘||do_loop.session_id||’-‘||do_loop.oracle_username);
dbms_output.put_line(‘Object (Owner/Name): ‘||do_loop.owner||’.’||do_loop.object_name);
dbms_output.put_line(‘Object Type : ‘||do_loop.object_type);
for next_loop in (select sid from v$lock
where id2 = do_loop.xidsqn
and sid != do_loop.session_id)
LOOP
dbms_output.put_line(‘Sessions being blocked : ‘||next_loop.sid);
end loop;
end loop;
END;