Friday, February 25, 2011

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Environment
10.2.0.4.0 Oracle Database on OEL 4 platform.

Problem
After I executed following expdp command, I immediately received following errors:

expdp system/******** tables=user1.table1 directory=expimp dumpfile=expdp_user1_table1.dmp logfile=expdp_user1_table1_`date +"%Y-%m-%d-%H%M"`.log parallel=4 tables=user1.table1


ORA-31693: Table data object "user1"."table1" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-39077: unable to subscribe agent ORA-39077: unable to subscribe agent KUPC$A_1_20110225

According to this link, I reattached to the job and modified my expdp command like following:
1 Check the expdp job name:

select job_name,state from dba_datapump_jobs;
JOB_NAME STATE
—————————— ——————————
SYS_EXPORT_TABLE_01 NOT RUNNING
2 Re-attach to the job and kill it
expdp system/xxx attach=SYS_EXPORT_TABLE_01

Export> kill_job

3 Redo export
expdp system/******** tables=user1.table1 directory=expimp dumpfile=expdp_user1_table1_%U.dmp logfile=expdp_user1_table1_`date +"%Y-%m-%d-%H%M"`.log parallel=4 tables=user1.table1



The above execution returned following new errors:

ORA-31693: Table data object "USER1"."TABLE1
ORA-29913: error in executing ODCIEXTTABLEOPEN callout .....
ORA-39077: unable to subscribe agent ORA-39077:........



Solution
Remove "parallel=4", it just works. I learned from my experience doing expdp/impdp in RAC environment, the "parallel" option is tricky, in 10g or 11g RAC, I had to add one more option of "cluster=N" in addition to "parallel=M", or it will fail.

References:

Labels:

Thursday, February 24, 2011

CSS-00120: failed to enumerate tables to scan

Problem
I had spent hours trying to figure out the cause of following errors when I was running the following command:

$csscan   TABLE=SYS.SQL_VERSION$ FROMCHAR=WE8MSWIN1252 TOCHAR=AL32UTF8 LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000


......................
Enumerating table to scan...
ORA-00942: table or view does not exist
CSS-00150: failed to enumerate table
CSS-00120: failed to enumerate tables to scan
Scanner terminated unsuccessfully.

Cause
Finally, I checked following values and found out inconsistencies:
[oracle@cash admin]$ which csscan
/u01/11.2.0/grid/bin/csscan
[oracle@cash admin]$ which sqlplus
/u01/11.2.0/grid/bin/sqlplus
[oracle@cash admin]$ echo $ORACLE_HOME
/u02/oracle/product/11.2.0/dbhome_1


According to this note - Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) [ID 745809.1]:
"Then the wrong cminst.sql was runned. For example using csscan from an 10.2.0.4 client but the Csminst.sql that was used is the one from the 10.2.0.1 database oracle home. Please run the Csminst.sql from the actual csscan home."


Because the "csscan" executed was the one under "/u01/11.2.0/grid/bin/csscan, rather than the one under $ORACLE_HOME/bin/csscan (/u02/oracle/product/11.2.0/dbhome_1/bin/csscan), and the "csminst.sql" script was executed from "/u02/oracle/product/11.2.0/dbhome_1/rdbms/admin", this inconsistency caused the above errors.

Solution
After I reset the "PATH" environment values, the errors were gone.
-----Before change

[oracle@sting ~]$ echo $PATH
/u01/11.2.0/grid/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/NX/bin:/opt/dell/srvadmin/bin:/u02/oracle/product/11.2.0/dbhome_1/bin:/home/oracle/bin:.


--After change
[oracle@sting ~]$ . .bash_profile
[oracle@sting ~]$ echo $PATH
/u02/oracle/product/11.2.0/dbhome_1/bin:/u01/11.2.0/grid/bin:/home/oracle/bin:/u01/11.2.0/grid/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/NX/bin:/opt/dell/srvadmin/bin:/u02/oracle/product/11.2.0/dbhome_1/bin:/home/oracle/bin:..


--Rerun csscan

[oracle@sting ~]$ $ORACLE_HOME/bin/csscan   TABLE=SYS.SQL_VERSION$ FROMCHAR=WE8M


Character Set Scanner v2.2 : Release 11.2.0.2.0 - Production on Thu Feb 24 16:05
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Username: sys/Thunder1 as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Enumerating table to scan...
. process 1 scanning SYS.SQL_VERSION$[AAAACVAABAAAASgAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.



Related Links

  1. Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) [ID 745809.1]
  2. Changing the Database Character Set ( NLS_CHARACTERSET ) [ID 225912.1]
  3. Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) [ID 260192.1]
  4. The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g and 11g [ID 276914.1]
  5. Csscan output explained [ID 444701.1]
  6. Which Character Set Supports Which Language [ID 62421.1]
  7. AL32UTF8 / UTF8 (Unicode) Database Character Set Implications [ID 788156.1]
  8. Choosing between WE8ISO8859P1, WE8ISO8859P15 or WE8MSWIN1252 as NLS_CHARACTERSET [ID 264294.1]


Labels: ,

Wednesday, February 23, 2011

ORA-24247: network access denied by access control list (ACL)

A developer encountered following errors today in one of our 11.2.0.2 single instance database (OEL 5.5):
SQL> exec pakage-name(12345678,3885);
BEGIN express6genoutput(12345678,3885); END;

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1527
ORA-29261: bad argument
ORA-06512: at "user1.pakage-name", line 22
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1 

Based on this Metalink note: ORA-24247 Using UTL_TCP, UTL_HTTP, UTL_SMTP and UTL_MAIL with11gR1 or Later [ID 1209644.1], I fixed this problem after executing following commands:
Note:
1 I replaced the "SCOTT" with the username in my database that is having this kind of error.
2 Before I executing those commands, I did "find / -name network_service.xml" as root user, which returned nothing, so I didn't uncomment the 2nd comment.

BEGIN

  -- Only uncomment the following line if ACL "network_services.xml" has already been created
  --DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_service.xml');

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => 'network_services.xml',
    description => 'FTP ACL',
    principal => 'SCOTT',
    is_grant => true,
    privilege => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'network_services.xml',
    principal => 'SCOTT',
    is_grant => true,
    privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'network_services.xml',
    host => '*');

  COMMIT;

END;

Labels:

Links to some Oracle "My Support" features and functionality

Links to some Oracle "My Support" features and functionality


Getting Started
• Register for Advisor Webcasts, Doc ID 405149.1.
• Search the knowledge and bug database, Doc ID 733384.5.
• Sign-up for hot topic emails (Doc ID 793436.1) for knowledge articles, product alerts, bugs, and desupport notices as well as subscribe to Oracle newsletters.
• Manage the lifecycle of our Service Requests, Doc ID 733245.5.

Searching for Solutions
• Use the Knowledge > Browse Knowledge region to type in the product name or use the product menus to select a specific product.
• Search the knowledgebase, for all Oracle products, by entering a search term in the Global Search bar.
• Target a specific source by clicking the down arrow button at the left of the Global Search bar and choosing knowledgebase, archives, bug database, article ID, or documentation.
• The Advanced Search allows you to refine and combine options to create a more precise search. Options include selecting multiple knowledge sources, products, and keywords.
• Once you have results from any of these searches, use the Refine Search options narrowing your search and making the results more relevant.
• Knowledge Searching, Doc ID 733384.5.
• Recorded Training, Doc ID 603505.1, Quick Search in My Oracle Support viewlet
• PowerView, Doc ID 780132.5.

Diagnostic and Support Tools
• Navigate to the Knowledge > Browse Knowledge > Tools and Training > Diagnostic Tools
• Diagnostic Tools Catalog, Doc ID 559339.1
Advisor Webcasts, Doc ID 405149.1 for Support Tools Training and Web Seminars
• Remote Diagnostic Agent (RDA) 4 - Getting Started, Doc ID 314422.1
March 2010

My Oracle Support configuration manager
• Navigate to More > Collector tab to watch a video tutorial, FAQs, Prerequisites, Getting Started, Quick Start, Installation and Administration Guides, and other helpful resources.
• Health Checks Catalog, Doc ID 868955.1
• Advisor Webcasts, Doc ID 405149.1, titled My Oracle Support configuration manager
• Recorded Training, Doc ID 603505.1, My Oracle Support configuration manager viewlet
• Service Request Priority Routing Using Configuration Manager, Doc ID: 555057.1 as well as Priority Handling
• Inventory Report, Doc ID 733251.5

Patches and Updates
• My Oracle Support Patches and Updates, Doc ID 847410.5
• Recorded Trainings, Doc ID 603505.1 for the Patches and Updates viewlet
• Introduction to Oracle Recommended Patches, Doc ID 756388.1
• Improved Patches & Updates Tab – What You Need to Know, including New Proactive Patching Features, Doc ID 943815.1
• Critical Patch Updates and Security Alerts

Oracle Collaborative Support (OCS)
• The support engineer initiates the web conference session and you can join via My Oracle Support by navigating to Service Request > Action > Join Web Conference or by going to stconference.oracle
• Oracle Collaborative Support (OCS) via Oracle Web Conferencing (OWC), Doc ID 278545.1

Support Education
• Navigate to Knowledge > Browse Knowledge > Tools and Training > Training (Web Seminars)
• Refer to Training (Web Seminars), Doc ID 559337.1 or oracle.com/support/seminars
• Send an email to: support-training_us@oracle.com
• Listen to the Speed or Recorded Trainings, Doc IDs 555057.1 or 603505.1, respectively
Community-Wide Resources
• Log into My Oracle Support > Community tab or go to http://communities.oracle.com/ (Login Required)
• Oracle Community or Support Community Resources*
• Oracle Technology Network (OTN)*
• Blogs.Oracle.com* • Official Oracle Wiki*
• AskTom* • Oracle Podcasts*
• Oracle By Example (OBE)* • Oracle PartnerNetwork (OPN)*

Documentation
• Navigate to Knowledge > Online documentation
• eTRM Access Document, ID 150230.1
• Oracle Documentation*
*NOTE: Login Not Required

Labels:

Tuesday, February 8, 2011

DataPump Export/Import in Rac Instance Alters SERVICE_NAMES

Environment
2 nodes 11.2.0.2 Oracle RAC on OEL 5.5 platform. 


Problem
Yesterday while other DBA was doing DataPump Import on the same node, I was working on Grid Control agent installation and noticed that the agent host's service name was changed to something like "SYS$SYS.KUPC$C_1_20060315133546.xxx.yyyy.COM". In the alert log file, it recorded that:

ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20110207110835.node.hostname.COM','SYS$SYS.KUPC$C_1_20110207110835.node.hostname.COM','SYS$SYS.KUPC$C_1_20110207111059.node.hostname.COM' SC
OPE=MEMORY SID='node1';
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_1_20110207111059.node.hostname.COM','SYS$SYS.KUPC$S_1_20110207110835.node.hostname.COM','SYS$SYS.KUPC$C_1_20110207110835.node.hostname.COM','S
YS$SYS.KUPC$S_1_20110207111059.node.hostname.COM' SCOPE=MEMORY SID='node1';


In the $ORACLE_BASE/sysman/emd/targets.xml file in the agent host, there are several similar service name entries, which causing Grid Control server to connect to the agent. 


Solution
According to Metalink note 363396.1 - DataPump Export: Rac Instance Startup/Shutdown Alters SERVICE_NAMES [ID 363396.1]
"DataPump doesn't change existing service name but adds new ones for its own queue operation.

Assuming you started instance with service name TEST01 and TEST02, it will add another one at the start of operation and put the original value back at the end of operation.




There is no need to be concerned of DataPump alerting SERVICE_NAMES.

It adds new services on top of existing ones and removes the added one at the end of operation."

According to the note, I didn't change the service name back to its original value, instead I modified the targets.xml file to replace the newly created service name to its original one like "node1.hostname.com". 

When the DataPump Import finished at 16:51, the alert log file recorded following actions:

Mon Feb 07 16:51:09 2011
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20110207164733.node1.hostname.COM' SCOPE=MEMORY SID='racflo1';
ALTER SYSTEM SET service_names='node1.hostname.com' SCOPE=MEMORY SID='racflo1';



Related links





Labels: , ,

Friday, February 4, 2011

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

Today I encountered this ORA- error when trying to update a table:

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE


According to this post, I resolved the error by rebuild the "FAILED" index after identifying which index was corrupt: 
select index_name,index_type,status,domidx_status,domidx_opstatus from user_indexes where index_type = 'DOMAIN' and (domidx_status <> 'VALID' or domidx_opstatus <> 'VALID');

2 alter index &indexname rebuild; 

Labels: ,

Wednesday, February 2, 2011

Database hang: how to collect data for troubleshooting?

Here are some good Metalink Notes related to this hang issue:


A. How to collect 2 Hanganalyze and 2 Systemstate dumps.?
Using SQL*Plus connect as SYSDBA using the following command:
sqlplus " / as sysdba"
If there are problems making this connection then in 10gR2 and above, the sqlplus "preliminary connection" can be used :
sqlplus -prelim " / as sysdba"



Do this 2 times in 2 separate windows, creating 2 sqlplus sessions (SQL1 and SQL2)
In SQL1 gather the hanganalyze by executing the following:

SQL1> oradebug setmypid SQL1> oradebug unlimit; SQL1> oradebug hanganalyze 3
In SQL2 gather the systemstates by executing the following:

SQL2> oradebug setmypid SQL2> oradebug unlimit; SQL2> oradebug dump systemstate 266
Gather a second hang analyze having waited at least 1 minute to give time to identify process state changes. In SQL1 execute the following:

SQL1> oradebug hanganalyze 3


In SQL2 execute the following to collect a second systemstate dump:

SQL2> oradebug setmypid SQL2> oradebug unlimit; SQL2> oradebug dump systemstate 266
If you are using systemstate level 266 and it is taking much longer than expected to generate the dump file, then end this systemstate dump and try level 258.


Purpose

This bulletin describes the methods and tools used in diagnosing database hanging issues and performance issue).
These issues may be due to tuning problems, design problems or Oracle bugs such as latching issues.
We determine the cause by narrowing down the problem and getting as much information as possible WHILE THE DATABASE IS HANGING.

The following diagnostic steps are discussed.

1. Describe the Problems. Under all situations go through this sections and describe the problem.
2 Look for errors.
3. Do the requested queries.
4. Gather OS level data.
5. Get systemstate and hanganalyze dumps.
6. Get BSTAT-ESTAT OR STATPACK output.
7. Generate a core dump and run a debugger (contact support first).
8. If certain process get PROCESSSTATE dumps
9. Check List for diagnostics.

Note: It may not be necessary to shutdown the database to stop the hanging. But if you MUST shutdown before speaking to support, please attempt to get these diagnostics so that we can debug the problem.
Without these diagnostics it may be impossible for us to determine the cause of the problem.




Introduction

HANGFG (Hang file generator) is a series of unix shell scripts used to automate the generation and collection of hanganalyze and  systemstate trace files. HANGFG generates and collects hang trace files based on the impact of taking diagnostic traces on a system which is already in a degraded state. The overall decision on what level of impact the user can afford is left up to the user when he runs HANGFG, as the level of impact is passed in as an argument to the tool. HANGFG is also capable of making this decision for the user if the user selects light or medium impact (option 1 or 2) as an argument to the tool.  HANGFG is RAC aware and can run in either a RAC or non RAC environment.

Overview

One of the major problems with obtaining the necessary diagnostic information to diagnose database hanging problems is having the necessary diagnostic data collected while the problem is actually occurring. Additionally, the necessary diagnostic data is seldom collected because of the time it takes to react to recognizing there is a problem, trying to determine what kind of data to collect, and knowing how to collect the data. Frequently, the problem has passed or the database has to be shutdown to correct the problem. HANGFG automates the generation and collection of hang diagnostic traces. The user need only run one diagnostic, HANGFG, during the database hang. The commands to issue the generation of diagnostic traces are performed by HANGFG, thus freeing the user from having to know and issue the individual cryptic commands to the database to generate these traces. HANGFG will also look to see how badly a system is degraded before issuing any commands that could degrade the system even further. Finally HANGFG will collect all the trace files that are produced from issuing these commands as well as any other oracle trace file that gets updated from the time the first hang diagnostic is performed. This greatly increases the probability that all the necessary hang related diagnostic traces are collected at one time thereby reducing the amount of pinging between the user and Oracle support for requests for additional diagnostic data requests.


Labels: ,

RAC database hang issue

One of two-nodes Oracle 10.2.04 RAC database hanged and restarted around 1:20 AM this morning.  According to the trace files, alert log files and other log files, here are some error messages reported in those files:

  • Received ORADEBUG command 'dump errorstack 1' from process Unix process pid: 16209, image: *** 2011-02-02 10:21:13.667
  • ksedmp: internal or fatal error
    In alert_node1.log file:
       
    • Tue Feb  1 16:52:47 2011
    • IPC Send timeout detected. Receiver ospid 10555 MMNL absent for 2001 secs; Foregrounds taking over MMNL absent for 2001 secs; Foregrounds taking over Tue Feb 1 17:40:21 2011 Errors in file /oracle/admin/imsr/bdump/imsr1_lmd0_10555.trc:
    • Tue Feb 1 23:15:27 2011

      Trace dumping is performing id=[cdmp_20110201162127]

      Wed Feb 2 01:10:51 2011

      Errors in file /oracle/admin/imsr/bdump/imsr1_pmon_10547.trc:

      ORA-00482: LMD* process terminated with error

      Wed Feb 2 01:21:46 2011

      PMON: terminating instance due to error 482

      Wed Feb 2 01:21:46 2011

      System state dump is made for local instance

      System State dumped to trace file /oracle/admin/imsr/bdump/imsr1_diag_10549.trc

    • Wed Feb 2 01:21:51 2011

      Instance terminated by PMON, pid = 10547

      Wed Feb 2 01:21:52 2011

      Instance terminated by USER, pid = 16125

      Wed Feb 2 01:21:54 2011

      Starting ORACLE instance (normal)
    • Wed Feb 2 09:52:33 2011

      Thread 1 advanced to log sequence 12344 (LGWR switch)

      Current log# 2 seq# 12344 mem# 0: /oradata/imsr/redo02.log

      Thread 1 cannot allocate new log, sequence 12345

      Checkpoint not complete

      Current log# 2 seq# 12344 mem# 0: /oradata/imsr/redo02.log

      Wed Feb 2 09:52:42 2011

      Thread 1 advanced to log sequence 12345 (LGWR switch)

      Current log# 1 seq# 12345 mem# 0: /oradata/imsr/redo01.log

      Wed Feb 2 10:08:26 2011

      IPC Send timeout detected. Receiver ospid 16215

      Wed Feb 2 10:21:13 2011

      Errors in file /oracle/admin/imsr/bdump/imsr1_lmd0_16215.trc:

      Wed Feb 2 10:27:38 2011

      Trace dumping is performing id=[cdmp_20110202094318]
    In alert_node2.log file:

    • Waiting for instances to leave: 
    • IPC Send timeout detected.Sender: ospid 11471

      Receiver: inst 1 binc 1824903189 ospid 1621
    • Wed Feb 2 10:16:22 2011


      MMNL absent for 1807 secs; Foregrounds taking over


      Wed Feb 2 10:16:33 2011


      Waiting for instances to leave:


    Here are some Metalink notes and articles I found related to the above error messages:

    Based on the above links, it's likely that followings are the causes of this database hang problem:
    1 MAXBYTES is smaller than BYTES
    set lines 300
    col file_name format a50
    select file_name, tablespace_name, bytes/1024/1024, maxbytes/1024/1024 from dba_data_files;

    2 Hit Oracle bugs (very likely)

    3 Automatic SGA setting caused crash

    To be continued.....................


    Labels: ,

    Oracle articles to read later

    Here are some Oracle related articles I need to read later:

    Labels: ,