ORA-20200: The instance was shutdown between snapshots – Solved!!!

The AWR Report is only generated using snapshots from period that instance was Started. If any shutdown occurrs it break stats and AWR can’t generate a report comparing a period where stats belong a old Instance Startup.

This occurrs because Instance stats is not persistent accross reboots, (as the name says is a Instance), so all stats get reseted in every reboot.

When generating reports between hours is easy identify when instance was started, but when generating awr reports between many days this become a painfull task if instance was restarted multiples times during a desired period.

How to find the best Interval to Generate your AWR Reports?

To Single Instance this can be done using bellow query:

UNDEF num_days
COL startup_time FOR a30
COL db_name FOR a10
COL snap_start FOR 9999999
COL snap_end FOR 9999999
COL start_interval FOR a25
COL end_interval FOR a25
COL range_interval FOR a40
COL qtd_snaps FOR 999

    MIN(snap_id) snap_start,
    MAX(snap_id) snap_end,
    MIN(end_interval_time) start_interval,
    MAX(end_interval_time) end_interval,
    EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
    || ' Days(s) '
    || EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
    || ' Hour(s) '
    || EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
    || ' Minute(s) ' range_interval,
    MAX(snap_id) - MIN(snap_id) qtd_snaps
    dba_hist_snapshot s,
    dba_hist_database_instance di
    di.dbid = s.dbid
    AND   di.instance_number = s.instance_number
    AND   end_interval_time > DECODE(&&num_days,0,TO_DATE('31-JAN-9999','DD-MON-YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy'
) - (&num_days - 1) )
    startup_time ASC;








In above output is easy identify what SNAP_ID to use without keep trying and getting ORA-20200 or by reading a huge list of snaps.

The above query is NOT valid to get SNAP_ID to generate AWR Global RAC Report.
Soon as possible I will post a  Query to generate  same output to easy our life when generating  AWR Global RAC Report.

Hope its helps.


IBM POWER7 AIX and Oracle Database performance considerations

This post is intended to provide DBA's with advice and website links to assist with IBM Power Systems running in an Oracle environment.
Most issues that show up on POWER7 are the result of not following best practices advice that applies to all Power Systems generations.

Metalink: IBM POWER7 AIX and Oracle Database performance considerations -- 10g & 11g (Doc ID 1507249.1)

IBM White paper: IBM POWER7 AIX and Oracle Database performance considerations

Why Use Parallel Processing?

This demonstration compares the use of parallel and serial processing for the same SQL query.
This demonstration is associated with lessons 2 and 3 of the Oracle University seminar: Parallel Processing in Oracle 11g.


Oracle Architecture (9i,10g,11g) and Tuning on AIX (5.3,6.1,7.1)

This paper is intended for IBM Power Systems customers, IBM Technical Sales Specialists, and consultants who are interested in learning more the requirements involved in building and tuning an Oracle RDBMS system for optimal performance on the AIX platform.

This white paper contains best practices which have been collected during the extensive period of time my team colleagues and I have spent working in the Oracle RDBMS based environment. It is focused on the AIX versions 5.3, 6.1, 7.1 and Oracle 9i, 10g and 11g.

This paper begins with a short descript ion of the most important Oracle DB architectural elements. It continues with an overview of the AIX-related tuning elements that are most crucial for optimal DB activity.

This document can be expanded into many different OS or DB-related directions. Addit ional informationon related topics is included in Appendix A of this paper, as well as references to supporting documentation. However, this paper is not focused on the application tuning area. Application performance tuning is a subject too broad to be covered in a white paper of this length.

See link below..
Oracle Architecture and Tuning on AIX (v 2.10)

Cluster Health Monitor (CHM)

Oracle is strongly encouraging the admins of Clusterware / RAC using the CHM (Cluster Health Monitor).
This tool (formerly known as Instantaneous Problem Detector for Clusters or IPD/OS) is designed to detect and analyze operating system (OS) and cluster resource related degradation and failures in order to bring more explanatory power to many issues that occur in clusters where Oracle Clusterware and Oracle RAC are running such as node eviction. It tracks the OS resource consumption at each node, process, and device level continuously. It collects and analyzes the cluster-wide data. In real time mode, when thresholds are hit, an alert is shown to the operator. For root cause analysis, historical data can be replayed to understand what was happening at the time of failure.

See this presentation on this issue on link below

– What is? Why use? Where to get?
Cluster Health Monitor (CHM)
• Installation
– Of the Tool
– Of the GUI
• CHM in Action
• Administration
• FAQ & More Information
– OTN Migration



Oracle Real Application Clusters on IBM AIX Best practices in memory tuning and configuring for system stability


Customers who experience Oracle Real Application Clusters (RAC) node evictions due to excessive AIX kernel paging should carefully review and implement these recommended best practices. Testing and experience have found that memory over commitments may cause scheduling delays for Oracle’s ‘oprocd’ process resulting in node evictions.
Implementing all of these recommendations will reduce scheduling delays and corresponding oprocd initiated evictions.

Problem validation

This paper addresses the best practices for environments experience node evictions caused bycritical processes not being able to get scheduled in a timely fashion on AIX due to memory overcommitment. To validate that node evections are caused by this situation, the followingvalidation steps should be taken.

Click link below…

rac_aix_memory_tuning October 17 2011


RMAN Performance Tuning / Notes – My Oracle Support (Metalink)

I’ll post here useful notes from MOS about the RMAN Performance and Tuning.

To view these documents you must have access to the MOS.


RMAN Myths Dispelled: Common RMAN Performance Misconceptions [ID 134214.1]

This document will help dispel some of the common misconceptions related to proper usage of Recovery Manager (RMAN).




RMAN Restore Database Slow / How To Improve RMAN Restore Performance [ID 467694.1]

Goal: RMAN Restore database is slow.  Backup to tape takes 13 hours and the restore takes over 26hrs.



Advise On How To Improve Rman Performance [ID 579158.1]

Goal: How  to boost RMAN performance ?

Also is a recommended for improving RMAN performance on AIX5L based system. IBM suggestions the AIX related advices



RMAN Performance Tuning Diagnostics [ID 311068.1]

Provide diagnostic reference for collecting RMAN backup performance diagnostic information.



RMAN Performance Tuning Using Buffer Memory Parameters [ID 1072545.1]

The purpose of RMAN performance tuning is to identify the bottlenecks for a given backup or restore job and use RMAN commands, initialization parameters, or adjustments to physical media to improve overall performance.




RMAN: Monitoring Recovery Manager Jobs [ID 144640.1]

To inform all the Oracle Recovery Manager Documentation has the information to resolve most problems.

All RMAN users should review the RMAN Troubleshooting section of the online documentation provided.