Showing posts with label oracle. Show all posts
Showing posts with label oracle. Show all posts

Friday, January 29, 2016

Oracle Hang Management

Hang Manager (HM) has existed since 10.2.0.1. The main goal of Hang Manager is to reliably detect, and if hang resolution is enabled, resolve hangs in a timely manner. Over various releases, Hang Manager has been enhanced along with the wait event infrastructure on which it relies. However, it is only in 11.2.0.2 that Hang Manager actually resolves any hangs by terminating sessions and/or processes. This is the default operation in 11.2.0.2. Hang Manager will not terminate an instance unless the resolution scope, which is controlled by the initialization parameter _HANG_RESOLUTION_SCOPE, is set to INSTANCE. By default, this parameter is set to PROCESS.

Hang Manager is only active when RAC is enabled, that is, CLUSTER_DATABASE = TRUE. Also, the parameters _HANG_DETECTION_ENABLED and _HANG_RESOLUTION_SCOPE control hang detection and resolution respectively. Currently, Hang Manager does not operate on a non-RAC enabled instance.

DIA0 process is responsible for hang detection and deadlock resolution.

NOT TO BE CONFUSED WITH 
DIAG process: diagnosability process, performs diagnostic dumps and executes oradebug commands

When DIA0 process kills off a process for hang management,
ORA 32701 is thrown, and this indicates that "Hang Management" is in use.

Hang can occur due to variety of reasons:
 - application related
 - resource related
 - internal blockage in Oracle kernel
 - other reason

'Resolvable Hangs' int the incident trace file indicate one or more hangs that were found and identify the final blocking session and instance on which they occurred. If the current hang resolution state is 'PROCESS',
any hangs requiring session or process termination are automatically resolved by DIA0 process

 >11.2.0.2, Hang Management will try to resolve the hang by terminating the ultimate blocker

If 32701 occured, it is important to determine the cause of the hang.

