GoldenGate error OGG-01755 SQL error: OCI Error 26,723 = ORA-26723

November 5, 2012 Leave a comment

I was trying to register extract  OGG v 11.2 on one of my dev servers

GGSCI (tpadldb10.gratiscard.com) 1> dblogin  userid srvegate password xxxxx

Successfully logged into database.

 GGSCI (tpadldb10.gratiscard.com) 2> register extract testext database

and run into error above :  OGG-01755  SQL error: OCI Error 26,723.

See Extract user privileges in the Oracle GoldenGate for Oracle Installation and Setup Guide.

So I open OGG installation document and granted every possible privilege .

Table 4–1 Database privileges by Oracle GoldenGate process

http://docs.oracle.com/cd/E35209_01/doc.1121/e35957.pdf

and try again  to register extract and got same error.

Finally I dediced to trace database sesssion . Register command abanded with ORA- 26723   (OCI Error 26,723 translates to ORA-26723 ).

Then quick look  for ORA-26723  

http://aprakash.wordpress.com/2012/10/26/ora-26723/

Grant the DV_GOLDENGATE_REDO_ACCESS role to any user who is responsible for using the Oracle GoldenGate TRANLOGOPTIONS DBLOGREADER method to access redo logs in an Oracle Database Vault environment

At that point I realize that I  Data Vault is installed on the server . Since I never used it ( DV_GOLDENGATE_REDO_ACCESS  is not there to grant ) I have just disabled it :

% cd $ORACLE_HOME/rdbms/lib
% ar -t libknlopt.a | grep -c kzvidv.o

Output of this command was 1  ( DV enabled ).So  I turned it off..

  % make -f ins_rdbms.mk dv_off

 make -f rdbms/lib/ins_rdbms.mk ioracle

 run

 register extract testext database

and OGG-02003  Extract TESTEXT  successfully registered with database.

Categories: Troubleshooting

Oracle GoldenGate 11.2

March 29, 2012 2 comments

OGG 11.2 offers  integrated capture mode. Extract integrates with database log mining server to receive change data in LCR format .
Oracle is utilizing Streams more and more ..

Oracle database must be Oracle 11.2.0.3 with the patch for bug 13560925.

Here is example how to change extract more to integrated and back to classic mode.

You can check current extract mode using:
info extract <ext> , it will show following:

for IE mode: Log Read Checkpoint Oracle Integrated Redo Logs

for classic: Log Read Checkpoint Oracle Redo Logs

[oracle@test gg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Feb  1 2012 00:55:59

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (test) 1> dblogin  userid srvegate, password test
Successfully logged into database.

GGSCI (test) 2> alter extract testext, upgrade integrated tranlog
ERROR: REGISTER EXTRACT testext DATABASE must be performed before upgrading to integrated capture..
GGSCI (test) 4> REGISTER EXTRACT testext DATABASE

2012-03-26 16:42:07  WARNING OGG-02064  Oracle compatibility version 11.2.0.0.0 has limited datatype support for integra                          ted capture. Version 11.2.0.3 required for full support.
2012-03-26 16:42:50  INFO    OGG-02003  Extract testext successfully registered with database at SCN 18279580332.
GGSCI (test) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     testext      00:27:39      00:00:01
EXTRACT     RUNNING     testpump     00:00:00      00:00:07
GGSCI (test) 3> ALTER EXTRACT testext,  UPGRADE INTEGRATED TRANLOG
ERROR: EXTRACT testext is running and cannot be altered (1,2,No such file or directory).
GGSCI (test) 4> stop testext

Sending STOP request to EXTRACT testext …
Request processed.

GGSCI (test) 6> dblogin userid srvegate, password test
Successfully logged into database.

GGSCI (test) 7>  ALTER EXTRACT testext,  UPGRADE INTEGRATED TRANLOG
Extract testext successfully upgraded to integrated capture.
GGSCI (test) 8> start testext

Sending START request to MANAGER …
EXTRACT testext starting

GGSCI (test) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     testext      00:20:00      00:00:01
EXTRACT     RUNNING     testpump     00:00:00      00:00:03

 
GGSCI (test) 1> dblogin  userid srvegate, password test
Successfully logged into database.

GGSCI (test) 2> alter extract testext, DOWNGRADE INTEGRATED TRANLOG

ERROR: Found checkpoint values from mulitiple threads. Need to use the THREADS option with 2 or greater..
GGSCI (test) 3> alter extract testext, DOWNGRADE INTEGRATED TRANLOG threads 2
Extract testext successfully downgraded from integrated capture.
GGSCI (test) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     testext      00:00:00      00:02:35
EXTRACT     RUNNING     testpump     00:00:00      00:00:04
GGSCI (test) 5> start testext

Sending START request to MANAGER …
EXTRACT testext starting
GGSCI (test) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     testext      00:00:03      00:00:00
EXTRACT     RUNNING     testpump     00:00:00      00:00:01

Categories: HA

Index Monitoring

March 1, 2012 2 comments
For those not familiar with it, index monitoring is Oracle's way to track whether an index is being used, letting you know if it is needed.

Two things to keep in mind:
1) It doesn't always mark an index as used even if it is used. If it isn't used in an execution plan but is used to enforce a foreign key or unique constraint, it won't get flagged as used.
2) The view used to look at index usage is schema specific. You may be monitoring indexes, but the indexes won't show up in v$object_usage unless you log in as the schema owner. It's better to go directly to the underlying query to view all monitored indexes (query below).

