Oracle orachk Assessment Report

failed_checks

System Health Score is 88 out of 100 (detail)

Benefit / Impact:

Having the service_names parameter set within the spfile will cause the service to start (and register with the listener) with database startup.   
This issue will be apparent with role-based services (primary role services being start on the standby database).  In a RAC environment Clusterware manages these services based on policies you define when creating the service. 


Action / Repair:

Reset the service_names parameter to its default value (which is null)
 
SQL> alter system reset service_names sid='*' 

Benefit / Impact:

Setting this kernel parameter to 1 causes the kernel to panic if a kernel oops or bug is encountered.


Risk:

Kernel panic or oops represents potential problems with the server. If it's set to 0, the kernel will try to keep running (with whatever awful consequences). If it's set to 1, the kernel will enter the panic state and halt.

Action / Repair:

As root user, execute the following command from the shell prompt

echo 1 > /proc/sys/kernel/panic_on_oops

To set this kernel parameter permanently, edit /etc/sysctl.conf and make the following entry

kernel.panic_on_oops = 1

Benefit / Impact:

When the size of SGA is increased with the proper minimum value set for shared pool and buffer cache, the number of resize operations should be reduced.

Risk:

An undersized SGA can lead to frequent resizing operations of the SGA which could potentially cause slow performance or database hang situations.

Action / Repair:

Perform the following 3 actions:

1.  Increase the SGA_TARGET or MEMORY_TARGET initialization parameters. If frequent resizing is occurring V$SGA_TARGET_ADVICE can be used to ensure that SGA_TARGET is optimally set.  Refer to “Tuning SGA_TARGET Using V$SGA_TARGET_ADVICE (Doc ID 1323708.1)” for more assistance.

2.  Set the minimum value for shared pool or buffer cache to the high watermark size of past resize  assuming that is the peak level .

Ex:    
SQL> SELECT MAX(final_size) FROM V$SGA_RESIZE_OPS WHERE component='shared pool';
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=<newvalue> scope=both;

3.  Increase the time interval between resizes. To do this, the following parameter can be set dynamically:
  
SQL> alter system set "_memory_broker_stat_interval"=999;
  
This will increase the time between resize to at least 999 seconds and thereby reducing the number of resize operations. The parameter "_memory_broker_stat_interval" is expressed in seconds with a default setting of 30 seconds. If you are running on RAC you will need to do this on all nodes.

1. 1 Note: 742599.1 - High 'cursor: pin S wait on X' and/or 'library cache lock' Waits. Cause: Shared Pool/Buffer Cache Resize Activity
2. 2 Note: 1344228.1 - Severe Performance Problems Due to Sudden drop/resize/shrink of buffer cache
3. 3 Note: 1323708.1 - Tuning SGA_TARGET Using V$SGA_TARGET_ADVICE

Benefit / Impact:

Identifying the database sessions that are currently waiting for the resources (such as: enqueues, latches, IO calls, CPU, Memory) and identifying the blocker session for the waiters can avoid database performance issues or database hang situations.

Risk:

Slow database performance or a database hang situation may happen when a high number of waiter sessions exist and a high number of blockers are found.

Action / Repair:

Please execute the following SQL to identify the sessions waiting.

set pages 100;
set lines 150;
col sid format 999999;
col event format a40;
col state format a15;
col waiting head "Waiting(in seconds)" format 9,999.99;
col waited head "Waited" format 9,999.99;
select sid, state, event,
       seconds_in_wait  waiting,
       wait_time/100    waited, p1,p2,p3, BLOCKING_SESSION "Blocker"
from v$session
where event not in
(
  'SQL*Net message from client',
  'SQL*Net message to client',
  'rdbms ipc message'
)
and state = 'WAITING'
and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP');

Please refer the following interactive workflow to troubleshoot the database performance issues based on the wait events observed.

Troubleshooting Assistant: Troubleshooting Performance Issues (Doc ID 1543445.2)

This would be very helpful in narrowing down the performance problems without contacting Oracle support.

1. 1 Note: 1477599.1 - Best Practices: Proactive Data Collection for Performance Issues
2. 2 Note: 452358.1 - How to Collect Diagnostics for Database Hanging Issues

Benefit / Impact:

When the size of redo logs are increased or more redolog groups are added, the number of log switches would get reduced and thus improving database performance.

Risk:

Too many log switches per hour could potentially lead to slow database performance and could lead to 'log file sync' waits.

Action / Repair:

Increase the size of redologs to twice of the current value.

Refer to the following document for steps:
How To Maintain and/or Add Redo Logs (Doc ID 602066.1)

1. 1 Note: 602066.1 - How To Resize and/or Add Redo Logs
2. 2 Note: 73475.1 - Script: Perl sample script to parse the Alert Log for log switches

Benefit / Impact:

When the session_cached_cursors database parameter is set to a high value then the number of soft parses will be reduced.  This will reduce the contention in the library cache for sharing the cursors during repeated executions of the SQL statements.

Risk:

The amount of soft parses could increase if the value for the session_cached_cursors parameter is not increased. This may lead to slow performance due to excessive parse calls.

Action / Repair:

The default value for session_cached_cursors is 50.
The parameter value can be modified based on the USAGE from the report.
If the current value is set to less than or equal to 300 and the USAGE is greater than or equal to 100% then the value can be doubled.

Set the parameter value in the parameter file (init.ora or spfile) and restart the database.

SQL> alter system set session_cached_cursors=<value> scope=spfile sid='*';

1. 1 Note: 208857.1 - SCRIPT - to Set the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters Based on Usage
2. 2 Note: 62143.1 - Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
3. 3 Note: 30804.1 - Init.ora Parameter "SESSION_CACHED_CURSORS" Reference Note

Benefit / Impact:

When the PGA size is increased as per V$PGA_TARGET_ADVICE, performance degradation due to PGA over allocation to disk could be avoided.

Risk:

Slow database performance or even hang situations could arise when many PGA over allocations to disk occur.

Action / Repair:

Perform the following 2 actions:

1.	 Increase the size of PGA_AGGREGATE_TARGET when the optimal_percent is not 100.

V$PGA_TARGET_ADVICE view predicts how the statistics cache hit percentage and over allocation count in V$PGASTAT will be impacted if you change the value of the initialization parameter PGA_AGGREGATE_TARGET.

The following select statement can be used to find this information 

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
FROM   v$pga_target_advice;

You can set the new value for PGA_AGGREGATE_TARGET where the "CACHE_HIT_PERC" equals to 100 and "ESTD_OVERALLOC_COUNT" equals to 0.

Steps to set PGA_AGGREGATE_TARGET
==========================

SQL> alter system set pga_aggregate_target = <value>M;

2.	Monitor the size of all active work areas and to determine if these active work areas spill to a temporary segment and also identify the SQLs that incur huge PGA usage.  Use  V$SQL_WORKAREA_ACTIVE  to display the work areas that are active (or executing) in the instance.

Example:

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE, SQL_ID
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

SID OPERATION         ESIZE     MEM       MAX MEM    PASS   TSIZE    SQL_ID
--- -----------------     ---------  ---------   -------------   ------  -------    --------
8   GROUP BY (SORT)      315       280             904                  0                    gx4mv66pvj3xz  
8   HASH-JOIN                 2995      2377         2430                  1     20000    ... 
9   GROUP BY (SORT)  34300     22688     22688                  0                     ... 
11  HASH-JOIN              18044     54482     54482                  0                     ...  
12  HASH-JOIN              18044     11406     21406                  1   120000   d2g2xjjz4d2rp

This output shows that session 12 (column SID) is running a hash-join having its work area running in one-pass mode (PASS column). This work area is currently using 11406 KB of memory (MEM column) and has used, in the past, up to 21406 KB of PGA memory (MAX MEM column). It has also spilled to a temporary segment of size 120000 KB. Finally, the column ESIZE indicates the maximum amount of memory that the PGA memory manager expects this hash-join to use. This maximum is dynamically computed by the PGA memory manager according to workload

