How to Troubleshoot and Resolve an ORA-1110 Error

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline:  +86 13764045638 E-mail: service@parnassusdata.com

Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.3 [Release 9.0.1 to 11.2]

Information in this document applies to any platform.





 

GOAL

The goal of this article is to assist DBAs who encounter the ORA-01110, and to point them in the right direction.

Several notes have been referenced depending on the subsequent errors.

If the DBA is unable to resolve the issue after reading the appropriate note, a script to collect diagnostic information has been provided below. The output of this script should be uploaded to the service request.

SOLUTION

Definition

The ORA-01110 displays the physical datafile in which Oracle is having a problem accessing.

The ORA-01110 is accompanied by one or more messages. These messages may be Oracle specific messages or be related to the operating system.

The first aim is to identify all error messages encounted prior to addressing the issue.

Below is a list of the common errors that may follow the ORA-01110.

• Addressing an ORA-01157 (cannot identify datafile <name> - file not found)

             - Does the datafile in question exist at the os level ?

              If yes ensure that it is valid. Check the permissions & state of the file. Run an RMAN DBV or RMAN validate on the file/s this may provide additional diagnostics.

              If not locate the file or restore from backup.

• Addressing an ORA-01578 (ORACLE data block corrupted (file # %s, block # %s))

             - Identify the object flagged as corurpted:

                SELECT tablespace_name, segment_type, owner, segment_name
                FROM dba_extents
                WHERE file_id = &AFN
                and &BL between block_id AND block_id + blocks - 1;

             - Determine if the corruption is wide spread

                Options to scan DB ( using DBV or  RMAN)

                % dbv userid=<username/password> file=<full ptah to corrupted file> blocksize=<tablespace block size>

                RMAN> backup validate check logical database;  ( this will scan the entire Database)

                RMAN> backup validate check logical tablespace <TS_NAME> ;  ( this will scan the entire tablespace listed)

                RMAN> backup validate check logical datafile X;  (Where X is the datafile in question)

                When either RMAN command is complete review:

                SQL> select * from v$database_block_corruption ( This will list corrupted blocks found within DB)

               - The following note provided avenues into resolving the corruption:

                Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g (Doc ID 28814.1)

• Addressing an ORA-00376 (file <name> cannot be read at this time)

             - If the file is physically present then ensure that the permissions and size of file is as expected.

             - Check to see if the file is online - look at v$recover_file. If the file is offline you will need to recover and online it assuming you are running in archivelog mode.

             - To assist Oracle supporting you please upload the results of the script below

• Addressing an ORA-01194 (file <name> needs more recovery to be consistent) & ORA-01547 (warning: RECOVER succeeded but OPEN RESETLOGS would get error)

             - The error indicates that the recovery may still have files which are in an inconsistent state. This may be due to datafiles being restored at different times or we have not applied enough achivelogs following a backup.

             - At a minimum, all archivelogs that were generated during an online backup must be applied during recovery.

• Addressing an ORA-01135 (file %s accessed for DML/query is offline)

     - The File is OFFLINE, and you must set in Online to be accessible 

        Example:
         SQL> recover datafile '%s';
         SQL> alter database datafile '%s' online;

To assist Oracle support diagnose the issue please provide the following:

            Please provide the output in the service request that you may raise. (upload recovery_info.txt)

set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
spool off
exit