Since index monitoring is very low cost, it makes sense to turn it on for all candidate indexes. Indexes on FKs and unique indexes are doing work even if not used in execution plans, so they are not candidates to drop. Here's the query to get all non-unique, non-FK indexes (assumes no concatenated PK's - if you have that, the query gets more complicated):

SELECT 'ALTER INDEX '||ic.index_name||' MONITORING USAGE;'
  FROM all_ind_columns ic, all_indexes i
 WHERE i.uniqueness = 'NONUNIQUE' --don't monitor unique indexes
   AND i.table_owner = 'SCHEMA_OWNER_HERE'
   AND ic.index_owner = i.owner
   AND ic.index_name = i.index_name
   AND ic.position = 1
   AND NOT EXISTS (SELECT 'x' --Don't monitor indexes on FK's
                     FROM all_cons_columns cc, all_constraints c
                    WHERE ic.table_name = cc.table_name
                      AND ic.column_name = cc.column_name
                      AND c.constraint_name = cc.constraint_name
                      AND c.constraint_type IN ('R'));

Here's the query to look at monitored objects if you're not logged in as the schema owner:

select d.username, io.name, t.name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
       ou.start_monitoring,
       ou.end_monitoring 
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou,
     dba_users d
where io.owner# = d.user_id
  AND d.username = 'SCHEMA_OWNER_HERE'
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#;

And here's an example of index monitoring in action, including the Unique index usage not being flagged:

CREATE TABLE test_monitoring AS SELECT level id, dbms_random.value(1,1000) value FROM dual CONNECT BY LEVEL <= 5000;

Table created.

CREATE UNIQUE INDEX test_monitoring_idx ON test_monitoring(id);

Index created.

ALTER INDEX test_monitoring_idx MONITORING USAGE;

Index altered.

--Using index for PK enforcement - does not flag the index as used:
INSERT INTO test_monitoring VALUES (100,0);
INSERT INTO test_monitoring VALUES (100,0)
*
ERROR at line 1:
ORA-00001: unique constraint (BAYPAS.TEST_MONITORING_IDX) violated 

SELECT index_name, used FROM v$object_usage WHERE index_name = UPPER('test_monitoring_idx');

INDEX_NAME                     USE                                              
------------------------------ ---                                              
TEST_MONITORING_IDX            NO                                               

--But we run a select that will use the index
SELECT * FROM test_monitoring WHERE id = 100;

        ID      VALUE                                                           
---------- ----------                                                           
       100   255.5571                                                           

--And now the index shows up as used:
SELECT index_name, used FROM v$object_usage WHERE index_name = 'TEST_MONITORING_IDX';

INDEX_NAME                     USE                                              
------------------------------ ---                                              
TEST_MONITORING_IDX            YES      


Granville Bonyata
Categories: Performance

Querying multiple rows from dual

February 6, 2012 Leave a comment

By Granville Bonyata

Sometimes it’s useful to have a query that always returns a certain number of rows, such as a report that returns one row for every day of the month. Using DUAL and CONNECT BY allows you to return as many rows as needed. For instance, this query returns one row for every day of the month:

SELECT TO_DATE(level||’-‘||TO_CHAR(SYSDATE,’MON-YYYY’)) each_day from dual connect by level <= TO_CHAR(LAST_DAY(SYSDATE),’DD’) ORDER BY 1;

SQL> /

EACH_DAY                                                                        ———                                                                       01-JAN-12                                                                       02-JAN-12                                                                       03-JAN-12                                                                       04-JAN-12                                                                       05-JAN-12                                                                       06-JAN-12                                                                       07-JAN-12                                                                       08-JAN-12                                                                       09-JAN-12                                                                       10-JAN-12                                                                       11-JAN-12                                                                       12-JAN-12                                                                       13-JAN-12                                                                       14-JAN-12                                                                       15-JAN-12                                                                       16-JAN-12                                                                       17-JAN-12                                                                       18-JAN-12                                                                       19-JAN-12                                                                       20-JAN-12                                                                       21-JAN-12                                                                       22-JAN-12                                                                       23-JAN-12                                                                       24-JAN-12                                                                       25-JAN-12                                                                       26-JAN-12                                                                       27-JAN-12                                                                       28-JAN-12                                                                       29-JAN-12                                                                       30-JAN-12                                                                       31-JAN-12

31 rows selected.

Categories: New Features

Reducing cluster waits by Granville Bonyata

January 27, 2012 Leave a comment

