Google+ Followers

Pages

Friday, June 29, 2012

OEL and ORACLE DATABASE INSTALLATION





SSH login without password


Your aim

You want to use Linux and OpenSSH to automize your tasks. Therefore you need an automatic login from host A / user a to Host B / user b. You don't want to enter any passwords, because you want to call ssh from a within a shell script.

How to do it

First log in on A as user a and generate a pair of authentication keys. Do not enter a passphrase:
a@A:~> ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/a/.ssh/id_rsa): 
Created directory '/home/a/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/a/.ssh/id_rsa.
Your public key has been saved in /home/a/.ssh/id_rsa.pub.
The key fingerprint is:
3e:4f:05:79:3a:9f:96:7c:3b:ad:e9:58:37:bc:37:e4 a@A
Now use ssh to create a directory ~/.ssh as user b on B. (The directory may already exist, which is fine):
a@A:~> ssh b@B mkdir -p .ssh
b@B's password: 
Finally append a's new public key to b@B:.ssh/authorized_keys and enter b's password one last time:
a@A:~> cat .ssh/id_rsa.pub | ssh b@B 'cat >> .ssh/authorized_keys'
b@B's password: 
From now on you can log into B as b from A as a without password:
a@A:~> ssh b@B hostname
B





mail url: http://www.linuxproblem.org/art_9.html

Enable remote desktop on linux using VNC


Step-by-step

In this section, I’ll show how to configure VNC server on Redhat / OEL server and using VNC Viewer connect the server remotely from Windows XP.
  1. On Redhat server, login with your username that you want to enable remote access. In this example, I will use ‘admin’ user.
    Redhat Gnome's desktop
  2. Open Terminal, type ‘vncpasswd’. Type your password and verify password again. This command will use to set you password for remote access for the current user.
    Set VNC password
  3. Before next step, you need to logged on as root by type ‘su root’.
    Log as root using su
  4. Edit the file /etc/sysconfig/vncservers by type ‘ vi /etc/sysconfig/vncservers’.
    Note: If you are new to Linux, vi is an editor tool in command line mode on Linux.
    Edit /etc/sysconfig/vncservers
  5. You’ll see text file as in the figure below. Next, I’ll edit on the highlight line.
    /etc/sysconfig/vncservers
  6. Uncomment on the highlight line. If you have more than one usernames that want to enable remote acces, you can change VNCSERVERS value in this format, “1:username1 2:username2 3:username3 ….”. In this example, I have only one user which is root so this line of mine is ‘VNCSERVERS = “1:admin”‘.
    Edit /etc/sysconfig/vncservers
  7. Now save the file and exit. To save file, hold ESC + ‘:’ and type ‘wq’ to write and quit file.
    Write and quit file in vi editor
  8. Next, log off the user if you are not user ‘root’ and log in as root. Enable VNC service by type ‘chkconfig vncserver on’. Then, start the VNC service by type ‘service vncserver start’.
    Set up VNC server as service and start
  9. If you have firewall enable on Redhat, be sure that your firewall configuration won’t block connection from remote computer by open port TCP 5901 for remote access. Open Applications -> System Settings -> Security Level. Add ’5901:tcp’ on Other ports.
    Note: VNC uses TCP protocol on port 5901.
    Open TCP port 5901 for remote access
  10. Now you can connect Redhat server from remote computer. On my Windows XP computer, open VNC Viewer on Windows XP, type IP Address of Redhat server with number as a username specify in step 6. In this example, I want to remote to Redhat server as ‘admin’ user which I assign as number 1 in step 6 (1:admin) and my Redhat server is 10.110.141.220. So I type ’10.110.141.220:1′.
    Note: You can download VNC Viewer for free at realvnc.com
    Test connect to Redhat server from remote computer
  11. Type your password for ‘admin’ user which has been assign in step 2.
    Specify password
  12. Now you have connect to Redhat server remotely. But you’ll see that the interface looks different. You have to do a little thing more.
    Remote screen
  13. On Redhat server, open terminal and type ‘vi /home/username/.vnc/xstartup’. In this example, I type ‘vi /home/admin/.vnc/xstartup’.
    Note: If you going to enable remote access for user ‘root’, the file would be at ‘/root/.vnc/xstartup’.
    Edit /home/admin/.vnc/xstartup
  14. Uncomment these two lines and save the file.
    Note: If you want to force to load Gnome or KDE Desktop on remote access, edit the last line from ‘twm &’ to ‘startx &’ for Gnome and ‘startkde &’ for KDE Desktop.
    Edit xstartup
  15. Type ‘service vncserver restart’ to apply changes.
    Restart VNCServer service
  16. Reconnect using VNC viewer on remote computer again. Now you will see the desktop as you were log in at the server but now you’re remotely :) .
    Connect to Redhat server remotely


