Upgrade Database with Physical Standby

Upgrade Database with Physical Standby 

How to Upgrade Database with Physical Standby

How do I upgrade or patch my physical standby database

Which steps are required for Physical Standby DB, when you want it to upgrade with Primary DB or you want to path the both Primary and Standby DB's

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

Those are the highlevel steps:

REFERENCE

===========

Oracle Reference Guide

Patching, Upgrading, and Downgrading Databases in an Oracle Data Guard Configuration

ACTION PLAN

=============

You can upgrade in TWO different ways

1. Using a Physical Standby database as your backup

== > Verify the Primary and Standby databases are in SYNC by comparing results of these 3 queries

Primary: SQL > alter system archive log current;

Primary: SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

PhyStdby:SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

PhyStdby:SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;

== > Standby: stop Managed Recovery == > SQL > recover managed standby database CANCEL

         select name, open_mode, database_role from v$database;

select process, status, sequence# from v$managed_standby;

         alter database recover managed standby database cancel;

== > Standby: shut down the database

== > Primary : DEFER log shipping

     alter system set log_archive_dest_state_2=DEFER SCOPE=BOTH;

== > Primary : shut down the database instance

== > Primary : change your environmental variables ==> Variables that point to the current ORACLE_HOME == > $ORACLE_HOME . $TNS_ADMIN as well all files you need to have in the new $OH

     tnsnames.ora

     listener.ora

     sqlnet.ora

     parameter file

     password file

== > Primary : bring up the database instance == > SQL > startup

== > Primary : execute post-patching steps

== > Primary : verify the process is SUCCESSFULLY completed

== > Standby: change your environmental variables

     tnsnames.ora

     listener.ora

     sqlnet.ora

     parameter file

     password file

 

== > Standby: mount the database instance == > SQL > startup mount

== > Standby: start Managed Recovery == > SQL > recover managed standby database                                                                                                                DISCONNECT

 == > online redolog apply via redologs     

alter database recover managed standby database using current logfile disconnect from session;              

== > logapply     via arcive log  

ALTER DATABASE RECOVER  managed standby database  parallel 10 disconnect;  

== > Primary : ENABLE log shipping

     alter system set log_archive_dest_state_2=enable scope=both;

== > Verify the Primary and Standby databases are in SYNC by comparing results of these 3 queries

+++++++++++++++++++

2. Patching the databases at the SAME TIME

== > Primary : DEFER log shipping

== > Standby: stop Managed Recovery == > SQL > recover managed standby database CANCEL

== > Standby: shut down the database

== > Standby: change your environmental variables

== > Standby: mount the database instance == > SQL > startup mount

== > Standby: start Managed Recovery == > SQL > recover managed standby database DISCONNECT

== > Primary : shut down the database instance

== > Primary : change your environmental variables

== > Primary : bring up the database instance == > SQL > startup

== > Primary : ENABLE log shipping

== > Primary : execute post-patching steps

== > Verify the Primary and Standby databases are in SYNC by comparing results of these 3 queries

+++++++++++++++

Of course, you shall have all necessary files under your new $ORCALE_HOME

To apply a patch you might need to execute ONE or TWO steps

== > Patch Oracle binaries < == On BOTH databases : Primary AND Standby

== > Execute post patching step(s) < == On the Primary ONLY

== > A Physical Standby can NOT be modified DIRECTLY == > Only by applying changes received from the Primary database via redo log files