/* 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-343444/