OLR Internals

In Oracle 11g Release 2, Oracle introduced the Oracle Local Registry (OLR), a new feature that complements the Oracle Cluster Registry (OCR) within the Grid Infrastructure. The OLR is an essential component stored locally on each node in the Grid Infrastructure home directory, specifically at 'grid_home/cdata/<hostname>.olr', with its location documented in '/etc/oracle/olr.loc'.

Each node maintains its unique OLR file within the Grid Infrastructure software home. The OLR contains critical security contexts required by the Oracle High Availability Service during the early stages of Clusterware startup. The data within the OLR is vital for the Oracle High Availability Services daemon (OHASD) to initialize, encompassing information about GPnP wallets, Clusterware configurations, and version details. This information, alongside the Grid Plug and Play (GPnP) configuration file, is crucial for locating the voting disks. If these disks are stored in ASM, the GPnP profile’s discovery string will be used by the cluster synchronization daemon for their retrieval.

In this post, I'll delve into the purpose of the OLR, its necessity, and the type of data it contains. To fully understand these aspects, we need to examine the contents of an OLR dump:

ocrdump -local -stdout

[SYSTEM]

[SYSTEM.version]

[SYSTEM.version.activeversion]

[SYSTEM.version.hostnames.rac1]

[SYSTEM.version.hostnames.rac2]

[SYSTEM.ORA_CRS_HOME]

[SYSTEM.evm]

[SYSTEM.GPnP.profiles]

[SYSTEM.CRSADMIN]

[SYSTEM.CRSUSER]

[SYSTEM.CRSD]

[SYSTEM.CRSD.SERVERPOOLS]

[SYSTEM.CRSD.SERVERS]

[SYSTEM.CRSD.SERVERS.rac1.STATE]

[SYSTEM.CRSD.TYPES]

[SYSTEM.CRSD.TYPES.ora!local_resource!type.START_DEPENDENCIES]

[SYSTEM.CRSD.TYPES.ora!local_resource!type.START_DEPENDENCIES.CONFIG]

[SYSTEM.CRSD.TYPES.ora!local_resource!type.STOP_DEPENDENCIES]

[SYSTEM.CRSD.TYPES.ora!local_resource!type.STOP_DEPENDENCIES.CONFIG]

[SYSTEM.CRSD.TYPES.ora!network!type]

[SYSTEM.CRSD.TYPES.ora!cluster_resource!type.AUTO_START]

[SYSTEM.CRSD.TYPES.ora!database!type.INSTANCE_FAILOVER]

[SYSTEM.CRSD.TYPES.ora!database!type.ORACLE_HOME]

[SYSTEM.CRSD.RESOURCES.ora!net1!network]

[SYSTEM.CRSD.RESOURCES.ora!gsd]

[SYSTEM.CRSD.RESOURCES.ora!LISTENER_SCAN1!lsnr.INTERNAL]

[SYSTEM.CRSD.RESOURCES.ora!LISTENER_SCAN2!lsnr]

[SYSTEM.CRSD.RESOURCES.ora!oc4j]

[SYSTEM.CRSD.RESOURCES.ora!rac2!vip.INTERNAL]

[SYSTEM.CRSD.RESOURCES.ora!LISTENER!lsnr.INTERNAL]

[SYSTEM.CRSD.RESOURCES.ora!orcl!db.INTERNAL]

[DATABASE.NODEAPPS.rac2]

[DATABASE.VIP_RANGE]

[DATABASE.ASM]

[DATABASE.ASM.rac1.+asm1.VERSION]

[DATABASE.ASM.rac2.+asm2.ORACLE_HOME]

[CRS]

[CRS.CUR]

[CRS.HIS]

[CRS.SEC]

[CRS.STAGE]

[CRS.STAGE.node1]

I've tried to format the output for clarity. The OLR contains extensive information, including ORA_CRS_HOME, Clusterware versions, configurations, local host versions, active versions, GPnP details, OCR latest backup times and locations, node names, and the status of node resources—whether they need to start or not—and the start & stop dependencies of these resources. These dependencies are classified as either weak (optional) or hard (mandatory).

Understanding the purpose of the OLR is crucial. Although the OCR needs to be accessible by Clusterware to determine which resources to start on a node, Oracle 11gR2 allows the OCR to be stored in ASM. This poses a challenge because ASM itself is a resource that must be started. Here, the OLR comes into play. Being a locally available file on the operating system, the OLR can be read by any process with the appropriate privileges, without any dependencies.

