Spatial DBA - Oracle and ArcSDE

Friday, March 16, 2012

Oracle Notes and Links for DBA

Oracle Support:

--Oracle document

Oracle Database 11g Release 2 (11.2):http://www.oracle.com/pls/db112/homepage

-- Oracle 10gR2

· 10.2.0.x Oracle Database and Networking Patches for Microsoft Platforms 342443.1

· 10G New Storage Features and Enhancements 243245.1

· 10g Recyclebin Features And How To Disable it( _recyclebin ) 265253.1

· 10G: Rename Tablespace Enhancement 243332.1

· 10GR2 NEW FEATURE - DEFAULT DIRECTORY DATA_PUMP_DIR 334590.1

· ALERT: Oracle 10g Release 2 (10.2) Support Status and Alerts [ID 316900.1]

· Automatic PGA Memory Managment in 9i and 10g 223730.1

· Complete checklist for manual upgrades to 10gR1 (10.1.0.x) [ID 263809.1]

· Complete Checklist for Manual Upgrades to 10gR2 [ID 316889.1]

· Differences Between Enterprise, Standard and Personal Editions on Oracle 10.2 465465.1

· Flashback Table feature in Oracle Database 10g 265254.1

· Full UNDO Tablespace In 10gR2 413732.1

· How to Reload the JVM in 10.1.0.X and 10.2.0.X 276554.1

· Oracle Database 10g Automated SGA Memory Tuning Note:257643.1

· Oracle Database Software Component Guide for 10gR2 [ID 783288.1]

· Oracle 10G - Server Generated Alerts 266970.1

· Oracle 10g Upgrade Companion [ID 466181.1]

· Oracle Database 10.2 End of Premier Support - Frequently Asked Questions [ID 1130327.1]

· REMINDER: Premier Support for Oracle Database 10.1 ends January 2009 [ID 761713.1]

· Restoring table statistics in 10G onwards [ID 452011.1]

· Rule Based Optimizer is to be Desupported in Oracle10g 189702.1

· Script to create user OUTLN in 10.2 422983.1

· SYSAUX Tablespace in Oracle 10g and higher [ID 243246.1]

-- Oracle 11g

· 11g Diagnosability: Frequently Asked Questions 453125.1

· 11g New Features : Top 5 Features In 11g 453487.1

· 11g R1 New Feature : Case Sensitive Passwords and Strong User Authentication 429465.1

· Automatic Memory Management(AMM) on 11g 443746.1

· FAQs on OPatch Version : 11.1 453495.1

· Frequently Asked Questions (FAQs) on Upgrades to 11g Release 1 (11.1) 727736.1

· How To Shrink A Temporary Tablespace in 11G ? 452697.1

· Oracle Database Software Component Guide for 11gR1 [ID 783290.1]

-- Oracle 11gR2

· How to Troubleshoot 11gR2 Installation Issue [ID 1056322.1]

· Manual installation, deinstallation and verification of Oracle Text 11gR2 [ID 970473.1]

· Oracle 11gR2 Upgrade Companion [ID 785351.1]

· Oracle Database 11g Release 2 Information Center [ID 988222.1]

· Self-PacedTraining for Oracle Database 11g Release 2 [ID 1060197.1]

-- Oracle case study

· Case Study:Resolving High CPU Usage on Oracle Servers 369412.1

-- Oracle characterset

· Changing WE8ISO8859P1/ WE8ISO8859P15 or WE8MSWIN1252 to (AL32)UTF8 260192.1

-- Oracle database upgrading

· Certification Information for Oracle Database on Solaris SPARC 64 [ID 1307041.1]

· Client / Server / Interoperability Support Between Different Oracle Versions [ID 207303.1]

· Complete checklist for manual upgrades to 10gR1 (10.1.0.x) [ID 263809.1]

· Complete Checklist for Manual Upgrades to 10gR2 [ID 316889.1]

· Complete Checklist for Manual Upgrades to 11gR1 [ID 429825.1]

· Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]

· Core Oracle Database Certification Information [ID 1306539.1]

· Critical Patch Updates and Security Alerts: http://www.oracle.com/technetwork/topics/security/alerts-086861.html

