ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 归档数据库

归档数据库

原创 Linux操作系统 作者:yuecaibo 时间:2012-03-05 23:59:39 0 删除 编辑

归档数据库
 在日志切换过程中 会循环覆盖 恢复性只限制于当前数据库里的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>

修改归档模式
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>


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.

SYS@beijing>


归档模式下 如果联机日志不能归档 有什么影响

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.

SYS@beijing>

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.

SYS@beijing>

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>

 

启用备用归档
SYS@beijing> alter system set log_archive_dest_1='location=/arc mandatory alternate=log_archive_dest_2 noreopen';

System altered.

SYS@beijing>

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.

SYS@beijing>


归档位置设置一个
当一个目的地不能归档成功后的影响
1、新用户无法登录 2、事务是否能产生
解决:在开启一个新的

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24756465/viewspace-717779/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 归档数据库
请登录后发表评论 登录
全部评论

注册时间:2012-03-03

  • 博文量
    42
  • 访问量
    36102