DBA Story (dba on unix/linux)

Piner@10gR2>select f,b from (
select dbms_rowid.rowid_relative_fno(rowid) f,
　　　　　 dbms_rowid.rowid_block_number(rowid) b
from test) group by f,b;
Piner@10gR2>select file#,dbablk,tch from x$bh where obj=
(select data_object_id from dba_objects
where owner='PINER' and object_name='TEST')
order by dbablk;

select count(*) from v$bh where objd=
(select data_object_id from dba_objects
where owner='PINER' and object_name='TEST')
and status !='free'

select /*+ rule */ owner,object_name from dba_objects
where data_object_id in
(select obj from
(select obj from x$bh order by tch desc)
where rownum < 11) ;
SQL> select tch, flag,
decode(bitand(flag,1), 0, 'N', 'Y') dirty,
decode(bitand(flag,16), 0, 'N', 'Y') temp,
decode(bitand(flag,1536), 0, 'N', 'Y') ping,
decode(bitand(flag,16384), 0, 'N', 'Y') stale,
decode(bitand(flag,65536), 0, 'N', 'Y') direct,
decode(bitand(flag,1048576), 0, 'N', 'Y') new
from x$bh
where dbablk = 12
and obj=11835
and tch>0;

select dbms_rowid.rowid_object('AAAAeNAADAAAAWZAAA') data_object_id#,
dbms_rowid.rowid_relative_fno('AAAAeNAADAAAAWZAAA') rfile#,
dbms_rowid.rowid_block_number('AAAAeNAADAAAAWZAAA') block#,
dbms_rowid.rowid_row_number('AAAAeNAADAAAAWZAAA') row# from dual;

#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 8 -size_large 8 -type rand &
#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 1024 -size_large 1024 -type seq &
#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 8 -size_large 128 -matrix col -num_small 500 -type rand &
#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 8 -size_large 128 -matrix detailed -type rand &

rac1.local_listener='(address=(protocol=tcp)(host=dbrac01)(port=1521)) '
rac1.remote_listener='(address=(protocol=tcp)(host=dbrac02)(port=1521)) '
rac2.local_listener='(address=(protocol=tcp)(host=dbrac02)(port=1521)) '
rac2.remote_listener='(address=(protocol=tcp)(host=dbrac01)(port=1521)) '
$ more tnsnames.ora
# tnsnames.ora.Network Configuration File
rac=
(description=
(load_balance=on)
(failover=on)
(address_list=
(address=(protocol=tcp)(host=dbrac01)(port=1521))
(address=(protocol=tcp)(host=dbrac02)(port=1521)))
(connect_data=
(service_name=rac)))
[oracle@dbtest admin]$ more test.sh
#!/bin/sh
sqlplus "test/test@rac" <<EOF
select instance_name from v\$instance;
exit
EOF

Listeners_rac =
(address_list =
(address = (protocol = tcp)(host =dbrac01-vip)(port = 1521))
(address = (protocol = tcp)(host =dbrac02-vip)(port = 1521))
(address = (protocol = tcp)(host =dbrac03-vip)(port = 1521))
(address = (protocol = tcp)(host =dbrac04-vip)(port = 1521))
)
Rac =
(description =
(load_balance = on)
(address = (protocol = tcp)(host = dbrac01-vip)(port = 1521))
(address = (protocol = tcp)(host = dbrac02-vip)(port = 1521))
(address = (protocol = tcp)(host = dbrac03-vip)(port = 1521))
(address = (protocol = tcp)(host = dbrac04-vip)(port = 1521))
(connect_data =
(server = dedicated)
(service_name = rac)
)
)

rac=
(description=
(load_balance=on)
(failover=on)
(address_list=
(address=(protocol=tcp)(host=dbtest)(port=1521))
(address=(protocol=tcp)(host=dbtest)(port=1522)))
(connect_data=
(service_name=rac)
(failover_mode=(type=select)(method=basic))
))

SQL>create diskgroup dgroup1
Normal reduadancy
failgroup controller1
disk '/devices/diska1', '/devices/diska2','/devices/diska3','/devices/diska4',
failgroup controller2
disk '/devices/diskb1', '/devices/diskb2', '/devices/diskb3', '/devices/diskb4';

create database orcl
controlfile reuse
logfile '+dgroup1/redo1' size 100M
datafile '+dgroup1/system' size 1G
sysaux datafile '+dgroup1/sysaux' size 2G
default temporary tablespace temp
tempfile '+dgroup1/temp' size 8G
undo tablespace undo
datafile '+dgroup1/rollback' size 8G;

db_unique_name=pridb
log_archive_config='DG_CONFIG=(pridb,stbdb)'
log_archive_dest_1='LOCATION=/arc/archive/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pridb'
log_archive_dest_state_1=ENABLE
log_archive_dest_2='SERVICE=standby_test VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stb_db'
log_archive_dest_state_2=ENABLE
SQL>alter database add standby logfile
group 4 ('/u01/oracle/oradata/tbdb/stdy_redo04.log') size 102400K,
group 5 ('/u01/oracle/oradata/tbdb/stdy_redo05.log') size 102400K,
group 6 ('/u01/oracle/oradata/tbdb/stdy_redo06.log') size 102400K,
group 7 ('/u01/oracle/oradata/tbdb/stdy_redo07.log') size 102400K;

$more removearchive.sh
#!/usr/bin/ksh
# creator: piner
# function: clear archive log of standby database if it had applied
# usage: crontab on standby
# last modify: 2007-10-10 create
#env variable
export ORACLE_HOME=/u01/oracle/product/9.2
export HOME=/home/oracle
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=test
#local variable
BDUMP=/u01/oracle/admin/${ORACLE_SID}/bdump
ALERT=$BDUMP/alert_${ORACLE_SID}.log
ALERTBAK=$BDUMP/alert_${ORACLE_SID}.log.bak
RMARC=$HOME/worksh/rmarchlog.sh
TMLOG=$HOME/logs/logtmp.log
#get rm archivelog script，thread 1
grep "Media Recovery Log" $ALERT|grep 1_|sed '$d'|awk '{print $4}'|sed -e 's/^/rm /' > $RMARC
grep "Media Recovery Log" $ALERT|grep 1_|sed -n '$p' > $TMLOG
#if rac env,then thread 2
#grep "Media Recovery Log" $ALERT|grep 2_|sed '$d'|awk '{print $4}'|sed -e 's/^/rm /' >> $RMARC
#grep "Media Recovery Log" $ALERT|grep 2_|sed -n '$p' >> $TMLOG
#start rm archive log
ksh $RMARC
#clear alert log
cat $ALERT >> $ALERTBAK
cat $TMLOG > $ALERT
#rm tmp file
rm -f $RMARC
rm -f $TMLOG
echo "DB done"
ARCHIVE_DIR=/arc/archive/test
#find $ ARCHIVE_DIR -name "*.arc" -ctime +3 -exec rm {} \;

