Upgrade Oracle database from 10g to 11g version

Oracle upgrade 10g to 11g version

Blog explain the manually upgrade process of Oracle database from version 10G to 11G

Steps of Up-gradation of Oracle database from 10g to 11g manually
Process divided into three section:
Preupgrade: Steps performed before upgrade of database.
Upgrade: Steps for performing database upgrade
Post upgrade: Steps performed after upgrade

Section 1 (Pre Upgrade)
Pre-upgrade Steps that do not require downtime and must be finished much before downtime starts:
1. Get ORACLE_HOME mount point /ora11g with 25GB space.
2. Install 11g software on the new mount point.
3. Make sure /etc/oraInst.loc is pointing to the correct inventory.

Recommendations for Source database

Step 1: Always check for DUPLICATE objects in SYS/SYSTEM


Note: 1030426.6 how to Clean up Duplicate objects Owned by SYS and SYSTEM Schema

Step 2: Run the Pre-Upgrade Information Tool for Collecting Pre-Upgrade Information

Step a. Log in to the system.
Run the Pre-Upgrade Information Tool (utlu112i.sql) from the Oracle Database 11g location as $ORACLE_HOME/rdbms/admin directory and try to execute on 10g database database as $ORACLE_HOME/rdbms/admin/utlu112i.sql

$ sqlplus '/ as sysdba'
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off

Check the spool file and examine the output of the upgrade information and take necessary actions like deprecated parameters, size in system tablespace, remove hidden parameter and gather system stats etc.

Step 3: Create Script for DBLINK for backup purpose.


Step 4: Check for time zone files

Select TZ_VERSION from registry$database;

Case 1. If the source database is using a time zone file older than version 11 i.e. outputs of the above query is less than 11 then proceed with the Step 6.

Case 2.If the source database is using a time zone file greater than version 11 (i.e. output of the above query is more than 11) then utlu112i.sql (Pre-Upgrade Information Tool) will generate the following warning.
Example: WARNING: –> Database is using a time zone file greater than version 11. BEFORE upgrading the database, patch the 11gR2
$ORACLE_HOME/oracore/zoneinfo/ with a timezone data file of the same version as the one used in the release database.In such case, do NOT proceed with upgrade. Contact your manager!

