原文链接: Oracle 11g 归档日志

Oracle 11g 查看归档日志

操作步骤
1
2
3
4
5
$ bin/sqlplus / as sysdba
SQL> startup mount
SQL> show parameter db_recovery_file_dest;
SQL> archive log list;
SQL> select * from v$flash_recovery_area_usage;

SQL> set linesize 800;

具体操作及输出
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
$ bin/sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 28 11:07:08 2017
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size            1191184496 bytes
Database Buffers          402653184 bytes
Redo Buffers                7360512 bytes
Database mounted.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
          0
SQL> set linesize 800;
SQL> show parameter db_recovery_file_dest;
NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/kylin/app/kylin/flash_re
                                                 covery_area
db_recovery_file_dest_size           big integer 3882M
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     145
Next log sequence to archive   145
Current log sequence           147
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                      99.96                         0             100
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0
7 rows selected.

相关知识

  • LOG_ARCHIVE_DEST: 指定归档文件存放的路径, 该路径只能是本地磁盘, 默认为’’
  • LOG_ARCHIVE_DEST_n: 默认值为’’. Oracle最多支持把日志文件归档到10个地方, n从1到10
    归档地址可以为本地磁盘, 或者网络设备
  • DB_RECOVERY_FILE_DEST: 指定闪回恢复区路径

三者关系:
如果设置了DB_RECOVERY_FILE_DEST
就不能设置LOG_ARCHIVE_DEST, 默认的归档日志存放于DB_RECOVERY_FILE_DEST指定的闪回恢复区中
可以设置LOG_ARCHIVE_DEST_n, 如果这样, 那么归档日志存放于LOG_ARCHIVE_DEST_n设置的目录中
如果想要归档日志继续存放在DB_RECOVERY_FILE_DEST中, 可以通过如下命令:
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST';

如果设置了LOG_ARCHIVE_DEST, 就不能设置LOG_ARCHIVE_DEST_n和DB_RECOVERY_FILE_DEST
如果设置了LOG_ARCHIVE_DEST_n, 就不能设置LOG_ARCHIVE_DEST
也就是说, LOG_ARCHIVE_DEST参数和DB_RECOVERY_FILE_DEST,LOG_ARCHIVE_DEST_n都不共存
而DB_RECOVERY_FILE_DEST和LOG_ARCHIVE_DEST_n可以共存

LOG_ARCHIVE_DEST只能与LOG_ARCHIVE_DUPLEX_DEST共存. 这样可以设置两个归档路径
LOG_ARCHIVE_DEST设置一个主归档路径, LOG_ARCHIVE_DUPLEX_DEST设置一个从归档路径
所有归档路径必须是本地的

如果LOG_ARCHIVE_DEST_n设置的路径不正确, 那么Oracle会在设置的上一级目录归档
比如设置LOG_ARCHIVE_DEST_1=’location=C:\archive1’, 而OS中并没有archive1这个目录, 那么Oracle会在C盘归档