Upgrade oracle11g Database

Upgrade oracle11g Database

Oracle support cycle

 

Oracle is also the same for its own products. For its own products in different periods, the support intensity is different.
In general, the supported intensity is divided into three levels: Premier Support (highest-priority Support), Extended Support (medium-priority Support), and Sustaining Support (lowest-priority Support)

1. 5-year standard support service period from product release-Premier support

2. subsequent three-year extended support service period ------- extended support

3. Enter the permanent support service period ------------ sustaining support

However, according to different cycles, the Oracle support fee will be different. In the extended server, 10% of the fee will be charged in the first year to provide support, and 20% of the fee will be charged in the second year to provide support, in the third year, 20% of the fee is charged to provide support.

Oracle Database Upgrade is inevitable with the release of Oracle11g database. The upgrade includes two parts: database software upgrade and Database Upgrade.

Upgrade Roadmap

 

General upgrade steps:

(1) back up the database

(2) Run patchset and Upgrade oracle software

(3) prepare a new ORACLE_HOME

(4) Run dbua or script to upgrade the instance

(5) Check the updated version information and invalid objects.

Upgrade patch Selection

Metalink Document id: 1922396.1 updates the required patch number

Upgrade Method Selection

There are many methods, each of which has its own characteristics. select an appropriate upgrade method based on the characteristics.

 

 

You can choose to upgrade 9i and 11g to the latest 11gR2 for Reference Document id 1674333.1 and 1602485.1.

 

Step 2
======
Download the RDBMS Software 11.2.0.2 or later.

Step 2
======
Install the latest 11.2 RDBMS Software in the new ORACLE_HOME.

Run the runInstaller of the Patchset.

./RunInstaller

Note that the Patchset of 11gR2 can be directly used for installation. You can select the operation type on this interface. This function is more convenient, which leads to a larger patchset. Here we select upgrade an existing database.

Note the installation location here. My previous installation directory is 11.2.0. Here I changed it to 11.2.0.4. oracle will be installed in other locations, which can reduce the downtime and is recommended by oracle.

Step 2
======
After installing the latest 11.2 RDBMS Software, start the 11.2.0.1 instance with the previous ORACLE_HOME and execute $ ORACLE_HOME/rdbms/admin/utluw. I of 11.2.0.2 for the previous running instance. SQL script, and the output spool to a file. Run the new script in the original directory.
Whether you are using DBUA or manual Upgrade, you must run the Pre-Upgrade Information Tool. Otherwise, an error may occur:

1. Log in to the system as theowner of the environment of the database being upgraded.

Important:

The Pre-UpgradeInformation Tool must be copied to and must be run from the environment of thedatabase being upgraded.

Note: The New ORACLE_HOME script must be called here.

2. Start SQL * Plus.

3. Connect to the databaseinstance as a user with SYSDBA privileges.

4. Set the system to spool resultsto a log file for later analysis:

SQL> SPOOL upgrade_info.log

5. Run the Pre-Upgrade InformationTool:

SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112i. SQL

6. Turn off the spooling of scriptresults to the log file:

SQL> SPOOL OFF

Step 2
======
Run the dbupgdiag. SQL script in the following My Oracle Support Article to verify that all components in dba_registry are valid and that no invalid data dictionary object exists in dba_objects.

If dbupgdiag. if the SQL script reports any invalid object, run $ ORACLE_HOME/rdbms/admin/utlrp. SQL (may need to be used multiple times) to make invalid objects in the database valid until the number of invalid objects does not change.

Step 2
=====
Disable all batch processing and cron jobs, and then perform full database backup.

1. log on to RMAN:
Rman "target/nocatalog"
2. Run the following RMAN command:
RUN
{
Allocate channel chan_name type disk;
Backup database format 'some _ backup_directory % U' TAG before_upgrade;
Backup current controlfile format 'controlfile location and name ';
}

Step 2
========
Close the database cleanly.

