在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与之相匹配,否则小心主库宕机。 手动做的步骤如下: 1检查闪回
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_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 51000M sql> 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,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 immediate sql>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,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 Production With the Partitioning,OLAP,Data Mining and Real Application Testing options sql> select count(*) from test; select count(*) from test * ERROR at line 1: ORA-00942: table or view does not exist sql> select * from STAGE_TERADATA_OFFLINE_PKEYS; no rows selected
该有的还在,不该有的也没有了,挺好。