Tuesday, June 7, 2016

Upgrade Primary and Standby from 11.2.0.3 to 11.2.0.4

Upgrade Primary and Standby
from 11.2.0.3 to 11.2.0.4


Scenario:

Here in this demonstration, I am going to upgrade primary and standby database from 11.2.0.3 to 11.2.04 with broker.

Approach

  • ·         Before upgrade we have to ensure both db are in sync position
  • ·         Stop the MRP and Listener on standby database
  • ·         Copy the password file, sqlnet.ora, ldap.ora, listener.ora and tnsnames.ora to backup location for both primary and standby.
  • ·         Stop the Archive Shipping on Primary.
  • ·         Install 11.2.0.4 binary  on Primary and perform manual upgrade (I choose here) you can use via GUI Console for Upgrade and Start the database.
  • ·         Once Upgrade on primary, move to standby site and install software only in new home.
  • ·         Copy the password file, tnsname,listener,sqlnet to new home on both primary and standby
  • ·         Start standby database using new binary and put in mount mode
  • ·         Enable archive shipping on primary
  • ·         Put the Standby in MRP mode
  • ·         Reconfigure Broker for Primary and Standby



ON PRIMARY

Before upgrade primary and standby database, we have to make sure that both database primary and standby is in sync position.


NAME      INSTANCE_NAME    OPEN_MODE   DATABASE_ROLE    CURRENT_SCN
--------- ---------------- ----------  ----------------  -----------
PROD      PROD             READ WRITE  PRIMARY              1918182

SQL> select DEST_NAME,ERROR,STATUS, APPLIED_SCN, VERIFY
from v$archive_dest where rownum < 5 ;

DEST_NAME            ERROR       STATUS    APPLIED_SCN VER
------------------- ---------- --------- ----------- ---
LOG_ARCHIVE_DEST_1               VALID               0 NO
LOG_ARCHIVE_DEST_2               VALID         1918176 NO
LOG_ARCHIVE_DEST_3               INACTIVE            0 NO
LOG_ARCHIVE_DEST_4               INACTIVE            0 NO


SQL> Alter system switch logfile ;

System altered.

SQL> /

System altered.


Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) (Doc ID 556610.1)

Run the dbupgdiag script for pre upgrade task.

SQL> @dbupgdiag

Enter location for Spooled output:

Enter value for 1: /tmp

07_Jun_2016_0818          .log

PROD_

                          *** Start of LogFile ***

  Oracle Database Upgrade Diagnostic Utility       06-07-2016 08:18:25

===============
Hostname
===============

....
....

================
Component Status
================

Comp ID Component                          Status    Version        Org_Version    Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
AMD     OLAP Catalog                       VALID     11.2.0.3.0
APEX    Oracle Application Express         VALID     3.2.1.00.12
APS     OLAP Analytic Workspace            VALID     11.2.0.3.0
CATALOG Oracle Database Catalog Views      VALID     11.2.0.3.0
CATJAVA Oracle Database Java Packages      VALID     11.2.0.3.0
CATPROC Oracle Database Packages and Types VALID     11.2.0.3.0
CONTEXT Oracle Text                        VALID     11.2.0.3.0
EM      Oracle Enterprise Manager          VALID     11.2.0.3.0
EXF     Oracle Expression Filter           VALID     11.2.0.3.0
JAVAVM  JServer JAVA Virtual Machine       VALID     11.2.0.3.0
ORDIM   Oracle Multimedia                  VALID     11.2.0.3.0
OWB     OWB                                VALID     11.2.0.3.0
OWM     Oracle Workspace Manager           VALID     11.2.0.3.0
RUL     Oracle Rules Manager               VALID     11.2.0.3.0
SDO     Spatial                            VALID     11.2.0.3.0
XDB     Oracle XML Database                VALID     11.2.0.3.0
XML     Oracle XDK                         VALID     11.2.0.3.0
XOQ     Oracle OLAP API                    VALID     11.2.0.3.0

Validate the pre upgrade result before proceeding.

ON PRIMARY

Create guarantee restore point to avoid any issue in failure

SQL> CREATE RESTORE POINT BEFORE_UPGRADE_11204 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> select name,database_role,open_mode,flashback_on,log_mode from v$database;

NAME  DATABASE_ROLE    OPEN_MODE   FLASHBACK_ON       LOG_MODE
----- ---------------- ----------- ------------------ ------------
PROD  PRIMARY          READ WRITE  YES                ARCHIVELOG

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DB_INCR, GUARANTEE_FLASHBACK_DATABASE
FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';


