Hi,
In this article I will explain you the steps to convert Dataguard to Read Write mode.
I have explained you to open in synchronized dataguard in Read only mode as Active standby in the Following article. In this article, I will tell you how to convert Dataguard to write Read Write mode and then to convert it into dataguard mode again.
Flashback must be turned on in order to be able to active standby mode. To enable Flashback, we need to use the FRA parameter like following.
SQL> show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/recovery_area db_recovery_file_dest_size big integer 2022043K
Flashback is enabled as follows. This can be done in case the dataguard is off and in mount mode.
SQL> alter database flashback on; Database altered.
Create a guaranteed restore point. When we convert to dataguard, we will use this restore point.
SQL> create restore point before_open guarantee flashback database; Restore point created.
Stop Redo Apply process
SQL> alter database recover managed standby database cancel;
Manually create an archive log in the primary database like following.
SQL> alter system archive log current; System altered.
We need to delay the redo transport from the Primary Database to the Standby database. So disable related log_archive_dest_state_2 parameter like following.
SQL> alter system set log_archive_dest_state_2=DEFER; SQL> alter system switch logfile;
Now we can activate the Standby database and convert it into Read Write mode.
SQL> alter database activate standby database; Database altered.
Open standby database in read and write mode
SQL> startup mount force; ORACLE instance started. Total System Global Area 534462464 bytes Fixed Size 2230072 bytes Variable Size 163580104 bytes Database Buffers 360710144 bytes Redo Buffers 7942144 bytes Database mounted. alter database set standby database to maximize performance; alter database open; Database altered.
We opened the standby database in Read write mode namely Snapshot database mode.
This operation is done such as test or Disaster purpose.
SQL> create user mehmet identified by salih; User created. SQL> grant dba to mehmet; Grant succeeded.
If Disaster tests or any other tests are completed, we can convert it into dataguard again like following steps.
Open it in Mount mode
SQL> startup mount force; ORACLE instance started. Total System Global Area 534462464 bytes Fixed Size 2230072 bytes Variable Size 163580104 bytes Database Buffers 360710144 bytes Redo Buffers 7942144 bytes Database mounted.
Convert Snapshot database to Dataguard with restore point
select * from v$restore_point; set pages 100 set line 200 column message format a50 select sid,message from v$session_longops where sofar <> totalwork; set linesize 400 col DB_DATA for 999999999999 col FLASHBACK_SIZE for 999999999999 SELECT TO_CHAR(A.BEGIN_TIME,'DDMMYY HH24:MI') "FLASH BEGIN",TO_CHAR(A.END_TIME,'DDMMYY HH24:MI') "FLASH END",A.DB_DATA, B.FLASHBACK_SIZE, (A.DB_DATA/B.FLASHBACK_SIZE)*100 "% COMPLETE" FROM V$FLASHBACK_DATABASE_STAT A, V$FLASHBACK_DATABASE_LOG B;
SQL> flashback database to restore point BEFORE_OPEN; DROP RESTORE POINT before_readwrite0612_2016; Flashback complete.
Convert Snapshot database to the standby database.
3- SQL> alter database convert to physical standby; Database altered.
The Standby database is reopened in Mount mode.
SQL> startup mount force; ORACLE instance started. Total System Global Area 534462464 bytes Fixed Size 2230072 bytes Variable Size 163580104 bytes Database Buffers 360710144 bytes Redo Buffers 7942144 bytes Database mounted.
Redo Apply is started again like following.
alter database recover managed standby database using current logfile disconnect;
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/