Step by step Upgrade from 10gR2 to 11gR2

Upgrade 10.2.0.5[64Bit] to 11gR2[64Bit]
Steps	Details
Install Oracle 11gR2 in new Oracle Home	make sure there are no Linking-Error Install Latest Patchset available [11.2.0.2]
Take a Cold backup of source Database
Copy all database file to appropriate folder	Create directories for the following:Datafile Control file Redolog Diag files Archive
Copy utlu 112i.sql from ORACLE_HOME/rdbms/admin and execute in source database.	Check the spool file and examine the output of the upgrade$ sqlplus ‘/ as sysdba’ SQL> spool upgrade_info.log SQL> @utlu112i.sql SQL> spool off SQL>  Depending upon the spool file change initialization parameter, deprecated and obsolete parameters
Run Check the Integrity of the source databasedbupgdiag.sql	If script reports any invalid objects, run utlrp.sql to validate invalid objectSQL> @ORACLE_HOME/rdbms/admin/utlrp.sql After vaklidating invalid object Re-run dbupgdiag.sql and make sure everything is fine.
Deprecated CONNECT Role	This query can be used to identify ehich users and roles are granted the CONNECT PrivlegeSELECT grantee FROM dba_role_privs WHERE granted_role = ‘CONNECT’ and grantee NOT IN ( ‘SYS’, ‘OUTLN’, ‘SYSTEM’, ‘CTXSYS’, ‘DBSNMP’, ‘LOGSTDBY_ADMINISTRATOR’, ‘ORDSYS’, ‘ORDPLUGINS’, ‘OEM_MONITOR’, ‘WKSYS’, ‘WKPROXY’, ‘WK_TEST’, ‘WKUSER’, ‘MDSYS’, ‘LBACSYS’, ‘DMSYS’, ‘WMSYS’, ‘EXFSYS’, ‘SYSMAN’, ‘MDDATA’, ‘SI_INFORMTN_SCHEMA’, ‘XDB’, ‘ODM’);
In case database to be downgraded, create a script for DBLINK	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)||’;’ TEXT FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#;
Check for TIMESTAMP with TIMEZONE datatype	select TZ_VERSION from registry$database;
Check that National Characterset (NLS_NCHAR_CHARACTERSET)	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
Check Schemas with Stale Statistics	$ sqlplus ”/as sysdba”SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
Check for logical corruption	Set verify off Set space 0 Set line 120 Set heading off Set feedback off Set pages 1000 Spool analyze.sqlSELECT ‘Analyze cluster “‘||cluster_name||’” validate structure cascade;’ FROM dba_clusters WHERE owner=’SYS’ UNION 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) UNION SELECT ‘Analyze table “‘||table_name||’” validate structure cascade into invalid_rows;’ FROM dba_tables WHERE owner=’SYS’ AND partitioned=’YES’;spool off  This Will create ascript called analyze.sql. Now execute this $ sqlplus “/ as sysdba” SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql SQL> @analyze.sql
Ensure that all snapshot refreshes are successfully completed, and that replication is stopped.	SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times;
Ensure that no files need media recovery	Query v$recover_file;SELECT * FROM v$recover_file; It should return no rows
Ensure that no files are  in backup mode	SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;This should return no rows
Resolve outstanding distributed transactions prior to the upgrade.	SQL> select * from dba_2pc_pending;incase it returns rows you should do the following: SQL> SELECT local_tran_id FROM dba_2pc_pending; SQL> EXECUTE dbms_transaction.purge_lost_db_entry(”); SQL> COMMIT;
To check if a standby database exists, issue the following query:	SELECT SUBSTR(value,INSTR(value,’=',INSTR(UPPER(value),’SERVICE’))+1) FROM v$parameter WHERE name LIKE ‘log_archive_dest%’ AND UPPER(value) LIKE ‘SERVICE%’;If this query returns a row, then sync the standby database with the Primary database. 1. Make sure all the logs are transported to the standby server after a final log switch in the primary. 2. Start the recovery of the standby database with the NODELAY option.
Disable all batch and cron jobs.
Ensure the users sys and system have ‘system’ as their default tablespace. You must have sufficient space in the tablespace or be set to extents unlimited.	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;
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 the AUD$ table is not in SYSTEM tablespace and not owned by the SYS user then before doing the upgrade put is back of the SYSTEM tablespace and it should be owned by SYS .
Check whether database has any externally authenticated SSL users.	SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = ‘GLOBAL’;If Exist Do this steps after Upgrade1.Make sure all following Env Variables are pointing to 11g - ORACLE_BASE - ORACLE_HOME - PATH Check oratab file also.  2. vi /etc/oratab SID:ORACLE_HOME:Y
Check the location of datafile, controlfile and redo logs. Also take a backup of all configuration files like listener.ora tnsnames.ora	$ cp listener.ora  /backup_ora $ cp tnsnames.ora  /backup_oraSQL> SELECT name FROM v$controlfile; SQL> SELECT file_name FROM dba_data_files; SQL> SELECT group#, member FROM v$logfile;.
Stop the listener for the database, dbconsole and isqlplus	$ lsnrctl stop $ emctl stop dbconsole $ isqlplusctl stop
Shutdown the database.	$ sqlplus “/as sysdba” SQL> shutdown immediate;Tak e a cold backup or RMAN backupConnect to RMAN:  rman “target / nocatalog” RUN { ALLOCATE CHANNEL chan_name TYPE DISK; BACKUP DATABASE FORMAT ‘<db_backup_directory>%U’ TAG before_upgrade; BACKUP CURRENT CONTROLFILE TO ‘<controlfile_backup_directory>’; } sql> Shutdown immediate
Edit init<sid>.ora file	Comment out any obsoleted parameters – Preupgrade Check log -Change all deprecated parameters – Preupgrade Check log -Verify that the parameter DB_DOMAIN is set properly The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.
Check for enough freespace on archive log destination file systems.	Note that if your database is in archivelog mode, then it is always desirable and advisable to upgrade the database in noarchivelog mode as that will reduce the time taken to upgrade the database. After the upgrade you can again put the database in the archivelog mode.
Upgrade Database	$ cd $ORACLE_HOME/rdbms/admin $ sqlplus “/ as sysdba” Use startup eith upgrade option SQL> startup UPGRADE
Run upgrade script	SQL> set echo on SQL> SPOOL upgrade.log SQL> @catupgrd.sql SQL> spool off
Run post upgrade tool, which provides the summary of the upgrade	$ORACLE_HOME/rdbms/admin/utlu112s.sql$ sqlplus “/as sysdba” SQL> STARTUP SQL> @utlu112s.sql  SQL> select comp_name, status, version from dba_registry;
Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions	SQL> @catuppst.sqlrun utlrp to recompile any invalid objects SQL> @utlrp.sql
Get the diagnostic information of the upgraded database	Download and run dbupgdiag.sqlsql> @dbupgdiag.sql If it shows any invalid object run utlrp.sql to validate any invalid  onjects
Post upgrade steps	Create the listener $ netca $ lsnrctl start
Upgrade the timezone file	Please refer the article Note 977512.1  to upgrade the tiemzone
Upgrade Statistics Tables Created by the DBMS_STATS Package	EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘SYS’,'dictstattab’);
Upgrade Externally Authenticated SSL Users	ORACLE_HOME/rdbms/bin/extusrupgrade –dbconnectstring <hostname:port_no:sid> –dbuser <db admin> –dbuserpassword <password> -a
Edit init.ora	If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE - Migrate your initialization parameter file to a server parameter file.SQL> create spfile from pfile;
Upgrade OCR key for the database	use srvconfig srvconfig -upgrade db_name -orahome pre_11g_oracle_homerun srvctl from old oracle_home % $ORACLE_HOME/bin/srvctl remove database -d db_nameFrom 11g Oracle_Home:  % $ORACLE_HOME/bin/srvctl add database -d db_name -o <location of 11g home> % $ORACLE_HOME/bin/srvctl add instance -d db_name -i instance -n node
Shut down the database	sqlplus ‘/ as sysdba’ shutdown
Startup database	sqlplus ‘/as sysdba’ Startup

Advertisement