In the example below, the DIA0 terminated session sid:3768 with serial # 19475 (ospid:73677)
 for terminating blocker process (ospid: 73677 sid: 3768 ser#: 19475) of hang with


-----------------------------------------------------------------------------------------
ORA-32701: Possible hangs up to hang ID=16 detected
Incident details in: /ade/b/3518352270/oracle/log/diag/rdbms/mainrac/mainrac1/incident/incdir_81/mainrac1_dia0_20207_i81.trc
DIA0 requesting termination of session sid:44 with serial # 1 (ospid:5964) on instance 2
  due to a GLOBAL, HIGH confidence hang with ID=16.
  Hang Resolution Reason: Although hangs of this root type are typically
  self-resolving, the previously ignored hang was resolved.
DIA0 could not resolve a GLOBAL, HIGH confidence hang with ID=16.
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
Errors in file /u01/app/oracle/diag/rdbms/dm02cdb1/orcl2/trace/orcl2_dia0_87406.trc  (incident=31458) (PDBNAME=CDB$ROOT):
ORA-32701: Possible hangs up to hang ID=86 detected
Incident details in: /u01/app/oracle/diag/rdbms/dm02cdb1/orcl2/incident/incdir_31458/orcl2_dia0_87406_i31458.trc
Thu Dec 31 21:59:43 2015
Sweep [inc][31458]: completed
Sweep [inc2][31458]: completed
Thu Dec 31 21:59:44 2015
DIA0 terminating blocker (ospid: 73677 sid: 3768 ser#: 19475) of hang with ID = 86
    requested by master DIA0 process on instance 2
    Hang Resolution Reason: Previously ignored hang - hang did not self-resolve
   as was expected.  Automatic hang resolution will be performed.
    by terminating session sid:3768 with serial # 19475 (ospid:73677)
DIA0 successfully terminated session sid:3768 with serial # 19475 (ospid:73677) with status 0.
DIA0 successfully resolved a GLOBAL, HIGH confidence hang with ID=86.

-----------------------------------------------------------------------------------------
*** 2015-12-31 21:59:40.213
Resolvable Hangs in the System
                     Root       Chain Total               Hang              
  Hang Hang          Inst Root  #hung #hung  Hang   Hang  Resolution        
    ID Type Status   Num  Sess   Sess  Sess  Conf   Span  Action            
 ----- ---- -------- ---- ----- ----- ----- ------ ------ -------------------
    86 HANG RSLNPEND    2  3768     3     3   HIGH GLOBAL Terminate Process 
 Hang Resolution Reason: Previously ignored hang - hang did not self-resolve
    as was expected.  Automatic hang resolution will be performed.

  Inst  Sess   Ser             Proc  Wait    Wait
   Num    ID    Num      OSPID  Name Time(s) Event
  ----- ------ ----- --------- ----- ------- -----
        PDBID PDBNm          
        ----- ---------------
      1   4528 13156     28552    FG     704 library cache lock
           11 MODERNIZATION_DEV_12
      1   2770 52684     22975    FG      50 library cache pin
           11 MODERNIZATION_DEV_12
      2   3768 19475     73677    FG    1183 not in wait
           11 MODERNIZATION_DEV_12

Dumping process info of pid[65.73677] (sid:3768, ser#:19475)
    requested by master DIA0 process on instance 2.

-----------------------------------------------------------------------------------------

*** 2015-12-31 21:59:40.213
[TOC00004]
Process diagnostic dump for oracle@dm02dbadm02.ttiinc.com, OS id=73677,
pid: 65, proc_ser: 187, sid: 3768, sess_ser: 19475
-------------------------------------------------------------------------------
os thread scheduling delay history: (sampling every 1.000000 secs)
  0.000000 secs at [ 22:59:39 ]
    NOTE: scheduling delay has not been sampled for 0.476107 secs
  0.000000 secs from [ 22:59:35 - 22:59:40 ], 5 sec avg
  0.000000 secs from [ 22:58:40 - 22:59:40 ], 1 min avg
  0.000000 secs from [ 22:54:40 - 22:59:40 ], 5 min avg

*** 2015-12-31 21:59:41.679
loadavg : 2.31 2.47 2.23
System user time: 0.03 sys time: 0.00 context switch: 17233
Memory (Avail / Total) = 75526.99M / 257965.57M
Swap (Avail / Total) = 23089.19M /  24576.00M
F S UID         PID   PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 R oracle    73677      1 99  80   0 - 19898980 ?   21:39 ?        00:19:45 oracleorcl2 (LOCAL=NO)





Friday, April 18, 2014

Enable Fast Start Failover using DGMGRL Oracle Data Guard Broker

Enable FSFO and starting the Observer

An Observer can be any system with access to the databases in DG configuration and at least Oracle Client installed.

                1. Ensure that SRL's are configured on both the PRIMARY DB and 
                    the STANDBY DB's
               
                2. Ensure that the LogXptMode is set to SYNC
               
                3. Set the FastStartFailoverTarget property of PRIMARY to desired 
                    STANDBY DB.
               
                   edit database orcl1 set property 
                                      'FastStartFailoverTarget'='orcl2';
               
                4. Upgrade protection Mode to MaxAvailability if necessary.
               
                   edit configuration set protection mode as MAxAvailability;
               
                5. Enable Flashback on Primary and Standby DB's
                               
                   Also set DB_FLASHBACK_RETENTION_TARGET to appropriate value.
                   This parameter retains the flashback logs such that the database can be 
                   flashed back to the specified interval of time.
               
                6. Start Observer
                               
                    Connect to standby DB using DGMGRL and issue:
                               
                    start observer;
                               
                7. Enable Fast start Failover

                    enable FAST_START failover;
               
                    This statement creates FSFP process
                               
                   2014-03-12 02:51:34.451      DMON: Creating process FSFP
           2014-03-12 02:51:37.549      FSFP: Process started
           2014-03-12 02:51:38.550      DMON: FSFP successfully started

                         DGMGRL> show configuration
      
              Configuration - dg_config
      
              Protection Mode: MaxAvailability
              Databases:
              orcl1 - Primary database
              orcl2 - (*) Physical standby database
      
              Fast-Start Failover: ENABLED
      
              Configuration Status:

              SUCCESS

Oracle Data Guard Broker Configuration management

Configuration management

1.       Change parameter, database property using DGMGRL

We will change the property 'StandbyFileManagement' for database ORCL2 to AUTO from MANUAL which is default setting for the parameter STANDBY_FILE_MANAGEMENT.

                edit database orcl2 set property 'StandbyFileManagement'='AUTO';
      
      
       DGMGRL> show database verbose orcl2
      
       Database - orcl2
      
         Role:            PHYSICAL STANDBY
         Intended State:  APPLY-ON
         Transport Lag:   0 seconds
         Apply Lag:       0 seconds
         Real Time Query: OFF
         Instance(s):
           orcl2
      
         Properties:
           DGConnectIdentifier             = 'orcl2'
           ObserverConnectIdentifier       = ''
           LogXptMode                      = 'ASYNC'
           DelayMins                       = '0'
           Binding                         = 'OPTIONAL'
           MaxFailure                      = '0'
           MaxConnections                  = '1'
           ReopenSecs                      = '300'
           NetTimeout                      = '30'
           RedoCompression                 = 'DISABLE'
           LogShipping                     = 'ON'
           PreferredApplyInstance          = ''
           ApplyInstanceTimeout            = '0'
           ApplyParallel                   = 'AUTO'
           StandbyFileManagement           = 'AUTO'
           ArchiveLagTarget                = '0'
           LogArchiveMaxProcesses          = '4'
           LogArchiveMinSucceedDest        = '1'
           DbFileNameConvert               = 'orcl1, orcl2'
           LogFileNameConvert              = 'orcl1, orcl2'
           FastStartFailoverTarget         = ''
           StatusReport                    = '(monitor)'
           InconsistentProperties          = '(monitor)'
           InconsistentLogXptProps         = '(monitor)'
           SendQEntries                    = '(monitor)'
           LogXptStatus                    = '(monitor)'
           RecvQEntries                    = '(monitor)'
           HostName                        = 'dg3'
           SidName                         = 'orcl2'
           StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg3)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl2_DGMGRL)(INSTANCE_NAME=orcl2)(SERVER=DEDICATED)))'
           StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
           AlternateLocation               = ''
           LogArchiveTrace                 = '0'
           LogArchiveFormat                = '%t_%s_%r.dbf'
           TopWaitEvents                   = '(monitor)'
      
       Database Status:
       SUCCESS