Procedure:

Step 2 (Windows platform only)
======================================
1) set the environment variable ORACLE_HOME to point to 11.2.0.1 for installation.
2) Stop the Oracle database service that points to 11.2.0.1.

C: \> net stop OracleServiceORCL
3) use the % ORACLE_HOME % \ bin \ ORADIM executable program to delete the 11.2.0.1 Oracle service.

C: \> ORADIM-DELETE-SID ORCL
4) set the environment variable ORACLE_HOME to point to 11.2.0.2 for installation.

5) copy the init. ora/spfile and password file (orapw <sid>. ora) from 11.2.0.1% ORACLE_HOME %/database to 11.2.0.2% ORACLE_HOME %/database.
6) set the network configuration file (listener. ora, sqlnet. ora, tnsnames. from 11.2.0.1% ORACLE_HOME % \ network \ admin (or $ TNS_ADMIN) to 11.2.0.2% ORACLE_HOME % \ network \ admin (or % TNS_ADMIN %.
7) If DB Console/DB Control is configured and used, copy the following two directories and their contents from 11.2.0.1 to 11.2.0.2. (If DB Console/DB Control is not configured, these directories may not exist .)
ORACLE_HOME/ORACLE_HOME/Solaris/j2ee/OC4J_DBConsole _ 8) use the executable program 11.2.0.2 to create the Oracle 11.2.0.2 service at a command prompt.

% ORACLE_HOME % \ bin \ ORADIM
C: \> ORADIM-NEW-SID-syspwd password-startmode auto-PFILE % ORACLE_HOME % \ DATABASE \ INIT <SID>. ORA
For example:

C: \> ORADIM-NEW-sid orcl-SYSPWD pass_with_sysdba_priv-startmode auto-PFILE % ORACLE_HOME % \ DATABASE \ INIT <SID>. ORA

PASSWORD = PASSWORD of the new database instance. This is the password of the user connected with the SYSDBA permission. -The SYSPWD option is not required. If this option is not specified, operating system authentication is used and no password is required.
Step 2 (Unix and Linux)
========================
Configure the target 11.2.0.2 ORACLE_HOME
1) Make sure that the environment variables ORACLE_BASE, ORACLE_HOME, PATH, NLS_10, and LIBRARY_PATH are set to point to 11.2.0.2 for installation.
Set ORACLE_SID to 11.2.0.1 database name to upgrade.
Modify the/etc/oratab file to point to your 11.2.0.2 ORACLE_HOME.
2) Disable Database Vault

Note 453903.1-Enabling and Disabling Oracle Database Vault in UNIX
3) copy the init. ora/spfile and password file (orapw <sid>. ora) from 11.2.0.1 $ ORACLE_HOME/dbs to 11.2.0.2 $ ORACLE_HOME/dbs
4) set the network configuration file (listener. ora, sqlnet. ora, tnsnames. from 11.2.0.1 $ ORACLE_HOME/network/admin (or $ TNS_ADMIN) to 11.2.0.2 $ ORACLE_HOME/network/admin (or $ TNS_ADMIN ).
5) If DB Console/DB Control is configured and used, copy the following two directories and their contents from 11.2.0.1 to 11.2.0.2. (If DB Console/DB Control is not configured, these directories may not exist .)
ORACLE_HOME/ORACLE_HOME/Solaris/j2ee/OC4J_DBConsole _ Specify the actual name for 6) Make sure that the COMPATIBLE initialization parameter is set correctly for Oracle Database 11g Version 2 (11.2. If COMPATIBLE is not set correctly, the Pre-Upgrade Information Tool displays a warning in the Database section.
7) Adjust the value of the initialization parameter to at least the minimum value indicated by Pre-Upgrade Information Tool. For customers who have installed JVM, you must set java_pool_size and shared_pool_size to at least 250 MB before the upgrade. Otherwise, the JVM upgrade may fail and the following error occurs:

