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

首页 > 数据库 > Oracle > 一次关于redo file的操作和心得体会

一次关于redo file的操作和心得体会

原创 Oracle 作者：ludal 时间：2010-08-26 00:16:06 0 删除 编辑

环境：

AIX 6.1

ORACLE 11G 02

===========================================================

SQL> /

MEMBER
--------------------------------------------------
/oracle/oradata/FTDATA/onlinelog/o1_mf_3_676yzm37_
.log

/oracle/flash_recovery_area/FTDATA/onlinelog/o1_mf
_3_676yzs8n_.log

/oracle/oradata/FTDATA/onlinelog/o1_mf_2_676yz6to_
.log

/oracle/flash_recovery_area/FTDATA/onlinelog/o1_mf
_2_676yzdyh_.log

MEMBER
--------------------------------------------------

/oracle/oradata/FTDATA/onlinelog/o1_mf_1_676yytol_
.log

/oracle/flash_recovery_area/FTDATA/onlinelog/o1_mf
_1_676yz0rq_.log


6 rows selected.

Elapsed: 00:00:00.01
SQL> select group#,member from v$logfile;

GROUP# MEMBER
---------- --------------------------------------------------
3 /oracle/oradata/FTDATA/onlinelog/o1_mf_3_676yzm37_
.log

3 /oracle/flash_recovery_area/FTDATA/onlinelog/o1_mf
_3_676yzs8n_.log

2 /oracle/oradata/FTDATA/onlinelog/o1_mf_2_676yz6to_
.log

2 /oracle/flash_recovery_area/FTDATA/onlinelog/o1_mf
_2_676yzdyh_.log

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

1 /oracle/oradata/FTDATA/onlinelog/o1_mf_1_676yytol_
.log

1 /oracle/flash_recovery_area/FTDATA/onlinelog/o1_mf
_1_676yz0rq_.log


6 rows selected.

Elapsed: 00:00:00.01
SQL> alter database add logfile group 4 '/oracle/oradata/FTDATA/redo04.log' size 100m;

Database altered.

Elapsed: 00:00:13.81
SQL> alter database add logfile group 5 '/oracle/oradata/FTDATA/redo05.log' size 100m;

Database altered.

Elapsed: 00:00:13.71
SQL> alter system switch log;
alter system switch log
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected


Elapsed: 00:00:00.05
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.44
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 1 thread 1: '/oracle/oradata/FTDATA/onlinelog/o1_mf_1_676yytol_.log'
ORA-00312: online log 1 thread 1:
'/oracle/flash_recovery_area/FTDATA/onlinelog/o1_mf_1_676yz0rq_.log'


Elapsed: 00:00:00.21
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 1 thread 1: '/oracle/oradata/FTDATA/onlinelog/o1_mf_1_676yytol_.log'
ORA-00312: online log 1 thread 1:
'/oracle/flash_recovery_area/FTDATA/onlinelog/o1_mf_1_676yz0rq_.log'


Elapsed: 00:00:00.01
SQL> ORA-01624
SP2-0042: unknown command "ORA-01624" - rest of line ignored.
SQL> alter system archive log current;

alter system archive log current
*
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log


Elapsed: 00:00:00.04
SQL> SQL>
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log


Elapsed: 00:00:00.01
SQL>
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log


Elapsed: 00:00:00.01
SQL>
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log


Elapsed: 00:00:00.00
SQL>
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 5
SQL> alter system switch log;
alter system switch log
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected


Elapsed: 00:00:00.01
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.15
SQL> alter database drop logfile group 1;

Database altered.

Elapsed: 00:00:00.24
SQL> alter database drop logfile group 2;

Database altered.

Elapsed: 00:00:00.21
SQL> alter database drop logfile group 3;

Database altered.

Elapsed: 00:00:00.21
SQL> alter database add logfile group 1 '^C/oracle/oradata/FTDATA/redo01,/oracle/oradata/FTDATA/redo02' size 100m;