· Critical Patch Update - Introduction to Database n-Apply CPUs 438314.1

· Database, FMW, EM Grid Control, and OCS Software Error Correction Support Policy [ID 209768.1]

· Database Install Community: https://communities.oracle.com/portal/server.pt/community/database_install/278

· Database Server Upgrade/Downgrade Compatibility Matrix [ID 551141.1]

· Different Upgrade Methods For Upgrading Your Database [ID 419550.1]

· How to Download and Run Oracle's Database Pre-Upgrade Utility [ID 884522.1]

· How to Pre-Process SYS.AUD$ Records Pre-Upgrade From 10.1 or later to 11gR1 or later. [ID 1329590.1]

· Information Center: Overview Database Server/Client Installation and Upgrade/Migration [ID 1351022.2]

· Information Center: Install and Configure Database Server/Client Installations [ID 1351051.2]

· Information Center: Patching and Maintaining Oracle Database Server/Client Installations [ID 1351428.2]

· Intro to Patch Set Updates (PSU) 854428.1

· List of Known Performance/Wrong Results Bugs Reported on 11.2.0.2 and 11.2.0.3 [ID 1320966.1]

· Master Note For OPatch [ID 293369.1]

· Master Note For Oracle Database Server Installation [ID 1156586.1]

· My Oracle Support Help - Certifications [ID 870956.5]

· OPatch 6880880: https://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=6880880

· Oracle 10g Upgrade Companion [ID 466181.1]

· Oracle 11gR1 Upgrade Companion [ID 601807.1]

· Oracle 11gR2 Upgrade Companion [ID 785351.1]

· Oracle Database on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2) [ID 169706.1]

· Oracle Database (RDBMS) Releases Support Status Summary [ID 161818.1]

· Oracle Database Software Downloads: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

· Oracle Database Upgrade Path Reference List [ID 730365.1]

· Oracle Lifetime Support Policies [ID 971415.1]

· Patch Set Updates for Oracle Products [ID 854428.1]

· Patch Set Updates - One-off Patch Conflict Resolution [ID 1061295.1]

· Reference List of Critical Patch Update Availability Documents For Oracle Database and Fusion Middleware Product 783141.1

· Release Schedule of Current Database Releases [ID 742060.1]

· Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

· Security Alerts and Critical Patch Updates- Frequently Asked Questions 237007.1

· Starting With Oracle JDBC Drivers [ID 401934.1]

· Technology Products Lifetime Support Policy: http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf

· Upgrade Advisor: Database Upgrade and Migration [ID 1351112.2]

· VERSION NUMBER - Oracle version numbers explained [ID 39691.1]

11g

· 11g: How to Determine if XDB is Being Used in the Database? [ID 733667.1]

· 11g Install : Understanding about Oracle Base, Oracle Home and Oracle Central/Global Inventory locations [ID 454442.1]

· 11gR2 Clusterware and Grid Home - What You Need to Know [ID 1053147.1]

· Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset [ID 1358166.1]

· ALERT: Oracle 11g Release 1 (11.1) Support Status and Alerts [ID 454507.1]

· ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts [ID 880782.1]

· Complete Checklist for Manual Upgrades to 11gR1 [ID 429825.1]

· Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]

· Complete checklist for out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset. [ID 1276368.1]

· Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1]

· Oracle 11gR1 Upgrade Companion [ID 601807.1]

· Oracle 11gR2 Upgrade Companion [ID 785351.1]

· Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]

Oracle Components

· Oracle database

o Oracle Created Database Users: Password, Usage and Files References [ID 160861.1]

· XDB

o 11g: How to Determine if XDB is Being Used in the Database? [ID 733667.1]

o Change in default storage model of XMLType to BINARY XML in 11.2.0.2.0 [ID 1207893.1]

o Master Note for Oracle XML Database (XDB) Install / Deinstall [ID 1292089.1]

Oracle Community

· Community: Database Utilities: https://communities.oracle.com/portal/server.pt/community/database_utilities/335

Oracle database HOW-TO

· 11g: How to Determine if XDB is Being Used in the Database? [ID 733667.1]

Oracle errors

· An Introduction to Error Message Articles 22080.1

