12 – 二月 – 2012 – 专业Oracle数据库恢复，或许是您恢复数据的最后机会@phone:17813235971

每天都检查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##########################

1.安装timesten

[oracle@bas linux8664]$ ./setup.sh

NOTE: Each TimesTen installation is identified by a unique instance name.
The instance name must be a non-null alphanumeric string, not longer
than 255 characters.

Please choose an instance name for this installation? [ tt1122 ] xifenfei
Instance name will be 'xifenfei'.
Is this correct? [ yes ] yes

Of the three components:

[1] Client/Server and Data Manager
[2] Data Manager Only
[3] Client Only

Which would you like to install? [ 1 ] 1

Of the following options :

[1] /home/oracle
[2] /home/oracle/timesten
[3] Specify a location
[q] Quit the installation

Where would you like to install the xifenfei instance of TimesTen? [ 1 ] 2
Where would you like to create the daemon home directory? [ /home/oracle/timesten/TimesTen/xifenfei/info ]

The daemon logs will be located in /home/oracle/timesten/TimesTen/xifenfei/info
Would you like to specify a different location for the daemon logs? [ no ]
Installing into /home/oracle/timesten/TimesTen/xifenfei ...
Uncompressing ...

NOTE: If you are configuring TimesTen for use with Oracle Clusterware, the
daemon port number must be the same across all TimesTen installations
managed within the same Oracle Clusterware cluster.

NOTE: All installations that replicate to each other must use the same daemon
port number that is set at installation time. The daemon port number can
be verified by running 'ttVersion'.

The default port number is 53396.

Do you want to use the default port number for the TimesTen daemon? [ yes ]
The daemon will run on the default port number (53396).

NOTE: For security, we recommend that you restrict access to the
TimesTen installation to members of a single OS group. Only members of
that OS group will be allowed to perform direct mode connections to
TimesTen, and only members of that OS group will be allowed to perform
operations that access TimesTen data stores, TimesTen files and shared
memory. The OS group defaults to the primary group of the instance
administrator. You can default to this group, choose another OS group
or you can make this instance world-accessible. If you choose to make
this instance world-accessible, all database files and shared memory
are readable and writable by all users.

Restrict access to the the TimesTen installation to the group 'oinstall'? [ yes ] yes

NOTE: Enabling PL/SQL will increase the size of some TimesTen libraries.

Would you like to enable PL/SQL for this instance? [ yes ] yes
TNS_ADMIN was not set in your environment but there is a tnsnames.ora
file in /opt/app/oracle/product/10.2.0/db_1/network/admin.
Would you like to use this TNS_ADMIN setting for the In-Memory Database Cache? [ yes ] yes

TNS_ADMIN will be set to /opt/app/oracle/product/10.2.0/db_1/network/admin
You can change TNS_ADMIN later by running <install_dir>/bin/ttmodinstall.

NOTE: It appears that you are running version 3.4 or higher of the g++
compiler. TimesTen ships with multiple sets of client libraries and server
binaries : one built for compatibility with g++ 3.4.6 and one with
g++ 4.1.0. The installer has created links to the 3.4.6 library in the
<install_dir>/lib directory and to the 3.4.6 server binary in the
<install_dir>/bin directory. If you want to use a different compiler,
please modify the links to point to the desired library and server binary.

Installing server components ...
What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 53397 ]
Do you want to install QuickStart and the TimesTen Documentation? [ no ] no
Would you like to install the documentation (without QuickStart)? [ yes ]
Where would you like to create the doc directory (s=skip)? [ /home/oracle/timesten/TimesTen/xifenfei/doc ] s
Installing client components ...

Would you like to use TimesTen Replication with Oracle Clusterware? [ no ] no

NOTE: The TimesTen daemon startup/shutdown scripts have not been installed.

Run the 'setuproot' script :
cd /home/oracle/timesten/TimesTen/xifenfei/bin
./setuproot -install
This will move the TimesTen startup script into its appropriate location.

The startup script is currently located here :
'/home/oracle/timesten/TimesTen/xifenfei/startup/tt_xifenfei'.

The documentation was not installed.
To manually install the documentation, run the command 'setup.sh -installDoc'

The 11.2.2.2 Release Notes are located here :
'/home/oracle/timesten/TimesTen/xifenfei/README.html'

