Sessions and Process related Views of oracle

Sessions and Process related Views of oracle:

Sessions and Processes related important Queries of DBA are as follows:

We can know the active sessions in my Database as follows:
There are times that you may need to issue a “SHUTDOWN IMMEDIATE” command to an Oracle Database. It is critical that you should consider checking who are the users that are currently running sessions on that Database. You don’t want to shutdown on your Boss or Important person’s session. Here’s a simple SQL to find all Active sessions in your Oracle Database:
1. SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
2. FROM V$Session
3. WHERE
4. Status=‘ACTIVE’ AND
5. UserName IS NOT NULL;
How to kill all sessions connected to my Database:

Before killing sessions, if possible do
ALTER SYSTEM ENABLE RESTRICTED SESSION;
to stop new sessions from connecting.
If you want to stop new users from connecting, but allow current sessions to continue until they are inactive, you can put the database in QUIESCE mode:
ALTER SYSTEM QUIESCE RESTRICTED;

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';


KILL ALL SESSION BY GETTING ALTER SYSTEM SCRIPTS:

Step 1:Generate all the sessions to be killed as follows:
--------
SQL> select 'Alter system kill session '''||sid||','||serial#||''';'
from v$session; 2

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
Alter system kill session '1839,3172';
Alter system kill session '1842,295';
Alter system kill session '1844,553';
Alter system kill session '1845,2707';
Alter system kill session '1846,219';
Alter system kill session '1847,1555';
(Or)
Note:Use immediate option with alter system to rollback transactions
Eg: Alter system kill session '1847,1555' immediate;




Killing inactive session as follows:


SQL> SELECT 'ALTER SYSTEM KILL SESSION'''||SID||','||SERIAL#||''';'
2 FROM V$SESSION WHERE STATUS='INACTIVE';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION'1835,1618';
ALTER SYSTEM KILL SESSION'1842,295';
ALTER SYSTEM KILL SESSION'1845,2707';
ALTER SYSTEM KILL SESSION'1850,114';
ALTER SYSTEM KILL SESSION'1855,11910';
KILL ALL SESSIONS OF PARTICULAR USER:
-----------------------------------------------------------------------
begin
for sessions in ( select sid
, serial#
from v$session
where username = 'user_to_kill_sessions')
loop
execute immediate 'alter system kill session '''||sessions.sid||','|sessions.serial#||'''';
end loop;
end;

Some toad work import views:

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

select *from v$session;
select *from v$process;
select pid,username,program from v$process where username='20C1248';

Check while migration work in Sony <em>which user is using which program and status:------------------------------------------------------------------------------

SELECT s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,s.module,s.status,s.action FROM v$process p,v$session s WHERE p.addr = s.paddr
and machine like '%BHASKAR%';

SELECT s.sid,s.serial#,p.spid,s.osuser,s.program,s.module,s.status,s.action FROM v$process p,v$session s WHERE p.addr = s.paddr
and osuser='20C1248';

SELECT a.username, a.osuser, b.spid
FROM v$session a, v$process b
WHERE a.paddr = b.addr
AND a.username IS NOT null;

select spid,serial# from v$process where spid='29467';

select 'Alter system kill session '''||sid||','||serial#||''';'
from v$session;

TO FIND THE BLOCKING SESSION ALONG WITH SID AND KILLING IN THE
DATABASE:
select process,sid, blocking_session from v$session where blocking_session is not null;


Kill oracle process in Database:
--------------------------------------------------------
select process,sid, blocking_session from v$session where blocking_session is not null;
select SERIAL# from v$session where SID=1568;
conn /as sysdba
alter system kill session '732,11231';


How to see current transaction in Database?


SQL>select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
from v$session a, v$transaction b
where a.saddr = b.ses_addr;


How to find the job related details scheduled from Oracle Database?

SQL> SELECT
JOB||' '||SCHEMA_USER||' '||LAST_DATE||' '||NEXT_DATE||' '||WHAT||' '||FAILURES
FROM DBA_JOBS;