#!/bin/ksh
# creator: piner
# function: check standby archive send and applied
# usage: crontab on standby
# last modify: piner 2005-4-20 create
#environment variable
export ORACLE_SID=test
export ORACLE_HOME=/u01/oracle/product/9.2
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=american_america.zhs16gbk
#local variable
LOGFILE=/home/oracle/logs/logstatus.log
SQLPLUS=$ORACLE_HOME/bin/sqlplus
DATE=`date +%Y-%m-%d:%H:%M:%S`
dbname=`hostname`
#monitor value
appdoff=0.5
sendoff=0.02
#check database status
$SQLPLUS /nolog <<EOF
connect / as sysdba
spool $LOGFILE
SELECT status FROM v\$instance;
spool off
exit
EOF
sleep 1
if [ -f $LOGFILE ] ; then
STATUS=`egrep -i "(OPEN|MOUNT)" $LOGFILE|wc -l`
echo $STATUS
else
touch $LOGFILE
echo "log file not exists" > $LOGFILE
fi
if [ $STATUS -eq 0 ] ; then
mesglog="$dbname standby status error,db not mount or open!"
##Standby没有open或者是mount
##发送邮件，关于发送邮件的方法，参考本书第16章
exit 1
fi
#check archive log applied
$SQLPLUS /nolog <<EOF
connect / as sysdba
spool $LOGFILE
select decode(Sign(max(Next_time) - (sysdate - $appdoff)),-1,'false','true') from v\$archived_log t
where applied = 'YES' and first_time >sysdate-1;
select thread#,sequence#, to_char(next_time,'yyyy-mm-dd hh24:mi:ss') log_time,applied from v\$archived_log t
where applied = 'NO' and first_time>sysdate-1
order by sequence#;
spool off
exit
EOF
APPSTATUS=`grep -i "false" $LOGFILE|grep -v "'false','true'"|wc -l`
if [ $APPSTATUS -ge 1 ] ; then
##应用日志错误
##发送邮件，关于发送邮件的方法，参考本书第16章
fi
#check archive log sended
$SQLPLUS /nolog <<EOF
connect / as sysdba
spool $LOGFILE
select decode(Sign(max(next_time) - (sysdate - $sendoff)),-1,'false','true') from v\$archived_log t
where first_time>sysdate-1;
select max(sequbnce#) max_sequbnce#,to_char(max(next_time),'yyyy-mm-dd hh24:mi:ss') log_time from v\$archived_log t
where first_time>sysdate-1;
spool off
exit
EOF
SENDSTATUS=`grep -i "false" $LOGFILE|grep -v "'false','true'"|wc -l`
if [ $SENDSTATUS -ge 1 ] ; then
##发送日志错误
##发送邮件，关于发送邮件的方法，参考本书第16章
fi

LISTENER_PRI =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_db)(PORT = 1521))
)
)
)
SID_LIST_LISTENER_PRI =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /u01/oracle/product/9.2)
(SID_NAME = test)
)
)
LISTENER_STB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby_db)(PORT = 1522))
)
)
)
SID_LIST_LISTENER_STB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /u01/oracle/product/9.2)
(SID_NAME = test)
)
)
test =
(DESCRIPTION =
(failover = on )
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_db)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = standby_db)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
)
)

SQL>create tablespace streams_tbs datafile 'file_name' size 500M;
SQL> create user strmadmin identified by password
2 default tablespace streams_tbs
3 quota unlimited on streams_tbs;
SQL> grant dba to strmadmin;
SQL> connect strmadmin/password
SQL> create database link dbdest.net connect to strmadmin
2 identified by password using 'dest_db';
SQL> connect strmadmin/password
SQL> create database link dbsour.net connect to strmadmin
2 identified by password using 'source_db';

SQL>connect strmadmin/test
SQL> declare
2 empty_tbs dbms_streams_talespace_adm.tablespace_set;
3 begin
4 dbms_streams_adm.pre_instantiation_setup(
5 maintain_mode => 'GLOBAL',
6 tablespace_names => empty_tbs,
7 source_database => 'dbsour.net',
8 destination_database => 'dbdest.net',
9 perform_actions => true,
10 bi_directional => true,
11 include_ddl => true,
12 start_processes => true,
13 exclude_schemas => NULL,
14 exclude_flags => dbms_streams_adm.exclude_flags_unsupported +
15 dbms_streams_adm.exclude_flags_dml +
16 dbms_streams_adm.exclude_flags_ddl);
17 end;
18 /

SQL> set serveroutput on size 1000000
SQL> declare
2 until_scn number;
3 begin
4 until_scn:= dbma_flashback.get_system_change_number;
5 dbms_output.put_line('Until SCN: ' || until_scn);
6 end;
7 /
RMAN> run
2> {
3> set until scn 533910;
4> duplicate target database to dbdest nofilenamecheck open restricted;
5> }

SQL>DECLARE
2 empty_tbs dbms_streams_tablespace_adm.tablespace_set;
3 BEGIN
4 dbms_streams_adm.post_instantiation_setup (
5 maintain_mode => 'GLOBAL',
6 tablespace_names => empty_tbs,
7 source_database => 'dbsour.net',
8 destination_database => 'dbdest.net',
9 perform_actions => true,
10 bi_directional => true,
11 include_ddl => true,
12 start_processes => true,
13 instantiation_scn => 533909,
14 exclude_schemas => NULL,
15 exclude_flags => dbms_streams_adm.exclude_flasgs_unsupported +
16 dbms_streams_adm.exclude_flags_dml +
17 dbms_streams_adm.exclude_flags_ddl);
18 end;
19 /

STRMADM@dest>begin
2 dbms_streams_adm.maintain_global(
3 source_directory_object => 'DIR_SOURCE',
4 destination_directory_object => 'DIR_DEST',
5 source_database => 'dbsour.test',
6 destination_database => 'dbdest.test',
7 perform_actions => true,
8 dump_file_name => 'streams_rep.dmp',
9 bi_directional => false,
10 include_ddl => true,
11 instantiation => dbms_streams_adm.instantiation_full);
12 end;
13 /

declare
tables dbms_utility.uncl_apply;
begin
tables(1) := 'piner.test1';
tables(2) := 'piner.test2';
tables(3) := 'piner.test3';
tables(4) := 'piner.test4';
dbms_streams_adm.maintain_tables(
table_names => tables,
source_directory_object => NULL,
destination_directory_object => NULL,
source_database => 'dbsour.net',
destination_database => 'dbdest.net',
perform_actions => false,
script_name => 'configure_rep.sql',
script_directory_object => 'SCRIPT_DIRECTORY',
bi_directional => false,
include_ddl => false,
instantiation => dbms_streams_adm.instantiation_table_network );
end;
/

SQL>begin
2 dbms_capture_adm.set_parameter(
3 capture_name => 'PINER$CAP',
4 parameter => 'downstream_real_time_mine',
5 value => 'y');
6 end;
7 /

SQL>col forward_block heading 'Forward Block' format A50
SQL>col forward_block_dblink heading 'Forward Block|Database Link' format A13
SQL>col status heading 'Status' format A12
SQL>set long 10000
SQL>select forward_block,forward_block_dblink,status
2 from dba_recoverable_script_blocks
3 where script_id = '39EDB8E5AB6CD5C3E040010A3D0552BE'
4 and block_num = 7;
SQL>begin
2 dbms_streams_adm.recover_operation(
3 script_id => '39EDB8E5AB6CD5C3E040010A3D0552BE',
4 operation_mode => 'FORWARD');
5 end;
6 /

begin
dbms_propagation_sdm.start_ propagation (
propagation_name => 'PROPAGATION$_195');
end;
/
begin
dbms_apply_sdm.set_parameter(apply_name=>'APPLY$_DBSOUR_187',
parameter=>'disable_on_error',
value=>'N');
end;
/
exec dbms_apply_sdm.start_apply(apply_name=>'APPLY$_DBSOUR_187');

exec dbms_capture_adm.stop_capture(capture_name);
exec dbms_capture_adm.drop_capture(capture_name);
exec dbms_propagation_adm.stop_propagation(propagation_name);
exec dbms_propagation_adm.drop_propagation(propagation_name);
exec dbms_apply_adm.stop_apply(apply_name);
exec dbms_apply_adm.drop_apply(apply_name);
exec dbms_streams_adm.remove_streams_configuration;

Piner@10gR2> select versions_starttime, versions_endtime, versions_xid,
2 versions_operation, a,b
3 from test versions between timestamp minvalue and maxvalue
4 order by versions_starttime;

13:25:41 Piner@10gR2>select versions_xid, versions_operation
13:25:42 2 from test versions between timestamp
13:25:42 3 to_date('2007-08-27 13:21:56','yyyy-mm-dd hh24:mi:ss')
13:25:42 4 and MAXVALUE
13:25:42 5 WHERE versions_xid is not null
13:25:42 6 order by VERSIONS_STARTTIME;

