Tales From A Lazy Fat DBA

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.