Undocumented/Hidden Parameters in Oracle 11g

Oracle contains two type of initialization parameters i.e,

1.) Documented Initialization Parameter.
2.) Undocumented Initialization Parameter. 

Here, We will check about the  undocumented Initialization Parameter .

Undocumented Initialization parameter are also called hidden Parameter and there is no document available about these parameter . Every version of Oracle has special undocumented initialization parameters. These undocumented initialization parameters are usually only used in emergencies or with the express consent of Oracle technical support.

The X$KSPPI table contains all of the parameters that influence the oracle database in operation.  X$tables are available only when we connect to the oracle instance as the user 'SYS' .                               

The current version i.e, Oracle 11g has 2394 parameters, out of which 343 parameters are listed in the V$parameter view. This means that there are still 2051 parameters which are not listed in either V$views or Oracle Documentation . Hidden parameter starts with underscore sign  "_"  . To check all initialization  parameter fire below command.

SQL> select count(*) from X$KSPPI;
  COUNT(*)
----------
      2394

To check documented parameter fire the following comand .

SQL> select count(*) from v$parameter;
  COUNT(*)
----------
       343

Here we will discuss when to use hidden parameter , where to use them, and what tricks are involved in using them. Some issues involved with using these parameters are listed below :

1.) Undocumented parameters change from version to version (even in the same versions from patchset to patchset) without any notice. These parameters are not consistent across versions.

2.) All these parameters may not be available with all the ports. This means that they are not compatible across the operating systems. A few parameters are Operating System specific.

3.) Use of undocumented parameters without the knowledge of Oracle Support is a violation of the Support agreement with Oracle. Oracle Worldwide support has every right to refuse to support the databases affected by using these parameters.

4.) A few parameters can potentially corrupt the databases and some parameters within those few will help us in bringing up the corrupted database with minimal data loss. So these parameters are equally useful inspite of those dangers.

5.) Since no external documentation is available, it is important to test them before using these parameters in our production system. It is also suggested to have a complete backup before using these parameters.

We can obtain all the undocumented Parameter i.e, hidden Parameter by giving the following command .

SQL> select a.ksppinm name, b.ksppstvl value,b.ksppstdf deflt,

decode (a.ksppity, 1,

'boolean', 2,

'string', 3,

'number', 4,

'file', a.ksppity) type, a.ksppdesc description

from

sys.x$ksppi a,

sys.x$ksppcv b

where   a.indx = b.indx

   and

a.ksppinm like '\_%' escape '\'

order by name

We can also check the hidden and non-hidden parameter from memory by creating pfile .The command for creating the pfile is 

SQL> Create pfile='C:\pfile.ora'  from memory;

The above command  will not work in case of 10g or lower version of oracle. In my case the content of  pfile created from memory is as :

__db_cache_size=104M

__java_pool_size=4M

__large_pool_size=4M

__oracle_base='D:\oracle' # ORACLE_BASE set from environment

__pga_aggregate_target=132M

__sga_target=196M

__shared_io_pool_size=0

__shared_pool_size=76M

__streams_pool_size=0

_aggregation_optimization_settings=0

_always_anti_join='CHOOSE'

_always_semi_join='CHOOSE'

_and_pruning_enabled=TRUE

_b_tree_bitmap_plans=TRUE

_bloom_filter_enabled=TRUE

_bloom_folding_enabled=TRUE

_bloom_pruning_enabled=TRUE

_complex_view_merging=TRUE

_compression_compatibility='11.2.0.0.0'

_connect_by_use_union_all='TRUE'

_convert_set_to_join=FALSE

_cost_equality_semi_join=TRUE

_cpu_to_io=0

_dimension_skip_null=TRUE

_eliminate_common_subexpr=TRUE

_enable_type_dep_selectivity=TRUE

_fast_full_scan_enabled=TRUE

_first_k_rows_dynamic_proration=TRUE

_gby_hash_aggregation_enabled=TRUE

_generalized_pruning_enabled=TRUE

_globalindex_pnum_filter_enabled=TRUE

_gs_anti_semi_join_allowed=TRUE

_improved_outerjoin_card=TRUE

_improved_row_length_enabled=TRUE

_index_join_enabled=TRUE

_ksb_restart_policy_times='0'

_ksb_restart_policy_times='60'

_ksb_restart_policy_times='120'

