Saturday, January 23, 2021

Execute orachk (part of AHF 20.4)

 [oracle@ol8 ~]$ /opt/oracle.ahf/bin/tfactl access lsusers

.--------------------------------.
|        TFA Users in ol8        |
+-----------+---------+----------+
| User Name | Status  | Promoted |
+-----------+---------+----------+
| oracle    | Allowed | false    |
'-----------+---------+----------'

[oracle@ol8 ~]$ orachk


List of running databases

1. orcl
2. None of above

Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1]. 1
.  .
Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

-------------------------------------------------------------------------------------------------------
                                                 Oracle Stack Status
-------------------------------------------------------------------------------------------------------
  Host Name       CRS Installed       ASM HOME  RDBMS Installed    CRS UP    ASM UP  RDBMS UP    DB Instance Name
-------------------------------------------------------------------------------------------------------
        ol8                        No           No          Yes           No       No      Yes                orcl
-------------------------------------------------------------------------------------------------------


Copying plug-ins


*** Checking Best Practice Recommendations ( Pass / Warning / Fail ) ***

Collections and audit checks log file is
/opt/oracle.ahf/data/ol8/orachk/user_oracle/output/orachk_ol8_orcl_012321_160827/log/orachk.log

============================================================
                    Node name - ol8
============================================================
. . . . . .


 Collecting - Database Parameters for orcl database
 Collecting - Database Undocumented Parameters for orcl database
 Collecting - List of active logon and logoff triggers for orcl database
 Collecting - CPU Information
 Collecting - Disk I/O Scheduler on Linux
 Collecting - DiskMount Information
 Collecting - Kernel parameters
 Collecting - Maximum number of semaphore sets on system
 Collecting - Maximum number of semaphores on system
 Collecting - Maximum number of semaphores per semaphore set
 Collecting - Memory Information
 Collecting - OS Packages
 Collecting - Operating system release information and kernel version
 Collecting - Patches for RDBMS Home
 Collecting - Table of file system defaults
 Collecting - number of semaphore operations per semop system call


Data collections completed. Checking best practices on ol8.
------------------------------------------------------------

 WARNING =>  Linux swap configuration does not meet recommendation
 WARNING =>  Package compat-libcap1-1.10-7.el7-x86_64 is recommended but not installed
 INFO =>     Most recent ADR incidents for /u01/db
 INFO =>     Oracle GoldenGate failure prevention best practices
 INFO =>     user_dump_dest has trace files older than 30 days for orcl
 INFO =>     At some times checkpoints are not being completed for orcl
 WARNING =>  Package compat-libstdc++-33-3.2.3-61-x86_64 is recommended but not installed
 WARNING =>  One or more redo log groups are not multiplexed for orcl
 WARNING =>  Primary database is not protected with Data Guard (standby database) for real-time data protection and availability for orcl
 INFO =>     Important Storage Minimum Requirements for Grid & Database Homes
 CRITICAL => Operating system hugepages count does not satisfy total SGA requirements
 FAIL =>     Table AUD$[FGA_LOG$] should use Automatic Segment Space Management for orcl
 FAIL =>     Database parameter DB_LOST_WRITE_PROTECT is not set to recommended value on orcl instance
 INFO =>     The Optimizer Fix 28345522 is disabled by default for orcl
 WARNING =>  Database parameter DB_BLOCK_CHECKING on primary is not set to the recommended value. for orcl
 INFO =>     The Optimizer Fix 22149010 is disabled by default for orcl
 INFO =>     The Optimizer Fix 25167306 is disabled by default for orcl
 INFO =>     The Optimizer Fix 28965084 is disabled by default for orcl
 INFO =>     The Optimizer Fix 28776811 is disabled by default for orcl
 INFO =>     The Optimizer Fix 29132869 is disabled by default for orcl
 INFO =>     The Optimizer Fix 28498976 is disabled by default for orcl
 INFO =>     The Optimizer Fix 29687220 is disabled by default for orcl
 INFO =>     The Optimizer Fix 30232638 is disabled by default for orcl
 INFO =>     The Optimizer Fix 29930457 is disabled by default for orcl
 INFO =>     The Optimizer Fix 29304314 is disabled by default for orcl
 INFO =>     The Optimizer Fix 28776431 is disabled by default for orcl
 INFO =>     Operational Best Practices
 INFO =>     Database Consolidation Best Practices
 INFO =>     Computer failure prevention best practices
 INFO =>     Data corruption prevention best practices
 INFO =>     Logical corruption prevention best practices
 INFO =>     Database/Cluster/Site failure prevention best practices
 INFO =>     Client failover operational best practices
 WARNING =>  Oracle patch 29423227 is not applied on RDBMS_HOME /u01/db
 WARNING =>  Oracle patch 31031240 is not applied on RDBMS_HOME /u01/db
 WARNING =>  Oracle patch 29867728 is not applied on RDBMS_HOME /u01/db
 WARNING =>  Oracle patch 26749785 is not applied on RDBMS_HOME /u01/db
 WARNING =>  Oracle patch 29259068 is not applied on RDBMS_HOME /u01/db
 INFO =>     The Optimizer Fix 30347410 is disabled by default for orcl
 INFO =>     The Optimizer Fix 27261477 is disabled by default for orcl
 WARNING =>  Redo log files should be appropriately sized for orcl
 WARNING =>  Oracle clusterware is not being used
 WARNING =>  RAC Application Cluster is not being used for database high availability on orcl instance
 WARNING =>  Flashback on PRIMARY is not configured for orcl
 INFO =>     Database failure prevention best practices
 WARNING =>  fast_start_mttr_target has NOT been changed from default on orcl instance
 FAIL =>     Active Data Guard is not configured for orcl
 INFO =>     Parallel Execution Health-Checks and Diagnostics Reports for orcl
 INFO =>     Oracle recovery manager(rman) best practices
 INFO =>     Database feature usage statistics for orcl
 WARNING =>  Consider investigating changes to the schema objects such as DDLs or new object creation for orcl
 WARNING =>  Consider increasing the value of the session_cached_cursors database parameter for orcl
 WARNING =>  Consider investigating the frequency of SGA resize operations and take corrective action for orcl
