Oracle OLR corruption and restoration | CRS-1013 | CRS-6706

 Introduction:

In a RAC configuration, this is pretty common to corrupt the OLR registries under grid. So sometime we need to restore it to make things work again.

 

Error codes:

CRS-1013: The OCR location in an ASM disk group is inaccessible.

WARNING: failed to online diskgroup resource ora.ASM.dg (unable to communicate with CRSD/OHASD)

 ORA-15368: Oracle Cluster Registry operation failed with error 32.

 Running SRDC might show the exact symptoms:
eg: Verifying OLR integrity ...FAILED
Cause: OLR is corrupted

 

Fix:

Login as grid user 

go to OLR Location:

eg
/opt/app/grid/crsdata/<hostname>/olr

 <GI_HOME>/bin/ocrconfig -local -showbackup

This will show you the backups available for the OLR.
 

To restore:

First check the below process is running or not:
ps -ef| grep ohasd.bin

This should return no process, if ohasd.bin is still up and running, stop it on local node:

# <GI_HOME>/bin/crsctl stop crs -f  <========= for GI Cluster 

OR  

# <GI_HOME>/bin/crsctl stop has  <========= for GI Standalone
 

Once it's down, restore with the following command: 

# <GI_HOME>/bin/ocrconfig -local -restore <olr-backup>

(Here consider the most recent available backup file name)

NOTE:
If the command fails, create a dummy OLR, set correct ownership and permission and retry the restoration command:

# cd <OLR location>
# touch <hostname>.olr
# chmod 600 <hostname>.olr
# chown <grid>:<oinstall> <hostname>.olr

Once it's restored, GI can be brought up:

# <GI_HOME>/bin/crsctl start crs   <========= for GI Cluster 

OR  

$ <GI_HOME>/bin/crsctl start has  <========= for GI Standalone, this must be done as grid user.
 

In 12.1 onward, if patches are applied after the OLR is backed up, and later the backup is restored, the patch level will be different and GI won't start with error:

CRS-6706: Oracle Clusterware Release patch level ('1196363452') does not match Software patch level ('600291166'). Oracle Clusterware cannot be started.
To fix the above problem, run as root user:

<GI_HOME>/crs/install/rootcrs.sh -prepatch 
<GI_HOME>/crs/install/rootcrs.sh -postpatch 

In my case, I was facing another issue. So I had to follow some additional steps. the reason behind is that, my OLR was last backed up 6 months ago, and after that I did CPU patching on the database. so the backup OLR file does not have the information of the new CPU patches and we need to update that information to the current OLR file. to do so,

 

As root user unlock the GI home:
cd $ORACLE_HOME
$ORACLE_HOME/crs/install/rootcrs.sh -unlock

Apply the local patch to OLR

$ORACLE_HOME/bin/clscfg -localpatch

Lock the GI home

$ORACLE_HOME/crs/install/rootcrs.sh -lock

Start the clusterware:
crsctl start crs -wait

crsctl status resource -t
 

That it, now the OLR is fixed finally.  

 

Reference:

Oracle Support: How to backup or restore OLR:(KB137305) 

Oracle Support: CRS-6706: Oracle Clusterware Release patch level ('nnn') does not match Software patch level ('mmm') KB140963 

Oracle Home remove/Detach from Inventory

Remove/Detach Oracle Home from Inventory

Prechecks:

Check /etc/oraInst.loc

go to the inventory location:

cd ContentsXML

cat inventory.xml and check which one needs to be detached.

inventory.xml example:

<?xml version = '1.0' encoding = 'UTF-8' standalone = 'yes'?>

<!-- Copyright (c) 1999, 2024, Oracle. All rights reserved. -->

<!-- Do not modify the contents of this file by hand. --><INVENTORY>

   <VERSION_INFO>

      <SAVED_WITH>13.9.4.0.0</SAVED_WITH>

      <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

   </VERSION_INFO>

   <HOME_LIST>

      <HOME NAME="OraDB19Home1" LOC="/u01/app/oracle/19.3.0/" TYPE="O" IDX="4"/>

      <HOME NAME="agent13c1" LOC="/u01/app/em13c/agent_13.4.0.0.0" TYPE="O" IDX="3"/>

      <HOME NAME="agent13c2" LOC="/u01/app/em13c/agent_13.5.0.0.0" TYPE="O" IDX="6"/>

   </HOME_LIST>

   <COMPOSITEHOME_LIST/>

