Ora-01722: Invalid Number When Running The Analytical Engine In Demantra [ID 1208653.1]
Applies to:
Oracle Demantra Demand Management - Version: 7.1.1.2 and later [Release: 7 and later ] Information in this document applies to any platform.
Purpose
After setting up a new machine for Analytical Engine, it fails with Ora-01722: Invalid Number. We need to investigate what is the set up that made the engine to fails.
Last Review Date
November 17, 2011
Instructions for the Reader
A Troubleshooting Guide is provided to assist
in debugging a specific issue. When possible, diagnostic tools are included in the document
to assist in troubleshooting.
Troubleshooting Details
--- Symptoms ---
Engine log files shows: 12:30:28:010 ERROR Database Error [EngTreeIterator.cpp,1115] 12:30:28:010 ERROR - Source: Microsoft OLE DB Provider for Oracle 12:30:27:756 ERROR DB_Command::Execute threw an exception: 12:30:28:010 ERROR - Description: ORA-01722: invalid number 12:30:27:756
ERROR UPDATE mdp_matrix SET item_node = ?,loc_node = ?,level_id = 2,
metrics_obs = ?, metrics_mape = ?, metrics_mre = ?, metrics_rmse = ?,
metrics_pbias = ? WHERE t_ep_item_EP_ID = ? AND t_ep_inventory_EP_ID = ?
AND item_node=0 AND loc_node=0 AND level_id > 0 AND do_fore = 1 AND
prediction_status = 1 12:30:28:010 ERROR - Message: IDispatch error #3079 12:30:28:010 ERROR - Error Number: 0x80040e07 - Undefined Error. 12:30:27:756 ERROR - ErrorDescription: ORA-01722: invalid number
--- Changes ---
This issue happens after setting up a new machine for the Analytical Engine. Running the engine from the old machine or from a different one is working fine, without errors.
--- Cause ---
Usually this kind of error is caused by decimal sign (comma instead of point).
--- Solution ---
Below are some steps followed to investigate ORA-01722: invalid number when running the analytical engine:
tried to execute the failing update from some SQL tool by replacing
the '?' with the parameters values. Confirmed also that the specific
combination exist in mdp_matrix (t_ep_item_EP_ID , t_ep_inventory_EP_ID)
tried to restart db
execute mdac_typ.exe
reregister the engine
confirm that you are having the exact client version as the database version
if your db is on Unix, please check also the solution from microsoft : http://support.microsoft.com/kb/216978/en-us
run select 99/100 from dual; on the specific machine to confirm decimal sign is point (.)
confirm the regional settings are set to English on the server machine, specially for the user that start the engine
check NLS settings (NLS_LANG and NLS_NUMERIC_CHARACTERS) on the server side: in registry, environment variables, oracle client
OERR: ORA 2253 constraint specification not allowed here [ID 19486.1]
Error: ORA 2253
Text: constraint specification not allowed here
-------------------------------------------------------------------------------
Cause: A constraint specification is not allowed at this point in the
statement.
Action: Remove or relocate the constraint specification and try again.
Shared
pool memory is allocated in chunks and as shared pool fragments with
time you will see more smaller chunks being allocated for all sql
statements requiring hard parse.
So, to reduce shared pool
fragmentation due to large memory allocation requirements, Oracle
reserves a minimum of 5% shared pool size for such allocations.
Any
chunk allocation with more than "_shared_pool_reserved_min_alloc =
4400" bytes, would go to reserved shared pool unless such memory
allocation can be satisfied from the shared pool memory without aging
out any other chunks.
Interestingly all sessions connected to the
database require 27224 bytes (in Oracle 9i) each from shared pool,
which is called "session param v" chunk, and it has to come out from one
chunk.
If you have an application which keeps closing and
opening new connections, you would need to set minimum
shared_pool_reserved_size as follows:
Maximum of (shared_pool_size*(5/100), "sessions" * 27224)
This is one more reason why it is a good idea to use connection pooling and don't bother much about above equation.
You can check the utilization of shared pool reserved size by querying v$shared_pool_reserved view.
System
change number(SCN) is represented with SCN_WRAP and SCN_BASE. Whenever
SCN_BASE reaches 4294967290 (2 power 32), SCN_WRAP goes up by one and
SCN_BASE will be reset to 0. This way you can have a maximum SCN at
1.8E+19.
SCN = (SCN_WRAP * 4294967290) + SCN_BASE
Checkpoint
number is the SCN number at which all the dirty buffers are written to
the disk, there can be a checkpoint at
object/table-space/data-file/database level.
Checkpoint number is never updated for the data-files of read only table spaces.
Oracle
doesn't use any sequence to generate SCN_BASE numbers, it make calls to
"kcmgas" function (per Steve Adams), which is a permanent memory
structure for the instance. It is reported in v$sysstat under "calls to
kcmgas" name.
Does Oracle do either crash recovery or transaction
recovery after shutdown abort if the checkpoint was taken right before
the instance crash?
Yes, Oracle perform rollforward first if there are any changes beyond last checkpoint and rollback any uncommitted transactions.
SCN numbers are being reported at frequent intervals by SMON in "SMON_SCN_TIME" table.
You can also query v$transaction to arrive at the SCN for that transaction.
Controlfile records information about last checkpoint and archived sequence along with other information.
Oracle Goldengate can be used as a replication tool, ETL, and even as a DR solution.
Oracle
Goldengate (GG) is probably the best replication software and it is
very easy to configure and deploy it in large scale environment. Here
are some of the things you need to be aware of:
1)
All GG configuration files are ascii text based files. Very easy to
make changes but it is prone to human errors in an environment having
many DBA's working on it.
2) In order to use
parallel apply threads, GG breaks down the database transaction into
multiple transactions based on the hashing key defined for range split
of the data. So, transactional consistency will not be guaranteed during
real time but there won't be any data loss, but make sure that your
application can tolerate this.
3) If there is
no primary key or unique index exists on any table, GG will use all the
columns as supplemental logging key pair for both extracts and
replicats. But if you define key columns in the GG extract parameter
file and if you don't have the supplemental logging enabled on that key
columns combination, then GG will assume missing key columns record data
as "NULL", which is a huge deal, and this will introduce logical data
corruption on the target.
4) GG started
supporting bulk data loads with their 11.1 release but any NOLOGGING
data changes will be silently ignored without any warning.
5) GG doesn't support compression on the source database.
6)
GG does support DDL replication but it is not easy to do selective DDL
replication, it replicates every DDL that happens on the source database
which is not desirable for some customers.
7) Tables being replicated to on the target can also be written to by any other application or DBA's.
8)
GG supports ignoring data conflicts for updates after the first
instantiation of the target database until it catches up. But it is very
easy to forget turning off that parameter and any updates being lost
will not be alerted by GG.
9) GG still works
by reverse engineering the Oracle redolog. This may not be totally true
with GG 11, but I expect GG to interpret Oracle redo more directly in
later versions of 11 or 12.
10) GG dynamically
decides to change the key columns that form the supplemental logging
based on the state of primary key (i.e. in VALIDATED or NONVALIDATED
state), which can introduce data corruptions on the target databases as
the expected key columns data is missing in the trail files and they
will be set to NULL. They now have the patch available for this, you can
set "_USEALLKEYCOLUMNS and ALLOWNONVALIDATEDKEYS" parameters in GLOBALS
file to get around this problem.
Use cases:
I
think Oracle is not promoting logical standby as much as they should
have. Oracle logical standby guarantees data consistency, data
integrity, maintains order of transactions, and doesn't let target
database tables to be modified by others which by itself offer great
confidence in data quality.
Oracle Xstreams
offers greatest flexibility and superior performance in extracting data
from the source database and applying the same to the target database.
For
Oracle database upgrades or having a logical DR standby it is better to
use Oracle logical standby, use Xstreams if you want more flexibility
and high performance in moving data across databases, and use GG for
keeping the downstream database up to date for reporting, ETL purposes,
or to move data across hybrid databases.
Oracle Assets - Version 11.5.10.2 and later Information in this document applies to any platform.
FAMACR
This knowledge document is a replacement for 820793.1 which has been deleted.
Purpose
Troubleshooting Guide for Mass Addition
Troubleshooting Steps
1. Why post mass addition
program errors with: 'FA_MASSADD_CHILD_LOC_NULL' while trying to change
Mass Addition from 'ON HOLD' to 'POST' ?
Solution
The error is because the line which
is erroring out or a merged child line of this line has a NULL Location
or Depreciation Expense account. To fix the error Populate the
Depreciation Expense and Location for all associated lines.
2. When trying to run the post mass addition program, why is a particular book is not appearing in LOV ?
Solution
For a particular book to appear in Post mass addition program book LOV, below 2 parameters must be met: 1.
The desired book should have a mass addition line which is in Post
status. Lines must be in the POST queue to meet the select criteria for
the LOV.
2. Depreciation status should be in completed status for the desired book and all the books associated with the desired book.
So before running the post mass addition program make sure that above two parameters are met
3. How to correct error ORA-01400: cannot insert NULL into FA_ADDITIONS_B.CURRENT_UNITS ?
Solution
The error is because Assignments
form (FA_MASSADD_DISTRIBUTIONS table) of the invoice line which is
erroring has a null value for units.Populate the units on the
assignments form in Prepare Mass Additions to a the proper units (a
value other than null needs to be entered here) and the error will no
longer appear.
4. While attempting to set mass addition line to POST status, the following error occurs:
The maximum value size for segment Company is 2. Truncating value OTHER CAP.EXP.LIGHT FENCES 5 to OT
Solution
The issue is caused by the following
setup:Category flexfield is defined with segment's separator as the
period (.) and the category includes another period '.' in segment's
values.
As a result the application cannot validate the Category
segment's values and return a misleading message referencing company
segment of the accounting flexfield (which is the next field to validate
in the form). So to fix this issue modify the Category flexfield setup :
Navigate to Setup> Financials > Flexfield > Key > segments Query the Category Flexfield Unfreeze Change
the segment's separator from the period (.) to another one such as the
pipe (|) . This should be a character which is never included in the
major or minor categories values
5. How to resolve the error 'Cache: Error retrieving row from FA_METHODS table' in Post mass addition program ?
Solution
The error is because the
depreciation method assigned to the Mass Addition was not a valid
Depreciation method.The method and the life combination which is
attached in Prepare mass addition form must also exist in FA_METHODS
table.
6. Post mass addition program errors with: Error: Incorrect Asset Type and Category combination for &CATEGORY_ID.
Solution
The error is because the category
assigned to this mass addition has 'Capitalize' checkbox checked and the
current invoice which is erroring is a expensed invoice. A expensed
asset can be added / posted to a category in which capitalize check box
is checked.
To resolve the issue please follow the following steps: 1. Connect to an FA responsibility 2. Navigate to setup > Asset System > Categories 3. Query the categories having Capitalize checkbox Unchecked or if no one exist , define a new one 4.
Navigate to Mass additions > Prepare mass addition Query the failing
mass addition at POST status and change the category to a correct one 5. Rerun Post mass additions and review the asset was successfully created as EXPENSED asset
7. How to resolve EXPENSE CCID CANNOT BE NULL error messages during Mass Additions ?
Solution
Error is because no depreciation expense account was entered for the invoice which is erroring. Navigate to Mass Additions > Prepare Mass Additions Query up the Mass Addition line in question. Click Open and Click the Assignments button and ensure that expense account is populated. Save and try the Mass Additions Post again.
8. When attempting to query
project details in Mass Additions form or in Assets form, the following
error is shown ORA-01403: no data found
ORA-06512: at "APPS.PA_UTILS", line 1315
Solution
The reason for the error was the following profile options not set to the proper values on the instance : MO: Default Operating Unit MO: Operating Unit MO: Security Profile
Please
connect to System Administrator responsibility and make sure that these
profile options are set at Site, Responsibility, and User levels.
Error is due to Incorrect value for ASSET_CATEGORY_ID in table FA_MASS_ADDITIONS for the mass addition lines.
The
Prepare Mass Additions form validates for the ASSET_CATEGORY_ID of the
uploaded lines in the FA_MASS_ADDITIONS table. The error occurs if the
value does not exist in FA_CATEGORIES.
Make sure that the data
is inserted correctly in the FA_MASS_ADDITIONS.ASSET_CATEGORY_ID column
when using SQL loader to upload the mass additions.
10. Why error
APP-OFA-47777: This Book is not setup for this Category appears when
attempting to assign asset category in the prepare mass addition form:
Solution
The error is because The date placed
in service of the mass addition line is before the selected category's
start date placed in service.
- Correct the value of the date
placed in service in the prepare mass additions screen to be within the
date placed in service range in the default rules of the asset category.
- Update the value of the INVOICE_DATE column in the FA_MASS_ADDITIONS table to the correct date.
11. How to resolve the below error which appears when attempting to run Post Mass Additions,
Please run the Gain/Loss Program before performing this transaction.
Mass Addition ID: XXXXX ==> ** FAILED **
Solution
The issue is caused by the following setup:Allow CIP assets check box was checked
As
the CIP asset exists also in the tax book, the previous partial
retirement transaction entered in the corporate book was also performed
automatically via API in the tax book. Post Mass Additions check and
finds that gain/loss was not run for the tax book before trying to add
an invoice to the CIP asset.
1. Go into the Fixed Assets responsibility. 2. Run Gain/Loss also for the tax book. 3. Submit Post Mass Additions again.
12. Why asset_type field is not populated for Asset lines that are loaded into the FA_MASS_ADDITIONS table by SQL*Loader
Solution
This normally happens when Invalid
value is inserted in fa_mass_additions for the asset_type field. The
values for asset_type need to be inserted in the FA_MASS_ADDITIONS table
in upper case (for example; CAPITALIZED, CIP or EXPENSED).
1.Modify the SQL*Loader file such that the asset_type field in FA_MASS_ADDITIONS is inserted in upper case.
2.
Re-test the issue in the Prepare Mass Additions form and now, the Asset
Type will automatically default into the form (in mixed case) driving
from the FA_LOOKUPS table.
13. Post mass addition program is erroring with: APP-482858 ASSET NUMBER already exists. Please enter a new value.
Solution
The cause is that multiple assets have been loaded to FA_MASS_ADDITIONS with the same asset number.
Either
change the asset number values to a unique value or NULL the values out
and let the system create them during the mass addition posting
process.
14. Post mass addition program is erroring with: APP-OFA-47011: Error: Failed to process the add_to_asset function call
Solution
This error can appear due to two reasons.
One
is Depreciation has been run in this book, or an associated book and
Cost adjustments cannot be made when depreciation has been processed for
the period for the asset that is being adjusted.
Run Rollback Depreciation in book that is ahead, or advance all books to the next period, and then add the mass addition line.
Second
reason could be that the asset to which the failing invoice line is
added already has a amortized transaction and amortization flag for the
invoice in mass addition form is set to NO. Once the asset is amortized
all the future adjustment should be amortized and cannot be
expensed.That is the reason post mass addition program ends in error.
To correct this Check the amortization flag in the mass addition form and post the mass addition.
15. Post mass addition program is erroring with: APP-47010: Error: Failed to get Prorate Date and Depreciation Start Date.
Solution
This problem occurs because the Mass
Addition lines you are trying to post have a Date Placed in Service
(DPIS) that does not exist in the prorate convention defined for the
category which is assigned to the failing mass addition line.
To fix this issue: a)
Query the Mass Addition line in the Prepare Mass Additions form and
note the category and DPIS defined for that mass addition line. b) Query the Category and note the Prorate Convention assigned to the category. c)
Query Prorate Convention and ensure the DPIS on the mass addition line
exists in a prorate period, extend the calendar to include this date if
necessary
16. Why error "Please
choose an existing combination" appears when trying to save a record in
Asset Workbench or Prepare Mass Addition form ?
Solution
Error is because Profile Option -
Flexfields: Validate on Server is not set to YES (dynamic inserts is not
allowed).Set profile option Flexfields: Validate on Server to YES at
site level and the error will no longer appear.
17. When trying to retire
an asset, below error message appears: APP-47434 you must post mass
additions before you retire the asset.
Solution
This error appears when Records exists in FA_MASS_ADDITIONS which is attached to this asset and still in POST status.
To
fix this error either post the COST ADJUSTMENT to the asset, or remove
the asset from the mass addition record in the Prepare Mass Additions
form.
18. Post mass addition
program is erroring with: APP-00988 ORACLE error 1403 in get_date And
APP-47010 Error: failed to get prorate date and depreciation start date.
Solution
The error is because the Mass
Addition line will be added to an Asset Category with a Prorate
Convention that has gaps in the periods, or doesn't include the prorate
date corresponding to the DPIS.
To fix the error: 1) Query the Mass Addition line in the Prepare Mass Additions form and note the assigned Asset Category and DPIS. 2) Query the Asset Category and note the assigned Prorate Convention. 3)
Query the assigned Prorate Convention and ensure the DPIS from the mass
addition line exists in a prorate period, extending the calendar to
include this date, if necessary. Also, verify there are no gaps or
overlapping periods.
19. How to resolve
'APP-47777 This book is not setup for this asset category'. appearing
while trying to post the mass addition line ?
Solution
The error is because DPIS for the
Mass Addition line is prior to the DPIS assigned to the category or the
book, Check the Date Placed in Service (DPIS) for the Mass addition line
and the category.
If the DPIS for the Mass Addition line is
prior to the DPIS assigned to the category or the book then this is the
problem.The Category/Book DPIS acts as an as an effective date for
adding new assets.
20. Post mass addition
program ends with error: Error: function fa_massadd_pkg.do_mass_addition
returned failure And Cache: Error retrieving row from
FA_CATEGORY_BOOK_DEFAULTS table for:
Solution
The issue is caused by the following setup: The DATE_PLACED_IN_SERVICE (DPIS) has been entered as a date before the start DPIS for the specific category.
To
correct the issue Change the DPIS in the Mass Additions Interface, to a
date greater than the start DPIS of the particular category, as defined
in the Asset Categories > Dafault Rules form.
21. Post Mass Additions is
failing with the following error: You cannot expense an adjustment to an
asset for which you have already Amortized an adjustment.Please
Amortize this adjustment.
Solution
Error message is a intended one
because once the asset has a amortized adjustment all future
addjustments needs to be amortized and Expensed adjustments can not be
made on that asset.
The intended functionality does not allow
expensed adjustments on an amortized asset.The failing cost adjustment
must be 'AMORTIZED' to be posted successfully.
1. Run the following script to update AMORTIZE_FLAG to YES for all affected mass additions:
update fa_mass_additions set amortize_flag = 'YES' where mass_addition_id = &mass_addition_id;
2. Commit; 3. Resubmit Post Mass Additions
22. Why Post Mass addition program is failing with Error Message: Asset Key CCID cannot be NULL ?
Solution
The error is because Asset Key CCID column is not populated, but the Asset Key FF is set as Required.
To
fix the error Either populate the Asset Key CCID in FA_MASS_ADDITIONS
table, or uncheck the Required checkbox for the Asset Key Flexfield
segment(s) and then rerun the post mass addition program.
23. Post mass addition
program is erroring with: Cache: Error retrieving row from
FA_CATEGORY_BOOK_DEFAULTS table for: , Category ID: XXX , Book: xxxx
Solution
The error is because the category
shown in the log file does not have default rules setup in category form
or the category is not assigned to all the tax book.
To by pass
the error ensure that the Asset Category assigned to the mass addition
line is attached to all the corresponding tax books attached to the book
for which post mass addition program is run and also make sure that
Asset Category has the Default Rules setup.
24. Mass Additions Post
fails with the following errors in the log file: Request ID: XXX ,
Cache: Error retrieving row from FA_METHODS table , Depreciation Method:
STL , Life in Months: 60
Solution
The error is because the method and
life combination entered for the invoice is not a valid one. The method
and life combination must be defined and exist in the FA_METHODS table.
Update
the method and life values for the mass addition line in Prepare Mass
Additions form or directly in the FA_MASS_ADDITIONS table. Once this
line has a valid value, this error should no longer be returned.
How to resolve 'APP-47777 This book is not setup for this asset
category'. appearing while trying to post the mass addition line ?
Solution
The error is because DPIS for the
Mass Addition line is prior to the DPIS assigned to the category or the
book, Check the Date Placed in Service (DPIS) for the Mass addition line
and the category.
If the DPIS for the Mass Addition line is
prior to the DPIS assigned to the category or the book then this is the
problem.The Category/Book DPIS acts as an as an effective date for
adding new assets.
ORA-1017 May Cause ORA-3136 WARNING : Inbound Connection Timed Out in Alert Log [ID 793259.1]
Applies to:
Oracle Net Services - Version 10.1.0.2.0 to 11.2.0.3 [Release 10.1 to 11.2] Information in this document applies to any platform.
Checked for relevance on 30-SEP-2011.
Goal
How to reproduce the error ORA-3136 WARNING : inbound connection timed out ? This is the most common reason when you notice the warning message in the alert log. For other reasons you need to check : Note 465043.1 - Troubleshooting ORA - 3136 WARNING Inbound Connection Timed Out
Fix
The reason for the ORA-3136 error being thrown in the alert log is
when a client fails to complete the authentication process in the time
allowed by INBOUND_CONNECT_TIMEOUT. Very often, the 3136 error is
actually the result of an ORA-01017 or "invalid username /password".
When this error is returned to a client and, while at the same prompt,
if incorrect credentials are supplied again OR no other attempt is made
in under the 1 minute threshold (default), the instance will throw the
message in the alert.log: WARNING: inbound connection timed out
(ORA-3136)
Lets try to reproduce it.
Place the following
sqlnet.ora parameter on the server. This would be the sqlnet.ora file
that is referenced by the listener. Again, 60 seconds is the default
setting.
SQLNET.INBOUND_CONNECT_TIMEOUT=60
Make the tnsnames entry on the server with dedicated as server like
Keep the SQL*Plus session open for 60 seconds and in the alert log after 60th second , you will notice ORA-3136
$tail -f alert_orcl10203.log Thu Mar 19 17:52:36 2009 WARNING: inbound connection timed out (ORA-3136)
In the server sqlnet tracing you would see following :
[19-MAR-2009 17:51:35:897] nspsend: 33 4F 52 41 2D 30 31 30 |3ORA-010| [19-MAR-2009 17:51:35:897] nspsend: 31 37 3A 20 69 6E 76 61 |17:.inva| [19-MAR-2009 17:51:35:897] nspsend: 6C 69 64 20 75 73 65 72 |lid.user| [19-MAR-2009 17:51:35:897] nspsend: 6E 61 6D 65 2F 70 61 73 |name/pas| [19-MAR-2009 17:51:35:897] nspsend: 73 77 6F 72 64 3B 20 6C |sword;.l| [19-MAR-2009 17:51:35:897] nspsend: 6F 67 6F 6E 20 64 65 6E |ogon.den| [19-MAR-2009 17:51:35:897] nspsend: 69 65 64 0A |ied. | [19-MAR-2009 17:51:35:897] nspsend: 156 bytes to transport [19-MAR-2009 17:51:35:897] nspsend: normal exit [19-MAR-2009 17:51:35:897] nsdofls: exit (0) [19-MAR-2009 17:51:35:897] nsdo: nsctxrnk=0 [19-MAR-2009 17:51:35:897] nsdo: normal exit [19-MAR-2009 17:51:35:897] nsdo: entry [19-MAR-2009 17:51:35:897] nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3 [19-MAR-2009 17:51:35:897] nsdo: rank=64, nsctxrnk=0 [19-MAR-2009 17:51:35:897] nsdo: nsctx: state=8, flg=0x400c, mvd=0 [19-MAR-2009 17:51:35:897] nsdo: gtn=156, gtc=156, ptn=10, ptc=2011 [19-MAR-2009 17:51:35:897] nsdo: switching to application buffer [19-MAR-2009 17:51:35:897] nsrdr: entry [19-MAR-2009 17:51:35:897] nsrdr: recving a packet [19-MAR-2009 17:51:35:897] nsprecv: entry [19-MAR-2009 17:51:35:897] nsprecv: reading from transport... [19-MAR-2009 17:51:35:897] nttrd: entry o At this point the server expects information from the client o Since for one minute no update came from client, the server got closed [19-MAR-2009 17:52:36:865] ntt2err: entry [19-MAR-2009 17:52:36:865] ntt2err: soc 1660 error - operation=5, ntresnt[0]=522, ntresnt[1]=4, ntresnt[2]=0 [19-MAR-2009 17:52:36:865] ntt2err: exit [19-MAR-2009 17:52:36:865] ntt2err: entry [19-MAR-2009 17:52:36:865] ntt2err: soc 1660 error - operation=5, ntresnt[0]=530, ntresnt[1]=38, ntresnt[2]=0 [19-MAR-2009 17:52:36:865] ntt2err: exit
If this error persists or is frequent, it may be necessary to enable event tracing in order to capture more information.
SQL>alter system set events '3136 trace name errorstack level 3';
Once this is set, the next occurrence of the ORA-3136 should result
in some trace data being dumped to USER_DUMP_DEST or
BACKGROUND_DUMP_DEST.
The actual location of these directories can be found by issuing: SQL>show parameter dump_dest;
Here's an example of the trace data for an ORA-3136 event that was thrown because of an ORA-1017: d:\app\hostname\diag\rdbms\instance_name\instance_name\trace\orcl_ora_4604.trc
This is excerpted:
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0) ----- Error Stack Dump ----- ORA-03136: inbound connection timed out
Further down in the event trace we see the ORA-01017:
SQL>alter system set events '3136 trace name errorstack level 0';
See the following additional documents on this error and troubleshooting INBOUND CONNECT TIMEOUT issues: Note 465043.1 Troubleshooting ORA-3136: WARNING Inbound Connection Timed Out Note 3136.1 Connections that Used to Work in Oracle 10.1 Now Intermittently Fail with ORA-3113 or ORA-3136 from 10.2 Onwards
Troubleshooting ORA-3136: WARNING Inbound Connection Timed Out [ID 465043.1]
Applies to:
Oracle Net Services - Version 10.2.0.1.0 to 11.2.0.1 [Release 10.2 to 11.2] Information in this document applies to any platform.
Purpose
Troubleshooting guide for "ORA-3136 WARNING inbound connection timed out" seen in the alert log.
Troubleshooting Steps
The "WARNING: inbound connection timed out (ORA-3136)" in the alert
log indicates that the client was not able to complete the
authentication process within the period of time specified by the
parameter SQLNET.INBOUND_CONNECT_TIMEOUT.
You might also see the errors ORA-12170 or TNS-12535 in the sqlnet.log that is generated on the server.
Check $ORACLE_HOME/network/log for this file. This entry should contain
client address from which the timeout originated and may be helpful in
determining how to troubleshoot the issue. Some applications or JDBC
thin driver applications may not have these details. The sqlnet.log
file is not generated by default in 11g and newer.
From 10.2.0.1 onwards the default setting for the parameter
SQLNET.INBOUND_CONNECT_TIMEOUT is 60 seconds. If the client is not able
to authenticate within 60 seconds, the warning would appear in the
alert log and the client connection will be terminated.
Note: This timeout restriction was introduced to
combat Denial of Service (DoS) attack whereby malicious clients attempt
to flood database servers with connect requests that consumes resources.
The following are the most likely reasons for this error -
Server gets a connection request from a malicious client which is
not supposed to connect to the database. In this case the error thrown
would be the expected and desirable behavior. You can get the client
address for which the error was thrown in the sqlnet.log file that is
local to the database.
The server receives a valid client connection request but the client
takes a long time to authenticate more than the default 60 seconds.
The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.
To understand what is causing this issue, following checks can be done
The default value of 60 seconds is good enough in most conditions for
the database server to authenticate a client connection. If it is
taking longer, then it's worth checking the following items before
implementing the workaround:
1. Check whether local connection on the database server is successful & quick.
2. If local connections are quick ,then check for underlying network delay with the help of your network administrator.
3. Check whether your Database performance has degraded in anyway.
4. Check alert log for any critical errors for eg, ORA-600 or ORA-7445 and get them resolved first.
These critical errors might have triggered the slowness of the database server.
It is often necessary to increase the values for INBOUND CONNECT TIMEOUT
at both the listener and the database in order to resolve this
issue. It is usually advisable to set the database (sqlnet.ora) value
slightly higher than the listener (listener.ora). The authentication
process is more demanding for the database than the listener.
To set these parameters to use values higher than the default of 60
seconds, follow these instructions and restart the listener. There is
no need to restart Oracle:
Edit the server side sqlnet.ora file and add this parameter:
SQLNET.INBOUND_CONNECT_TIMEOUT=<n> Where <n> is the value in seconds.
E.g.:
SQLNET.INBOUND_CONNECT_TIMEOUT = 120
Edit the listener.ora file and add this parameter:
INBOUND_CONNECT_TIMEOUT_<listenername> = <n> Again, where <n> is the timeout value in seconds.
For example if the listener name is LISTENER then use:
INBOUND_CONNECT_TIMEOUT_LISTENER = 110
From Oracle version 10.2.0.1 onwards the default
value of INBOUND_CONNECT_TIMEOUT_<listenername> is 60 seconds. For
previous releases it is zero or OFF by default.
How to check whether inbound timeout is active for the listener and database server:
For example, INBOUND_CONNECT_TIMEOUT_<listener_name> =110
You can check whether the parameter is active or not by simply doing telnet to the listener port.
$ telnet <database server IP> <listener port>
for eg.
$ telnet 123.23.23.23 1521
The telnet session should disconnect after 110 seconds which indicates
that the inbound connection timeout for the listener is active.
To check whether database server SQLNET.INBOUND_CONNECT_TIMEOUT is active:
Eg.
SQLNET.INBOUND_CONNECT_TIMEOUT=120
a. For Dedicated server setup, enable the support level sqlnet server tracing will show the timeout value as below:
niotns: Enabling CTO, value=120000 (milliseconds) <== 120 seconds
niotns: Not enabling dead connection detection.
niotns: listener bequeathed shadow coming to life...
b. For shared Server setup,
$ telnet <database server IP> <dispatcher port>
Example.
$ telnet 123.23.23.23 51658
The telnet session should disconnect after 120 seconds which indicates that the sqlnet.inbound_connect_timeout is active.
If you have further questions regarding this issue then please create a
Service Request in My Oracle Support and provide the following
information:
a. Client and matching server traces generated at support level.
Note 395525.1 How to Enable Oracle Net Client,Server,Listener,Kerberos and External procedure Tracing from Net Manager (netmgr): Note 374116.1 How to Match Oracle Net Client and Server Trace Files
b. Upload sqlnet.ora, listener.ora Sqlnet.log, & Alert_<sid>.log from database server
SELECT
owner, object_name, object_type FROM dba_objects WHERE object_id in (54883,
54009);
owner object_name object_type
---------- ------------------ ---------------
HRMS ASSET_ALIAS TABLE
HRMS EQUITY_INFO TABLE
Normally,
deadlocks occur on the same object. In
this example, we are trying to access 2 different tables owned by HRMS schema
but resulting to a deadlock.
SELECT
owner, object_name, object_type FROM dba_objects WHERE object_id in (54883,
54009);
owner
object_name object_type
HRMS
ASSET_ALIAS TABLE
HRMS
EQUITY_INFO TABLE
The
deadlock was due to a "SELECT FOR UPDATE" on 2 different tables:
SELECT
ASSET_CODE, EQUITY_TYPE, EQUITY_SHRS_OUTSTANDING, EQUITY_ADR_FLAG,
EQUITY_TRANSFER_AGENT, EQUITY_CM_UNSPECIFIED, EQUITY_CM_CLASS_A,
EQUITY_CM_CLASS_B, EQUITY_PR_CONV, EQUITY_PR_CUMM, EQUITY_PR_PART, EQUITY_CONV_RATIO,
EQUITY_CTRY_OF_INC, EQUITY_AUDIT_PARENT, EQUITY_AUDIT_CHILD FROM EQUITY_INFO
WHERE (ASSET_CODE =:1) FOR UPDATE
Use the
scipt belows to get all the referencing tables of ASSET table.
Select
acc.table_name REFERENCING_TABLE_NAME, acc.column_name REFERENCING_COLUMN_NAME from
all_constraints ac1,all_constraints ac2,all_cons_columns acc where
ac1.constraint_type = 'P' and ac1.table_name = 'ASSET'and ac2.r_constraint_name
= ac1.constraint_name
and
ac2.constraint_name = acc.constraint_name;
Query
results to 84 rows referencing to ASSET table.
At this
point, what a production DBA can do is to inform the Application Designers and
Developers to review the current application design.
The above
deadlock example occurs because the application which issues the update
statements has no strict ordering of the rows it updates. The strict ordering of the updates ensures
that a deadly embrace cannot occur. Note that the deadlock need not be between
rows of the same table - it could be between rows in different tables. Hence it
is important to place rules on the order in which tables are updated as well as
the order of the rows within each table.
Show all processes causing a dead lock
SELECT a.session_id, username, TYPE, mode_held, mode_requested, lock_id1,
lock_id2
FROM v$session b, dba_blockers c, dba_locks a
WHERE c.holding_session = a.session_id AND c.holding_session = b.sid
/
The issue occurs when there is a db link which is a loop back database link i.e. points to itself
For Example:
HRMS@> DROP DATABASE LINK SHAAN.WORLD;
DROP DATABASE LINK SHAAN.WORLD
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
Now rename this global with some other name to fix the actual issue.
HRMS@> Alter database rename global_name to SHAAN1.XXXX;
Database altered.
Now you will able to delete the database link as the global name is seperated from the DBlink
HRMS@> DROP DATABASE LINK SHAAN.WORLD;
Database link dropped.
After dropping the dblink rename back to your global name.
HRMS@> Alter database rename global_name to SHAAN.WORLD;
Database altered
RMAN-03002: failure of restore command at 10/03/2012
10:40:06
ORA-01180: cannot create datafile 1
ORA-01110: data file 1: ‘C:\oracle1\oradata\system01.dbf’ After restoring the control file, RMAN restore of database fails with
the above error. As the physical datafiles do not exist RMAN attempts to create
the physical files but this is not allowed if the file belongs to the SYSTEM
tablespace.
This problem can occur on any platform. In this case:
Check the Backup location set properly the rman is not
finding the full backup so is not able to create datafile.
It is also possible you have changed your incarnation by
using “alter database open resetlogs” and the current incarnation only contains
incremental level backups. There is no full backups in this incarnation.
Solution:
Catalog the proper location for full backup
RMAN> catalog start with
‘H:\oraback\Weekly_20121102_full_orcl3-1034.DB’;
If do not have valid full backup in current incarnation then
Reset the database to the previous incarnation and run the restore and recovery
again.
RMAN-03009: failure of backup command on ch00
channel at 03/26/2012 13:01:03
ORA-19505: failed to identify file
“/oraback/arch/1_1100721_664058960.dbf”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
In this case nothing happened with your backup command. If
you read the error, it is saying particular archive logfile is not available.
This may be due to file deleted at OS level due to some reason. In this
situation, do the following
1. Run crosscheck command against archivelogs
RMAN>Crosscheck archivelog all;
2. If you find any archives marked as EXPIRED, then delete
those expired archives
RMAN>Delete expired archivelog all;
This will prompt you YES or NO. If you don’t want a prompt
While trying to take the backup
through the RMAN some times we are facing the below error.
RMAN> backup database plus
archivelog;
channel ORA_DISK_1: starting piece 1 at 03-OCT-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 10/03/2012 12:05:04
ORA-19502: write error on file "4crcv3st_1_1", blockno 5354
(blocksize=8192)
Solution:
As the name it is clear. It is a write error on output file.
Check the file then try again.
Check the location parameter default setting by issuing a
command like:
Configure channel 1 device type disk format 'H:\oraback\backup_%U';
It is also possible the backup will be attempting to write
to a larger disk space than is available; there is not enough disk space on the
drive to complete the backup.
Database
Duplication Fails with error: RMAN-06053, RMAN-06025
When I try to duplicate database first time using RMAN the
duplicate command is failed with following error:
RMAN-03002: failure of Duplicate Db command at
07/28/2010 23:11:44
RMAN-03015: error occurred in stored script Memory
Script
RMAN-06053: unable to perform media recovery because
of missing log
RMAN-06025: no backup of log thread 1 seq 12 lowscn 487272 found to restore
Cause:
RMAN unable to start archive log restore to default
destination. The problem is that rman is not able to apply the entire archive
log needed for complete recovery. For example, if you only backed up logs
through sequence 15, but the most recent archived log is sequence 16, then
DUPLICATE fails.
Solution:
When creating the duplication script either, use the SET
UNTIL command to specify a log sequence number for incomplete recovery or
include NOFILENAMECHECK with the duplicate command. For example, to terminate
recovery after applying log sequence 15, enter:
RUN
{
SET UNTIL
SEQUENCE 16 THREAD 1; #recovers up to but not including log 16
DUPLICATE
TARGET DATABASE TO 'dupdb';
}
-or- add NOFILENAMECHECK with
duplicate database command:
DUPLICATE TARGET DATABASE TO "clone"
NOFILENAMECHECK;
For Example:
RMAN> DUPLICATE TARGET DATABASE TO
"clone";
When
NOFILENAMECHECK is used with the DUPLICATE command, RMAN does not validate the
filenames during restoration.
Note:If the primary database and the standby database are on
the same host, The DUPLICATE NOFILENAMECHECK option should not be used.
RMAN Does
Not Recognize Character Set Name: PLS-00553
While working with Sadhan I configure the rman backup server
and start taking different production DB backup. In one fine morning one of our
database break down needs media recovery. I connected the rman to target
database and try to restore the database but I receive the following error
while using the “restore database” command.
RMAN-03002: failure during compilation of
command
RMAN-03013: command type: send
RMAN-06003: ORACLE error from target database:
ORA-06550: line 1, column 51:
PLS-00553: character set name is not recognized
Cause:
After searching about this error I found RMAN failed to
compile the SEND statement because the NS_LANG of the target database differed
from the NS_LANG of the recovery catalog. In the environment or shell
script, set and export NS_LANG to the correct value for the recovery catalog so
that RMAN receives the correct value.
Solution:
1. Query the target database to determine NLS_CHARACTERSETparameter.
SQL> SELECTVALUEFROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
-------------
AR8MSWIN1256
Try same Query with the rman catalog database I found the
database character set is same as the production database here also.
2. Set the character environment variable in the rman
catalog system same as the server.
My Computer -> Properties -> Advanced ->
Environment Variables -> System Variables -> New/Edit/Delete (to set the
variables)
Note:If
the connection is made through a listener, then the listener must be started
with the correct Globalization Support settings. Otherwise, the spawned
connections inherit the incorrect Globalization Support settings from the
listener.
Backup
Fails Because of Control File Enqueue: ORA-00230
In this scenario, a backup job
fails because RMAN cannot make a snapshot control file. The message stack is as
follows:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/30/2001 22:48:44
ORA-00230: operation disallowed: snapshot control file enqueue unavailable
Cause:
When RMAN needs to back up or resynchronize from the
control file, it first creates asnapshotor
consistent image of the control file. If one RMAN job is already backing up the
control file while another needs to create a new snapshot control file, then
you may see the following message:
Waiting for snapshot control file enqueue
Under normal circumstances, a job that must wait for the
control file enqueue waits for a brief interval and then successfully obtains
the enqueue. RMAN makes up to five attempts to get the enqueue and then fails
the job. The conflict is usually caused when two jobs are both backing up the
control file, and the job that first starts backing up the control file waits
for service from the media manager.
Solution:
To determine which job is holding the conflicting enqueue:
1.Start a
new SQL*Plus session and login with the sysdba:
2.Execute
the query to check the waiting cause:
SELECT s.SID, USERNAME AS "User", PROGRAM, MODULE,
ACTION, LOGON_TIME "Logon", l.*
FROM V$SESSION s, V$ENQUEUE_LOCK l
WHERE l.SID = s.SID
AND l.TYPE = 'CF'
AND l.ID1 = 0 AND l.ID2 = 2;
The output is look like as: (if there is lock)
SID User Program Module Action Logon
--- ---- -------------------- ------------------- ---------------- ---------
4 SYS rman@rman (TNS V1-V3) backup full datafile: c10000210 STARTED 16-NOV-12
This situation generally came across when a job is writing
to a tape drive, but the tape drive is waiting for new tape to be inserted. In
the mean time if you start new job then you will probably receive the enqueue
message because the first job cannot complete until the new tape is loaded.
After you have determined which job is creating the
enqueue, you can do one of the following:
§Wait
until the job holding the enqueue completes
§Cancel
the current job and restart it after the job holding the enqueue completes
Backup
Fails with Invalid RECID Error: RMAN-20035, RMAN-6038
When you attempt a backup and
receive the following error messages:
RMAN-3014: Implicit resync of recovery catalog failed
RMAN-6038: Recovery catalog package detected an error
RMAN-20035: Invalid high RECID error
It indicates the control file and
the recovery catalog is not synchronized. RMAN detects that the control file
currently in use is older than the control file previously used to
resynchronize.
Cause:
This due to any of the scenario you are restore a backup
controlfile through a non-oracle mechanism and then open the database through
Resetlogs option or making a copy of control file through operating system
utility and trying the restore on new system through RNAN. You do not use
catalog so RMAN does not get any information regarding this process. The recovery catalog indicates that the
highestRECIDis 100, but the
control file indicates that the highestRECIDis 90. The control
fileRECIDshould always be
greater than or equal to the recovery catalogRECID, so RMAN issuesRMAN-20035
Solution:
Login with sysdba:
sys/oracle@orcl3
Mount the database
SQL> shutdown immediate;
SQL> startup mount;
1.Perform cancel based recovery to open the database with Resetlogs
option.
Alter Database Recover Database until Cancel Using Backup Controlfile;
Alter Database Recover Cancel;
2.Then connect to the rman and open the database using Resetlogs option
This error came across me in developer 6i while I am trying
to re-install (after first time installation), while searching on the internet
and after trying several times I came to know that while reinstalling the
developer the older installation is either not completely removed from the
registry and whenever re-installing it is picking some garbage or unwanted path
with your environment variable.
Solution:
Check your environment path its size must be less than 1024
character. Fix it some around 900 characters such as C:\Oracle\Dev6\BIN
Be sure to save the value of the PATH in some text file BEFORE
modifying it. Always backup your registry before modifying it to so any thing
goes wrong you can import again the previous registry. Check my other post “How
to Export the current registry setting as a backup”:
While re-installing it is better to stop your anti virus.
May be possibility antivirus will interrupt your installation process.
When restoring the PATH to the original value (after Forms
installation and restart), don't forget to add the new Oracle home part of the
PATH (e.g. C:\Oracle\Dev6\BIN).
Added it to the end, to preserve old Oracle settings. So, after installation
"<new PATH>" should be "<old PATH>;<new Oracle
home BIN directory
Caution: Beware that installation of
"older" tools can sometimes harm some of your existing installations.
Finally if you are not finding the solution and if you do not have installed
any things related to oracle (if not then) in your system and you have decided
to fresh installation then completely remove your oracle registry and do the
fresh installation with new oracle path.
To remove the oracle registry:
Type regedit
in RUN
Go to HKEY_LOCAL_MACHINE –> SOFTWARE –> ORACLE
Now from the File Menu of registry export to save the
registry and from edit menu of registry delete to delete the registry (Please
make sure you are about to delete only oracle registry).