Piner@9iR2>exec sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_9.arc',options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
Piner@9iR2>exec sys.dbms_logmnr.start_logmnr(Options => sys.dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
Piner@9iR2>exec sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_10.arc');
Piner@9iR2>select t.SCN,t.TIMESTAMP,t.SEG_OWNER,t.OPERATION,
2 t.SQL_REDO,t.SQL_UNDO
3 from v$logmnr_contents t where t.SEG_NAME='TEST';

declare
mysql varchar2(4000);
num number :=0;
begin
for c_tmp in (select sql_undo from logmnr_contents where operation = 'DELETE') loop
--去掉语句结尾的分号，如果语句中本身有分号，则不能采用这个方法。
mysql := replace(c_tmp,sql_undo,';','');
execute immediate mysql;
num := num + 1;
if mod(num,1000)=0 then
commit;
end if;
end loop;
commit;
exception
when others then
--异常处理
end;

SQL>select name,sequence#,dictionary_begin d_beg, dictionary_end d_end
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log
where dictionary_end = 'YES' and sequence# <= 210);--或者是>=210
SQL> select name,sequence#,dictionary_begin d_beg, dictionary_end d_end
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log
where dictionary_begin = 'YES' and sequence# <= 208);
exec dbms_logmnr.add_logfile(logfilename => '/usr/oracle/data/db1arch_1_210_482701534.dbf',options => dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename => '/usr/oracle/data/db1arch_1_208_482701534.dbf');
exec dbms_logmnr.add_logfile(logfilename => '/usr/oracle/data/db1arch_1_207_482701534.dbf');
exec dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_onlinr_catalog + dbms_logmnr.committed_data_only);
SQL>alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
exec dbms_logmnr.start_logmnr(dictfilename => '/oracle/database/dictionary.ora',
starttime => '2007-04-11 08:30:00',
endtime => '2007-04-11 08:45:00');

SQL> declare i integer;
2 begin
3 for i in 1..100 loop
4 insert into test values(i,100-i);
5 end loop;
6 commit;
7 end;
8 /
SQL> create or replace trigger tr_test
2 before insert or update or delete on test
3 for each row
4 declare
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 begin
7 update audit_test set c=c+1;
8 commit;
9 end;
10 /

$more backup_exp.sh
#!/bin/sh
# creator: piner
# function: backup database with expdp
# usage: crontab on linux
# last modify: tuolei 2007-08-29 create
#set environment variable
export ORACLE_SID=test
export ORACLE_HOME=/u01/oracle/product/9.2
export NLS_LANG=american_america.zhs16gbk
#开始备份，假定目录dump_test已经创建，并且dump_test=/u01/oracle/backup
FILE=`date date +%Y%m%d`
expdp user/pass directory=dump_test dumpfile=$FILE.dmp
#删除以前过期的备份
DUMP=/u01/oracle/backup
find $DUMP -name "*.dmp" -mtime +7 -exec rm {} \;

[oracle@db worksh]$ more rmanback.sh
#!/bin/ksh
#环境变量
export ORACLE_HOME=/opt/oracle/product/9.2
export ORACLE_SID=test
export NLS_LANG="AMERICAN_AMERICA.zhs16gbk"
export PATH=$PATH:$ORACLE_HOME/bin
#备份脚本
echo "-----------------------------start-----------------------------";date
$ORACLE_HOME/bin/rman <<EOF
connect target
delete noprompt obsolete;
backup database format '/netappdata1/rmanback/tbdb2/%U_%s.bak' filesperset = 4;
exit;
EOF
echo "------------------------------end------------------------------";date

run{
allocate channel node_c1 device type disk connect 'sys/pass@dbin1';
allocate channel node_c2 device type disk connect 'sys/pass@dbin2';
sql 'alter system archive log thread 2 current';
sql 'alter system archive log thread 1 current';
backup archivelog all delete input format '/u01/dbbak/%U_%s.bak' filesperset = 10;
}

RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> set until time = '2007-11-09:11:44:00';
5> restore database;
6> recover database;
7> alter database open resetlogs; }
RMAN> run {
2> set until sequence 120 thread 1;
3> alter database mount;
4> restore database;
5> recover database; # 恢复到日志119
6> alter database open resetlogs;
7> }
RMAN> run {
2>set newname for datafile
3>'/u01/oradata/tools01.dbf' to '/tmp/tools01.dbf';
4>restore datafile '/u01/oradata/tools01.dbf';
5>switch datafile all;
6>}

#!/bin/sh
# creator: Piner
# function: backup database or archive with rman
# usage: crontab on linux
# last modify: piner 2007-08-29 create
#环境变量
export ORACLE_SID=test
export ORACLE_HOME=/u01/oracle/product/9.2
export NLS_LANG=american_america.zhs16gbk
#运行变量
RMAN=$ORACLE_HOME/bin/rman
SQLPLUS=$ORACLE_HOME/bin/sqlplus
TEE=/usr/bin/tee
LOGFILE=/home/oracle/logs/rmanback.log
#目录结构为类似这样：/back1/rmanbak/test/week1
DBDEST1=/back1/rmanbak/${ORACLE_SID}/week`date +%w`
DBDEST2=/back2/rmanbak/${ORACLE_SID}/week`date +%w`
CTLFILE=/back1/rmanbak/${ORACLE_SID}/week`date +%w`/${ORACLE_SID}_ctl_`date +%Y%m%d%H%M`.ctl
#开始运行RMAN来备份数据库与归档日志，并记录备份的日志文件
echo "----------------backup start----------------------" > $LOGFILE
$RMAN <<EOF | $TEE -a $LOGFILE
connect target
#分配多通道备份，只备份数据库
Run{
allocate channel c1 type disk format '$DBDEST1/%U_%s.bak' maxpiecesize = 2000M;
allocate channel c2 type disk format '$DBDEST2/%U_%s.bak' maxpiecesize = 2000M;
backup database filesperset = 4;
}
#保留两次有效备份
delete noprompt obsolete redundancy = 2 device type disk;
exit;
EOF
#备份控制文件
$SQLPLUS "/ as sysdba" <<EOF | $TEE -a $LOGFILE
alter database backup controlfile to '$CTLFILE';
exit
EOF
echo "-----------------backup end-----------------------" >> $LOGFILE

RMAN> allocate channel c1 device type sbt
2> parms='env=(nsr_server=tape_srv,
3> nsr_group=Oracle_tapes)';
相关的数据库与归档日志备份脚本如下：
#全备份脚本
backup tag 'full'
database filesperset = 4
plus archivelog delete input filesperset = 10;
#零级备份脚本
backup incremental level 0 tag 'db0'
database filesperset = 4
database skip readonly
plus archivelog delete input filesperset = 10;
#一级备份脚本
backup incremental level 1 tag 'db1'
database filesperset = 4
database skip readonly
plus archivelog delete input filesperset = 10;

RMAN> connect target sys/password@prod #源数据库
RMAN>connect auxiliary sys/password@aux #辅助数据库
connected to target database: TEST (DBID=290922305)
connected to auxiliary database: TEST (not mounted)
RMAN>RUN{
allocate auxiliary channel c1 type disk;
recover tablespace 'USERS' until time "to_date('2007-10-22 15:48:17' , 'yyyy-mm-dd
hh24:mi:ss')" AUXILIARY DESTINATION '/u01/oracle/new/';
sql 'alter tablespace USERS online';
}

RMAN>duplicate target database
to dupdb
password file
spfile
from active database
db_file_name_convert '/u01/Oracle/oradata/','/u02/Oracle/oradata/'
parameter_value_convert '/u01/oracle/pfile/','/u02/Oracle/oradata/'
set sga_max_size '300M'
set sga_target '250M'
set log_file_name_convert '/u01/oracle/redo/','/u02/oracle/redo/';
run{
set newname for datefile 1 to '/u01/oradatd1/system01.dbf';
set newname for datefile 2 to '/u01/oradata2/undotbs01.dbf';
…….
duplicate ……
}
duplicate
……
parameter_values_convert '/u01/oracle/pfile/', '+DISK1'
set db_create_file_dest '+DISK1';

SQL> begin
2 dbms_streams_adm.maintain_schemas(
3 schema_names => 'piner',
4 source_directory_object => null,
5 destination_directory_object => null,
6 source_database => 'sour.net',
7 destination_database => 'dest.net',
8 perform_actions => true,
9 bi_directional => false,
10 include_ddl => true,
11 instantiation => dbms_streams_adm.instantiation_schema_network);
12 end;
13 /

SQL>create table table_name as select * from table_name@db_link where 1=0;
SQL> alter table table_name
2 add constraint constraint_name primary key [unique] (field_name);
SQL>create materialized view table_name on prebuilt table refresh fast as
2 select * from table_name@db_link;
SQL>exec dbms_mview.refresh('table_name',method => 'Complete');--全刷新
SQL>exec dbms_mview.refresh('table_name');--快速刷新
SQL>create index index_name on table_name(field_name);
SQL>declare jobid number;
SQL>begin
2 sys.dbms_job.submit(job => jobid,
3 what => 'dbms_mview.refresh(''table_name'');',
4 next_date => sysdate,
5 interval => 'sysdate+1/1440');
6 commit;
7 end;
8/

