oracle日志操作记录

最新推荐文章于 2023-10-23 10:09:03 发布

cuanmo8775

最新推荐文章于 2023-10-23 10:09:03 发布

阅读量425

点赞数

文章标签：数据库

/*在startup之前,REDO01B.LOG已经被重命名为REDO01B.LOG--*/
/*在startup之前,REDO02A.LOG已经被重命名为REDO02A.LOG--*/
/*在startup之前,REDO03B.LOG已经被重命名为REDO03B.LOG--*/

感谢http://space.itpub.net/12778571/viewspace-201664 蒙昭良

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as system

SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
         1 STALE   ONLINE D:\ORACLE\ORADATA\RXL\REDO01A.LOG
         1 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO01B.LOG
         2 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO02A.LOG
         2                 ONLINE D:\ORACLE\ORADATA\RXL\REDO02B.LOG
         3 STALE   ONLINE D:\ORACLE\ORADATA\RXL\REDO03A.LOG
         3 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO03B.LOG

6 rows selected

SQL> alter database add logfile member 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG' to group 1
2 ;

alter database add logfile member 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG' to group 1

ORA-01577: 无法添加日志文件'D:\ORACLE\ORADATA\RXL\REDO01B.LOG' - 文件已是数据库的一部分

SQL> alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG';

alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG'

ORA-00362: 组成组 1 中的有效日志文件要求输入成员
ORA-01517: 日志成员: 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG'

SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
         1 STALE   ONLINE D:\ORACLE\ORADATA\RXL\REDO01A.LOG
         1 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO01B.LOG
         2 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO02A.LOG
         2              ONLINE D:\ORACLE\ORADATA\RXL\REDO02B.LOG
         3 STALE   ONLINE D:\ORACLE\ORADATA\RXL\REDO03A.LOG
         3 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO03B.LOG

6 rows selected

SQL> alter system switch logfile;

System altered

SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
         1 STALE   ONLINE D:\ORACLE\ORADATA\RXL\REDO01A.LOG
         1 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO01B.LOG
         2 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO02A.LOG
         2         ONLINE D:\ORACLE\ORADATA\RXL\REDO02B.LOG
         3         ONLINE D:\ORACLE\ORADATA\RXL\REDO03A.LOG
         3 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO03B.LOG

6 rows selected

SQL> alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG';

alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG'

ORA-00362: 组成组 1 中的有效日志文件要求输入成员
ORA-01517: 日志成员: 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG'

SQL> alter system switch logfile;

System altered

SQL> select * from v$logfile;
Cannot execute commands now

SQL>

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
         1         ONLINE D:\ORACLE\ORADATA\RXL\REDO01A.LOG
         1 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO01B.LOG
         2 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO02A.LOG
         2         ONLINE D:\ORACLE\ORADATA\RXL\REDO02B.LOG
         3         ONLINE D:\ORACLE\ORADATA\RXL\REDO03A.LOG
         3 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO03B.LOG

6 rows selected

小类注：
1.若是添加的日志成员已经存在，不管是其status是什么，必须先drop,之后才能add
2.在group1中,REDO01A.LOG 的status是STALE,此时再drop logfile，就会报ORA-00362错,
可以这么理解，因为REDO01A.LOG 的status是STALE了,此时再drop logfile REDO01B.LOG,
就会导致group1中没有日志可供使用.所以,先要alter system switch logfile;

3.当前的日志组不能删除,这个好理解.select status from v$log;查询日志组的状态.
status为current的为当前的日志组.这里我还不太明白current和active的含义.

SQL> alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG';

alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG'

ORA-01609: 日志1是线程1的当前日志 - 无法删除成员
ORA-00312: 联机日志 1 线程 1: 'D:\ORACLE\ORADATA\RXL\REDO01A.LOG'
ORA-00312: 联机日志 1 线程 1: 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG'

SQL> alter system switch logfile;

System altered

SQL> select * from v$logfile;

