Oracle11g温习-第八章：归档日志
2013年4月27日 星期六
10:36
|
1、归档和非归档的区别 |
|
1) 归档会在日志切换时，备份历史日志，用于OLTP（在线事务处理系统)，可以进行冷备份和热备份，可以实现数据库完全恢复、不完全恢复(基于时间点) 2) 归档会启用arch的后台进程、占用磁盘空间 3) 非归档用于OLAP/DSS（在线应用处理系统/数据决策系统)，只能冷备份，只能恢复到最后次备份状态 |
|
2、查看归档模式 |
|
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 可以通过V$INSTANCE和V$DATABASE查看归档状态 |
|
3、设置归档模式 |
|
SQL> shutdown immediate ——关库 ORACLE instance shut down.
SQL> startup mount ——起库到mount状态
SQL> alter database archivelog; ——修改归档模式
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
SQL> alter database open;
Database altered.
|
|
4、归档模式下配置 |
|
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 ——使用这10个参数设置归档日志的位置 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 ——一般不使用这两个参数设置归档位置，因为最多只能设2个位置 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
设定归档日志的目标路径：
SQL> alter system set log_archive_dest_1='location=/disk4/arch/lx02'; ---------把历史日志归档到本机目录下 （location 代表本机) SQL> alter system set log_archive_dest_2='service=test';（对方主机名)； ---------把历史日志归档到远程服务器下 SQL> alter system set log_archive_dest_1='location=/disk4/arch/cuug mandatory'; ——mandatory 强制，日志归档未完成之前不允许覆盖。（默认 optional，允许覆盖) ——默认optional（日志在没有归档完成前可以被覆盖) ——远程备份，把历史日志备份到，服务名为test的另外的数据库上。（service 代表远程)
设定归档日志文件名格式（log_archive_format)： %s ，sequence ，日志序列号 %t ，thread# ， 日志线程号 %r ，resetlog ， 代表数据库的周期
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
SQL> alter system set log_archive_format ='arch_%t_%s_%r.log' scope=spfile;
System altered.
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.
SQL> show parameter archive; log_archive_format string arch_%t_%s_%r.log |
|
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
|
|
6、日志归档 |
|
1)自动归档，日志切换时 2)手工： 3)在归档时，会把归档信息写入到控制文件
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
SQL> alter system archive log current; ——对当前日志归档
System altered.
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
SQL> alter system switch logfile;——日志切换时，自动归档
System altered.
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
查看已经归档日志： 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 |