Pause RMAN backup

How to Pause and resume RMAN backup

There are certain times when backup is running for long and critical applications are hanging for limited resources. During that time, rather than killing the whole backup session, it is a handy option to pause the backup from server end and then resume it after the application transactions are completed. 

This is performed in a Development/testing environment.

Check the details of the Currently running backup:

col START_TIME for a15

col END_TIME for a15

col TIME_TAKEN_DISPLAY for a10

col INPUT_BYTES_DISPLAY for a10

col OUTPUT_BYTES_DISPLAY for a10

col OUTPUT_BYTES_PER_SEC_DISPLAY for a10

col output_device_type for a10

SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME,to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,input_type, output_device_type,input_bytes_display, output_bytes_display,output_bytes_per_sec_display,COMPRESSION_RATIO COMPRESS_RATIO FROM v$rman_backup_job_details WHERE status like 'RUNNING%';

Dataguard - Archive Gaps

Dataguard essential queries

Query to check the log gap on primary/standby database:

 

set pagesize 300

set linesize 200

set wrap off

select thread#, sequence#, applied,to_char(first_time,'mm/dd/yy hh24:mi:ss') first,to_char(next_time, 'mm/dd/yy hh24:mi:ss') next,to_char(completion_time, 'mm/dd/yy hh24:mi:ss') completion from v$archived_log where thread# = 1 order by first_time;

 

ORA-09925: Unable to create audit trail file

Issues with Database login (ORA-09925)

ERROR:

ORA-09925: Unable to create audit trail file

SVR4 Error: 49: Disc quota exceeded

Additional information: 9925

ORA-01075: you are currently logged on

This error occurs when the filesystem is filled up and oracle is not able to allocate any space to connect to the database. To troubleshoot, we need to check the mountpoint and cleanup the log files or any old dump files. 

ORA-01940: cannot drop a user that is currently connected

Issue with drop user in Oracle

While Dropping a user if the drop user command is throwing error like:

ORA-01940: cannot drop a user that is currently connected

Follow the below steps:

select 'alter system kill session '''||sid||','||serial#||''' immediate;' "SQL Statement" from v$session where username=UPPER('&username');

SQL Statement

--------------------------------------------------------

alter system kill session '101,265842' immediate;

Execute the alter statement to kill the active session.

alter system kill session '101,265842' immediate;

System altered.

Now the drop user will work:

drop user <username> cascade;

Note: cascade option drops all the dependent objects under the schema.

Gather Statistics

 

Oracle Gather Stat 

Gather Table, Index and Schema Statistics

 

DBMS_STATS.GATHER_TABLE_STATS is used to gather stats for a single table

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES');

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES',cascade=>TRUE);

( Note: Cascade gathers Index stats associated with the table )

OPatch Failed with error code 73

Oracle OPatch  Error

Error:

Oracle Home       : <ORACLE_HOME> Path 

Central Inventory : The inventory that is being used by this opatch session

   from           : The inventory that is stored in the Oracle Home

OPatch version    : 12.2.0.1.44

OUI version       : 12.2.0.7.0

Log file location : /u01/oracle/product/19.3.0/cfgtoollogs/opatch/opatch<date>.log

OPatchSession cannot load inventory for the given Oracle Home <oracle_home>. Possible causes are:

   No read or write permission to ORACLE_HOME/.patch_storage

   Central Inventory is locked by another OUI instance

   No read permission to Central Inventory

   The lock file exists in ORACLE_HOME/.patch_storage

   The Oracle Home does not exist in Central Inventory

UtilSession failed: OPatch failed to locate Central Inventory.

Possible causes are:

    The Central Inventory is corrupted

    The oraInst.loc file specified is not valid.

This is because, sometime the /etc/oraInst.loc is pointing to the ora inventory in a temp location which the $ORACLE_HOME/oraInst.loc is not aware.