Starting the daemon ...
TimesTen Daemon startup OK.
End of TimesTen installation.

--设置开机启动
[root@bas linux8664]# cd /home/oracle/timesten/TimesTen/xifenfei/bin/
[root@bas bin]# ./setuproot -install
Would you like to install the TimesTen daemon startup scripts into /etc/init.d? [ yes ] yes
Copying /home/oracle/timesten/TimesTen/xifenfei/startup/tt_xifenfei to /etc/init.d

Successfully installed the following scripts :
/etc/init.d/tt_xifenfei
/etc/rc.d/rc0.d/K45tt_xifenfei
/etc/rc.d/rc1.d/K45tt_xifenfei
/etc/rc.d/rc2.d/S90tt_xifenfei
/etc/rc.d/rc3.d/S90tt_xifenfei
/etc/rc.d/rc5.d/S90tt_xifenfei
/etc/rc.d/rc6.d/K45tt_xifenfei

2.配置DSN

[root@bas info]# pwd
/home/oracle/timesten/TimesTen/xifenfei/info
[root@bas info]# more sys.odbc.ini
[ODBC Data Sources]
TT_1122=TimesTen 11.2.2 Driver

[TT_1122]
Driver=/home/oracle/timesten/TimesTen/xifenfei/lib/libtten.so
DataStore=/home/oracle/timesten/TimesTen/xifenfei/info/TT_1122
DatabaseCharacterSet=ZHS16GBK
PermSize=300
TempSize=64
OracleNetServiceName=orcl_tt

Driver = the TimesTen Direct Linked ODBC Driver
DataStore = the location and the name of the database files
LogDir = the directory for the transaction logs
PermSize = the size of the permanent region of the database. In the above example, this is configured for 40MB
TempSize = the size of the temporary region of the database. In the above example, this is configured for 32MB
DatabaseCharacterSet = the character set used by the database
OracleNetServiceName = the TNS service name to the Oracle database. This attribute is required for In-Memory Database Cache only.

3.查看相关文件和进程

[root@bas info]# ps -ef|grep timesten|grep -v grep
oracle   30391     1  0 00:01 ?        00:00:00 /home/oracle/timesten/TimesTen/xifenfei/bin/timestend -initfd 13
oracle   30395 30391  0 00:01 ?        00:00:00 /home/oracle/timesten/TimesTen/xifenfei/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1000000 -facility user
oracle   30396 30391  0 00:01 ?        00:00:00 /home/oracle/timesten/TimesTen/xifenfei/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1000001 -facility user
oracle   30397 30391  0 00:01 ?        00:00:00 /home/oracle/timesten/TimesTen/xifenfei/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1000002 -facility user
oracle   30398 30391  0 00:01 ?        00:00:00 /home/oracle/timesten/TimesTen/xifenfei/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1000003 -facility user
oracle   30400 30391  0 00:01 ?        00:00:00 /home/oracle/timesten/TimesTen/xifenfei/bin/ttcserver -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1000004 -p 53397 -facility user -group oinstall

[root@bas TimesTen]# pwd
/home/oracle/timesten/TimesTen
[root@bas TimesTen]# ls -l
total 4
drwxr-x---  17 oracle oinstall 4096 Feb 12 00:01 xifenfei
[root@bas TimesTen]# cd xifenfei
[root@bas xifenfei]# ls -l
total 140
drwxr-x---  4 oracle oinstall  4096 Feb 12 00:01 3rdparty
drwxr-x---  2 oracle oinstall  4096 Feb 12 00:43 bin
drwxr-x---  3 oracle oinstall  4096 Dec 24 00:23 include
drwxr-x---  2 oracle oinstall  4096 Feb 12 00:18 info
drwxr-x---  2 oracle oinstall  4096 Feb 12 00:00 lib
drwxr-x---  2 oracle oinstall  4096 Dec 24 00:23 mibs
drwxr-x---  3 oracle oinstall  4096 Dec 24 00:23 network
drwxr-x---  3 oracle oinstall  4096 Dec 24 00:23 nls
drwxr-x---  2 oracle oinstall  4096 Dec 24 00:23 oraclescripts
drwxr-x---  4 oracle oinstall  4096 Feb 11 23:59 PERL
drwxr-x---  8 oracle oinstall  4096 Dec 24 00:23 plsql
-r--r-----  1 oracle oinstall 74764 Feb 12 00:00 README.html
drwxr-x---  2 oracle oinstall  4096 Feb 11 23:59 startup
drwxr-x---  2 oracle oinstall  4096 Dec 24 00:23 support
drwxrw----  5 oracle oinstall  4096 Feb 12 00:00 ttclasses
drwxr-x---  3 oracle oinstall  4096 Feb 11 23:59 ttoracle_home

