Infrastructure at your Service

Marc Wagner

How to migrate High Availability databases on an ODA?

By September 25, 2020 Oracle One Comment

Through this blog, I would like to show, with a real customer case, how I have been migrating databases in High Availability environment on an ODA. High Availability using Data Guard for Oracle Enterprise Edition or dbvisit for Oracle Standard Edition 2. One of the advantage here would be to use the Standby Database as fallback in case there would be some application issue with new database version. Although, I would expect that for any productive database, customer would have a similar test database on which he would perform a good testing.

In this procedure I have been upgrading database named DB1 from 11.2.0.4 to 12.1.0.2 version. The primary server is named ODA-PRI (running DB1_RZA database) and the standby server is named ODA-STD (running DB1_RZB database).

Steps

To perform an upgrade in High Availability environment, there would be 16 steps :

  1. Have a good database backup.
  2. Create new 12.1.0.2 dbhomes (or use existing one) on the primary and the standby ODA.
  3. Stop the application.
  4. Make sure the standby database is synchronized with the primary database.
  5. Stop the synchronization between the primary and the standby database.
  6. Stop the standby database.
  7. Upgrade the primary database to 12.1.0.2.
  8. Start the application.
  9. Test the application.
    • In case there is a critical issue failover to the standby database and create again old primary as new standby database.
    • If all is ok, have the standby database upgraded as well by doing the next steps.
  10. Move the standby database to the new home.
  11. Upgrade the standby database in the clusterware.
  12. Set compatible parameter to 12.1.0 on the standby database.
  13. Start the standby database.
  14. Update the registry metadata on the ODA.
  15. Start the log shippment again from the primary to the standby. The standby database will be upgraded through the archive logs.
  16. Test a switchover.

Note :

  1. It is recommended to take an inc0 backup once the primary database has been upgraded.
  2. Usually we would keep the compatible parameter to the previous release and after a few weeks, change it on the standby first and then the primary. This to allow a possible downgrade of the databases. In my case, customer wanted to immediately have the full 12.1 conditions, and did not intend to restart the database later on. Also there was no intention for a possible downgrade as we would have been using the standby database as a fallback.

1- Check database backup

On our customer environment we are using our DMK Management Kit which is very easy to use and powerfully to administrate the database.
I have then checked dmk_dbbackup logs to ensure I have a good backup.

[email protected]:/home/oracle/ [DB1] cda
 
[email protected]:/u01/app/oracle/admin/DB1/ [DB1] cd log
 
[email protected]:/u01/app/oracle/admin/DB1/log/ [DB1] ls -ltrh *inc0*
-rw-r--r-- 1 oracle oinstall 34K Jun 21 21:05 DB1_bck_inc0_no_arc_del_20200621_200002.log
-rw-r--r-- 1 oracle oinstall 33K Jul 1 21:03 DB1_bck_inc0_no_arc_del_20200701_200002.log
-rw-r--r-- 1 oracle oinstall 34K Aug 1 21:06 DB1_bck_inc0_no_arc_del_20200801_200002.log
-rw-r--r-- 1 oracle oinstall 35K Aug 10 08:43 DB1_bck_inc0_no_arc_del_20200810_075712.log
 
[email protected]:/u01/app/oracle/admin/DB1/log/ [DB1] tail DB1_bck_inc0_no_arc_del_20200810_075712.log
 
Recovery Manager complete.
 
RMAN return Code: 0
 
#**************************************************************************************************#
# END OF: DB1_bck_inc0_no_arc_del_20200810_075712.log #
#--------------------------------------------------------------------------------------------------#
# timestamp: 2020-08-10_08:43:52 #
#**************************************************************************************************#
[email protected]:/u01/app/oracle/admin/DB1/log/ [DB1]

2- Create new 12.1.0.2 dbhomes (or use existing one) on the primary and the standby ODA

This steps might be similar for the primary and standby ODA. Make sure to use the same version of PSU on both your ODA.

I have been creating a new Oracle Home version 12.1.0.2.

Checking dbhomes, I do not have any 12.1 running on my ODAs :
[[email protected] ~]# odacli list-dbhomes
 
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
d6df9457-e4cd-4c39-b3cb-8d03be3c4598 OraDB11204_home1 11.2.0.4.190115 /u01/app/oracle/product/11.2.0.4/dbhome_1 Configured
3d92c8ea-2ad5-4565-acd5-1d931cf22b15 OraDB11204_home3 11.2.0.4.190115 /u01/app/oracle/product/11.2.0.4/dbhome_3 Configured
9d2d92d0-3b98-42ac-9f39-9bd6deeb2e16 OraDB19000_home1 19.7.0.0.200414 /u01/app/oracle/product/19.0.0.0/dbhome_1 Configured

I’m running ODA version 19.7 :
[[email protected] ~]# odacli describe-component
System Version
---------------
19.7.0.0.0
 
Component Installed Version Available Version
---------------------------------------- -------------------- --------------------
OAK 19.7.0.0.0 up-to-date
GI 19.7.0.0.200414 up-to-date
DB {
[ OraDB11204_home1,OraDB11204_home3 ] 11.2.0.4.190115 11.2.0.4.200414
[ OraDB19000_home1 ] 19.7.0.0.200414 up-to-date
}
DCSAGENT 19.7.0.0.0 up-to-date
ILOM 4.0.4.52.r133103 up-to-date
BIOS 41060700 up-to-date
OS 7.8 up-to-date
FIRMWARECONTROLLER QDV1RF30 up-to-date
FIRMWAREDISK 0121 up-to-date
HMP 2.4.5.0.1 up-to-date

I have downloaded Patch p23494992 from My Oracle Support and extracted it locally :
[[email protected] patchs]# ls -ltrh
total 5.8G
-rw-r--r-- 1 root root 5.8G Jun 19 09:54 12.1.0.2_p23494992_197000_Linux-x86-64.zip
 
[[email protected] patchs]# unzip 12.1.0.2_p23494992_197000_Linux-x86-64.zip
Archive: 12.1.0.2_p23494992_197000_Linux-x86-64.zip
extracting: odacli-dcs-19.7.0.0.0-200423-DB-12.1.0.2.zip
inflating: README.txt
 