create table rep_table_logs
(
Rep_key number not null,
Dml_type varchar2(1) not null,
Dml_time date default sysdate not null,
Rep_flag number default 0 not null
)

create or replace trigger tr_table_repl
after insert or update or delete
on table_name
for each row
/**********************************************************
*create date: *
*creator:piner *
*function description: *
* *
*modify history: *
***********************************************************/
declare
m_dml_type varchar2(1);
m_rep_id number;
errormsg varchar2(500);
begin
if inserting then
m_dml_type := 'I';
m_rep_id := :new.id;
elsif updating then
if :new.id != :old.id then
raise_application_error(-20001,'You can not modify primary key');
end if;
m_dml_type := 'U';
m_rep_id := :old.id;
elsif deleting then
m_dml_type := 'D';
m_rep_id := :old.id;
else
m_dml_type := 'X';
m_rep_id := -1;
end if;
insert into rep_table_logs(rep_key,dml_type,dml_time)
values(m_rep_id,m_dml_type,sysdate);
exception
when others then
raise;
end tr_table_repl;

create or replace procedure sp_table_repl
is
--复制参数
m_rep_flag number := 1;
m_exc_flag number := 2;
m_sp_name varchar2(10) := 'rep_table';
--过程参数
end_stamp date;
num_row number;
errormsg varchar2(256);
pages_nums number;
per_pages number := 10000;
begin
/***************************************************************************
*create date: *
*creator: piner *
*function: *
* *
*modify history: *
* *
*注意，该过程加入了并发控制，保证不能被并发运行，但是，该过程在运行的过程中*
*不能被异常kill，如果被kill了，需要手工修改该标志从runing到stop。 *
****************************************************************************/
--处理标志，为了保证过程不被并发执行
update rep_table_flag f set f.run_flag='runing',f.sp_time=sysdate
where f.run_flag='stop' and f.sp_type = m_sp_name;
num_row := sql%rowcount;
commit;
--更新到0行，表示有过程正在执行，该进程返回，不再执行
if num_row = 0 then
return;
end if;
---------------------------------------------------------------------
----------------------start------------------------------------------
--未处理日志数
select count(*) into num_row
from rep_table_logs_syn t
where t.rep_flag = 0;
pages_nums := trunc(num_row/per_pages) + 1;
--分页处理。每次处理一个per_pages的数目
for pages in 1 .. pages_nums loop
select max(dml_time) into end_stamp from (
select dml_time from rep_table_logs_syn
where rep_flag = 0
order by dml_time)
where rownum <= per_pages;
--把这部分正在处理的日志，标记置为m_exc_flag，表示正在处理
update rep_table_logs_syn set rep_flag = m_exc_flag
where rep_flag = 0
and dml_time < end_stamp;
commit;
-----------------------------------------------------------------
-------------------------delete----------------------------------
--处理delete的记录
delete /*+ ordered use_nl(l) */ from rep_table u where id in
(select rep_key from rep_table_logs_syn l
where l.dml_type = 'D'
and l.rep_flag = m_exc_flag);
--更新处理过的标志位，注意，这里包含了insert,update,delete的所有日志
--因为如果该记录有删除日志存在，它的insert update是没有必要做的
update rep_table_logs_syn t set t.rep_flag = t.rep_flag + m_rep_flag
where t.rep_key in
(select rep_key from rep_table_logs_syn l
where l.dml_type = 'D'
and l.rep_flag = m_exc_flag);
commit;
---------------------------------------------------------------------
-------------------------insert--------------------------------------
--处理insert的记录，查看要插入的日志在目标表中是否已经存在
--一般情况下应当是不存在的，除了开始取数据可能存在的合并区
select /*+ ordered use_nl(l,u) */ count(*) into num_row
from rep_table_logs_syn l,
rep_table u
where l.rep_key = u.id
and l.dml_type = 'I'
and l.rep_flag = m_exc_flag;
--如果发现要更新的记录存在于目标表中，则删除该记录
if num_row > 0 then
delete from rep_table where id in
(select /*+ ordered use_nl(l,u) */ l.rep_key
from rep_table_logs_syn l,
rep_table u
where l.rep_key = u.id
and l.dml_type = 'I'
and l.rep_flag = m_exc_flag);
end if;
--把需要同步的数据插入到目标表中
insert into rep_table
select /*+ ordered use_nl(l,u) */ u.*
from rep_table_logs_syn l,
syn_rep_table_db1 u
where l.rep_key = u.id
and l.dml_type = 'I'
and l.rep_flag = m_exc_flag;
--更新insert的标志位
update rep_table_logs_syn l set rep_flag = rep_flag + m_rep_flag
where dml_type = 'I'
and rep_flag = m_exc_flag;
commit;
---------------------------------------------------------------------
-------------------------update--------------------------------------
--处理update的记录
--处理重复记录，其实多个update只需要同步一次即可
update rep_table_logs_syn set rep_flag = rep_flag + m_rep_flag
where rowid in
(select rid from (
select rowid rid,row_number() over(partition by rep_key
order by rowid) rn
from rep_table_logs_syn
where rep_flag = m_exc_flag
and dml_type = 'U')
where rn >= 2);
commit;
--更新，先插入到临时表
insert into rep_table_temp
select /*+ ordered use_nl(l,u) */ u.*
from rep_table_logs_syn l,
syn_rep_table_db1 u
where l.rep_key = u.id
and l.dml_type = 'U'
and l.rep_flag = m_exc_flag;
--从临时表更新数据
update /*+ ordered use_nl(u) */ rep_table t
set (field1,field2...fieldn)
= (select * from rep_table_temp u
where u.id=t.id)
where exists
(select null from rep_table_temp tt
where t.id = tt.id);
--更新update的标志位
update rep_table_logs_syn l set rep_flag = rep_flag + m_rep_flag
where dml_type = 'U'
and rep_flag = m_exc_flag;
commit;
----------------------当前页全部更新完毕----------------------------
--更新时间戳
update rep_table_flag f set f.sp_time=sysdate
where f.sp_type = m_sp_name;
commit;
------------------------------------------------------------------
-------------------------end loop---------------------------------
end loop;
--删除处理完的日志
delete from rep_table_logs_syn where rep_flag = m_rep_flag + m_exc_flag;
--做完标志
update rep_table_flag f set f.run_flag='stop',f.sp_time=sysdate
where f.sp_type = m_sp_name;
commit;
exception
when others then
--如果失败，则回滚
rollback;
--提交错误信息
errormsg := sqlerrm;
--做完标志
update rep_table_flag f set f.run_flag='stop',f.sp_time=sysdate
where f.sp_type = m_sp_name;
insert into rep_table_sp_errors(error_id,error_msg,error_time)
values(m_sp_name,errormsg,sysdate);
commit;
raise;
end sp_table_repl;