4.试用Timesten

[oracle@bas bin]$ ttisql -version
TimesTen Release 11.2.2.2.0
[oracle@bas info]$ ttisql TT_1122

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=TT_1122";

Warning 01S00: Undefined connection attribute "ConnectionCharacte" on line 144 of file /home/oracle/timesten/TimesTen/xifenfei/info/sys.odbc.ini

Connection successful: DSN=TT_1122;UID=oracle;DataStore=/home/oracle/timesten/TimesTen/xifenfei/info/TT_1122;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/timesten/TimesTen/xifenfei/lib/libtten.so;PermSize=300;TempSize=64;TypeMode=0;OracleNetServiceName=orcl_tt;
(Default setting AutoCommit=1)

Command> tables;
0 tables found.
Command> create table t_xff(id number,name varchar2(100));
Command> tables;
  ORACLE.T_XFF
1 table found.
Command> desc t_xff;

Table ORACLE.T_XFF:
  Columns:
    ID                              NUMBER
    NAME                            VARCHAR2 (100) INLINE

1 table found.
Command> insert into t_xff values(1,'xifenfei');
1 row inserted.
Command> insert into t_xff values(2,'www.orasos.com');
1 row inserted.
Command> commit;
Command> select * from t_xff;
< 1, xifenfei >
< 2, www.orasos.com >
2 rows found.
Command> update t_xff set name='xff' where id=1;
1 row updated.
Command> select * from t_xff;
< 1, xff >
< 2, www.orasos.com >
2 rows found.
Command> delete from t_xff where id=1;
1 row deleted.
Command> commit;
Command> select * from t_xff;
< 2, www.orasos.com >
1 row found.
Command> help
Use "help all" to get a description of all commands or use "help <cmd>" to
limit it to that command.

?                         free                      sqlcolumns               
!                         functions                 sqlgetinfo               
@@                        globalprocessing          sqlquerytimeout          
accept                    help                      sqlstatistics            
allfunctions              history                   sqltables                
allindexes                host                      statsclear               
allpackages               if                        statsestimate            
allprocedures             indexes                   statsupdate              
allsequences              isolation                 synonyms                 
allsynonyms               monitor                   tables                   
alltables                 tblsize                   timing                   
allviews                  multipleconnections       tryglobalprocessing      
autocommit                ncharencoding             tryhash                  
builtins                  optfirstrow               trymaterialize           
cachegroups               optprofile                trymergejoin             
cachesqlget               packages                  trynestedloopjoin        
clearhistory              passthrough               tryrowid                 
clienttimeout             prefetchcount             tryrowlocks              
close                     prepare                   tryserial                
closeall                  print                     trytbllocks              
cmdcache                  procedures                trytmphash               
columnlabels              quantify                  trytmptable              
commit                    prompt                    trytmprange              
commitdurable             remark                    tryrange                 
compact                   repschemes                undefine                 
compare                   retryconnect              unsetjoinorder           
connect                   rollback                  unsetuseindex            
define                    run                       use                      
describe                  savehistory               variable                 
disconnect                sequences                 verbosity                
dssize                    set                       version                  
e:                        setjoinorder              vertical                 
exec                      setuseindex               views                    
execandfetch              setvariable               waitfor                  
exit                      show                      whenever                 
explain                   showjoinorder             xlabookmarkdelete        
fetchall                  showplan                  <sql_statement>          
fetchone                  sleep                                              

Command> exit
Disconnecting...
Done.

专业Oracle数据库恢复技术支持

专业Oracle数据库恢复，或许是您恢复数据的最后机会@phone:17813235971

Daily Archives: 2012 年 02 月 12 日

shell处理alert日志

Timesten安装与试用

S	M	T	W	T	F	S
« Jan				Mar »
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29