[[email protected] patchs]# ls -ltrh
total 12G
-rw-r--r-- 1 root root 5.8G Apr 23 20:10 odacli-dcs-19.7.0.0.0-200423-DB-12.1.0.2.zip
-rw-r--r-- 1 root root 252 May 23 20:55 README.txt
-rw-r--r-- 1 root root 5.8G Jun 19 09:54 12.1.0.2_p23494992_197000_Linux-x86-64.zip

I updated the ODA repository with new DB clone :
[[email protected] patchs]# odacli update-repository -f /tmp/patchs/odacli-dcs-19.7.0.0.0-200423-DB-12.1.0.2.zip
{
"jobId" : "a9677ae9-93af-43eb-b738-4a74f9686573",
"status" : "Created",
"message" : "/tmp/patchs/odacli-dcs-19.7.0.0.0-200423-DB-12.1.0.2.zip",
"reports" : [ ],
"createTimestamp" : "August 10, 2020 09:51:41 AM CEST",
"resourceList" : [ ],
"description" : "Repository Update",
"updatedTime" : "August 10, 2020 09:51:41 AM CEST"
}
 
[[email protected] patchs]# odacli describe-job -i "a9677ae9-93af-43eb-b738-4a74f9686573"
 
Job details
----------------------------------------------------------------
ID: a9677ae9-93af-43eb-b738-4a74f9686573
Description: Repository Update
Status: Success
Created: August 10, 2020 9:51:41 AM CEST
Message: /tmp/patchs/odacli-dcs-19.7.0.0.0-200423-DB-12.1.0.2.zip
 
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------

I have then created new needed dbhome (on this customer we are using Standard Edition database with dbvisit software) :
[[email protected] ~]# odacli create-dbhome -de SE -v 12.1.0.2.200414
 
Job details
----------------------------------------------------------------
ID: cb35718a-3aeb-4979-acc3-d352b1f541e4
Description: Database Home OraDB12102_home1 creation with version :12.1.0.2.200414
Status: Created
Created: August 10, 2020 9:57:22 AM CEST
Message: Create Database Home
 
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
 
[[email protected] ~]# odacli describe-job -i cb35718a-3aeb-4979-acc3-d352b1f541e4
 
Job details
----------------------------------------------------------------
ID: cb35718a-3aeb-4979-acc3-d352b1f541e4
Description: Database Home OraDB12102_home1 creation with version :12.1.0.2.200414
Status: Success
Created: August 10, 2020 9:57:22 AM CEST
Message: Create Database Home
 
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Setting up ssh equivalance August 10, 2020 9:57:23 AM CEST August 10, 2020 9:57:23 AM CEST Success
Validating dbHome available space August 10, 2020 9:57:23 AM CEST August 10, 2020 9:57:23 AM CEST Success
Creating DbHome Directory August 10, 2020 9:57:23 AM CEST August 10, 2020 9:57:23 AM CEST Success
Extract DB clones August 10, 2020 9:57:23 AM CEST August 10, 2020 9:59:25 AM CEST Success
Clone Db home August 10, 2020 9:59:25 AM CEST August 10, 2020 10:01:19 AM CEST Success
Enable DB options August 10, 2020 10:01:19 AM CEST August 10, 2020 10:01:30 AM CEST Success
Run Root DB scripts August 10, 2020 10:01:30 AM CEST August 10, 2020 10:01:30 AM CEST Success
Removing ssh keys August 10, 2020 10:01:39 AM CEST August 10, 2020 10:01:39 AM CEST Success

Following steps is mandatory if using our DMK Management Kit. We need to create a dummy for the new database home :
[email protected]:/home/oracle/ [rdbms11204_1] cdd
[email protected]:/u01/app/oracle/local/dmk/ [rdbms11204_1] cd etc
[email protected]:/u01/app/oracle/local/dmk/etc/ [rdbms11204_1] cp -p dmk.oratab dmk.oratab.20200810_1003
[email protected]:/u01/app/oracle/local/dmk/etc/ [rdbms11204_1] vi dmk.oratab
[email protected]:/u01/app/oracle/local/dmk/etc/ [rdbms11204_1] diff dmk.oratab dmk.oratab.20200810_1003
4d3
---rdbms12102_1:/u01/app/oracle/product/12.1.0.2/dbhome_1:D
[email protected]:/u01/app/oracle/local/dmk/etc/ [rdbms11204_1]

Just a tips : if you are using passwordless SSH authentication for the oracle user, you will have to create the keys again as any odacli create or delete command will remove them.

Check new Oracle database home :
[[email protected] ~]# odacli list-dbhomes
 
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
d6df9457-e4cd-4c39-b3cb-8d03be3c4598 OraDB11204_home1 11.2.0.4.190115 /u01/app/oracle/product/11.2.0.4/dbhome_1 Configured
9d2d92d0-3b98-42ac-9f39-9bd6deeb2e16 OraDB19000_home1 19.7.0.0.200414 /u01/app/oracle/product/19.0.0.0/dbhome_1 Configured
73847823-ae83-4bf0-a630-f8884cf4387a OraDB12102_home1 12.1.0.2.200414 /u01/app/oracle/product/12.1.0.2/dbhome_1 Configured

3- Stop the application

Before performing next upgrade steps, it is now time to have the customer stopping the application. You might have your own script to check running session, if not you can use below queries :
SQL> set linesize 300
SQL> set pagesize 500
SQL> col machine format a20
SQL> col service_name format a20
SQL> select SID, serial#, username, machine, process, program, status, service_name, logon_time from v$session where username not in ('SYS', 'PUBLIC') and username is not null order by status, username;

