归档数据库
在日志切换过程中 会循环覆盖 恢复性只限制于当前数据库里的redolog
归档就是将覆盖前的redo备份出来 提高恢复性
归档运行方式: 当前组被冻结 ==> 备份redo(归档) ==> 切换下一组redo
这里归档无法写,也可以切换成功,但前提是新组(切换目标组)已经归档完毕
否则无法产生新连接.
归档由独立的进程ARC来完成
控制文件决定了是否归档 实例中的参数决定归档位置和格式
归档的参数
老版本的参数
只能归档到本地
和闪回不兼容
直接写路径 /arc
log_archive_dest
log_archive_duplex_dest
新版本的参数
即可到本地又可到远程
和闪回兼容
写路径 location=
log_archive_dest_1
log_archive_dest_2
...
归档文件的格式
%s SEQUENCE# 日志序列
%t THREAD#
%r resetlogs的版本
ALTER SYSTEM ARCHIVE LOG ALL;
归档除当前日志文件外的所有未归档日志文件，不做日志切换。
ALTER SYSTEM ARCHIVE LOG CURRENT;
归档所有未归档的日志文件，包括当前日志文件，并做日志切换。
将数据库转换成归档模式 必须再mount模式下启用归档 而且上一次是一致性停库
查看数据库是否归档
SYS@beijing> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 22
Current log sequence 25
SYS@beijing>
或者
SYS@beijing> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
修改归档模式
SYS@beijing> shut immediate (必须一致停库)
SYS@beijing> startup mount
SYS@beijing> alter database archivelog;
SYS@beijing> alter database open;
SYS@beijing> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 22
Next log sequence to archive 25
Current log sequence 25
SYS@beijing>
归档位置
由 USE_DB_RECOVERY_FILE_DEST 参数中的路径来决定
如果此参数空(未设置) 归档在 $ORACLE_HOME/flash_recovery_area/$ORACLE_SID/archivelog/年_月_日/ 目录
SYS@beijing> show parameter USE_DB_RECOVERY_FILE_DEST
SYS@beijing>
转换为非归档
1.正常停库
2.启动到mount
3.转换非归档
alter database noarchivelog;
4.启库
修改存档位置
由参数log_archive_dest 来决定存储位置
可以归档到10个位置 还有控制参数的状态
自己建立目录
SYS@beijing> ! mkdir $ORACLE_BASE/arc1
SYS@beijing>
SYS@beijing> alter system set log_archive_dest_1='location=/home/oracle/arc1';
System altered.
SYS@beijing>
SYS@beijing> alter system switch logfile;
System altered.
SYS@beijing> ! ls -lthr $ORACLE_BASE/arc1
总计 8.0K
-rw-r----- 1 oracle oinstall 4.5K 02-22 03:02 1_28_743735468.dbf
SYS@beijing> SELECT NAME FROM V$BGPROCESS where PADDR <> '00';
NAME
--------------------------------------------------
PMON
PSP0
MMAN
DBW0
ARC0
ARC1
ARC2
LGWR
CKPT
SMON
RECO
CJQ0
QMNC
MMON
MMNL
15 rows selected.
归档模式下 如果联机日志不能归档 有什么影响
mkdir /arc
chmod 777 /arc
SYS@beijing> alter system set log_archive_dest_1='location=/arc'
SYS@beijing> select SEQUENCE#,name from v$archived_log;
SEQUENCE# NAME
---------- --------------------------------------------------
26 /home/oracle/arc11_26_743735468.dbf
27 /home/oracle/arc11_27_743735468.dbf
28 /home/oracle/arc1/1_28_743735468.dbf
29 /home/oracle/arc1/1_29_743735468.dbf
SYS@beijing> alter system switch logfile;
System altered.
SYS@beijing> select SEQUENCE#,name from v$archived_log;
SEQUENCE# NAME
---------- --------------------------------------------------
26 /home/oracle/arc11_26_743735468.dbf
27 /home/oracle/arc11_27_743735468.dbf
28 /home/oracle/arc1/1_28_743735468.dbf
29 /home/oracle/arc1/1_29_743735468.dbf
30 /arc/1_30_743735468.dbf
6 rows selected.
chmod 755 /arc 使oracle用户无法写归档目录
连续切日志 一个轮回后 数据库hang住 此时普通用户不能登录
idle> conn /as sysdba
Connected.
SYS@beijing> conn scott/seker
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
Warning: You are no longer connected to ORACLE.
@>
即使把权限改成777也不会归档 数据库内部已经认为此位置无效了
解决方法 建立新的位置:
mkdir /arclog
chmod 777 /arclog
用sys启动新session 开启一个新的归档位置
SYS@beijing> alter system set log_archive_dest_2='location=/arclog';
System altered.
hang住的session也正常归档了
SYS@beijing> select SEQUENCE#,NAME from v$archived_log;
SEQUENCE# NAME
---------- --------------------------------------------------
25 /home/oracle/flash_recovery_area/BEIJING/archivelo
g/2011_02_22/o1_mf_1_25_6p5f9m61_.arc
26 /home/oracle/arc11_26_743735468.dbf
27 /home/oracle/arc11_27_743735468.dbf
28 /home/oracle/arc1/1_28_743735468.dbf
29 /home/oracle/arc1/1_29_743735468.dbf
30 /arc/1_30_743735468.dbf
31 /arclog/1_31_743735468.dbf
32 /arclog/1_32_743735468.dbf
33 /arclog/1_33_743735468.dbf
34 /arclog/1_34_743735468.dbf
10 rows selected.
启用备用归档
SYS@beijing> alter system set log_archive_dest_1='location=/arc mandatory alternate=log_archive_dest_2 noreopen';
System altered.
alter system set log_archive_dest_2='location=/arclog optional';
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=alternate;
相关参数
mandatory:强制归档，归档成功后，重做日志才能被覆盖。
alter system set log_archive_dest_1='location=D:\ora10\archive1 mandatory';
reopen:指定重新归档的时间间隔。
alter system set log_archive_dest_1='location=D:\ora10\archive1 mandatory reopen=500';
optional:无论归档是否成功，都覆盖重做日志
alter system set log_archive_dest_1='location=D:\ora10\archive1 optional';
控制本地归档成功的最小个数
alter system set log_archive_min_succeed_dest=2;
控制归档位置(对log_archive_dest_n配置的归档位置)
禁用归档位置
alter system set log_archive_dest_state_2=defer;
启用归档位置
alter system set log_archive_dest_state_2=enable;
alternate=log_are_dest_2
这个参数作用:当前location=/arc失效时,启用log_are_dest_2路径归档 就是备用
SYS@beijing> select SEQUENCE#,NAME from v$archived_log;
SEQUENCE# NAME
---------- --------------------------------------------------
34 /arclog/1_34_743735468.dbf
35 /arc/1_35_743735468.dbf
36 /arc/1_36_743735468.dbf
chmod 755 /arc
alter system switch logfile;
alter system switch logfile;
SYS@beijing> select SEQUENCE#,NAME from v$archived_log;
SEQUENCE# NAME
---------- --------------------------------------------------
34 /arclog/1_34_743735468.dbf
35 /arc/1_35_743735468.dbf
36 /arc/1_36_743735468.dbf
37 /arclog/1_37_743735468.dbf
38 /arclog/1_38_743735468.dbf
日志挖掘 DDL直接挖 但挖掘dml语句需要将数据库置为 追加日志数据模式.
1.修改追加日志数据模式
SYS@beijing> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SYS@beijing> alter database add SUPPLEMENTAL log data;
Database altered.
SYS@beijing> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
SYS@beijing> conn /as sysdba
2.生成日志挖掘队列:
exec dbms_logmnr.add_logfile('/home/oracle/oradata/ora10g/redo01abc.log');
也可以继续把其他redo文件继续添加进去
exec dbms_logmnr.add_logfile('/home/oracle/oradata/ora10g/redo02.log');
3.开始挖:
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
4.从v$logmnr_contents查前滚SQL和反算回来的回滚SQL
select sql_redo from v$logmnr_contents where lower(sql_redo) like '%update%' and seg_name='EMP';
select sql_undo from v$logmnr_contents where lower(sql_redo) like '%update%' and seg_name='EMP';
5.结束挖掘
EXECUTE DBMS_LOGMNR.END_LOGMNR;
练习:将commit的update数据找回来
idle> conn / as sysdba
Connected.
SYS@beijing> alter database add SUPPLEMENTAL log data;
Database altered.
SYS@beijing> alter user scott account unlock identified by seker;
User altered.
产生交易:
SYS@beijing> update scott.emp set sal=100 ;
14 rows updated.
SYS@beijing> commit;
Commit complete.
SYS@beijing> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------
1 1 39 52428800 2 YES INACTIVE 712844 22-FEB-11
2 1 38 52428800 2 YES INACTIVE 712840 22-FEB-11
3 1 40 52428800 2 NO CURRENT 713890 22-FEB-11
4 1 37 52428800 2 YES INACTIVE 712820 22-FEB-11
SYS@beijing> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- --------------------------------------------- ---
3 ONLINE /home/oracle/oradata/ora10g/redo03.log NO
2 ONLINE /home/oracle/oradata/ora10g/redo02.log NO
1 ONLINE /home/oracle/oradata/ora10g/redo01abc.log NO
4 ONLINE /home/oracle/oradata/ora10g/redo04.log NO
1 ONLINE /home/oracle/oradata/ora10g/redo01a.log NO
2 ONLINE /home/oracle/oradata/ora10g/redo02a.log NO
3 ONLINE /home/oracle/oradata/ora10g/redo03a.log NO
4 ONLINE /home/oracle/oradata/ora10g/redo04a.log NO
8 rows selected.
SYS@beijing> exec dbms_logmnr.add_logfile('/home/oracle/oradata/ora10g/redo03.log');
PL/SQL procedure successfully completed.
SYS@beijing> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SYS@beijing> select sql_redo from v$logmnr_contents where lower(sql_redo) like '%update%' and seg_name='EMP';
SQL_REDO
--------------------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '800' and ROWID = 'AAAMn/AAFAAAAAfAAA';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '1600' and ROWID = 'AAAMn/AAFAAAAAfAAB';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '1250' and ROWID = 'AAAMn/AAFAAAAAfAAC';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '2975' and ROWID = 'AAAMn/AAFAAAAAfAAD';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '1250' and ROWID = 'AAAMn/AAFAAAAAfAAE';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '2850' and ROWID = 'AAAMn/AAFAAAAAfAAF';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '2450' and ROWID = 'AAAMn/AAFAAAAAfAAG';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '3000' and ROWID = 'AAAMn/AAFAAAAAfAAH';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '5000' and ROWID = 'AAAMn/AAFAAAAAfAAI';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '1500' and ROWID = 'AAAMn/AAFAAAAAfAAJ';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '1100' and ROWID = 'AAAMn/AAFAAAAAfAAK';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '950' and ROWID = 'AAAMn/AAFAAAAAfAAL';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '3000' and ROWID = 'AAAMn/AAFAAAAAfAAM';
update "SCOTT"."EMP" set "SAL" = '100' where "SAL" = '1300' and ROWID = 'AAAMn/AAFAAAAAfAAN';
14 rows selected.
SYS@beijing> select sql_undo from v$logmnr_contents where lower(sql_redo) like '%update%' and seg_name='EMP';
SQL_UNDO
--------------------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '800' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAA';
update "SCOTT"."EMP" set "SAL" = '1600' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAB';
update "SCOTT"."EMP" set "SAL" = '1250' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAC';
update "SCOTT"."EMP" set "SAL" = '2975' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAD';
update "SCOTT"."EMP" set "SAL" = '1250' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAE';
update "SCOTT"."EMP" set "SAL" = '2850' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAF';
update "SCOTT"."EMP" set "SAL" = '2450' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAG';
update "SCOTT"."EMP" set "SAL" = '3000' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAH';
update "SCOTT"."EMP" set "SAL" = '5000' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAI';
update "SCOTT"."EMP" set "SAL" = '1500' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAJ';
update "SCOTT"."EMP" set "SAL" = '1100' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAK';
update "SCOTT"."EMP" set "SAL" = '950' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAL';
update "SCOTT"."EMP" set "SAL" = '3000' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAM';
update "SCOTT"."EMP" set "SAL" = '1300' where "SAL" = '100' and ROWID = 'AAAMn/AAFAAAAAfAAN';
14 rows selected.
SYS@beijing> EXECUTE DBMS_LOGMNR.END_LOGMNR;
PL/SQL procedure successfully completed.
归档位置设置一个
当一个目的地不能归档成功后的影响
1、新用户无法登录 2、事务是否能产生
解决：在开启一个新的
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24756465/viewspace-717779/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24756465/viewspace-717779/
8964
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
