Alter System Switch Logfile hangs ( after doing RAC database maintainance operations )

Analyzing and fixing the problem

After a RAC database reboot trying to run 

SQL>   alter system switch logfile;
--> Hang - need to press <crtl>C to get back to sqlplus prompt
 ^C  alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
---> Something must be wrong with archive settings 

Verify Archiver settings: 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     46
Next log sequence to archive   47
Current log sequence           47
--> Archving goes to FRA - Checking add. archive locations 

SQL> select dest_name,status,error from v$archive_dest_status;
DEST_NAME         STATUS    ERROR
------------------------ --------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_1       ERROR      ORA-16032: parameter    destination string cannot be translated
LOG_ARCHIVE_DEST_2     INACTIVE
--> Only LOG_ARCHIVE_DEST_1 is active and this location has a problem !

SQL>  show parameter log_archive_dest_1    ;
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1             string     LOCATION=USE_DB_RECOVERY_FILE_DEST

Verify FRA location for space                         
SQL>  select * from v$flash_recovery_area_usage;
FILE_TYPE         PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                  0             0        0
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

SQL> show parameter db_recov
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string
db_recovery_file_dest_size         big integer 40G

--> AHH - No db_recovery_file_dest defined  
SQL> alter system set db_recovery_file_dest='+FRA2' scope=both SID='*';

--> Reboot database and verify settings

SQL>   select dest_name,status,error from v$archive_dest_status;
DEST_NAME              STATUS    ERROR
---------------------- --------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_1     VALID
LOG_ARCHIVE_DEST_2     INACTIVE
LOG_ARCHIVE_DEST_3     INACTIVE
LOG_ARCHIVE_DEST_4     INACTIVE
..
--> LOG_ARCHIVE_DEST_1 is  VALID now

SQL>   select * from v$flash_recovery_area_usage;
FILE_TYPE           PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                       .05                         0        1
REDO LOG                           .75                         0        6
ARCHIVED LOG                      1.06                         0       59
BACKUP PIECE                     20.99                         0       18
IMAGE COPY                       21.22                         0        6
FLASHBACK LOG                        0                         0        0
FOREIGN ARCHIVED LOG                 0                         0        0

--> v$flash_recovery_area_usage looks ok

Verify that switch logfile is working now
SQL>   alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered

Reference

Leave a Reply

Your email address will not be published. Required fields are marked *