How To Open Physical Standby For Read Write Testing and Flas

发布时间：2020-12-15 19:59:46 所属栏目：百科来源：网络整理

导读：Reference: How To Open Physical Standby For Read Write Testing and Flashback?MOS Note? ID 805438.1 Step 1 - In Standby database A?) Set up a flash recovery area.? ?If Flash Recovery Area ( FRA )?is not configured in the standby then enable

Reference:

How To Open Physical Standby For Read Write Testing and Flashback?MOS Note?ID 805438.1

Step 1 - In Standby database

A?) Set up a flash recovery area.?

?If Flash Recovery Area ( FRA )?is not configured in the standby then enable it and make sure to give enough space for?to FRA

  SQL>?ALTER?SYSTEM?SET?DB_RECOVERY_FILE_DEST_SIZE=5G;? 
 
 SQL>?ALTER?SYSTEM?SET?DB_RECOVERY_FILE_DEST='/u01/oracle/flashback'; 

B ) Cancel Redo Apply and create a guaranteed restore point.

SQL>?ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?CANCEL;??
SQL>?CREATE?RESTORE?POINT?Standby_flashback_testing?GUARANTEE?FLASHBACK?DATABASE;?

To Confirim the details of restore point and its scn and time stamp run

SQL> select NAME,SCN,TIME from v$restore_point;?

NAME?????????????????????????????????????????????????????????????? SCN??????????????????? TIME?
--------------------------------------------------?????-------------??? ------------------------------?
STANDBY_FLASHBACK_TESTING??? ?22607810??? 12-APR-09 01.10.21.000000000 P?

Step 2 - In Primary Database

A) On the primary database,switch logs so the SCN of the restore point?will be archived on the physical standby database. When using standby redo log files,this step is essential to ensure the database can be properly flashed back to the restore point.

SQL>?ALTER?SYSTEM?ARCHIVE?LOG?CURRENT;

B ) Defer log archive destinations pointing to the standby that will be activated.

SQL>?ALTER?SYSTEM?SET?LOG_ARCHIVE_DEST_STATE_2=DEFER;

Step 3 - In Standby database

A ) Activate the physical standby database:

SQL>?ALTER?DATABASE?ACTIVATE?STANDBY?DATABASE;

Once its done you can check the controlfile status will be changed from Standby to Current

SQL> select CONTROLFILE_TYPE from v$database;?

CONTROL?
-------?
CURRENT

B) Then open the database.

SQL>?ALTER?DATABASE?SET?STANDBY?DATABASE?TO?MAXIMIZE?PERFORMANCE;?
SQL>?ALTER?DATABASE?OPEN;

Step 4 - In Standby database

Once the standby database has been activated,you can run reporting tools or perform other testing and activities for days or even weeks,independent of the primary database

Any results stored in the activated database will be lost when you later flash back the database. Results that should be saved must be copied out of the activated database before flashing it back.

For example :

SQL>?create?table?testing?(?col1?varchar2?(100));?

Table?created.?

SQL>?insert?into?testing?values?(?'testing?for?flashback?on?standby?database');?

1?row?created.?

SQL>?commit;?

Commit?complete.

?Step 5 - In standby database

A ) Revert the active standby database back to Physical standby database

??? A1. Mount the database.
??? A2. Flashback the database to restore point.

SQL>?STARTUP?MOUNT?FORCE;

ORACLE?instance?started.?
Total?System?Global?Area??289406976?bytes?
Fixed?Size??????????????????1290208?bytes?
Variable?Size?????????????159383584?bytes?
Database?Buffers??????????125829120?bytes?
Redo?Buffers????????????????2904064?bytes?
Database?mounted.?

SQL>?FLASHBACK?DATABASE?TO?RESTORE?POINT?Standby_flashback_testing?;

You can confirm the same by checking the controlfile status. It will be now backup controlfile

SQL> select controlfile_type from v$database;?

CONTROL?
--------------?
BACKUP

B ) Convert to Standby database

SQL>?ALTER?DATABASE?CONVERT?TO?PHYSICAL?STANDBY;??
SQL>?STARTUP?MOUNT?FORCE;??
SQL>?ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?DISCONNECT;?

SQL> select controlfile_type from v$database;

CONTROL?
--------------?
STANDBY

Step 6 - In standby database?

A ) Put the standby database in managed recovery mode.Let archive gap resolution fetch all missing archived redo log files and allow Redo Apply to apply the gap.?

Step 7 - In Primary database?

A ) Re-enable archiving to the physical standby database:

SQL>?ALTER?SYSTEM?ARCHIVE?LOG?CURRENT;??
SQL>?ALTER?SYSTEM?SET?LOG_ARCHIVE_DEST_STATE_2=ENABLE;?

Step 8 - In Standby database?

A ) Open the database in Read only mode and ensure that all the transaction done in active mode are no more

SQL>?ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?CANCEL;??
SQL>?ALTER?DATABASE?OPEN?READ?ONLY;??
SQL>?select?*?from?testing;??
select?*?from?testing??
*??
ERROR?at?line?1:??
ORA-00942:?table?or?view?does?not?exist?

B ) Drop the restore point

SQL>?STARTUP?FORCE?MOUNT;??
SQL>?ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?DISCONNECT;??
SQL>?DROP?RESTORE?POINT?Standby_flashback_testing?;??

Caution:? While the database is activated,it is not receiving redo data from the primary database and cannot provide disaster protection. It is recommended that there be at least two physical standby databases participating in the configuration so that the primary database remains protected against data loss.?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!