11G Migration Using Rolling Upgrade

October 12, 2016 by S4

Filed under Oracle

Last modified October 12, 2016

1      INTRODUCTION

1.1     Objective

Database upgrade and patch applying takes considerable amount of down time of production server.Oracle 11 G provides rolling upgrade method which allows up gradation /patch applying activities with no down time. This document explains migrating oracle 10G to 11G using data guard rolling upgrade method.

1.2     Scope

What is covered in this document.

  • Rolling update for 11g Migration
  • Switchover
  • Best practices

What is not covered in this document?

  • Detailed Stand by setup.
  • Database Installation/Instance setup.
  • Failover
  • Fallback scenarios
  • RAC installations

1      STANDARDS

1.1     Setup

The below table and figure explains initial data configuration setting.

 

Primary Stand By
IP XX.XX.XX.XX XX.XX.XX.YY
Database Name PRIM STAN
Database version 10.2 10.2
OS Windows XP Windows XP
ORACLE_HOME D:\10g\dbhome D:\10g\dbhome
oracle 11g migration

oracle 11g migration

1.1     Steps –High level

 

S.No Steps Details
1 Prepare for Rolling upgrade Setting up flash back area and restore point.
2 Stop SQL apply (Stand by) This process will stop redo log apply service and start accumulating log files.
3 Upgrade Stand By database Use DBUA to upgrade database version 10G to 11G
4 Restart SQL apply (Stand by) Restart SQL apply services on stand by so that accumulated logs would be applied on stand by to make sync with
5 Monitor Stand by Check DBA_LOGSTDBY_EVENTS to monitor log apply and take stand on fall back/or continue upgradation.Here we consider changes to be done on Unsupportted data types

 

6 Switch Over (Stand by to Primary) Once stand by database verified reverse the roles by changing current stand by database (STAN) as primary and primary database (PRIM) as  stand by databse.
7 Stop SQL apply (Primary) Redo log apply stopped in primary database to facilitate upgrade.Redologs started accumulating in current primary (STAN) database.
8 Upgrade primary database Use DBUA to upgrade database version 10G to 11G
9 Restart SQL apply (Primary) Restart SQL apply services on stand by so that accumulated logs would be applied on stand by to make sync with
10 Monitor current Stand by

(PRIM)

Check DBA_LOGSTDBY_EVENTS to monitor log apply and take stand on fall back/or continue upgradation.Here we consider changes to be done on Unsupportted data types

 

11 Raise compatible level Set compatable level for both databases
12 Perform Switch Over (Primary to Stand by) This switch over brings back original stand by configuration.

1.1     Steps –Detailed

1.1.1  Prepare for Rolling upgrade

 

To start with rolling upgrade both primary and standby should have flash back turned on.To turn flashback on .

 

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> ALTER DATABASE FLASHBACK

SQL> ALTER DATABASE OPEN

 

Since It’s Oracle 10.2 creating restore point. As we will not be discussing fall back scenario in this document no explanation provided about restore points. But creating restore point is one of the best practices during rolling upgrade.

 

SQL> CREATE RESTORE POINT PRE_UPG_11G GUARANTEE FLASHBACK DATABASE;

 

1.1.2   Stop SQL apply (Stand by)

 

Now issue alter database command to stop log apply services.

 

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY ;

 

 

1.1.3  Upgrade Stand By database (STAN)

oracle 11g migration

oracle 11g migration

Now stand by database ready to be upgraded.This could be done by running catupgrd.sql.But this requires some manual parameters setting .So we will be using DBUA to upgrde 10G to 11G.DBUA will take care of all parameter changes during upgrade.

 

1.1.1  Restart SQL apply (Stand by)

 

Now logical stand by apply needs to be started.

 

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE

 

 

Now log apply services will be started in stand by server.

1.1.2  Monitor Stand by (STAN)

Check DBA_LOGSTDBY_EVENTS to monitor log apply and take stand on fall back/or continue upgradation.Here we consider changes to be done on Unsupportted data types.

1.1.3  Switch Over (Stand by to Primary)

 

Now it’s time to switch over between primary and secondary system to upgrade primary database.

On primary.

 

Now query switch_over status from V$databse on primary to confirm switch over process

 

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

 

If above query may return’SESSIONS ACTIVE’ (OR)’TO STANDBY’ then issues

 

’TO STANDBY’

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

‘SESSIONS ACTIVE’

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY WITH SESSION SHUTDOWN;

 

On Standby

 

 

Now stand by needs to made as primary.

 

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

 

1.1.4  Stop SQL apply (Primary)

 

Now defer redo log apply.

PRIM@SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

 

1.1.1   Upgrade primary database

 

Now primary database ready for upgrade.

oracle 11g migration

oracle 11g migration

Use DBUA to upgrade primary database server by installing new ORACLE_HOME

 

Now upgrade the primary database.As a best practice create restore point before database upgrade.

 

 

SQL> CREATE RESTORE POINT PRE_UPG_11G GUARANTEE FLASHBACK DATABASE;

 

1.1.1  Restart SQL apply (Primary)

 

Now issue below alter system command to restart log apply services on primary.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

1.1.1  Monitor current Stand by

oracle 11g migration

oracle 11g migration

1.1.1  Raise compatible level

Now set the compatible parameter on both databases

 

PRIM@SQL> ALTER SYSTEM SET compatible=’11.1.0.7′ SCOPE=SPFILE;

STAN@SQL> ALTER SYSTEM SET compatible=’11.1.0.7′ SCOPE=SPFILE;

Alter the compatible parameter is irreversible operation.

Downgrading is not possible after this upgrade

 

1.1.2  Perform Switch Over (Primary to Stand by)

 

Now decide on switching back to original configuration on configuration before upgrade.Then another switch over is required for making STAN as stand by database.

Save

Leave a Comment