ホームページ >データベース >mysql チュートリアル >归档日志管理
归档日志管理
- WBOYオリジナル
- 2016-06-07 15:23:071758ブラウズ
数据环境： SQL select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CO
数据环境：SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production1. 归档日志概念
什么是归档重做日志：An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group. For example, if you are multiplexing your redo log, and if group 1 contains identical member files a_log1 andb_log1, then the archiver process (ARC<em>n</em>) will archive one of these member files. Should a_log1 become corrupted, then ARC<em>n</em> can still archive the identical b_log1. The archived redo log contains a copy of every group created since you enabled archiving.归档重做日志：就是在归档模式下，对非活动重做日志进行备份。有后台ARCn进程进行归档备份
[oracle@localhost orcl]$ ps -ef|grep arcoracle 3835 1 0 01:26 ? 00:00:01 ora_arc0_orcloracle 3837 1 0 01:26 ? 00:00:01 ora_arc1_orcloracle 3839 1 0 01:26 ? 00:00:01 ora_arc2_orcloracle 3841 1 0 01:26 ? 00:00:00 ora_arc3_orcl
2. 归档和非归档模式切换
模式切换需要在mount状态才可切换
SQL> startup mount;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 92
Next log sequence to archive 94
Current log sequence 94
归档模式切换到非归档模式：
SQL> alter database noarchivelog; Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 92
Current log sequence 94
非归档模式切换到归档模式:
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 93
Next log sequence to archive 95
Current log sequence 95
如果非归档模式切换到归档模式，报ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
错误原因：在非归档模式下，直接用shutdown abort关闭数据，导致ora-00265错误
解决方法：把数据启动到open状态，然后shutdown immediate关闭数据，重新把数据库启动mount状态
3. 配置归档进程
oracle 11g 默认归档进程是4
SQL> show parameter log_archive_max_processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 4
<strong>后台进程：</strong>
[oracle@localhost ~]$ ps -ef|grep arc
oracle 9164 1 0 17:12 ? 00:00:00 ora_arc0_orcl
oracle 9166 1 1 17:12 ? 00:00:00 ora_arc1_orcl
oracle 9168 1 1 17:12 ? 00:00:00 ora_arc2_orcl
oracle 9170 1 1 17:12 ? 00:00:00 ora_arc3_orcl
修改归档进程数量：
<strong>例如开启10个归档进程</strong>
SQL> alter system set log_archive_max_processes=10;
System altered.
SQL> show parameter log_archive_max_processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 10
[oracle@localhost ~]$ ps -ef|grep arc
oracle 9164 1 0 17:12 ? 00:00:00 ora_arc0_orcl
oracle 9166 1 0 17:12 ? 00:00:00 ora_arc1_orcl
oracle 9168 1 0 17:12 ? 00:00:00 ora_arc2_orcl
oracle 9170 1 0 17:12 ? 00:00:00 ora_arc3_orcl
oracle 9198 1 0 17:16 ? 00:00:00 ora_arc4_orcl
oracle 9200 1 0 17:16 ? 00:00:00 ora_arc5_orcl
oracle 9202 1 0 17:16 ? 00:00:00 ora_arc6_orcl
oracle 9204 1 0 17:16 ? 00:00:00 ora_arc7_orcl
oracle 9206 1 0 17:16 ? 00:00:00 ora_arc8_orcl
oracle 9208 1 0 17:16 ? 00:00:00 ora_arc9_orcl
4 配置归档文件格式
默认格式：%t_%s_%r.arc
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
修改归档文件格式，修改完成以后需要重启数据库才会生效
SQL> alter system set log_archive_format='%t_%s_%r.arc' <strong>scope=spfile; </strong>
System altered.
log_archive_format参数说明:
(<strong>归档文件格式必须包含以下几个参数：</strong><strong>%s, %t and %r,否则启动数据库会报错</strong>
SQL> startup open;
ORA-19905: log_archive_format must contain %s, %t and %r
）
%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
5. 修改归档文件目录
归档日志存默认路径：受DB_RECOVERY_FILE_DEST参数控制
SQL> select dest_id,dest_name,destination from v$archive_dest;
DEST_ID DEST_NAME DESTINATION
---------- -------------------- --------------------------------------------------
1 LOG_ARCHIVE_DEST_1 USE_<strong>DB_RECOVERY_FILE_DEST</strong>
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
<strong>db_recovery_file_dest </strong> string /home/oracle/app/oracle/flash_
recovery_area
语法：alter system set log_archive_dest_n='location=文件路径' scope=spfile
说明：a.文件存放路径必须<strong>本地需要用location指定，远程需要service指定</strong>，另外重启数据库才会生效。配置远程归档位置时,SERVICE选项需要指定远程数据库的网络服务名(在tnsnames.ora文件中配置)
b. 初始化参数LOG_ARCHIVE_DEST_n 不能与初始化参数LOG_ARCHIVE_DEST和LOG_ARCHIVE_DUPLEX_DEST同时使用.
(oracle官方文档说明：如果是企业，将不在推荐使用log_archive_duplex_dest参数
If you are using Oracle Enterprise Edition, this parameter is deprecated in favor of the LOG_ARCHIVE_DEST_n parameters.
If Oracle Enterprise Edition is not installed or it is installed but you have not specified any LOG_ARCHIVE_DEST_n parameters, this parameter is valid)
c.LOG_ARCHIVE_DEST_n参数
OPTIONAL:该选项是默认选项.使用该选项时,无论归档是否成功,都可以覆盖重做日志.
MANDATORY:强制归档.使用该选项时,只有在归档成功之后,重做日志才能被覆盖.
REOPEN:该属性用于指定重新归档的时间间隔,默认值为300秒,必须跟在MANDATORY后.
SQL> alter system set log_archive_dest_1='<strong>location=</strong>/home/oracle/app/oracle' <strong>scope=spfile;</strong>
System altered
SQL> select dest_id,dest_name,destination from v$archive_dest;
DEST_ID DEST_NAME DESTINATION
---------- -------------------- --------------------------------------------------
1 LOG_ARCHIVE_DEST_1 <strong>/home/oracle/app/oracle</strong>
SQL> alter system archive log current; （手动强制归档）
System altered.
[oracle@localhost oracle]$ ls -ls<strong> /home/oracle/app/oracle </strong>
总计 244
208 -rw-r----- 1 oracle oinstall 207872 12-10 18:26 4bc445e1_1_102_818781763.arc
归档日志已经在相应的目录下生成
知识扩展：
ALTER SYSTEM SWITCH LOGFILE 是强制日志切换，不一定就归档当前的重做日志文件（若自动归档打开，就归档前的重做日志，若自动归档没有打开，就不归档当前重做日志。）
ALTER SYSTEM ARCHIVE LOG CURRENT 是归档当前的重做日志文件，如果自动归档有没有打开，将报ORA-00258错误。
alter system archive log all;--归档所有已填满的联机日志（只能在非归档模式下起作用，如果在归档模式下运行，会报ORA-00271: there are no logs that need archiving）
<strong>主要的区别在于</strong>
ALTER SYSTEM SWITCH LOGFILE 对单实例数据库或RAC中的当前实例执行日志切换。
ALTER SYSTEM ARCHIVE LOG CURRENT 会对数据库中的所有实例执行日志切换
<strong>设置：log_archive_duplex_dest参数</strong>
SQL> alter system set log_archive_duplex_dest='/home/oracle/app/oracle/archiveduplex' scope=spfile;
<strong>取消归档文件路径</strong>
alter system set log_archive_dest_n='' scope=spfile
alter system set log_archive_duplex_dest='' scope=spfile
<strong>禁用某个归档日志路径</strong>
语法：alter system set LOG_ARCHIVE_DEST_STATE_n = { enable | defer | alternate }
SQL> alter system set log_archive_dest_state_2=defer;--禁用
5. 归档涉及到视图
v$archive_dest：查询归档所在的目录（重要字段是：dest_id , dest_name , destination）
v$loghist：显示日志历史信息
v$archive_processes：归档进程信息
v$archived_log：显示归档信息（重要字段：name, sequence#, first_change#）
声明：
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。