ORA-19502: write error on file & ORA-16038 (with ORA-19502,ORA-00312)

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.

DB LINK Create & Drop Through Procedure

CREATE or replace PROCEDURE scott.create_db_link AS
BEGIN
EXECUTE IMMEDIATE 'create database link LINK1 connect to scott identified by tiger using ''testdb''';
END create_db_link;
 /


exec scott.create_db_link;


col db_link format a50
select OWNER,DB_LINK,USERNAME from dba_db_links;
select * from dba_db_links where OWNER='SCOTT';



----drop database link scott.LINK1.REGRESS.RDBMS.DEV.US.ORACLE.COM;     ##
drop database link scott.LINK1.REGRESS.RDBMS.DEV.US.ORACLE.COM
                   *
ERROR at line 1:
ORA-02024: database link not found

----drop database link scott.LINK1;                                                 ##
drop database link scott.LINK1;
                   *
ERROR at line 1:
ORA-02024: database link not found

CREATE PROCEDURE scott.drop_db_link AS
BEGIN
EXECUTE IMMEDIATE 'drop database link LINK1';
END drop_db_link;
 /


exec scott.drop_db_link;


col db_link format a50
select OWNER,DB_LINK,USERNAME from dba_db_links;
select * from dba_db_links where OWNER='SCOTT';


drop procedure scott.create_db_link ;
drop procedure scott.drop_db_link;