分类目录归档：Linux

shell处理alert日志

发表于 2012 年 2 月 12 日由惜分飞

每天都检查oracle日志，所以写了一个比较完善的shell，让其自动处理，在运行程序之前，需要在该脚本目录下新建tmp目录

#!/usr/bin/ksh
export LANG=en

#临时目录
tmp_dest=$HOME/check/tmp
#用户名
username=username
#密码
password=password

cd $tmp_dest
sqlplus -s $username/$password<<XFF>/dev/null
set echo on
spool sqlplus.txt
col name format a20
col value format a55
select name,value from v\$parameter where name='background_dump_dest';
spool off
exit
XFF

alert_path_num=$(grep -n "background_dump_dest" $tmp_dest/sqlplus.txt |awk -F":" '{print $1}')
alert_path=$(cat $tmp_dest/sqlplus.txt |sed -n "${alert_path_num}p" | awk -F" " '{print $2}')

first_day=`cat $tmp_dest/first_day.tmp`
d_day=$(date +%e)
if [ $d_day -lt 10 ]
then
###########注意:以下两种方式选择其一###################
#部分系统出现alert日志例如:Tue Aug  7 07:44:59 2012
 last_day=$(date +%a)' '$(date +%b)'.*'$d_day'.*'$(date +%Y)

#部分系统出现alert日志例如:Thu Jun 07 13:56:18 2012
 n_day=`echo $d_day | awk 'gsub(/^ *| *$/,"")'`
 last_day=$(date +%a)' '$(date +%b)'.*0'$n_day'.*'$(date +%Y)

else
 last_day=$(date|cut -c 1-10).*$(date +%Y)
fi
echo $last_day > $tmp_dest/first_day.tmp
first_num=$(grep -n "$first_day" $alert_path/alert_$ORACLE_SID.log |head -1|awk -F":" '{print $1}')
if [ -z "$first_num" ]
then
   first_num=1
fi

#修改$last_day为'.*',表示alert日志结尾
last_num=$(grep -n ".*" $alert_path/alert_$ORACLE_SID.log |tail -1|awk -F":" '{print $1}')

point=1
export=$point

echo "########################## checking alert_log start $first_day ########################## "

sed -n "${first_num},${last_num}p" $alert_path/alert_$ORACLE_SID.log > $tmp_dest/trunc_alert
cat $tmp_dest/trunc_alert|grep ORA-|while read line
do
   line=$(echo "$line"|sed -e 's/\[/\\[/g;s/\]/\\]/g')
   time=$(grep -n "$line" $tmp_dest/trunc_alert | awk -F':' '{print $1}'|wc -l)

if [ "$time" -ge 1 ]
then
   num=$(grep -n "$line" $tmp_dest/trunc_alert|awk -F':' '{print $1}'|tail -1)
   #echo $num
   front_num=$((num-1))
   back_num=$((num+9))

   echo "++++++++++++++++++$point++++++++++++++++++++"
    sed -n "${front_num},${back_num}p" $tmp_dest/trunc_alert
   echo "++++++++++++++++++$point-End++++++++++++++++"
   point=$((point+1))

   flag=1
else
     if [ -z "$time" ]
     then
     flag=0
     fi
fi
done

#rm $tmp_dest/trunc_alert
#rm $tmp_dest/sqlplus.txt

if [ " $flag " -eq 0 ]
   then
   echo "No errors in $first_day !"
fi
echo "########################## checking alert_log end $last_day ########################## "

检查结果

########################## checking alert_log start Fri Jan 27.*2012 ########################## 
++++++++++++++++++1++++++++++++++++++++
  Current log# 3 seq# 918 mem# 0: /opt/oracle/oradata/orcl/mcrm/redo03.log
