Author Archives: alesk
Another day, another bug … Bug 4732503 – Self-deadlock on TT enqueue
Sequence:
0) Oracle 10.2.0.2 EE
1) user scott hit his tablespace quota on tablespace users; he remains connected to the instance
2) DBA tries to add some space to the schema: alter user scott quota 2000m on users;
3) DBA session with the alter user statement will hang until scott session is not closed (or killed)
There are several variations of this scenario: alter tablespace add datafile, allocation of undo segments in undo tablespace etc.
Fixed in 10.2.0.3 / 11g R1.
Regards,
Ales
ORA-12638: Credential retrieval failed
I’m hunting down more or less a sporadic error that sometimes our users get during initial attempt to connect to Oracle server.
Some facts:
– this error was never raised on NT4 domain; it was first noticed shortly after we migrated all our clients to Active Directory
– we don’t use OS authentication, all users are using classic Oracle accounts
– we do have SQLNET.AUTHENTICATION_SERVICES = (NTS) in the sqlnet.ora on all client machines
– so far, the only workaround is to remove SQLNET.AUTHENTICATION_SERVICES = (NTS) from sqlnet.ora on all worstations
– another observation is that we usually get several trace files generated in UDUMP, such as:
Dump file d:\oracle\admin\ora\udump\orcl_ora_3920.trc Tue Aug 21 08:18:17 2007 ORACLE V10.2.0.2.0 - Production vsnsta=0 vsnsql=14 vsnxtr=3 Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options Windows Server 2003 Version V5.2 Service Pack 1 CPU : 4 - type 586, 4 Physical Cores Process Affinity : 0x00000000 Memory (Avail/Total): Ph:2488M/8190M, Ph+PgF:4528M/10044M, VA:1411M/3071M Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 36 Windows thread id: 3920, image: ORACLE.EXE (SHAD) opiino: Attach failed! error=-1 ifvp=00000000
– Metalink Note:399727.1 “Opiino: Attach Failed! Error=-1 Ifvp=0″ Written Many Times Per Minute In Udump File And ORA-12500 In Listener.log” doesn’t apply to our situation, because there is only one database running on the server. Also, we do not observe ORA-12500 in the listener.log – this is weird.
Based on Event Log message at the client:
Warning - LsaSrv - SPNEGO (Negotiator) - Event 40961 The Security System could not establish a secured connection with the server ldap/server.domain.com/domain.com@domain.com. No authentication protocol was available.
…I would say that this is an OS issue, not an Oracle Net bug. Google search revealed that Event 40961 is usually a consequence of miss-configured DNS (missing reverse lookup entry for the server), but not in our case. If I’ll ever find the culprit for the error I’ll update this thread with a solution.
Oracle XE with character set EE8MSWIN1250
A short memo about the procedure to change LATIN1 to LATIN2 code page in Oracle XE (I can’t remember where I originally found this tip, probably somewhere on OTN!?):
1) Make sure you download and install Oracle XE (Western European) version of XE (single byte character set) and not Universal (Unicode)
2) change the database character set to EE8MSWIN1250:
cmd> sqlplus /nolog sql> connect / as sysdba sql> SHUTDOWN IMMEDIATE; sql> STARTUP MOUNT; sql> ALTER SYSTEM ENABLE RESTRICTED SESSION; sql> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; sql> ALTER SYSTEM SET AQ_TM_PROCESSES=0; sql> ALTER DATABASE OPEN; sql> ALTER DATABASE CHARACTER SET INTERNAL_USE EE8MSWIN1250; sql> SHUTDOWN; sql> STARTUP RESTRICT; sql> SHUTDOWN; sql> STARTUP;
That’s it. Now, you have Oracle XE with EE8MSWIN1250. This can be useful if you want to support old clients (for example Oracle Forms) that doesn’t recognize AL32UTF8 code page that is used in Universal distribution.
ArcServe – ca_auth
I have always a problem remembering the proper steps needed to authorize DBA’s to execute backup jobs with CABATCH utility in ArcServe.
For example, assume the following setup:
BACKUPSERVER ... is the server with ArcServe running
DBAWORKSTATION ... is workstation from which DBA can execute remote backup job with <em>cabatch</em> utility
DBAUSER ... is Windows OS domain account for DBA.
The DBAUSER while logged on DBAWORKSTATION should be allowed to open command line, run cabatch utility and send the backup job to remote BACKUPSERVER.
The procedure to authorize DBAUSER to execute cabatch:
1) Log on BACKUPSERVER as Administrator (assuming this account was used during ArcServe installment)
2) Add ArcServe <em>internal</em> user named dbauser (I prefer the same name as it's used on windows domain for DBA)
cmd> ca_auth -user add dbauser password
Remember, both username and password is internal, ArcServe stuff, and has nothing to do with OS account!
You can list existing ArcServe users with:
cmd> ca_auth -user getall
3) Make account the equivalent to caroot
cmd> ca_auth -equiv add dbauser dbaworkstation dbauser caroot carootpassword
Here is explantion of positional parameters used above:
cmd> ca_auth -equiv add
Usage :
ca_auth [-cahost host] -equiv add ntuser hostName BrightStorUser [caroot_username] [caroot_password]
ca_auth [-cahost host] -equiv getequiv [ntuser hostName]
ca_auth [-cahost host] -equiv delete ntuser hostName [caroot_username] [caroot_password]
ca_auth -equiv whoami
I hope this note will spare me some time in the future :)
Count dates with AWK
Someone on the public forum asked this question:
“Say I want to find out how many dates are there in 1000 lines of characters.Also date format is given (…in my case date format is of ‘DD-MON-YYYY’).”
When I read the question I thought, what a nice exercise for regex with awk. Here is part of the answer I gave:
#######################
# Countdates.awk script
#######################
BEGIN {i=0}
{
$0 = toupper($0)
i += gsub(/(0[1-9]|[1-2][0-9]|3[01])-(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)-((19[0-9][0-9])|(20[0-9][0-9]))/,"")
}
END {print "Number of dates (DD-MON-YYYY) in the file: ", i}
#######################
# End Countdates.awk
#######################
Sample file with three lines (dates.txt):
xxxxx01-Jan-200002-Feb-2003 xxxx13-APR-1990xxxxx xx32-OCT-2007---xxx00-dEC-1900 xxx 1-MAY-2003xxxxx xxx2004-AUG-07 xxxxx-08-Aug-2007- 12-Jun-1890
Now, if you run the script:
G:\>gawk -f countdates.awk dates.txt Number of dates (DD-MON-YYYY) in the file: 4</code>
It’ll catch three dates in the first line (01-Jan-2000, 02-Feb-2003,13-APR-1990), none in second line
and one in the third line (08-Aug-2007).
Short explanation of the script:
$0 = toupper($0) ... it's needed to catch Apr, APR, aPR
gsub() functions returns number of substitutions which is added to variable i
/ ...search pattern ... /
(0[1-9]|[1-2][0-9]|3[01]) ... trying to catch only valid days 01-31 (assuming leading zero)
(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC) .... month names abbreviation in uppercase to compare against input line ($0) which we uppercased at he beggining
((19[0-9][0-9])|(20[0-9][0-9])) .... assuming only years that starts with 19xx and 20xx are valid (that's why we didn't count 12-Jun-1890)
You can take this example and fine “tune” it for the data you actually have. This script lacks true date validation (e.g. it’ll count 29-FEB-2007 as a date despite the fact that it’s really wrong), for this, perl/python etc. are better suited (first catching the date candidates with the power of regex pattern search, then validating those strings as a true dates with some date/time module.).
dbaportal.eu 