To kill inactive old sessions, you can use below PL/SQL commands :
BEGIN
for s in (select SID, serial#, username, machine, process, program, status, service_name, logon_time from v$session where username not in ('SYS', 'PUBLIC') and username is not null and status='INACTIVE' order by status, username)
loop
dbms_output.put_line ('Session : ' || s.sid || ',' ||s.serial#);
dbms_output.put_line ('');
execute immediate 'alter system kill session ''' || s.sid || ',' ||s.serial# || ''' immediate';
end loop;
end;
/

4- Make sure the standby database is synchronized with the primary database

The High Availability solution will depend if you are running Enterprise Edition or Standard Edition.
I will consider that you are using Data Guard for Enterprise Edition and dbvisit solution for Standard Edition.

High availability with Data Guard

To make sure the standby is synchronized, use show database command on the standby database :
DGMGRL> show configuration
 
Configuration - DB1
 
Protection Mode: MaxAvailability
Members:
DB1_RZA - Primary database
DB1_RZB - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS (status updated 19 seconds ago)
 
 
DGMGRL> show database DB1_RZB
 
Database - DB1_RZB
 
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 4.00 KByte/s
Real Time Query: OFF
Instance(s):
DB1
 
Database Status:
SUCCESS

What is mandatory is to have no transport lag and no apply lag :
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)

High availability with dbvisit

Use dbvctl command with option -i to ensure there is no gap. DB1 would be the name of my DDC configuration file :
[email protected]:/home/oracle/ [DB1] /u01/app/dbvisit/standby/dbvctl -d DB1 -i
=============================================================
Dbvisit Standby Database Technology (9.0.02_0_gbd40c486) (pid 91009)
dbvctl started on ODA-PRI: Mon Aug 10 09:29:17 2020
=============================================================
 
Dbvisit Standby log gap report for DB1_RZA at 202008100929:
-------------------------------------------------------------
Description | SCN | Timestamp
-------------------------------------------------------------
Source 13139995191 2020-08-10:09:29:20 +02:00
Destination 13139994931 2020-08-10:09:27:37 +02:00
 
Standby database time lag (DAYS-HH:MI:SS): +00:01:43
 
Report for Thread 1
-------------------
SOURCE
Current Sequence 52863
Last Archived Sequence 52862
Last Transferred Sequence 52862
Last Transferred Timestamp 2020-08-10 09:27:42
 
DESTINATION
Recovery Sequence 52863
 
Transfer Log Gap 0
Apply Log Gap 0
 
=============================================================
dbvctl ended on ODA-PRI: Mon Aug 10 09:29:25 2020
=============================================================

Mandatory is to have no gap :
Transfer Log Gap 0
Apply Log Gap 0

If there is any gap, same command can be run on the primary to ship the archive log and on the standby to apply them :
[email protected]|STD:/home/oracle/ [DB1] /u01/app/dbvisit/standby/dbvctl -d DB1

5- Stop the synchronization between the primary and the standby database

You will need to stop the synchronization of the databases to isolate the standby from any new change.

High availability with Data Guard

In case FSFO is used, you will need to disable it :
DGMGRL> disable fast_start failover;
Disabled.

You will need to set the protection mode to maxperformance :
DGMGRL> edit configuration set protection mode as maxperformance;
Succeeded.

You will need to stop applying the change vector on the standby :
DGMGRL> edit database DB1_RZB set state=apply-off;
Succeeded.

You will need to stop shipping the change vector on the primary :
DGMGRL> edit database DB1_RZA set state=transport-off;
Succeeded.

High availability with dbvisit

Archive log shippment and apply is done through the linux crontab. Purpose is then to deactivate it on both the primary and the standby database using crontab -e command.

On the primary :
#00,10,20,30,40,50 * * * * /u01/app/dbvisit/standby/dbvctl -d DB1 >/tmp/dbvisit_apply_logs_DB1.log 2>&1

On the standby :
#05,15,25,35,45,55 * * * * /u01/app/dbvisit/standby/dbvctl -d DB1 >/tmp/dbvisit_apply_logs_DB1.log 2>&1

6- Stop the standby database

[email protected]:/home/oracle/ [DB1] DB1
********* dbi services Ltd. *********
STATUS : MOUNTED
DB_UNIQUE_NAME : DB1_RZB
OPEN_MODE : MOUNTED
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PHYSICAL STANDBY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
*************************************
 
[email protected]:/home/oracle/ [DB1] srvctl stop database -d DB1_RZB
 
[email protected]:/home/oracle/ [DB1] DB1
********* dbi services Ltd. *********
STATUS : STOPPED
*************************************
[email protected]:/home/oracle/ [DB1]

7- Upgrade the primary database to 12.1.0.2

7a- Prerequirements

I first checked if the existing database had any invalid object :
SQL> set lines 300
 
SQL> col status format a20
 
SQL> col comp_name format a40
 
SQL> select comp_name, status from dba_registry;
 
COMP_NAME STATUS
---------------------------------------- --------------------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Workspace Manager VALID
 
SQL> SELECT count(*) FROM dba_invalid_objects;
 
COUNT(*)
----------
0

I executed preupgrd.sql script :
[email protected]:/home/oracle/ [DB1] echo $ORACLE_SID
DB1
 
[email protected]:/home/oracle/ [DB1] echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/dbhome_1
 
[email protected]:/home/oracle/ [DB1] ls -lthr /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/pre*
-rw-r--r-- 1 oracle oinstall 14K May 15 2014 /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/preupgrd.sql
 
[email protected]:/home/oracle/ [DB1] sqh
 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 10 10:29:32 2020
 
Copyright (c) 1982, 2013, Oracle. All rights reserved.
 
 
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters option
 
SQL> @/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/preupgrd.sql
...
...
...

I checked preupgrade log :
[email protected]:/home/oracle/ [DB1] more /u01/app/oracle/cfgtoollogs/DB1_RZA/preupgrade/preupgrade.log
Oracle Database Pre-Upgrade Information Tool 08-10-2020 10:29:46
Script Version: 12.1.0.2.0 Build: 015
**********************************************************************
Database Name: DB1
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
Compatible: 11.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone file: V31
Database log mode: ARCHIVELOG
**********************************************************************
[Update parameters] [No parameters to update] ...
...
...

I executed the preupgrade_fixup.sql script :
SQL> @/u01/app/oracle/cfgtoollogs/DB1_RZA/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2020-08-10 10:29:45 Version: 12.1.0.2 Build: 015
Beginning Pre-Upgrade Fixups...
Executing in container DB1
 
**********************************************************************
Check Tag: NEW_TIME_ZONES_EXIST
Check Summary: Check for use of newer timezone data file
Fix Summary: Time zone data file must be updated in the new ORACLE_HOME.
**********************************************************************
Fixup Returned Information:
ERROR: --> New Timezone File in use
 
Database is using a time zone file newer than version 18.
BEFORE upgrading the database, patch the new
ORACLE_HOME/oracore/zoneinfo/ with a time zone data file of the
same version as the one used in the 11.2.0.4.0 release database.
**********************************************************************
 
 
**********************************************************************
Check Tag: PURGE_RECYCLEBIN
Check Summary: Check that recycle bin is empty prior to upgrade
Fix Summary: The recycle bin will be purged.
**********************************************************************
Fixup Succeeded
**********************************************************************
 
 
**********************************************************************
[Pre-Upgrade Recommendations] **********************************************************************
 
*****************************************
********* Dictionary Statistics *********
*****************************************
 
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
 
^^^ MANUAL ACTION SUGGESTED ^^^
 
 
*****************************************
*********** Hidden Parameters ***********
*****************************************
 
Please review and remove any unnecessary hidden/underscore parameters prior
to upgrading. It is strongly recommended that these be removed before upgrade
unless your application vendors and/or Oracle Support state differently.
Changes will need to be made in the init.ora or spfile.
 
******** Existing Hidden Parameters ********
 
_datafile_write_errors_crash_instance = FALSE
_db_writer_coalesce_area_size = 16777216
_disable_interface_checking = TRUE
_enable_NUMA_support = FALSE
_file_size_increase_increment = 2143289344
_gc_policy_time = 0
_gc_undo_affinity = FALSE
_ktb_debug_flags = 8
 
^^^ MANUAL ACTION SUGGESTED ^^^
 
 
*****************************************
************ Existing Events ************
*****************************************
 
Please review and remove any unnecessary events prior to upgrading.
It is strongly recommended that these be removed before upgrade unless
your application vendors and/or Oracle Support state differently.
Changes will need to be made in the init.ora or spfile.
 
******** Existing Events ********
 
 
 
^^^ MANUAL ACTION SUGGESTED ^^^
 
 
**************************************************
************* Fixup Summary ************
 
1 fixup routine was successful.
0 fixup routines returned INFORMATIONAL text that should be reviewed.
1 ERROR LEVEL check returned INFORMATION that must be acted on prior to upgrade.
 
************************************************************
====>> USER ACTION REQUIRED <<====
************************************************************
 
1) Check Tag: NEW_TIME_ZONES_EXIST failed.
Check Summary: Check for use of newer timezone data file
Fixup Summary:
"Time zone data file must be updated in the new ORACLE_HOME."
^^^ MANUAL ACTION REQUIRED ^^^
 
