DB link from Oracle to SQL Server

We had a need for a DB link from an Oracle database to a SQL Server database. I initially feared that this might be a huge task, but it turned out to be quite a easy setup. Thanks Oracle for making it almost seamless!

There are basically 4 steps involved 

1.) Install & configure the Gateway

The Oracle Gateway can be found in the Oracle Database 11g Release 2 Enterprise Edition software. Installation is straight forward. You basically have to make 2 choices during installation. What Gateway you want to install (SQL Server in our case) and which ORACLE_HOME you want to use. You can create a separate Gateway home or just use the current ORACLE_HOME, which is what we did.

After installation you will find a new directory in ORACLE_HOME called dg4msql. Configure the Gateway by creating a agent init.ora file in $ORACLE_HOME/dg4msql/admin. The name of the file is important, as the name you use will be used in your listener as well. We kept it to the default initdg4msql.ora. The information you need to supply in the agent init.ora file are SQL Server Database Server Host Name, SQL Server Database Server Port number and SQL Server Database. The information all goes into one parameter called HS_FDS_CONNECT_INFO.

Ex:

# This is a customized agent init file that contains the HS parameters

# that are needed for the Database Gateway for Microsoft SQL Server

#

# HS init parameters

#

# HS_FDS_CONNECT_INFO=[server_name]:port//mssql_db

HS_FDS_CONNECT_INFO=[ABC02.company.co.za]:1433//MSCIM

# alternate connect format is hostname/serverinstance/databasename

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

2.) Listener & names lookup

We used port 1524 to setup the gateway

LISTENER =

  (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = tcp)(HOST = abc01.company.co.za)(PORT = 1521))

    (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1524))

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = dg4msql)

      (ORACLE_HOME = /opt/apps/oracle/product/11.2.0/dbhome_1)

      (PROGRAM = dg4msql)

    )

  )

And Local Naming

MSCIM =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1524))

    (CONNECT_DATA =

      (SID = dg4msql)

    )

    (HS = OK)

  )

HS=OK - indicates this connect descriptor is using heterogeneous Service.

3.) Database Link

Once the SQL Server team gave us a username and password we could just create a DB link

CREATE PUBLIC DATABASE LINK MSCIM_DBLINK CONNECT TO 'mssql_username' IDENTIFIED BY 'mssql_password' USING 'MSCIM'

4.) Data dictionary translation support

Oracle has also supplied a script in $ORACLE_HOME/ dg4msql/admin/ called dg4msql_cvw.sql that must be run on the SQL Server side. The script creates views on SQL Server for usage by the Oracle Data Dictionary. 4 views called ALL_IND_COLUMNS, USER_IND_COLUMNS, ALL_CONS_COLUMNS, and USER_CONS_COLUMNS will be created on the SQL Server db making it easier for someone familiar with Oracle Data Dictionary views to query the SQL Server database.

Background Managed Standby Recovery process not detected

We have had 2 instances where the dg broker did not start MAnaged Standby Recovery of the physical standby database. The first time was after a switchover and the next time was after hardware maintenance. The physical standby came up successfully and the dg broker started up, but Managed Standby Recovery never started. On both occasions I noticed these entries in the alert.log file:

...

Mon Sep 12 14:03:32 2011

Starting Data Guard Broker (DMON)

..

OCISessionBegin with PasswordVerifier succeeded

ALTER SYSTEM SET ..

ALTER SYSTEM SET ..

ALTER SYSTEM SET ..

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

...

Mon Sep 12 14:08:06 2011

Background MRP initialization phase wait timeout   **

Background Managed Standby Recovery process not detected  **

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

I bounced the database a few times but the same error persisted. The only way I could solve this was to “kick start” the recovery by manually recovering a few archivelogs:

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

*

ERROR at line 1:

ORA-01153: an incompatible media recovery is active

But

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

only hangs

shutdown immediate also only hangs

shutdown abort did it.

SQL> startup mount

Start manual recovery before the DG Broker starts Managed Standby Recovery

