Is it necessary to enable log archiving when ORACLE creates a database?
Oracle database can run in two modes: archive mode (archivelog) and non-archive mode (noarchivelog).
The archive mode can improve the recoverability of the Oracle database.
All production databases should run in this mode, and the archive mode should be combined with the corresponding backup strategy. Only the archive mode without a corresponding backup strategy will only cause trouble.
By default, the oracle database is created in non-logging log file mode,
In the non-archive log mode, the redo log will be rewritten directly when the log is switched. If the data file is damaged due to media failure at this time, the rewritten data will be lost when the database is restored;
In the archive log mode, the database can use all archive log files generated from the most recent database backup to ensure no data loss; most production databases run in archivelog mode.
After the oracle database opens the archive log mode, it will automatically start a new process: the archiver ARCn. By default, 4 processes are started. In actual applications, up to 30 archiver processes can be started.
[oracle@dbserver1 archivelog]$ ps -ef | grep -i ora_oracle 26567 1 0 08:48 ? 00:00:00 ora_arc0_orcloracle 26573 1 0 08:48 ? 00:00:00 ora_arc1_orcloracle 26575 1 0 08:48 ? 00:00:00 ora_arc2_orcloracle 26577 1 0 08:48 ? 00:00:00 ora_arc3_orcl
Archive Log is an inactive redo log backup. By using archive logs, all redo history records can be retained. When the database is in ARCHIVELOG mode and log switching,
The background process ARCH will save the contents of the redo log to the archive log. When the database has a media failure, the data file backup, archive log and redo log can be used to completely restore the database.
#########################
Non-archive mode switch to archive mode
Before modification:
Confirm the environment. Check the database, whether to open the archive: ---The information shown below, provided that when installing the database, you did not choose to turn on flashback, and you did not choose to turn on archive
SQL> archive log listDatabase log mode No Archive ModeAutomatic archival DisabledArchive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/archOldest online log sequence 4Current log sequence 6
SQL> select name,log_mode from v$database;
NAME LOG_MODE
–------- ------------
ORCL NOARCHIVELOG
SQL> show parameter db_recovery
NAME TYPE VALUE
–---------------------------------- ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
######### The following tests the operation of switching the database from non-archive mode to archive mode when flashback is not turned on ############
SQL> shutdown immediate Database closed.
SQL> startup mount
ORACLE instance started.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
SQL> select name from v$archived_log;
no rows selected
SQL> alter system archive log current;
System altered.
SQL> select name from v$archived_log;
NAME
–------------------------------------------------------------------------------
/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf
SQL> show parameter recover;
NAME TYPE VALUE
–---------------------------------- ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
no rows selected
When log_archive_log is set to the default value, the archive directory will become ?/dbs/arch.
SQL> alter system set log_archive_dest_1='';System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ?/dbs/arch
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
After restarting the database to mount, the archive directory changes back to the original default directory.
SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/archOldest online log sequence 5Next log sequence to archive 7Current log sequence 7---Set the maximum number of open processes for archiving SQL> alter system set log_archive_max_processes = 5;System altered.---Modify the log storage format SQL> alter system set log_archive_format = "archive_%t_%s_%r.log" scope=spfile;System altered.SQL> shutdown immediateDatabase dismounted.ORACLE instance shut down
SQL> startup
ORACLE instance started.
Total System Global Area 939495424 bytes
Fixed Size 2258840 bytes
Variable Size 637536360 bytes
Database Buffers 293601280 bytes
Redo Buffers 6098944 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SQL> show parameter db_recovery
NAME TYPE VALUE
–---------------------------------- ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL> select name from v$archived_log;
NAME
–------------------------------------------------------------------------------
/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf
SQL> alter system switch logfile;
System altered.
SQL> select recid, name, first_time from v$archived_log;
NAME
–------------------------------------------------------------------------------
/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf
/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/archarchive_1_7_94709105
4.log
SQL> select NAME,SPACE_LIMIT/1024/1024/1024,SPACE_USED/1024/1024/1024,NUMBER_OF_FILES from V$RECOVERY_FILE_DEST;
no rows selected
SQL> select flashback_on from v$database;
FLASHBACK_ON
–----------------
NO
#############################
Turn on database flashback Set the path and size of the flash recovery area to test. First turn off the archive mode and then turn on the default path after archiving is the flashback path of the database.
Check the database, whether to turn on flashback
Note: If you turn on the flashback function, you need to be in archive mode, or it doesn't make sense to not flashback! ! ! !
However, if you open the archive, you don't necessarily need to open the flashback function! ! !
If flashback is turned on, and then archive mode is turned on. If the archive log does not specify a new path, the archive log is saved in the flashback area database by default and displayed as: USE_DB_RECOVERY_FILE_DEST, which actually corresponds to the path of the flashback.
In addition, the location and size of the flashback area and archive path can be set manually.
SQL> select flashback_on from v$database;FLASHBACK_ON------------------NO SQL> shutdown immediateSQL> startup mount
SQL> alter database flashback on; When installing the database, I did not choose to turn on flashback, and the following error occurred
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
Set up the flash recovery area:
Set the flash recovery area size: oracle default flash_recovery_area is 2G
SQL> alter system set db_recovery_file_dest_size=5g scope=spfile;
View the space size of the specified path in the flashback area:
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
–---------------------------------- ----------- ------------------------------
db_recovery_file_dest_size big integer 30G
Set the flashback area location:
SQL> alter system set db_recovery_file_dest='/home/oracle/u01/app/oracle/account_flashback_area' scope=spfile;
Set the flashback target to 5 days, in minutes, oracle defaults to 1440 minutes, which is one day)
SQL> alter system set db_flashback_retention_target=7200 scope=spfile;
Create flashback related directories at the operating system level:
[oracle@dbserver oracle]$ mkdir -p /home/oracle/u01/app/oracle/account_flashback_area
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
–----------------
YES
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
Conclusion: If flashback is turned on and the archive mode is turned on again, and the archive log does not specify a new path, the archive log is saved in the flashback area database by default and displayed as: USE_DB_RECOVERY_FILE_DEST, which actually corresponds to the path of the flashback.
At present, we will test to close the flashback and restart the database to view the archive log path.
SQL> select open_mode from v$database;OPEN_MODE--------------------READ WRITE
SQL> shutdown immediate
Database closed.
SQL> startup mount
ORACLE instance started.
Close the archive mode ---- remind us that the database is running in the archive mode. When flashback is turned on, the archive cannot be closed directly.
SQL> alter database noarchivelog;alter database noarchivelog*ERROR at line 1:ORA-38774: cannot disable media recovery - flashback database is enabled
Then we close the flashback first
SQL> alter database flashback off;Database altered.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
SQL> select flashback_on from v$database;
FLASHBACK_ON
–----------------
NO
Restart, open archive view archive path
SQL> shutdown immediateDatabase closed.SQL> startup mount ORACLE instance started.SQL> alter database archivelog;Database altered.SQL> alter database open;Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> select flashback_on,open_mode from v$database;
FLASHBACK_ON OPEN_MODE
–---------------- --------------------
NO READ WRITE
Conclusion: If you open the archive, you don't necessarily need to turn on the flashback function, you don't need to turn on the flashback! ! !
Test 3: Modify the default archive path
SQL> shutdown immediateSQL> startup mountORACLE instance started.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> alter system set log_archive_dest_1=‘location=/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch’;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> select flashback_on,open_mode from v$database;
FLASHBACK_ON OPEN_MODE
–---------------- --------------------
NO MOUNTED
Test 4: We close the archive, first turn on the flashback, then turn on the archive, start the database, check the archive path, see if the customized path is met, modify it to the default flashback path.
SQL> alter database noarchivelog;Database altered.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6
Current log sequence 8
SQL> alter database flashback on; ––Flashback cannot be turned on in non-archive mode
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
Conclusion: Flashback cannot be turned on in non-archive mode
################################################
SQL> alter database archivelog;Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL>
SQL> alter database flashback on;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
Conclusion: If the storage path is specified for the archive log, then if flashback is turned on, the storage path of the archive log remains the path originally specified by the user
#############################
Carry on:
Modify the archive log to the default storage path of the database Repeat the above operation to confirm whether the path of the archive log is the path of the flashback area
SQL> shutdown immediateDatabase closed.SQL> startup mountORACLE instance started.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> alter system set log_archive_dest_1=’’; –-Modify the archive log as the default storage path of the database
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ?/dbs/arch
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ?/dbs/arch
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> select flashback_on,open_mode from v$database;
FLASHBACK_ON OPEN_MODE
–---------------- --------------------
YES READ WRITE
SQL> shutdown immediate
Database closed.
SQL> startup
ORACLE instance started.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
Conclusion: The archive log is the default storage path of the database. When the flashback function is enabled, switch to the archive mode. The path of the archive log is the path of the flashback area by default.
