Dataguard Apply Services (redo applications and SQL Apps)

<span id="Label3"></p><p><p><strong>Application Services</strong> <strong>Apply Services</strong></p></p><p><p>Organized according to Oracle official documentation</p></p><p><p>http://docs.oracle.com/cd/E11882_01/server.112/e25608/log_apply.htm#i1027052</p></p><p><p><br></p></p><p><p><strong>Apply Services</strong> <strong>automatically applied from the library</strong> <strong>Redo</strong> <strong>to achieve</strong> <strong>Primary</strong> <strong>and the</strong> <strong>Standby</strong> <strong>of Data Synchronization. </strong></p></p><p><p></p></p><p><p><strong>1.Apply Services</strong> <strong>in two ways:</strong></p></p><p><p>Redo Apply (physical Standby)</p></p><p><p>SQL Apply (logical Standby)</p></p><p><p><br></p></p><p><p><strong>2.Apply Services</strong> <strong>configuration Options</strong></p></p><p><p><1> Real-time Application Redo</p></p><p><p>Real-time Application Requirements:</p></p><p><p>Standby Library is archive mode</p></p><p><p>Standby library with Standbyredo log (used to receive redo entries from Primary)</p></p><p><p>If the real-time app feature is turned on, the Applyservice on the standby library can apply the redo entries it receives without waiting for the standby redo log archive on the standby library before redo can be applied.</p></p><p><p>In this way, both switchover and failover can be done quickly because all standby redo logs have been applied before switchover and failover (not in real-time mode,</p></p><p><p>It takes time to apply all standby redo log files before switching.</p></p><p><p><br></p></p><p><p>Turn on real-time app redo Features:</p></p><p><p>Physical Standby:alter Database RECOVER MANAGED STANDBY database USING currentlogfile;</p></p><p><p>Logic standby:alter DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;</p></p><p><p><br></p></p><p><p>Figure 7-1 in the online documentation clearly explains the real-time Application.</p></p><p><p>http://docs.oracle.com/cd/E11882_01/server.112/e25608/log_apply.htm#i1023371</p></p><p><p></p></p><p><p><2> Time-lapse Application archive log</p></p><p><p>In some scenarios, we need the standby library to delay the application of the archived logs, that is, the standby library has received the redo log from the main library, and has been archived in the standby library, but the archive log is not applied for the time Being.</p></p><p><p>By setting the delay time (in minutes), The standby library temporarily avoids data loss due to incorrect operation or corruption of the primary library.</p></p><p><p><br></p></p><p><p>Why in this way:</p></p><p><p>The actual work may exist in this situation, primary library misoperation deleted the Data. If the standby library is in real-time application log mode, the corresponding data for the standby library will also be Deleted.</p></p><p><p>This can be avoided by applying the archived log mode with Delay. Primary library mistakenly deleted data, standby library in the set delay time will not apply the archive log, will certainly not delete Data.</p></p><p><p>By using the delay time, it is possible to retrieve the deleted data completely.</p></p><p><p><br></p></p><p><p>Set Delay time:</p></p><p><p>The Log_archive_dest_n parameter in the parameters file of the primary and standby libraries plus the delay option, if only the delay parameter is specified, but no specific value is specified, the default is 30 Minutes.</p></p><p><p>If the real-time Log app is enabled, the delay parameter is ignored, so the Iqidong real-time delay log app is not Pasted.</p></p><p><p><br></p></p><p><p>If you want the standby library to delay the application of the log for 360 minutes, set the delay parameter in Primary.</p></p><p><p>Such as:</p></p><p><p>--360 minutes delay</p></p><p><p>Sql〉altersystem set log_archive_dest_2= ' Service=standby lgwr SYNC affirm delay=360valid_for= (ONLINE_LOGFILES,PRIMARY _role) Db_unique_name=standby ';</p></p><p><p>--if It's a new library, you can also set it directly in the parameter file</p></p><p><p>Log_archive_dest_2= ' SERVICE=STANDBYLGWR SYNC affirm delay=360 valid_for= (online_logfiles,primary_role) DB_UNIQUE_ Name=standby '</p></p><p><p><br></p></p><p><p>Cancel delay Time Setting:</p></p><p><p>Physical standby:</p></p><p><p>Alterdatabase RECOVER MANAGED STANDBY DATABASE nodelay;</p></p><p><p>Logic standby:</p></p><p><p>Sql>alter DATABASE START LOGICAL STANDBY APPLY nodelay;</p></p><p><p><br></p></p><p><p><strong>3.</strong> <strong>redo</strong> <strong>log application</strong> for <strong>physical</strong> <strong>standby</strong> <strong>Library</strong></p></p><p><p>By default, the Log app uses an archive log. however, when the physical standby is used in real-time log applications, the RFS process writes the redo entries of the primary library to standby standby log in the redo library;</p></p><p><p>The log application process can then read the standby redo log directly for the log Application.</p></p><p><p></p></p><p><p><1> start log app (starting redo Apply)</p></p><p><p>To start the log application on the physical standby library, make sure that the physical standby library is in the Mount State (?? Open State is ok?? ）。</p></p><p><p>Start the log application using the ALTER DATABASE RECOVER MANAGED STANDBY database Statement.</p></p><p><p><br></p></p><p><p>Start the log application at the Foreground:</p></p><p><p>sql> alterdatabase RECOVER MANAGED STANDBY DATABASE; In this way, it is useless to do anything at the command line until the other session kills the Session.</p></p><p><p>Start the Log app in the Background:</p></p><p><p>sql> alterdatabase RECOVER MANAGED STANDBY DATABASE DISCONNECT;</p></p><p><p>Real-time log application: plus using current logfile</p></p><p><p>sql> alterdatabase RECOVER MANAGED STANDBY DATABASE USING Current LOGFILE; --front Desk</p></p><p><p>sql> alterdatabase RECOVER MANAGED STANDBY DATABASE USING current LOGFILE DISCONNECT fromsession; --backstage</p></p><p><p></p></p><p><p><2> Stop Log App</p></p><p><p>sql> ALTER DATABASE RECOVER MANAGED standbydatabase CANCEL;</p></p><p><p><br></p></p><p><p><3> Physical Standby Library monitoring log Application</p></p><p><p>--show Protection mode, Protection level, database role, Toggle State</p></p><p><p>Sql> SELECT protection_mode,protection_level,database_rolerole, switchover_status from v$database;</p></p><p><p></p></p><p><p>Protection_mode Protection_level ROLE Switchover_status</p></p><p><p>-------------------- ------------------------------------ --------------------</p></p><p><p>MAXIMUM Performance MAXIMUM Performance Physicalstandby not allowed</p></p><p><p><br></p></p><p><p>--fast-start Failover Status</p></p><p><p>Sql> Select Fs_failover_status "fsfostatus", fs_failover_current_target target,fs_failover_threshold THRESHOLD,</p></p><p><p>Fs_failover_observer_present "observerpresent" from v$database;</p></p><p><p><br></p></p><p><p>FSFO STATUS TARGET THRESHOLD OBSERVE</p></p><p><p>---------------------- ---------------------------------------- -------</p></p><p><p>DISABLED 0</p></p><p><p><br></p></p><p><p>--log application, Log transfer status in physical standby library</p></p><p><p>sql> SELECT PROCESS, STATUS, thread#,sequence#,block#,blocks,pid from v$managed_standby;</p></p><p><p>PROCESS STATUS thread# sequence# block# BLOCKS PID</p></p><p><p>--------- ------------ ---------- -------------------- ---------- ----------</p></p><p><p>ARCH CLOSING 1 34 1 3 9742</p></p><p><p>ARCH CONNECTED 0 0 0 0 9746</p></p><p><p>ARCH CLOSING 1 30 1 3 9750</p></p><p><p>ARCH CLOSING 1 31 1 31 9754</p></p><p><p>ARCH CLOSING 1 26 1 77 9758</p></p><p><p>ARCH CLOSING 1 32 1 11 9762</p></p><p><p>ARCH CLOSING 1 27 1 1 9766</p></p><p><p>ARCH CLOSING 1 33 1 2 9770</p></p><p><p>RFS IDLE 0 0 0 0 10986</p></p><p><p>RFS IDLE 1 37 688 1 9797</p></p><p><p>RFS IDLE 0 0 0 0 9801</p></p><p><p>RFS IDLE 0 0 0 0 9805</p></p><p><p>MRP0 Applying_log 1 37 688 1024000 10916</p></p><p><p>RFS IDLE 0 0 0 0 10995</p></p><p><p>RFS IDLE 0 0 0 0 10999</p></p><p><p><br></p></p><p><p>--mainly See RFS and MRP0</p></p><p><p>--rfs (Remote File Server) process: receive redo of primary database, save in Standbyredo log (arch mode does not write standby, save archive Directly)</p></p><p><p>The Status Values are:</p></p><p><p>WRITING: the process is active and is writing redo to the archive log</p></p><p><p>Idle: free, No log synchronization</p></p><p><p>Receiving: Listening to network communication</p></p><p><p>OPENING: Opening archive Log</p></p><p><p>--mrp0 (Manager Recover Process): responsible for log application, as long as it is enabled to the Log app will have this service. In the physical standby is the mrp, in the logic standby is the lsp.</p></p><p><p>Status Value:</p></p><p><p>Wait_for_log: wait for the log to complete</p></p><p><p>Applying_log: logging is being applied</p></p><p><p><br></p></p><p><p>--the Physical standby received the archive transmitted by the primary library.</p></p><p><p>Sql> SELECT thread#,sequence#,first_change#,next_change# from v$archived_log;</p></p><p><p></p></p><p><p>--datagruad Information</p></p><p><p>Sql> SELECT MESSAGE from v$dataguard_status;</p></p><p><p>MESSAGE</p></p><p><p>--------------------------------------------------------------------------------</p></p><p><p>Lns:beginning to archive log 1 thread 1 sequence 37</p></p><p><p>lns:completed Archiving log 1 thread 1 sequence 37</p></p><p><p>Arc2:beginning to archive thread 1 sequence 37 (1051112-1053910)</p></p><p><p>arc2:completed archiving thread 1 sequence 37 (1051112-1053910)</p></p><p><p>Lns:standby Redo logfile selected for thread 1sequence-destination log_</p></p><p><p>Archive_dest_2</p></p><p><p><br></p></p><p><p>--check whether the archive directory is normal in the standby library</p></p><p><p>Sql> Select Dest_name,status,error,target,processfrom v$archive_dest where rownum<4;</p></p><p><p><br></p></p><p><p>--cracks: Standby Library and Primary library difference how many redo files</p></p><p><p>SELECT * from v$archive_gap;</p></p><p><p></p></p><p><p><br></p></p><p><p><strong>4.</strong> <strong>redo</strong> <strong>log application</strong> for <strong>logical</strong> <strong>standby</strong> <strong>Library</strong></p></p><p><p>SQL applications convert the archive log or standby redo log through LOGMNR to SQL statements and then execute SQL on the logical standby Data. SQL application when the standby library is in open state,</p></p><p><p>At this time in the standby library can also do other operations, such as reports, queries and so On.</p></p><p><p><br></p></p><p><p><1> Start your SQL app</p></p><p><p>sql> ALTER DATABASE START LOGICAL STANDBY APPLY;</p></p><p><p>sql> ALTER DATABASE START LOGICAL STANDBY applyimmediate; --logical standby Database Real-time SQL application</p></p><p><p></p></p><p><p><2> Stop SQL Apps</p></p><p><p>sql> ALTER DATABASE STOP LOGICAL STANDBY APPLY;</p></p><p><p><br></p></p><p><p><3> Monitoring of SQL applications</p></p><p><p>--sql events that occur during the application Process. By default, this view records up to 10,000 events. Can be passed Dbms_logstdby. Apply_set () Modification.</p></p><p><p>sql> ALTER SESSION SET nls_date_format = ' Dd-mon-yy HH24:MI:SS ';</p></p><p><p>sql> COLUMN STATUS FORMAT A60</p></p><p><p>sql> SELECT event_time, STATUS, EVENT fromdba_logstdby_events ORDER by event_timestamp, commit_scn, current_scn;</p></p><p><p></p></p><p><p></p></p><p><p>--sql dynamic information for archived logs during application</p></p><p><p>sql> COLUMN dict_begin FORMAT A10;</p></p><p><p>sql> SET NUMF 99999999;</p></p><p><p>Sql> SELECT file_name, sequence# as seq#,first_change# as f_scn#,next_change# as n_scn#, Timestamp,dict_begin as BEG,DI Ct_end as END,</p></p><p><p>thread# as thr#, applied from Dba_logstdby_log to sequence#;</p></p><p><p></p></p><p><p>file_name seq# f_scn n_scn timestam BEG END thr# Applied</p></p><p><p>------------------------- ---- ------- --------------- --- --- --- ---------</p></p><p><p>/oracle/dbs/hq_nyc_2.log 2 101579 101588 11:02:58 no No 1 YES</p></p><p><p>/oracle/dbs/hq_nyc_3.log 3 101588 142065 11:02:02 no No 1 YES</p></p><p><p>/oracle/dbs/hq_nyc_4.log 4 142065 142307 11:02:10 no No 1 YES</p></p><p><p>/oracle/dbs/hq_nyc_5.log 5 142307 142739 11:02:48 Yes YES1 Yes</p></p><p><p>/oracle/dbs/hq_nyc_6.log 6 142739 143973 12:02:10 no No 1 YES</p></p><p><p>/oracle/dbs/hq_nyc_7.log 7 143973 144042 01:02:11 no No 1 YES</p></p><p><p>/oracle/dbs/hq_nyc_8.log 8 144042 144051 01:02:01 no No 1 YES</p></p><p><p>/oracle/dbs/hq_nyc_9.log 9 144051 144054 01:02:16 no No 1 YES</p></p><p><p>/oracle/dbs/hq_nyc_10.log 144054 144057 01:02:21 no No 1 YES</p></p><p><p>/oracle/dbs/hq_nyc_11.log 144057 144060 01:02:26 no No 1 current</p></p><p><p>/oracle/dbs/hq_nyc_12.log 144060 144089 01:02:30 no No 1 current</p></p><p><p>/oracle/dbs/hq_nyc_13.log 144089 144147 01:02:41 No No 1 NO</p></p><p><p></p></p><p><p></p></p><p><p>--？？？</p></p><p><p>sql> COL NAME FORMAT A20</p></p><p><p>sql> COL VALUE FORMAT A12</p></p><p><p>sql> COL UNIT FORMAT A30</p></p><p><p>sql> SELECT NAME, VALUE, UNIT fromv$dataguard_stats;</p></p><p><p></p></p><p><p>NAME VALUE UNIT</p></p><p><p>-------------------- ------------------------------------------</p></p><p><p>Apply finish time +00 00:00:00 Day (2) to second (1) interval</p></p><p><p>Apply Lag +00 00:00:00 Day (2) to second (0) interval</p></p><p><p>Transport lag +00 00:00:00 Day (2) to second (0) interval</p></p><p><p></p></p><p><p></p></p><p><p>This article transferred from: http://blog.chinaunix.net/uid-26844646-id-5602383.html</p></p><p><p>Dataguard Apply Services (redo applications and SQL Apps)</p></p></span>