监视与管理存储结构
联机与归档重做日志文件
联机重做日志
select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
1 1 2 104857600 1 NO CURRENT 1251781 2008-06-25 11:54:08
2 1 1 104857600 1 YES INACTIVE 1156941 2008-06-19 12:37:03
3 1 0 104857600 1 YES UNUSED 0
列出重做日志的组号、日志序号、日志大小、成员数、是否归档、状态和日志所包含的起始 SCN 等。
具体日志文件看 v$logfile。
归档日志
select * from v$archive_processes where rownum<=6;
PROCESS STATUS LOG_SEQUENCE STAT
0 ACTIVE 0 IDLE
1 ACTIVE 0 IDLE
2 STOPPED 0 IDLE
3 STOPPED 0 IDLE
4 STOPPED 0 IDLE
5 STOPPED 0 IDLE
列出归档进程的状态。
show parameter log_archive_dest
NAME TYPE VALUE
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
NAME TYPE VALUE
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 1
下一个存档日志序列 3
当前日志序列 3
可以设置归档的复用(默认使用 LOG_ARCHIVE_DEST_10,目的地设为闪回区)
show parameter log_archive_max
NAME TYPE VALUE
log_archive_max_processes integer 2
设置归档进程的数量
col name format a95
select recid,name,dest_id,thread#,sequence#
from v$archived_log;
RECID NAME DEST_ID THREAD# SEQUENCE#
第 28 页
1 10 1 1
2 10 1 2
3 10 1 3
4 10 1 4
5 10 1 5
6 10 1 6
7 10 1 7
8 10 1 8
9 10 1 9
10 10 1 10
11 C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\WWW\ARCHIVELOG\2008_06_16\O1_MF_1_11_45CYN71G_.ARC 10 1 11
RECID NAME DEST_ID THREAD# SEQUENCE#
12 C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\WWW\ARCHIVELOG\2008_06_19\O1_MF_1_11_45MRMJC7_.ARC 10 1 11
13 C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\WWW\ARCHIVELOG\2008_06_19\O1_MF_1_12_45MRN6OH_.ARC 10 1 12
14 10 1 10
15 C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\WWW\ARCHIVELOG\2008_06_25\O1_MF_1_1_463JC0TV_.ARC 10 1 1
16 C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\WWW\ARCHIVELOG\2008_06_30\O1_MF_1_2_46K0ZYDP_.ARC 10 1 2
列出了归档日志号、归档日志文件名
1 、归档目标号 2 、重做线程号、重做日志序列号
select recid,resetlogs_change#,resetlogs_time,resetlogs_id,first_change#,first_time,next_change#,next_time
from v$archived_log;
RECID RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
1 534907 2008-05-14 13:57:07 654703027 534907 2008-05-14 13:57:07 557329 2008-05-21 09:31:15
2 534907 2008-05-14 13:57:07 654703027 557329 2008-05-21 09:31:15 658052 2008-05-24 09:19:36
3 534907 2008-05-14 13:57:07 654703027 658052 2008-05-24 09:19:36 722458 2008-05-27 13:50:18
4 534907 2008-05-14 13:57:07 654703027 722458 2008-05-27 13:50:18 758484 2008-05-28 13:28:54
5 534907 2008-05-14 13:57:07 654703027 758484 2008-05-28 13:28:54 805831 2008-05-30 13:03:21
6 534907 2008-05-14 13:57:07 654703027 805831 2008-05-30 13:03:21 824017 2008-05-30 15:10:32
7 534907 2008-05-14 13:57:07 654703027 824017 2008-05-30 15:10:32 851110 2008-06-04 09:39:00
8 534907 2008-05-14 13:57:07 654703027 851110 2008-06-04 09:39:00 946871 2008-06-08 10:03:45
9 534907 2008-05-14 13:57:07 654703027 946871 2008-06-08 10:03:45 989868 2008-06-10 09:31:33
10 534907 2008-05-14 13:57:07 654703027 989868 2008-06-10 09:31:33 1057071 2008-06-11 22:00:41
11 534907 2008-05-14 13:57:07 654703027 1057071 2008-06-11 22:00:41 1126967 2008-06-16 13:30:46
RECID RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
12 534907 2008-05-14 13:57:07 654703027 1057071 2008-06-11 22:00:41 1126967 2008-06-16 13:30:46
13 534907 2008-05-14 13:57:07 654703027 1126967 2008-06-16 13:30:46 1157385 2008-06-19 12:37:26
14 534907 2008-05-14 13:57:07 654703027 989868 2008-06-10 09:31:33 1057071 2008-06-11 22:00:41
15 1156941 2008-06-19 12:37:03 657808623 1156941 2008-06-19 12:37:03 1251781 2008-06-25 11:54:08
16 1156941 2008-06-19 12:37:03 657808623 1251781 2008-06-25 11:54:08 1323115 2008-06-30 14:58:05
RESETLOGS_CHANGE# 重置日志时的系统改变号
RESETLOGS_TIME 重置日志的时间
RESETLOGS_ID 重置日志的标识(与归档日志有关)
FIRST_CHANGE# 此归档日志内记录的首个系统改变号
FIRST_TIME 此改变号对应的时间
NEXT_CHANGE# 下一个归档日志内记录的首个系统改变号
NEXT_TIME 此改变号对应的时间
select scn_to_timestamp(1251781) from dual;
select scn_to_timestamp(1323115) from dual;
SCN_TO_TIMESTAMP(1251781)
25-6 月 -08 11.54.07.000000000 上午
SCN_TO_TIMESTAMP(1323115)
30-6 月 -08 02.58.05.000000000 下午
SCN 与时间的转换
select recid,blocks,block_size,creator,registrar,standby_dest,archived,applied,deleted,status,
completion_time,dictionary_begin,dictionary_end,end_of_redo
from v$archived_log;
RECID BLOCKS BLOCK_SIZE CREATOR REGISTR STA ARC APP DEL S COMPLETION_TIME DIC DIC END
1 196372 512 ARCH ARCH NO YES NO YES D 2008-05-21 09:31:29 NO NO NO
2 177124 512 ARCH ARCH NO YES NO YES D 2008-05-24 09:20:07 NO NO NO
3 197542 512 ARCH ARCH NO YES NO YES D 2008-05-27 13:50:35 NO NO NO
4 30945 512 ARCH ARCH NO YES NO YES D 2008-05-28 13:29:02 NO NO NO
5 58560 512 ARCH ARCH NO YES NO YES D 2008-05-30 13:03:33 NO NO NO
6 190000 512 ARCH ARCH NO YES NO YES D 2008-05-30 15:10:47 NO NO NO
7 198030 512 ARCH ARCH NO YES NO YES D 2008-06-04 09:39:17 NO NO NO
8 197672 512 ARCH ARCH NO YES NO YES D 2008-06-08 10:03:58 NO NO NO
9 38107 512 ARCH ARCH NO YES NO YES D 2008-06-10 09:31:41 NO NO NO
10 192602 512 ARCH ARCH NO YES NO YES D 2008-06-11 22:01:03 NO NO NO
11 198586 512 ARCH ARCH NO YES NO NO A 2008-06-16 13:30:56 NO NO NO
RECID BLOCKS BLOCK_SIZE CREATOR REGISTR STA ARC APP DEL S COMPLETION_TIME DIC DIC END
12 198586 512 FGRD FGRD NO YES YES NO A 2008-06-19 12:37:26 NO NO NO
1 如果值为 NULL,不是此日志被清除,就是 RMAN 备份时使用了 delete input
2 值为 0 表示不是一个有效的归档目标号
第 29 页
13 125272 512 FGRD FGRD NO YES NO NO A 2008-06-19 12:37:33 NO NO NO
14 192602 512 FGRD FGRD NO YES YES YES D 2008-06-19 12:37:43 NO NO NO
15 197932 512 ARCH ARCH NO YES NO NO A 2008-06-25 11:54:29 NO NO NO
16 188542 512 FGRD FGRD NO YES NO NO A 2008-06-30 14:58:22 NO NO NO
BLOCKS 所包含块数
BLOCK_SIZE 块大小,由操作系统决定而不是用户指定(单位:字节)
CREATOR(归档的创建者) ARCH 归档进程
FGRD 后台进程
RMAN 恢复管理
SRMN 后备式恢复管理
LGWR 日志进程
REGISTRAR(归档的登记) RFS 远程文件服务进程
ARCH 归档进程
FGRD 后台进程
RMAN 恢复管理器
SRMN 后备式恢复管理器
LGWR 日志进程
STANDBY_DEST
ARCHIVED 值为 YES,则说明重做日志被归档;值为 NO,则说明重做日志是在恢复时临时产生(有点猜)
APPLIED 归档是(YES)否(NO)应用到后备机
DELETED 是(YES)否(NO)通过 RMAN 物理删除了此归档文件
STATUS(归档文件状态) A 有效的
D 已删除
U 无效的
X 已到期
COMPLETION_TIME 归档完成时间
DICTIONARY_BEGIN
DICTIONARY_END
END_OF_REDO 是(YES)否(NO)包含所有重做的结尾信息
select recid,backup_count,archival_thread#,activation#,is_recovery_dest_file,compressed,fal,end_of_redo_type
from v$archived_log;
RECID BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_ COM FAL END_OF_RED
1 0 1 765947759 YES NO NO
2 0 1 765947759 YES NO NO
3 0 1 765947759 YES NO NO
4 0 1 765947759 YES NO NO
5 0 1 765947759 YES NO NO
6 0 1 765947759 YES NO NO
7 0 1 765947759 YES NO NO
8 0 1 765947759 YES NO NO
9 0 1 765947759 YES NO NO
10 0 1 765947759 YES NO NO
11 1 1 765947759 YES NO NO
RECID BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_ COM FAL END_OF_RED
12 1 1 765947759 YES NO NO
13 1 1 765947759 YES NO NO RESETLOGS
14 0 1 765947759 YES NO NO
15 1 1 769049733 YES NO NO
16 1 1 769049733 YES NO NO
列出了备份次数、重做线程号、实例号、是否放入闪回区等信息
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞3
添加新评论0 条评论