If you do not set up the database to perform automatic archiving, the DBA
must manually archive the redo log files. Manual archiving puts a greater burden on the DBA to manage the database. You have several options when
choosing which redo log files you want to back up. The following table lists the options available to a DBA to manually archive redo log
In most cases you will use the archive all command. The alter system in the above commands is optional.
The following simulation takes a database in noarchivelog mode and puts it in archivelog mode with manual archiving of the log files. It archives
the log files and then queries the database to determine its state. Let us start our simulation.
To switch a database from NOARCHIVELOG mode to ARCHIVELOG mode with manual archiving of the log files using SQL*Plus, follow these steps:
- Make sure you have a backup of your database before proceeding, as this process involves shutting down the database and changing its mode.
- Connect to the database with SYSDBA privileges using SQL*Plus
sqlplus / as sysdba
- Shut down the database:
- Start the database in MOUNT mode:
- Enable ARCHIVELOG mode:
ALTER DATABASE ARCHIVELOG;
- Open the database:
ALTER DATABASE OPEN;
Now your database is in ARCHIVELOG mode. However, you still need to set up manual archiving for the log files. Follow these steps to configure manual archiving:
- Identify the location of your archived redo log files. You can use the following command to find the default location:
SHOW PARAMETER log_archive_dest;
If you want to change the location, you can use the following command to set a new directory:
ALTER SYSTEM SET log_archive_dest='/path/to/your/archive/directory' SCOPE=spfile;
- Manually archive log files using the following command:
ALTER SYSTEM ARCHIVE LOG CURRENT;
You will need to run this command whenever you want to archive your log files manually.
- (Optional) To automate the manual archiving process, you can create a scheduled task or cron job that runs the "ALTER SYSTEM ARCHIVE LOG CURRENT" command at specified intervals.
Remember to monitor your archived redo log files directory to avoid running out of disk space. You can also consider configuring a log rotation policy or setting up a backup solution to handle the archived log files.
Please note that manual archiving is generally not recommended for production databases, as it can be error-prone and may lead to data loss in case of a failure. It's usually better to use an automatic archiving process for production databases to ensure data consistency and recoverability.