flashback logging internals l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Flashback Logging Internals PowerPoint Presentation
Download Presentation
Flashback Logging Internals

Loading in 2 Seconds...

play fullscreen
1 / 53

Flashback Logging Internals - PowerPoint PPT Presentation


  • 369 Views
  • Uploaded on

Flashback Logging Internals. Julian Dyke Independent Consultant. Web Version - December 2007. juliandyke.com. © 2007 Julian Dyke. Extended Clusters versus Fast Start Failover Flashback Database Flashback Logging Internals. Agenda. Extended Clusters versus Fast Start Failover.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Flashback Logging Internals' - vilina


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
flashback logging internals

Flashback Logging Internals

Julian Dyke

Independent Consultant

Web Version - December 2007

juliandyke.com

©2007 Julian Dyke

agenda
Extended Clusters versus Fast Start Failover

Flashback Database

Flashback Logging Internals

Agenda
extended clusters overview

Instance 2

Instance 1

Node 2

Node 1

Extended ClustersOverview

Public Network

Private Network

Quorum

Site3

Storage Network

Storage Networks

Database

Database

Site1

Site2

extended clusters overview5
Extended ClustersOverview
  • Currently the Holy Grail of high availability
  • RAC nodes located at physically separate sites
    • In-built disaster recovery
  • In the event of a site failure, database is still available
  • Active / Active configuration
    • Users can access database via either site
  • Storage is duplicated at each site
  • Can use ASM or vendor-supplied storage technology to ensure all writes are replicated to storage on each site
extended clusters advantages and disadvantages
Extended ClustersAdvantages and Disadvantages
  • Advantages
    • Disaster recovery - all changes written to both sites
    • Active / Active - both sites available
  • Disadvantages
    • Complexity
    • Cache fusion traffic between sites
    • Requires Enterprise Edition licences + RAC option
    • Cost of inter-site fibre network
fast start failover overview

Quorum

Observer

Instance 2

Instance 1

Node 2

Node 1

Fast Start FailoverOverview

Public Network

Private Network

Site3

Storage Network

Storage Networks

Database

Database

Site1 - Primary

Site2 - Standby

fast start failover overview8
Fast Start FailoverOverview
  • Target standby database must be nominated
  • Failure of primary database can be detected and automatically failed over to nominated standby database
  • Primary database can potentially be reinstated automatically
  • Requires flashback logging
  • Requires DGMGRL configuration
  • Must configure MAXIMUM AVAILABILITY protection mode
    • Standby database archive log destination must be configured as LGWR SYNC
fast start failover advantages disadvantages
Fast Start FailoverAdvantages & Disadvantages
  • Advantages
    • No interconnect network required between sites
    • No fibre network required between sites
    • RAC licences not required if each site is a single-instance
  • Disadvantages
    • Active / Passive
    • Requires Enterprise Edition licence
fast start failover observer
Fast Start FailoverObserver
  • Requires third independent site with:
    • Oracle client installation (administrative user)
    • Oracle Net configuration to primary and standby
  • On third site:
    • DGMGRL starts observer
    • Observer monitors state of primary database
    • If primary database fails observer initiates failover to target standby database
  • Observer checks if standby database can still see primary database before initiating failover
  • Performance impact of observer process on primary / standby is minimal
flashback database introduction
Flashback DatabaseIntroduction
  • Introduced in Oracle 10.1
  • Uses past block images to back out changes to a database
  • Allows database to be recovered to a previous time to correct problems caused by:
    • logical data corruptions
    • user errors
  • Amount of time required to flashback a database is proportional to how far back database must be reverted
  • Time to restore and recover entire database could be much longer
flashback database introduction13
Flashback DatabaseIntroduction
  • During normal database operation, Oracle occasionally logs past block images in flashback logs
  • Flashback logs are
    • written sequentially
    • not archived
  • Oracle automatically creates, resizes and deletes flashback logs in the flash recovery area
  • DBA should be aware of flashback logs
    • To monitor performance
    • To decide how much space to allocate to flash recovery area