_ksb_restart_policy_times='240' # internal update to set default

_left_nested_loops_random=TRUE

_local_communication_costing_enabled=TRUE

_minimal_stats_aggregation=TRUE

_mmv_query_rewrite_enabled=TRUE

_new_initial_join_orders=TRUE

_new_sort_cost_estimate=TRUE

_nlj_batching_enabled=1

_optim_adjust_for_part_skews=TRUE

_optim_enhance_nnull_detection=TRUE

_optim_new_default_join_sel=TRUE

_optim_peek_user_binds=TRUE

_optimizer_adaptive_cursor_sharing=TRUE

_optimizer_better_inlist_costing='ALL'

_optimizer_cbqt_no_size_restriction=TRUE

_optimizer_coalesce_subqueries=TRUE

_optimizer_complex_pred_selectivity=TRUE

_optimizer_compute_index_stats=TRUE

_optimizer_connect_by_combine_sw=TRUE

_optimizer_connect_by_cost_based=TRUE

_optimizer_connect_by_elim_dups=TRUE

_optimizer_correct_sq_selectivity=TRUE

_optimizer_cost_based_transformation='LINEAR'

_optimizer_cost_hjsmj_multimatch=TRUE

_optimizer_cost_model='CHOOSE'

_optimizer_dim_subq_join_sel=TRUE

_optimizer_distinct_agg_transform=TRUE

_optimizer_distinct_elimination=TRUE

_optimizer_distinct_placement=TRUE

_optimizer_eliminate_filtering_join=TRUE

_optimizer_enable_density_improvements=TRUE

_optimizer_enable_extended_stats=TRUE

_optimizer_enhanced_filter_push=TRUE

_optimizer_extend_jppd_view_types=TRUE

_optimizer_extended_cursor_sharing='UDO'

_optimizer_extended_cursor_sharing_rel='SIMPLE'

_optimizer_extended_stats_usage_control=224

_optimizer_fast_access_pred_analysis=TRUE

_optimizer_fast_pred_transitivity=TRUE

_optimizer_filter_pred_pullup=TRUE

_optimizer_fkr_index_cost_bias=10

_optimizer_group_by_placement=TRUE

_optimizer_improve_selectivity=TRUE

_optimizer_join_elimination_enabled=TRUE

_optimizer_join_factorization=TRUE

_optimizer_join_order_control=3

_optimizer_join_sel_sanity_check=TRUE

_optimizer_max_permutations=2000

_optimizer_mode_force=TRUE

_optimizer_multi_level_push_pred=TRUE

_optimizer_native_full_outer_join='FORCE'

_optimizer_new_join_card_computation=TRUE

_optimizer_null_aware_antijoin=TRUE

_optimizer_or_expansion='DEPTH'

_optimizer_order_by_elimination_enabled=TRUE

_optimizer_outer_to_anti_enabled=TRUE

_optimizer_push_down_distinct=0

_optimizer_push_pred_cost_based=TRUE

_optimizer_rownum_bind_default=10

_optimizer_rownum_pred_based_fkr=TRUE

_optimizer_skip_scan_enabled=TRUE

_optimizer_sortmerge_join_inequality=TRUE

_optimizer_squ_bottomup=TRUE

_optimizer_star_tran_in_with_clause=TRUE

_optimizer_system_stats_usage=TRUE

_optimizer_table_expansion=TRUE

_optimizer_transitivity_retain=TRUE

_optimizer_try_st_before_jppd=TRUE

_optimizer_undo_cost_change='11.2.0.1'

_optimizer_unnest_corr_set_subq=TRUE

_optimizer_unnest_disjunctive_subq=TRUE

_optimizer_use_cbqt_star_transformation=TRUE

_optimizer_use_feedback=TRUE

_or_expand_nvl_predicate=TRUE

_ordered_nested_loop=TRUE

_parallel_broadcast_enabled=TRUE

_partition_view_enabled=TRUE

_pivot_implementation_method='CHOOSE'

_pre_rewrite_push_pred=TRUE

_pred_move_around=TRUE

_push_join_predicate=TRUE

_push_join_union_view=TRUE

_push_join_union_view2=TRUE

_px_minus_intersect=TRUE

_px_pwg_enabled=TRUE

_px_ual_serial_input=TRUE

_query_rewrite_setopgrw_enable=TRUE

