Table of Contents
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