Locations of visitors to this page

Thursday, March 26, 2009

monitoring network activity of processes 查看进程网络活动

monitoring network activity of processes
监控进程网络活动


1. 用strace跟踪进程发出的网络相关的系统调用
strace -t -e trace=network -e signal=\!alrm -p $(pgrep -u s3op2 emulator)
[root@DEV-Blur-DB-1 ~]# strace -t -e trace=network -e signal=\!alrm -p $(pgrep -u s3op2 emulator)
Process 9744 attached - interrupt to quit
[ Process PID=9744 runs in 32 bit mode. ]
08:00:38 recv(11, "OPEN\36\2\0\0\0\0\0\0007\0\0\0\261\21\0\0\260\257\272\261shell:ex"..., 8729, 0) = 79
08:00:38 send(11, "OKAY\1\0\0\0\36\2\0\0\0\0\0\0\0\0\0\0\260\264\276\246", 24, 0) = 24
...
08:01:30 recv(11, "OKAY\36\2\0\0\1\0\0\0\0\0\0\0\0\0\0\0\260\264\276\246", 4162, 0) = 24
08:01:30 socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 12
08:01:30 setsockopt(12, SOL_SOCKET, SO_REUSEADDR, [1], 4) = 0
08:01:30 setsockopt(12, SOL_SOCKET, SO_OOBINLINE, [1], 4) = 0
08:01:30 connect(12, {sa_family=AF_INET, sin_port=htons(5222), sin_addr=inet_addr("124.65.150.30")}, 16) = -1 EINPROGRESS (Operation now in progress)
08:01:30 send(12, "", 0, 0)             = 0
08:01:31 send(11, "WRTE\1\0\0\0\36\2\0\0\246\0\0\0\0379\0\0\250\255\253\27203-26 08"..., 190, 0) = 190
08:01:31 recv(11, "OKAY\36\2\0\0\1\0\0\0\0\0\0\0\0\0\0\0\260\264\276\246", 4138, 0) = 24
08:01:31 send(12, "<stream:stream token=\"fake-token"..., 207, 0) = 207
08:01:31 recv(12, "<stream:stream xmlns=\'jabber:cli"..., 8760, 0) = 182
08:01:31 send(11, "WRTE\1\0\0\0\36\2\0\0c\0\0\0N\37\0\0\250\255\253\27203-26 08"..., 123, 0) = 123
08:01:31 recv(11, "OKAY\36\2\0\0\1\0\0\0\0\0\0\0\0\0\0\0\260\264\276\246", 4114, 0) = 24
08:01:31 send(11, "WRTE\1\0\0\0\36\2\0\0\205\0\0\0\21*\0\0\250\255\253\27203-26 08"..., 157, 0) = 157
08:01:31 recv(11, "OKAY\36\2\0\0\1\0\0\0\0\0\0\0\0\0\0\0\260\264\276\246", 4090, 0) = 24
08:01:31 recv(12, "<stream:features><starttls xmlns"..., 8578, 0) = 136
...
08:03:45 recv(11, "OKAY\36\2\0\0\1\0\0\0\0\0\0\0\0\0\0\0\260\264\276\246", 2362, 0) = 24
08:03:45 send(11, "WRTE\1\0\0\0\36\2\0\0t\0\0\0B%\0\0\250\255\253\27203-26 08"..., 140, 0) = 140
08:03:45 recv(11, "OKAY\36\2\0\0\1\0\0\0\0\0\0\0\0\0\0\0\260\264\276\246", 2338, 0) = 24
Process 9744 detached

或用aix,solaris下的truss


2. tcpdump跟踪网络活动
tcpdump -ibond0 host 124.65.150.30
[root@DEV-Blur-DB-1 ~]# tcpdump -ibond0 host 124.65.150.30
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on bond0, link-type EN10MB (Ethernet), capture size 96 bytes
08:01:30.963078 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: S 2339214547:2339214547(0) win 5840 <mss 1460,sackOK,timestamp 3879708500 0,nop,wscale 7>
08:01:30.963687 IP 124.65.150.30.xmpp-client > DEV-Blur-DB-1.s3lab.mot.com.40181: S 4093453715:4093453715(0) ack 2339214548 win 4380 <mss 1460,nop,wscale 0,nop,nop,timestamp 1103908394 3879708500,sackOK,eol>
08:01:30.963712 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: . ack 1 win 46 <nop,nop,timestamp 3879708500 1103908394>
08:01:31.143569 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: P 1:208(207) ack 1 win 46 <nop,nop,timestamp 3879708680 1103908394>
08:01:31.144866 IP 124.65.150.30.xmpp-client > DEV-Blur-DB-1.s3lab.mot.com.40181: P 1:183(182) ack 208 win 4587 <nop,nop,timestamp 1103908575 3879708680>
08:01:31.144900 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: . ack 183 win 54 <nop,nop,timestamp 3879708681 1103908575>
08:01:31.244338 IP 124.65.150.30.xmpp-client > DEV-Blur-DB-1.s3lab.mot.com.40181: P 183:319(136) ack 208 win 4587 <nop,nop,timestamp 1103908675 3879708681>
08:01:31.244383 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: . ack 319 win 63 <nop,nop,timestamp 3879708781 1103908675>
08:01:34.726795 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: P 208:274(66) ack 319 win 63 <nop,nop,timestamp 3879712263 1103908675>
08:01:34.728264 IP 124.65.150.30.xmpp-client > DEV-Blur-DB-1.s3lab.mot.com.40181: P 319:1130(811) ack 274 win 4653 <nop,nop,timestamp 1103912158 3879712263>
08:01:34.728294 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: . ack 1130 win 76 <nop,nop,timestamp 3879712265 1103912158>
08:01:35.932557 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: P 274:464(190) ack 1130 win 76 <nop,nop,timestamp 3879713469 1103912158>
08:01:35.935533 IP 124.65.150.30.xmpp-client > DEV-Blur-DB-1.s3lab.mot.com.40181: P 1130:1181(51) ack 464 win 4843 <nop,nop,timestamp 1103913366 3879713469>
08:01:35.935588 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: . ack 1181 win 76 <nop,nop,timestamp 3879713472 1103913366>
08:01:36.499741 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: P 464:925(461) ack 1181 win 76 <nop,nop,timestamp 3879714036 1103913366>
08:01:36.501393 IP 124.65.150.30.xmpp-client > DEV-Blur-DB-1.s3lab.mot.com.40181: P 1181:1394(213) ack 925 win 5304 <nop,nop,timestamp 1103913932 3879714036>
08:01:36.501422 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: . ack 1394 win 88 <nop,nop,timestamp 3879714038 1103913932>
08:01:36.601453 IP 124.65.150.30.xmpp-client > DEV-Blur-DB-1.s3lab.mot.com.40181: P 1394:1697(303) ack 925 win 5304 <nop,nop,timestamp 1103914032 3879714038>
08:01:36.601495 IP DEV-Blur-DB-1.s3lab.mot.com.40181 > 124.65.150.30.xmpp-client: . ack 1697 win 101 <nop,nop,timestamp 3879714138 1103914032>
08:03:06.161609 IP DEV-Blur-DB-1.s3lab.mot.com.41925 > 124.65.150.30.teradataordbms: S 2434143153:2434143153(0) win 5840 <mss 1460,sackOK,timestamp 3879803697 0,nop,wscale 7>
08:03:06.162257 IP 124.65.150.30.teradataordbms > DEV-Blur-DB-1.s3lab.mot.com.41925: S 674537425:674537425(0) ack 2434143154 win 4380 <mss 1460,nop,wscale 0,nop,nop,timestamp 1104003593 3879803697,sackOK,eol>
...

85 packets captured
171 packets received by filter
0 packets dropped by kernel
[root@DEV-Blur-DB-1 ~]#
不能跟踪某一个进程

也可用其它工具, 比如wireshark, solaris的snoop等



3. auditctl审计进程的系统调用
添加审计规则
auditctl -a entry,always -F arch=b32 -S socketcall -F pid=$(pgrep -u s3op2 emulator) -k emulator
auditctl -l
ausearch -ts today -k emulator
tail -f /var/log/audit/audit.log
查看audit.log日志或ausearch查看
[root@DEV-Blur-DB-1 ~]# auditctl -a entry,always -F arch=b32 -S socketcall -F pid=$(pgrep -u s3op2 emulator) -k emulator
[root@DEV-Blur-DB-1 ~]# auditctl -l
LIST_RULES: entry,always arch=1073741827 (0x40000003) pid=9744 (0x2610) key=emulator syscall=socketcall
[root@DEV-Blur-DB-1 ~]# tail -f /var/log/audit/audit.log
...
type=SYSCALL msg=audit(1238054490.902:157569): arch=40000003 syscall=102 success=yes exit=209 a0=9 a1=ff918fe4 a2=ab906ac a3=ab906ac items=0 ppid=9743 pid=9744 auid=4294967295 uid=1003 gid=1002 euid=1003 suid=1003 fsuid=1003 egid=1002 sgid=1002 fsgid=1002 tty=pts61 ses=4294967295 comm="emulator" exe="/data/s3op2/morrison_viper_0_6_10_emulator_linux/bin/emulator" key="emulator"
type=SYSCALL msg=audit(1238054490.902:157570): arch=40000003 syscall=102 success=yes exit=24 a0=a a1=ff919000 a2=ff919200 a3=9abca0 items=0 ppid=9743 pid=9744 auid=4294967295 uid=1003 gid=1002 euid=1003 suid=1003 fsuid=1003 egid=1002 sgid=1002 fsgid=1002 tty=pts61 ses=4294967295 comm="emulator" exe="/data/s3op2/morrison_viper_0_6_10_emulator_linux/bin/emulator" key="emulator"
type=SYSCALL msg=audit(1238054490.961:157571): arch=40000003 syscall=102 success=yes exit=12 a0=1 a1=ff918ea4 a2=ab8ce88 a3=2000 items=0 ppid=9743 pid=9744 auid=4294967295 uid=1003 gid=1002 euid=1003 suid=1003 fsuid=1003 egid=1002 sgid=1002 fsgid=1002 tty=pts61 ses=4294967295 comm="emulator" exe="/data/s3op2/morrison_viper_0_6_10_emulator_linux/bin/emulator" key="emulator"
type=SYSCALL msg=audit(1238054490.961:157572): arch=40000003 syscall=102 success=yes exit=0 a0=e a1=ff918e84 a2=ab8ce88 a3=2000 items=0 ppid=9743 pid=9744 auid=4294967295 uid=1003 gid=1002 euid=1003 suid=1003 fsuid=1003 egid=1002 sgid=1002 fsgid=1002 tty=pts61 ses=4294967295 comm="emulator" exe="/data/s3op2/morrison_viper_0_6_10_emulator_linux/bin/emulator" key="emulator"
type=SYSCALL msg=audit(1238054490.961:157573): arch=40000003 syscall=102 success=yes exit=0 a0=e a1=ff918e84 a2=ab8ce88 a3=2000 items=0 ppid=9743 pid=9744 auid=4294967295 uid=1003 gid=1002 euid=1003 suid=1003 fsuid=1003 egid=1002 sgid=1002 fsgid=1002 tty=pts61 ses=4294967295 comm="emulator" exe="/data/s3op2/morrison_viper_0_6_10_emulator_linux/bin/emulator" key="emulator"
type=SYSCALL msg=audit(1238054490.962:157574): arch=40000003 syscall=102 success=no exit=-115 a0=3 a1=ff918ea4 a2=ab8ce88 a3=2000 items=0 ppid=9743 pid=9744 auid=4294967295 uid=1003 gid=1002 euid=1003 suid=1003 fsuid=1003 egid=1002 sgid=1002 fsgid=1002 tty=pts61 ses=4294967295 comm="emulator" exe="/data/s3op2/morrison_viper_0_6_10_emulator_linux/bin/emulator" key="emulator"
type=SOCKADDR msg=audit(1238054490.962:157574): saddr=020014667C41961E40CF990A0F930A08
type=SYSCALL msg=audit(1238054490.964:157575): arch=40000003 syscall=102 success=yes exit=0 a0=9 a1=ff919080 a2=ff919180 a3=9abca0 items=0 ppid=9743 pid=9744 auid=4294967295 uid=1003 gid=1002 euid=1003 suid=1003 fsuid=1003 egid=1002 sgid=1002 fsgid=1002 tty=pts61 ses=4294967295 comm="emulator" exe="/data/s3op2/morrison_viper_0_6_10_emulator_linux/bin/emulator" key="emulator"
type=SYSCALL msg=audit(1238054491.073:157576): arch=40000003 syscall=102 success=yes exit=190 a0=9 a1=ff918fe4 a2=ab906ac a3=ab906ac items=0 ppid=9743 pid=9744 auid=4294967295 uid=1003 gid=1002 euid=1003 suid=1003 fsuid=1003 egid=1002 sgid=1002 fsgid=1002 tty=pts61 ses=4294967295 comm="emulator" exe="/data/s3op2/morrison_viper_0_6_10_emulator_linux/bin/emulator" key="emulator"
type=SYSCALL msg=audit(1238054491.073:157577): arch=40000003 syscall=102 success=yes exit=24 a0=a a1=ff919000 a2=ff919200 a3=9abca0 items=0 ppid=9743 pid=9744 auid=4294967295 uid=1003 gid=1002 euid=1003 suid=1003 fsuid=1003 egid=1002 sgid=1002 fsgid=1002 tty=pts61 ses=4294967295 comm="emulator" exe="/data/s3op2/morrison_viper_0_6_10_emulator_linux/bin/emulator" key="emulator"
...
不好用, 结果看不懂, 还不如strace
syscall=102表示sys_socketcall, a0=?是socketcall函数的第一个参数, 这里表示套接字调用, 参考/usr/include/linux/net.h

删除规则
[root@DEV-Blur-DB-1 ~]# auditctl -d entry,always -F arch=b32 -S socketcall -F pid=$(pgrep -u s3op2 emulator) -k emulator
[root@DEV-Blur-DB-1 ~]# auditctl -l
No rules
[root@DEV-Blur-DB-1 ~]#

另外参考
/usr/include/asm-x86_64/unistd.h
/usr/include/asm-i386/unistd.h
/usr/include/linux/audit.h
等头文件


4. systemtap
需要安装当前内核版本对应的kernel-debuginfo-common和kernel-debuginfo
RPM安装包可从debuginfo.centos.org下载
[root@DEV-Blur-DB-1 ~]# rpm -ivh kernel-debuginfo-common-2.6.18-92.el5.x86_64.rpm
Preparing...                ########################################### [100%]
   1:kernel-debuginfo-common########################################### [100%]
[root@DEV-Blur-DB-1 ~]# rpm -ivh kernel-debuginfo-2.6.18-92.el5.x86_64.rpm
Preparing...                ########################################### [100%]
   1:kernel-debuginfo       ########################################### [100%]
[root@DEV-Blur-DB-1 ~]# ll

cat /usr/share/doc/systemtap-0.6.2/examples/socket-trace.stp
#! /usr/bin/env stap
probe kernel.function("*@net/socket.c").call {
  printf ("%s -> %s\n", thread_indent(1), probefunc())
}
probe kernel.function("*@net/socket.c").return {
  printf ("%s <- %s\n", thread_indent(-1), probefunc())
}
stap -v /usr/share/doc/systemtap-0.6.2/examples/socket-trace.stp

不会用,仅供参考
SystemTap Beginners Guide

solaris下可用dtrace





-fin-

Tuesday, March 24, 2009

prevent users using exp or imp 禁止用户使用导出和导入

how to prevent users using exp or imp

提问: 何如禁止用户使用exp或imp
回答: 可以, 但禁止了也不能保证数据安全


1.
参考文档
$ORACLE_HOME/rdbms/admin/catexp.sql

Default Privileges Assigned To PUBLIC In 10.2.0.4 Database

只要从PUBLIC回收一些视图的权限, 便可以禁止用户使用exp或imp


2.
exp加trace=y, 跟踪一下
exp a/a file=a.dmp rows=n tables=\(no_such_table\) log=a.exp.log trace=y
[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\) log=a.exp.log trace=y

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 05:51:18 2009

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
EXP-00011: A.NO_SUCH_TABLE does not exist
Export terminated successfully with warnings.
[oracle@DEV-RPT-2 ~]$

查看跟踪文件, 查看exp查询的EXU,IMP视图等
sed -n '
/EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
/IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)
[oracle@DEV-RPT-2 ~]$ sed -n '
> /EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
> /IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
> ' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)
EXU8OPT
EXU8USRU
EXU8FUL
EXU8VER
EXU81JAVT
EXU9XDBUID
EXU81CSC
EXU9GSAS
EXU9NLS
EXU10ADEFPSWITCHES
EXU10R2DEFPSWITCHES
EXU8NXPU
EXU10TABS
EXU8NXPU
[oracle@DEV-RPT-2 ~]$



3. 跟踪imp
imp a/a file=a.dmp show=y log=a.imp.log trace=y
[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y log=a.imp.log trace=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 05:53:33 2009

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing A's objects into A
Import terminated successfully without warnings.
[oracle@DEV-RPT-2 ~]$

查看跟踪文件
sed -n '
/EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
/IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)
[oracle@DEV-RPT-2 ~]$ sed -n '
> /EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
> /IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
> ' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)
imp
exu8opt
IMP9USR
EXU8FUL
EXU9NLS
IMP9COMPAT
SET_IMP_TIMEZONE
SET_IMPORT_MODE
SET_IMP_EVENTS
[oracle@DEV-RPT-2 ~]$


4. 根据前面文档和跟踪得出的结果, 取消exp,imp权限

禁止exp
revoke select on sys.exu8usru from public;
[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\)

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 07:57:10 2009

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


EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$
[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 07:57:41 2009

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing A's objects into A
Import terminated successfully without warnings.
[oracle@DEV-RPT-2 ~]$
grant select on sys.exu8usru to public;

禁止imp
revoke select on sys.imp9usr from public;
[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\)

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 07:58:27 2009

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
EXP-00011: A.NO_SUCH_TABLE does not exist
Export terminated successfully with warnings.
[oracle@DEV-RPT-2 ~]$
[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 07:58:35 2009

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$
grant select on sys.imp9usr to public;

禁止exp和imp
revoke select on sys.exu8opt from public;
[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\)

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 07:59:45 2009

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


EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$
[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 07:59:51 2009

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


IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$
grant select on sys.exu8opt to public;


以上是10.2.0.4的测试结果, 其它版本的没试, 应该类似



外部链接:
Prevent the use of EXP and IMP for specified Oracle Users
Trace option in Export and Import
Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump
Compatibility Matrix for Export And Import Between Different Oracle Versions



-fin-

Wednesday, March 18, 2009

bitmap conversion 位图转换

bitmap conversion
位图转换

在多个字段连接, 没有联合索引, 高基数(high-cardinality)的情况下, 可能会产生位图转换(bitmap conversion)

Jonathan Lewis "Cost-Based Oracle Fundamentals" P456:
B-tree to Bitmap Conversions
One of the optimizer’s strategies is to range scan B-tree indexes to acquire lists of rowids, convert the lists of rowids into the equivalent bitmaps, and perform bitwise operations to identify a small set of rows. Effectively, the optimizer can take sets of rowids from index range scans and convert them to bitmap indexes on the fly before doing an index_combine on the resulting bitmap indexes.
In 8i, only tables with existing bitmap indexes could be subject to this treatment, unless the parameter _b_tree_bitmap_plans had been set to relax the requirement for a preexisting bitmap index.
In 9i, the default value for this parameter changed from false to true—so you may see execution plans involving bitmap conversions after you’ve upgraded, even though you don’t have a single bitmap index in your database. Unfortunately, because of the implicit packing assumption that the optimizer uses for bitmap indexes, this will sometimes be a very bad idea.
As a related issue, this change can make it worth using the minimize_records_per_block option on all your important tables.


比如:
conn a/a
set autot off
drop table t1;
create table t1 as
select floor(dbms_random.value(1,90000)) a,
       floor(dbms_random.value(1,50000)) b,
       floor(dbms_random.value(1,10000)) c,
       cast('1' as char(2000)) x,
       '111111' aa
  from dual
connect by level <= 100000;
create index ind_t1_a on t1(a);
create index ind_t1_b on t1(b);
create index ind_t1_c on t1(c);
analyze table t1 compute statistics for table for all columns for all indexes;
SQL> set autot trace exp stat
SQL> select aa from t1 where (a between 1000 and 3000 or a between 9010 and 9015) and ((b between 3000 and 7000) or (c between 3000 and 9000));

1428 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2150721541

------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |  1429 | 24293 |       |  1825   (1)| 00:00:22 |
|   1 |  TABLE ACCESS BY INDEX ROWID      | T1       |  1429 | 24293 |       |  1825   (1)| 00:00:22 |
|   2 |   BITMAP CONVERSION TO ROWIDS     |          |       |       |       |            |          |
|   3 |    BITMAP AND                     |          |       |       |       |            |          |
|   4 |     BITMAP OR                     |          |       |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|          |       |       |       |            |          |
|   6 |       SORT ORDER BY               |          |       |       |       |            |          |
|*  7 |        INDEX RANGE SCAN           | IND_T1_A |       |       |       |     7   (0)| 00:00:01 |
|   8 |      BITMAP CONVERSION FROM ROWIDS|          |       |       |       |            |          |
|   9 |       SORT ORDER BY               |          |       |       |       |            |          |
|* 10 |        INDEX RANGE SCAN           | IND_T1_A |       |       |       |     2   (0)| 00:00:01 |
|  11 |     BITMAP OR                     |          |       |       |       |            |          |
|  12 |      BITMAP CONVERSION FROM ROWIDS|          |       |       |       |            |          |
|  13 |       SORT ORDER BY               |          |       |       |  1896K|            |          |
|* 14 |        INDEX RANGE SCAN           | IND_T1_C |       |       |       |   127   (0)| 00:00:02 |
|  15 |      BITMAP CONVERSION FROM ROWIDS|          |       |       |       |            |          |
|  16 |       SORT ORDER BY               |          |       |       |   264K|            |          |
|* 17 |        INDEX RANGE SCAN           | IND_T1_B |       |       |       |    19   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("A">=1000 AND "A"<=3000)
  10 - access("A">=9010 AND "A"<=9015)
  14 - access("C">=3000 AND "C"<=9000)
  17 - access("B">=3000 AND "B"<=7000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1560  consistent gets
          0  physical reads
          0  redo size
      25080  bytes sent via SQL*Net to client
       1537  bytes received via SQL*Net from client
         97  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
       1428  rows processed

SQL>
由多个字段索引取得的ROWID转换成位图, 然后进行与或操作, 最后转换回ROWID

修改参数,禁止位图转换
alter session set "_b_tree_bitmap_plans"=false;
SQL> select aa from t1 where (a between 1000 and 3000 or a between 9010 and 9015) and ((b between 3000 and 7000) or (c between 3000 and 9000));

1428 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4246370027

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |  1301 | 22117 |  2071   (1)| 00:00:25 |
|   1 |  CONCATENATION               |          |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1       |     4 |    68 |     8   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_T1_A |     6 |       |     2   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1       |  1297 | 22049 |  2063   (1)| 00:00:25 |
|*  5 |    INDEX RANGE SCAN          | IND_T1_A |  2055 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C">=3000 AND "C"<=9000 OR "B"<=7000 AND "B">=3000)
   3 - access("A">=9010 AND "A"<=9015)
   4 - filter("C">=3000 AND "C"<=9000 OR "B"<=7000 AND "B">=3000)
   5 - access("A">=1000 AND "A"<=3000)
       filter(LNNVL("A"<=9015) OR LNNVL("A">=9010))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2354  consistent gets
          0  physical reads
          0  redo size
      25080  bytes sent via SQL*Net to client
       1537  bytes received via SQL*Net from client
         97  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1428  rows processed

SQL>
位图转换减少了一致性读(consistent gets)的次数, 但增加了一些内存排序(sorts (memory))



奇怪的是, 如果WHERE条件中只查了一个字段, 也可能出现bitmap conversion
alter session set "_b_tree_bitmap_plans"=true;
set autot off
drop table t1;
create table t1 as
select level a,
       cast('1' as char(2000)) x,
       '111111' aa
  from dual
connect by level <= 100000
 order by dbms_random.value;
create index ind_t1_a on t1(a);
analyze table t1 compute statistics for table for all columns for all indexes;
SQL> set autot trace exp stat
SQL> select aa from t1 where a between 1 and 3 or a between 10 and 15;

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 768713482

---------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |     7 |    70 |    13  (16)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1       |     7 |    70 |    13  (16)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |          |       |       |            |          |
|   3 |    BITMAP OR                     |          |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|          |       |       |            |          |
|   5 |      SORT ORDER BY               |          |       |       |            |          |
|*  6 |       INDEX RANGE SCAN           | IND_T1_A |       |       |     2   (0)| 00:00:01 |
|   7 |     BITMAP CONVERSION FROM ROWIDS|          |       |       |            |          |
|   8 |      SORT ORDER BY               |          |       |       |            |          |
|*  9 |       INDEX RANGE SCAN           | IND_T1_A |       |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("A">=10 AND "A"<=15)
   9 - access("A">=1 AND "A">=3)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        600  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL>
用use_concat提示后变成
SQL> select /*+use_concat*/ aa from t1 where a between 1 and 3 or a between 10 and 15;

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4246370027

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     7 |    70 |    13   (0)| 00:00:01 |
|   1 |  CONCATENATION               |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1       |     2 |    20 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_T1_A |     2 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T1       |     5 |    50 |     8   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IND_T1_A |     5 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A">=1 AND "A"<=3)
   5 - access("A">=10 AND "A"<=15)
       filter(LNNVL("A"<=3) OR LNNVL("A">=1))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        600  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

有必要bitmap conversion吗, 不用它多好啊, 还能少2次内存排序




外部链接:
Execution plan operation shows bitmap conversion from rowids
Optimization of large inlists/multiple OR`s
Using the USE_CONCAT hint with IN/OR Statements
Oracle Database 10g Performance Tuning Tips & Techniques By Richard J. Niemiec
NO_EXPAND Hint
Table 19-3 OPERATION and OPTIONS Values Produced by EXPLAIN PLAN
Sorry I did not phrase my question right



-fin-

Monday, March 16, 2009

how to find solaris serial number 如何得到SUN服务器序列号

How To Find Solaris Chassis Serial Number
如何在Solaris系统中得到SUN服务器的序列号

Getting Hardware Information in Solaris:

The serial reported on a Sun in the boot banner is just the hostid in decimal and is not related to the actual chassis serial number (CSN)

On older Sun platforms, the only source for the CSN is the tag on the chassis or the “yellow sheet” that ships with the box.

To make it software-accessible, you might want to use sneep , as was mentioned already.
Sneep can also store and protect almost any other platform-specific data that you might need, like Asset tag or contact information.

Many of the newer platforms include some kind of built-in CSN support, but Sun is still (as of 11/2007) working on a standard way to get at it; some use prtdiag, some use SMBIOS or IPMI or FRUID, some put it in the LOM or ILOM or other System Controller.

Sneep 1.79 knows how to get at some of these, and future updates will try to improve on this and track the upcoming standard as it gets defined and implemented.

As of 11/2007, the sneep download at http://www.sun.com/download/products.xml?id=4304155a is 1.76
and it doesn’t know anything that you haven’t told it
(unless sneep can find the serial in your explorer or CST configuration)

If you have to have 1.79 (which also fixes a problem with eeprom handling by Veritas vxfs 4.1 and above), make a request on sneep-support@sun.com

Right now the best solution is to use sneep and to tell it your serial number once. Sneep will put it in the eeprom (on SPARC) protect it from loss or damage.

It does the best it can on x86, but there is no true eeprom.
At least you have a simple, standard way to get the serial back .

sneep-support@sun.com


SNEEP(Serial Number in EEPROM):
Before sneep, only a few of the newer Sun platforms had a mechanism to maintain a software-accessible serial number. Among those platforms, there is a wide variety of mechanisms for this, making consistent access to this information difficult.

SNEEP FAQ:
Unknown Serial

I loaded sneep on my system and when I ran it, Sneep I loaded on my system and when I ran it,
sneep said that the serial was "unknown". sneep said that the serial was "unknown."
Why doesn't sneep know what it is? Why does not sneep know what it is?

Most Sun platforms have no way of reporting their serial number. Sun Most platforms have no way of reporting their serial number.
(until after they are sneeped) (until after they are sneeped)

Only a relatively small number of the newer Sun platforms Only a relatively small number of the newer Sun platforms
natively provide serial number data which can be read by sneep, natively provide serial number data which can be read by sneep,
but there are a variety of ways in which this data is provided. but there are a variety of ways in which this data is provided.





首先用SNEEP工具将序列号记录到EEPROM中
/opt/SUNWsneep/bin/sneep -s <serial number>
然后就可以查询了
/opt/SUNWsneep/bin/showplatform -p csn



以下命令似乎好像有时也能查到
System serial number
uname -X
command to find system serial number
prtdiag -v
Finding a Sun Hardware serial number
/usr/sfw/bin/ipmitool
ipmitool fru



-fin-

tar over ssh 通过ssh进行tar备份

tar over ssh
tar+ssh备份

1. 备份远程服务器上的文件到本地
如:
mkdir -p $HOME/dev-rpt-2
ssh oracle@dev-rpt-2 'tar -C $HOME -zcf - tmp' >$HOME/dev-rpt-2/oracle_dev-rpt-2_tmp.tar.gz

并解开:
ssh oracle@dev-rpt-2 'tar -C $HOME -zcf - tmp' |tar -C $HOME/dev-rpt-2 -zxvf -


2. 恢复远程备份文件到本地
mkdir -p $HOME/tmp2
ssh root@dev-db-1 'cat $HOME/dev-rpt-2/oracle_dev-rpt-2_tmp.tar.gz' | \
tar -C $HOME/tmp2 -zxvf -


3. 备份本地文件到远程服务器上
tar -C $HOME -zcf - tmp2 | \
ssh root@dev-db-1 'mkdir -p $HOME/dev-rpt-2 && cat >$HOME/dev-rpt-2/oracle_dev-rpt-2_tmp2.tar.gz'

并解压:
tar -C $HOME -zcf - tmp2 | \
ssh root@dev-db-1 'mkdir -p $HOME/dev-rpt-2 && tar -C $HOME/dev-rpt-2 -zxvf -'


4. 恢复本地备份文件到远程服务器上
cat $HOME/dev-rpt-2/oracle_dev-rpt-2_tmp2.tar.gz | \
ssh oracle@dev-rpt-2 'mkdir $HOME/tmp3 && tar -C $HOME/tmp3 -zxvf -'


-fin-

Wednesday, March 11, 2009

base conversion using command line 命令行中的进制转换

base conversion using command line
命令行进制转换


1. shell内置的算术扩展功能(Shell expansion- Bash Guide for Beginners)

16进制->10进制:
# echo $((0xFACE))
64206
不区分大小写
# echo $((0xface))
64206

或写成"BASE#N"的形式
# echo $((16#FACE))
64206

10进制->16进制:
好像不行


2. coreutils的printf命令

16进制->10进制:
# printf '%d\n' 0xFACE
64206
格式语法如同C
# printf '%08d\n' 0xFACE
64206

10进制->16进制:
# printf '%#08X\n' 64206
0X00FACE


3. bc(Basic Calculator)计算器

16进制->10进制:
# echo 'ibase=16;obase=A;FACE' | bc
64206
第二个参数obase必须是16进制表示的, 大写的
要转换的数值也必须是大写的

10进制->16进制:
# echo 'ibase=10;obase=16;64206'|bc
FACE


4.awk脚本

16进制->10进制:
用printf函数转
# awk 'BEGIN {printf "%d\n", 0xFACE}'
64206
或用non-decimal-data选项
# echo 0xFACE| awk --non-decimal-data '{print $1}'
64206

10进制->16进制:
# awk 'BEGIN {printf "%#08X\n", 64206}'
0X00FACE


5. dc(Digital Calculator)计算器

16进制->10进制:
# echo "16i FACE p"|dc
64206

10进制->16进制:
# echo "16o 64206 p" | dc
FACE


6. perl脚本

16进制->10进制:
# perl -e 'printf ("%d\n", 0xFACE)'
64206
带上输出格式
# perl -e 'printf ("%08d\n", 0xFACE)'
00064206

10进制->16进制:
# perl -e 'printf ("%#08X\n", 64206)'
0X00FACE


7. python

16进制->10进制:
# python -c 'print "%d" % int("0xFACE",16)'
64206
格式输出:
# python -c 'print "%08d" % int("0xFACE",16)'
00064206

10进制->16进制:
# python -c 'print "%#08X" % 64206'
0X00FACE



-fin-

Thursday, March 5, 2009

how to get current SCN 如何获得当前系统变更号

how to get current SCN(System Change Number)
如何获得当前系统变更号

9i及以后
select current_scn from v$database;
select dbms_flashback.get_system_change_number from dual;

9i之前
select to_number(m.ksmmmval,'XXXXXXXXXXXXXXXX')
  from x$ksmmem m,
       x$ksmfsv f
 where m.addr = f.ksmfsadr
   and f.ksmfsnam = 'kcsgscn_';


比如:
select current_scn,
       dbms_flashback.get_system_change_number,
       (select to_number(m.ksmmmval,'XXXXXXXXXXXXXXXX')
          from x$ksmmem m,
               x$ksmfsv f
         where m.addr = f.ksmfsadr
           and f.ksmfsnam = 'kcsgscn_')
  from v$database;
SQL> select current_scn,
  2         dbms_flashback.get_system_change_number,
  3         (select to_number(m.ksmmmval,'XXXXXXXXXXXXXXXX')
  4            from x$ksmmem m,
  5                 x$ksmfsv f
  6           where m.addr = f.ksmfsadr
  7             and f.ksmfsnam = 'kcsgscn_')
  8    from v$database;

CURRENT_SCN GET_SYSTEM_CHANGE_NUMBER
----------- ------------------------
(SELECTTO_NUMBER(M.KSMMMVAL,'XXXXXXXXXXXXXXXX')FROMX$KSMMEMM,X$KSMFSVFWHEREM.ADDR=F.KSMFSADRANDF.KSMFSNAM='KCSGSCN_')
---------------------------------------------------------------------------------------------------------------------
    2429997                  2429997
                                                                                                              2429997


SQL>



-fin-

Wednesday, March 4, 2009

creating standby database with image copies 使用映像拷贝创建备用数据库

creating standby database with image copies
使用复制文件备份方式创建备用数据库


1.
主库停远程归档
sqlplus /nolog <<EOF
conn / as sysdba
alter system set log_archive_dest_2='';
alter system set log_archive_dest_state_2='enable';
exit
EOF


2.
删除备库
sqlplus /nolog <<EOF
conn / as sysdba
shutdown immediate
exit
EOF

cd /home/oracle/app/oracle/oradata
rm -rf test_s.bak
mv test_s test_s.bak
mkdir -p test_s/{archive,flash_recovery_area,utlfile}


3. NFS共享目录
备库共享数据文件目录, 让主库服务器访问, 主库备份到这个目录

备库开启NFS
su - root
service nfs start
exportfs -iv -o rw,no_root_squash dev-rpt-2:/home/oracle/app/oracle/oradata/test_s
[root@DEV-pxy-1 ~]# service nfs start
Starting NFS services:                                     [  OK  ]
Starting NFS quotas:                                       [  OK  ]
Starting NFS daemon:                                       [  OK  ]
Starting NFS mountd:                                       [  OK  ]
[root@DEV-pxy-1 ~]# exportfs -iv -o rw,no_root_squash dev-rpt-2:/home/oracle/app/oracle/oradata/test_s
exporting DEV-RPT-2.s3lab.mot.com:/home/oracle/app/oracle/oradata/test_s
[root@DEV-pxy-1 ~]#
no_root_squash保留root权限
首先要启portmap服务, 否则启nfs服务报错
Starting NFS services:  [  OK  ]
Starting NFS quotas: Cannot register service: RPC: Unable to receive; errno = Connection refused
rpc.rquotad: unable to register (RQUOTAPROG, RQUOTAVERS, udp).
[FAILED]
Starting NFS daemon: 
...

主库挂载NFS目录
su - root
mkdir -p /mnt/standby-db-oradata
mount -v -t nfs -o rw dev-pxy-1:/home/oracle/app/oracle/oradata/test_s /mnt/standby-db-oradata
[root@DEV-RPT-2 mnt]# mount -v -t nfs -o rw dev-pxy-1:/home/oracle/app/oracle/oradata/test_s /mnt/standby-db-oradata
dev-pxy-1:/home/oracle/app/oracle/oradata/test_s on /mnt/standby-db-oradata type nfs (rw,addr=192.168.12.51)
[root@DEV-RPT-2 mnt]# ls -l /mnt/standby-db-oradata
total 12
drwxr-x---  2 oracle oinstall 4096 Mar  4 02:53 archive
drwxr-x---  2 oracle oinstall 4096 Mar  4 02:53 flash_recovery_area
drwxr-x---  2 oracle oinstall 4096 Mar  4 02:53 utlfile
[root@DEV-RPT-2 mnt]#
挂载nfs最好加上oracle的建议参数 hard,bg,proto=tcp,suid,rsize=32768,wsize=32768,noac
(noac用于RAC环境)
比如: rw,bg,hard,rsize=32768,wsize=32768,nfsvers=3,proto=tcp,nointr,timeo=600
否则RMAN报错ORA-27054
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c1 channel at 03/04/2009 17:29:27
ORA-19504: failed to create file "/mnt/other/df_data01.dbf"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Add ...

如何取消NFS共享:
备库端先umount nfs目录, 主库端再用exportfs -u ... 取消共享, 最后停止nfs等服务


4. 查询数据库信息

查询系统变更号, 数据文件名等
sqlplus -s /nolog <<'EOF'
conn / as sysdba
set pages 9999 line 140
select current_scn from v$database;
col ts_name for a20
col df_name for a70
select df.file# df_no,ts.name ts_name,df.name df_name
  from v$datafile df, v$tablespace ts
 where df.ts# = ts.ts#
 order by df.file#;
select value from v$parameter where name='log_archive_format';
select * from (select resetlogs_id from v$archived_log order by completion_time desc) where rownum=1;
exit
EOF
[oracle@DEV-RPT-2 ~]$ sqlplus -s /nolog <<'EOF'
> conn / as sysdba
> set pages 9999 line 140
> select current_scn from v$database;
> col ts_name for a20
> col df_name for a70
> select df.file# df_no,ts.name ts_name,df.name df_name
>   from v$datafile df, v$tablespace ts
>  where df.ts# = ts.ts#
>  order by df.file#;
> select value from v$parameter where name='log_archive_format';
> select * from (select resetlogs_id from v$archived_log order by completion_time desc) where rownum=1;
> exit
> EOF

CURRENT_SCN
-----------
    2391414


     DF_NO TS_NAME              DF_NAME
---------- -------------------- ----------------------------------------------------------------------
         1 SYSTEM               /home/oracle/app/oracle/oradata/test/system01.dbf
         2 UNDOTBS1             /home/oracle/app/oracle/oradata/test/undotbs01.dbf
         3 SYSAUX               /home/oracle/app/oracle/oradata/test/sysaux01.dbf
         4 USERS                /home/oracle/app/oracle/oradata/test/users01.dbf
         5 TS_TEST              /home/oracle/app/oracle/oradata/test/ts_test01.dbf


VALUE
--------------------------------------------------------------------------------------------------------------------------------------------
%t_%s_%r.arc


RESETLOGS_ID
------------
   676622925

[oracle@DEV-RPT-2 ~]$



5.
删除主库原来的备份, 以免干扰
rman target /
crosscheck archivelog all;
delete noprompt expired archivelog all;
list backupset;
delete noprompt backupset;
list copy;
delete noprompt copy of controlfile;
delete noprompt copy of datafile 1,2,3,4,5;
delete noprompt copy of archivelog like '/mnt/standby-db-oradata/archive%';
exit


6.
创建IMAGE COPY备份

主库上运行
rman target /
run {
 allocate channel c1 type disk rate 5M;
 copy datafile 1 to '/mnt/standby-db-oradata/system01.dbf';
 backup as copy datafile 2 format '/mnt/standby-db-oradata/undotbs01.dbf';
 backup as copy datafile 3 format '/mnt/standby-db-oradata/sysaux01.dbf';
 backup as copy datafile 4 format '/mnt/standby-db-oradata/users01.dbf';
 backup as copy datafile 5 format '/mnt/standby-db-oradata/ts_test01.dbf';
 sql 'alter system archive log current';
 backup as copy archivelog from scn=2391414 format '/mnt/standby-db-oradata/archive/%h_%e_676622925.arc';
 backup as copy current controlfile for standby format '/mnt/standby-db-oradata/control01.ctl';
 backup as copy controlfilecopy '/mnt/standby-db-oradata/control01.ctl' format '/mnt/standby-db-oradata/control02.ctl';
 backup as copy controlfilecopy '/mnt/standby-db-oradata/control01.ctl' format '/mnt/standby-db-oradata/control03.ctl';
 release channel c1;
}
list copy;
exit
用 allocate channel ... rate ...限制读取的速率

10g中 backup as copy 取代了copy 命令, 这里用copy是为了避免备份system表空间时自动备份控制文件
copy允许覆盖同名文件, 不报错
backup as copy不允许同名文件, 会报错ORA-27038: created file already exists

归档日志可以不用backup as copy备份, 用操作系统命令拷贝到备库也行

用backup as copy controlfilecopy .. 复制2份控制文件备份, 也可以操作系统命令拷贝

[oracle@DEV-RPT-2 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Mar 4 06:09:45 2009

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

connected to target database: TEST (DBID=1973292924)

RMAN>

Recovery Manager complete.
[oracle@DEV-RPT-2 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Mar 4 06:10:38 2009

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

connected to target database: TEST (DBID=1973292924)

RMAN> run {
2>  allocate channel c1 type disk rate 5M;
3>  copy datafile 1 to '/mnt/standby-db-oradata/system01.dbf';
 backup as copy datafile 2 format '/mnt/standby-db-oradata/undotbs01.dbf';
4> 5>  backup as copy datafile 3 format '/mnt/standby-db-oradata/sysaux01.dbf';
6>  backup as copy datafile 4 format '/mnt/standby-db-oradata/users01.dbf';
7>  backup as copy datafile 5 format '/mnt/standby-db-oradata/ts_test01.dbf';
 sql 'alter system archive log current';
8> 9>  backup as copy archivelog from scn=2391414 format '/mnt/standby-db-oradata/archive/%h_%e_676622925.arc';
10>  backup as copy current controlfile for standby format '/mnt/standby-db-oradata/control01.ctl';
11>  backup as copy controlfilecopy '/mnt/standby-db-oradata/control01.ctl' format '/mnt/standby-db-oradata/control02.ctl';
12>  backup as copy controlfilecopy '/mnt/standby-db-oradata/control01.ctl' format '/mnt/standby-db-oradata/control03.ctl';
13>  release channel c1;
14> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=1632 devtype=DISK

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input datafile fno=00001 name=/home/oracle/app/oracle/oradata/test/system01.dbf
output filename=/mnt/standby-db-oradata/system01.dbf tag=TAG20090304T061050 recid=36 stamp=680595110
channel c1: datafile copy complete, elapsed time: 00:01:05
channel c1: throttle time: 0:00:49
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input datafile fno=00002 name=/home/oracle/app/oracle/oradata/test/undotbs01.dbf
output filename=/mnt/standby-db-oradata/undotbs01.dbf tag=TAG20090304T061155 recid=37 stamp=680595155
channel c1: datafile copy complete, elapsed time: 00:00:45
channel c1: throttle time: 0:00:32
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input datafile fno=00003 name=/home/oracle/app/oracle/oradata/test/sysaux01.dbf
output filename=/mnt/standby-db-oradata/sysaux01.dbf tag=TAG20090304T061241 recid=38 stamp=680595198
channel c1: datafile copy complete, elapsed time: 00:00:45
channel c1: throttle time: 0:00:30
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input datafile fno=00004 name=/home/oracle/app/oracle/oradata/test/users01.dbf
output filename=/mnt/standby-db-oradata/users01.dbf tag=TAG20090304T061326 recid=39 stamp=680595208
channel c1: datafile copy complete, elapsed time: 00:00:03
channel c1: throttle time: 0:00:01
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input datafile fno=00005 name=/home/oracle/app/oracle/oradata/test/ts_test01.dbf
output filename=/mnt/standby-db-oradata/ts_test01.dbf tag=TAG20090304T061329 recid=40 stamp=680595229
channel c1: datafile copy complete, elapsed time: 00:00:25
channel c1: throttle time: 0:00:16
Finished backup at 04-MAR-09

sql statement: alter system archive log current

Starting backup at 04-MAR-09
current log archived
channel c1: starting archive copy
input archive log thread=1 sequence=2081 recid=3470 stamp=680594623
output filename=/mnt/standby-db-oradata/archive/1_2081_676622925.arc recid=3478 stamp=680595237
channel c1: archivelog copy complete, elapsed time: 00:00:02
channel c1: starting archive copy
input archive log thread=1 sequence=2079 recid=3466 stamp=680594351
output filename=/mnt/standby-db-oradata/archive/1_2079_676622925.arc recid=3479 stamp=680595239
channel c1: archivelog copy complete, elapsed time: 00:00:02
channel c1: starting archive copy
input archive log thread=1 sequence=2083 recid=3476 stamp=680595235
output filename=/mnt/standby-db-oradata/archive/1_2083_676622925.arc recid=3480 stamp=680595241
channel c1: archivelog copy complete, elapsed time: 00:00:02
channel c1: starting archive copy
input archive log thread=1 sequence=2080 recid=3467 stamp=680594351
output filename=/mnt/standby-db-oradata/archive/1_2080_676622925.arc recid=3481 stamp=680595243
channel c1: archivelog copy complete, elapsed time: 00:00:02
channel c1: starting archive copy
input archive log thread=1 sequence=2082 recid=3471 stamp=680594624
output filename=/mnt/standby-db-oradata/archive/1_2082_676622925.arc recid=3482 stamp=680595245
channel c1: archivelog copy complete, elapsed time: 00:00:02
channel c1: starting archive copy
input archive log thread=1 sequence=2084 recid=3477 stamp=680595235
output filename=/mnt/standby-db-oradata/archive/1_2084_676622925.arc recid=3483 stamp=680595247
channel c1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
copying standby control file
output filename=/mnt/standby-db-oradata/control01.ctl tag=TAG20090304T061408 recid=41 stamp=680595250
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input control file copy name=/mnt/standby-db-oradata/control01.ctl
output filename=/mnt/standby-db-oradata/control02.ctl tag=TAG20090304T061408 recid=42 stamp=680595255
channel c1: datafile copy complete, elapsed time: 00:00:07
channel c1: throttle time: 0:00:03
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input control file copy name=/mnt/standby-db-oradata/control01.ctl
output filename=/mnt/standby-db-oradata/control03.ctl tag=TAG20090304T061408 recid=43 stamp=680595263
channel c1: datafile copy complete, elapsed time: 00:00:07
channel c1: throttle time: 0:00:03
Finished backup at 04-MAR-09

released channel: c1

RMAN> list copy;


List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
36      1    A 04-MAR-09       2392559    04-MAR-09       /mnt/standby-db-oradata/system01.dbf
37      2    A 04-MAR-09       2392586    04-MAR-09       /mnt/standby-db-oradata/undotbs01.dbf
38      3    A 04-MAR-09       2392609    04-MAR-09       /mnt/standby-db-oradata/sysaux01.dbf
39      4    A 04-MAR-09       2392629    04-MAR-09       /mnt/standby-db-oradata/users01.dbf
40      5    A 04-MAR-09       2392635    04-MAR-09       /mnt/standby-db-oradata/ts_test01.dbf

List of Control File Copies
Key     S Completion Time Ckp SCN    Ckp Time        Name
------- - --------------- ---------- --------------- ----
43      A 04-MAR-09       2392663    04-MAR-09       /mnt/standby-db-oradata/control03.ctl
42      A 04-MAR-09       2392663    04-MAR-09       /mnt/standby-db-oradata/control02.ctl
41      A 04-MAR-09       2392663    04-MAR-09       /mnt/standby-db-oradata/control01.ctl

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
3262    1    1973    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1973_676622925.arc
3264    1    1974    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1974_676622925.arc
3266    1    1975    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1975_676622925.arc
3268    1    1976    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1976_676622925.arc
3270    1    1977    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1977_676622925.arc
3272    1    1978    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1978_676622925.arc
3274    1    1979    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1979_676622925.arc
3276    1    1980    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1980_676622925.arc
3278    1    1981    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1981_676622925.arc
3280    1    1982    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1982_676622925.arc
3282    1    1983    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1983_676622925.arc
3284    1    1984    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1984_676622925.arc
3286    1    1985    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1985_676622925.arc
3288    1    1986    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1986_676622925.arc
3290    1    1987    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1987_676622925.arc
3292    1    1988    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1988_676622925.arc
3294    1    1989    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1989_676622925.arc
3296    1    1990    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1990_676622925.arc
3298    1    1991    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1991_676622925.arc
3300    1    1992    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1992_676622925.arc
3302    1    1993    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1993_676622925.arc
3304    1    1994    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1994_676622925.arc
3306    1    1995    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1995_676622925.arc
3308    1    1996    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1996_676622925.arc
3310    1    1997    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1997_676622925.arc
3312    1    1998    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1998_676622925.arc
3314    1    1999    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1999_676622925.arc
3316    1    2000    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2000_676622925.arc
3318    1    2001    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2001_676622925.arc
3320    1    2002    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2002_676622925.arc
3322    1    2003    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2003_676622925.arc
3324    1    2004    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2004_676622925.arc
3326    1    2005    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2005_676622925.arc
3328    1    2006    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2006_676622925.arc
3330    1    2007    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2007_676622925.arc
3332    1    2008    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2008_676622925.arc
3334    1    2009    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2009_676622925.arc
3336    1    2010    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2010_676622925.arc
3338    1    2011    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2011_676622925.arc
3340    1    2012    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2012_676622925.arc
3342    1    2013    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2013_676622925.arc
3344    1    2014    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2014_676622925.arc
3346    1    2015    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2015_676622925.arc
3348    1    2016    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2016_676622925.arc
3350    1    2017    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2017_676622925.arc
3352    1    2018    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2018_676622925.arc
3354    1    2019    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2019_676622925.arc
3356    1    2020    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2020_676622925.arc
3358    1    2021    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2021_676622925.arc
3360    1    2022    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2022_676622925.arc
3362    1    2023    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2023_676622925.arc
3364    1    2024    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2024_676622925.arc
3366    1    2025    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2025_676622925.arc
3368    1    2026    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2026_676622925.arc
3370    1    2027    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2027_676622925.arc
3372    1    2028    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2028_676622925.arc
3374    1    2029    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2029_676622925.arc
3376    1    2030    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2030_676622925.arc
3378    1    2031    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2031_676622925.arc
3380    1    2032    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2032_676622925.arc
3381    1    2033    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2033_676622925.arc
3382    1    2034    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2034_676622925.arc
3383    1    2035    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2035_676622925.arc
3384    1    2036    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2036_676622925.arc
3385    1    2037    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2037_676622925.arc
3386    1    2038    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2038_676622925.arc
3387    1    2039    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2039_676622925.arc
3388    1    2040    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2040_676622925.arc
3389    1    2041    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2041_676622925.arc
3391    1    2042    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2042_676622925.arc
3393    1    2043    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2043_676622925.arc
3395    1    2044    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2044_676622925.arc
3397    1    2045    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2045_676622925.arc
3399    1    2046    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2046_676622925.arc
3401    1    2047    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2047_676622925.arc
3403    1    2048    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2048_676622925.arc
3405    1    2049    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2049_676622925.arc
3407    1    2050    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2050_676622925.arc
3409    1    2051    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2051_676622925.arc
3411    1    2052    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2052_676622925.arc
3413    1    2053    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2053_676622925.arc
3415    1    2054    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2054_676622925.arc
3417    1    2055    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2055_676622925.arc
3419    1    2056    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2056_676622925.arc
3421    1    2057    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2057_676622925.arc
3423    1    2058    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2058_676622925.arc
3425    1    2059    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2059_676622925.arc
3427    1    2060    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2060_676622925.arc
3429    1    2061    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2061_676622925.arc
3431    1    2062    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2062_676622925.arc
3433    1    2063    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2063_676622925.arc
3435    1    2064    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2064_676622925.arc
3437    1    2065    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2065_676622925.arc
3439    1    2066    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2066_676622925.arc
3441    1    2067    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2067_676622925.arc
3443    1    2068    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2068_676622925.arc
3445    1    2069    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2069_676622925.arc
3447    1    2070    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2070_676622925.arc
3449    1    2071    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2071_676622925.arc
3451    1    2072    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2072_676622925.arc
3452    1    2073    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2073_676622925.arc
3453    1    2074    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2074_676622925.arc
3457    1    2075    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2075_676622925.arc
3458    1    2076    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2076_676622925.arc
3464    1    2077    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2077_676622925.arc
3465    1    2078    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2078_676622925.arc
3479    1    2079    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2079_676622925.arc
3466    1    2079    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2079_676622925.arc
3481    1    2080    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2080_676622925.arc
3467    1    2080    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2080_676622925.arc
3478    1    2081    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2081_676622925.arc
3470    1    2081    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2081_676622925.arc
3482    1    2082    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2082_676622925.arc
3471    1    2082    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2082_676622925.arc
3480    1    2083    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2083_676622925.arc
3476    1    2083    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2083_676622925.arc
3483    1    2084    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2084_676622925.arc
3477    1    2084    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2084_676622925.arc

RMAN> exit


Recovery Manager complete.
[oracle@DEV-RPT-2 ~]$


7. 启动备库

密码文件用原来的就行
初始化参数audit_trail不能是db了, 得改成os

启动备库到mount standby database状态
rman target /
startup mount
10g以前用
startup nomount
sql 'alter database mount standby database';

[oracle@DEV-pxy-1 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Mar 4 06:20:19 2009

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area    2147483648 bytes

Fixed Size                     2085320 bytes
Variable Size                486542904 bytes
Database Buffers            1644167168 bytes
Redo Buffers                  14688256 bytes

RMAN>

告警日志:
Wed Mar  4 06:20:39 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 500
LICENSE_SESSIONS_WARNING = 150
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =182
LICENSE_MAX_USERS = 0
SYS auditing is enabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 1500
  sessions                 = 1655
  resource_limit           = TRUE
  license_max_sessions     = 500
  license_sessions_warning = 150
  __shared_pool_size       = 452984832
  __large_pool_size        = 16777216
  __java_pool_size         = 16777216
  __streams_pool_size      = 0
  filesystemio_options     = setall
  sga_target               = 2147483648
  control_files            = /home/oracle/app/oracle/oradata/test_s/control01.ctl, /home/oracle/app/oracle/oradata/test_s/control02.ctl, /home/oracle/app/oracle/oradata/test_s/control03.ctl
  db_file_name_convert     = /home/oracle/app/oracle/oradata/test/, /home/oracle/app/oracle/oradata/test_s/
  log_file_name_convert    = /home/oracle/app/oracle/oradata/test/, /home/oracle/app/oracle/oradata/test_s/
  control_file_record_keep_time= 30
  db_block_size            = 8192
  __db_cache_size          = 1644167168
  compatible               = 10.2.0.4.0
  log_archive_dest_1       = LOCATION=/home/oracle/app/oracle/oradata/test_s/archive
  log_archive_dest_state_1 = ENABLE
  standby_archive_dest     = /home/oracle/app/oracle/oradata/test_s/archive
  log_archive_format       = %t_%s_%r.arc
  archive_lag_target       = 1800
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /home/oracle/app/oracle/oradata/test_s/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  audit_sys_operations     = TRUE
  db_domain                =
  instance_name            = test_s
  session_cached_cursors   = 200
  utl_file_dir             = /home/oracle/app/oracle/oradata/test_s/utlfile
  job_queue_processes      = 10
  background_dump_dest     = /home/oracle/app/oracle/admin/test_s/bdump
  user_dump_dest           = /home/oracle/app/oracle/admin/test_s/udump
  core_dump_dest           = /home/oracle/app/oracle/admin/test_s/cdump
  audit_file_dest          = /home/oracle/app/oracle/admin/test_s/adump
  audit_trail              = OS
  db_name                  = test
  db_unique_name           = test_s
  open_cursors             = 3000
  pga_aggregate_target     = 52428800
PMON started with pid=2, OS id=15607
PSP0 started with pid=3, OS id=15609
MMAN started with pid=4, OS id=15611
DBW0 started with pid=5, OS id=15613
LGWR started with pid=6, OS id=15615
CKPT started with pid=7, OS id=15617
SMON started with pid=8, OS id=15619
RECO started with pid=9, OS id=15621
CJQ0 started with pid=10, OS id=15623
MMON started with pid=11, OS id=15625
MMNL started with pid=12, OS id=15627
Wed Mar  4 06:20:41 2009
alter database mount
Wed Mar  4 06:20:45 2009
Setting recovery target incarnation to 2
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=15, OS id=15631
Wed Mar  4 06:20:45 2009
ARC0: Archival started
ARC1: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Wed Mar  4 06:20:45 2009
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC0: Thread not mounted
ARC1 started with pid=16, OS id=15633
ARC1: Becoming the heartbeat ARCH
ARC1: Thread not mounted
Wed Mar  4 06:20:45 2009
Successful mount of redo thread 1, with mount id 1978778425
Wed Mar  4 06:20:45 2009
Physical Standby Database mounted.
Completed: alter database mount


11. 媒体恢复备库

recover database;
RMAN> recover database;

Starting recover at 04-MAR-09
Starting implicit crosscheck backup at 04-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1642 devtype=DISK
Finished implicit crosscheck backup at 04-MAR-09

Starting implicit crosscheck copy at 04-MAR-09
using channel ORA_DISK_1
Crosschecked 5 objects
Finished implicit crosscheck copy at 04-MAR-09

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

starting media recovery

archive log filename=/home/oracle/app/oracle/oradata/test_s/archive/1_2083_676622925.arc thread=1 sequence=2083
archive log filename=/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc thread=1 sequence=2084
archive log filename=/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc thread=1 sequence=2085
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/04/2009 06:21:05
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'
ORA-00310: archived log contains sequence 2084; sequence 2085 required
ORA-00334: archived log: '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'

RMAN>

告警日志:
Wed Mar  4 06:21:05 2009
alter database recover datafile list clear
Wed Mar  4 06:21:05 2009
Completed: alter database recover datafile list clear
Wed Mar  4 06:21:05 2009
alter database recover datafile list
 1 , 2 , 3 , 4 , 5
Completed: alter database recover datafile list
 1 , 2 , 3 , 4 , 5
Wed Mar  4 06:21:05 2009
alter database recover if needed
 standby start
Media Recovery Start
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
ORA-279 signalled during: alter database recover if needed
 standby start
...
Wed Mar  4 06:21:05 2009
alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2083_676622925.arc'
Wed Mar  4 06:21:05 2009
Media Recovery Log /home/oracle/app/oracle/oradata/test_s/archive/1_2083_676622925.arc
ORA-279 signalled during: alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2083_676622925.arc'...
Wed Mar  4 06:21:05 2009
alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'
Wed Mar  4 06:21:05 2009
Media Recovery Log /home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc
ORA-279 signalled during: alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'...
Wed Mar  4 06:21:05 2009
alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'
Wed Mar  4 06:21:05 2009
Media Recovery Log /home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc
Errors with log /home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc
ORA-310 signalled during: alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'...
Wed Mar  4 06:21:05 2009
alter database recover cancel
Wed Mar  4 06:21:07 2009
Media Recovery Canceled
Completed: alter database recover cancel


12. 主库开启远程归档
tnsping test_s
sqlplus -s /nolog <<EOF
conn / as sysdba
alter system set log_archive_dest_2='service=test_s optional reopen=60';
alter system set log_archive_dest_state_2='enable';
exit
EOF


13. 备库开启自动恢复
sqlplus /nolog <<EOF
conn / as sysdba
alter database recover managed standby database disconnect from session;
exit
EOF

告警日志:
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 15720
RFS[1]: Identified database type as 'physical standby'
Wed Mar  4 06:22:07 2009
RFS LogMiner: Client disabled from further notification
Wed Mar  4 06:22:11 2009
alter database recover managed standby database disconnect from session
Wed Mar  4 06:22:11 2009
Attempt to start background Managed Standby Recovery process (test_s)
MRP0 started with pid=17, OS id=15724
Wed Mar  4 06:22:11 2009
MRP0: Background Managed Standby Recovery process started (test_s)
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
Wed Mar  4 06:22:16 2009
Waiting for all non-current ORLs to be archived...
Wed Mar  4 06:22:16 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Mar  4 06:22:16 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 /home/oracle/app/oracle/oradata/test_s/redo01.rdo
Clearing online log 1 of thread 1 sequence number 2083
Wed Mar  4 06:22:16 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Mar  4 06:22:17 2009
Completed: alter database recover managed standby database disconnect from session
Wed Mar  4 06:22:22 2009
Clearing online redo logfile 1 complete
Wed Mar  4 06:22:22 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Mar  4 06:22:22 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 2 /home/oracle/app/oracle/oradata/test_s/redo02.rdo
Clearing online log 2 of thread 1 sequence number 2084
Wed Mar  4 06:22:22 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 2 complete
Wed Mar  4 06:22:27 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Mar  4 06:22:27 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 /home/oracle/app/oracle/oradata/test_s/redo03.rdo
Clearing online log 3 of thread 1 sequence number 2085
Wed Mar  4 06:22:27 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Mar  4 06:22:32 2009
Clearing online redo logfile 3 complete
Media Recovery Waiting for thread 1 sequence 2085


14.测试打开备库

只读打开
alter database recover managed standby database cancel;
alter database open;
10g以前用
alter database open read only;


告警日志
Wed Mar  4 06:28:46 2009
alter database recover managed standby database cancel
Wed Mar  4 06:28:48 2009
MRP0: Background Media Recovery cancelled with status 16037
Wed Mar  4 06:28:48 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Wed Mar  4 06:28:49 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-16037: user requested cancel of managed recovery operation
Wed Mar  4 06:28:49 2009
MRP0: Background Media Recovery process shutdown (test_s)
Wed Mar  4 06:28:49 2009
Managed Standby Recovery Canceled (test_s)
Wed Mar  4 06:28:49 2009
Completed: alter database recover managed standby database cancel
Wed Mar  4 06:29:00 2009
alter database open
Wed Mar  4 06:29:00 2009
SMON: enabling cache recovery
Wed Mar  4 06:29:00 2009
Re-creating tempfile /home/oracle/app/oracle/oradata/test_s/temp01.dbf
Re-creating tempfile /home/oracle/app/oracle/oradata/test_s/ts_temp01.dbf
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 2
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open
Wed Mar  4 06:29:01 2009
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.

复原
alter database recover managed standby database disconnect from session;
告警日志
Wed Mar  4 06:29:26 2009
alter database recover managed standby database disconnect from session
Wed Mar  4 06:29:26 2009
Stopping background process CJQ0
Wed Mar  4 06:29:26 2009
Stopping Job queue slave processes, flags = 27
Wed Mar  4 06:29:26 2009
Job queue slave processes stopped
Wed Mar  4 06:29:26 2009
SMON: disabling cache recovery
Wed Mar  4 06:29:26 2009
Attempt to start background Managed Standby Recovery process (test_s)
MRP0 started with pid=10, OS id=16009
Wed Mar  4 06:29:26 2009
MRP0: Background Managed Standby Recovery process started (test_s)
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
Wed Mar  4 06:29:31 2009
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 2086
Wed Mar  4 06:29:32 2009
Completed: alter database recover managed standby database disconnect from session


15.
第6步备份数据文件,还有一种方式

首先转换表空间到热备份模式(hot backup mode), alter tablespace xxx begin backup, 10g可以在数据库级alter database begin backup
然后用rsync等工具复制到备库服务器上(rsync能限制传输速度,断点续传)
如:
cd /home/oracle/app/oracle/oradata/test/
rsync -azv --progress --bwlimit=1024 -e ssh ts_test01.dbf oracle@dev-pxy-1:/home/oracle/app/oracle/oradata/test_s/
最后转换成正常模式, alter tablespace xxx end backup;

如果没有切换到热备份模式, 直接就拷数据文件, 到时恢复会报错
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
 parallel recovery started with 2 processes

每个表空间都这样备份一遍
转换表空间到热备份模式的问题是, 导致产生很多重做日志, 影响性能, 所以也不推荐在事务繁忙的生产系统上使用





外部链接:
Creating a Standby Database with Image Copies(这个要备份恢复, 太费事了)
What Happens When A Tablespace/Database Is Kept In Begin Backup Mode
What Happens During a Hot Backup
ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS


-fin-
Website Analytics

Followers