3 Replies Latest reply: Jan 9, 2013 12:04 AM by moreajays RSS

    Question on archival issues on ASM

    user 777111
      Hi All,
      My database is 10.2.0.4 running on AIX

      If my FRA gets fill and my database is in hang state, what action does a DBA immediately needs to take in order to continue the database running?
      The scenario is, getting a new ASM disk from unix team will take some time. Lets say we have a normal NON ASM filesystem readily available.
      can i change my LOG_ARCHIVE_DEST to this NON ASM FILESYSTEM location ? will my database write its archive log files in that locaiton ?
      However all my other data files, redo , controls are still in +DATA disk. can this be possible and will this work?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
        • 1. Re: Question on archival issues on ASM
          Salman Qureshi
          Hi,
          can i change my LOG_ARCHIVE_DEST to this NON ASM FILESYSTEM location ?
          Yes you can to id. Before doing this, you can take backup of your current archived redo logs on local file system with DELETE INPUT option so that your archived redo logs are backed up and then deleted from the ASM and your database comes out of hung state.

          Salman
          • 2. Re: Question on archival issues on ASM
            kuljeet singh -
            user 777111 wrote:
            Hi All,
            My database is 10.2.0.4 running on AIX

            If my FRA gets fill and my database is in hang state, what action does a DBA immediately needs to take in order to continue the database running?
            The scenario is, getting a new ASM disk from unix team will take some time. Lets say we have a normal NON ASM filesystem readily available.
            can i change my LOG_ARCHIVE_DEST to this NON ASM FILESYSTEM location ? will my database write its archive log files in that locaiton ?
            However all my other data files, redo , controls are still in +DATA disk. can this be possible and will this work?
            yes,you can change the archive location to non-asm location. but also make sure you have some freespace in +DATA DG as it would required if some of your datafile want to grow.

            also run rman to take archivelog backup and delete it.
            • 3. Re: Question on archival issues on ASM
              moreajays
              HI,

              You can quickly do this
              SQL> select * from v$flash_recovery_area_usage;
              
              FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
              -------------------- ------------------ ------------------------- ---------------
              CONTROL FILE                        .01                         0               1
              REDO LOG                              0                         0               0
              ARCHIVED LOG                          0                         0               0
              BACKUP PIECE                          0                         0               0
              IMAGE COPY                            0                         0               0
              FLASHBACK LOG                         0                         0               0
              FOREIGN ARCHIVED LOG                  0                         0               0
              
              7 rows selected.
              
              SQL> select thread#, max(sequence#) from gv$backup_redolog group by  thread#;
              
                 THREAD# MAX(SEQUENCE#)
              ---------- --------------
                       1           2611
                       2           1501
              
              SQL> !
              [ora11g@remedy-ebu-db1 ~]$ rman target /
              
              Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 9 11:31:33 2013
              
              Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
              
              connected to target database: REMCORP (DBID=2835396233)
              
              RMAN> delete archivelog until sequence  2610 thread 1;
              RMAN> delete archivelog until sequence  1500 thread 2;
              Thanks,
              Ajay More
              http://moreajays.blogspot.com