Oracle Database Commands and Practical Examples: upgrade troubleshooting

Showing posts with label upgrade troubleshooting. Show all posts

Monday 4 October 2021

standby redo log file header 19.0.0.0.0 exceeded the one currently supported on this standby 12.1.0.2.0.

Recently we got error after upgrade from 12c to 19c. compatible parameter not set to 19c on physical standby database , Due to this after applying MRP we got error.

Error : The compatibility version in redo log file header 19.0.0.0.0 exceeded the one currently supported on this standby 12.1.0.2.0.

2021-09-19T17:24:47.440414+05:30

rfs (PID:5459): Opened log for T-1.S-302 dbid 912183804 branch 1081693119

rfs (PID:5459): The compatibility version in redo log file header 19.0.0.0.0 exceeded the one currently supported on this standby 12.1.0.2.0.

2021-09-19T17:25:31.765108+05:30

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_19/o1_mf_1_302_jng987fp_.arc

2021-09-19T17:30:49.929833+05:30

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_19/o1_mf_0_0_jng9mkwx_.arc

2021-09-19T17:30:50.019755+05:30

rfs (PID:5816): Primary database is in MAXIMUM PERFORMANCE mode

rfs (PID:5816): No SRLs available for T-1

2021-09-19T17:30:50.068208+05:30

Scenario :

SQL> show parameter compatible

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

compatible string 12.1.0.2.0

noncdb_compatible boolean FALSE

Change compatible parameter to '19.0.0'

SQL> alter system set compatible='19.0.0' scope=spfile;

shudown standby database

startup mount

SQL> select process, thread#, sequence#, status from v$managed_standby;

PROCESS THREAD# SEQUENCE# STATUS

--------- ---------- ---------- ------------

ARCH 0 0 CONNECTED

DGRD 0 0 ALLOCATED

ARCH 0 0 CONNECTED

ARCH 1 110 CLOSING

ARCH 0 0 CONNECTED

RFS 1 302 IDLE

7 rows selected.

Apply MRP on standby database



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

Database altered.

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
  2    3    4  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
  5  WHERE
  6  ARCH.THREAD# = APPL.THREAD#
  7  ;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                    196                   110         86

SQL> /

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                    301                   199        102

SQL> /

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                    302                   302          0

SQL>
SQL> select process, thread#, sequence#, status from v$managed_standby;

PROCESS      THREAD#  SEQUENCE# STATUS
--------- ---------- ---------- ------------
ARCH               1        298 CLOSING
DGRD               0          0 ALLOCATED
DGRD               0          0 ALLOCATED
ARCH               0          0 CONNECTED
ARCH               1        110 CLOSING
ARCH               1        301 CLOSING
MRP0               1        303 APPLYING_LOG
RFS                1        303 IDLE
RFS                1          0 IDLE
RFS                0          0 IDLE
RFS                0          0 IDLE

PROCESS      THREAD#  SEQUENCE# STATUS
--------- ---------- ---------- ------------
RFS                0          0 IDLE

12 rows selected.

SQL>

ORA-38880: Cannot advance compatibility from 12.1.0.2.0 to 19.0.0.0.0 due to guaranteed restore points

We recently done manual upgradation from 12c to 19c after post upgrade after changing compatible parameter to 19c we still got error. This is due to restore point was not dropped before pre upgrade. so we shut reverted back compatible parameter to 12c and delete restore point then change it back to 19c compatible parameter and startup the database server.

Error:

ORA-38880: Cannot advance compatibility from 12.1.0.2.0 to 19.0.0.0.0 due to guaranteed restore points

Scenario:

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1862269648 bytes

Fixed Size 9136848 bytes

Variable Size 637534208 bytes

Database Buffers 1207959552 bytes

Redo Buffers 7639040 bytes

ORA-38880: Cannot advance compatibility from 12.1.0.2.0 to 19.0.0.0.0 due to guaranteed restore points

SQL> show parameter compatible;

NAME TYPE VALUE

------------------------------------ --------------------------------- ------------------------------

compatible string 19.0.0

noncdb_compatible boolean FALSE

SQL> select name from v$restore_point;

select name from v$restore_point

ERROR at line 1:

ORA-01507: database not mounted

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup force;

ORACLE instance started.

Total System Global Area 1862269648 bytes

Fixed Size 9136848 bytes

Variable Size 637534208 bytes

Database Buffers 1207959552 bytes

Redo Buffers 7639040 bytes

ORA-38880: Cannot advance compatibility from 12.1.0.2.0 to 19.0.0.0.0 due to guaranteed restore points

Change compatible parameter to 12.1.0.2.0 then check restore point and delete it.

SQL> alter system set compatible="12.1.0.2.0" scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1862269648 bytes
Fixed Size                  9136848 bytes
Variable Size             637534208 bytes
Database Buffers         1207959552 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;

GUA NAME
--- --------------------------------------------------------------------------------------------------------------------------------
YES BEFORE_UPGRADE_19C

1 row selected.

SQL> select flashback_On from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

1 row selected.

SQL> alter database flashback off;

Database altered.

SQL> select flashback_On from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

1 row selected.

SQL> drop restore point BEFORE_UPGRADE_19C;

Restore point dropped.

SQL> select flashback_On from v$database;

FLASHBACK_ON
------------------
NO

1 row selected.

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.1.0.2.0
noncdb_compatible                    boolean     FALSE

SQL>  alter system set compatible='19.0.0' scope=spfile;

System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1862269648 bytes
Fixed Size                  9136848 bytes
Variable Size             637534208 bytes
Database Buffers         1207959552 bytes
Redo Buffers                7639040 bytes
Database mounted.

Database opened.
SQL> SQL> sho parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0
noncdb_compatible                    boolean     FALSE
SQL>

HOME

Monday 4 October 2021

standby redo log file header 19.0.0.0.0 exceeded the one currently supported on this standby 12.1.0.2.0.

ORA-38880: Cannot advance compatibility from 12.1.0.2.0 to 19.0.0.0.0 due to guaranteed restore points