**************************************************
You MUST resolve the above error prior to upgrade
**************************************************
 
 
**************** Pre-Upgrade Fixup Script Complete *********************
 
PL/SQL procedure successfully completed.

The hidden parameters are ODA specific and can be ignored.

I manually purge the recycle_bin :
SQL> EXECUTE dbms_preup.purge_recyclebin_fixup;
 
PL/SQL procedure successfully completed.
 
SQL> PURGE DBA_RECYCLEBIN;
 
DBA Recyclebin purged.
 
SQL>

I gathered dictionary statistics :
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
 
PL/SQL procedure successfully completed.

I found out, thanks to MOS community article, that there was a bug for the time zone requirements :
Bug 17303129 : UPGRADE DATABASE FROM 11.1.0.7 TO 12.1.0.1, “OLDER TIMEZONE IN USE” OCCURRED
and that I did not have to downgrade my time zone :
SQL> select version FROM v$timezone_file;
 
VERSION
----------
31
 
SQL> select TZ_VERSION from registry$database;
 
TZ_VERSION
----------
31

I removed OLAP component :
SQL> @?/olap/admin/catnoamd.sql
...
...
...
drop type olapsys.olap_sys_aw_access_obj
*
ERROR at line 1:
ORA-01435: user does not exist
...
...
...

and could check that OLAP is installed but not used :
SQL> col c1 heading 'OLAP|Installed' format a20
SQL> select decode(count(*), 0, 'No', 'Yes') c1 from v$option where parameter = 'OLAP';
 
OLAP
Installed
--------------------
Yes
 
SQL> col c1 heading 'OLAP|Used' format a20
SQL> select decode(count(*), 0, 'No', 'Yes') c1 from dba_feature_usage_statistics where name like '%OLAP%' and first_usage_date is not null;
 
OLAP
Used
--------------------
No

7b- Upgrade the primary database to 12.1.0.2 using odacli upgrade-database

As it should normally be done I wanted to upgrade the database using odacli upgrade-database.
[[email protected] ~]# odacli list-databases
 
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
d897e7d6-9e2d-45e4-a0d7-a1e232d47f16 DB1 Si 11.2.0.4.190115 false Oltp Odb1 Acfs Configured d6df9457-e4cd-4c39-b3cb-8d03be3c4598
 
[[email protected] ~]# odacli list-dbhomes
 
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
d6df9457-e4cd-4c39-b3cb-8d03be3c4598 OraDB11204_home1 11.2.0.4.190115 /u01/app/oracle/product/11.2.0.4/dbhome_1 Configured
9d2d92d0-3b98-42ac-9f39-9bd6deeb2e16 OraDB19000_home1 19.7.0.0.200414 /u01/app/oracle/product/19.0.0.0/dbhome_1 Configured
73847823-ae83-4bf0-a630-f8884cf4387a OraDB12102_home1 12.1.0.2.200414 /u01/app/oracle/product/12.1.0.2/dbhome_1 Configured
 
[[email protected] ~]# odacli upgrade-database -i d897e7d6-9e2d-45e4-a0d7-a1e232d47f16 -from d6df9457-e4cd-4c39-b3cb-8d03be3c4598 -to 73847823-ae83-4bf0-a630-f8884cf4387a
{
"jobId" : "7c6580a2-3646-4a93-8fcc-582a6f297562",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "August 10, 2020 12:01:44 PM CEST",
"resourceList" : [ ],
"description" : "Database service upgrade with db ids: [d897e7d6-9e2d-45e4-a0d7-a1e232d47f16]",
"updatedTime" : "August 10, 2020 12:01:44 PM CEST"
}
 
[[email protected] upgrade1]# odacli describe-job -i "7c6580a2-3646-4a93-8fcc-582a6f297562"
 
