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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle11g redo log 建立、新增、刪除（重做日誌組，重做日誌檔案）Oracle
- 重做日誌管理
- MySQL重做日誌（redo log）MySql
- MySQL重做日誌恢復資料的流程MySql
- HTML基礎練習HTML
- MySQL基礎練習MySql
- JAVA 基礎練習題Java
- Python基礎練習題Python
- linux基礎練習題Linux
- IOS基礎-Masonry 練習iOS
- 日誌框架體系整理( 基礎 )框架
- Apache基礎配置與日誌管理Apache
- 記錄一則clear重做日誌檔案的案例
- oracle alert日誌Oracle
- 訓練日誌 2018.10.24
- Java基礎 --- 綜合練習Java
- Django基礎之四（日誌）【待續】Django
- MySQL 修改InnoDB重做日誌檔案的數量或大小MySql
- Archived Redo Logs歸檔重做日誌介紹及其優點Hive
- 零基礎寫框架(2)：故障排查和日誌基礎框架
- oracle刪除日誌Oracle
- oracle歸檔日誌Oracle
- Reborn Bugcat 訓練日誌GC
- python 基礎之scrapy 原理練習Python
- python基礎 while迴圈練習PythonWhile
- python基礎語句小練習Python
- 9道python基礎練習題Python
- 基礎練習 高精度加法(java)Java
- Redis基礎篇（三）持久化：AOF日誌Redis持久化
- Apache基礎配置與日誌管理解析Apache
- Java學習之基礎語法練習Java
- Oracle歸檔日誌清理Oracle
- 歸檔oracle alert日誌Oracle
- 從 Oracle 日誌解析學習資料庫核心原理Oracle資料庫
- yolov5訓練日誌YOLO
- 某行日誌平臺 Elasticsearch 運維基礎篇Elasticsearch運維
- Linux-本地日誌服務管理（rsyslog基礎）Linux
- python基礎（四）----列表、字典練習題Python
- java基礎小練習（31-35）Java