_remove_aggr_subquery=TRUE

_replace_virtual_columns=TRUE

_right_outer_hash_enable=TRUE

_selfjoin_mv_duplicates=TRUE

_sql_model_unfold_forloops='RUN_TIME'

_sqltune_category_parsed='DEFAULT' # parsed sqltune_category

_subquery_pruning_enabled=TRUE

_subquery_pruning_mv_enabled=FALSE

_system_trig_enabled=FALSE

_table_scan_cost_plus_one=TRUE

_undo_autotune=FALSE

_union_rewrite_for_gs='YES_GSET_MVS'

_unnest_subquery=TRUE

_use_column_stats_for_function=TRUE

aq_tm_processes=0

audit_file_dest='D:\ORACLE\ADMIN\INDIA\ADUMP'

audit_trail='DB'

compatible='11.2.0.0.0'

control_files='D:\ORACLE\ORADATA\INDIA\CONTROL01.CTL'

control_files='D:\ORACLE\FLASH_RECOVERY_AREA\INDIA\CONTROL02.CTL'

core_dump_dest='d:\oracle\diag\rdbms\india\india\cdump'

db_block_size=8192

db_domain=''

db_name='india'

db_recovery_file_dest='D:\oracle\flash_recovery_area'

db_recovery_file_dest_size=4977M

diagnostic_dest='D:\ORACLE'

dispatchers='(PROTOCOL=TCP) (SERVICE=indiaXDB)'

enable_ddl_logging=FALSE

log_buffer=6029312 # log buffer update

memory_target=328M

open_cursors=300

optimizer_dynamic_sampling=2

optimizer_mode='ALL_ROWS'

plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora

processes=150

query_rewrite_enabled='TRUE'

recyclebin='OFF'

remote_login_passwordfile='EXCLUSIVE'

resource_manager_plan=''

result_cache_max_size=864K

skip_unusable_indexes=TRUE

undo_retention=900

undo_tablespace='UNDOTBS1' 

Enjoy   :-) 

Read-Only Tables in Oracle 11g

Sometime it is necessary to make the particular table read only . Prior to 11g ,a read only table was achieved by using the triggers,constraints and other method to prevent the data from being changed. In many of those cases only INSERT, UPDATE, and DELETE operations were prevented while many DDL operations were not. In oracle 11g ,Tables can be marked as read only, hence preventing the DML operation against. As performance point of view , read-only table performance is quite good because Oracle does not have the additional overhead of maintaining internal consistency, there may be a small, but measurable reduction in resource consumption .                                                                                                                                

When a table is in read-only mode, operations that attempt to modify table data are disallowed. The following operations are not permitted on a read-only table:

• All DML operations on the table or any of its partitions.
• TRUNCATE TABLE
• SELECT FOR UPDATE
• ALTER TABLE ADD/MODIFY/RENAME/DROP COLUMN
• ALTER TABLE SET COLUMN UNUSED
• ALTER TABLE DROP/TRUNCATE/EXCHANGE (SUB)PARTITION
• ALTER TABLE UPGRADE INCLUDING DATA or ALTER TYPE CASCADE INCLUDING TABLE DATA for a type with read-only table dependents
• FLASHBACK TABLE

The following operations are permitted on a read-only table :

• SELECT
• CREATE/ALTER/DROP INDEX
• ALTER TABLE ADD/MODIFY/DROP/ENABLE/DISABLE CONSTRAINT
• ALTER TABLE for physical property changes
• ALTER TABLE DROP UNUSED COLUMNS
• ALTER TABLE ADD/COALESCE/MERGE/MODIFY/MOVE/RENAME
• ALTER TABLE MOVE
• ALTER TABLE ENABLE ROW MOVEMENT and ALTER TABLE SHRINK
• RENAME TABLE and ALTER TABLE RENAME TO
• DROP TABLE
• ALTER TABLE DEALLOCATE UNUSED
• ALTER TABLE ADD/DROP SUPPLEMENTAL LOG

Here is the Demo of the read only table.

SQL> create table test (id number ,name varchar2(12));

Table created.

SQL> insert into test values (1,'joy');

1 row created.

SQL> insert into test values (2,'hope');

1 row created.

SQL> insert into test values (3,'peace');

1 row created.

SQL> insert into test values (4,'happy');

1 row created.

SQL> commit ;

 Commit complete.

