Wednesday, October 14, 2020

Unable to obtain current patch information due to error: 16000

Error:

When starting up a standby database in open mode I noticed the following error in the alertlog:

Unable to obtain current patch information as the database or pluggable database was opened for read-only access.
===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 16000
===========================================================



Analysis:

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

 

Fix:

Unfortunately there is no fix for the time being as of now, but the following bug is already being worked by the development team:


Bug 21098077 - PACKAGES USED TO OBTAIN DATABASE PATCH INFORMATION SHOULD HANDLE READ-ONLY DB

Meanwhile, This error is minor and doesn't indicate any problem with the database, so it can be safely ignored.

Monday, October 12, 2020

SQL Tuning Script

 This shell script will help you tune a SQL Statement in Oracle by doing the following functions:

It will ask you for the SQL Statement sqlid, then it will do the following:

1- Display the SQL Statement Statistics like: Executions, ELAPSED/CPU time, Waits, I/O stats:

 


2- Display the SQL TEXT along with populating the bind variables:

 

 

3- Display the current Execution Plan:


4- Display the history of Execution Plans and execution statistics [if found]. [This section quoted from Tim Gorman's sqlhistory.sql script after taking his permission (many thanks to Tim)]

This will help the user investigate the statement performance over the time by checking the change to the execution plan.


5- Display the SQL Plan Baseline, if the statement is already using a baseline for execution plan stability. [SQL Plan Baseline is an 11g new feature maintains the execution plan stability by storing the execution plans for a statement and use the best plan out of them in case the user didn't fix a plan yet]

6- If the statement is not part of SQL Plan Baseline, the script will offer the option of fixing an execution plan by creating a new baseline for this statement: [If the DB version is 11g+]

 


 7- If the statement is already in a SQL Plan Baseline, it will check the number of the available plans for this statement, if the plans are more than 1 plan, the script will display all the plans details, and will offer the option to fix a plan:

 

 You can view the plan details in a tabular format using the shown Select statement: [in 12c+]

select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical'));

 

 Then you can get back to the script and decide which plan to FIX for this statement:


Then it will show the statement to use in case you want to rollback the change by dropping the Baseline (yes we can disable the baseline but disabling the baseline will not eliminate the optimizer of using it, disabling the baseline cannot be considered as a complete rollback here).

8- Finally the script will display the option of tuning the SQL Statement by submitting a tuning task using SQL Tuning Advisor feature [This feature is a licensed feature, please don't use it unless your company is already acquired the Diagnostic & Tuning License to avoid licensing audit troubles in the future]:

This script is provided "AS IS" without any warranty. This script is tested on Linux environments but you have to test it first on a test environment before start to use it on production.

 Special thanks to Farrukh Salman "Karkoor" for his contribution in this script.

You can Download the script from this link:

https://www.dropbox.com/s/fll8e7ybrjgsexo/sql_tune.sh?dl=0


GitHub version:



Thursday, October 8, 2020

Unable to obtain current patch information due to error: 20013 When Starting up a DB Instance

Error:

When Starting a RAC DB instance I noticed the following error in the alertlog which was hampering the display of installed patches list:

QPI: opatch executable does not exist, opatch
QPI: OPATCH_INST_DIR not present:/u01/oracle/12.2.0.3/db/OPatch
Unable to obtain current patch information due to error: 20013, ORA-20013: DBMS_QOPATCH ran mostly in non install area
ORA-06512: at "SYS.DBMS_QOPATCH", line 2327
ORA-06512: at "SYS.DBMS_QOPATCH", line 854
ORA-06512: at "SYS.DBMS_QOPATCH", line 634
ORA-06512: at "SYS.DBMS_QOPATCH", line 2309



Analysis:

The top of the error message is clearly saying:

OPATCH_INST_DIR not present:/u01/oracle/12.2.0.3/db/OPatch

The right location of OPatch in my environment is /u01/oracle/12.2.0.3/OPatch, not as mentioned in the error message: /u01/oracle/12.2.0.3/db/OPatch

Checking the OPATCH directory location in DBA_DIRECTORIES:

SQL> set lines 170
           col DIRECTORY_NAME for a30
           col DIRECTORY_PATH for a100
           select DIRECTORY_NAME,DIRECTORY_PATH from DBA_DIRECTORIES where DIRECTORY_NAME like 'OPATCH%';


It's not only OPATCH_INST_DIR which pointing to the wrong directory, but the other OPATCH directories as well like; OPATCH_SCRIPT_DIR and OPATCH_LOG_DIR.


Solution:

As per note 2033620.1, the right OPATCH directories path should be as the following:


OPATCH_INST_DIR:       $ORACLE_HOME/OPatch     
OPATCH_SCRIPT_DIR:  $ORACLE_HOME/QOpatch
OPATCH_LOG_DIR:       $ORACLE_HOME/QOpatch

So let's correct those paths using the following commands:

SQL> CREATE OR REPLACE DIRECTORY OPATCH_INST_DIR   AS '/u01/oracle/12.2.0.3/OPatch';
SQL> CREATE OR REPLACE DIRECTORY OPATCH_SCRIPT_DIR AS '/u01/oracle/12.2.0.3/QOpatch';
SQL> CREATE OR REPLACE DIRECTORY OPATCH_LOG_DIR    AS '/u01/oracle/12.2.0.3/QOpatch';

SQL> select DIRECTORY_NAME,DIRECTORY_PATH from DBA_DIRECTORIES where DIRECTORY_NAME like 'OPATCH%';

To verify that everything is OK after correcting the directory location:

Run the following command, where it should run successfully without returning any errors:

# $ORACLE_HOME/OPatch/datapatch  -prereq



References:
Datapatch fails with "ORA-20009:" “ORA-20013: DBMS_QOPATCH ran mostly in non install area” ORACLE_HOME is a SYMBOLIC LINK Doc ID 2033620.1




Monday, October 5, 2020

New Sections in 19c AWR In Comparison With 11g's AWR

AWR report in 19c is packed up with many new sections. In this post I'll highlight the new sections comparing with 11g's AWR report.

Starting with "Report Summary" sections:

- Top ADDM Findings: 

- TOP 10 Foreground Wait Events: (Instead of TOP 5 in 11g)

- Wait classes: (new table)

 

- IO Profile (new table)

 

- Recovery Progress: [In case the report is taken from a Standby DB]

- In-Memory Area new item (Cache Size)


Moving to "Main Report" section:

Drilling down to each of these brand-new sections:

Goldengate related statistics are now available in the AWR: 


AQ's stats are new in AWR as well:

And here is the nice part, in 19c the ASH report is now embedded inside the AWR report, whereas you don't have to generate ASH reports separately anymore

Same like ASH, ADDM report is also available:

That's not all; there is still some minor new details in the 19c's AWR report I didn't cover in this post to make it briefed.

Sunday, October 4, 2020

CRS-2974: unable to act on resource

Problem:


On a 19c RAC DB when shutting down any instance gracefully using srvctl command it throws this error:

$ srvctl stop instance -d proto -i proto2
PRCD-1131 : Failed to stop database proto and its services on nodes r6ttnode2
PRCR-1133 : Failed to stop database proto and its running services
PRCR-1132 : Failed to stop resources using a filter
CRS-2974: unable to act on resource 'ora.proto.db' on server 'r6ttnode2' because that would require stopping or relocating resource 'ora.proto.jdbcapp.svc' but the appropriate force flag was not specified


Analysis:


If you have created a service on RAC 19c with -preferred option, SRVCTL command will not shutdown the instance unless you use -force parameter, which will not allow the transactions to failover to the other available node in case you are using TAF policy (Transparent Application Failover).


Workarounds:


- [Recommended] First stop the services on the instance you want to shut down, then shutdown the instance gracefully using SRVCTL command: [This will allow the QUERIES to failover to the other available node]


$ srvctl stop service  -d proto -i proto2
$ srvctl stop instance -d proto -i proto2

- [Less recommended] Shutdown the instance gracefully from the SQLPLUS console: [This will allow the QUERIES to failover to the available node]


$ export ORACLE_SID=proto2
SQL> SHUTDOWN IMMEDIATE;

Note: Unless you are having and ADG or GoldenGate setup, SHUTDOWN ABORT of a RAC instance is considered a safe procedure as well. I personally start with SHUTDOWN IMMEDIATE as I've faced rare cases in the past; where ABORTING an instance can corrupt any controlfiles.

- [Least recommended] Shutdown the instance forcefully using SRVCTL command: [Current Running QUERIES will fail]


$ srvctl stop instance -d proto -i proto2 -force

Note:
This SRVCTL error doesn't show up when stopping the whole DB (all instances at once): using: $ srvctl stop database -d proto

Friday, October 2, 2020

Script to Monitor and Report Audit Trail Records in sys.aud$



 

Script Description:

This script monitors the major audit trail records and failed login attempts, it can be easily deployed and customized. So far, it has been tested on Linux Environment.

Script Category: Auditing & Security

How it works:

You have to schedule this script to run in the OS scheduler "crontab", you can decide how fast you should receive a notification whenever a new "major" audit event get created. Then set the same schedule interval in minutes inside the script to determine the window of time which the script will fetch the data from sys.aud$.

e.g. If you schedule the script to run every 30 minutes in the crontab, you should set the Threshold "MINUTES" inside the scripts to 30.

The script uses sendmail as the only notification method, so you have to set the following parameter replacing the pattern youremail@yourcompany.com to your actual Email:

EMAIL="youremail@yourcompany.com"

In addition, the script has many parameters to help you narrow down the scope of audit actions monitoring, you can change the values in green color:

 HTMLENABLE=Y      # Enable HTML Email Format [Default Enabled].
 RECORDSNUM=1     # Send an Email if the sum of audit records >= the threshold [Default 1 record].
 REPORT_FAILED_LOGINS=Y    # Enable the reporting of failed login attempts. [Default Enabled].
 REPORT_AUDIT_RECORDS=Y  # Enable the reporting of audit records [Default Enabled].
 EXCLUDE_DBUSERS="'dba_bundleexp7'"  # Exclude DB user from reporting their activities [In lowercase]. e.g. EXCLUDE_DBUSERS="'sys','scott'"
 EXCLUDE_OSUSERS="'user1'"    # Exclude OS user from reporting their activities [In lowercase]. e.g. EXCLUDE_OSUSERS="'oracle','grid'"
 EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT'" # Exclude specific AUDIT EVENTS from reporting.

EXCLUDE_ACTIONS is an important parameter to help you exclude specific audit actions from getting reported. This reporting mechanism should cover only crucial activities that need immediate attention like DROP, TRUNCATE, ALTER, ... etc for example; minor activities like Log In, Log Off and SELECT shouldn't be in the scope of such monitoring mechanism; otherwise you will get your mailbox filled unnecessarily.

I've already excluded common audit actions from the scope of getting reported like:

SELECT, SET ROLE, LOGON, LOGOFF, LOGOFF BY CLEANUP, EXPLAIN, PL/SQL EXECUTE, SYSTEM AUDIT

Let's suppose you want to exclude DML's from getting reported; modify the parameter as follows:

EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT','INSERT','UPDATE','DELETE','SESSION REC'"

Ensure that you enclose each audit action between "single quotation" '' and separating between them using "comma" ,

To display all the available actions names that you can use for that parameter, run this statement:

SQL> select distinct action_name from dba_audit_trail order by 1;  

Feel free to add more actions to be excluded to EXCLUDE_ACTIONS parameter, and I encourage you to do so.

Precautions:

The last and the most important remaining point here is indexing sys.aud$ table:

Your audit trail table sys.aud$ may have millions/billions of rows, querying this table frequently can degrade your database performance and most probably it can bring the server on its knees; especially if it has humble resources!

If reporting audit records is important for you, then you must create the following index before start using this script and to avoid any performance degradation it can cause by this script: (Create it with online option to avoid getting the DB hung)

SQL> CREATE INDEX sys.idx_ntimestamp# ON sys.aud$(ntimestamp#) ONLINE;
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS', tabname => 'AUD$', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

When the script run, it will check if sys.aud$ (ntimestamp#) is indexed, if it's not; it will display a warning message along with the recommended index creation statement to execute.


It is worth to mention that Oracle doesn't recommend creating any indexes on sys.aud$ table as it may degrade the performance of record insertions to sys.aud$, in addition to other minor reasons mentioned in this Note: The Effect Of Creating Index On Table Sys.Aud$ (Doc ID 1329731.1)

From my experience; I use this script along with the creation of the above index on very busy OLTP databases without noticing any performance degradation. Creating an index on sys.aud$ table is like creating an index on any other "busy table"; Yes it will add an extra overhead (to maintain the index) when audit records are inserted, but still Oracle can handle this efficiently.

Indeed, you are the right one to decide whether adding an index to sys.aud$ table will degrade the performance or not; based on the daily volume of audit data that get inserted to sys.aud$, along with the hardware resources of your DB server.

One last thing, you may ask why I coded the script to select directly from sys.aud$ instead of using DBA_AUDIT_TRAIL; the answer is that I've noticed in many cases that selecting from DBA_AUDIT_TRAIL will force the optimizer to not use the above mentioned indexes leading to an expensive full table scans, this is why I avoided using it.

Please feel free to share your thoughts and suggestions.

To download the script:

https://www.dropbox.com/s/vja3hptpzy7a3in/monitor_audit_records.sh?dl=0

After you click on that link, click on the down arrow at the top-right side of the page:



GitHub Version: