Oracle Notes

Sharing Oracle technology knowledge and experiences.

Archive for the ‘Oracle Troubleshooting’ Category

Giving Oracle a Checkup

Posted by sdefilip on January 27, 2011


The Oracle Health Check Monitor (HM) facility is new with Oracle 11.1 database.  It can run diagnostic checks that detect:

>   file corruptions – reports failures if these files are inaccessible, corrupt or inconsistent. If the database is in mount or open mode, this check examines the log files and data files listed in the control file. If the database is in NOMOUNT mode, only the control file is checked.

>   physical and logical block corruptions – detects disk image block corruptions such as checksum failures, head/tail mismatch, and logical inconsistencies within the block. Most corruptions can be repaired using Block Media Recovery. Corrupted block information is also captured in the V$DATABASE_BLOCK_CORRUPTION view. This check does not detect inter-block or inter-segment corruption.

> undo and redo corruptions –

For redo, HM scans the contents of the redo log for accessibility and corruption, as well as the archive logs, if available. The Redo Integrity Check reports failures such as archive log or redo corruption.

For undo, HM finds logical undo corruptions. After locating an undo corruption, this check uses PMON and SMON to try to recover the corrupted transaction. If this recovery fails, then Health Monitor stores information about the corruption in V$CORRUPT_XID_LIST. Most undo corruptions can be resolved by forcing a commit.

>   transaction integrity check – identical to the Undo Segment Integrity Check except that it checks only one

specific transaction

>  data dictionary corruptions – examines the integrity of core dictionary objects, such as tab$ and col$. It  performs the following operations:

    • Verifies the contents of dictionary entries for each dictionary object.
    • Performs a cross-row level check, which verifies that logical constraints on rows in the dictionary are enforced.
    • Performs an object relationship check, which verifies that parent-child relationships between dictionary objects are enforced.

The Dictionary Integrity Check operates on the following dictionary objects:

tab$, clu$, fet$, uet$, seg$, undo$, ts$, file$, obj$, ind$, icol$, col$, user$, con$, cdef$, ccol$, bootstrap$, objauth$, ugroup$, tsq$, syn$, view$, typed_view$, superobj$, seq$, lob$, coltype$, subcoltype$, ntab$, refcon$, opqtype$, dependency$, access$, viewcon$, icoldep$, dual$, sysauth$, objpriv$, defrole$, and ecol$.

Each of the above checks can be used with parameters that provide specific subcategories of information.

Run HM Checker Manually

The Oracle Health Monitor (HM) can be run using the following syntax manually:


DBMS_HM.RUN_CHECK(‘type of check’, ‘name of HM check run’);




DBMS_HM.RUN_CHECK(‘Data Block Integrity Check’, ‘db_blk_integ_run’);

SQL> /

The types of checks that can be obtained in this manner (in place of ‘type of check’ above) are:

HM Test Check
DB Structure Integrity Check
CF Block Integrity Check
Data Block Integrity Check
Redo Integrity Check
Logical Block Check
Transaction Integrity Check
Undo Segment Integrity Check
No Mount CF Check
Mount CF Check
CF Member Check
All Datafiles Check
Single Datafile Check
Tablespace Check Check
Log Group Check
Log Group Member Check
Archived Log Check
Redo Revalidation Check
IO Revalidation Check
Block IO Revalidation Check
Txn Revalidation Check
Failure Simulation Check
Dictionary Integrity Check
ASM Mount Check
ASM Allocation Check
ASM Disk Visibility Check
ASM File Busy Check

Most health checks accept input parameters. You can view parameter names and descriptions with the V$HM_CHECK_PARAM view. Some parameters are mandatory while others are optional. If optional parameters are omitted, defaults are used. The following query displays parameter information for all health checks:

SELECT check_name, parameter_name, p.type,

p.default_value, p.description

FROM v$hm_check_param p, v$hm_check c

WHERE p.check_id = and c.internal_check = ‘N’


Input parameters are passed in the input_params argument as name/value pairs separated by semicolons (;). The following example illustrates how to pass the transaction ID as a parameter to the Transaction Integrity Check:



check_name   => ‘Transaction Integrity Check’,

run_name     => ‘my_run’,

input_params => ‘TXN_ID=7.33.2’);


Running HM Checker using Enterprise Manager:

1.      On the Database Home page, in the Related Links section, click Advisor Central.

2.      Click Checkers to view the Checkers subpage.

3.      In the Checkers section, click the checker you want to run.

4.      Enter values for input parameters or, for optional parameters, leave them blank to accept the defaults.

5.      Click Run, confirm your parameters, and click Run again.

Viewing HM Checker Reports

You can now view a report of a checker execution. The report contains findings, recommendations, and other information. You can view reports using Enterprise Manager, the ADRCI utility, or the DBMS_HM PL/SQL package. The following table indicates the report formats available with each viewing method.

Report Viewing Method Report Formats Available
Enterprise Manager HTML
DBMS_HM PL/SQL package HTML, XML, and text
ADRCI utility XML

To view run findings using Enterprise Manager

  1. Access the Database Home page.
  2. In the Related Links section, click Advisor Central.
  3. Click Checkers to view the Checkers subpage.
  4. Click the run name for the checker run that you want to view.

The Run Detail page appears, showing the findings for that checker run.

  1. Click Runs to display the Runs subpage.

Enterprise Manager displays more information about the checker run.

  1. Click View Report to view the report for the checker run.

The report is displayed in a new browser window.

Viewing Reports Using DBMS_HM

You can view Health Monitor checker reports with the DBMS_HM package function GET_RUN_REPORT. This function enables you to request HTML, XML, or text formatting. The default format is text, as shown in the following SQL*Plus example:

SET LONG 100000







Run Name                     : HM_RUN_1061

Run Id                       : 1061

Check Name                   : Data Block Integrity Check

Mode                         : REACTIVE

Status                       : COMPLETED

Start Time                   : 2011-01-12 22:11:02.032292 -07:00

End Time                     : 2011-01-12 22:11:20.835135 -07:00

Error Encountered            : 0

Source Incident Id           : 7418

Number of Incidents Created  : 0

Input Paramters for the Run



Run Findings And Recommendations


Finding Name  : Media Block Corruption

Finding ID    : 1065

Type          : FAILURE

Status        : OPEN

Priority      : HIGH

Message       : Block 64349 in datafile 1:

‘/ade/sfogel_emdb/oracle/dbs/t_db1.f’ is media corrupt

Message       : Object BMRTEST1 owned by SYS might be unavailable


Finding Name  : Media Block Corruption

Finding ID    : 1071

Type          : FAILURE

Status        : OPEN

Priority      : HIGH

Message       : Block 64351 in datafile 1:

‘/ade/sfogel_emdb/oracle/dbs/t_db1.f’ is media corrupt

Message       : Object BMRTEST2 owned by SYS might be unavailable

Viewing Reports Using the ADRCI Utility

You can create and view Health Monitor checker reports using the ADRCI utility.

To create and view a checker report using ADRCI

  1. Ensure that operating system environment variables (such as ORACLE_HOME) are set properly, and then enter the following command at the operating system command prompt:

2.      ADRCI

The utility starts and displays the following prompt:


Optionally, you can change the current ADR home. Use the SHOW HOMES command to list all ADR homes, and the SET HOMEPATH command to change the current ADR home. See Oracle Database Utilities for more information.

3. Enter the following command:

show hm_run

This command lists all the checker runs (stored in V$HM_RUN) registered in the ADR repository.

4. Locate the checker run for which you want to create a report and note the checker run name. The REPORT_FILE field contains a filename if a report already exists for this checker run. Otherwise, generate the report with the following command:

5.      create report hm_run run_name

6. To view the report, enter the following command:

show report hm_run run_name

**  For more details regarding HM views, parameters and more see

*** Reference Oracle® Database Administrator’s Guide 11g Release 1 (11.1)

Part Number B28310-04

Posted in Oracle 11g, Oracle 11g Database Administration, Oracle 11g New Features, Oracle Performance, Oracle Troubleshooting, Tips | Leave a Comment »

Oracle 11g GoldenGate and Compressed Tables

