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.

OPW-00029: Password complexity failed in oracle 19c

Problem

Error occor while creating password file in oracle 19c

[oracle@oradb dbs]$ orapwd file=orapwPROD password=Welcome123 entries=30

OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.

Solution

by default oracle use 12.2 format for password complexity since DB version is 19c. Two option for overcome this issue

provide password which meet the complexity requirement

[oracle@oradb dbs]$ orapwd file=orapwPROD password=Welcome#123 entries=30

OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.

OR

Create password file for oracle db version 12.1

Check exisitng password file format

[oracle@oradb dbs]$ orapwd describe file=orapwPROD
Password file Description : format=12.2

Delete exisiting password file and Create with 12.1 format

[oracle@oradb dbs]$ rm -rf orapwPROD

[oracle@oradb dbs]$ orapwd file=orapwPROD password=Welcome123 entries=30 format=12

Check password file format again

[oracle@oradb dbs]$ orapwd describe file=orapwPROD
Password file Description : format=12