For instance the ones running for more than 12hours:
select ‘alter system kill session ”’||s.sid||’,’||s.serial#||”’ immediate;’,
s.osuser,s.machine,s.module, CEIL(24*(SYSDATE-t.START_DATE))
from v$session s, v$transaction t
WHERE S.SADDR=T.SES_ADDR
and CEIL(24*(SYSDATE-t.START_DATE))>12;
My company wants me to change my password to a more complex one.
Few days ago I saw a nice cartoon about ‘password strength’: http://xkcd.com/936/
Today I checked this Microsoft Security website to see how strong a password is: https://www.microsoft.com/security/pc-security/password-checker.aspx
I tried with the password: aaaaaaaaaaaaaaaaaaaaaaaaaaaa (about 28 times letter ‘a’). The result was:
– testing and documenting the apply of Oracle PSU by:
. clone a master patched Oracle Home
. attach new home to inventory
. move existing DBs to use the new Oracle Home
. run post-PSU operations on each DB (catpsu.sql, etc)
Advantages:
– minimize downtime
– automatize patching
GoldenGate is a powerful tool bought by Oracle to replace the “Oracle Streams”. It allows heterogeneous replication of data and works just fine almost out-of-the-box.
My client uses it to make a cache copy of data a kind of active standby while running on Oracle 10.2.
Today I had to fight for a problem that comes from the application, which seldom writes to the source database dates in a invalid format, with an year 0 (Oracle should not accept, but the year got in, we are still investigating how).
This makes the GoldenGate replication process to abend, as the write on the target database fails with an “ORA-01841: (full) year must be between -4713 and +9999, and not be 0”.
For the workaround, using GoldenGate MAP function, I managed to transform the dates like the following:
Source: 0000-12-09:08:11:20
Target: 0001-12-09:08:11:20
MAP NESSOFT.NEXT_ORD, TARGET NESSOFT_NL.NEXT_ORD , &
COLMAP ( USEDEFAULTS,
EXPIRE = @IF ( @STRCMP(@STREXT(EXPIRE,1,4),"0000") = 0, @STRCAT("0001-",@STREXT(EXPIRE,6,18)),EXPIRE)
);
This allowed to me also to discover the discard file (.dsc) on the dirrtp folder of GoldenGate, where you can see the output of the MAP function, useful for the debug.
After working more than 5 years with either an US or English keyboard, I find very difficult to get used to a Swiss keyboard. This is not the case for writing e-mails, but mostly for working. Unfortunately by being a consultant now I’ve often to work on clients terminals and their installed keyboard, not giving even the opportunity to hang around with a US keyboard for my pleasure.
Why the US or English keyboard are better for computer scientists?
– you have easy access to the brackets (), [], {}, which are always in two different keys and not hidden away with AltGr;
– you have easy access to <> in two different keys, not needing to make sure the shift is pressed or not.
– Also comma ´ and ‘ are different keys;
– The slashes / are both in logical places
– The @ is accessible with a simple Shift key, like is the pipe |
And you can also easily use the US-International keyboard to write in many languages, including the portuguese where you have ã,à,é,ç…
I hope that soon will be easier to change keyboards on the OSes and also that a better universal standard (at least for roman alphabet based) appears.
The other day during an operation on one of the DNS servers, some of our RAC listeners went down and stop responding.
We found this happened when they brought down one of the DNS name servers, even thought one was up and running.
Ping, ssh and so on was working fine. But any operation with ‘lsnrctl’ or ‘srvctl xxx listener’ would hang.
After investigation together with the system administrator it was found that it was related to the IPv6 lookups which are activated by default on AIX and are done when the first IPv4 lookup fails.
To solve the problem was just necessary to disable the IPv6 lookups by changing the /etc/netsvc.conf file to:
hosts=local4,bind4
This is explained on this IBM note.
The Metalink analyst kept insisting that the “ulimit -d” value was too low.
Running an RDA for Oracle on AIX today I saw that the ulimit values were different if I run on command line or through perl:
$perl -e 'system("ulimit -a");'
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 2097152
stack(kbytes) unlimited
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) 2000
threads(per process) unlimited
processes(per user) unlimited
$ulimit -a
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 2000
pipe size (512 bytes, -p) 64
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 2048
virtual memory (kbytes, -v) unlimited
In Oracle 11g there is this new procedure which runs Orion (likely) from inside the database. You can get the IO per second, latency and MB per second of your disk sub-system.
Asynch IO needs to be set to true not to get an error.
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (4, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;
/
And the results:
max_iops = 13817 latency = 3 max_mbps = 103
Yesterday I went through a problem related to library cache latch contention which I was able to diagnose with the help of Arup Nanda blog.
It was OMS-Enterprise Manager Grid Control who was blocking dozens of other user sessions… The problem is classified in Metalink under Bugs but described as “not a bug”: Bug 10061837: CHILD LIB CACHE LATCH HELD BY SESSION TRAVERSING V$SQL CAUSES HANG
This led to investigate if we could decrease the library cache latch, and investigation on how to avoid soft parses and how to tune the SESSION_CACHED_CURSOR parameter. Help this time come from a compilation work by Juan Carlos Reyes Pacheco on oracle-l mailing list
Another question was brought by another DBA, what is the influence of “select … from dual” into library cache latches. This time a good answer from Tom Kyte showed the huge gain of having direct assignment of variables in PLSQL instead of using or abusing of the dual table. I copy Tom Kyte’s conclusion: “Why do so many people “select …. from dual” in plsql when a simple assignment would work??”
Running the Remote Diagnostics Assistant from Oracle is always a pain with so many questions.
Today I took a bit more time reading the Readme and I found you can avoid most of the questions by using predefined profiles.
Today I was running on a 10g RAC, so what I did for the setup was (instead of ./rda.pl -S)
./rda.pl -S -p Rac_Assessment
With this I had only questions about the SID, way to connect, if there is OCFS, ASM… but no questions about the 300 other Oracle products…
More information on Metlink note: Remote Diagnostic Agent (RDA) 4 – Profile Manual Pages [ID 391983.1]
Something interesting also, it that you can remove more sensitive data from the RDA report. The readme says:
RDA allows you to remove sensitive data from RDA reports. The security profile can be used to turn on filtering and can be combined with other profiles. For example:
-S -p DB10g-Security
This will do the RDA setup for the DB10g profile and turn on filtering through the Security profile. If you want to enable the filtering for an existing setup:
-X Filter enable