· Common Causes and Solutions on ORA-1157 Error Found in Backup & Recovery 184327.1

· IMP-00034 When There is a Space in the Username 445134.1

· OERR: ORA 54 "resource busy and acquire with NOWAIT specified" 18245.1

· Database Crashes With ORA-00494 753290.1

· How to deal with ORA-600 Internal Errors 146581.1

· What is an ORA-600 Internal Error? 146580.1

· Database Link Fails With ORA-2019 115857.1

· ORA-03113 on Unix - What Information to Collect 17613.1

· Troubleshooting and Diagnosing ORA-4031 Error 396940.1

· ORA-04068 Errors from User-Written and Oracle Packages 106206.1

· Customer Introduction to ORA-7445 Errors 211909.1

· ORA-600/ORA-7445 Troubleshooter 153788.1

· Oracle Universal Installer ( OUI ) Error Messages and Solution Reference List [ID 757964.1]

· ORA-1652 Error Troubleshooting [ID 793380.1]

· ORA-24247 Using UTL_TCP, UTL_HTTP, UTL_SMTP and UTL_MAIL with11gR1 or Later [ID 1209644.1] 11gR1

Oracle FAQs

· 11g Diagnosability: Frequently Asked Questions 453125.1

· Certify FAQ, Product Navigation & User's Guide 184977.1

· Changing the Database Character Set - Frequently Asked Questions 227337.1

· Character Sets & Conversion - Frequently Asked Questions 227330.1

· Character Set Scanner - Frequently Asked Questions 227338.1

· Cost Based Optimizer - Common Misconceptions and Issues 35934.1

· Database Performance FAQ 402983.1

· Dates & Calendars - Frequently Asked Questions 227334.1

· FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU) 461480.1

· FAQ: Differences Between Enterprise, Standard and Personal Editions 465455.1

· FAQ: Query Tuning Frequently Asked Questions 398838.1

· FAQ about Detecting and Resolving Locking Conflicts 15476.1

· FAQ about UTL_TCP, UTL_SMTP and UTL_MAIL 369777.1

· FAQ for Oracle Security Alert 68 282108.1

· FAQ Memory Corruption 429380.1

· FAQs on OPatch Version : 11.1 453495.1

· Frequently Asked Questions About Sequences 108643.1

· Frequently Asked Questions (FAQs) on Upgrades to 11g Release 1 (11.1) 727736.1

· Frequently Asked Questions for Oracle Text 153264.1

· Frequently Asked Questions for Oracle Multimedia (Formerly interMedia) 220018.1

· Frequently Asked Questions for Oracle Workspace Manager 156963.1

· Frequently asked questions on Rman backup retention policy 463875.1

· Globalization (NLS) - Frequently Asked Questions 60134.1

· Interim Patch (One-Off Patch) FAQ 726362.1

· NLS considerations in Import/Export - Frequently Asked Questions 227332.1

· Setting NLS Parameters - Frequently Asked Questions 227331.1

· Oracle and Firewalls: Answers to Frequently Asked Questions 2084440.6

· Oracle Advanced Security Frequently Asked Questions 165465.1

· Oracle Label Security Frequently Asked Questions 213684.1

· Oracle Password Management Policy 114930.1

· Oracle Server (RDBMS) Releases Support Status Summary 161818.1

· Oracle Server - Export and Import FAQ 175624.1

· Oracle Server - Export Data Pump and Import DataPump FAQ 556636.1

· RAC: Frequently Asked Questions 220970.1

· Should Sessions be Killed in OS or Using Alter System Kill Session? 161794.1

· Streams Complete Reference FAQ 752871.1

· SUSPEND / RESUME on a DB 282764.1

· Timestamps & time zones - Frequently Asked Questions 340512.1

· Ultra Search Frequently Asked Questions 232648.1

· What Exactly Is An Unprivileged User? 357575.1

Oracle How-to

· AUTOTRACE Option in sqlplus 43214.1

· Cloning A Database Home And Changing The User/Group That Owns It 558478.1

· Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g 28814.1

· How ORA_ROWSCN Works with Rowdependencies and Norowdependecies 805424.1

· How To Calculate CPU Cost 457228.1