cat /etc/oraInst.loc

cat $ORACLE_HOME/oraInst.loc

check if both the files are pointing to the same location. if not, take a backup of the file and update the central inventory in $ORACLE_HOME/oraInst.loc.

cp -p $ORACLE_HOME/oraInst.loc $ORACLE_HOME/oraInst.loc.old

Modify the file accordingly for parameter inventory_loc=<Full PATH of the inventory, which can be copied from /etc/oraInst.loc>

Note: One more observation on the file permission, if you are running the grid with different usernames, the same group and sharing the same inventory, make sure, the files under /u01/oraInventory/ContentsXML have 644 permissions. So that while doing patching with Oracle users, it can modify those XML files.

Hope this helps resolve the error, happy patching!!

Best Wishes!!

OPatch Session Hung Forever | CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX

Performance Issue with OPatch

Opatch Hung forever

CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX

If you ever face issues with opatch utility is taking long time to run the prerequisite check and in the log you see the below line.

CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX

It is time to cleanup some inactive patches from opatch inventory, to do that please follow the below instructions.

cd $ORACLE_HOME/OPatch

--This will list the inactive patches

./opatch util listorderedinactivepatches

--This will remove the inactive patches

./opatch util deleteinactivepatches

This will take some time depending on how many old inactive patches is present in the inventory.

Reference:

OPatch 12.2.0.1.37+ Introduces a New Feature to Delete Inactive Patches in the ORACLE_HOME/.patch_storage Directory (Doc ID 2942102.1)

Oracle DB Links

What is DB link? 

Oracle DB links are used to connect to different sources through procedures/views. It helps creating views or materialized views by pulling data from different db sources.

To check the db links present in the database, the below query can be used.

Query:

select * from dba_db_links;

SQL> desc dba_db_links;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL VARCHAR2(128)

 DB_LINK                                   NOT NULL VARCHAR2(128)

 USERNAME                                           VARCHAR2(128)

 HOST                                               VARCHAR2(2000)

 CREATED                                   NOT NULL DATE

 HIDDEN                                             VARCHAR2(3)

 SHARD_INTERNAL                                     VARCHAR2(3)

 VALID                                              VARCHAR2(3)

 INTRA_CDB                                          VARCHAR2(3)

Create a DB link:

Create or replace database link "<DB link name>" connect as "<remote username>" identified by "<Remote user password>" using "<Remote DB TNS details>";

Hope this helps. Please let me know if you face any issues, comment below.

Find blocking session in Oracle Apps

Blocking Session Query for Oracle Apps

How to find the blocking sessions in oracle database and who is blocked by whom. Here user can find out the blocking session details like session id, user name, application user name, module , sql id, log on time etc.

You can modify the query as per business requirement. This is one of the most important query for a dba.

select sysdate,  

       vdb.name,

       blking.sid "Blocking DB Session ID", 

       blking.serial# "serial#",

       blking.status "Status",

       blking.client_identifier "Blocking eBS Username",

       blking_user_tab.description "Blocking User",

       blking.module "Blocking Module",

       blking.machine "Blocking OS Server",

       blking.logon_time "Blocking Session Logon Time",

       blking.sql_id "Blocking Current SQL_ID",

       blking.prev_sql_id "Blocking Past SQL_ID",

       blking.process "Blocking OS Process",

       blking.blocking_session "Nested Blocking DB Session ID",

       blkd.client_identifier "Blocked eBS Username",

       blkd_user_tab.description "Blocked User",

       blkd.sid "Blocked DB Session ID",

       blkd.module "Blocked Module",

       blkd.machine "Blocked Server",

       blkd.logon_time "Blocked Session Login",

       blkd.sql_id "Blocked Current SQL_ID",

       blkd.prev_sql_id "Blocked Past SQL_ID", 

       blkd.process "Blocked OS Process",

       BLKD.BLOCKING_SESSION "Session blocking"

from  

   v$session blkd, v$session blking, (select distinct a.client_identifier, b.description 

from   v$session a left join applsys.fnd_user b on a.client_identifier = b.user_name) blkd_user_tab, 

