oracle基礎練習5章 重做日誌
/* 2008/06/12 星期四
＊蒙昭良
*環境：linux5 + Oracle10gR2
*oracle基礎練習5章
＊重做日誌
*/
一、LGWR程式什麼時候把redo log buffer中的資料寫入redofile?
1、當commit時
2、當重做日誌緩衝區的資料改變記錄超過1M時
3、當重做日誌緩衝區的資料容量達到總容量的1／3時
4、在DBWn程式把DataBase buffer Cache的資料寫入資料檔案之前
5、每3秒
二、DBWn程式什麼時候把DataBase Buffer Cache的 資料寫入資料檔案？
1、當資料庫緩衝區的數量超過所設定的限額
2、當時間到了所設定的時間間隔
3、當程式需要資料庫高速緩衝區而找不到剩餘空間時
4、當發生檢查點時
5、當表被drop或者truncate時
6、當表空間設定為read only 時
7、當表空間進行類似的聯機備份時，alter tablespace users Begin Backup
8、當臨時表空間設定成離線offline或者正常normal狀態時
SQL> alter system switch logfile;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> show parameter fast_start_mttr_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 0
SQL> alter system set fast_start_mttr_target=900;
System altered.
SQL> show parameter fast_start_mttr_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 900
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 67 1 52428800 INACTIVE YES
2 68 1 52428800 INACTIVE YES
3 69 1 52428800 CURRENT NO
SQL> desc v$log;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
IS_
---
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log
NO
SQL> alter database add logfile
2 ('/u01/disk1/redo04a.log',
3 '/u01/disk2/redo04b.log')
4 size 15M;
Database altered.
SQL> select group#,sequence#,members,bytes/1024/1024 "MB",status,archived
2 from v$log;
GROUP# SEQUENCE# MEMBERS MB STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 67 1 50 INACTIVE YES
2 68 1 50 INACTIVE YES
3 69 1 50 CURRENT NO
4 0 2 15 UNUSED YES
SQL> alter database drop logfile group 4;
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 67 1 52428800 INACTIVE YES
2 68 1 52428800 INACTIVE YES
3 69 1 52428800 CURRENT NO
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
IS_
---
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log
NO
SQL> alter database add logfile member
2 '/u01/disk1/redo01b.log' to group 1,
3 '/u01/disk2/redo02b.log' to group 2,
4 '/u01/disk3/redo03b.log' to group 3;
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 67 2 52428800 INACTIVE YES
2 68 2 52428800 INACTIVE YES
3 69 2 52428800 CURRENT NO
SQL> col member for a50
SQL> set line 100
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
1 INVALID ONLINE /u01/disk1/redo01b.log NO
2 INVALID ONLINE /u01/disk2/redo02b.log NO
3 INVALID ONLINE /u01/disk3/redo03b.log NO
6 rows selected.
SQL> alter database drop logfile member
2 '/u01/disk2/redo02b.log';
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 67 2 52428800 INACTIVE YES
2 68 1 52428800 INACTIVE YES
3 69 2 52428800 CURRENT NO
SQL> select * from v$controlfile
2 ;
STATUS
-------
NAME
----------------------------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl
NO 16384 450
/u01/control02/control02.ctl
NO 16384 450
STATUS
-------
NAME
----------------------------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
/u01/control03/control03.ctl
NO 16384 450
SQL> col name for a55;
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------- --- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl NO 16384 450
/u01/control02/control02.ctl NO 16384 450
/u01/control03/control03.ctl NO 16384 450
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host cp /u01/app/oracle/oradata/orcl/*.* /u01/backup
SQL> host cp /u01/app/oracle/product/10.2.0/db_1/dbs /u01/backup/dbs
SQL> host cp /u01/app/oracle/product/10.2.0/db_1/dbs/*.* /u01/backup/dbs
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> spool off
SQL> alter database clear logfile group 1;
Database altered.
SQL> l
1* select group#,sequence#,members,bytes,status,archived from v$log
SQL> /
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 0 2 52428800 UNUSED YES
2 68 1 52428800 INACTIVE YES
3 69 2 52428800 CURRENT NO
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 70 2 52428800 CURRENT NO
2 68 1 52428800 INACTIVE YES
3 69 2 52428800 ACTIVE YES
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
1 ONLINE /u01/disk1/redo01b.log NO
3 INVALID ONLINE /u01/disk3/redo03b.log NO
SQL>
SQL> show parameter db_create_online_log_dest
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
SQL> alter system set db_create_online_log_dest_1='/u01/disk1';
System altered.
SQL> alter system set db_create_online_log_dest_2='/u01/disk2';
System altered.
SQL> alter system set db_create_online_log_dest_3='/u01/disk3';
System altered.
SQL> alter database add logfile;
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 70 2 52428800 CURRENT NO
2 68 1 52428800 INACTIVE YES
3 69 2 52428800 ACTIVE YES
4 0 3 104857600 UNUSED YES
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
1 ONLINE /u01/disk1/redo01b.log NO
4 ONLINE /u01/disk1/ORCL/onlinelog/o1_mf_4_450v238t_.log NO
3 INVALID ONLINE /u01/disk3/redo03b.log NO
4 ONLINE /u01/disk2/ORCL/onlinelog/o1_mf_4_450v23tg_.log NO
4 ONLINE /u01/disk3/ORCL/onlinelog/o1_mf_4_450v272g_.log NO
8 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 70 2 52428800 ACTIVE YES
2 72 1 52428800 ACTIVE YES
3 73 2 52428800 CURRENT NO
4 71 3 104857600 ACTIVE YES
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 74 2 52428800 ACTIVE YES
2 72 1 52428800 INACTIVE YES
＊蒙昭良
*環境：linux5 + Oracle10gR2
*oracle基礎練習5章
＊重做日誌
*/
一、LGWR程式什麼時候把redo log buffer中的資料寫入redofile?
1、當commit時
2、當重做日誌緩衝區的資料改變記錄超過1M時
3、當重做日誌緩衝區的資料容量達到總容量的1／3時
4、在DBWn程式把DataBase buffer Cache的資料寫入資料檔案之前
5、每3秒
二、DBWn程式什麼時候把DataBase Buffer Cache的 資料寫入資料檔案？
1、當資料庫緩衝區的數量超過所設定的限額
2、當時間到了所設定的時間間隔
3、當程式需要資料庫高速緩衝區而找不到剩餘空間時
4、當發生檢查點時
5、當表被drop或者truncate時
6、當表空間設定為read only 時
7、當表空間進行類似的聯機備份時，alter tablespace users Begin Backup
8、當臨時表空間設定成離線offline或者正常normal狀態時
SQL> alter system switch logfile;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> show parameter fast_start_mttr_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 0
SQL> alter system set fast_start_mttr_target=900;
System altered.
SQL> show parameter fast_start_mttr_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 900
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 67 1 52428800 INACTIVE YES
2 68 1 52428800 INACTIVE YES
3 69 1 52428800 CURRENT NO
SQL> desc v$log;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
IS_
---
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log
NO
SQL> alter database add logfile
2 ('/u01/disk1/redo04a.log',
3 '/u01/disk2/redo04b.log')
4 size 15M;
Database altered.
SQL> select group#,sequence#,members,bytes/1024/1024 "MB",status,archived
2 from v$log;
GROUP# SEQUENCE# MEMBERS MB STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 67 1 50 INACTIVE YES
2 68 1 50 INACTIVE YES
3 69 1 50 CURRENT NO
4 0 2 15 UNUSED YES
SQL> alter database drop logfile group 4;
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 67 1 52428800 INACTIVE YES
2 68 1 52428800 INACTIVE YES
3 69 1 52428800 CURRENT NO
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
IS_
---
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log
NO
SQL> alter database add logfile member
2 '/u01/disk1/redo01b.log' to group 1,
3 '/u01/disk2/redo02b.log' to group 2,
4 '/u01/disk3/redo03b.log' to group 3;
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 67 2 52428800 INACTIVE YES
2 68 2 52428800 INACTIVE YES
3 69 2 52428800 CURRENT NO
SQL> col member for a50
SQL> set line 100
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
1 INVALID ONLINE /u01/disk1/redo01b.log NO
2 INVALID ONLINE /u01/disk2/redo02b.log NO
3 INVALID ONLINE /u01/disk3/redo03b.log NO
6 rows selected.
SQL> alter database drop logfile member
2 '/u01/disk2/redo02b.log';
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 67 2 52428800 INACTIVE YES
2 68 1 52428800 INACTIVE YES
3 69 2 52428800 CURRENT NO
SQL> select * from v$controlfile
2 ;
STATUS
-------
NAME
----------------------------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl
NO 16384 450
/u01/control02/control02.ctl
NO 16384 450
STATUS
-------
NAME
----------------------------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
/u01/control03/control03.ctl
NO 16384 450
SQL> col name for a55;
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------- --- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl NO 16384 450
/u01/control02/control02.ctl NO 16384 450
/u01/control03/control03.ctl NO 16384 450
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host cp /u01/app/oracle/oradata/orcl/*.* /u01/backup
SQL> host cp /u01/app/oracle/product/10.2.0/db_1/dbs /u01/backup/dbs
SQL> host cp /u01/app/oracle/product/10.2.0/db_1/dbs/*.* /u01/backup/dbs
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> spool off
SQL> alter database clear logfile group 1;
Database altered.
SQL> l
1* select group#,sequence#,members,bytes,status,archived from v$log
SQL> /
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 0 2 52428800 UNUSED YES
2 68 1 52428800 INACTIVE YES
3 69 2 52428800 CURRENT NO
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 70 2 52428800 CURRENT NO
2 68 1 52428800 INACTIVE YES
3 69 2 52428800 ACTIVE YES
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
1 ONLINE /u01/disk1/redo01b.log NO
3 INVALID ONLINE /u01/disk3/redo03b.log NO
SQL>
SQL> show parameter db_create_online_log_dest
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
SQL> alter system set db_create_online_log_dest_1='/u01/disk1';
System altered.
SQL> alter system set db_create_online_log_dest_2='/u01/disk2';
System altered.
SQL> alter system set db_create_online_log_dest_3='/u01/disk3';
System altered.
SQL> alter database add logfile;
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 70 2 52428800 CURRENT NO
2 68 1 52428800 INACTIVE YES
3 69 2 52428800 ACTIVE YES
4 0 3 104857600 UNUSED YES
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
1 ONLINE /u01/disk1/redo01b.log NO
4 ONLINE /u01/disk1/ORCL/onlinelog/o1_mf_4_450v238t_.log NO
3 INVALID ONLINE /u01/disk3/redo03b.log NO
4 ONLINE /u01/disk2/ORCL/onlinelog/o1_mf_4_450v23tg_.log NO
4 ONLINE /u01/disk3/ORCL/onlinelog/o1_mf_4_450v272g_.log NO
8 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 70 2 52428800 ACTIVE YES
2 72 1 52428800 ACTIVE YES
3 73 2 52428800 CURRENT NO
4 71 3 104857600 ACTIVE YES
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 74 2 52428800 ACTIVE YES
2 72 1 52428800 INACTIVE YES
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12778571/viewspace-343444/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle重做日誌檔案基礎Oracle
- 重做日誌基礎操作
- 2 Day DBA-管理Oracle例項-管理聯機重做日誌-練習：多路複用聯機重做日誌Oracle
- Oracle的重做日誌Oracle
- 為oracle新增重做日誌組及重做日誌成員Oracle
- oracle 恢復重做日誌Oracle
- 2 Day DBA-管理Oracle例項-管理聯機重做日誌-練習：切換日誌檔案Oracle
- oracle線上調整重做日誌Oracle
- Oracle重做日誌調整技巧Oracle
- 重做日誌
- 聯機重做日誌、歸檔日誌、備用重做日誌
- oracle 聯機重做日誌檔案Oracle
- 重做日誌管理
- Oracle11g redo log 建立、新增、刪除（重做日誌組，重做日誌檔案）Oracle
- 修改Oracle重做日誌檔案的大小Oracle
- redo重做日誌管理
- 恢復重做日誌
- 2 Day DBA-管理Oracle例項-管理聯機重做日誌- 多路複用聯機重做日誌Oracle
- MySQL重做日誌（redo log）MySql
- 重做日誌的恢復
- dml操作重做日誌分析
- Oracle Redo(重做日誌) 模擬故障和恢復Oracle Redo
- oracle基礎練習1－4章Oracle
- AIX基礎日誌AI
- Oracle資料庫重做日誌及歸檔日誌的工作原理說明Oracle資料庫
- Oracle基礎 04 歸檔日誌 archivelogOracleHive
- Oracle基礎 05 聯機日誌 redologOracle
- logmnr分析線上重做日誌
- 手工建立多路重做日誌組
- 重做日誌大小的設定
- oracle 11g rac 新增重做日誌檔案Oracle
- Oracle歸檔日誌比聯機重做日誌小很多的情況總結Oracle
- MySQL基礎練習MySql
- JavaScript基礎練習JavaScript
- expdp基礎練習
- 16、重做日誌檔案的狀態及重做日誌組的狀態說明
- Linux 日誌基礎Linux
- linux基礎日誌Linux