Upgrade Oracle 10g to Oracle 11gR2

FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals

Home

Members

Calendar

Who's On

Welcome Guest ( Login | Register )

Upgrade Steps From 10g to 11gR2

1. BACKUP BINARIES, DB,TNSNAMES,LISTENER,INIT,SPFILE,PWFILE THAT ARE BEING UPGRADED

2. COMPATIBLE PARAMETER MUST BE SET TO MINIMUM 10.0.0.0. THE RECOMMENDED IS 11.2.0 FOR 11gR2 PARAMETER FILE.

Part A.

Install the New Oracle Database Software & Apply any patches necessary.

Part B.

Run the Pre-Upgrade Information Tool (You Must Run This tool)

1. Copy the Pre-Upgrade Information Tool (utlul12i.sql) from the Oracle Database 11gR2 directory: $ORACLE_HOME/rdbms/admin to a temporary directory /tmp.

2. Set your environment to the one that is being upgraded. Assuming 10g.

3. Change directory to /tmp that you copied utlu112i.sql to in Step 1.

4. Start SQL*Plus and login as ‘/ as sysdba’

5. Spool the results to a log file:

o SQL> SPOOL upgrade_info.log

6. Run the Pre-Upgrade Information Tool:

o SQL> @utlul12i.sql

o SQL> SPOOL OFF

Check the output of the Pre-Upgrade Information Tool in upgrade_info.log.

The following is an example of the output generated by the Pre-Upgrade Information Tool:

Oracle Database 11.2 Pre-Upgrade Information Tool 10-14-2008 23:25:25 . ******************************************************************** Database: ****************************************************************** --> name: ORCL --> version: 10.2.0.2.0 --> compatible: 10.2.0.2 --> blocksize: 8192 Run the Pre-Upgrade Information Tool Upgrading to the New Release 3-9 --> platform: Linux IA (32-bit) --> timezone file: V2 . ****************************************************************** Tablespaces: [make adjustments in the current environment] ******************************************************************** WARNING: --> SYSTEM tablespace is not large enough for the upgrade. .... currently allocated size: 560 MB .... minimum required size: 910 MB .... increase current size by: 350 MB .... tablespace is NOT AUTOEXTEND ENABLED.
		--> UNDOTBS1 tablespace is adequate for the upgrade. .... minimum required size: 457 MB .... AUTOEXTEND additional space required: 352 MB --> SYSAUX tablespace is adequate for the upgrade. .... minimum required size: 617 MB .... AUTOEXTEND additional space required: 287 MB --> TEMP tablespace is adequate for the upgrade. .... minimum required size: 61 MB .... AUTOEXTEND additional space required: 41 MB --> EXAMPLE tablespace is adequate for the upgrade. .... minimum required size: 69 MB . ******************************************************************** Update Parameters: [Update Oracle Database 11.2 init.ora or spfile] ****************************************************************** WARNING: --> "sga_target" needs to be increased to at least 388 MB . ****************************************************************** Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile] ****************************************************************** WARNING: --> "plsql_compiler_flags" old value was "INTERPRETED"; new name is "plsql_code_type" new value is "INTERPRETED" . ****************************************************************** Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile] ****************************************************************** --> "max_enabled_roles" --> "remote_os_authent" --> "background_dump_dest" replaced by "diagnostic_dest" --> "user_dump_dest" 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 --> Messaging Gateway [upgrade] VALID --> OLAP Analytic Workspace [upgrade] VALID --> OLAP Catalog [upgrade] VALID --> Oracle Label Security [upgrade] VALID --> EM Repository [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle interMedia [upgrade] VALID --> Spatial [upgrade] VALID Run the Pre-Upgrade Information Tool 3-10 Oracle Database Upgrade Guide --> Data Mining [upgrade] VALID --> Expression Filter [upgrade] VALID --> Rule Manager [upgrade] VALID --> Oracle Application Express [upgrade] --> Oracle OLAP API [upgrade] VALID . ****************************************************************** Miscellaneous Warnings ******************************************************************** WARNING: --> Database is using a timezone file older than version 11. .... After the release migration, it is recommended that DBMS_DST package .... be used to upgrade the 10.2.0.2.0 database timezone version .... to the latest version which comes with the new release. WARNING: --> Database contains stale optimizer statistics.
	.... Refer to the 11g Upgrade Guide for instructions to update .... statistics prior to upgrading the database. .... Component Schemas with stale statistics: .... SYS .... WMSYS .... CTXSYS WARNING: --> Database contains INVALID objects prior to upgrade. .... The list of invalid SYS/SYSTEM objects was 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 7 INVALID objects. .... USER FLOWS_010600 has 1 INVALID objects. .... USER SYS has 1 INVALID objects. WARNING: --> Database contains schemas with objects dependent on network packages. .... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs. .... USER WKSYS has dependent objects. .... USER SYSMAN has dependent objects. .... USER FLOWS_010600 has dependent objects. WARNING: --> EM Database Control Repository exists in the database. .... Direct downgrade of EM Database Control is not supported. Refer to the .... 11g Upgrade Guide for instructions to save the EM data prior to upgrade. . WARNING: --> There are materialized view refreshes in progress. .... Ensure all materialized view refreshes are complete prior to upgrade. WARNING: --> There are files which need media recovery. .... Ensure no files need media recovery prior to upgrade. WARNING: --> There are files in backup mode. .... Ensure no files are in backup mode prior to upgrade. WARNING:--> There are outstanding unresolved distributed transactions. .... Resolve outstanding distributed transactions prior to upgrade. WARNING:--> A standby database exists. .... Sync standby database prior to upgrade. WARNING: --> log_archive_format must be updated. .... As of 10.1, log_archive_format requires a %r format qualifier .... be present in its format string. Your current setting is: .... log_archive_format='%t_%s.dbf'. .... Archive Logging is currently OFF, but failure to add the %r to the .... format string will still prevent the upgraded database from starting up. WARNING:--> recycle bin in use. .... Your recycle bin is turned on and it contains .... 3 object(s). It is REQUIRED .... that the recycle bin is empty prior to upgrading .... your database. Run the Pre-Upgrade Information Tool Upgrading to the New Release 3-11 .... The command: PURGE DBA_RECYCLEBIN 1. .... must be executed immediately prior to executing your upgrade.

Check the warning messages and rectify them as needed. The following points must be addressed correctly otherwise there maybe problems.

Example: CONNECT role now only has create session privilege.

If you have any db links with passwords, you may want to back up the sys.link$ table to save the information.

Oracle recommends gathering stats before the upgrade: EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

■ Deprecated CONNECT Role

■ Access Control to Network Utility Packages

■ Database Links with passwords

■ TIMESTAMP WITH TIME ZONE Data Type

■ Optimizer Statistics

■ Invalid Objects

■ Save Oracle Enterprise Manager Database Control Data

■ Complete Materialized View Refreshes

■ Ensure No Files Need Media Recovery

■ Ensure No Files Are in Backup Mode

■ Resolve Outstanding Distributed Transactions

■ Sync Standby Database with the Primary Database

■ Purging the Database Recycle Bin

Part C.

Prepare the new 11gR2 ORACLE_HOME

1. Copy configuration files (init file, tnsnames, listener, pwfile) from old Oracle Home to new.

2. Update init files with new COMPATIBLE parameter, fix any deprecated ones, and adjust the values to at least the minimum values indicated by the Pre-Upgrade Tool.

3. Update any relative path names in parameter file to fully path names.

Part D.

Upgrade the Database

1. Shutdown the database:

o SQL> SHUTDOWN IMMEDIATE;

2. Make sure the following checks:

o The oratab file points to Oracle Database 11g Release 2 Oracle Home

o The following environment variables point to the Oracle 11g Release 2 directories:

§ ORACLE_HOME

§ PATH

3. Change to the $ORACLE_HOME/rdbms/admin directory and start SQL*Plus

o sqlplus ‘/ as sysdba’

4. Start the instance by issuing following command (you may get messages that parameters are obsolete, fix those and start up the db again):

o SQL> STARTUP UPGRADE;

o SQL> SPOOL upgrade.log

5. Run the catupgrd.sql script:

o SQL> @catupgrd.sql

6. Once completed, shutdown the database and restart it.

o SQL> SHUTDOWN IMMEDIATE;

o SQL> STARTUP;

7. Run the Post-Upgrade Status Tool to provide a summary of the upgrade. (If there are any INVALID components, then check upgrade manual for fixes.)

o SQL> @utlul12s.sql

8. Run catuppst.sql to perform upgrade actions that do not require db to be in upgrade mode:

o SQL> $ORACLE_HOME/rdbms/admin/catuppst.sql

9. Run utlrp to recompile any remaining stored PL/SQL and other objects.

o SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql

10. Verify that all objects are valid:

o SQL> SELECT count(*) FROM dba_invalid_objects;

o SQL> SELECT distinct object_name FROM dba_invalid_objects;

You Can download these steps in the attachment.

Post Attachments

Upgrade Steps From 10g to 11gR2.pdf (213 views, 343.36 KB)

Posted 2/28/2011 4:30:43 PM

Post #545

maka42

I have 2 questions concerning the Upgrade:

1. in the beginning there is installed the Oracle 11 software including all patches.
After the migration I miss the execution of the post scripts belonging to the patches.
Are they executed automatically during the migration?
(For example the catcpu.sql)

2. in the pre activities there is a list of warnings and one of them is:

WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.

When I did my upgrade from Oracle 10.2.0.4 to Oracle 11.2.0.1, I got this warning as well, but I did not find the instructions for saving the EM data. Anyway they were not up to date because of a systemcopy, so I did not save them. Therefore at the end of the upgrade I got the message, that the Enterprise Manager Configuration was not successfully finished. But I do not know, how to "clean up" my Enterprise Manager Configuration. What must I do to reconfigure it in Oracle 11?

Thanks for your help!

Marlene

Posted 3/2/2011 9:54:42 AM

Post #548

Admin

Hi Marlene,

The upgrade script will run the catcpu so you don't have to run it again manually.

Regarding the repository, you can drop the sysman schema to clean it up. You can also use the repmanager to recreate it . It will ask you do you want to drop it if it already exists. Here is basic explaination of the steps:

During the installation process, if the SYSMAN schema already exists in the database that you specify for the Management Repository, the installer will prompt you to manually drop the schema. If this is not done, the installer will automatically drop the schema before proceeding to the next configuration assistant.

If the Oracle Management Service Configuration Assistant fails before completion, you can click Retry, which automatically cleans up the repository when the configuration tool is rerun. To manually clean up the repository, use the following command:

OMS_HOME/sysman/admin/emdrep/bin/RepManager <Host name> <Port> <SID> -ACTION Drop

You may need to set the LD_LIBRARY_PATH to the Management Service's ORACLE_HOME/lib directory before running the script.

Refer to Oracle Enterprise Manager Advanced Configuration for further instructions on how to drop the existing repository from the database. This should be specific to the version of Oracle document.

Hope this helps.

« Prev Topic | Next Topic »

Reading This Topic

Active Users: 1 (1 guest, 0 members, 0 anonymous members)

No members currently viewing this topic.

Forum Moderators: silencer

Permissions

All times are GMT -5:00, Time now is 6:13am

Contextual Links