-- This is the global parameter file -- You can use $ora_sid, $ora_home, $run_date, $this_day (Mon) -- and reference as $conf{$ora_sid} -- -- CVS info: -- $Id: dba.conf,v 1.6 1999/04/01 09:01:20 demiurg Exp $ -- 'DVP'=> { 'rhost'=> 'sor', 'rlogin'=> 'oracle', -- Remote Oracle Login 'dba_mail'=>'demiurg@zenon.net', -- Debug key if 'Y' then debug 'debug' => 'Y', 'ora_home'=>'/export/home/oracle8', 'nls_lang' => 'AMERICAN_AMERICA.CL8KOI8R', 'path'=>'.:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:'.$ora_home.'/bin:', ---------- Ps monitor ----------------------------- 'ps' => { 'ps1'=> 'ps -fu oracle | grep "ora_[a-z]*_$ora_sid"', 'ps1_min' => '6', 'ps1_max'=> '0', 'ps1_action' => '', 'ps2' => 'ps -eu oracle | grep "tnslsnr"', 'ps2_min' => '1', 'ps2_max' =>'0', 'ps2_action' => 'lsnrctl start &', 'ps3' => 'ps -fu oracle | grep "ctxsrv"', 'ps3_min' => '3', 'ps3_action' =>'ctxstart &', 'ps_failed' => 'Process failed' }, ---------- Recovery start hear ----------------- 'recovery'=> { 'job'=>'Recovery job '.$ora_sid.' at '.$run_date, 'ora_home'=>'/export/home/oracle', 'shutdown'=>'svrmgrl << EOF connect internal; shutdown immediate; exit; EOF', --------- Расположение ---- 'postfix'=>'.dbf.gz', 'SYSTEM' => $ora_home.'/dbs/ZEN/system.dbf', 'RAW_DEVICE' => $ora_home.'/dbs/ZEN/raw_device.dbf', 'INDEXES' => $ora_home.'/dbs/ZEN/indexes.dbf', 'TEMP' => $ora_home.'/dbs/ZEN/temp.dbf', 'RBS' => $ora_home.'/dbs/ZEN/temp.dbf', -- 'cp'=>'gzip -d -c %s | dd if=/dev/stdin/ of=%s' }, ---------- Backup start hear ------------------ 'backup'=> { 'job'=>'Backup job at '.$run_date, 'remote_dest'=>'/backup/ZEN', 'work_dir'=>"/home", 'ologin'=>'snn/rexx', 'filelist'=>$conf{$ora_sid}->{'backup'}->{'work_dir'}.'/'.'backup.lst', -- Before cp command - may be shudown or tablespace offline 'before_cmd'=>'echo $PATH', -- Main backup script 'backup_cmd' => "sqlplus -s $conf{$ora_sid}->{'backup'}->{'ologin'} << EOF column tablespace_name noprint column seqn noprint set pagesize 0 set linesize 132 set feedback off set term off set echo off set sqlprompt ".'""'." Whenever SQLERROR exit FAILURE select chr(1) tablespace_name, -9999 seqn, 'alter system switch logfile;' from dual UNION select chr(1) tablespace_name, -9998 seqn, 'alter database backup controlfile to ' || '''controlfile.HOT.full.before'' reuse;' from dual UNION select tablespace_name, 0, 'alter tablespace '||tablespace_name||' begin backup;' from sys.dba_tablespaces where status = 'ONLINE' and tablespace_name in ($conf{$ora_sid}->{'backup'}->{$this_day}) UNION select tablespace_name, file_id, '!gzip < '||file_name|| ' $conf{$ora_sid}->{'backup'}->{'work_dir'}' || '/' || substr(file_name,instr(file_name,'/',-1) + 1) || '.gz' from sys.dba_data_files where status = 'AVAILABLE' and tablespace_name in ( $conf{$ora_sid}->{'backup'}->{$this_day} ) UNION select tablespace_name, 9999, 'alter tablespace '||tablespace_name||' end backup;' from sys.dba_tablespaces where status = 'ONLINE' and tablespace_name in ($conf{$ora_sid}->{'backup'}->{$this_day} ) UNION select chr(255) tablespace_name, 9998 seqn, 'alter database backup controlfile to ''' || 'controlfile.HOT.full.after'' reuse;' from dual UNION select chr(255) tablespace_name, 9999 seqn, 'alter system switch logfile;' from dual ORDER BY 1, 2 spool ora_HOT_backup.sql / spool off -- -- Prepare file list to remote copy -- alter database backup controlfile to trace; set feed off head off echo off term off set sqlprompt ".'""'." set pagesize 0 set linesize 80 / select p.value || '/'|| lower ('$ora_sid') ||'_ora_' || proc.spid os_process_id from ".'v\$session'." sess, ".'v\$process proc'.", ".'v\$parameter'." p where proc.addr = sess.paddr and sess.audsid = userenv('SESSIONID') and p.name = 'user_dump_dest' UNION select '$conf{$ora_sid}->{'backup'}->{'work_dir'}'||'/' || substr(file_name,instr(file_name,'/',-1) + 1) || '.gz' from dba_data_files where tablespace_name in ($conf{$ora_sid}->{'backup'}->{$this_day}) union select '$ora_home/dbs/' || 'init$ora_sid'||'.ora' from dual spool $conf{$ora_sid}->{'backup'}->{'filelist'} / spool off exit; EOF", 'ok_string'=>'Sucsess', 'err_string'=>'Backup failed', -- How copy ot remote (or other directory, tape) 'cp'=>' ', 'post_cmd'=>'', -- Backup sheduler ----- 'Sun'=>"'RAW_DEVICE'", 'Mon'=>"'INDEXES'", 'Tue'=>"'SYSTEM', 'TEMP', 'RBS'", 'Wed'=>"'SYSTEM', 'TEMP', 'RBS'", 'Fri'=>"'SYSTEM', 'TEMP', 'RBS'" }, ----------- End Backup ------------------------- ---------- Export start hear -------------- 'export' => { 'ologin' => 'snn/rexx', 'workdir'=> '/export/home/demiurg/tmp', 'remote_dest' => '/backup/DVP/exp', -- Cp must have 2 arg - source and target, 'cp' => '/export/home/oracle/admin/md5.sh %s '.$conf{$ora_sid}->{'export'}->{'remote_dest'}.'/%s', -- Set nls_lang before export 'nls_lang' => 'AMERICAN_AMERICA.RU8PC855', -- Do before export 'before_cmd' => '/usr/bin/rm tmp.pipe; /usr/sbin/mknod tmp.pipe p; /usr/bin/cat tmp.pipe | /usr/local/bin/gzip >'.$conf{$ora_sid}->{'export'}->{'workdir'}.'/'. 'exp'.$ora_sid.'.dmp &', -- Export File names -- Note that if file is pipe then 'cp' will copy 'work_dir'.'exp'.$ora_sid.'.dmp 'file'=>'tmp.pipe', 'log' =>'exp'.$ora_sid.'.log', -- Search in log good string 'ok_string' => 'Export terminated successfully without warnings', -- Message subject if fail 'export_fail' => 'Export fail', -- Do after export 'post_cmd' => '/usr/bin/rm '. $conf{$ora_sid}->{'export'}->{'workdir'}.'/'. 'exp'.$ora_sid.'.dmp', -- Export Sheduler 'Mon' => '', 'Tue' => '', 'Thu' => 'DIRECT=Y COMPRESS=Y CONSISTENT=Y ROWS=Y INDEXES=N RECORD=Y tables= \( accounts \)' } }, --------- End Export ---------------------------- --- ----------End DVP ----------------------------------------------------------- ----------------------------------------------------------------------------- 'NMS'=>{ 'rhost'=> 'pbs', 'rlogin'=> 'oracle', 'dba_mail'=>'demiurg@zenon.net, dimentov@zenon.net', 'debug' => 'Y', 'ora_home'=>'/export/home/oracle', 'path'=>'.:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/export/home/oracle/bin', 'export' => { 'ologin' => '/', 'workdir'=> '/usr/backup/data', 'remote_dest' => '/backup/NMS/exp', -- Cp must have 2 arg - source and target, 'cp' => 'cat %s | ssh -p 222 -l '.$conf{$ora_sid}->{'rlogin'}." ".$conf{$ora_sid}->{'rhost'}.' "~/admin/zenmd5.sh '.$conf{$ora_sid}->{'export'}->{'remote_dest'}.'/%s'.';"', -- Set nls_lang before export 'nls_lang' => 'AMERICAN_AMERICA.RU8PC855', -- Do before export 'before_cmd' => '/usr/bin/rm tmp.pipe; /usr/sbin/mknod tmp.pipe p; /usr/bin/cat tmp.pipe | /usr/local/bin/gzip >'.$conf{$ora_sid}->{'export'}->{'workdir'}.'/'. 'exp'.$ora_sid.'.dmp &', -- Export File names -- Note that if file is pipe then 'cp' will copy 'work_dir'.'exp'.$ora_sid.'.dmp 'file'=>'tmp.pipe', 'log' =>$conf{$ora_sid}->{'export'}->{'workdir'}.'/exp'.$ora_sid.'.log', -- Search in log good string 'ok_string' => 'Export terminated successfully without warnings', -- Message subject if fail 'export_fail' => 'Export fail', -- Do after export 'post_cmd' => '', -- Export Sheduler 'Sun' => 'DIRECT=Y COMPRESS=Y CONSISTENT=Y ROWS=Y INDEXES=Y RECORD=Y OWNER=NMS', 'Mon' => 'DIRECT=Y COMPRESS=Y CONSISTENT=Y ROWS=Y INDEXES=Y RECORD=Y OWNER=NMS', 'Tue' => 'DIRECT=Y COMPRESS=Y CONSISTENT=Y ROWS=Y INDEXES=Y RECORD=Y OWNER=NMS', 'Wed' => 'DIRECT=Y COMPRESS=Y CONSISTENT=Y ROWS=Y INDEXES=Y RECORD=Y OWNER=NMS', 'Thu' => 'DIRECT=Y COMPRESS=Y CONSISTENT=Y ROWS=Y INDEXES=Y RECORD=Y OWNER=NMS', 'Fri' => 'DIRECT=Y COMPRESS=Y CONSISTENT=Y ROWS=Y INDEXES=Y RECORD=Y OWNER=NMS', 'Sat'=> 'DIRECT=Y COMPRESS=Y CONSISTENT=Y ROWS=Y INDEXES=Y RECORD=Y OWNER=NMS' } }, ------------------------------------------------------------------------------ 'ZEN'=> { 'rhost'=> 'nss', 'rlogin'=> 'oracle8', 'tmp_file'=>'date.out', 'dba_mail'=>'demiurg@zenon.net', 'debug'=>'Y', 'ora_home'=>'/oracle/817', 'nls_lang' => 'AMERICAN_AMERICA.CL8KOI8R', 'path'=>'.:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/oracle/817/bin', ---------- Ps monitor ----------------------------- 'ps' => { 'ps1'=> 'ps -fu oracle8 | grep "ora_[a-z]*_$ora_sid"', 'ps1_min' => '6', 'ps1_max'=> '0', 'ps1_action' => '', 'ps2' => 'ps -eu oracle8 | grep "tnslsnr"', 'ps2_min' => '1', 'ps2_max' =>'0', 'ps2_action' => 'lsnrctl start &', 'ps3' => 'ps -fu oracle8 | grep "ctxsrv"', 'ps3_min' => '1', 'ps3_action' =>'ctxstart &', 'ps_failed' => 'Process failed' }, ---------- StandBy ------------------------------- 'standby'=>{ 'log_arch_dest' => '/mnt/arch', 'log_arch_mask' => "'*.arc'", 'rlog_arch_dest' => "/backup/ZEN/arch", 'rlist' => 'ssh -l '.$conf{$ora_sid}->{rlogin}.' '.$conf{$ora_sid}->{rhost}.' "cd '.$conf{$ora_sid}->{standby}->{rlog_arch_dest}.'; find . -name '. $conf{$ora_sid}->{standby}->{log_arch_mask}.' -print"', 'llist' => 'cd '.$conf{$ora_sid}->{'standby'}->{'log_arch_dest'}.'; find . -name '. $conf{$ora_sid}->{'standby'}->{'log_arch_mask'}.' -print' , 'recovery' => "ssh -l $conf{$ora_sid}->{'rlogin'} $conf{$ora_sid}->{'rhost'} \"export ORACLE_SID=$ora_sid; svrmgrl << EOF connect internal; !echo \$ORACLE_SID; !uname -n; startup nomount; set autorecovery on; alter database mount; recover from '$conf{$ora_sid}->{standby}->{rlog_arch_dest}' using backup controlfile; select 'archive:log' || max(recid) || '_1.arc:' from v\\\\\\\$log_history; shutdown immediate; exit; EOF\" ", 'cp' => 'cat %s | ssh -c blowfish -l '.$conf{$ora_sid}->{'rlogin'}.' '.$conf{$ora_sid}->{'rhost'}.' "~/admin/zenmd5.sh '.$conf{$ora_sid}->{'standby'}->{'rlog_arch_dest'}.'/%s'.' ;"', 'standby_failed' => 'Standby failed ! (corrupted log ?)' }, ---------- Recovery start hear ----------------- 'recovery'=> { 'job'=>'Recovery job '.$ora_sid.' at '.$run_date, 'ora_home'=>'/export/home/oracle8', 'test' => 'Y', 'shutdown'=>'/export/home/oracle8/bin/svrmgrl << EOF connect internal; shutdown immediate; exit; EOF', --------- Расположение ---- 'postfix'=>'.dbf.gz', 'SYSTEM' => $ora_home.'/dbs/ZEN/system.dbf', 'RAW_DEVICE' => $ora_home.'/dbs/ZEN/raw_device.dbf', 'INDEXES' => $ora_home.'/dbs/ZEN/indexes.dbf', 'TEMP' => $ora_home.'/dbs/ZEN/temp.dbf', 'RBS' => $ora_home.'/dbs/ZEN/rbs.dbf', -- 'cp'=>'gzip -d -c %s | dd if=/dev/stdin/ of=%s bs=102400' }, ---------- Backup start hear ------------------ 'backup'=> { 'job'=>'Backup job at '.$run_date, 'remote_dest'=>'/backup/ZEN', 'work_dir'=>'/export/home/oracle8/admin/ZEN/backup/update', 'ologin'=>'/', 'filelist'=>'/export/home/oracle8/admin/ZEN/backup/update/backup.lst', 'recentlist' => '/export/home/oracle8/admin/ZEN/backup/update/recent.lst', 'cp' => 'cat %s | ssh -c blowfish -l '.$conf{$ora_sid}->{'rlogin'}.' '.$conf{$ora_sid}->{'rhost'}.' "~/admin/zenmd5.sh '.$conf{$ora_sid}->{'backup'}->{'remote_dest'}.'/%s'.' ;"', -- Before cp command - may be shudown or tablespace offline 'before_cmd'=>'echo $PATH', -- Main backup script 'backup_cmd' => "sqlplus -s $conf{$ora_sid}->{'backup'}->{'ologin'} << EOF column tablespace_name noprint column seqn noprint set pagesize 0 set linesize 500 set feedback off set term off set echo off -- set sqlprompt ".'""'." Whenever SQLERROR exit FAILURE select chr(1) tablespace_name, -9999 seqn, 'alter system switch logfile;' from dual UNION select chr(1) tablespace_name, -9998 seqn, 'alter database backup controlfile to ' || '''$conf{$ora_sid}->{'backup'}->{'work_dir'}/controlfile.HOT.full.before'' reuse;' from dual UNION select tablespace_name, 0, 'alter tablespace '||tablespace_name||' begin backup;' from sys.dba_tablespaces where status = 'ONLINE' and tablespace_name in ($conf{$ora_sid}->{'backup'}->{$this_day}) UNION select tablespace_name, file_id, '!dd if='||file_name|| ' of=/dev/stdout bs=102400 | gzip -1 > $conf{$ora_sid}->{'backup'}->{'work_dir'}' || '/' || substr(file_name,instr(file_name,'/',-1) + 1) || '.gz' from sys.dba_data_files where status = 'AVAILABLE' and tablespace_name in ( $conf{$ora_sid}->{'backup'}->{$this_day} ) UNION select tablespace_name, 9999, 'alter tablespace '||tablespace_name||' end backup;' from sys.dba_tablespaces where status = 'ONLINE' and tablespace_name in ($conf{$ora_sid}->{'backup'}->{$this_day} ) UNION select chr(255) tablespace_name, 9998 seqn, 'alter database backup controlfile to ' || '''$conf{$ora_sid}->{'backup'}->{'work_dir'}/controlfile.HOT.full.after'' reuse;' from dual UNION select chr(255) tablespace_name, 9999 seqn, 'alter system switch logfile;' from dual ORDER BY 1, 2 spool $conf{$ora_sid}->{'backup'}->{'work_dir'}/ora_HOT_backup.sql / spool off; \@$conf{$ora_sid}->{'backup'}->{'work_dir'}/ora_HOT_backup.sql; -- -- Prepare file list to remote copy alter database backup controlfile to trace; set feed off head off echo off term off trims on set sqlprompt ".'""'." set pagesize 0 set linesize 80 / select p.value || '/'|| lower ('$ora_sid') ||'_ora_' || proc.spid || '.trc' from ".'v\$session'." sess, ".'v\$process proc'.", ".'v\$parameter'." p where proc.addr = sess.paddr and sess.audsid = userenv('SESSIONID') and p.name = 'user_dump_dest' UNION select '$conf{$ora_sid}->{'backup'}->{'work_dir'}'||'/' || substr(file_name,instr(file_name,'/',-1) + 1) || '.gz' from dba_data_files where tablespace_name in ($conf{$ora_sid}->{'backup'}->{$this_day}) union select '$ora_home/dbs/' || 'init$ora_sid'||'.ora' from dual union select '$conf{$ora_sid}->{'backup'}->{'work_dir'}'||'/'||'controlfile.HOT.full.after' from dual union select '$conf{$ora_sid}->{'backup'}->{'work_dir'}'||'/'||'controlfile.HOT.full.before' from dual union select '$conf{$ora_sid}->{'backup'}->{'recentlist'}' from dual; spool $conf{$ora_sid}->{'backup'}->{'filelist'} / spool off; select distinct '$conf{$ora_sid}->{'standby'}->{'rlog_arch_dest'}'|| '/' || 'log'|| to_char(recid)|| '_1.arc' from".' v\$log_history'." l, ".'v\$backup'." v where v.change# < l.next_change# union all select distinct '$conf{$ora_sid}->{'backup'}->{remote_dest}' || '/' || substr(d.file_name,instr(d.file_name,'/',-1) + 1) || '.gz.' || to_char(v.time,'DDMMYY') from".' v\$backup'." v, dba_data_files d where v.file# = d.file_id order by 1; spool $conf{$ora_sid}->{'backup'}->{'recentlist'} / spool off; exit; EOF", 'ok_string'=>'Sucsess', 'err_string'=>'Backup failed', 'post_cmd'=>'', -- Backup sheduler ---- 'Sun'=>"'RBS'", 'Wed'=>"'TEMP', 'HISTORY', 'SYSTEM'", 'Thu'=>"'INDEXES', 'RAW_DEVICE", 'Fri'=>"'INDEXES2'", 'Sat'=>"'RAW_DEVICE2', 'TSS'", 'Tue'=>"'RAW_DEVICE', 'HISTORY', 'TEMP2'", 'Mon'=>"'SYSTEM', 'FINANCE'" }, ----------- End Backup ------------------------- ---------- Export start hear -------------- 'export' => { -- Oracle login (must have exp_full_database) 'ologin' => '/', 'workdir'=> '/export/home/oracle8/admin/ZEN/backup/exp', 'remote_dest' => '/backup/ZEN/exp', -- Cp must have 2 arg - source and target, output mus be MD5 digist 'cp' => 'cat %s | ssh -c blowfish -l '.$conf{$ora_sid}->{'rlogin'}." ".$conf{$ora_sid}->{'rhost'}.' "~/admin/zenmd5.sh '.$conf{$ora_sid}->{'export'}->{'remote_dest'}.'/%s'.' ;"', -- Set nls_lang before export 'nls_lang' => 'AMERICAN_AMERICA.RU8PC855', -- Do before export 'before_cmd' => 'rm tmp.pipe; mknod tmp.pipe p; cat tmp.pipe | gzip >'.$conf{$ora_sid}->{'export'}->{'workdir'}.'/exp'.$ora_sid.'.dmp &', -- Export File names -- Note that if file is pipe then 'cp' will copy 'work_dir'.'exp'.$ora_sid.'.dmp 'file'=>'tmp.pipe', 'log' =>$conf{$ora_sid}->{'export'}->{'workdir'}.'/exp'.$ora_sid.'.log', -- Search in log good string 'ok_string' => 'Export terminated successfully without warnings', -- Message subject if fail 'export_fail' => 'Export fail', -- Do after export 'post_cmd' => '', -- Export Sheduler 'Wed'=> 'COMPRESS=Y CONSISTENT=Y FULL=Y ROWS=N INDEXES=Y RECORD=Y BUFFER=1024000', 'Mon' => q{DIRECT=Y COMPRESS=Y CONSISTENT=Y OWNER='(ZEN,ACC, RGT)' ROWS=Y INDEXES=N RECORD=Y BUFFER=1024000}, 'Tue' => q{DIRECT=Y COMPRESS=Y CONSISTENT=Y OWNER='(ZEN,ACC, RGT)' ROWS=Y INDEXES=N RECORD=Y BUFFER=1024000}, 'Sun' => q{DIRECT=Y COMPRESS=Y CONSISTENT=Y OWNER='(ZEN,ACC, RGT)' ROWS=Y INDEXES=N RECORD=Y BUFFER=1024000}, 'Thu' => q{DIRECT=Y COMPRESS=Y CONSISTENT=Y OWNER='(ZEN,ACC, RGT)' ROWS=Y INDEXES=N RECORD=Y BUFFER=1024000}, 'Fri' => q{DIRECT=Y COMPRESS=Y CONSISTENT=Y OWNER='(ZEN,ACC, RGT)' ROWS=Y INDEXES=N RECORD=Y BUFFER=1024000}, 'Sat' => q{DIRECT=Y COMPRESS=Y CONSISTENT=Y OWNER='(ZEN,ACC, RGT)' ROWS=Y INDEXES=N RECORD=Y BUFFER=1024000} }, ---------------------------------------------------------------------------- --------------- Rotate rules ---------------------------------------------- 'rotate' => { 'job' => 'Rotate', 'test' => 'Y', 'ftphost' => 'zts.hq.zenon.net', 'ftplogin' => 'dsv', 'ftppasswd' => 'dsvolk.', 'find' => "find /backup/ZEN -name '*.*' -mtime +1 -size +0 -type f; find /backup/NMS/exp -name '*.*' -mtime +1 -size +0 -type f", 'tape' => '/dev/rmt/0hn', 'cycle' => '6', 'log_dir' => '/backup/tape', 'log_mask' => 'tape.', 'ok' => ' - OK', 'fail' => ' - job failed', 'maxsize' => '4294967296', 'recentlist' => '/backup/ZEN/recent.lst.'.$run_date } }