DB restore point and datagurad
打完补丁，取消之前设置的隐含参数配置
alter system reset "_kt_debug_flags" scope=spfile sid='*';
########
12.5.1 Flashing Back a Physical Standby Database to a Specific Point-in-Time
The following steps describe how to avoid re-creating a physical standby database after you issued the OPEN RESETLOGS statement on the primary database.
Step 1 Determine the SCN before the RESETLOGS operation occurred.
On the primary database, use the following query to obtain the value of the system change nddddber (SCN) that is 2 SCNs before the RESETLOGS operation occurred on the primary database:
SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# - 2) FROM V$DATABASE;
Step 2 Obtain the current SCN on the standby database.
On the standby database, obtain the current SCN with the following query:
SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
Step 3 Determine if it is necessary to flash back the database.
If the value of CURRENT_SCN is larger than the value of resetlogs_change# - 2, issue the following statement to flash back the standby database.
SQL> FLASHBACK STANDBY DATABASE TO SCN resetlogs_change# -2;
-
If the value of
CURRENT_SCNis less than the value of the resetlogs_change# - 2, skip to Step 4. -
If the standby database's SCN is far enough behind the primary database's SCN, log apply services will be able to continue through the
OPEN RESETLOGSstatement without stopping. In this case, flashing back the database is unnecessary because log apply services do not stop upon reaching theOPEN RESETLOGSstatement in the redo data.
To start Redo Apply on the physical standby database, issue the following statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
The standby database is now ready to receive and apply redo from the primary database.
有可能用到 primary 的 redo Log 做恢复。
####sample :
############sampe 1
primary:
SQL> select to_char(RESETLOGS_CHANGE#,999999999999999999999999) from v$database;
TO_CHAR(RESETLOGS_CHANGE#,999999999999999999999999
--------------------------------------------------
94660018598
SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# - 2) FROM V$DATABASE;
TO_CHAR(RESETLOGS_CHANGE#-2)
--------------------------------------------------------------------------------
94660018596
standby:
SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
TO_CHAR(CURRENT_SCN)
--------------------------------------------------------------------------------
94659996840
Col name format a46
Select name, sequence#, first_change# FROM v$archived_log where first_change > 94659996840;
standby：
94660018596
alter database recover managed standby database cancel;
SBY>shutdown immediate;
startup mount;
flashback database to scn 94660018596;
alter database open;
select recid,thread#,sequence#,first_change#,next_change# from v$log_history where first_change > 94659996840 and next_change# < 94660018596 ;
###########primary
Completed: ALTER DATABASE RECOVER database until cancel
Thu May 31 20:03:14 2018
alter database open resetlogs
RESETLOGS after complete recovery through change 94660018597 -》 94660018597
Archived Log entry 96541 added for thread 1 sequence 542 ID 0x6dc2cadd dest 1:
Resetting resetlogs activation ID 1841482461 (0x6dc2cadd)
Thu May 31 20:09:37 2018
Setting recovery target incarnation to 3
Thu May 31 20:09:37 2018
Assigning activation ID 1841807754 (0x6dc7c18a)
LGWR: STARTING ARCH PROCESSES
Thu May 31 20:09:37 2018
ARC0 started with pid=23, OS id=29249
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu May 31 20:09:38 2018
ARC1 started with pid=24, OS id=29251
Thu May 31 20:09:38 2018
ARC2 started with pid=25, OS id=29253
Thu May 31 20:09:38 2018
ARC3 started with pid=26, OS id=29255
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /db/osas/data/osas/redo02.log
############# stndbay
Thu May 31 20:09:37 2018
Setting recovery target incarnation to 3
Thu May 31 20:09:37 2018
Assigning activation ID 1841807754 (0x6dc7c18a)
LGWR: STARTING ARCH PROCESSES
Thu May 31 20:09:37 2018
ARC0 started with pid=23, OS id=29249
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu May 31 20:09:38 2018
ARC1 started with pid=24, OS id=29251
Thu May 31 20:09:38 2018
ARC2 started with pid=25, OS id=29253
Thu May 31 20:09:38 2018
ARC3 started with pid=26, OS id=29255
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /db/osas/data/osas/redo02.log
Thu May 31 20:09:40 2018
RFS[820]: Assigned to RFS process 121969
RFS[820]: New Archival REDO Branch: 977601794 Current: 977336000
RFS[820]: Selected log 20 for thread 1 sequence 1 dbid 1825387057 branch 977601794
RFS[820]: New Archival REDO Branch(resetlogs_id): 977601794 Prior: 977336000
RFS[820]: Archival Activation ID: 0x6dc7c18a Current: 0x6dc2cadd
RFS[820]: Effect of primary database OPEN RESETLOGS
RFS[820]: Incarnation entry added for Branch(resetlogs_id): 977601794 (osas) <- add new branch
Thu May 31 20:09:40 2018
Setting recovery target incarnation to 3
Thu May 31 20:09:40 2018
Archived Log entry 51855 added for thread 1 sequence 1 ID 0x6dc7c18a dest 1:
Thu May 31 20:09:41 2018
RFS[821]: Assigned to RFS process 121973
RFS[821]: Selected log 17 for thread 1 sequence 542 dbid 1825387057 branch 977336000
Thu May 31 20:09:41 2018
Archived Log entry 51856 added for thread 1 sequence 542 ID 0x6dc2cadd dest 1:
FS[821]: Assigned to RFS process 121973
RFS[821]: Selected log 17 for thread 1 sequence 542 dbid 1825387057 branch 977336000
Thu May 31 20:09:41 2018
Archived Log entry 51856 added for thread 1 sequence 542 ID 0x6dc2cadd dest 1:
Thu May 31 20:09:42 2018
RFS[822]: Assigned to RFS process 121977
RFS[822]: Selected log 20 for thread 1 sequence 2 dbid 1825387057 branch 977601794
Thu May 31 20:09:42 2018
Primary database is in MAXIMdddd PERFORMANCE mode
RFS[823]: Assigned to RFS process 121979
RFS[823]: Selected log 21 for thread 1 sequence 3 dbid 1825387057 branch 977601794
Thu May 31 20:09:42 2018
Archived Log entry 51857 added for thread 1 sequence 2 ID 0x6dc7c18a dest 1:
Thu May 31 20:18:38 2018
alter database recover managed standby database disconnect
Attempt to start background Managed Standby Recovery process (osas)
Thu May 31 20:18:38 2018
MRP0 started with pid=31, OS id=122648
MRP0: Background Managed Standby Recovery process started (osas)
started logmerger process
hu May 31 20:18:43 2018
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 80 slaves
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 94660018597 《- scn 94660018597
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 541 branch(resetlogs_id) 977336000
Fetching gap sequence in thread 1 branch(resetlogs_id) 977336000, gap seq 541-541
Completed: alter database recover managed standby database disconnect
Thu May 31 20:20:35 2018
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 541-541
DBID 1825387057 branch 977336000
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
------------------------------------------------------------
######## 0
http://www.oracle-wiki.net/startdocshowtoconfigflashdb
1.flashback database
flashback database 如果 不开的话 ，那么就会使用undo log 来回滚数据库，回滚的时间大概在一天以内。
flashback database 如果 开的话，就是会使用flashback log 来回滚数据库，回滚的时间可以延长到好几天以前或者any time.
- Only run alter database flashback on; if there is a requirement to flashback to ANY previous point in time.
2.检查当前的回滚点的情况语句如下：
sqlplus '/ as sysdba'
SQL> select name, time,guarantee_flashback_databse from v$restore_point;
SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
SQL> quit
##sample
目的:做flashback point 一般是为了将容灾库拉起来应急，
或者对数据库进行破环操作的回退，
在本次案例中，我们是将之前 已经a 已经对 进行了 数据的删除，现在需要给b 做实验，所以需要回退数据库，在重新发起删除操作
1.Determine if Flashback Database is Already Enabled
select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
2.1. Identify the Restore point
select name, time,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
NAME
--------------------------------------------------------------------------------
TIME GUA
--------------------------------------------------------------------------- ---
BEFORE_CLEAR_110303
03-MAR-21 11.07.59.000000000 AM YES
#15:09 begin time, 28结束，根据清理时间评估，清理数据花了2小时，实际 回退到清理数据之前的版本 point 也要20分钟。
sqlplus '/ as sysdba'
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to restore point BEFORE_CLEAR_110303;
##15:32 开始， 37完成
SQL> alter database open resetlogs;
Dropping a Restore Point
1. Restore points can be dropped with the database open using the following commands
sqlplus '/ as sysdba'
SQL> drop restore point BEFORE_CLEAR_110303;
SQL> quit
在新建一个restore point.
sqlplus '/ as sysdba'
SQL> create restore point BEFORE_CLEAR_110312 GUARANTEE FLASHBACK DATABASE;
####
Flashback database is useful feature introduced with 10g that allows the database to be effictively rewound to a previous point in time.
This feature is particularly useful for test, V&P, and development environments where you may wish to try out a new piece of code or fuctionality over and over, refining as you go. Rather than having to restore the database after each test, flashback database allows the database to be rewound very quickly to a previous point in time.
Flashback database can be effectively run in two modes. One mode where you log every change to every block, which allows you to flahsback the database to any previous point in time. Or another mode, where Oracle only tracks the "before" images of changed blocks, so that you can flashback to the start of the restore point.
How to Set-up Flashback Database
1. Ensure db_recovery_file_dest is set.
- sqlplus '/ as sysdba'
- SQL> alter system set db_recovery_file_dest='+<FRA Diskgroup>' SCOPE=spfile;
2. Ensure db_recovery_file_dest_size is set
- SQL> alter system set db_recovery_file_dest_size=100G SCOPE=spfile;
3. Stop and start the database
- sqlplus '/ as sysdba'
- SQL> shutdown immediate;
- SQL> startup mount;
- If flashback to any previous point in time is required, then turn flashback on using the following command
- SQL> alter database flashback on;
- SQL> alter database open;
- SQL> alter system set db_flashback_retention_target=2880;
NOTES
- Set the db_recovery_file_dest to an appropriate location for the flashback recovery files.
- Set the db_recovery_file_dest_size to an appropriate size for the amount and size of the testing required.
- Set the db_flashback_retention_target to an appropriate time, in mins, to retain flashbackability.
- Only run alter database flashback on; if there is a requirement to flashback to ANY previous point in time.
Determine if Flashback Database is Already Enabled
1. Run the following commands to determing Flashback is turned on.
- sqlplus '/ as sysdba'
- SQL> select flashback_on from v$database;
Creating and Using Flashback Restore points.
This worked example assddddes the database is using ASM to manage its storage.
Createing a Restore point
Create a restore point whenever the database is at a state that it may needed to be flashed back to. Use the optional GUARANTEE FLASHBACK DATABASE clause to ensure that the restore point is not aged out of the flashback recovery area (FRA) as dictated by the db_flashback_retention_target parameter.
1. You may want to create the restore point in mount mode. If so, put the database into mount mode now.
2. Create a restore point
- sqlplus '/ as sysdba'
- SQL> create restore point <restore point name> [GUARANTEE FLASHBACK DATABASE];
Rolling Back to a Restore Point
1. Identify the Restore point
- sqlplus '/ as sysdba'
- SQL> select name, time,guarantee_flashback_databse from v$restore_point;
- SQL> quit
2. For a non RAC environment use the following commands to flashback to a restore point.
- sqlplus '/ as sysdba'
- SQL> shutdown immediate;
- SQL> startup mount;
- SQL> flashback database to restore point <restore point name>;
- SQL> alter database open resetlogs;
3. For RAC instances use the following commands.
- One one of the nodes run, srvctl stop database -d <database name> -o immediate
- sqlplus '/ as sysdba'
- SQL> startup mount;
- SQL> flashback database to restore point <restore point name>;
- SQL> alter database open resetlogs;
- SQL> shutdown immediate;
- SQL> quit
- srvctl start database -d <database name>
- Run crs_stat -t to confirm that the database is backup okay.
NOTES
- Any tables created and updated without the LOGGING option will be suseptable to block curruption errors when the database is flashed back. These can be remedied by issuing the TRUNCATE TABLE command against the affected object(s).
Dropping a Restore Point
1. Restore points can be dropped with the database open using the following commands
- sqlplus '/ as sysdba'
- SQL> drop restore poijnt <restore point name>;
- SQL> quit
Monitoring Flashback Logging
After enabling flashback logging, Oracle keeps track of the amount of logging generated. This can be queried from v$flashback_database_log, the estimate gets better with age. Note that this is the size of the flashback logs only and does not include space used by archive logs and RMAN backups.
1. Monitor flashback logs
- sqlplus '/ as sysdba'
- SQL> select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;
- SQL> quit
Finding the Earliest Flashback Point
Querying V$flashback_database_log will show you the earliest point you can flashback your database to based on the size of the FRA and the currently available flashback logs.
1. Find the earliest flashback point
- sqlplus '/ as sysdba'
- SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
- SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
- SQL> quit
Disabling Flashback Database
Full any previous point in time flashback can be disabled with the database open. Any unused Flashback logs will be automatically removed at this point and a message detailing the file deletion written to the alert log.
1. Disabling flashback
- sqlplus '/ as sysdba'
- SQL> ALTER DATABASE FLASHBACK OFF;
- SQL> quit
Troubleshooting
The following common errors can occur.
Message
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
Cause
Oracle needs to have the required archive logs in the archive destination at the time of flashback.
Solution
Use rman to restore the missing archive logs. The sequence and thread nddddbers are in the error message.
rman target /
run{
allocate channel t1 device type 'sbt_tape';
FLASHBACK DATABASE TO RESTORE POINT <restore point name>;
}
Now resdddde the flashback process from the RESETLOGS step.
Useful Scripts for Flashback
The following scripts can be used for flashing back a database automatically for a group of users. For example, a training environment.
################################### sample 1
###On the standby
Stop the apply process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Create a guaranteed restore point
SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;
Start the apply process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT;
###On the primary
Create a guaranteed restore point
SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;
####in primary :
----恢复到闪回点
SQL> shut abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1272213504 bytes
Fixed Size 1336260 bytes
Variable Size 922750012 bytes
Database Buffers 335544320 bytes
Redo Buffers 12582912 bytes
Database mounted.
SQL> flashback database to restore point SWITCHOVER_START_GRP;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
###in standby db;
Note: If a guaranteed restore points are created, make sure they are dropped post-switchover!
##########after switch over :
On the standby
SQL> drop restore point SWITCHOVER_START_GRP;
On the primary
Create a guaranteed restore point
SQL> drop restore point SWITCHOVER_START_GRP;
#####sample 2:
#####
当前数据库是A->B（DGA） ,A->C(DGC) ,一主两备。
B.C设置强制回滚点
现在是临时将B激活成主库，同C 库构成一个新的主备库关系，以用来做测试使用，在这期间，确保DB_RECOVERY_FILE_DEST_SIZE足够大。
测试完成，通过将B库/C库还原到强制还原点，同时再次与A库形成一主两备库模式
DG A:
25.8.1.7/8
DG B
58.2.101.3/4
####step 0 disable log_stat_2 and log_dest_3 主库A 关掉同步关系
alter system set log_archive_dest_state_2=defer sid='*' scope=both;
alter system set log_archive_dest_state_3=defer sid='*' scope=both;
### step 1: setting DG A and DG b a restore point
创建强制回滚点，同时将B 库 （DG A） 设置为主库。方式failover
DG A:(create restore point and faiover and active DG A):
stop node 2 instance;
###below all done in instance 1
show parameter DB_RECOVERY_FILE_DEST_SIZE
50
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
startup mount
select to_char(RESETLOGS_CHANGE#,999999999999999999999999) from v$database;
CREATE RESTORE POINT DGA_START_GRP GUARANTEE FLASHBACK DATABASE;
select name,time,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
104986223361
select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*)
from v$datafile_header
group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;
alter database recover managed standby database finish;
alter database commit to switchover to primary WITH SESSION SHUTDOWN;
（WARNING: This will only succeed if the correct RECOVER FINISH-statement was
issued before. If you forgot the 'SKIP STANDBY LOGFILE' although you
have no Standby RedoLogs, the COMMIT to Switchover will fail with the
error that more Media Recovery is required here.
- If the COMMIT TO SWITCHOVER fails for any reason you have to use the ACTIVATE
command which forces the Failover (and may cause Data Loss !!)
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
）
alter database open;
SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
104986223918
###C库（DG B ）创建强制回滚点
DG B (create restore point):
stop node 2 instance;
show parameter DB_RECOVERY_FILE_DEST_SIZE
120
SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
TO_CHAR(CURRENT_SCN)
----------------------------------------
104986223362
###停下C(dgb)的节点2 ，所有操作在节点1操作，all done in instance 1
shutdown immediate
startup mount
select to_char(RESETLOGS_CHANGE#,999999999999999999999999) from v$database;
CREATE RESTORE POINT DGB_START_GRP GUARANTEE FLASHBACK DATABASE;
select name,time,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
TO_CHAR(CURRENT_SCN)
----------------------------------------
104986223362
###DG A: (chage log_arch_dest paramter to make DG A and DG B to a new DG SYNC conf)
（修改DGA(B) 同步归档参数，确保DGA 归档到DG B,同时不会同步归档到primary, 这2个库构成一个新的DG 关系，以用来测试）
fal_client dba2-old/dba1-old
fal_server dba
log_archive_dest_2 SERVICE=dba lgwr async noaffirm reopen=60 valid_for=(online
_logfiles,primary_role) db_unique_name=dba
log_archive_dest_3 SERVICE=rdba lgwr async noaffirm reopen=60 valid_for=(stand
by_logfiles,standby_role) db_unique_name=rdba
log_archive_dest_state_2 ENABLE
log_archive_dest_state_3 DEFER
change:
alter system set log_archive_dest_3='SERVICE=rdba lgwr async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=rdba' sid='*' scope=both;
alter system set log_archive_dest_state_3=ENABLE sid='*' scope=both;
alter system set log_archive_dest_state_2=defer sid='*' scope=both;
###DG B: （begin MRP ,dg sync is ok）
（DGB (C) 开启MRP,测试同步是否正常）
--ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
alter database recover managed standby database using current logfile disconnect;
fal_client dba1-1-old/dba2-1-old
fal_server dba
log_archive_config DG_CONFIG=(dba,sdba,rdba)
log_archive_dest_2 SERVICE=sdba lgwr async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=sdba
log_archive_dest_3 SERVICE=dba lgwr async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=dba
log_archive_dest_state_2 ENABLE
log_archive_dest_state_3 ENABLE
change:
alter system set fal_server=rdba sid='*' scope=both;
alter system set fal_client='dba1-1-old' sid='dba1' scope=both;
alter system set log_archive_dest_state_3=defer sid='*' scope=both;
####开始DGA 和DGB 的主库和容灾库的测试，该测试主要关注的是，容灾库是否只要打开一个节点，还是同时打开2个节点，
###因为发现容灾库在同时打开2个节点时候，测试使用脚本切换的时候，时间会超过15分钟。
###因为发现容灾库在同时打开1个节点时候，测试使用脚本切换的时候，时间会超过5分钟。
##after both standby rac is rstart.开启2个节点的容灾库，开始测试
##prim lns communicate with standby rfs will need 3 minutes,主要关注LNS 写入速度
#-- Query v$managed_standby to see the status of processes involved in the shipping redo on this system.
#--Does not include processes needed to apply redo.
set linesize 900
SELECT inst_id, thread#, process, pid, status, client_process, client_pid, sequence#, block#, active_agents, known_agents FROM gv$managed_standby ORDER BY thread#, pid;
--DG 库端，关注RFS和LGWR 的通信 ,正常情况下，instance1有2个process RFS 跟主库的LGWR通信 ，instance 2 有2个process RFS 跟主库的LGWR通信
set linesize 900
SELECT inst_id, thread#, process, pid, status, client_process, client_pid, sequence#, block#, active_agents, known_agents FROM gv$managed_standby ORDER BY thread#, pid;
INST_ID THREAD# PROCESS PID STATUS CLIENT_P CLIENT_PID SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- ---------- --------- ---------- ------------ -------- ---------------------------------------- ---------- ---------- ------------- ------------
1 2 RFS 11862220 IDLE LGWR 26083514 31 135 0 0
1 1 RFS 10486134 IDLE LGWR 11993218 40 612
--关闭一个standby instance2, 该实例instance2 跟prim实例2通信的RFS会消失，只等主库2日志切换达到4-5次，才能再次看到RFS，可以理解成RFS的切换
INST_ID THREAD# PROCESS PID STATUS CLIENT_P CLIENT_PID SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- ---------- --------- ---------- ------------ -------- ---------------------------------------- ---------- ---------- ------------- ------------
1 2 RFS 11862220 IDLE LGWR 26083514 38 135 0 0
###开始DG A (prim)和DG B (standby)的switchover.测试完成
DG A (prim)
shutdown instance 2
##below is all done instance 1
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
RESOLVABLE GAP
10 seconnds
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
alter system switch logfile ;
alter system archive log current;
alter system checkpoint;
select switchover_status from v$database;
SESSIONS ACTIVE
alter database commit to switchover to physical standby with session shutdown;
DG B (standby)
select switchover_status from v$database;
alter database commit to switchover to primary with session shutdown;
shutdown immediate
startup
DG A (prim)
conn / as sysdba
startup
alter database recover managed standby database using current logfile disconnect;
###测试完成后，开始用回滚点恢复dga (prim)和dgb(now pimary) 到回滚点，同时与A库构成一主两备的模式 ,begin to test remote dg restore potint
##(发现当数据库是standby 库，回滚数据库 到回滚点是可以顺利进行，总是提示开始恢复归档日志时候，归档日志序列号不对，只能通过迂回方式，先active standby到primary,在恢复到回滚点)
DG A （now standby ,）:
startup mount
stop mrp
alter database recover managed standby database finish;
alter database commit to switchover to primary WITH SESSION SHUTDOWN;
(now primary)
flashback database to restore point DGA_START_GRP;
alter database convert to physical standby;
select open_mode from v$database;
start mrp
drop restore point DGA_START_GRP;
DG B: (now pimary)
startup mount
stop mrp
flashback database to restore point DGB_START_GRP;
alter database convert to physical standby;
select open_mode from v$database;
start mrp
drop restore point DGB_START_GRP;
https://blog.csdn.net/u012366626/article/details/40003609
ORA-10456: cannot open standby database; media recovery session may be in
How To Failover and Flashback a Physical Standby for testing without recreating broker configuration and database (文档 ID 2060572.1)
####issue after flashback db to standby mode ,mrp start failed with error
日志
Managed Standby Recovery not using Real Time Apply
Mon May 27 17:44:20 2019
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 104987799903) is orphaned on incarnation#=1
MRP0: Detected orphaned datafiles!
Recovery will possibly be retried after flashback...
Errors in file /db/dba/oracleapp/database/11.2.0/diag/rdbms/rdba/dba1/trace/dba1_pr00_12583054.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+dba_DATA/dba/datafile/system.283.964975893'
Recovery Slave PR00 previously exited with exception 19909
Mon May 27 17:44:40 2019
检查：
pri:
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 dba 1071884928 CURRENT 1 20-NOV-14
standby
RMAN> list incarnation of database;
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 dba 1071884928 PARENT 1 20-NOV-14
2 2 dba 1071884928 PARENT 104986223364 24-MAY-19
3 3 dba 1071884928 CURRENT 104987083382 27-MAY-19
solution:
RMAN> reset database to incarnation 1;
########### 2
很多人在学习flashback database这个oracle技术的时候，都会有一个疑问，就是如果我只有一个数据库作为生产库的话，是否有这样的业务需求导致我们要把数据库闪回到以前的时间点？以及这样做是否值得（当前时间点和闪回目的时间点中间的所有操作将丢失）？
其实抛开这个问题的讨论，在oracle的dataguard环境配置中，对于故障切换后产生故障的主机从新回到dataguard环境，以及处于测试的目的我们人为的激活了备库，flashback database都可以提供非常好的选择。
下面的例子描述了这样一个场景：
用户需要一个和生产系统一样或者类似的测试环境，但是单纯的搭建一个环境显然是不值得的，对于部署了dataguard环境的生产系统来说，我们可以选择把备库临时激活，以读写的方式打开，用于测试的需要，等测试结束后，把备库通过flashback database技术闪回到激活之前的时间点，从新的加入到dataguard配置环境中。
第1 步 准备要被激活的物理备数据库。
1 ．检查数据库的flashback database模式和闪回日志存放的目录。（也可以是NO ,只是会使用undo 回滚）
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
2 ．由于备库在应用日志，所以要取消日志应用，并创建一个激活之前的还原点。
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CLOSING
MRP0 WAIT_FOR_LOG
RFS IDLE
RFS IDLE
RFS IDLE
6 rows selected.
SQL> recover managed standby database cancel;
SQL> create restore point test_scn guarantee flashback database;
Restore point created.
第2 步 准备主数据库
1 ．归档当前日志文件。
在主数据库上，切换日志使得还原点（在步骤 1 中创建）的 SCN 将在物理备数据库上被归档：
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
当使用备重做日志文件时，这个步骤是必须的以确保数据库能被正确地闪回到还原点。
2 ．延迟指向将被激活的备的日志归档目的地。
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
System altered.
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string DEFER
第3 步 激活物理备数据库。
在物理备数据库上，执行下述步骤：
1 ．激活物理备数据库并打开到open状态：
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> ALTER DATABASE OPEN;
第4 步 使用激活的数据库用于报表或测试。
一旦备数据库已经被激活，你能运行报表工具或执行其它测试并激活几天甚至几周，独立于主数据库。
警告：当数据库被激活时，它不从主数据库接收重做数据库并不能提供灾难保护。建议至少有
两个物理备数据库参与配置，使得主数据库保持对数据丢失的保护。
第5 步 回复激活的数据库回到物理备数据库。
在你完成测试之后，你需要重新与主数据库同步激活的数据库。在激活的数据库上执行
下面语句以快速闪回它到保障的还原点并将它重新与主数据库同步：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219160 bytes
Variable Size 75498920 bytes
Database Buffers 234881024 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> flashback database to restore point test_scn;
Flashback complete.
SQL> alter database convert to physical standby;
Database altered.
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219160 bytes
Variable Size 75498920 bytes
Database Buffers 234881024 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> recover managed standby database disconnect;
Media recovery complete.
第6 步 重新允许归档到物理备数据库目的地。
在主数据库上，执行下面语句来重新允许归档到物理备数据库：
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
第7 步 测试dataguard环境工作正常
1.在主库删除表空间usertest，并切换日志
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db01/system01.dbf
/u01/app/oracle/oradata/db01/undotbs01.dbf
/u01/app/oracle/oradata/db01/sysaux01.dbf
/u01/app/oracle/oradata/db01/users01.dbf
/u01/app/oracle/oradata/db01/example01.dbf
/u01/app/oracle/oradata/db01/usertest01.dbf
6 rows selected.
SQL> drop tablespace usertest including contents and datafiles;
Tablespace dropped.
SQL> alter system switch logfile;
System altered.
2.在备库观察日志操作是否正确应用，下面的查询可以看到文件应经被删除，说明主库的日志应用到备库。
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------------
/u01/app/oracle/oradata/db01/system01.dbf
/u01/app/oracle/oradata/db01/undotbs01.dbf
/u01/app/oracle/oradata/db01/sysaux01.dbf
/u01/app/oracle/oradata/db01/users01.dbf
/u01/app/oracle/oradata/db01/example01.dbf
################33
11G DATAGUARD: FLASHBACK STANDBY AFTER RESETLOGS ON PRIMARY
4）闪回日志在出现空间压力的情况下，oracle会自动删除闪回日志，则有可能导致无法闪回到指定的时间点。如果希望闪回到指定的时间点，可进行如下操作
----创建闪回点
SQL> create restore point flashback_20160515 guarantee flashback database;
Restore point created.
SQL> select name from v$restore_point;
NAME
----------------------------------------
FLASHBACK_20160515
----恢复到闪回点
SQL> shut abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1272213504 bytes
Fixed Size 1336260 bytes
Variable Size 922750012 bytes
Database Buffers 335544320 bytes
Redo Buffers 12582912 bytes
Database mounted.
SQL> flashback database to restore point flashback_20160515;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
----清理闪回点
SQL> select name from v$restore_point;
NAME
----------------------------------------
FLASHBACK_20160515
SQL> drop restore point flashback_20160515;
Restore point dropped.
SQL> select name from v$restore_point;
no rows selected
###sample 9
Wed Dec 11 15:07:53 2019
RFS[47]: Selected log 10 for thread 1 sequence 349409 dbid 2063200898 branch 823972482
Wed Dec 11 15:09:06 2019
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 340945-340962
DBID 2063200898 branch 823972482
FAL[client]: All defined FAL servers have been attempted.
-----------------------------------------------------
select * from v$archived_log b where b.SEQUENCE#>=340945 and b.SEQUENCE#<=340962 and b.DEST_ID=1 and b.DELETED='YES';
找出丢失的归档 可以 os copy或者rman 恢复
恢复单独归档
list backup of archivelog from sequence 8 until sequence 9 thread 1;
restore archivelog from sequence 13 until sequence 14 thread 2;
————————————————
版权声明：本文为CSDN博主「悠悠m」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/sjh_orcl/article/details/15298773
###sample
11g 强制拉起db.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
SELECT database_role,SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE OPEN;
10g 强制db
alter database activate physical standby database;
#######sample 5
###本文目的是提前在standby 准备restore point, 然后临时启用standby 为读写状态，待测试完成后，
##将 standby 回退到restore point ,重新开始同步DG
pri pdddddb03
std sdddddb01
以下操作都是在汕尾 .3:1528 sdddddb01 上操作
### 开始准备On the standby done
hagrp -freeze sg_dddd_db
SELECT database_role,SWITCHOVER_STATUS FROM V$DATABASE;
select '- value->',value, '- lag seconds->',substr(value,2,2)*24*3600+substr(value,5,2)*3600+substr(value,8,2)*60+substr(value,11,2) seconds from v$dataguard_stats where name='apply lag'
Stop the apply process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> shutdown immediate
SQL> alter databse mount;
Create a guaranteed restore point
SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;
SQL>select name,time,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
SQL>SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
2921999457
Start the apply process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
cd /os/dddd/app/product/database/diag/rdbms/rdddd/dddd/trace
tail -f alert*.log
## 开始已切换 done
in standby database;
SELECT database_role,SWITCHOVER_STATUS FROM V$DATABASE;
alter database recover managed standby database finish;
alter database commit to switchover to primary WITH SESSION SHUTDOWN;
SQL> shutdown abort
startup
###二个小时后， 切换完验证完成后，业务 in standby db， 开始回退到问题时间点,重新同步
On the standby 58.2.103.3:1528 sdddddb01
SELECT database_role,SWITCHOVER_STATUS FROM V$DATABASE;
shutdown immediate
startup mount;
flashback database to restore point SWITCHOVER_START_GRP;
SQL>SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE; ->一定要将整个数据库还原到 standby 时间点的的 scn ，这样才能应用，重新同步
2921999457
shutdown immediate
startup mount;
#注意：db 不能open, open 情况下 scn 会跳变的
##mount 情况下scn不会跳变
ALTER DATABASE CONVERT TO PHYSICAL STANDBY; ->mount 情况切换到standby ,这样scn号不会出现跳变
shutdown immediate
startup mount;
SELECT database_role,SWITCHOVER_STATUS FROM V$DATABASE;
select name,time,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
Start the apply process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
SQL>select '- value->',value, '- lag seconds->',substr(value,2,2)*24*3600+substr(value,5,2)*3600+substr(value,8,2)*60+substr(value,11,2) seconds from v$dataguard_stats where name='apply lag'
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> alter database open;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
SQL> alter database open;
On the standby
On the standby
SQL> alter database mount;
SQL> drop restore point SWITCHOVER_START_GRP;
SQL> alter database open;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
SQL> drop restore point SWITCHOVER_START_GRP;
hagrp -unfreeze sg_ddd_db
########sample 6 ORA-00326: 就会存在一个问题，备库scn 领先于主库，
注意：open 情况下 scn 会跳变的
mount 情况下scn不会跳变
在线克隆单机虚拟机数据库，克隆完成，一定不要open,只能再Mount 情况下执行，ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
如果open了，这样scn号出现跳变，在想重新恢复主库和从库的关系，就会存在一个问题，备库scn 领先于主库，
再执行recover managed standby database 提示如下：
log begins at change 1134009624, need earlier change 1133799604
--
Media Recovery failed with error 326
Errors in file /dg/oracle/app/product/diag/rdbms/rdg/dg/trace/dg_pr00_27875.trc:
ORA-00283: recovery session canceled due to errors
ORA-00326: log begins at change 1134009624, need earlier change 1133799604
ORA-00334: archived log: '/dg/oracle/app/product/fast_recovery_area/Rdg/archivelog/2022_05_17/o1_mf_1_344_k8677zhk_.arc.bak'
ORA-283 signalled during: ALTER DATABASE RECOVER managed standby database ...
原因分析：
因为当前的备库 是克隆过去的，并且克隆完成后，以主库方式open了一下，然后再转换成physical standby , 所以该库scn号领先于 主库，这样就会导致这个报错 ORA-00326:
解决办法：
重建dg 库
step1 : 停下库， 开始克隆数据库到新服务器。
step2: 在旧服务器上生成控制文件的备份文件，然后复制到新服务器，然后开始启动恢复。（一定确保不要以主句的方式open）
step 3,观察恢复的日志，确保从库的日志不会出现
参考文档
参考步骤：
操作步骤
-》准备一个已经搭好的单实例读写库
-》停库克隆出一个新服务器，并修改服务器ip及/etc/hosts文件
-> 主从库按正常配置监听及tns文件，启动监听
-> 主库启动到mount状态
->创建standby controlfile
->ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/erpdg.ctl';
->将standby controlfile拷贝到克隆的从库，并根据参数文件修改路径与文件名
->从库设置参数文件，按正常修改dg相关参数 log_archive_config,log_archive_dest_1,log_archive_dest_2,db_unique_name,fal_client,fal_server
->启动主库
-》 启动从库为standby 模式， -》值得 注意的是吗，从库这个时候才能启动为Open, 如果提前启动，可能会有存在问题ora-00326
-》检查主从同步情况
-》检查从库是否要重建standby redologfile;
————————————————
版权声明：本文为CSDN博主「Hehuyi_In」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Hehuyi_In/article/details/120630766
https://blog.csdn.net/Hehuyi_In/article/details/120630766
https://blog.csdn.net/weixin_41561862/article/details/104186255
