Wednesday, December 18, 2019

Things to Know when you do 19c upgrade

Oracle 19c Upgrade Checklist and Best Practices

This document outlines key considerations and recommended steps when performing an upgrade to Oracle Database 19c, focusing on best practices for a smooth transition and optimal post-upgrade performance.

1. Recommended Upgrade Method

  • autoupgrade.jar: The autoupgrade.jar utility is the recommended and most robust way to perform Oracle 19c upgrades. It automates many pre-checks, pre-upgrade fixes, and post-upgrade tasks, simplifying the process and reducing manual errors.

2. Pre-Upgrade Checks

Before initiating the upgrade, ensure the following:

a. Dictionary Statistics

Verify that dictionary and fixed object statistics have been gathered recently. This is crucial for the optimizer's performance during and after the upgrade.

column OPERATION format a40
set linesize 200
select to_char(max(END_TIME),'DD-MON-YY hh24:mi') LATEST, OPERATION
from DBA_OPTSTAT_OPERATIONS
where OPERATION in ('gather_dictionary_stats','gather_fixed_objects_stats')
group by operation;

b. Stats on Clustered Indexes (If not using autoupgrade.jar)

If you are not using autoupgrade.jar (which typically handles this), it's recommended to gather statistics on critical SYS schema clustered indexes. This helps the optimizer in the new version.

exec dbms_stats.gather_schema_stats('SYS');
exec dbms_stats.gather_index_stats('SYS','I_OBJ#');
exec dbms_stats.gather_index_stats('SYS','I_FILE#_BLOCK#');
exec dbms_stats.gather_index_stats('SYS','I_TS#');
exec dbms_stats.gather_index_stats('SYS','I_USER#');
exec dbms_stats.gather_index_stats('SYS','I_TOID_VERSION#');
exec dbms_stats.gather_index_stats('SYS','I_MLOG#');
exec dbms_stats.gather_index_stats('SYS','I_RG#');

3. Post-Upgrade Actions

