Friday, September 20, 2013

ORA-00376: file 50 cannot be read at this time

No need to say anything but following is the error you may get and below is the way to solve it.
How and why this happens? In our case, whenever there is a SAN Scan occurs we see couple of datafiles getting corrupted here and there.

ORA-12801: error signaled in parallel query server P001, instance ndhdbp4 ay_p4 (2)
ORA-00376: file 50 cannot be read at this time
ORA-01110: data file 50: '/ora07/oradata/pay_p/payer_hosting_8k_16.dbf'
when trying to selete from table I_SUBSCRIPTION_SUMMARY


SQL> select file#, ts#, status from gv$datafile where status!='ONLINE';

     FILE#        TS# STATUS
---------- ---------- -------
         1          0 SYSTEM
        28          0 SYSTEM
        36          7 RECOVER
        50          6 RECOVER

SQL> select file_name, file_id from dba_data_files where file_id in (36,50);

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID
----------
/ora19/oradata/pay_p/pcs_fs_data_8k_13.dbf
        36

/ora07/oradata/pay_p/payer_hosting_8k_16.dbf
        50


SQL> recover datafile '/ora19/oradata/pay_p/pcs_fs_data_8k_13.dbf';
Media recovery complete.
SQL> alter database datafile '/ora19/oradata/pay_p/pcs_fs_data_8k_13.dbf' online;

Database altered.

SQL> recover datafile '/ora07/oradata/pay_p/payer_hosting_8k_16.dbf';
Media recovery complete.
SQL> alter database datafile '/ora07/oradata/pay_p/payer_hosting_8k_16.dbf' online;

Database altered.

SQL>

Wednesday, August 28, 2013

ORA-01114: IO error writing block to file 8 (block # 3307) ORA-29701: unable to connect to Cluster Manager

Was getting that error for any ddl operation.

crsctl check crs throws this error:

>crsctl check crs
Failure 1 contacting Cluster Synchronization Services daemon

vxfs: msgcnt 2849 mesg 001: V-2-1: vx_nospace - /dev/vx/dsk/local_ora/oracle_lv                                                           file system full (1 block extent)Cannot communicate with Cluster Ready Services
Cannot communicate with Event Manager

That tells me that ORA-1114 is nothing to worry as this is just file system is full and oracle was not able to write any traces.

Clear up the mount point and you should be good.

Thursday, August 22, 2013

Statistics reply buffer exceeded. Results truncated...

In GoldenGate when using "stats extract group_name" you may get this error when the ouptut is too big to fit in the buffer:

"Statistics reply buffer exceeded.  Results truncated..."

You can write the statistics to a report file like below for the entire group stats:

GGSCI (ndhdbp3) 18> send epcs07, report

Sending REPORT request to EXTRACT EPCS07 ...
Request processed.


Or use stats extract group_name, table owner.table_name(or *) to get stats on individual table or schema.