Manually Archiving Log files in Oracle 23c

In "Oracle Database 23c", it is still possible to manually archive one or more redo log files, just as in previous versions.
This is useful in scenarios such as preparing for backups or troubleshooting redo log behavior.
✅ Command to Manually Archive Redo Logs
You can use the following SQL command from within SQL\*Plus or another Oracle client when connected as SYSDBA:

ALTER SYSTEM ARCHIVE LOG ALL;

This command forces the database to archive all online redo log groups that are not yet archived. If you want to archive a specific log file, use:

ALTER SYSTEM ARCHIVE LOG LOGFILE <path_to_logfile> ;

Replace <path_to_logfile> with the full path of the redo log file.
🛠️ Notes and Considerations

• ARCHIVELOG mode must be enabled. You can check with:
  

  SELECT LOG_MODE FROM V$DATABASE;
  

• You can enable ARCHIVELOG mode by mounting the database and executing:
  

  SHUTDOWN IMMEDIATE;
  STARTUP MOUNT;
  ALTER DATABASE ARCHIVELOG;
  ALTER DATABASE OPEN;
  

• Ensure the archiver process (ARCn) is running properly and that the archive destination has sufficient space.

To monitor manual archiving activity or status, query:

SELECT SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;

And for archived logs:

SELECT NAME, FIRST_TIME, NEXT_TIME 
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Here is a corresponding `.sql` script that automates the **manual archiving process** for **Oracle 23c**, including detection of the ARCHIVELOG mode and enabling it if needed. This script is intended to be run as **SYSDBA** from SQL\*Plus or Oracle SQL Developer:
✅ `manual_archiving.sql`

-- manual_archiving.sql
-- Automates the manual redo log archiving process in Oracle 23c

-- Display current archive log mode
SET SERVEROUTPUT ON
DECLARE
    v_log_mode VARCHAR2(20);
BEGIN
    SELECT LOG_MODE INTO v_log_mode FROM V$DATABASE;
    DBMS_OUTPUT.PUT_LINE('Current LOG_MODE: ' || v_log_mode);

    IF v_log_mode = 'ARCHIVELOG' THEN
        DBMS_OUTPUT.PUT_LINE('Database is in ARCHIVELOG mode. Proceeding to archive logs...');
        EXECUTE IMMEDIATE 'ALTER SYSTEM ARCHIVE LOG ALL';
        DBMS_OUTPUT.PUT_LINE('Manual archiving initiated.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Database is NOT in ARCHIVELOG mode.');
        DBMS_OUTPUT.PUT_LINE('Please restart database in MOUNT mode and enable ARCHIVELOG mode manually:');
        DBMS_OUTPUT.PUT_LINE('  SHUTDOWN IMMEDIATE;');
        DBMS_OUTPUT.PUT_LINE('  STARTUP MOUNT;');
        DBMS_OUTPUT.PUT_LINE('  ALTER DATABASE ARCHIVELOG;');
        DBMS_OUTPUT.PUT_LINE('  ALTER DATABASE OPEN;');
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error encountered: ' || SQLERRM);
END;
/

🔧 Usage Instructions

1. Save the script to a file named manual_archiving.sql.
2. Connect to your Oracle 23c database as SYSDBA:
   

   sqlplus / as sysdba
   

3. Run the script:
   

   @/path/to/manual_archiving.sql
   

You may choose not to enable automatic archiving for your database if you are archiving to a location that is not always accessible. You may be archiving directly to tape, for example, and may not want to leave the archival tape permanently mounted in the drive. In such a situation, you can periodically mount the tape, archive whatever log files have been filled, and then dismount the tape. Note that you would have to repeat the process throughout the day, and the interval after which you would repeat the process would depend on the number and size of your redo log files and the rate that that redo was being generated. The ALTER SYSTEM command may be used to archive one or more log files manually.
The following diagram describes the syntax used for this purpose:

THREAD thread_number	Identifies the thread number. This is necessary only in Oracle Parallel Server environments.
SEQUENCE seq_num	Archives the log file corresponding to the specified sequence number.
CHANGE change_num	Archives the log files containing the specified system change number.
CURRENT	Archives the current redo log file. Note that Oracle will do a log switch first.
GROUP group_num	Archives the specified redo log file group.
LOGFILE 'filename'	Archives the named log file.
NEXT	Archives the next log file in the sequence that needs archiving.
ALL	Archives all log files that haven't been archived yet.
TO 'location'	Allows you to override the archive log destination for this one operation.

In normal database operations, the NEXT and ALL options are the most useful. For example, if you were periodically archiving all your log files to tape, you would use this command:

ALTER SYSTEM ARCHIVE LOG ALL;

In addition to the ALTER SYSTEM SQL statement, the NEXT and ALL options may be used with the Server Manager ARCHIVE LOG command. So if you were using Server Manager, you could issue an ARCHIVE LOG ALL command.
In the next lesson, you will learn how to view a list of the log history.