ORA-07445: exception encountered: core dump [qmkmgetConfig () + 52] [SIGSEGV] [ADDR: 0x18] [PC: 0x103FFEC34] [Address not mapped to object] []

Procedure:

 

Procedure: (copy the listener and password file)

Edit Environment Variables

Modify/etc/oratab. The directory also contains ORACLE_HOME.

[Oracle @ dave db_1] $ cat/etc/oratab

If DB Console/DB Control is configured and used, copy the following two directories and their contents from 11.2.0.1 to 11.2.0.2. (If DB Console/DB Control is not configured, these directories may not exist .)
ORACLE_HOME/ORACLE_HOME/Solaris/j2ee/OC4J_DBConsole _

Make sure that the COMPATIBLE initialization parameter is set correctly for Oracle Database 11g Version 2 (11.2. If COMPATIBLE is not set correctly, the Pre-Upgrade Information Tool displays a warning in the Database section.

You must set java_pool_size and shared_pool_size to at least 250 MB before the upgrade,

Add parameters:

Create pfile from spfile;

Modify parameters

Finally generate spfile

SQL> create spfile from pfile;

Step 2
======
Manually upgrade the database.
1) Start sqlplus and run the catupgrd. SQL script under the newly installed target $ ORACLE_HOME/rdbms/admin.

Sqlplus "/as sysdba"
SQL> spool/tmp/upgrade. log
SQL> startup upgrade
SQL> set echo on
SQL> @ $ ORACLE_HOME/rdbms/admin/catupgrd. SQL;
SQL> spool off
SQL> Shutdown immediate

This very important step ensures the integrity and consistency of the new database software. If the parameter file contains obsolete initialization parameters when starting the database, delete these parameters from the initialization parameter file. If necessary, you can convert the spfile to pfile and then edit the pfile and delete related parameters.
Run Post-Upgrade Status Tool $ ORACLE_HOME/rdbms/admin/utlu112s. SQL, which provides a summary about the upgrade. it displays the status of each database component after the upgrade and the time it takes to upgrade each component. Any errors encountered during the upgrade will also be listed. These errors must be properly handled.

$ Sqlplus "/as sysdba"
SQL> STARTUP
SQL> @ utlu112s. SQL
Run catuppst. SQL in the $ ORACLE_HOME/rdbms/admin directory to complete other UPGRADE operations that do not need to be performed in the database in UPGRADE mode:

SQL> @ catuppst. SQL
This script can run in parallel with utlrp. SQL. Run utlrp. SQL in another session to re-compile the remaining PL/SQL and Java code:

SQL> @ utlrp. SQL
Run dbupgdiag. SQL in the following document to check the database integrity after the upgrade.

Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag. SQL)
If dbupgdiag. SQL finds some invalid objects, execute $ ORACLE_HOME/rdbms/admin/utlrp. SQL multiple times to re-compile these invalid objects until the number of invalid objects does not change.
After re-compiling these invalid objects, run dbupgdiag. SQL again to confirm that everything is normal.

Steps After Upgrade
==============================

1) upgrade cluster configuration

If you are using an Oracle cluster, you must update the cluster configuration.

Starting from 11.2.0.2, the upgrade command updates the configuration to the version of the running software.

You can use the srvctl command, for example:

Srvctl upgrade database

This command updates the configuration information of the database and its corresponding service to the version of the software that is running.

The syntax and options are as follows:

Srvctl upgrade database-d db_unique_name-o Oracle_home
The Table A-161 srvctl upgrade database Options
Option description
-D db_unique_name
Database unique name
-O Oracle_home
ORACLE_HOME path

2) use DBMS_DST to upgrade the time zone to the latest version.

