ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 （logfile基础操作）

oracle实验记录 （logfile基础操作）

原创 Linux操作系统 作者：fufuh2o 时间：2009-07-28 09:28:28 0 删除 编辑

logfile的基本操作
SQL> select group#,member from v$logfile;

    GROUP#
----------
MEMBER
------------------------------------------------------------------------------

         3
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG

         2
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG

         1
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG


    GROUP#
----------
MEMBER
------------------------------------------------------------------------------

         1
E:\REDO01_2.LOG


添加一个group
SQL> alter database add logfile group 4 ('e:\redo04_1.log','e:\redo04_2.log') si
ze 10m;

Database altered.

SQL> select group#,member from v$logfile;


SQL> alter database add logfile group 5 ('e:\redo05_1.log','d:\redo05_2.log') si
ze 10m;

Database altered.

    GROUP# MEMBER
---------- ------------------------------
         3 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO03.LOG

         2 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO02.LOG

         1 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO01.LOG

         1 E:\REDO01_2.LOG
         4 E:\REDO04_1.LOG

    GROUP# MEMBER
---------- ------------------------------
         4 E:\REDO04_2.LOG

6 rows selected.

添加组员(向组)
SQL> alter database add logfile member 'e:\redo04_3.log' to group 4;

Database altered.

添加组员(向组中成员 ,组中有几个写几个)
  MAXLOGMEMBERS 3 controlfile中 所以group 4已经不能添加
