Oracle Certified Master's - Blogs: June 2016

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 CLOSING 1 186 1 943

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

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.

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

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.

Recreate Dataguard Broker Configuration on for Primary and Standby

http://dbaocm.blogspot.in/2016/06/recreate-dataguard-broker-configuration_7.html

vm224:PROD> dgmgrl sys/oracle@prod

DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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.... :)

Oracle Certified Master's - Blogs

Home

Tuesday, June 7, 2016

Upgrade Primary and Standby from 11.2.0.3 to 11.2.0.4