Change the Oracle archive path and archive mode

In the oracle10g and 11g versions, the ORACLE default log archive path is the Flashback recovery area ($ORACLE _base/flash_recovery_area). For this path, Oracle has a limitation, that is, the default is only 2G of space, and not just the default path of the archive log, but also the default address of the backup file and the flashback log, so that the archive log lock uses less than 2G of space, without setting the path size of the case, Many systems have encountered an issue where archived logs are full and cannot be archived, which can be used to view archived information using the following SQL statement.

Sql> Archive Log List database log mode             non-archive mode AutoArchive             Disable archive endpoint            use_db_recovery_file_dest oldest online log sequence     321 current log sequence           326.

The above archive endpoint Use_db_recovery_file_dest is the Flash recovery area ($ORACLE _base/flash_recovery_area) By default, and you can view the Flashback recovery area information from the following SQL.

Sql> Show parameter Db_recovername                        TYPE         VALUE--------------------------  -------------------------- -------------db_recovery_file_dest       string      D:\oracle\flash_recovery_areadb_recovery_file_dest_size  Big integer   2G

As you can see from the SQL results above, the Flashback recovery area is D:\oracle\flash_recovery_area, the size is 2G, and you can view the limit information for the flashback recovery by querying the V$recovery_file_dest view.

Sql> Select name,space_limit,space_used from V$recovery_file_dest;name                           space_limit space_ Used---------------------------------------------------D:\oracle\flash_recovery_area   2147483648   21225472

By default, archived logs are stored in the Flashback Recovery Area (D:\oracle\flash_recovery_area), and if the Flashback recovery area is already used to 2G, the archive log may not be able to continue archiving, the database is compacted, and the usual solution is to increase the flashback recovery area, Can be implemented with the following SQL.

Sql> alter system set DB_RECOVERY_FILE_DEST_SIZE=3G;

Even if the current urgency is solved in this way, the Flashback recovery area Oracle will automatically manage, if the Flash recovery area is not enough space to clean up useless data, but if the backup strategy is not perfect, the database is very busy, it is possible to encounter this situation, usually need to modify the path of the archive log To address this issue by placing the archive log on a different, unrestricted path, you can modify the archive log's storage path by using the following SQL.

Sql> alter system set log_archive_dest_1= ' Location=d:\arch ';

The database is then booted into the Mount state, the database is modified to archive mode, and the database is booted to the open state.

sql> shutdown immediate database has been closed. The database has been uninstalled. The ORACLE routine has been closed. Sql> startup Mountoracle Routine has been started. The database is loaded. sql> ALTER DATABASE Archivelog; sql> ALTER DATABASE open;

Review the archive of the database again.

Sql> Archive Log List database log mode            archive mode AutoArchive             Enable archive endpoint            D:\arch oldest online log sequence     321 next archive log sequence   326 current log sequence           326

You can switch logs by toggling the log to see if an archive log is generated under the archive path to verify that the archive path is set correctly.

sql> alter system switch logfile;

See if an archive path is generated under the archive path (D:\arch).

d:\arch>dir/barc0000000326_0764368160.0001

You can see that the archive log has been generated under the D:\arch path, the name of the archive log is limited by the Log_archive_format parameter and can be viewed by the following command.

Sql> Show parameter Log_archive_formatname                   TYPE         VALUE----------------------------------------------Log _archive_format     string       arc%s_%r.%t

The above generated archive file name is arc0000000326_0764368160.0001,%s 0000000326 is the log switch number, that is, the archive log list above the current sequence of logs,%r is the scene number,%t is the thread number, Can be understood as the node number, if not the RAC environment,%t is 1, you can also add the Log_archive_format parameter value%d,%d is a 16 binary identity dbid, the following demo:

Sql> alter system set log_archive_format= ' Arc%s_%r.%t_%d.log ' scope=spfile; sql> shutdown immediate database has been closed. The database has been uninstalled. The ORACLE routine has been closed. The sql> startuporacle routine has been started. The database is loaded. The database is already open. sql> alter system switch logfile;

Check the name of the archive log, 5aa14a62 is the 16 binary dbid.

D:\arch>dir/barc0000000326_0764368160.0001arc0000000327_0764368160.0001_5aa14a62. LOG

Change the Oracle archive path and archive mode