Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, July 29, 2011

Unix memory leak issues.

If several crashes occur that have an associated large core file, then this is a good indication of a potential memory leak.


At any stage if you want to generate the core dump of an active process:

On Solaris:
-0 option is to manually give the path and filename for the core dump
gcore -o /obishared/obiqb/Lognode2/core.19199 19199
gcore: /obishared/obiqb/Lognode2/core.19199.19199 dumped




Start collecting memory consumption statistics.
Solaris

The prstat command can be used to gather basic performance data to help identify if there are any processes consuming a large amount

of memory. For example, the data below is sorted by the SIZE column. SIZE is the total virtual memory size of the process:

prstat -s size


AIX

The ps command can be used to show basic memory usage per process. For example, the data below is sorted by the VSZ column, the total

virtual memory size of the process in KB:

ps -efo "vsz,pid,user,cpu,thcount,comm" | sort –n


HP-UX

The top command can be used to show basic memory usage per process. For example, the SIZE column is the total virtual memory size of

the process in KB:

top

Linux

The ps command can be used to show basic memory usage per process. For example, the data below shows the memory being used by all the

siebmtshmw proceses in KB:


ps axo user,pid,vsz,thcount,cmd | grep siebmtshmw






If you are experiencing some of the symptoms described above or if you suspect a memory leak, it is particularly important that

performance data is captured so the memory leak can be confirmed. It is important to gather at least the following pieces of

information:


Process ID (PID)
Size of the process
The executing command
The timestamp information showing exactly when the data was captured.


One method of capturing this data is to use a shell script. The following are examples of shell scripts that can be used for the

various OS types:

Solaris

while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps -eo pid,vsz,rss,pcpu,args | grep $pid |grep -v grep >> ps.log.$pid
done
sleep 30
done

AIX

while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps -efo "pid,vsz,user,cpu,thcount,comm" | grep $pid |grep -v grep >> ps.log.$pid
done
sleep 30
done

HP-UX

while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps -elf | grep $pid >> ps.log.$pid
done
sleep 30
done


Linux


while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps axo pid,vsz,user,%cpu,thcount,cmd | grep $pid |grep -v grep >> ps.log.$pid
done
sleep 30
done


above are snippets from MOS.
References:

Note 477520.1 "How To Troubleshoot Siebel Server Component Crashes on UNIX". It is however possible for a process to crash as a result of a memory leak.
Note 477004.1 "How Can Users Prevent Core Files from Being Overwritten on UNIX Platforms?"

Thursday, July 28, 2011

Print first column of a file

prstat -s size > 1.log

awk '{print $1}' 1.log

Tuesday, July 26, 2011

Oracle 11g jobs enabled by default .

check the status of the jobs from the below query :

select * from DBA_AUTOTASK_CLIENT

and if the they are enabled and you don't need these jobs, try the below commands to disable them.


SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.disable( 2
3 client_name => 'auto optimizer stats collection',
4 operation => NULL,
5 window_name => NULL);
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_AUTO_TASK_ADMIN.disable( client_name => 'auto space advisor', operation => NULL,window_name => NULL);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_AUTO_TASK_ADMIN.disable( client_name => 'sql tuning advisor', operation => NULL,window_name => NULL);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>


and now again check the status.


Oracle APPS Version?

How do I check which version of Apps I am using ?

SQL> select RELEASE_NAME from apps.fnd_product_groups;

RELEASE_NAME
--------------------------------------------------
12.1.2

Monday, July 25, 2011

OPatch lsinventory hang

OPatch lsinventory just hangs:

recent changes : cloned from RAC to stand alone server.

One of the things we forgot after cloning is making changes in the ORACLE_HOME inventory file:

test case:

export OPATCH_DEBUG=TRUE
$opatch lsinventory -all

shows the old Oracle server names:


Rac::getInstance(): local node is "ourdbdrdb01cdc"
Rac::getRemoteNodes()
No cmd. arg. given, return clusterNodes detected as remoteNodes.
Remote nodes excluding local node:
"ourdbprdb01cdp""ourdbprdb02cdp""ourdbprdb03cdp" <==== these are prod server names still reflecting on the DEV lsinventory:
Rac::isCFS()


So removed the cluster_info from the below file: (backup the file before making any changes)

$ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml






Reran the opatch after removing the cluster info from xml file and it succeeded this time..




--------------------------------------------------------------------------------

OPatch succeeded.

Saturday, July 23, 2011

ORA-16038 & ORA-19809: limit exceeded for recovery files

Today my FRA got filled up and I was getting "archiver error: cannot connect until freed"

So as the quick and dirty way to solve is to delete the archivelogs from the FRA.

Since this is a test environment and I don't care about the backups and want bring the system back as quickly as possible.

so I went ahead and deleted the archivelogs.

ASMCMD> cd ARCHIVELOG/
ASMCMD> ls
2011_07_24/
ASMCMD> rm -r 2011_07_24/
You may delete multiple files and/or directories.
Are you sure? (y/n) y

Now the good thing is space is freed but the bad thing is yet to come:


I still cannot connect to the database and getting the archiver error, hmmm I just freed up the space, deleted todays archive logs also...including the current one ..."hmmmm wait I deleted the current archive log also"....bad ....

so now I cannot switch the logfile on instance-2 on which there was a long running job running since two days:

it just hangs
SQL> alter system switch logfile;




^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL>
SQL>
SQL>
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16038: log 3 sequence# 485 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 2:
'+ODIQA_REDO_01/TESTDB/onlinelog/group_3.269.750189149'
ORA-00312: online log 3 thread 2:
'+ODIQA_REDO_02/TESTDB/onlinelog/group_3.269.750189157'


SQL> alter system switch logfile;


^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL>
SQL>
SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 2 INACTIVE
4 2 CURRENT

####cannot drop the group also..

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance TESTDB2 (thread 2) needs to be archived
ORA-00312: online log 3 thread 2:
'+ODIQA_REDO_01/TESTDB/onlinelog/group_3.269.750189149'
ORA-00312: online log 3 thread 2:
'+ODIQA_REDO_02/TESTDB/onlinelog/group_3.269.750189157'


SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL>
SQL>
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 2 CURRENT
4 2 ACTIVE

SQL> alter system switch logfile;




^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

still the same...

so tried another feat.

rman>connect target /
rman>crosscheck archive log all;
rman>DELETE EXPIRED ARCHIVELOG ALL;
Deleted 198 EXPIRED objects


RMAN> exit


Recovery Manager complete.


Now I gave another shot:


SQL> alter system checkpoint;

System altered.




SQL> alter system archive log current;

System altered.

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 CURRENT
3 2 ACTIVE
4 2 CURRENT

SQL> alter system archive log current;

System altered.

SQL>




It finally worked..cool deal..

ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM

Users complained saying they are getting the below error while accessing the reports from BI.

ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM


when I saw the allert log.

"alert_BIDB.log" 73844 lines, 3767957 characters
ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM
ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM
ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM
ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM
ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM
ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM
ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM
ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM
ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM
ORA-1653: unable to extend table SYS.AUD$ by 128 in tablespace SYSTEM

and even I cannot login into the DB via TOAD.

so logged into the unix server directly and invoked the sqlplus.

and then backed up the aud$ table and truncated the data in it.

SQL> select count(*) from aud$;

COUNT(*)
----------
143413



SQL> Create table Aud$_bkp tablespace users as select * from aud$ ;

Table created.

SQL> truncate table aud$;

Table truncated.

SQL> select count(*) from aud$;

COUNT(*)
----------
0


SQL> SELECT USER_NAME, AUDIT_OPTION, SUCCESS, FAILURE
FROM sys.dba_stmt_audit_opts
where SUCCESS='BY SESSION' or FAILURE='BY SESSION'
union
SELECT USER_NAME, PRIVILEGE, SUCCESS, FAILURE
FROM sys.dba_priv_audit_opts;

from the above output ran the noaudit to diable auditing.

SQL> noaudit ALTER PROFILE,CREATE ANY JOB ,CREATE ANY LIBRARY ,CREATE EXTERNAL JOB ,CREATE USER,DROP ANY TABLE,DROP USER , EXEMPT ACCESS POLICY ;

Noaudit succeeded.

SQL>
SQL> commit;

Commit complete.

Friday, July 22, 2011

opatch: whereis: not found

while invoking Opatch: version 11.2.1.1.6

opatch apply
/ora_backup/u0001/patches/OPatch/OPatch/opatch: whereis: not found

fix:

>export PATH=$PATH:/usr/ucb


>/ora_backup/u0001/patches/OPatch/OPatch/opatch apply
Invoking OPatch 11.2.0.1.6

Tuesday, July 19, 2011

oraagent.bin consuming more or leaking memory

The below process is taking up almost 27g memory on the database server.

it's 11gR2 ---11.2.0.1

PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND

18583 oracle 46 59 0 27250M 26740M sleep 74.8H 0.27% oraagent.bin