1. 1 Note: 223730.1 - Automatic PGA Memory Management

Benefit / Impact:

Changes to schema objects could lead to bad execution plan for the SQL statements involving the objects that changed. Investigating the changes may help in identifying the root cause behind the sudden change in the execution plan for the SQL statements or slow application performance.

Risk:

The generation of bad execution plan due to the changes to schema objects might lead to application performance issues.

Action / Repair:

When performance issues are noticed on SQL statements, check with the DBA or Application Developers about the changes to the schema objects and take corrective action to revert it back if possible.

The report only shows top 20 objects created/modified during the last 7 days.
Please run the following SQL to get the complete list of objects.

set linesize 200;
set pagesize 100;
set heading on;
COLUMN OWNER FORMAT A15
COLUMN OBJECT_NAME FORMAT A25
COLUMN OBJECT_TYPE FORMAT A10
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP,STATUS FROM DBA_OBJECTS WHERE (CREATED>=SYSDATE-7 or LAST_DDL_TIME>=SYSDATE-7) AND OWNER NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP') ORDER BY LAST_DDL_TIME DESC;

1. 1 Note: 1662591.1 - How To Track The Changes That May Affect Database Performance

Benefit / Impact:

One or more optimizer related parameters are set to a non-default value.



Risk:

The settings for these parameters should be recommended by the application vendor or by Oracle Support. If any optimizer related parameters are set, the application should be certified and tested to work well with the parameter settings. Ensure that the performance is as expected.

Non default settings of optimizer related parameters may result in performance issues. The settings of such optimizer parameters can have an impact on the entire application performance.

Please Note:  If the database instance is running Siebel/Peoplesoft Applications then you may not need to change any settings, please check your product documentation.



Action / Repair:

To modify the parameter value, use the alter session or alter system commands. Refer to the Initialization Parameter chapter of the Oracle Database Reference Documentation for more details on setting these parameters.

Benefit / Impact:

One or more optimizer related parameters are set to a non-default value.



Risk:

The settings for these parameters should be recommended by the application vendor or by Oracle Support. If any optimizer related parameters are set, the application should be certified and tested to work well with the parameter settings. Ensure that the performance is as expected.

Non default settings of optimizer related parameters may result in performance issues. The settings of such optimizer parameters can have an impact on the entire application performance.

Please Note:  If the database instance is running Siebel/Peoplesoft Applications then you may not need to change any settings, please check your product documentation.



Action / Repair:

To modify the parameter value, use the alter session or alter system commands. Refer to the Initialization Parameter chapter of the Oracle Database Reference Documentation for more details on setting these parameters.

Benefit / Impact:

New optimizer features and fixes are enabled when the OPTIMIZER_FEATURES_ENABLE parameter is set to the current database version.  Setting the OPTIMIZER_FEATURES_ENABLE parameter to a value other than the current database version should only be used as a short term measure at the suggestion of oracle support.



Risk:

By reducing the OPTIMIZER_FEATURES_ENABLE level, new optimizer features and fixes are disabled.  This can potentially have a serious negative impact on performance because it eliminates the possibility of choosing better plans which are only available with the new features and fixes.



Action / Repair:

If Oracle Support has not recommended that you modify this parameter, then you should set the OPTIMIZER_FEATURES_ENABLE parameter to the current database version to ensure that new optimizer features are enabled.  This parameter can be set with the following command:

SQL> alter system set optimizer_features_enable='X.X.X.X' scope=spfile;

Refer to the following MOS document for more details.

1. 1 Note: 1362332.1 - Use Caution if Changing the OPTIMIZER_FEATURES_ENABLE Parameter After an Upgrade

Benefit / Impact:

Setting NLS_SORT to BINARY will result in the ability for your SQL to use standard indexes and queries will be more efficient.



Risk:

Setting NLS_SORT to a value other than BINARY will make regular indexes unusable.  This will negatively affect the performance of your SQL unless you have specific indexes to support the new sorting.



Action / Repair:

Set NLS_SORT to BINARY.   Change the value in the initialization parameter file and then restart the instance. If you do need to have a different sorting in place, then make sure you have the necessary indexes created using a different sorting (NLSSORT function).

Refer to the following MOS document for more details.

1. 1 Note: 227335.1 - Linguistic Sorting - Frequently Asked Questions

1. 1 Note: 847951.1 - How to Change Extent Management of a Tablespace from Uniform Size to Autoallocate

(2) Online patches consume additional memory and if kept permanently the memory consumption increases as the number of processes in the system increases.
(3) It is strongly recommended to rollback all Online patches and replace them with regular (offline) patches on next instance shutdown or the earliest maintenance window.

1. 1 Note: 761111.1 - RDBMS Online Patching Aka Hot Patching
2. 2 Online patching

Benefit / Impact:

Core dumps are sometimes needed for debugging purposes



Risk:

Improper setting of COREDUMPSIZE could prevent or prolong troubleshooting if the maximum core file size is too small.


Action / Repair:

The system may not have an adequate COREDUMPSISZE. It is recommended to set the OS core file size to 2GB or greater to aid in troubleshooting issues that generate core dumps (i.e. ORA-7445 errors).

1. 1 Note: 567524.1 - Setting Shell Limits For The User "oracle" In Case Of 10gR2 And 11gR1 RDBMS Software

Benefit / Impact:

One or more optimizer related parameters are set to a non-default value.



Risk:

The settings for these parameters should be recommended by the application vendor or by Oracle Support. If any optimizer related parameters are set, the application should be certified and tested to work well with the parameter settings. Ensure that the performance is as expected.

Non default settings of optimizer related parameters may result in performance issues. The settings of such optimizer parameters can have an impact on the entire application performance.

Please Note:  If the database instance is running Siebel/Peoplesoft Applications then you may not need to change any settings, please check your product documentation.



Action / Repair:

To modify the parameter value, use the alter session or alter system commands. Refer to the Initialization Parameter chapter of the Oracle Database Reference Documentation for more details on setting these parameters.

The Completely Fair Queuing (CFQ) scheduler is the default algorithm in Red Hat Enterprise Linux 4 which is suitable for a wide variety of applications and provides a good compromise between throughput and latency. In comparison to the CFQ algorithm, the Deadline scheduler caps maximum latency per request and maintains a good disk throughput which is best for disk-intensive database
applications. Hence, the Deadline scheduler is recommended for database systems.

1. 1 Setting the Disk I/O Scheduler on Linux
2. 2 Note: 1352304.1 - Linux: Should 'CFQ Scheduler' or 'DEADLINE Scheduler' Be Used In Oracle RAC
3. 3 recommended I/O scheduler for a database workload in RHEL

Add the following line to the /etc/pam.d/login file, if it does not already exist:

session    required     pam_limits.so

1. 1 Setting Shell Limits for the oracle User

Benefit / Impact:

When you perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause, database backups made prior to the unrecoverable operation are invalidated and new backups are required. You can specify the SQL ALTER DATABASE or SQL ALTER TABLESPACE statement with the FORCE LOGGING clause to override the NOLOGGING setting; however, this statement will not repair a database that is already invalid.



Risk:

Changes under NOLOGGING will not be available after executing database recovery from a backup made prior to the unrecoverable change.



Action / Repair:

To verify that the data files are recoverable, execute the following Sqlplus command as the userid that owns the oracle home for the database:
select file#, unrecoverable_time, unrecoverable_change# from v$datafile where unrecoverable_time is not null;
If there are any unrecoverable actions, the output will be similar to:
     FILE# UNRECOVER UNRECOVERABLE_CHANGE#
---------- --------- ---------------------
        11 14-JAN-13               8530544
