expdp or exp were failing with ORA-39126 SYS.KUPW$WORKER

The expdp or exp were failing with the below errors:

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [PROCACT_SCHEMA:"OPS$ORACLE"]
ORA-19051: Cannot use fast path insert for this XMLType table
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9001
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
70000007befb878     20462  package body SYS.KUPW$WORKER
70000007befb878      9028  package body SYS.KUPW$WORKER
70000007befb878     10935  package body SYS.KUPW$WORKER
70000007befb878      2728  package body SYS.KUPW$WORKER
70000007befb878      9697  package body SYS.KUPW$WORKER
70000007befb878      1775  package body SYS.KUPW$WORKER
700000072fa8930         2  anonymous block

Reason:
When the 11g DB was started LD_LIBRARY_PATH and LIBPATH were pointing to 10g Home (any DB at the time of startup if the above Library variables are not pointing to current DB ORCLE_HOME)

- in my case we have oracle versions in the server 11g and 10g, at the time 11g DB startup LIB variables are pointing to 10g.

Fix:
I’ve set the below parameters explicitly and restart the database which has fixed the issue
ushux37$ export LIBPATH=/u01/app/oracle/product/11.2.0/lib:/u01/app/oracle/product/11.2.0/lib32
ushux37$ export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/lib:/u01/app/oracle/product/11.2.0/network/lib

setting parameter "OPTIMIZER_INDEX_COST_ADJ" in 11g

- We have migrated one of the critical production database from 10g to 11g(11.2.0) successfully and also application team confirmed everyting working as expected. After a week time, application team reported issue as few of the SQLs(huge select) are running very slow (in 10g they completed in 2.5 mintues, but 11g those are not completed after 10 mintues also).

- We started working on this issue and find the solution, "There is an oracle optimizer paramemter "OPTIMIZER_INDEX_COST_ADJ", which by default
has a value of 100 in 11g.

- This value means that the optimiser considers a full table scan to have the same cost as
  using an index. Consequently queries were not using the indexes at all and getting slower
  and slower as the tables became larger.

- We have tested by setting "OPTIMIZER_INDEX_COST_ADJ" with different values 20,15 and 10 and we observed massively improves performance with value 10, the query which is completed in 2.5 mintues in 10g, after setting this parameter value to 10 in 11g it just completed in 28 seconds.

Note: The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

11gR2 ASM Commands and Details

ASM

ASM Parameters:
==> There are three groupings of parameters for an ASM instance, they are below.
- INSTANCE_TYPE=ASM is the only mandatory parameter setting.
- There are a number of ASM-specific parameters, These have names starting with ASM_.
- Some database parameters are valid for ASM.
  For example, MEMORY_TARGET

ASM_DISKGROUPS:

- ASM_DISKGROUPS specifies a list of disk group names that ASM automatically mounts at instance startup.
- Is automatically modified when disk groups are added, deleted, mounted, or unmounted if using an SPFILE
- Must be manually adjusted if using a PFILE(Except when ASMCA is used to create a new disk group)

Disk Groups Mounted at ASM startup:

==> At startup, the Oracle ASM instance attempts to mount the following disk groups:
- Disk groups specified in the ASM_DISKGROUPS initialization parameter
- Disk group used by Cluster Synchronization Services (CSS) for voting files
- Disk groups used by Oracle Clusterware for the Oracle Cluster Registry (OCR)
- Disk group used by the Oracle ASM instance to store the ASM server parameter file (SPFILE)

ASM_POWER_LIMIT:

- The ASM_POWER_LIMIT initialization parameter specifies the default power for disk rebalancing.
 - Default is 1.
 - Meaning ASM conducts rebalancing operations using minimal system resources
- Allowable range is 0 to 11.
- 0 disables rebalancing operations.
- Lower values use fewer system resources but result in slower rebalancing operations.
- Higher values use more system resources to achieve faster rebalancing operations.
- It can be set dynamically using ALTER SYSTEM or ALTER SESSION.

CLUSTER_DATABASE:

- CLUSTER_DATABASE specifies whether or not storage clustering is enabled.
- Set CLUSTER_DATABASE = TRUE for multiple ASM instances to access the same ASM disks concurrently.
- Clustered ASM can support clustered (RAC) and single-instance (non-RAC) databases.

MEMORY_TARGET:

- MEMORY_TARGET specifies the total memory used by an ASM instance.
- Oracle strongly recommends that you use automatic memory management for ASM.
- All other memory-related instance parameters are automatically adjusted based on MEMORY_TARGET.
- The default value of 272 MB is suitable for most environments. It can be increased dynamically using ALTER SYSTEM.

Adjusting ASM Instance Parameters in SPFILEs:

- The server parameter file (SPFILE) is a binary file that cannot be edited using a text editor.
- Use Oracle Enterprise Manager or the ALTER SYSTEM SQL command to adjust ASM instance parameter settings in an SPFILE.

Eg:
SQL> ALTER SYSTEM SET ASM_DISKSTRING='ORCL:*' SID='*' SCOPE=SPFILE;

