Error ORA-01110, ORA-01115 and ORA-27061

[Former Member]

Hi gurus,

I woke up this morning and my DEV system is down.

I'm facing an error when i try to start the database, the error is:

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 9019539456 bytes

Fixed Size                  2220680 bytes

Variable Size            4630515064 bytes

Database Buffers         4362076160 bytes

Redo Buffers               24727552 bytes

Database mounted.

ORA-01110: data file 22: '/oracle/ED0/sapdata1/system_2/system.data2'

ORA-01115: IO error reading block from file 22 (block # 1)

ORA-27061: waiting for async I/Os failed

Linux-x86_64 Error: 5: Input/output error

Additional information: -1

Additional information: 8192

When i se the trans.log:

4 ETW000  [dbsloci.	,00000]  *** ERROR => CONNECT failed with sql error '1033'	35  0.023734
4 ETW000  [dev trc	,00000]	set_ocica() -> SQL error code 1033	12  0.023746
4 ETW000  [dev trc	,00000]  -->oci_get_errmsg (con_hdl=0, rc=1033)	9  0.023755
4 ETW000  [dev trc	,00000]	OCIErrorGet -> SQL error code: 1033	44  0.023799
4 ETW000  [dev trc	,00000]	ORA-01033: ORACLE initialization or shutdown in progress
4 ETW000	13  0.023812
4 ETW000  [dev trc	,00000]  SAPUSER or connect error 99=DBSL_ERR_DB, oerr=1033.	11  0.023823
4 ETW000  [dblink	,00433]  ***LOG BY2=>sql error 1033   performing CON [dblink#4 @ 433]	25  0.023848
4 ETW000  [dblink	,00433]  ***LOG BY0=>ORA-01033: ORACLE initialization or shutdown in progress [dblink#4 @ 433]
4 ETW000	13  0.023861
2EETW169 no connect possible: "DBMS = ORACLE	--- dbs_ora_tnsname = 'ED0'"

Thanks in advanced.

Cleiton Folster Eli.

[Former Member · Accepted solution]

Hi Cleiton,

It could be an authorization issue or check weather the datafile is existing or not

Thanks,
Pavan

[Former Member · Accepted solution]

Hi Pavan,

I gave the permission 777 to file system.data2 and i started the database.

Now appears the error:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

When i execute the command ALTER DATABASE OPEN RESETLOGS; appears the error:

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/ED0/sapdata1/system_1/system.data1'

Thanks for the help!!!

Cleiton.

[former_member185239 · Accepted solution]

Hi Cleiton,

Run the sql query

Alter database recover;

With Regards

Ashutosh

[Former Member · Accepted solution]

Hi,

Execute below commands:

1) recover database;

2) alter database open; OR alter database open resetlogs;

Paste the error, if any.

Regards,

Nick Loy

[Brindavan_M]

Once again check the filesystem is mounted or not and the permission as well.

First try the below command

SQL> recover database

If the result is like below

ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done.

shut down the database

SOLUTION ;

1) shut down the database

2) Add following line to INIT.ORA file

_allow_resetlogs_corruption=true

3) start the database

Execute the below query

4) SQL>alter database open resetlogs;

Note if your system running with spfile, then take the backup of spfile& pfile. After the edit the initiSID.ora file and insert the line as mentioned. Create spfile from pfile, the start the database.

Thanks,

Brindavan

[fidel_vales]

Note that if you do this:

2) Add following line to INIT.ORA file
_allow_resetlogs_corruption=true

your DB will be logically corrupted and not supported. after you do this you must export all the data and import it in a newly created DB

[Brindavan_M]

Yes.. vales,

Thanks for you valuable inputs.

[Former Member]

There may several reasons.Try following:

1.Shut down your database and copy SYSAUX01.DBF datafile other hard drive for example c:\oracle\SYSAUX01.DBF.And

sqlplus "/as sysdba"

startup mount;

alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF'

to 'c:\oracle\SYSAUX01.DBF';

