Logfile Lab
1)theory logifle with incremental logfile.
1@@@@check swith logfile.
SQL> create table hr.tbig as select * from dba_source;
SQL> insert into hr.tbig select * from hr.tbig;
SQL> commit;
SQL> select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
711753
SQL> select INCARNATION#,RESETLOGS_CHANGE#,STATUS
from v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# STATUS
------------ ----------------- -------
1 1 PARENT
2 446075 CURRENT
@@@observe the v$log when switch.
@@@consider the RBA still stand in last logfile group.so it is active.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARCHIVED,
STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 14 52428800 1 YES ACTIVE @@@RBA
2 1 15 52428800 1 YES ACTIVE @@@RBA
3 1 16 52428800 1 NO CURRENT
@@@check the ckpt# and scn in the control file.
SQL> select RESETLOGS_CHANGE#,CHECKPOINT_CHANGE#,CURRENT_SCN
from v$database;
RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN
----------------- ------------------ -----------
446075 713887 714098
SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARCHIVED,
STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 14 52428800 1 YES INACTIVE @@@clear
2 1 15 52428800 1 YES INACTIVE @@@clear
3 1 16 52428800 1 NO CURRENT
@@@check alert_orcl_log.
@@@when you switch logfile,the system haved appointed a SCN.
@@@then do work slowly step by step.
@@@we could see from 13:16:13 to 13:22:09,it takes about 6 minutes to reach that
@@@appointed SCN.
[oracle@station3 bdump]$ tail -f alert_orcl.log
Mon Oct 31 13:16:13 2011
Beginning log switch checkpoint up to RBA [0x10.2.10], SCN: 713936
Thread 1 advanced to log sequence 16
Current log# 3 seq# 16 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Mon Oct 31 13:20:50 2011
Completed checkpoint up to RBA [0xf.2.10], SCN: 713887 @@@incremental checkpoint.
Mon Oct 31 13:22:09 2011
Completed checkpoint up to RBA [0x10.2.10], SCN: 713936 @@@switch log file.
Mon Oct 31 13:43:36 2011
Incremental checkpoint up to RBA [0x10.44f.0], current log tail at RBA [0x10.2bb9.0]
@@@check the checkpoint# of control file
SQL> select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
713936
@@@the change of checkpoint#.
713887-711753=2134
@@@the media recover is that uniformize ckpt# of the header of datafiles
@@@with control files.
@@@example:
@@@current_scn,checkpoint_change# is "==" or "<>"
@@@checkpoint RBA,the last of current logs is "==" or "<>"
@@@situation1:both of them are "==",it must be normal shutdown.
@@@situation2:both of them are "<>",it must be abnormal shutdown.
2@@@@ckpt changes when tablespace switch between offline and online.
@@@if you open autobackup controlfile,tablespace offline would be backup control file
@@@autometically.we could check the alter_orcl.log.
[oracle@station3 bdump]$ tail -f alert_orcl.log
Mon Oct 31 14:02:04 2011
alter tablespace users offline
Mon Oct 31 14:02:04 2011
Starting control autobackup
......
......
Control autobackup written to DISK device handle
'/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_10_31/o1_mf_s_765986525_7bwgh0on_.bkp'
Completed: alter tablespace users offline
@@@the offline tablespace's ckpt# is higher then others.
@@@why?
@@@because DBWn write the relative data blocks in the Data Buffer Cache
@@@to disk.make sure nothing stay in cache about this offline tablespace.
SQL> select STATUS,CHECKPOINT_CHANGE#,FILE#,TS# from v$datafile;
STATUS CHECKPOINT_CHANGE# FILE# TS#
------- ------------------ ---------- ----------
SYSTEM 713936 1 0
ONLINE 713936 2 1
ONLINE 713936 3 2
OFFLINE 716513 4 4 @@@higher
ONLINE 713936 5 6
ONLINE 713936 6 7
6 rows selected.
@@@when tablespace online,the autobackup control file would be active.
@@@precondition:you set autobackup control file on.
@@@keep remember that.
SQL> alter tablespace users online;
Tablespace altered.
[oracle@station3 bdump]$ tail -f alert_orcl.log
Mon Oct 31 14:19:39 2011
Incremental checkpoint up to RBA [0x10.386c.0], current log tail at RBA [0x10.38e6.0]
Mon Oct 31 14:32:34 2011
alter tablespace users online
Mon Oct 31 14:32:35 2011
Starting control autobackup
......
......
Control autobackup written to DISK device handle
'/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_10_31/o1_mf_s_765988355_7bwj85ks_.bkp'
Completed: alter tablespace users online
@@@when tablespace online again ,this tablespace still higher then others.
SQL> select STATUS,CHECKPOINT_CHANGE#,FILE#,TS# from v$datafile;
STATUS CHECKPOINT_CHANGE# FILE# TS#
------- ------------------ ---------- ----------
SYSTEM 713936 1 0
ONLINE 713936 2 1
ONLINE 713936 3 2
ONLINE 717358 4 4 @@@this one
ONLINE 713936 5 6
ONLINE 713936 6 7
6 rows selected.
@@@do a normal checkpoint,uniformize the ckpt#
@@@remember controlfile record the ckpt# again.
SQL> alter system checkpoint;
System altered.
SQL> select STATUS,CHECKPOINT_CHANGE#,FILE#,TS# from v$datafile;
STATUS CHECKPOINT_CHANGE# FILE# TS#
------- ------------------ ---------- ----------
SYSTEM 717498 1 0
ONLINE 717498 2 1
ONLINE 717498 3 2
ONLINE 717498 4 4
ONLINE 717498 5 6
ONLINE 717498 6 7
6 rows selected.
[oracle@station3 bdump]$ tail -f alert_orcl.log
Mon Oct 31 14:36:51 2011
Beginning global checkpoint up to RBA [0x10.3b6e.10], SCN: 717498
Completed checkpoint up to RBA [0x10.3b6e.10], SCN: 717498
2)datafile and archivelog name's rule by OMF in 10g
1@@@@ o1_mf_%t_%s_%rxxxxxxx_.arc/dbf for Oracle Manager File(OMF)
@@@%t => thread , %s => sequence , %r => incarnamtion
@@@xxxxxxx => avoid replicate.
[oracle@station3 2011_10_31]$ pwd
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_10_31
[oracle@station3 2011_10_31]$ ls
o1_mf_1_13_7bwc4drd_.arc o1_mf_1_14_7bwcqdlt_.arc o1_mf_1_15_7bwcrx64_.arc
3)add a path to save archivelog.10 paths at most.
1@@@@the archivelog have 10 copys.
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
2@@@@add a path to save archivelog on other disk.
@@@Note:if you set dest_1,the system would cancel the default which save the archivelog
@@@to /u01/app/oracle/flash_recovery_area/ORCL/xxxx.dbf/.
@@@so we must set it back,if you want two path to save the archivelog.
SQL> alter system set log_archive_dest_1='location=/home/oracle/archive_other_disk/ORCL';
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
[oracle@station3 ORCL]$ pwd
/home/oracle/archive_other_disk/ORCL
[oracle@station3 ORCL]$ ls
1_16_761453378.dbf 1_17_761453378.dbf
[oracle@station3 ORCL]$ pwd
/u01/app/oracle/flash_recovery_area/ORCL
[oracle@station3 ORCL]$ ls
autobackup backupset onlinelog
3@@@@check the v$archived_log.
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/home/oracle/archive_other_disk/ORCL/1_16_761453378.dbf
/home/oracle/archive_other_disk/ORCL/1_17_761453378.dbf
4@@@@set the default path back.
SQL> alter system set log_archive_dest_10='location=use_db_recovery_file_dest';
System altered.
@@@switch logfile to check
[oracle@station3 2011_10_31]$ pwd
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_10_31
[oracle@station3 2011_10_31]$ ls
o1_mf_1_18_7bwprvbh_.arc
@@@check
[oracle@station3 ORCL]$ pwd
/home/oracle/archive_other_disk/ORCL
[oracle@station3 ORCL]$ ls
1_16_761453378.dbf 1_17_761453378.dbf 1_18_761453378.dbf
4)add the member of logfile group.
1@@@@add a onlinelog to other disk.
@@@the assign directory must be exist.you know that.
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/ORCL/';
System altered.
SQL> alter system set db_create_online_log_dest_1='/u01/app/oracle/oradata';
System altered.
@@@recommend you set it to other disk.this case is not good.
SQL> alter system set db_create_online_log_dest_2
='/u01/app/oracle/flash_recovery_area/ORCL';
System altered.
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata/ORCL/
db_create_online_log_dest_1 string /u01/app/oracle/oradata
db_create_online_log_dest_2 string /u01/app/oracle/flash_recovery_area/
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
@@@Note:
@@@when you set online_log_dest and add a logfile group,you would find
@@@the db_create_online_log_dest_1(/u01/app/oracle/oradata) and
@@@db_create_online_log_dest_2(/u01/app/oracle/flash_recovery_area/)
@@@generate the logfile name using OMF format.
@@@generally we set the two directory in different disks.
@@@check it generate a directory xxx/ORCL/onlinelog/xxxx
[oracle@station3 onlinelog]$ pwd
/u01/app/oracle/oradata/ORCL/onlinelog
[oracle@station3 onlinelog]$ ls
o1_mf_4_7bwr2k2t_.log o1_mf_5_7bwr38o1_.log o1_mf_6_7bwr47bg_.log
@@@when you create a logfile group,the default redoxx.log is must be assigned
@@@unless you set db_create_file_dest.
@@@then you add a logfile member to that group,assigning the directory to
@@@other disk which must exist.
@@@I add the the group later.so it is ,you know that.
SQL> alter database add logfile group 7 size 50M;
Database altered.
SQL> alter database add logfile member to group 7 size 50M;
Database altered.
@@@
/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_7_7bwsl7by_.log (51M)
/u01/app/oracle/oradata/ORCL/onlinelog/
o1_mf_4_7bwr2k2t_.log (51M)
o1_mf_5_7bwr38o1_.log(51M)
o1_mf_6_7bwr47bg_.log (51M)
o1_mf_7_7bwsl604_.log (51M)
@@@the member of group must be equal to each other, or you waste space.
2@@@@switch logfile group
@@@if your db_create_file_dest is none,you must assign the path to add logifle.
@@@it is boring.so we set it one.
@@@remember as soon as you set it, the file would be named by the system using OMF format.
@@@automatically.
@@@if the online_log_dest have 5,it would cost I/O very much.
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest 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 database add logfile group 4 size 50M;
Database altered.
SQL> alter database add logfile group 5 size 50M;
Database altered.
SQL> alter database add logfile group 6 size 50M;
Database altered.
SQL> select status,members,archived,GROUP# from v$log;
STATUS MEMBERS ARC GROUP#
---------------- ---------- --- ----------
INACTIVE 1 YES 1
INACTIVE 1 YES 2
CURRENT 1 NO 3
UNUSED 1 YES 4
UNUSED 1 YES 5
UNUSED 1 YES 6
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select status,members,archived,GROUP# from v$log;
STATUS MEMBERS ARC GROUP#
---------------- ---------- --- ----------
INACTIVE 1 YES 1
INACTIVE 1 YES 2
ACTIVE 1 YES 3 @@@there are ckpt RBA.
ACTIVE 1 YES 4
ACTIVE 1 YES 5
CURRENT 1 NO 6
SQL> alter system checkpoint;
System altered.
SQL> select status,members,archived,GROUP# from v$log;
STATUS MEMBERS ARC GROUP#
---------------- ---------- --- ----------
INACTIVE 1 YES 1
INACTIVE 1 YES 2
INACTIVE 1 YES 3
INACTIVE 1 YES 4
INACTIVE 1 YES 5 @@@all data buffer cache clean.
CURRENT 1 NO 6
@@@check the alert_orcl.log
Beginning global checkpoint up to RBA [0x16.43.10], SCN: 726233
Completed checkpoint up to RBA [0x16.43.10], SCN: 726233
Completed checkpoint up to RBA [0x16.2.10], SCN: 726183
Completed checkpoint up to RBA [0x15.2.10], SCN: 726181
Completed checkpoint up to RBA [0x14.2.10], SCN: 726178
@@@
[oracle@station3 orcl]$ ls
control01.ctl control03.ctl redo01.log redo03.log system01.dbf temp01.dbf users01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf tbsaudit.dbf undotbs01.dbf
@@@
@@@current logfile could not be removed, because it could not be backuped.
@@@then the instance recovery need it for forward recovery.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
SQL> select status,members,archived,GROUP# from v$log;
STATUS MEMBERS ARC GROUP#
---------------- ---------- --- ----------
ACTIVE 1 YES 1
CURRENT 1 NO 4
INACTIVE 1 YES 5
INACTIVE 1 YES 6
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> select status,members,archived,GROUP# from v$log;
STATUS MEMBERS ARC GROUP#
---------------- ---------- --- ----------
CURRENT 1 NO 4
INACTIVE 1 YES 5
INACTIVE 1 YES 6
3@@@@add the member to logfile group.
SQL> alter database add logfile member '/home/oracle/redo04_mirror.log' to group 4;
Database altered.
SQL> alter database add logfile member '/home/oracle/redo05_mirror.log' to group 5;
Database altered.
SQL> alter database add logfile member '/home/oracle/redo06_mirror.log' to group 6;
Database altered.
@@@there are two member in different logfile group.
[oracle@station3 ~]$ du -sh /home/oracle/redo0*
51M /home/oracle/redo04_mirror.log
51M /home/oracle/redo05_mirror.log
51M /home/oracle/redo06_mirror.log
[oracle@station3 ~]$ du -sh /u01/app/oracle/oradata/ORCL/onlinelog/*
51M /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_4_7bwr2k2t_.log
51M /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_5_7bwr38o1_.log
51M /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_6_7bwr47bg_.log
4@@@@
@@@db_recovery is enable if the value exists.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
Logfile Overview
原创emperor_majesty 博主文章分类：B_Recovery ©著作权
©著作权归作者所有：来自51CTO博客作者emperor_majesty的原创作品，请联系作者获取转载授权，否则将追究法律责任
上一篇：Hot Backup
提问和评论都可以，用心的回复会被更多人看到
评论
发布评论
相关文章