SQL> select * from test ;

          ID      NAME

----------      ------------

         1      joy

         2      hope

         3      peace

         4      happy

SQL> select table_name,status,read_only from user_tables where table_name='TEST';

TABLE_NAME                     STATUS          REA

------------------                       -----------         -------

TEST                                     VALID             NO

Now placing the table "test" in read only mode .

SQL> alter table test read only;

Table altered.

SQL> insert into test values (5,'sunny');

insert into test values (5,'sunny')

            *

ERROR at line 1:

ORA-12081: update operation not allowed on table "HR"."TEST"

SQL> delete from test;

delete from test

            *

ERROR at line 1:

ORA-12081: update operation not allowed on table "HR"."TEST"

SQL> truncate table test;

truncate table test

               *

ERROR at line 1:

ORA-12081: update operation not allowed on table "HR"."TEST"

Now bringing the table "test"  in read write mode .

SQL> alter table test read write;

Table altered.

SQL> insert into test values (5,'sunny');

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from test;

        ID NAME

---------- ------------

         1 joy

         2 hope

         3 peace

         4 happy

         5 sunny

Enjoy  :-)

Automatic Diagnostic Repository (ADR) in Oracle 11g

A special repository, named ADR (Automatic Diagnostic Repository) is automatically maintained by Oracle 11g to hold diagnostic information about critical error events. This repository is maintained in memory which enables database components to capture diagnostic data at its first failure for critical errors.

In Oracle 11g, the init.ora parameters like user_dump_dest and background_dump_dest are deprecated. They have been replaced by the single parameter DIAGNOSTIC_DEST which identifies the location of the ADR . ADR is file based repository for diagnostic data like trace file,process dump,data structure dump etc.

The default location of  DIAGNOSTIC_DEST  is  $ORACLE_HOME/log, and if ORACLE_BASE is set in environment  then DIAGNOSTIC_DEST is set to $ORACLE_BASE.  The ADR can be managed via the 11g Enterprise Manager GUI  (Database Control and not Grid Control) or via the ADR command line interpreter adrci .

11g new initialize parameter DIAGNOSTIC_DEST decide location of ADR root.

  

Structure of ADR Directory is designed in such a way that uses consistent diagnostic data formats across products and instances, and a integrated set of tools enable customers and Oracle Support to correlate and analyze diagnostic data across multiple instances .

In 11g alert file is saved in 2 location, one is in alert directory ( in XML format) and old style alert file in trace directory . Within ADR base, there can be many ADR homes, where each ADR home is the root directory for all diagnostic data for a particular instance. The location of an ADR home for a database is shown in the above pictures . Both the files can be viewed with  EM and ADRCI Utility.

SQL> show parameter diag

NAME               TYPE            VALUE

-------------     -------       -----------------

diagnostic_dest   string           D:\ORACLE

Below table shows us the new location of Diagnostic trace files

   Data                                Old location                        ADR location

-------------------         ------------------------        ---------------------

Core Dump                       CORE_DUMP_DEST                 $ADR_HOME/cdump

Alert log data                    BACKGROUND_DUMP_DEST   $ADR_HOME/trace

Background process trace   BACKGROUND_DUMP_DEST       $ADR_HOME/trace

User process trace             USER_DUMP_DEST                 $ADR_HOME/trace

We can use V$DIAG_INFOview to list some important ADR locations such as ADR Base, ADR Home, Diagnostic Trace, Diagnostic Alert, Default Trace file, etc.

SQL> select * from v$diag_info;
INST_ID          NAME                VALUE

----------    -----------          ---------------------------

1                   Diag Enabled        TRUE

1                  ADR Base             d:\oracle

1                  ADR Home            d:\oracle\diag\rdbms\noida\noida

1                  Diag Trace           d:\oracle\diag\rdbms\noida\noida\trace

1                  Diag Alert             d:\oracle\diag\rdbms\noida\noida\alert

1                  Diag Incident        d:\oracle\diag\rdbms\noida\noida\incident

1                  Diag Cdump          d:\oracle\diag\rdbms\noida\noida\cdump

1                  Health Monitor      d:\oracle\diag\rdbms\noida\noida\hm

1                  Active Problem Count       0

1                  Active Incident Count      0

10 rows selected.

ADRCI ( Automatic Diagnostic Repository Command  Interpreter) :