Note: In a clustered ASM environment, SPFILEs should reside in ASM or a cluster file system.

- For example, to adjust your SPFILE so that your ASM environment discovers only Oracle ASMLib disks, you could execute:

SQL> ALTER SYSTEM SET ASM_DISKSTRING='ORCL:*' SID='*' SCOPE=SPFILE;

Starting and Stopping ASM Instances by Using srvctl:

- The Sever Control utility (srvctl) can be used to start and stop ASM instances.

One node at a time:

$ srvctl start asm -n host01$ srvctl start asm -n host02
$ srvctl status asm -n host01
ASM is running on host01.
$ srvctl status asm -n host02
ASM is running on host02.

All nodes simultaneously:

$ srvctl stop asm
$ srvctl status asm -n host01
ASM is not running on host01.
$ srvctl status asm
ASM is not running on host01,host02.
$ 

Starting and Stopping ASM Instances by Using ASMCA and ASMCMD:

- The ASMCA utility shown in the slide allows you to start and stop an ASM instance. The ASMCMD utility also includes the ability to start and start ASM instances with the following

commands:

asmcmd start asm

$ asmcmd
ASMCMD [+] > shutdown
ASMCMD [+] > shutdown --immediate
ASMCMD [+] > shutdown --abort
ASMCMD> startup --nomount --pfile asm_init.ora
ASMCMD> startup --mount

Note: Oracle Clusterware is a client of ASM when the OCR files and voting files are in ASM disk groups. Stopping the Oracle Clusterware services includes stopping ASM.

Starting and Stopping the ASM Listeners:

Using the lsnrctl utility:

$ lsnrctl start listener
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-OCT-2009 22:44:22
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Starting /u01/app/11.2.0/grid/bin/tnslsnr: please wait...
... Intermediate output removed ...
The command completed successfully
$

Using the srvctl utility:

$ srvctl start listener -n host01
$

ASM Dynamic Performance Views:

- The ASM instance hosts memory-based metadata tables presented as dynamic performance views.
- Is accessed by ASM utilities to retrieve metadata-only information using the SQL language
- Contains many dedicated ASM-related views such as:

V$ASM_ALIAS
V$ASM_ATTRIBUTE
V$ASM_CLIENT
V$ASM_DISK
V$ASM_DISK_IOSTAT
V$ASM_DISK_STAT
V$ASM_DISKGROUP
V$ASM_DISKGROUP_STAT
V$ASM_FILE
V$ASM_OPERATION
V$ASM_TEMPLATE
V$ASM_ACFSVOLUME
V$ASM_FILESYSTEM

Note: The V$ASM_* views exist in both ASM and database instances. The rows returned will vary.

- Following is a typical example of a query that retrieves information about disks in a particular disk group:

SQL> SELECT G.NAME DISK_GROUP, D.NAME, D.STATE, D.TOTAL_MB,
2 D.FREE_MB
3 FROM V$ASM_DISK D, V$ASM_DISKGROUP G
4 WHERE D.GROUP_NUMBER = G.GROUP_NUMBER
5 AND G.NAME = 'DATA';
DISK_GROUP NAME STATE TOTAL_MB FREE_MB
---------- ------ -------- ---------- ----------
DATA SDE5 NORMAL 977 136
DATA SDE6 NORMAL 977 134
DATA SDE7 NORMAL 977 135
DATA SDE10 NORMAL 977 150
DATA SDE8 NORMAL 977 150
DATA SDE9 NORMAL 977 150

Tracing Remote Session and tkprof

Methods to set Event 10046:
==========================
Method 1 :
syntax: exedc dbms_system.set_ev(l_sid,l_serial#,10046,l_level,'');
SQL > execute dbms_system.set_ev(10,224,10046,8,'');

Method 2:SQL> oradebug unlimit
SQL> oradebug setospid 10927
SQL> oradebug event 10046 trace name context forever, level 8                 ------- this is to turn on
SQL> oradebug event 10046 trace name context off                             

TKPROF:
tkprof clndv10_ora_21234002.trc clndv10_ora_21234002.txt table=system.plan_table explain=system/manager sys=no waits=yes sort=prsela, exeela, fchela
Note: in the tkprof sys=no means it won't show the sqls belongs to sys user.

Genating DDL from export dump file or check expdp dumpfile is corruped or not

- We can generate DDL from export dump file using below command, it won't import the data into DB, it generate the DDLs and save into the file.
- What does this sqlfile do?
- Here we have used sqlfile option, which will not import any data into the database.
- This will write all DDL statements (which will be executed if a import is performed) into the file which we mentioned in the command.
- So this will read the entire DATAPUMP Export dump file and will report if a corruption is detected
 nohup impdp / dumpfile=expdp_hsdv04_maximo7_2Jul2014.dmp logfile=Impdp_maximo7_into_hsdv01_07Jul14.log schemas=MAXIMO7 directory=dumpdir REMAP_TABLESPACE=MAXTEMP:TEMP sqlfile=Impdp_dump_file_corruption_check.sql &