oracle生产环境 闪回、归档操作参考

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

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

场景描述：

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

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

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

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

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.log" 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

(2) 开启闪回：

SQL> alter database flashback on;

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

SQL> alter system set db_recovery_file_dest=‘/home/oracle/u01/app/oracle/account_flashback_area‘ scope=spfile;

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

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

设置闪回路径大小

alter system set db_recovery_file_dest_size=5g 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

oracle生产环境 闪回、归档操作参考

标签：lte   dbf   for   hello   false   over   logfile   相关   ima