1、报错查看SQL> alter database open resetlogs upgrade;alter database open resetlogs upgrade*ERROR at line 1:ORA-00392: log 3 of thread 1 is being cleared, operation not allowedORA-00312: online log 3 thread 1: '/u01/oradata/orcl/redo03.log'
2、查看当前日志组状态SQL> select group#,bytes/1024/1024||'M',status from v$log; GROUP# BYTES/1024/1024||'M' STATUS---------- ----------------------------------------- ---------------- 1 50M CLEARING 3 50M CLEARING_CURRENT 2 50M CLEARING
3、clear 日志组SQL> alter database clear logfile group 1;Database altered.SQL> alter database clear logfile group 2;Database altered.SQL> alter database clear logfile group 3;Database altered.SQL> select group#,bytes/1024/1024||'M',status from v$log; GROUP# BYTES/1024/1024||'M' STATUS---------- ----------------------------------------- ---------------- 1 50M UNUSED 3 50M CURRENT 2 50M UNUSED
4、验证是否报错alter database open resetlogs upgrade;注意：执行这条sql的日志文件的物理变化，那就是执行之前目录中还没有日志文件，执行之后，日志文件自动创建
5、日志组合日志文件之间的关系SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;GROUP# STATUS TYPE MEMBER------ ---------- ---------- ------------------------------ 1 ONLINE /u01/oradata/orcl/redo01.log 2 ONLINE /u01/oradata/orcl/redo02.log 3 ONLINE /u01/oradata/orcl/redo03.log此时每个group组中都有一个member成员1：每个日志组至少有一个成员，成员之间的关系是镜像关系2：每个数据库中至少有两个组来回switch切换
6、日志管理（常用）1：切换日志：alter s鲻戟缒男ystem switch logfile2：添加一个日志组SQL> alter database 锾攒揉敫add logfile group 4 '/u01/oradata/orcl/redo04.log' size 20m;SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;GROUP# STATUS TYPE MEMBER------ ---------- ---------- ------------------------------ 1 ONLINE /u01/oradata/orcl/redo01.log 2 ONLINE /u01/oradata/orcl/redo02.log 3 ONLINE /u01/oradata/orcl/redo03.log 4 ONLINE /u01/oradata/orcl/redo04.log 注意：如果没有制定组号，数据库会在创建时自动添加当前最大组号加13：添加一个成员SQL> alter database add logfile member '/u01/oradata/orcl/redo04a.log' to group 4;SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;GROUP# STATUS TYPE MEMBER------ ---------- ---------- ------------------------------ 1 ONLINE /u01/oradata/orcl/redo01.log 2 ONLINE /u01/oradata/orcl/redo02.log 3 ONLINE /u01/oradata/orcl/redo03.log 4 ONLINE /u01/oradata/orcl/redo04.log 4 INVALID ONLINE /u01/oradata/orcl/redo04a.log注意：1）添加成员不指定大小，镜像关系会自动设置相同大小2）添加之后处于invalid非正常状态，需要不停的切换日志才能变成正常状态4：删除一个成员SQL> alter database drop logfile member '/u01/oradata/orcl/redo04.log';注意：1）CURRENT状态不能删除,先切换再删除 alter system switch logfile;2）ACTIVE有时候能删，有时候不能删，如果删除报错，只能等5：删除一个日志组SQL> alter database drop logfile group 3;注意：1）CURRENT状态不能删除,先切换再删除 alter system switch logfile;2）ACTIVE有时候能删，有时候不能删，如果删除报错，只能等6：清楚报废日志SQL> alter database clear logfile group 4;注意：如果redo04.log文件报废，而且处于非CURRENT状态