Job details
----------------------------------------------------------------
ID: 7c6580a2-3646-4a93-8fcc-582a6f297562
Description: Database service upgrade with db ids: [d897e7d6-9e2d-45e4-a0d7-a1e232d47f16] Status: Failure
Created: August 10, 2020 12:01:44 PM CEST
Message: DCS-10001:Internal error encountered: Databases failed to upgrade are : [d897e7d6-9e2d-45e4-a0d7-a1e232d47f16].
 
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Database Service Upgradation August 10, 2020 12:01:44 PM CEST August 10, 2020 12:05:31 PM CEST Failure
Database Service Upgradation August 10, 2020 12:01:44 PM CEST August 10, 2020 12:05:31 PM CEST Failure
Setting up ssh equivalance August 10, 2020 12:01:45 PM CEST August 10, 2020 12:01:45 PM CEST Success
Run catnoamd.sql August 10, 2020 12:01:45 PM CEST August 10, 2020 12:01:46 PM CEST Success
Database Upgrade August 10, 2020 12:01:46 PM CEST August 10, 2020 12:05:30 PM CEST Success
Deleting and creating the spfile and pfile August 10, 2020 12:05:30 PM CEST August 10, 2020 12:05:31 PM CEST Success
Database Upgrade Validation August 10, 2020 12:05:31 PM CEST August 10, 2020 12:05:31 PM CEST Failure

But upgrade could not be done successfully using odacli upgrade-database command. I have been investigating the logs and tried several times before deciding not to lose more time and to run a manual upgrade. There was no other solution, the maintenance windows was going on.

7c- Upgrade the primary database to 12.1.0.2 manually

Upgrading the database manually would cover below steps :

  1. Switching database to new home
  2. Updating oratab
  3. Start database in upgrade mode
  4. Run upgrade
  5. Upgrade database configuration in oracle clusterware
  6. Startup the database
  7. Run catuppst.sql
  8. Check dba registry and invalid objects
  9. Execute utlrp.sql
  10. Run postupgrade_fixups.sql
  11. Gather statistics
  12. Run post upgrade status tool
  13. Check dba registry and invalid objects
  14. Solve invalid objects
  15. Upgrade time zone
  16. Check data patch
  17. Set compatible parameter value to 12.1.0
  18. Restart database with grid
  19. Update registry metadata
  20. Update network (tnsnames.ora and listener.ora for statistcs entries)
Switching database to new home

Stop database :
[email protected]:/home/oracle/ [DB1] DB1
********* dbi services Ltd. *********
STATUS : OPEN
DB_UNIQUE_NAME : DB1_RZA
OPEN_MODE : READ WRITE
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 11.2.0.4.0
*************************************
 
[email protected]:/home/oracle/ [DB1] srvctl stop database -d DB1_RZA
 
[email protected]:/home/oracle/ [DB1] DB1
********* dbi services Ltd. *********
STATUS : STOPPED
*************************************

Move spfile and password file from 11.2.0.4 home to 12.1.0.2 home :
[email protected]:/home/oracle/ [DB1] cdh
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/ [DB1] cd dbs
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] cp -p initDB1.ora /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] cp -p orapwDB1 /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] mv initDB1.ora initDB1.ora.off
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] mv orapwDB1 orapwDB1.off

Updating oratab

[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] cp -p /etc/oratab ~/oratab.20200810_1243
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] vio
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] diff /etc/oratab ~/oratab.20200810_1243
33c33
DB1:/u01/app/oracle/product/11.2.0.4/dbhome_1:N # line added by Agent
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] dmk

Start database in upgrade mode

Trying to start the database in upgrade mode I got following error :
ORA-00723: Initialization parameter COMPATIBLE must be explicitly set
Surprising, but it seems that the compatible parameter was not initally set for this database. I had to set it to 11g :
SQL> show parameter compatible
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.0.0
noncdb_compatible boolean FALSE
 
SQL> show spparameter compatible
 
SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* compatible string
* noncdb_compatible boolean
 
SQL> alter system set compatible='11.2.0.0.0' scope=spfile;
 
System altered.

Starting the database in upgrade mode was then successful :
SQL> startup upgrade
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
 
Total System Global Area 4.5097E+10 bytes
Fixed Size 2936480 bytes
Variable Size 6710886752 bytes
Database Buffers 3.8252E+10 bytes
Redo Buffers 131280896 bytes
Database mounted.
Database opened.

Run upgrade

