1Z0-031.7 - 2. 重做日志文件的相关操作（日志组和日志成员的增加与删除，日志成员的重命名，clear日志组，移到日志组）...-CSDN博客

共同说明：

[] : 表示是可选的

| : 表示或者

[@more@]

1.ADD LOG GROUP:

只增加一个日志成员的写法，用此种方法以后一定要再至少创建一个日志成员：

ALTER DATABASE [database] ADD LOGFILE GROUP integer filespec SIZE size [REUSE] [,GROUP integer filespec SIZE size [REUSE] ...] ;

同时增加多个日志成员的写法（注意多了个小括号）：

ALTER DATABASE [database] ADD LOGFILE GROUP integer ( filespec , filespec , filespec ...] ) SIZE size [REUSE];

说明：

--- filespec ：包括地址的全文件名

--- 注意可以同时创建多个文件

--- REUSE: 表示如果已经存在此文件就重用它！

eg:

alter database add logfile group 5 '/opt/oracle/oradata/dbtest/redo05_1.log' SIZE 10M;

alter database add logfile group 5 '/opt/oracle/oradata/dbtest/redo05_1.log' size 10m reuse;

alter database add logfile group 6 '/opt/oracle/oradata/dbtest/redo06_1.log' size 10m,

group 7 '/opt/oracle/oradata/dbtest/redo07_1.log' size 10m;

alter database add logfile group 8

('/opt/oracle/oradata/dbtest/redo08_1.log','/opt/oracle/oradata/dbtest/redo08_2.log') size 10m;

alter database add logfile group 5

('/opt/oracle/oradata/dbtest/redo05_1.log','/opt/oracle/oradata/dbtest/redo05_2.log') size 10m reuse,

group 6 '/opt/oracle/oradata/dbtest/redo06_1.log' size 10m;

2.ADD LOG MEMBER:

ALTER DATABASE [database] ADD LOGFILE MEMBER 'filename' [REUSE] [, 'filename' [REUSE]...] TO GROUP integer | ( all_file_in_the_group_spec );

说明：

--- filespec ：包括地址的全文件名

--- REUSE: 表示如果已经存在此文件就重用它！

eg:

alter database add logfile member '/opt/oracle/oradata/dbtest/redo04_3.log' to group 4;

alter database add logfile member

'/opt/oracle/oradata/dbtest/redo02_3.log','/opt/oracle/oradata/dbtest/redo02_4.log' to group 2;

alter database add logfile member '/opt/oracle/oradata/dbtest/redo04_4.log'

to ('/opt/oracle/oradata/dbtest/redo04_1.log','/opt/oracle/oradata/dbtest/redo04_2.log','/opt/oracle/oradata/ dbtest/redo04_3.log');

3.DROP LOG GROUP:

ALTER DATABASE [database] DROP LOGFILE GROUP integer | ( all_file_in_the_group_spec ) [,GROUP integer | ( all_file_in_the_group_spec ) ... ];

说明：

--- An instance requires at least two groups of online redo log files.（每个组至少要有两个成员）

--- An active or current group cannot be dropped.（一个处于active或current状态的组不能被删除）

--- When an online redo log group is dropped, the operating system files are not deleted. （所用的删除操作是指删除数据库中的信息，在操作系统中不删除。）

--- all_file_in_the_group_spec ：此日志组包括的所有的成员的全文件名

--- 当删除用的是drop logfile group 5的时候，则group 5中的所有成员也同样在数据库中被删除

eg:

alter database drop logfile group 5;

alter database drop logfile

('/opt/oracle/oradata/dbtest/redo05_1.log','/opt/oracle/oradata/dbtest/redo05_2.log')

alter database drop logfile group 6 ,

('/opt/oracle/oradata/dbtest/redo05_1.log','/opt/oracle/oradata/dbtest/redo05_2.log');

4.DROP LOG MEMBER

ALTER DATABASE [database] DROP LOGFILE MEMBER 'filename'[, 'filename' ...];

说明：

--- If the member you want to drop is the last valid member of the group, you cannot drop that member.（组的最后一个成员不能删除）

--- If the group is current, you must force a log file switch before you can drop the member.（正在使用的组不能删除）

--- If the database is running in ARCHIVELOG mode and the log file group to which the member belongs is not archived, then the member cannot be dropped.（在归档模式下，必须归档后才能删除）

--- When an online redo log member is dropped, the operating system file is not deleted.

eg:

alter database drop logfile member '/opt/oracle/oradata/dbtest/redo04_4.log';

alter database dbtest drop logfile member

'/opt/oracle/oradata/dbtest/redo02_3.log','/opt/oracle/oradata/dbtest/redo02_4.log';
5. RENAME LOG FILE

--- MUST BE IN THE MOUNT MODE 必须在mount模式下

--- 如果要将A文件重命名为B文件，则必须在操作系统下复制A一份命名为B文件，再在数据库中更改！

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

SQL 10G>

SQL 10G>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL 10G>startup mount

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 1220336 bytes

Variable Size 155189520 bytes

Database Buffers 360710144 bytes

Redo Buffers 7168000 bytes

Database mounted.

SQL 10G>!

[oracle@csdba ~]$ cd /opt/oracle/oradata/dbtest/

[oracle@csdba dbtest]$ ls *.log

redo01_1.log redo01_2.log redo02_1.log redo02_2.log redo03_1.log redo03_2.log redo04_1.log redo04_2.log

[oracle@csdba dbtest]$ cp redo04_2.log redo04_2_b.log

[oracle@csdba dbtest]$ ls *.log

redo01_1.log redo01_2.log redo02_1.log redo02_2.log redo03_1.log redo03_2.log redo04_1.log redo04_2_b.log

redo04_2.log

[oracle@csdba dbtest]$ exit

exit

SQL 10G>alter database rename file redo04_2.log to redo04_2_b.log;

alter database rename file redo04_2.log to redo04_2_b.log
*

ERROR at line 1:

ORA-02236: invalid file name

SQL 10G>alter database rename file '/opt/oracle/oradata/dbtest/redo04_2.log' to

'/opt/oracle/oradata/dbtest/redo04_2_b.log';

Database altered.

SQL 10G>alter database rename file '/opt/oracle/oradata/dbtest/redo04_2_b.log' to

'/opt/oracle/oradata/dbtest/redo04_2.log';

Database altered.

SQL 10G>alter database open;

Database altered.

SQL 10G>

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

6. CLEAR LOG FILE

当日志文件（组）的所有成员被破坏以后（丢失，错误信息等），DBA可以clear这个日志文件组。clear文件相当于先删除再重建一个新的一模一样的日志文件组一样，只是该方法当只有两个日志组其中一个出问题的情况，甚至这个出问题的log file没有归档（在归档模式下）。

Syntax：

ALTER DATABASE [database] CLEAR [UNARCHIVED] LOGFILE {GROUP integer | (‘filename’ [,’filename’,...])} [,{GROUP integer | (‘filename’ [,’filename’,...])},...]...;

7.Relocating Log File:

relocating = create a new same log file + remove the old log file

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

转载于:http://blog.itpub.net/8633028/viewspace-914541/