If nologging changes have occurred and the data must be recoverable then a backup of those datafiles that have nologging operations within should be done immediately. Please consult the following sections of the Backup and Recovery User guide for specific steps to resolve files that have unrecoverable changes

The standard best practice is to enable FORCE LOGGING at the database level (ALTER DATABASE FORCE LOGGING;) to ensure that all transactions are recoverable. However, placing the a database in force logging mode for ETL operations can lead to unnecessary database overhead. MAA best practices call for isolating data that does not need to be recoverable. Such data would include:

Data resulting from temporary loads
Data resulting from transient transformations
Any non critical data

To reduce unnecessary redo generation, do the following:

Specifiy FORCE LOGGING for all tablespaces that you explicitly wish to protect (ALTERTABLESPACE FORCE LOGGING;)
Specify NO FORCE LOGGING for those tablespaces that do not need protection (ALTERTABLESPACE NO FORCE LOGGING;).
Disable force logging at the database level (ALTER DATABASE NO FORCE LOGGING;) otherwise the database level settings will override the tablespace settings.

Once the above is complete, redo logging will function as follows:

Explicit no logging operations on objects in the no logging tablespace will not generate the normal redo (a small amount of redo is always generated for no logging operations to signal that a no logging operation was performed).

All other operations on objects in the no logging tablespace will generate the normal redo.
Operations performed on objects in the force logging tablespaces always generate normal redo.