Posted by sdefilip on January 6, 2011

Oracle GoldenGate and Compressed Tables

OGG does not support compressed tables or partitions, neither does it handle it well with proper error messages until OGG v10.4. The abends may or may not produce any error message and sometimes produce wrong messages.

From V11., Oracle has enhanced the error handling part in BugDB 9425542, which gives meaningful error message on the compressed record before Extract abend. It will list out the table name, rowid, etc

Example :
ERROR OGG-01028 Record on table QATEST1.TAB1 with rowid AAM4EkAAEAACBguAAA from transaction 5.24.270123 (0x0005.018.00041f2b) is compressed. Compression is not supported.

However, due to bug 10063108, sometimes the error message on compressed tables are not entirely correct. This problem has been fixed in and above

A table created as compressed will cause all of the DML’s to go into compressed blocks on disk. If the user does an “alter table nocompress”, every DML that goes into the table AFTER that point in time will be uncompressed. The query for compression will return “nocompress” now, but the simple “alter” does not change the already existing compressed blocks on disk that were created before the “alter”. So to capture the records from a table which was compressed we need to do the following

SQL> alter table move nocompress;

This will touch every single block on disk and will uncompress everything thereby causing OGG to work properly and not abend.

If there is even a single partition in a partitioned table that is compressed, it will cause an abend. Partition compression can be verified by getting the full DDL for the table by running the DBMS_METADATA.GET_DDL package. For table partitions that are compressed, run the below query and get the partition names & tablespace names.

SQL> SELECT partition_name, subpartition_name, tablespace_name, high_value FROM user_tab_subpartitions WHERE table_name = ‘table_name’;

Alter statement for partition to move to nocompress:


Eensure that you have enough disk space within your tablespaces before running the ALTER statement.

Support of compressed tables will be in future releases of OGG, however, in current V10.4 and V11.1.1.x, the only option, if a “move nocompress” is not possible, is to comment the compressed table or exclude them from the Extract.

Listing some scenarios in which we have seen similar errors for compression  :
1) The extract abends with the following error

GGS ERROR ZZ-0QY Failed to validate table <SCHEMA>.<TABLE NAME>. The table is compressed and extract will not be able to extract data from Oracle logs.

This bug happens when DDL is enabled, the checking was not done correctly when table resides on Bigfile tablespace.

Issue is fixed in V10.4.0.93 and V11.

Reference bugdb – 10063075

2) When running with OGG version earlier than V11., Extract might abend with below error message, without telling the name of the table or the Objectid of the compressed table

Source Context :

