Monday, 25 February 2013


Ora-01722: Invalid Number When Running The Analytical Engine In Demantra [ID 1208653.1]

Applies to:

Oracle Demantra Demand Management - Version: and later   [Release: 7 and later ]
Information in this document applies to any platform.


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 :
  • 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

ORA 2253

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 
Action: Remove or relocate the constraint specification and try again.

Sunday, 24 February 2013

shared pool

What is shared_pool_reserved_size:

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.

SCN and checkpoint.

Difference between SCN and checkpoint.

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.

Golden Gate

Oracle Golden Gate

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.

Saturday, 23 February 2013

Mass Additions Troubleshooting

Applies to:

Oracle Assets - Version and later
Information in this document applies to any platform.
This knowledge document is a replacement for 820793.1 which has been deleted.


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' ?


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.

For more information refer to Note 162788.1

2. When trying to run the post mass addition program, why is a particular book is not appearing in LOV ?


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 ?


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


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
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 ?


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.

Reference Note 578714.1

6. Post mass addition program errors with: Error: Incorrect Asset Type and Category combination for &CATEGORY_ID.


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 ?


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


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.

Reference Note 739775.1

9. How to resolve the below error when querying the invoice in Prepare Mass Additions from,

FRM-40735: WHEN-NEW-RECORD-INSTANCE trigger raised unhandled exception


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:


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.

Reference Note 469570.1

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 **


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.

Reference Note 389781.1

12. Why asset_type field is not populated for Asset lines that are loaded into the FA_MASS_ADDITIONS table by SQL*Loader


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.

Reference Note 344147.1

13. Post mass addition program is erroring with: APP-482858 ASSET NUMBER already exists. Please enter a new value.


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


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.


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

Reference Note 142324.1

16. Why error "Please choose an existing combination" appears when trying to save a record in Asset Workbench or Prepare Mass Addition form ?


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.

Reference Note 564360.1

17. When trying to retire an asset, below error message appears: APP-47434 you must post mass additions before you retire the asset.


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.


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 ?


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.

Reference Note 184533.1

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:


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.

Reference Note 404542.1

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.


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 ?


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


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


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 ?


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.

Reference Note 184533.1

Friday, 22 February 2013


ORA-1017 May Cause ORA-3136 WARNING : Inbound Connection Timed Out in Alert Log [ID 793259.1]

Applies to:

Oracle Net Services - Version to [Release 10.1 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 30-SEP-2011.


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


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.

Make the tnsnames entry on the server with dedicated as server like

Note: Ensure that tnsping orcl10203 works well and the same value set in tnsnames.ora is reflected in the output.

Now open a SQL*Plus session and enter wrong username or password:
[oracle@testnet admin]$ sqlplus system/jkkdsf@orcl10203

SQL*Plus: Release - Production on Thu Mar 19 17:51:35 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ORA-01017: invalid username/password; logon denied

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:
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:

DF2D500 00000000 00000000 00000000 00000000  [................]
DF2D510 00000000 074B3FBA 0DF2D69D 2D41524F  [.....?K.....ORA-]
DF2D520 31303130 69203A37 00000000 0CE5DCBC  [01017: i........]
DF2D530 00000000 2F656D61 00000003 00000000  [....ame/........]

We can also see the originating client address:
13105470 FFFFFFFF FFFFFFFF FFFFFFFF 754164FF  [.............dAu]
13105480 6E656874 61636974 20646574 203A7962  [thenticated by: ]
13105490 41544144 45534142 6C43203B 746E6569  [DATABASE; Client]
131054A0 64646120 73736572 4128203A 45524444  [ address: (ADDRE]
131054B0 283D5353 544F5250 4C4F434F 7063743D  [SS=(PROTOCOL=tcp]
131054C0 4F482829 313D5453 35312E30 37312E39  [)(HOST=10.100.200]
131054D0 32322E36 50282937 3D54524F 34333734  [.300)(PORT=4734]
131054E0 14FF2929 6E616D6A 73726564 5C73752D  [))..JOHN_DOE\]

 Turn event tracing off using:

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 to [Release 10.2 to 11.2]
Information in this document applies to any platform.


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 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 -
  1. 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.
  2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.
  3. 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.


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:

From Oracle version 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 1521

The telnet session should disconnect after 110 seconds which indicates that the inbound connection timeout for the listener is active.

Alternatively, check at the LSNRCTL prompt using:

LSNRCTL>set current_listener <listener_name>
LSNRCTL>show inbound_connect_timeout

To check whether database server SQLNET.INBOUND_CONNECT_TIMEOUT is active:

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>
$ telnet  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

Tuesday, 19 February 2013



A deadlock occurs when a session wants a resource held by another session, but that session also wants a resource held by the first session.
Below is a snippet of an actual production database trace file showing deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00090008-0012afb1 165 501 X 178 461 X
TX-0010002f-000530d1 178 461 X 165 501 X
Rows waited on:
Session 461: obj - rowid = 0000D663 - AAANZjAAFAAABypAAV
(dictionary objn - 54883, file - 5, block - 7337, slot - 21)
Session 501: obj - rowid = 0000D2F9 - AAANL5AAFAAAA80AAG
(dictionary objn - 54009, file - 5, block - 3892, slot - 6)
Information on the OTHER waiting sessions:
Session 461:
pid=178 serial=24604 audsid=420036396 user: 56/HRMS
O/S info: user: HRMS, term: TC2HRMS2, ospid: 3072:2676, machine:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00090008-0012afb1    165  501     X    178   461   X
TX-0010002f-000530d1    178  461     X    165   501   X
This shows who was holding each lock, and who was waiting for each lock.
The columns in the graph indicate:
Resource Name =   Lock name being held / waited for.
process            =  V$PROCESS.PID of the Blocking / Waiting session
session            =  V$SESSION.SID of the Blocking / Waiting session
holds               =  Mode the lock is held in
waits               =  Mode the lock is requested in
So in this example:
SID 501 holds TX-00090008-0012afb1 in X mode
and wants TX-0010002f-000530d1 in X mode
SID 461 holds TX-0010002f-000530d1 in X mode
and wants TX-00090008-0012afb1 in X mode
Rows waited on:
Session 461: obj - rowid = 0000D663 - AAANZjAAFAAABypAAV
(dictionary objn - 54883, file - 5, block - 7337, slot - 21)
Session 501: obj - rowid = 0000D2F9 - AAANL5AAFAAAA80AAG
(dictionary objn - 54009, file - 5, block - 3892, slot - 6)
To get the objects:
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
The deadlock was due to a "SELECT FOR UPDATE" on 2 different tables:
Lets try to check its dependencies to other tables.
SQL> select table_name, R_CONSTRAINT_NAME from dba_constraints where table_name in ('ASSET_ALIAS','EQUITY_INFO') and constraint_type = 'R';
--------------------    ------------------------------
As we can see, there is a referantial constraints XPK_ASSET used by both ASSET_ALIAS and EQUITY_INFO tables.
SQL> select table_name, constraint_name from dba_constraints where constraint_name = 'XPK_ASSET';
----------------------------  ------------------------------
ASSET                         XPK_ASSET
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,
  FROM v$session b, dba_blockers c, dba_locks a
 WHERE c.holding_session = a.session_id AND c.holding_session = b.sid


ORA-02082: a loopback database link must have a connection qualifier

The issue occurs when there is a db link which is a loop back database link i.e. points to itself
For Example:
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

Query the Global name and note down that name.

HRMS> select * from global_name;
1 row selected.

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
Database link dropped.

After dropping the dblink rename back to your global name.
HRMS@> Alter database rename global_name to SHAAN.WORLD;
Database altered


ORA-01180: Cannot create datafile 1

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.
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.


Failed to identify file in RMAN: ORA-19505
After configuring the target database with the RMAN first time. I try to take database backup through RMAN and found the following error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
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
RMAN>Delete noprompt archivelog all;
3. Then take a fresh archivelog backup
RMAN>Backup archivelog all;


Write error on file: ORA-19502:
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)

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.

RMAN-06053, RMAN-06025

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
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.



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:
  SET UNTIL SEQUENCE 16 THREAD 1; #recovers up to but not including log 16
-or- add NOFILENAMECHECK with duplicate database command:
For Example:
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


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.



1. Query the target database to determine NLS_CHARACTERSET parameter.
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.
% setenv NLS_LANG american_america.we8dec
In windows you can go through the registry to setup environment variable:
also check from the below path:
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


When RMAN needs to back up or resynchronize from the control file, it first creates a snapshot or 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.



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:
ACTION, LOGON_TIME "Logon", l.* 
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
§         Cancel the job creating the enqueue

RMAN-20035, RMAN-6038

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.


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 highest RECID is 100, but the control file indicates that the highest RECID is 90. The control file RECID should always be greater than or equal to the recovery catalog RECID, so RMAN issues RMAN-20035



Login with sysdba:
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
CONNECT rsys/oracle@target catalog catalog/catalog@rman
RMAN> Alter database open resetlogs;
3.      Do not forget to take the fresh backup after resetlogs option;

forms60. vrf(78) OS_error

forms60. vrf(78) OS_error

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.
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
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).