We can change the ‘LogXptMode’ property as below. This will update the LOG_ARCHIVE_DEST_n parameter appropriately.
      
       edit database [orcl1|orcl2] set property logxptmode=SYNC;

2.       Change the broker configuration file location

The location of the broker configuration files is determined by the parameters DG_BROKER_CONFIG_FILEn (where n in {1,2}).

Default location is $ORACLE_HOME/dbs
Default names of configuration files are:  dr{1|2}<db_unique_name>             

CHANGING CONFIG FILE LOCATION:
               
The location of the configuration file can only be changed when DMON is not running.
                               
·         set DG_BROKER_START=FALSE
·         alter system set DG_BROKER_CONFIG_FILEn=new loc;
·         manually move files at OS level to new location
                                IF old/new location is ASM, use DBMS_FILE_TRANSFER.COPY_FILE
·         start DMON process

3.       Change DB STATEs

Turn off redo apply,

                edit database orcl2 set STATE='APPLY-OFF';

        Activate active DG aka REAL TIME QUERY,
               
                edit database orcl2 set STATE='APPLY-OFF';

       SQL> alter database open;

       edit database orcl2 set STATE='APPLY-ON';
      
       DGMGRL> show database orcl2
      
       Database - orcl2
      
         Role:            PHYSICAL STANDBY
         Intended State:  APPLY-ON
         Transport Lag:   0 seconds
         Apply Lag:       0 seconds
         Real Time Query: ON
         Instance(s):
           orcl2
      
       Database Status:
       SUCCESS

4.       Change protection mode

a)      Configure the SRL if necessary
b)      Set LogXptMode aptly
                               
