Tuesday, July 21, 2020

Standby DB Error 'ORA-00318: log 11 of thread 1, expected file size doesn't match' in Alert Log

Problem

Standby Alertlog prompt following error continously
Errors in file /u01/app/oracle/diag/rdbms/prod_stb/PROD/trace/PROD_rfs_56892.trc:
ORA-00341: log 10 of thread 1, wrong log # 11 in header
ORA-00312: online log 10 thread 1: '/u03/oradata/PROD/standby_redo11.log'
2020-07-16T22:32:55.488499+06:00
Errors in file /u01/app/oracle/diag/rdbms/prod_stb/PROD/trace/PROD_rfs_56892.trc:
ORA-00318: log 11 of thread 1, expected file size 102400 doesn't match 4194304
ORA-00312: online log 11 thread 1: '/u03/oradata/PROD/standby_redo12.log'

Solution

Size of standby redolog files should be same as primary online redolog files. Need to change Standby redolog file size same as primary redolog files

1. Check primary redolog file details.
SQL> SELECT thread#, group#, members, sequence#, bytes/1024/1024, archived ,status FROM v$log ORDER BY thread#, group#;

   THREAD#     GROUP#    MEMBERS  SEQUENCE# BYTES/1024/1024 ARC STATUS
---------- ---------- ---------- ---------- --------------- --- ----------------
         1          1          2      17737            2048 YES INACTIVE
         1          2          2      17738            2048 YES INACTIVE
         1          3          2      17739            2048 NO  CURRENT
2. Check standby redolog group details from Standby Database
SQL>  SELECT thread#, group#,bytes/1024/1024,status FROM v$standby_log;

   THREAD#     GROUP# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
         1         11            512 ACTIVE
         1         12            512 UNASSIGNED
         1         13            512 UNASSIGNED
         1         14            512 UNASSIGNED

3. Check standby redolog file details from Standby Database
SQL> SELECT group#, type, member FROM v$logfile WHERE type = 'STANDBY' order by group#;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
        11 STANDBY /u03/oradata/PROD/standby_redo111.log
        11 STANDBY /u03/oradata/PROD/standby_redo112.log
        12 STANDBY /u03/oradata/PROD/standby_redo121.log
        12 STANDBY /u03/oradata/PROD/standby_redo122.log
        13 STANDBY /u03/oradata/PROD/standby_redo131.log
        13 STANDBY /u03/oradata/PROD/standby_redo132.log
        14 STANDBY /u03/oradata/PROD/standby_redo141.log
        14 STANDBY /u03/oradata/PROD/standby_redo142.log

4. Cancel standby synchronization
SQL>  alter database recover managed standby database cancel;

Database altered.

5. Drop Exisiting standby redolog groups
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 11;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 12;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 13;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 14;

Database altered.

6. Delete physical files from server
[oracle@prdstb ~]$ cd /u03/oradata/PROD/
[oracle@prdstb PROD]$ ls sta*
standby_redo11.log  standby_redo21.log  standby_redo31.log
standby_redo12.log  standby_redo22.log  standby_redo32.log
[oracle@prdstb PROD]$ rm -rf standby_redo*
7. Re-Create standby redolog files with same size as primary
QL>  alter database add standby logfile thread 1 group 11 ('/u03/oradata/PROD/standby_redo111.log','/u03/oradata/PROD/standby_redo112.log') size 2048M;

Database altered.

SQL>  alter database add standby logfile thread 1 group 12 ('/u03/oradata/PROD/standby_redo121.log','/u03/oradata/PROD/standby_redo122.log') size 2048M;

Database altered.

SQL> alter database add standby logfile thread 1 group 13 ('/u03/oradata/PROD/standby_redo131.log','/u03/oradata/PROD/standby_redo132.log') size 2048M;

Database altered.

SQL> alter database add standby logfile thread 1 group 14 ('/u03/oradata/PROD/standby_redo141.log','/u03/oradata/PROD/standby_redo142.log') size 2048M;

Database altered.
8. Re-start standby synchronization
SQL>  alter database recover managed standby database disconnect from session;

Database altered.

No comments:

Post a Comment