SourceModule : []
SourceID : [/mnt/ecloud/workspace/Build_OpenSys_r11.
SourceFunction : [get_subrec(int32_t, mempool_t *, unsigned char *, u
nsigned short, subrec_info_t *, redo_thread_t *, BOOL, log_context_t *)]
SourceLine : [5434]

2010-11-10 11:50:26 ERROR OGG-01028 compressed tables are not supported.

This is because there is a temp table created as compressed, then followed directly by a direct load operation.

Try to exclude the table DBMS_TABCOMP_TEMP_UNCMP and DBMS_TABCOMP_TEMP_CMP, which was created by Compression advisory.

From OGG v11. onwards when the extract abends because of direct load inserts on tables with compressed partitions, the extract gives the table name in the error.

Reference bugdb – 10279456

However, if the table has been dropped immediately after creation, the Extract will only give out the object ID of the “compressed” table.  To find out exactly what was the compressed table name, you could query GGS_DDL_HIST table if DDL replication is enabled.  A sample query




Posted in Golden Gate, Oracle 11g, Oracle 11g Database Administration, Oracle 11g New Features, Oracle Fusion Middleware, Oracle GoldenGate, Oracle Troubleshooting, Tips | 5 Comments »

Using Oracle GoldenGate’s Logdump Utility

Posted by sdefilip on August 5, 2010


A recent client had a problem which required the use of the OGG Logdump utility to verify whether a particular table row had been extracted and placed in the Replicat trail file for replication to the target.  In this post, I will outline the steps that were used to find the answer.

1.   Begin logdump session:

            $ logdump

          GoldenGate Log File Dump Utility

          Version Build 001

          Copyright (C) GoldenGate Software, Inc. 1995-2009.

          The GoldenGate software may include other code written by third parties;

          details regarding such third party code, including applicable copyright,

          legal and licensing notices are available at


2.    Open Replicat Trail File


Logdump 1>open ./dirdat/ad000053

Current LogTrail is /shared/oradb/GoldenGate/pnet01d/dirdat/ad000053

Logdump 2 >ghdr on       <–shows record header info about transaction

Logdump 3 >detail data     <— adds  hex and ASCII data values to the

                                                        column information                       

Logdump 4 >next            <—  moves to the first record; advances by one



2010/08/04 21:22:18.134.541 FileHeader           Len   928 RBA 0

Name: *FileHeader*

 3000 01ad 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0…0…GG..TL..1…

 0002 3200 0004 ffff fffd 3300 0008 02f1 b33c d650        | ..2…….3……<.P

 840d 3400 0031 002f 7572 693a 7473 7332 6130 3137 | ..4..1./uri:tss2a017

 6e33 3a3a 7368 6172 6564 3a6f 7261 6462 3a47 6f6c  |::shared:oradb:Gol

 6465 6e47 6174 653a 706e 6574 3031 6436 0000 1300 |denGate:anondb02

 112e 2f64 6972 6461 742f 6164 3030 3030 3533 3700 |…/dirdat/ad0000537

 0001 0138 0000 0400 0000 3539 0000 0800 0000 001d | …8……59……..

(above is header data in hex and char)

2010/08/04 22:36:23.000.000 Insert               Len   456 RBA 6608  <–this is

                                                                                                                                an insert

                                                                                                                                 at this rba

Name: FIN_AUDIT.FIN_GL_ATTR      <— source object


Logdump 5>next      <— shows next record which contains continuing header

                                            info; “next’ or ‘n’ also  scans through records in

                                            sequential order


Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)

UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)

RecLength  :     0  (x0000)   IO Time    : 2010/08/04 21:22:17.611.797

IOType     :   151  (x97)     OrigNode   :     0  (x00)

TransInd   :     .  (x03)     FormatType :     R  (x52)

SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)

AuditRBA   :          0       AuditPos   : 0

Continued  :     N  (x00)     RecCount   :     0  (x00)


2010/08/04 21:22:17.611.797 RestartOK            Len     0 RBA 936


After  Image:                                             Partition 0   G  s


3.    Search for Timestamp in Trail File


Logdump 6 >sfts 2010-08-04       <–searches for the timestamp ‘2010-08-04’

Scan for timestamp >= 2010/08/04 04:00:00.000.000 GMT    <— timestamp


Column    41 (x0029), Len    31 (x001f)       <–column info with data

 0000 3230 3130 2d30 382d 3034 3a32 323a 3336 3a30 | ..2010-08-04:22:36:0

 372e 3839 3333 3139 3030 30                       | 7.893319000RecLength :9 (x0000)   IO Time    : 2010/08/04 21:22:17.611.797

 Since we were looking for any records that were inserted on 2010/08/04, we have found a valid entry in the Replicat trail file that verifies that the record has been placed in the file on the target side by the OGG Collector component.

 A few other of my favorite, very useful  Logdump commands are:

 >filter include filename FIN_AUDIT.FIN_GL_ATTR   

     Which will filter out all records that do not contain the table name

     specified and narrow down the set of records that subsequent commands

     will operate on until a “>filter clear” is issued

>pos FIRST

      Which will go to the first record in the file

 >pos <rba>

       Which will go to a specific rba in the trail file

 >log to <filename>.txt

        Which will log your Logdump session

There are several other useful commands for examining trail files or redo logs.  See the Oracle GoldenGate Troubleshooting Guide  for a complete list.



Posted in Data Integration, Data Warehouse, Golden Gate, Oracle GoldenGate, Oracle Troubleshooting, Replication, Tips | Tagged: , , , , | 1 Comment »

Useful Oracle GoldenGate Troubleshooting Commands

Posted by sdefilip on July 15, 2010