flashback database flashing back
Flashback DatabaseFlashing Back
  • Before images are used to restore database to a point in the past
    • Forward recovery is then used to bring the database to a consistent state
  • Oracle returns datafiles to previous point in time
    • Not auxiliary files such as initialization parameter files
flashback database applications
Flashback DatabaseApplications
  • Flashback recovery of database to earlier SCN
    • Testing
    • Application / User errors
  • Recovery through resetlogs
  • Opening standby database with write access
  • Fast start failover
  • Automatic reinstantiation of old primary following fast start failover to standby
  • Alternative to delayed redo application for physical or logical standby databases
flashback database what do we already know
Flashback DatabaseWhat do we already know?
  • Introduced in Oracle 10.1
  • Requires flash recovery area
  • Maintains before image logs for block changes
  • Records are appended to flashback logs
  • Uses RVWR background process
flashback database what don t we know
Flashback DatabaseWhat don't we know?
  • Are index blocks logged?
  • Is undo logged?
  • Is temporary segments logged?
  • What happens when a segment is deleted
  • Is a block logged every time it is changed?
  • If not, how does Oracle know?
  • What when an object leaves the buffer cache
  • Is there any control structure
  • What about multiple block sizes?
  • How does it work in RAC?
  • What about contention - latches?
  • Undocumented parameters?
  • When is flashback overwritten?
flash recovery area prerequisites
Flash Recovery AreaPrerequisites
  • Archiving must be enabled
  • Flash recovery area must be configured using
    • DB_RECOVERY_FILE_DEST_SIZE - size of flashback recovery area in bytes
    • DB_RECOVERY_FILE_DEST - location of flashback recovery area
  • For example:

SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 10G;

SQL> ALTER SYSTEM SET db_recovery_file_dest = '/oradata/recovery';

flashback database parameters
Flashback DatabaseParameters
  • One supported parameter:
    • DB_FLASHBACK_RETENTION_TARGET
  • Specifies upper limit on how far back in time database may be flashed back
  • Specified in minutes
  • Default value is 1440 minutes (24 hours)
  • Affects number of flashback logs retained in flash recovery area
flashback database configuration
Flashback DatabaseConfiguration
  • To enable flashback logging database must be mounted but not open

SQL> STARTUP MOUNTSQL> ALTER DATABASE FLASHBACK ON;SQL> ALTER DATABASE OPEN;

  • To disable flashback logging use:

SQL> ALTER DATABASE FLASHBACK OFF;

  • To check if flashback is currently enabled:

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON------------YES

flashback database system change numbers and times
Flashback DatabaseSystem Change Numbers and Times
  • To check current SCN use:

SQL> SELECT current_scn FROM v$database;

  • To check oldest SCN that can be flashed back to use:

SQL> SELECT oldest_flashback_scn FROM v$flashback_database_log;

  • To check oldest time that can be flashed back to use:

SQL> ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SQL> SELECT oldest_flashback_time FROM v$flashback_database_log;

flashback database operation
Flashback DatabaseOperation
  • To flashback the database use the following syntax:

SQL> FLASHBACK [ STANDBY ] DATABASE [ database ] { TO { { SCN | TIMESTAMP } expr | RESTORE POINT restore_point } | TO BEFORE { SCN | TIMESTAMP } expr | RESETLOGS} };

  • Database must be mounted and not open to flashback
  • For example

SQL> SHUTDOWN IMMEDIATESQL> STARTUP MOUNTSQL> FLASHBACK DATABASE TO SCN 461918;

Flashback complete.

SQL> ALTER DATABASE OPEN READ ONLY;

SQL> ALTER DATABASE OPEN RESETLOGS

flashback database restrictions
Flashback DatabaseRestrictions
  • Cannot flash back to an SCN ahead of the current SCN
  • Cannot flash back to a time in the future
  • Database must be opened with read write access
    • Cannot open read only
  • Database must be opened with RESETLOGS
  • Cannot flash back if datafile resized (shrunk) during flashback period
flashback database dynamic performance views
Flashback DatabaseDynamic Performance Views
  • V$FLASHBACK_DATABASE_LOG
  • V$FLASHBACK_DATABASE_STAT
