第八章： 归档日志
1、归档和非归档的区别
1）归档会在日志切换时，备份历史日志，用于OLTP，可以进行冷备份和热备份，可以实现数据库完全恢复、不完全恢复(基于时间点）
2）归档会启用arch的后台进程、占用磁盘空间
3）非归档用于OLAP/DSS，只能冷备份，只能恢复到最后次备份状态
2、查看归档模式：
05:26:29 SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 1
Current log sequence 3
05:26:53 SQL>
3、设置归档模式
05:26:53 SQL> shutdown immdiate
SP2-0717: illegal SHUTDOWN option
05:32:17 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
05:32:25 SQL> startup mount
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 125830908 bytes
Database Buffers 121634816 bytes
Redo Buffers 2973696 bytes
Database mounted.
05:32:38 SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 1
Current log sequence 3
05:32:41 SQL> alter database archivelog;
Database altered.
05:32:48 SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
05:32:49 SQL> alter database open;
Database altered.
05:32:58 SQL>
4、归档模式下配置
05:32:58 SQL> show parameter archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 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
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
remote_archive_enable string true
standby_archive_dest string ?/dbs/arch
05:34:04 SQL>
设定归档日志的目标路径：
05:35:40 SQL> alter system set log_archive_dest_1='location=/disk4/arch/lx02';
System altered.
---------把历史日志归档到本机目录下 （location 代表本机）
05:36:06 SQL> alter system set log_archive_dest_2='service=test';
System altered.
15:27:29 SQL> alter system set log_archive_dest_1='location=/disk4/arch/cuug mandatory';
System altered.
-----------------mandatory 强制，日志归档未完成之前不允许覆盖。（默认 optional，允许覆盖）
--------------------默认optional（日志在没有归档完成前可以被覆盖）
--------远程备份，把历史日志备份到，服务名为test的另外的数据库上。（service 代表远程）
05:36:21 SQL>
设定归档日志文件名格式：
05:40:42 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 10485760 2 YES INACTIVE 386892 02-AUG-11
3 1 1 10485760 2 YES INACTIVE 386891 02-AUG-11
2 1 3 10485760 2 NO CURRENT 387003 02-AUG-11
05:41:00 SQL> alter system set log_archive_format ='arch_%t_%s_%r.log' scope=spfile;
System altered.
05:43:00 SQL> startup force;
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 125830908 bytes
Database Buffers 121634816 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
05:43:18 SQL>
05:43:18 SQL> show parameter archive
log_archive_format string arch_%t_%s_%r.log
%s ，sequence ，日志序列号
%t ，thread# ，日志线程号
%r ，resetlog ，代表数据库的周期
5、查看归档进程
[oracle@oracle ~]$ ps -ef |grep ora_|grep -v grep
oracle 9404 1 0 05:43 ? 00:00:00 ora_pmon_lx02
oracle 9406 1 0 05:43 ? 00:00:00 ora_psp0_lx02
oracle 9408 1 0 05:43 ? 00:00:00 ora_mman_lx02
oracle 9410 1 0 05:43 ? 00:00:00 ora_dbw0_lx02
oracle 9412 1 0 05:43 ? 00:00:00 ora_lgwr_lx02
oracle 9414 1 0 05:43 ? 00:00:00 ora_ckpt_lx02
oracle 9416 1 0 05:43 ? 00:00:00 ora_smon_lx02
oracle 9418 1 0 05:43 ? 00:00:00 ora_reco_lx02
oracle 9420 1 0 05:43 ? 00:00:01 ora_mmon_lx02
oracle 9422 1 0 05:43 ? 00:00:00 ora_mmnl_lx02
oracle 9426 1 0 05:43 ? 00:00:00 ora_arc0_lx02
oracle 9428 1 0 05:43 ? 00:00:00 ora_arc1_lx02
oracle 9430 1 0 05:43 ? 00:00:00 ora_arc2_lx02
oracle 9432 1 0 05:43 ? 00:00:00 ora_qmnc_lx02
oracle 9438 1 0 05:43 ? 00:00:00 ora_cjq0_lx02
oracle 9440 1 0 05:43 ? 00:00:00 ora_q000_lx02
oracle 9442 1 0 05:43 ? 00:00:00 ora_q001_lx02
[oracle@oracle ~]$
5、日志归档：1）自动归档，日志切换时
2）手工：
3）在归档时，会把归档信息写入到控制文件
05:46:33 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 10485760 2 YES INACTIVE 386892 02-AUG-11
3 1 4 10485760 2 NO CURRENT 407544 02-AUG-11
2 1 3 10485760 2 YES INACTIVE 387003 02-AUG-11
05:46:38 SQL> alter system archive log current;
System altered.
05:46:45 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 5 10485760 2 NO CURRENT 407705 02-AUG-11
3 1 4 10485760 2 YES ACTIVE 407544 02-AUG-11
2 1 3 10485760 2 YES INACTIVE 387003 02-AUG-11
05:46:47 SQL> alter system switch logfile;
System altered.
05:46:59 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 5 10485760 2 YES ACTIVE 407705 02-AUG-11
3 1 4 10485760 2 YES ACTIVE 407544 02-AUG-11
2 1 6 10485760 2 NO CURRENT 407710 02-AUG-11
查看已经归档日志：
05:47:01 SQL> select name from v$archived_log;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/disk4/arch/lx02/arch_1_3_758093069.log
/disk4/arch/lx02/arch_1_4_758093069.log
/disk4/arch/lx02/arch_1_5_758093069.log
05:47:10 SQL>