edit database orcl1 set property 'LogXptMode'='SYNC';
edit database orcl2 set property 'LogXptMode'='SYNC';
                               
c)       now change the mode for the ENTIRE CONFIGURATION
               
                edit configuration set protection mode as MaxAvailability;
                               
         Effects:
               
                In the STANDBY alert log:
                               
                RFS[6]: Assigned to RFS process 7356
RFS[6]: Identified database type as 'physical standby': Client is LGWR SYNC pid 4753
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[6]: Selected log 4 for thread 1 sequence 65 dbid 1224670660 branch 840221191
Recovery of Online Redo Log: Thread 1 Group 4 Seq 65 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/orcl2/srl04.log
Wed Mar 12 01:05:58 2014
RFS[7]: Assigned to RFS process 7366
RFS[7]: Identified database type as 'physical standby': Client is ARCH pid 4817

On the PRIMARY Site

SQL> select dest_id, dest_name, status, affirm, transmit_mode, process from v$archive_dest where status != 'INACTIVE';

DEST_ID DEST_NAME                      STATUS    AFF TRANSMIT_MOD PROCESS
---------- ------------------------------ --------- --- ------------ ----------
1 LOG_ARCHIVE_DEST_1             VALID     NO  SYNCHRONOUS  ARCH
2 LOG_ARCHIVE_DEST_2             VALID     YES PARALLELSYNC LGWR

On STANDBY site :

SQL> select dest_id, dest_name, status, affirm, transmit_mode, process from v$archive_dest where status != 'INACTIVE';

DEST_ID DEST_NAME                      STATUS    AFF TRANSMIT_MOD PROCESS
---------- ------------------------------ --------- --- ------------ ----------
1 LOG_ARCHIVE_DEST_1             VALID     NO  SYNCHRONOUS  ARCH
2 LOG_ARCHIVE_DEST_2             VALID     NO  ASYNCHRONOUS LGWR

32 STANDBY_ARCHIVE_DEST           VALID     YES SYNCHRONOUS  RFS

Thursday, April 17, 2014

Oracle wait events

There are various events that might induce waiting time for a database operation.
These events are known are wait events.

These wait events are classified into various wait classes based on the type of operation they affect.

The wait events can be observed using the following views:

                v$SESSION_EVENT
                v$SESSION_WAIT
                v$SYSTEM_EVENT
                v$EVENT_NAME

Following are descriptions of some important wait events:

db sequential file reads

  This wait event occurs when Oracle process wants to read a block that is not currently in the Buffer cache, and it is waiting to read the block into SGA from disk.
  A sequential read is generally a single-block read. Sequential read reads data into contiguous memory in SGA.
 
  Causes:
   
    - use of unselective index
    - fragmented index
    - High I/O on particular disk
    - bad application design
 
  Action:
   
    Faster data block identification, faster disk to memory transfer, caching of data in memory will decrease the occurrence and intensity of this wait event.
   
    Attention needs to be paid to following aspects:
   
    - check that right index is being used to access the table
    - check the column order of index with the WHERE clause in SQL
    - use partitioning to reduce the number of blocks visited
    - statistics should be up to date
    - relocate HOT datafiles
    - consider using KEEP pool to cache frequently accessed indexes/table
    - check the execution plans to ascertain the access paths, join methods   
 
db file scattered reads

  This wait happens when a session is waiting for multi-block IO to complete.
  A scattered read reads multiple blocks and scatters them into different buffers in SGA.
  Typically happens during Full Table Scans and Index Fast Full Scans.
  The number of consecutive blocks read at one time is determined by parameter,    
   DB_FILE_MULTIBLOCK_READ_COUNT.
 
  Causes:
   
    - Full Table Scans
    - Index Fast Full Scans
 
  Actions:
 
    - If an application has been running fine for some time, and there has been no code 
      change, and this wait event starts to show up, check if one or more indexes have
      been dropped
    - optimize multiblock read count by setting appropriate 
       DB_FILE_MULTIBLOCK_READ_COUNT
    - Consider caching frequently accessed indexes/tables in KEEP pool
    - use partitioning to reduce the number of blocks visited
    - optimize SQL with the goal to reduce the number of physical and logical reads
    - check if an Index range scan or unique scan can be done instead of FTS or IFFS
    - make sure statistics are up to date