6 rows selected

SQL> alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG';

Database altered

SQL> alter database add logfile member 'D:\ORACLE\ORADATA\RXL\REDO01B.LOG' to group 1
2 ;

Database altered

SQL> alter database add logfile member 'D:\ORACLE\ORADATA\RXL\REDO01A.LOG' to group 2;

alter database add logfile member 'D:\ORACLE\ORADATA\RXL\REDO01A.LOG' to group 2

ORA-01577: 无法添加日志文件'D:\ORACLE\ORADATA\RXL\REDO01A.LOG' - 文件已是数据库的一部分

SQL> alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO02A.LOG';

alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO02A.LOG'

ORA-01609: 日志2是线程1的当前日志 - 无法删除成员
ORA-00312: 联机日志 2 线程 1: 'D:\ORACLE\ORADATA\RXL\REDO02A.LOG'
ORA-00312: 联机日志 2 线程 1: 'D:\ORACLE\ORADATA\RXL\REDO02B.LOG'

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         1          1         82 104857600          2 NO       ACTIVE                 4702690 2009-3-8 15
         2          1         83 104857600          2 NO       CURRENT                4702927 2009-3-8 15
         3          1         81 104857600          2 NO       INACTIVE               4702608 2009-3-8 15

SQL> select * from v$log;

SQL> select * from v$logfile;

6 rows selected

SQL> alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO02A.LOG';

alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO02A.LOG'

SQL> alter system switch logfile;

System altered

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         1          1         82 104857600          2 NO       INACTIVE               4702690 2009-3-8 15
         2          1         83 104857600          2 NO       ACTIVE                 4702927 2009-3-8 15
         3          1         84 104857600          2 NO       CURRENT                4705096 2009-3-8 16

SQL> alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO02A.LOG';

Database altered

SQL> alter database add logfile member 'D:\ORACLE\ORADATA\RXL\REDO01A.LOG' to group 2;

alter database add logfile member 'D:\ORACLE\ORADATA\RXL\REDO01A.LOG' to group 2

ORA-01577: 无法添加日志文件'D:\ORACLE\ORADATA\RXL\REDO01A.LOG' - 文件已是数据库的一部分

SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
         1         ONLINE D:\ORACLE\ORADATA\RXL\REDO01A.LOG
         1 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO01B.LOG
         2         ONLINE D:\ORACLE\ORADATA\RXL\REDO02B.LOG
         3         ONLINE D:\ORACLE\ORADATA\RXL\REDO03A.LOG
         3 INVALID ONLINE D:\ORACLE\ORADATA\RXL\REDO03B.LOG

SQL> alter database add logfile member 'D:\ORACLE\ORADATA\RXL\REDO02A.LOG' to group 2;

Database altered

SQL> select * from v$logfile;

6 rows selected

小类注：
1.在group1中, add logfile REDO02A.LOG 成功后,查询status是INVALID,
此时因为还没有使用REDO02A.LOG,所以status是INVALID.

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         1          1         82 104857600          2 NO       INACTIVE               4702690 2009-3-8 15
         2          1         83 104857600          2 NO       ACTIVE                 4702927 2009-3-8 15
         3          1         84 104857600          2 NO       CURRENT                4705096 2009-3-8 16

SQL> alter system switch logfile;

System altered

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         1          1         85 104857600          2 NO       CURRENT                4705344 2009-3-8 16
         2          1         83 104857600          2 NO       INACTIVE               4702927 2009-3-8 15
         3          1         84 104857600          2 NO       ACTIVE                 4705096 2009-3-8 16

SQL> alter database drop logfile member 'D:\ORACLE\ORADATA\RXL\REDO03B.LOG';

Database altered

SQL> alter database add logfile member 'D:\ORACLE\ORADATA\RXL\REDO03B.LOG' to group 3;
Cannot execute commands now

SQL>

Database altered

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/161195/viewspace-563252/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/161195/viewspace-563252/