Troubleshooting Commands


If there are any errors, the extract would appear as STOPPED. In this case, you have to investigate the errors.

First, check the report file C:\OGG10G\dirrpt\EMP_EXT.rpt.

Next, see if there are any indicative messages in the log files ggserr.log and sqlnet.log, which are in the main C:\OGG10G directory.

The ggserr.log file contains event information such as:

2009-12-02  14:53:26  GGS INFO        301   Oracle GoldenGate

Manager for Oracle, mgr.prm:  Command received from GGSCI on host (START EXTRACT  EMP_EXT ).

2009-12-02  14:53:26  GGS INFO        302   Oracle GoldenGate Manager for Oracle, mgr.prm:


These events can also be seen in the following way:



2009-12-02 15:09:34  GGS INFO        302  Oracle GoldenGate Manager for Oracle,

mgr.prm:  EXTRACT EMP_EXT starting.

2009-12-02 15:13:26  GGS INFO        399  Oracle GoldenGate Command Interpreter

for Oracle:  GGSCI command (AnonDB): EDIT PARAM emp_ext.

The sqlnet.log file may show errors such as

TNS-12557: TNS:protocol adapter not loadable

This particular error indicates a database connection issue, so you need to explore at the listener level. Make sure the Path includes the Oracle Home bin subdirectory right at the start. There may be other TNS errors that may indicate other solutions.

EMP_EXT.rpt may show an error such as the following:

2009-12-08 13:01:27  GGS ERROR       182  OCI Error beginning

session (status = 28009-ORA-28009: connection as SYS should be as SYSDBA or SYSOPER).

2009-12-08 13:01:27  GGS ERROR       190  PROCESS ABENDING.

This indicates that the Extract process is trying to log on as sys without being the SYSDBA. Simply change the login in the parameter file for the Extract to “system”.

I should note here that specifying SYSDBA at the end of the login line in the Extract parameter file doesn’t work, although it does work with the DBLOGIN command in GGSCI that you will see next.

The ggserr.log file may show similar errors:

2009-12-03 00:43:16  GGS INFO        399  Oracle GoldenGate

Command Interpreter for Oracle:  GGSCI command (AnonDB): start manager.

2009-12-03 00:43:25  GGS ERROR       182  Oracle GoldenGate

Manager for Oracle, mgr.prm:  OCI Error during OCIServerAttach

(status = 12154-ORA-12154: TNS:could not resolve the connect identifier specified).

2009-12-03 00:43:25  GGS ERROR       190  Oracle GoldenGate

Manager for Oracle, mgr.prm:  PROCESS ABENDING.

This indicates an issue with the login credentials supplied in the Manager parameter file, mgr.prm. The Manager has not started as a result.

Another possible error can be seen in the Extract report file. For example, EMP_EXT.rpt can have this error:

2009-12-07 16:40:08  GGS ERROR       190  No minimum supplemental

logging is enabled. This may cause extract process to handle key

update incorrectly if key column is not in first row piece.

2009-12-07 16:40:08  GGS ERROR       190  PROCESS ABENDING.

The solution to this is obviously to enable supplemental logging at the database level. C


The first step is to use the GGSCI command DBLOGIN to connect to the database, so that other commands can be issued that will work on the database.

Note that DBLOGIN works fine as follows:

GGSCI ( AnonDB) 1>

DBLOGIN USERID system@localhost:1521/FIPRD3 PASSWORD fipassword1

Successfully logged into database.

Now you need to add a checkpoint table for the employees table that you are replicating:


Successfully created checkpoint table HR.EMPLOYEES_CHKPT.

Checkpoints that are stored in this table refer to the current read and write positions of the Replicat process. This is used to prevent the loss of data in case the process needs to be restarted, or if there is any fault in the server or a hiccup in the network that would otherwise result in data loss. The other advantage is that multiple Extract or Replicat processes can be read from the same set of trails by using checkpoints.

Checkpoints are optional in the sense that they are not required for Extract and Replicat processes that run in batch mode, because such processes can always be restarted. However, checkpoints are necessary in the case of continuously operating Extract and Replicat processes.. They are normally maintained as files in the dirchk subdirectory, but in the case of Replicat they can optionally be stored in the database in the checkpoint table.

