How to temporarily activate the physical Standby database in Oracle 10g/11g for testing

In the actual operating environment, we often encounter such requirements. For example, if you want to avoid affecting the running time of the DB patch in the current network environment, or, you want to connect to Standby DB for testing of actual business operations before performing DB switching. If you want to test Standby DB in 9i, if you do not need to create a new test environment, standby DB can be activated for testing, but the activated Standby DB cannot be used for disaster recovery. Standby DB must be rebuilt. After 10 Gbit/s and 11 Gbit/s, the new features can be used to solve this problem. The activated Standby DB can flash back to the status before activation and continue to participate in disaster recovery without reconstruction.

The following describes the implementation steps:
Oracle 10 Gb environment:
1: Make sure that the Flash Recovery Area is set.
SQL> show parameter db_recovery

NAME TYPE VALUE
----------------------------------------------------------------------------------------
Db_recovery_file_dest string/data1/flash_recovery_area
Db_recovery_file_dest_size big integer 80G

If not, use the alter system command to set it. Restart the database.
2: Cancel Standby DB synchronization and create a guaranteed Restore Point.
SQL> alter database recover managed standby database cancel;
SQL> create restore point beforetest guarantee flashback database;

3: Activate the Standby database for testing
SQL> alter database activate standby database;
SQL> alter database open;
Test Standby DB is activated and can be written to open. Then you can start the test.

4: Flash the activated Standby database back and switch it back to Standby DB.
SQL> startup mount force
SQL> flashback database to restore point beforetest;
SQL> alter database convert to physical standby;

5. Delete the previously created Restore Point and re-enable synchronization.
SQL> startup mount force
SQL> drop restore point beforetest;
SQL> alter database recover managed standby database disconnect from session;

It indicates that an error will be reported during log transmission from the master database to the slave database. We recommend that you stop the log transmission from the master database to the Standby DB:
SQL> alter system set log_archive_dest_state_2 = defer;
Re-enable log transmission after flashing back:
SQL> alter system set log_archive_dest_state_2 = enable;

Oracle 11g environment:
The steps are simpler. The new features of Snapshot Standby Database are added to the 11g, which is actually a package of the above 10g functions, the only difference is that archive logs from the master database can be accepted after being converted to the read/write mode.
The procedure is as follows:
1: Cancel log application
SQL> alter database recover managed standby database cancel;

2: restart and convert to Snapshot Standby Database.
SQL> shutdown immediate;
SQL> startup mount
SQL> alter database convert to snapshot standby;

3: read/write tests can be performed after enabling
SQL> alter database open;

4: Switch to physical Standby and enable synchronization.
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to physical standby;
SQL> shutdown immediate;
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect from session;

Notes:
The Restore point created in 10 Gb must be deleted after the Flash. Otherwise, the flash recovery area may be full, resulting in DB Hang. Do not worry about this issue in the 11g. The Restore point will be automatically deleted when you convert it to physical standby.

Oracle DG modify logical Standby data

Oracle DG logical Standby creation instructions

View Management of Oracle DG logic Standby

Oracle 10g rac Standby automatic deletion script for archiving logs

Monitoring and management of physical Standby of DataGuard