buffer busy waits
 
  This wait happens when a sessions wants to access a block in Buffer cache but cant because the block is busy.
  A buffer must be PINNED before it can be read/modified. Only one process can pin a buffer at one time.
  This event represents read/read, read/write and write/write contention.
  The reason code is present in the P3 column of v$SESSION_WAIT.
 
  Causes:
 
    - another session is reading the block into the buffer
    - another session holds the block in an incompatible mode to our request
   
    It can get intensified if
    - the block size is large, thus block contains a high number of rows
    - lot of sessions are asking for the same blocks
 
  Actions:
 
    Main method to reduce the buffer busy waits is to reduce the total IO on system
   
    Data Blocks
     
      - eliminate hot blocks from application (probably by providing redundancy)
      - check for repeatedly scanned, unselective indexes
      - try rebuilding object with higher PCTFREE so as to reduce the 
         number of rows per block
      - use automatic segment space management
   
    Segment Header
   
      - increase the number of FREELISTs
      - use automatic segment space management
   
    Undo Blocks
   
      - increase number of rollback segments
      - use automatic undo management
   
   
log file parallel write

  This wait is the LGWR wait that occurs when the OS writes contents of the redo buffer to the online log files.
  LGWR does a group write for multiple commits/rollbacks, hence, parallel write.
 
  Causes:
 
    - large amount of redo being generated
    - slow IO subsystem
   
  Actions:
 
    - reduce amount of redo being generated
    - don't leave tablespaces in HOT backup mode longer than necessary
    - use faster disks for redo log files
    - ensure that disks holding the archived logs and those holding online redo logs are 
       separate to reduce LGWR, ARCn contention
    - consider using NOLOGGING option
 
log file sync

  This wait is logged against the oracle foreground process that is waiting for COMMIT/ROLLBACK to complete
 
  Actions:
 
    - reduce number of commits by batching transactions, so that there are fewer 
       COMMIT operations

free buffer waits

  This wait happens when we are waiting for free buffer in the buffer cache but there are none as there are too many dirty buffers
 
  Causes:
   
    - either buffer cache is too small or DBWn is too slow in flushing bocks to disk
    - DBWn is unable to keep up to the write requests
    - Checkpoints are happening too fast. This in turn could be due to
       
        - high database activity
        - small Online Redo Log file size
       
    - Large sorts and FTS's are filling the buffer cache faster than 
      DBWn is able to write to disk
    - if number of blocks that need to be written to disk is greater than 
      number of blocks that DBWn can write in one batch
 
  Actions:
 
    - reduce checkpoint frequency, increase size of redo logfiles
    - check the V$DB_CACHE_ADVICE to optimally size the DB buffer
    - ensure hotspots don't exists by spreading datafiles over disks and disk controllers
    - pre-sorting, reorganization of data can help
   
enqueue waits

  This wait events indicates a wait for a lock that in held by other session/s in a mode incompatible to the requested mode.
  There are over 60 types of enqueue waits. Can be viewed through V$ENQUEUE_STAT.
  
Major among them are:
 
    TX - transaction lock
   
      This indicates contention for row level lock. It is related to buffer busy waits
     
      Causes:
     
        - when a transaction tries to update/delete rows that are currently locked by 
           another session
        - usually an application issue (bad application design)
     
      Actions:
       
        - Since this is a blocking session wait, the action is to find the blocking session/s 
          and take appropriate action
       
    TM - DML enqueue lock
   
      This is related to transaction management.
     
      Causes:
     
        - unindexed foreign key columns
     
      Actions:
       
        - index the foreign key columns

LATCH:

  Latches are lightweight mechanisms that are used to serialize access to memory structures.
  It is not possible to queue for latch - a process either gets the latch or does not.
  Various types of latches can be seen through v$LATCH view.
 
  Latches apply only to memory structures in SGA. They do not apply to database objects.
  The SGA has many latches and they exist to protect various memory structures from potential corruption by concurrent access. 
  Memory structures protected by latches include things like buffer cache, java pool and library cache.
 
  A latch is requested by process in 2 modes:
   
 Immediate mode - A process asks for latch, if it fails to acquire, the control is returned to the process
