Attache some comments when changing Oracle initialization parameters in spfile

Just came across the following and think it is good to share here.

When spfile is used in an Oracle database, and Oracle initialization parameters are modified, it is CAPABLE to attach a comment to the change:

alter system set ***=*** comment='Your comment' scope=both;

The COMMENT clause lets you associate a comment string with this change in the value of the parameter. If you also specify SPFILE, then this comment will appear in the parameter file to indicate the most recent change made to this parameter.

Where to see the changes? I have not figured out.

More Oracle DBA tips, please visit Oracle DBA Tips 

RMAN-05001, RMAN-05501, RMAN-03002 when cloning database with RMAN on the same database server

Error: 
The following error occurred when cloning an auxiliary database on the same server as the target database (10.2.0.3ee, Solaris):


RMAN> duplicate target database to dupdb;


Starting Duplicate Db at 16-MAR-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=211 devtype=DISK
^C
user interrupt received
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/16/2012 09:39:30
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary filename /oracle/targetdb/user_tables02.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary filename /oracle/targetdb/user_tables01.dbf conflicts with a file used by the target database


Solution:

The reason is pretty clear: cloned auxiliary data files CANNOT be placed in the same location as the target data files.

One of the solution is to put the following Oracle initialization parameters in the pfile or spfile:

db_file_name_convert=('/oracle/targetdb', '/oracle/dupdb')
log_file_name_convert=('/oracle/targetdb', '/oracle/dupdb')


More Oracle DBA tips, please visit Oracle DBA Tips 

SDE schema import and Oracle error ora-30512

I was importing sde schema from an export taken before a failed sde 10 upgrading. And the following error occurred:


. . importing table                     "VERSIONS"          1 rows imported
IMP-00017: following statement failed with ORACLE error 4020:
 "ASSOCIATE STATISTICS WITH PACKAGES "ST_RELATION_OPERATORS" USING "ST_DOMAIN"
 "_STATS""
IMP-00003: ORACLE error 4020 encountered
ORA-04020: deadlock detected while trying to lock object 30x3A00344700x39ECF02680x3A436F958
IMP-00017: following statement failed with ORACLE error 4020:
 "ASSOCIATE STATISTICS WITH INDEXTYPES "ST_SPATIAL_INDEX" USING "ST_DOMAIN_ST"
 "ATS""
IMP-00003: ORACLE error 4020 encountered
ORA-04020: deadlock detected while trying to lock object 30x3A00344700x39ECF02680x3A436F958
IMP-00017: following statement failed with ORACLE error 4020:
 "ASSOCIATE STATISTICS WITH TYPES "ST_GEOMETRY" USING "ST_DOMAIN_STATS""
IMP-00003: ORACLE error 4020 encountered
ORA-04020: deadlock detected while trying to lock object 30x3A0C6A1300x39ECF02680x3A436F958
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 30512:
 "ALTER TABLE "ST_GEOMETRY_COLUMNS" ENABLE CONSTRAINT "GEOM_SRID_FK1""
IMP-00003: ORACLE error 30512 encountered
ORA-30512: cannot modify SDE.ST_GEOMETRY_COLUMNS more than once in a transaction
Import terminated successfully with warnings.

I googled "ora 30512" and got the following link:

Error:  Underlying "ORA-30512: cannot modify SDE.ST_GEOMETRY_COLUMNS more than once in a transaction" encountered with Oracle Import Utility

And for sure the error can be ignored: It is safe to ignore the Oracle IMP utility error ORA-30512. Even though the error is encountered and reported in the Oracle import log, the GEOM_SRID_FK1 constraint is successfully enabled.

Next time it is better to use IMPDP/EXPDP instead of IMP/EXP.

More Oracle DBA tips, please visit Oracle DBA Tips 

ORA-00600: internal error code, arguments: [12333], [100], [163], [132], [], [], [], []

Got an Ora-00600 error today.

Error message:
ORA-00600: internal error code, arguments: [12333], [100], [163], [132], [], [], [], []

