Friday, June 29, 2012
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.
- On Redhat server, login with your username that you want to enable remote access. In this example, I will use ‘admin’ user.
- 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.
- Before next step, you need to logged on as root by type ‘su root’.
- 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.
- You’ll see text file as in the figure below. Next, I’ll edit on the highlight line.
- 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”‘.
- Now save the file and exit. To save file, hold ESC + ‘:’ and type ‘wq’ to write and quit file.
- 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’.
- 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.
- 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
- Type your password for ‘admin’ user which has been assign in step 2.
- 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.
- 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’.
- 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.
- Type ‘service vncserver restart’ to apply changes.
- 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
.
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
Subscribe to:
Posts (Atom)