· How to Change the Existing Undo Tablespace to a New Undo Tablespace 431652.1

· How To Change The Group Of The Oracle Home on Unix/Linux Operating System After Installation? 430059.1

· How To Change The Owner Of Oracle Database Software On Unix/Linux Operating System 405245.1

· How to Check if the Oracle JVM is Installed Correctly in the Database 397770.1

· How to Correctly Check if the Oracle JVM is Installed in the Database 397770.1

· How to Create a Database with Automatic Undo Management 135053.1

· How To Determine If An Oracle Installation Is An Enterprise Or A Standard Edition? 735550.1

· How do we determine if Oracle Text component is been used in the database? 726932.1

· How To Determine If An Oracle Installation Is An Enterprise Or A Standard Edition? 735550.1

· How to determine if one-off patches are in conflict with other installed patches 269069.1

· How to Determine if Ultra Search is Being Used? 738126.1

· How to Determine Whether Time Zone Changes Will Affect Your Database 406410.1

· How To Determine Which Patchset Is Applied ? 421533.1

· How to 'DROP' a Datafile from a Tablespace 111316.1

· How to enable/disable Archive Log Mode In 10g and 11g 371139.1

· How To Find and Download The Latest Patchset and Associated Patch Number For Oracle Database Release 330374.1

· How To Find Out If OLAP is Being Used And How To Remove OLAP 739032.1

· How To Find Version Of Oracle Client ? 417395.1

· How To Find Which Oracle Database Edition Is Currently Running? 417573.1

· How To Give Grant Select On X$ Objects In Oracle 10g? 453076.1

· How to Move Tables from One Tablespace to Another. 147356.1

· How to perform FULL System Export/Imports 10767.1

· How to Move Tables from One Tablespace to Another 147356.1

· How To Move All Tables From One User To Another Tablespace 158162.1

· How to perform ORACLE_HOME backup ? 565017.1

· How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN 223543.1

· How to Recover from a Lost Datafile with Different Scenarios 198640.1

· How to Recreate the Controlfile 1012929.6

· How to Rename or Move Datafiles and Logfiles 115424.1

· How to Resize a Datafile 1029252.6

· How To Resize and/or Add Redo Logs 602066.1

· HOW TO SETUP LOCK VIEWS SUCH AS DBA_LOCKS AND DBA_BLOCKERS 1056205.6

· How to Shrink the datafile of Temporary Tablespace 273276.1

· How to Shrink the datafile of Undo Tablespace 268870.1

· How to Tell if the Oracle Software is 32-Bit or 64-Bit 119707.1

· How to use JServer Accelerator and the NCOMP Utility 134985.1

· How To Use Automatic Shared Memory Management (ASMM) In Oracle10g 295626.1

· How to Tell if the Oracle Software is 32-Bit or 64-Bit 119707.1

· Identify the corruption extension using RMAN/DBV/ANALYZE etc 836658.1

· Information On Installed Database Components and Schemas 472937.1

· Maintenance of Online Redo Log Groups and Members 102995.1

· monitoring sessions and SQL text 234162.1

· MOVING TABLES BETWEEN TABLESPACES USING EXPORT/IMPORT 1012307.6

· NET8 TRACING 1030488.6

· Recreating database objects 30910.1

· Retrieve deleted files on Unix / Linux using File Descriptors 444749.1

· What To Do and Not To Do When 'shutdown immediate' Hangs 375935.1

Oracle Install/Uninstall

· How to Deinstall and Reinstall XML Database (XDB) 243554.1

· How To Install and Uninstall the XML Developers Toolkit (XDK for PL/SQL which includes XDK for Java) 171658.1

· How to Manually Install and Uninstall Ultra Search 10gR2 Server Components 337992.1

· How To Manually Install Data Mining 420791.1

· How To Remove the Data Mining Option from the Database 297551.1

· HOW TO SETUP LOCK VIEWS SUCH AS DBA_LOCKS AND DBA_BLOCKERS 1056205.6

· Manual installation, deinstallation of Oracle Text 10gR1 and 10gR2 280713.1

· Removing 10g Database and Software from AIX, HP-UX, Linux, Sun and Tru64 Platforms 275493.1

· XDB 11g Install and Deinstall Note. 742014.1

