E:\My Documents\radix\Oracle\常用sql>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 13 21:09:30 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL>conn / as sysdba;
Connected.
查询当前状态redolog
SQL>select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 61 52428800 1 INACTIVE
2 64 52428800 1 CURRENT
3 0 52428800 1 UNUSED
SQL>alter database add logfile member 'D:\oracle\oradata\test\REDO03_B.log' to group 3;
Database altered.
SQL>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE D:\ORACLE\ORADATA\TEST\REDO03.LOG NO
2 ONLINE D:\ORACLE\ORADATA\TEST\REDO02.LOG NO
1 ONLINE D:\ORACLE\ORADATA\TEST\REDO01.LOG NO
3 INVALID ONLINE D:\ORACLE\ORADATA\TEST\REDO03_B.LOG NO
SQL>select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 61 52428800 1 INACTIVE
2 64 52428800 1 CURRENT
3 0 52428800 2 UNUSED
切换logfile到第三组;
SQL>alter system switch logfile ;
System altered.
SQL>select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 61 52428800 1 INACTIVE
2 64 52428800 1 ACTIVE
3 65 52428800 2 CURRENT
将redolog日志归档
SQL>alter system checkpoint;
System altered.
SQL>select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 61 52428800 1 INACTIVE
2 64 52428800 1 INACTIVE
3 65 52428800 2 CURRENT
为前两组添加组成员
SQL>alter database add logfile member 'D:\oracle\oradata\test\REDO01_B.log' to group 1;
Database altered.
SQL>alter database add logfile member 'D:\oracle\oradata\test\REDO02_B.log' to group 2;
Database altered.
SQL>select group#,member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------
3 D:\ORACLE\ORADATA\TEST\REDO03.LOG
2 D:\ORACLE\ORADATA\TEST\REDO02.LOG
1 D:\ORACLE\ORADATA\TEST\REDO01.LOG
3 D:\ORACLE\ORADATA\TEST\REDO03_B.LOG
1 D:\ORACLE\ORADATA\TEST\REDO01_B.LOG
2 D:\ORACLE\ORADATA\TEST\REDO02_B.LOG
6 rows selected.
SQL>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE D:\ORACLE\ORADATA\TEST\REDO03.LOG NO
2 ONLINE D:\ORACLE\ORADATA\TEST\REDO02.LOG NO
1 ONLINE D:\ORACLE\ORADATA\TEST\REDO01.LOG NO
3 ONLINE D:\ORACLE\ORADATA\TEST\REDO03_B.LOG NO
1 INVALID ONLINE D:\ORACLE\ORADATA\TEST\REDO01_B.LOG NO
2 INVALID ONLINE D:\ORACLE\ORADATA\TEST\REDO02_B.LOG NO
6 rows selected.
SQL>alter system switch logfile;
System altered.
SQL>alter system switch logfile;
System altered.
SQL>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE D:\ORACLE\ORADATA\TEST\REDO03.LOG NO
2 ONLINE D:\ORACLE\ORADATA\TEST\REDO02.LOG NO
1 ONLINE D:\ORACLE\ORADATA\TEST\REDO01.LOG NO
3 ONLINE D:\ORACLE\ORADATA\TEST\REDO03_B.LOG NO
1 ONLINE D:\ORACLE\ORADATA\TEST\REDO01_B.LOG NO
2 ONLINE D:\ORACLE\ORADATA\TEST\REDO02_B.LOG NO
6 rows selected.
SQL>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------
1 1 66 52428800 512 2 YES ACTIVE 2098256 13-DEC-12 2098259 13-DEC-12
2 1 67 52428800 512 2 NO CURRENT 2098259 13-DEC-12 2.8147E+14
3 1 65 52428800 512 2 YES ACTIVE 2098093 13-DEC-12 2098256 13-DEC-12
SQL>alter system checkpoint;
System altered.
SQL>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------
1 1 66 52428800 512 2 YES INACTIVE 2098256 13-DEC-12 2098259 13-DEC-12
2 1 67 52428800 512 2 NO CURRENT 2098259 13-DEC-12 2.8147E+14
3 1 65 52428800 512 2 YES INACTIVE2098093 13-DEC-12 209825613-DEC-12
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ---------------------------------------- --- 3 ONLINE D:\ORACLE\ORADATA\TEST\REDO03.LOG NO 2 ONLINE D:\ORACLE\ORADATA\TEST\REDO02.LOG NO 1 ONLINE D:\ORACLE\ORADATA\TEST\REDO01.LOG NO 3 ONLINE D:\ORACLE\ORADATA\TEST\REDO03_B.LOG NO 1 ONLINE D:\ORACLE\ORADATA\TEST\REDO01_B.LOG NO 2 ONLINE D:\ORACLE\ORADATA\TEST\REDO02_B.LOG NO 6 rows selected.