在Oracle DG Standby库上启用flashback database功能

曾经发生的一次故障，让想起了在 Oracle Data Guard 环境中配置 flashback databae 功能的必要性；这样在 primary 库中用户不小心做 truncate 或其他不便回退操作时，也可以在 standbay 上做一下 flashback database 后只读模式打开 standby 库，将丢失数据导出或 dblink 方式传输到其他数据库中。[@more@]

1、首先要坚持standby库环境及参数：

-bash-3.00$ sqlplus / as sysdba

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /backup/archivelog

Oldest online log sequence 6799

Next log sequence to archive 0

Current log sequence 6802

SQL> show parameter db_flashback_retention_target

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target integer 1440

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest_size big integer 0

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string

db_recovery_file_dest_size big integer 0

SQL> show parameter flashback

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target integer 1440

也就是在备库还没有配置过闪回区域相关的两个如下参数：

db_recovery_file_dest

db_recovery_file_dest_size

2、检查standby 数据库服务器存储空间使用情况：

-bash-3.00$ df -h

Filesystem size used avail capacity Mounted on

/dev/md/dsk/d10 119G 31G 87G 27% /

………..

/dev/dsk/c1t0d0s6 652G 368G 277G 58% /oradata

/dev/dsk/c1t0d1s6 808G 225G 574G 29% /backup

可以看出有足够Flashback Logs空间；

3、具体配置过程

Step 1. Shut down the database and start up mount. The database must be mounted but not open. （关闭standby数据库并打开为mount状态）

SQL>shutdown immediate;

SQL>startup mount;

SQL> select status from v$instance;

STATUS

------------

MOUNTED

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /backup/archivelog

Oldest online log sequence 6799

Next log sequence to archive 0

Current log sequence 6802

Step 2. Set the flashback retention target to your desired value. We will use 24 hours as the window. （配置 db_flashback_retention_target参数，单位为分钟）

alter system set db_flashback_retention_target=1440 SCOPE=BOTH;

Step 3. Set the values for db_recovery_file_dest and db_recovery_file_dest_size (flash recovery area parameters).（配置闪回区域参数）

Flashback logs increase FRA usage significantly. It would behoove you to at least double the given size of the FRA.

因此具体参数带该如下：

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 600151336 SCOPE=BOTH ;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/backup/fra/' SCOPE=BOTH;

Step 4. Turn flashback logging on. This is done in the same fashion as turning archivelog on-with

an ALTER DATABASE command when the database is mounted but not open.（启用flashback功能）

SQL> alter database flashback on;

Step 5. 因standby库，因此不能open而进入管理模式：

SQL>alter database recover managed standby database disconnect from session;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18841027/viewspace-1054422/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18841027/viewspace-1054422/