According to the below notes:

ORAAGENT or ORAROOTAGENT High Resource Usage [ID 1062676.1]

Bug 10168006 - oraagent process memory growth periodically [ID 10168006.8]

confirms it is a known bug and as a work around just the kill the process and oracle will re spawn the process.

Monday, July 18, 2011

11gR2 : Restarting DUPLICATE After a Failure

A hasle in 10g & 11gR1 is we cannot "re-start" the failed duplicated database command from the point where it failed. We have to start all over again.

Where as in 11gR2 as per the docs: Now we can ...need to test this scenario.

http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#i1008564

Restarting DUPLICATE After a Failure

RMAN automatically optimizes a DUPLICATE command that is a repeat of a previously failed DUPLICATE command. The repeat DUPLICATE command notices which datafiles were successfully copied earlier and does not copy them again. This applies to all forms of duplication, whether they are backup-based (with or without a target connection) or active database duplication. The automatic optimization of the DUPLICATE command can be especially useful when a failure occurs during the duplication of very large databases.

If a DUPLICATE operation fails, you need only run the DUPLICATE comand again, using the same parameters contained in the original DUPLICATE command. The second DUPLICATE operation:

  • Locates the datafiles that were successfully duplicated by the initial DUPLICATE command.

  • Displays a message similar to the following for each datafile that it does not need to duplicate again:

    RMAN-05560: Using previous duplicated file /oradata/new/data01.f for datafile 1 with checkpoint SCN of 1654665 
  • Restores only the missing or incomplete datafiles, thereby avoiding re-copying and restoring all the datafiles.

Before you attempt to resume a failed DUPLICATE operation, you must reset the auxiliary instance to NOMOUNT mode. One way to do this is to exit RMAN, use SQL*Plus to reset the auxiliary instance to NOMOUNT mode, start RMAN and then repeat the DUPLICATE command. If you do not want RMAN to automatically recover from a failed DUPLICATE operation, specify the keyword NORESUME to disable the functionality. Using the keyword NORESUME in the first invocation of DUPLICATE prevents a subsequent DUPLICATE command for the new database from using this automatic optimization.

Wednesday, July 13, 2011

remove ^M from files

if you want remove ^M characters from the files.


1) ftp the files using ascii mode

or

2) use dos2unix in Unix. (Most O.S should have this) if you don't

Then

3) use any of the below for one time .

cat filename |perl -pe 's/\015\012/\n/g' > filename.without.ctrl.m

or

This will do an inline edit of the file, if you wish to do every file in the directory
perl -p -i -e 's/^M//g' *

or

sed 's/^M//g' ${INPUT_FILE} > tmp.txt

mv tmp.txt ${INPUT_FILE}

or

tr -d "\15" < ${INPUT_FILE} > tmp.txt;

mv tmp.txt ${INPUT_FILE}

or)

For multiple files.

#!/bin/ksh
for f in `ls *.CSV` ###( change this .CSV to whatever file extensions you have)
do
mv $f ${f}~ && tr -d '\015\032' <${f}~ >$f
rm ${f}~
done

Tuesday, July 12, 2011

Concurrent Manager Not Running, Manager Status Column Shows 'System Hold Fix Manager before resetting counters

CM is not running and you will get the below messages:

Concurrent Manager Not Running, Manager Status Column Shows 'System Hold Fix Manager before resetting counters







Resolution:

Shutdown apps on all nodes and make sure no FNDLIBR process are running.


Shutdown and start the databases so that you have a clean env.

run the cmclean.sql as per the doc:

Article- ID : 134007.1

start all your apps now:



All my CM are UP now. Happy day..







Note:- I didn't use adrelink.sh since I have not made any changes to the Application tier or oracle tech stack.

Monday, July 11, 2011

oracle.apps.ad.util.TimedProcess$TimedProcessProcess Not enough space

When I ran ./adstpall.sh I was getting the below errors.

and when checked the log, I see the below strange messages.



Executing service control script:
/product/app/ICMQC/inst/apps/ICMQC1_icmqapp02cdp/admin/scripts/adopmnctl.sh start
Timeout specified in context file: 100 second(s)

RC-50208: Exception in method TimedProcessProcess.run
Raised by oracle.apps.ad.util.TimedProcess$TimedProcessProcess: java.io.IOException: Cannot run program "/bin/env": error=12, Not enough space


