_oracle System _ with the Tang Notes 006-Log principle

To put it simply, learning Oracle database is two goals: to ensure consistency of database data and improve database performance (this doesn't matter with logs). Log function: Just to ensure the consistency of database data;1. Oracle Log Principle
Interpretation method of historical records
How logging is logged
2, the actual log production process
3. Archive Mode

    ALTER DATABASE Archivelog ARCHIVE LOG LIST SELECT log_mode from v$database ALTER DATABASE Noarchivelog ALT    ER SYSTEM ARCHIVE log current alter system ARCHIVE log all alter system SET log_archive_max_processes=3;    alter system set Log_archive_dest = '/u01/app/oracle/archivelog1 ' scope = SPFile;    alter system set Log_archive_duplex_dest = '/u01/app/oracle/archivelog2 ' scope = SPFile;    alter system set log_archive_dest_1 = ' Location=/u01/app/oracle/archivelog3 ';    alter system set log_archive_dest_2 = ' service=standby1 ';    alter system set Log_archive_format = ' Arch_%t_%s_%r.arc ';      Col dest_name format A20;    Col destination format A30;    Select Dest_name,status,archiver,destination,log_sequence,reopen_secs,transmit_mode,process from V$archive_dest;    Select Name,sequence#,registrar,standby_dest,archived,status from V$archived_log; V$archived_log--Get information about archiving from Control files V$archive_dest--Archive path and status V$log_historyControl the history of the log in the file V$database---see if the database is in an archived state v$archive_processes background process information about archive select * from v$    LogFile Select member,bytes/1024/1024 from V$log a,v$logfile b where a.group#=b.group# select Name,status from V$archived_ Log

4. How to ensure that committed transactions are not lost5, Write-ahead-log: Log Write Priority6. LGWR bypasses the OS cache and writes directly to the disk, but does not bypass the stored write cache
7. Log Buffer Size Setting

    9i ago, generally 3M    in 10g, Oracle automatically adjusts its value, and he follows the principle that ' Fixed SGA Size ' + ' Redo buffers ' is an integer multiple of granule size    select * from v$ Sgainfo where name in (' Fixed SGA size ', ' Redo buffers ', ' granule Size ');    --in 10.2.0.3, the default value of log Buffer is 14M, in 10.2.0.4, the default value is 15M    select * from V$version where rownum<2;

8, LGWR trigger condition: Write-ahead-log: Log Write Priority

    1. User Submissions    2. There are 1/3 redo log buffers that were not written to disk    3. A redo log buffer greater than 1M was not written to disk    4. Every 3 seconds    5.DBWR the SCN that needs to write data is greater than the SCN of the LGWR record, DBWR Trigger LGWR Write

9. Log Optimization Recommendations

    In the OLTP system, the REDO LOG file is mainly small, relatively frequent, the general write size in a few k, and the number of write IO per second will reach dozens of times, hundreds of or even thousands of times. The Redo log file is therefore suitable for storage on disks with higher IOPS speeds, and SATA disks that can only reach hundreds of IOPS are not suitable for storing redo log files. In addition, because the redo log file is written serially, the redo log file's underlying stripe processing is very limited for redo log write performance.

10, REDO LOG switch time should be as far as possible not less than 10-20 minutes

Select To_char (first_time, ' yyyy-mm-dd hh24:mi:ss ') f_time,sequence# from V$log_history

11. Some Log related operations

ALTER DATABASE Add logfile Group 5 '/opt/oracle/oradata/dbtest/redo05_1.log ' SIZE 10Malter database Add logfile member '/O Pt/oracle/oradata/dbtest/redo04_3.log ' to group 4alter drop logfile Group 5alter database drop logfile  ('/op T/oracle/oradata/dbtest/redo05_1.log ', '/opt/oracle/oradata/dbtest/redo05_2.log ')