Oracle归档日志管理的方法有哪些
这篇文章主要讲解了"Oracle归档日志管理的方法有哪些",文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习"Oracle归档日志管理的方法有哪些"吧!
一般情况下,数据库安装时选择的是默认NOARCHIVELOG模式。如果要开启ARCHIVELOG模式,则需要为归档日志路径设置初始化参数。
归档日志路径可以配置一个也可以配置多个。路径可以是本地路径,如本地文件系统,ASM磁盘组;也可以是远程路径,如备份数据库。设置多个归档日志路径,可以对归档日志做冗余,即使有一个目标盘损坏,也可以保证归档日志是可用的。
在配置参数时,归档日志的路径可以动态修改,在下一次日志切换的时候生效。
这里给出3种常用的归档路径的设置方案:单一归档路径方案、两路归档路径方案和多路归档路径的方案。
方案1:单一归档路径
如果只设置单一的归档目录,默认使用db_recovery_file_dest参数,使用Fast Recovery Area存放归档数据;也可手工配置log_archive_dest初始化参数,可以配置为本地文件系统,ASM磁盘组。使用log_archive_dest_n参数的方法在第三种方案进行讨论。
具体操作如下:
--如果使用fast recovery area存放归档日志,需要注意该区域有大小限制,建议将大小调大SYS@cams> show parameter db_recovery_file_dest NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest string /u01/app/oracle/fast_recovery_ areadb_recovery_file_dest_size big integer 4182MSYS@cams> alter system set db_recovery_file_dest_size=10G; System altered. SYS@cams> show parameter db_recovery_file_dest NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest string /u01/app/oracle/fast_recovery_ areadb_recovery_file_dest_size big integer 10G
--如果使用本地路径存放归档日志,需要将db_recovery_file_dest参数置空,然后设置log_archive_dest参数。这里给出的是本地文件系统例子,读者可自行测试ASM磁盘组。SYS@cams> alter system set db_recovery_file_dest=''; System altered. SYS@cams> alter system set log_archive_dest='/u01/app/oracle/archive'; System altered.
需要将LOG_ARCHIVE_DEST_n和DB_RECOVERY_FILE_DEST参数置空,以免出现如下问题:
SYS@cams> alter system set log_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST';alter system set log_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST'*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n orDB_RECOVERY_FILE_DEST
此时,可以看到trace日志的变化,LOG_ARCHIVE_DEST_1的默认值自动调整过程。其中,在使用Fast Recovery Area归档的情况下,LOG_ARCHIVE_DEST_1的默认值为USE_DB_RECOVERY_FILE_DEST。
Tue Dec 18 17:48:24 2018ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;Tue Dec 18 17:52:13 2018Cleared LOG_ARCHIVE_DEST_1 parameter default valueUsing LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/11.2.0/db_1/dbs/archALTER SYSTEM SET db_recovery_file_dest='' SCOPE=BOTH;Tue Dec 18 17:52:28 2018ALTER SYSTEM SET log_archive_dest='/u01/app/oracle/archive' SCOPE=BOTH;
--首先创建新的归档路径[oracle@XLJ181 trace]$ mkdir -p /u01/app/oracle/archive
--此时对日志进行切换,触发写归档日志SYS@cams> alter system switch logfile;System altered.
--查看新生成的归档日志[oracle@XLJ181 trace]$ ll /u01/app/oracle/archivetotal 11960-rw-r----- 1 oracle oinstall 12244992 Dec 18 17:57 1_25_994950965.dbf
trace日志中也可以看到LGWR switch操作
Tue Dec 18 17:57:10 2018Thread 1 cannot allocate new log, sequence 26Private strand flush not complete Current log# 1 seq# 25 mem# 0: /u01/app/oracle/oradata/cams/redo01.logThread 1 advanced to log sequence 26 (LGWR switch) Current log# 2 seq# 26 mem# 0: /u01/app/oracle/oradata/cams/redo02.logTue Dec 18 17:57:10 2018Archived Log entry 20 added for thread 1 sequence 25 ID 0x9e3b45f3 dest 1:
方案2:两路归档路径
如果要设置两路归档路径,常用的方案是配置log_archive_dest和log_archive_duplex_dest参数。用log_archive_dest_n参数的方法在第三种方案进行讨论。
具体操作如下:
--创建新的归档路径[oracle@XLJ181 trace]$ mkdir -p /u01/app/oracle/archive1
--设置log_archive_duplex_dest参数路径并切换日志触发写归档日志SYS@cams> alter system set log_archive_duplex_dest='/u01/app/oracle/archive1'; System altered. SYS@cams> alter system switch logfile; System altered.
--查看新生成的归档日志文件,显然新生成的归档大小和文件名一致。两份归档日志互为冗余。[oracle@XLJ181 trace]$ ll /u01/app/oracle/archivetotal 15704-rw-r----- 1 oracle oinstall 12244992 Dec 18 17:57 1_25_994950965.dbf-rw-r----- 1 oracle oinstall 3830272 Dec 18 18:30 1_26_994950965.dbf[oracle@XLJ181 trace]$ ll /u01/app/oracle/archive1total 3744-rw-r----- 1 oracle oinstall 3830272 Dec 18 18:30 1_26_994950965.dbf
查看trace日志,可以看到LGWR switch操作,然后分别在两个目录下写归档。
Tue Dec 18 18:30:01 2018Thread 1 cannot allocate new log, sequence 27Private strand flush not complete Current log# 2 seq# 26 mem# 0: /u01/app/oracle/oradata/cams/redo02.logThread 1 advanced to log sequence 27 (LGWR switch) Current log# 3 seq# 27 mem# 0: /u01/app/oracle/oradata/cams/redo03.logTue Dec 18 18:30:02 2018Archived Log entry 21 added for thread 1 sequence 26 ID 0x9e3b45f3 dest 1:Archived Log entry 22 added for thread 1 sequence 26 ID 0x9e3b45f3 dest 2:
方案3:多路归档路径
如果要设置多路归档路径,需要使用log_archive_dest_n参数,其中,n的取值为1到31的整数,也就是说,可以指定1到31个独立的路径作为归档日志的路径。
log_archive_dest_n参数的取值可以选择LOCATION或SERVIDE关键字。如果使用LOCATION关键字,可以选择本地文件系统、ASM磁盘组或USE_DB_RECOVERY_FILE_DEST代表的闪回恢复区;如果使用SERVICE关键字,可以选择备库的网络连接标识符连接备份数据库。
具体操作如下:
--创建本地归档目录[oracle@XLJ181 admin]$ mkdir -p /u01/app/oracle/archive2[oracle@XLJ181 admin]$ mkdir -p /u01/app/oracle/archive3
--配置log_archive_dest_n参数SYS@cams> alter system set log_archive_duplex_dest=''; System altered. SYS@cams> alter system set log_archive_dest=''; System altered. SYS@cams> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'; System altered. SYS@cams> alter system set log_archive_dest_2='LOCATION=/u01/app/oracle/archive2'; System altered. SYS@cams> alter system set log_archive_dest_3='LOCATION=/u01/app/oracle/archive3'; System altered.
需要注意置空的顺序,以免出现如下错误
SYS@cams> alter system set log_archive_dest='';alter system set log_archive_dest=''*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-16022: LOG_ARCHIVE_DEST cannot be NULL because LOG_ARCHIVE_DUPLEX_DEST isnon-NULL
需要将LOG_ARCHIVE_DEST和LOG_ARCHIVE_DUPLEX_DEST参数置空,以免出现如下问题:
SYS@cams> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST orLOG_ARCHIVE_DUPLEX_DEST
--切换日志,触发写归档日志SYS@cams> alter system switch logfile; System altered.
查看新生成的归档日志文件,显然新生成的归档大小和文件名一致。两份归档日志互为冗余。
[oracle@XLJ181 admin]$ ll /u01/app/oracle/archive2total 5440-rw-r----- 1 oracle oinstall 4416512 Dec 18 19:09 1_27_994950965.dbf-rw-r----- 1 oracle oinstall 1148416 Dec 18 19:23 1_28_994950965.dbf[oracle@XLJ181 admin]$ ll /u01/app/oracle/archive3total 5440-rw-r----- 1 oracle oinstall 4416512 Dec 18 19:09 1_27_994950965.dbf-rw-r----- 1 oracle oinstall 1148416 Dec 18 19:23 1_28_994950965.dbf
查看trace日志,看到错误提示,提示DB_RECOVERY_FILE_DEST未设置值
Tue Dec 18 19:23:59 2018Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_arc3_29341.trc:ORA-19801: initialization parameter DB_RECOVERY_FILE_DEST is not setErrors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_arc3_29341.trc:ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translatedORA-19801: initialization parameter DB_RECOVERY_FILE_DEST is not setArchived Log entry 25 added for thread 1 sequence 28 ID 0x9e3b45f3 dest 2:Archived Log entry 26 added for thread 1 sequence 28 ID 0x9e3b45f3 dest 3:
为DB_RECOVERY_FILE_DEST参数配置数值,切换日志
SYS@cams> alter system set DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'; System altered. SYS@cams> alter system switch logfile; System altered. SYS@cams> alter system switch logfile; System altered.
查看trace日志信息,最后可看到同时写入了3个归档路径
Tue Dec 18 19:27:58 2018**********************************************************WARNING: Files may exists in db_recovery_file_destthat are not known to the database. Use the RMAN commandCATALOG RECOVERY AREA to re-catalog any such files.If files cannot be cataloged, then manually delete themusing OS command.One of the following events caused this:1. A backup controlfile was restored.2. A standby controlfile was restored.3. The controlfile was re-created.4. db_recovery_file_dest had previously been enabled and then disabled.**********************************************************ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;Tue Dec 18 19:27:58 2018db_recovery_file_dest_size of 10240 MB is 5.63% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.Tue Dec 18 19:28:01 2018Thread 1 cannot allocate new log, sequence 30Private strand flush not complete Current log# 2 seq# 29 mem# 0: /u01/app/oracle/oradata/cams/redo02.logThread 1 advanced to log sequence 30 (LGWR switch) Current log# 3 seq# 30 mem# 0: /u01/app/oracle/oradata/cams/redo03.logTue Dec 18 19:28:01 2018Archived Log entry 27 added for thread 1 sequence 29 ID 0x9e3b45f3 dest 2:Archived Log entry 28 added for thread 1 sequence 29 ID 0x9e3b45f3 dest 3:Tue Dec 18 19:33:38 2018Thread 1 cannot allocate new log, sequence 31Private strand flush not complete Current log# 3 seq# 30 mem# 0: /u01/app/oracle/oradata/cams/redo03.logThread 1 advanced to log sequence 31 (LGWR switch) Current log# 1 seq# 31 mem# 0: /u01/app/oracle/oradata/cams/redo01.logTue Dec 18 19:33:39 2018Expanded controlfile section 11 from 28 to 62 recordsRequested to grow by 34 records; added 2 blocks of recordsArchived Log entry 29 added for thread 1 sequence 30 ID 0x9e3b45f3 dest 1:Archived Log entry 30 added for thread 1 sequence 30 ID 0x9e3b45f3 dest 2:Archived Log entry 31 added for thread 1 sequence 30 ID 0x9e3b45f3 dest 3:
感谢各位的阅读,以上就是"Oracle归档日志管理的方法有哪些"的内容了,经过本文的学习后,相信大家对Oracle归档日志管理的方法有哪些这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是,小编将为大家推送更多相关知识点的文章,欢迎关注!