oracle归档日志存放与参数关系

概述:有没有想过oracle的归档日志是如何被控制存储在系统当中的顺序的?

1、当数据库处于非归档模式下,并且没有设置任何有关参数时,归档的路径为:

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/product/11.2.0/dbs/arch
Oldest online log sequence     4
Current log sequence           6
SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
SQL> 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_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19                  string
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  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
log_archive_dest_state_1             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_10            string      enable
log_archive_dest_state_11            string      enable
log_archive_dest_state_12            string      enable
log_archive_dest_state_13            string      enable
log_archive_dest_state_14            string      enable
log_archive_dest_state_15            string      enable
log_archive_dest_state_16            string      enable
log_archive_dest_state_17            string      enable
log_archive_dest_state_18            string      enable
log_archive_dest_state_19            string      enable
log_archive_dest_state_2             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable
log_archive_dest_state_3             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_30            string      enable
log_archive_dest_state_31            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
SQL> !echo $ORACLE_HOME
/u01/app/product/11.2.0

开启数据库为归档模式:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  947847168 bytes
Fixed Size                  2258920 bytes
Variable Size             306186264 bytes
Database Buffers          633339904 bytes
Redo Buffers                6062080 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/product/11.2.0/dbs/arch
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> alter system switch logfile;

System altered.

[root@ocp dbs]# ls -l
total 28
-rw-rw----  1 oracle oinstall 1544 Dec  5 09:16 hc_ocp.dat
-rw-r--r--  1 oracle oinstall  860 Dec  5 09:08 initocp.ora
-rw-r--r--. 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r-----  1 oracle oinstall   24 Oct 25 21:44 lkOCP
-rw-r-----  1 oracle oinstall 1536 Oct 25 21:46 orapwocp
-rw-r-----  1 oracle oinstall 2560 Dec  5 09:16 spfileocp.ora
-rw-r-----  1 oracle oinstall 2560 Dec  5 09:01 spfileocp.ora.bak
[root@ocp dbs]# ls -l
total 1120
-rw-r-----  1 oracle oinstall 1117696 Dec  5 09:18 arch1_6_990481459.dbf
-rw-rw----  1 oracle oinstall    1544 Dec  5 09:16 hc_ocp.dat
-rw-r--r--  1 oracle oinstall     860 Dec  5 09:08 initocp.ora
-rw-r--r--. 1 oracle oinstall    2851 May 15  2009 init.ora
-rw-r-----  1 oracle oinstall      24 Oct 25 21:44 lkOCP
-rw-r-----  1 oracle oinstall    1536 Oct 25 21:46 orapwocp
-rw-r-----  1 oracle oinstall    2560 Dec  5 09:16 spfileocp.ora
-rw-r-----  1 oracle oinstall    2560 Dec  5 09:01 spfileocp.ora.bak

 

2、设置log_archive_dest_n(n为1-10)参数为本地归档路径时(这里使用1):

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oraarch';

System altered.

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oraarch
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
SQL> alter system switch logfile;

System altered.

[oracle@ocp oraarch]$ pwd
/u01/app/oraarch
[oracle@ocp oraarch]$ ls -l
total 12
-rw-r----- 1 oracle oinstall 10240 Dec  5 09:24 1_7_990481459.dbf

3、设置闪回恢复区与log_archive_dest_1 并存时(注意设置闪回恢复区后需要开启闪回数据库功能):

SQL> alter system set db_recovery_file_dest='/u01/app/fast_recovery_area';
alter system set db_recovery_file_dest='/u01/app/fast_recovery_area'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

(注意在修改db_recovery_file_dest与db_recovery_file_dest_size参数时有先后顺序需要先设置db_recovery_file_dest_size参数后设置db_recovery_file_dest并且会立即生效)


SQL> alter system set db_recovery_file_dest_size=4G;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/fast_recovery_area';

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oraarch
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
SQL> alter system switch logfile;

System altered.

