Oraclue
Oracle internals, debugging and undocumented features
Category Archives: Troubleshooting
GoldenGate error OGG-01755 SQL error: OCI Error 26,723 = ORA-26723
November 5, 2012
Posted by on 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.)
October 26, 2011
Posted by on 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
October 14, 2011
Posted by on 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
February 16, 2011
Posted by on 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
November 22, 2010
Posted by on 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.