flashback log files location and naming
Flashback Log FilesLocation and Naming
  • Stored in Flash Recovery Area (mandatory)
    • Subdirectory is <database_name>/flashback
  • Use Oracle-Managed Files (OMF) (mandatory)
  • For example
    • o1_mf_3504ofnh_.flb
    • o1_mf_350g3r24_.flb
    • o1_mf_350jl666_.flb
  • Used sequentially
    • Can be reused
  • Generated when required
    • Dropped when space required in flash recovery area
flashback log files sizing
Flashback Log FilesSizing
  • Flashback log size same as database block size
    • e.g. 4096 or 8192
  • Initial size is 1001 x block size
    • determined by
      • _flashback_log_size (defaults to 1000)
      • additional block for file header
    • e.g
      • 1001 x 8192 = 8200192 bytes
  • Subsequent size reduces to 3989504
    • probably determined by
      • size of flashback generation buffer (3981204)
      • additional block for file header
    • note there is a rounding error here
flashback log files controlfile dumps
Flashback Log FilesControlfile Dumps

SQL> ALTER SESSION SET EVENTS 'immediate trace name controlf level 3';

*******************************************************************FLASHBACK LOGFILE RECORDS*******************************************************************FLASHBACK LOG FILE #4: (name #12) /oradata/recovery/PROD/flashback/o1_mf_350kw47d_.flbThread 1 flashback log links: forward: 5 backward: 3size: 486 seq: 4 bsz: 8192 nab: 0x1e7 flg: 0x0 magic: 3 dup: 1Low scn: 0x0000.00071169 05/20/2007 14:05:08High scn: 0x0000.00071980 05/02/2007 15:16:48

FLASHBACK LOG FILE #5: (name #13) /oradata/recovery/PROD/flashback/o1_mf_350p2jz0_.flbThread 1 flashback log links: forward: 6 backward: 4size: 486 seq: 5 bsz: 8192 nab: 0x1e7 flg: 0x0 magic: 5 dup: 1Low scn: 0x0000.00071980 05/20/2007 15:16:48High scn: 0x0000.0007247b 05/02/2007 16:43:13

FLASHBACK LOG FILE #6: (name #14) /oradata/recovery/PROD/flashback/o1_mf_350v4kz1_.flbThread 1 flashback log links: forward: 1 backward: 5size: 486 seq: 4 bsz: 8192 nab: 0xffffffff flg: 0x0 magic: 4 dup: 1Low scn: 0x0000.0007247b 05/20/2007 16:43:13High scn: 0xffff.ffffffff 05/02/2007 00:00:00

Current Logfile

flashback logging recovery writer process
Flashback LoggingRecovery Writer Process
  • Flashback uses the recovery writer (RVWR) background process
    • Copies flashback blocks from flashback generation buffer to flashback logs

SELECT descriptionFROM v$bgprocessWHERE name = 'RVWR';

DESCRIPTION---------------Recovery Writer

  • Checks for records in flashback generation buffer every 3 seconds
    • Waits on rdbms ipc message
  • In Linux records written to disk using pwrite64
    • Multi block writes (8192 byte records)
flashback logging recovery writer process31
Flashback LoggingRecovery Writer Process
  • Recovery process structure is linked into SGA global area

SELECT addr FROM x$ksbdpWHERE ksbdpnam = 'RVWR';

ADDR---------------2000D860

SELECT ksmfsnam,ksmfstyp FROM x$ksmfsvWHERE ksmfsadr = '2000D860';

KSMFSNAM KSMFSTYP-------- -------krfwrp_ ksbdp

  • ksbdp structure for RVWR background process is krfwrp_
flashback generation buffer sizing
Flashback Generation BufferSizing
  • Flashback uses a flashback generation buffer
  • Size of generation buffer is recorded in V$SGASTAT
  • Size is determined by _flashback_generation_buffer_size
    • defaults to 4194304
    • To verify size of buffer use

SELECT bytes FROM v$sgastatWHERE pool = 'shared pool'AND name = 'flashback generation buff';