The High Availability Services stack comprises daemons that communicate with their counterparts on other nodes. Once the High Availability Services stack is operational, the cluster node can join the cluster and use shared components like the OCR. Part of the startup sequence for the High Availability Services stack is stored in the GPnP profile, but it also relies on information from the OLR.

A pertinent question is why OCR is still needed if we have OLR. Comparing the OLR and OCR reveals that the OLR contains fewer keys. For example, an 'ocrdump' might show 704 keys for the OCR versus 526 keys for the OLR. Most keys in the OLR pertain to the OHASD process, while those in the OCR are related to CRSD. This indicates the necessity of the OLR (along with the GPnP profile) for starting the High Availability Services stack.

I hope this explanation helps you understand the OLR, its purpose, its content, and why it is essential.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011

Transport Good Execution Plan(Hash Plan) from one database to another database

1. Create Empty Tuning Set

BEGIN

  DBMS_SQLTUNE.CREATE_SQLSET (

    sqlset_name  => 'SQLT_WKLD_STS', 

    description  => 'STS to store SQL from the private SQL area' 

);

END;

2. Load the SQL Information for the SQL ID into this Tuning Set

DECLARE c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN c_sqlarea_cursor FOR SELECT VALUE(p) FROM TABLE( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( ' sql_id = ''2t8gdjy0pbs57'' AND plan_hash_value = ''618955923'' ') ) p; -- load the tuning set DBMS_SQLTUNE.LOAD_SQLSET ( sqlset_name => 'SQLT_WKLD_STS' , populate_cursor => c_sqlarea_cursor ); END; /

3. Display the content of Sql Tuning set

SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, ELAPSED_TIME AS "ELAPSED", BUFFER_GETS FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS' ) ); SQL_ID SCH SQL_TEXT ELAPSED BUFFER_GETS ------------- ------------------------- -------------------------------------------------------------------------------- ---------- ----------- 2t8gdjy0pbs57 TESTUSER INSERT /*+ APPEND */ INTO W_L_T.PROJECT as SELECT….. 24844903 2577896

SELECT sql_id, parsing_schema_name as "SCH", sql_text,buffer_gets as "B_GETS",disk_reads, ROUND(disk_reads/buffer_gets*100,2) "%_DISK" FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS', '(disk_reads/buffer_gets) >= 0.50' ) ); SQL_ID SCH SQL_TEXT B_GETS DISK_READS %_DISK ------------- ------------------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- 2t8gdjy0pbs57 TESTUSER INSERT /*+ APPEND */ INTO W_L_T.PROJECT as SELECT….. 2577896 2435837 94.49

4. Create a Staging table to hold the exported SQL Tuning set

BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name => 'MY_TUNING_SET', schema_name => 'PDBADMIN'); END; /

5. Load the SQL Tuning Set information to the Staging Table

BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( sqlset_name => 'SQLT_WKLD_STS', sqlset_owner => 'PDBADMIN', staging_table_name => 'MY_TUNING_SET', staging_schema_owner => 'PDBADMIN' ); END; /

6. Export The table

nohup expdp userid="USERNAME/PASSWORD@lnx01.oraclevcn.com:1521/PDB1S001.oraclevcn.com" parfile/u01/dumps/mytunigset_table.par &

7. Import of the Staging table

nohup impdp userid="USERNAME/PASSWORD@lnx02.oraclevcn.com:1521/PDB1P001.oraclevcn.com" parfile/u01/dumps/mytunigset_table.par &

8. Unpack the SQL Tuning set from the staging table to the destination server

BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET ( sqlset_name => '%' , sqlset_owner => 'PDBADMIN' , replace => true , staging_table_name => 'MY_TUNING_SET' , staging_schema_owner => 'PDBADMIN'); END; /

9. Load the plan from SQL Tuning Set to SQL Plan Baseline

VARIABLE v_plan_cnt NUMBER EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( - sqlset_name => 'SQLT_WKLD_STS', - sqlset_owner => 'PDBADMIN', - basic_filter => 'sql_id = ''2t8gdjy0pbs57'' AND plan_hash_value = 618955923' );

10. Flush the two SQLs from the shared pool, so that the optimizer will pick the new plan

select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' from V$SQLAREA where SQL_ID in ('2t8gdjy0pbs57'); exec SYS.DBMS_SHARED_POOL.PURGE ('0000001006B396C8, 2113289046', 'C');

11. To verify the Baseline created in the database

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

12. Drop SQL tuning Set from Source

BEGIN DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'SQLT_WKLD_STS'); END;

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011