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