四.redo log file(重做日志文件):存放所有事物日志 1. 重做日志文件概述
·记录数据库数据进行的所有修改操作,保证数据的一致性和安全性;
·数据库失败需要进行回复时,能提供恢复所需的依据;
·每个数据库应有多个redo log file,按序循环轮流被使用进行日志记载,每个redo log file称为一个日志组。一个例程至少需两组联机重做日志文件。多个日志组应分开放在不同的disk上以均衡disk I/O。
·为防止每个日志组文件本身失效,应对每个日志组文件进行至少一份镜像,每个日志组文件和其镜像文件称为其日志组的组员。两者应分开在不同的disk上以避免disk的失效。
2. 查看重做日志文件
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------
1 1 26 52428800 1 NO CURRENT 948038 31-MAR-10
2 1 24 52428800 1 NO INACTIVE 926844 31-MAR-10
3 1 25 52428800 1 NO INACTIVE 947638 31-MAR-10
日志组的状态:
current: 使用状态,该日志组正在使用中。
active: 激活状态,实例恢复时将需要该日志组。
inactive:非激活状态,实例恢复时不再需要该日志组。
unused: 未使用状态,该日志组尚未记录过日志信息(新添加组)。
SQL> select group#,status,type,substr(member,1,40) from v$logfile;
GROUP# STATUS TYPE SUBSTR(MEMBER,1,40)
---------- ------- ------- --------------------------------------------------------------------
3 STALE ONLINE /u01/app/oracle/oradata/ora10/redo03.log
2 STALE ONLINE /u01/app/oracle/oradata/ora10/redo02.log
1 ONLINE /u01/app/oracle/oradata/ora10/redo01.log
日志组成员的状态:
空: 该文件是可用/活动的(但不一定是正在使用的,参考v$log)
Stale: 表示该文件内容不完全(系统特殊状态)
invalid: 表明该文件暂不可访问(新添加镜像)
deleted:表明该文件已不再使用(已删除)
3. 修改重做日志文件
·添加日志组(添加重做日志文件个数)
SQL> alter database ora10 add logfile group
4 ('/u01/app/oracle/oradata/ora10/redo04.log') size 5M; 新添加组状态为unused
·添加日志组成员(添加镜像文件)
SQL> alter database ora10 add logfile member
2 '/u01/app/oracle/oradata/redo01.log' to group 1,
3 '/u01/app/oracle/oradata/redo02.log' to group 2,
4 '/u01/app/oracle/oradata/redo03.log' to group 3,
5 '/u01/app/oracle/oradata/redo04.log' to group 4;
SQL> select group#,status,type,substr(member,1,40) from v$logfile;
GROUP# STATUS TYPE SUBSTR(MEMBER,1,40)
---------- ------- ------- --------------------------------------------------------------------
3 STALE ONLINE /u01/app/oracle/oradata/ora10/redo03.log
2 STALE ONLINE /u01/app/oracle/oradata/ora10/redo02.log
1 ONLINE /u01/app/oracle/oradata/ora10/redo01.log
4 ONLINE /u01/app/oracle/oradata/ora10/redo04.log
1 INVALID ONLINE /u01/app/oracle/oradata/redo01.log
2 INVALID ONLINE /u01/app/oracle/oradata/redo02.log
3 INVALID ONLINE /u01/app/oracle/oradata/redo03.log
4 INVALID ONLINE /u01/app/oracle/oradata/redo04.log
SQL> alter database ora10 add logfile member
2 '/u01/app/oracle/oradata/redo01.log' to group 1,
3 '/u01/app/oracle/oradata/redo02.log' to group 2,
4 '/u01/app/oracle/oradata/redo03.log' to group 3,
5 '/u01/app/oracle/oradata/redo04.log' to group 4;
SQL> alter system switch logfile;
SQL> select group#,status,type,substr(member,1,40) from v$logfile;
GROUP# STATUS TYPE SUBSTR(MEMBER,1,40)
---------- ------- ------- -----------------------------------------
3 STALE ONLINE /u01/app/oracle/oradata/ora10/redo03.log
2 STALE ONLINE /u01/app/oracle/oradata/ora10/redo02.log
1 ONLINE /u01/app/oracle/oradata/ora10/redo01.log
4 ONLINE /u01/app/oracle/oradata/ora10/redo04.log
1 INVALID ONLINE /u01/app/oracle/oradata/redo01.log
2 INVALID ONLINE /u01/app/oracle/oradata/redo02.log
3 INVALID ONLINE /u01/app/oracle/oradata/redo03.log
4 ONLINE /u01/app/oracle/oradata/redo04.log
·删除日志组(消减重做日志文件个数)
SQL>alter database oracle drop logfile group 4;
SQL> select * from v$logfile;
SQL> select * from v$log;
inactive 和unused状态的日志组才能删除,current和active状态的日志组不能删除,要删除current状态的日志组,应先用logswitch 将current状态的日志组转换为active状态,而要删除active状态的日志组,则需用checkpoint将其转换为inactive状态, 才能删除。
SQL> alter system switch logfile;
·删除日志组成员(删除镜像文件)
SQL> alter database ora10 drop logfile
member '/u01/app/oracle/oradata/redo01.log';
日志组最后一个日志成员不能被删除;
current日志组中任何成员都不能删除;
其他情况下不能删组成员时,可用logswitch将日志组状态进行转换后才能删除。
·移动或重命名日志文件
SQL>shutdown immediate
SQL>host mv /u01/app/oracle/oradtata/ora10/*.log /u01/app
SQL>startup mount
SQL> alter database rename file
2 '/u01/app/oracle/oradata/ora10/redo01.log',
3 '/u01/app/oracle/oradata/ora10/redo02.log',
4 '/u01/app/oracle/oradata/ora10/redo03.log',
5 '/u01/app/oracle/oradata/ora10/redo04.log'
6 to
7 '/u01/app/redo01.log',
8 '/u01/app/redo02.log',
9 '/u01/app/redo03.log',
10 '/u01/app/redo04.log';
Database altered.
SQL> alter database open;
Database altered.
转载请保留固定链接: https://linuxeye.com/database/663.html |