Oracle Auto upgrade RAC with dataguard

 Upgrading RAC database from Oracle 11g (11.2.0.4) to Oracle 19c (19.9.0)

Practical upgrade RAC and dataguard environment :


>>>High-level steps of the AutoUpgrade process
 1  Take a full backup of the primary database.
 2  Enable the flashback feature in the primary and the physical standby database.
 3  Set the fast recovery area size to a higher value in both the primary and the physical standby database.
 4  Gather the dictionary statistics to the latest as this will help the database upgrade process to run faster.
 5  Purge the recyclebin as this will help us to save time during the database upgrade and in reducing the  downtime needed for the database upgrade.
 6  Ensure that the physical standby database is in sync with the primary database before starting the            database upgrade process.
 7  Create a GRP in the physical standby database.
 8  Disable the dataguard broker in both the primary and the physical standby database.
 9  Shutdown the physical standby database.
 10  Run the AutoUpgrade script in the ANALYZE mode in the primary database and fix any issues that     are  identified by the AutoUpgrade tool.
 11  Upgrade the primary database by running the AutoUpgrade script in the DEPLOY mode.
 12  Perform any post upgrade checks on the primary database.
 13  Upgrade the physical standby database with the migrate redo data that it receives from the primary database.
 14  Enable the dataguard broker and perform a final check on both the primary database and the physical standby database.
 15  Perform any post upgrade steps for the physical standby database.



scripts used :

scripts which we can use to check the primary database configuration and the physical standby database lag.

>>Script-1 : This script will pull the primary database details like, the mode and the role of the database.

cat /home/Oracle/rac_database_info.sql
set lines 200
col DATABASE_HOST for a30;
col HOST_NAME for a15;
col DATABASE_ROLE for a10
col OPEN_MODE for a10
col STARTUP_TIME for a20
SELECT i.HOST_NAME "DATABASE_HOST", i.INSTANCE_NAME "DB_NAME", d.DATABASE_ROLE " DATABASE_ROLE", d.OPEN_MODE "OPEN_MODE", STARTUP_TIME
from GV$DATABASE d, gv$instance i
where i.INST_ID=d.INST_ID;

Script-2: This script will check the current lag on the physical standby database and display the physical standby database role and mode info as well.

 cat  /home/Oracle/standby_database_lag.sql
set lines 200
col DATABASE_HOST for a30;
col HOST_NAME for a15;
col DATABASE_ROLE for a10
col OPEN_MODE for a10
col STARTUP_TIME for a20
SELECT i.HOST_NAME "DATABASE_HOST", i.INSTANCE_NAME "DB_NAME", d.DATABASE_ROLE " DATABASE_ROLE", d.OPEN_MODE " OPEN_MODE ", STARTUP_TIME from GV$DATABASE d, gv$instance i  where i.INST_ID=d.INST_ID;
select inst_id,process, status, thread#, sequence#, block#, blocks
from gv$managed_standby
where process='MRP0';
select a.thread#, (select max (sequence#)
from v$archived_log  where archived='YES' and thread#=a.thread#) archived, max(a.sequence#) applied,  (select max(sequence#)
from v$archived_log
where archived='YES' and thread#=a.thread#)-max(a.sequence#)gap from v$archived_log a where a.applied='YES' group by a.thread# order by thread#;

  • configuration of primary database and physical standby database
we have the primary database, which is a two-node RAC database and a physical standby database which is also a two-node RAC database. 

The primary database environment is (11.2.0.4) database patched with the April 2020 Patch Set Update ( PSU ).


ENVIORNMENT   DETAILS
=======================
The primary database name is vicedbpr and the following are its RAC instances:

SQL> @/home/Oracle/rac_database_info.sql
DATABASE_HOST   DB_NAME      DATABASE_ROLE   OPEN_MODE   STARTUP_TIME
--------------  ----------   --------------  ----------- ------------
chapvm-19crac1  vicedbpr1    PRIMARY         READ WRITE  15-FEB-23
chapvm-19crac2  vicedbpr2    PRIMARY         READ WRITE  15-FEB-23

The physical standby database environment

