Pages

Thursday, April 23, 2015

ORA-01113: file string needs media recovery


Question:  I get this error when starting my Oracle database.  What is the ORA-01113 error?


SQL> ALTER TABLESPACE sample
 ONLINE  2  ;
ALTER TABLESPACE sample
*
ERROR at line 1:
ORA-01113: file 27 needs media recovery
ORA-01110: data file 27: '/u02/oracle/oradata/ORCL/sample.dbf'


SQL> recover datafile '/u02/oracle/oradata/ORCL/sample.dbf';
Media recovery complete.
SQL> ALTER TABLESPACE sample ONLINE;
ALTER TABLESPACE sample ONLINE
*
ERROR at line 1:
ORA-01113: file 29 needs media recovery
ORA-01110: data file 29: '/u02/oracle/oradata/ORCL/SAMPLE10.dbf'


SQL> recover datafile '/u02/oracle/oradata/ORCL/SAMPLE10.dbf';
Media recovery complete.
SQL> ALTER TABLESPACE sample ONLINE;
ALTER TABLESPACE sample ONLINE
*
ERROR at line 1:
ORA-01113: file 30 needs media recovery
ORA-01110: data file 30: '/u01/app/QPDEV/SAMPLE11.dbf'


SQL> recover datafile '/u01/app/QPDEV/SAMPLE11.dbf';
Media recovery complete.
SQL> ALTER TABLESPACE sample ONLINE;

Tablespace altered.



The ORA-01113 error can be thrown when you begin to start Oracle with the "startup" command. or sometimes if u take Tablespace offline and try to bring it back online.
 The ORA-01113 has many causes: 

Monday, April 13, 2015

ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener

ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener


When you start or stop your oracle service using dbstart/dbshut scripts on Unix/Linux system and you may get the error ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener error.

[oracle@linux1bin]$ echo $ORACLE_SID
qptest
[oracle@linux1 bin]$ . dbshut
ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener
Usage: -bash ORACLE_HOME

Then you need to edit the “dbstart” & “dbshut” file, the should be located at $ORACLE_HOME\bin
Go through the file and find line
ORACLE_HOME_LISTNER=$1
and change to
ORACLE_HOME_LISTNER=$ORACLE_HOME