oracle 闪回、归档的设置建议

时间:2022-09-07 15:35:19

闪回与归档的相关测试 详见博客:http://www.cnblogs.com/hellojesson/p/7050097.html

数据库在归档模式下的管理 详见博客:http://www.cnblogs.com/hellojesson/p/7182219.html

场景描述:

本地数据库 当前运行在非归档模式 没有开启闪回功能!!!

我们要做的操作是将:数据库切换在归档模式 ,开启数据库的闪回功能;

并且:自定义闪回恢复区路径以及大小 自定义归档日志存放路径。

补充一个操作说明:其实可以参照oracle默认的配置,将归档日志存放在闪回区的指定路径中,这里是为了说明,归档是可以自己定义指定目录存放的,因此下文中的操作,将归档日志放在了和闪回不同的目录!!!

在ORACLE10g和11g版本,ORACLE默认的日志归档路径为闪回恢复区($ORACLE_BASE/flash_recovery_area)。对于这个路径,ORACLE有一个限制,就是默认只有2G的空间,而且不只是归档日志的默认路径,也是备份文件和闪回日志的默认地址,
这样的话归档日志所使用的空间就达不到2G,在没有设置好这个路径大小的情况下,很多系统都遇到过归档日志满,无法归档而导致数据库夯住的问题。虽然闪回恢复区ORACLE会自动管理,即闪回恢复区空间不足就会清理掉没用的数据,但是如果备份策略不是很完善,数据库非常繁忙的情况下,还是有可能遇到无法归档导致数据库夯住的问题,这时候通常需要修改归档日志的路径,将归档日志放到其他不受限制的路径下来解决这个问题,这种情况下就可通过下面操作来实现。

确认当前系统环境:  没有开启归档  没有开启闪回

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

(1) 首先设置 归档日志路径  设置归档日志存放格式

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 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 archivelog;
Database altered.

修改日志文件命名格式:

SQL> alter system set log_archive_max_processes = 5;

SQL> alter system set log_archive_format = "%t_%s_%r.dbf" scope=spfile;
--- 注意: 上方的%t_%s_%r.dbf命名格式,10g之后必须包含%t %s %r否则后续会报错!!!
出现:“ORA-19905:log_archive_format must contain %s,%t and %r”错误,实例将无法启动。 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

(2) 开启闪回:

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.
---如果出现上述 报错 需要先在数据库层面创建:闪回的路径 并指定空间大小
(有网友说这块必须得先设置空间大小再指定路径,否则报错,我这块暂没有遇到这种情况,如果提示相关错误的话,下方的操作,就先设置闪回空间大小,再指定路径),
另外还需要在操作系统层面创建相应的目录; 参考下方相关操作!!!然后再回来执行上述打开闪回的命令。

(3) 然后 设置闪回路径 设置闪回路径大小  设置闪回保存策略

设置闪回区位置:

SQL> alter system set db_recovery_file_dest='/home/oracle/u01/app/oracle/flashback_area' scope=spfile;

操作系统层面创建闪回相关目录:

[oracle@dbserver oracle]$ mkdir -p /home/oracle/u01/app/oracle/flashback_area

设置闪回路径大小

alter system set db_recovery_file_dest_size=30g scope=spfile;

设置闪回目标为5天,以分钟为单位,oracle默认1440分钟,即一天)

SQL> alter system set db_flashback_retention_target=7200 scope=spfile;

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 open_mode,flashback_on from v$database;
OPEN_MODE FLASHBACK_ON
-------------------- ------------------
MOUNTED YES

(4) 重启数据库

查看相关配置是否生效

SQL> select open_mode,flashback_on from v$database;
OPEN_MODE FLASHBACK_ON
-------------------- ------------------
MOUNTED YES SQL> alter database open;
SQL> shutdown immediate
SQL> startup 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 open_mode,flashback_on from v$database;
OPEN_MODE FLASHBACK_ON
-------------------- ------------------
READ WRITE YES

查看闪回恢复区的配置

SQL> show parameter db_recovery

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/oracle/u01/app/oracle/account_flashback_area
db_recovery_file_dest_size big integer 5G
检查flash recovery area的使用情况,查看archivelog
SQL> set linesize 200
SQL> set pagesize 80
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 0 0 0
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 1.95 0 2
FOREIGN ARCHIVED LOG 0 0 0
7 rows selected.

计算flash recovery area已经占用的空间

SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
.0585 SQL> select archiver from v$instance; ARCHIVE
-------
STARTED 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 name 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/arch_1_8_947091054.log SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/oracle/u01/app/oracle/fl
ashback_area
db_recovery_file_dest_size big integer 30G

补充个发现:
当选择oracle默认的配置,将归档日志存放在闪回区之后,生成的归档日志,是以日期为文件夹,并且日志后缀名为.arc,
而如果归档日志是自定义指定的存放路径,那么归档日志文件是以定义的日志格式存放的,后缀名为.bdf。

SQL> col name for a100
SQL> select recid, name, first_time from v$archived_log; RECID NAME FIRST_TIME
---------- ---------------------------------------------------------------------------------------------------- -------------------
1 /home/oracle/u01/app/oracle/archivelog/1_6_950980202.dbf 2017-08-02 17:10:47
2 /home/oracle/u01/app/oracle/archivelog/1_7_950980202.dbf 2017-08-03 10:13:28
3 /home/oracle/u01/app/oracle/archivelog/1_8_950980202.dbf 2017-08-03 11:48:58
4 /home/oracle/u01/app/oracle/archivelog/1_9_950980202.dbf 2017-08-03 11:49:47
5 /home/oracle/u01/app/oracle/flashback_area/ORCL/archivelog/2017_08_03/o1_mf_1_10_dr5lkd29_.arc 2017-08-03 14:44:51
6 /home/oracle/u01/app/oracle/flashback_area/ORCL/archivelog/2017_08_03/o1_mf_1_11_dr5lm0og_.arc 2017-08-03 15:01:00
7 /home/oracle/u01/app/oracle/flashback_area/ORCL/archivelog/2017_08_03/o1_mf_1_12_dr5lqxsh_.arc 2017-08-03 15:01:52
8 /home/oracle/u01/app/oracle/flashback_area/ORCL/archivelog/2017_08_03/o1_mf_1_13_dr5lstob_.arc 2017-08-03 15:04:29