If you’re seeing high cluster waits because you have multiple processes all inserting into the same set of tables, the cluster waits are resolved by pointing to a single node on the cluster. But just pointing to a single node defeats the rollover benefits of running on RAC. To that end, Oracle gives us Services to assist with workload management. It’s a TNS entry that <em>prefers</em> a specific node, but will use a secondary instance if the preferred is unavailable. So all processes that use a common set of resources can all be pointed to the same instance, eliminating the cluster waits.

In practice I have seen this improve performance by 500% on high volume inserts, so definitely a feature <a href=”http://docs.oracle.com/cd/B19306_01/rac.102/b14197/hafeats.htm#CHDGEBED” title=”worth checking out.” target=”_blank”></a>

Categories: Uncategorized

Dbms_xplan by Granville Bonyata

January 25, 2012 1 comment

I  am extremly busy these days and do not have any time to blog.That’s why my friend Granville Bonyate decided to write few small posts.Here is the first on in series.

 

Having worked with Oracle for many (twenty!) years, I sometimes find new features released several years ago that I didn’t catch at the time. Most recently, it would be that 10g added the display_cursor function to dbms_xplan. Most usefully, you can pass in the Sql-Id (from v$sql) and get the formatted execution plan for a SQL statement: Select * from table(dbms_xplan.display_cursor(‘d5dj7zctjwy1y’)); More information here:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xplan.htm

 

Very useful when you’re at a site that doesn’t have TOAD or OEM.

Granville Bonyata

 

Categories: Performance

Resolving Oracle specific errors ( ORA-01031, ORA-00942 etc.)

October 26, 2011 2 comments

I lost a count how many times I run into ORA-01031 error .Since I am the one executing the query I can see which SQL statement I am running and then focus on specific objects to find missing privilages.

It gets more difficult when end-user get this error.Thanks to Oracle events this is easy to resolve.

Normally I would just enable errrostack tracing on level 3 for specific error and get a user SQL statement.Once I get it I can quickly resolve permission issue .

To get errorstack for ORA-01031 I would run alter statement bellow:

alter system set events ‘1031 trace name errorstack level 3;

and then wait for error to happen .It will be recorder into database alert log file .From there I can find trace file located in dump destination.So here is my file:

*** 2011-10-25 10:13:28.025
*** SESSION ID:(84.2771) 2011-10-25 10:13:28.025
*** CLIENT ID:() 2011-10-25 10:13:28.025
*** SERVICE NAME:(test.world) 2011-10-25 10:13:28.025
*** MODULE NAME:() 2011-10-25 10:13:28.025
*** ACTION NAME:() 2011-10-25 10:13:28.025

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
—– Error Stack Dump —–
ORA-01031: insufficient privileges
—– SQL Statement (None) —–
Current SQL information unavailable – no cursor.

—– Call Stack Trace —–
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
——————– ——– ——————– —————————

However errorstack did not produce SQL statement what means I do not know which object is causing this error.

Solution is  to add more diagnostic events to generate additional information.

Dumping library cache with level 10 I will be able to capture it.So here is my statement:
alter system set events ‘1031 trace name errorstack level 3; name library_cache level 10′;

Notice that I am running two different actions using one command.

 again this my trace:
*** 2011-10-25 09:31:30.751
*** SESSION ID:(245.633) 2011-10-25 09:31:30.751
*** CLIENT ID:() 2011-10-25 09:31:30.751
*** SERVICE NAME:(test.world) 2011-10-25 09:31:30.751
*** MODULE NAME:() 2011-10-25 09:31:30.751
*** ACTION NAME:() 2011-10-25 09:31:30.751

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
—– Error Stack Dump —–
ORA-01031: insufficient privileges
—– SQL Statement (None) —–
Current SQL information unavailable – no cursor.

—– Call Stack Trace —–
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)

Part above comes from errorstack ..  However if you search your trace file for word “library” you will get first occurence for  library dump  and see objects in question ( TEST.TEST_QUEUE ) in my case.
proc=0x25419d9e8, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8476, pg=0

LibraryObjectLock:  Address=0x25a903f18 Handle=0x259a47d80 Mode=N CallPin=0x25a903d18 CanBeBrokenCount=2 Incarnation=1 ExecutionCount=0

User=0x257202818 Session=0x257202818 ReferenceCount=1 Flags=PNC/[0002] SavepointNum=d1
LibraryHandle:  Address=0x259a47d80 Hash=150a21c LockMode=N PinMode=S LoadLockMode=0 Status=VALD
ObjectName:  Name=TEST.TEST_QUEUE
FullHashValue=93a71dce5afcd4f5d2a394790150a21c Namespace=QUEUE(10) Type=QUEUE(24) Identifier=105660 OwnerIdn=116
To disable tracing simple run command

alter system set events ‘1031 trace name errorstack off; name library_cache off';

Of course you can use method for other oracle errors  like  ORA-00942 .Just change error number.

e.g.

alter system set events ‘942 trace name errorstack level 3; name library_cache level 10′;

Categories: Troubleshooting
Follow

Get every new post delivered to your Inbox.

Join 44 other followers