Note 1201253.1
Title: Actions For DST Updates When Upgrading To Or Applying The 11.2.0.2 Patchset
3) upgrade Recovery Catalog
For more information about how to UPGRADE the Recovery Catalog and upgrade catalog commands, see the topics in the Oracle Database Backup and Recovery User's Guide.
4) Upgrade the Statistics Table created by the DBMS_STATS package
If you have created a statistics table using DBMS_STATS.CREATE_STAT_TABLE, run the following command to upgrade the tables:

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE ('Scott ', 'stat _ table ');
In the example, SCOTT is the owner of the statistics table, and STAT_TABLE is the name of the statistics table. Execute this command for each statistics table.
5) Enable Oracle Database Vault and revoke the DV_PATCH_ADMIN role.
If you have used the Oracle Database Vault and disabled it before upgrading according to the previous instructions, you need:
Enable Database Vault.

Note 453903.1-Enabling and Disabling Oracle Database Vault in UNIX
Revoke the Database Vault DV_PATCH_ADMIN role of the SYS user.
Reference http://download.oracle.com/docs/cd/E11882_01/server.112/e17222.pdf

6) The Oracle Warehouse Builder (OWB) component in the database is not installed during the upgrade process. After the upgrade, there are several steps to upgrade the component.

7) Call dbca to recreate the OEM.

If dbca shows that the OEM has been configured, rm drops the following two directories and runs again.

ORACLE_HOME/hostname_dbname

ORACLE_HOME/Solaris/j2ee/OC4J_DBConsole_hostname_dbname

Finally, verify the version and status of each component:

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS. DBA_REGISTRY

COMP_NAME VERSION STATUS

-----------------------------------------------------------------------------

OWB 11.2.0.1.0 VALID

Oracle Application Express 3.2.1.00.10 VALID

Oracle Enterprise Manager 11.2.0.4.0 VALID

OLAP Catalog 11.2.0.4.0 VALID

Spatial 11.2.0.4.0 VALID

Oracle Multimedia 11.2.0.4.0 VALID

Oracle XML Database 11.2.0.4.0 VALID

Oracle Text 11.2.0.4.0 VALID

Oracle Expression Filter 11.2.0.4.0 VALID

Oracle Rules Manager 11.2.0.4.0 VALID

Oracle Workspace Manager 11.2.0.4.0 VALID

Oracle Database Catalog Views 11.2.0.4.0 VALID

Oracle Database Packages and Types 11.2.0.4.0 VALID

JServer JAVA Virtual Machine 11.2.0.4.0 VALID

Oracle XDK 11.2.0.4.0 VALID

Oracle Database Java Packages 11.2.0.4.0 VALID

OLAP Analytic Workspace 11.2.0.4.0 VALID

Oracle olap api 11.2.0.4.0 VALID

18 rows selected.

Now, the upgrade operation is complete.

If you encounter problems during the upgrade process, you can re-execute the upgrade script as follows:

1. Shut down the database as follows:

SQL> SHUTDOWNIMMEDIATE

2. Restart the databasein UPGRADE mode:

SQL> STARTUP UPGRADE

3. Set the system to spool results to a logfile for later verification of success:

SQL> SPOOLupgrade. log

4. Rerun catupgrd. SQL:

SQL> @ catupgrd. SQL

Note:

You can rerunthe catupgrd. SQL script as Daily times as necessary. the first timeyou run the script, there shoshould be no error messages returned. if you rerunthe script, then the ORA-00001 messageis displayed. you can safely ignore this message.

5. Rerun utlu112s. SQL:

SQL> @ utlu112s. SQL

Graphical upgrade

Graphical upgrade is simpler. After you call the graphical interface, press enter to complete the upgrade.

 

Next, the most important thing is to upgrade the database through DBUA. Then the OUI will prompt you whether to upgrade the ASM or database. If the database is installed on the asm, you must upgrade the asm first, then upgrade the database. The upgrade information is summarized and confirmed to start the upgrade. The Database Upgrade is patient because it may be slow. It is best to have a Data Guard or RAC environment during the upgrade process and provide services first.