How To check server reboot time on windows server:


C:\Documents and Settings\Tc62MYNODE.APAC>net statistics server

Server Statistics for \\MYNODEETD106244


Statistics since 10/19/2011 2:22 PM


Sessions accepted 1
Sessions timed-out 7
Sessions errored-out 12

Kilobytes sent 62885
Kilobytes received 15769

Mean response time (msec) 0

System errors 0
Permission violations 0
Password violations 0

Files accessed 7275
Communication devices accessed 0
Print jobs spooled 0

Times buffers exhausted

Big buffers 0
Request buffers 0

The command completed successfully.


How to know the logon time in minutes(secs) and OS user details login time to Oracle Database?

select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine from v$session where type !='BACKGROUND';



Happy SQL learning...


Best regards,

Rafi.

OCP preparation

Hi,
Deciding the things early and act on them should be your main aim for prepararing and passing the Oracle certification exams.

Which path to choose specially when you have the choice between Oracle 9i/10g

1)Oracle 9i OCP:
-----------------

I decided to go with oracle 9i since I took Oracle WORKFORCE Development in Oracle 9i.

Pros:
-----
1. Training of Oracle 9i course which include SQL,Fundamental 1(Architecture of Oracle 9i),Fundamental 2(Backup and recovery) and Performance tuning was availble on cost which was affordable.
2.I took training from Oracle Partner institute so I got the benefit of getting certification.

Note: For getting your OCP certifation,Training in one module is compulsory either SQL or any other other module.

Cons:
-----
1)I have four exams to get my Oracle 9i OCP

If you choose Oracle 10g:

Pros:
-----
1)Two exams can help you getting OCP certification.

Cons:
----
1) Training of Oracle 10g might be a bit expensive depending on the institute you choose.

After completion of my Oracle 9i OCP,I upgraded to Oracle 10g OCP by writing one paper.
It is very important for you to upgrade in each time in order to compete and have better market value,Soon after six months I have completed Oracle 11g OCP also.

For preparing Oracle certification study the below books:

1)SAM ALAPATI
2)ORACLE INSTRUTOR LED TRAINING BOOKS FOR Oracle 9i/10g
3)ORACLE PRESS BOOKS

Links you can follow:
1)TAHITI.ORACLE.COM (OFFICIAL ORACLE DOCUMENTATION)
2)ORACLE BASE

Hope it helps.


Best regards,

Rafi.

SSH x-forwarding document

SSH x-forwarding document

Download the Xming software from the below link.

http://sourceforge.net/projects/xming/files/Xming/6.9.0.31/Xming-6-9-0-31-setup.exe/download

or

http://downloads.sourceforge.net/project/xming/Xming/6.9.0.31/Xming-6-9-0-31-setup.exe?use_mirror=space

and install the Xming on your local PC/Laptop. Once the software got installed successfully, go to Start --> Programs -->Xming-->select Xming to start, once Xming started you will see one X in your leftside windows tray, that means Xming is running on your PC/Laptop.

Download the putty, install the same on your PC/Laptop.

Open putty, On the left side of the putty, in Category section, go to Connection --> SSH --> X11, make sure that Enable X11 Forwarding is checked.

In the same Category Section, Click on Session, enter the hostname or Ip address, login into the server.
Once you successfully logged in, say echo $DISPLAY, it should return a value like localhost:0.10, and also say xclock &, it should display a clock in your PC/Laptop.

Note :- Make sure that the ssh services started on the server side



Best regards,

Rafi.

Changing hostname in ORACLE APPS

Hostname/Server Name change on Oracle Application servers
Hi All,

Below are the steps followed to change the Hostname/Server Name change on Oracle Application servers.
Note:- Refer document 338003.1 and 341322.1
1. Deregister the current database server (Required)
As the database hostname and/or port will be changed, the current database server node needs to be de-registered.
To deregister the current database server node, run the following command as the owner of the Oracle RDBMS file system and current database instance :
perl /appsutil/bin/adgentns.pl appspass= \
contextfile= -removeserver