Willing to wait mode - A process asks for a latch,
                                                       if it fails to acquire,
                                                          it keeps requesting it for specified number of 
                                                          times (spinning),
                                                       if it still fails to acquire,
                                                          it sleeps, then wakes up after certain specified 
                                                          time and begins the whole process again.
 
cache buffer chain latch

  This latch is acquired when searching for data blocks.
  Buffer cache is a chain of blocks and each chain is protected by a latch while it is being scanned.
 
  Causes:
   
    - when multiple sessions repeatedly access one or more blocks that are protected by
      the same child 'cache buffer chain' latch
    - sql statements with high buffer gets (logical reads) per execution
 
  Actions:
 
    - reduce logical IO rates by tuning and minimizing IO requirements of involved SQL
    - reducing number of rows per block
    - reducing block size
 
cache buffer LRU chain latch

   This latch is acquired to introduce a new block into the buffer cache based on LRU block replacement policy.
  
   Causes:
   
     - intense buffer cache activity caused by inefficient SQL
     - SQL scanning large unselective indexes, performing FTS
  
   Actions:
  
     - implement multiple buffer pools
     - increase buffer cache size
  
  
Direct path reads and direct path writes wait events are related to operations that are performed in PGA like sorting, group by operation, hash join.

direct path reads

  Direct path reads are used by oracle when reading directly into PGA bypassing SGA.
  Direct path reads are common in DSS or during heavy batch activity.
 
  Causes:
 
    - sorting operations, direct path reads usually occur in connection with 
       temporary segments
    - sql statements that require sort (order by, group by, union, distinct, rollup)
    - join methods that require sort (hash join, sort merge join)
 
  Actions:
   
    - check the sql to see if the IO can be reduced
    - set PGA_AGGREGATE_TARGET to appropriate value as advised by 
       V$PGA_TARGET_ADVICE
    - wherever possible, use Hash Join instead of Sort Merge join, and Nested Loops 
       instead of Hash Join
    - make sure optimizer selects right driving table 
 