The physical standby database name is vicedbdr and the following are its RAC instances:
SQL> @/home/Oracle/rac_database_info.sql
DATABASE_HOST      DB_NAME    DATABASE_ROLE      OPEN_MODE  STARTUP_TIME
-----------------  ---------- ----------------   ---------  ------------
chapstdby-19crac1  vicedbdr1  PHYSICAL STANDBY   MOUNTED    15-FEB-23
chapstdby-19crac2  vicedbdr2  PHYSICAL STANDBY   MOUNTED    15-FEB-23

>>Install Oracle 19c binaries and install (software only) and apply patch

The target primary database binary version and its patch set is as follows:

Cluster Nodes  : chapvm-19crac1
chapvm-19crac2
OS version  : Oracle Enterprise Linux 7.1 64 bit
Oracle Home  : /u01/app/oracle/product/19c/db_1
Database Version    : 19.3.0.0 with October 2020 Database Bundle Patch
Grid Version  : 19.9.0



The target physical standby database binary version and its patch

Cluster Nodes  : chapstdby-19crac1
chapstdby-19crac2
OS version  : Oracle Enterprise Linux 7.1 64 bit
Oracle Home  : /u01/app/oracle/product/19c/db_1
Database Version      : 19.3.0.0 with October 2020 Database Bundle Patch
Grid Version   : 19.9.0

Pre-requisites 

1. Take full backup
2. enable flashback in primary source db and physical db
3 check space in fra (For GRP during upgrade)
4  Gather Stats
5 purge recyclibin ( purge dba_recyclebin; )

6 hidden parameters 

Script-1
select name,value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

Script-2

select ' ALTER SYSTEM RESET "' || name || '" scope=spfile sid=' || '''*'';' from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

Script -3 

select ' ALTER SYSTEM SET "' || name || '"=' || value|| ' scope=spfile sid=' || '''*'';' from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\'
order by name;


Ensure physical standby database is in sync with the primary database

SQL> @/home/Oracle/rac_database_info.sql

sample result:

DATABASE_HOST    DB_NAME     DATABASE_ROLE   OPEN_MODE    STARTUP_TIME
--------------   ----------  --------------  ------------ ------------
chapvm-19crac1   vicedbpr1   PRIMARY         READ WRITE   15-FEB-23
chapvm-19crac2   vicedbpr2   PRIMARY         READ WRITE   15-FEB-23

>>alter system switch all logfile;

>> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence       21
Next log sequence to archive   22
Current log sequence               22


IMPORTANT :

Creating Guaranteed Restore Point (GRP) in the physical standby database

If we need to downgrade the db using the flashback method, we have to create the guaranteed restore points for this purpose. In the primary database, the AutoUpgrade process will create the guaranteed restore point before it starts the database upgrade, but in the physical standby database, we need to create the guaranteed restore point manually.


>>>Dataguard 
On the primary database, defer the log shipping

>>alter system set log_archive_dest_state_2='DEFER' scope=both sid='*';

On the physical standby database, cancel the MRP process 

>> Alter database recover managed standby database cancel;

>>>---->>>
Once the MRP is cancelled, we can create the guaranteed restore point


>> create restore point standby_before_19c_upgrade guarantee flashback database;


we will enable the log shipping back in the primary and start the MRP in the physical standby database

1.On the primary database, enable the log shipping.

SQL> alter system set log_archive_dest_state_2='ENABLE' scope=both sid='*';


2 .On the physical standby database, start the MRP process

SQL> alter database recover managed standby database disconnect from session;


Upgrading Oracle 11g (11.2.0.4) database to 19c (19.9.0)

In the primary database, switch the logfiles and take a snapshot of the current archive

>>Standby sync check:
SQL> @/home/Oracle/rac_database_info.sql
DATABASE_HOST   DB_NAME     DATABASE_ROLE   OPEN_MODE   STARTUP_TIME
--------------  ---------   --------------  ----------  ------------  
chapvm-19crac1  vicedbpr1   PRIMARY         READ WRITE  15-FEB-21
chapvm-19crac2  vicedbpr2   PRIMARY         READ WRITE  15-FEB-21

SQL> alter system switch all logfile;
System altered.

 SQL>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     50
Next log sequence to archive   51
Current log sequence           51

Check to see whether the physical standby database is receiving the redo logs from the primary database and whether the MRP process is applying the logs to keep it in sync with the primary database.

SQL> @/home/Oracle/standby_database_lag.sql

