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:


Monday, September 28, 2020

DBA BUNDLE 5.8

September's release of the DBA Bundle is now available: (It's a combination of Aug+Sep releases)

dbdailychk.sh [For generating a database Health Check Report]:
- Added Memory utilization check feature, combining both RAM and SWAP in the calculation:
The following parameters need to be edited by the user to match the environment requirements:
 CHECK_MEMORY=Y          # REPORT LOW MEMORY USING BELOW TWO DEFINED THRESHOLDS COMBINED [OS]
 USEDPHYSICALMEM_PCT=95 # THRESHOLD FOR RAM %UTILIZATION                                [OS]
 USEDSWAPMEM_PCT=75     # THRESHOLD FOR SWAP SPACE %UTILIZATION                         [OS]

How it works?
 
Low memory will get reported if both; RAM %USED is >=95% and SWAP space %USED is >=75% 
 
Above thresholds are generic; feel free to change them as per your experience with your systems.
e.g. if you feel your system start to get slow when the RAM %USED reach 85% and SWAP space utilization hit 10%, then you edit those parameters like this:
USEDPHYSICALMEM_PCT=85
USEDSWAPMEM_PCT=10
 
If you are not interested in getting notified about Memory high utilization, just disable this function by setting:
CHECK_MEMORY=N
 
- Reorganized the script's sections to make it easier for the user to edit and understand.
 
rebuild_table.sh [For rebuilding a table online]:
- Added the feature of rebuilding the table on a different tablespace.
- Enhanced the formatting of the outputs and the logfile.
For a detailed discussion on this script new features please visit: http://dba-tips.blogspot.com/2020/09/major-update-to-rebuild-table-script.html 
 
parameter_val.sh [For viewing Normal & Hidden Parameters]
- User can view all NON-DEFAULT parameters.
- The user can list all the parameters on the instance.
 
user_ddl.sh [For generating User creation DDL statement]
- If the database version is 12c+ the script will automatically use dbms_metadata.get_ddl to generate the DDL (User creation + Privileges + showing quotas on tablespaces).

 
- Minor functionality enhancements and bug fixes included in these scripts:
 
export_data.sh   [For exporting data]
- Fixed a directory creation bug.
 
dbalarm.sh [For database monitoring]
- Excluded the reporting of ORA-235 which triggers during the unlocking of the control file" as advised by MOS 2312580.1
 
rebuild_table.sh  [For rebuilding a table online]:
- Correction of DBA_REDEFINITION_ERRORS query. 
 
object_size.sh [For calculating the total object size]
- Enhanced the formatting of the outputs. 
 
aliases_DBA_BUNDLE.sh [The main Bundle deployment script]
- Removed the constraint of extracting the bundle under User's home directory, now the user is free to extract the bundle anywhere.

 
If you are new to the DBA Bundle, please visit below link to understand its functions:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

Thursday, September 17, 2020

Major Update to Rebuild Table Script

 Few months back:

I've shared a script to rebuild tables in Oracle using online rebuild options like [(DBMS_REDEFINITION (9i+) & ALTER TABLE MOVE ONLINE (12.2+)] if those features are not available in the database (i.e. database edition is Standard Edition) the script will utilize the legacy "ALTER TABLE MOVE" command along with rebuilding indexes.

The original post link: http://dba-tips.blogspot.com/2019/05/rebuild-table-script-and-reclaim-wasted.html

What is new?

I received many enhancements requests from readers along with bug fixing and here is a summary of new features:

Major Enhancements:

- When using ALTER TABLE MOVE ONLINE or the legacy one, you will be prompted to choose whether to keep the table in its original tablespace or you want to move the table to another tablespace:


Leaving it blank will rebuild the table on its original tablespace.

Of course, if you choose a different tablespace the script will automatically calculate the free space on the tablespace and make sure it's sufficient to host the table after the rebuild.

Note: This feature is not added to DBMS_REDEFINITION, although it's available starting with 12.2,  it will need the user to specify the tablespace for each index separately, I thought it will be better to not add this feature to not confuse the user, anyways the user can still use "ALTER TABLE MOVE ONLINE" option which is already available on 12.2 which will give similar result.

