Ошибка ora 01110

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

Error: ORA-01110
Text: datafile <name>: <str>
——————————————————————————-
Cause : This message reports the filename involved with other messages.
Action: See the associated messages for a description of the problem.

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.

ORA-01157    «cannot identify datafile <name> — file not found»
ORA-01578    «ORACLE data block corrupted (file # %s, block # %s)»
ORA-00376    «file <name> cannot be read at this time»
ORA-01194    «file <name> needs more recovery to be consistent»
ORA-01547    «warning: RECOVER succeeded but OPEN RESETLOGS would get error»
ORA-01135    «file %s accessed for DML/query is offline»
 

  • 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
Источник

Saw errors ORA-01196 thrown by the database when trying to activate a standby database.

SQL> alter database activate standby database;
alter database activate standby database
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+data/system.243.34698032'

Generally, if we’d like to open the standby database for testing purpose temporarily, we may convert it as a snapshot database and convert it back to the standby role after testing. However, we can also make the standby database open as read-write permanently.

Rationale

The statement above intends to open the database as a primary role no matter what current status of recovery. Which implies the following archivelogs can be abandoned or forgotten from this moment. It’s usually for building testing environments and no easy ways to turn back. The only requisite to open as a primary is that the applied archivelogs must make the database consistent.

So I think the recovering standby database might have been interrupted by some incidents internally or externally.

Solutions

Sometimes, there may be some unaware archivelog gaps needed to be resolved before you active the standby database. You have to either resolve the gaps or make the database consistent before doing activation.

Since it’s only for testing purpose, there’s no serious reason to resolve the gap, so we choose the second approach.

  1. Cancel the apply service.

    2. SQL> alter database recover managed standby database cancel;

  2. Manually recover the standby database until you think it’s far enough to open, then enter CANCEL.

    3. SQL> recover standby database until cancel;
    ...
    CANCEL

  3. Now we can try to activate the standby database.

    4. SQL> alter database activate standby database;

    Database altered.

  4. Bounce the database

    5. SQL> shutdown immediate;
    SQL> startup;

Done.

Источник

Alert log shows following ORA- errors:

ORA-01578:ORACLE data block corrupted (file # 13, block # 652351)
ORA-01110:data file 13:'/media/sf_software/oradata/OEMREP/EMPDBREPOS/mgmt.dbf'
ORA-26040:Data block was loaded using the NOLOGGING option

Find out the segment impacted, and confirm this is an empty table.

SQL>select OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME 
    from dba_extents 
   where file_id =13 and 652351 between block_id AND block_id+blocks-1;

OWNER    SEGMENT_NAME           SEGMENT_TYPE   TABLESPACE_NAME
-------- ---------------------  -------------- ----------------
SYSMAN   MOS_PA_EVAL_TARGETS_E  TABLE          MGMT_TABLESPACE
SQL> select count(*) from SYSMAN.MOS_PA_EVAL_TARGETS_E;

COUNT(*)
----------
0
SQL>select FILE#, BLOCK#, BLOCKS,scn_to_timestamp(NONLOGGED_START_CHANGE#), 
           scn_to_timestamp( NONLOGGED_END_CHANGE#), OBJECT# 
    from v$nonlogged_block;

FILE# BLOCK#   BLOCKS  SCN_TO_TIMESTAMP(NONLOGGED_START_CHANGE#)  SCN_TO_TIMESTAMP(NONLOGGED_END_CHANGE#)  OBJECT#
---------------------- -----------------------------------------  ---------------------------------------  -------
13   652351   1        12-MAY-20 11.58.27.000000000 AM           12-MAY-20 10.59.29.000000000 PM           91461

Truncate or move the table. 

SQL>truncate table SYSMAN.MOS_PA_EVAL_TARGETS_E;

or

SQL> alter table SYSMAN.MOS_PA_EVAL_TARGETS_E move tablespace MGMT_TABLESPACE;

luckily, the table is empty. it is easy to deal with. If table is not empty, then we can skip corrupted blocks to avoid DML ORA- errors.

Skip corrupt blocks 

SQL> select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E;

select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E
                      *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 13, block # 652351)
ORA-26040: Data block was loaded using the NOLOGGING option
BEGIN
  DBMS_REPAIR.skip_corrupt_blocks (
    schema_name => 'SYSMAN',
    object_name => 'MOS_PA_EVAL_TARGETS_E',
    object_type => DBMS_REPAIR.table_object,
    flags       => DBMS_REPAIR.skip_flag);
END;
/
 
SQL> select owner, table_name, SKIP_CORRUPT  from dba_tables 
      where table_name='MOS_PA_EVAL_TARGETS_E'

OWNER      TABLE_NAME                     SKIP_COR
---------- ------------------------------ --------
SYSMAN     MOS_PA_EVAL_TARGETS_E          ENABLED

Backup the table data, truncate the table , and then restore the table data

SQL> create table SYSMAN.STG_MOS_PA_EVAL_TARGETS_E as 
            select * from SYSMAN.MOS_PA_EVAL_TARGETS_E;

SQL> Truncate table SYSMAN.MOS_PA_EVAL_TARGETS_E;

SQL>Insert into SYSMAN.MOS_PA_EVAL_TARGETS_E 
                select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E ;

The nologging corrupt block is returned to free space list

SQL> Select BYTES from dba_free_space 
     where file_id=13 and 652351 between block_id and block_id + blocks -1;

BYTES
----------
65536

SQL> select * from dba_extents 
     where file_id =13 and 652351 between block_id AND block_id+blocks-1;

no rows selected

After a little while, the nologging corrupt block will be reused

SQL> Select BYTES from dba_free_space
where file_id=13 and 652351 between block_id and block_id + blocks -1; 

no rows selected

SQL> select count(*) from dba_extents
where file_id =13 and 652351 between block_id AND block_id+blocks-1; 

COUNT(*)
----------
1
Источник

Hello DBA-Mates,

We are back after a long time with ORA-01110: data file 1:
‘/u01/oracle/oradata/ORA-DATA3/system01.dbf’. Got so many messages and
comments as why stopped writing but we didn’t stopped , there was some bad time
which slowly passing away and we came back.

Thank you all for your supporting
and loving.

Recently, one of our friend faced this below issue and
they were not able to recover the database. So, friends before playing with
database take the backup always either online or offline.

Here, we have just shown as if we
missed system datafile or deleted, how can we recover it and start the
database.

1.

Take the offline backup.

SQL> select name,open_mode,log_mode,flashback_on from
v$database;

NAME     
OPEN_MODE  LOG_MODE     FLASHBACK_ON

——— ———- ———— ——————

ORA-DATA3      READ
WRITE ARCHIVELOG   NO

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence           4

******************************************

Make some transactions inorder to generate archive logs

(if account is locked then):

SQL> alter user scott identified by tiger account
unlock;

SQL> conn scott/tiger

Connected.

SQL> create table student(name varchar2(50))
tablespace users;

Table created.

SQL> begin

  2  for i in 1..300000 loop

  3  insert into student values(’10g DBA Training
and R12 Apps DBA Training student’);

  4  end loop;

  5  commit;

  6  end;

  7  /

*************************************************************

sql>conn /as sysdba;

SQL>  select *
from v$recover_file;

no rows selected

SQL> column name format a40

SQL> select name,file#,status from v$datafile;

NAME                                          FILE# STATUS

—————————————- ———-
——-

/u01/oracle/oradata/ORA-DATA3/system01.dbf             1 SYSTEM

/u01/oracle/oradata/ORA-DATA3/undotbs01.dbf            2 ONLINE

/u01/oracle/oradata/ORA-DATA3/sysaux01.dbf             3 ONLINE

/u01/oracle/oradata/ORA-DATA3/users01.dbf              4 ONLINE

SIMULATE THE FAILURE OF SYSTEM DATAFILE LOSS

[oracle@sku ORA-DATA3]$ clear

[oracle@sku ORA-DATA3]$ pwd

/u01/oracle/oradata/ORA-DATA3

[oracle@sku ORA-DATA3]$ ls -l syst*

-rw-r—— 1 oracle oinstall 503324672 Sep 14 23:37
system01.dbf

[oracle@sku ORA-DATA3]$ ls

control01.ctl     
control04.ctl      redo01.log   redo05a.log 
sysaux01.dbf

control01.ctl_old 
control04.ctl_old  redo02a      redo05.log   system01.dbf

control02.ctl     
example01.dbf     
redo02a.log  redo06a.log  temp01.dbf

control02.ctl_old 
indx01.dbf         redo02.log   redo06.log  
undotbs01.dbf

control03.ctl     
redo01a           
redo03a.log  redo07a.log  users01.dbf

control03.ctl_old 
redo01a.log        redo03.log   redo07.log

[oracle@sku ORA-DATA3]$ mv
system01.dbf system01.dbf_old

[oracle@sku ORA-DATA3]$

*********************************************

SQL> shutdown immediate

ORA-01116: error in opening database file 1

ORA-01110: data file 1: ‘/u01/oracle/oradata/ORA-DATA3/system01.dbf’

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or
directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size                  2020128 bytes

Variable Size            
301993184 bytes

Database Buffers         
771751936 bytes

Redo Buffers              
14753792 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 —
see DBWR trace file

ORA-01110: data file 1:
‘/u01/oracle/oradata/ORA-DATA3/system01.dbf’

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size                  2020128 bytes

Variable Size            
301993184 bytes

Database Buffers         
771751936 bytes

Redo Buffers              
14753792 bytes

Database mounted.

SQL>

SQL> select * from v$recover_file;

     FILE#
ONLINE  ONLINE_

———- ——- ——-

ERROR                                                               
CHANGE#

——————————————————————
———-

TIME

———

         1
ONLINE  ONLINE

                                                                     
535300

15-SEP-11

Restore the system01.dbf from
Offline Backup

[oracle@sku ORA-DATA3]$ pwd

/u01/oracle/oradata/ORA-DATA3

$cp
/u01/oracle/offline_backup/system01.dbf .  
##(This will also work if you restore system01.dbf from online backup)

[oracle@sku ORA-DATA3]$ ls -l *dbf

-rw-r—— 1 oracle oinstall 104865792 Sep 14 23:50
example01.dbf

-rw-r—— 1 oracle oinstall   4202496 Sep 14 23:50 indx01.dbf

-rw-r—— 1 oracle oinstall 251666432 Sep 14 23:50
sysaux01.dbf

-rw-r—— 1 oracle oinstall 503324672 Sep 14 23:56
system01.dbf

-rw-r—— 1 oracle oinstall  20979712 Sep 14 20:12 temp01.dbf

-rw-r—— 1 oracle oinstall  26222592 Sep 14 23:50 undotbs01.dbf

-rw-r—— 1 oracle oinstall  10493952 Sep 14 23:50 users01.dbf

******************

[oracle@sku 2011_09_14]$ pwd

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15

[oracle@sku 2011_09_15]$ ls -alrt

total 33708

drwxr-x— 3 oracle oinstall     4096 Sep 15 02:47 ..

-rw-r—— 1 oracle oinstall 34460160 Sep 15 02:47
o1_mf_1_1_7726hlvy_.arc

-rw-r—— 1 oracle oinstall     1024 Sep 15 02:48 o1_mf_1_2_7726j9bo_.arc

drwxr-x— 2 oracle oinstall     4096 Sep 15 02:48 .

*************************************

SQL> recover database;

ORA-00279: change 680758 generated at 09/14/2011 20:31:38
needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_9_%u_.arc

ORA-00280: change 680758 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO |
CANCEL}

AUTO

ORA-00279: change 681408 generated at 09/14/2011 20:45:01
needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_10_%u_.arc

ORA-00280: change 681408 for thread 1 is in sequence #10

ORA-00278: log file

‘/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_9_771kpqbq_.

arc’ no longer needed for this recovery

ORA-00279: change 681427 generated at 09/14/2011 20:53:03
needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_11_%u_.arc

ORA-00280: change 681427 for thread 1 is in sequence #11

ORA-00278: log file

‘/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_10_771kpqrc_

.arc’ no longer needed for this recovery

ORA-00279: change 684925 generated at 09/14/2011 22:00:07
needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_12_%u_.arc

ORA-00280: change 684925 for thread 1 is in sequence #12

ORA-00278: log file

‘/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_11_771onh9m_

.arc’ no longer needed for this recovery

Log applied.

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from v$recover_file;

no rows selected

SQL> select file#,name,status from v$datafile;

     FILE#
NAME                                    
STATUS

———- —————————————-
——-

         1
/u01/oracle/oradata/ORA-DATA3/system01.dbf   
SYSTEM

         2
/u01/oracle/oradata/ORA-DATA3/undotbs01.dbf  
ONLINE

         3
/u01/oracle/oradata/ORA-DATA3/sysaux01.dbf   
ONLINE

         4
/u01/oracle/oradata/ORA-DATA3/users01.dbf    
ONLINE

===============================================

IF

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1:
‘/opt/oracle/oradata/orcl/system01.dbf’

SQL> recover datafile 1;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL>

Источник

Если вы не можете открыть базу данных потому, что получили вот такой подарок:

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/mydbs/temp0.dbf '
ORA-07366: sfifi: invalid file, file does not have valid header block.

то не стоит расстраиваться. Эта проблема решается довольно просто.

Эти ошибки означают, что поврежден файл временного табличного пространства. В этом ничего страшного нет. Вы же знаете, что во временном табличном пространстве не содержаться объекты, без которых база данных не может существовать. А это значит, что пространство можно удалить и создать новое. Поступаем следующим образом:

STARTUP MOUNT
ALTER DATABASE DATAFILE '/mydbs/temp0.dbf' OFFLINE DROP;
ALTER DATABASE OPEN;
DROP TABLESPACE temp;
CREATE TABLESPACE temp DATAFILE '/mydbs/temp0.dbf '  SIZE 500M REUSE TEMPORARY;

Вас заинтересует / Intresting for you:

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка openvpn android
  • Ошибка openssl fatal
  • Ошибка openldap при запросе
  • Ошибка openldap при gssapi соединения
  • Ошибка openiv не имеет доступа к выбранной директории