Describing pre/post checklist and upgrade process details for upgrading a Oracle 10R2 or 11.2.0.1 to Oracle 11gR2(11.2.0.2/3)
Please check OS version as upgrade is only certified for RH 5.
cat /etc/red*
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
**For 11.2.0.2.3, ensure RHEL is upgraded to 5.5 and above.
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
**For 11.2.0.2.3, ensure ASM is upgraded to 11.2.0.3. (Check dir $ORA_CRS_HOME then cd .. then pwd)
Please ensure that 11.2.0.3.x86_64 is pushed on the box and tnsnames.ora and listener.ora are updated accordingly.
Verify all ET standards
* Index Placement
* DB User profile assignment
Need to install Oracle 11g using runinstaller.
Phase 1: PRE UPGRADE TASKS
Phase 2: UPGRADING THE DATABASE TO ORACLE 11G R2
Phase 3: POST UPGRADE TASKS.
Phase 1 : Pre upgrade tasks
Step 1 : Create the directory
mkdir preupgrade_bkup
Step 2 : Disable all the below jobs :
– All batch and cron jobs.
– Backup
– Archive removal and rotation
– Application jobs
– Stats collection job
– standby job at standby server and make sure all arch has been applied at DR/STANDBY server side.
Step 3 : Run the gather stats job
a) Gathering System Stats
exec dbms_stats.gather_schema_stats(‘SYS’);
b) Gathering Dictionary Stats
exec dbms_stats.gather_dictionary_stats;
c) Gathering all fixed objects stats (dynamic performance tables)
exec dbms_stats.gather_fixed_objects_stats;
d) Gathering Database Stats
exec dbms_stats.gather_database_stats(options => ‘GATHER AUTO’);
e) Run the stats for all the Application schemas :
exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>’OWNER’, ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=>DBMS_STATS.AUTO_DEGREE, CASCADE=>TRUE, OPTIONS=>’GATHER STALE’);
exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>’OWNER’, ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=>DBMS_STATS.AUTO_DEGREE, CASCADE=>TRUE);
Step 4 : Capture Database link information :
spool create_dblink.info
SELECT
‘create ‘||DECODE(U.NAME,’PUBLIC’,’public ‘)||’database link ‘||CHR(10)
||DECODE(U.NAME,’PUBLIC’,Null, 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# ;
spool off
Step 5 : Create Pre-Upgrade BackupSET.
Run the database backup, be it RMAN (full not incremental) or hot backup whatever is configured in the environment.
Step 6: Verify Disk free space for archive mount point, we should have 15-20 gigs of space for arch (command to verify the space : df –h .)
Step 7 : Ensure no files need media recovery:
SQL> select * from v$recover_file;
no rows selected
*** Make sure currently no backup is running for the database, if yes then end backup using command.
SQL > Alter database end backup;
Database altered.
Step 7 : Verify DB version and installed options :
Ensure the properties for SYSTEM ,SYS user and database component status should be valid and default tablespace should be SYSTEM and SYSAUX respectively.
SQL> select username, default_tablespace from dba_users where username in (‘SYS’,’SYSTEM’);
=============================================================================================
SYS SYSTEM
SYSTEM SYSTEM
2 rows selected.
Database Components Information
select COMP_NAME, VERSION, status from dba_registry;
Database Patches Information
select ACTION_TIME, NAMESPACE, VERSION, COMMENTS, BUNDLE_SERIES from sys.registry$history Order by ACTION_TIME;
Database DST Information
col VERSION for 999
SELECT * FROM v$timezone_file;
Step 8: Purge the recyclebin :
PURGE DBA_RECYCLEBIN;
Step 9: Recompile invalid objects:
SQL>@?/rdbms/admin/utlrp.sql
Step 10 : Spool file before running upgrade tool :
SQL> spool pre_upgrade_database_details.log
SQL> @? /rdbms/admin/utlu112i.sql
SQL> spool off
Step 10: Stop the agent and listener :
emctl stop agent
lsnrctl stop <listener-name>
Step 11: Bring down the DB :
SQL>shutdown immediate;
Keeping in mind if the DB is an RAC change the CLUSTER_DATABASE=false
SQL> alter system set cluster_database = false scope = spfile;
Phase 2: Upgrading to Oracle Database 11g Release 2
[oracle]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle]$ export ORACLE_SID=orcl
[oracle]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
connected to the database sys as sysdba
sqlplus “/ as sysdba” –> will be connected to idle instance
Step 1 : Create pfile (change compatible to 11.2.0.3) and start the instance using new pfile :
SQL > startup nomount;
Step 2: Create passwordfile
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password={password} entries =80 force=y
Step 3 : Upgrade the DB, upgrd.sql will take around 30 minutes to complete
SQL> spool $ET_INSTANCE_ROOT/bkup/upgrade/upgrade_to_11203.log
SQL> startup upgrade
SQL> @?/rdbms/admin/catupgrd.sql
SQL> startup
*** Can check the spool file for any errors …
Phase III: Post-Install Oracle Database 11gR2
Step 1: Verify Upgrade Status:
SQL> @?/rdbms/admin/utlu112s.sql .
Oracle Database 11.2 Post-Upgrade Status Tool 01-28-2011 10:45:32 .
Component Status Version HH:MM:SS .
Oracle Server . VALID 11.2.0.3.0 00:10:43
Gathering Statistics . 00:00:40 Total Upgrade Time: 00:12:03
PL/SQL procedure successfully completed.
Steps 2: Need to verify the components after the upgrade :
SQL >select COMP_NAME, VERSION, status from dba_registry;
SQL>select ACTION_TIME, NAMESPACE, VERSION, COMMENTS, BUNDLE_SERIES from sys.registry$history Order by ACTION_TIME;
SQL>SELECT * FROM v$timezone_file;
Step 3: Need to upgrade the timezone to 14
SQL>conn / as sysdba
SQL>shutdown immediate;
SQL>startup upgrade;
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
SQL> col value format a10
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’ ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
—————————— ———-
DST_PRIMARY_TT_VERSION 11
DST_SECONDARY_TT_VERSION 7
DST_UPGRADE_STATE Upgrade
3 rows selected.
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS=’YES’;
no rows selected
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
An upgrade window has been successfully started.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’ ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE —————————— ———- DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE 3 rows selected.
SQL> select * from v$timezone_file;
FILENAME VERSION
——————– ———-
timezlrg_14.dat 14
Step 4: Verify invalid objects and compile them :
SQL>@?/rdbms/admin/utlrp.sql
Step 4: Start the database, agent, listner cleanly:
SQL> shutdown immediate
SQL> startup mount
SQL> alter system set cluster_database=TRUE scope=spfile; <— Only required for RAC
SQL> startup (If RAC, do it on both nodes)
lsnrctl start <listener-name>
Step 5: Run the gather statistics job again :
Step 6: Enable all the jobs which we commented before upgrade activity
Step 7 : Verify the database parameters.
Tales From A Lazy Fat DBA 