SQL> RECOVER standby DATABASE  UNTIL CANCEL;

ORA-00279: change 10662691458828 generated at 09/13/2011 01:18:04 needed for thread 1

ORA-00289: suggestion : +PCASDGF/abcprd/archivelog/2011_09_13/thread_1_seq_82723.528.742958295

ORA-00280: change 10662691458828 for thread 1 is in sequence #82723

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 10662691458829 generated at 09/13/2011 01:18:13 needed for thread 1

ORA-00289: suggestion : +PCASDGF/abcprd/archivelog/2011_09_13/thread_1_seq_82724.2112.742961677

ORA-00280: change 10662691458829 for thread 1 is in sequence #82724

ORA-00278: log file '+PCASDGF/abcprd/archivelog/2011_09_13/thread_1_seq_82723.528.742958295' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

Hit enter, to manually recover a few logs and then

ORA-00279: change 10663204118583 generated at 09/13/2011 07:00:27 needed for thread 1

ORA-00289: suggestion : +PCASDGF/abcprd/archivelog/2011_09_13/thread_1_seq_82745.2365.742980297

ORA-00280: change 10663204118583 for thread 1 is in sequence #82745

ORA-00278: log file '+PCASDGF/abcprd/archivelog/2011_09_13/thread_1_seq_82744.2938.742978831' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

CANCEL

Media recovery cancelled.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

Database altered.

Just to be sure I did another bounce a few minutes later and this time the DG Broker did everything it was supposed to and Managed Standby Recovery was started automatically.

Logon to Active Data Guard fails with ORA-16000

We have a reporting user used by executives for real time reporting on one of our OLTP databases. Some of the queries were getting resource intensive so we decided to move those queries to our Active Data Guard instance.

But then the reports started failing with

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

on database logon. 

I found an old bug in MOS that pointed me in the right direction. After a failed login on Production, the same userid will get this error on the Active Data Guard instance, until the “logon counter” gets reset by a successful logon on production.

On Active Data Guard:

SQL >select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

...

SQL> select OPEN_MODE, DATABASE_ROLE from v$database;

OPEN_MODE  DATABASE_ROLE

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

READ ONLY  PHYSICAL STANDBY

SQL> conn infoweb/correctpwd

Connected.

On Production:

SQL >select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

...

SYS@casprd>select OPEN_MODE, DATABASE_ROLE from v$database;

OPEN_MODE  DATABASE_ROLE

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

READ WRITE PRIMARY

now login as infoweb with a wrong password on Production:

SQL>connect infoweb/wrongpwd

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Back to Active Data Guard, logon as infoweb with the correct password:

SQL> conn infoweb/correctpwd

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

Warning: You are no longer connected to ORACLE.

Back to Production, logon as infoweb with the correct password to reset the logon counter:

SQL> conn infoweb/correctpwd

Connected.

Back to Active Data Guard, logon as infoweb with the correct password:

SQL> conn infoweb/correctpwd

Connected.

This is similar to a 10.2.0.4 bug, Bug 5847453  Failed logon counter with read only DB throws ORA-16000 on failed logon [ID 5847453.8]. The note states that the issue was fixed in 10.2.0.5 and 11.1.0.6.

ACCOUNT_STATUS = EXPIRED(GRACE)

Users are identified within the app, so all DB connections are done through 1 schema. We created a profile for this schema where the password will never expire. App support has a manual process to change the password every 60 days, but Oracle must never be allowed to expire the password automatically.

The problem is that I found the ACCOUNT_STATUS for the main schema to be EXPIRED(GRACE) and that the EXPIRY_DATE is 3 days away.

This schemas profile was changed by accident, and rectified within a few hours. But the damage was done the ACCOUNT_STATUS still showed EXPIRED(GRACE).