If you specify the checkpoint table in the GLOBALS parameter file, the above command can use that specification. In this scenario, the command could simply be


No checkpoint table specified, using GLOBALS specification (hr.employees_chkpt).

Successfully created checkpoint table HR.EMPLOYEES_CHKPT.

You can now add the Replicat group as follows, specifying the exact same EXTTRAIL that was used by the Extract group set up in the first database. So the Replicat group feeds on or consumes the trail created by the Extract group:

GGSCI (AnonDB) 4> ADD REPLICAT emp_rep, EXTTRAIL C:\OGG10G\dirdat\et, CHECKPOINTTABLE hr.employees_chkpt,


Edit the parameter file for this Replicat group as follows:

GGSCI (AnonDB) 5> EDIT PARAM emp_rep

In the new file, enter the following:

REPLICAT emp_rep

USERID system@localhost:1521/FIPRD3, PASSWORD fipassword1


MAP hr.employees, TARGET hr.employees;

Because the tables have exactly the same DDL structure, you use the ASSUMETARGETDEFS parameter.

Now you can start the Replicat group:

GGSCI (AnonDB) 6> start REPLICAT emp_rep

Sending START request to MANAGER (‘GGSMGR’) …


Wait a few seconds to see the status; if you try immediately, the status may say “stopped.” When you see the status asrunning”, check the detailed information, and also issue an info all command to show all running processes:

GGSCI (AnonDB) 7> status REPLICAT emp_rep


GGSCI (AnonDB) 8> status REPLICAT emp_rep


GGSCI (AnonDB) 11> info REPLICAT emp_rep detail

REPLICAT   EMP_REP   Last Started 2009-12-08 13:35   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:01 ago)

Log Read Checkpoint  File C:\OGG10G\dirdat\et000001

2009-12-08 13:33:24.000000  RBA 985

Extract Source                          Begin             End

C:\OGG10G\dirdat\et000001               2009-12-08 13:33  2009-12-08 13:33

C:\OGG10G\dirdat\et000000               * Initialized *   2009-12-08 13:33

Current directory    C:\OGG10G

Report file          C:\OGG10G\dirrpt\EMP_REP.rpt

Parameter file       C:\OGG10G\dirprm\EMP_REP.prm

Checkpoint file      C:\OGG10G\dirchk\EMP_REP.cpr

Checkpoint table     HR.EMPLOYEES_CHKPT

Process file         C:\OGG10G\dirpcs\EMP_REP.pcr

Error log            C:\OGG10G\ggserr.log

GGSCI (AnonDB) 12> info all

Program     Status      Group       Lag           Time Since Chkpt


EXTRACT     RUNNING     EMP_EXT     00:00:00      00:00:03

REPLICAT    RUNNING     EMP_REP     00:00:00      00:00:06

Posted in Best Practices, CDC, Data Integration, Data Warehouse, Oracle GoldenGate, Oracle Troubleshooting, Tips | Tagged: , , , , | Leave a Comment »

ORA-06401 NETCMN: invalid driver designator

Posted by sdefilip on July 13, 2009

When recently issuing a command to connect to a remote database, the Oracle “ora-06401” error was encountered.

Oracle documentation states the following cause, along with what it suggests to be the sole solution:

ORA-06401 NETCMN: invalid driver designator

Cause: The login (connect) string contains an invalid driver designator.

Action: Correct the string and re-submit.

This usually means that the command syntax is incorrect. However, other causes exist:

1. improper authorization for the user issuing the command
2. invalid connection string for the target database version
3. ^M characters present in the file tnsnanes.ora file. These can be seen by viewing the ascii file after it has been converted to binary or by editing the file in a UNIX/Linux editor, such as vi. These characters are usually created by editing the file with a non-Linux/Unix editor and then transferring it by FTP to the server.
Use the following vi editor command to remove all of these characters from the file:


Posted in Oracle 11g, Oracle 11g Database Administration, Oracle Troubleshooting, Tips | 2 Comments »