Last modified on 5 April 2013, at 05:56

RAC Attack - Oracle Cluster Database at Home/Archived Logs


  1. Look at the sequence number of the two current logs.

    SQL> select thread#, sequence# from v$log where status='CURRENT';
     
       THREAD#  SEQUENCE#
    ---------- ----------
             1          6
             2          3
    


  2. Execute “alter system switch logfile” and check the result. What happened?

    SQL> alter system switch logfile;
    System altered.
     
    SQL> select thread#, sequence# from v$log where status='CURRENT';
     
       THREAD#  SEQUENCE#
    ---------- ----------
             1          6
             2          4
    


  3. Execute “alter system archive log current” and check the result. What happened now?

    SQL> alter system archive log current;
    System altered.
     
    SQL> select thread#, sequence# from v$log where status='CURRENT';
     
       THREAD#  SEQUENCE#
    ---------- ----------
             1          7
             2          5
    


  4. Check where the archived logs are being stored by default. (Was this what you expected?) Explicitly assign them to the FRA and test to see if your changes took effect.

    SQL> col t format a5
    SQL> col name format a70
    SQL> select thread#||'.'||sequence# t, name from v$archived_log;
    T     NAME
    ----- ----------------------------------------------------------------------
    1.6   /u01/app/oracle/product/11.1.0/db_1/dbs/arch1_6_685220246.dbf
    1.6   /u01/app/oradata/RAC/archivelog/2009_05_04/o1_mf_1_6_4zy1wofw_.arc
    2.4   /u01/app/oracle/product/11.1.0/db_1/dbs/arch2_4_685220246.dbf
    2.4   /u01/app/oradata/RAC/archivelog/2009_05_04/o1_mf_2_4_4zy1wpn9_.arc
     
    SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
    System altered.
     
    SQL> alter system archive log current;
    System altered.
     
    SQL> select thread#||'.'||sequence# t, name from v$archived_log;
    T     NAME
    ----- ----------------------------------------------------------------------
    2.5   /u01/app/oradata/RAC/archivelog/2009_05_04/o1_mf_2_5_4zy2xzv3_.arc
    1.7   /u01/app/oradata/RAC/archivelog/2009_05_04/o1_mf_1_7_4zy2y1q6_.arc
    


  5. Take a backup of all the current archivelogs, using the default RMAN configuration. Why does it fail?

    collabn1:/home/oracle[RAC1]$ rman target /
    Recovery Manager: Release 11.1.0.6.0 - Production on Mon May 4 10:43:45 2009
    connected to target database: RAC (DBID=2273202257)
     
    RMAN> backup archivelog all;
     
    Starting backup at 04-MAY-09
    current log archived
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=113 instance=RAC1 device type=DISK
    archived log /u01/app/oradata/RAC/archivelog/2009_05_04/o1_mf_1_4_4zy03zdh_.arc not found or out of sync with catalog
    trying alternate file for archived log of thread 1 with sequence 4
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of backup command at 05/04/2009 10:47:56
    RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
    ORA-19625: error identifying file /u01/app/oracle/product/11.1.0/db_1/dbs/arch1_4_685220246.dbf
    ORA-27037: unable to obtain file status
    Linux Error: 2: No such file or directory
    Additional information: 3
    


  6. Crosscheck the archived logs; most will fail. Then copy the files from the local FRA on collabn2 and crosscheck them again.

    RMAN> crosscheck archivelog all;
    RMAN> list archivelog all;
     
    List of Archived Log Copies for database with db_unique_name RAC
    =====================================================================
    Key     Thrd Seq     S Low Time
    ------- ---- ------- - ---------
    6       1    4       X 26-APR-09
       Name: /u01/app/oradata/RAC/archivelog/2009_05_04/o1_mf_1_4_4zy03zdh_.arc
     
    RMAN> host 'scp -r collabn2:/u01/app/oradata/RAC/archivelog/* /u01/app/oradata/RAC/archivelog/';
     
    o1_mf_1_5_4zy044nl_.arc                       100% 1024     1.0KB/s   00:00
    o1_mf_2_6_4zy3k2s0_.arc                       100%   12KB  11.5KB/s   00:00
    o1_mf_2_3_4zy1txkr_.arc                       100%   13KB  12.5KB/s   00:01
    o1_mf_1_4_4zy03zdh_.arc                       100%   29MB   4.9MB/s   00:06
    o1_mf_2_2_4zy1dksv_.arc                       100% 1209KB   1.2MB/s   00:01
    o1_mf_2_4_4zy1wpn9_.arc                       100%  233KB 233.0KB/s   00:00
    o1_mf_2_5_4zy2xzv3_.arc                       100%   19KB  19.0KB/s   00:00
     
    RMAN> crosscheck archivelog all;
    RMAN> list archivelog all;
     
    List of Archived Log Copies for database with db_unique_name RAC
    =====================================================================
    Key     Thrd Seq     S Low Time
    ------- ---- ------- - ---------
    6       1    4       A 26-APR-09
       Name: /u01/app/oradata/RAC/archivelog/2009_05_04/o1_mf_1_4_4zy03zdh_.arc