Shulou(Shulou.com)06/01 Report--

In Oracle 10g, it is still very cumbersome to prepare a read-write standby database. After preparing dataguard, you have to manually create a restore point, manually stop log transfer, manually activate and force it to open. After testing, if the SCN difference between the primary and standby is too much, you have to do incremental backup tracking. Statistics show that it takes 15 steps, which is almost the same as setting up a physical standby, so it is rarely used. To 11g finally liberated, enable snapshot backup only 3 steps (of course, the number of intermediate restart does not count), restore to real-time application backup also only 2 steps, log or continue to pass, need mirror library test friends, you can rest assured to use (with dataguard borker is simpler). Of course, the conversion to Snapshot Standby is subject to some additional conditions (see the previous paragraph to build one):

1 database flashback is open;

db_recovery_file_dest_size is still sufficient space;

3 If the protection mode used is Maximum Protection mode, there must be other Standby matching it, otherwise be careful of the main library downtime.

The manual steps are as follows:

1 Check for flashbacks

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

The current Standby state is read-only Apply state, at this point you need to terminate the Apply process and switch back to mount state. Otherwise, the convert action is not allowed.

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.

This error is easy to solve:

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 transitions

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

Interested in watching 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

Check the current database status:

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

It has become writable, query SCN: at the beginning of flash_back

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

From here, you can do anything with the backup library:

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.

Back to:

1 Close library, switch

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

Check alert_sid.log here to see

Flashback Restore Start

Flashback Restore Complete

Drop guaranteed restore point

Deleted restore point

2 Close library, close flashback, enable 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

Check the data you just tested:

[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

What should be there is still there, what should not be there is not there, good.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.