Prerequisites for Oracle Database Upgrade

Oracle Database Upgrade has always been a complex project. DBAs need to spend a lot of time and effort on preparation for Product Database Upgrade because of its high complexity, therefore, even if you are fully prepared, unexpected problems may still occur during the upgrade process. In order to complete the upgrade task more efficiently and reduce the downtime, we need to create a "comfortable" defensive Database "atmosphere for the upgrade ":

1. To ensure the performance of the upgraded database, it is necessary to effectively collect the database performance statistics before the upgrade so that we can compare the performance problems after the upgrade:

• To ensure real and effective performance statistics, it is necessary to collect data one month before the Database Upgrade.
• The collected performance statistics should be as accurate and authentic as possible
• Use the Statspack performance report in Oracle 8i/9i, set the snapshot level to 6 or higher, set the snapshot interval to 30 minutes, and export the perfstat user using the exp tool before the specific upgrade, refer to Metalink Document Note: 466350.1 to introduce how to compare the Statspack snapshots before and after the upgrade.
• Use AWR auto load repository performance reports in Oracle 10g/11g to ensure that snapshots are collected for about 30 days. The interval between snapshots should be 30-60 minutes; then, you can use the dbms_swrf_internal.awr_extract stored procedure to export awr to the dumpfile file, load the AWR information after the upgrade, and use the DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML function to compare the performance

2. defensive measures before the formal upgrade:

• Excessive audit information may lead to a decrease in the update speed. You can export audit data before the upgrade and clear the audit dictionary base table:

 

1. Truncation SYS. AUD $ base table:
2. SQL>Truncate table sys. AUD $;

• It is also necessary to clear the recycle bin after 10 GB:

 

1. Clear DBA recycle bin:
2. SQL>Purge DBA_RECYCLEBIN;

• Remove some "expired" parameters. The reason for setting these parameters is probably to correct some problems in the original version, such as setting the event parameter; however, in the new version, it is worth discussing whether to set these parameters. Of course, you can submit an SR for this matter:

 

1. These "expired" parameters may include: old ones suchOptimizer_features_enable=8. 1.7.4,_ Always_semi_join=Off,_ Unnest_subquery=False
2. OrEvent="10061 trace name context forever, level 10"And so on.

• Collect statistics for data dictionaries in the database:

 

