重庆思庄Oracle、Redhat认证学习论坛

标题: 更改重做日志文件的大小 [打印本页]

作者: jiawang 时间: 2019-3-14 13:46
标题: 更改重做日志文件的大小
--查看现有日志组及其文件大小
SQL> set lines 200
SQL> col member for a120
SQL> select group#, bytes/1024/1024, status from v$log;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
   1          50 CURRENT
   2          50 INACTIVE
   3          50 INACTIVE

SQL> select GROUP#,MEMBER from v$logfile order by 1,2;

GROUP# MEMBER
---------- ------------------------------------------------------------------------------------------------------------------------
   1 /u01/app/oracle/oradata/orcl/redo01.log
   2 /u01/app/oracle/oradata/orcl/redo02.log
   3 /u01/app/oracle/oradata/orcl/redo03.log

--创建3个磁盘组，每个磁盘组大小200m
SQL> alter database add logfile group 4  '/u01/app/oracle/oradata/orcl/redo04.log ' size 200M;
Database altered.

SQL> alter database add logfile group 5  '/u01/app/oracle/oradata/orcl/redo05.log ' size 200M;
Database altered.

SQL> alter database add logfile group 6  '/u01/app/oracle/oradata/orcl/redo06.log ' size 200M;
Database altered.

SQL> select group#, bytes/1024/1024, status from v$log;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
   1          50 CURRENT
   2          50 INACTIVE
   3          50 INACTIVE
   4          200 UNUSED
   5          200 UNUSED
   6          200 UNUSED

6 rows selected.

--切换当前日志组到新的日志组，保证日志组1,2,3 不处于CURRENT 状态
SQL> alter system switch logfile;
System altered.

SQL> alter system checkpoint;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

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

GROUP# STATUS
---------- ----------------
   1 INACTIVE
   2 INACTIVE
   3 INACTIVE
   4 CURRENT
   5 INACTIVE
   6 INACTIVE

6 rows selected.

SQL> select group#, bytes/1024/1024, status from v$log;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
   1          50 INACTIVE
   2          50 INACTIVE
   3          50 INACTIVE
   4          200 CURRENT
   5          200 INACTIVE
   6          200 INACTIVE

6 rows selected.

--删除日志组
SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL> alter database drop logfile group 3;
Database altered.

SQL> select group#, bytes/1024/1024, status from v$log;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
   4          200 CURRENT
   5          200 INACTIVE
   6          200 INACTIVE

SQL> alter database add logfile group 1  '/u01/app/oracle/oradata/orcl/redo01.log ' size 200M;
Database altered.

SQL> alter database add logfile group 2  '/u01/app/oracle/oradata/orcl/redo02.log ' size 200M;
Database altered.

SQL> alter database add logfile group 3  '/u01/app/oracle/oradata/orcl/redo03.log ' size 200M;
Database altered.

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

GROUP# MEMBER
---------- ------------------------------------------------------------------------------------------------------------------------
   1 /u01/app/oracle/oradata/orcl/redo01.log
   2 /u01/app/oracle/oradata/orcl/redo02.log
   3 /u01/app/oracle/oradata/orcl/redo03.log
   4 /u01/app/oracle/oradata/orcl/redo04.log
   5 /u01/app/oracle/oradata/orcl/redo05.log
   6 /u01/app/oracle/oradata/orcl/redo06.log

6 rows selected.

SQL> select group#, bytes/1024/1024, status from v$log;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
   1          200 UNUSED
   2          200 UNUSED
   3          200 UNUSED
   4          200 CURRENT
   5          200 INACTIVE
   6          200 INACTIVE

6 rows selected.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)