Discussion of database upgrades (10GR2 to 11GR2)

Today we have a general understanding of the database upgrade, or a lot of things worth considering.

First, the pre-upgrade preparation is a lot of work to do:

1. Testing and preparation, mainly applied to do stress testing. Because of the version upgrade, the optimizer may make SQL performance fallback (with a less selective execution plan), can be combined with database replay to do stress testing, spa simulates the impact of environmental changes, and the SPM is fixed on the execution plan.

2. The method of upgrading, mainly is the graphical interface Dbua (the path of the new directory) to complete, relatively simple; manual upgrade (manual execution of scripts, Dbua only to replace these manual actions, the new directory path), EXPDP (Source Library Path)/IMPDP (new library path) and so on.

Here are several ways to support rolling upgrades:

It can be seen that the program of upgrading is varied according to the different environment.

3. Before upgrading, be sure to $oracle_home/rdbms/admin/the new library path Utlu112i.sql script to the source library execution, mainly to check whether the current environment (such as table space size, implied parameters, warnings or recommendations) is appropriate to upgrade, we can be based on warnings and recommendations to adjust accordingly.

The following glue out the single-machine database upgrade experiment step, for parameters, from 10.2.0.5 upgrade to 11.2.0.3.

Time permitting, will do a RAC upgrade and update the article

======================================================================================================

First, the Source library--Backup

Run {
Allocate channel CH1 device type disk;
Allocate channel CH2 device type disk;
Allocate channel CH3 device type disk;
Allocate channel CH4 device type disk;
Backup as BACKUPSET full tag ' db_full ' format '/backup/db_%d_%t_%s_%u.bak ' database include current controlfile;
SQL ' alter system archive log current ';
Release channel CH4;
Release channel CH3;
Release channel CH2;
Release channel CH1;
}

sql> archive log list;
Database Log Mode Archive mode
Automatic Archival Enabled
Archive destination/arch/ora10g
Oldest online log sequence 49
Next log sequence to archive 51
Current Log Sequence 51

Second, Source Library-run script utlu112i.sql, do pre-upgrade check
[Email protected] backup]$ Sqlplus/as SYSDBA

Sql*plus:release 10.2.0.5.0-production on Mon Jan 26 20:27:42 2015

Copyright (c) 1982, Oracle. All rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options

Sql> Spool/tmp/upgrade_info.log
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql

Print the contents as follows:
================
Sql> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql
Oracle Database 11.2 pre-upgrade information Tool 01-26-2015 20:34:56
Script version:11.2.0.3.0 build:001
.
**********************************************************************
Database:
**********************************************************************
-name:ora10g
-version:10.2.0.5.0
-compatible:10.2.0.5.0
-blocksize:8192
--Platform:linux x86 64-bit
-TimeZone File:v4
.
**********************************************************************
Tablespaces: [Make adjustments in the current environment]
**********************************************************************
--SYSTEM tablespace is adequate for the upgrade.
..... minimum required size:643 MB
--UNDOTBS1 tablespace is adequate for the upgrade.
..... minimum required size:400 MB
--Sysaux tablespace is adequate for the upgrade.
..... minimum required size:433 MB
--TEMP tablespace is adequate for the upgrade.
..... minimum required size:60 MB
.
**********************************************************************
Flashback:off
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 Init.ora or SPFile]
Note:pre-upgrade tool is run on a lower version 64-bit database.
**********************************************************************
--If Target Oracle is 32-bit, refer this for Update Parameters:
--No Update parameter changes is required.
.

--> If Target Oracle is 64-bit, refer here for Update Parameters:
--No Update Paramete R changes is required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 Init.ora or SPFile]
**********************************************************************
-- No renamed parameters found. No changes is required.
.
**********************************************************************
obsolete/deprecated Parameters: [ Update Oracle Database 11.2 init.ora or SPFile]
*****************************************************************
-sql_trace    10.2     DEPRECATED
--Background_dump_dest   11.1     deprecated  replaced by  "Diagnostic_dest"
--user_dump_dest    11.1     deprecated  replaced by  "Diagnostic_dest"
.

