Unix: Rsync password authentication
1) create configre file /usr/local/etc/rsyncd.conf
chmod 640 rsyncd.conf
uid = rsync
gid = rsync
use chroot = no
max connections = 4
syslog facility = local5
pid file = /var/run/rsyncd.pid
[www]
path = /usr/local/websites/
comment = all of the websites
2) create user/group as rsync/rsync
3) run rsyncd as "rsync --daemon"
4) create /usr/local/etc/rsyncd.conf (use mode 640)
auth users = tridge, susan
secrets file = /usr/local/etc/rsyncd.secrets
5) create /usr/local/etc/rsyncd.secrets (chmod 640)
tridge:mypass
susan:herpass
6) rsync -avz susan@ducky::www /home/dan/test
7) add option "--password-file /home/dan/test/rsync.password"
add password in /home/dan/test/rsync.password
UNIX: context switch
Each process has its own virtual memory space. References to real memory are provided through a process-specific set of address translation maps. The computer's Memory Management Unit (MMU) contains a set of registers that point to the current process's address translation maps. When the current process changes, the MMU must load the translation maps for the new process. This is called a "Context Switch"
Oracle: transformation between v$lock and v$session_wait
enqueue
-- P1: lock type(or name) and mode
-- P2: resource identifier ID1 for the lock
-- P3: resource identifier ID2 for the lock
v$lock.id1 = p2
v$lock.id2 = p3
--lock type:
v$lock.type = chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1,1671680)/65535)
--the mode in which the enqueue is being requested
v$lock.request = mod(p1,65536)
--find locks and lock holders
to show only holders and waiters for locks being waited on
select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1,
id2,
lmode,
request,
type
from v$lock
where (id1, id2, type) in
(select id1, id2, type from v$lock where request > 0)
order by id1, request;
Oracle: i/o wait events
1) db file sequential read:
single block read into one SGA buffer
single block I/Os are usually the result of using indexes
rarely, full table scan calls could get truncated to a single block call due to
extent boundaries, or buffers already present into the buffer cache.
2) db file scattered read:
multiblock read into many discontinuous SGA buffers
scattered read wait event identified that a full table scan is occurring.
3) direct read:
single or multiblock read into the PGA, bypassing the SGA
Oracle: Wait events
-- query following views for wait events
V$SESSION_WAIT V$SESSION_EVENT V$SYSTEM_EVENT
-- V$SESSION_WAIT is a current state view.
it lists either the event currently being waited for or the event last waited
for on each session
-- V$SESSION_EVENT lists the cumulative history of events waited for on each session
After a session exists, the wait event statics for that session are removed from
this view.
-- V$SYSTEM_EVENT lists the events and times waited for by the whole instance
since instance startup
-- timed statistics are automatically collected for database
if the initialization parameter statistics_level is set to typical or all
if STATISTICS is set to BASIC, then you must set TIMED_STATISTICS to TRUE to enable
collection of timed statistics
Linux: Tune disk device
-query current device settings
/sbin/hdparm /dev/hda
-run benchmark before tuning
/dbin/hdparm -Tt /dev/hda
Sybase: SQL text auditing
exec sp_audit cmdtext,wlsuser,"all","on"
exec sp_audit cmdtext,wlsuser,"all","off"
Solaris: How to limit display of other user's processes
On Solaris10, it is possible to allow user only see their own processes by command
ps -aef
Following is how to configure it.
1)to set limit globally edit /etc/security/policy.conf file and uncomment PRIV_DEFAULT line
set its value as
PRIV_DEFAULT=basic,!proc_info
2)to set limit per user basis
edit /etc/user_attr and add following entry
gmb::::lock_after_retries=no;defaultpriv=basic
Oracle: PL/SQL table and cursor
set serveroutput on
declare
-- Declare the PL/SQL table
type deptarr is table of dept%rowtype
index by binary_integer;
d_arr deptarr;
-- Declare cursor
type d_cur is ref cursor return dept%rowtype;
c1 d_cur;
i number := 1;
begin
-- Populate the PL/SQL table from the cursor
open c1 for select * from dept;
loop
exit when c1%NOTFOUND;
fetch c1 into d_arr(i);
i := i+1;
end loop;
close c1;
-- Display the entire PL/SQL table on screen
for i in 1..d_arr.last loop
dbms_output.put_line('DEPTNO : '||d_arr(i).deptno );
dbms_output.put_line('DNAME : '||d_arr(i).dname );
dbms_output.put_line('LOC : '||d_arr(i).loc );
dbms_output.put_line('---------------------------');
end loop;
end;
/
Oracle: 9i flashback query
Flashback Query is enabled and disabled using the DBMS_FLASHBACK package. The point in time of the flashback can be specified using the SCN or the actual time:
EXECUTE Dbms_Flashback.Enable_At_System_Change_Number(123);
EXECUTE Dbms_Flashback.Enable_At_Time('28-AUG-01 11:00:00');
Once you've finished performing all your read-only operations you can turn off flashback query using:
EXECUTE Dbms_Flashback.Disable;
to get current system change number
select dbms_flashback.get_system_change_number from dual;
Rsync: certification authentication on unix
1) use rsync over ssh to copy files between systems
rsync -av -e ssh src:/dir/* dest:/dir
2) use rsync over ssh and use public key for user authentication
(login from host A to host B without password authentication)
1) on host A generate private/public key pair
ssh-keygen -t rsa -b 2048 -f ~/.ssh_key/A-rsync-key
2) copy A-rsync-key.pub file to host B:~/.ssh/authorized_keys file
(use sshd_config to determine the location of this file)
3) create "config" file in A:~/.ssh as
Host B
User root
Compression yes
Protocol 2
RSAAuthentication yes
StrictHostKeyChecking no
ForwardAgent yes
ForwardX11 no
IdentityFile /.ssh_key/A-rsync-key
Apache: configure SSI - server side includes
1) add following lines to httpd.conf
AddType text/html .shtml
AddHandler server-parsed .shtml
2) modify options line in Document root to add Includes
Options Indexes FollowSymLinks +Includes
3) optionally turn on XBitHack
XBitHack On
and chmod 744 filename
4) alternatively you can create .htaccess file under a given directory and add
AddType text/html .shtml
AddHandler server-parsed .shtml
Options Indexes FollowSymLinks Includes
Perl: Serach Matching
.* : match anything not a newline till end of line
Oracle: trace event
init.ora: event='1401 trace name errorstack, level 12'
user session: alter session set events '10046 trace name context forever, level 4';
* 10046 trace name context forever, level 4
Trace SQL statements and show bind variables in trace output.
* 10046 trace name context forever, level 8
This shows wait events in the SQL trace files
* 10046 trace name context forever, level 12
This shows both bind variable names and wait events in the SQL trace files
* 1401 trace name errorstack, level 12
1401 trace name errorstack, level 4
1401 trace name processstate
Dumps out trace information if an ORA-1401 "inserted value too large for column" error
occurs. The 1401 can be replaced by any other Oracle Server error code that you want
to trace.
* 60 trace name errorstack level 10
Show where in the code Oracle gets a deadlock (ORA-60), and may help to diagnose
the problem.
============================================================
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 10
SQL> oradebug tracefile_name
or to trace a specific error
SQL> select username, spid from v$process;
SQL> oradebug setospid 2280
SQL> oradebug event 10046 trace name context off
Oracle: lock - enqueue
A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET are used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.
Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.
Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:
semmns = sum of the "processes" parameter for each instance
(see init
.ora for each instance)
semmni = number of instances running simultaneously;
semmsl = semmns
Oracle: v$sql_plan view
SQL Execution Statistics in 9i
The SQL tuning process prior to 9.2 involved executing SQL commands, then OS commands, and then SQL commands again. This is a very time-consuming and burdensome process. In 9.2, Oracle decided to retain the SQL metrics for each statement in the SGA (library cache) while the statement remains cached. The DBA could then diagnose SQL issues at a SQL prompt and leave tkprof alone. This is a vast improvement over prior versions.
Oracle9.2 contains the following views that enable the DBA to identify SQL issues directly from a SQL prompt. These views should be used to periodically check SQL statistics and full-table scans, alerting the DBA to problem areas requiring corrective action.
• v$sql_plan - This view shows the same information as shown by Explain Plan except it is the actual execution plan and not the predicted one – just like tkprof and even better than Explain Plan.
• v$sql_plan_statistics - This view contains the execution statistics for each operation (step) in the v$sql_plan. Queries should access this view and look for poor SQL operations including TABLE ACCESS FULL – full-table scans.
• v$sql_plan_statistics_all - This view combines data from v$sql_plan, v$sql_plan_statistics and v$sql_workarea.
Both v$sql_plan_statistics and v$sql_plan_statistics_all are not populated by default. The option statistics_level=all must be set.
to query v$sql_plan
1) retrieve "address" and "hash_value" from v$sql
2) query v$sql_plan with columns "operation","options","object_name" and "cost"
using "address" and "hash_value" as primary key.
Perl: Find the Nth Occurrence of a match
while(/(\w+)\s+fish\b/gi){
if(++$count == $WANT){
# do what ever you want to do
}
}
\w+ match a word
\s+ 1 or n space
\b word boundary
/g gloal match/substitue as often as possible.
/i ignore case
Perl: match numbers
to match one or more numbers in a string
$str ~= /(\d+)/
Perl: difference between + and *
+ means 1 or more occurences
* means 0 or more occurences
for examples:
$str = "good food";
($str1 = $str) =~ s/o*/e;
($str2 = $str) =~ s/o+/e;
result is:
$str1: egood food
$str2: ged food
Perl: transaction across databases
How to create transaction across multiple databases in perl script.
#!/usr/local/bin/perl -w
#
# this script will test transaction rollback
#
use DBD::Sybase;
$dbh_ora = DBI->connect("dbi:Oracle:host=192.168.69.11;sid=qadtrain",
'qad',
'qad',
{AutoCommit=>0}
)|| die "Database connection not made: $DBI::errstr";
$dbh_syb = DBI->connect("dbi:Sybase:server=DEVELOP;database=CIS",
"sa",
"october"
)|| die "Database connection not made: $DBI::errstr";
$dbh_syb->{AutoCommit}=0;
$dbh_syb->{RaiseError}=1;
$dbh_ora->{AutoCommit}=0;
$dbh_syb->{RaiseError}=1;
eval{
my $v1=4;
$sth_ora=$dbh_ora->prepare("insert into peij1 values(?,?)");
$sth_ora->execute($v1,$v1);
$sth_syb=$dbh_syb->prepare("insert into peij1 values(?,?)");
$sth_syb->execute($v1,$v1);
$dbh_ora->commit;
$dbh_syb->commit;
};
if($@){
warn "Transaction aborted due to $@";
$dbh_ora->rollback;
$dbh_syb->rollback;
}