1. In Oracle 9i, you can perform the following process to collect data dictionary statistics,
2. SQL>Exec DBMS_STATS.GATHER_SCHEMA_STATS
3. ('Sys ',Options=>'Gather ',Estimate_percent=>
4. DBMS_STATS.AUTO_SAMPLE_SIZE,Method_opt=>'
5. All columns size auto ',Cascade=>TRUE );
7. Collecting dictionary statistics in Oracle10g/11g can be completed by the GATHER_DICTIONARY_STATS stored procedure:
8. SQL>Exec DBMS_STATS.GATHER_DICTIONARY_STATS;

• We need to prepare for the rollback Database Upgrade task. We can only restore the database by backing up the database before 10 Gb. After 10 Gb, we can use the Restore Point Feature of the flash back database to roll back the database, note the following:
  • Restore the source to archive the database and enable flashback database
  • Available only after Version 10.2
  • Ensure that the flashback recovery area has sufficient disk space.
  • Do not modify the compatible parameter immediately after the upgrade. The restore point cannot work across compatible.

   

  1. /* First, create an effective recovery point to ensure the flash back to the database before the official upgrade */
  5. SQL>Select * from global_name;
  7. GLOBAL_NAME
  8. --------------------------------------------------------------------------------
  9. Http://www.oracledatabase12g.com/archives/oracle%E6%95%B0%E6%8D% AE %E5%BA%93%E5%8D%87%E7%BA%A7%E5%89%8D%E5%BF%85%E8%A6%81%E7%9A%84%E5%87%86%E5%A4%87%E5%B7%A5%E4%BD%9C.html
  12. SQL>Create restore point pre11gupgrd guarantee flashback database;
  13. Restore point created.
  15. /* After confirming the above four notes, we can implement the upgrade with confidence */
  16. SQL>Shutdown immediate;
  17. ..............
  18. SQL>@? /Rdbms/admin/catupgrd. SQL
  19. .............
  20. Upgrade failed
  22. /* When an error that cannot be bypassed occurs during the upgrade, we may have to roll back the database to the Restore point, that is, before the upgrade */
  24. /* Restore the environment to 10 Gb after the instance is closed */
  26. SQL>Startup mount;
  28. /* Return to restore origin pre11gupgrd */
  29. SQL>Flashback database to restore point pre11gupgrd;
  30. Flashback complete.
  32. SQL>Alter database open;
  33. Alter database open
  34. *
  35. ERROR at line 1:
  36. The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
  38. SQL>Alter database open resetlogs;
  40. /* Use resetlogs to open the database */
  42. /* It is necessary to delete this restore point */
  43. SQL>Select * from v $ restore_point;
  45. SCN DATABASE_INCARNATION # GUA STORAGE_SIZE
  46. ----------------------------------------------
  47. TIME
  48. ---------------------------------------------------------------------------
  49. NAME
  50. --------------------------------------------------------------------------------
  51. 5081633 3 YES 15941632
  52. 08-FEB-11 08.20.33.000000000 PM
  53. PRE11GUPGRD
  55. SQL>Drop restore point pre11gupgrd;
  56. Restore point dropped.
  
  
  

  
  

• Download the latest version of the pre-upgrade check script, as shown in figureUtlu102i. SQL/utlu111i. SQL/utlu112i. SQLMetalink Document Note: 884522.1 <How to Download and Run Oracle's Database Pre-Upgrade Utility> indicates

 

1. /* Upload the update information spool to the log file */
2. SQL>SPOOL/tmp/UPGRADE/utluw. I. log
3. SQL>@/Tmp/UPGRADE/utluw. I. SQL

• You need to pay attention to the invalidation objects in SYS and SYSTEM User Mode. It is necessary to repair all the invalidation objects before the upgrade:

 

1. Select unique object_name, object_type, owner
2. FROM dba_objects
3. WHEREStatus='Invalid';

• After the upgrade is complete, we recommend that you run the utlrp. SQL script to re-compile (Recompile) objects. The invalid objects before and after the upgrade from 11.1.0.7 will be automatically compared and executed? The/rdbms/admin/utluiobj. SQL script can list the comparison information. The base table registry $ sys_inv_objs and registry $ nonsys_inv_objs respectively list the database's invalid sys or non-sys objects:

1. SQL>Select * from v $ version;
3. BANNER
4. --------------------------------------------------------------------------------
5. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
6. PL/SQL Release 11.2.0.1.0-Production
7. CORE 11.2.0.1.0 Production
8. TNS for Linux: Version 11.2.0.1.0-Production
9. NLSRTL Version 11.2.0.1.0-Production
11. SQL>@? /Rdbms/admin/utluiobj. SQL
12. .
13. Oracle Database 11.1 Post-Upgrade Invalid Objects Tool 22:23:22
14. .
15. This tool lists post-upgrade invalid objects that were not invalid
16. Prior to upgrade (it ignores pre-existing pre-upgrade invalid objects ).
17. .
18. Owner Object Name Object Type
19. .
20. SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW
22. PL/SQL procedure successfully completed.

3. Fixed invalid components during the upgrade process (component)

• Make sure that these components are linked to the current Oracle software 2-in-hexadecimal executable file or library file.
• If you confirm that some components (component) are not used and want to manually remove them completely (or you want to reinstall them again), refer to the following documents:Note: 472937.1 Information On Installed Database Components/Schemas

1. Note.300056.1 Debug and Validate Invalid Objects
2. Note: 753041.1 How to diagnose Components with non valid status
3. Note.7300007.1 How to Determine if XDB is Being Used in the Database?
5. Component upgrade failed instance 1: the Database has been upgraded from 10.2 to 11.2. In a 10g environment, the Database Vault component has been installed,
6. The Database Vault component is turned off before the relink is upgraded. The XDB component fails to be upgraded to 11.2;
7. The reason is that the installation or switching of Database Vault will invalidate the XDB component, or the cause is Bug 8942758.
8. The solution is to execute the utlrp. SQL script to re-compile the invalid objects and components before the upgrade. In this example, execute utlrp. SQL to enable the XDB component valid.
10. Component upgrade failed instance 2: the database is upgraded from 10.2.0.4 to 11.1.0.7, and the "oracle server" component is invalid during the upgrade;
11. The reason is that the DMBS_SQLPA package references a column that does not exist. For this problem, see metalink documentation 782735.1 and Notes: 605317.1/736353.1.
12. An effective solution is as follows:
13. 1. drop sys. PLAN_TABLE $ base table or synonym PUBLIC. PLAN_TABLE before the upgrade.
14. 2. If you have performed the upgrade operation and encountered the problem, you can use the following methods to fix the problem:
15. @ Catplan. SQL -- recreate the plan table
16. @ Dbmsxpln. SQL -- reload dbms_xplan spec
17. @ Prvtxpln. plb -- reload dbms_xplan implementation
18. @ Prvtspao. plb -- reload dbms_sqlpa
19. Alter package SYS. DBMS_SUMADVISOR compile;
20. Alter package SYS. DBMS_SUMADVISOR compile body;

4. run commands such as slibclean on AIX to clean up the operating system environment. If shared library files are not cleaned and loaded on a few proprietary platforms, the upgrade may fail.

5. Open the echo output of sqlplus before executing the catupgrd. SQL script to dump all the output information during the upgrade to the log file:

 

1. SQL>Set echo on
3. SQL>SPOOL/tmp/upgrade. log
5. SQL>@ Catupgrd. SQL
6. SQL>Spool off

By default, the DBUA graphical upgrade tool outputs spool and "echo". These logs can be found in the $ ORACLE_HOME/export toollogs/dbua // upgrade/directory.