The ADR Command Interpreter (ADRCI) is a command-line tool that we use to manage Oracle Database diagnostic data. ADRCI is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database Release 11g. ADRCI enables:

• Viewing diagnostic data within the Automatic Diagnostic Repository (ADR).
• Viewing Health Monitor reports.
• Packaging of incident and problem information into a zip file for transmission to Oracle Support.

Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more .

ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.

To use ADRCI in interactive mode :

Enter the following command at the operating system command prompt:

C:\>adrci

ADRCI: Release 11.1.0.6.0 - Beta on Wed May 18 12:31:40 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ADR base = "d:\oracle"

To get list of adrci command type help command as below : 

adrci> help

 HELP [topic]

   Available Topics:

        CREATE REPORT

        ECHO

        EXIT

        HELP

        HOST

        IPS

        PURGE

        RUN

        SET BASE

        SET BROWSER

        SET CONTROL

        SET ECHO

        SET EDITOR

        SET HOMES | HOME | HOMEPATH

        SET TERMOUT

        SHOW ALERT

        SHOW BASE

        SHOW CONTROL

        SHOW HM_RUN

        SHOW HOMES | HOME | HOMEPATH

        SHOW INCDIR

        SHOW INCIDENT

        SHOW PROBLEM

        SHOW REPORT

        SHOW TRACEFILE

        SPOOL

 There are other commands intended to be used directly by Oracle, type  "HELP EXTENDED" to see the list

Viewing the Alert Log :  

The alert log is written as both an XML-formatted file and as a text file. we can view either format of the file with any text editor, or we can run an ADRCI command to view the XML-formatted alert log with the XML tags stripped. By default, ADRCI displays the alert log in your default editor

The following are variations on the SHOW ALERT command:

adrci > SHOW ALERT -TAIL

This displays the last portion of the alert log (the last 10 entries) in your terminal session.

adrci> SHOW ALERT -TAIL 50

This displays the last 50 entries in the alert log in your terminal session.

adrci> SHOW ALERT -TAIL -F

This displays the last 10 entries in the alert log, and then waits for more messages to arrive in the alert log. As each message arrives, it is appended to the display. This command enables you to perform "live monitoring" of the alert log. Press CTRL-C to stop waiting and return to the ADRCI prompt.Here are few Example :

adrci> show alert

Choose the alert log from the following homes to view:

1: diag\clients\user_neerajs\host_444208803_11

2: diag\clients\user_system\host_444208803_11

3: diag\clients\user_unknown\host_411310321_11

4: diag\rdbms\delhi\delhi

5: diag\rdbms\noida\noida

6: diag\tnslsnr\ramtech-199\listener

Q: to quit

Please select option:  4

Output the results to file: c:\docume~1\neeraj~1.ram\locals~1\temp\alert_932_4048_delhi_1.ado

'vi' is not recognized as an internal or external command,

operable program or batch file.

Please select option: q

Since we are on window platform so we don't have vi editor.So we have set editor for window say notepad.

adrci> set editor notepad

adrci> SHOW ALERT 

Choose the alert log from the following homes to view:

1: diag\clients\user_neerajs\host_444208803_11

2: diag\clients\user_system\host_444208803_11

3: diag\clients\user_unknown\host_411310321_11

4: diag\rdbms\delhi\delhi

5: diag\rdbms\noida\noida

6: diag\tnslsnr\ramtech-199\listener

Q: to quit

Please select option: 4 

Output the results to file: c:\docume~1\neeraj~1.ram\locals~1\temp\alert_916_956_noida_7.ado

Here it will open the alert log file and check the file as per our need . 

If we want to filter the alert log file then we can filter as below :

adrci> show alert  -P "message_text  LIKE '%ORA-600%'"

This displays only alert log messages that contain the string 'ORA-600'.

Choose the alert log from the following homes to view:

1: diag\clients\user_neerajs\host_444208803_11

2: diag\clients\user_system\host_444208803_11

3: diag\clients\user_unknown\host_411310321_11

4: diag\rdbms\delhi\delhi

5: diag\rdbms\noida\noida

6: diag\tnslsnr\ramtech-199\listener

Q: to quit

Please select option: 5 

Here, there is no ora-600 error in alert log file so it is blank 

Finding Trace Files  : 