[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_1/ [DB1] cd $ORACLE_HOME/rdbms/admin
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/ [DB1] $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l $ORACLE_HOME/diagnostics catupgrd.sql
 
Argument list for [catctl.pl] SQL Process Count n = 4
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = /u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = 0
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
 
catctl.pl version: 12.1.0.2.0
Oracle Base = /u01/app/oracle
 
Analyzing file catupgrd.sql
Log files in /u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics
catcon: ALL catcon-related output will be written to /u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics/catupgrd_catcon_10444.lst
catcon: See /u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics/catupgrd*.log files for output generated by scripts
catcon: See /u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics/catupgrd_*.lst files for spool files, if any
Number of Cpus = 4
SQL Process Count = 4
 
------------------------------------------------------
Phases [0-73] Start Time:[2020_08_10 12:59:54] ------------------------------------------------------
Serial Phase #: 0 Files: 1 Time: 33s
Serial Phase #: 1 Files: 5 Time: 17s
Restart Phase #: 2 Files: 1 Time: 1s
Parallel Phase #: 3 Files: 18 Time: 3s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 8s
...
...
...
Serial Phase #:71 Files: 1 Time: 0s
Serial Phase #:72 Files: 1 Time: 0s
Serial Phase #:73 Files: 1 Time: 23s
 
------------------------------------------------------
Phases [0-73] End Time:[2020_08_10 13:09:13] ------------------------------------------------------
...
...
...

Upgrade database configuration in oracle clusterware

Clusterware needs to be updated to have database configured on new oracle home :
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics/ [DB1] srvctl upgrade database -db DB1_RZA -oraclehome /u01/app/oracle/product/12.1.0.2/dbhome_1
 
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics/ [DB1] srvctl config database -db DB1_RZA
Database unique name: DB1_RZA
Database name: DB1
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile: /u02/app/oracle/oradata/DB1_RZA/dbs/spfileDB1.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths: /u02/app/oracle/oradata/DB1_RZA,/u03/app/oracle/
Services:
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: DB1
Configured nodes: oda-pri
Database is administrator managed

/etc/oratab should be checked to ensure it’s reflect the same.

Startup the database

[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics/ [DB1] DB1
********* dbi services Ltd. *********
STATUS : STOPPED
*************************************
 
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics/ [DB1] srvctl start database -d DB1_RZA
 
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics/ [DB1] DB1
********* dbi services Ltd. *********
STATUS : OPEN
DB_UNIQUE_NAME : DB1_RZA
OPEN_MODE : READ WRITE
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 12.1.0.2.0
CDB Enabled : NO
*************************************

Run catuppst.sql

[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics/ [DB1] echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0.2/dbhome_1
 
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_1/diagnostics/ [DB1] sqh
 
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 10 13:27:33 2020
 
Copyright (c) 1982, 2014, Oracle. All rights reserved.
 
Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
With the Real Application Clusters option
 
SQL> @?/rdbms/admin/catuppst.sql
...
...
...
PL/SQL procedure successfully completed.
 
 
PL/SQL procedure successfully completed.
 
 
PL/SQL procedure successfully completed.
 
 
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2020-08-10 13:27:44
 
 
Session altered.
 
SQL>

Check dba registry and invalid objects

SQL> set line 300
SQL> col comp_name format a50
 
SQL> select comp_name, status from dba_registry;
 
COMP_NAME STATUS
-------------------------------------------------- -----------
Oracle Database Catalog Views UPGRADED
Oracle Database Packages and Types UPGRADED
Oracle Workspace Manager VALID
Oracle XML Database VALID
 
SQL> SELECT count(*) FROM dba_invalid_objects;
 
COUNT(*)
----------
7768

Execute utlrp.sql

Execute utlrp.sql to recompile all objects :
SQL> @?/rdbms/admin/utlrp.sql
...
...
...
ERRORS DURING RECOMPILATION
---------------------------
2
 
Function created.
 
PL/SQL procedure successfully completed.
 
Function dropped.
 
PL/SQL procedure successfully completed.
 
SQL> SELECT count(*) FROM dba_invalid_objects;
 
COUNT(*)
----------
2
 
SQL>

Run postupgrade_fixups.sql

SQL> @/u01/app/oracle/cfgtoollogs/DB1_RZA/preupgrade/postupgrade_fixups.sql

Gather statistics

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Run post upgrade status tool

SQL> @?/rdbms/admin/utlu121s.sql

Solve invalid objects

SQL> SELECT distinct object_name FROM dba_invalid_objects order by OBJECT_NAME;
 
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
VIP_START_TRIGGER
VIP_STOP_TRIGGER
 
SQL> select owner,object_name, object_type, status from dba_objects where object_name in ('VIP_START_TRIGGER','VIP_STOP_TRIGGER');
 
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- -------
DBISERVICES VIP_START_TRIGGER TRIGGER INVALID
DBISERVICES VIP_STOP_TRIGGER TRIGGER INVALID

Both triggers have been created again.

Check dba registry and invalid objects

SQL> set line 300
SQL> col comp_name format a50
SQL> select comp_name, version, status from dba_registry;
 
COMP_NAME VERSION STATUS
-------------------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views 12.1.0.2.0 VALID
Oracle Database Packages and Types 12.1.0.2.0 VALID
Oracle Workspace Manager 12.1.0.2.0 VALID
Oracle XML Database 12.1.0.2.0 VALID
 
SQL> SELECT count(*) FROM dba_invalid_objects;
 
COUNT(*)
----------
0
 
SQL> @?/rdbms/admin/utluiobj.sql
.
Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 08-10-2020 13:39:45
.
This tool lists post-upgrade invalid objects that were not invalid
prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).
.
Owner Object Name Object Type
.
 
PL/SQL procedure successfully completed.
 
SQL>

Upgrade time zone

Time zones have been upgraded with oracle script : upg_tzv_check.sql and upg_tzv_apply.sql
SQL> select version from v$timezone_file;
 
VERSION
----------
34

Check data patch

SQL> select PATCH_ID, VERSION from DBA_REGISTRY_SQLPATCH;
 
PATCH_ID VERSION
---------- --------------------
30691015 12.1.0.2
30805558 12.1.0.2

Set compatible parameter value to 12.1.0

SQL> show parameter compatible
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.0.0
noncdb_compatible boolean FALSE
 
SQL> show spparameter compatible
 
SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* compatible string 11.2.0.0.0
* noncdb_compatible boolean
 
SQL> alter system set compatible='12.1.0' scope=spfile;
 
System altered.

Restart database with grid

[email protected]:/home/oracle/mwagner/upgrade_TZ/ [DB1] srvctl stop database -d DB1_RZA
 
[email protected]:/home/oracle/mwagner/upgrade_TZ/ [DB1] DB1
********* dbi services Ltd. *********
STATUS : STOPPED
*************************************
 
[email protected]:/home/oracle/mwagner/upgrade_TZ/ [DB1] srvctl start database -d DB1_RZA
 
[email protected]:/home/oracle/mwagner/upgrade_TZ/ [DB1] DB1
********* dbi services Ltd. *********
STATUS : OPEN
DB_UNIQUE_NAME : DB1_RZA
OPEN_MODE : READ WRITE
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 12.1.0.2.0
CDB Enabled : NO
*************************************

Update registry metadata

To get the primary ODA registry metadata updated with the manual upgrade, I used odacli update-registry command. See my other blog for more details.

[[email protected] upgrade3]# odacli list-dbhomes
 
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
d6df9457-e4cd-4c39-b3cb-8d03be3c4598 OraDB11204_home1 11.2.0.4.190115 /u01/app/oracle/product/11.2.0.4/dbhome_1 Configured
9d2d92d0-3b98-42ac-9f39-9bd6deeb2e16 OraDB19000_home1 19.7.0.0.200414 /u01/app/oracle/product/19.0.0.0/dbhome_1 Configured
73847823-ae83-4bf0-a630-f8884cf4387a OraDB12102_home1 12.1.0.2.200414 /u01/app/oracle/product/12.1.0.2/dbhome_1 Configured
 
[[email protected] upgrade3]# odacli list-databases
 
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
d897e7d6-9e2d-45e4-a0d7-a1e232d47f16 DB1 Si 11.2.0.4.190115 false Oltp Odb1 Acfs Configured d6df9457-e4cd-4c39-b3cb-8d03be3c4598
 
[[email protected] log]# odacli update-registry -n db -f
 
Job details
----------------------------------------------------------------
ID: 2dbada8a-f76d-44bb-bb6e-c507d52e5ae3
Description: Discover Components : db
Status: Created
Created: August 10, 2020 1:58:37 PM CEST
Message:
 
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
 
[[email protected] log]# odacli describe-job -i 2dbada8a-f76d-44bb-bb6e-c507d52e5ae3
 
Job details
----------------------------------------------------------------
ID: 2dbada8a-f76d-44bb-bb6e-c507d52e5ae3
Description: Discover Components : db
Status: Success
Created: August 10, 2020 1:58:37 PM CEST
Message:
 
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Rediscover DBHome August 10, 2020 1:58:37 PM CEST August 10, 2020 1:58:39 PM CEST Success
Rediscover DBHome August 10, 2020 1:58:39 PM CEST August 10, 2020 1:58:41 PM CEST Success
Rediscover DBHome August 10, 2020 1:58:41 PM CEST August 10, 2020 1:58:48 PM CEST Success
Discover DBHome August 10, 2020 1:58:48 PM CEST August 10, 2020 1:58:48 PM CEST Success
Rediscover DB: DB1_RZA August 10, 2020 1:59:41 PM CEST August 10, 2020 1:59:51 PM CEST Success
 
[[email protected] log]# odacli list-databases
 
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
d897e7d6-9e2d-45e4-a0d7-a1e232d47f16 DB1 Si 12.1.0.2.200414 false Oltp Odb1 Acfs Configured 73847823-ae83-4bf0-a630-f8884cf4387a

Update network (tnsnames.ora and listener.ora for statistcs entries)

tnsnames.ora file is specific to each Oracle home. Therefore it is needed to copy previous 11.2.0.4 tnsnames.ora file to new 12.1.0.2 home (if any).
listener.ora file needs to be updated with new home for each static entries.

8- Start the application

The application can now be started. The session can be listed using same commands as in the part 3-Stop the application.

9- Test the application

The application needs to be tested. In case there is any critical issue and rollback of the operation is needed, we can use the standby and do a failover. Later the old upgraded primary will have to be deleted in order to create it again as standby. If all is ok, we can move forward and get the standby on the same stand.

10- Move the standby database to the new home

I checked homes :
[[email protected] patchs]# odacli list-dbhomes
 
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
c58cdcfd-e5b2-4041-b993-8df5a5d5ada4 OraDB11204_home1 11.2.0.4.190115 /u01/app/oracle/product/11.2.0.4/dbhome_1 Configured
b45cfba8-f891-469e-9b45-be1e3e2e010c OraDB12201_home1 12.2.0.1.190115 /u01/app/oracle/product/12.2.0.1/dbhome_1 Configured
60c9afb9-4bfe-4a11-bb96-ea43adf74f3d OraDB12102_home2 12.1.0.2.200414 /u01/app/oracle/product/12.1.0.2/dbhome_2 Configured

Standby database has been stopped at the beginning of the process and is still stopped. I moved init and password file from 11.2.0.4 to 12.1.0.2 home :
[email protected]:/home/oracle/ [DB1] cdh
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/ [DB1] cd dbs
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] ls *DB1*
hc_DB1.dat initDB1.ora initDB1.ora.old orapwDB1
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] cp -p initDB1.ora /u01/app/oracle/product/12.1.0.2/dbhome_2/dbs
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] cp -p orapwDB1 /u01/app/oracle/product/12.1.0.2/dbhome_2/dbs
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] mv initDB1.ora initDB1.ora.off
[email protected]:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [DB1] mv orapwDB1 orapwDB1.off

