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 ;
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.... :)
Unable to open the below link.
ReplyDeletehttp://dbaocm.blogspot.in/2015/08/manual-database-upgrade-11203-to-11204.html