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
select OBJECT_NAME, OBJECT_TYPE from DBA_OBJECTS where OBJECT_NAME||OBJECT_TYPE in (select OBJECT_NAME||OBJECT_TYPE from DBA_OBJECTS where OWNER='SYS') and OWNER='SYSTEM' and OBJECT_NAME not in ('AQ$_SCHEDULES_PRIMARY', 'AQ$_SCHEDULES','DBMS_REPCAT_AUTH');
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> 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.
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||''''||chr(10)||';' FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#;
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 18.104.22.168.0 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
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
AND (iot_type='IOT' OR iot_type is NULL)
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
This creates a script called analyze.sql.
Now execute the following steps.
This script (analyze.sql) should not return any errors.
sqlplus "/ as sysdba"
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
SQL> EXEC DBMS_SQLTUNE.CREATE_SQLSET('SQL_PLAN_IN_10g');
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&being_snap, &end_snap,
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
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’;
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
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
Step 11: Purge the recyclebin
SQL> PURGE DBA_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:-
SQL> CREATE RESTORE POINT grpt GUARANTEE FLASHBACK DATABASE;
(prior to upgrade , in mount state)
SQL> SELECT * FROM V$RESTORE_POINT;
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
$ export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
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> 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"
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:
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.
Post Upgrade Steps
Step 1: Set the Environment Variables
---Modify /etc/oratab entry
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