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
