Env Details:-
Primary DB Version: 19.15
Env : RAC 2 node Exadata
GI: 19.15.0.0
OS: OEL 7
Standby DB Version : 19.15
Env : Standalone DB on ODA
GI: 19.15.0.0
OS: OEL 7
Scenario: – For one of my customers, there was a requirement to change compatible parameter in both primary and standby database. The customer went through a database upgrade (12c to 19c) activity and had GRP (Guaranteed Restore Point) in the database. The GRP was kept for a week post DB upgrade to make sure there is in case of any requirement Database can fall back to its old version. Now after a week, once GRP was dropped, we had to set compatible parameter to 19.0.0 in both primary and standby database. Following are the steps that I performed to achieve this.
Steps:-
First make sure there is no log gap between primary & Standby Database
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”,
2 APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
3 FROM
4 (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
5 (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
6 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
7 (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
8 WHERE
9 ARCH.THREAD# = APPL.THREAD#
10 ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 97989 97989 0
2 90142 90142 0
Current compatible set in database
SQL> show parameter compatible
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 12.1.0.2.0
Now change the compatible parameter in standby database then do it on primary database.
On Standby DB:-
STANDBYDB_SQL> alter system set compatible=’19.0.0′ scope=spfile ;
After changing we need to bounce the database to get it in effect, so cancel the MRP apply.
STANDBYDB_SQL> alter database recover managed standby database cancel;
Now stop and start the standby database.
[oracle@standbydb]$ srvctl stop database -d $ORACLE_UNQNAME
[oracle@standbydb]$ srvctl start database -d $ORACLE_UNQNAME -o mount
Now start the MRP process to apply the redologs in standby db.
STANDBYDB_SQL> alter database recover managed standby database disconnect from session;
On Primary DB:-
Login to, primary database and change the compatible parameter.
PRIMARYDB_SQL> alter system set compatible=’19.0.0′ scope=spfile sid=’*’;
[oracle@primarydb]$ srvctl stop database -d $ORACLE_UNQNAME
[oracle@primarydb]$ srvctl start database -d $ORACLE_UNQNAME
Leave a Reply