ADRCI enables us to view the names of trace files that are currently in the automatic diagnostic repository (ADR). We can view the names of all trace files in the ADR, or we can apply filters to view a subset of names. For example, ADRCI has commands that enable us to:

• Obtain a list of trace files whose file name matches a search string.
• Obtain a list of trace files in a particular directory.
• Obtain a list of trace files that pertain to a particular incident.

The following statement lists the name of every trace file that has the string 'mmon' in its file name. The percent sign (%) is used as a wildcard character, and the search string is case sensitive.

adrci> SHOW TRACEFILE  %pmon%

This statement lists the name of every trace file that is located in the  directory and that has the string 'mmon' in its file name:

adrci> SHOW TRACEFILE -RT

This statement lists the names of all trace files related to incident number 1681:

Viewing Incidents : 

The ADRCI SHOW INCIDENT command displays information about open incidents. For each incident, the incident ID, problem key, and incident creation time are shown. If the ADRCI homepath is set so that there are multiple current ADR homes, the report includes incidents from all of them.

adrci> SHOW INCIDENT

ADR Home = d:\oracle\diag\rdbms\noida\noida:

*******************************************************************

0 rows fetched

Purging Alert Log Content : 

The adrci command ‘purge’ can be used to purge entries from the alert log. Note that this purge will only apply to the XML based alert log and not the text file based alert log which still has to be maintained using OS commands.  The purge command takes the input in minutes and specifies the number of minutes for which records should be retained.

So to purge all alert log entries older than 7 days the following command will be used:

adrci > purge -age 10080 -type ALERT

ADR Retention can be controlled with ADRCI :

There is retention policy for ADR that allow to specify how long to keep the data ADR incidents are controlled by two different policies:

The incident metadata retention policy ( default is 1 year )

The incident files and dumps retention policy ( Default is one month)

We can change retention policy using “adrci” MMON  purge data automatically on expired ADR data.

adrci> show control

The above command will show the  shortp_policy and longp_policy and this policy can the changed as below:

adrci> set control (SHORTP_POLICY = 360 )

adrci> set control (LONGP_POLICY = 4380 )

For more detail information on ADR visit below url :

http://download.oracle.com/docs/cd/E11882_01/server.112/e10701/adrci.htm

Enjoy   :-) 

Again the hell ORA-00600 Occurs...

This is third time when i have face ORA-00600 . As i have mention last two scenario in my previous post . My production database was working fine .I have created it few days ago .There are about 100 users connected to database . I have scheduled the backup through OEM . On very next day while monitering I find that the backup scheduled through OEM get failed . On diagnosis , i find that the backup failed due to RMAN fatal error . Then i check my alert file and find the ORA-600 .  Even having the ORA-00600 error the production database is going fine.

Error on rman prompt is :

RMAN> run {
                               backup database plus archivelog ;
                              delete noprompt obsolete;
                     }
Starting backup at 11-MAY-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-00571: =======================================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =======================================================
RMAN-00601 : fatal error in recovery manager
RMAN-03004 : fatal error during execution of command

Since i don't have any physical or logical backup of database,so i decided to take logical backup and again got the following error .

C:\>expdp system/xxxx@rosen schemas=CCI_HOAL directory=dpump dumpfile=hoal_11052011.dmp logfile=exp_hoal_11052011log.log

Export: Release 11.2.0.1.0 - Production on Fri May 11 11:27:34 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_07":  system/********@rosen schemas=CCI_HOAL directory=dpump dumpfile=hoal_11052011.dmp logfile=exp_hoal_11052011log.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4.48 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39097: Data Pump job encountered unexpected error -600
ORA-39065: unexpected master process exception in MAIN
ORA-00600: internal error code, arguments: [ksuloget2], [0xFEBDD104], [0xFEBCEE54], [496], [0xFEBDD128], [], [], [], [], [], [], []

and it get's hang here.So i kill the job

Export> kill_job
UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4583
ORA-06512: at line 1

So, we have decided to do some workround to get rid of these error. After some troubleshooting and diagnosis we have decided to reduce the size of the sga target . This error occurs when we set the size of sga target more than 1GB. Hence to solve this, I have decided to decrease the size of sga target. Here, in this scenario's i have not exactly solved the issue because it strongly recommended to get support from Oracle whenever we face ORA-00600 .Hope this may help you if the above error occurs.

To know more about ORA-00600. click here .

Enjoy     J J J