I found an excellent blog post by Robert Geier (http://blog.contractoracle.com/2009/11/analysis-of-oracle-password-expiry.html) to explain the situation. Here’s a abbreviation of his explanation: password expiry is controlled by the PASSWORD_LIFE_TIME profile limit, is activated at login, and is reset by a password change. Changing the profile will not change aud$.ASTATUS.

SQL> select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = ‘QWERTY’;

USERNAME PROFILE ACCOUNT_STATUS

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

QWERTY QWERTY_NONEXPIRY EXPIRED(GRACE)

So even though the profile was changed back to QWERTY_NONEXPIRY the ACCOUNT_STATUS was still EXPIRED(GRACE). The only way to reset the ACCOUNT_STATUS back to OPEN is to change the password, because only a password change will reset aud$.ASTATUS. But a password change will give a ORA-28007 because of the current profile limits for PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME gets violated.

SQL> select password from sys.user$ where name='QWERTY';

PASSWORD

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

87FE2DD49B2882B2

SQL> alter user QWERTY identified by values '87FE2DD49B2882B2';

alter user QWERTY identified by values '87FE2DD49B2882B2'

*

ERROR at line 1:

ORA-28007: the password cannot be reused

So I had to create a new temporary profile with the required settings to allow me to change the password to be the same as the current password:

SQL> create profile tmtmp limit PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME UNLIMITED;

SQL> alter user QWERTY profile tmtmp;

SQL> alter user QWERTY identified by values '87FE2DD49B2882B2';

User altered.

SQL> alter user QWERTY profile QWERTY_NONEXPIRY;

SQL> select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = 'QWERTY';

USERNAME PROFILE ACCOUNT_STATUS

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

QWERTY QWERTY_NONEXPIRY OPEN

Manually Add Datafile

I have a database that runs on RAW devices. Whenever I add a datafile to a tablespace, out UNIX Sys Admins must first create the RAW device on the Primary & Standby Server. The other day they created the RAW device on the standby server but forgot to change the ownership of the RAW device to oracle:dba.

The datafile was successfully added to the primary database, but I found the following error in the alert.log of the standby:

File #323 added to control file as 'UNNAMED00323'.

Originally created as:

'/dev/vx/rdsk/oradg1/casprd_SA_I_CMD10'

Recovery was unable to create the file as:

'/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10'

MRP0: Background Media Recovery terminated with error 1119

Mon Mar 9 23:41:44 2010

Errors in file /opt/apps/oracle/admin/caspp/bdump/caspp_mrp0_15261.trc:

ORA-01119: error in creating database file '/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10'

ORA-27040: file create error, unable to create file

HPUX-ia64 Error: 13: Permission denied

Managed Standby Recovery not using Real Time Apply

Some recovered datafiles maybe left media fuzzy

Media recovery may continue but open resetlogs may fail

Mon Mar 9 23:41:45 2010

Errors in file /opt/apps/oracle/admin/caspp/bdump/caspp_mrp0_15261.trc:

ORA-01119: error in creating database file '/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10'

ORA-27040: file create error, unable to create file

HPUX-ia64 Error: 13: Permission denied

I can only qquery some v$ views when a standby database is mounted. So to fix the problem I did the following:

On production get the relevant details

SQL>select ts# from v$tablespace where name = 'SA_I_CMD';

TS#

----------

17

SQL> col name for a80;

SQL> select FILE#, NAME, bytes/1024/1024 MB from v$datafile where ts#=17;

FILE# NAME MB

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

34 /dev/vx/rdsk/oradg/casprd_SA_I_CMD03 32500

35 /dev/vx/rdsk/oradg/casprd_SA_I_CMD 31144

36 /dev/vx/rdsk/oradg/casprd_SA_I_CMD02 30712

37 /dev/vx/rdsk/oradg/casprd_SA_I_CMD01 32360

38 /dev/vx/rdsk/oradg/casprd_SA_I_CMD04 32500

39 /dev/vx/rdsk/oradg/casprd_SA_I_CMD05 30000

201 /dev/vx/rdsk/oradg/casprd_SA_I_CMD06 20000

266 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD07 32500

307 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD08 32500

311 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD09 32500

323 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD10 32500

Lets look at the same query on the standby side

SQL> col name for a80

SQL> select FILE#, NAME, bytes/1024/1024 MB from v$datafile where ts#=17;

FILE# NAME MB

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

34 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD03 32500

35 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD 31144

36 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD02 30712

37 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD01 32360

38 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD04 32500

39 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD05 30000

201 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD06 20000

266 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD07 32500

307 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD08 32500

311 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD09 32500

323 /opt/apps/oracle/product/10.2.0.4.0/dbs/UNNAMED00323 0

After the UNIX admin corrected the file permissions of the RAW device I had to manually add it as a datafile to the standby database.

SQL>alter system set STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL>ALTER DATABASE CREATE DATAFILE ‘/opt/apps/oracle/product/10.2.0.4.0/dbs/UNNAMED00323' as '/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10';

Database altered.

SQL>select FILE#, NAME, bytes/1024/1024 MB from v$datafile where ts#=17;

FILE# NAME MB

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

34 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD03 32500

35 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD 31144

36 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD02 30712

37 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD01 32360

38 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD04 32500

39 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD05 30000

201 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD06 20000

266 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD07 32500

307 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD08 32500

311 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD09 32500

323 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10 32500

11 rows selected.

SQL>alter system set STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;

Database altered.

Bug when creating a physical standby database in 11G

Creating a physical standby database in 11G became a whole lot easier with just one RMAN command – unless you run into a bug :-)

