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

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.

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

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’;

Disabling Oracle Streams Propagation when nothing else works

It’s been a while since I  wrote any post .. I need vacation too 🙂

Here is very short one but useful tip for folks that are using Oracle Streams.

I know everyone is talking about GoldenGate but trust  me Oracle Streams are good solution if  you do not have budget for GoldenGate.Oracle is working very hard to implement best of Streams into GG.

Anyway this tip shows how to disable Oracle Streams propagation when nothing else is working.At least in my case..

I was trying to stop Oracle Streams propagation process but for some reason nothing did not work..  Finally I realized that Oracle Streams propagation process is related to Oracle jobs and by  quickly disabling (  remember there are other jobs that runs ) oracle scheduler I was able to stop Streams propagation process .

First disable scheduler:

SQL> exec dbms_scheduler.set_scheduler_attribute(‘SCHEDULER_DISABLED’,’TRUE’);
PL/SQL procedure successfully completed.

Stop propagation:

SQL> exec dbms_propagation_adm.stop_propagation(‘TEST_PROPAGATION’, true);

PL/SQL procedure successfully completed.

and enabling scheduler again.

SQL>  exec dbms_scheduler.set_scheduler_attribute(‘SCHEDULER_DISABLED’,’FALSE’);

OEM 11g install and ORA-12650

Two weeks ago I was trying to  install  OEM grid control version 11 .I did it many times already but this time my process  failed in the middle of install.

After digging numerous number of  install logs I have found message like one bellow:

HealthMonitor Jan 26, 2011 5:39:44 PM OMS Heartbeat Recorder:  error: Could not get id: java.sql.SQLException: Oracle Error ORA-12650
Critical error err=3 detected in module OMS Heartbeat Recorder:

Quick search turn out that    ORA-12650 No common encryption or data integrity algorithm  is error related to  Oracle Advanced Security Option.

Then I realized that have ASO enabled on datatabase which will be used as OEM respository.

My sqlnet.ora looks something like this: 

/* ASO Encryption */

SQLNET.CRYPTO_SEED = “-blablablablablabla”
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (RC4_256)
SQLNET.ENCRYPTION_TYPES_CLIENT = (RC4_256)

/* ASO Checksumm */

SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (MD5)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (MD5)

After  taking this part from sqlnet.ora file  my install finished successfully.

It sounds easy but it took me some time to dig all install logs and find the cause..

MOS notes – Troubleshooting ASM

I just summarized few notes that I have used  for troubleshooting ASM.

Placeholder for AMDU binaries and using with ASM 10g [ID 553639.1]

ASMIOSTAT Script to collect iostats for ASM disks [ID 437996.1]

Information to gather when diagnosing ASM space issues [ID 351117.1]

and of course you can always dump ASM block  using

SQL> alter system dump datafile ‘<+DiskGroupName>/<Filename>’ block X; command to see it inside.