Note:-Please seek oracle support assistance to mitigate this problem. Upon their guidance, the following commands could help validate, identify corrupted blocks.

              oracle> dbv file=<data_file_returned_by_above_command> userid=sys/******
              RMAN> validate check logical database;
              SQL> select COUNT(*) from v$database_block_corruption;

1. 1 Backing Up Database Files with RMAN
2. 2 Recovering NOLOGGING Tables and Indexes

For every database, there will be a zip file of format <pxhcdr_DBNAME_HOSTNAME_DBVERSION_DATE_TIMESTAMP.zip> in ORAchk output directory. 

Warning: log write elapsed time 564ms, size 2KB

Even though this threshold is very high and latencies below this range could impact the application performance, it is still worth to capture and report it to customers for necessary action.The performance impact of LGWR latencies include commit delays,Broadcast-on-Commit delays etc.

1. 1 Note: 601316.1 - LGWR Is Generating Trace file with "Warning: Log Write Time 540ms, Size 5444kb" In 10.2.0.4 Database

To achieve the most comprehensive data corruption prevention and detection , use Oracle Active Data Guard and configure DB_BLOCK_CHECKSUM,DB_LOST_WRITE_PROTECT and DB_BLOCK_CHECKING database initialization parameters on the primary database and all standby databases in a Data Guard environment. Workload specific testing is required to assess whether the performance overhead with especially DB_BLOCK_CHECKING is acceptable.

Using ASM, RMAN, Exadata Storage and Zero Data Loss Recovery Appliance (Recovery Appliance) provide additional data protection checks and repair for Oracle databases and backups.    

 Key HA Benefits
<ul>
<li>Application downtime due to data corruptions can be reduced from hours and days to seconds to no downtime.</li>
 <li>Prevention, quick detection and fast repair of data block corruptions. </li>
 <li>With Active Data Guard, physical data block corruptions can be repaired automatically using current blocks from primary or standby databases.</li>
<li>With ASM, Exadata Storage and Recovery Appliance, data corruptions can be detected in the background and in some cases repaired automatically.</li>
<li>With DB_BLOCK_CHECKING=MED or FULL, logical data block corruptions can be detected and prevented on the primary or standby database.</li>
</ul>

1. 1 Oracle Active Data Guard

Active Data Guard is the real-time data protection and availability solution that eliminates single point of failure by maintaining one or more synchronized physical replicas of the production database. If an unplanned outage of any kind impacts the production database, applications and users can quickly failover to a synchronized standby, minimizing downtime and preventing data loss. An Active Data Guard standby can be used to offload read-only applications, ad-hoc queries, and backups from the primary database or be dual-purposed as a test system at the same time it provides disaster protection. An Active Data Guard standby can also be used to minimize downtime for planned maintenance when upgrading to new Oracle Database patch sets and major database releases and for select migrations.  
 
For zero data loss protection and fastest recovery time, deploy a local Data Guard standby database with Data Guard Fast-Start Failover and integrated client failover. For protection against outages impacting the entire data center, or a broad geography, deploy a Data Guard standby database at a remote location. Oracle 12c Far Sync Standby can achieve zero data loss protection across Wide Area Network or across regions.

Key HA Benefits:
<ul>
<li>Active Data Guard provides optimal data protection by using physical replication and comprehensive Oracle validation to maintain an exact byte-for-byte copy of the primary database that can be open read-only to offload reporting, ad-hoc queries and backups.</li>
<li>With Oracle 11g release 2 and higher Active Data Guard and real time apply, data block corruptions can be repaired automatically and downtime can be reduced from hours and days of application impact to zero downtime with zero data loss.</li>
<li>With MAA best practices, Data Guard Fast-Start Failover and integrated client failover, downtime from database, cluster and site failures can be reduced from hours to days to seconds.</li>
<li>With remote standby database (Disaster Recovery Site), you have protection from complete site failures.</li>
<li>In all cases, the Active Data Guard instances can be active and used for other activities.</li>
<li>Data Guard can reduce risks and downtime for planned maintenance activities by using Database rolling upgrade with transient logical standby or DBMS rolling, standby-first patch apply and database migrations.</li>
<li>For other advanced replication requirements where read-write access to a replica database is required while it is being synchronized with the primary database see Oracle GoldenGate logical replication. Oracle GoldenGate can be used to support heterogeneous database platforms and database releases, an effective read-write full or subset logical replica and to reduce or eliminate downtime for application, database or system changes. Oracle GoldenGate flexible logical replication solution's main trade-off is the additional administration for application developer and database administrators.</li>
</ul>

1. 1 Oracle Data Guard: Disaster Recovery for Oracle Exadata Database Machine
2. 2 Note: 1265700.1 - Oracle Patch Assurance - Data Guard Standby-First Patch Apply
3. 3 Note: 1302539.1 - Best Practices for Corruption Detection, Prevention, and Automatic Repair - in a Data Guard Configuration
4. 4 HABP: Chapter 9: Configuring Oracle Data Guard 11gR2
5. 5 Goldengate resources

1. 1 Configure the Size of Redo Log Files and Groups Appropriately

1. 1 Use Locally Managed Tablespaces
2. 2 Note: 105120.1 - Advantages of Using Locally Managed vs Dictionary Managed Tablespaces

1. 1 Use Automatic Segment Space Management

1. 1 Use Temporary Tablespaces and Specify a Default Temporary Tablespace

1. 1 Set the Database ARCHIVELOG Mode and FORCE LOGGING Mode

Considerations for a direct writes in a data warehouse type of application: Even though direct operations aren't using the buffer cache, fast_start_mttr_target is very effective at controlling crash recovery time because it ensures adequate checkpointing for the few buffers that are resident (ex: undo segment headers). fast_start_mttr_target should be set to the desired RTO (Recovery Time Objective) while still maintaing performance SLAs.

Oracle Flashback Technology enables fast logical failure repair. Oracle recommends that you use automatic undo management with sufficient space to attain your desired undo retention guarantee, enable Oracle Flashback Database, and allocate sufficient space and I/O bandwidth in the fast recovery area.  Application monitoring is required for early detection.  Effective and fast repair comes from leveraging and rehearsing the most common application specific logical failures and using the different flashback features effectively (e.g flashback query, flashback version query, flashback transaction query, flashback transaction, flashback drop, flashback table, and flashback database, and 12.2 flashback pluggable database (PDB)).

Key HA Benefits:
<ul>
<li>With application monitoring and rehearsed repair actions with flashback technologies, application downtime can reduce from hours and days to the time to detect the logical inconsistency.</li>
<li>Fast repair for logical failures caused by malicious or accidental DML or DDL operations.</li>
<li>Effect fast point-in-time repair at the appropriate level of granularity: transaction, table, pluggable database, or database.</li></ul>
Questions that need to be addressed by your application and operations team:
<ol>
<li>Can your application or monitoring infrastructure detect logical inconsistencies?</li>
<li>Is your operations team prepared to use various flashback technologies to repair quickly and efficiently?</li>
<li>Is security practices enforced to prevent unauthorized privileges that can result logical inconsistencies?</li>
</ol>

1. 1 HABP: Chapter 5.1.4: Enable Flashback Database in 11gR2

Benefit / Impact:

With AUDIT_TRAIL set for database (AUDIT_TRAIL=db), and the AUD$ and FGA_LOG$ tables located in a dictionary segment space managed SYSTEM tablespace, "gc" wait events are sometimes observed during heavy periods of database logon activity. Testing has shown that under such conditions, placing the AUD$ and FGA_LOG$ tables in the SYSAUX tablespace, which uses automatic segment space management, reduces the space related wait events.

The impact of verifying that the AUD$ and FGA_LOG$ tables are in the SYSAUX table space is low. Moving them if they are not located in the SYSAUX does not require an outage, but should be done during a scheduled maintenance period or slow audit record generation window.



Risk:

If AUD$ and FGA_LOG$ tables are not verifed to use automatic segment space management, there is a risk of a performance slowdown during periods of high database login activity.



Action / Repair:

To verify the segment space management policy currently in use by the AUD$ and FGA_LOG$ tables, use the following Sqlplus command:

select t.table_name,ts.segment_space_management from dba_tables t, dba_tablespaces ts where ts.tablespace_name = t.tablespace_name and t.table_name in ('AUD$','FGA_LOG$');

The output should be:
TABLE_NAME                     SEGMEN
------------------------------ ------
FGA_LOG$                       AUTO
AUD$                           AUTO 
If one or both of the AUD$ or FGA_LOG$ tables return "MANUAL", use the DBMS_AUDIT_MGMT package to move them to the SYSAUX tablespace:

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$ 
audit_trail_location_value => 'SYSAUX');  
END;  
/

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$ 
audit_trail_location_value => 'SYSAUX');
END;
/   

The output should be similar to:
PL/SQL procedure successfully completed. 

If the output is not as above, investigate and correct the condition.
NOTE: This "DBMS_AUDIT_MGMT.set_audit_trail" command should be executed as part of the dbca template post processing scripts, but for existing databases, the command can be executed, but since it moves the AUD$ & FGA_LOG$ tables using "alter table ... move" command, it should be executed at a "quiet" time

1. 1 Use Automatic Segment Space Management

Oracle database can be configured with best practices that are applicable to all Oracle databases, including single-instance, Oracle RAC databases, Oracle RAC One Node databases, and the primary and standby databases in Oracle Data Guard or Oracle GoldenGate configurations.

Key HA Benefits:

(1) Improved recoverability
(2) Improved stability

1. 1 Database High Availability Best Practices 12c R1: Configuring Oracle Database with Oracle Clusterware
2. 2 Database High Availability Best Practices 11g R2: Configuring Oracle Database with Oracle Clusterware
3. 3 HABP: Chapter 6: Configuring Oracle Database with Oracle Clusterware

Benefit / Impact:

For performance reasons redo logs should be sized so that they do not switch more than once every 10 minutes or 7 switches in an hour during peak throughput.

Risk:

Switching logs too often causes poor recovery performance on the standby as recovery only checkpoints at a log boundary which pauses recovery while the buffer cache is flushed.

Action / Repair:

To verify redo log size, as the owner userid of a given database, and with the environment properly set to access that database, execute the following command set:

function logswitches {
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' <<EOF
set feedback off newpage none termout on
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
select * from (
select thread#,sequence#,first_time "LOG START TIME",(blocks*block_size/1024/1024)/((next_time-first_time)*86400) "REDO RATE(MB/s)", \
(((blocks*block_size)/a.average)*100) pct_full
from v\$archived_log, (select avg(bytes) average from v\$log) a
where ((next_time-first_time)*86400<300)
and first_time > (sysdate-90)
and (((blocks*block_size)/a.average)*100)>80
and dest_id=1
order by 4 desc
)
where rownum<11;
exit
EOF
}

export SWITCHES=$(logswitches)

if [ $(echo "$SWITCHES"| wc -l) -le 1 ]
 then
  echo -e "SUCCESS: Redo logs are appropriately sized"
 else
  echo
  echo -e "WARNING: Redo logs are potentially mis-sized.  Below is a list of archived logs from"
  echo -e "the previous 90 days which were active for less than 5 minutes and the redo rate seen"
  echo -e "for the duration of that log.  These indicate the peak redo rate. Resizing of the log"
  echo -e "files to accomodate this rate may be required.\n"
  echo "$SWITCHES"
fi

The expected output is:

SUCCESS: Redo logs are appropriately sized

Example of a "WARNING" result:

WARNING: Redo logs are potentially mis-sized.  Below is a list of archived logs from
the previous 90 days which were active for less than 5 minutes and the redo rate seen
for the duration of that log.  These indicate the peak redo rate. Resizing of the log
files to accomodate this rate may be required.

   THREAD#  SEQUENCE# LOG START TIME      REDO RATE(MB/s)   PCT_FULL
---------- ---------- ------------------- --------------- ----------
         2      12374 2017/02/24 15:03:04      412.881913 95.7611859
         2      12382 2017/02/24 15:15:10       408.75571 89.8144871
         2      12296 2017/02/24 12:06:26       396.99937 92.0774907
         2      12380 2017/02/24 15:09:31      371.651196 90.7351553
         2      12385 2017/02/24 15:17:28      370.488538 90.4513031
         2      12378 2017/02/24 15:06:18      362.405012 95.1136202
         2      12389 2017/02/24 15:20:51      356.672996 95.7862049
         2      12377 2017/02/24 15:05:32      354.660687 99.5751441
         2      12350 2017/02/24 13:27:40      353.577854  97.113058
         2      12354 2017/02/24 13:30:45      347.344079 97.5209206

If logs are frequently switching, the redo logs should be increased in size according to the following chart:

Peak redo rate according
to EM or AWR reports 	     Recommended redo log group size
<1 MB/sec 	                     1 GB
<=3 MB/sec 	                     3 GB
<= 5 MB/sec 	                     4 GB
<= 25 MB/sec 	             16 GB
<= 50 MB/sec 	             32 GB
> 50 MB/sec 	                     64 GB

Read the Exceptions carefully before taking action.

1. 1 Note: 1030426.6 - How to Clean Up Duplicate Objects Owned by SYS and SYSTEM Schema

1. 1 Note: 472937.1 - Information On Installed Database Components and Schemas
2. 2 Note: 753041.1 - How To Diagnose Components With NON VALID Status In DBA_REGISTRY After an Upgrade

Benefit / Impact:

Experience and testing has shown that certain database initialization parameters should be set at specific values. These are the best practice  values set at deployment time. By setting these database initialization  parameters as recommended, known problems may be avoided and performance  maximized. 
The parameters are common to all database instances. The impact of setting  these parameters is minimal.  The performance related settings provide guidance to maintain highest stability without sacrificing performance. Changing the default performance  settings can be done after careful performance evaluation and clear understanding of the performance impact. 
  


Risk:

If the database initialization parameters are not set as recommended, a variety of issues may be encountered, depending upon which initialization  parameter is not set as recommended, and the actual set value. 
  


Action / Repair:

"RECYCLEBIN = ON" provides higher availability by enabling the Flashback Drop  feature. "ON" is the default value and should not be changed. 

Oracle Flashback Technology enables fast logical failure repair. Oracle recommends that you use automatic undo management with sufficient space to attain your desired undo retention guarantee, enable Oracle Flashback Database, and allocate sufficient space and I/O bandwidth in the fast recovery area.  Application monitoring is required for early detection.  Effective and fast repair comes from leveraging and rehearsing the most common application specific logical failures and using the different flashback features effectively (e.g flashback query, flashback version query, flashback transaction query, flashback transaction, flashback drop, flashback table, and flashback database, and 12.2 flashback pluggable database (PDB)).

Key HA Benefits:
<ul>
<li>With application monitoring and rehearsed repair actions with flashback technologies, application downtime can reduce from hours and days to the time to detect the logical inconsistency.</li>
<li>Fast repair for logical failures caused by malicious or accidental DML or DDL operations.</li>
<li>Effect fast point-in-time repair at the appropriate level of granularity: transaction, table, pluggable database, or database.</li></ul>
Questions that need to be addressed by your application and operations team:
<ol>
<li>Can your application or monitoring infrastructure detect logical inconsistencies?</li>
<li>Is your operations team prepared to use various flashback technologies to repair quickly and efficiently?</li>
<li>Is security practices enforced to prevent unauthorized privileges that can result logical inconsistencies?</li>
</ol>

Benefit / Impact:

Initially db_block_checking is set to off due to potential performance impact. Performance testing is particularly important given that overhead is incurred on every block change. Block checking typically causes 1% to 10% overhead, but for update and insert intensive applications (such as Redo Apply at a standby database) the overhead can be much higher. OLTP compressed tables also require additional checks that can result in higher overhead depending on the frequency of updates to those tables. Workload specific testing is required to assess whether the performance overhead is acceptable.



Risk:

If the database initialization parameters are not set as recommended, a variety of issues may be encountered, depending upon which initialization parameter is not set as recommended, and the actual set value.



Action / Repair:

Based on performance testing results set the primary or standby database to either medium or full depending on the impact. If performance concerns prevent setting DB_BLOCK_CHECKING to either FULL or MEDIUM at a primary database, then it becomes even more important to enable this at the standby database. This protects the standby database from logical corruption that would be undetected at the primary database.
For higher data corruption detection and prevention, enable this setting but performance impacts vary per workload.Evaluate performance impact.

1. 1 Note: 1302539.1 - Best Practices for Corruption Detection, Prevention, and Automatic Repair - in a Data Guard Configuration
2. 2 Protect Against Data Corruption

1. 1 Note: 68304.1 - WHAT IS UMASK
2. 2 Configuring the oracle User's Environment

Benefit / Impact:

Experience and testing has shown that certain database initialization parameters should be set at specific values. These are the best practice values set at deployment time. By setting these database initialization parameters as recommended, known problems may be avoided and performance maximized.
The parameters are common to all database instances. The impact of setting these parameters is minimal.
The performance related settings provide guidance to maintain highest stability without sacrificing performance. Changing the default performance settings can be done after careful performance evaluation and clear understanding of the performance impact.



Risk:

If the database initialization parameters are not set as recommended, a variety of issues may be encountered, depending upon which initialization parameter is not set as recommended, and the actual set value.



Action / Repair:

This is important for data block lost write detection and repair. Enable for
primary and standby databases.

See referenced MOS notes for more info.

1. 1 Note: 1265884.1 - Resolving ORA-752 or ORA-600 [3020] During Standby Recovery
2. 2 Note: 1302539.1 - Best Practices for Corruption Detection, Prevention, and Automatic Repair - in a Data Guard Configuration
3. 3 Protect Against Data Corruption

Benefit / Impact:

Documented value, cluster stability

The soft nofile shell limit for the Oracle DB software install owner as defined in /etc/security/limits.conf should be >= 1024.



Risk:

Resource starvation leading to node instability



Action / Repair:

Change DB software install owner soft nofile shell limit

Benefit / Impact:

Documented value, cluster stability

The hard nproc shell limit for the Oracle DB software install owner as defined in /etc/security/limits.conf should be >= 16384.



Risk:

Resource starvation (processes) leading to node instability



Action / Repair:

Change DB software install owner hard nproc shell limit

Benefit / Impact:

Experience and testing has shown that certain database initialization parameters should be set at specific values. These are the best practice values set at deployment time. By setting these database initialization parameters as recommended, known problems may be avoided and performance maximized.
The parameters are common to all database instances. The impact of setting these parameters is minimal.
The performance related settings provide guidance to maintain highest stability without sacrificing performance. Changing the default performance settings can be done after careful performance evaluation and clear understanding of the performance impact.



Risk:

If the database initialization parameters are not set as recommended, a variety of issues may be encountered, depending upon which initialization parameter is not set as recommended, and the actual set value.



Action / Repair:

DB_BLOCK_CHECKSUM = FULL aids in block corruption detection. Enable for primary and standby databases

1. 1 Note: 1302539.1 - Best Practices for Corruption Detection, Prevention, and Automatic Repair - in a Data Guard Configuration
2. 2 Protect Against Data Corruption

Benefit / Impact:

OSWatcher is used to collect and archive operating system and network metrics to aid support in diagnosing performance issues. Checking OSWatcher status helps to ensure critical data is available for analysis when system occurring issues. 
 
There is no impact from verifying the OSWatcher status. 

Risk:

When OSWatcher is not running, data is not gathered on a regular basis. System engineers may not be able to monitor system health, to analyze and resolve issues, which may result in much longer maintenance windows. 

Action / Repair: 
To check the current status of OSWatcher, run the command: 

# ps -elf | grep OSWatcher  | grep -v grep 
 
To start, stop or reset the OSWatcher utility, please reference the latest OSWatcher User's Guide (Doc ID 1531223.1) : 

1. 1 Note: 1531223.1 - OS Watcher User's Guide
2. 2 Note: 301137.1 - OS Watcher User Guide
3. 3 Note: 2044825.1 - How-To Update the OSC-Exawatcher Package on All Database Domains

Benefit / Impact:

Properly configuring operating system hugepages on Linux and using the database initialization parameter "use_large_pages" to "only" results in more efficient use of memory and reduced paging.
The impact of validating that the total current hugepages are greater than or equal to estimated requirements for all currently active SGAs is minimal. The impact of corrective actions will vary depending on the specific configuration, and may require a reboot of the database server.



Risk:

The risk of not correctly configuring operating system hugepages in advance of setting the database initialization parameter "use_large_pages" to "only" is that if not enough huge pages are configured, some databases will not start after you have set the parameter.



Action / Repair:

Pre-requisite: All database instances that are supposed to run concurrently on a database server must be up and running for this check to be accurate.

NOTE: Please refer to below referenced My Oracle Support notes for additional details on configuring hugepages.

NOTE: If you have not reviewed the below referenced My Oracle Support notes and followed their guidance BEFORE using the database parameter "use_large_pages=only", this check will pass the environment but you will still not be able to start instances once the configured pool of operating system hugepages have been consumed by instance startups. If that should happen, you will need to change the "use_large_pages" initialization parameter to one of the other values, restart the instance, and follow the instructions in the below referenced My Oracle Support notes. The brute force alternative is to increase the huge page count until the newest instance will start, and then adjust the huge page count after you can see the estimated requirements for all currently active SGAs.

1. 1 Note: 401749.1 - Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration
2. 2 Note: 361323.1 - HugePages on Linux: What It Is... and What It Is Not...

1. 1 Note: 739530.1 - How to delete or refresh/recycle a SQL*Net Listener log file while the Listener is active.

1. 1 Configuring Kernel Parameters

Benefit / Impact:

Optimal system memory management.



Risk:

In an Oracle RDBMS application, setting kernel.shmmax too high is not needed and could enable configurations that may leave inadequate system memory for other necessary functions.



Action / Repair:

Oracle Support officially recommends a "minimum" for SHMMAX of 1/2 of physical RAM. However, many Oracle customers choose a higher fraction, at their discretion.  Setting the kernel.shmmax as recommended only causes a few more shared memory segments to be used for whatever total SGA that you subsequently configure in Oracle.

1. 1 Configuring Kernel Parameters
2. 2 Note: 567506.1 - Maximum SHMMAX values for Linux x86 and x86-64

1. 1 Disk Space Requirements

Benefit / Impact:

The soft nproc shell limit for the Oracle DB software install owner as defined in /etc/security/limits.conf should be >= 2047.

Risk:

The soft nproc shell limit for the Oracle DB software install owner as defined in /etc/security/limits.conf should be >= 2047.  As long as the soft nproc limit is 2047 or above then the configuration should be OK.



Action / Repair:

Change DB software install owner soft nproc shell limit

$ ulimit -Su
2047

1. 1 Note: 788480.1 - Resource Limits Using /etc/security/limits.conf and /etc/profile
2. 2 Note: 169706.1 - Oracle Database OS Installation and Configuration Requirements Quick Reference

1. 1 Note: 265831.1 - Automatic Checkpoint Tuning in Oracle 10g
2. 2 Note: 147468.1 - Checkpoint Tuning and Troubleshooting Guide

1. 1 Note: 1521910.1 - ORA-7445 Troubleshooting Tool

1. 1 Note: 1521912.1 - ORA-600 Troubleshooting Tool

For this check to pass, you should not have core files older than 30 days in core_dump_destination.

1. 1 Note: 1587357.1 - Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Solaris and MS Windows Operating Systems Installation and Configuration
2. 2 Configuring Kernel Parameters
3. 3 Note: 169706.1 - Oracle Database OS Installation and Configuration Requirements Quick Reference

1. 1 Note: 1587357.1 - Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Solaris and MS Windows Operating Systems Installation and Configuration
2. 2 Configuring Kernel Parameters
3. 3 Note: 169706.1 - Oracle Database OS Installation and Configuration Requirements Quick Reference

1. 1 Note: 1587357.1 - Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Solaris and MS Windows Operating Systems Installation and Configuration
2. 2 Configuring Kernel Parameters
3. 3 Note: 169706.1 - Oracle Database OS Installation and Configuration Requirements Quick Reference

1. 1 Note: 1587357.1 - Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Solaris and MS Windows Operating Systems Installation and Configuration
2. 2 Configuring Kernel Parameters
3. 3 Note: 169706.1 - Oracle Database OS Installation and Configuration Requirements Quick Reference

Benefit / Impact:

SEMOPM should be set >= 100 

Risk:

Kernel Parameter SEMOPM Is Lower Than The Recommended Value

Action / Repair:

SEMOPM should be set >= 100 

1. 1 Note: 1587357.1 - Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Solaris and MS Windows Operating Systems Installation and Configuration
2. 2 Configuring Kernel Parameters
3. 3 Note: 169706.1 - Oracle Database OS Installation and Configuration Requirements Quick Reference

1. 1 Note: 1587357.1 - Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Solaris and MS Windows Operating Systems Installation and Configuration
2. 2 Configuring Kernel Parameters
3. 3 Note: 301830.1 - Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28
4. 4 Note: 169706.1 - Oracle Database OS Installation and Configuration Requirements Quick Reference

Warning: OS async I/O limit 128 is lower than recovery batch 1024

1. 1 Note: 471846.1 - Warning: OS async I/O limit 128 is lower than recovery batch 1024 in Alert log

1. 1 Note: 397770.1 - How to Check if the Oracle JVM is Installed Correctly in the Database

net.core.rmem_default =262144
net.core.rmem_max = 2097152 (10g)
net.core.rmem_max = 4194304 (11g and above)  

net.core.wmem_default =262144
net.core.wmem_max =1048576

See referenced documents for more details

1. 1 Configuring Kernel Parameters

net.core.rmem_default =262144
net.core.rmem_max = 2097152 (10g)
net.core.rmem_max = 4194304 (11g and above)  

net.core.wmem_default =262144
net.core.wmem_max =1048576

See referenced documents for more details

1. 1 Configuring Kernel Parameters

net.core.rmem_default =262144
net.core.rmem_max = 2097152 (10g)
net.core.rmem_max = 4194304 (11g and above)  

net.core.wmem_default =262144
net.core.wmem_max =1048576

See referenced documents for more details

1. 1 Configuring Kernel Parameters

net.core.rmem_default =262144
net.core.rmem_max = 2097152 (10g)
net.core.rmem_max = 4194304 (11g and above)  

net.core.wmem_default =262144
net.core.wmem_max =1048576

See referenced documents for more details

1. 1 Configuring Kernel Parameters

Benefit / Impact:

High AWR space usage in SYSAUX could lead to various problems related to AWR.
Monitoring the space usage may help in identifying the problem elements and taking corrective actions.

Risk:

Too much AWR space usage in SYSAUX could lead to various problems in AWR like AWR snapshots not working, etc.

Action / Repair:

When AWR space consumption is greater than or equal to 50% of total SYSAUX, then run the following SQLs to identify the top AWR objects.

REM Top 10 AWR objects consuming high SYSAUX space
set lines 200
set pages 100
col segment_name format a30;
col mb format 99,990.0;
col segment_type format a20;

select * from (select 'AWR ' as component, segment_name,
        sum(bytes)/1024/1024 as MB,
        segment_type
		from    (select segment_name, bytes,segment_type,
                 (case when segment_type like '%TABLE%' then bytes else 0 end) as tbytes,
                 (case when segment_type like '%INDEX%' then bytes else 0 end) as ibytes
          from   dba_segments
          where  (segment_name like 'WRH%' or segment_name like 'WRM%')
            and  tablespace_name = 'SYSAUX'
            and  owner = 'SYS') where bytes > 500000
group by segment_name,segment_type
order by sum(bytes) desc) where rownum<=10;

Note: For more objects information, you can run the following script in SQLPlus:

@$ORACLE_HOME/rdbms/admin/awrinfo.sql

Refer to the section "Space usage within AWR Components (> 500K)" in the output.

Refer the following documents for troubleshooting:

1. Troubleshooting Issues with SYSAUX Usage by the Automatic Workload Repository (AWR) (Doc ID 1399365.1)
2. How to Address Issues Where AWR Data Uses Significant Space in the SYSAUX Tablespace (Doc ID 287679.1)
3. Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER (Doc ID 329984.1)
4. Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)

Known Bugs:

1. Doc ID 14084247.8 => Bug 14084247 - Failed AWR purge can lead to continued SYSAUX space use - affects 11.2.0.2, 11.2.0.3 & 11.2.0.4
2. Doc ID 9797851.8 => Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged - affects 11.2.0.2 & 11.2.0.3
3. Doc ID 13901201.8 => Bug 13901201 - WRH$_SQL_PLAN not purged leading to excessive SYSAUX tablespace growth - affects 11.1.0.7, 11.2.0.2 & 11.2.0.3

It is recommended to apply the patches for the above bugs if found relevant and not applied already.

If you found none of the AWR objects consumption is higher & only negligible then you can safely ignore this.

Such warnings might come if your total SYSAUX space is lesser as the checks are based on 50% of total SYSAUX. In that case, you can increase the size of the SYSAUX tablespace to avoid the warnings.

1. 1 Note: 1292724.1 - Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large
2. 2 Note: 1399365.1 - Troubleshooting Issues with SYSAUX Usage by the Automatic Workload Repository (AWR)
3. 3 Note: 329984.1 - Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER
4. 4 Note: 287679.1 - How to Address Issues Where AWR Data Uses Significant Space in the SYSAUX Tablespace (Doc ID 287679.1)
5. 5 Note: 14084247.8 - Bug 14084247 - Failed AWR purge can lead to continued SYSAUX space use
6. 6 Note: 13901201.8 - Bug 13901201 - WRH$_SQL_PLAN not purged leading to excessive SYSAUX tablespace growth
7. 7 Note: 9797851.8 - Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged (Doc ID 9797851.8)

Benefit / Impact:

Documented value is the /etc/security/limits.conf file as documented in 11gR2 Grid Infrastructure  Installation Guide, section 2.15.3 Setting Resource Limits for the Oracle Software Installation Users.  

If the /etc/security/limits.conf file is not configured as described in the documentation then log in to the system as the database software owner (e.g., oracle) and check the soft stack configuration as described below.

Risk:

The soft stack shell limit for the Oracle DB software install owner as defined in /etc/security/limits.conf should be >= 10240.  As long as the soft stack limit is 10240 or above then the configuration should be OK.



Action / Repair:

Change DB software install owner soft stack shell limit

$ ulimit -Ss
10240

	"Warning: The SCN headroom for this database is only x days y hours!"
and
    "The Current SCN value is <scn>, SCN Compat value is <x>", every 24 hrs

Benefit / Impact:

Experience and testing has shown that certain database initialization parameters should be set at specific values. These are the best practice values set at deployment time. By setting these database initialization parameters as recommended, known problems may be avoided and performance maximized.
The parameters are common to all database instances. The impact of setting these parameters is minimal.
The performance related settings provide guidance to maintain highest stability without sacrificing performance. Changing the default performance settings can be done after careful performance evaluation and clear understanding of the performance impact.

Risk:

If the database initialization parameters are not set as recommended, a variety of issues may be encountered, depending upon which initialization parameter is not set as recommended, and the actual set value.



Action / Repair:

OPEN_CURSORS >= 300.  Initial deployment database uses 1000

Benefit / Impact:

Too many recyclebin objects consume high space and lead to slow performance of dictionary SQLs and other performance problems.

Keeping the tablespaces free from high no.of recyclebin objects by monitoring periodically would help avoid such situation.

Risk:

High space consumption in tablespaces due to recyclebin would lead to slow performance of dictionary queries and other space management problems in the underlying tablespaces.

Action / Repair:

Identify the users and corresponding tablespaces with space usage more than 10% of its total size.

SQL> col "User" format a20
SQL> col "Tablespace Name" format a20
SQL> set lines 150
SQL> with rec_bytes as
(select (sum(space)*(select value from v$parameter where name='db_block_size'))/1024 bytes_in_kb,owner,ts_name from dba_recyclebin group by owner,ts_name order by 1 desc)
select owner "User", ts_name "Tablespace Name",bytes_in_kb "Space Consumption(KB)",(select sum(a.bytes)/1024 from dba_data_files a where a.tablespace_name=ts_name) "Size of Tablespace(KB)",
ceil((bytes_in_kb/(select sum(a.bytes)/1024 from dba_data_files a where a.tablespace_name=ts_name))*100) "Percent Usage(%)"
from rec_bytes where bytes_in_kb>0 order by 3 desc;

When one or more tablespaces have space consumption more than 10% of its total size, then please do the following.

If "Percent Usage(%)" is greater than 10% then purge the recyclebin of particular user.

1. Purge the recyclebin of particular user 

SQL> connect <user/pwd>
SQL> purge recyclebin;

2. Purge the complete recyclebin.

SQL> connect / as sysdba
SQL> purge dba_recyclebin;

3. You can turn off the recyclebin if you do not want.
recyclebin=OFF /* set in init.ora */

