1.快照备用测试
-----快照备用就是允许对备库进行修改，并在指定时间周期后，恢复到物理备用数据库。当备库转换为快照方式时，可以继续接收日志，但不应用，待转为备库后在应用日志。
----一般快照备用主要是用于性能的负载测试、功能测试、热恢复测试
----下面做测试
1.1 主库情况
----数据库最大可用状态
sys@ORCL>select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
sys@ORCL>create table shall(id int,name varchar2(10));
Table created.
sys@ORCL>begin
2 for i in 1..10000 loop
3 insert into shall values (i,'zhong');
4 end loop;
5 commit;
6* end;
sys@ORCL>/
PL/SQL procedure successfully completed.
sys@ORCL>select count(*) from shall;
COUNT(*)
----------
10000
1.2 备库设置快照方式
----备库状态
sys@ORCL>select database_role,open_mode,name,db_unique_name from v$database;
DATABASE_ROLE OPEN_MODE NAME DB_UNIQUE_NAME
---------------- -------------------- --------- ------------------------------
PHYSICAL STANDBY READ ONLY WITH APPLY ORCL orclps1
----快照操作时需要用到的空间，及存放大小
sys@ORCL>show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4182M
-----取消日志应用
sys@ORCL>recover managed standby database cancel;
Media recovery complete.
sys@ORCL>shut immediate
sys@ORCL>startup mount
----设置快照模式，需要在mount设置
sys@ORCL>alter database convert to snapshot standby;
Database altered.
sys@ORCL>alter database open;
Database altered.
----现在数据库是读写状态，但是日志只接收不应用
sys@ORCL>select database_role,open_mode,name,db_unique_name from v$database;
DATABASE_ROLE OPEN_MODE NAME DB_UNIQUE_NAME
---------------- -------------------- --------- ------------------------------
SNAPSHOT STANDBY READ WRITE ORCL orclps1
sys@ORCL>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
33 YES
34 NO
35 NO
36 NO
37 NO
38 NO
----使用的空间
sys@ORCL>!ls /u01/app/oracle/fast_recovery_area/ORCLPS1/flashback
o1_mf_ckw6slp2_.flb o1_mf_ckw6sq1k_.flb
-----------此时，主库上的操作，备库上无法看到。备库可以修改当前数据内容，但是在转为备库后自动丢失
----主库上truncate表shall
sys@ORCL>truncate table shall;
Table truncated.
----备库上修改shall表
sys@ORCL>select count(*) from shall;
COUNT(*)
----------
10000
sys@ORCL>begin
2 for i in 1..2345 loop
3 insert into shall values (i,'shall');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
sys@ORCL>select count(*) from shall;
COUNT(*)
----------
12345
----转会物理备库
sys@ORCL>shut immediate
sys@ORCL>startup mount
----需要在mount转回物理备库，此时在快照模式的修改数据将丢失
sys@ORCL>alter database convert to physical standby;
Database altered.
sys@ORCL>shut abort
sys@ORCL>startup
----应用日志
sys@ORCL>alter database recover managed standby database using current logfile disconnect;
Database altered.
sys@ORCL>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
37 YES
38 IN-MEMORY
1 NO
sys@ORCL>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
37 YES
38 YES
1 NO
39 YES
40 YES
2.快照备用+flashback database（备库上部署闪回数据库）
dataguard开启flashback database后，备库上可以随时闪回到过去一段时间内，进行相关的测试。另外最主要的好处在于升级。当数据库升级时，可以先升级备库，在升级主库。升级的过程大致是，先在主库、备库上创建闪回点，然后将物理备库转为逻辑备库，先升级备库，备库升级完成后，切换主库为备库，此时原主库闪回到原备库升级之前的状态，开始进行升级。升级成功后再次切换主、备库，最后将逻辑备库转为物理备库。
当然还有比较重要的就是数据的恢复，比如主库某表truncate或表空间损坏等等，此时可以在备库上闪回到数据正常的时间点进行数据的恢复。
2.1 主库情况
----数据库最大可用状态
sys@ORCL>select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
sys@ORCL>create table zhong(id int,name varchar2(10));
Table created.
sys@ORCL>begin
2 for i in 1..10000 loop
3 insert into zhong values (i,'shall');
4 end loop;
5 commit;
6* end;
sys@ORCL>/
PL/SQL procedure successfully completed.
sys@ORCL>select count(*) from zhong;
COUNT(*)
----------
10000
2.2 备库开启flashback on方式
----备库状态
sys@ORCL>select database_role,open_mode,db_unique_name,flashback_on from v$database;
DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME FLASHBACK_ON
---------------- -------------------- ------------------------------ ------------------
PHYSICAL STANDBY READ ONLY WITH APPLY orclps1 NO
----开启flashback后需要用到的空间，及存放大小
sys@ORCL>show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4182M
-----取消日志应用
sys@ORCL>alter database recover managed standby database cancel;
Database altered.
sys@ORCL>shut immediate
sys@ORCL>startup mount
----在mount中打开flashback on
sys@ORCL>alter database flashback on;
Database altered.
----设置快照模式
sys@ORCL>alter database convert to snapshot standby;
Database altered.
sys@ORCL>alter database open;
Database altered.
----现在数据库是读写状态，但是日志只接收不应用
sys@ORCL>select database_role,open_mode,db_unique_name,flashback_on from v$database;
DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME FLASHBACK_ON
---------------- -------------------- ------------------------------ ------------------
SNAPSHOT STANDBY READ WRITE orclps1 YES
sys@ORCL>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
38 YES
1 NO
39 YES
40 YES
41 YES
42 NO
43 NO
44 NO
45 NO
46 NO
1 NO
47 NO
----使用的空间
sys@ORCL>!ls /u01/app/oracle/fast_recovery_area/ORCLPS1/flashback
o1_mf_ckwd58bq_.flb o1_mf_ckwd5b61_.flb
-----------此时，主库上的操作，备库上无法看到。备库可以修改当前数据内容，但是在转为备库后自动丢失
----备库上进行测试
------创建闪回点
sys@ORCL>create restore point before_test guarantee flashback database;
Restore point created.
sys@ORCL> select name from v$restore_point;
NAME
----------------------------------------------------------------------------------------------------
SNAPSHOT_STANDBY_REQUIRED_04/25/2016 22:59:29
BEFORE_TEST
------查看数据，并进行truncate恢复测试
sys@ORCL>select count(*) from zhong;
COUNT(*)
----------
10000
sys@ORCL>truncate table zhong;
Table truncated.
sys@ORCL>select count(*) from zhong;
COUNT(*)
----------
0
------闪回到truncate之前
sys@ORCL>shut abort
sys@ORCL>startup mount
sys@ORCL>flashback database to restore point before_test;
Flashback complete.
sys@ORCL>alter database open resetlogs;
Database altered.
sys@ORCL>select count(*) from zhong;
COUNT(*)
----------
10000
sys@ORCL>select database_role,open_mode,db_unique_name,flashback_on from v$database;
DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME FLASHBACK_ON
---------------- -------------------- ------------------------------ ------------------
SNAPSHOT STANDBY READ WRITE orclps1 YES
------转会物理备库
sys@ORCL>shut immediate
sys@ORCL>startup mount
----需要在mount转回物理备库，此时在快照模式的修改数据将丢失
sys@ORCL>alter database convert to physical standby;
Database altered.
sys@ORCL>shut abort
sys@ORCL>startup
----应用日志
sys@ORCL>alter database recover managed standby database using current logfile disconnect;
Database altered.
sys@ORCL>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
1 YES
2 NO
3 NO
51 YES
1 NO
52 YES
53 YES
54 YES
55 YES
56 YES
57 YES
58 IN-MEMORY
57 rows selected.
------最后清理闪回点
sys@ORCL>select name from v$restore_point;
NAME
----------------------------------------------------------------------------------------------------
BEFORE_TEST
sys@ORCL>drop restore point before_test;
drop restore point before_test
*
ERROR at line 1:
ORA-16000: database open for read-only access
sys@ORCL>alter database recover managed standby database cancel;
Database altered.
sys@ORCL>shut immediate
sys@ORCL>startup mount
sys@ORCL>drop restore point before_test;
Restore point dropped.
sys@ORCL>alter database open;
Database altered.
sys@ORCL>alter database recover managed standby database using current logfile disconnect;
Database altered.