NAME                   SCN        TIME                            DB_INCR    GUA    
---------------------  ---------- ------------------------------  -------    ----
BEFORE_UPGRADE_11204   1936507    07-JUN-16 08.45.37.000000000 AM       2    YES    


SQL> select * from V$FLASHBACK_DATABASE_LOGFILE;

NAME                             LOG#    THREAD#  SEQUENCE#  FIRST_CHANGE# FIRST_TIM TYPE
------------------------------ ------- ---------- ----------  ------------- --------- ---------
/u01/app/oracle/fast_recovery_      1          1         21        1686460 24-SEP-15 NORMAL
area/PROD/flashback/o1_mf_bzzz
qg7t_.flb

/u01/app/oracle/fast_recovery_      2          1         22        1714633 25-SEP-15 NORMAL
area/PROD/flashback/o1_mf_bzzz
qkj6_.flb

/u01/app/oracle/fast_recovery_      3          1         24        1805314 25-SEP-15 NORMAL
area/PROD/flashback/o1_mf_c00d
6yfs_.flb
/u01/app/oracle/fast_recovery_      4          1         28        1923719 07-JUN-16 NORMAL
area/PROD/flashback/o1_mf_c00n
yy39_.flb

/u01/app/oracle/fast_recovery_      5          1         29        1924857 07-JUN-16 NORMAL
area/PROD/flashback/o1_mf_c01k
32ld_.flb

/u01/app/oracle/fast_recovery_      6          1         30        1933739 07-JUN-16 NORMAL
area/PROD/flashback/o1_mf_c02j
tlkd_.flb

/u01/app/oracle/fast_recovery_      7          1         19        1593927 24-SEP-15 RESERVED
area/PROD/flashback/o1_mf_c02j
vtmb_.flb

/u01/app/oracle/fast_recovery_      8          1         20        1644745 24-SEP-15 NORMAL
area/PROD/flashback/o1_mf_c02n
7jn3_.flb

/u01/app/oracle/fast_recovery_      9          1         23        1759366 25-SEP-15 NORMAL
area/PROD/flashback/o1_mf_c03y
k3yl_.flb

/u01/app/oracle/fast_recovery_     10          1         25        1831906 26-SEP-15 NORMAL
area/PROD/flashback/o1_mf_c04f
obgg_.flb

/u01/app/oracle/fast_recovery_     11          1         26        1868589 26-SEP-15 NORMAL
area/PROD/flashback/o1_mf_c04j
0c1n_.flb
/u01/app/oracle/fast_recovery_     12          1         27        1916204 07-JUN-16 NORMAL
area/PROD/flashback/o1_mf_c051
rytm_.flb


In case of any failure upgrade follow below step to rollback to earlier position.

After upgrade is completed remove restore point.


SQL> DROP RESTORE POINT BEFORE_UPGRADE_11204;

Restore point dropped.


To rollback in case of failed upgrade on primary.   Use flashback to get back to original state.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE  TO RESTORE POINT BEFORE_UPGRADE_11204;

SQL> ALTER DATABASE OPEN RESETLOGS;



On Standby :

select PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS from v$managed_standby ;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      OPENING               1        186          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        186          1        943
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1        187         31          3
MRP0      APPLYING_LOG          1        187         33     204800


Stop Listener and MRP

vm225:STBY> lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 07-JUN-2016 08:01:15

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vm225.ora.com)(PORT=1521)))
The command completed successfully

SQL> conn / as sysdba

SQL> recover managed standby database cancel ;
Media recovery complete.


Backup the Listener and tnsnames to backup location for both primary and standby database.

ON PRIMARY

Start the Upgrade on Primary Database.

Please refer my blog for manual upgrade database from 11.2.0.3 to 11.2.0.4.

Before starting please install 11.2.0.4 software in separate home.


Once upgrade is completed verify the upgrade.



SQL> Select * from registry$history;