#!/usr/bin/perl
###########################################################################
#creator: #
#create date: #
#function: #
# #
#modify history: #
# #
###########################################################################
use strict;
use warnings;
use DBI;
#$OUTPUT_AUTOFLUSH = false
$|=1;
if (my $pid = open(CHILD,"-|")) {
#parent process
#environment variable for oracle nls_lang
$ENV{"NLS_LANG"} = 'AMERICAN_AMERICA.US7ASCII';
#这里连接远程数据库
#user/pass@dbname
my $dbname1="test";
my $user1="test";
my $passwd1="test";
my $count=0;
#connect oracle database
my $dbh1 = DBI->connect("dbi:Oracle:$dbname1",$user1,$passwd1) or die ("can't connect
to oracle database ",$DBI::errstr);
#这里采用了进程间通信的方式，这里是父进程
#父进程把从子进程读到的信息，按照"::"作为分界符的方法，切换成多个字段的记录
#这个分界符是可以自定义的
#read child process information
while (<CHILD>){
my @newrec = split(/::/,"$_");
chomp(@newrec);
my $sqls = "insert into test(id,name) values('$newrec[0]','$newrec[1]')";
my $rows = $dbh1 -> do($sqls);
$count += $rows;
}
close(CHILD) or die("Cannot close:$!");
$dbh1->disconnect();
print "inert rows:$count.ok\n";
}
elsif(defined($pid)){
#child process
#这里是子进程，可以拥有跟父进程不一样的字符集环境变量
#environment variable for oracle nls_lang
$ENV{"NLS_LANG"} = 'AMERICAN_AMERICA.ZHS16GBK';
#这里连接字符集不同的另外一个数据库
#user/pass@dbname
my $dbname2="";
my $user2="test";
my $passwd2="test";
#connect oracle database
my $dbh2 = DBI->connect("dbi:Oracle:$dbname2",$user2,$passwd2) or die ("can't connect
to oracle database ",$DBI::errstr);
#query sql
my $sth=$dbh2->prepare("select * from test");
#execute sql
my $rc=$sth->execute;
my @recs;
#get row data and send to parent process
#把记录拼合在一起，用"::"来分隔记录
#然后把分隔好的记录传递给父进程
while(@recs = $sth -> fetchrow_array()){
print $recs[0]."::".$recs[1]."\n";
}
$dbh2->disconnect();
$sth->finish();
#exit child process
exit();
}
else{
print "could not fork process\n";
}

### $dbh1->{AutoCommit} = 0;
### $dbh1->{RaiseError} = 1;
### #data
### my @col1;
### my @col2;
### #read child process information
### while (<CHILD>){
### my @newrec = split(/::/,"$_");
### chomp(@newrec);
### $col1[$count] = $newrec[0];
### $col2[$count] = $newrec[1];
### $count++;
### }
###
### my (@rowstats, $rv);
### my $sth1 = $dbh1->prepare(
### "insert into test(TNAME,TABTYPE) values (?, ?)");
### $sth1->bind_param_array(1, \@col1);
### $sth1->bind_param_array(2, \@col2);
### $rv = $sth1->execute_array(
### {ArrayTupleStatus => \@rowstats});
###
### close(CHILD) or die("Cannot close:$!");
### $dbh1->disconnect();
### print "inert rows:$rv,ok\n";

RMAN>convert datafile
2> '/home/ora10g/data_user01.dbf'
3> to platform='AIX-Based Systems (64-bit)'
4> from platfrom='Linux IA (32-bit)'
5> db_file_name_convert='/home/ora10g/','/u01/oracle/oradata/test/';

$ more test.ctl
Load data
insert
into table test
Fields terminated by "," Optionally enclosed by "'"
(id,
name,
SQL>create_date date "yyyy-mm-dd hh24:mi:ss" nullif(create_date="NULL"))
$ sqlldr userid=piner/xxxxxx control=test.ctl data=test.dat

Piner@9iR2>create table ext_test
2 (employee_id NUMBER(4),
3 name VARCHAR2(20),
4 job_id VARCHAR2(10),
5 hire_date DATE,
6 salary NUMBER(8,2),
7 email VARCHAR2(25)
8 )
9 organization external
10 (
11 type oracle_loader
12 default directory dump_dir
13 access parameters
14 (
15 records delimited by newline
16 badfile dump_dir:'test%a_%p.bad'
17 logfile dump_dir:'test%a_%p.log'
18 fields terminated by ',' optionally enclosed by '"'
19 missing field values are null
20 ( employee_id,name, job_id,
21 hire_date char date_format date mask "yyyy-mm-dd",
22 salary, email
23 )
24 )
25 location ('test1.dat', 'test2.dat')
26 )
27 parallel
28 reject limit unlimited;
Table created.
Piner@10gR2>create table ext_test
organization external
(
type oracle_datapump
default directory dump_dir
location ('test.dmp')
)
as
select * from test;
Piner@10gR2>create table ext_test
(field1 number,
field2 varchar2(100)
)
organization external
(type oracle_datapump
default directory dump_dir
location('test.dmp'));

SELECT D.TABLESPACE_NAME,SPACE SUM_SPACE,
SPACE-NVL(FREE_SPACE,0) USED_SPACE,
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) USED_RATE,
FREE_SPACE FREE_SPACE
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

$more statpack.sh
#!/bin/ksh
# creator: piner
# function:produce statpack snapshot
date
#设置环境变量
export ORACLE_SID=test
export ORACLE_HOME=/u01/oracle/product/9.2
export NLS_LANG=american_america.zhs16gbk
#收集快照点
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect perfstat/perfstat
exec statspack.snap
exit
EOF
$more spreport.sh
#!/bin/ksh
# creator: piner
# function:get statpack report
date
#设置环境变量
export ORACLE_SID=test
export ORACLE_HOME=/u01/oracle/product/9.2
export NLS_LANG=american_america.zhs16gbk
#设置运行时变量
SQLPLUS=$ORACLE_HOME/bin/sqlplus
LOGFILE=/home/oracle/logs/spreport.txt #日志文件
REPFILE=/home/oracle/worksh/spreport.lst #报表文件
#获得最后2个快照点
$SQLPLUS -S perfstat/perfstat <<EOF
set echo off
set feedback off
set heading off
set pagesize 0
set linesize 1000
set trimspool on
spool $LOGFILE
select snap_id from
(select snap_id from stats\$snapshot where instance_number=1
order by snap_time desc) where rownum<3;
spool off;
set echo on
set feedback on
set heading on
exit
EOF
line1=`tail -1 $LOGFILE`
line2=`head -1 $LOGFILE`
echo "line1 is"$line1
echo "line2 is"$line2
#产生报表
rm $REPFILE
$SQLPLUS -S perfstat/perfstat <<EOF
define begin_snap=$line1
define end_snap=$line2
define report_name=$REPFILE
@?/rdbms/admin/spreport.sql
exit
EOF
#发送邮件
mail -s `date +%Y%m%d`'-'`hostname`spreport test@mail.com < $REPFILE

SQL> select to_char(snap_time,'yyyy-mm-dd hh24') time,
2 round(sum(read/1024)) "write(kb)",round(sum(write/1024)) "read(kb)" from
3 (select sn.snap_time snap_time,(newr.value-oldr.value) read,
4 (neww.value-oldw.value) write
5 from stats$sysstat newr,
6 stats$sysstat oldr,
7 stats$sysstat neww,
8 stats$sysstat oldw,
9 stats$snapshot sn
10 where sn.snap_id=newr.snap_id
11 and sn.snap_id=neww.snap_id
12 and sn.snap_id-1=oldr.snap_id
13 and sn.snap_id-1=oldw.snap_id
14 and newr.name='physical reads'
15 and oldr.name='physical reads'
16 and neww.name='physical writes'
17 and oldw.name='physical writes'
18 ) v
19 group by to_char(snap_time,'yyyy-mm-dd hh24');

select * from (
select rownum rn,object_id,object_name,subobject_name,status from
(select object_id,object_name,subobject_name,status
from test
where owner='SYS'
and object_type='TABLE'
order by created desc)
where rownum <= m) t
where rn >=n
Piner@10g R2>create index ind_test on test(owner,object_type,created);
Index created.
Piner@10g R2>select * from (
2 select rownum rn,object_id,object_name,subobject_name,status from
3 (select object_id,object_name,subobject_name,status
4 from test
5 where owner='SYS'
6 and object_type='TABLE'
7 order by created desc)
8 where rownum <= 50) t
9 where rn >=1;

Piner@10g R2>select /*+ ordered use_nl(t,test) */
2 object_id,object_name,subobject_name,status
3 from (select rid from (
4 select rownum rn,rid from (
5 select rowid rid from test
6 where owner='SYS'
7 and object_type='TABLE'
8 order by created desc)
9 where rownum <= 500)
10 where rn >=451) t,
11 test
12 where t.rid=test.rowid;

SQL> create or replace type numtabletype as table of number;
SQL>create or replace type vartabletype as table of varchar2(1000);
SQL>create or replace function str2numList( p_string in varchar2 ) return numTableType
as
v_str long default p_string || ',';
v_n number;
v_data numTableType := numTableType();
begin
loop
v_n := to_number(instr( v_str, ',' ));
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr( v_str, v_n+1 );
end loop;
return v_data;
end;
SQL>create or replace function str2varList( p_string in varchar2 ) return VarTableType
as
v_str long default p_string || ',';
v_n varchar2(2000);
v_data VarTableType := VarTableType();
begin
loop
v_n :=instr( v_str, ',' );
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr( v_str, v_n+1 );
end loop;
return v_data;
end;
Select /*+ ordered use_nl(a,u) */ id, name,nick
from table(STR2NUMLIST(:bind0)) a,
users u
where u.id = a.column_value;
Select /*+ leading(a) */ id, name,nick
from users u
where id in
(select * from table(STR2NUMLIST(:bind0)) a);
Select /*+ index(u ind_users_id) */ id, name,nick
from users u
where id in
(select * from the (select cast(STR2NUMLIST(:bind0) AS NUMTABLETYPE)
from dual) where rownum<1000);

declare
resource_busy exception;
pragma exception_init(resource_busy, -54);
begin
loop
begin
execute immediate 'alter index ...... noparallel';
exit;
exception
when resource_busy then
dbms_lock.sleep(1);
end;
end loop;
end;

Piner@Ora9R2> create or replace procedure sp_test is
num number;
begin
runstats_pgk.rs_start;
for i in 1..1000 loop
execute immediate 'select b from test where a=:x' using i;
end loop;
runstats_pgk.rs_middle;
for i in 1..1000 loop
execute immediate 'select b from test where a='||i;
end loop;
runstats_pgk.rs_stop(100);
end sp_test;
/

SQL 10G>col OBJECT_NAME format a20;
SQL 10G>col USER_NAME format a20;
SQL 10G>select /*+ rule */ lpad('--',decode(b.BLOCK,1,0,4))||s.username user_name,
2 b.TYPE,o.owner||'.'||o.object_name object_name,
3 s.sid,s.serial#,decode(b.REQUEST,0,'BLOCKED','WAITING') status
4 FROM dba_objects o,v$session s,v$lock v,v$lock b
5 WHERE v.ID1=o.object_id AND v.SID=s.sid
6 and v.SID=b.SID and (b.BLOCK=1 or b.REQUEST>0)
7 and v.TYPE='TM'
8 order by by b.ID2,v.ID1,User_name desc;

SQL 10G> select sql_text
2 from v$sqlarea
3 where (v$sqlarea.address, v$sqlarea.hash_value) in (
4 select sql_address, sql_hash_value
5 from v$session
6 where sid in (
7 select sid
8 from v$session a, x$kglpn b
9 where a.saddr = b.kglpnuse
10 and b.kglpnmod <> 0
11 and b.kglpnhdl IN (select p1raw
12 from v$session_wait
13* where sid=160 and event like 'library%')))

one session other session
------------------------------------ -----------------------------------
T1> SQL>insert into test values()
1 row inserted
T2> SQL>insert into test values()
1 row inserted
T3> SQL> update mystat set user_nums =
(select count(*) from test
where username=?)
where id=?;
1 row updated
T4> SQL> update mystat set user_nums =
(select count(*) from test
where username=?)
where id=?;
1 row updated
T5>SQL> commit;
Commit complete
T6> SQL> commit;
Commit complete
其中时间T1<T2<T3<T5<T5<T6

declare
row_num number := 0;
begin
for c_test in (select id from my_test t where ftype is null) loop
update my_test t set t.ftype = 1 where id = c_test.id;
row_num := row_num + 1;
--1000条提交一次，可根据需要修改
if mod(row_num,1000) =0 then
commit;
end if;
end loop;
commit;
end;/

update my_users u set user_cnt=
(select user_cnt from
(select user_id,count(*) user_cnt from my_test group by user_id) tmp
where tmp.user_id=u.id)
where exists
(select null from my_test t where u.id=t.user_id);
SQL>select tablespace_name,
round(sum(bytes)/(1024*1024),2) free_space
from dba_free_space
where tablespace_name='TBS_UNDO'
group by tablespace_name;
declare
row_num number := 0;
begin
for c_user in (select user_id,user_cnt from tmp_test t where flags = 0) loop
--更新目标表
update my_users t set t.user_cnt = c_user.cnt where id = c_user.user_id;
--更新临时表的标记位
update tmp_test f set f.flags = 1 where user_id = c_user.user_id;
row_num := row_num + 1;
--1000条提交一次
if mod(row_num,1000) =0 then
commit;
end if;
end loop;
commit;
end;

declare
row_num number := 0;
begin
for c_user in (select user_id,user_cnt from tmp_test t
where flags = 0 and rownum <= 50000) loop
update my_users t set t.user_cnt = c_user.cnt where id = c_user.user_id;
update tmp_test f set f.flags = 1 where user_id = c_user.user_id;
row_num := row_num + 1;
if mod(row_num,1000) =0 then
commit;
end if;
end loop;
commit;
end;
begin
for i in 1.. 5 loop
dbms_lock.sleep(300);
--这里嵌入更新数据的循环。
end loop;
end;

declare
row_num number := 0;
begin
for c_user in (select user_id,user_cnt from tmp_test t where flags = 0) loop
--更新备份表，备份需要更新的数据
update tmp_test t set t. user_old_cnt =
(select user_cnt from my_users s where s.id=t.user_id)
where t.user_id = c_user.user_id;
--再更新业务表
update my_users t set t.user_cnt = c_user.cnt where id = c_user.user_id;
update tmp_test t set t.flags = 1 where user_id = c_user.user_id;
row_num := row_num + 1;
if mod(row_num,1000) =0 then
commit;
end if;
end loop;
commit;
end;

declare
row_num number := 0;
begin
for c_test in (select rowed rid from table_name t where field_name is null) loop
update table_name t set t.field_name = 0 where rowid = c_test.rid;
row_num := row_num + 1;
if mod(row_num,1000) =0 then
commit;
end if;
end loop;
commit;
end;

$ more send_dbmail.sh
#!/bin/sh
# creator: Piner
# function:
# usage:send mail
# last modify:
# modifier history:
#这里表示，至少需要有符合条件的三个参数，否则，会提示该脚本的用法
if [ $# -ne 3 ]; then
echo "Usage: `basename $0`(title file group1|group2)"
exit 1
fi
#分组，把需要发送的对象分成不同的组，如组1的人员可能是主机管理者
#组2的人员可能是DB管理者
group1_mail="test1@gmail.com test2@gmail.com"
group2_mail="test2@gmail.com test3@gmail.com test4@gmail.com"
TITLE=$1
MFILE=$2
#send mail procedure
case $3 in
group1)echo "send mail to dba-list"
mail -s "$TITLE" $group1_mail < $MFILE
;;
group2)echo "send mail to dw"
mail -s "$TITLE" $group2_mail < $MFILE
;;
*)echo "Usage: `basename $0`(title file group1|group2)"
;;
esac
#send end
echo "send mail end"