[oracle@ocp oraarch]$ ll
total 3236
-rw-r----- 1 oracle oinstall   10240 Dec  5 09:24 1_7_990481459.dbf
-rw-r----- 1 oracle oinstall 3298304 Dec  5 09:28 1_8_990481459.dbf
[oracle@ocp oraarch]$ ll /u01/app/fast_recovery_area/OCP/archivelog
total 0

SQL> select FLASHBACK_ON,log_mode from v$database;

FLASHBACK_ON       LOG_MODE
------------------ ------------
NO                 ARCHIVELOG

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oraarch
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9
SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/fast_recovery_area
db_recovery_file_dest_size           big integer 4G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/u01/app/oraarch

4、数据只设置闪回恢复区时没有开启闪回数据库功能:

SQL> alter system set log_archive_dest_1='';

System altered.

SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            ?/dbs/arch
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9

5、开启数据库闪回功能并且只设置了闪回恢复区的参数:

开始(/关闭)闪回数据库功能需要在mount状态下开启(/关闭)或者在open状态下开启(/关闭)闪回数据库功能并重启使其生效:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  947847168 bytes
Fixed Size                  2258920 bytes
Variable Size             306186264 bytes
Database Buffers          633339904 bytes
Redo Buffers                6062080 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select FLASHBACK_ON,log_mode from v$database;

FLASHBACK_ON       LOG_MODE
------------------ ------------
YES                ARCHIVELOG

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9

SQL> alter system switch logfile;

System altered.

[oracle@ocp oraarch]$ ll
total 3236
-rw-r----- 1 oracle oinstall   10240 Dec  5 09:24 1_7_990481459.dbf
-rw-r----- 1 oracle oinstall 3298304 Dec  5 09:28 1_8_990481459.dbf
[oracle@ocp oraarch]$ ll /u01/app/fast_recovery_area/OCP/archivelog/2018_12_05/
total 1196
-rw-r----- 1 oracle oinstall 1222656 Dec  5 09:44 o1_mf_1_9_g0gccfly_.arc

6、开启数据库闪回功能并且设置了闪回恢复区的参数也设置了log_archive_dest_n参数:

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oraarch';

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oraarch
Oldest online log sequence     8
Next log sequence to archive   10
Current log sequence           10
SQL> alter system switch logfile;

System altered.

[oracle@ocp oraarch]$ ll
total 3240
-rw-r----- 1 oracle oinstall    1536 Dec  5 09:46 1_10_990481459.dbf
-rw-r----- 1 oracle oinstall   10240 Dec  5 09:24 1_7_990481459.dbf
-rw-r----- 1 oracle oinstall 3298304 Dec  5 09:28 1_8_990481459.dbf
[oracle@ocp oraarch]$ ll /u01/app/fast_recovery_area/OCP/archivelog/2018_12_05/
total 1196
-rw-r----- 1 oracle oinstall 1222656 Dec  5 09:44 o1_mf_1_9_g0gccfly_.arc

 

总结:与归档路径设置有关的参数基本就是这几个参数:alter system set log_archive_dest_n、db_recovery_file_dest、LOG_ARCHIVE_DEST、LOG_ARCHIVE_DUPLEX_DEST;其中后面两个参数已经基本废弃不再使用也不建议使用;

1、当开启数据归档模式,没有设置归档路径或者没有设置归档路径但是设置了闪回参数而没有开启闪回数据库功能时,则oracle数据库会将归档日志文件保存至$ORACLE_HOME/dbs/目录下;

2、当开启数据归档模式并设置 log_archive_dest_n(1-10)本地归档目录时,不管您是否设置闪回参数并是否开启闪回数据库功能,oracle都会有优先将归档日志文件保存至 log_archive_dest_n(1-10)指定的本地目录;

3、当开启数据归档模式,只有设置了闪回恢复区参数时并开启了闪回数据库功能时oracle数据库会将归档文件日志保存在闪回恢复区;注意开启闪回数据库功能需要重启数据库使其生效。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值