RC-50208: Exception in method TimedProcessProcess.run
Raised by oracle.apps.ad.util.TimedProcess$TimedProcessProcess: java.io.IOException: Cannot run program "/product/app/ICMQC/inst/apps/ICMQ
C1_icmqapp02cdp/admin/scripts/adopmnctl.sh": error=12, Not enough space
script returned:



### hmm strange I have enough space of disk then why "Not enough space" errors.

checked few other things like DB,Listener,space all is clean.

Now when I tried to start the individual process some came up and some errored out saying they are already runing even though they were not running.



/product/app/ICMQC/inst/apps/ICMQC1_icmqapp02cdp/admin/scripts/apmnctl.sh start

06/03/11-13:14:40 :: adapcctl.sh: starting OPMN if it is not running

opmnctl: opmn is already running.


Now I tried to stop using adstpall.sh


icmqapp02cdp /product/app/ICMQC/inst/apps/ICMQC1_icmqapp02cdp/admin/scripts >./adstpall.sh

You are running adstpall.sh version 120.10.12010000.4


Enter the APPS username: APPS

Enter the APPS password:
Error occurred during initialization of VM
Could not reserve enough space for object heap

adstpall.sh:Exiting with status 1


ahaaaaaaaa, here we go now I got the real error so" we dont have memory for heap"


Since we have three applications running on the same VM, decided to cut down the heap footprint for one of the application:

In the context_file on the app nodes:
changed from:

-server -verbose:gc -Xmx2048M -Xms512M

to

-server -verbose:gc -Xmx256M -Xms64M


for all oacore,jvm,opmn and so on

ran the autoconfig.sh to make sure these takes effect.



icmqapp02cdp /product/app/ICMQC/inst/apps/ICMQC1_icmqapp02cdp/admin/scripts >./adautocfg.sh
Enter the APPS user password:
-----

AutoConfig completed successfully.




Now tried the adstpall.sh

icmqapp02cdp /product/app/ICMQC/inst/apps/ICMQC1_icmqapp02cdp/admin/scripts >./adstpall.sh

You are running adstpall.sh version 120.10.12010000.4


Enter the APPS username: APPS

all the processes were brought down cleanly.......



adstpall.sh:Exiting with status 0



Now tried to start the services:

All enabled services for this node are started.

adstrtal.sh: Exiting with status 0


all came up clean..I am a happy camper.

lesson learnt, we have more applications running on the same box, try to do the math and allocate the sizes accordingly.








Saturday, July 9, 2011

ORA-01157: cannot identify/lock data file -- see DBWR trace file

Yesterday I refreshed this DB from Prod,since it was a friday and I was in a hurry so I left the process after it completed the duplicate DB command.

Over the weekend I saw


Fri Jul 08 16:59:18 2011
Errors in file /icm01/u0001/app/diag/rdbms/QC/QC2/trace/QC2_j000_19878.trc:
ORA-01157: cannot identify/lock data file 513 - see DBWR trace file
ORA-01110: data file 513: '+QA_DATA_01'
Fri Jul 08 17:04:19 2011


first thing I checked is to make sure I dont have any offline datafiles:

SQL> select name,status from v$datafile where status!='ONLINE' and status !='SYSTEM';

no rows selected

cool, so everything looks good.

Now check the alert log:

Fri Jul 08 17:28:17 2011
Errors in file /icm01/u0001/app/diag/rdbms/QC/QC2/trace/QC2_j000_6623.trc:
ORA-01157: cannot identify/lock data file 523 - see DBWR trace file
ORA-01110: data file 523: '+QA_DATA_01'

no usefule info, Oh ooo.. so check the trace file:


>vi /icm01/u0001/app/diag/rdbms/QC/QC2/trace/QC2_j000_19878.trc


*** 2011-07-08 16:59:20.276
*** SESSION ID:(1522.9) 2011-07-08 16:59:20.276
*** SERVICE NAME:(SYS$USERS) 2011-07-08 16:59:20.276
*** MODULE NAME:(DBMS_SCHEDULER) 2011-07-08 16:59:20.276
*** ACTION NAME:(QUEST_PPCM_JOB_PM_2) 2011-07-08 16:59:20.276

DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 516: '+QA_DATA_01'
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 523: '+QA_DATA_01'
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 513: '+QA_DATA_01'

nothing much useful info either in the trace file.

so lets try to offline the datafile:


SQL> alter database datafile 523 offline;
alter database datafile 523 offline
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "523"


nope...doesn't work...

Only thing I left to check is TEMPFILEs,since they won't come across the backups so, lets check the tempfiles.


SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+QA_DATA_01
+QA_DATA_01
+QA_DATA_01
+QA_DATA_01
+QA_DATA_01


Bingo...there we go...so we dont have the tempfiles.


SQL> select name from v$tablespace where name like '%TEMP%';

NAME
------------------------------
TEMP2
TEMP1

SQL> drop tablespace temp1;
drop tablespace temp1
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace


SQL> drop tablespace temp2;

Tablespace dropped.

SQL> create temporary tablespace temp2 ;

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> drop tablespace temp1;

Tablespace dropped.

SQL> create temporary tablespace temp1;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> alter tablespace temp1 add tempfile;

Tablespace altered.


SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+QA_DATA_01/QC/tempfile/temp2.450.756037653
+QA_DATA_01/QC/tempfile/temp1.466.756037693
+QA_DATA_01/QC/tempfile/temp1.454.756037771

Wednesday, July 6, 2011

java.sql.SQLException: No suitable driver when involing ODI

If you are trying to login into the remote server using local operator client.
ODI-10g:

The error means:
■ No suitable driver
The JDBC URL is incorrect. Check that the URL syntax is valid


In the below screenshot:























I had a space in my URL link: and as soon as I deleted it the connection worked fine.










If you are trying to connect to the local ODI database:

Make sure odiparam.bar is correctly configured in the %ODI_HOME%/bin/odiparams.bat (or odiparams.sh) file.
from

rem
rem Repository Connection Information
rem
set ODI_SECU_DRIVER=org.hsqldb.jdbcDriver
set ODI_SECU_URL=jdbc:hsqldb:hsql://localhost
set ODI_SECU_USER=sa
set ODI_SECU_ENCODED_PASS=
set ODI_SECU_WORK_REP=WORKREP
set ODI_USER=SUPERVISOR
set ODI_ENCODED_PASS=LELKIELGLJMDLKMGHEHJDBGBGFDGGH

to

set ODI_SECU_DRIVER=oracle.jdbc.driver.OracleDriver set ODI_SECU_URL=jdbc:oracle:thin:@myserver:1521:orcl

Change apps user password

To change R12 apps user password via forms:

security->define->search>username

change the password here:

if it is greyed out then check whether the system profile is set to disabled:
"Application SSO LDAP Synchronization" enable this and you should be able to change the password via forms.

if not you can also use the below procedure:

SQL> exec apps.fnd_user_pkg.updateuser('TEST','welcome123');

PL/SQL procedure successfully completed.

or

begin
fnd_user_pkg.updateuser(x_user_name => 'TEST',x_owner => 'TEST',x_unencrypted_password => 'welcome123');
dbms_output.put_line('updateuser: ' || sqlerrm);
commit;
end;
/

or

for all the users:


set serveroutput on
declare
b_OK boolean;

CURSOR C1 IS
SELECT user_name
FROM apps.fnd_user
WHERE substr(user_name,1,1) in ('A','B','C','D')
AND substr(user_name,2,1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND substr(user_name,3,1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND substr(user_name,4,1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND substr(user_name,5,1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND substr(user_name,6,1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
-- AND USER_NAME = 'E131492'
ORDER by user_name desc;
begin
For l_username IN C1 LOOP
b_OK:=apps.fnd_user_pkg.changepassword(l_username.user_name,'TEST123');
if (b_OK) then
update apps.fnd_user set user_guid='' where user_name = l_username.user_name;
commit;
dbms_output.put_line('Password reset');
else
dbms_output.put_line('Password could not be reset');
end if;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;


or

You can change the cursor to something like:-
============================
 CURSOR C1 IS
select user_name from apps.fnd_user
 where end_date is null and user_name not in ('APPS','SYSADMIN')
ORDER by user_name desc;

 

Friday, July 1, 2011

ASM add disk and rebalance

add the new luns to the existing disk group.

SQL> alter diskgroup DISK_DATA_01 add disk '/dev/oracle/disk64g_t1_0018';

Diskgroup altered.


change the rebalance speed from default:

SQL> alter diskgroup ICMPRD_DATA_01 rebalance power 11;

Diskgroup altered.


check the status of the rebalance operation:

select sysdate, OPERATION,STATE, POWER, ACTUAL, SOFAR, EST_WORK,EST_RATE, EST_MINUTES
from v$asm_operation;


SYSDATE OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
--------- ----- ---- ---------- ---------- ---------- ---------- ---------- -----------
01-JUL-11 REBAL RUN 11 11 5907 259758 5591 45