4. There is a known bug where the recyclebin does not automatically purge.

Bug 13931087 - RECYCLEBIN does not automatically purge under space pressure (Doc ID 13931087.8) - affected in 11.2.0.3/11.2.0.4

1. 1 Note: 1596147.1 - How To Delete/Purge Recyclebin or DBA_RECYCLBIN For Objects Older Than x Days/minutes
2. 2 Note: 13931087.8 - Bug 13931087 - RECYCLEBIN does not automatically purge under space pressure
3. 3 Note: 1595081.1 - Objects in RECYCLEBIN can't be purged
4. 4 Note: 1450488.1 - How To Estimate the Time Required to Purge Large Number of Objects from Recycle Bin
5. 5 Note: 864503.1 - DBA_RECYCLEBIN
6. 6 Note: 1506286.1 - BIN$ Rows In Sys.obj$ After Purge Of Recyclebin

Benefit / Impact:

Too many recyclebin objects consume high space and lead to slow performance of dictionary SQLs.
Keeping the recyclebin intact by monitoring periodically would help avoid such situation.

Risk:

Too many recyclebin objects in any user would lead to slow performance of dictionary queries. 

Action / Repair:

When one or more database users having more than 10 recyclebin objects, please do the following.

Identify the recyclebin objects by DB users and take action for users having > 10 recyclebin objects. 