SQL> alter database add logfile member 'e:\redo04_4.log' to ('E:\REDO04_2.LOG','
E:\REDO04_1.LOG','e:\redo04_3.log');
alter database add logfile member 'e:\redo04_4.log' to ('E:\REDO04_2.LOG','E:\RE
DO04_1.LOG','e:\redo04_3.log')
*
ERROR at line 1:
ORA-00357: too many members specified for log file, the maximum is 3


SQL> alter database add logfile member 'e:\redo01_3.log' to ('E:\REDO01_2.LOG','
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG');

Database altered.     给group1加
SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ------------------------------
         3 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO03.LOG

         2 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO02.LOG

         1 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO01.LOG

         1 E:\REDO01_2.LOG
         4 E:\REDO04_1.LOG

    GROUP# MEMBER
---------- ------------------------------
         4 E:\REDO04_2.LOG
         4 E:\REDO04_3.LOG
         1 E:\REDO01_3.LOG

8 rows selected.

 

 

删除操作
SQL> select group# ,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 INACTIVE~~~~~~~~~不活动的可以删除
         2          1 INACTIVE
         3          1 CURRENT
         4          3 UNUSED**************新加入的还未使用 可以删除


SQL> alter database drop logfile  member 'E:\REDO04_2.LOG';

Database altered. 删除1个成员
drop logfile  group 4删除整个组
另外还有clear logfile 清除logfile重建 无法归档的话unarchived

 

移动操作
只能移动不活动的
查看环境
SQL> select group# ,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 INACTIVE
         2          1 UNUSED
         3          1 INACTIVE
         4          2 CURRENT

SQL> col member format a30
SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ------------------------------
         3 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO03.LOG

         2 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO02.LOG

         1 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO01.LOG

         1 E:\REDO01_2.LOG
         4 E:\REDO04_1.LOG

    GROUP# MEMBER
---------- ------------------------------
         4 E:\REDO04_3.LOG
         1 E:\REDO01_3.LOG

7 rows selected.

目标:移动group 4中 一个member, 并删除另一个member or group

SQL> alter system switch logfile;

System altered.

SQL> select group# ,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 INACTIVE
         2          1 CURRENT
         3          1 INACTIVE
         4          2 ACTIVE~~~~~~~~~~~~~~~还有未写入disk的 dirty buffer

SQL> alter system checkpoint;~~~~~~~~实验环境dirty block数目还不足以发生检查点写入,为了实验速度 ,手动执行检查点 写入
从alert可以看出 switch之后 在等待 并没有立刻完成( 完成条件不足) swtich  logfile是个级别低的full checkpoint 发生后要等待 条件满足DBWn才写,具体条件会单独在另一次实验中记录(增量检查点，完全检查点)

Beginning log switch checkpoint up to RBA [0x8.2.10], SCN: 0x0000.00514d4c
Thread 1 advanced to log sequence 8
Wed Jul 22 10:12:31 2009
ARC2: Evaluating archive   log 4 thread 1 sequence 7
Wed Jul 22 10:12:32 2009
Committing creation of archivelog 'E:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\XHTEST\ARCHIVELOG\2009_07_22\O1_MF_1_7_56DXDJ2G_.ARC'
Created Oracle managed file E:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\XHTEST\ARCHIVELOG\2009_07_22\O1_MF_1_7_56DXDJ2G_.ARC
Wed Jul 22 10:12:32 2009
  Current log# 2 seq# 8 mem# 0: E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG
Wed Jul 22 10:13:39 2009
Beginning global checkpoint up to RBA [0x8.b16.10], SCN: 5328377
Completed checkpoint up to RBA [0x8.2.10], SCN: 5328204
Completed checkpoint up to RBA [0x8.b16.10], SCN: 5328377
System altered.

SQL> select group# ,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 INACTIVE
         2          1 CURRENT
         3          1 INACTIVE
         4          2 INACTIVE**************都写入了

SQL> host copy E:\REDO04_1.LOG d:\redo04_1.log
已复制         1 个文件。

SQL> host copy E:\REDO04_1.LOG d:\redo04_1.log
已复制         1 个文件。

SQL> alter database rename file 'E:\REDO04_1.LOG' to 'd:\redo04_1.log';

Database altered.

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ------------------------------
         3 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO03.LOG

         2 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO02.LOG

         1 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO01.LOG

         1 E:\REDO01_2.LOG
         4 D:\REDO04_1.LOG***************************

    GROUP# MEMBER
---------- ------------------------------
         4 E:\REDO04_3.LOG
         1 E:\REDO01_3.LOG

7 rows selected.

SQL> alter database drop logfile member 'D:\REDO04_1.LOG';
alter database drop logfile member 'D:\REDO04_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'D:\REDO04_1.LOG'


SQL> alter database drop logfile member 'E:\REDO04_3.LOG';
alter database drop logfile member 'E:\REDO04_3.LOG'
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error


SQL> alter database drop log group 4;
ERROR:
ORA-03114: not connected to ORACLE


SQL> alter database drop logfile group 4;
ERROR:
ORA-03114: not connected to ORACLE


SQL> conn / as sysdba
Connected.
SQL> alter database drop logfile member 'E:\REDO04_3.LOG';
alter database drop logfile member 'E:\REDO04_3.LOG'
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error


SQL> conn / as sysdba
Connected.
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [1], [], [], [], [], [], []
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~总结这样分盘存放 造成无法删除 member 无发删除group


SQL> alter database rename file 'D:\REDO04_1.LOG' to 'e:\redo04_1.log';

Database altered.

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ------------------------------
         3 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO03.LOG

         2 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO02.LOG

         1 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO01.LOG

         1 E:\REDO01_2.LOG
         4 E:\REDO04_1.LOG

    GROUP# MEMBER
---------- ------------------------------
         4 E:\REDO04_3.LOG
         1 E:\REDO01_3.LOG

7 rows selected.

SQL> select group# ,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 INACTIVE
         2          1 CURRENT
         3          1 INACTIVE
         4          2 INACTIVE

SQL> alter database drop logfile member 'E:\REDO04_1.LOG';
alter database drop logfile member 'E:\REDO04_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_1.LOG'


SQL> alter database drop logfile member 'E:\REDO04_4.LOG';
alter database drop logfile member 'E:\REDO04_4.LOG'
*
ERROR at line 1:
ORA-00360: not a logfile member: E:\REDO04_4.LOG


SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [1], [], [], [], [], [], []
移动回来也无法删除

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ------------------------------
         3 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO03.LOG

         2 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO02.LOG

         1 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO01.LOG

         1 E:\REDO01_2.LOG
         4 E:\REDO04_1.LOG

    GROUP# MEMBER
---------- ------------------------------
         4 E:\REDO04_3.LOG
         1 E:\REDO01_3.LOG

7 rows selected.

SQL> select group# ,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 INACTIVE
         2          1 CURRENT
         3          1 INACTIVE
         4          2 INACTIVE

SQL> alter database drop logfile member 'E:\REDO04_3.LOG';
alter database drop logfile member 'E:\REDO04_3.LOG'
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error


SQL> conn / as sysdba
Connected.
SQL> alter database drop logfile member 'E:\REDO04_1.LOG';
alter database drop logfile member 'E:\REDO04_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_1.LOG'


SQL> conn / as sysdba
Connected.
SQL> alter database drop logfile group 4
  2  ;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [1], [], [], [], [], [], []

切几次也不行

SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database drop logfile member 'E:\REDO04_1.LOG';
alter database drop logfile member 'E:\REDO04_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_1.LOG'


SQL> alter database drop logfile member 'E:\REDO04_1.LOG';
alter database drop logfile member 'E:\REDO04_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_1.LOG'


SQL> alter database drop logfile group 4
  2  ;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [1], [], [], [], [], [], []
清除也不行

 

SQL> startup force
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                   788068 bytes
Variable Size             145488284 bytes
Database Buffers           41943040 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.

 

SQL> select group# ,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 CURRENT
         2          1 INACTIVE
         3          1 INACTIVE
         4          2 INACTIVE

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [1], [], [], [], [], [], []


SQL>
重启也不行

SQL> alter database add logfile member 'E:\REDO04_4.LOG' TO GROUP 4;

Database altered.

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [2], [], [], [], [], [], []


SQL> alter database drop logfile member 'E:\REDO04_4.LOG';
alter database drop logfile member 'E:\REDO04_4.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_4.LOG'

添加一个再删也不行

 


SQL> col member format a30
SQL> select group# ,member from v$logFILE;

    GROUP# MEMBER
---------- ------------------------------
         3 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO03.LOG

         2 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO02.LOG

         1 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO01.LOG

         1 E:\REDO01_2.LOG
         4 E:\REDO04_1.LOG

    GROUP# MEMBER
---------- ------------------------------
         4 E:\REDO04_4.LOG
         4 E:\REDO04_3.LOG
         1 E:\REDO01_3.LOG

8 rows selected.

SQL> select group# ,members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 CURRENT
         2          1 INACTIVE
         3          1 INACTIVE
         4          3 INACTIVE

SQL> alter database drop logfile member 'E:\REDO04_4.LOG','E:\REDO04_3.LOG','E:\
REDO04_1.LOG';
alter database drop logfile member 'E:\REDO04_4.LOG','E:\REDO04_3.LOG','E:\REDO0
4_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_4.LOG'

!!!!!!!!!!!!!!!!!!!!!!!!最后 倒腾来倒腾去 REDO LOG 出问题了 group4  关闭归档依然有问题 group4 中
alter database open

Wed Jul 22 12:05:01 2009
Errors in file e:\oracle\product\10.1.0\admin\orcl\bdump\orcl_lgwr_9040.trc:
ORA-00314: log 4 of thread 1, expected sequence# 22 doesn't match 0
ORA-00312: online log 4 thread 1: 'D:\REDO04_1.LOG'

Wed Jul 22 12:05:01 2009
Errors in file e:\oracle\product\10.1.0\admin\orcl\bdump\orcl_lgwr_9040.trc:
ORA-00314: log 4 of thread 1, expected sequence# 22 doesn't match 0
ORA-00312: online log 4 thread 1: 'D:\REDO04_1.LOG'
LGWR 进程有问题
理论就算current logfile 损坏了,要恢复比较麻烦drop 不了

SQL> alter database clear logfile group 4;重建

Database altered.

SQL> alter database open;

Database altered.


SQL> col member format a30
SQL> select group#,member,status from v$logfile;

    GROUP# MEMBER                         STATUS
---------- ------------------------------ -------
         3 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO03.LOG

         2 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO02.LOG

         1 E:\ORACLE\PRODUCT\10.1.0\ORADA STALE
           TA\ORCL\REDO01.LOG

         1 E:\REDO01_2.LOG                STALE
         4 D:\REDO04_1.LOG

    GROUP# MEMBER                         STATUS
---------- ------------------------------ -------
         4 E:\REDO04_4.LOG
         4 E:\REDO04_3.LOG                INVALID
         1 E:\REDO01_3.LOG                STALE

8 rows selected.

SQL> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 INACTIVE
         2          1 INACTIVE
         3          1 INACTIVE
         4          3 CURRENT

SQL> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 INACTIVE
         2          1 INACTIVE
         3          1 CURRENT
         4          3 ACTIVE~~~~~~~~~切一下

SQL> alter system checkpoint;

System altered.

SQL> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 INACTIVE
         2          1 INACTIVE
         3          1 CURRENT
         4          3 INACTIVE

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [2], [], [], [], [], [], []
还是删除不了
SQL> select group#,member,status from v$logfile;

    GROUP# MEMBER                         STATUS
---------- ------------------------------ -------
         3 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO03.LOG

         2 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO02.LOG

         1 E:\ORACLE\PRODUCT\10.1.0\ORADA STALE
           TA\ORCL\REDO01.LOG

         1 E:\REDO01_2.LOG                STALE
         4 D:\REDO04_1.LOG

    GROUP# MEMBER                         STATUS
---------- ------------------------------ -------
         4 E:\REDO04_4.LOG
         4 E:\REDO04_3.LOG                INVALID
         1 E:\REDO01_3.LOG                STALE

8 rows selected.
SQL> alter system switch logfile;

System altered.

SQL> select group#,member,status from v$logfile;

    GROUP# MEMBER                         STATUS
---------- ------------------------------ -------
         3 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO03.LOG

         2 E:\ORACLE\PRODUCT\10.1.0\ORADA
           TA\ORCL\REDO02.LOG

         1 E:\ORACLE\PRODUCT\10.1.0\ORADA STALE
           TA\ORCL\REDO01.LOG

         1 E:\REDO01_2.LOG                STALE
         4 D:\REDO04_1.LOG

    GROUP# MEMBER                         STATUS
---------- ------------------------------ -------
         4 E:\REDO04_4.LOG
         4 E:\REDO04_3.LOG                INVALID
         1 E:\REDO01_3.LOG                STALE

8 rows selected.

SQL> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 INACTIVE
         2          1 CURRENT
         3          1 INACTIVE
         4          3 INACTIVE
~最后好象还是没有办法~~~~~~~~~~~~~~~

正确的 分盘存储logfile 方式 是如下的(在建立或ADD时候 分盘)
SQL> alter database add logfile group 5 ('e:\redo05_1.log','d:\redo05_2.log') si
ze 10m;

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          3 ACTIVE
         2          1 INACTIVE
         3          1 INACTIVE
         4          3 CURRENT
         5          2 INACTIVE

SQL> select group#,member,status from v$logfile where group#=5;

    GROUP# MEMBER                         STATUS
---------- ------------------------------ -------
         5 E:\REDO05_1.LOG~~~~~~~~~~~~~~~~~
         5 D:\REDO05_2.LOG~~~~~~~~~~~~~~~~~~~~~

SQL> alter database drop logfile member 'D:\REDO05_2.LOG';

Database altered.

 

SQL> alter database add  logfile member 'd:\redo05_2.log' to group 5;

Database altered.

SQL> select group#,member,status from v$logfile where group#=5;

    GROUP# MEMBER                         STATUS
---------- ------------------------------ -------
         5 E:\REDO05_1.LOG~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         5 D:\REDO05_2.LOG                INVALID~~~~~~~~~~~~~~


SQL> alter database drop logfile group 5;

Database altered.

SQL> select group#,member,status from v$logfile where group#=5;

no rows selected

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

0
0
上一篇： oracle实验记录 （audit）
下一篇： oracle实验记录 （bigfile tablespace）
请登录后发表评论 登录
全部评论
fufuh2o

注册时间：2009-06-26

  • 博文量
    182
  • 访问量
    471639

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593