**********************************************************************
Components: [The following database components would be upgraded or installed]
**********************************************************************
-Oracle Catalog views [upgrade] VALID
--Oracle Packages and Types [upgrade] VALID
--Jserver JAVA Virtual machine [upgrade] VALID
--Oracle XDK for Java [upgrade] VALID
--Oracle Workspace Manager [upgrade] VALID
--EM Repository [upgrade] VALID
--Oracle XML Database [upgrade] VALID
--Oracle Java Packages [upgrade] VALID
--Oracle intermedia [upgrade] VALID
--Expression Filter [upgrade] VALID
--Rule Manager [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING:--Database is using a timezone file older than version 14.
.... After the release migration, it's recommended that DBMS_DST package
Used to upgrade the 10.2.0.5.0 database timezone version
... to the latest version which comes with the new release.
WARNING:--Database contains INVALID objects prior to upgrade.
.... The list of invalid Sys/system objects is written to
...... registry$sys_inv_objs.
.... The list of Non-sys/system objects was written to
...... registry$nonsys_inv_objs.
.... Use Utluiobj.sql after the upgrade to identify any new invalid
..... objects due to the upgrade.
.... USER public has 234 INVALID objects.
.... USER TEST has 3 INVALID objects.
.... USER SYSTEM has 2 INVALID objects.
.... USER Sysman has INVALID objects.
.... USER SYS has 198 INVALID objects.
.... USER Wmsys has 6 INVALID objects.
WARNING:-EM database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to Upgrade.
WARNING:--Your Recycle Bin is turned on and currently contains no objects.
.... Because It is REQUIRED that the Recycle Bin was empty prior to upgrading
.... and your Recycle Bin is turned on, the command of the need to execute:
PURGE Dba_recyclebin
..... prior to executing your upgrade to confirm the Recycle Bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
Upgrading the database.
To gather dictionary statistics execute the following command
While connected as SYSDBA:

EXECUTE Dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command
While connected as SYSDBA:

SELECT name,description from SYS. V$parameter WHERE Name
Like ' \_% ' ESCAPE ' \ '

Changes'll need to being made in the Init.ora or SPFile.

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing Non-default events execute the following commands
While connected as SYSDBA:
Events:
SELECT (Translate (VALUE,CHR (13) | | Chr (Ten), ")) from Sys.v$parameter2
WHERE UPPER (name) = ' EVENT ' and isdefault= ' FALSE '

Trace Events:
SELECT (Translate (VALUE,CHR (13) | | Chr (Ten), ")) from Sys.v$parameter2
WHERE UPPER (name) = ' _trace_events ' and isdefault= ' FALSE '

Changes'll need to being made in the Init.ora or SPFile.

**********************************************************************
Oracle recommends examining audit tables aud$ and fga_log$ before
Upgrading the database.

This database had 0 rows in aud$ and at fga_log$ that
'll is updated during the database upgrade from 10.2.0.5.0.

During This upgrade, null Dbids in aud$ and fga_log$ would be updated
With non-null values.

The upgrade downtime could be affected if there is many rows to update.
If downtime is a concern, the audit update could was done manually prior
to upgrading the database.

Refer to My Oracle support Note 1329590.1 titled
Pre-process SYS. aud$ Records pre-upgrade from 10.1 or later to 11.2 ".

**********************************************************************

<<<<<<<<< according to the output results, do the relative adjustment ... omitted here

Third, upgrading
[Email protected] ~]$ env | grep ORA
oracle_sid=ora10g
Oracle_base=/u01/app/oracle
Oracle_term=xterm
Oracle_home=/u01/app/oracle/product/11.2.0/db_1
[[email protected] ~]$ lsnrctl start

Lsnrctl for linux:version 11.2.0.3.0-production on 26-jan-2015 20:41:52

Copyright (c) 1991, Oracle. All rights reserved.

Starting/u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr:please wait ...

Tnslsnr for Linux:version 11.2.0.3.0-production
System parameter File Is/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages Written To/u01/app/oracle/diag/tnslsnr/tivoli02/listener/alert/log.xml
Listening on: (Description= (Address= (protocol=tcp) (host=tivoli02) (port=1521)))
Listening on: (Description= (Address= (PROTOCOL=IPC) (key=extproc1521)))

Connecting to (Description= (address= (protocol=tcp) (host=tivoli02) (port=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for Linux:version 11.2.0.3.0-production
Start Date 26-jan-2015 20:41:52
Uptime 0 days 0 hr. 0 min. 0 sec
Trace level off
Security on:local OS Authentication
SNMP OFF
Listener Parameter File/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File/u01/app/oracle/diag/tnslsnr/tivoli02/listener/alert/log.xml
Listening Endpoints Summary ...
(Description= (address= (protocol=tcp) (host=tivoli02) (port=1521)))
(Description= (address= (PROTOCOL=IPC) (key=extproc1521)))
The listener supports no services
The command completed successfully
[email protected] ~]$ which lsnrctl
/u01/app/oracle/product/11.2.0/db_1/bin/lsnrctl

Sqlpus/as SYSDBA
Startup upgrade
Sql> SPOOL Upgrade.log
Sql> @?/rdbms/admin/catupgrd.sql---about minutes
Sql> STARTUP
Sql> @?/rdbms/admin/utlu112s.sql
Sql> @?/rdbms/admin/catuppst.sql
Sql> @?/rdbms/admin/utlrp.sql
Sql> SELECT Count (*) from dba_invalid_objects;
Sql> SELECT distinct object_name from dba_invalid_objects;
<<<<<< If you have a failed object, you need to re-compile it manually

Discussion of database upgrades (10GR2 to 11GR2)