Shulou(Shulou.com)06/01 Report--

This article shows you how to change the ORACLE archiving path and archiving mode, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

In ORACLE10g and 11g versions, the default log archive path for ORACLE is the flashback recovery area ($ORACLE_BASE/flash_recovery_area). ORACLE has a limitation on this path, that is, there is only 2G of space by default, and it is not only the default path for archived logs, but also the default address for backup files and flashback logs. In this way, the space used by archived log locks is less than 2G. Without setting the size of this path, many systems have encountered the problem that archived logs are full and cannot be archived, resulting in database tamping. You can use the following SQL statement to view the archive information.

SQL > archive log list database log mode non-archiving mode automatic archiving disables archiving end point USE_DB_RECOVERY_FILE_DEST earliest online log sequence 321 current log sequence 326.

The USE_DB_RECOVERY_FILE_DEST at the end of the archive above is the flashback recovery area ($ORACLE_BASE/flash_recovery_area) by default. You can view the information of the flashback recovery area through the SQL below.

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 above SQL results, the flashback recovery area is D:\ oracle\ flash_recovery_area with a size of 2G. You can also query the v$recovery_file_dest view to view the limits of flashback recovery.

SQL > select name,SPACE_LIMIT,SPACE_USED from vested recovery filedestinationNAME SPACE_LIMIT SPACE_USED-- D:\ oracle\ flash_recovery_area 2147483648 21225472

By default, the archive log is stored in the flashback recovery area (D:\ oracle\ flash_recovery_area). If 2G has been used in the flashback recovery area, the archive log may not be archived and the database may be tamped. The usual solution is to increase the flashback recovery area, which can be implemented with the following SQL.

The SQL > alter system set db_recovery_file_dest_size=3G; system has changed.

Even if this method is used to solve the current urgent need, although the flashback recovery area ORACLE will automatically manage, if the flashback recovery area is insufficient space, it will clean up the useless data, but if the backup strategy is not perfect and the database is very busy, you may still encounter this situation. You usually need to modify the path of the archived log and put the archived log under other unrestricted paths to solve this problem. The path where the archive logs are stored can be modified through the SQL below.

The SQL > alter system set log_archive_dest_1='location=D:\ arch'; system has changed.

Then start the database to MOUNT state, modify the database to archive mode and build the database to start to OPEN state.

The SQL > shutdown immediate database has been closed. The database has been uninstalled. The ORACLE routine has been closed. The SQL > startup mountORACLE routine has been started. The database is loaded. The SQL > alter database archivelog; database has changed. The SQL > alter database open; database has changed.

Check the archiving of the database again.

SQL > archive log list database log mode archiving mode automatic archiving enables archiving end point D:\ arch earliest online log sequence 321 next archive log sequence 326 current log sequence 326

You can verify whether the archive path is set correctly by switching logs to see if any archive logs are generated under the archive path. You can switch logs by using the following command.

The SQL > alter system switch logfile; system has changed.

Check to 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, which can be viewed by the following command.

SQL > show parameter log_archive_formatNAME TYPE VALUE-- log_archive_format string ARC%S_%R.%T

The name of the archive file generated above is ARC0000000326_0764368160.0001,%S, that is, 0000000326 is the log switching number, which is the current log sequence in the above archive log list.% R is the scene number, and% T is the thread number, which can be understood as the node number. If it is not the RAC environment,% T is 1. You can also add% DMagus% D to the log_archive_format parameter value with DBID identified as hexadecimal, as shown below:

The SQL > alter system set log_archive_format='ARC%S_%R.%T_%D.log' scope=spfile; system has changed. The 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. The SQL > alter system switch logfile; system has changed.

Check the name of the archive log. 5AA14A62 is the hexadecimal DBID.

D:\ arch > dir/bARC0000000326_0764368160.0001ARC0000000327_0764368160.0001_5AA14A62.LOG the above is how to change the ORACLE archiving path and archiving mode. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.