Oracle在线日志添加

查看当前日志组的状态，日志组的编号

sql> SELECT group#,members,bytes/1024/1024 byte_mb,status FROM v$log;

GROUP#    MEMBERS    BYTE_MB STATUS

---------- ---------- ---------- ------------------------------------------------

1          2        100 CURRENT

2          2        100 INACTIVE

3          2        100 INACTIVE

查看日志文件所在的位置

sql>SELECT group#,member FROM v$logfile;

GROUP# MEMBER

---------- --------------------

3  /u01/app/oracle/oradata/ORCL/redo03a.log 

3  /u01/app/oracle/oradata/ORCL/redo03b.log 

2 /u01/app/oracle/oradata/ORCL/redo02a.log 

2 /u01/app/oracle/oradata/ORCL/redo02b.log 

1 /u01/app/oracle/oradata/ORCL/redo01a.log

1 /u01/app/oracle/oradata/ORCL/redo01b.log

6 rows selected.

增加新的日志组

sql>ALTER DATABASE ADD LOGFILE GROUP4('/u01/app/oracle/oradata/ORCL/redo04a.log','/u01/app/oracle/oradata/ORCL/redo04b.log') SIZE 100M;
Database altered.

sql>ALTER DATABASE ADD LOGFILE GROUP5('/u01/app/oracle/oradata/ORCL/redo05a.log','/u01/app/oracle/oradata/ORCL/redo05b.log') SIZE 100M;
Database altered.

sql>ALTER DATABASE ADD LOGFILE GROUP6('/usr/oracle/app/oradata/orcl/redo06a.log','/usr/oracle/app/oradata/orcl/redo06b.log') SIZE 100M;
Database altered.

切换当前日志到新的日志组

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

查看切换后的状态

sql> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;

GROUP#    MEMBERS    BYTE_MB STATUS

---------- ---------- ---------- ------------------------------------------------

1          2        100 ACTIVE

2          2        100 INACTIVE

3          2        100 INACTIVE

4          2       1024 CURRENT

5          2       1024 UNUSED

6          2       1024 UNUSED

6 rows selected.

改变检查点

sql>ALTER SYSTEM CHECKPOINT;

System altered.
备份当前的最新的控制文件

sql>alter database backupcontrolfile to trace resetlogs;

清空重做日志文件

alter database clear logfile group 4;

更改重做日志文件的位置或名称

SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/orcl/redo06.log' TO '/u01/app/oracle/oradata/orcl01/redo06.log';

Database altered.

删除日志组

sql>alter database drop logfile group 4; 

sql>alter database drop logfile group 5; 

sql>alter database drop logfile group 6;