SQL> col "User" format a20
SQL> select owner "User", cnt "Recyclebin objects" from (select count(*) cnt, owner from dba_recyclebin group by owner order by 1 desc);

1. Purge the recyclebin of particular user.

SQL> connect <user/pwd>
SQL> purge recyclebin;

2. Purge the complete recyclebin.

SQL> connect / as sysdba
SQL> purge dba_recyclebin;

3. You can turn off the recyclebin if you do not want.
recyclebin=OFF /* set in init.ora */

4. There is a known bug where the recyclebin does not automatically purge.

Bug 13931087 - RECYCLEBIN does not automatically purge under space pressure (Doc ID 13931087.8) - affected in 11.2.0.3/11.2.0.4

1. 1 Note: 1596147.1 - How To Delete/Purge Recyclebin or DBA_RECYCLBIN For Objects Older Than x Days/minutes
2. 2 Note: 13931087.8 - Bug 13931087 - RECYCLEBIN does not automatically purge under space pressure
3. 3 Note: 1595081.1 - Objects in RECYCLEBIN can't be purged
4. 4 Note: 1450488.1 - How To Estimate the Time Required to Purge Large Number of Objects from Recycle Bin
5. 5 Note: 864503.1 - DBA_RECYCLEBIN

Benefit / Impact:

When Oracle calculates the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter, the I/O used for table scans will be optimized.  Oracle calculates the value of DB_FILE_MULTIBLOCK_READ_COUNT by using: 
* operating system optimal I/O size
* the size of the buffer cache
* the maximum number of sessions



Risk:

The optimizer will favor large I/Os if you explicitly set the DB_FILE_MULTIBLOCK_READ_COUNT parameter to a large value.  This can affect the performance of your queries.



Action / Repair:

Unset the  DB_FILE_MULTIBLOCK_READ_COUNT parameter in the spfile or init.ora file.  This parameter can also be unset with the following command, but the database needs to be restarted in order for the change to take effect:

SQL> alter system reset db_file_multiblock_read_count scope=spfile sid='*';

Refer to the following MOS document for more details.

1. 1 Note: 1398860.1 - How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated?

Benefit / Impact:

Database memory allocation should never be greater than the physical memory installed on a database node. Over allocating memory can cause memory swapping which will negatively impact performance.

Risk:

Database performance can be significantly impacted by over allocating memory.

Action / Repair:

Calculation uses a multiple of the PGA_AGGREGATE_TARGET setting depending on the database version. For 11g databases, tool will multiply the PGA_AGGREGATE_TARGET setting by 3, for 12c databases, tool will multiply the PGA_AGGREGATE_TARGET setting by 2. These values are summed for all database running instances and compared to the amount of physical memory not allocated to HUGEPAGES.
The PGA database instance memory total allocated is greater than memory available on the node for PGA, please change memory allocations by reducing PGA_AGGREGATE_TARGET as appropriate in one or more database instances. After making changes to PGA_AGGREGATE_TARGET, re-run tool to determine if PGA memory allocation is less than memory available for PGA.