2. Deregister the current Applications server (Required)
As the Applications hostname will be changed, the current Applications server node needs to be de-registered.
To deregister the current Applications server node, run the following command as the owner of the Oracle Applications file system and current database instance:
perl $AD_TOP/bin/adgentns.pl appspass= \
contextfile= -removeserver
3. Update the AutoConfig Context files of Database and Application servers
Open conext file using vi editor, press esc :%s/sony/sonydadc\.com/g
In the application server context file, make sure that s_javamailer_reply_to set to right value
4. Shutdown the Applications Tier Services (Required)
5. Shutdown the Database Listener (required)
6. Shutdown the Database
7. Change the machine hostname
8. Start the Database
9. Change the Listener.ora file to reflect the new host name
10. Start the listener
11. Reseed the Net Services Topology Model (Required)
a.Run AutoConfig on the Database Tier (Required)
Run AutoConfig on the database tier node using the following syntax :
cd /appsutil/bin/
./adconfig.sh contextfile=/appsutil/_.xml
b. Run AutoConfig on the Applications Tier(s) (Required)
Run AutoConfig on the Applications tier node using the following syntax :
cd /admin/scripts/
./adautocfg.sh appspass=
12. Relogin into Application server and source the environment
13. Start the Applications Tier Services
14. Finishing Tasks
Follow the steps in Note: 230672.1 Section 3: Finishing Tasks
Connect as apps
update apps.fnd_profile_option_values b
set b.profile_option_value = replace(b.profile_option_value,'http://tapplohr.sony:8000','http://tapplohr.sonydadc:8000')
where b.profile_option_value like '%http://tapplohr.sony:8000%';

commit;


select text_value from WF_NOTIFICATION_ATTRIBUTES
where upper(text_value) like ('%TAPPLOHR%');

update WF_NOTIFICATION_ATTRIBUTES
set text_value = replace(text_value,'tapplohr.sony','tapplohr.sonydadc.com')
where upper(text_value) like ('%TAPPLOHR%');

commit;

select text_value from WF_ITEM_ATTRIBUTE_VALUES
where upper(text_value) like ('%TAPPLOHR%');
update WF_ITEM_ATTRIBUTE_VALUES
set text_value = replace(text_value,'tapplohr.sony','tapplohr.sonydadc.com')
where upper(text_value) like ('%TAPPLOHR%');

commit;

connect as / as sysdba
ALTER DATABASE RENAME GLOBAL_NAME TO HRDADC.SONYDADC.COM;

Make sure Workflow services are up and running.

Login as SYSADMIN, select Workflow Administrator Web Applications responsibility, go to Business Events – Systems – Query for old system name like HRDADC.SONY, click on update , and change the system name to HRDADC.SONYDADC.COM, and then apply.

connect as apps

update WF_AGENTS
set address=replace(address,'HRDADC.SONY','HRDADC.SONYDADC.COM');

commit;
Above steps for the below items which are in RED, For green one NO action required.
WF_NOTIFICATION_ATTRIBUTES TEXT_VALUE Value starts with http://[old web host] : Update to new web host
WF_ITEM_ATTRIBUTE_VALUES TEXT_VALUE Value starts with "http://[old web host] : Update to new web host
WF_SYSTEMS GUID Create a new System defined as the new global Database name using the Workflow Administrator Web Applications responsibility.
WF_SYSTEMS NAME Value needs to be replaced with the Database global name
WF_AGENTS ADDRESS Update Database link with the new Database global name.
FND_FORM_FUNCTIONS WEB_HOST_NAME Update with the new web host name
FND_FORM_FUNCTIONS WEB_AGENT_NAME Update to point at the new PLSQL listener name
FND_CONCURRENT_REQUESTS LOGFILE_NAME Update with the correct path to the logfile directory
FND_CONCURRENT_REQUESTS OUTFILE_NAME Update with the new directory path on the Target System




Best regards,

Rafi.