$ more send_dbmobile.sh
#!/bin/sh
# creator: piner
# function:
# usage:send message to mobile
# last modify:
# modifier :
#这里表示至少需要两个参数，信息内容与分组信息
if [ $# -ne 2 ]; then
echo "Usage: `basename $0`(message group1|group2)"
exit 1
fi
#列出每个人的手机号码，仅仅是存在这个脚本中，
#如果有人更换了号码，只需要修改这里即可
user1=138xxxxxxxx
user2=139xxxxxxxx
user3=137xxxxxxxx
user4=130xxxxxxxx
#写一个函数，来包装这个发送过程，这里假定短信网关是一个单独的服务器，
#IP地址是192.168.1.1，服务端口为8080，则可以用wget来调用这个发送短信的接口
#该函数仅仅是在这个脚本中被调用。
sendmobile() {
SURL='sms/index.php?bid=TEST&receiver='
SURL="${SURL}$1"
message=`echo $2 | sed -e 's/ /%20/g'`
SURL="${SURL}&message=$message"
wget -O /dev/null -o /dev/null "http://192.168.1.1:8080/$SURL" > /dev/null 2>&1
echo "succed to mobile"
}
#如果发现接收到的消息是空，则不发送，无意义，并返回失败
if [ -z "$1" ]
then
echo "message is null!"
exit 1
fi
mesglog="$1"
#这里开始发送手机短信，也是采用分组的方式
#把需要发送到的目标按要求分成不同的组，
#到时候，如目标是主机，则可以发送短信到主机组，数据库则可以发送短信到DB组。
#send procedure
case $2 in
group1)echo "send message to group1"
sendmobile "$user1" "$mesglog"
sendmobile "$user2" "$mesglog"
;;
group2)echo "send message to group2"
sendmobile "$user3" "$mesglog"
sendmobile "$user4" "$mesglog"
;;
*)echo "Usage: `basename $0`(message group1|group2)"
;;
esac
#send end
echo "send message to mobile end"

