重做日志文件管理(增,删,改,查,切).docx

【实验】重做日志文件（redo log files）管理（增，删，改，查，切）上一篇 / 下一篇 2009-03-09 17:39:30 / 天气: 天气不错，能看到蓝天 / 心情: 高兴 / 个人分类：Oracle 查看( 46 ) / 评论( 0 ) / 评分( 0 / 0 ) 1.当前日志相关信息sysora11g select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM- - - - - - - - - 1 1 10 209715200 1 YES INACTIVE 461938 09-MAR-09 2 1 11 209715200 1 NO CURRENT 485885 09-MAR-09 3 1 9 209715200 1 YES INACTIVE 432636 04-MAR-09sysora11g select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_- - - - - 1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO 2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO 3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO2.添加重做日志组sysora11galter database add logfile group 4 (/oracle/u02/oradata/ora11g/redo04_01.log,/oracle/u02/oradata/ora11g/redo04_02.log) size 50m;Database altered.sysora11g select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM- - - - - - - - - 1 1 10 209715200 1 YES INACTIVE 461938 09-MAR-09 2 1 11 209715200 1 NO CURRENT 485885 09-MAR-09 3 1 9 209715200 1 YES INACTIVE 432636 04-MAR-09 4 1 0 52428800 2 YES UNUSED 0sysora11g select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_- - - - - 1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO 2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO 3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO 4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO 4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO3.添加日志文件sysora11galter database add logfile member /oracle/u02/oradata/ora11g/redo01_02.log to group 1, /oracle/u02/oradata/ora11g/redo02_02.log to group 2, /oracle/u02/oradata/ora11g/redo03_02.log to group 3;Database altered.sysora11g select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM- - - - - - - - - 1 1 10 209715200 2 YES INACTIVE 461938 09-MAR-09 2 1 11 209715200 2 NO CURRENT 485885 09-MAR-09 3 1 9 209715200 2 YES INACTIVE 432636 04-MAR-09 4 1 0 52428800 2 YES UNUSED 0sysora11g select * from v$logfile order by 1; GROUP# STATUS TYPE MEMBER IS_- - - - - 1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO 1 INVALID ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO 2 INVALID ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO 2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO 3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO 3 INVALID ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO 4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO 4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO8 rows selected.4.重命名日志成员在重命名日志组成员之前新的目标必须已经存在。Oracle的sql命令只是把控制文件中的内部指针指向新的日志文件。1).关闭数据库sysora11g shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.2).使用操作系统命令重命名或移动日志文件ora11gRHEL53 /oracle/u02/oradata/ora11g$mv redo01.log redo01_01.logora11gRHEL53 /oracle/u02/oradata/ora11g$mv redo02.log redo02_01.logora11gRHEL53 /oracle/u02/oradata/ora11g$mv redo03.log redo03_01.log3).启动数据库实例到mount状态，重命名控制文件中的日志文件成员。NotConnected select * from v$logfile order by 1,4; GROUP# STATUS TYPE MEMBER IS_- - - - - 1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO 2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO 3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO 4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO 4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO 1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO 2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO 3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO8 rows selected.NotConnectedalter database rename file /oracle/u02/oradata/ora11g/redo01.log to /oracle/u02/oradata/ora11g/redo01_01.log;Database altered.NotConnectedalter database rename file /oracle/u02/oradata/ora11g/redo02.log to /oracle/u02/oradata/ora11g/redo02_01.log;Database altered.NotConnectedalter database rename file /oracle/u02/oradata/ora11g/redo03.log to /oracle/u02/oradata/ora11g/redo03_01.log;Database altered.4).open数据库，验证结果NotConnectedalter database open;Database altered.sysora11g select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM- - - - - - - - - 1 1 18 209715200 2 YES INACTIVE 486960 09-MAR-09 2 1 19 209715200 2 YES INACTIVE 486964 09-MAR-09 3 1 21 209715200 2 NO CURRENT 486973 09-MAR-09 4 1 20 52428800 2 YES INACTIVE 486968 09-MAR-09sysora11g select * from v$logfile order by 1,4; GROUP# STATUS TYPE MEMBER IS_- - - - - 1 ONLINE /oracle/u02/oradata/ora11g/redo01_01.log NO 1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO 2 ONLINE /oracle/u02/oradata/ora11g/redo02_01.log NO 2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO 3 ONLINE /oracle/u02/oradata/ora11g/redo03_01.log NO 3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO 4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO 4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO8 rows selected.5).最后，不要忘记备份控制文件sysora11galter database backup controlfile to trace;Database altered.5.删除一个非活动的重做日志组的成员sysora11g select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM- - - - - - - - - 1 1 18 209715200 2 YES INACTIVE 486960 09-MAR-09 2 1 19 209715200 2 YES INACTIVE 486964 09-MAR-09 3 1 21 209715200 2 NO CURRENT 486973 09-MAR-09 4 1 20 52428800 2 YES INACTIVE 486968 09-MAR-09sysora11galter database drop logfile member /oracle/u02/oradata/ora11g/redo04_02.log;Database altered.sysora11g !ls -l /oracle/u02/oradata/ora11g/redo04_02.log-rw-r- 1 oracle oinstall 52429312 Mar 9 16:28 /oracle/u02/oradata/ora11g/redo04_02.logsysora11g!rm -f /oracle/u02/oradata/ora11g/redo04_02.logsysora11g select * from v$logfile order by 1,4; GROUP# STATUS TYPE MEMBER IS_- - - - - 1 ONLINE /oracle/u02/oradata/ora11g/redo01_01.log NO 1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO 2 ONLINE /oracle/u02/oradata/ora11g/redo02_01.log NO 2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO 3 ONLINE /oracle/u02/oradata/ora11g/redo03_01.log NO 3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO 4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO7 rows selected.6.删除一个非活动的重做日志组sysora11galter database drop logfile group 4;Database altered.sysora11g!rm -f /oracle/u02