Oracle Multimedia/interMedia

· Master Note for Oracle Multimedia/interMedia [ID 1078222.1]

· Where to Find the Information to Install, Upgrade, Downgrade and Deinstall interMedia/Oracle Multimedia? [ID 337415.1]

Oracle performance and optimization

· Checkpoint Tuning and Troubleshooting Guide 147468.1

· Cost Based Optimizer (CBO) Overview [ID 10626.1]

· Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above [ID 754931.1]

· How to Set the Optimizer Mode for the Database [ID 98956.1]

· Limitations of the Oracle Cost Based Optimizer [ID 212809.1]

· Oracle I/O Performance 276514.1

· Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1]

· REDO LOGS SIZING ADVISORY 274264.1

· Systemwide Tuning using STATSPACK Reports 228913.1

· What are Latches and What Causes Latch Contention 22908.1

· Which Optimizer is Being Used ? [ID 66484.1]

Oracle parameters

· Init.ora Parameter "COMPATIBLE" Reference Note [ID 30699.1]

· Init.ora Parameter "OPTIMIZER_FEATURES_ENABLE" Reference Note [ID 62337.1]

· Init.ora Parameter "OPTIMIZER_MODE" Reference Note [ID 30784.1]

Oracle scripts

· Script to Check Schemas with Stale Statistics [ID 560336.1]

· Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

Oracle Spatial

· Accidentally installed SPATIAL into SYS, SYSTEM or another Schema (413693.1)

· How to determine if Spatial is been used for the database? (726929.1)

· Index: How To Setup Oracle Spatial (220481.1)

· Re-installing Spatial with Existing Tables Having an SDO_GEOMETRY Column (250791.1)

Oracle Utilities - imp/exp, impdp/expdp

· Compatibility and New Features when Transporting Tablespaces with Export and Import [ID 291024.1]

· Compatibility Matrix for Export And Import Between Different Oracle Versions [Video] [ID 132904.1]

· Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video] [ID 553337.1]

· How to Find the Database and Export/Import Version [ID 175627.1]

Oracle Data Guard

· Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1]

· Bug 12702521: CANNOT SUPPORT SPARC<->AIX MIXED DATA GUARD DUE TO CONTROLFILE INCOMPATIBILITY

Database backup, restore, recovery

· How to Install, Configure, Check and Troubleshoot VERITAS NetBackup 4.5 for Oracle 209117.1

· How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN 223543.1

· Backup and Recovery Scenarios [ID 94114.1]

· Create a Duplicate Database with NOCATALOG on Same Node 245262.1

· Creating a Duplicate Database on a New Host 388431.1

· Data Recovery Advisor - Corruption Reference Guide [ID 1317849.1]

· Data Recovery Advisor -Reference Guide. [ID 466682.1]

· Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g [ID 28814.1]

· How to Make a Copy of a Noarchivelog Database on the Same Unix Machine [ID 375672.1]

· Master Note for Handling Oracle Database Corruption Issues [ID 1088018.1]

· Recover A Lost Datafile With No Backup 1060605.6

· Recovering from a lost datafile in a ROLLBACK tablespace 1013221.6

· Steps to Manually Clone a Database [ID 458450.1]

RMAN

· Creation Of Rman Duplicate Without Target And Recovery Catalog Connection. [ID 1113713.1]

· How to restore and recover a database from an RMAN backup. [ID 881395.1]

· Master Note For Oracle Recovery Manager (RMAN) [ID 1116484.1]

· Oracle10g / 11g - Getting Started with Recovery Manager (RMAN) [ID 360416.1]

· Oracle10G RMAN Database Duplication [ID 259694.1]

· Perform Backup Based RMAN DUPLICATE Without Connecting To Target Database For Both Disk & Tape Backups [ID 1375864.1]

· Pl/Sql Package Rman.Dbms_rcvcat Version xxxxx In Rcvcat Database Is Not Current [ID 1365435.1]

· RMAN -- Frequently Asked Question (FAQ) [ID 469777.1]

· RMAN: Tablespace Point In Time Recovery (TSPITR) Procedure 109979.1

· RMAN 'Duplicate Database' Feature in 11G [ID 452868.1]