After the upgrade is complete, consider these immediate actions:

  • Adjust Stats History Retention:

    exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(14);

    This sets the statistics history retention to 14 days.

  • Set Key Parameters in SPFILE:

    • _cursor_obsolete_threshold=1024

    • deferred_segment_creation=false

    • _sql_plan_directive_mgmt_control=0

    • Set optimizer_adaptive_statistics=FALSE explicitly in your SPFILE (It's recommended to explicitly set this to FALSE as adaptive statistics can sometimes lead to unexpected plan changes.)

4. Optimizer Parameters

  • COMPATIBLE and OPTIMIZER_FEATURES_ENABLE:

    • Ensure the COMPATIBLE parameter is set to the latest version (e.g., 19.0.0).

    • The OPTIMIZER_FEATURES_ENABLE parameter should also be set to the latest version ('19.1.0') to leverage the latest optimizer enhancements.

5. Performance Analysis and Tuning

a. Collect Execution Plans Before Upgrade

Capture existing execution plans to compare them after the upgrade and identify any regressions.

  • From Cursor Cache: Query V$SQL_PLAN or GV$SQL_PLAN for active and frequently executed SQL statements.

  • Using AWR: Analyze AWR reports for top SQL statements.

  • SQL Tuning Sets (STS): The most robust method. Create an STS from the AWR or cursor cache to capture SQL statements, their execution statistics, and execution plans.

    • This allows you to replay the workload later using SQL Performance Analyzer (SPA).

b. Compare AWR Snapshots

  • AWRDDRPT.sql: Use the AWRDDRPT.sql script (located in $ORACLE_HOME/rdbms/admin) to generate AWR Diff reports. This allows you to compare performance metrics between AWR snapshots taken before and after the upgrade.

  • Export AWR Data: You can export AWR data using the awrexp script (also in $ORACLE_HOME/rdbms/admin) to analyze it on a different database or for long-term storage.

c. SQL Tuning Sets (STS) and SQL Performance Analyzer (SPA)

  • Capture STS: Capture a representative workload into a SQL Tuning Set.

  • Load STS: Load this STS into the upgraded database.

  • SQL Performance Analyzer (SPA): Use SPA (part of Real Application Testing) to compare the performance of the SQL statements in the STS before and after the upgrade. SPA identifies SQL statements with plan changes or performance regressions.

d. SQL Plan Management (SPM)

SPM is a powerful feature to control and stabilize execution plans.

  • Configuration:

    • DBMS_SPM.CONFIGURE('PLAN_RETENTION_WEEKS', 5); (Default is 53 weeks)

    • DBMS_SPM.CONFIGURE('SPACE_BUDGET_PERCENT', 5); (Default is 10%)

  • Baseline Capture:

    • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE (Set this to start recording new plans as baselines. Remember to turn it off after capturing.)

  • Baseline Selection/Usage:

    • OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE (Ensures the optimizer uses existing baselines.)

    • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE (Turn off capture during normal operation.)

  • Evolution:

    • DBMS_SPM.REPORT_AUTO_EVOLVE_TASK: Reports on the automatic evolution task.

    • DBMS_SPM.CREATE_EVOLVE_TASK: Manually creates a task to evolve (verify and accept) new plans into baselines.

e. SQL Tuning Advisor (STA)

  • Utilize the SQL Tuning Advisor to analyze problematic SQL statements identified during post-upgrade testing. It can recommend various tuning actions, including new indexes, SQL profile creation, or SQL structure changes.

f. Export/Import STS to New DB

  • After capturing an STS from the source database, you can export it and import it into the target (upgraded) database for performance analysis.

  • DBMS_SPM.LOAD_PLANS_FROM_SQLSET: This procedure can be used to load plans from an STS into the SQL Plan Baseline (SPM) repository of the new database.

g. Workload Capture and Replay

  • Real Application Testing (RAT): This feature allows you to capture a real production workload from the source database and replay it on the upgraded database. This provides a highly accurate way to test the impact of the upgrade on performance.

    • SPA is a free feature, while Real Application Testing (which includes workload capture/replay) requires a separate license.

h. Automatic SPM (Exadata 19c)

  • On Exadata with Oracle 19c, Automatic SPM can further simplify SQL plan management by automatically managing baselines for frequently executed SQL.

By following these guidelines, you can significantly improve the success rate and performance stability of your Oracle 19c database upgrade.

 -> Comptable (features) and optimiser_features_enable (use latest)  - Keep the latest

 -> Collect execution plan before upgrade (cursor cache and AWR) [how to ?] [sql tunning sets]

 -> compare AWR snapshots (AWRDDRPT.sql), You can export AWR data using the awrexp script in rdbms/admin

 -> capture STS -> load STS (SQL performance analyser)

 -> SPM  ( 53 week default - dbms_spm.configure('plan retention week',5) , (space_budget_percent',5)
   Baseline Capture -> optimiser_capture_sql_plan_baselines= TRUE (start recording ) 
   selection -> optimise_use_sql_plan_baselines= TRUE,OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE
   evolution -> dbms_spm.report_auto_evolve_task,  DBMS_SPM.CREATE_EVOLVE_TASK

 -> SQL Tuning Advisor

 -> export/import STS to new DB  

 -> DBMS_SPM.LOAD_PLANS_FROM_SQLSET

 -> capture workload, reply workload (compare)

 -> SPA is Real Application Testing.SPM is a free feature

 -> AUTOMATIC SPM - Exadata 19C 

Posted by at No comments:
Labels: ,

Tuesday, December 3, 2019

How to upgrade DB from 12.1 to 19.3

Oracle Database 12.1 to 19.3 Upgrade Steps

This document provides a step-by-step guide for upgrading an Oracle Database from version 12.1 to 19.3. It outlines key actions, from software installation to post-upgrade tasks, to ensure a successful and efficient upgrade process.

Pre-Upgrade Preparations

  1. Install Oracle 19c Software:

    • Install the Oracle Database 19.3.0 software binaries into a new Oracle Home directory. Do not install it over your existing 12.1 Oracle Home. This new home will be referred to as 19.3_Home.

  2. Remove Obsolete init Parameters:

    • Review your current init.ora or SPFILE for any parameters that are no longer supported or are obsolete in Oracle 19c. Remove or adjust these parameters as necessary. Refer to Oracle documentation for a complete list of obsolete parameters.

  3. Stop the Listener:

    • Before proceeding with the database upgrade, stop the Oracle Listener associated with your 12.1 database.

    • lsnrctl stop

  4. Gather Dictionary and Fixed Objects Statistics:

    • It is critical to have up-to-date dictionary and fixed object statistics before starting the upgrade. This helps the upgrade process itself and ensures optimal performance post-upgrade.

    EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

  5. Empty Recycle Bin:

    • Purge the recycle bin to avoid potential issues during the upgrade.

    PURGE DBA_RECYCLEBIN;

  6. Check and Update Time Zone File:

    • Verify the current time zone file version and update it to the latest version compatible with Oracle 19c if necessary. This is crucial for consistent time zone handling.

    • Refer to Oracle Support Note "Updating the Time Zone File and Timestamp with Time Zone Data in Oracle Database" for detailed instructions.

  7. Run Pre-Upgrade Information Tool:

    • Execute the preupgrade.jar tool from the 19c Oracle Home, pointing it to your 12.1 database. This tool performs a comprehensive analysis of your database for potential upgrade issues and generates fix-up scripts.

    (12.1_Home)/jdk/bin/java -jar (19.3_Home)/rdbms/admin/preupgrade.jar FILE TEXT DIR /home/oracle/upgrade

    • After running the tool, execute the generated fix-up script:

    @/home/oracle/upgrade/preupgrade_fixups.sql

    • Review the preupgrade.log and preupgrade_info.txt files for any remaining warnings or manual actions required.

Upgrade Execution

  1. Stop RAC Instances (if applicable):

    • If you are upgrading a Real Application Clusters (RAC) database, stop the second (and subsequent) RAC instances.

    • Disable the cluster on the first instance and then stop the first instance.

  2. Set Environment Variables for 19c:

    • Ensure your environment variables (especially ORACLE_HOME and PATH) are set to point to the new 19.3 Oracle Home.

  3. Start Database in Upgrade Mode:

    • Start the database from the 19.3 Oracle Home in upgrade mode.

    sqlplus / as sysdba
startup upgrade

  4. Invoke Database Upgrade:

    • Execute the dbupgrade utility from the 19.3 Oracle Home. This command initiates the actual database upgrade process.

    (19.3_Home)/bin/dbupgrade

    • Monitor the output of this command closely for any errors or warnings.

Post-Upgrade Actions

  • After dbupgrade completes, the database will typically shut down.

  • Start the database in normal mode from the 19.3 Oracle Home.

  • Run the post-upgrade scripts and perform necessary post-upgrade checks as recommended by Oracle documentation (e.g., catupgrade.sql, utlrp.sql).

  • Re-enable cluster services and start all RAC instances if applicable.

  • Perform performance analysis and tuning as outlined in the "Oracle 19c Upgrade Checklist and Best Practices" document, including AWR comparisons, STS analysis, and SPM configuration.

Following these steps carefully will help ensure a successful upgrade of your Oracle 12.1 database to 19.3.

Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)