通过 alter system dump logfile语句dump REDO及归档日志信息示例
来源:互联网 发布:hadoop2.6 windows 编辑:程序博客网 时间:2024/06/06 20:16
说明:alter system dump logfile 'filename';
这个语句在NOMOUNT/MOUNT/OPEN状态下,均可以DUMP REDO日志或归档日志,从而可以从文件头信息中找到DBID,在数据恢复时很有用。因为我们可以仅使用任意一个参数文件,就可以将一个实例打开到NOMOUNT,然后将待恢复却不知道DBID的数据库的REDO或归档文件进行DUMP(也可传输到远程库),找出DBID。
实验语句如下:
SYS@ bys3>select status from v$instance; --NOMOUNT状态下即可,MOUNT或OPEN时当然也可以了。STATUS
------------
STARTED
SYS@ bys3> alter system dump logfile '/u01/oradata/bys3/redo01.log';
System altered.
Elapsed: 00:01:46.62 --这个语句执行时间较长,生成的TRC文件也很大。
SYS@ bys3>oradebug setmypid; --此语句要用SYSDBA权限执行,普通DBA用户会报错:ORA-01031: insufficient privileges
Statement processed.
SYS@ bys3>oradebug tracefile_name --找出产生的TRACE文件名
/u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_5044.trc
DUMP归档日志的语句就不贴了,和DUMP REOD的一样,只是文件路径文件名改一下就行。在最下面贴出有一部分DUMP归档日志的信息。
################################################################
2.查看DUMP REDO文件产生的TRACE文件:--文件太大,截取开头的一部分
[oracle@bys3 trace]$ head -n 200 bys3_ora_5044.trc --查看TRACE文件开头的两百行。Trace file /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_5044.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux --所在主机的OS、数据库相关信息。
Node name: bys3.bys.com
Release: 2.6.32-200.13.1.el5uek
Version: #1 SMP Wed Jul 27 20:21:26 EDT 2011
Machine: i686
Instance name: bys3
Redo thread mounted by this instance: 0 <none>
Oracle process number: 17
Unix process pid: 5044, image: oracle@bys3.bys.com (TNS V1-V3)
*** 2013-11-17 15:18:38.083 ---产生此TRACE的会话相关信息
*** SESSION ID:(1.3) 2013-11-17 15:18:38.083
*** CLIENT ID:() 2013-11-17 15:18:38.083
*** SERVICE NAME:() 2013-11-17 15:18:38.083
*** MODULE NAME:(sqlplus@bys3.bys.com (TNS V1-V3)) 2013-11-17 15:18:38.083
*** ACTION NAME:() 2013-11-17 15:18:38.083
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
DUMP OF REDO FROM FILE '/u01/oradata/bys3/redo01.log' --DUMP的文件信息-这里DUMP的是REDO日志
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=3358363031=0xc82c8d97, Db Name='BYS3' ---数据库的DBID和DB_NAME信息
Activation ID=3358374039=0xc82cb897
Control Seq=1596=0x63c, File size=102400=0x19000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000049, SCN 0x0000000b3984-0xffffffffffff"
thread: 1 nab: 0x10e3a seq: 0x00000031 hws: 0x5 eot: 1 dis: 0
resetlogs count: 0x318f5cd7 scn: 0x0000.00000001 (1)
prev resetlogs count: 0x0 scn: 0x0000.00000000
Low scn: 0x0000.000b3984 (735620) 11/17/2013 10:00:19
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.00000001 (1) 11/14/2013 14:23:38
Thread closed scn: 0x0000.000b8756 (755542) 11/17/2013 15:10:53
Disk cksum: 0x39cb Calc cksum: 0x39cb
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 1530 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 8
Format ID is 2
redo log key is 8956285942a954206654d49467f23e84
redo log key flag is 5
Enabled redo threads: 1
REDO RECORD - Thread:1 RBA: 0x000031.00000002.0010 LEN: 0x0500 VLD: 0x06
SCN: 0x0000.000b3984 SUBSCN: 1 11/17/2013 10:00:19
(LWN RBA: 0x000031.00000002.0010 LEN: 0005 NST: 0001 SCN: 0x0000.000b3984)
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 3 rdba: 0x00c00a90 BFT:(1024,12585616) non-BFT:(3,2704)
scn: 0x0000.000b38bd seq: 0x02 flg:0x04
################################################################
3.DUMP 归档日志的TRACE文件
[oracle@bys3 trace]$ head -n 150 bys3_ora_11074.trcTrace file /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_11074.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: bys3.bys.com
Release: 2.6.32-200.13.1.el5uek
Version: #1 SMP Wed Jul 27 20:21:26 EDT 2011
Machine: i686
Instance name: bys3
Redo thread mounted by this instance: 0 <none>
Oracle process number: 17
Unix process pid: 11074, image: oracle@bys3.bys.com (TNS V1-V3)
*** 2013-11-17 15:43:43.376
*** SESSION ID:(1.5) 2013-11-17 15:43:43.376
*** CLIENT ID:() 2013-11-17 15:43:43.376
*** SERVICE NAME:() 2013-11-17 15:43:43.376
*** MODULE NAME:(sqlplus@bys3.bys.com (TNS V1-V3)) 2013-11-17 15:43:43.376
*** ACTION NAME:() 2013-11-17 15:43:43.376
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
DUMP OF REDO FROM FILE '/home/oracle/arc_1_12_822301084.arc'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=3957527513=0xebe313d9, Db Name='BYS1'
Activation ID=3957570009=0xebe3b9d9
Control Seq=936=0x3a8, File size=102400=0x19000
File Number=3, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000012, SCN 0x0000000e38ff-0x0000000e3a35"
thread: 1 nab: 0x82 seq: 0x0000000c hws: 0x3 eot: 0 dis: 0
resetlogs count: 0x3103519c scn: 0x0000.000b8338 (754488)
prev resetlogs count: 0x296a3120 scn: 0x0000.00000001 (1)
Low scn: 0x0000.000e38ff (932095) 08/08/2013 16:33:05
Next scn: 0x0000.000e3a35 (932405) 08/08/2013 16:34:02
Enabled scn: 0x0000.000b8338 (754488) 08/01/2013 08:58:04
Thread closed scn: 0x0000.000e38ff (932095) 08/08/2013 16:33:05
Disk cksum: 0xfb18 Calc cksum: 0xfb18
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 15 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800011
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 8
Format ID is 0
redo log key is 986daba9692cd9af21da3cb2493c8a95
redo log key flag is 5
Enabled redo threads: 1
- 通过 alter system dump logfile语句dump REDO及归档日志信息示例
- alter session set events语句dump REDO日志文件头信息示例
- 用alter system dump命令dump redo、 undo、 data block示例
- oracle redo internal 之 dump logfile (dump redo logfile)
- 学习 alter system dump
- alter session set events语句dump 控制文件头信息示例
- Dump Redo Log File
- alter system switch logfile作用
- ALTER SYSTEM SWITCH LOGFILE 意义与作用及与ALTER SYSTEM ARCHIVE LOG CURRENT区别
- 通过class-dump查看应用程序类信息
- 【ORACLE】DUMP转储:redo log 、undo段及table段
- alter system switch logfile/ alter system archive log current区别
- alter system switch logfile与alter system archive log current
- alter system switch logfile与alter system archive log current
- alter system switch logfile 和alter system archive log current
- Alter system switch logfile hang解决方法
- dump
- dump
- C#控件的闪烁问题解决方法总结
- mipsel-linux-uclibc交叉编译libogg
- java学习笔记(一)
- Cocos2d-x 让飞机随着触摸移动起来
- 10个不为人知的Linux命令
- 通过 alter system dump logfile语句dump REDO及归档日志信息示例
- sql2008安装完成之后,无法启动
- java学习之路--static,this,代码块,内存的划分等--java实战经典第五章
- 11 个很少人知道但很有用的 Linux 命令
- 严版数据结构 第29页 单链表 C++实现
- 如何快速取得一个二进制状态的所有子状态
- 第十二周 小学生计算能力测试
- cocos2d-x2.2以上版本 安装步骤
- 动态规划,Dijkstra算法,A*算法的比较