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.

Tuesday, July 7, 2020

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

Friday, June 26, 2020

Oracle DB Upgrade to 18c fails with Error : ORA-04063: SYS.ALERT_QUE has errors

Problem
DB Upgrade from 11.2.0.4 to 18c failed with ORA-04063: SYS.ALERT_QUE has errors

Solution
SYS.ALERT_QUE corrupted in source database. recreate  SYS.ALERT_QUE  will sort the problem

1. Connect as sysdba and enable restricted mode
  sqlplus / as sysdba
  SQL> alter system enable restricted session;
2. Drop Existing ALERT_QT tables
exec dbms_aqadm.drop_queue_table('ALERT_QT',true);
3.  Re-create tables, sequence, type and queue for server alert
SQL> @$ORACLE_HOME/rdbms/admin/dbmsslrt.sql
SQL> @$ORACLE_HOME/rdbms/admin/catalrt.sql
SQL> @$ORACLE_HOME/rdbms/admin/catmwin.sql
SQL> @$ORACLE_HOME/rdbms/admin/exechae.sql
4. Compile Invalid objects
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
5. disable restricted mode
SQL> alter system disable restricted session;
6. Retry the DB upgrade

Reference - Upgrade to 12.2 fails with Error : ORA-04063: SYS.ALERT_QUE has errors (Doc ID 2632809.1)

Friday, March 13, 2020

Steps to Add ASM disk to diskgroup


1. Check existing diskgroup details and redundancy
[oracle@orahost01 ~]$ . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@orahost01 ~]$ asmcmd

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576   1817119   499770                0          499770              0             N  DATA/
MOUNTED  NORMAL  N         512   4096  1048576    550160   543956           137540          203208              0             N  FRA/
MOUNTED  NORMAL  N         512   4096  1048576     18435    18256             6145            6055              0             N  OCR/
OR
[oracle@orahost01 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 6 04:45:11 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option

SQL> select state, name, type from v$asm_diskgroup;

STATE       NAME                           TYPE
----------- ------------------------------ ------
MOUNTED     DATA                           EXTERN
MOUNTED     FRA                            NORMAL
MOUNTED     OCR                            NORMAL
2. Check existing disk details
SQL> select GROUP_NUMBER,MOUNT_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk;
GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME                 PATH
------------ ------- -------- ------- --------- ------------
           0 CLOSED  NORMAL   UNKNOWN                      /dev/oracleasm/disks/ASMDATA06
           1 CACHED  NORMAL   UNKNOWN DATA05               /dev/oracleasm/disks/ASMDATA05
           1 CACHED  NORMAL   UNKNOWN DATA04               /dev/oracleasm/disks/ASMDATA04
           1 CACHED  NORMAL   UNKNOWN DATA03               /dev/oracleasm/disks/ASMDATA03
           1 CACHED  NORMAL   UNKNOWN DATA02               /dev/oracleasm/disks/ASMDATA02
           1 CACHED  NORMAL   UNKNOWN DATA01               /dev/oracleasm/disks/ASMDATA01
           3 CACHED  NORMAL   UNKNOWN OCR_00             /dev/oracleasm/disks/OCR_01
           2 CACHED  NORMAL   UNKNOWN FRA_01               /dev/oracleasm/disks/FRA_01
           2 CACHED  NORMAL   UNKNOWN FRA_02               /dev/oracleasm/disks/FRA_02

9 rows selected.
3. Add the disk into diskgroup with same naming convention
SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASMDATA06' name DATA06;

Diskgroup altered.
4. Check the current size of diskgroup after adding the disk
SQL> select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup
 order by 1;

GROUP_NUMBER NAME        STATE     TYPE   TOTAL_MB   FREE_MB
------------ --------- --------- ------ ---------- ----------
           1 DATA                 MOUNTED     EXTERN    2329103    1011748
           2 FRA                  MOUNTED     NORMAL     550160     543956
           3 OCR                  MOUNTED     NORMAL      18435      18256
5. You can check the status of the process from below sql statement
SQL> select operation, state, sofar, est_work, est_minutes from v$asm_operation where group_number=1;

OPERATION STATE      SOFAR   EST_WORK EST_MINUTES
----- ---- ---------- ---------- -----------
REBAL RUN        3346     361739         107
 SQL> select operation, state,power, actual, sofar, est_work, est_minutes from v$asm_operation where group_number=1;

OPERATION STATE POWER ACTUAL     SOFAR   EST_WORK EST_MINUTES
----- ----- ---- ---- ------- ------ ---------
REBAL RUN   1   1  3456     361740         115
5. You can speed up the re-balancing process by changing power limit for diskgroup
SQL> Alter diskgroup DATA rebalance power 4;
SQL> select operation, state,power, actual, sofar, est_work, est_minutes from v$asm_operation where group_number=1;

OPERATION STATE POWER ACTUAL     SOFAR   EST_WORK EST_MINUTES
----- ----- ---- ---- ------- ------ ---------
REBAL RUN   4   4  1200     324740         205

Thursday, March 12, 2020

Solution for ORA-03113: end-of-file on communication channel

Problem

Following Error occur while restart the database after the unclean shutdown
SQL> startup
ORACLE instance started.
Total System Global Area 4943876096 bytes
Fixed Size                  2261688 bytes
Variable Size             989859144 bytes
Database Buffers         3942645760 bytes
Redo Buffers                9109504 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 18379
Session ID: 191 Serial number: 3
Solution

Redo log file might become corrupted while the database is open,Need to clear the corrupted redo logs to open the database.
SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 11 18:35:04 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.
SQL> startup mount;

ORACLE instance started.

Total System Global Area 4943876096 bytes
Fixed Size                  2261688 bytes
Variable Size             989859144 bytes
Database Buffers         3942645760 bytes
Redo Buffers                9109504 bytes
Database mounted.
SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.
SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4943876096 bytes
Fixed Size                  2261688 bytes
Variable Size             989859144 bytes
Database Buffers         3942645760 bytes
Redo Buffers                9109504 bytes
Database mounted.
Database opened.