Oracle修改日志归档模式、归档路径以及空间大小的相关测试
ORACLE 创建数据库的时候要不要开启日志归档？
oracle数据库可以运行在2种模式下:归档模式(archivelog)和非归档模式(noarchivelog) 。
归档模式可以提高Oracle数据库的可恢复性。
生产数据库都应该运行在此模式下，归档模式应该和相应的备份策略相结合，只有归档模式没有相应的备份策略只会带来麻烦。
在默认情况下，oracle数据库是在非归日志档模式中创建的，
在非归档日志模式中，进行日志切换时会直接重写redo log，如果此时数据文件因为介质失败被损坏，则数据库恢复时会丢失掉被重写的数据；
在归档日志模式下，数据库可以应用最近一次数据库备份开始生成的所有归档日志文件，保证数据无丢失；大部分的生产数据库以archivelog模式运行。
oracle数据库在开启归档日志模式后，会自动启动新的进程：归档器ARCn。默认情况下是开启4个进程，在实际应用中最多可以启动30个归档器进程。
[oracle@dbserver1 archivelog]$ ps -ef | grep -i ora_ oracle 26567 1 0 08:48 ? 00:00:00 ora_arc0_orcl oracle 26573 1 0 08:48 ? 00:00:00 ora_arc1_orcl oracle 26575 1 0 08:48 ? 00:00:00 ora_arc2_orcl oracle 26577 1 0 08:48 ? 00:00:00 ora_arc3_orcl
归档日志(Archive Log)是非活动的重做日志备份.通过使用归档日志,可以保留所有重做历史记录,当数据库处于ARCHIVELOG模式并进行日志切换式,
后台进程ARCH会将重做日志的内容保存到归档日志中.当数据库出现介质失败时,使用数据文件备份,归档日志和重做日志可以完全恢复数据库.
#########################
非归档模式 切换 归档模式
修改之前：
确认环境。查看数据库，是否开启归档： ---如下显示的信息，前提是安装数据库的时候，没有选择开启闪回，也没有选择开启归档
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 4 Current 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
######### 如下 测试没有开启闪回的情况下 数据库从非归档模式切换到归档模式的操作 ############
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
将log_archive_log设置为默认值时，归档目录将会变为?/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.
重启数据库到mount后，归档目录变回为原先默认的目录。
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> alter system set log_archive_max_processes = 5; System altered. ---修改日志存放格式 SQL> alter system set log_archive_format = "archive_%t_%s_%r.log" scope=spfile; System altered. SQL> shutdown immediate Database 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
#############################
开启数据库闪回 设置闪回恢复区路径 以及大小 测试 先关闭归档模式 然后再开启归档后的 默认路径 为数据库的闪回路径
查看数据库，是否开启闪回
注意：开启闪回功能的话，需要在归档模式下，要不闪回没有意义！！！！
但是，开启归档的话，不一定需要开启闪回功能！！！
如果开启了闪回，再开启归档模式，归档日志没有指定新路径的话，那么归档日志是默认保存在闪回区的 数据库中显示为：USE_DB_RECOVERY_FILE_DEST，实际对应闪回的路径。
另外闪回区和归档路径的 位置和大小都是可以手动设置的。
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> shutdown immediate SQL> startup mount SQL> alter database flashback on; 安装数据库的时候 没有选择开启闪回，出现了如下错误 alter database flashback on * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38709: Recovery Area is not enabled.
设置闪回恢复区：
设置闪回区大小： oracle默认flash_recovery_area 为2G
SQL> alter system set db_recovery_file_dest_size=5g scope=spfile;
查看闪回区指定路径的空间大小：
SQL> show parameter db_recovery_file_dest_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 30G
设置闪回区位置：
SQL> alter system set db_recovery_file_dest='/home/oracle/u01/app/oracle/account_flashback_area' scope=spfile;
设置闪回目标为5天，以分钟为单位，oracle默认1440分钟，即一天）
SQL> alter system set db_flashback_retention_target=7200 scope=spfile;
操作系统层面创建闪回相关目录：
[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
结论：如果开启了闪回，再开启归档模式，归档日志没有指定新路径的话，那么归档日志是默认保存在闪回区的 数据库中显示为：USE_DB_RECOVERY_FILE_DEST，实际对应闪回的路径。
目前我们再测试关闭闪回 重启数据库 查看归档日志路径
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> shutdown immediate Database closed. SQL> startup mount ORACLE instance started.
关闭归档模式 ----提示我们数据库运行在归档模式 闪回开启的情况下，是不能直接关闭归档的
SQL> alter database noarchivelog; alter database noarchivelog * ERROR at line 1: ORA-38774: cannot disable media recovery - flashback database is enabled
那么我们就先关闭闪回
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
重新启动，开启归档 查看归档路径
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 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
结论：开启归档的话，不一定需要开启闪回功能，可以不开启闪回！！！
测试3：修改归档默认路径
SQL> shutdown immediate SQL> startup mount 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 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
测试4： 我们关闭归档 先开启闪回 再开启归档 启动数据库 查看归档路径 是否会见自定义的路径 修改为默认的闪回路径
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; ----非归档模式下 无法开启闪回 alter database flashback on * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled.
结论： 非归档模式下 无法开启闪回
################################################
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
结论： 如果归档日志指定了存储路径 那么开启闪回的话 归档日志的存放路径还是保持用户原先指定的路径
#############################
继续：
修改归档日志为数据库默认的存放路径 重复上述操作 确认归档日志的路径 是否为闪回区的路径
SQL> shutdown immediate Database closed. SQL> startup mount ORACLE 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=''; ---修改归档日志为数据库默认的存放路径 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
结论：归档日志为数据库默认的存放路径，在闪回功能开启的情况下，切换为归档模式，归档日志的路径默认为闪回区的路径。
其他参考：
http://xmdiao.blog.51cto.com/6331248/1392207/
http://blog.csdn.net/weixin_36707770/article/details/53885423
http://blog.csdn.net/panfelix/article/details/43452121
http://blog.sina.com.cn/s/blog_54eeb5d90100kqf4.html
https://zhidao.baidu.com/question/2054618093433889467.html
http://blog.chinaunix.net/uid-28255964-id-4344766.html
http://jingyan.baidu.com/article/d3b74d64abfa6b1f77e60982.html
http://www.2cto.com/database/201109/102332.html
http://database.51cto.com/art/201108/287998.htm
http://blog.chinaunix.net/uid-26446098-id-4979004.html
