系統日誌及資料庫相關資訊收集

定期收集使用者和dblink資訊：

sqlplus / as sysdba @user_and_link_collect.sql

----------------------------------------------------------------------------

cat user_and_link_collect.sql

set pages 1000

column dat1 new_value dblink_collect;

select instance_name ||'_dblink_collect' as dat1 from v$instance;

set markup html on;

spool /tmp/&&dblink_collect..html

select * from dba_db_links order by owner,db_link,USERNAME;

spool off

set markup html off

set pages 1000

column dat2 new_value user_collect;

select instance_name ||'_user_collect' as dat2 from v$instance;

set markup html on;

spool /tmp/&&user_collect..html

select * from dba_users order by ACCOUNT_STATUS desc,PROFILE desc,username;

select * from dba_profiles order by PROFILE,RESOURCE_NAME;

spool off

set markup html off

eixt

----------------------------------------------------------------------------

sqlplus / as sysdba @user_and_link_collect.sql

----------------------------------------------------------------------------

cat bak_tns.sh

#!/bin/bash

cp $ORACLE_HOME/network/admin/tnsnames.ora /tmp/"$HOSTNAME"_tnsnames.ora

----------------------------------------------------------------------------

統一上傳到FTP：

#!/bin/bash

#定義引數

datetime=`date +%Y%m%d_%H%M%S_%N |cut -b1-8`

ftp_user=username #ftp使用者名稱

ftp_pass=P@ssw0rd #ftp密碼

ftp_ip=192.160.x.x #ftp地址

ftp_bak_dir="/" #ftp上存放備份的目錄，需要先建好，以計算機名區分

FTP_SID=$ORACLE_SID

FTP_HOSTNAME=$HOSTNAME

data_bak_dir="/tmp" #本地要備份的資料目錄

#上傳FTP

ftp -v -n $ftp_ip << END

user $ftp_user $ftp_pass

type binary

cd $ftp_bak_dir

lcd $data_bak_dir

prompt

mput "$FTP_SID"_dblink_collect.html

mput "$FTP_SID"_user_collect.html

mput "$FTP_HOSTNAME"_tnsnames.ora

bye

END

----------------------------------------------------------------------------

收集錯誤日誌資訊：

set pages 1000

column dat3 new_value alert_collect;

select instance_name ||'_alert_collect' as dat3 from v$instance;

set markup html on;

spool /tmp/&&alert_collect..html

WITH diag_alert_ext AS

 (SELECT /*+ materialize */

   originating_timestamp,

   message_text

    FROM v$diag_alert_ext

   WHERE originating_timestamp > systimestamp - INTERVAL '1' DAY)

SELECT originating_timestamp,

       message_text

  FROM diag_alert_ext

 WHERE message_text LIKE '%Error%'

    OR message_text LIKE '%Fail%'

    OR message_text LIKE '%WARNING%'

    OR message_text LIKE '%Invalid%'

    OR message_text LIKE '%ORA-%'

    OR message_text LIKE '%Global Enqueue Services%'

    OR message_text LIKE '%dead%'

    OR message_text LIKE '%Starting ORACLE instance%'

 ORDER BY originating_timestamp DESC;

spool off

set markup html off

----------------------------------------------------------------------------

#!/bin/bash

#定義引數

datetime=`date +%Y%m%d_%H%M%S_%N |cut -b1-8`

ftp_user=username #ftp使用者名稱

ftp_pass=P@ssw0rd #ftp密碼

ftp_ip=192.169.x.x #ftp地址

ftp_bak_dir="/" #ftp上存放備份的目錄，需要先建好，以計算機名區分

FTP_SID=$ORACLE_SID

FTP_HOSTNAME=$HOSTNAME

data_bak_dir="/tmp" #本地要備份的資料目錄

#上傳FTP

ftp -v -n $ftp_ip << END

user $ftp_user $ftp_pass

type binary

cd $ftp_bak_dir

lcd $data_bak_dir

prompt

mput "$FTP_SID"_alert_collect.html

mput "$FTP_HOSTNAME"_messages.log

bye

END

----------------------------------------------------------------------------

收集當前的系統日誌：

cat bak_message.sh

sh /home/oracle/scripts/bak_message.sh

#!/bin/bash

LANG=en_US.UTF-8

sed -n '/^'"`date +"%b %d" -d "-1 days"`"'/,/^'"`date +"%b %d" -d "+1 days"`"'/p' /var/log/messages > /tmp/"$HOSTNAME"_messages.log

put_user_and_link_collect.sh

user_and_link_collect.sql

bak_tns.sh

bak_message.sh

put_alter_message.sh