DBA BUNDLE 5.8

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

https://www.dropbox.com/s/k96rl0f4g39ukih/DBA_BUNDLE5.tar?dl=0

The new features include:
 

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

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

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 work around this bug and get the application back running:

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_6ef2b0406c073b2e',
    plan_name      => 'SQL_PLAN_6xwph81q0fftfdbdc6b11',
    attribute_name  => 'enabled',
    attribute_value  => 'NO');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

Conclusion:

SQL Statements having a Baseline may hit bug 29942554 if a new plan get accepted and added to the same baseline. As a workaround, disable the new accepted plan inside the Baseline.

"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.

Setup AWR on Standby DB

 Starting with Oracle 12.2, AWR can be configured and run against Standby DB, before that release (since 11gR2) the only option available for having similar data was by generating an ASH report on the Standby DB, which was limited by how much data is still available in the standby instance memory.

Here are the briefed steps of how to set up AWR on the standby DB (Steps are the same for both RAC & standalone DB):

[On Primary]: Unlock user sys$umf

SQL> alter user sys$umf identified by "abc#123#ORCL" account unlock;


[On Primary]: Enable "_umf_remote_enabled" parameter:

SQL> alter system set "_umf_remote_enabled"=TRUE scope=BOTH;


[On Primary]: Create two DB links, from primary to standby and vice versa:

SQL> drop database link PR_TO_STBY;
SQL> drop database link STBY_TO_PR;

SQL> create database link PR_TO_STBY CONNECT TO sys$umf IDENTIFIED BY "abc123" using 'ORCL';
SQL> create database link STBY_TO_PR CONNECT TO sys$umf IDENTIFIED BY "abc123" using 'ORCLSTBY';
 

--Make sure the DB Links are working:

select * from dual@PR_TO_STBY;

D
-
X

select * from dual@STBY_TO_PR;

D
-
X
 

[On Primary] Add the primary DB to the new AWR Topology:

SQL> exec dbms_umf.unconfigure_node;
SQL> exec dbms_umf.configure_node ('prim');


[On Standby] Add the Standby DB to the new AWR Topology:

SQL> exec dbms_umf.UNCONFIGURE_NODE;
SQL> exec dbms_umf.configure_node ('stby', 'STBY_TO_PR');

[On Primary]: Create a new Topology:

SQL> exec DBMS_UMF.drop_topology   ('Topology_1');
SQL> exec DBMS_UMF.create_topology ('Topology_1');


[On Primary]: Register the standby database with the topology:

SQL> exec DBMS_UMF.register_node ('Topology_1', 'stby', 'PR_TO_STBY', 'STBY_TO_PR', 'FALSE', 'FALSE');

[On Primary]: Register the Standby DB as a remote DB: [May take from 2-75 seconds]

SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stby');

If you encounter "ORA-13519": Try to unregister and register the DB again:
SQL> exec DBMS_WORKLOAD_REPOSITORY.unregister_remote_database('stby','Topology_1',TRUE);
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stby');

If encounter "ORA-20509: Service not registered": Run the following commands in sequence:
SQL> exec DBMS_UMF.unregister_node ('Topology_1', 'stby');
SQL> exec DBMS_UMF.register_node ('Topology_1', 'stby', 'PR_TO_STBY', 'STBY_TO_PR', 'FALSE', 'FALSE');
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stby');

[On Primary]: Verify the above steps by running these queries:

SQL>     set line 170
    col topology_name format a15
    col node_name       format a15
    select * from dba_umf_topology;
    select TOPOLOGY_NAME, NODE_NAME, NODE_ID, NODE_TYPE from umf$_registration;
    select * from dba_umf_registration;


[On Primary]: Create a Snapshot on the Standby DB:

SQL> alter system archive log current;
SQL> exec dbms_workload_repository.create_remote_snapshot('stby');

Note: By default one snapshot will be created automatically every one hour on the Standby DB.

[On Primary or Standby]: To generate an AWR report for the standby DB:

SQL> @?/rdbms/admin/awrrpti
When asked for dbid, enter the DBID of the Standby DB (from the list).

 

Change the AWR SNAPSHOT Frequency on the Standby:

Unfortunately, the only way to change the AWR Snapshot frequency is to change the frequency on the Primary, then it will reflect on the Standby!!:

-- Changing the AWR Snapshot Frequency to 30 minutes on both Primary & Standby:

SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30);

[Up to the date of writing this post; Oracle didn't provide a way to change the SNAPSHOT frequency on Standby without impacting the Snapshot frequency on the Primary]
 

Note: Whenever a SWITCHOVER happen between primary and standby you have to do a similar role switch for AWR settings as well using this command:

SQL> exec DBMS_UMF.SWITCH_DESTINATION(topology_name =>'Topology_1',force_switch=> 'TRUE');

In case the above command failed after the switchover and the snapshots are not getting automatically generated on the new primary DB, try to re-deploy all the above mentioned steps again to re-configuring AWR on the new standby.

References:
https://www.oracle.com/technetwork/database/availability/redo-apply-2745943.pdf