Oracle Upgrade Database with Standby in Configuraton

-

Oracle Upgrade Database with Standby in Configuraton


With Oracle 12c database support coming to a EOS, It has become important for the DBAs to upgrade Oracle database to 19c. With Standby in configuration , DBA need to perform some addition steps. In this blog , We will show and shared the steps that need to be performed on the standby database in order to upgrade database with standby in configuration:

1) Check log Shipping Status 

select destination,status from v$archive_dest_status where dest_id <3;

NOTE: Make sure status is valid. 

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,’.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’
and
resetlogs_id in
( select max(resetlogs_id) from v$archived_log)
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
and
resetlogs_id in
( select max(resetlogs_id) from v$archived_log)
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
and
resetlogs_id in
( select max(resetlogs_id) from v$archived_log)
);

NOTE: Make sure there is no archive lag b/w primary and standby database.

2)  Disable Log Shipping on the Primary Database 

alter system set log_archive_dest_state_2='DEFER';

3)  On the Standby machine create a pfile if one does not exist 

SQL>  create pfile from spfile;

4) Shut down the standby database

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

5) Configure the listener with 19c Oracle Home

6)  Copy mandatory files from 12c home to 19c home.

Copy the password file  and init.ora file/spfile from the 12c Oracle Home/dba location  to 19c Oracle Home/dbs location

Copy tnsnames.ora,sqlnet.ora from 12c ORACLE_HOME/network/admin to 19c ORACLE_HOME/network/admin

7) Start Standby database with 19c Home

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size                  2182344 bytes
Variable Size             847250232 bytes
Database Buffers          176160768 bytes
Redo Buffers               18292736 bytes
SQL> alter database mount;
Database altered.

8) Start MRP Process on the standby database

SQL> recover managed standby database disconnect from session;
Media recovery complete.

9) Start log shipping again on Primary
SQL> alter system set log_archive_dest_state_2='ENABLE';
System altered.

10) Perform few log switched on the primary database and confirm if the archive logs are applied to Primary database.

SQL> alter system archive log current;

11) Proceed to upgrade the Primary database. Follow our Blog for Conventional and DBUA Silent method to perform the primary database upgrade.

12) During the upgrade you can check the alert log for Primary and standby database to make sure standby sync is in progress.

Alert Log File on the Primary Database:

Thread 1 advanced to log sequence 83 (LGWR switch)
  Current log# 2 seq# 83 mem# 0: /archive/REDO02-1.LOG
  Current log# 2 seq# 83 mem# 1: /archive/REDO02-2.LOG
Tue Jan 30 17:41:46 2022
LNS: Standby redo logfile selected for thread 1 sequence 83 for destination LOG_ARCHIVE_DEST_2
Tue Jan 30 17:41:52 2022
Archived Log entry 27569 added for thread 1 sequence 82 ID 0xb70f3f6c dest 1:
Tue Jan 30 17:42:51 2022

Alert Log File on the Standby database:

Media Recovery Waiting for thread 1 sequence 82 (in transit)
Tue Jan 30 17:41:55 2022
RFS[8]: Selected log 5 for thread 1 sequence 83 dbid -1385662071 branch 735920934
Tue Jan 30 17:41:59 2022
Archived Log entry 77 added for thread 1 sequence 82 ID 0xb70f3f6c dest 1:
Tue Jan 30 17:42:01 2022
Media Recovery Log /archive/testdb_0000000082_0735920934_0001.ARC

13) Post primary database upgrade ,Check Status archive log and make sure there is no archive log lag.

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,’.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’
and
resetlogs_id in
( select max(resetlogs_id) from v$archived_log)
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
and
resetlogs_id in
( select max(resetlogs_id) from v$archived_log)
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
and
resetlogs_id in
( select max(resetlogs_id) from v$archived_log)
);

14) Make Standby database in read only mode and check component status.

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.

SQL> col comp_name format a50
SQL> set linesize 120
SQL> select comp_name,version,status from dba_registry

Make sure all components are valid. 

15) Ask application team to perform application testing.

16) Change compatible parameter for both primary and standby database. 

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

Please check our other blogs for Upgrade, Dataguard ,Standby  & DR.






Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

-
Image
Restart Innodb MySQL Cluster after Complete outage(All node Down) In DBA World, Their could be activity(like DC Migration) where all node of an MySQL Innodb cluster need to brought down. In MySQL Innodb cluster, We need to follow below steps in order to bring up cluster. 1) Start all node in the MySQL Innodb cluster. systemctl start myql 2) Try to connect cluster using mysqlsh and check status. 3) Strat cluster using complete outage command. Restart Cluster using below command: var cluster = dba.rebootClusterFromCompleteOutage(); root@mysqlhost01:~# mysqlsh --uri clusteradmin@mysqlhost01:3306 Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************ Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.17 Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of...
Read more

Oracle Block Corruption - Detection and Resolution

-
Oracle Block Corruption - Detection and Resolution Any kind of corruption in database can  lead to stand still operation of the organization and can cause sever impact to organisation. In this blog , We are going to discuss about Block Corruption of the oracle database. Block Corruption :  Oracle Block Corruption occurs due inconsistent block Header ,Footer ,Checksum or Structure. Depending on the kind of insistency ,We can divide corruption into Physical and Logical corruption. Physical Corruption is due to Bad header, header or footer mismatch ,Invalid Block checksum & misplaced block. Usually this kind of the block corruption are due to external reason and difficult to resolve by oracle utilities alone.  Logical Corruption is due to corrupt or invalid Block structure when block checksum is valid. This kind of the Corruption can be resolved by the Oracle Utilities. Detection of Block Corruption Whenever a corrupt block is read Oracle reports an error in the alert l...
Read more

Add or Remove a node in MySQL Innodb Cluster

-
Add or Remove a node in MySQL Innodb Cluster In any database Cluster Solution ,it's regular activity to add or remove node from the cluster. In this blog , We will discuss about addition and removal of the cluster node from the MySQL Innodb cluster. Remove a node from the MySQL Innodb cluster . 1) Connect to primary node and check status. mysqlsh --uri clusteradmin@mysqlhost01:3306 MySQL  mysqlhost01:3306 ssl  JS > cluster.status() {     "clusterName": "mysqlcluster",     "defaultReplicaSet": {         "name": "default",         "primary": "mysqlhost01:3306",         "ssl": "REQUIRED",         "status": "OK",         "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",         "topology": {     ...
Read more