(转载)
SQL> select lf.GROUP#,lf.MEMBER,lg.STATUS from v$logfile lf,v$log lg
2 where lf.GROUP#=lg.GROUP#;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG INACTIVE
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG INACTIVE
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG CURRENT
SQL> alter database add logfile group 8 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\R
EDO08.LOG' size 100m;
数据库已更改。
SQL> select lf.GROUP#,lf.MEMBER,lg.STATUS from v$logfile lf,v$log lg
2 where lf.GROUP#=lg.GROUP#;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG INACTIVE
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG INACTIVE
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG CURRENT
8 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO08.LOG UNUSED
SQL> alter system switch logfile;
系统已更改。
SQL> select lf.GROUP#,lf.MEMBER,lg.STATUS from v$logfile lf,v$log lg
2 where lf.GROUP#=lg.GROUP#;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG INACTIVE
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG INACTIVE
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG ACTIVE
8 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO08.LOG CURRENT
SQL> alter database drop logfile group 1;#只有inactive和unused状态的日志才能删除且须手工删除OS上日志文件
数据库已更改。
SQL> alter database drop logfile group 2;
数据库已更改。
SQL> alter database drop logfile group 3;#这里报错数据库必须保留2个重做日志文件
alter database drop logfile group 3
*
第 1 行出现错误:
ORA-01567: 删除日志 3 时将为实例 orcl (线程 1) 保留两个以下的日志文件
ORA-00312: 联机日志 3 线程 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'
SQL> alter database add logfile group 9 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\R
EDO09.LOG' size 100m;
数据库已更改。
SQL> alter database drop logfile group 3;
数据库已更改。
SQL> select lf.GROUP#,lf.MEMBER,lg.STATUS from v$logfile lf,v$log lg
2 where lf.GROUP#=lg.GROUP#;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
9 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO09.LOG UNUSED
8 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO08.LOG CURRENT
SQL> alter database add logfile group 1 'D:\oracle\product\10.2.0\oradata\orcl\redo01.log' size 100M;
数据库已更改。
SQL> alter database add logfile group 2 'D:\oracle\product\10.2.0\oradata\orcl\redo02.log' size 100M;
数据库已更改。
SQL> alter database add logfile group 3 'D:\oracle\product\10.2.0\oradata\orcl\redo03.log' size 100M;
数据库已更改。
SQL> select lf.GROUP#,lf.MEMBER,lg.STATUS from v$logfile lf,v$log lg
2 where lf.GROUP#=lg.GROUP#;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
9 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO09.LOG UNUSED
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG UNUSED
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG UNUSED
8 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO08.LOG CURRENT
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG UNUSED
SQL> alter system switch logfile;
系统已更改。
SQL> select lf.GROUP#,lf.MEMBER,lg.STATUS from v$logfile lf,v$log lg
2 where lf.GROUP#=lg.GROUP#;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
9 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO09.LOG UNUSED
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG CURRENT
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG UNUSED
8 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO08.LOG ACTIVE
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG UNUSED
SQL> alter database drop logfile group 9;#这里先删除group9是因为group8为active,只有inactive和unused状态的才能删
数据库已更改。
SQL> select lf.GROUP#,lf.MEMBER,lg.STATUS from v$logfile lf,v$log lg
2 where lf.GROUP#=lg.GROUP#;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG ACTIVE
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG ACTIVE
8 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO08.LOG INACTIVE
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG CURRENT
SQL> alter database drop logfile group 8;
数据库已更改。
SQL> select lf.GROUP#,lf.MEMBER,lg.STATUS from v$logfile lf,v$log lg
2 where lf.GROUP#=lg.GROUP#;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG ACTIVE
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG ACTIVE
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG CURRENT
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15142212/viewspace-605427/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15142212/viewspace-605427/