Best Practice checking completed. Checking recommended patches on ol8
--------------------------------------------------------------------------------
Collecting patch inventory on ORACLE_HOME /u01/db
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
1 Recommended RDBMS patches for 190000 from /u01/db on ol8
--------------------------------------------------------------------------------
Patch#   RDBMS    ASM     type                Patch-Description
--------------------------------------------------------------------------------
 32218454yes              merge                                                                                           
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
              RDBMS homes patches summary report
--------------------------------------------------------------------------------
Total patches  Applied on RDBMS Applied on ASM ORACLE_HOME
--------------------------------------------------------------------------------
1              1              0                /u01/db
--------------------------------------------------------------------------------

------------------------------------------------------------
Detailed report (html) -  /opt/oracle.ahf/data/ol8/orachk/user_oracle/output/orachk_ol8_orcl_012321_160827/orachk_ol8_orcl_012321_160827.html

UPLOAD [if required] - /opt/oracle.ahf/data/ol8/orachk/user_oracle/output/orachk_ol8_orcl_012321_160827.zip

Check ORACLE_PATCH 29259068 for RDBMS home

Recommendation
Benefit / Impact:

This would fix one of the top issues since 12.2 where automatic optimizer statistics collection job takes long time due to MERGE query on MON_MODS_ALL$. The performance of the MERGE query is now improved.

Risk:

Automatic optimizer statistics collection job would take long time due to poor performance of MERGE query on MON_MODS_ALL$.

Action / Repair:

It is highly recommended to download & apply the Patch 29259068.
Links
  1. Note: 2708396.1 - Merge on SYS.MON_MODS_ALL$ Running Slow During Auto Stats Job


Check ORACLE_PATCH 26749785 for RDBMS home

Recommendation
Benefit / Impact:

Currently there is no option to disable automatic statistics advisor that runs everyday during maintenance window and incur huge SYSAUX space. This fix would provide the new preference "AUTO_STATS_ADVISOR_TASK" in DBMS_STATS to enable/disable AUTO_STATS_ADVISOR_TASK manually. 

Risk:

The statistics advisor runs everyday during maintenance window and incurs high SYSAUX space.

Action / Repair:

It is highly recommended to download & apply the Patch 26749785. You can run the following command to disable advisor.

SQL> exec DBMS_STATS.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
Links
  1. Note: 2686022.1 - How To Disable Optimizer Statistics Advisor From 12.2 Onwards


Check ORACLE_PATCH 29867728 for RDBMS home

Recommendation
Benefit / Impact:

This would fix one of the critical issues reported since 12.2 regarding SQL regression with UNION ALL PUSHED PREDICATE (JPPD) query transformation.

Risk:

There could be SQL plan regression found for SQL statement using UNION ALL where the plan shows 'UNION ALL PUSHED PREDICATE' and runs for long time.

Action / Repair:

It is highly recommended to download & apply the Patch 29867728. Remember to enable the fix control after applying the patch.

ALTER SYSTEM SET "_FIX_CONTROL"="29867728:1" SCOPE=BOTH;
Links
  1. Note: 29867728.8 - Bug 29867728 - UNION ALL PUSHED PREDICATE Is Chosen On 19c When UNION-ALL Plan May Have Less Cost

Check ORACLE_PATCH 31031240 for RDBMS home

Recommendation
Benefit / Impact:

This would fix one of the issues reported in 19c for slow inserts to global temporary table (GTT).

Risk:

The elapsed time for Insert into global temporary table (GTT) keeps on increasing proportionately with growing no.of records to the GTT.

Action / Repair:

It is highly recommended to download & apply the Patch 31031240.

Check ORACLE_PATCH 29423227 for RDBMS home

Recommendation
Benefit / Impact:

This would fix one of the issues reported in 19c for 'library cache lock' waits where the hang might happen when dropping a partition with global indexes created in the table.

Risk:

The SQL command to drop a partition with global indexes created in the table might hang with 'library cache lock' wait.

Action / Repair:

It is recommended to download & apply the Patch 29423227 if 'alter table drop partition' command is executed with global indexes
Links
  1. Note: 2619066.1 - High Library Cache Lock Waits After Upgrading To 19C During Partition Index Maintenance
Posted by Luo Donghua at 4:32 PM
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)