Cause: Of these error mostly disk is full in that case to skip some blocks then generating the error. So keep monitor the server Mount point size.
1.
ORA-19502: write error on file "", block number (block size=) in Dataguard
We have received an alert in the morning that the Primary Database is out of Sync. Primary and Standby are with difference of 9 archives.
I have logged on to the primary and identified the below error.
ORA-19502: write error on file "", block number (block size=)
SQL> set line 200
col DEST_NAME for a50
col BINDING for a10
select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE';
DEST_ID DEST_NAME STATUS BINDING ERROR
---------- -------------------------------------------------- --------- ---------- -----------------------------------------------------------------
1 LOG_ARCHIVE_DEST_1 VALID OPTIONAL
2 LOG_ARCHIVE_DEST_2 ERROR OPTIONAL ORA-19502: write error on file "", block number (block size=)
In the above output the STATUS column is showing ERROR.
I have logged on to the Standby database and found the archive mount point is full. I removed the applied archives and freed up some space. How to check Sync Status on Standby
Defer'd and Enable'd the shipping on Primary Database.
SQL> alter system set log_archive_dest_state_2=defer;
System Altered.
SQL> alter system set log_archive_dest_state_2=enable;
System Altered.
SQL> set line 200
SQL> col DEST_NAME for a50
SQL> col BINDING for a10
SQL> select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE'
/
DEST_ID DEST_NAME STATUS BINDING ERROR
---------- -------------------------------------------------- --------- ---------- -----------------------------------------------------------------
1 LOG_ARCHIVE_DEST_1 VALID OPTIONAL
2 LOG_ARCHIVE_DEST_2 VALID OPTIONAL
Now the STATUS column is showing Valid and the ERROR column is null.
Archive started shipping to Standby Database and got applied.
How to check Sync Status on Standby
2.
ORA-16038 (with ORA-19502,ORA-00312)
-----------------------------------------------------------------------------
SQL> ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 1219160 bytes
Variable Size 167773608 bytes
Database Buffers 1073741824 bytes
Redo Buffers 15556608 bytes
Database mounted.
ORA-16038: log 1 sequence# 8557 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 1 thread 1: '/u02/redofs/redo01.log'
------------------------------------------------------------------------------
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered
SQL>shutdown immediate
Database closed.
Database dismount.
ORACLE instance shutdown.
SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 1219160 bytes
Variable Size 167773608 bytes
Database Buffers 1073741824 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.
1.
ORA-19502: write error on file "", block number (block size=) in Dataguard
We have received an alert in the morning that the Primary Database is out of Sync. Primary and Standby are with difference of 9 archives.
I have logged on to the primary and identified the below error.
ORA-19502: write error on file "", block number (block size=)
SQL> set line 200
col DEST_NAME for a50
col BINDING for a10
select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE';
DEST_ID DEST_NAME STATUS BINDING ERROR
---------- -------------------------------------------------- --------- ---------- -----------------------------------------------------------------
1 LOG_ARCHIVE_DEST_1 VALID OPTIONAL
2 LOG_ARCHIVE_DEST_2 ERROR OPTIONAL ORA-19502: write error on file "", block number (block size=)
In the above output the STATUS column is showing ERROR.
I have logged on to the Standby database and found the archive mount point is full. I removed the applied archives and freed up some space. How to check Sync Status on Standby
Defer'd and Enable'd the shipping on Primary Database.
SQL> alter system set log_archive_dest_state_2=defer;
System Altered.
SQL> alter system set log_archive_dest_state_2=enable;
System Altered.
SQL> set line 200
SQL> col DEST_NAME for a50
SQL> col BINDING for a10
SQL> select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE'
/
DEST_ID DEST_NAME STATUS BINDING ERROR
---------- -------------------------------------------------- --------- ---------- -----------------------------------------------------------------
1 LOG_ARCHIVE_DEST_1 VALID OPTIONAL
2 LOG_ARCHIVE_DEST_2 VALID OPTIONAL
Now the STATUS column is showing Valid and the ERROR column is null.
Archive started shipping to Standby Database and got applied.
How to check Sync Status on Standby
2.
ORA-16038 (with ORA-19502,ORA-00312)
-----------------------------------------------------------------------------
SQL> ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 1219160 bytes
Variable Size 167773608 bytes
Database Buffers 1073741824 bytes
Redo Buffers 15556608 bytes
Database mounted.
ORA-16038: log 1 sequence# 8557 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 1 thread 1: '/u02/redofs/redo01.log'
------------------------------------------------------------------------------
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered
SQL>shutdown immediate
Database closed.
Database dismount.
ORACLE instance shutdown.
SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 1219160 bytes
Variable Size 167773608 bytes
Database Buffers 1073741824 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.
No comments:
Post a Comment