ACTION_TIME                      ACTION            VERSION             ID COMMENTS                  
-------------------------------- ---------------- ------------ --------- --------------------------
17-SEP-11 10.21.11.595816 AM     APPLY             11.2.0.3             0 Patchset 11.2.0.2.0       
21-SEP-15 05.34.00.711510 PM     APPLY             11.2.0.3             0 Patchset 11.2.0.2.0       
22-SEP-15 05.33.33.589114 PM     APPLY             11.2.0.3            14 PSU 11.2.0.3.14           
07-JUN-16 09.22.57.669866 AM     VIEW INVALIDATE                  8289601 view invalidation
07-JUN-16 09.22.57.734862 AM     UPGRADE           11.2.0.4.0             Upgraded from 11.2.0.3.0
07-JUN-16 09.23.53.755412 AM     APPLY             11.2.0.4             0 Patchset 11.2.0.2.0 



Do not enable archive shipping parameter (log_archive_dest_state_2) on primary.


ON STANDBY

Start the Upgrade Standby database.

·         Here we are only install the software (binary) for 11.2.0.4 on new home using runInstaller

·         Set the environment for newly installed binary

·         copy the password file, listener and tnsnames.ora file to 11.2.0.4 home.

·         Using new home start the database in mount mode.

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/DB_1
export ORACLE_SID=STBY

SQL> conn / as sysdba
SQL> startup mount


ON PRIMARY

Enable the archiving shipping on primary

SQL> alter system set log_archive_dest_state_2=enable ;

System altered.

SQL> select DEST_NAME,ERROR,STATUS, APPLIED_SCN, VERIFY
from v$archive_dest where rownum < 5;

DEST_NAME            ERROR       STATUS    APPLIED_SCN VER
------------------- ---------- --------- ----------- ---
LOG_ARCHIVE_DEST_1               VALID               0 NO
LOG_ARCHIVE_DEST_2               VALID               0 NO
LOG_ARCHIVE_DEST_3               INACTIVE            0 NO
LOG_ARCHIVE_DEST_4               INACTIVE            0 NO


SQL> alter system switch logfile ;

System altered.

SQL> /

System altered

ON STANDBY

vm225:STBY> sqlplus / as sysdba

SQL> select PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS
from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1        245          1        143
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        191      10240        710
ARCH      OPENING               1        191          0          0
RFS       CLOSING               1        219      88065        825
RFS       CLOSING               1        218      90113        342
RFS       CLOSING               1        220      96257       1496
RFS       WRITING               1        246         11          1

8 rows selected.


Enable managed recovery process (MRP)

SQL> recover managed standby database using current logfile disconnect from session  ;

Media recovery complete.

Verify the recovery on standby site

SQL> select PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS from v$managed_standby

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1        245          1        143
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        191      10240        710
ARCH      OPENING               1        191          0          0
RFS       IDLE                  0          0          0          0
RFS       CLOSING               1        243      81921       1383
RFS       CLOSING               1        242      83969        171
RFS       WRITING               1        246         81          1
MRP0      APPLYING_LOG          1        187      12192      97752

9 rows selected.


NAME      INSTANCE_NAME    OPEN_MODE   DATABASE_ROLE     CURRENT_SCN
--------- ---------------- ----------- ----------------  -----------
PROD      STBY             MOUNTED     PHYSICAL STANDBY     2077485


Configure Broker on Primary and Standby

Create broker configuration  or follow my existing blogs for recreating broker configuration.



vm224:PROD> dgmgrl sys/oracle@prod
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL>


DGMGRL> create configuration DG_PROD_NEW as PRIMARY DATABASE IS PROD connect identifier is 'PROD' ;
Configuration "dg_prod_new" created with primary database "prod"



Adding STBY database into broker


DGMGRL> ADD DATABASE STBY AS CONNECT IDENTIFIER IS 'STBY' maintained as PHYSICAL ;
Database "stby" added



Validating Configuration

DGMGRL> SHOW CONFIGURATION ;

Configuration - dg_prod_new

  Protection Mode: MaxPerformance
  Databases:
    prod - Primary database
    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Enable Configuration

DGMGRL> ENABLE CONFIGURATION ;
Enabled.

DGMGRL> SHOW CONFIGURATION

Configuration - dg_prod_new

  Protection Mode: MaxPerformance
  Databases:
    prod - Primary database
    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS



Validate Configuration


DGMGRL> SHOW CONFIGURATION VERBOSE

Configuration - dg_prod_new

  Protection Mode: MaxPerformance
  Databases:
    prod - Primary database
    stby - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


Now broker is re-configured and ready for use.



Hope this help.... :)





1 comment:

  1. Unable to open the below link.

    http://dbaocm.blogspot.in/2015/08/manual-database-upgrade-11203-to-11204.html

    ReplyDelete