My primary DB runs on Oracle Managed Files on ASM. My physical standby will also run on Oracle Managed Files on ASM on another server, but the ASM diskgroups are not the same.

I used Arub Nanda’s great article Oracle Database 11g: The Top New Features for DBAs and Developers as a guide. The Data Guard article is number 15 in the series and well worth a read if you want to get up and running quickly.
The RMAN script I used:

run {
allocate channel c1 type disk;
allocate auxiliary channel s1 type disk;

SET NEWNAME FOR DATAFILE 1 TO NEW;
SET NEWNAME FOR DATAFILE 2 TO NEW;
SET NEWNAME FOR DATAFILE 3 TO NEW;
SET NEWNAME FOR DATAFILE 4 TO NEW;
SET NEWNAME FOR DATAFILE 5 TO NEW;
SET NEWNAME FOR DATAFILE 6 TO NEW;
SET NEWNAME FOR DATAFILE 7 TO NEW;
SET NEWNAME FOR DATAFILE 8 TO NEW;
SET NEWNAME FOR DATAFILE 9 TO NEW;
SET NEWNAME FOR DATAFILE 10 TO NEW;
SET NEWNAME FOR DATAFILE 11 TO NEW;
SET NEWNAME FOR DATAFILE 12 TO NEW;
SET NEWNAME FOR TEMPFILE 1 TO NEW;

duplicate target database
for standby
from active database
dorecover
spfile
parameter_value_convert 'adevc','adevt'
set db_unique_name='adevt'
set control_files='/opt/apps/oracle/product/11.1.0.7.0/dbs/control01.ctl'
set fal_client='adevt'
set fal_server='adevc'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(adevc,adevt)'
set log_archive_dest_1='location="/dump/oracle/adevt_archivelogs", valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
set log_archive_dest_2='service=adevc LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=casdev'
set log_archive_dest_state_2='enable'
set db_create_file_dest='+TADG1'
set db_create_online_log_dest_1='+TADG1'
;

sql channel c1 'alter system archive log current';
sql channel s1 'alter database recover managed standby database using current logfile disconnect';
}
The error found in the RMAN logfile:
channel c1: starting datafile copy
input datafile file number=00023 name=+CADG1/adevc/datafile/all_ts.408.688130429
RMAN-03009: failure of backup command on c1 channel at 04/30/2010 14:54:55
ORA-19504: failed to create file "+TADG1"
channel c1 disabled, job failed on it will be run on another channel
released channel: c1
released channel: s1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/30/2010 14:54:55
RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on c1 channel at 04/30/2010 14:54:55
ORA-19504: failed to create file "+TADG1"

I found the root cause of the problem on metalink - Standby DATAFILE Creation Failing with RAC / ASM / OMF [ID 787753.1] It turns out to be Bug:7462589. The workaround for the Bug is that the ASM diskgroups on the standby must also be on the primary. The note states that this will be fixed in 11GR2. So my workaround was to create a temporary ASM diskgroup called TADG1 (with only 1 disk) on the primary database server. After that the restore completed successfully.

Long wait times for cursor: pin S and latch: library cache

On our OLTP db we sometimes experienced short bursts (5-15 seconds) of CPU starvation and waits for cursor: pin S or latch: library cache. Especially during busy times. CPU utilization is normally 65% so we were confident that the waits also cause the CPU starvation and not vice versa. In the past few months we have logged multiple SRs with Oracle for the issue but only made progress with 1 where bug Bug 6904068 - High CPU usage when there are "cursor: pin S" waits [ID 6904068.8] was identified. The problem with the other SRs was a case of wrong scope definition. Even though I would upload all the relevant information (ASH, AWR and OS Watcher stats) Oracle Support would look at the top wait in AWR (“db file sequenctial read”) and suggest that we solve that by doing some query tuning and hopefully the mutex/latch problem will also disappear. Even when I convinced them that the problem is not “db file sequential read” they would ask me to take 3x system state dumps when the problem appears. That was an impossible task as the first sytem state dump would kick in when the problem is already occurring, so we could never give support the info they claimed they needed to diagnose the problem.

We were in the fortunate position to ask Tanel Poder to have a look. He also posted some info about the problem and how he systematically solved it. And with the same info I always give to Oracle support, he could correctly identify the problem in a couple of minutes! Most amazing is that the solution is available in the Oracle Documentation. 10g Release 2 (10.2) for UNIX-Based Operating Systems. B15658-02. Appendix B Administering Oracle Database on HP-UX.

What the documentation says:
On HP-UX, most processes use a time-sharing scheduling policy. Time sharing can have detrimental effects on Oracle performance by descheduling an Oracle process during critical operations, for example, when it is holding a latch. HP-UX has a modified scheduling policy, referred to as SCHED_NOAGE, that specifically addresses this issue. Unlike the normal time-sharing policy, a process scheduled using SCHED_NOAGE does not increase or decrease in priority, nor is it preempted.
This feature is suited to online transaction processing environments because online transaction processing environments can cause competition for critical resources.

So the OS scheduler may deschedule a Oracle process while it is holding a mutex/latch because the OS is not aware of what is going on inside Oracle. Now other processes which happen to use that cursor will fail to get the mutex and start spinning. If the mutex holder process has a lower priority than all the other processes who yielded CPU, then it may not get onto CPU soon enough, so it still holds that mutex and the other processes spin and use CPU trying to get that mutex.

How Tanel proved it:
Below are queries from v$event_histogram for the top 2 problem waits. Notice the number of histogram buckets where the wait time is > 512ms:

EVENT_NAME WAIT_TIME_MILLI WAIT_COUNT WAIT%
------------------------------ --------------- -------------- ------
latch: library cache 1 815,980 52.76
latch: library cache 2 187,232 12.11
latch: library cache 4 168,402 10.89
latch: library cache 8 138,053 8.93
latch: library cache 16 102,906 6.65
latch: library cache 32 69,149 4.47
latch: library cache 64 43,797 2.83
latch: library cache 128 11,916 0.77
latch: library cache 256 3,867 0.25
latch: library cache 512 1,005 0.06
latch: library cache 1,024 1,108 0.07
latch: library cache 2,048 1,516 0.10
latch: library cache 4,096 1,293 0.08
latch: library cache 8,192 351 0.02

