Problem Description
While mounting oracle database it fails with ORA-12709 like below.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-12709: error while loading create database character set
Cause of the Problem
The error ORA-12709 is returned due to incorrect setting of environmental variable NLS_LANG
or ORA_NLS33.
Solution of the Problem
Check your NLS_LANG environmental variable by,
$echo $NLS_LANG.
If it is set to wrong value and your database character set is WE8ISO8859P1 then set by,
$export NLS_LANG=American_America.WE8ISO8859P1
Note that, For Oracle7 V7.3.2 version ORA_NLS33 environmental parameter is called ORA_NLS,
for Oracle7 V7.3.3 and V7.3.4 it is called ORA_NLS32,
for Oracle8 it is called ORA_NLS33 because of NLS libraries version.
When using both Oracle8 V8.x and Developer/2000 V1.6.1 in the same Oracle Home, ORA_NLS33 needs to be set to $ORACLE_HOME/ocommon/nls/admin/datad2k
The environmental variable along with database version is given below.
RDBMS 7.2.x -> ORA_NLS
RDBMS 7.3.x -> ORA_NLS32
RDBMS 8.0.x -> ORA_NLS33
RDBMS 8.1.x -> ORA_NLS33
RDBMS 9.X.X -> ORA_NLS33
RDBMS 10.X -> ORA_NLS10
After you set both parameters correctly login as sys user.
$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.8.0 - Production on Sun Sep 26 00:40:18 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Shutdown and Startup the database.
SQL> startup force;
ORACLE instance started.
Total System Global Area 320300808 bytes
Fixed Size 734984 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL>
Showing posts with label Troubleshooting. Show all posts
Showing posts with label Troubleshooting. Show all posts
Sunday, September 26, 2010
Tuesday, August 31, 2010
ORA-13516: AWR Operation failed: SWRF Schema not initialized
Problem Description
While executing dbms_workload_repository package the following error message is reported.
Cause of the Problem
These errors have happened because of wrong or invalid objects with respect to AWR.
Solution of the Problem
In order to resolve the issue you need to drop and recreate the AWR objects, which you can do by running CATNOAWR.SQL and CATAWR.SQL scripts.
Note that from 10.2 onwards, the script name has changed. The catalog script for AWR Tables, used to create the Workload Repository Schema is CATAWRTB.SQL .
In case of Oracle 10gR1,
To re-create the AWR objects, by running the catnoawr.sql and catawr.sql scripts located in $ORACLE_HOME/rdbms/admin.
Note that, the CATNOAWR.SQL scripts is no longer available in 10.2 and cannot be found
in the $ORACLE_HOME/rdbms/admin directory.
While executing dbms_workload_repository package the following error message is reported.
sql> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);
BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>0); END;
ERROR at line 1:
ORA-13516: AWR Operation failed: SWRF Schema not initialized
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133
ORA-06512: at line 1
Cause of the Problem
These errors have happened because of wrong or invalid objects with respect to AWR.
Solution of the Problem
In order to resolve the issue you need to drop and recreate the AWR objects, which you can do by running CATNOAWR.SQL and CATAWR.SQL scripts.
Note that from 10.2 onwards, the script name has changed. The catalog script for AWR Tables, used to create the Workload Repository Schema is CATAWRTB.SQL .
In case of Oracle 10gR1,
To re-create the AWR objects, by running the catnoawr.sql and catawr.sql scripts located in $ORACLE_HOME/rdbms/admin.
SQL> @$ORACLE_HOME/rdbms/admin/catnoawr.sqlIn case of Oracle 10gR2,
SQL> @$ORACLE_HOME/rdbms/admin/catawr.sql
SQL> shut immediate
SQL> startup
SQL> @$ORACLE_HOME/rdbms/admin/CATAWRTB.sqlOn re-start of the database instance, the AWR tables will be populated with the required data.
SQL> shut immediate;
SQL> startup
Note that, the CATNOAWR.SQL scripts is no longer available in 10.2 and cannot be found
in the $ORACLE_HOME/rdbms/admin directory.
Monday, August 2, 2010
ORA-28056: Writing audit records to Windows Event Log failed
Problem Description
Connecting as sysdba fails with ORA-28056.
$ sqlplus / as sysdba
ORA-28056: Writing audit records to Windows Event Log failed.
Cause of the Problem
The problem happened because Event Viewer log is full and not able to log anymore events.
Solution of the Problem
You need to free an event log.
- Log in as a administrator or a member of the Administrators group in order to free an event log.
- Open Event Viewer. To do so click on Start Menu, click Control Panel, click Performance and Maintenance, click Administrative Tools, and then double-click Event Viewer.
Or, click on Start Menu, click Control Panel, click Administrative Tools, and then double-click Event Viewer.
- Now you can follow any of the following steps.
1) When a log is full, it stops recording new events. Clearing the log is one way to free the log and start recording new events. To do so, on the Action menu (left click)or on the left side Application/System/Security (as available) right click and select Clear All events.
2) You can also free a log and start recording new events by overwriting old events. To overwrite events, on the Action menu, or on the left side Application/System/Security (as available) click Properties, and then click Overwrite events as needed. This ensures that all new events are written to the log, even when the log is full.
3) You can also start logging new events by increasing the maximum log size. To increase the log size, on the Action menu or on the left side Application/System/Security (as available) click Properties, and then increase the Maximum log size by typing a bigger value.
Connecting as sysdba fails with ORA-28056.
$ sqlplus / as sysdba
ORA-28056: Writing audit records to Windows Event Log failed.
Cause of the Problem
The problem happened because Event Viewer log is full and not able to log anymore events.
Solution of the Problem
You need to free an event log.
- Log in as a administrator or a member of the Administrators group in order to free an event log.
- Open Event Viewer. To do so click on Start Menu, click Control Panel, click Performance and Maintenance, click Administrative Tools, and then double-click Event Viewer.
Or, click on Start Menu, click Control Panel, click Administrative Tools, and then double-click Event Viewer.
- Now you can follow any of the following steps.
1) When a log is full, it stops recording new events. Clearing the log is one way to free the log and start recording new events. To do so, on the Action menu (left click)or on the left side Application/System/Security (as available) right click and select Clear All events.
2) You can also free a log and start recording new events by overwriting old events. To overwrite events, on the Action menu, or on the left side Application/System/Security (as available) click Properties, and then click Overwrite events as needed. This ensures that all new events are written to the log, even when the log is full.
3) You can also start logging new events by increasing the maximum log size. To increase the log size, on the Action menu or on the left side Application/System/Security (as available) click Properties, and then increase the Maximum log size by typing a bigger value.
Sunday, August 1, 2010
ORA-12640, ORA-21561: OID generation failed
Problem Description
In the sqlnet.ora file there is an entry SQLNET.AUTHENTICATION_SERVICES=NONE and now whenever you connect to database using RMAN it fails with following error.
ORA-21561: OID generation failed
Whenever you change the sqlnet.ora entry to SQLNET.AUTHENTICATION_SERVICES=NTS, it fails with error ORA-12640: Authentication adapter initialization failed.
Cause of the Problem
The problem happened due to lower value of Windows SharedSection heap size.
Solution of the Problem
1. Click on start menu on your windows machine and then go to Run.
2. Type regedit in the Run box and press Enter.
3. In the Registry Editor window navigate to
\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems\
You will see key name Windows.
4. Right click on windows name and select modify. You should see value data field containing an entry like below.
%SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,3072,512 Windows=On SubSystemType=Windows ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3 ServerDll=winsrv:ConServerDllInitialization,2 ProfileControl=Off MaxRequestThreads=16
From the field value data the parameter SharedSection's third value (512) is the size of the desktop heap for each desktop that is associated with a "noninteractive" window station.
Increase the desktop heap size that is modify the third value to 1024, so the values are now listed as 1024, 3072, 1024.
After you modify the entry try to connect to database using RMAN and it should resolve the problem.
In the sqlnet.ora file there is an entry SQLNET.AUTHENTICATION_SERVICES=NONE and now whenever you connect to database using RMAN it fails with following error.
ORA-21561: OID generation failed
Whenever you change the sqlnet.ora entry to SQLNET.AUTHENTICATION_SERVICES=NTS, it fails with error ORA-12640: Authentication adapter initialization failed.
Cause of the Problem
The problem happened due to lower value of Windows SharedSection heap size.
Solution of the Problem
1. Click on start menu on your windows machine and then go to Run.
2. Type regedit in the Run box and press Enter.
3. In the Registry Editor window navigate to
\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems\
You will see key name Windows.
4. Right click on windows name and select modify. You should see value data field containing an entry like below.
%SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,3072,512 Windows=On SubSystemType=Windows ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3 ServerDll=winsrv:ConServerDllInitialization,2 ProfileControl=Off MaxRequestThreads=16
From the field value data the parameter SharedSection's third value (512) is the size of the desktop heap for each desktop that is associated with a "noninteractive" window station.
Increase the desktop heap size that is modify the third value to 1024, so the values are now listed as 1024, 3072, 1024.
After you modify the entry try to connect to database using RMAN and it should resolve the problem.
General Solutions of ORA-12547: TNS: lost contact
In the post Troubleshoot ORA-12547: TNS: lost contact it is already discussed about the specific causes and solutions of ORA-12547: TNS: lost contact. If you don't have those specific scenarios then this post will provide you general solutions for error ORA-12547 which might help you to avoid this error.
1. Verify that environmental variable ORACLE_HOME, ORACLE_SID, PATH, LD_LIBRARY_PATH are correct.
$ echo $ORACLE_HOME
$ echo $ORACLE_SID
$ echo $LD_LIBRARY_PATH
$ echo $PATH
2. On AIX, HP-UX, Linux, MacOSX, Solaris, Tru64 system make sure kernel parameters settings are ok. To set, check and modify your kernel parameters have a look at the post How to check and modify Kernel Parameters in Linux
and Install Oracle on Linux.
3. There may have incorrect permissions on the ORACLE.exe
The 'ls' command should show permissions 6751 (as follows)
$ cd $ORACLE_HOME/bin
$ ls -l oracle
The output should be
-rwsr-s--x 1 oracle dba
If not then please execute the following
$ chmod 6751 oracle
4. Check whether current ulimit settings is ok.
$ ulimit -a
A sample output is,
$ulimit -n 4096
5. Check the permission on the library files under $ORACLE_HOME/lib and $ORACLE_HOME/lib32 are ok. Since since sqlplus and other binaries require these libraries so if sqlplus does not have permission on these file it may throw ORA-12517: TNS: lost contact. It should have 755 on unix system.
You can give 755 permissions to $ORACLE_HOME/lib and $ORACLE_HOME/lib32
$ chmod -R 755 $ORACLE_HOME/lib
$ chmod -R 755 $ORACLE_HOME/lib32
$ cd $ORACLE_HOME/bin
$ relink all
1. Verify that environmental variable ORACLE_HOME, ORACLE_SID, PATH, LD_LIBRARY_PATH are correct.
$ echo $ORACLE_HOME
$ echo $ORACLE_SID
$ echo $LD_LIBRARY_PATH
$ echo $PATH
2. On AIX, HP-UX, Linux, MacOSX, Solaris, Tru64 system make sure kernel parameters settings are ok. To set, check and modify your kernel parameters have a look at the post How to check and modify Kernel Parameters in Linux
and Install Oracle on Linux.
3. There may have incorrect permissions on the ORACLE.exe
The 'ls' command should show permissions 6751 (as follows)
$ cd $ORACLE_HOME/bin
$ ls -l oracle
The output should be
-rwsr-s--x 1 oracle dba
If not then please execute the following
$ chmod 6751 oracle
4. Check whether current ulimit settings is ok.
$ ulimit -a
A sample output is,
$ ulimit -aIf open files (-n) is set to lower then make it unlimited or set it to a higher value, for example:
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
open files (-n) 256
pipe size (512 bytes, -p) 10
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 29995
virtual memory (kbytes, -v) unlimited
$ulimit -n 4096
5. Check the permission on the library files under $ORACLE_HOME/lib and $ORACLE_HOME/lib32 are ok. Since since sqlplus and other binaries require these libraries so if sqlplus does not have permission on these file it may throw ORA-12517: TNS: lost contact. It should have 755 on unix system.
You can give 755 permissions to $ORACLE_HOME/lib and $ORACLE_HOME/lib32
$ chmod -R 755 $ORACLE_HOME/lib
$ chmod -R 755 $ORACLE_HOME/lib32
$ cd $ORACLE_HOME/bin
$ relink all
Saturday, July 31, 2010
Troubleshoot ORA-12547: TNS: lost contact
Problem Description
Whenever you connect to database as sysdba it fails with ORA-12517 like below.
ORA-12547 indicates that the communication channel has been broken. Based on the scenarios cause and solution of the problem is discussed below.
1)Local connection as sysdba (Bequeath):
Problem
BEQ connection fails when connecting with / as sysdba
Cause
Oracle binaries have not been linked correctly
Solution
Relink the Oracle binaries by executing the following command,
$ ORACLE_HOME/bin relink all
2)Bequeath connections fail with ora-12547 and Remote connections fail with ORA-12500:
From the listener.log file it shows,
Cause
This is oracle bug 2654576 and happened due to the processes value reaches a limit.
Solution
i) The bug is fixed in patch set 9.2.0.3 and in 10g. So upgrade oracle database is a solution.
ii) Increase the PROCESSES parameter in the init.ora to a higher value for example 2000.
iii) If increasing the PROCESSES parameter does not help then increase the value of the hidden parameter.
_attach_count_slack = 2000
3) Remote connections to the database server fail with ORA-12547
Cause 1
SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name is set in the database server's sqlnet.ora and listener.ora. If the client fails to establish a connection and complete authentication in the time specified defined by these parameter, then the database server terminates the connection.
If this is the case and your oracle database version is 10g and higher then ORA-3136 errors is reported in the alert.log.
Cause 2
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the failing client.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the failing client.
Solution 1
If problem happened due to cause 1 then SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name to appropriate values.
Note that the database server and the listener has to be restarted for these parameters to take effect.
Solution 2
For cause 2 either add the IP address of the failing client in the TCP.INVITED_NODES list or remove it from the TCP.EXCLUDED_NODES list.
It is recommended to restart the database server and the listener for these parameters to take effect.
4) Listener fails to start with Oracle error ORA-12547
Cause
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the failing client.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the failing client.
Solution
Either add the IP address of the failing client in the TCP.INVITED_NODES list or remove it from the TCP.EXCLUDED_NODES list.
It is recommended to restart the database server and the listener for these parameters to take effect.
5) After changing /etc/system and rebooting system, the 10g R2 TNS Listener fails to start
This one is happened due to a DNS client process, 'BIND 9' not running/started on the Solaris 10 Server.
Solution
You need to start the BIND 9 DNS Client on the Solaris10 Server.
Whenever you connect to database as sysdba it fails with ORA-12517 like below.
$ sqlplus / as sysdbaCause of the Problem
SQL*Plus: Release 10.2.0.2.0 - Production on Sun Aug 01 11:27:27 2010
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
ERROR:
ORA-12547: TNS:lost contact
Enter user-name:
ORA-12547 indicates that the communication channel has been broken. Based on the scenarios cause and solution of the problem is discussed below.
1)Local connection as sysdba (Bequeath):
Problem
BEQ connection fails when connecting with / as sysdba
Cause
Oracle binaries have not been linked correctly
Solution
Relink the Oracle binaries by executing the following command,
$ ORACLE_HOME/bin relink all
2)Bequeath connections fail with ora-12547 and Remote connections fail with ORA-12500:
From the listener.log file it shows,
TNS-12500: TNS:listener failed to start a dedicated server processThe version of the database server is 9.2.0.2
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Cause
This is oracle bug 2654576 and happened due to the processes value reaches a limit.
Solution
i) The bug is fixed in patch set 9.2.0.3 and in 10g. So upgrade oracle database is a solution.
ii) Increase the PROCESSES parameter in the init.ora to a higher value for example 2000.
iii) If increasing the PROCESSES parameter does not help then increase the value of the hidden parameter.
_attach_count_slack = 2000
3) Remote connections to the database server fail with ORA-12547
Cause 1
SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name is set in the database server's sqlnet.ora and listener.ora. If the client fails to establish a connection and complete authentication in the time specified defined by these parameter, then the database server terminates the connection.
If this is the case and your oracle database version is 10g and higher then ORA-3136 errors is reported in the alert.log.
Cause 2
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the failing client.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the failing client.
Solution 1
If problem happened due to cause 1 then SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name to appropriate values.
Note that the database server and the listener has to be restarted for these parameters to take effect.
Solution 2
For cause 2 either add the IP address of the failing client in the TCP.INVITED_NODES list or remove it from the TCP.EXCLUDED_NODES list.
It is recommended to restart the database server and the listener for these parameters to take effect.
4) Listener fails to start with Oracle error ORA-12547
Cause
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the failing client.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the failing client.
Solution
Either add the IP address of the failing client in the TCP.INVITED_NODES list or remove it from the TCP.EXCLUDED_NODES list.
It is recommended to restart the database server and the listener for these parameters to take effect.
5) After changing /etc/system and rebooting system, the 10g R2 TNS Listener fails to start
$ lsnrctl startCause
Starting /u01/oracle/product/10.2.0/Db_1/bin/tnslsnr: please wait...
TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
System parameter file is /u01/oracle/product/10.2.0/Db_1/network/admin/listener.ora
Log messages written to /u01/oracle/product/10.2.0/Db_1/network/log/listener.log
Trace information written to /u01/oracle/product/10.2.0/Db_1/network/trace/listener.trc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server11)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc10)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server11)(PORT=1521)))
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Solaris Error: 131: Connection reset by peer
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc10)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Solaris Error: 146: Connection refused
This one is happened due to a DNS client process, 'BIND 9' not running/started on the Solaris 10 Server.
Solution
You need to start the BIND 9 DNS Client on the Solaris10 Server.
Saturday, February 27, 2010
EXP-00008, ORA-04063, ORA-06508, EXP-00083 PL/SQL: could not find program unit
Problem Description
Oracle full database export fails with error stack EXP-00008, ORA-04063, ORA-06508, EXP-00083 like below.
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling WMSYS.LT_EXPORT_PKG.schema_info_exp
Cause of the Problem
The above problems are caused by the revoke of the execute privilege on UTL_FILE package from Public.
Oracle Workspace Manager (OWM) and some other database components (which are not installed in the database) need the privilege to execute procedure SYS.UTL_FILE.
During the creation of a default Database, the Workspace Manager gets installed by default. and the WMSYS user becomes intertwined with the export process and must be valid or at least the major parts for an export to complete properly as the export process looks for any "version-enabled" tables that Workspace Manager schema may be using.
Solution of the Problem
In order to solve above problems do following steps.
1. Grant execute privilege on SYS.UTL_FILE package to WMSYS user.
SQL> grant execute on SYS.UTL_FILE to WMSYS;
2. check for invalid objects in the WMSYS schema using following query,
SQL> select object_name,object_type,owner,status from dba_objects where status='INVALID' and owner='WMSYS';
3. Run script $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
Related Documents
http://arjudba.blogspot.com/2010/02/exp-00008-ora-06550-pls-00201-exp-00083.html
http://arjudba.blogspot.com/2010/02/ora-39127-ora-04063-ora-06508-ora-06512.html
http://arjudba.blogspot.com/2010/02/exp-00008-ora-04063-ora-06508-exp-00083.html
http://arjudba.blogspot.com/2009/12/export-fully-fails-with-pls-00201-ora.html
http://arjudba.blogspot.com/2009/12/export-fails-with-exp-00002-error-in.html
http://arjudba.blogspot.com/2009/01/ora-31655-no-data-or-metadata-objects.html
http://arjudba.blogspot.com/2009/01/expdp-fails-with-ora-31693-ora-06502.html
http://arjudba.blogspot.com/2008/12/ora-39095-dump-file-space-has-been.html
http://arjudba.blogspot.com/2008/09/expdp-fails-with-ora-31626-ora-31633.html
http://arjudba.blogspot.com/2008/07/data-pump-export-fails-with-ora-39000.html
http://arjudba.blogspot.com/2009/07/ora-39165-schema-sys-was-not-found-ora.html
http://arjudba.blogspot.com/2009/07/ora-39166-object-was-not-found-sys.html
http://arjudba.blogspot.com/2009/05/ora-39000-ora-39143-dump-file-may-be.html
http://arjudba.blogspot.com/2009/05/expdp-fails-with-ora-39001ora-39169ora.html
Oracle full database export fails with error stack EXP-00008, ORA-04063, ORA-06508, EXP-00083 like below.
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling WMSYS.LT_EXPORT_PKG.schema_info_exp
Cause of the Problem
The above problems are caused by the revoke of the execute privilege on UTL_FILE package from Public.
Oracle Workspace Manager (OWM) and some other database components (which are not installed in the database) need the privilege to execute procedure SYS.UTL_FILE.
During the creation of a default Database, the Workspace Manager gets installed by default. and the WMSYS user becomes intertwined with the export process and must be valid or at least the major parts for an export to complete properly as the export process looks for any "version-enabled" tables that Workspace Manager schema may be using.
Solution of the Problem
In order to solve above problems do following steps.
1. Grant execute privilege on SYS.UTL_FILE package to WMSYS user.
SQL> grant execute on SYS.UTL_FILE to WMSYS;
2. check for invalid objects in the WMSYS schema using following query,
SQL> select object_name,object_type,owner,status from dba_objects where status='INVALID' and owner='WMSYS';
3. Run script $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
Related Documents
http://arjudba.blogspot.com/2010/02/exp-00008-ora-06550-pls-00201-exp-00083.html
http://arjudba.blogspot.com/2010/02/ora-39127-ora-04063-ora-06508-ora-06512.html
http://arjudba.blogspot.com/2010/02/exp-00008-ora-04063-ora-06508-exp-00083.html
http://arjudba.blogspot.com/2009/12/export-fully-fails-with-pls-00201-ora.html
http://arjudba.blogspot.com/2009/12/export-fails-with-exp-00002-error-in.html
http://arjudba.blogspot.com/2009/01/ora-31655-no-data-or-metadata-objects.html
http://arjudba.blogspot.com/2009/01/expdp-fails-with-ora-31693-ora-06502.html
http://arjudba.blogspot.com/2008/12/ora-39095-dump-file-space-has-been.html
http://arjudba.blogspot.com/2008/09/expdp-fails-with-ora-31626-ora-31633.html
http://arjudba.blogspot.com/2008/07/data-pump-export-fails-with-ora-39000.html
http://arjudba.blogspot.com/2009/07/ora-39165-schema-sys-was-not-found-ora.html
http://arjudba.blogspot.com/2009/07/ora-39166-object-was-not-found-sys.html
http://arjudba.blogspot.com/2009/05/ora-39000-ora-39143-dump-file-may-be.html
http://arjudba.blogspot.com/2009/05/expdp-fails-with-ora-39001ora-39169ora.html
Wednesday, February 17, 2010
ORA-00204 ORA-00202 ORA-27071 HP-UX Error: 9: Bad file number
Problem Description
RMAN backup fails with ORA-00204 ORA-00202 ORA-27071 HP-UX Error: 9: Bad file number as below.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '/SID/oradata/APEXP/control01.ctl'
ORA-27071: unable to seek to desired position in file
HP-UX Error: 9: Bad file number
An another version of error message appear while starting RMAN is,
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00204: error in reading (block 3, # blocks 1) of control file
ORA-00202: control file: '/SID/oradata/APEXP/control01.ctl'
ORA-27071: unable to seek to desired position in file
HP-UX Error: 9: Bad file number Additional information: 2
Cause of the Problem
The RMAN-06900, RMAN-06901 error occurs when the RMAN couldn't log the output in $rman_output. The actual investigation of the error comes in the associated additional error messages. If you look for additional error message we will see ORA-27071, HP-UX Error: 9: Bad file number. The word 'HP-UX Error: 9: Bad file number' indicates that error is coming from operating system. The OS user who is invoking RMAN don't have OS permission on the control file and hence HP-UX generates bad file number error.
Solution of the Problem
The user who is invoking RMAN don't have write permission on the controlfile. You must need to change permission from operating system. You can do so by issuing,
$chmod 666 /SID/oradata/APEXP/control01.ctl
After you change the permission now try to invoke RMAN command again.
Related Documents:
RMAN backup fails with ORA-00204 ORA-00202 ORA-27071 HP-UX Error: 9: Bad file number as below.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '/SID/oradata/APEXP/control01.ctl'
ORA-27071: unable to seek to desired position in file
HP-UX Error: 9: Bad file number
An another version of error message appear while starting RMAN is,
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00204: error in reading (block 3, # blocks 1) of control file
ORA-00202: control file: '/SID/oradata/APEXP/control01.ctl'
ORA-27071: unable to seek to desired position in file
HP-UX Error: 9: Bad file number Additional information: 2
Cause of the Problem
The RMAN-06900, RMAN-06901 error occurs when the RMAN couldn't log the output in $rman_output. The actual investigation of the error comes in the associated additional error messages. If you look for additional error message we will see ORA-27071, HP-UX Error: 9: Bad file number. The word 'HP-UX Error: 9: Bad file number' indicates that error is coming from operating system. The OS user who is invoking RMAN don't have OS permission on the control file and hence HP-UX generates bad file number error.
Solution of the Problem
The user who is invoking RMAN don't have write permission on the controlfile. You must need to change permission from operating system. You can do so by issuing,
$chmod 666 /SID/oradata/APEXP/control01.ctl
After you change the permission now try to invoke RMAN command again.
Related Documents:
How to Restore the Controlfile from Backup.
ORA-00214: Controlfile Version Inconsistent on Startup or Shutdown
Controlfile in Oracle Database.
New Feature of 10.2g: Eliminate Control File Re-Creation
Creating controlfile fails with ORA-01503, ORA-01161
Backup Database control file -User Managed
Recover database after only lose of all controlfiles
Saturday, January 30, 2010
RMAN-06900, RMAN-06901, ORA-19921 maximum number of 64 rows exceeded
Problem Description
RMAN backups has been successfully completed but from the backup logs the following error have been generated.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 64 rows exceeded
A variation of the above error is,
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded
Analysis And Solution of the Problem
As soon as you get error message RMAN-06900 and RMAN-06901 you immediately look for associated error messages. The associated error message should tell you more information and you need to look for those messages in order to solve this error. For example here we are getting additional error "ORA-19921: maximum number of 128 rows exceeded". So our solution will lie on ORA-19921.
RMAN gives a warning message of RMAN-6900, RMAN-6901 ORA-19921 when the output is too huge and oracle is not able to write the log output into v$rman_output. There are several reasons when oracle will not be able to write the log output into V$RMAN_OUTPUT. For example, one of your control file becomes corrupted, hence oracle is unable to write the the log output to RMAN view/table. The another major reasons cause this problem to happen is due to oracle bug.
You get oracle error "ORA-19921: maximum number of 64 rows exceeded" due to oracle Bug 465973.
You get oracle error "ORA-19921: MAXIMUM NUMBER OF 128 ROWS EXCEEDED" due to oracle Bug 8264365.
The bug 465973 is fixed in Oracle 10.2.0.4 and Oracle 11G. So if you see oracle only gives warning message and backup successfully done then you can simply ignore error messages or upgrade oracle or apply patch where bug is fixed.
However if you see your backup is not done due to these errors then immediately check for additional messages. If it happened due to controlfiles then take care of those errors.
Related Documents:
RMAN backups has been successfully completed but from the backup logs the following error have been generated.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 64 rows exceeded
A variation of the above error is,
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded
Analysis And Solution of the Problem
As soon as you get error message RMAN-06900 and RMAN-06901 you immediately look for associated error messages. The associated error message should tell you more information and you need to look for those messages in order to solve this error. For example here we are getting additional error "ORA-19921: maximum number of 128 rows exceeded". So our solution will lie on ORA-19921.
RMAN gives a warning message of RMAN-6900, RMAN-6901 ORA-19921 when the output is too huge and oracle is not able to write the log output into v$rman_output. There are several reasons when oracle will not be able to write the log output into V$RMAN_OUTPUT. For example, one of your control file becomes corrupted, hence oracle is unable to write the the log output to RMAN view/table. The another major reasons cause this problem to happen is due to oracle bug.
You get oracle error "ORA-19921: maximum number of 64 rows exceeded" due to oracle Bug 465973.
You get oracle error "ORA-19921: MAXIMUM NUMBER OF 128 ROWS EXCEEDED" due to oracle Bug 8264365.
The bug 465973 is fixed in Oracle 10.2.0.4 and Oracle 11G. So if you see oracle only gives warning message and backup successfully done then you can simply ignore error messages or upgrade oracle or apply patch where bug is fixed.
However if you see your backup is not done due to these errors then immediately check for additional messages. If it happened due to controlfiles then take care of those errors.
Related Documents:
How to Restore the Controlfile from Backup.
ORA-00214: Controlfile Version Inconsistent on Startup or Shutdown
Controlfile in Oracle Database.
New Feature of 10.2g: Eliminate Control File Re-Creation
Creating controlfile fails with ORA-01503, ORA-01161
Backup Database control file -User Managed
Recover database after only lose of all controlfiles
Tuesday, January 19, 2010
Shutdown fails with ORA-24324, ORA-24323, ORA-01090
Problem Description
Shut down oracle database (shutdown normal/shutdown immediate) fails with following errors.
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
Cause of the Problem
The problem happened because background processes are hanging/not stared correctly during the previous startup of this database. Hence the smeaphores and shared memory segements are not getting detached properly now during shutdown.
Solution of the Problem
1) Verify that there are no background processes owned by "oracle"
$ ps -ef | grep ora_ | grep $ORACLE_SID
If background processes exist, remove them by using the Unix "kill" command.
For example to kill a process ID number 1200 issue,
$ kill -9 1200
2) Verify that no shared memory segments and semaphores that are owned by "oracle" still exist.
If there are shared memory segments and semaphores owned by "oracle", remove the shared memory segments and semaphores.
A) checking and removing shared memory.
Verify the shared memory segment by,
$ ipcs -mt
To remove shared memory segment issue,
$ ipcrm -m Shared_Memory_ID_Number
where Shared_Memory_ID_Number must be replace by shared memory id number.
B) checking and removing semaphores
Check the semaphores by,
$ipcs -sbt
To remove the semaphores issue,
$ ipcrm -s Semaphore_ID_Number
where Semaphore_ID_Number must be replaced by your Semaphore ID Number.
3) Verify that file $ORACLE_HOME/dbs/lk{db_name} does not exist where db_name is your actual database name.
$ cd $ORACLE_HOME/dbs
$ rm lk{db_name}
4) Verify that file "$ORACLE_HOME/dbs/sgadef{sid}.dbf" does not exist where sid is your actual database SID.
$ cd $ORACLE_HOME/dbs
$ rm sgadef{SID}.dbf
5) Exit sql*plus window, re login to sql*plus and try to bring up oracle one step at a time.
$ sqlplus /nolog
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;
Note that while performing above steps it is recommended to first bring down all the other instances running on the server and then perform the above steps one by one. Re-booting the server will also fix this problem.
Related Documents
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/11/ora-01033-oracle-initialization-or.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-00444.html
http://arjudba.blogspot.com/2008/05/database-startup-fails-with-errors-ora.html
http://arjudba.blogspot.com/2008/04/ora-01034-ora-27101-shared-memory-realm.html
Shut down oracle database (shutdown normal/shutdown immediate) fails with following errors.
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
Cause of the Problem
The problem happened because background processes are hanging/not stared correctly during the previous startup of this database. Hence the smeaphores and shared memory segements are not getting detached properly now during shutdown.
Solution of the Problem
1) Verify that there are no background processes owned by "oracle"
$ ps -ef | grep ora_ | grep $ORACLE_SID
If background processes exist, remove them by using the Unix "kill" command.
For example to kill a process ID number 1200 issue,
$ kill -9 1200
2) Verify that no shared memory segments and semaphores that are owned by "oracle" still exist.
If there are shared memory segments and semaphores owned by "oracle", remove the shared memory segments and semaphores.
A) checking and removing shared memory.
Verify the shared memory segment by,
$ ipcs -mt
To remove shared memory segment issue,
$ ipcrm -m Shared_Memory_ID_Number
where Shared_Memory_ID_Number must be replace by shared memory id number.
B) checking and removing semaphores
Check the semaphores by,
$ipcs -sbt
To remove the semaphores issue,
$ ipcrm -s Semaphore_ID_Number
where Semaphore_ID_Number must be replaced by your Semaphore ID Number.
3) Verify that file $ORACLE_HOME/dbs/lk{db_name} does not exist where db_name is your actual database name.
$ cd $ORACLE_HOME/dbs
$ rm lk{db_name}
4) Verify that file "$ORACLE_HOME/dbs/sgadef{sid}.dbf" does not exist where sid is your actual database SID.
$ cd $ORACLE_HOME/dbs
$ rm sgadef{SID}.dbf
5) Exit sql*plus window, re login to sql*plus and try to bring up oracle one step at a time.
$ sqlplus /nolog
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;
Note that while performing above steps it is recommended to first bring down all the other instances running on the server and then perform the above steps one by one. Re-booting the server will also fix this problem.
Related Documents
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/11/ora-01033-oracle-initialization-or.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-00444.html
http://arjudba.blogspot.com/2008/05/database-startup-fails-with-errors-ora.html
http://arjudba.blogspot.com/2008/04/ora-01034-ora-27101-shared-memory-realm.html
ORA-01102: cannot mount database in EXCLUSIVE mode
Problem Description
While I start oracle database instance it fails with following error.
Connected to an idle instance.
ORACLE instance started.
Total System Global Area 877574740 bytes
Fixed Size 651436 bytes
Variable Size 502653184 bytes
Database Buffers 263840000 bytes
Redo Buffers 10629120 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
Problem Investigation
After you hit ORA-01102 error you should immediately check your database alert log for further analysis. Following is the sample example error messages generated in the alert log after you hit ORA-01102.
Alert log Error Message Version 01
ALTER DATABASE MOUNT
Wed Oct 22 03:40:21 2009
scumnt: failed to lock /dba/oracle/product/920/dbs/lkARJU exclusive
Wed Oct 22 03:40:21 2009
ORA-09968: scumnt: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 26165
Wed Oct 22 03:40:29 2009
ORA-1102 signalled during: ALTER DATABASE MOUNT
Alert log Error Message Version 02
ALTER DATABASE MOUNT
Mon Mar 6 15:31:21 2009
scumnt: failed to lock /apps/oracle/product/9.2/dbs/lkARJU exclusive
Mon Mar 6 15:31:21 2009
ORA-09968: scumnt: unable to lock file
Compaq Tru64 UNIX Error: 13: Permission denied
Additional information: 1246156
Mon Mar 6 15:31:21 2009
ORA-1102 signalled during: ALTER DATABASE MOUNT...
Cause of the Problem
This ORA-01102 error indicates an instance tried to mount the database in exclusive mode, but some other instance has already mounted the database in exclusive or parallel mode. By default a database is started in EXCLUSIVE mode. The real cause of ORA-01102 would be found in the alert log file where you will find additional information. The common reasons causing error ORA-01102 are as follows.
1) The processes for Oracle (pmon, smon, lgwr and dbwr) still exist. You can search them by ps -ef |grep YOUR_DB_NAME_HERE.
2) Shared memory segments and semaphores still exist even though the database has been shutdown.
3) There exists a file named "$ORACLE_HOME/dbs/lk{db_name}" where db_name is your actual database name.
4) A file named "$ORACLE_HOME/dbs/sgadef{sid}.dbf" exists where sid is your actual database SID.
5) You have two databases in your host. Now starting anyone of these causes error ORA-01102 if the other one is already started. If one is shutdown, the other database can be started successfully. This happened as while starting up, both the databases are trying to lock the same file. This is obvious if within the parameter files for these databases have the same entries for control_files and db_name. For example you have two databases named dba1 and dba2. Now inside the spfile/pfile of both databases that is inside initDBA1.ora and initDBA2.ora (in case of pfile) you have the similar entries like below.
...
*.control_files='xxx/control01.ctl','xxx/control02.ctl','xxx/control03.ctl'
*.db_name=DBA1
...
Solution of the Problem
1) Verify that there are no background processes owned by "oracle"
$ ps -ef | grep ora_ | grep $ORACLE_SID
If background processes exist, remove them by using the Unix "kill" command.
For example to kill a process ID number 7818 issue,
$ kill -9 7818
2) Verify that no shared memory segments and semaphores that are owned by "oracle" still exist.
Verify by command,
$ ipcs -b
If there are shared memory segments and semaphores owned by "oracle", remove the shared memory segments.
To remove shared memory segment issue,
$ ipcrm -m Shared_Memory_ID_Number
where Shared_Memory_ID_Number must be replace by shared memory id number.
To remove the semaphores issue,
$ ipcrm -s Semaphore_ID_Number
where Semaphore_ID_Number must be replaced by your Semaphore ID Number.
3) Verify that file $ORACLE_HOME/dbs/lk{db_name} does not exist where db_name is your actual database name.
4) Verify that file "$ORACLE_HOME/dbs/sgadef{sid}.dbf" does not exist where sid is your actual database SID.
5) If you see you have several databases in your machine and both of them uses have same entry in the parameter control_files and db_name then use correct values belonging to the individual databases.
In the sql*plus nomount stage you can issue,
show parameter db_name;
show parameter control_files;
in order to verify the entry.
6) From alert log if you see error like "Compaq Tru64 UNIX Error: 13: Permission denied" then ensure that in the file/directory oracle has permission and ensure that oracle is owner of the file. With chmod and chown you can change permission and ownership respectively.
Note that The "lk{db_name}" and "sgadef{sid}.dbf" files are used for locking shared memory. It may happen that even though no memory is allocated, Oracle thinks memory is still locked. By removing the "sgadef" and "lk" files you remove any knowledge oracle has of shared memory that is in use. So after removing those two file you can try to startup database.
Related Documents
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/11/ora-01033-oracle-initialization-or.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-00444.html
http://arjudba.blogspot.com/2008/05/database-startup-fails-with-errors-ora.html
http://arjudba.blogspot.com/2008/04/ora-01034-ora-27101-shared-memory-realm.html
While I start oracle database instance it fails with following error.
Connected to an idle instance.
ORACLE instance started.
Total System Global Area 877574740 bytes
Fixed Size 651436 bytes
Variable Size 502653184 bytes
Database Buffers 263840000 bytes
Redo Buffers 10629120 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
Problem Investigation
After you hit ORA-01102 error you should immediately check your database alert log for further analysis. Following is the sample example error messages generated in the alert log after you hit ORA-01102.
Alert log Error Message Version 01
ALTER DATABASE MOUNT
Wed Oct 22 03:40:21 2009
scumnt: failed to lock /dba/oracle/product/920/dbs/lkARJU exclusive
Wed Oct 22 03:40:21 2009
ORA-09968: scumnt: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 26165
Wed Oct 22 03:40:29 2009
ORA-1102 signalled during: ALTER DATABASE MOUNT
Alert log Error Message Version 02
ALTER DATABASE MOUNT
Mon Mar 6 15:31:21 2009
scumnt: failed to lock /apps/oracle/product/9.2/dbs/lkARJU exclusive
Mon Mar 6 15:31:21 2009
ORA-09968: scumnt: unable to lock file
Compaq Tru64 UNIX Error: 13: Permission denied
Additional information: 1246156
Mon Mar 6 15:31:21 2009
ORA-1102 signalled during: ALTER DATABASE MOUNT...
Cause of the Problem
This ORA-01102 error indicates an instance tried to mount the database in exclusive mode, but some other instance has already mounted the database in exclusive or parallel mode. By default a database is started in EXCLUSIVE mode. The real cause of ORA-01102 would be found in the alert log file where you will find additional information. The common reasons causing error ORA-01102 are as follows.
1) The processes for Oracle (pmon, smon, lgwr and dbwr) still exist. You can search them by ps -ef |grep YOUR_DB_NAME_HERE.
2) Shared memory segments and semaphores still exist even though the database has been shutdown.
3) There exists a file named "$ORACLE_HOME/dbs/lk{db_name}" where db_name is your actual database name.
4) A file named "$ORACLE_HOME/dbs/sgadef{sid}.dbf" exists where sid is your actual database SID.
5) You have two databases in your host. Now starting anyone of these causes error ORA-01102 if the other one is already started. If one is shutdown, the other database can be started successfully. This happened as while starting up, both the databases are trying to lock the same file. This is obvious if within the parameter files for these databases have the same entries for control_files and db_name. For example you have two databases named dba1 and dba2. Now inside the spfile/pfile of both databases that is inside initDBA1.ora and initDBA2.ora (in case of pfile) you have the similar entries like below.
...
*.control_files='xxx/control01.ctl','xxx/control02.ctl','xxx/control03.ctl'
*.db_name=DBA1
...
Solution of the Problem
1) Verify that there are no background processes owned by "oracle"
$ ps -ef | grep ora_ | grep $ORACLE_SID
If background processes exist, remove them by using the Unix "kill" command.
For example to kill a process ID number 7818 issue,
$ kill -9 7818
2) Verify that no shared memory segments and semaphores that are owned by "oracle" still exist.
Verify by command,
$ ipcs -b
If there are shared memory segments and semaphores owned by "oracle", remove the shared memory segments.
To remove shared memory segment issue,
$ ipcrm -m Shared_Memory_ID_Number
where Shared_Memory_ID_Number must be replace by shared memory id number.
To remove the semaphores issue,
$ ipcrm -s Semaphore_ID_Number
where Semaphore_ID_Number must be replaced by your Semaphore ID Number.
3) Verify that file $ORACLE_HOME/dbs/lk{db_name} does not exist where db_name is your actual database name.
4) Verify that file "$ORACLE_HOME/dbs/sgadef{sid}.dbf" does not exist where sid is your actual database SID.
5) If you see you have several databases in your machine and both of them uses have same entry in the parameter control_files and db_name then use correct values belonging to the individual databases.
In the sql*plus nomount stage you can issue,
show parameter db_name;
show parameter control_files;
in order to verify the entry.
6) From alert log if you see error like "Compaq Tru64 UNIX Error: 13: Permission denied" then ensure that in the file/directory oracle has permission and ensure that oracle is owner of the file. With chmod and chown you can change permission and ownership respectively.
Note that The "lk{db_name}" and "sgadef{sid}.dbf" files are used for locking shared memory. It may happen that even though no memory is allocated, Oracle thinks memory is still locked. By removing the "sgadef" and "lk" files you remove any knowledge oracle has of shared memory that is in use. So after removing those two file you can try to startup database.
Related Documents
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/11/ora-01033-oracle-initialization-or.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-00444.html
http://arjudba.blogspot.com/2008/05/database-startup-fails-with-errors-ora.html
http://arjudba.blogspot.com/2008/04/ora-01034-ora-27101-shared-memory-realm.html
Sunday, January 17, 2010
RMAN-06091 no channel allocated for maintenance (of an appropriate type)
Problem Description
RMAN DELETE OBSOLETE command fails with following error stack.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 01/17/2010 02:15:40
RMAN-06091: no channel allocated for maintenance (of an appropriate type)
Problem Investigation
This error occurs if you attempt to delete obsolete backup from tape but channel had not being allocated on tape. And also these error stacks will appear if you attempt to delete obsolete backup from disk but channel had not being allocation on disk.
Note that if you allocate channel to disk and then attempt to delete obsolete it should work if your backups are in disk. We assume that backups are in disk and let's try to delete it after allocating channel to disk.
Solution of the Problem
Using show all command check the rman configuration parameter settings.
Using the following command verify that whether the backup sets are on tape or in disk.
So our command will be as follows.
RMAN> allocate channel for maintenance device type 'sbt_tape' PARMS '...';
Here please change '...' to your actual tape params
RMAN> delete obsolete;
or issue following command to delete obsolete backups from tape.
RMAN> allocate channel for maintenance type sbt_tape;
However if your backups are on disk then your command will look like below.
If you want to delete both obsolete and expired backups from disk then you can issue,
RMAN-06091 no channel allocated for maintenance (of an appropriate type)
RMAN-06429: TARGET database is not compatible with this version of RMAN
How to take RMAN backup on a remote disk/ location
RMAN-00554,RMAN-04005,ORA-0103 when remote connection by rman
Set Date format inside RMAN environment
How to skip a tablespace for restore operation
How to debug Backup, Restore Session in RMAN
Restore operation fails with RMAN-11003 ORA-01511 ORA-01516
Recover database after only lose of all controlfiles
RMAN-04014: startup failed: ORA-07446: sdnfy: bad value
How to restore an Spfile from autobackup older than 7 days
RMAN-06172: no autobackup found
Creating a Duplicate Database on a Remote Host -Part1
Database Duplication Fails Missing Log RMAN-06053 RMAN-06025
How to perform Database Point in time Recovery DBPITR
RMAN DELETE OBSOLETE command fails with following error stack.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 01/17/2010 02:15:40
RMAN-06091: no channel allocated for maintenance (of an appropriate type)
Problem Investigation
This error occurs if you attempt to delete obsolete backup from tape but channel had not being allocated on tape. And also these error stacks will appear if you attempt to delete obsolete backup from disk but channel had not being allocation on disk.
Note that if you allocate channel to disk and then attempt to delete obsolete it should work if your backups are in disk. We assume that backups are in disk and let's try to delete it after allocating channel to disk.
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
allocated channel: ORA_MAINT_DISK_2
channel ORA_MAINT_DISK_2: sid=149 devtype=DISK
RMAN> delete obsolete;But no help. It fails still.
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 3739 Dec 24 2009 04:36:02
Backup Piece 6037 Dec 24 2009 04:36:02 lml1m82u_1_2
Backup Set 3738 Dec 24 2009 04:36:02
Backup Piece 6036 Dec 24 2009 04:36:02 lnl1m82u_1_2
Backup Set 3743 Dec 24 2009 04:38:06
Backup Piece 6038 Dec 24 2009 04:38:06 c-3269547898-20091224-01
.
.
.
Backup Set 3857 Jan 11 2010 04:25:21
Backup Piece 6217 Jan 11 2010 04:25:21 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010 0111-00
Backup Set 3862 Jan 12 2010 04:58:50
Backup Piece 6223 Jan 12 2010 04:58:50 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010 0112-00
Backup Set 3867 Jan 13 2010 04:40:29
Backup Piece 6228 Jan 13 2010 04:40:29 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010 0113-00
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 01/17/2010 02:15:40
RMAN-06091: no channel allocated for maintenance (of an appropriate type)
Solution of the Problem
Using show all command check the rman configuration parameter settings.
RMAN> show all;From the above configuration we see that channels are allocated to disk.
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'ora_cf%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/db/app/oracle/product/10.2.0/db/dbs/snapcf_OMSRPS.f'; # default
Using the following command verify that whether the backup sets are on tape or in disk.
RMAN> list backup;From the above command we see the backups are on SBT_TAPE. So you have to issue delete command from the tape.
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
3743 Full 7.25M SBT_TAPE 00:01:55 Dec 24 2009 04:38:06
BP Key: 6038 Status: AVAILABLE Compressed: NO Tag: TAG20091224T043611
Handle: c-3269547898-20091224-01 Media:
Standby Control File Included: Ckp SCN: 1318360946 Ckp time: Dec 24 2009 04:32:46
SPFILE Included: Modification time: Aug 25 2009 03:53:48
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
3746 Incr 1 228.05M SBT_TAPE 00:08:49 Dec 25 2009 04:18:25
BP Key: 6045 Status: AVAILABLE Compressed: YES Tag: LEVEL_1_122509
Handle: lvl1ot00_1_2 Media:
List of Datafiles in backup set 3746
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
1 1 Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/system01.dbf
2 1 Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/undotbs01.dbf
3 1 Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/sysaux01.dbf
7 1 Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/rman_ts.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
3747 Incr 1 136.52M SBT_TAPE 00:17:24 Dec 25 2009 04:27:00
BP Key: 6044 Status: AVAILABLE Compressed: YES Tag: LEVEL_1_122509
Handle: lul1ot00_1_2 Media:
List of Datafiles in backup set 3747
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
4 1 Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/users01.dbf
5 1 Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/mgmt.dbf
6 1 Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/mgmt_ecm_depot1.dbf
So our command will be as follows.
RMAN> allocate channel for maintenance device type 'sbt_tape' PARMS '...';
Here please change '...' to your actual tape params
RMAN> delete obsolete;
or issue following command to delete obsolete backups from tape.
RMAN> allocate channel for maintenance type sbt_tape;
However if your backups are on disk then your command will look like below.
RMAN> allocate channel for maintenance type disk;And then,
allocated channel: ORA_MAINT_DISK_4
channel ORA_MAINT_DISK_4: sid=79 devtype=DISK
RMAN> delete obsolete device type disk;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 3792 Jan 01 2010 04:25:18
Backup Piece 6115 Jan 01 2010 04:25:18 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010 0101-00
Backup Set 3799 Jan 02 2010 04:25:44
Backup Piece 6126 Jan 02 2010 04:25:44 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010 0102-00
.
.
.
Backup Piece 6217 Jan 11 2010 04:25:21 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010 0111-00
Backup Set 3862 Jan 12 2010 04:58:50
Backup Piece 6223 Jan 12 2010 04:58:50 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010 0112-00
Backup Set 3867 Jan 13 2010 04:40:29
Backup Piece 6228 Jan 13 2010 04:40:29 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010 0113-00
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-20100101-00 recid=6115 stamp=7071 13518
deleted backup piece
backup piece handle=/db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-20100102-00 recid=6126 stamp=7071 99944
deleted backup piece
.
.
.
deleted backup piece
backup piece handle=/db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-20100112-00 recid=6223 stamp=7080 65929
deleted backup piece
backup piece handle=/db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-20100113-00 recid=6228 stamp=7081 51228
Deleted 13 objects
If you want to delete both obsolete and expired backups from disk then you can issue,
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;Related Documents
RMAN> run
{
report obsolete;
CROSSCHECK BACKUP;
CROSSCHECK COPY;
DELETE EXPIRED BACKUP;
DELETE EXPIRED COPY;
delete obsolete;
}
release channel;
RMAN-06091 no channel allocated for maintenance (of an appropriate type)
RMAN-06429: TARGET database is not compatible with this version of RMAN
How to take RMAN backup on a remote disk/ location
RMAN-00554,RMAN-04005,ORA-0103 when remote connection by rman
Set Date format inside RMAN environment
How to skip a tablespace for restore operation
How to debug Backup, Restore Session in RMAN
Restore operation fails with RMAN-11003 ORA-01511 ORA-01516
Recover database after only lose of all controlfiles
RMAN-04014: startup failed: ORA-07446: sdnfy: bad value
How to restore an Spfile from autobackup older than 7 days
RMAN-06172: no autobackup found
Creating a Duplicate Database on a Remote Host -Part1
Database Duplication Fails Missing Log RMAN-06053 RMAN-06025
How to perform Database Point in time Recovery DBPITR
Saturday, January 16, 2010
ORA-16014 ORA-00312 ORA-16038 ORA-19809: limit exceeded for recovery files
Problem Description:
From alert log, we find the following errors
Flash recovery area is used for archival destination. ORA-16014 ORA-00312 ORA-16038 ORA-19809: limit exceeded for recovery files will be raised because there is no space to create additional archive log in the flash recovery area. Note that the error inside trace file "ORA-19815: WARNING: db_recovery_file_dest_size of 99857989632 bytes is 70.10% used, and has 29852624896 remaining bytes available." can be misleading. Though it says 70.10% used but actually it is 100% used. Ensure by issuing following query in the database,
Consequences of the Error
Due to ORA-16014, ORA-00312, ORA-16038 and ORA-19809 there might have following effects to database.
1) Database archive process stops and database hangs.
2) Users are not able to connect to database.
3) If you try to open database you are not able to open database bypass these errors.
4) In the Alert log/Trace file Flash Recovery Area reports ORA-19809: limit exceeded for recovery files.
Solution of the Problem
There may have several solutions to solve above problems.
Solution 00: Ensure that you have enough space in the underlying directory
This step is common and application for all solution methods.
In the underlying mount point of your archival destination ensure that you have enough space. That is ensure that your hard disk is not full. If you don't have sufficient space in your disk space free up the space by deleting unnecessary files.
On Linux/Solaris you can check disk space of all mount points by $df -h
On HP-UX issue $df -k
Solution 01: Increase the size of Flash recovery area
The size of flash recovery area is determine by the parameter db_recovery_file_dest_size. You can check the current size of flash recovery area in Sql*plus by issuing,
Solution 02: Change the database archival location outside FRA
You can solve the problem if you stop using flash recovery area for your archival destination. You can disable using flash recovery area by issuing following query,
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';
System altered.
After disabling flash recovery area you will see your archival location is changed to database default archival location. If you have enough space in your default archive location (you can check it by issuing "archive log list" after connecting as sysdba) you should no longer get above errors.
Solution 03: Free up space in FRA
If you use RMAN to database backup and store backups to FRA then check space distribution in the FRA by following query,
SQL>select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;
After issuing query if you see almost all the space are used by Archivelogs and backup pieces then there is no space to reclaim. So you need to free up some spaces with by of the following methods.
a) Take backup of archivelogs to some other locations and delete the archivelogs from flash recovery area.
You can delete older archivelogs say before 7 days ago by following RMAN command.
RMAN>Delete archivelog all completed before 'SYSDATE-7';
b) If flashback logs are enable then make sure you have enough space for all the flashback logs. If you don't need flashback logs you can disable it by command in mount stage.
SQL>Alter database FLASHBACK OFF;
c) Note that if you have guaranteed restore point then flashback logs will not be reclaimed by flash recovery area. Even due to some bugs Flashback logs are not reclaimed by flash recovery area when using guaranteed restore point (after dropping guaranteed restore point) or when changing db_flashback_retention_target to a lower value. If there is such guaranteed restore point and it is no longer needed you can delete by,
SQL> Drop restore point restore_point_name;
d) From your archival location using any operating system utility like rm or del or delete you can remove the backups/archive log files. After you remove the files the OS knows that you have deleted files but still you need to inform the database to be aware of the resulting free space.
To inform database about free space connect as RMAN and then issue following commands.
RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;
RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;
Solution 4: Scenarios based on Database Backup Strategy
Follow the scenarios as demonstrated in topic Database Startup fails with error ORA-16038,ORA-19809, ORA-00312. In the post it is demonstrated what we will do if we don't (in Solution C:) have any backup and also the secnarios if we (Solution D:) have recent backup and only need archivelogs.
Related Documents
Set up Flash Recovery Area for RMAN
Database Startup fails with error ORA-16038,ORA-19809, ORA-00312
From alert log, we find the following errors
Sat Jan 16 03:08:36 2010If we check the content of trace file /SIDS/app/oracle/admin/OMSRPS/bdump/omsrps_arc4_9885.trc we see,
ARCH: Archival stopped, error occurred. Will continue retrying
Sat Jan 16 03:08:36 2010
ORACLE Instance OMSRPS - Archival Error
Sat Jan 16 03:08:36 2010
ORA-16014: log 6 sequence# 11181 not archived, no available destinations
ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log'
ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log'
Sat Jan 16 03:08:36 2010
Errors in file /SIDS/app/oracle/admin/OMSRPS/bdump/omsrps_arc4_9885.trc:
ORA-16014: log 6 sequence# 11181 not archived, no available destinations
ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log'
ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log'
ORA-19815: WARNING: db_recovery_file_dest_size of 99857989632 bytes is 70.10% used, and has 29852624896 remaining bytes available.Cause of the Problem
*** 2010-01-16 03:23:54.463
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
*** 2010-01-16 03:23:54.464 62692 kcrr.c
ARC4: Error 19809 Creating archive log file to '/backup/flash_recovery_area/OMSRPS/archivelog/2010_01_16/o1_mf_1_11181_%u_.arc'
*** 2010-01-16 03:23:54.464 60970 kcrr.c
kcrrfail: dest:1 err:19809 force:0 blast:1
ARCH: Connecting to console port...
ARCH: Connecting to console port...
*** 2010-01-16 03:23:54.513 21373 kcrr.c
ORA-16038: log 6 sequence# 11181 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log'
ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log'
Flash recovery area is used for archival destination. ORA-16014 ORA-00312 ORA-16038 ORA-19809: limit exceeded for recovery files will be raised because there is no space to create additional archive log in the flash recovery area. Note that the error inside trace file "ORA-19815: WARNING: db_recovery_file_dest_size of 99857989632 bytes is 70.10% used, and has 29852624896 remaining bytes available." can be misleading. Though it says 70.10% used but actually it is 100% used. Ensure by issuing following query in the database,
SQL> set lines 120We see in the flash recovery area space limit and space used are almost same and no more space for archival. Hence we get error.
SQL> col name format a50
SQL> select name
,floor(space_limit / 1024 / 1024) "Size MB"
,ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/
NAME Size MB Used MB
------------------------------------------- --------- ------------
/backup/flash_recovery_area 95232 95152
Consequences of the Error
Due to ORA-16014, ORA-00312, ORA-16038 and ORA-19809 there might have following effects to database.
1) Database archive process stops and database hangs.
2) Users are not able to connect to database.
3) If you try to open database you are not able to open database bypass these errors.
4) In the Alert log/Trace file Flash Recovery Area reports ORA-19809: limit exceeded for recovery files.
Solution of the Problem
There may have several solutions to solve above problems.
Solution 00: Ensure that you have enough space in the underlying directory
This step is common and application for all solution methods.
In the underlying mount point of your archival destination ensure that you have enough space. That is ensure that your hard disk is not full. If you don't have sufficient space in your disk space free up the space by deleting unnecessary files.
On Linux/Solaris you can check disk space of all mount points by $df -h
On HP-UX issue $df -k
Solution 01: Increase the size of Flash recovery area
The size of flash recovery area is determine by the parameter db_recovery_file_dest_size. You can check the current size of flash recovery area in Sql*plus by issuing,
SYS@OMSRPS> show parameter db_recovery_fileor by issuing query,
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/flash_recovery_area
db_recovery_file_dest_size big integer 93G
SQL> select value from v$parameter where name='db_recovery_file_dest_size';Here, I have increased to 95G. Ensure by issuing,
VALUE
-----------------------------------------------------------------------------
99857989632
In order to increase the size of the parameter issue following query.
SYS@OMSRPS> alter system set db_recovery_file_dest_size = 95G scope=both;
System altered.
SYS@OMSRPS> show parameter db_recovery_fileNow check your alert log file and you see Archiver process freed from errors.
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
db_recovery_file_dest string /backup/flash_recovery_area
db_recovery_file_dest_size big integer 95G
Sat Jan 16 03:37:31 2010
ALTER SYSTEM SET db_recovery_file_dest_size='95G' SCOPE=BOTH;
Sat Jan 16 03:38:25 2010
Archiver process freed from errors. No longer stopped
Sat Jan 16 03:38:25 2010
Primary database is in MAXIMUM PERFORMANCE mode
RFS[9]: No standby redo logfiles of size 512000 blocks available
Sat Jan 16 03:38:27 2010
Media Recovery Waiting for thread 1 sequence 11186 (in transit)
Solution 02: Change the database archival location outside FRA
You can solve the problem if you stop using flash recovery area for your archival destination. You can disable using flash recovery area by issuing following query,
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';
System altered.
After disabling flash recovery area you will see your archival location is changed to database default archival location. If you have enough space in your default archive location (you can check it by issuing "archive log list" after connecting as sysdba) you should no longer get above errors.
Solution 03: Free up space in FRA
If you use RMAN to database backup and store backups to FRA then check space distribution in the FRA by following query,
SQL>select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;
After issuing query if you see almost all the space are used by Archivelogs and backup pieces then there is no space to reclaim. So you need to free up some spaces with by of the following methods.
a) Take backup of archivelogs to some other locations and delete the archivelogs from flash recovery area.
You can delete older archivelogs say before 7 days ago by following RMAN command.
RMAN>Delete archivelog all completed before 'SYSDATE-7';
b) If flashback logs are enable then make sure you have enough space for all the flashback logs. If you don't need flashback logs you can disable it by command in mount stage.
SQL>Alter database FLASHBACK OFF;
c) Note that if you have guaranteed restore point then flashback logs will not be reclaimed by flash recovery area. Even due to some bugs Flashback logs are not reclaimed by flash recovery area when using guaranteed restore point (after dropping guaranteed restore point) or when changing db_flashback_retention_target to a lower value. If there is such guaranteed restore point and it is no longer needed you can delete by,
SQL> Drop restore point restore_point_name;
d) From your archival location using any operating system utility like rm or del or delete you can remove the backups/archive log files. After you remove the files the OS knows that you have deleted files but still you need to inform the database to be aware of the resulting free space.
To inform database about free space connect as RMAN and then issue following commands.
RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;
RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;
Solution 4: Scenarios based on Database Backup Strategy
Follow the scenarios as demonstrated in topic Database Startup fails with error ORA-16038,ORA-19809, ORA-00312. In the post it is demonstrated what we will do if we don't (in Solution C:) have any backup and also the secnarios if we (Solution D:) have recent backup and only need archivelogs.
Related Documents
Set up Flash Recovery Area for RMAN
Database Startup fails with error ORA-16038,ORA-19809, ORA-00312
Interact with RMAN Client.
Tuesday, January 12, 2010
ORA-00205: error in identifying control file
Problem Description
Whenever you try to start your oracle database instance or mount your database it fails with ORA-00205 error message as below.
SQL> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 306185108 bytes
Database Buffers 222298112 bytes
Redo Buffers 5844992 bytes
ORA-00205: error in identifying control file, check alert log for more info
Error Investigation
As error message "ORA-00205: error in identifying control file, check alert log for more info" suggests to look for alert log messages so immediately after getting this error message you should look for alert log message.
Based on this error message you might get various types of alert long entries which would really help you to solve your problem. Following is the several versions of error messages.
Error Message Version 01:
ORA-00202: control file: '/oracle/controlfile/contro101.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Jan 8 11:19:42 2010
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri Jan 8 11:19:50 2010
Shutting down instance (abort)
Error Message Version 02:
ORA-00202: controlfile: '/oracle/controlfile/CONTROL01.CTL'
ORA-27086: skgfglk: unable to lock file - already in use
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
Error Message Version 03:
ORA-00202: control file: 'F:\ORACLE\CONTROL.CTL'
ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 38) Reached the end of the file.
Error Message Version 04:
Errors in file /oracle/9.2.0/admin/bdump/abc_ckpt_3117.trc:
ORA-00206: error in writing (block 3, # blocks 1) of controlfile
ORA-00202: controlfile: '/oracle/data/ctl03.dbf'
ORA-27072: skgfdisp: I/O error
Linux Error: 30: Read-only file system
Additional information: 2
General Cause And Solution of ORA-00205 Problem:
The ORA-00205 problem is reported because the system could not find a control file of the specified name and size. The name of control file is specified by the CONTROL_FILES initialization parameter within spfile/pfile. While mounting oracle database oracle finds that in the disk all those files are not there or size is unmatched as it is specified within spfile/pfile.
If you see ORA-00205 is reported while starting up/mount the oracle database then check that the proper control filename is referenced in the CONTROL_FILES initialization parameter in the initialization parameter. If not check your correct spfile/pfile or fix location of CONTROL_FILES parameter within spfile/pfile and try again to mount the database.
Whenever you are using mirrored control files, that is, more than one control file is referenced in the initialization parameter file, remove the control filename listed in the message from the initialization parameter file and restart the instance. If the message does not recur, remove the problem control file from the initialization parameter file and create another copy of the control file with a new filename in the initialization parameter file.
Step by step Solution
Step 01: After you see ORA-00205 immediately look for alert log files for further investigation.
Step 02: Check the control_files parameter in your initialization parameter. You can simply check by ,
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string D:\APP\ARJU\ORADATA\A\CONTROL0
1.CTL, D:\APP\ARJU\ORADATA\A\C
ONTROL02.CTL, D:\APP\ARJU\ORAD
ATA\A\CONTROL03.CTL
Now compare this location with the location of controlfile in your disk system.
From the alert log if you see error message like version 1 i.e you have noticed "ORA-00202: control file: '/oracle/controlfile/contro101.ctl'" then from your CONTROL_FILES initialization parameter remove the entry /oracle/controlfile/contro101.ctl if you have multiple version of controlfiles and start the database. If you fail then again check for alert logs. If it is same error message like points to another controlfile then try to remove that control file too if you have at least three versions of controlfiles. If you fail then possibly all of your controlfiles are lost. In that case you need to restore controlfile from previous backup or create a new one if you don't have any previous backup of controlfile. In the post Recover database after only lose of all controlfiles it is discussed how you can recover controlfile. In the post Restore the Controlfile from Backup using RMAN it is discussed how you can restore controlfile from previous RMAN backup using RMAN. In the post How to create controlfile it is discussed how to create a controlfile whenever you are lost all controlfile and you don't have any backups.
If you see other version of error versions then take necessary actions as alert log suggests. For example if you see error message like version 2 that is
"ORA-00202: controlfile: '/oracle/controlfile/CONTROL01.CTL'
ORA-27086: skgfglk: unable to lock file - already in use"
then ensure that oracle database is shutdown cleanly and no other process is holding lock of the control file. After you ensure that try starting database again.
If you see error message like version 3 that is
"ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file"
then possibly your control file got corrupted and follow the same steps as it is demonstrated in step 2 for error version 01.
If you see error message like version 4 that is
"ORA-00202: controlfile: '/oracle/data/ctl03.dbf'
ORA-27072: skgfdisp: I/O error
Linux Error: 30: Read-only file system"
then ensure that your control file has proper permission set from operating system. That means it must have read, write permission to oracle owner user.
Step 03:
After you identify and solve the problem ensure that you have multiple copies of your CONTROL_FILES parameter onwards. If you use spfile in your database startup then after your nomount state of database and physically copied controlfiles to all locations as you want, you can point multiple copies of controlfile by issuing "ALTER SYSTEM SET CONTROL_FILES=..., ..." command from database. A similar example is demonstrated in the topic ORA-00214: Controlfile Version Inconsistent on Startup or Shutdown
Related Documents:
Whenever you try to start your oracle database instance or mount your database it fails with ORA-00205 error message as below.
SQL> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 306185108 bytes
Database Buffers 222298112 bytes
Redo Buffers 5844992 bytes
ORA-00205: error in identifying control file, check alert log for more info
Error Investigation
As error message "ORA-00205: error in identifying control file, check alert log for more info" suggests to look for alert log messages so immediately after getting this error message you should look for alert log message.
Based on this error message you might get various types of alert long entries which would really help you to solve your problem. Following is the several versions of error messages.
Error Message Version 01:
ORA-00202: control file: '/oracle/controlfile/contro101.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Jan 8 11:19:42 2010
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri Jan 8 11:19:50 2010
Shutting down instance (abort)
Error Message Version 02:
ORA-00202: controlfile: '/oracle/controlfile/CONTROL01.CTL'
ORA-27086: skgfglk: unable to lock file - already in use
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
Error Message Version 03:
ORA-00202: control file: 'F:\ORACLE\CONTROL.CTL'
ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 38) Reached the end of the file.
Error Message Version 04:
Errors in file /oracle/9.2.0/admin/bdump/abc_ckpt_3117.trc:
ORA-00206: error in writing (block 3, # blocks 1) of controlfile
ORA-00202: controlfile: '/oracle/data/ctl03.dbf'
ORA-27072: skgfdisp: I/O error
Linux Error: 30: Read-only file system
Additional information: 2
General Cause And Solution of ORA-00205 Problem:
The ORA-00205 problem is reported because the system could not find a control file of the specified name and size. The name of control file is specified by the CONTROL_FILES initialization parameter within spfile/pfile. While mounting oracle database oracle finds that in the disk all those files are not there or size is unmatched as it is specified within spfile/pfile.
If you see ORA-00205 is reported while starting up/mount the oracle database then check that the proper control filename is referenced in the CONTROL_FILES initialization parameter in the initialization parameter. If not check your correct spfile/pfile or fix location of CONTROL_FILES parameter within spfile/pfile and try again to mount the database.
Whenever you are using mirrored control files, that is, more than one control file is referenced in the initialization parameter file, remove the control filename listed in the message from the initialization parameter file and restart the instance. If the message does not recur, remove the problem control file from the initialization parameter file and create another copy of the control file with a new filename in the initialization parameter file.
Step by step Solution
Step 01: After you see ORA-00205 immediately look for alert log files for further investigation.
Step 02: Check the control_files parameter in your initialization parameter. You can simply check by ,
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string D:\APP\ARJU\ORADATA\A\CONTROL0
1.CTL, D:\APP\ARJU\ORADATA\A\C
ONTROL02.CTL, D:\APP\ARJU\ORAD
ATA\A\CONTROL03.CTL
Now compare this location with the location of controlfile in your disk system.
From the alert log if you see error message like version 1 i.e you have noticed "ORA-00202: control file: '/oracle/controlfile/contro101.ctl'" then from your CONTROL_FILES initialization parameter remove the entry /oracle/controlfile/contro101.ctl if you have multiple version of controlfiles and start the database. If you fail then again check for alert logs. If it is same error message like points to another controlfile then try to remove that control file too if you have at least three versions of controlfiles. If you fail then possibly all of your controlfiles are lost. In that case you need to restore controlfile from previous backup or create a new one if you don't have any previous backup of controlfile. In the post Recover database after only lose of all controlfiles it is discussed how you can recover controlfile. In the post Restore the Controlfile from Backup using RMAN it is discussed how you can restore controlfile from previous RMAN backup using RMAN. In the post How to create controlfile it is discussed how to create a controlfile whenever you are lost all controlfile and you don't have any backups.
If you see other version of error versions then take necessary actions as alert log suggests. For example if you see error message like version 2 that is
"ORA-00202: controlfile: '/oracle/controlfile/CONTROL01.CTL'
ORA-27086: skgfglk: unable to lock file - already in use"
then ensure that oracle database is shutdown cleanly and no other process is holding lock of the control file. After you ensure that try starting database again.
If you see error message like version 3 that is
"ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file"
then possibly your control file got corrupted and follow the same steps as it is demonstrated in step 2 for error version 01.
If you see error message like version 4 that is
"ORA-00202: controlfile: '/oracle/data/ctl03.dbf'
ORA-27072: skgfdisp: I/O error
Linux Error: 30: Read-only file system"
then ensure that your control file has proper permission set from operating system. That means it must have read, write permission to oracle owner user.
Step 03:
After you identify and solve the problem ensure that you have multiple copies of your CONTROL_FILES parameter onwards. If you use spfile in your database startup then after your nomount state of database and physically copied controlfiles to all locations as you want, you can point multiple copies of controlfile by issuing "ALTER SYSTEM SET CONTROL_FILES=..., ..." command from database. A similar example is demonstrated in the topic ORA-00214: Controlfile Version Inconsistent on Startup or Shutdown
Related Documents:
How to Restore the Controlfile from Backup.
Saturday, January 9, 2010
Causes and Solutions of ORA-00054
Before reading this topic please have a look at the topic ora-00054: resource busy and acquire with nowait specified. At that post I have specified the reason and solution of ORA-00054 problem. Also have a look at DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT in order to have an idea about new feature exist in oracle 11g which will help to minimize ORA-00054 problem.
Explanation of ORA-00054 Error
Generally ORA-00054 is reported if a SQL statement would have blocked waiting for another user to complete some operation.
Issuing any of the following two type of SQL statements can cause ORA-54 problem.
i) SQL statement had a 'NOWAIT' clause so instead of blocking the statement returns this error.
or
ii) SQL statement was DDL operation and was blocked.
So for both DDL or SELECT .. FOR UPDATE NOWAIT can raise this error if an incompatible lock is held.
Diagnosis of the problem
A) The V$LOCK view lists the locks currently held by the Oracle Database and outstanding
requests for a lock or latch.
Let's have a look at column of v$lock view.
The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks.
The user type locks are:
i) TM - DML enqueue
ii) TX - Transaction enqueue
iii)UL - User supplied
The system type locks are:
- LDOME is Lock mode in which the session holds the lock.
- REQUEST is Lock mode in which the process requests the lock.
- Both LMODE and REQUEST can have 0 to 6 value which means
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
B) To find out the blocked process issue,
SQL> select * from v$lock where request!=0;
C) If you see locks being taken out on a child table when doing deletes on the parent then indexing the foreign key column on the child table will stop happening error ORA-00054.
D) In order to find the list of locks in a database issue following query,
Explanation of ORA-00054 Error
Generally ORA-00054 is reported if a SQL statement would have blocked waiting for another user to complete some operation.
Issuing any of the following two type of SQL statements can cause ORA-54 problem.
i) SQL statement had a 'NOWAIT' clause so instead of blocking the statement returns this error.
or
ii) SQL statement was DDL operation and was blocked.
So for both DDL or SELECT .. FOR UPDATE NOWAIT can raise this error if an incompatible lock is held.
Diagnosis of the problem
A) The V$LOCK view lists the locks currently held by the Oracle Database and outstanding
requests for a lock or latch.
Let's have a look at column of v$lock view.
SQL> desc v$lock;- Where TYPE indicates type of user or system lock.
Name Null? Type
----------------------------------------- -------- ------------
ADDR RAW(4)
KADDR RAW(4)
SID NUMBER
TYPE VARCHAR2(2)
ID1 NUMBER
ID2 NUMBER
LMODE NUMBER
REQUEST NUMBER
CTIME NUMBER
BLOCK NUMBER
The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks.
The user type locks are:
i) TM - DML enqueue
ii) TX - Transaction enqueue
iii)UL - User supplied
The system type locks are:
1) BL - Buffer hash table instance
2) NA..NZ - Library cache pin instance (A..Z = namespace)
3) CF - Control file schema global enqueue
4) PF - Password File
5) CI - Cross-instance function invocation instance
6) PI, PS - Parallel operation
7) CU - Cursor bind
8) PR - Process startup
9) DF - Data file instance
10) QA..QZ - Row cache instance (A..Z = cache)
11) DL - Direct loader parallel index create
12) RT - Redo thread global enqueue
13) DM - Mount/startup db primary/secondary instance
14) SC - System change number instance
15) DR - Distributed recovery process
16) SM - SMON
17) DX - Distributed transaction entry
18) SN - Sequence number instance
19) FS - File set
20) SQ - Sequence number enqueue
21) HW - Space management operations on a specific segment
22) SS - Sort segment
23) IN - Instance number
24) IR -Instance recovery serialization global enqueue
25) SV - Sequence number value
26) IS - Instance state
27) TA - Generic enqueue
28) IV - Library cache invalidation instance
29) TS - Temporary segment enqueue (ID2=0)
30) JQ - Job queue
31) TS - New block allocation enqueue (ID2=1)
32) KK - Thread kick
33) TT - Temporary table enqueue
34) LA .. LP - Library cache lock instance lock (A..P = namespace)
35) UN - User name
36) MM - Mount definition global enqueue
37) US - Undo segment DDL
38) MR - Media recovery
39) WL - Being-written redo log instance
40) ST Space transaction enqueue
- LDOME is Lock mode in which the session holds the lock.
- REQUEST is Lock mode in which the process requests the lock.
- Both LMODE and REQUEST can have 0 to 6 value which means
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
B) To find out the blocked process issue,
SQL> select * from v$lock where request!=0;
C) If you see locks being taken out on a child table when doing deletes on the parent then indexing the foreign key column on the child table will stop happening error ORA-00054.
D) In order to find the list of locks in a database issue following query,
SQL> column lock_id1 format a10Related Documents
SQL> set pagesize 120
SQL> column lock_type format a12
SQL> column mode_held format a10
SQL> column mode_requested format a10
SQL> column blocking_others format a20
SQL> column username format a10
SQL> SELECT session_id
, lock_type
, mode_held
, mode_requested
, blocking_others
, lock_id1
FROM dba_lock l
WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread')
/
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE BLOCKING_OTHERS LOCK_ID1
---------- ------------ ---------- ---------- -------------------- ----------
159 XR Null None Not Blocking 4
159 Control File Row-S (SS) None Not Blocking 0
159 RS Row-S (SS) None Not Blocking 25
153 AE Share None Not Blocking 99
158 Temp Segment Row-X (SX) None Not Blocking 3
161 PW Row-X (SX) None Not Blocking 1
132 AE Share None Not Blocking 99
156 AE Share None Not Blocking 99
132 TO Row-X (SX) None Not Blocking 62986
127 CU Exclusive None Not Blocking 640372732
127 AE Share None Not Blocking 99
127 FU Exclusive None Not Blocking 0
132 DML Row-X (SX) None Not Blocking 70833
127 DML Row-X (SX) None Not Blocking 5750
127 DML Row-X (SX) None Not Blocking 56048
153 DML Row-X (SX) None Not Blocking 70833
127 Transaction Exclusive None Not Blocking 65550
153 Transaction Exclusive None Not Blocking 589833
18 rows selected.
New features in Oracle database administration in 11g
DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT
DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT
Thursday, January 7, 2010
Troubleshoot ORA-12154 TNS-12154 TNS-03505: Failed to resolve name
In this document I will discuss step by step solutions for ORA-12154, TNS-12154 and TNS-03505. About ORA-12154 it is already discussed in Troubleshoot ORA-12154. This post will be applicable to troubleshoot for both ORA-12154, TNS-12154 and TNS-03505.
What Oracle Says about the Problem
If you open http://arjudba.blogspot.com/2008/06/how-to-get-oracle-error-message-from.html you will see about the description of TNS-12154 or ORA-12154 they are telling same like below.
For case TNS-03505: it says
Step 01: Overview of the Problem
Both ORA-12154, TNS-12154 and TNS-03505 indicates that SQL*Net could not find the alias specified for a connection in the TNSNAMES.ORA file or other naming adapter.
As the problem is regarding TNSNAMES.ORA and SQLNET.ORA so in order to dig into solution it is important to print out or a view of both the TNSNAMES.ORA and the SQLNET.ORA files. Looking at these files at the same time is helpful since references will be made to both.
Step 02: Identify the location of TNSNAMES.ORA and SQLNET.ORA
It is necessary to locate the location of TNSNAMES.ORA and SQLNET.ORA. If you simply use tnsping dummy it will say the location of SQLNET.ORA and there TNSNAMES.ORA should be in the same location.
Through above ways we can determine current path of these two files. However it is important to know the default path of these files. Based on the operating system default path are mentioned below.
i)Windows 3.x client
ORAWIN\NETWORK\ADMIN directory
ii)Windows 95/98 client
SQL*Net 2.x - ORAWIN95\NETWORK\ADMIN
Net8 - ORAWIN95\NET80\admin
Net8i - ORACLE\ORA81\NETWORK\ADMIN
iii)Windows NT client
SQL*Net 2.x - ORANT\NETWORK\ADMIN
Net8 - ORANT\NET80\ADMIN
Net8i - ORACLE\ORA81\NETWORK\ADMIN
iv)UNIX Client
$ORACLE_HOME/NETWORK/ADMIN
or /etc
or /var/opt/oracle
Step 03: Print out or View both TNSNAMES.ORA and SQLNET.ORA.
Following is the correct TNSNAMES.ORA from my system
Step 04: Check several factors one by one.
To Troubleshoot problem let's start with some complex TNSNAMES.ORA and SQLNET.ORA.
TNSNAMES.ORA Experimental Sample
For example, my "tnsping a" worked because in my TNSNAMES.ORA I have an entry regarding alias A which I have pasted my TNSNAMES.ORA above.
tnsping dummy will fail because there is no entry in my TNSNAMES.ORA with name dummy and also connection with dummy will fail too.
Make sure by using IP Address entry instead of hostname inside TNSNAMES.ORA. So use HOST=192.168.1.1 something like that instead of HOST=ARJU.
ii) Check both file again and ensure that there is no CTRL-A (^A) or CTRL-C (^C) characters inserted at the ends of any lines.
iii) If both are fine check the TNSNAMES.ORA alias. Here in our experimental sample it is DEV.WORLD which means .WORLD is our domain and it should be specified it our SQLNET.ORA. So add the parameter NAMES.DEFAULT_DOMAIN = world anywhere in the SQLNET.ORA file. Save the file, and try the connection again.
iv) If within the TNSNAMES.ORA there is no default domain in the alias*(for example it is simply DEV but not DEV.WORLD) then remove NAMES.DEFAULT_DOMAIN = world from the SQLNET.ORA file if there existed. After removing the parameter, save the SQLNET.ORA file, and try connecting again.
v) If the parameter NAMES.DIRECTORY_PATH exists in the SQLNET.ORA file, make sure the value in parenthesis lists TNSNAMES. For example following two examples are valid entry.
vi) At this phase the configuration files are most likely technically accurate.
Now you have to ensure that your TNSNAMES.ORA is in the correct location and you have used correct TNS entry while connecting. The TNS location can be set by TNS_ASMIN environmental variable.
On your Unix environment,
% echo $TNS_ADMIN
If nothing is returned, try setting the TNS_ADMIN environment variable to explicitly point to the location of the TNSNAMES.ORA file.
In C Shell, the syntax is:
% setenv TNS_ADMIN full_path_to_tnsnames.ora_file
In K Shell, the syntax is:
% TNS_ADMIN=full_path_to_tnsnames.ora_file; export TNS_ADMIN
After setting try the connection/tnsping again.
In case of Windows 3.x,
- TNS_ADMIN is set in the ORACLE.INI.
- Look in the C:\WINDOWS directory for the ORACLE.INI file. Look for the parameter TNS_ADMIN. TNS_ADMIN is a parameter that can be set to have SQL*Net point to an alternate location for the configuration files.
- Search for duplicate TNSNAMES.ORA and SQLNET.ORA files.
- Make sure none exist.
- Once you are sure try the connection/tnsping again.
In case of Windows 95/98,
- Open the file ORACLE_HOME\bin\oracle.key.
- Search for Registry under the location specified by the .key file, such as HKEY_LOCAL_MACHINE\Software\Oracle\Home1 for a parameter called TNS_ADMIN.
- TNS_ADMIN is a parameter that can be set to have SQL*Net point to an alternate location for the configuration files.
- Search for duplicate TNSNAMES.ORA and SQLNET.ORA files.
- Make sure none exist.
- Once you are sure try the connection/tnsping again.
In case of Windows XP,
- Make sure TNS_ADMIN environemental points to correct location. Ensure that there must not exist both user variable or system variable environmental variable. If nothing exist you can add one user variable. To set user/system variable,
-- Right click on My computer
-- Click Properties.
-- Click Advanced Tab.
-- Click Environemental Variables.
-- You can have both User and System Variables there.
You can add, edit or delete variables. You can simply add TNS_ADMIN and its location in the user variable section. Note that Variable Name would be "TNS_ADMIN" and variable would be "The location of the TNSNAMES.ORA in your client environement."
- After you set try connection again.
Your problem would be resolved.
Related Documents
ORA-12560: TNS:protocol adapter error on windows machine while starting oracle
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Startup fails with oracle error ORA-00119, ORA-00132
What Oracle Says about the Problem
If you open http://arjudba.blogspot.com/2008/06/how-to-get-oracle-error-message-from.html you will see about the description of TNS-12154 or ORA-12154 they are telling same like below.
Oracle Error : TNS-12154
TNS: could not resolve the connect identifier specified
Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.
Action:
- If you are using local naming (TNSNAMES.ORA file):
- Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)
- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.
- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.
- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.
- If you are using directory naming:
- Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).
- Verify that the LDAP directory server is up and that it is accessible.
- Verify that the net service name or database name used as the connect identifier is configured in the directory.
- Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier
- If you are using easy connect naming:
- Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).
- Make sure the host, port and service name specified are correct.
- Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.
For case TNS-03505: it says
TNS-03505: Failed to resolve name
Cause: The service name you provided could not be found in TNSNAMES.ORA, an Oracle Names server, or a native naming service.
Action: Verify that you entered the service name correctly. You may need to ensure that the name was entered correctly into the network configuration.
which indicates for both ORA-12154, TNS-12154 and TNS-03505 we will have a generic solution.
Step 01: Overview of the Problem
Both ORA-12154, TNS-12154 and TNS-03505 indicates that SQL*Net could not find the alias specified for a connection in the TNSNAMES.ORA file or other naming adapter.
As the problem is regarding TNSNAMES.ORA and SQLNET.ORA so in order to dig into solution it is important to print out or a view of both the TNSNAMES.ORA and the SQLNET.ORA files. Looking at these files at the same time is helpful since references will be made to both.
Step 02: Identify the location of TNSNAMES.ORA and SQLNET.ORA
It is necessary to locate the location of TNSNAMES.ORA and SQLNET.ORA. If you simply use tnsping dummy it will say the location of SQLNET.ORA and there TNSNAMES.ORA should be in the same location.
E:\Documents and Settings\Arju>tnsping dummyFrom the above output we see both of these files are located in the path D:\app\Arju\product\11.1.0\db_1\network\admin\.
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 08-JAN-2010 11:00:46
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
D:\app\Arju\product\11.1.0\db_1\network\admin\sqlnet.ora
TNS-03505: Failed to resolve name
Through above ways we can determine current path of these two files. However it is important to know the default path of these files. Based on the operating system default path are mentioned below.
i)Windows 3.x client
ORAWIN\NETWORK\ADMIN directory
ii)Windows 95/98 client
SQL*Net 2.x - ORAWIN95\NETWORK\ADMIN
Net8 - ORAWIN95\NET80\admin
Net8i - ORACLE\ORA81\NETWORK\ADMIN
iii)Windows NT client
SQL*Net 2.x - ORANT\NETWORK\ADMIN
Net8 - ORANT\NET80\ADMIN
Net8i - ORACLE\ORA81\NETWORK\ADMIN
iv)UNIX Client
$ORACLE_HOME/NETWORK/ADMIN
or /etc
or /var/opt/oracle
Step 03: Print out or View both TNSNAMES.ORA and SQLNET.ORA.
Following is the correct TNSNAMES.ORA from my system
# tnsnames.ora Network Configuration File: D:\app\Arju\product\11.1.0\db_1\network\admin\tnsnames.oraFollowing is the correct SQLNET.ORA from my system.
# Generated by Oracle configuration tools.
A =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ARJU)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = a)
)
)
# sqlnet.ora Network Configuration File: D:\app\Arju\product\11.1.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Step 04: Check several factors one by one.
To Troubleshoot problem let's start with some complex TNSNAMES.ORA and SQLNET.ORA.
TNSNAMES.ORA Experimental Sample
DEV.WORLD =SQLNET.ORA Experimental Sample
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 10.1.1.1)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
TRACE_LEVEL_CLIENT = OFFi) Determine which tns entry you used to connect. For example if you used dummy then issue a tnsping dummy and ensure that the entry DUMMY must exist in the TNSNAMES.ORA.
SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH = (TNSNAMES)
AUTOMATIC_IPC = OFF
For example, my "tnsping a" worked because in my TNSNAMES.ORA I have an entry regarding alias A which I have pasted my TNSNAMES.ORA above.
E:\Documents and Settings\Arju>tnsping a
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 08-JAN-2010 11:00:34
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
D:\app\Arju\product\11.1.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ARJU)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = a)))
OK (330 msec)
tnsping dummy will fail because there is no entry in my TNSNAMES.ORA with name dummy and also connection with dummy will fail too.
Make sure by using IP Address entry instead of hostname inside TNSNAMES.ORA. So use HOST=192.168.1.1 something like that instead of HOST=ARJU.
ii) Check both file again and ensure that there is no CTRL-A (^A) or CTRL-C (^C) characters inserted at the ends of any lines.
iii) If both are fine check the TNSNAMES.ORA alias. Here in our experimental sample it is DEV.WORLD which means .WORLD is our domain and it should be specified it our SQLNET.ORA. So add the parameter NAMES.DEFAULT_DOMAIN = world anywhere in the SQLNET.ORA file. Save the file, and try the connection again.
iv) If within the TNSNAMES.ORA there is no default domain in the alias*(for example it is simply DEV but not DEV.WORLD) then remove NAMES.DEFAULT_DOMAIN = world from the SQLNET.ORA file if there existed. After removing the parameter, save the SQLNET.ORA file, and try connecting again.
v) If the parameter NAMES.DIRECTORY_PATH exists in the SQLNET.ORA file, make sure the value in parenthesis lists TNSNAMES. For example following two examples are valid entry.
NAMES.DIRECTORY_PATH=(TNSNAMES)Though NAMES.DIRECTORY_PATH parameter is not needed, but if it exists then it should look ok. After removing it you can try connecting again.
NAMES.DIRECTORY_PATH=(TNSNAMES, HOSTNAME)
vi) At this phase the configuration files are most likely technically accurate.
Now you have to ensure that your TNSNAMES.ORA is in the correct location and you have used correct TNS entry while connecting. The TNS location can be set by TNS_ASMIN environmental variable.
On your Unix environment,
% echo $TNS_ADMIN
If nothing is returned, try setting the TNS_ADMIN environment variable to explicitly point to the location of the TNSNAMES.ORA file.
In C Shell, the syntax is:
% setenv TNS_ADMIN full_path_to_tnsnames.ora_file
In K Shell, the syntax is:
% TNS_ADMIN=full_path_to_tnsnames.ora_file; export TNS_ADMIN
After setting try the connection/tnsping again.
In case of Windows 3.x,
- TNS_ADMIN is set in the ORACLE.INI.
- Look in the C:\WINDOWS directory for the ORACLE.INI file. Look for the parameter TNS_ADMIN. TNS_ADMIN is a parameter that can be set to have SQL*Net point to an alternate location for the configuration files.
- Search for duplicate TNSNAMES.ORA and SQLNET.ORA files.
- Make sure none exist.
- Once you are sure try the connection/tnsping again.
In case of Windows 95/98,
- Open the file ORACLE_HOME\bin\oracle.key.
- Search for Registry under the location specified by the .key file, such as HKEY_LOCAL_MACHINE\Software\Oracle\Home1 for a parameter called TNS_ADMIN.
- TNS_ADMIN is a parameter that can be set to have SQL*Net point to an alternate location for the configuration files.
- Search for duplicate TNSNAMES.ORA and SQLNET.ORA files.
- Make sure none exist.
- Once you are sure try the connection/tnsping again.
In case of Windows XP,
- Make sure TNS_ADMIN environemental points to correct location. Ensure that there must not exist both user variable or system variable environmental variable. If nothing exist you can add one user variable. To set user/system variable,
-- Right click on My computer
-- Click Properties.
-- Click Advanced Tab.
-- Click Environemental Variables.
-- You can have both User and System Variables there.
You can add, edit or delete variables. You can simply add TNS_ADMIN and its location in the user variable section. Note that Variable Name would be "TNS_ADMIN" and variable would be "The location of the TNSNAMES.ORA in your client environement."
- After you set try connection again.
Your problem would be resolved.
Related Documents
ORA-12560: TNS:protocol adapter error on windows machine while starting oracle
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Startup fails with oracle error ORA-00119, ORA-00132
Subscribe to:
Posts (Atom)