Oracle闪回、归档设置

　　oracle默认的日志归档路径为闪回恢复区，但是默认大小为2G空间，而且不只是归档日志的默认路径，也是备份文件和闪回日志的默认地址，这样会导致空间使用不足，这种情况当归档日志满了，无法归档导致数据库夯住；这个时候就需要修改归档日志的路径，将归档日志放到其他不受限制的路径下来解决这个问题。

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

SQL> archive log list;
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           /u01/app/oracle/12.1.0.1/db_1/dbs/arch
Oldest online log sequence     1
Current log sequence           3

SQL> select  flashback_on from v$database;

FLASHBACK_ON
------------------
NO

 1、首先设置归档日志路径，设置归档日志存放格式

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch' scope=both;

System altered.

SQL> archive log list;
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           /u01/app/oracle/arch
Oldest online log sequence     1
Current log sequence               3

2、修改日志文件命名格式：

SQL> alter system set log_archive_max_processes = 5;

System altered.

SQL>  alter system set log_archive_format = "%t_%s_%r.dbf" scope=spfile;

System altered.

3、设置闪回路径，设置闪回路径大小，设置闪回保存策略（设置闪回目标为7天，以分钟为单位，oracle默认1440分钟，即一天））

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

System altered.

SQL> alter system set db_recovery_file_dest_size=30g scope=spfile;

System altered.

SQL> alter system set db_flashback_retention_target=10080 scope=both;

System altered.

4、开启归档，开启闪回

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size            8792056 bytes
Variable Size          838862856 bytes
Database Buffers      318767104 bytes
Redo Buffers            7983104 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

5、查看相关配置是否生效
　　查看归档

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/app/oracle/arch
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL> show parameter archive

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target             integer     0
log_archive_config             string
log_archive_dest             string
log_archive_dest_1             string     location=/u01/app/oracle/arch
..........
..........

 

　　查看闪回

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     /u01/app/oracle/flashback_area
db_recovery_file_dest_size         big integer 30G

　　检查flash recovery area的使用情况，查看archivelog

SQL> set linesize 200
SQL> set pagesize 80
SQL> 
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE        PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                 0               0           0          0
REDO LOG                 0               0           0          0
ARCHIVED LOG                 0               0           0          0
BACKUP PIECE                   .06               0           1          0
IMAGE COPY                 0               0           0          0
FLASHBACK LOG                   1.3               0           2          0
FOREIGN ARCHIVED LOG             0               0           0          0
AUXILIARY DATAFILE COPY          0               0           0          0

8 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
-----------------------------
            .0408

SQL> alter system switch logfile;

System altered.

SQL> select name from v$archived_log;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/arch/1_3_1004462648.dbf

 11:10:03

转载于:https://www.cnblogs.com/Memory-Python/p/10837023.html