BYTES----------4194304

flashback generation buffer granules
Flashback Generation BufferGranules
  • Flashback generation buffer appears to be limited to a single granule
  • If granule size is less than _flashback_generation_buffer_size
    • buffer size will be rounded down
  • For example for a 4mb granule size:

SELECT bytes FROM v$sgastatWHERE pool = 'shared pool'AND name = 'flashback generation buff';

BYTES----------3981204

  • Granule size can be controlled using _ksmg_granule_size
flashback generation buffer location
Flashback Generation BufferLocation
  • To determine location of flashback generation buffer use:

ALTER SYSTEM SET EVENTS 'immediate trace name global_area level 2';

ksbdp krfwrp_ [2000D860, 2000D88C) = 0000007B 2AE1C924 00000000 00000000 ...Dump of memory from 0x2000D870 to 0x2000D88C2000D870 52575652 00000200 00006723 0005A080 [RVWR....#g......]2000D880 00000001 199DC5EA 00040081 KSBDPPRO = 0X2AE1C924 KSBDPSER = 1 KSBDPERR = 0 KSBDPNAM = 'RVWR' KSBDPFLG = 2

Location of RVWR background process

krfwb krfwbf_ [2000D8DC 2000D970) = 000001E5 00002000 003C7288 00001FE8 ...Dump of memory from 0x2000D8CC to 0x2000D9F02000D8C0 27834200 2000D8D0 003CBD94 000001E6 000001E6 000000032000D8E0 29A1B71C 00000002 00037D60 00000001 etc..

Location of flashback generation buffer

  • In this 32 bit example location is 0x27834200
flashback generation buffer shared pool reserved area
Flashback Generation BufferShared Pool Reserved Area
  • Size of flashback generation buffer is affected by shared pool reserved area
  • By default 5% of each granule is allocated to shared pool reserved area
  • For example our flashback generation buffer is 0x27834200
  • Granule size is 4MB

SELECT ksmchptr,ksmchsiz FROM x$ksmsprWHERE ksmchptr >= '27800000'AND ksmchptr < '27C00000';

SELECT MAX(baseaddr), gransize FROM x$ksmgeWHERE baseaddr <= '27834200';

KSMCHPTR KSMCHSIZ-----------------------27800038 2427800050 21288827833FE8 24

MAX(BASEADDR) GRANSIZE-------------------------27800000 4194304

flashback logging latches
Flashback LoggingLatches
  • The following latches are used by flashback logging
    • flashback allocation
    • flashback mapping
    • flashback copy
    • flashback sync request
    • flashback FBA barrier
    • flashback SCN barrier
    • hint flashback FBA barrier
    • flashback hint SCN barrier
  • By default each latch only has one child except
    • flashback copy latch
    • maximum number of copy latches may be determined

by _flashback_copy_latches

flashback log files dumps
Flashback Log FilesDumps
  • The following dumps are undocumented
  • All flashback records for a thread can be dumped using:

SQL> ALTER SYSTEM DUMP FLASHBACK THREAD <thread_number>

  • In a single instance database thread_number will always be 1
  • All flashback records for a specific flashback logfile can be dumped using

SQL> ALTER SYSTEM DUMP FLASHBACK LOGFILE <log_file_number>

  • Flashback logfiles are numbered from 1 upwards
flashback log files dumps38
Flashback Log FilesDumps
  • All flashback records for a specific record type can be dumped using:

SQL> ALTER SYSTEM DUMP FLASHBACK LOGFILE <log_file_number> TYPE <type>;

  • All flashback records for a specific database block number can be dumped using:

SQL> ALTER SYSTEM DUMP FLASHBACK LOGFILE <log_file_number> DBA <absolute_file_number> . <block_number>;

  • By default block dumps etc are included in the dump file
  • To dump a summary of records in the flashback log use:

SQL> ALTER SYSTEM DUMP FLASHBACK LOGFILE <log_file_number> LOGICAL;

flashback log files dumps39
Flashback Log FilesDumps
  • Example of header

DUMP OF FLASHBACK LOG FILE 9 FILE HEADER: Compatibility Vsn = 169869568=0xa200100 Db ID=308670124=0x1265eeac, Db Name='FLASH' Activation ID=308689068=0x126638ac Control Seq=318=0x13e, File size=972=0x3cc File Number=9, Blksiz=8192, File Type=8 FLASH BACK FLASHBACK HEADER: Flashback Block Header: Seq: 9 Block: 1 Cks: 0x22b Flag: 0x1 Lst: 0 description:"Thread 0001, Seq# 0000000009, SCN 0x00000003a2d7" thread: 1 seq: 9 version 0 nab: 0x3cd reset logs count: 0x25102f2c scn: 0x0000.00000001 formatted blocks: 972 usable blocks: 972 magic: 5 previous magic: 0 flags: 0x0 Low scn: 0x0000.0003a2d7 05/07/2007 10:31:48 High scn: 0x0000.000401d3 05/26/2007 16:59:06 Last Marker: fba: (lno 0 thr 0 seq 0 bno 0 bof 0)

flashback database dumps
Flashback DatabaseDumps
  • Example of block image

**** Record at fba: (lno 9 thr 1 seq 9 bno 966 bof 692) **** RECORD HEADER: Type: 1 (Block Image) Size: 28 RECORD DATA (Block Image): file#: 1 rdba: 0x00406efc Next scn: 0x0000.00000000 [0.0] Flag: 0x0 Block Size: 8192 BLOCK IMAGE: buffer rdba: 0x00406efc scn: 0x0000.00034d8e seq: 0x01 flg: 0x06 tail: 0x4d8e0601 frmt: 0x02 chkval: 0xf52b type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0xB56CDC00 to 0xB56CFC00B56CDC00 0000A206 00406EFC 00034D8E 06010000 [.....n@..M......]B56CDC10 0000F52B 00000001 0000023D 00034D8C [+.......=....M..]

<hex block dump>

.....

<symbolic block dump>

flashback records record types
Flashback RecordsRecord Types
  • Every flashback record has a type
flashback logging rvwr background process dumps
Flashback LoggingRVWR Background Process Dumps
  • Some additional RVWR background process dumps can be executed from ORADEBUG
  • Dumping session must attach to RVWR process
  • Either use operating system process id

$ ps -ef | grep rvwr | grep -v greporacle 11055 1 0 16:04 ? 00:00:00 ora_rvwr_PROD

SQL> ORADEBUG SETOSPID 11055;

  • Or use Oracle process id

SQL> SELECT pid FROM v$process WHERE addr IN( SELECT paddr FROM v$bgprocess WHERE name = 'RVWR');

PID---20

SQL> ORADEBUG SETORAPID 20;

flashback logging rvwr background process dumps43
Flashback LoggingRVWR Background Process Dumps
  • To dump flashback generation status use:

SQL> ORADEBUG DUMP FLASHBACK_GEN 1

  • To dump flashback logfile headers use:

SQL> ORADEBUG DUMP FBHDR 1

  • To dump all logical flashback records in the current flashback incarnation use:

SQL> ORADEBUG DUMP FBINC 1

  • To include before images in the above dump use:

SQL> ORADEBUG DUMP FBINC 2

  • To dump the last 2000 flashback records use:

SQL> ORADEBUG DUMP FBTAIL 1

flashback log physical structure

STOP

Flashback LogPhysical Structure
  • Block size determined by db_block_size parameter
  • Block 0 contains file header
  • Remaining blocks have 16 byte block header Includes check sum

Block Header

FileHeader

flashback records logical structure
Flashback RecordsLogical Structure
  • Added sequentially to flashback logs
  • Consists of a header and an optional body
  • If present body is written first followed by header
  • For all record types
    • Header includes type and length
    • Structure is read backwards
  • Logical records can cross physical record boundaries
flashback records logical structure46

STOP

Flashback RecordsLogical Structure

Body

Record# 1

Header

Body

Record# 2

Header

Body

Record# 3

Header

Header

Record# 4

Body

Record# 5

Header

flashback records physiological structure

STOP

Flashback RecordsPhysiological Structure

File Header

FlashbackRecords

Empty Space

flashback records block images
Flashback RecordsBlock Images
  • For block images
    • Body is a copy of the data block
    • Used for data blocks, undo blocks
    • Not compressed
    • Flashback records are always larger than single block
    • Include 28 byte header
  • Common block types appearing as block images include
    • Data and index blocks (trans data)
    • Segment headers
    • Undo headers
    • Undo blocks (manual and automatic)
    • Local tablespace bitmap blocks
    • Automatic segment space management bitmap blocks
flashback logs flashback log tail
Flashback LogsFlashback log tail
  • New flashback records are always appended beyond the flashback log tail
  • Flashback database commands start at the flashback log tail and work forwards
  • To check flashback log tail use:

SQL> ALTER SESSION SET EVENTS 'immediate trace name controlf level 2';

  • For example:

****************************************************************CHECKPOINT PROGRESS RECORDS****************************************************************THREAD #1 - status:0x2 flags:0x0 dirty:15low cache rba:(0xd.1f33.0) on disk rba:(0xd.1f42.0)on disk scn: 0x0000.0004087e 05/26/2007 18:11:01resetlogs scn: 0x0000.00000001 05/05/2007 23:07:24heartbeat: 623592856 mount id: 310450827Flashback log tail log# 1 thread# 1 seq 10 block 309 byte 0

flashback logs flashback log tail50
Flashback Logs Flashback log tail
  • Current pointer is also maintained in SGA. For example:

SQL> ALTER SESSION SET EVENTS 'immediate trace name global_area level 2';

krfwb krfwbf_ [2000D8BC, 2000D9F0) = 000001E5 00002000 003C7288 00001FE8Dump of memory from 0x2000D8CC to 0x2000D9F02000D8C0 28434200 [.BC(]2000D8D0 003CBD94 000001E6 000001E6 00000003 [..<.............]2000D8E0 2A61B71C 00000002 003C7288 00000001 [..a*.....r<.....]2000D8F0 00000001 002A21F0 00000002 00000000 [.....!*.........]2000D900 00000001 00000000 00000000 00000002 [................]2000D910 00000000 002A01D4 003C6C3C 00000001 [......*.<l<.....]2000D920 00000000 00000000 00000002 00000001 [................]2000D930 00000000 00000000 00000152 00000002 [........R.......]2000D940 0000000A 00000135 00000001 000002B4 [....5...........]2000D950 000001E5 00000000 00000000 00000000 [................]2000D960 00000000 24ACC246 00000030 00000001 [....F..$0.......]2000D970 00000001 0000000A 00000009 000003E8 [................]2000D980 00002000 00000000 2A4976FC 2BBBA220 [. .......vI* ..+]2000D990 2A567EEC 00000047 0000000A 00000001 [.~V*G...........]2000D9A0 00000000 00000001 00000009 00000000 [................]2000D9B0 00000001 00000080 00000800 00000000 [................]2000D9C0 00000000 00000000 00000000 00000000 [................]2000D9D0 00000001 00000000 00000000 00000000 [................]2000D9E0 001E5ECA 002A2050 00000000 00000000 [.^..P *.........]

Sequence Number0xA = 10

Block Number0x135=309

Log Number0x1=1

flashback logging conclusions
Flashback LoggingConclusions
  • Very similar design to LGWR
    • Changes initially written to memory buffer
    • RVWR subsequently flushes flashback records to disk
  • Requires memory buffer
    • Defaults to 4MB
    • Efficient multi-block disk writes
flashback logging conclusions52
Flashback LoggingConclusions
  • All blocks are logged when they first become dirty
    • Includes data, indexes, undo, segment headers, bitmaps
    • Subsequent changes not necessarily logged
    • No separate structure identified so probably uses flags in buffer headers to monitor which blocks have been logged
    • Flag may be reset when DBWR flushes dirty block to disk
  • Amount of flashback redo log generated roughly equivalent to value of physical writes statistics
thank you for listening
Thank you for listening

info@juliandyke.com