下面的测试来至于一次聊天
1，数据库版本
|
SQL> select * from v$version; BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production PL/SQL Release 11.2.0.3.0 – Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 – Production NLSRTL Version 11.2.0.3.0 – Production |
2，查询进程的操作进程号
|
SQL> select sid from v$mystat where rownum<2; SID ———- 89 SQL> select spid from v$process where addr in (select paddr from v$session where sid=89); SPID ———————— 5335 |
3，strace进程
|
[oracle11g@rhel5 arch]$ strace -fo /tmp/trace.out -p 5335 Process 5335 attached – interrupt to quit SQL> select name from v$archived_log; NAME ——————————————————————————– /oracle11g/arch/1_2_844511118.dbf /oracle11g/arch/1_6_844511118.dbf /oracle11g/arch/1_379_832114428.dbf /oracle11g/arch/1_4_844511118.dbf /oracle11g/arch/1_1_844511118.dbf /oracle11g/arch/1_381_832114428.dbf /oracle11g/arch/1_380_832114428.dbf /oracle11g/arch/1_3_844511118.dbf /oracle11g/arch/1_5_844511118.dbf 9 rows selected. [root@rhel5 ~]# cat /tmp/trace.out|grep -E ‘open|pread’ 5335 open(“/oracle11g/app/oracle/oradata/orcl11g/control01.ctl”, O_RDWR|O_SYNC) = 10 5335 open(“/oracle11g/app/oracle/fast_recovery_area/orcl11g/control02.ctl”, O_RDWR|O_SYNC) = 10 5335 pread(256, “\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4\244\366\0\0\0\0\0\0\0\0 \v\272\373\3677″…, 16384, 16384) = 16384 5335 pread(256, “\25\302\0\0\17\0\0\0\\\3764\0\377\377\1\4\367x\0\0\0G\0\0\0\0\0\0\0\0\0\1″…, 16384, 245760) = 16384 5335 pread(256, “\25\302\0\0\21\0\0\0\\\3764\0\377\377\1\4Q\t\0\0\0\0\0\0\0\0\0\0\3037V2″…, 16384, 278528) = 16384 5335 pread(256, “\25\302\0\0\306\0\0\0Z\3764\0\377\377\1\4\205I\0\0\3577V2B\0\1\0\2\0\0\0″…, 16384, 3244032) = 16384 查询文件描述符 [root@rhel5 fd]# ls -l total 0 lr-x—— 1 oracle11g dba 64 Apr 10 10:30 0 -> /dev/null l-wx—— 1 oracle11g dba 64 Apr 10 10:30 1 -> /dev/null l-wx—— 1 oracle11g dba 64 Apr 10 10:30 12 -> pipe:[15584] l-wx—— 1 oracle11g dba 64 Apr 10 10:30 2 -> /dev/null lrwx—— 1 oracle11g dba 64 Apr 10 10:30 256 -> /oracle11g/app/oracle/oradata/orcl11g/control01.ctl lrwx—— 1 oracle11g dba 64 Apr 10 10:30 257 -> /oracle11g/app/oracle/fast_recovery_area/orcl11g/control02.ctl lr-x—— 1 oracle11g dba 64 Apr 10 10:30 3 -> /dev/null lr-x—— 1 oracle11g dba 64 Apr 10 10:30 4 -> /dev/null lr-x—— 1 oracle11g dba 64 Apr 10 10:30 5 -> /oracle11g/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb lr-x—— 1 oracle11g dba 64 Apr 10 10:30 6 -> /proc/5335/fd lr-x—— 1 oracle11g dba 64 Apr 10 10:30 7 -> /dev/zero lrwx—— 1 oracle11g dba 64 Apr 10 10:30 8 -> /oracle11g/app/oracle/admin/orcl11g/adump/orcl11g_ora_5335_1.aud lr-x—— 1 oracle11g dba 64 Apr 10 10:30 9 -> pipe:[15583] |
4，dump控制文件
|
SQL> oradebug setmypid Statement processed. SQL> oradebug dump controlf 8 Statement processed. SQL> oradebug tracefile_name; /oracle11g/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_8177.trc 通过trace文件，我们可以发现下面的记录 *************************************************************************** ARCHIVED LOG RECORDS *************************************************************************** (size = 584, compat size = 584, section max = 308, section in-use = 12, last-recid= 12, old-recno = 1, last-recno = 12) (extent = 1, blkno = 98, numrecs = 308) Earliest record: RECID #1 Recno 1 Record timestamp 04/10/14 10:26:55 Thread=1 Seq#=2 Flags: <produced by archive operation> <created by the RMAN process> Resetlogs scn and time scn: 0x0000.0053290d 04/10/14 10:25:18 filename /oracle11g/arch/1_2_844511118.dbf Low scn: 0x0000.00532ad4 04/10/14 10:25:41 Next scn: 0x0000.00532ad7 04/10/14 10:25:42 Block count=1 Blocksize=512 Latest record: RECID #12 Recno 12 Record timestamp 04/10/14 10:51:52 Thread=1 Seq#=6 Flags: <produced by archive operation> <created by the foreground process> Resetlogs scn and time scn: 0x0000.0053290d 04/10/14 10:25:18 filename /oracle11g/arch/1_6_844511118.dbf Low scn: 0x0000.00532ae1 04/10/14 10:25:46 Next scn: 0x0000.00532ae4 04/10/14 10:25:46 Block count=1 Blocksize=512 RECID #11 Recno 11 Record timestamp 04/10/14 10:51:52 Thread=1 Seq#=5 Flags: <produced by archive operation> <created by the foreground process> Resetlogs scn and time scn: 0x0000.0053290d 04/10/14 10:25:18 filename /oracle11g/arch/1_5_844511118.dbf Low scn: 0x0000.00532ade 04/10/14 10:25:46 Next scn: 0x0000.00532ae1 04/10/14 10:25:46 Block count=1 Blocksize=512 RECID #10 Recno 10 Record timestamp 04/10/14 10:51:52 Thread=1 Seq#=7 Flags: <produced by archive operation> <created by the foreground process> Resetlogs scn and time scn: 0x0000.0053290d 04/10/14 10:25:18 filename /oracle11g/arch/1_7_844511118.dbf Low scn: 0x0000.00532ae4 04/10/14 10:25:46 Next scn: 0x0000.00532ae5 04/10/14 10:51:52 Block count=1 Blocksize=512 RECID #9 Recno 9 Record timestamp 04/10/14 10:26:56 Thread=1 Seq#=5 Flags: <produced by archive operation> <created by the RMAN process> Resetlogs scn and time scn: 0x0000.0053290d 04/10/14 10:25:18 filename /oracle11g/arch/1_5_844511118.dbf Low scn: 0x0000.00532ade 04/10/14 10:25:46 Next scn: 0x0000.00532ae1 04/10/14 10:25:46 Block count=1 Blocksize=512 RECID #8 Recno 8 Record timestamp 04/10/14 10:26:56 Thread=1 Seq#=3 Flags: <produced by archive operation> <created by the RMAN process> Resetlogs scn and time scn: 0x0000.0053290d 04/10/14 10:25:18 filename /oracle11g/arch/1_3_844511118.dbf Low scn: 0x0000.00532ad7 04/10/14 10:25:42 Next scn: 0x0000.00532adb 04/10/14 10:25:45 Block count=3 Blocksize=512 RECID #7 Recno 7 Record timestamp 04/10/14 10:26:56 Thread=1 Seq#=380 Flags: <produced by archive operation> <created by the RMAN process> Resetlogs scn and time scn: 0x0000.000f30dc 11/21/13 22:53:48 filename /oracle11g/arch/1_380_832114428.dbf Low scn: 0x0000.005327e4 04/10/14 10:17:41 Next scn: 0x0000.005327ec 04/10/14 10:17:41 Block count=1 Blocksize=512 RECID #6 Recno 6 Record timestamp 04/10/14 10:26:56 Thread=1 Seq#=381 Flags: <produced by archive operation> <created by the RMAN process> Resetlogs scn and time scn: 0x0000.000f30dc 11/21/13 22:53:48 filename /oracle11g/arch/1_381_832114428.dbf Low scn: 0x0000.005327ec 04/10/14 10:17:41 Next scn: 0x0000.0053290d 04/10/14 10:25:18 Block count=481 Blocksize=512 RECID #5 Recno 5 Record timestamp 04/10/14 10:26:56 Thread=1 Seq#=1 Flags: <produced by archive operation> <created by the RMAN process> Resetlogs scn and time scn: 0x0000.0053290d 04/10/14 10:25:18 filename /oracle11g/arch/1_1_844511118.dbf Low scn: 0x0000.0053290d 04/10/14 10:25:18 Next scn: 0x0000.00532ad4 04/10/14 10:25:41 Block count=124 Blocksize=512 RECID #4 Recno 4 Record timestamp 04/10/14 10:26:56 Thread=1 Seq#=4 Flags: <produced by archive operation> <created by the RMAN process> Resetlogs scn and time scn: 0x0000.0053290d 04/10/14 10:25:18 filename /oracle11g/arch/1_4_844511118.dbf Low scn: 0x0000.00532adb 04/10/14 10:25:45 Next scn: 0x0000.00532ade 04/10/14 10:25:46 Block count=1 Blocksize=512 RECID #3 Recno 3 Record timestamp 04/10/14 10:26:56 Thread=1 Seq#=379 Flags: <produced by archive operation> <created by the RMAN process> Resetlogs scn and time scn: 0x0000.000f30dc 11/21/13 22:53:48 filename /oracle11g/arch/1_379_832114428.dbf Low scn: 0x0000.00532765 04/10/14 10:15:47 Next scn: 0x0000.005327e4 04/10/14 10:17:41 Block count=2 Blocksize=512 RECID #2 Recno 2 Record timestamp 04/10/14 10:26:55 Thread=1 Seq#=6 Flags: <produced by archive operation> <created by the RMAN process> Resetlogs scn and time scn: 0x0000.0053290d 04/10/14 10:25:18 filename /oracle11g/arch/1_6_844511118.dbf Low scn: 0x0000.00532ae1 04/10/14 10:25:46 Next scn: 0x0000.00532ae4 04/10/14 10:25:46 Block count=1 Blocksize=512 RECID #1 Recno 1 Record timestamp 04/10/14 10:26:55 Thread=1 Seq#=2 Flags: <produced by archive operation> <created by the RMAN process> Resetlogs scn and time scn: 0x0000.0053290d 04/10/14 10:25:18 filename /oracle11g/arch/1_2_844511118.dbf Low scn: 0x0000.00532ad4 04/10/14 10:25:41 Next scn: 0x0000.00532ad7 04/10/14 10:25:42 Block count=1 Blocksize=512 |
找到归档日志记录存放位置：等您坐沙发呢！