SQL> alter database add logfile group 1'/oracle/oradata/FTDATA/redo01.log,/oracle/oradata/FTDATA/redo02.log' size 100m;
alter database add logfile group 1'/oracle/oradata/FTDATA/redo01.log,/oracle/oradata/FTDATA/redo02.log' size 100m
*
ERROR at line 1:
ORA-00301: error in adding log file
'/oracle/oradata/FTDATA/redo01.log,/oracle/oradata/FTDATA/redo02.log' - file cannot be created
ORA-27040: file create error, unable to create file
IBM AIX RISC System/6000 Error: 2: No such file or directory


Elapsed: 00:00:00.12
SQL> alter database add logfile group 1 '/oracle/oradata/FTDATA/redo01' size 100m;

Database altered.

Elapsed: 00:00:13.32
SQL> alter database add logfile group 2 '/oracle/oradata/FTDATA/redo02' size 100m;

Database altered.

Elapsed: 00:00:13.60
SQL> alter database add logfile group 3 '/oracle/oradata/FTDATA/redo03' size 100m;

Database altered.

Elapsed: 00:00:13.72
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.18
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.14
SQL> alter database drop logfile group 4;

Database altered.

Elapsed: 00:00:00.16
SQL> alter database drop logfile group 5;
alter database drop logfile group 5
*
ERROR at line 1:
ORA-01624: log 5 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 5 thread 1: '/oracle/oradata/FTDATA/redo05.log'


Elapsed: 00:00:00.02
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.13
SQL> alter database drop logfile group 5;
alter database drop logfile group 5
*
ERROR at line 1:
ORA-01624: log 5 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 5 thread 1: '/oracle/oradata/FTDATA/redo05.log'


Elapsed: 00:00:00.01
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:03.08
SQL> alter database drop logfile group 5;
alter database drop logfile group 5
*
ERROR at line 1:
ORA-01623: log 5 is current log for instance FTDATA (thread 1) - cannot drop
ORA-00312: online log 5 thread 1: '/oracle/oradata/FTDATA/redo05.log'


Elapsed: 00:00:00.01
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log


Elapsed: 00:00:00.02
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:02.92
SQL> desc v$logfile;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(257)
IS_RECOVERY_DEST_FILE VARCHAR2(3)

SQL> select status,member from v$logfile;

STATUS MEMBER
-------------- --------------------------------------------------
/oracle/oradata/FTDATA/redo01
/oracle/oradata/FTDATA/redo02
/oracle/oradata/FTDATA/redo03
/oracle/oradata/FTDATA/redo05.log