Step 5: Check that National Character set (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.

Select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

If this is UTF8 or AL16UTF16 then no action is needed.
If is not UTF8 or AL16UTF16 then refer the following article.
Note 276914.1 The National Character Set in Oracle 9i and 10g.

Step 6: Gather Dictionary Stats


Step 7: Disable Oracle Database Vault

select * from v$option where PARAMETER='Oracle Database Vault';
Output should be FALSE.
Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX

Step 8: Check for corruption in the dictionary, use the following commands:

Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';
spool off

This creates a script called analyze.sql.
Now execute the following steps.

sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
This script (analyze.sql) should not return any errors.

1. ORA-30657 might occur, if there is any external table is validated, which can be safely ignored as per Note 209355.1 ORA-30657: Using ANALYZE TABLE for an External Table
2. Errors shown below when executing analyze.sql can be ignored:
SP2-0734: unknown command beginning “SQL> SELEC…” – rest of line ignored.

Step 9: Ensure the users SYS and SYSTEM have ‘system’ as their default tablespace.
You must have sufficient space in the tablespace .

SQL> SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');

If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace to SYSTEM by using the below command:-

SQL> ALTER user SYS default tablespace SYSTEM;
SQL> ALTER user SYSTEM default tablespace SYSTEM;

Step 10: Take AWR’s backup before upgrade
In Oracle 10g/11g:@?/rdbms/admin/awrextr.sql
The above script ask DBID, Number of days for snap, DATA_PUMP Directory where export backup is created, Begin and end snap number for backup.

Step 11: Create a SQL Set and give the begin and end snap id

baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&being_snap, &end_snap,
DBMS_SQLTUNE.LOAD_SQLSET('SQL_PLAN_IN_10g', baseline_ref_cursor);

The values for being_snap and end_snap should cover a period of two days (48 hours)

Step 12: Always check for NON VALID components and try to remove unused component before Upgrade

SQL> select substr(COMP_ID, 1,10) compid, substr(COMP_NAME,1,24) compname, STATUS, VERSION from DBA_REGISTRY where STATUS = 'VALID';
This should return no rows.
Note: 753041.1 How to diagnose Components with NON VALID status

Step 13: Take backup of RMAN configuration
Connect with rman prompt

rman> Show all;

Step 14: Take stats backup for future performance issue. Follow the link for stats backup:
For Taking Stats Backup for Application User

Section 2 (Upgrade)
Steps to be performed at the time of downtime

Step 1: Take complete backup of the database
Step 2: Remove deprecated and hidden parameters from spfile or pfile.
Step 3: Ensure that all database components/objects are VALID in the source database

Spool status.log
Set lines 200 pages 200
Col owner for a10
Col object_name for a40
Select status,count(*) from dba_objects group by status order by 2 ;
Select owner,object_name,object_type from dba_objects where status’VALID’;
Spool off

Step 4: Disable all Cron jobs or Task scheduler jobs present related to database.
Step 5: Truncate AUD$ table.
If required take sys.aud$ table backup with exp utility.
-- truncate table sys.aud$;

Step 6: Ensure that if the aud$ table exists that it is in the sys schema and in the system tablespace.

SELECT owner,tablespace_name FROM dba_tables WHERE table_name='AUD$';
--If it is not in SYSTEM table space, then move the AUD$ table in system tablespace.
SQL> Alter table sys.aud$ move tablespace SYSTEM;

Step 7: Ensure that all snapshot refreshes are successfully completed, and that replication is stopped.

SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times;

Step 8: Ensure that no files need media recovery

SELECT * FROM v$recover_file;
This should return no rows.

Step 9: Ensure that no files are in backup mode

SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
This should return no rows.

Step 10: Resolve outstanding distributed transactions prior to the upgrade.

SQL> select * from dba_2pc_pending;
If this returns rows you should do the following:SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');

Step 11: Purge the recyclebin


Step 12: Create a GUARANTEED RESTORE POINT (This is required in case we need to fallback)
• In case of failure, flashback to the restore point
• In case of success: DON’T FORGET to delete it
Set parameters db_recovery_file_dest and db_recovery_file_dest_size.

Below are the commands:-
(prior to upgrade , in mount state)

Step 13: Make sure the following environment variables point to the Oracle 11g

$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

Step 14: Change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE--Set the system to spool for log file
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off

--Check the log file upgrade.log for any errors .
$ Cat upgrade.log | grep –i ORA- | sort | uniq –c

--Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log.
$ sqlplus "/as sysdba"
SQL> @utlu112s.sql

Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:

SQL> @catuppst.sql
This script can be run concurrently with utlrp.sql. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.

SQL> @utlrp.sql

Post Upgrade Steps

Step 1: Set the Environment Variables

---Modify /etc/oratab entry
For Instance,

Step 2: Edit init.ora:
Migrate your initialization parameter file to a serverparameter file.Create a server parameter file with an initialization parameter file

SQL> create spfile from pfile;

Step 3: Unset parameters
db_recovery_file_dest and db_recovery_file_dest_size .
Step 4: Drop the restore point created before up-gradation

SQL> DROP RESTORE POINT grpt; (after successful upgrade)

Step 5: Modify the listener.ora file and tnsnames.ora file

Step 6: Enable all Cron jobs.
Step 7: Move the AUD$ table back to its respective tablespace

SQL> Alter table sys.aud$ move tablespace ;

Step 8: Revert back any underscore parameter and events (which are relevant) which were removed in Section 2 – Step 2.

Step 9: Configured RMAN backup as default from show all backup

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.