Online redo log file)

Almost all changes that occur in oracle online redo log files are recorded in online redo log files. oracle uses these redo log groups to restore the database, therefore, they are very important. The main work of online redo log files is to back up data, 1: record all data changes 2: Provide a recovery mechanism 3: It must be organized into groups 4: there are at least two groups of online redo log files. Each redo log is assigned to the group. oralce writes an online redo log group at a time. Once the online redo log in this group is filled, oracle writes the conversion to the next online redo log group. Therefore, oracle writes data between multiple groups in a loop. Each online redo log file is assigned a unique sequence number ). The serial numbers of online redo log files will not be repeated unless the database is rebuilt. We can have multiple online reorganizations even log files in one group, which means that each online redo log group contains at least two online redo log files, and each file in the group becomes a member, each member should be allocated to different disks. In order to include the lost group of files, oracle writes data to the member in the group concurrently. The online redo log files in a group are identical, write information to all online redo log file groups through the LGWR process. Each member in the group has the same size. When oracle Data writes information to the redo log file group, it first allocates a log sequence numbers. multiplexing: at least two member members in a group, this avoids the spof of redo log files. A group of redo log files contains multiple members, each of which has a number. When a database is created, the online redo log file is created, and the database cannot be missing the online redo log file. If all member files in a group are lost, the database will be damaged, therefore, it is very important to save and back up these files. There are two types of redo logs: Online redo logs and archive logs. When does LGWR write redo log file? 1: when a transaction is committed, 2: Write every three seconds. 3: When the redo log buffer turns to 1/3 full, write 4: when the redo log buffer size operation is 1 MB, write operation is required. 5: write before a checkpoints operation in DBWn. The function of the DBWn process (write the redo log files information back to datafiles) must be to write the redo log first and then write it to the data file. Online redo day Files Work: Use Them cyclically and understand the definition of log switch. (Switch between different groups for writing), log switch triggers checkpoints to write the dirty data in the memory back to the database file and calls a checkpoints force LOG switching (log Switches) and checkpoints V $ log; this view is the view of online redo log files. 1: Use v $ logfile to find the redo log file and group of the database; [SQL] SQL> col member format a50; SQL> select group #, type, member from v $ logfile; GROUP # type member ---------- ------- creating 3 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO03.LOG 2 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO02.LOG 1 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO01.LOG 2 The following command executes the alter system switch logfile command and then queries the group number 3 currently written by oracle again, which indicates that the log switch log group switch operation is performed. This is done manually. [SQL] SQL> select group #, thread #, sequence #, status from v $ log; GROUP # THREAD # SEQUENCE # STATUS ---------- ------------------ 1 1 1 442 INACTIVE 2 1 443 CURRENT 3 1 441 inactive SQL> alter system switch logfile; the system has changed. SQL> select group #, thread #, sequence #, status from v $ log; GROUP # THREAD # SEQUENCE # STATUS ---------- ---------------- 1 1 442 INACTIVE 2 1 443 ACTIVE 3 1 444 CURRENT 3: control the checkpoints action (the data information in the online redo log file is regularly written to the data file). Go to the reference and find this parameter: fast_start_mttr_target to specify the maximum value and maximum time of quick recovery, specifies how long the DBWn process must write information in the redo log file to the data file. This parameter is a key value that affects the performance, because it can touch I/O to write [SQL] SQL> show parameter fast_start_mttr_t Arget; name type value before ----------- ------------------------------ fast_start_mttr_target integer 0 indicates how long the DBWn process must write information in the redo log file to the data file. to modify the time, use: [SQL] SQL> alter system set fast_start_mttr_target = 60 scope = both; the system has changed. SQL> show parameter fast_start_mttr_target; NAME TYPE VALUE ------------- -------------------------------- fast_start_mttr_target integer 60 after the above alter system set fast_start_mttr_target = 100 scope = both; indicates that, the DBWn process must write the information in the redo log file to the data file within 60 seconds. 4. to manually write the online redo log information back to the data file, run the following command: [SQL] SQL> alter system checkpoint; the system has been changed. SQL> for more information about group and member operations, see click me 5: to add an online redo log group, run the following command to add the redo log group number: 4 [SQL] SQL> select group #, type, member from v $ logfile; GROUP # type member ---------- ------- creating 3 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO03.LOG 2 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO02.LOG 1 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO01.LOG SQL> alter database add Logfile group 4 ('d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO04.LO G') size 52429312; the database has been changed. SQL> select group #, type, member from v $ logfile; GROUP # TYPE MEMBER ---------- ------- wait 3 ONLINE D: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO03.LOG 2 ONLINE D: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO02.LOG 1 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO01.LOG 4 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO04.LOG SQL> 6: ADD the member command to a redo log group [SQL] SQL> ALTER DATABASE ADD LOGFILE ME MBER 'd: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO05.LOG't o group 4; the database has been changed. SQL> select group #, type, member from v $ logfile; GROUP # TYPE MEMBER ---------- ------- wait 3 ONLINE D: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO03.LOG 2 ONLINE D: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO02.LOG 1 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO01.LOG 4 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO04.LOG 4 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO05.LOG as shown above: There are two redo log files with group number 4 Respectively: the size and content of the two member log files are exactly the same. You can place two member instances on different disks. D: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO04.LOGD: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO05.LOG shows the concepts of group and member and displays on different disk. 7. Key points for deleting redo file member: before deleting a group, make sure that there are at least two redo log files in the group, that is, there are at least two member. If you want to delete this (only one member exists in one group ), if you want to delete a member, you can run the operating system command to delete it. First, query the following information, then delete it, And then query and compare the command: alter database drop logfile member 'd: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO05.LOG '; [SQL] SQL> select group #, type, member from v $ logfile; GROUP # TYPE MEMBER ----------- -------------------------------------------------- 3 ONLINE D: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO03.LOG 2 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO02.LOG 1 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO01.LOG 4 online d: \ AP P \ TOPWQP \ ORADATA \ ORCL \ REDO04.LOG 4 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO05.LOG SQL> ALTER DATABASE DROP LOGFILE MEMBER 'd: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO05.LOG '; the database has been changed. SQL> select group #, type, member from v $ logfile; GROUP # TYPE MEMBER ---------- ------- wait 3 ONLINE D: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO03.LOG 2 ONLINE D: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO02.LOG 1 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO01.LOG 4 online d: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO04.LOG an instance requires at least two groups of online redo log files. 8: deleting an active or current group of redo files groups cannot be deleted. Inactive status indicates that the dirty data in redo log files has been written back to the data file, so it is inactive. You can delete a group. If you want to delete a group in the current state, you can use alter system switch logfile to delete the group. If you want to delete the group, you must manually delete the group in the control file. Alter database drop logfile group 4 command: alter database drop logfile group 4; [SQL] SQL> select group #, members, archived, status from v $ log; GROUP # members arc status ---------- --- -------------- 1 1 1 no inactive 2 1 no inactive 3 1 no current 4 1 yes unused SQL> ALTER DATABASE DROP LOGFILE GROUP 4; DATABASE changed. SQL> select group #, members, archived, status from v $ log; GROUP # MEMBERS ARC STATUS ---------- --- ---------------- 1 1 1 NO INACTIVE 2 1 NO INACTIVE 3 1 NO CURRENT 9: relocate and rename must back up the database during the structure of the redo log file. 1 before relocate: First shutdown immediate 2: to the directory and then copy, 3: startup mount 4: alter database rename file 'file path file name' to 'file path filename '; see the following for details: 5. relocating A redo log GroupYou may be want to relocate your existing redo log group and change the path of redo log files. here are the steps to accomplish itStep 1: Shutdown database 1 SQL> shutdown immediate; Step 2: Backup your existing control file and redo log file at operating system level. step 3: Move your redo log member to a new location. you may also change the file name if you want. 1 # mv/u02/oradata/mydb/redo02.log/u03/oradata/mydb/redo03.logStep 4: Startup in nomount mode 1 SQL> startup nomount; Step 5: path of redo log files is kept in control file. you have to update that record by command below: 1 SQL> ALTER DATABASE RENAME FILE '/u02/oradata/mydb/redo02.log' TO '/u03/oradata/mydb/redo03.log' Control file is modified and the path of redo log member is updated. now you can open your database. 1 SQL> alter database open;