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
勇敢坚韧真正之才智乃刚毅之志向
posted on 2019-05-09 11:11 Memory_Python 阅读(968) 评论(0) 编辑 收藏 举报