DATABASE_HOST      DB_NAME    DATABASE_ROLE     OPEN_MODE   STARTUP_TIME
-----------------  --------   ----------------  ----------  -----------
chapstdby-19crac1  vicedbdr1  PHYSICAL STANDBY  MOUNTED     15-FEB-23
chapstdby-19crac2  vicedbdr2  PHYSICAL STANDBY  MOUNTED     15-FEB-23
INST_ID   PROCESS    STATUS       THREAD#   SEQUENCE#  BLOCK#  BLOCKS
--------- ---------  ------------ --------  ---------- ------- -------
1         MRP0      APPLYING_LOG      1    51         54      102400
THREAD#    ARCHIVED    APPLIED    GAP
---------- ----------  ---------- ----------
1          50          49          1
2          42          42          0

>>if databroker is configured 
Disable the dataguard broker configuration


DGMGRL> DISABLE CONFIGURATION;


Stop the data guard broker (DMON) process in the primary database and standby 

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

SQL> show parameter DG_BROKER_START
NAME                       TYPE        VALUE
---------------------      ----------  -----------------------------
dg_broker_config_file1       string      +DATA/vicedbpr/dr1vicedbpr.dat
dg_broker_config_file2       string      +DATA/vicedbpr/dr2vicedbpr.dat
dg_broker_start                    boolean     FALSE


Network files
1  Add the source database TNS entry in the tnsnames.ora file of Oracle 19c (19.9.0) database home.

 2  Take a backup of the current tnsnames.ora file in Oracle 19c (19.9.0) database home, as follows:[oracle@chapvm-19crac1 admin]$ cd /u01/app/oracle/product/19c/db_1/network/admin
[oracle@chapvm-19crac1 admin]$ cp tnsnames.ora tnsnames.ora.Feb15
 
3  Add the primary and physical standby database entries to Oracle 19c (19.9.0) tnsnames.ora file

[oracle@chapvm-19crac1 admin]$ cd /u01/app/oracle/product/19c/db_1/network/admin

[oracle@chapvm-19crac1 admin]$ vi tnsnames.ora

vicedbpr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chapvm-19crac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = vicedbpr)
)
)

vicedbdr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chapstdby-19crac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = vicedbdr)
)
)

>>>>
Copy the primary database initialization parameter files ( pfile and spfile ) from Oracle 11g (11.2.0.4) to Oracle 19c (19.9.0) database home,

>>>
The password file is in the dbs folder; we can copy it to the Oracle 19c (19.9.0) dbs folder

Check the INVALID objects on primary 


============Download Autoupgrade tool============

Run create config command >>


 $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config


>>>[oracle@chapvm-19crac1 vicedbpr]$ cat vicedbpr_19c.cfg
# Global configurations
global.autoupg_log_dir=/u01/software/upgrade/vicedbpr
upg1.log_dir=/u01/software/upgrade/vicedbpr
upg1.sid=vicedbpr1
upg1.source_home=/u01/app/Oracle/product/11.2.0.4/db_1
upg1.target_home=/u01/app/Oracle/product/19c/db_1
upg1.start_time=NOW                            # Start time
upg1.upgrade_node=chapvm-19crac1.localdomain   # Local node
upg1.run_utlrp=yes                                      # For Utlrp
upg1.timezone_upg=yes                          # Timezone upgrade
upg1.target_version=19                         # Target database version upg1.defer_standby_log_shipping=yes            # AutoUpgrade can defer the log-shipping to configured standby databases



Running AutoUpgrade in the Analyze mode


$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config vicedbpr_19c.cfg -mode ANALYZE


>>>SAMPLE RESULT
AutoUpgrade tool launched with default options
Processing config file …
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg>
upj> Job 100 completed
------------------- Final Summary --------------------
Number of databases           [1]
Jobs finished                  [1]
Jobs failed                   [0]
Jobs pending                  [0]
Please check the summary report at:
/u01/software/upgrade/vicedbpr/cfgtoollogs/upgrade/auto/status/status.html
/u01/software/upgrade/vicedbpr/cfgtoollogs/upgrade/auto/status/status.log 

 open status.log and check vicedbpr_preupgrade.log 

please resolve all REQUIRED ACTIONS and RECOMMENDED ACTIONS.


>>>> Shutdown physical standby database
 srvctl status  database -d vicedbdr


Upgrade the primary database using AutoUpgrade

 $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config vicedbpr_19c.cfg -mode DEPLOY


SAMPLE RESULT

Processing config file …
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands

upg> lsj
+----+---------+-----+---------+-------+--------------+--------+-------+
|Job#|  DB_NAME|STAGE|OPERATION| STATUS|    START_TIME| UPDATED|MESSAGE|
+----+---------+-----+---------+-------+--------------+--------+-------+
| 101|vicedbpr1|  GRP|EXECUTING|RUNNING|21/02/16 08:32|08:32:13|       |
+----+---------+-----+---------+-------+--------------+--------+-------+
Total jobs 1


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

While the AutoUpgrade process is running, we can open a new terminal and check the different directories that the AutoUpgrade tool creates for the various logs.

Here, directory
/u01/software/upgrade/vicedbpr/vicedbpr1/100 was created when we ran AutoUpgrade with the ANALYZE mode, whereas, the directory
/u01/software/upgrade/vicedbpr/vicedbpr1/101 was created when we ran AutoUpgrade with the DEPLOY mode.


 tail -f autoupgrade_20210216_user.log

sample result
2021-02-16 08:37:10.681 INFO Begin Upgrade on Database [vicedbpr]
2021-02-16 08:37:16.005 INFO 0%Upgraded
2021-02-16 08:40:16.341 INFO 8%Upgraded
2021-02-16 08:43:16.548 INFO 21%Upgraded
2021-02-16 08:46:16.986 INFO 39%Upgraded
2021-02-16 08:49:17.240 INFO 49%Upgraded
2021-02-16 08:52:17.597 INFO 49%Upgraded
2021-02-16 08:55:18.010 INFO 75%Upgraded
2021-02-16 08:58:18.249 INFO 91%Upgraded
2021-02-16 09:01:18.849 INFO 91%Upgraded
2021-02-16 09:04:19.032 INFO 92%Upgraded
2021-02-16 09:06:31.241 INFO SUCCESSFULLY UPGRADED

db upgrade done 

If this is the RAC database, we can check if the database is up in all the nodes. If it's not up in the other nodes of the cluster, we can start the database in all the nodes.


>>>
Upgrading the physical standby database to Oracle 19c (19.9.0)

The physical standby database upgrade will happen with the migrating redo logs that it receives from the primary database. As of now, the physical standby database is down. Let's upgrade its configuration file first and then start the database using the server control utility.

pre-checks

  • copy the password files/parameter files and the database tnsnames.ora entry from Oracle 11g to Oracle 19c home on  all the nodes.


  • Modify the database entry in /etc/oratab to point to the Oracle 19c (19.3.0) home.

  • Upgrade the cluster configuration information of the physical standby database using the srvctl upgrade command

>>
[oracle@chapstdby-19crac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19c/db_1
[oracle@chapstdby-19crac1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@chapstdby-19crac1 ~]$ srvctl upgrade database -d vicedbdr -o /u01/app/oracle/product/19c/db_1
[oracle@chapstdby-19crac1 ~]$ srvctl config database -db vicedbdr

>>
  • Start the physical standby database in the MOUNT mode.

>>
  • Enable the log shipping on the primary database

SQL> ALTER SYSTEM SET log_archive_dest_state_2=enable SCOPE=BOTH sid='*';


  • Start the MRP in the physical standby database

SQL> ALTER DATABASE RECOVER managed standby database using current logfile disconnect;

  • wait for some time for the standby database to catchup with the primar
  • Stop the database and start it by using the srvctl command.
  • Start the MRP and check the lag in the standby database

upgradation should complete now .

if databroker is required please configure .

Comments

Post a Comment

Popular posts from this blog

Image
  AutoUpgrade utility: The AutoUpgrade tool is a new upgrade utility which allows you to upgrade your Oracle databases with minimal manual intervention. The AutoUpgrade tool is designed in such a way that when it’s run, it not only identifies the issues before the database upgrade but also fixes them, and then performs the database upgrade and runs any post upgrade steps.  The AutoUpgrade tool can upgrade both the single instances and the cluster databases. It also supports both the non-container database(non-cdb) and thecontainer database(cdb) upgrades. The AutoUpgrade tool supports upgrading a non-cdb database to apluggable database(pdb) using one single command, and also supports all the operating systems where we can have the Oracle databases configured, and it upgrades multiple databases in one single operation. It is used only to upgrade the Oracle database NOT GRID  Support of the AutoUpgrade utility The AutoUpgrade tool can be used to upgrade the Oracle databases ...
Read more