(select distinct a.client_identifier, b.description 

from   v$session a left join applsys.fnd_user b on a.client_identifier = b.user_name) blking_user_tab,v$database vdb

where 

   blkd.blocking_session is not NULL and

   blkd.blocking_session = blking.sid

    and blkd.client_identifier = blkd_user_tab.client_identifier 

    and blking.client_identifier = blking_user_tab.client_identifier

order by 

   blking.blocking_session,blking.logon_time;

Thank you for your time, hope this helps.

Find Concurrent Program Run History from backend

Query to find concurrent program run history and status

SELECT f.request_id,

         pt.user_concurrent_program_name

             user_conc_program_name,

         f.actual_start_date

             start_on,

         f.actual_completion_date

             end_on,

         p.concurrent_program_name

             concurrent_program_name,

         DECODE (f.phase_code,

                 'R', 'RUNNING',

                 'C', 'COMPLETED',

                 f.phase_code)

             phase,

         DECODE (f.status_code,  'C', 'NORMAL',  'E', 'ERROR',  f.status_code)

             Status,

         f.requested_by,

         fu.user_id,

         fu.user_name

    FROM apps.fnd_concurrent_programs   p,

         apps.fnd_concurrent_programs_tl pt,

         apps.fnd_concurrent_requests   f,

         apps.fnd_user                  fu

   WHERE     f.concurrent_program_id = p.concurrent_program_id

         AND f.program_application_id = p.application_id

         AND f.concurrent_program_id = pt.concurrent_program_id

         AND f.program_application_id = pt.application_id

         AND pt.language = USERENV ('lang')

         AND f.actual_start_date IS NOT NULL

         AND f.actual_start_date >

             TO_DATE ('15-DEC-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')

         AND f.actual_completion_date <

             TO_DATE ('17-DEC-2021 23:59:59', 'DD-MON-YYYY HH24:MI:SS')

         AND f.requested_by = fu.user_id

         AND pt.USER_CONCURRENT_PROGRAM_NAME = '&User_concurrent_program_name'

--AND fu.user_name = '&user_name'

ORDER BY f.actual_start_date ASC; 

##Change the date accordingly.

Temp Tablespace Usage

Oracle Temp Tablespace usage

Check for Temp tablespace usage

set lin 200;

col file_name for a75;

col autoextensible for a15;

select file_name,tablespace_name,sum(bytes)/1024/1024 as FILE_SIZE,sum(maxbytes)/1024/1024 as MAX_SIZE,autoextensible from dba_temp_files

where tablespace_name ='TEMP' group by file_name,tablespace_name,autoextensible order by file_name;

Data file usage of a tablespace

Oracle Tablespaces 

Check Data File Size in any given Tablespace (Dynamic) :

set lin 200;

col file_name for a75;

col autoextensible for a15;

select file_name,tablespace_name,sum(bytes)/1024/1024 "FILE_SIZE(MB)",sum(maxbytes)/1024/1024 as MAX_SIZE,autoextensible from dba_data_files

where tablespace_name ='&tablespace_name' group by file_name,tablespace_name,autoextensible order by file_name;

Query to find Tablespace utilization in Oracle Database

Oracle Tablespace Utilization

Find the tablespace utilization on Oracle Database (10g,11g,12c,19c)

column file_name format a45

column tablespace_name format a10

col tablespace_name for a40

set verify off

set pages 3000

set line 3000

 

SELECT  dts.tablespace_name, 

NVL(ddf.bytes / 1024 / 1024, 0) avail,

NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,

NVL(dfs.bytes / 1024 / 1024, 0) free,

TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %" ,

TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes* 100, 0), '990.00') free_pct,

decode(sign((NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.90 - NVL(ddf.bytes/1024/1024, 0)),-1,0,

(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.90 - NVL(ddf.bytes / 1024 / 1024, 0))  "Required MB" FROM

sys.dba_tablespaces dts,

(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) ddf,

(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) dfs

WHERE

dts.tablespace_name = ddf.tablespace_name(+)

AND dts.tablespace_name = dfs.tablespace_name(+)

order by free_pct;

Best Wishes!!