Elapsed: 00:00:00.01
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance FTDATA (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/oracle/oradata/FTDATA/redo01'


Elapsed: 00:00:00.02
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.15
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_REC
---------- -------------- -------------- -------------------------------------------------- ------
1 ONLINE /oracle/oradata/FTDATA/redo01 NO
2 ONLINE /oracle/oradata/FTDATA/redo02 NO
3 ONLINE /oracle/oradata/FTDATA/redo03 NO
5 ONLINE /oracle/oradata/FTDATA/redo05.log NO

Elapsed: 00:00:00.01
SQL> desc $log;
ERROR:
ORA-00911: invalid character
ORA-00911: invalid character


SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.98
SQL> alter database drop logfile group 5;
alter database drop logfile group 5
*
ERROR at line 1:
ORA-01624: log 5 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 5 thread 1: '/oracle/oradata/FTDATA/redo05.log'


Elapsed: 00:00:00.01
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:02.60
SQL> alter database drop logfile group 5;
alter database drop logfile group 5
*
ERROR at line 1:
ORA-01623: log 5 is current log for instance FTDATA (thread 1) - cannot drop
ORA-00312: online log 5 thread 1: '/oracle/oradata/FTDATA/redo05.log'


Elapsed: 00:00:00.00
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:01.09
SQL> alter database drop logfile group 5;
alter database drop logfile group 5
*
ERROR at line 1:
ORA-01624: log 5 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 5 thread 1: '/oracle/oradata/FTDATA/redo05.log'


Elapsed: 00:00:00.01
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:03.30
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:02.24
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:01.17
SQL> alter database drop logfile group1;
alter database drop logfile group1
*
ERROR at line 1:
ORA-02236: invalid file name


Elapsed: 00:00:00.01
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 1 thread 1: '/oracle/oradata/FTDATA/redo01'


Elapsed: 00:00:00.01
SQL> alter database clear unarchive logfile group 1;
alter database clear unarchive logfile group 1
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected


Elapsed: 00:00:00.01
SQL> alter database clear unarchive logfile group 5;
alter database clear unarchive logfile group 5
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected


Elapsed: 00:00:00.00
SQL> alter database clear logfile group 5;
alter database clear logfile group 5
*
ERROR at line 1:
ORA-01624: log 5 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 5 thread 1: '/oracle/oradata/FTDATA/redo05.log'


Elapsed: 00:00:00.11
SQL> alter databse clear logfile group 1;
alter databse clear logfile group 1
*
ERROR at line 1:
ORA-00940: invalid ALTER command


Elapsed: 00:00:00.01
SQL> alter database clear logfile group 1;

Database altered.

Elapsed: 00:00:12.80
SQL> alter database clear logfile group 2;

Database altered.

Elapsed: 00:00:12.83
SQL> alter database clear logfile group 3;

Database altered.

Elapsed: 00:00:12.71
SQL> alter database drop logfile group 1;

Database altered.

Elapsed: 00:00:00.14
SQL> alter database drop logfile group 2;

Database altered.

Elapsed: 00:00:00.13
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01567: dropping log 3 would leave less than 2 log files for instance FTDATA (thread 1)
ORA-00312: online log 3 thread 1: '/oracle/oradata/FTDATA/redo03'


Elapsed: 00:00:00.03
SQL> alter database add logfile group 1 '/oracle/oradata/FTDATA/redo01.log' size 100m;

Database altered.

Elapsed: 00:00:11.98
SQL> alter database add logfile group 2 '/oracle/oradata/FTDATA/redo02.log' size 100m;

Database altered.

Elapsed: 00:00:12.04
SQL> alter database drop logfile group 3;

Database altered.

Elapsed: 00:00:00.14
SQL> alter database add logfile group 3 '/oracle/oradata/FTDATA/redo03.log' size 100m;

Database altered.

Elapsed: 00:00:12.01
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.16
SQL> alter system clear logfile group 5;
alter system clear logfile group 5
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


Elapsed: 00:00:00.01
SQL> alter database clear logfile group 5;
alter database clear logfile group 5
*
ERROR at line 1:
ORA-01624: log 5 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 5 thread 1: '/oracle/oradata/FTDATA/redo05.log'


Elapsed: 00:00:00.08
SQL> alter database clear logfile group 5;
alter database clear logfile group 5
*
ERROR at line 1:
ORA-01624: log 5 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 5 thread 1: '/oracle/oradata/FTDATA/redo05.log'


Elapsed: 00:00:00.05
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.13
SQL> alter database clear logfile group 5;
alter database clear logfile group 5
*
ERROR at line 1:
ORA-01624: log 5 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 5 thread 1: '/oracle/oradata/FTDATA/redo05.log'


Elapsed: 00:00:00.06
SQL> alter database drop logfile group 5;
alter database drop logfile group 5
*
ERROR at line 1:
ORA-01624: log 5 needed for crash recovery of instance FTDATA (thread 1)
ORA-00312: online log 5 thread 1: '/oracle/oradata/FTDATA/redo05.log'


Elapsed: 00:00:00.01
SQL> alter system checkpoint;

System altered.

Elapsed: 00:00:00.20
SQL> alter datbase drop logfile group 5;
alter datbase drop logfile group 5
*
ERROR at line 1:
ORA-00940: invalid ALTER command


Elapsed: 00:00:00.01
SQL> alter database drop logfile group 5;

Database altered.

Elapsed: 00:00:00.13


心得：
alter system switch logfile；执行一次，就只切换一个日志租，比如当前是group1，那么执行一次，就切换到group2
alter system archive log current；必须置数据库于归档模式下

ative状态的redolog 是可能把数据写进磁盘了，也可能没写进，所以这时候要删除这个组的重做联机日志时候就最好执行一下如下的命令：
alter system switch logfile；
alter system checkpoint；

[@more@]ci

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

0
0
上一篇： oracle调优之rac调优概念
下一篇： 关于oracle里的v$sga
请登录后发表评论 登录
全部评论
ludal

注册时间：2010-12-14

  • 博文量
    44
  • 访问量
    427861

最新文章

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

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

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

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