- Bug fixes along with reorganizing the steps to make it more simple for the user.

Minor Enhancements:

- User can enter blank value when asked for Parallelism Degree to utilize the maximum CPU power on server without the need to calculate it. "For those who are lazy to check CPU count on the machine 😊"

- Entering 0 value to Parallelism Degree will be accepted to disable the parallelism.


By the way, the info statement shown above "don't exceed the current number of CPUs" is my advice, of course you can exceed the number of CPUs and it may be faster for you, but in most cases I studied it won't help much, better you validate that yourself on your hardware!

- More notifications, messages added to the script to keep the user updated with what is going on.

- Added more tolerance for user's wrong inputs. 

- If the table selected for rebuild is being replicated by goldengate or streams, the user will get an info message that target table may miss some data during the table rebuild (this took me days of testing ad building case scenarios till I figured out this limitation!). By the way, this is not a script limitation, it's how the Oracle rebuild technology works internally so far.

- changed the color of important messages from green to yellow to give it more appearance.

 

To download the script:

https://www.dropbox.com/s/bmgbc0u76okokcs/rebuild_table.sh?dl=0


GitHub version is updated as well

Monday, September 7, 2020

ORA-13831: SQL profile or patch name specified is invalid [On 19c]

 Problem:

As of a sudden one critical application connecting to a 19c database crashed and kept throwing the following error in its logs:

ORA-13831: SQL profile or patch name specified is invalid


Analysis:

A quick search revealed that we're hitting Bug 29942554 

Because I had to get the application up and running quickly I've decided to go with the workaround (and luckily this bug has a workaround). But why I'm writing this post then? Because the workaround is not clear, so I thought of writing this article.


Solution:

1. Searching the Failing SQL Statement details shows that a new plan was created and accepted just few seconds before the application crash, and that would be the reason for triggering this bug:

SQL> SELECT SQL_HANDLE,PLAN_NAME,CREATED,LAST_MODIFIED,FIXED,ADAPTIVE,ENABLED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%SELECT TYP_LF_BOOKED_HELD(FLT.FAR%' ORDER BY CREATED;



 2. Disabling that latest created SQL Plan for the problematic SQL will workaround this bug and get the application back running:


SQL> set serveroutput on
          variable a_var number;
 exec :a_var :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
 SQL_HANDLE      => 'SQL_6ef2b0406c073b2e',
 PLAN_NAME       => 'SQL_PLAN_6xwph81q0fftfdbdc6b11',
 ATTRIBUTE_NAME  => 'enabled',
 ATTRIBUTE_VALUE => 'NO');

Conclusion:

If the failing SQL Statement is already inside a Baseline with a fixed plan but not yet enabled, this may make this SQL vulnerable to hit bug 29942554 if a new plan get accepted and added to the baseline.

As a workaround disable the new accepted plan inside the Baseline. To protect this SQL from hitting this bug again, try to enable the plan which already marked fixed in the baseline. (these are my words, not the official bug fix 😊 ).


Friday, September 4, 2020

"ORA-14451: unsupported feature with temporary table" Error When Creating a PRIVATE TEMPORARY TABLE in 19c

Introduction:
Private Temporary Tables (PTT) is a new feature in Oracle 18c, they have the similar characteristics of global temp tables but instead of being created under the TEMPORARY tablespace, they get created in the session's PGA, which is a great feature for boosting the performance (if your PGA is big enough to host this kind of  PTT's).

Problem:

SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_users ON COMMIT PRESERVE DEFINITION  AS SELECT * FROM dba_users;
CREATE PRIVATE TEMPORARY TABLE ORA$PTT_users ON COMMIT PRESERVE DEFINITION  AS SELECT * FROM dba_users
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

SQL> sho user
USER is "SYS"

Analysis:


Above misleading error will pop-up if you create a Private Temporary Table (PTT) while connecting as SYS, SYSDBA, SYSRAC or SYSBACKUP.


Solution:


Create the PRIVATE TEMPORARY TABLE (PTT) with any other user not connecting as SYSDBA, SYSRAC, SYSBACKUP,... etc.