导航：首页 > 数据库 >

Oracle 11g physical dataguard之快照备用

发表于：2024-10-09 作者：千家信息网编辑

千家信息网最后更新 2024年10月09日，在oracle 10g要准备一个读写备用的数据库还是很繁琐的，准备好dataguard后得手动创建还原点，手动停日志传送，手动激活并强制打开，测试完了，如果主备的SCN差太多，你还得做增量备份追，统计

千家信息网最后更新 2024年10月09日Oracle 11g physical dataguard之快照备用

在oracle 10g要准备一个读写备用的数据库还是很繁琐的，准备好dataguard后得手动创建还原点，手动停日志传送，手动激活并强制打开，测试完了，如果主备的SCN差太多，你还得做增量备份追，统计了下需15步，和搭一个physical standby的步骤差不多了，所以用的极少。到11g里终于解放了，启用快照备库只需3步(当然中间重启的次数不算),恢复到实时应用备用也只需2步，日志还是继续传，需要镜像库测试的朋友，可以放心用了(用dataguard borker更简单)。当然转换成Snapshot Standby，是有些附加条件的(没有的参照前文去搭建一个)：
1 数据库闪回得打开；
2 db_recovery_file_dest_size还是要有足够的空间的；
3 如果使用的保护模式是Maximum Protection模式，必须有其他的Standby与之相匹配,否则小心主库宕机。
手动做的步骤如下：
１检查闪回

 SQL> select flashback_on,database_role,open_mode from v$database;  FLASHBACK_ON       DATABASE_ROLE    OPEN_MODE------------------ ---------------- --------------------NO                 PHYSICAL STANDBY READ ONLY WITH APPLY

当前Standby状态是只读Apply状态，这个时候需要终止Apply过程，并且切换回mount状态。否则是不允许进行convert动作的。

SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.SQL> alter database flashback on;alter database flashback on*ERROR at line 1:ORA-38706: Cannot turn on FLASHBACK DATABASE logging.ORA-38709: Recovery Area is not enabled.

报错了，这个错误好解决：

 SQL> show parameter db_recovery_fileNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest                stringdb_recovery_file_dest_size           big integer 51000MSQL> alter system set db_recovery_file_dest='/data';SQL> alter database flashback on;SQL> select flashback_on from v$database; FLASHBACK_ON------------------YES

2 转换

SQL> alter database convert to snapshot standby;SQL> alter  database open;

有兴趣的可以看下alert_sid.log
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1974538
Resetting resetlogs activation ID 1662850232 (0x631d14b8)
Online log /data/db/onlinelog/group_1.261.899048765: Thread 1 Group 1 was previously cleared
Online log /data/db/onlinelog/group_2.260.899048765: Thread 1 Group 2 was previously cleared
Online log /data/db/onlinelog/group_3.277.899049819: Thread 2 Group 3 was previously cleared
Online log /data/db/onlinelog/group_4.278.899049819: Thread 2 Group 4 was previously cleared
Online log /data/db/onlinelog/group_5.280.908381663: Thread 1 Group 5 was previously cleared
Online log /data/db/onlinelog/group_6.281.908381749: Thread 1 Group 6 was previously cleared
Online log /data/db/onlinelog/group_7.282.908381877: Thread 1 Group 7 was previously cleared
检查下当前数据库状态：

 SQL> select open_mode, database_role, protection_mode from v$database;OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE-------------------- ---------------- --------------------READ WRITE           SNAPSHOT STANDBY MAXIMUM AVAILABILITY

已经变成可写状态，查询flash_back开始的SCN:

 SQL> select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;OLDEST_FLASHBACK_SCN OLDEST_FLASH-------------------- ------------             1974537 17-MAY-17

从这里开始可以对备库进行任何操作：

SQL> create table  test  as select * from all_objects; Table created. SQL> select count(*) from test;   COUNT(*)----------     14629 SQL> drop table STAGE_TERADATA_OFFLINE_PKEYS purge; Table dropped.

切回：
1 关库，切换

 SQL>shutdown immediateSQL>startup mount;SQL> alter database convert to physical standby;

这里查看alert_sid.log可以看到
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
删除了还原点
2 关库，关闪回，启用real time apply

SQL>shutdown immediate;SQL>startup mount;SQL>alter database flashback off;SQL>alter database open; SQL>RECOVER  managed standby database using current logfile disconnect from session  SQL>select open_mode, database_role, protection_mode,current_SCN from v$database;OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE      CURRENT_SCN-------------------- ---------------- -------------------- -----------READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY     1977350

检查下刚才测试的数据：

 [oracle@ora9-2 data]$ sqlplus scott/test SQL*Plus: Release 11.2.0.4.0 Production on Wed May 17 08:42:08 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. ERROR:ORA-28002: the password will expire within 18446744073709551614 days  Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select count(*) from test; select count(*) from test                     *ERROR at line 1:ORA-00942: table or view does not existSQL> select * from STAGE_TERADATA_OFFLINE_PKEYS; no rows selected

该有的还在，不该有的也没有了，挺好。

很赞哦！