EVENT_NAME WAIT_TIME_MILLI WAIT_COUNT WAIT%
------------------------------ --------------- -------------- ------
cursor: pin S 1 59,309,179 86.63
cursor: pin S 2 5,988,534 8.75
cursor: pin S 4 2,313,442 3.38
cursor: pin S 8 675,114 0.99
cursor: pin S 16 113,084 0.17
cursor: pin S 32 23,804 0.03
cursor: pin S 64 15,434 0.02
cursor: pin S 128 9,643 0.01
cursor: pin S 256 4,247 0.01
cursor: pin S 512 2,912 0.00
cursor: pin S 1,024 2,871 0.00
cursor: pin S 2,048 2,626 0.00
cursor: pin S 4,096 1,980 0.00
cursor: pin S 8,192 967 0.00
cursor: pin S 16,384 278 0.00
cursor: pin S 32,768 32 0.00

ps –efl > ps.txt
cat ps.txtgrep ".:..:.."awk '{ printf "%6d %50s\n", $7, $15 }'>ps1.txt
cat ps.txtgrep -v ".:..:.."awk '{ printf "%6d %50s\n", $7, $16 }'>ps2.txt
cat ps1.txt ps2.txtgrep ora.*abcprdsort -nuniq -csort -nbrhead -20

NUM
PROCS PRIO PROCESS_NAME
----- ------ -------------------
1936 154 oracleabcprd
25 148 oracleabcprd
4 241 oracleabcprd
1 240 oracleabcprd
1 235 oracleabcprd
1 229 oracleabcprd
1 223 oracleabcprd
1 207 oracleabcprd
1 195 oracleabcprd
1 187 oracleabcprd
1 181 oracleabcprd
1 179 oracleabcprd
1 154 ora_smon_abcprd
1 154 ora_rvwr_abcprd
1 154 ora_rsm0_abcprd
1 154 ora_reco_abcprd
1 154 ora_rbal_abcprd
1 154 ora_qmnc_abcprd
1 154 ora_q004_abcprd
1 154 ora_q003_abcprd
Note the different priorities of the oracle client processes. After we enabled SCHED_NOAGE for Oracle Database as described in the documentation. The output looked like this:

EVENT_NAME WAIT_TIME_MILLI WAIT_COUNT WAIT%
------------------------------ --------------- -------------- ------
latch: library cache 1 53,212 57.39
latch: library cache 2 10,517 11.34
latch: library cache 4 11,069 11.94
latch: library cache 8 5,308 5.72
latch: library cache 16 3,715 4.01
latch: library cache 32 3,506 3.78
latch: library cache 64 5,254 5.67
latch: library cache 128 131 0.14
latch: library cache 256 5 0.01
latch: library cache 512 7 0.01
latch: library cache 1,024 3 0.00

EVENT_NAME WAIT_TIME_MILLI WAIT_COUNT WAIT%
------------------------------ --------------- -------------- ------
cursor: pin S 1 2,267 15.46
cursor: pin S 2 0 0.00
cursor: pin S 4 0 0.00
cursor: pin S 8 0 0.00
cursor: pin S 16 0 0.00
cursor: pin S 32 0 0.00
cursor: pin S 64 0 0.00
cursor: pin S 128 12,380 84.45
cursor: pin S 256 12 0.08

NUM
PROCS PRIO PROCESS_NAME
----- ------ -------------------
1992 178 oracleabcprd
1 178 ora_smon_abcprd
1 178 ora_rvwr_abcprd
1 178 ora_rsm0_abcprd
1 178 ora_reco_abcprd
1 178 ora_rbal_abcprd
1 178 ora_qmnc_abcprd
1 178 ora_q001_abcprd
1 178 ora_q000_abcprd
1 178 ora_psp0_abcprd
1 178 ora_pmon_abcprd
1 178 ora_p009_abcprd
1 178 ora_p008_abcprd
1 178 ora_p007_abcprd
1 178 ora_p006_abcprd
1 178 ora_p005_abcprd
1 178 ora_p004_abcprd
1 178 ora_p003_abcprd
1 178 ora_p002_abcprd
Note that the number of wait buckets are less. The wait times are sort as it should be. And the process priority report shows that all oracle client processes are on the same priority.

Thanks Tanel!