Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 129,480
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Database Upgrade (Oracle 10R2 or 11.2.0.1 to Oracle 11gR2(11.2.0.2/3)

Posted by FatDBA on April 19, 2013

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.

Advertisements

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 )

w

Connecting to %s

 
%d bloggers like this: