oracle查询当前归档scn_【学习笔记】Oracle数据库 查看归档日志存放的位置

【学习笔记】Oracle数据库 查看归档日志存放的位置

时间:2016-10-21 19:19   来源:Oracle研究中心   作者:HTZ   点击:

次

天萃荷净

分享一篇关于查看Oracle数据库归档日志记录存放位置的文章，通过查看数据库进程、视图等信息分析内部运行原理

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 Oracle???????oracleplus.net

***************************************************************************

(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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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

本文固定链接: http://www.htz.pw/2014/04/27/%e6%89%be%e5%88%b0%e5%bd%92%e6%a1%a3%e6%97%a5%e5%bf%97%e8%ae%b0%e5%bd%95%e5%ad%98%e6%94%be%e4%bd%8d%e7%bd%ae.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库 查看归档日志存放的位置