$ more monitor_api.sh
#!/bin/sh
# creator: piner
# function:
# usage:call monitor's interface
# last modify:
# modifier :
#判断参数是否合适
#如果是发送短信，需要传递信息内容与发送组
#如果是发送邮件，需要传递标题，与文件名称与发送组
#如果仅仅是拷贝文件，需要提供源文件名称与目标文件名称
if [ $# -ge 1 ]; then
if [ $1 == 'mobile' ]; then
echo "send mobile message"
if [ $# -ne 3 ]
echo "Usage: `basename $0`(mobile message group)"
exit 1
fi
elif [ $1 == 'mail' ]; then
echo "send mail message"
if [ $# -ne 4 ]
echo "Usage: `basename $0`(mail title group filename)"
exit 1
fi
elif [ $1 == 'scp' ]; then
echo "only scp file to monitor"
if [ $# -ne 3 ]; then
echo "Usage: `basename $0`(scp source_file dest_file)"
exit 1
fi
else
echo "Usage: `basename $0`(mobile message group)"
echo "or Usage: `basename $0`(mail title group filename)"
echo "or Usage: `basename $0`(scp source_file dest_file)"
exit 1
fi
fi
#定义monitor上的脚本位置
SENDMSG= /home/admin/bin/send_dbmobile.sh
SENDDBM= /home/admin/bin/send_dbmail.sh
#选择方式，如果是发送短信
if [ $1 == 'mobile' ]; then
message=$2
group=$3
ssh admin@monitor "$SENDMSG '$message' '$group'"
fi
#选择方式，如果是发送邮件
#注意这里的文件是在monitor上的，是全路径名称
if [ $1 == 'mail' ]; then
title=$2
group=$3
mfile=$5
ssh admin@monitor "$SENDDBM '$title' '$mfile' '$group'"
fi
#选择方式，如果是拷贝文件，注意这里的文件是全路径名称
if [ $1 == 'scp' ]; then
sfile=$2
dfile=$3
scp $sfile admin@monitor:$dfile
fi

$more server_info.sh
#!/bin/ksh
# creator: piner
# function: copy system load and alert log file to monitor
# usage: crontab on host
# last modify:
#get host tag and Oracle sid
dbname=`hostname`
ORACLE_SID=$1
ORACLE_BASE=/u01/Oracle
ALERT=$ORACLE_BASE/admin/${ORACLE_SID}/bdump/alert_${ORACLE_SID}.log
#这里简单地指定monitor接口程序的位置，至于这个接口成本，见本章前面
#在实际情况中，可以把这个信息写到配置文件中
MONITOR=/home/Oracle/bin/monitor_api.sh
#load
uptime|sed 's/.*average://g' > /tmp/${dbname}load.log
#这里调用接口程序，表示把保存系统负载的信息提交到monitor上
$MONITOR "scp" "/tmp/${dbname}load.log" "/home/admin/log/${dbname}load.log"
#alert
#这里表示把数据库的alert文件，拷贝到monitor上
$MONITOR "scp" "$ALERT" "/tmp/${dbname}_alert.log"

$more disk_info.sh
#!/bin/ksh
# creator: piner
# function: copy disk using to monitor
# usage: crontab on host
# last modify:
#get host tag and Oracle sid
dbname=`hostname`
MONITOR=/home/Oracle/bin/monitor_api.sh
#disk spce
df -g|grep -v proc|grep -v Oraclebak|sed 1d > /tmp/${dbname}df.log
#这里调用接口程序，表示把保存磁盘使用率的信息提交到monitor上
$MONITOR "scp" "/tmp/${dbname}df.log" "/home/admin/log/${dbname}df.log"
create or replace procedure sp_getdb_info
( p_tran_nums out number,
p_elps_time out number)
is
m_count number;
m_old_tran_nums number :=0;
m_old_time date;
m_new_tran_nums number :=0;
m_new_time date;
begin
--注意，这里把原来的老数据记录在临时表中，如果临时表还没有初始化
--则需要先初始化临时表
select count(*) into m_count from tmp_db_info;
if m_count = 0 then
insert into tmp_db_info
select s.NAME,s.VALUE,sysdate from v$sysstat s
where s.NAME in
('user commits',
'user rollbacks');
commit;
--如果初始化完成，则直接返回
return;
end if;
--正常情况下，先获得上次执行时的时间值
select gmt_create into m_old_time from tmp_db_info
where rownum <= 1;
--然后得到上次的事务数
for c_tmp in (select * from tmp_db_info) loop
if c_tmp.event_name='user commits' then
m_old_tran_nums := m_old_tran_nums + c_tmp.db_values;
elsif c_tmp.event_name='user rollbacks' then
m_old_tran_nums := m_old_tran_nums + c_tmp.db_values;
else
null;
end if;
end loop;
--然后，删除临时表
delete from tmp_db_info;
--在临时表中写入新的值
insert into tmp_db_info
select s.NAME,s.VALUE,sysdate from v$sysstat s
where s.NAME in
('user commits',
'user rollbacks');
commit;
--得到现在的新值
select gmt_create into m_new_time from tmp_db_info
where rownum <= 1;
for c_tmp in (select * from tmp_db_info) loop
if c_tmp.event_name='user commits' then
m_new_tran_nums := m_new_tran_nums + c_tmp.db_values;
elsif c_tmp.event_name='user rollbacks' then
m_new_tran_nums := m_new_tran_nums + c_tmp.db_values;
else
null;
end if;
end loop;
--得到两次调用之间的时间差
p_elps_time := round((m_new_time - m_old_time)*24*3600,2);
--得到两次调用之间的每秒事务数
p_tran_nums := round((m_new_tran_nums - m_old_tran_nums)/p_elps_time,2);
end;

$ more user_db_info.sh
#!/bin/ksh
date
#Oracle sid，一般作为参数传递
export ORACLE_SID=$1
#Oracle环境变量
export ORACLE_HOME=/u01/Oracle/product/9.2
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=american_america.zhs16gbk
dbname=`hostname`
LOGFILE=/home/Oracle/logs/db_info.log
MONITOR=/home/Oracle/bin/monitor_api.sh
#调用sqlplus，执行存储过程，获得当前数值
$ORACLE_HOME/bin/sqlplus -S perfstat/perfstat <<EOF
set echo off
set feedback off
set heading off
set pagesize 0
set linesize 1000
set trimspool on
spool $LOGFILE
variable p_tran_nums number;
variable p_elps_time number;
begin
-- Call the procedure
sp_getdb_info(p_tran_nums => :p_tran_nums,
p_elps_time => :p_elps_time);
end;
/
col val format a120 newline
select 'Transactions/s: '||:p_tran_nums val,
'elapse time(s): '||:p_elps_time val
from dual;
spool off
exit
EOF

$more dbload_check.sh
#!/bin/sh
# creator: piner
# function: db's load check
# usage: crontab on monitor
# last modify:
#等待采集端主机采集负载信息
sleep 20
#负载文件所存在的目录地址
DESTDIR=/home/admin/log
#负载的报警阀值，也就是说，超过这个值的负载，则认为是有必要报警的
loadoff=20
#定义发送短信的接口
SENDMSG= /home/admin/bin/send_dbmobile.sh
#这里采用循环的方式在多个主机之间检测，如果增加或者减少一个主机，只需要修改这个列表即可
#这个列表也可以写在配置文件中
DEST_SERVERS="db1 db2 db3 db4"
for DEST_SERVER in $DEST_SERVERS
do
echo -e "\033[32;1m============== $DEST_SERVER ==============\033[0m";
#从主机传送过来的文件上，获得最新的负载信息
load=`cat $DESTDIR/${DEST_SERVER}load.log|awk '{print $1}'|sed s/','//g`
echo -e "System load now is \033[40;31m"$load"\033[40;37m"
#负载检查，注意，这里使用了命令bc，因为负载是一个小数，普通的shell不支持小数的比较，所以，这里引
入了bc来比较小数
#如果负载大于指定的阀值，则发送一个手机短信到特定的组中
if [ `echo "$load >= $loadoff" | bc` -eq 1 ]; then
mesglog="$DEST_SERVER's system load more than "$loadoff" now is "$load
$SENDMSG "$mesglog" "group1"
fi
done

$more alertcheck.sh
#!/bin/sh
# creator: piner
# function: check db's alert.log file
# usage: crontab on monitor
# last modify:
#等待采集端主机采集负载信息
sleep 20
#发送邮件与发送短信的接口
ENDMSG= /home/admin/bin/ send_dbmobile.sh
SENDDBM= /home/admin/bin/send_dbmail.sh
#同样采用循环的方式来检测，这里是主机名的列表
DEST_SERVERS="db1 db2 db3 db4"
for DEST_SERVER in $DEST_SERVERS
do
echo "---- $DEST_SERVER -----"
#这里定义了3类文件
#1是原始文件，也就是刚拷贝过来的文件
#2是目标文件，等比较完成以后，会把原始文件拷贝成目标文件
#3是差异文件，也就是原始文件跟目标文件比较后的差异
SOURCE=/tmp/${DEST_SERVER}_alert.log
DEST=/tmp/alertlog-${DEST_SERVER}
DIFFFILE=/tmp/${DEST_SERVER}.txt
#通过diff命令来比较原始文件与目标文件，并过滤所有的ORA-信息，
#也就是错误信息，写入到差异文件
diff $SOURCE $DEST | grep "ORA-" | grep "^<" > $DIFFFILE
valueserver=`cat $DIFFFILE 2>/dev/null`
cp -rf $SOURCE $DEST
#如果差异文件的内容不为空，则表示有错误信息
#通过手机发送报错信息，并发送邮件
if [ "$valueserver" ];then
mtitle="oracle_${DEST_SERVER}_alert"
mesglog="${DEST_SERVER}:$valueserver"
$SENDMSG "$mesglog" 'group1'
$SENDDBM "$mtitle" $DIFFFILE 'group1'
fi
echo "done"
done

#指定磁盘空间的报警阀值为90%以上
diskoff=90
#开始循环检测
DEST_SERVERS="db1 db2 db3 db4"
for DEST_SERVER in $DEST_SERVERS
do
echo -e "\033[32;1m============== $DEST_SERVER ==============\033[0m";
#disk space check
#读取以上的列表，在列表内部再次循环
while read LINE
do
per=0
per=`echo $LINE|awk '{print $4}'|sed s/%//g`
dname=`echo $LINE|awk '{print $7}'`
if [ $per -ge $diskoff ]
then
mesglog="$DEST_SERVER's ${dname} space more than "$diskoff"% now is "$per"%"
$SENDMSG "$mesglog" 'group1'
fi
done < $DESTDIR/${DEST_SERVER}df.log
done

#这里给出两个列表，一个是错误信息列表
#一个是警告列表，表示只需要发送邮件即可
Error_List="ORA-|Can not"
Warning_List="ORA-01555|ORA-1093"
#在对比日志文件的时候，先过滤所有错误，然后反过滤警告信息
diff $SOURCE $DEST | grep -E $Error_List |grep -v -E $Warning_List| grep "^<" > $DIFFFILE_ERROR
#然后单独过滤警告信息
diff $SOURCE $DEST | grep -E $Warning_List | grep "^<" > $DIFFFILE_WANGWANG
cp -rf $SOURCE $DEST
valueserver=`cat $DIFFFILE_ERROR 2>/dev/null`
if [ "$valueserver" ];then
#发送短信，参考以前的脚本
fi
valueserver=`cat $DIFFFILE_WANGWANG 2>/dev/null`
if [ "$valueserver" ];then
#发送邮件，参考以前的脚本
fi

#重要程度的主机
DEST_SERVERS="db1 db2 db3 db4"
for DEST_SERVER in $DEST_SERVERS
do
#处理重要程度的主机监控信息
Done
#非重要程度的主机
DEST_SERVERS="test1 test2 test3 test4"
for DEST_SERVER in $DEST_SERVERS
do
#处理非重要程度的主机监控信息
done

if [ `echo "$load >= $loadoff" | bc` -eq 1 ]
then
mesglog="$DEST_SERVER's system load more than "$loadoff" now is "$load
$SENDMSG "$mesglog" "group1" "{DEST_SERVER}_load.flg"
else
echo "0" > ${DEST_SERVER}_load.flg
fi
#初始化是否发送的标志位
send_flag=0
#这里判断是否有第三个参数
if [ $# -eq 3 ]; then
#如果有，则把参数3的值赋给变量flag_file，其实就是一个文件名称
flag_file=$3
#如果文件不存在，初始化文件，赋予标志位为1
if [ ! -f $flag_file ];then
echo "1" > $flag_file #初始标记为1，因为这里是第一次超过阀值
send_flag=1
else
send_flag=`cat $flag_file` #获得原来的标记
send_flag=$(($send_flag +1 )) #把原来的标记加上1
echo $send_flag > $flag_file #更新为新标记
fi
fi
#如果当前的标志位大于等于2，则把信息前面加上double check，以区别一般的短信
#否则，就是普通的短信，这里的$1其实就是信息内容，也就是以前脚本中的参数1。
if [ $send_flag -ge 2 ];then
mesglog="double check:$1"
elif [ $send_flag -eq 0 ];then
mesglog="$1" #########这里是原来脚本中的语句
else
#不满足多校验条件，返回
exit 0;
fi

create or replace trigger DDL_TRIGGER
before ddl on database
declare
n number;
str_stmt varchar2(4000);
sql_text ora_name_list_t;
l_trace number;
l_sid number;
str_session v$session%rowtype;
BEGIN
--get ddl script
n := ora_sql_txt(sql_text);
for i in 1..n loop
str_stmt := substr(str_stmt || sql_text(i),1,3000);
end loop;
--get modify status and permission
--这里表示许可的范围，如本机操作，如alter session操作
select count(*) into l_trace from dual
where sys_context('userenv','ip_address') is not null
and lower(str_stmt) not like 'alter% session %'
and lower(str_stmt) not like '%alter %compile%' ;
--get session information
select sid into l_sid from v$mystat where rownum=1;
select * into str_session
from v$session where sid = l_sid;
--if not permit then alert
if l_trace > 0 then
--write error to alert file
--如果发生了非许可范围内的操作，则记录日志到alert文件，并抛出异常
sys.dbms_system.ksdwrt(2,'ORA-20001:User:'||ora_login_user||',time:'||to_char
(sysdate,'yyyymmdd hh24:mi:ss')||'
,program:'||str_session.program||',IP:'||sys_context('userenv','ip_address')||',obje
ct:'||ora_dict_obj_name||',DDL: '||s
ubstr(str_stmt,1,100));
--raise exception to user
raise_application_error
(-20001,'You can not execute ddl on this object except on the local machine');
else
--如果是许可范围内的操作，也记录日志到alert，以供查询与审计
--write information to alert file
sys.dbms_system.ksdwrt(2,'User:'||ora_login_user||',time:'||to_char
(sysdate,'yyyymmdd hh24:mi:ss')||',program:'
||str_session.program||',IP:'||sys_context('userenv','ip_address')
||',object:'||ora_dict_obj_name||',DDL: '||substr(str_
stmt,1,100));
end if;
EXCEPTION
WHEN OTHERS THEN
--raise exception to user
raise;
END;

$more get_sql_by_spid.sh
#!/bin/ksh
sqlplus -S /nolog <<EOF
connect user/pass;
col sid format 99999
col serial# format 999999
col username format a10
col program format a32
set lines 1000
set pages 1000
set trimspool on
--获得会话信息
select sid,serial#,username,program,
to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time
from v\$session
where paddr in ( select addr from v\$process where spid=$1);
--获得该SQL语句的详细信息
select sql_text
from v\$sqltext_with_newlines
where hash_value in
(select sql_hash_value
from v\$session
where paddr in
(select addr from v\$process where spid=$1))
order by piece;
exit;
EOF

$more get_plan_by_hash.sh
#!/bin/ksh
sqlplus -S /nolog <<EOF
connect user/pass
set lines 1000
set pages 1000
set feedback off
set trimspool on
col sql_text fo

新书代码服务