direct path writes

  These are waits that are associated with direct write operations that write data into user's PGA to data files or to temporary segments
 
  Causes:
 
    - direct load operations ( like CTAS)
    - parallel DML operations
    - Sort IO (when a sort doesn't fit into PGA)
   
  Actions:
 
    - ensure OS asynchronous IO is configured correctly
    - ensure no disks are IO bound
   
latch free waits

  This wait indicates that the process is waiting for a latch that is currently busy.
 
  When a process is spinning, the spinning is recorded as CPU time, and when it goes to sleep, it is recorded as 'latch free wait' event.
   
library cache latch

  This latch prevents the cached sql statements and object definitions staored in the library cache within the shared pool.
  This latch must be acquired to add a statement to the library cache.
 
  This latch is to ensure that application is reusing as much SQL statement representation as possible. 
 
  Causes:
   
    - application is making heavy use of literal sql
 
  Actions:
   
    - consider increasing shared pool size, based on the shared pool advisor 
       (v$SHARED_POOL_ADVICE)
    - use bind variables wherever possible
   
shared pool latch

  This latch is used to protect critical operations when allocating and freeing memory in shared pool.
 
  Causes:
 
    - the shared pool latch waits and the library cache latch waits are generally due to intense hard parsing
 
  Actions:
 
    - avoid hard parses where possible
    - eliminate literal SQL, use bind variables
    - consider increasing shared pool size, based on the shared pool advisor 
       (v$SHARED_POOL_ADVICE)
    - try setting CURSOR_SHARING to FORCE if hard parses persist
   
row cache objects latch

  This latch comes into play when processes are attempting to access the cached data dictionary values.
 
  Generally, this latch is not a source of contention, but if there is significant wait on this latch, SHARED_POOL_SIZE needs to be corrected.

  Tuning library cache tunes the row cache indirectly.

Wednesday, April 16, 2014

Fiddling with Oracle SCN

­­According to Tom, system change number (SCN) is Oracle's clock - every time we commit, the clock increments. The SCN just marks a consistent point in time in the database.

Transactions are always happening in the database, either user initiated or oracle implicit; hence, the SCN of database would always keep progressing whenever these transactions are committed.

The following exercise aims to show the various SCN numbers and their significance.

CURRENT_SCN : This is the current system change number of database. It will keep change as a result of the various transactions getting committed in the database. If database is not open, it is NULL. For Standby database, it is current standby redo application SCN.

RESETLOGS_CHANGE# : It is the SCN when the last OPEN RESETLOGS statement was issued.
This can be verified from the RMAN repository.

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1369005126       PARENT  1          13-AUG-2009 23:00:48
2       2       ORCL     1369005126       CURRENT 754488     03-MAR-2014 09:26:33

CHECKPOINT_CHANGE# : It is the SCN at the last checkpoint. This changes whenever as checkpoint occurs.

CONTROLFILE_CHANGE# : it is the SCN of when last time the controlfile was updated. This also changes at the checkpoint, as the controlfile is updated by the checkpoint process.

ARCHIVELOG_CHANGE# : it the highest V$ARCHIVED_LOG.NEXT_CHANGE#. This changes when the log switch occurs and the redo logfile is archived.



­ SQL>  select current_scn, resetlogs_change#, checkpoint_change#, controlfile_change#, archivelog_change# from v$database;

CURRENT_SCN RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# ARCHIVELOG_CHANGE#
----------- ----------------- ------------------ ------------------- ----------------------------
    1337349            754488            1336728             1336961                  1336727


SQL>  select sequence#, resetlogs_change#, first_change#, next_change#, first_time, next_time from v$archived_log;

 SEQUENCE# RESETLOGS_CHANGE# FIRST_CHANGE# NEXT_CHANGE# FIRST_TIM NEXT_TIME
---------- ----------------- ------------- ------------ --------- ---------
         7            754488        836239       849791 04-MAR-14 04-MAR-14
         8            754488        849791       871897 04-MAR-14 05-MAR-14
         9            754488        871897       899518 05-MAR-14 04-APR-14
        10            754488        899518       927310 04-APR-14 05-APR-14
        11            754488        927310       944887 05-APR-14 05-APR-14
        12            754488        944887       977295 05-APR-14 08-APR-14
        13            754488        977295      1024120 08-APR-14 09-APR-14
        14            754488       1024120      1047871 09-APR-14 09-APR-14
        15            754488       1047871      1059596 09-APR-14 09-APR-14
        16            754488       1059596      1064794 09-APR-14 09-APR-14
        17            754488       1064794      1069996 09-APR-14 09-APR-14
        18            754488       1069996      1078366 09-APR-14 09-APR-14
        19            754488       1078366      1102677 09-APR-14 09-APR-14
        20            754488       1102677      1126438 09-APR-14 09-APR-14
        21            754488       1126438      1133559 09-APR-14 09-APR-14
        22            754488       1133559      1136571 09-APR-14 09-APR-14
        23            754488       1136571      1141765 09-APR-14 09-APR-14
        24            754488       1141765      1150843 09-APR-14 09-APR-14
        25            754488       1150843      1161944 09-APR-14 09-APR-14
        26            754488       1161944      1167053 09-APR-14 09-APR-14
        27            754488       1167053      1179993 09-APR-14 09-APR-14
        28            754488       1179993      1189333 09-APR-14 09-APR-14
        29            754488       1189333      1212452 09-APR-14 09-APR-14
        30            754488       1212452      1237939 09-APR-14 09-APR-14
        31            754488       1237939      1258472 09-APR-14 10-APR-14
        32            754488       1258472      1288770 10-APR-14 10-APR-14
        33            754488       1288770      1311084 10-APR-14 10-APR-14
        34            754488       1311084      1336727 10-APR-14 14-APR-14

28 rows selected.

Observing SCN

-          Check the current_scn, checkpoint_change#, controlfile_change#

SQL>  select current_scn, resetlogs_change#, checkpoint_change#, controlfile_change#, controlfile_sequence#, archivelog_change# from v$database;

CURRENT_SCN RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_SEQUENCE# ARCHIVELOG_CHANGE#
----------- ----------------- ------------------ ------------------- --------------------- ------------------
    1337465            754488            1336728             1337459                  2640            1336727

-          Force a checkpoint


SQL> alter system checkpoint;

System altered.

-          Check the current_scn, checkpoint_change#, controlfile_change#

SQL>  select current_scn, resetlogs_change#, checkpoint_change#, controlfile_change#, controlfile_sequence#, archivelog_change# from v$database;

CURRENT_SCN RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_SEQUENCE# ARCHIVELOG_CHANGE#
----------- ----------------- ------------------ ------------------- --------------------- ------------------
    1337485            754488            1337482             1337482                  2641            1336727

     Check the current_scn, checkpoint_change#, controlfile_change#

Datafile SCN

The datafile SCN information is stored in the controlfile and in the datafile header. At each checkpoint, the datafile header and controlfile are updated of this information. The V$DATAFILE view presents this information from the controlfile.

CHECKPOINT_CHANGE# : It is the SCN at the last checkpoint. This changes whenever as checkpoint occurs.

OFFLINE_CHANGE# : It is the SCN when the datafile was last offline. Updated when the datafile is made online

ONLINE_CHANGE# : it is the SCN when the datafile was last made online.


Observing Datafile SCN

-          Check the current_scn, checkpoint_change#, controlfile_change# from V$DATABASE


SQL>  select current_scn, resetlogs_change#, checkpoint_change#, controlfile_change#, controlfile_sequence#, archivelog_change# from v$database;


CURRENT_SCN RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_SEQUENCE# ARCHIVELOG_CHANGE#
----------- ----------------- ------------------ ------------------- --------------------- ------------------
    1339005            754488            1337482             1338762                  2651            1336727

-          Check the checkpoint_change# from V$DATAFILE


SQL>  select file#, checkpoint_change#, online_change#, offline_change#, plugin_change#, name from v$datafile;


     FILE# CHECKPOINT_CHANGE# ONLINE_CHANGE# OFFLINE_CHANGE# PLUGIN_CHANGE# NAME
---------- ------------------ -------------- --------------- -------------- ------------------------------------------------------------
         1            1337482         754488          754487              0 /u01/app/oracle/oradata/orcl/system01.dbf
         2            1337482         754488          754487              0 /u01/app/oracle/oradata/orcl/sysaux01.dbf
         3            1337482         754488          754487              0 /u01/app/oracle/oradata/orcl/undotbs01.dbf
         4            1337482         754488          754487              0 /u01/app/oracle/oradata/orcl/users01.dbf

-          Force checkpoint


SQL> alter system checkpoint;

System altered.

-          Check the current_scn, checkpoint_change#, controlfile_change# from V$DATABASE


SQL>  select current_scn, resetlogs_change#, checkpoint_change#, controlfile_change#, controlfile_sequence#, archivelog_change# from v$database;

CURRENT_SCN RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_SEQUENCE# ARCHIVELOG_CHANGE#
----------- ----------------- ------------------ ------------------- --------------------- ------------------
    1339019            754488            1339016             1339016                  2652            1336727

-          Check the checkpoint_change# from V$DATAFILE


SQL> select file#, checkpoint_change#, last_change#,  online_change#, offline_change#, plugin_change#, name from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE# ONLINE_CHANGE# OFFLINE_CHANGE# PLUGIN_CHANGE# NAME
---------- ------------------ ------------ -------------- --------------- -------------- ------------------------------------------------------------
         1            1339016                      754488          754487              0 /u01/app/oracle/oradata/orcl/system01.dbf
         2            1339016                      754488          754487              0 /u01/app/oracle/oradata/orcl/sysaux01.dbf
         3            1339016                      754488          754487              0 /u01/app/oracle/oradata/orcl/undotbs01.dbf
         4            1339016                      754488          754487              0 /u01/app/oracle/oradata/orcl/users01.dbf