Solution:
-- check Oracle ora-00600 error lookup tool, and get the following note:
-- ORA-600 [12333] "Fatal Two-Task Protocol Violation" [ID 35928.1]
-- Understanding and Diagnosing ORA-00600 [12333] / ORA-3137 [12333] Errors [ID 389713.1]

For any ONE-TIME occurrence we can safely ignore the error. This error does not relate to any potential data corruption or any data loss.  :)

Common Causes & Solutions:

1.  Check the client software version. An incompatible client software can cause such internal error. 

2. An incompatible NLS Client setting can also trigger such internal errors. 

3. Try to reproduce the issue from a different client machine, preferably from server. This will rule out the possibility of bad network. 

4. If error still continues , then do the following to understand from where the error is generated. 

•  We need to understand which session/program/application that triggers this issue. This shall be found from the trace files under the session state object '(session)'. Sometimes the ORA-00600 [12333] trace files will not capture the session information.  In such cases we can check with the end users about any issues around the time-frame of this internal error. 
• There is a HIGH chance for application run-time exception to lead to this ORA-00600 [12333]. Due to the unhandled exceptions there is a chance for incomplete information passed to the server hence the internal errors are raised. In such cases, handling those exceptions (or) addressing those errors on the application code will resolve the issue. 
•  In few cases, the problem application will work for a while before it terminates with the error. This could be due to the TIMEOUT settings at the Application Server (or) Forms Server.,etc. To resolve this either tune the failing application to complete within the TIMEOUT value (or) reset the TIMEOUT to an optimal value.
• There could be more possibilities here; in simple words the approach should be from application side, which would help us to understand the other issues that triggered this network interruption (ORA-00600 [12333]).

5. If you notice JDBC Client as the failing program under Session state object, please check the following

• That you use latest compatible version of JDBC Drivers in your application side.
  Note 430839.1 to verify the version of JDBC Drivers and for Installation steps. 
•  Monitor and try to handle correctly the timeouts from the application 
• Check that idle or died programs/processes are not just killed at the client side but using a good cleaning procedure. 
• Increase queue sizes / buffers and similar from the jdbc side.

6. In-case if all the above suggestions does not help to identify the cause, and the ORA-00600 [12333] persists we may need to enable the networking tracings from the problem client and need to review the network traces to understand where the problem occurs.

More Oracle DBA tips, please visit Oracle DBA Tips 

Upgrading ArcSDE 10 and ORA-00942

I got an Ora-00942 error. And the following link explains a good reason:

Problem:  Incorrect dbtune XML_IDX_INDEX_TEXT values cause ArcSDE for Oracle geodatabase upgrades to fail with "ORA-00942: table or view does not exist" (http://support.esri.com/en/knowledgebase/techarticles/detail/38312)

Description

If there is an invalid value for the XML_IDX_INDEX_TEXT parameter in the dbtune, upgrading an ArcSDE for Oracle geodatabase to version 10 fails with following error in the sde_setup.log:

"[....] Error creating GDB_Items table...
[....] ERROR Creating Geodatabase tables
, Error = -37
,EXT_Error = 942
,EXT_ERROR1 = ORA-00942: table or view does not exist"

Cause

This error occurs when trying to create new geodatabase schema tables with an incorrect value in the XML_IDX_INDEX_TEXT parameter of the DEFAULTS DBTUNE keyword similar to the following:

"TABLESPACE TEST"

▪ The value of this parameter can be checked using the following ArcSDE admin command:

sdedbtune -o list -u sde -p sde -i sde:oracle10g:test -k DEFAULTS -P XML_IDX_INDEX_TEXT

ArcSDE 10.0 for Oracle11g Build 685 Fri May 14 12:05:43 2010
Attribute Administration Utility
-----------------------------------------------------
##DEFAULTS

XML_IDX_INDEX_TEXT "TABLESPACE TEST"

END

▪ The following is the actual SQL that fails:

SQL> CREATE INDEX xmldocix5_tx ON sde_xml_doc5 (xml_doc_val) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('TABLESPACE TEST');

CREATE INDEX xmldocix5_tx ON sde_xml_doc5 (xml_doc_val) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('TABLESPACE TEST')
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-11000: invalid keyword TABLESPACE
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

Solution or Workaround

1. Remove the config_string for the XML_IDX_INDEX_TEXT parameter in the DEFAULTS keyword from the DBTUNE.
   
   OR
   
   Make sure its value is valid for Oracle text. See the Oracle documentation for more information - there is a link to the Oracle documentation in the Related Information section below.
2. Re-run the geodatabase upgrade.

More Oracle DBA tips, please visit Oracle DBA Tips 

ESRI ArcSDE reference links

ESRI has released so many versions, releases, service packs of ArcMap, ArcSDE and other ArcGIS Server products that it is a challenge to keep a useful list of reference links! At least here is one I accumulated and am accumulating day by day

ArcSDE ESRI website: http://www.esri.com ESRI White Papers: http://support.esri.com/en/knowledgebase/whitepapers ESRI Training: http://training.esri.com/gateway/index.cfm ESRI Canada: http://www.esricanada.com/ Esri Developer Network: http://edn.esri.com/ ArcGIS Resource Center: http://resources.arcgis.com/ ArcGIS Resource Center - Web-based Help: http://resources.arcgis.com/content/web-based-help ArcGIS Forums: http://forums.arcgis.com/ ArcGIS Desktop 10: http://help.arcgis.com/en/arcgisdesktop/10.0/help/ ArcSDE Installation Guides - ArcSDE 10 for Oracle: http://help.arcgis.com/en/geodatabase/10.0/install_guides/arcsde_for_oracle_installation_and_upgrade_guide/index.html ArcSDE Installation Guides - ArcSDE 10 for SQL Server: http://help.arcgis.com/en/geodatabase/10.0/install_guides/arcsde_for_microsoft_sql_server_installation_and_upgrade_guide/index.html ·         ArcSDE Knowledge Base: http://support.esri.com/en/knowledgebase/techarticles/browse/productid/19/productfamily/0/productVersions/642 ·          ArcSDE 9.1 document: http://edndoc.esri.com/arcsde/9.1/ ·         ArcSDE 9.2 document: http://edndoc.esri.com/arcsde/9.2/ ·         ArcSDE 9.3  Administration Command Reference: http://resources.esri.com/help/9.3/geodatabase/pdf/admincmd.pdf     ArcSDE Microsoft SQL Server Database Requirements 9.3/9.3.1:  http://downloads.esri.com/support/systemrequirements/arcsde_microsoft_sql_server_database_requirements.pdf ·         ArcSDE 10.0 o    Geodatabase and ArcSDE 10.0 Help: http://resources.arcgis.com/content/geodatabases/10.0/geodatabase-and-arcsde-help o    Geodatabase and ArcSDE 10.0 System Requirements: http://resources.arcgis.com/content/geodatabases/10.0/geodatabase-and-arcsde-system-requirements --ArcSDE 10 SQL Server Database Requirements: http://resources.arcgis.com/content/arcsde/10.0/sql-server-system-requirements o    ArcSDE Installation Guides: http://resources.arcgis.com/content/geodatabases/10.0/arcsde-installation-guides o    ArcSDE 10.0 for Oracle Installation Guides: http://help.arcgis.com/en/geodatabase/10.0/install_guides/arcsde_for_oracle_installation_and_upgrade_guide/index.html o    ArcSDE Geodatabase System Tables-10: http://help.arcgis.com/en/arcgisdesktop/10.0/help/002p/pdf/sdesystables_diagram.pdf ·         ArcGIS Server 9.2 Help: http://webhelp.esri.com/arcgisserver/9.2/java/ ·         ArcGIS Server 9.3 Help: http://webhelp.esri.com/arcgisserver/9.3/java/ ·         ArcGIS Server 9.3.1 Help: http://webhelp.esri.com/arcgisserver/9.3.1/java/ ·         ArcGIS Server 10 Help: http://help.arcgis.com/en/arcgisserver/10.0/help/arcgis_server_java_help/index.html ·           ·         Forum: Geodatabase & ArcSDE: http://forums.arcgis.com/forums/32-Geodatabase-amp-ArcSDE ·           What's new in ArcGIS 10: http://help.arcgis.com/en/arcgisdesktop/10.0/pdf/whats_new_in_arcgis_10.pdf ·         ArcGIS Server Product Life Cycle Support Status: http://downloads.esri.com/support/product%20life%20cycle/server_gis/ArcGISServer_PLC.pdf ·         ArcSDE 9.3/9.3.1 Oracle Database Requirements: http://wikis.esri.com/wiki/display/ag93bsr/ArcSDE+Oracle+Database+Requirements ·         ArcSDE 10 Oracle Database Requirements: http://resources.arcgis.com/content/arcsde/10.0/oracle-system-requirements ·         Product Announcements for ArcGIS Server: http://support.esri.com/en/knowledgebase/product-announcements/list/productid/66 ·         Patches and Service Packs: http://resources.arcgis.com/content/patches-and-service-packs ·         Patches and Service Packs for ArcGIS Server: http://resources.arcgis.com/content/patches-and-service-packs?fa=listPatches&PID=66

Patches and Service Packs for ArcSDE ·         ArcSDE 10 Service Pack 4  03-05-2012 - SERVICE PACK ·         ArcSDE 10 Service Pack 3 10-12-2011 - SERVICE PACK ·         ArcSDE 10 Service Pack 2 04-28-2011 - SERVICE PACK ·         ArcSDE 10 Service Pack 1  12-03-2010 - SERVICE PACK ·         ArcSDE 10.0 General Update Patch for SQL Server  08-31-2010 - PATCH ·         ArcSDE 9.3.1 Service Pack 2 Register as Versioned using Oracle Spatial Data Patch 08-11-2011 – PATCH ·         ArcSDE 9.3.1 Service Pack 2 General Update Patch for DB2, Informix, and Oracle 11-29-2010 – PATCH ·         ArcSDE 9.3.1 Service Pack 2 11-04-2010 - SERVICE PACK ·         ArcSDE 9.3.1 SP1 SQL Server Raster Catalog and Raster Field Patch  12-23-2009 - PATCH ·         ArcSDE 9.3.1 Service Pack 1  12-19-2009 - SERVICE PACK ·         ArcSDE 9.3.1 SQL Server 2000 Permissions Patch  06-29-2009 - PATCH ·         ArcSDE 9.3 Service Pack 1 Compress with Concurrent Editors Patch  05-11-2009 – PATCH ·         ArcSDE 9.3 Service Pack 1 Memory Allocation Patch  03-18-2009 – PATCH ·         ArcSDE 9.3 Service Pack 1  03-13-2009 - SERVICE PACK ·         ArcSDE 9.3 Service Pack 1 geodatabase direct connect for 9.2 clients setup  11-07-2008 - SERVICE PACK ·         ArcSDE 9.2 SP 6 SQL Server 2000 Permissions Patch  06-29-2009 - PATCH ·         ArcSDE 9.2 Service Pack 6 Compress with Concurrent Editors Patch  04-22-2009 – PATCH ·         ArcSDE 9.2 Service Pack 6 Memory Allocation Patch  02-09-2009 – PATCH ·         ArcSDE 9.2 Connection Performance and Geodatabase Repair Patch  12-19-2008 – PATCH ·         ArcSDE 9.2 ST_Geometry Spatial Index Patch  08-28-2008 – PATCH ·         ArcSDE 9.2 Service Pack 6  07-18-2008 - SERVICE PACK ·         ArcSDE 9.2 Service Pack 5  05-08-2008 - SERVICE PACK ·         ArcSDE 9.2 Oracle Binary and Lob Type Spatial View Patch  05-02-2008 – PATCH ·         ArcSDE 9.2 for Oracle10gR2 for Tru64 UNIX  01-16-2008 – PATCH ·         ArcSDE 9.2 Service Pack 4  11-16-2007 - SERVICE PACK ·         ArcSDE 9.2 Service Pack 3  07-31-2007 - SERVICE PACK ·         ArcSDE 9.2 SQL Server Case Sensitivity Patch 06-07-2007 – PATCH ·         ArcSDE 9.2 Service Pack 2  04-16-2007 - SERVICE PACK ·         ArcSDE 9.2 Versioning Patch  03-21-2007 – PATCH ·         ArcSDE 9.2 Oracle Patch  02-14-2007 – PATCH ·         ArcSDE 9.2 Service Pack 1  01-04-2007 - SERVICE PACK ·         ArcSDE 9.1 Compress with Concurrent Editors Patch  06-16-2009 – PATCH More Oracle DBA tips, please visit Oracle DBA Tips

How to resolve Error 1606 when installing ArcMap 10 Desktop

When installing ArcMap 10 on my workstation, I got the following error:

Error 1606. Could not access network location \\****\****\My Pictures\

Solution:

You receive an "Error 1606" error message when you try to install or remove a Microsoft program (http://support.microsoft.com/kb/886549 )

To resolve this issue yourself, follow these steps:

1. Click Start, click Run, type Regedit.exe, and then click OK.
2. Locate and then click the following registry subkey:
   
   

   HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders
3. In the right pane, verify that the values are the same as the values in the following table. If each value matches the table, go to step 7. 

For Windows Vista, Windows 7 and Windows Server 2008

Value name	Type	Value data
{374DE290-123F-4565-9164-39C4925E467B}	REG_EXPAND_SZ	%USERPROFILE%\Downloads
AppData	REG_EXPAND_SZ	%USERPROFILE%\AppData\Roaming
Cache	REG_EXPAND_SZ	%USERPROFILE%\AppData\Local\Microsoft\Windows\Temporary Internet Files
Cookies	REG_EXPAND_SZ	%USERPROFILE%\AppData\Roaming\Microsoft\Windows\Cookies
Desktop	REG_EXPAND_SZ	%USERPROFILE%\Desktop
Favorites	REG_EXPAND_SZ	%USERPROFILE%\Favorites
History	REG_EXPAND_SZ	%USERPROFILE%\AppData\Local\Microsoft\Windows\History
LocalAppData	REG_EXPAND_SZ	%USERPROFILE%\AppData\Local
My Pictures	REG_EXPAND_SZ	%USERPROFILE%\Pictures
My Music	REG_EXPAND_SZ	%USERPROFILE%\Music
My Video	REG_EXPAND_SZ	%USERPROFILE%\Videos
NetHood	REG_EXPAND_SZ	%USERPROFILE%\AppData\Roaming\Microsoft\Windows\Network Shortcuts
Personal	REG_EXPAND_SZ	%USERPROFILE%\Documents
PrintHood	REG_EXPAND_SZ	%USERPROFILE%\AppData\Roaming\Microsoft\Windows\Printer Shortcuts
Programs	REG_EXPAND_SZ	%USERPROFILE%\AppData\Roaming\Microsoft\Windows\Start Menu\Programs
Recent	REG_EXPAND_SZ	%USERPROFILE%\AppData\Roaming\Microsoft\Windows\Recent
SendTo	REG_EXPAND_SZ	%USERPROFILE%\AppData\Roaming\Microsoft\Windows\SendTo
Start Menu	REG_EXPAND_SZ	%USERPROFILE%\AppData\Roaming\Microsoft\Windows\Start Menu
Startup	REG_EXPAND_SZ	%USERPROFILE%\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup
Templates	REG_EXPAND_SZ	%USERPROFILE%\AppData\Roaming\Microsoft\Windows\Templates

Then tried again. It works!

More Oracle DBA tips, please visit Oracle DBA Tips