The most recent ADR incidents have been listed below.

1. 1 ADR Command Interpreter

Benefit / Impact:

Hidden (underscore) database initialization parameters are typically set to solve a specific problem, and the bigger picture impact is sometimes not considered. They tend to remain in use through upgrades despite the software bug they addressed being fixed. This best practice details the only hidden database initialization parameters that are currently known to be generally acceptable running the latest software, as well as the reason/configuration for this acceptance.


Risk:

Use of hidden (underscore) database initialization parameters not listed here without recommendation from Oracle development can cause instability, performance problems, corruptions, and crashes.

Action / Repair:

Unless instructed by Oracle development, remove hidden(underscore) database initialization parameters

Benefit / Impact:

This file maintains the list of different types of metadata such as the plugins, the platform types, blackouts, etc that need to be loaded during the Agent startup.

Risk:

Without this file, the 12C Agent will fail to start with the below error:
================================
Starting agent ................. failed.
Registry Manager failed at Startup
Consult the log files in: <AGENT_INST>/sysman/log
================================

Action / Repair:

Follow the recommendation in:

Note 1453987.1 - EM 12c : emctl start agent Fails to Start With 'Error : Registry Manager failed at Startup. Registry.xml File Lost/Corrupted' 

1. 1 Note: 1453987.1 - emctl start agent Fails to Start With 'Error : Registry Manager failed at Startup. Registry.xml File Lost/Corrupted'

1. 1 Disk Space Requirements

1. 1 Disk Space Requirements

Benefit / Impact:

Objects with stale statistics could lead to a bad execution plan for SQL statements.
Consider collecting the statistics for those objects.

Risk:

Too many application objects with stale statistics are not good for the database which could cause poor performance for the SQL statements running through the application.

Action / Repair:

Run the following command to get the list of all objects with stale statistics.

SQL> conn / as sysdba
SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO(); 

REM To list the objects having stale statistics (excluding SYS, SYSTEM, SYSMAN, DBSNMP objects)
REM Run the following from SYS or SYSTEM
set lines 120
column owner format a20
column table_name format a30
SELECT DT.OWNER,
       DT.TABLE_NAME, DT.LAST_ANALYZED,
       ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) STALE_PERCENT, NUM_ROWS
FROM   DBA_TABLES DT, DBA_TAB_MODIFICATIONS DTM
WHERE      DT.OWNER = DTM.TABLE_OWNER
       AND DT.TABLE_NAME = DTM.TABLE_NAME
       AND NUM_ROWS > 0 
       AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10 AND OWNER NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP')
ORDER BY 4 desc,5 desc;

Refer to the following documents to collect the optimizer statistics:

1.  How To: Gather Statistics for the Cost Based Optimizer (Doc ID 1226841.1)
2.  Master Note: Optimizer Statistics (Doc ID 1369591.1)
3.  FAQ: Statistics Gathering Frequently Asked Questions (Doc ID 1501712.1)

1. 1 Note: 1369591.1 - Master Note: Optimizer Statistics
2. 2 Note: 1501712.1 - FAQ: Statistics Gathering Frequently Asked Questions
3. 3 Note: 1226841.1 - How To: Gather Statistics for the Cost Based Optimizer

Benefit / Impact:

Non-default AWR settings could lead to high sysaux space usage.


Risk:

Non-default AWR settings could lead to high SYSAUX space usage.
When the snapshot interval is set to less than 1 hour(default) and the retention is greater than 8 days(default), then huge AWR snapshots could get created leading to heavy SYSAUX space usage.

Action / Repair:

When AWR consumes huge SYSAUX space due to non-default retention and snapshot interval settings, it is recommended to change the settings accordingly to reduce the space usage in SYSAUX.

From 10g onwards, use the following procedure to modify the statistics collection routine:

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT NULL);

Example:
The following example command changes the interval setting to two hours and the retention setting to two weeks for the local database:
execute dbms_workload_repository.modify_snapshot_settings(interval => 120,retention => 20160);

2 hours => 2*60 = 120
2 Weeks => 2*7*24*60 = 20160

If you query the DBA_HIST_WR_CONTROL table after this procedure is executed, you will see the changes to these settings.

The default settings for 'interval' and 'retention' are 60 minutes and 8 days on 11g (7 days on 10g). The values for both 'interval' and 'retention'  are expressed in minutes in the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS API.

Refer the following document for modifying AWR settings:

How to Modify Statistics collection by MMON for AWR repository (Doc ID 308450.1)

1. 1 Note: 308450.1 - How to Modify Statistics collection by MMON for AWR repository

Benefit / Impact:

High non-AWR space usage in SYSAUX could lead to various problems related to non-AWR components. 
The non-AWR components include: SM/OPTSTAT, SM/ADVISOR, SQL_MANAGEMENT_BASE, STATSPACK & SM/OTHER.
Monitoring the space usage may help in identifying the problem elements and taking corrective actions.

Risk:

Too much non-AWR space usage in SYSAUX can lead to various problems with non-AWR components: SM/OPTSTAT, SM/ADVISOR, SQL_MANAGEMENT_BASE, STATSPACK & SM/OTHER.

Action / Repair:

When non-AWR space consumption is greater than or equal to 50% of total SYSAUX, run the following SQLs to identify the top non-AWR objects.

REM Top 10 non-AWR objects consuming high SYSAUX space

set lines 200
set pages 100
col segment_name format a40;
col mb format 99,990.0;
col segment_type format a20;

select 'NON_AWR  ' as component, s.size_m as mb,
       s.owner || '.' || s.segment_name
        || (case when s.partition_name is null then ''
                 else '.' || s.partition_name end) as segment_name,
       s.segment_type
from
  (select segment_name, partition_name, owner, bytes/1024/1024 size_M, segment_type
     from dba_segments
     where tablespace_name = 'SYSAUX'
       and not (segment_name like 'WRH%' or segment_name like 'WRM%')
       and bytes > 500000
     order by size_M desc) s
  where rownum <= 10;

Note: For more objects information, you can run the following script in SQLPlus:

@$ORACLE_HOME/rdbms/admin/awrinfo.sql

Refer to the section "Space usage by non-AWR components (> 500K)" in the output.

Check for unaccounted space in SYSAUX which do not have entry in V$SYSAUX_OCCUPANTS by running the following:

(A) Total SYSAUX Usage in MB

SQL> select sum(bytes)/1024/1024 "Total SYSAUX Usage in MB"  from dba_segments where tablespace_name='SYSAUX';

(B) Total Accounted Space in SYSAUX per V$SYSAUX_OCCUPANTS

SQL> select sum(SPACE_USAGE_KBYTES)/1024 from v$sysaux_occupants;

(C) Total Unaccounted Space in Sysaux = (A) - (B) 

column owner format a30
select distinct owner,sum(bytes)/1024/1024 "Size in MB" from dba_segments where tablespace_name='SYSAUX' and owner not in (select distinct schema_name from v$sysaux_occupants) group by owner order by 2 desc;

NOTE: If the above space consumption is high [ more than 50% of (A) ] then consider moving those users to other tablespaces.

Refer the following documents for troubleshooting:

1. Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER (Doc ID 329984.1)
2. Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)
3. Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace (Doc ID 1499542.1)
4. SYSAUX Grows Because Optimizer Stats History is Not Purged (Doc ID 1055547.1)

Known Bug:

Doc ID 14373728.8 => Bug 14373728 - Old statistics not purged from SYSAUX tablespace - affects 11.2.0.2 & 11.2.0.3 only

It is recommended to apply the above patch if found relevant and not applied already.

If you found none of the non-AWR objects consumption is higher & only negligible then you can safely ignore this.

Such warnings might come if your total SYSAUX space is lesser as the checks are based on 50% of total SYSAUX. In that case, you can increase the size of the SYSAUX tablespace to avoid the warnings.