Of course tnsnames.ora file will have to be moved as well if any is used. The listener.ora will have to be updated for any static entries if used. This was not my case.

11- Upgrade the standby database in the clusterware

I upgraded the database in the clusterware :
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_2/network/admin/ [DB1] which srvctl
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl
 
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_2/network/admin/ [DB1] /u01/app/oracle/product/12.1.0.2/dbhome_2/bin/srvctl upgrade database -db DB1_RZB -oraclehome /u01/app/oracle/product/12.1.0.2/dbhome_2
 
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_2/ [DB1] srvctl config database -d DB1_RZB
Database unique name: DB1_RZB
Database name: DB1
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_2
Oracle user: oracle
Spfile: /u02/app/oracle/oradata/DB1_RZB/dbs/spfileDB1.ora
Password file:
Domain:
Start options: mount
Stop options: abort
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths: /u02/app/oracle/oradata/DB1_RZB,/u03/app/oracle/
Services:
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: DB1
Configured nodes: oda-std
Database is administrator managed

I updated /etc/oratab accordingly as it was not done by srvctl :
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_2/network/admin/ [DB1] grep DB1 /etc/oratab
DB1:/u01/app/oracle/product/11.2.0.4/dbhome_1:N # line added by Agent
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_2/network/admin/ [DB1] vi /etc/oratab
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_2/network/admin/ [DB1] grep DB1 /etc/oratab
DB1:/u01/app/oracle/product/12.1.0.2/dbhome_2:N # line added by Agent

12- Set compatible parameter to 12.1.0 on the standby database

SQL> alter system set compatible='12.1.0' scope=spfile;
 
System altered.

13- Start the standby database