Friday, June 22, 2012

Oracle Performance Queries



--Active Session Info

SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
ORDER BY a.spid, c.piece;

--9ma7902cbt7x4

--Trace SQL Query Average Execution Time Using SQL ID
SELECT sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
FROM v$sql s
WHERE s.sql_id='9ma7902cbt7x4';

--Get The Detail Explain Plan Using SQL ID
SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor('9ma7902cbt7x4'));

--Session Elapsed Processing Time
SELECT s.sid, s.username, s.module,
round(t.VALUE/1000000,2) "Elapsed Processing Time (Sec)"
FROM v$sess_time_model t, v$session s
WHERE t.sid = s.sid
AND t.stat_name = 'DB time'
AND s.username IS NOT NULL
AND t.VALUE/1000000 >= '1' --running more than 1 second
ORDER BY round(t.VALUE/1000000,2) DESC;

--Session Elapsed Processing Time Statistic By SID
SELECT  a.sid, b.username, a.stat_name, ROUND((a.VALUE/1000000),2) "Time (Sec)"
FROM v$sess_time_model a, v$session b
WHERE a.sid = b.sid
AND b.sid = '523' 
ORDER BY ROUND((a.VALUE/1000000),2) DESC;

--Use Longops To Check The Estimation Query Runtime
SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time,
last_update_time, time_remaining "REMAIN SEC", round(time_remaining/60,2) "REMAIN MINS",
elapsed_seconds "ELAPSED SEC", round(elapsed_seconds/60,2) "ELAPSED MINS",
round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS", message TIME
FROM v$session_longops
WHERE sofar<>totalwork
AND time_remaining <> '0'

--Detect Blocking Session
SELECT sid, serial#, username, STATUS, state, event,
blocking_session, seconds_in_wait, wait_time, action, logon_time
FROM gv$session
WHERE state IN ('WAITING')
AND wait_class != 'Idle'
AND event LIKE '%enq%'
AND TYPE='USER'

--Active Table Locking
SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
c.sql_text,b.logon_time, e.owner, e.object_name "Table Lock"
FROM v$session b, v$sqltext c, v$locked_object d, dba_objects e
WHERE b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = 'ACTIVE'
ORDER BY b.sid, c.piece

--RAC Active Table Locking
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text,
b.state, c.sql_text,b.logon_time,
b.STATUS,  e.owner, e.object_name "Table Lock"
FROM gv$process a, gv$session b, gv$sqltext c, gv$locked_object d, dba_objects e
WHERE a.addr=b.paddr
AND b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = 'ACTIVE'
ORDER BY a.spid, c.piece;

--Monitor Top Waiting Event Using Active Session History (ASH)
SELECT h.event,
SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC;

--Monitor Highest SQL Wait Time Using Active Session History (ASH)
SELECT h.session_id, h.session_serial#, h.sql_id, h.session_state,
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text,
SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.session_id, h.session_serial#, h.sql_id, h.session_state,
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text
ORDER BY SUM(h.wait_time + h.time_waited) DESC;

