欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle如何重做日志组

程序员文章站 2022-04-09 19:41:26
1、查询数据库中的重做日志组 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME 1 ......

  1、查询数据库中的重做日志组

  sql> select * from v$log;

  group# thread# sequence# bytes blocksize members arc status first_change# first_tim next_change# next_time

  1 1 64 52428800 512 1 no current 1522229 17-may-19 2.8147e+14

  2 1 62 52428800 512 1 no inactive 1502072 16-may-19 1515210 17-may-19

  3 1 63 52428800 512 1 no inactive 1515210 17-may-19 1522229 17-may-19

  2、具体查看日志文件详情(位置)

  sql> select * from v$logfile;

  group# status type member is_recovery_dest_file

  3 online /u01/app/oradata/orcl/redo03.log no

  2 online /u01/app/oradata/orcl/redo02.log no

  1 online /u01/app/oradata/orcl/redo01.log no

  3、创建重做日志组

  sql> alter database add logfile ('/u01/app/oradata/orcl/hbk01.rdo','/u01/app/oradata/orcl/hbk02.rdo') size 5m;

  database altered.

  sql> select * from v$logfile;

  group# status type member is_recovery_dest_file

  3 online /u01/app/oradata/orcl/redo03.log no

  2 online /u01/app/oradata/orcl/redo02.log no

  1 online /u01/app/oradata/orcl/redo01.log no

  4 online /u01/app/oradata/orcl/hbk01.rdo no

  4 online /u01/app/oradata/orcl/hbk02.rdo no

  可以看到多了两个重做日志文件hbk01.rdo,hbk02.rdo,组的编号为4,查询v$log可以知道4号组的status为unused,表示新建状态,未使用。

  sql> select * from v$log;

  group# thread# sequence# bytes blocksize members arc status first_change# first_tim next_change# next_time

  1 1 64 52428800 512 1 no current 1522229 17-may-19 2.8147e+14

  2 1 62 52428800 512 1 no inactive 1502072 16-may-19 1515210 17-may-19

  3 1 63 52428800 512 1 no inactive 1515210 17-may-19 1522229 17-may-19

  4 1 0 5242880 512 2 yes unused 0 0

  可以看出我们创建重做日志组的编号默认系统产生

  我们也可以知道组编号

  sql> alter database add logfile group 8 ('/u01/app/oradata/orcl/hbk10.rdo') size 5m;

  database altered.

  sql> select * from v$logfile;

  group# status type member is_recovery_dest_file

  4、删除重做日志组

  删除重做日志组只是更新了控制文件,被删除的组所拥有的重做日志文件并没有从操作系统中删除,需要手工删除,而且只能删除状态是inavtive的重做日志组,如果想删除当前(current)的重做日志组,必须手工切换,可以使用alter system switch logfile,而且每个数据库至少需要有两组重做日志组,因为要循环使用。

  删除之前先查询下

  sql> select group#,status from v$log;

  group# status

  1 current

  2 inactive

  3 inactive

  4 unused

  8 unused

  像组2和组3都可以删,而组1为当前使用,不能删,除非切换使用日志组。删除之前先记下文件位置,方便手动执行操作系统rm删除命令

  alter database drop logfile group 2;

  5、删除日志成员

  需要注意的是,一个日志组中至少需要保留一个成员,而且只能删除状态不是online或current的日志成员。

  sql> select * from v$logfile;无锡正规妇科 http://www.xasgyy.net/

  group# status type member is_recovery_dest_file

  sql> alter database drop logfile member '/u01/app/oradata/orcl/hbk10.rdo';

  alter database drop logfile member '/u01/app/oradata/orcl/hbk10.rdo'

  error at line 1:

  ora-00362: member is required to form a valid logfile in group 8

  ora-01517: log member: '/u01/app/oradata/orcl/hbk10.rdo'

  sql> alter database drop logfile member '/u01/app/oradata/orcl/hbk8.rdo';

  database altered.

  7、查看重做日志是否归档

  sql> select group#,status,archived from v$log;

  group# status arc

  1 inactive no

  2 inactive no

  3 inactive no

  4 inactive no

  8 current no

  no表示未归档,yes表示已归档。

  因为我的没有开启归档模式,所以都显示no

  sql> select name,log_mode from v$database;

  name log_mode

  orcl noarchivelog