About Vincent

Oracle DBA OCP 10g, 9i, 8 Electronic music producer

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 check the maximum number of session connected

For the last days, I want to know the maximum number of sessions on a database or for each node of my RAC.

The considered period is sysdate > AWR retention time

select a.instance_number, current_utilization, end_interval_time
from sys.wrh$_resource_limit a, sys.wrm$_snapshot b
where a.resource_name like ‘%sessions%’
and a.snap_id = b.snap_id
and a.instance_number = b.instance_number
and a.instance_number = 1
and b.begin_interval_time > sysdate – 30
order by current_utilization desc;

 

For the second node of a RAC change a.instance_number = 1 by a.instance_number=2.

 

NOTES:

AWR retention time:

select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) “Snapshot Interval”,
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) “Retention Interval”
from dba_hist_wr_control;

To change AWR retention time:

execute dbms_workload_repository.modify_snapshot_settings ( interval => 60,  retention => 100800);

 

Author: Vincent Fenoll Oracle DBA Montreal

Basic Multitenant CDB / PDB Operations

Here is a FAQ on Multitenant basic CDB / PDB Operations.

 

How do I know if my database is Multitenant or not?

select NAME, DECODE(CDB, ‘YES’, ‘Multitenant Option enabled’, ‘Regular 12c Database: ‘) “Multitenant Option ?” , OPEN_MODE, CON_ID from V$DATABASE;

 

What Pluggable databases do I have in this container database?

select CON_ID, NAME, OPEN_MODE from V$PDBS;

 

How do I connect to my Pluggable Database PDB1?

Connected as sysdba in the CDB:

alter session set container = pdb1;

With a tnsnames alias:

pdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = my-host)
(Port = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1)
)
)

Using easy connect

Format: CONNECT username/password@host[:port][/service_name][:server][/instance_name]
$ sqlplus scott/tiger@//localhost:1521/pdb1
$ sqlplus scott/tiger@//my-server-name:1525/pdb1

To switch back to the main container:
ALTER SESSION SET CONTAINER = CDB$ROOT;

Where am I connected?

SQL> show con_name
CON_NAME
———
PDB1

 

How to stop a PDB in Oracle RAC?

On the current node [or on all the nodes]:
(connected on the CDB) ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE [Instances=all];
(connected on the PDB) ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE [Instances=all];

This will stop the associated service too.
Manually stopping the associated service will not close the PDB. You have to use the SQL command.

 

How to start and stop a PDB in Oracle RAC?

On the current node [or on all the nodes]:
(connected on the CDB) ALTER PLUGGABLE DATABASE PDB1 OPEN [Instances=all;]
(connected on the PDB) ALTER PLUGGABLE DATABASE OPEN [Instances=all;]

You can also start the PDB with the associated service (srvctl start service -d my-db_unique_name -s pdb1)
This will NOT start the service(s) associated with this PDB.

 

Author: Vincent Fenoll – Oracle DBA Montreal

ASM Space used

I want to monitor space usage (free and used) on Oracle ASM diskgroups.

 

With v$ view and sql*Plus:

SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage
FROM v$asm_diskgroup;

NAME FREE_MB TOTAL_MB PERCENTAGE
———————————————————— ———- ———- ———-
DATA 62532 1536216 4.07052133
MGMT 5760 42000 13.7142857
OCRVOTE 20144 20480 98.359375
RECOVER 441296 445496 99.0572306

With asmcmd:

$ asmcmd lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 512 4096 4194304 1536216 498044 0 498044 0 N DATA/
MOUNTED EXTERN N 512 512 4096 4194304 42000 5760 0 5760 0 N MGMT/
MOUNTED EXTERN N 512 512 4096 4194304 20480 20144 0 20144 0 Y OCRVOTE/
MOUNTED EXTERN N 512 512 4096 4194304 445496 441296 0 441296 0 N RECOVER/

If you have it, Cloud control give also the information.

Vincent Fenoll – Oracle OCP Database administrator in Montreal

Change primary key value in Oracle

I want to modify the value of a primary key (PK) but there are foreign keys (FK) with data.

You will need to:
– Disable the foreign key constraints
– Change the values of the foreign keys
– Change the primary key values
– Re-enable the foreign key constraints.

The difficulty here is to quickly find all the foreign keys and disable them.

Here is how to disable the foreign key for all the dependant tables? Here is a script to automatically generate the SQL statements. Just change “my_table” and “my_column”

select ‘alter table ‘ || table_name || ‘ disable constraint ‘ || constraint_name || CHR(13) || ‘– my comment’ || CHR(13) || ‘;’ from user_constraints
where table_name in (select
src_cc.table_name as src_table
from
all_constraints c
inner join all_cons_columns dest_cc on
c.r_constraint_name = dest_cc.constraint_name
and c.r_owner = dest_cc.owner
inner join all_cons_columns src_cc on
c.constraint_name = src_cc.constraint_name
and c.owner = src_cc.owner
where
c.constraint_type = ‘R’
and dest_cc.owner = ‘DI’
and dest_cc.table_name = ‘my_table’
and dest_cc.column_name = ‘my_column’)
and constraint_name in (select
c.constraint_name
from
all_constraints c
inner join all_cons_columns dest_cc on
c.r_constraint_name = dest_cc.constraint_name
and c.r_owner = dest_cc.owner
inner join all_cons_columns src_cc on
c.constraint_name = src_cc.constraint_name
and c.owner = src_cc.owner
where
c.constraint_type = ‘R’
and dest_cc.owner = ‘DI’
and dest_cc.table_name = ‘my_table’
and dest_cc.column_name = ‘my_column’);

To re-enable the contraints replace “disable” with “enable”.

 

Author: Vincent Fenoll – Oracle DBA in Montreal (Canada)