如何設定redo log的OMF 及如何修改log檔案大小
知識點：
1。可以透過設定db_create_online_log_dest_n引數設定重作日誌的OMF
2。設定日誌檔案大小。
1。可以透過設定db_create_online_log_dest_n引數設定重作日誌的OMF
2。設定日誌檔案大小。
引用別人的一句話：
$log中的STATUS列,CURRENT表示當前使用的自然是不可以drop的,ACTIVE的表示非當前但是還是處於活動狀態,日誌中指向的快取中的髒資料塊還沒有完全被刷到磁碟上,所以也是不可以drop的,INACTIVE表示不活動的,相應資料塊都寫入磁碟,這種狀態如果需要歸檔並且已經歸檔,檔案就可以drop了.最後新增上去的日誌組,在一次還沒使用前處於的是一個UNUSED狀態ACTIVE和INACTIVE狀態都可能未歸檔,是否歸檔可以看ARCHIVED列
$log中的STATUS列,CURRENT表示當前使用的自然是不可以drop的,ACTIVE的表示非當前但是還是處於活動狀態,日誌中指向的快取中的髒資料塊還沒有完全被刷到磁碟上,所以也是不可以drop的,INACTIVE表示不活動的,相應資料塊都寫入磁碟,這種狀態如果需要歸檔並且已經歸檔,檔案就可以drop了.最後新增上去的日誌組,在一次還沒使用前處於的是一個UNUSED狀態ACTIVE和INACTIVE狀態都可能未歸檔,是否歸檔可以看ARCHIVED列
SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 12月 9 00:24:48 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn /as sysdba
已連線。
SQL> show parameter log
已連線。
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
......
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
......
SQL> alter system set db_create_online_log_dest_1='C:\oracle\oradata\heer';
系統已更改。
SQL> c /1/2
1* alter system set db_create_online_log_dest_2='C:\oracle\oradata\heer'
SQL> run
1* alter system set db_create_online_log_dest_2='C:\oracle\oradata\heer'
1* alter system set db_create_online_log_dest_2='C:\oracle\oradata\heer'
SQL> run
1* alter system set db_create_online_log_dest_2='C:\oracle\oradata\heer'
系統已更改。
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string C:\oracle\oradata\heer
db_create_online_log_dest_2 string C:\oracle\oradata\heer
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
。。。。。。
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string C:\oracle\oradata\heer
db_create_online_log_dest_2 string C:\oracle\oradata\heer
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
。。。。。。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 NO CURRENT 3641501 2005-12-08 21:54:48
2 1 7 104857600 1 YES INACTIVE 3583971 2005-12-07 22:29:25
3 1 6 104857600 1 YES INACTIVE 3524708 2005-12-06 22:18:19
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 NO CURRENT 3641501 2005-12-08 21:54:48
2 1 7 104857600 1 YES INACTIVE 3583971 2005-12-07 22:29:25
3 1 6 104857600 1 YES INACTIVE 3524708 2005-12-06 22:18:19
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
3 STALE ONLINE C:\ORACLE\ORADATA\HEER\REDO03.LOG
2 STALE ONLINE C:\ORACLE\ORADATA\HEER\REDO02.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\REDO01.LOG
---------- ------- ------- ----------------------------------------------------------------------------------------------------
3 STALE ONLINE C:\ORACLE\ORADATA\HEER\REDO03.LOG
2 STALE ONLINE C:\ORACLE\ORADATA\HEER\REDO02.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\REDO01.LOG
SQL> alter database drop logfile group 3;
資料庫已更改。
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
2 STALE ONLINE C:\ORACLE\ORADATA\HEER\REDO02.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\REDO01.LOG
---------- ------- ------- ----------------------------------------------------------------------------------------------------
2 STALE ONLINE C:\ORACLE\ORADATA\HEER\REDO02.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\REDO01.LOG
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 NO CURRENT 3641501 2005-12-08 21:54:48
2 1 7 104857600 1 YES INACTIVE 3583971 2005-12-07 22:29:25
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 NO CURRENT 3641501 2005-12-08 21:54:48
2 1 7 104857600 1 YES INACTIVE 3583971 2005-12-07 22:29:25
SQL> alter database add logfile group 3 size 10M;
資料庫已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 NO CURRENT 3641501 2005-12-08 21:54:48
2 1 7 104857600 1 YES INACTIVE 3583971 2005-12-07 22:29:25
3 1 0 10485760 2 YES UNUSED 0
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 NO CURRENT 3641501 2005-12-08 21:54:48
2 1 7 104857600 1 YES INACTIVE 3583971 2005-12-07 22:29:25
3 1 0 10485760 2 YES UNUSED 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY100_.LOG
2 STALE ONLINE C:\ORACLE\ORADATA\HEER\REDO02.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\REDO01.LOG
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY101_.LOG
---------- ------- ------- ----------------------------------------------------------------------------------------------------
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY100_.LOG
2 STALE ONLINE C:\ORACLE\ORADATA\HEER\REDO02.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\REDO01.LOG
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY101_.LOG
SQL> alter database drop logfile group 2;
資料庫已更改。
SQL> alter database add logfile group 2 size 10m;
資料庫已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 NO CURRENT 3641501 2005-12-08 21:54:48
2 1 0 10485760 2 YES UNUSED 0
3 1 0 10485760 2 YES UNUSED 0
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 NO CURRENT 3641501 2005-12-08 21:54:48
2 1 0 10485760 2 YES UNUSED 0
3 1 0 10485760 2 YES UNUSED 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY100_.LOG
2 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0100_.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\REDO01.LOG
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY101_.LOG
2 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0101_.LOG
---------- ------- ------- ----------------------------------------------------------------------------------------------------
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY100_.LOG
2 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0100_.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\REDO01.LOG
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY101_.LOG
2 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0101_.LOG
SQL> alter system switch logfile;
系統已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES ACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES ACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
SQL> /
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES ACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES ACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
......
這個地方查了幾遍都沒有完成checkpoint，索性顯示的再checkpoint一次，ok了。
這個地方查了幾遍都沒有完成checkpoint，索性顯示的再checkpoint一次，ok了。
SQL> alter system checkpoint;
系統已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES INACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES INACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
SQL>
SQL> select * from v$log;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES INACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 8 104857600 1 YES INACTIVE 3641501 2005-12-08 21:54:48
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
SQL> alter database drop logfile group 1;
資料庫已更改。
SQL> alter database add logfile group 1 size 10m;
資料庫已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 0 10485760 2 YES UNUSED 0
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 0 10485760 2 YES UNUSED 0
2 1 9 10485760 2 NO CURRENT 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY100_.LOG
2 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0100_.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_1_1SJS1S00_.LOG
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY101_.LOG
2 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0101_.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_1_1SJS1S01_.LOG
---------- ------- ------- ----------------------------------------------------------------------------------------------------
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY100_.LOG
2 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0100_.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_1_1SJS1S00_.LOG
3 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY101_.LOG
2 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0101_.LOG
1 ONLINE C:\ORACLE\ORADATA\HEER\O1_MF_1_1SJS1S01_.LOG
已選擇6行。
SQL> alter system switch logfile;
系統已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 10 10485760 2 NO CURRENT 3680899 2005-12-09 00:50:30
2 1 9 10485760 2 YES ACTIVE 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 10 10485760 2 NO CURRENT 3680899 2005-12-09 00:50:30
2 1 9 10485760 2 YES ACTIVE 3676851 2005-12-09 00:32:29
3 1 0 10485760 2 YES UNUSED 0
SQL> alter system switch logfile;
系統已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 10 10485760 2 YES ACTIVE 3680899 2005-12-09 00:50:30
2 1 9 10485760 2 YES INACTIVE 3676851 2005-12-09 00:32:29
3 1 11 10485760 2 NO CURRENT 3681024 2005-12-09 00:50:59
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 10 10485760 2 YES ACTIVE 3680899 2005-12-09 00:50:30
2 1 9 10485760 2 YES INACTIVE 3676851 2005-12-09 00:32:29
3 1 11 10485760 2 NO CURRENT 3681024 2005-12-09 00:50:59
參考文章：
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-627280/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 線上修改redo.log檔案的大小
- Redo Log Buffer的大小設定
- 增大redo log檔案大小
- 修改oracle redo log的大小Oracle Redo
- archive log檔案大小與redo log檔案大小關係探究Hive
- 線上修改REDO LOG的大小及增加新的日誌組
- MySQL如何計算統計redo log大小MySql
- 4.2.1.6 選擇資料庫並設定 Redo Log 塊的大小資料庫
- Dataguard環境修改主庫和standby庫online redo log&standby redo log大小
- oracle 線上修改online redo logfiles size 大小Oracle
- redo的等待log file sync和log file parallel write和redo size設定Parallel
- oracle檔案管理之 redo logOracle
- 修改online redo日誌檔案大小
- 如何設定weblogic的nodemanger配置Web
- mongodb修改oplog大小MongoDB
- Oracle調整redo log日誌大小Oracle
- ZBlogPHP如何線上管理檔案？PHP
- goldengate 捕捉oracle archive redo log 生成自有格式的trail檔案的大小記錄GoOracleHiveAI
- online redo log 操作 -- 更改RAC資料庫日誌檔案大小資料庫
- redo log檔案丟失處理措施
- Redo log檔案被刪除恢復
- MYSQL 是如何保證binlog 和redo log同時提交的？MySql
- 檢視Sql Server的log檔案大小SQLServer
- 如何修改PPT母版的公司名稱及logoGo
- 若依如何修改logoGo
- kingsql 如何修改Oralce日誌檔案大小SQL
- Standby Redo Log 的設定原則、建立、刪除、檢視、歸檔位置
- Redo Log之一：理解Oracle redo logOracle Redo
- 【操作】調整Online Redo Logs大小(Resizing Oracle Online Redo Logs)Oracle
- LoadRunner如何在指令碼執行時修改log設定選項？指令碼
- undo log和redo log
- MySQL的Redo log 以及Bin logMySql
- [筆記]RAC上改變redo logfile大小筆記
- 如何在本地修改Hosts檔案設定域名訪問？
- 【REDO】重做日誌檔案（redo log files）管理（增，刪，改，查，切）
- PostgreSQL如何刪除不使用的xlog檔案SQL
- MySQL中的redo log和undo logMySql
- logminer工具對redo log或archive log的挖掘Hive