Dev's Weblog

Reading time: 1 - 2 minutes

Sometimes it becomes very tough to find the relevant oracle trace file using session id in oracle.You can alter your session before setting an event to identify trace file quite easily but following method doesn't require alteration of session.

SELECT s.sid,
s.server,
lower(
CASE
WHEN s.server IN ('DEDICATED','SHARED') THEN
i.instance_name || '_' ||
nvl(pp.server_name, nvl(ss.name, 'ora')) || '_' ||
p.spid || '.trc'
ELSE NULL
END
) AS trace_file_name
FROM v$instance i,
v$session s,
v$process p,
v$px_process pp,
v$shared_server ss
WHERE s.paddr = p.addr
AND s.sid = pp.sid (+)
AND s.paddr = ss.paddr(+)
AND s.type = 'USER'
ORDER BY s.sid;

Sample Output:

       SID SERVER    TRACE_FILE_NAME
---------- --------- --------------------------------------------------
     8    DEDICATED orcl_ora_17842.trc
    10   DEDICATED orcl_ora_17846.trc
    16   DEDICATED orcl_ora_17856.trc
    199 DEDICATED orcl_ora_17848.trc
    394 DEDICATED orcl_ora_17844.trc
    400 DEDICATED orcl_ora_17850.trc
    580 DEDICATED orcl_ora_17852.trc
    585 DEDICATED orcl_ora_17840.trc

8 rows selected.

Reading time: < 1 minute

SQL> select scn_to_timestamp(max(ora_rowscn)) from WRH$_FILESTATXS;

SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))

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

24-JUN-10 11.00.10.000000000 AM

Reading time: 2 - 2 minutes

a ias_admin is user required to use Enterprise Manager Web (Application Server Console - iASConsole) Site.

a ias_admin password is set during Installation of Oracle Application Server (902, 904, 10.1.2.X) or Oracle Identity Management (Infra Tier 10.1.4.X)aias_admin account is NOT stored in OID (Oracle Internet Directory), It is stored in XML file (JAZN-XML - Java AuthoriZatioN)

How to Reset / Change ias_admin Password

You can reset/change ias_admin password in following ways

1. Using Enterprise Manager (Application Server Control) Web Site

-Login to Instance Home Page
-Click on Preferences on top right
-In new screen, click on "Change Password" on left menu
-Enter current password and New Password

2. Using Command line tool

emctl set password oldpassword new password

like

emctl set password password1 password2

If you don't know current ias_admin password then change it in configuration file

3. Change ias_admin password directly in configuration file
-Backup $ORACLE_HOME/sysman/j2ee/config/jazn-data.xml
-Search for xml entry like below

< user >
< name > ias_admin < / >
< credentials >{903}8QkQ/crno3lX0f3+67djasdasdvdawe < / credentials >
< /user >

and Update new password (password2 like )

< user >< name > ias_admin < / >
< credentials > !password2 / credentials >
< /user >

Note ! (Exclamation Mark in front of password. This signifies that password is stored in clear text)

Reading time: < 1 minute

SELECT

'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)

||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)

||'connect to ' || L.USERID || ' identified by '''

||L.PASSWORD||''' using ''' || L.host || ''''

||chr(10)||';' TEXT

FROM sys.link$ L,

sys.user$ U

WHERE L.OWNER# = U.USER# ;

Reading time: < 1 minute

exp system/oracle file=structure.dmp full=y rows=n

Open the structure.dmp and you will have definitions for all the objects of the database.