Oracle Database Upgrade (10gR2 to 11gR2)

Oracle Database Upgrade (10gR2 to 11gR2)

I have a general understanding of Oracle Database Upgrade today, but there are still many things worth considering.

First, preparations before the upgrade require a lot of work:

1. Testing and preparation, mainly for stress testing of applications. Because of version upgrades, the Optimizer may roll back the SQL Performance (select an execution plan with worse performance). You can use database replay for stress testing to simulate the impact of environment changes, and SPM fixed the execution plan.

2. the upgrade method is mainly implemented by the graphical interface DBUA (path of the new directory), which is relatively simple; manual upgrade (manual execution of scripts, DBUA only replaces these manual actions, new directory paths), EXPDP (source library path), IMPDP (New library path), and so on.

The following methods support rolling upgrade:

We can see that there are various upgrade solutions, depending on different environments.

3. before the upgrade, make sure that $ ORACLE_HOME/rdbms/admin/utlu112i. the SQL script is copied to the source database for execution. It mainly checks whether the current environment (such as the tablespace size, implicit parameters, warnings, or suggestions) is suitable for upgrading. We can adjust it according to the warning and suggestions.

The following is an example of how to upgrade a single-host database from 10.2.0.5 to 11.2.0.3.

If the time permits, the RAC will be upgraded and the article will be updated.

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

I. source database-Backup

Run {
Allocate channel specified device type disk;
Allocate channel ch2 device type disk;
Allocate channel ch3 device type disk;
Allocate channel methane 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 44;
Release channel ch3;
Release channel ch2;
Release channel identifier;
}

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

Ii. source database-run the script utluw. I. SQL to check before Upgrade
[Oracle @ tivoli02 backup] $ sqlplus/as sysdba

SQL * Plus: Release 10.2.0.5.0-Production on Mon Jan 26 20:27:42 2015

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

Connected:
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/utluw. I. SQL

The printed content is 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: 64-bit for Linux x86
--> 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 was run on a lower version 64-bit database.
**************************************** ******************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**************************************** ******************************
Renamed Parameters: [Update Oracle Database 11.2 init. ora or spfile]
**************************************** ******************************
-- No renamed parameters found. No changes are 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 will 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 is recommended that DBMS_DST package
... Be 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 was written
... Registry $ sys_inv_objs.
... The list of non-SYS/SYSTEM objects was written
... 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 210 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
... 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 be empty prior to upgrading
... And your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
... Prior to executing your upgrade to confirm the recycle bin is empty.
.
**************************************** ******************************
Recommendations
**************************************** ******************************
Oracle recommends gathering dictionary statistics prior
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 will need to be 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 (10), '') FROM sys. v $ parameter2
Where upper (name) = 'event' AND isdefault = 'false'

Trace Events:
SELECT (translate (value, chr (13) | chr (10), '') from sys. v $ parameter2
Where upper (name) = '_ TRACE_EVENTS' AND isdefault = 'false'

Changes will need to be made in the init. ora or spfile.

**************************************** ******************************
Oracle recommends examining audit tables AUD $ and FGA_LOG $ before
Upgrading the database.

This database has 0 rows in AUD $ and 23 rows in FGA_LOG $ that
Will be updated during the database upgrade from 10.2.0.5.0.

During this upgrade, null DBIDs in AUD $ and FGA_LOG $ will be updated
With non-null values.

The upgrade downtime cocould be affected if there are updating rows to update.
If downtime is a concern, the audit update cocould be done manually prior
To upgrading the database.

Please refer to My Oracle Support Note 1329590.1 titled "How
Pre-Process SYS. AUD $ Records Pre-Upgrade From 10.1 or Later to 11.2 ".

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

<Adjust the output result relatively...

 

Iii. upgrading
[Oracle @ tivoli02 ~] $ 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
[Oracle @ tivoli02 ~] $ Lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0-Production on 26-JAN-2015 20:41:52

Copyright (c) 1991,201 1, 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
[Oracle @ tivoli02 ~] $ 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 30 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 invalid objects exist, manual re-compilation is required.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Sharing pool for Oracle Performance Optimization