--Monitor Highest Object Wait Time Using Active Session History (ASH)
SELECT o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
h.sql_id, h.module, SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, dba_objects o, v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.current_obj# = o.object_id
AND e.event_id = h.event_id
GROUP BY  o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
h.sql_id, h.module
ORDER BY SUM(h.wait_time + h.time_waited) DESC;

--Monitor Highest Event Wait Time Using Active Session History (ASH)
SELECT h.event "Wait Event", SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.event_id = e.event_id
AND e.wait_class <> 'Idle'
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC

--Database Time Model Statistic
SELECT wait_class, NAME, ROUND (time_secs, 2) "Time (Sec)",
ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
FROM
(SELECT n.wait_class, e.event NAME, e.time_waited / 100 time_secs
FROM v$system_event e, v$event_name n
WHERE n.NAME = e.event
AND n.wait_class <> 'Idle'
AND time_waited > 0
UNION
SELECT
'CPU',
'Server CPU',
SUM (VALUE / 1000000) time_secs
FROM v$sys_time_model
WHERE stat_name IN ('background cpu time', 'DB CPU'))
ORDER BY time_secs DESC;

--Monitor I/O On Data Files
SELECT vfs.file#, dbf.file_name, dbf.tablespace_name, dbf.bytes, vfs.phyrds/vfs.phywrts,
vfs.phyblkrd/vfs.phyblkwrt, vfs.readtim, vfs.writetim
FROM v$filestat vfs, dba_data_files dbf
WHERE vfs.file# = dbf.file_id

--I/O Stats For Data Files & Temp Files
SELECT file_no,
filetype_name,
small_sync_read_reqs "Synch Single Block Read Reqs",
small_read_reqs "Single Block Read Requests",
small_write_reqs "Single Block Write Requests",
round(small_sync_read_latency/1000,2) "Single Block Read Latency (s)",
large_read_reqs "Multiblock Read Requests",
large_write_reqs "Multiblock Write Requests",
async_io "Asynch I/O Availability"
FROM v$iostat_file
WHERE filetype_id IN (2,6) --data file and temp file

--I/O Stats By Functionality
SELECT function_name,
small_read_reqs "Single Block Read Requests",
small_write_reqs "Single Block Write Requests",
large_read_reqs "Multiblock Read Requests",
large_write_reqs "Multiblock Write Requests",
number_of_wait "I/O Waits",
round(wait_time/1000,2) "Total Wait Time (ms)"
FROM v$iostat_function
ORDER BY function_name

--Temporary Tablespace Usage By SID
SELECT tu.username, s.sid, s.serial#, s.sql_id, s.sql_address, tu.segtype,
tu.extents, tu.blocks, SQL.sql_text
FROM v$tempseg_usage tu, v$session s, v$sql SQL
WHERE tu.session_addr = s.addr
AND tu.session_num = s.serial#
AND s.sql_id = SQL.sql_id
AND s.sql_address = SQL.address

--Monitor Overall Oracle Tablespace
SELECT d.STATUS "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
d.initial_extent "Initial Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99,999,999.999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %",
TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),'99,999,990.900') "MaxSize (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0), '990.00') "Used % of Max"
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,
SUM(bytes) bytes,
SUM(decode(autoextensible,'NO',bytes,'YES',maxbytes))
maxbytes FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
ORDER BY 10 DESC;

--Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name,
'physical reads', VALUE,0))/
(SUM(DECODE(name, 'db block gets', VALUE,0))+
(SUM(DECODE(name, 'consistent gets', VALUE, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio"
FROM v$sysstat --Use gv$sysstat if running on RAC environment

--Library Cache Hit Ratio
SELECT SUM(pins) "Total Pins", SUM(reloads) "Total Reloads",
SUM(reloads)/SUM(pins) *100 libcache
FROM v$librarycache --Use v$librarycache if running on RAC environment

--DB Session Memory Usage
SELECT se.sid,n.name, MAX(se.VALUE) maxmem
FROM v$sesstat se, v$statname n
WHERE n.statistic# = se.statistic#
AND n.name IN ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
GROUP BY n.name, se.sid
ORDER BY MAX(se.VALUE) DESC