Saturday, October 19, 2013

ORA-00214: control file '+DATA02_R5/fdmn1i/controlfile/current.265.799827461' version 6909395 inconsistent with file '+ARCH02_R5/fdmn1i/controlfile/current.11120.826208033' version 6909384

We already know about the reason but my overconfidence spoil around 1 hr for this error in RAC environment. Simple explanation is needed here '+DATA02_R5/fdmn1i/controlfile/current.265.799827461'
scn is 6909395 and other controlfile '+ARCH02_R5/fdmn1i/controlfile/current.11120.826208033' not able to write the changes after scn 6909384. So we need to need the recreate second
controfile as well. Please follow the below steps.

1)Add the Controlfile Entry Using spfile.


C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 19 16:43:00 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size                  2230600 bytes
Variable Size            1996490424 bytes
Database Buffers         1124073472 bytes
Redo Buffers               17231872 bytes
SQL> alter system set control_files ='+DATA02_R5/fdmn1i/controlfile/current.265.799827461' ,'+ARCH02_R5' scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup nomount

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     14
control_files                        string      +DATA02_R5/fdmn1i/controlfile/
                                                 current.265.799827461, +ARCH02
                                                 _R5
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

2)Restore controlfile using RMAN
rman target /


Recovery Manager: Release 11.2.0.2.0 - Production on Sat Oct 19 08:47:29 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: FDMN1I (not mounted)

RMAN> restore controlfile from '+DATA02_R5/fdmn1i/controlfile/current.265.799827461';

Starting restore at 19-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1511 instance=FDMN1I1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA02_R5/fdmn1i/controlfile/current.265.799827461
output file name=+ARCH02_R5/fdmn1i/controlfile/current.10645.829212487
Finished restore at 19-OCT-13

RMAN> exit


Recovery Manager complete.
/ora001/g09104064 FDMN1I1]>sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Sat Oct 19 08:48:17 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> alter system set control_files ='+DATA02_R5/fdmn1i/controlfile/current.265.799827461' ,'+ARCH02_R5/fdmn1i/controlfile/current.10645.829212487' scope=spfile;

System altered.

Please Dont use sqlplus here if you are already configured with Cluster Service

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Terminal>srvctl stop database -d FDMN1I -o immediate;
Terminal>srvctl  start database -d FDMN1I
Terminal>srvctl status database -d FDMN1I
Instance FDMN1I1 is running on node svr00085
Instance FDMN1I2 is running on node svr00086


Thanks,
Jyothish

Wednesday, October 16, 2013

ORA-1555 Snapshot Too old while a running job or query .

If we are getting this error frequently we need to think about the size of  undo tablespace and undo retention  time. Below query helped me to take optimum size of
undo tablespace and undo retention time.

                                                                        ACTUAL UNDO SIZE
OPTIMAL UNDO RETENTION =  ____________________________________
                                                       DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC


SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';



                     
OPTIMAL UNDO SIZE      = UNDO RETENTION*DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC


SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024) 
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';
 
 
 
  Note:This above estimation undo size and retention are depends upon the calculations from v$undostat during the previous run. Please impliment this estimation carefully
  as per your requirment and resource avaialability.


Thanks,
Jyothish