Tue Jan 31 22:00:22 2012
Errors in file /opt/oracle/admin/mcrm/bdump/mcrm_j001_23329.trc:
ORA-01114: IO error writing block to file 201 (block # 550944)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 550944
Additional information: -1
ORA-01114: IO error writing block to file 201 (block # 550922)
ORA-27072: File I/O error
Additional information: 4
Additional information: 550922
++++++++++++++++++1-End++++++++++++++++
++++++++++++++++++2++++++++++++++++++++
Additional information: 550944
Additional information: -1
ORA-01114: IO error writing block to file 201 (block # 550922)
ORA-27072: File I/O error
Additional information: 4
Additional information: 550922
Additional information: 114688
Wed Feb  1 07:24:02 2012
Thread 1 advanced to log sequence 919 (LGWR switch)
  Current log# 1 seq# 919 mem# 0: /opt/oracle/oradata/orcl/mcrm/redo01.log
Wed Feb  1 07:24:02 2012
Errors in file /opt/oracle/admin/mcrm/bdump/mcrm_arc1_3624.trc:
ORA-19504: failed to create file "/opt/oracle/oradata/archivelog/1_918_741262746.dbf"
++++++++++++++++++2-End++++++++++++++++
########################## checking alert_log end Sun Feb 12.*2012##########################

发表在 Linux | 评论关闭

dd操作数据文件

发表于 2012 年 1 月 13 日由惜分飞

1.dd主要参数

       Copy a file, converting and formatting according to the operands.

       bs=BYTES
              force ibs=BYTES and obs=BYTES

       cbs=BYTES
              convert BYTES bytes at a time

       count=BLOCKS
              copy only BLOCKS input blocks

       ibs=BYTES
              read BYTES bytes at a time

       if=FILE
              read from FILE instead of stdin

       obs=BYTES
              write BYTES bytes at a time

       of=FILE
              write to FILE instead of stdout

       seek=BLOCKS
              skip BLOCKS obs-sized blocks at start of output

       skip=BLOCKS
              skip BLOCKS ibs-sized blocks at start of input

2.拷贝数据文件头部

[oracle@node1 chf]$ dd if=system01.dbf of=/tmp/dd_xifenfei01.dbf bs=8192 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 6.2e-05 seconds, 132 MB/s
[oracle@node1 chf]$ ll /tmp/dd_xifenfei01.dbf
-rw-r--r-- 1 oracle oinstall 8192 01-13 16:49 /tmp/dd_xifenfei01.dbf


BBED> set filename '/tmp/dd_xifenfei01.dbf'
        FILENAME        /tmp/dd_xifenfei01.dbf

BBED> set block 1
        BLOCK#          1

BBED> dump
 File: ././dd_xifenfei01.dbf (0)
 Block: 1                Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
 00a20000 0000c0ff 00000000 00000000 64590000 00200000 00a30200 7d7c7b7a 
 a0810000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> set block 2
BBED-00309: out of range block number (2)


BBED> set offset 8190
        OFFSET          8190

BBED> dump
 File: ././dd_xifenfei01.dbf (0)
 Block: 1                Offsets: 8190 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0000 

 <32 bytes per line>
--证明只有1个数据块(8192),并且拷贝数据文件头部成功

3.模拟创建文件

[oracle@node1 tmp]$ ll /tmp/dd_xifenfei02.dbf -h
-rw-r--r-- 1 oracle oinstall 1.0G 01-13 16:58 /tmp/dd_xifenfei02.dbf

4.拷贝数据块到一个文件中

[oracle@node1 chf]$ dd if=/tmp/dd_xifenfei01.dbf of=/tmp/dd_xifenfei02.dbf bs=8192 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 6e-05 seconds, 137 MB/s
[oracle@node1 chf]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Fri Jan 13 17:01:02 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/tmp/dd_xifenfei02.dbf'

BBED> set block 1
        BLOCK#          1

BBED> dump
 File: ././dd_xifenfei02.dbf (0)
 Block: 1                Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
 00a20000 0000c0ff 00000000 00000000 64590000 00200000 00a30200 7d7c7b7a 
 a0810000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>


BBED> set block 2
BBED-00309: out of range block number (2)


BBED> set offset 8190
        OFFSET          8190

BBED> dump
 File: ././dd_xifenfei02.dbf (0)
 Block: 1                Offsets: 8190 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0000 

 <32 bytes per line>

通过对比在2步骤中拷贝出来的数据文件头部，证明现在已经把该头部拷贝到了3步骤创建的新文件中

发表在 Linux | 一条评论

恢复被rm意外删除数据文件

发表于 2011 年 12 月 31 日由惜分飞

一.模拟数据文件删除

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 31 22:00:52 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

--数据库版本
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

--所有数据文件
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ora11g/system01.dbf
/opt/oracle/oradata/ora11g/sysaux01.dbf
/opt/oracle/oradata/ora11g/undotbs01.dbf
/opt/oracle/oradata/ora11g/users01.dbf
/opt/oracle/oradata/ora11g/example01.dbf

--删除example01.dbf数据文件
SQL> !rm /opt/oracle/oradata/ora11g/example01.dbf

SQL> !ls -l /opt/oracle/oradata/ora11g/example01.dbf
ls: /opt/oracle/oradata/ora11g/example01.dbf: 没有那个文件或目录

--因为数据文件被删除，创建表失败
SQL> create table t_xifenfei tablespace example
  2  as select * from dba_tables;
as select * from dba_tables
                 *
ERROR at line 2:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/opt/oracle/oradata/ora11g/example01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

二.找回数据文件

--查找dbw进程spid
[oracle@node1 ~]$ ps -ef|grep dbw|grep -v grep
oracle   18387     1  0 Dec22 ?        00:00:12 ora_dbw0_ora11g

--查看该进程所有文件句柄
[oracle@node1 ~]$ ll /proc/18387/fd
总计 0
lr-x------ 1 oracle oinstall 64 12-31 22:03 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 12-31 22:03 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 10 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 12 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/orazhs.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 13 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lr-x------ 1 oracle oinstall 64 12-31 22:03 14 -> /proc/18387/fd
lr-x------ 1 oracle oinstall 64 12-31 22:03 15 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 16 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 17 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lrwx------ 1 oracle oinstall 64 12-31 22:03 18 -> /opt/oracle/product/11.2.0/db_1/dbs/lkORA11G
lr-x------ 1 oracle oinstall 64 12-31 22:03 19 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/orazhs.msb
l-wx------ 1 oracle oinstall 64 12-31 22:03 2 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 20 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 21 -> socket:[441562]
lrwx------ 1 oracle oinstall 64 12-31 22:03 256 -> /opt/oracle/oradata/ora11g/control01.ctl
lrwx------ 1 oracle oinstall 64 12-31 22:03 257 -> /opt/oracle/oradata/ora11g/system01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 258 -> /opt/oracle/oradata/ora11g/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 259 -> /opt/oracle/oradata/ora11g/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 260 -> /opt/oracle/oradata/ora11g/users01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 261 -> /opt/oracle/oradata/ora11g/example01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 12-31 22:03 262 -> /opt/oracle/oradata/ora11g/temp01.dbf
lr-x------ 1 oracle oinstall 64 12-31 22:03 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 4 -> /dev/null
lrwx------ 1 oracle oinstall 64 12-31 22:03 5 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lr-x------ 1 oracle oinstall 64 12-31 22:03 6 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 7 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 8 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 9 -> /dev/null
--通过句柄恢复数据文件[被删除数据文件会被标示(deleted)]
[oracle@node1 ~]$ cp /proc/18387/fd/261 /opt/oracle/oradata/ora11g/example01.dbf
--确认该数据文件已经恢复成功
[oracle@node1 ~]$ ll /opt/oracle/oradata/ora11g/example01.dbf
-rw-r----- 1 oracle oinstall 362422272 12-31 22:05 /opt/oracle/oradata/ora11g/example01.dbf

三.数据文件online

SQL> alter database datafile 5 offline;
 
Database altered.
 
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
 
Database altered.

SQL> create table t_xifenfei tablespace example
  2  as select * from dba_tables;

Table created.

四.补充说明
在意外使用os命令删除掉数据文件时，千万不要慌张重启数据库或者操作系统，可以通过dbwn进程相关句柄找回数据文件

发表在 Linux, Oracle备份恢复 | 7 条评论

17813235971

QQ 咨询

分类目录归档：Linux

shell处理alert日志

dd操作数据文件

恢复被rm意外删除数据文件

文章分类

最近发表

分类目录归档：Linux

shell处理alert日志

dd操作数据文件

恢复被rm意外删除数据文件

标签云

文章分类

最近发表