概述:有没有想过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数据库会将归档文件日志保存在闪回恢复区;注意开启闪回数据库功能需要重启数据库使其生效。