[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_2/ [DB1] srvctl status database -d DB1_RZB
Instance DB1 is not running on node rzb-oda02
 
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_2/ [DB1] srvctl start database -d DB1_RZB
 
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_2/ [DB1] srvctl status database -d DB1_RZB
Instance DB1 is running on node rzb-oda02
 
[email protected]:/u01/app/oracle/product/12.1.0.2/dbhome_2/ [DB1] DB1
********* dbi services Ltd. *********
STATUS : MOUNTED
DB_UNIQUE_NAME : DB1_RZB
OPEN_MODE : MOUNTED
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PHYSICAL STANDBY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
CDB Enabled : NO
*************************************

14- Update the registry metadata on the ODA

I used odacli update-registry command to update the ODA registry metadata :
[[email protected] patchs]# odacli list-dbhomes
 
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
c58cdcfd-e5b2-4041-b993-8df5a5d5ada4 OraDB11204_home1 11.2.0.4.190115 /u01/app/oracle/product/11.2.0.4/dbhome_1 Configured
b45cfba8-f891-469e-9b45-be1e3e2e010c OraDB12201_home1 12.2.0.1.190115 /u01/app/oracle/product/12.2.0.1/dbhome_1 Configured
60c9afb9-4bfe-4a11-bb96-ea43adf74f3d OraDB12102_home2 12.1.0.2.200414 /u01/app/oracle/product/12.1.0.2/dbhome_2 Configured
 
[[email protected] patchs]# odacli list-databases
 
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
b15def96-a259-4d3a-a26d-71381142c0bc DB1 Si 11.2.0.4.190115 false Oltp Odb1 Acfs Configured c58cdcfd-e5b2-4041-b993-8df5a5d5ada4
 
[[email protected] patchs]# odacli update-registry -n db -f
 
Job details
----------------------------------------------------------------
ID: f7f9c86b-2a2f-46bc-8195-9f6a76a42cde
Description: Discover Components : db
Status: Created
Created: August 10, 2020 3:46:41 PM CEST
Message:
 
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
 
[[email protected] patchs]# odacli describe-job -i f7f9c86b-2a2f-46bc-8195-9f6a76a42cde
 
Job details
----------------------------------------------------------------
ID: f7f9c86b-2a2f-46bc-8195-9f6a76a42cde
Description: Discover Components : db
Status: Success
Created: August 10, 2020 3:46:41 PM CEST
Message:
 
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Rediscover DBHome August 10, 2020 3:46:41 PM CEST August 10, 2020 3:46:43 PM CEST Success
Rediscover DBHome August 10, 2020 3:46:43 PM CEST August 10, 2020 3:46:45 PM CEST Success
Rediscover DBHome August 10, 2020 3:46:45 PM CEST August 10, 2020 3:46:48 PM CEST Success
Rediscover DB: DB1_RZB August 10, 2020 3:47:15 PM CEST August 10, 2020 3:47:21 PM CEST Success
 
[[email protected] patchs]# odacli list-databases
 
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
b15def96-a259-4d3a-a26d-71381142c0bc DB1 Si 12.1.0.2.200414 false Oltp Odb1 Acfs Configured 60c9afb9-4bfe-4a11-bb96-ea43adf74f3d

15- Start the log shippment again from the primary to the standby

High availability with Data Guard

In case FSFO is used, you will need to enable it again :
DGMGRL> enable fast_start failover
Disabled.

You will need to set the protection mode back to the previous one (example maxavailability) :
DGMGRL> edit configuration set protection mode as maxavailability;
Succeeded.

You will need to start applying the change vector on the standby :
DGMGRL> edit database DB1_RZB set state=apply-on;
Succeeded.

You will need to start shipping the change vector on the primary :
DGMGRL> edit database DB1_RZA set state=transport-on;
Succeeded.

Check that there is no gap on the standby :
DGMGRL> show database DB1_RZB
 
Database - DB1_RZB
 
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 4.00 KByte/s
Real Time Query: OFF
Instance(s):
DB1
 
Database Status:
SUCCESS

What is mandatory is to have no transport lag and no apply lag :
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)

High availability with dbvisit

First of all the ORACLE_HOME and ORACLE_HOME_DR variables of the dbvisit DDC configuration file need to be updated with new and appropriate ORACLE_HOME. This is done on the primary side. In my case /u01/app/oracle/product/12.1.0.2/dbhome_1 (variable ORACLE_HOME) for the primary and /u01/app/oracle/product/12.1.0.2/dbhome_2 for the standby (variable ORACLE_HOME_DR). The option -C from dbvctl can be used to synchronized the standby DDC configuration file afterwards :
[email protected]_PRI:/u01/app/oracle/product/12.1.0.2/dbhome_1/ [KLIPOLYP] cd /u01/app/dbvisit/standby/conf/
 
[email protected]_PRI:/u01/app/dbvisit/standby/conf/ [KLIPOLYP] cp -p dbv_DB1.env dbv_DB1.env.20201021
 
[email protected]_PRI:/u01/app/dbvisit/standby/conf/ [DB1] vi dbv_DB1.env
 
[email protected]_PRI:/u01/app/dbvisit/standby/conf/ [DB1] diff dbv_DB1.env dbv_DB1.env.20201021
56c56
ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1
---
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1
95c95
ORACLE_HOME_DR = /u01/app/oracle/product/12.1.0.2/dbhome_2
---
ORACLE_HOME_DR = /u01/app/oracle/product/11.2.0.4/dbhome_1
 
[email protected]_PRI:/u01/app/dbvisit/standby/conf/ [DB1] /u01/app/dbvisit/standby/dbvctl -d DB1 -C
=============================================================
Dbvisit Standby Database Technology (9.0.02_0_gbd40c486) (pid 88389)
dbvctl started on ODA_PRI: Wed Oct 21 11:15:35 2020
=============================================================
 
>>> Dbvisit Standby configurational differences found between ODA_PRI and ODA_STD.
Synchronised.
 
=============================================================
dbvctl ended on ODA_PRI: Wed Oct 21 11:15:38 2020
=============================================================

Archive log shippment is done through the linux crontab. Purpose is then to activate again it on both the primary and the standby database using crontab -e command.

On the primary :
00,10,20,30,40,50 * * * * /u01/app/dbvisit/standby/dbvctl -d DB1 >/tmp/dbvisit_apply_logs_DB1.log 2>&1

On the standby :
05,15,25,35,45,55 * * * * /u01/app/dbvisit/standby/dbvctl -d DB1 >/tmp/dbvisit_apply_logs_DB1.log 2>&1

Use dbvctl command with option -i to ensure there is no gap. DB1 would be the name of my DDC configuration file :
[email protected]:/home/oracle/ [DB1] /u01/app/dbvisit/standby/dbvctl -d DB1 -i
=============================================================
Dbvisit Standby Database Technology (9.0.02_0_gbd40c486) (pid 84500)
dbvctl started on ODA-PRI: Mon Aug 10 17:26:53 2020
=============================================================
 
Dbvisit Standby log gap report for DB1_RZA at 202008101726:
-------------------------------------------------------------
Description | SCN | Timestamp
-------------------------------------------------------------
Source 13140834499 2020-08-10:17:26:55 +02:00
Destination 13140828019 2020-08-10:16:59:35 +02:00
 
Standby database time lag (DAYS-HH:MI:SS): +00:27:20
 
Report for Thread 1
-------------------
SOURCE
Current Sequence 52884
Last Archived Sequence 52883
Last Transferred Sequence 52883
Last Transferred Timestamp 2020-08-10 17:22:56
 
DESTINATION
Recovery Sequence 52884
 
Transfer Log Gap 0
Apply Log Gap 0
 
=============================================================
dbvctl ended on ODA-PRI: Mon Aug 10 17:26:59 2020
=============================================================

Mandatory is to have no gap :
Transfer Log Gap 0
Apply Log Gap 0

16- Test a switchover

A switchover can be later tested to ensure everything is working properly.

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Marc Wagner
Marc Wagner

Consultant