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