怎么利用闪回数据库特性恢复failover 后的dataguard 环境

发布网友发布时间：2022-04-28 20:08

我来回答

共2个回答

懂视网时间：2022-04-08 06:27

*db_recovery_file_dest参数为0时，不能设定flash_recovery_area参数*

SQL> alter system set db_recovery_file_dest_size=2147483648 scope=spfile;

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

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=2880; (2 days) |

2. Turn flashback on:

 SQL> Startup mount exclusive;
 SQL> alter database archivelog;
 SQL> Alter database flashback on;
 SQL> Alter database open; |

3. Check status

SQL> SELECT flashback_on, log_mode FROM gv$database;
    SQL> SELECT estimated_flashback_size FROM gv$flashback_database_log;
$ ps -eaf | grep rvwr |

禁用Flashback Database

Alter database flashback on;

Flashback Database Using SQL or RMAN Commands

1.使用SQLPLUS

Use an SCN or a time stamp in the SQL version

Example: Flash back the database to a day before using SQL

SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> flashback database to timestamp(sysdate-1);
SQL> alter database open resetlogs;

2.使用 RMAN

Using RMAN, you can flash back to a time stamp, SCN, or log sequence number (SEQUENCE) and thread number (THREAD).

Example:

 RMAN> FLASHBACK DATABASE TO TIME = TO_DATE(‘2002-12-10 16:00:00‘,‘YYYY-MM-DD HH24:MI:SS‘);
 RMAN> FLASHBACK DATABASE TO SCN=23565;
 RMAN> FLASHBACK DATABASE TO SEQUENCE=223 THREAD=1;

视图

**VFLASHBACKDATABASELOG???monitortheestimatedandactualsizeoftheflashbacklogsintheflashrecovery?Checkflashrecoveryareadiskquota:‘‘‘SQL>selectretentiontarget,flashbacksize,estimatedflashbacksizeFROMVFLASHBACK_DATABASE_LOG;

- Determine the current flashback window:

SQL> SELECT oldest_flashback_scn,oldest_flashback_time FROM VFLASHBACKDATABASELOG;‘‘‘???VFLASHBACK_DATABASE_STAT -** monitors the overhead of logging flashback data in the flashback logs. It contains at most 24 rows, with one row for each of the last 24 hours.

The flashback generation for the last hour:

SQL> select to_char(end_time,‘yyyy-mm-dd hh:miAM‘) end_timestamp, flashback_data, db_data, redo_data from v$flashback_database_stat where rownum=1;

从Flashback Database中排除表空间

SQL> ALTER TABLESPACE <ts_name> FLASHBACK {ON|OFF}
SQL> SELECT name, flashback_on 2 FROM v$tablespace;

Note

Take the tablespace offline before you perform the database flashback operation.

After performing Flashback Database, drop the tablespace or recover the offline files with traditional point-in-time recovery.

Flash back a RESETLOGS operation

可以闪回到resetlogs之前的一个时间点

SQL> FLASHBACK DATABASE TO BEFORE RESETLOGS;

Limitations

You cannot use Flashback Database in the following situations:

The control file has been restored or re-created.

A tablespace has been dropped.

A data file has been shrunk

需要开启FRA特性

闪回数据库的一些命令:

**闪回到指定的日期**
FLASHBACK DATABASE TO TIMESTAMP(to_data(‘2015.07.11 20:03:00‘,‘YYYY.MM.DD HH24:MI:SS‘));
**闪回到5分钟之前**
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
**回退到过去的某个SCN点**
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;
**回退到某个Log Sequence号*
flashback database to sequence=223 thread=1;
**回退完成之后以resetlogs的方式打开数据库*
alter database open resetlogs;

**也可以创建一个恢复点*
CREATE RESTORE POINT before_changes;
FLASHBACK DATABASE TO RESTORE POINT before_changes;

来自为知笔记(Wiz)

闪回数据库

标签：

热心网友时间：2022-04-08 03:35

-----主库defer 日志传输
alter system set log_archive_dest_2=defer;

---enable 日志传输：
alter system set log_archive_dest_2=enable；

-----备库(mount)配置 flashback database：
STANDBY DATABASE: Stop redo apply, configure flashback retention,
start flashback database, open the database and start redo apply (Is active DG).

---检查备库是否启用flashback database:
select flashback_on from v$database;

注意这里需要确认下备库打开模式： mount？readonly with apply？
在11g 环境下备库可能启用了 ADG 特性备库日志处于实时应用，数据库模式为 readonly with apply
这时需要重启数据库到mount状态修改flashback database 模式；

如果备库处于mount 状态，可以先取消日志apply ，直接打开闪回数据库特性；

---取消备库日志应用：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

---需要配置一下两个参数来打开flashback database 特性：
ALTER SYSTEM SET db_recover_file_dest='/lixora/lixora/lixora/';
ALTER SYSTEM SET db_recover_file_dest_size=100G;

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240; ---4hours
ALTER DATABASE FLASHBACK ON;

--手工创建还原点（该步骤没有测试过）：
Creating Restore point in Physical Standby：
CREATE RESTORE POINT before_damage GUARANTEE FLASHBACK DATABASE

-------备库failover to primary db 应急切换步骤：
（注：模拟主库由于故障无法正常switchover，需要执行failover，强制备库->pridb并接管业务）

1.备库：
由于是failover，所以理解主库这时候已经无法正常使用，只需备库切换至pridb

【前提主库还是可用的：可选】查询没有应用的日志：
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;
该语句取得当前数据库各线程已归档文件最大序号，如果primary 与standby 最大序号不相同，
必须将多出的序号对应的归档文件复制到待转换的standby服务器。

Cp过来并register
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1'

停止应用恢复模式
alter database recover managed standby database finish;
or
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

转换standbydb为primary db
alter database commit to switchover to primary;

重启数据库，恢复正常业务
alter database open；

数据库角色查看：
select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
---------- ----------------
OPEN PRIMARY

------恢复failover 的备库：
C. Using SQL*PLUS
Step 1 Determine the Standby Became Primary SCN.
Step 2 Flashback the Failed Primary Database.
Step 3 Convert to physical standby database.
Step 4 Restart Redo Transport.
Step 5 Start Redo Apply.

Step 1 Determine the SCN at which the old standby database became the primary database.
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

Step 2 Flashback the Failed Primary Database to SCN standby_became_primary_scn.
SQL> SHUTDOWN IMMEDIATE;
SQL> startup mount
SQL> FLASHBACK DATABASE TO SCN ;

Step 3 Convert the database to a physical standby database and Restart database in mount stage.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 4 Restart Redo Transport to the New Physical Standby Database.
1. If you have not set the remote archive destination on current primary then set remote archive destination:
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=lixora VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lixora' SCOPE=BOTH;

2. Enable the destination
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

3. Perform a log switch to ensure that standby database begins receiving redo data from the new primary database
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;