Check DB Error

[Former Member]

Hi,

We are having standby(DRS) server for our production.

We have switch over to standby server. After running check DB we have faced the follwoing error :

BR0301E SQL error -1187 at location BrTspListGet-21

ORA-01187: cannot read from file 255 because it failed verification tests

ORA-01110: data file 255: '/oracle/R3P/sapdata1/temp_3/temp.data3'.

Our oracle version is 9.2.0.8 on AIX.

The data file 255 is existing with proper permissions.

Kindly suggest.

Regards,

Rahul.

[Former Member · Accepted solution]

Hi,

yes but it should not be done when both database is on the state open and running because your TEMP datafile maybe in use or changed while transfer process.

the better way is :

- to down production and database on both server for a while

- make sure that log os fully transferred and applied to standby server

- copy datafiles from production server to standby server.

hope it help you

rgds,

Alfonsus Guritno

[Former Member]

Hi Alfonsus,

Thanks it works.

Regards,

Rahul.

[Former Member]

Hi Alfonsus,

Thanks. Our standby server is full replica of our production server. We are taking sync with production by applying the logs on stamdby server.

In production the checkdb is working fine. So can I copy this temp datafile to our Standby server.

Regards,

Rahul.

[Former Member]

Hi,

It depends on how your production server and standby server is managed.

Do you replicate production server to standby server ? It means that your production server and standby server have an equal and real-time data? (In terms of this, you have an mirror server that will be ready to up in case your current production server is down).

If yes, have you tried a full replication between two server?

After that, what's happen if you are perform DB check on standby server ?

But if you don't install replication software, in example True Copy, or SharePlex Quest, one thing that you may try is to create a full offline backup from production server, and then restore it on standby server. Using this procedure, you have just copy datafiles from production server to standby server by means of restoring offline backup. Maybe it is smoother solution, but take a reasonable downtime during backup and restore.

You can also try to follow procedure explained on previous message, that is dropping old tablespace TEMP on standy server, whose corrupted datafiles exist, and then recreate new TEMP tablespace with new datafiles.

One thing you should make sure is that the same datafiles on production server is fine (you can perform DB check without error)

hope it help you

rgds,

Alfonsus Guritno

[Former Member]

Hi Alfonsus,

The data file 255: '/oracle/R3P/sapdata1/temp_3/temp.data3' is existing at filesystem level.

The error is on standby server.

The file is available on our production server. Can I copy this file to Standby server then revoer..

Regards,

Rahul.

[Former Member]

Hi,

- Make sure that correct datafile (/oracle/R3P/sapdata1/temp_3/temp.data3) is available to the database. Then, either open the database, or execute ALTER SYSTEM CHECK DATAFILES. you can also check by issuing command :

select file_name, status from dba_data_files

where tablespace_name='TEMP';

- if it is not work, probably that datafile is corrupt or datafile doesn' exist. If this datafile belongs to TEMPORARY TABLESPACE, you can consider to create a new TEMP tablespace and drop the current TEMP tablespace which contains the corrupted datafile.

first make sure no other user is connect to the database,

alter tablespace TEMP offline;

drop tablespace TEMP;

create tablespace TEMP datafile '/oracle/R3P/sapdata1/temp_3/temp.data30' size100M reuse online temporary;

alter tablespace TEMPORARY add datafile '/oracle/R3P/sapdata1/temp_3/temp.data31' size 100M reuse;

please be careful on each steps you make.

hope it help you,

rgds,

Alfonsus Guritno