Prerequisites for Oracle Database Upgrade

 

This article is transferred from the ml blog:

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

 

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:

(1) In order to ensure that the performance statistics are true and valid, it is necessary to collect data one month before the Database Upgrade.

(2) The collected performance statistics should be as accurate and truthful as possible

(3) Use the statspack performance report in Oracle 8i/9i, set the snapshot level to 6 or higher, and set the snapshot interval to 30 minutes, before the upgrade, export the perfstat user using the exp tool. Refer to Metalink Document Note: 466350.1 to introduce how to compare the statspack snapshots before and after the upgrade.

(4) 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:

 

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

Truncation SYS. AUD $ base table:

SQL> truncatetable SYS. AUD $;

 

For more information about Oracle 11g auditing, see:

Description of default Audit options for oracle11g

Http://blog.csdn.net/tianlesoftware/article/details/6707887

 

(2) It is also necessary to clear the recycle bin after 10 GB:

Clear DBA recycle bin:

SQL> purge dba_recyclebin;

 

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

These "Expiration" parameters may include: older ones such as optimizer_features_enable = 8.1.7.4, _ always_semi_join = OFF, _ unnest_subquery = false

Or event = "10061 trace name context forever, level 10", and so on.

 

You can view the parameter expiration in the V $ obsolete_parameter view.

For more information, see:

How to view Oracle parameter categories and Parameters

Http://blog.csdn.net/tianlesoftware/article/details/5583655

 

(4) collect statistical information for the data dictionary in the database:

In Oracle 9i, you can perform the following process to collect data dictionary statistics,

SQL> exec dbms_stats.gather_schema_stats

('Sys ', Options => 'gather', estimate_percent =>

Dbms_stats.auto_sample_size, method_opt =>'

All columns size auto', cascade => true );

 

Collecting dictionary statistics in Oracle10g/11g can be completed by the gather_dictionary_stats stored procedure:

SQL> execdbms_stats.gather_dictionary_stats;

 

We recommend that you execute this script before the oracle11g upgrade (utlu112i. SQL.

 

(5) We need to prepare for the rollback Database Upgrade task, which can only be completed by backup and recovery 10 Gb ago, after 10 Gb, we can use the Restore Point Feature of the flash back database to roll back the database, but pay attention to the following points:

(1) The Restore point requires that the database be in the archive and flashback database mode.

(2) features are only available after Version 10.2.

(3) ensure that the flashback recovery area has sufficient disk space.

(4) do not modify the compatible parameter immediately after the upgrade. The Restore point cannot work across compatible.

 

For more information and examples about flashbackrestore points, refer to my blog:

Oracleflashback database and restore points description

Http://blog.csdn.net/tianlesoftware/article/details/6917546

 

(6) download the latest version of the pre-upgrade check script (pre-upgrade checkscript), such as utlu102i. SQL/utlu111i. SQL/utlu112i. SQL; Metalink Document Note: 884522.1

This script is self-contained in the 11g environment and does not need to be downloaded.

 

/* Upload the update information spool to the log file */

SQL> spool/tmp/upgrade/utluw. I. Log

SQL> @/tmp/upgrade/utluw. I. SQL

 

For specific examples of the two scripts, refer:

Oracle uses RMAN to directly restore the database from 10g to 11g.

Http://blog.csdn.net/tianlesoftware/article/details/7311352

 

Example of upgrading oracle11.2.0.1 to 11.2.0.3

Http://blog.csdn.net/tianlesoftware/article/details/6833591

 

(7) 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:

Select unique object_name, object_type, owner

From dba_objects

Where status = 'invalid ';

 

(8) after the upgrade is complete, we recommend that you run the utlrp. SQL script to re-compile (recompile) objects. 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:

 

SQL> @? /Rdbms/admin/utluiobj. SQL

.

Oracle Database 11.1 post-upgrade invalidobjects tool 16:52:29

.

This tool lists post-upgrade invalidobjects that were not invalid

Prior to upgrade (it ignores pre-existingpre-upgrade invalid objects ).

.

Owner Object Name object type

.

Sys dbms_cube_exp package body

Sys dbms_network_acl_admin package body

Sys dbms_xs_principal_events_int package body

Sys KUPW $ WORKER package body

Sys Xs $ catview_util package body

 

PL/SQL procedure successfully completed.

 

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

(1) ensure that this component is indeed linked to the current Oracle software 2-in-the-go executable file or library file.

(2) If you confirm that some components (Component) are not used and want to manually remove them completely (or you want reinstall to reinstall them), refer to the following documents:

 

Note: 472937.1 information on installeddatabase components/Schemas

Note.300056.1 debug and validate invalidobjects

Note: 753041.1 how to diagnose componentswith non valid status

Note.7300007.1 how to determine if XDB isbeing used in the database?

 

Component upgrade failed instance 1:

The database is upgraded from 10.2 to 11.2. The Database Vault component has been installed in a 10 Gb environment, and the Database Vault component is turned off before the relink is upgraded, the XDB component fails to be upgraded during the upgrade to 11.2. The reason is that the installation or switching of the Database Vault will invalidate the XDB component, or is caused by bug 8942758.

Solution: run the utlrp. SQL script to re-compile the invalid objects and components before the upgrade. In this example, run utlrp. SQL to enable the XDB component valid.

 

Component upgrade failed instance 2:

When the database is upgraded from 10.2.0.4 to 11.1.0.7, the "ORACLE Server" component fails during the upgrade. 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.

 

An effective solution is as follows:

(1) Drop SYS. plan_table $ base table or public. plan_table before the upgrade.

(2) If you have performed the upgrade operation and encountered the problem, you can use the following methods to fix the problem:

@ Catplan. SQL -- recreate the plan table

@ Dbmsxpln. SQL -- reload dbms_xplan spec

@ Prvtxpln. PLB -- reload dbms_xplanimplementation

@ Prvtspao. PLB -- reload dbms_sqlpa

Alter package SYS. dbms_sumadvisor compile;

Alter package SYS. dbms_sumadvisor compilebody;

 

For more questions about components, refer:

Summary of Oracle component series

Http://blog.csdn.net/tianlesoftware/article/details/7339998

 

Oracle8i/9i/10g/11g component (components) Description

Http://blog.csdn.net/tianlesoftware/article/details/5937382

 

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:

SQL> set echo on

-- The default value is off. If it is set to on, the executed SQL statement is displayed, not just the result.

SQL> spool/tmp/upgrade. Log

SQL> @ catupgrd. SQL

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.

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Skype: tianlesoftware

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940