· RMAN 11G : Data Recovery Advisor - RMAN command line example [ID 762339.1]

· RMAN 11gR1 : New Features [ID 809867.1]

· RMAN 11GR2 : DUPLICATE WITHOUT CONNECTING TO TARGET DATABASE [ID 874352.1]

· RMAN Compatibility Matrix [ID 73431.1]

· Rman Enhancements In Oracle 11g. [ID 1115423.1]

· RMAN Fast Incremental Backups [ID 262853.1] 10g

· RMAN Performance Tuning Diagnostics 311068.1

· Manual Completion of a Failed RMAN Duplicate 360962.1

· Recovery Manager and Tempfiles 305993.1

Oracle database communities:

· https://communities.oracle.com/

· Backup & Recover Community: https://communities.oracle.com/portal/server.pt/community/database_backup_and_recovery/243

Oracle Scripts

· SCRIPT: cre_tz_views.sql - Assess Time Zone usage in a Database 412971.1

· SCRIPT: DIAG - Obtaining Formatted Explain Plan Output with Autotrace 31101.1

· SCRIPT: REPORT FILE I/O STATISTICS 1019629.6

· SCRIPT: REPORT SESSION STATS 1020001.6

· Script: To Recompile Invalid Objects 73995.1

· Script: To Find Invalid Objects in the Database 1014072.102

· Script To Monitor Memory Usage By Database Sessions 239846.1

Oracle Securities

· All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Data Vault 207959.1

· Oracle Created Database Users: Password, Usage and Files References 160861.1

· Oracle script directory

· SYSDBA and SYSOPER Privileges in Oracle 50507.1

Oracle Troubleshooting

· All What you Wanted To Know About SPFILE and PFILE Files [ID 293698.1]

· Diagnosing High CPU Utilization 164768.1

· TROUBLESHOOTING GUIDE (TSG) : UNABLE TO EXTEND Errors [ID 1025288.6]

· Troubleshooting Guide - Large Objects (LOBS) 846562.1

· Troubleshooting Oracle Net 779226.1

· TROUBLESHOOTING GUIDE: TNS-12154 TNS:could not resolve service name 114085.1

Oracle and UNIX

· Automatic Startup and Shutdown on Digital UNIX [ID 62068.1]

· How to configure swap space Note:286388.1

· How to Determine Which Solaris Package Contains a Particular File? 470562.1

· How to set a common operating system environment by using oraenv or coraenv scripts ? Note:444830.1

· How To Troubleshoot an Oracle Database Software Installation on UNIX 181459.1

· Oracle® Database on AIX®,HP-UX®,Linux®,Mac OS® X,Solaris®,Tru64 Unix® Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.1) 169706.1

· Sun Solaris and Swap Space Usage 757718.1

· TECH: Unix Virtual Memory, Paging & Swapping explained 17094.1

· UNIX: Checklist for Resolving Connect AS SYSDBA Issues 69642.1

Oracle utilities

· Compatibility Matrix for Export And Import Between Different Oracle Versions 132904.1

· Csscan output explained 444701.1

· DBVERIFY - Database file Verification Utility (7.3.2 - 10.2) 35512.1

· Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) 745809.1

· Oracle DataPump Quick Start 413965.1

· Oracle Server - Export and Import FAQ 175624.1

· Oracle Server - Export Data Pump and Import DataPump FAQ 556636.1

· What is TRCSESS and How to use it ? 280543.1

More Oracle DBA tips, please visit Oracle DBA Tips

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

Thursday, March 15, 2012

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

Wednesday, March 14, 2012

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

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.
Re-run the geodatabase upgrade.

More Oracle DBA tips, please visit Oracle DBA Tips

Spatial DBA - Oracle and ArcSDE

Friday, March 16, 2012

Oracle Notes and Links for DBA

Attache some comments when changing Oracle initialization parameters in spfile

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

Thursday, March 15, 2012

SDE schema import and Oracle error ora-30512

Wednesday, March 14, 2012

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

Upgrading ArcSDE 10 and ORA-00942

Description

Cause

Solution or Workaround

Popular Posts in Last 30 Days

About Me

My favorites

Blog Archive

Labels

Disclaimer