Enable Read Write On Physical Standby Database

Oracle 10g Enable Read Write On Physical Standby Database

On Standby

Enable Flashback Database.

SQL> show parameter db_recovery

NAME                       TYPE        VALUE
----------------------     ----------- -----------------------------------
db_recovery_file_dest      string      /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G

SQL> alter system set db_recovery_file_dest_size=6g;

System altered.

SQL> alter system set db_flashback_retention_target=1440;

System altered.

Retention period is 24 hours.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED



Prepare standby for read write.

Cancel Redo apply.

SQL> alter database recover managed standby database cancel;

Database altered.

Create a restore point named before_open_standby

SQL> create restore point before_open_standby guarantee
        flashback database;

Restore point created.



SQL> select scn,storage_size,time, name from v$restore_point;

SCN    STORAGE_SIZE TIME                            NAME
------ ------------ ------------------------------  ------------------- 
486759 8192000     19-JAN-12 06.37.39.000000000 AM  BEFORE_OPEN_STANDBY 


SQL>


On Primary

SQL> alter system archive log current;

System altered.

Stop Remote Archive shipping since we are not going to
use it.

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system switch logfile;

System altered.

On Standby


SQL> alter database activate standby database;

Database altered.

Skip the next statement. if the standby was not opened read-only
since the instance was last started.

SQL> startup mount force;

Switch to maximum performance mode.

SQL> alter database set standby database to maximize performance;

Database altered.

SQL> alter database open;

Database altered.

Stop remote redo shipping if any on standby.

SQL> alter system set log_archive_dest_state_2 = defer;

System altered.

----------------------------------------------------------------

Taking back the Standby Database to its original state.

SQL> startup mount force;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> flashback database to restore point before_open_standby;

Flashback complete.

SQL> alter database convert to physical standby;

Database altered.

SQL> startup mount force;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> drop restore point before_open_standby;

Restore point dropped.


SQL> alter system set log_archive_dest_state_2=enable scope=both;

System altered.

SQL> alter database recover managed standby database disconnect
        from session;

Database altered.

All the gaps in the archive logs should be automatically applied
because of the FAL_SERVER and FAL_CLIENT parameters.

If that does not work and your database is too far behind,
then make incremental backup on primary and apply it to standby.

On Primary


SQL> alter system set log_archive_dest_state_2 = enable scope=both;

System altered.