3113 我也遇见过,数据库启动不同阶段,有不同解决办法.metalink写的很详细了
(A) ORA-3113 when attempting to STARTUP Oracle
There are several phases involved in starting up a database. If ORA-3113
occurs during startup then abort the instance and start up using the
sequence below. If an error occurs at any step then see the related notes
below.
a. Start any required services. On error see A1
Eg: On NT start the OracleServiceSID
b. Connect as a SYSDBA user. On error see A1
Eg: sqlplus /nolog
SQL> connect / as sysdba
c. Startup nomount. On error see A1
Eg:
SQL> startup nomount
d. Mount the database. On error see A1 and A2
Eg:
SQL> alter database mount;
e. Recover the database On error see A3
Eg:
SQL> recover database
f. Open the database On error see A4
Eg:
SQL> alter database open;
g. Wait 3 minutes then issue a select. On error see A4
Eg:
SQL> select count(*) from DBA_OBJECTS;
A1) Errors connecting as SYSDBA / Internal OR on startup nomount
There is something fundamental wrong with the software / environment
if you cannot connect to Server Manager as a DBA user.
The steps here cover errors such as ORA-3113, ORA-12547: TNS:lost contact
or similar errors connecting to Oracle or starting the instance NOMOUNT.
Check the following items:
A1.1) If possible reboot the server disabling any automatic
startup of Oracle before you do so. This may seem drastic
but helps make sure you are working from a consistent
starting point.
A1.2) Check your environment points at the expected ORACLE_HOME
and ORACLE_SID and that TWO_TASK is not set (Unix) or
LOCAL is not set (NT registry).
Check the USER_DUMP_DEST and BACKGROUND_DUMP_DEST and default
trace directories under this environment for any user trace
files or alert log entries generated. These may help indicate
the cause of the problem.
Eg: ORA-600[SKGMINVALID] may indicate a problem with the
shared memory Unix parameters on Unix systems.
Try to show that any trace file / alert log entry you
find is truely related to the "CONNECT" command by re-issuing
the "connect" and checking for a new trace file / alert entry
at the time of the error.
A1.3) Unix only:
Some Unix platforms need LD_LIBRARY_PATH to be set
correctly to resolve any dynamically linked libraries.
As the user with the problem:
% script. /tmp/ldd.out
% id
% cd $ORACLE_HOME/bin
% ldd oracle
% exit
If the 'ldd' command does not exist go to the next step below.
Check that all lines listed show a full library file. If there
are any 'not found' lines reported contact Oracle support
with the output of /tmp/ldd.out .
A1.4) Unix only:
Your 'oracle' executable may be corrupt. Relink it thus:
Log in as the 'oracle' user.
% script. /tmp/relink.out
% cd $ORACLE_HOME/rdbms/lib
% mv $ORACLE_HOME/bin/oracle $ORACLE_HOME/bin/oracle.dd.mon.yy
% rm -f ./oracle
% make -f ins_rdbms.mk ioracle
% exit
Prior to Oracle7.3 the relink command was:
make -f oracle.mk ioracle
If this reports any errors Oracle support will need to see
the contents of the file /tmp/relink.out .
A1.5) Have you installed the Parallel Server Option ?
ORA-3113 can occur if you have installed the Parallel
Server Option but do NOT have a Distributed Lock Manager
installed or running correctly.
Unix:
If the Parallel Server Option was installed by accident
then it can be de-installed by relinking.
Eg:
Shut down any Oracle instances
% script. /tmp/relink.out
% cd $ORACLE_HOME/rdbms/lib
# 'oracle' should not exist so delete it if it present
% rm -f oracle
% make -f ins_rdbms.mk no_parropt ioracle
% exit
NB: Do NOT deinstall the Parallel Server Option if the
database is using Parallel Server unless both nodes
are shut down otherwise database corruption could occur.
A1.6) If the error is on STARTUP NOMOUNT:
Check the init.ora file used to start the database.
This provides the configuration details used
configure the instance. To help isolate the problem
it may be useful use a very basic init.ora file
when starting the instance. If this works then
parameters can be increased / introduced one at a
time to see if there is a problem with a particular
setting.
A1.7) Check for server side trace files which may give more
indication what the underlying problem is.
See section C for details on how to check
for server trace files.
A1.8) Ensure there is free disk space in:
a. Your USER_DUMP_DEST and BACKGROUND_DUMP_DEST locations
b. Your AUDIT destination (Unix)
The default is $ORACLE_HOME/rdbms/audit
c. Your Oracle Trace directory if Oracle Trace is enabled
See Note 45482.1
A1.9) Windows 2000 - Only
If the Server's sqlnet.ora file contains Authnetication services
which are NOT reachable by Oracle, then an ORA-3113 error will
result.
For example, if the sqlnet.ora file contains the parameter:
SQLNET.AUTHENTICATION_SERVICES = (NTS) and the Oracle database
is moved from a Windows NT Domain to an Active Directory one,
or if a Domain Controller is introduced, then an error will
result trying to start the database.
Remove the sqlnet.authentication_services line so that Oracle
does not look for a non-existent KDC (Kerberos Domain Controller).
A2) Errors Mounting the database
Check all the items in A1 first.
If an error occurs when mounting the database there may be problems
with the control-files or data files, or with resources required to
open these files.
A2.1) The location of the control files are specified in the
init.ora file. Try mounting using each controlfile in
turn.
eg: "Shutdown abort",
edit the init.ora to refer to ONE of the controlfiles only,
"startup nomount",
"alter database mount"
Repeat for each controlfile to see if any controlfile works.
A2.2) It is possible to re-create the controlfiles if you know the
location of all datafiles and online logs, or to restore an old
backup controlfile. Always back up the current controlfiles before
restoring any backup copies or issuing a CREATE CONTROLFILE
command.
The steps for this are not documented here.
A2.3) Unix:
Some unix platforms have a 'truss' command (or 'tusc').
If available this can be used to help trace how far Oracle
gets before the error occurs.
Eg:
% truss -o /tmp/truss.out -f svrmgrl
Keep the file /tmp/truss.out safe - Oracle Support MAY need to see it.
A3) Errors on RECOVER DATABASE
ORA-3113 during recover database is often related to a corruption on the
database or redo stream which causes the shadow process to die. There should
be a server side trace file produced for this sort of problem.
See Section C for details on how to locate any trace files
from both USER_DUMP_DEST and BACKGROUND_DUMP_DEST.
A3.1) If the "recover database" fails fairly quickly then it
may help to collect the redo up to the point of failure as this
may help identify where the problem is.
Use the following commands just prior to the RECOVER DATABASE
command:
SQL> alter session set max_dump_file_size=unlimited;
SQL> alter session set events
2> '10228 trace name context forever, level 10';
SQL> RECOVER DATABASE
This causes redo information to be written to the user trace
file. The last items of redo may help determine which file
has problems.
A3.2) If you do not have many datafiles in the database it may be
as quick to recover each file in turn to see if this narrows
down the problem.
Eg:
SQL> select name from v$datafile;
and then for each file:
SQL> RECOVER DATAFILE 'full_file_name'
If this gets to a problem file then back up the file and
use standard recovery options as if the file was lost.
A4) Errors on ALTER DATABASE OPEN
Database open performs very many operations and so it is necessary
to collect any trace information before determining the next steps.
However, the following may help isolate the problem more quickly:
A4.1) Move files out of your USER_DUMP_DEST and BACKGROUND_DUMP_DEST
directory as these steps will generate a lot of trace.
A4.2) Edit the init.ora file and add the lines:
event="10046 trace name context forever, level 12"
event="10015 trace name context forever, level 1"
event="10228 trace name context forever, level 1"
If you already have "EVENT=" lines in the init.ora file
this MUST directly below the other "Event=" lines.
These lines will trace:
SQL and BIND activity during startup
REDO applied
Information about transactional rollback required
A4.3) Startup the instance as described at the top of this section.
As soon as the error occurs REMOVE the above events from the init.ora
file and shutdown. Collect together the trace files and alert logs
as described in Section C
(A) ORA-3113 when attempting to STARTUP Oracle
There are several phases involved in starting up a database. If ORA-3113
occurs during startup then abort the instance and start up using the
sequence below. If an error occurs at any step then see the related notes
below.
a. Start any required services. On error see A1
Eg: On NT start the OracleServiceSID
b. Connect as a SYSDBA user. On error see A1
Eg: sqlplus /nolog
SQL> connect / as sysdba
c. Startup nomount. On error see A1
Eg:
SQL> startup nomount
d. Mount the database. On error see A1 and A2
Eg:
SQL> alter database mount;
e. Recover the database On error see A3
Eg:
SQL> recover database
f. Open the database On error see A4
Eg:
SQL> alter database open;
g. Wait 3 minutes then issue a select. On error see A4
Eg:
SQL> select count(*) from DBA_OBJECTS;
A1) Errors connecting as SYSDBA / Internal OR on startup nomount
There is something fundamental wrong with the software / environment
if you cannot connect to Server Manager as a DBA user.
The steps here cover errors such as ORA-3113, ORA-12547: TNS:lost contact
or similar errors connecting to Oracle or starting the instance NOMOUNT.
Check the following items:
A1.1) If possible reboot the server disabling any automatic
startup of Oracle before you do so. This may seem drastic
but helps make sure you are working from a consistent
starting point.
A1.2) Check your environment points at the expected ORACLE_HOME
and ORACLE_SID and that TWO_TASK is not set (Unix) or
LOCAL is not set (NT registry).
Check the USER_DUMP_DEST and BACKGROUND_DUMP_DEST and default
trace directories under this environment for any user trace
files or alert log entries generated. These may help indicate
the cause of the problem.
Eg: ORA-600[SKGMINVALID] may indicate a problem with the
shared memory Unix parameters on Unix systems.
Try to show that any trace file / alert log entry you
find is truely related to the "CONNECT" command by re-issuing
the "connect" and checking for a new trace file / alert entry
at the time of the error.
A1.3) Unix only:
Some Unix platforms need LD_LIBRARY_PATH to be set
correctly to resolve any dynamically linked libraries.
As the user with the problem:
% script. /tmp/ldd.out
% id
% cd $ORACLE_HOME/bin
% ldd oracle
% exit
If the 'ldd' command does not exist go to the next step below.
Check that all lines listed show a full library file. If there
are any 'not found' lines reported contact Oracle support
with the output of /tmp/ldd.out .
A1.4) Unix only:
Your 'oracle' executable may be corrupt. Relink it thus:
Log in as the 'oracle' user.
% script. /tmp/relink.out
% cd $ORACLE_HOME/rdbms/lib
% mv $ORACLE_HOME/bin/oracle $ORACLE_HOME/bin/oracle.dd.mon.yy
% rm -f ./oracle
% make -f ins_rdbms.mk ioracle
% exit
Prior to Oracle7.3 the relink command was:
make -f oracle.mk ioracle
If this reports any errors Oracle support will need to see
the contents of the file /tmp/relink.out .
A1.5) Have you installed the Parallel Server Option ?
ORA-3113 can occur if you have installed the Parallel
Server Option but do NOT have a Distributed Lock Manager
installed or running correctly.
Unix:
If the Parallel Server Option was installed by accident
then it can be de-installed by relinking.
Eg:
Shut down any Oracle instances
% script. /tmp/relink.out
% cd $ORACLE_HOME/rdbms/lib
# 'oracle' should not exist so delete it if it present
% rm -f oracle
% make -f ins_rdbms.mk no_parropt ioracle
% exit
NB: Do NOT deinstall the Parallel Server Option if the
database is using Parallel Server unless both nodes
are shut down otherwise database corruption could occur.
A1.6) If the error is on STARTUP NOMOUNT:
Check the init.ora file used to start the database.
This provides the configuration details used
configure the instance. To help isolate the problem
it may be useful use a very basic init.ora file
when starting the instance. If this works then
parameters can be increased / introduced one at a
time to see if there is a problem with a particular
setting.
A1.7) Check for server side trace files which may give more
indication what the underlying problem is.
See section C for details on how to check
for server trace files.
A1.8) Ensure there is free disk space in:
a. Your USER_DUMP_DEST and BACKGROUND_DUMP_DEST locations
b. Your AUDIT destination (Unix)
The default is $ORACLE_HOME/rdbms/audit
c. Your Oracle Trace directory if Oracle Trace is enabled
See Note 45482.1
A1.9) Windows 2000 - Only
If the Server's sqlnet.ora file contains Authnetication services
which are NOT reachable by Oracle, then an ORA-3113 error will
result.
For example, if the sqlnet.ora file contains the parameter:
SQLNET.AUTHENTICATION_SERVICES = (NTS) and the Oracle database
is moved from a Windows NT Domain to an Active Directory one,
or if a Domain Controller is introduced, then an error will
result trying to start the database.
Remove the sqlnet.authentication_services line so that Oracle
does not look for a non-existent KDC (Kerberos Domain Controller).
A2) Errors Mounting the database
Check all the items in A1 first.
If an error occurs when mounting the database there may be problems
with the control-files or data files, or with resources required to
open these files.
A2.1) The location of the control files are specified in the
init.ora file. Try mounting using each controlfile in
turn.
eg: "Shutdown abort",
edit the init.ora to refer to ONE of the controlfiles only,
"startup nomount",
"alter database mount"
Repeat for each controlfile to see if any controlfile works.
A2.2) It is possible to re-create the controlfiles if you know the
location of all datafiles and online logs, or to restore an old
backup controlfile. Always back up the current controlfiles before
restoring any backup copies or issuing a CREATE CONTROLFILE
command.
The steps for this are not documented here.
A2.3) Unix:
Some unix platforms have a 'truss' command (or 'tusc').
If available this can be used to help trace how far Oracle
gets before the error occurs.
Eg:
% truss -o /tmp/truss.out -f svrmgrl
Keep the file /tmp/truss.out safe - Oracle Support MAY need to see it.
A3) Errors on RECOVER DATABASE
ORA-3113 during recover database is often related to a corruption on the
database or redo stream which causes the shadow process to die. There should
be a server side trace file produced for this sort of problem.
See Section C for details on how to locate any trace files
from both USER_DUMP_DEST and BACKGROUND_DUMP_DEST.
A3.1) If the "recover database" fails fairly quickly then it
may help to collect the redo up to the point of failure as this
may help identify where the problem is.
Use the following commands just prior to the RECOVER DATABASE
command:
SQL> alter session set max_dump_file_size=unlimited;
SQL> alter session set events
2> '10228 trace name context forever, level 10';
SQL> RECOVER DATABASE
This causes redo information to be written to the user trace
file. The last items of redo may help determine which file
has problems.
A3.2) If you do not have many datafiles in the database it may be
as quick to recover each file in turn to see if this narrows
down the problem.
Eg:
SQL> select name from v$datafile;
and then for each file:
SQL> RECOVER DATAFILE 'full_file_name'
If this gets to a problem file then back up the file and
use standard recovery options as if the file was lost.
A4) Errors on ALTER DATABASE OPEN
Database open performs very many operations and so it is necessary
to collect any trace information before determining the next steps.
However, the following may help isolate the problem more quickly:
A4.1) Move files out of your USER_DUMP_DEST and BACKGROUND_DUMP_DEST
directory as these steps will generate a lot of trace.
A4.2) Edit the init.ora file and add the lines:
event="10046 trace name context forever, level 12"
event="10015 trace name context forever, level 1"
event="10228 trace name context forever, level 1"
If you already have "EVENT=" lines in the init.ora file
this MUST directly below the other "Event=" lines.
These lines will trace:
SQL and BIND activity during startup
REDO applied
Information about transactional rollback required
A4.3) Startup the instance as described at the top of this section.
As soon as the error occurs REMOVE the above events from the init.ora
file and shutdown. Collect together the trace files and alert logs
as described in Section C
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8102208/viewspace-483886/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8102208/viewspace-483886/