crontab -l

0 8 * * *  su - oracle -c "/home/oracle/scripts/instance_alter_collect.sh > /home/oracle/scripts/logs/instance_alter_collect.log" 2>&1

5 8 * * *  /bin/bash /home/oracle/scripts/bak_message.sh > /home/oracle/scripts/logs/bak_message.log 2>&1

30 8 * * *  su - oracle -c "/home/oracle/scripts/put_alter_message.sh > /home/oracle/scripts/logs/put_alter_message.log" 2>&1

#!/bin/bash

#定义参数

export ORACLE_SID1=mesopsdb

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

ftp_user=orazabbix

ftp_pass=Ora_zabbix123

ftp_ip=172.19.5.250

ftp_bak_dir="/"

FTP_SID=$ORACLE_SID1

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.log

mput "$FTP_HOSTNAME"_messages.log

bye

END

cat bak_message.sh 

#!/bin/bash

export.UTF-8

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

cat instance_alter_collect.sql 

set pages 1000

column dat3 new_value alert_collect;

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

spool /tmp/&&alert_collect..log

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

exit

或者：

sed -n '/^'"`date +"%Y-%m-%d" -d "-1 days"`"'/,/^'"`date +"%Y-%m-%d" -d "+1 days"`"'/p' ./alert_fdc1.log > /tmp/log-333.log

cat instance_alter_collect.sh 

#! /bin/bash

export ORACLE_SID=mesopsdb

sqlplus / as sysdba @/home/oracle/scripts/instance_alter_collect.sql

定期收集用户和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

exit

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

#!/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

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

收集当前的系统日志：%_d 增加下划线可以去掉月份前面多余的数字0

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

------

stat file_name.txt 查看文件信息

指定位置vsftp配置：只需要修改这两个配置文件

cat /etc/vsftpd/vsftpd.conf |grep -v ^$

anonymous_enable=NO

local_root=/home/orazabbix/ftpdata

anon_upload_enable=NO

anon_mkdir_write_enable=NO

local_enable=YES

write_enable=YES

local_umask=022

dirmessage_enable=YES

xferlog_enable=YES

connect_from_port_20=NO

pasv_enable=YES

pasv_min_port=3000

pasv_max_port=3050

xferlog_std_format=YES

listen=NO

listen_ipv6=YES

pam_service_name=vsftpd

userlist_enable=YES

userlist_file=/etc/vsftpd/vsftpd.user_list

userlist_deny=NO

tcp_wrappers=YES

chroot_local_user=YES

chroot_list_enable=NO

allow_writeable_chroot=YES

xferlog_file=/var/log/xferlog

[root@Zabbix-For-DB ftpdata]# cat /etc/vsftpd/vsftpd.user_list

orazabbix

systemctl status vsftpd.service #开机启动

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

export ORACLE_SID=

sqlplus / as sysdba @/home/oracle/scripts/user_and_link_collect.sql

sh /home/oracle/scripts/bak_tns.sh  

sh /home/oracle/scripts/put_user_and_link_collect.sh

##sqlplus orazabbix/"Ora_zabbix123#pdb"@172.20.5.78:1521/prmspdb @/home/oracle/scripts/instance_alter_collect.sql

MESDB2_dblink_collect.html

MESDB2_user_collect.html

zhmesdb02_tnsnames.ora

MESDB1_dblink_collect.html

zhmesdb01_tnsnames.ora

MESDB1_user_collect.html

scp root@172.19.5.11:/home/oracle/scripts/b收集用户和DBLINK/* /home/oracle/scripts/

chown oracle.oinstall /home/oracle/scripts/bak_tns.sh

chown oracle.oinstall /home/oracle/scripts/put_user_and_link_collect.sh

chown oracle.oinstall /home/oracle/scripts/user_and_link_collect.sql

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

0 8 * * *  su - oracle -c "/home/oracle/scripts/MESDB2_instance_alter_collect.sh > /home/oracle/scripts/logs/MESDB2_instance_alter_collect.log" 2>&1

5 8 * * *  su - oracle -c "/home/oracle/scripts/SPCDB2_instance_alter_collect.sh > /home/oracle/scripts/logs/SPCDB2_instance_alter_collect.log" 2>&1

10 8 * * *  /bin/bash /home/oracle/scripts/bak_message.sh > /home/oracle/scripts/logs/bak_message.log 2>&1

00 9 * * *  su - oracle -c "/home/oracle/scripts/put_alter_message.sh > /home/oracle/scripts/logs/put_alter_message.log" 2>&1

MESDB2_alert_collect.log

SPCDB2_alert_collect.log

zhmesdb02_messages.log

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

脚本内容：

[oracle@zhmesdb01 scripts]$ cat put_user_and_link_collect.sh  

#!/bin/bash

#定义参数

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

ftp_user=orazabbix #ftp用户名

ftp_pass=Ora_zabbix123 #ftp密码

ftp_ip=172.19.5.250 #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

[oracle@zhmesdb01 scripts]$ 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

exit

[oracle@zhmesdb01 scripts]$ cat bak_tns.sh  

#!/bin/bash

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

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

[root@zhmesdb01 ~]# cat /home/oracle/scripts/MESDB1_instance_alter_collect.sh

#! /bin/bash

export ORACLE_SID=MESDB1

sqlplus / as sysdba @/home/oracle/scripts/instance_alter_collect.sql

[root@zhmesdb01 ~]# cat /home/oracle/scripts/SPCDB1_instance_alter_collect.sh

#! /bin/bash

export ORACLE_SID=SPCDB1

sqlplus / as sysdba @/home/oracle/scripts/instance_alter_collect.sql

[root@zhmesdb01 ~]# cat /home/oracle/scripts/bak_message.sh

#!/bin/bash

export.UTF-8

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

[root@zhmesdb01 ~]# cat /home/oracle/scripts/put_alter_message.sh

#!/bin/bash

#定义参数

export ORACLE_SID1=MESDB1

export ORACLE_SID2=SPCDB1

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

ftp_user=orazabbix #ftp用户名

ftp_pass=Ora_zabbix123 #ftp密码

ftp_ip=172.19.5.250 #ftp地址

ftp_bak_dir="/" #ftp上存放备份的目录，需要先建好，以计算机名区分

FTP_SID=$ORACLE_SID1

FTP_SID2=$ORACLE_SID2

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.log

mput "$FTP_SID2"_alert_collect.log

mput "$FTP_HOSTNAME"_messages.log

bye

END

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

[root@zhmesdb01 ~]# cat /home/oracle/scripts/instance_alter_collect.sql

set pages 1000

column dat3 new_value alert_collect;

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

spool /tmp/&&alert_collect..log

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

exit