alter database open;

2.If this is block corruption then you need recover your database as following:

a).If you have rman backup then

   rman target /

   blockrecover datafile 3 block 36113;

b).If you have cold backup and your database running in archivelog mode then restore backup of sysaux tablespace then recover this:

recover tablespace sysaux;

Or visit the follow the link http://oracle.filerepairtool.net/blog/fix-oracle-error-ora-01115

[Former Member]

Hi gurus,

The costumer asking me to start de DB again, but i'm facing the same error:

ERROR at line 1:

ORA-01115: IO error reading block from file 3 (block # 26543)

ORA-01110: data file 3: '/oracle/ED0/sapdata1/sysaux_1/sysaux.data1'

ORA-27061: waiting for async I/Os failed

Linux-x86_64 Error: 5: Input/output error

Additional information: -1

Additional information: 8192

When i do Alter database recover; i receive the follow error:

SQL> Alter database recover;

Alter database recover

*

ERROR at line 1:

ORA-01115: IO error reading block from file 3 (block # 26543)

ORA-01110: data file 3: '/oracle/ED0/sapdata1/sysaux_1/sysaux.data1'

ORA-27061: waiting for async I/Os failed

Linux-x86_64 Error: 5: Input/output error

Additional information: -1

  Additional information: 8192

------------------------------------------------------------------------------------------------------

When i do recover database; i receive the follow error:

ORA-01115: IO error reading block from file 3 (block # 26543)

ORA-01110: data file 3: '/oracle/ED0/sapdata1/sysaux_1/sysaux.data1'

ORA-27061: waiting for async I/Os failed

Linux-x86_64 Error: 5: Input/output error

Additional information: -1

  Additional information: 8192

------------------------------------------------------------------------------------------------------

When i do alter database open resetlogs; i receive the follow error:

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

------------------------------------------------------------------------------------------------------

I think we need to make a restore of the /oracle/ED0/sapdata1/sysaux_1/sysaux.data1 file.

You have other solution?

Thanks all for the help.

Best regards,

Cleiton Folster Eli.

[Former Member]

Hi,

Try below:

1) shutdown immediate;

startup mount;

alter database end backup;

startup open;

2) shutdown immediate;

startup mount;

recover database;

alter database open;

Take a look at SAP Note 4161 and 4162.

Regards,

Nick Loy

[Former Member]

Hi Cleiton,

As it says its database recovery error, try to recover the complete database by using the query

SQL>recover database;

or only that data file by the query

SQL>recover datafile '/oracle/ED0/sapdata1/sysaux_1/sysaux.data1';

put the data files drive in the data file path.

Thanks,

Pavan

[Former Member]

Hi Cleiton

1) Perform a full db backup of your database

2) Execute statement ALTER DATABASE DATAFILE '/oracle/ED0/sapdata1/sysaux_1/sysaux.data1' OFFLINE;

3) Check volume /oracle/ED0/ for any disk errors, and clear them if have.

4) Restore '/oracle/ED0/sapdata1/sysaux_1/sysaux.data1' file from a backup that the problem has not yet happened;

5) Be sure that you have all archivelogs since it, and restore them if they are already archived to your tape or backup utility;

6) execute statement RECOVER DATAFILE  '/oracle/ED0/sapdata1/sysaux_1/sysaux.data1' - archivelogs may be asked on this step

7) Execute statement ALTER DATABASE DATAFILE '/oracle/ED0/sapdata1/sysaux_1/sysaux.data1' ONLINE;

Hope it helps

Regards

Marcelo

[Former Member]

Hi gurus,

Thanks for the helps.

In contact with the customer, the server has a hardware problems.

They are verifying.

Any news i will reply to you.

Thanks again.

[Former Member]

Hi Celton,

before doing that you have to recover the data file by using the following query

SQL>recover datafile "<datafile_path>";

do this for all the data files on your DB (from data1 to datan).

.

Thanks,

Pavan