Presrequisite
First we need to setup broker configuration. Refer this article
https://dbatracker.com/2020/06/19/setup-broker-and-switchover-in-oracle-cloud-19c/
Then we need to enable flashback and set FRA on standby side
verify if we have this
[oracle@cloud-dr-node01 ~]$ sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 19 07:05:48 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TRU19C MOUNTED PHYSICAL STANDBY
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/19c/dbhome_1/dbs/arch
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
lets’s enable it
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4647285816 bytes
Fixed Size 8905784 bytes
Variable Size 855638016 bytes
Database Buffers 3774873600 bytes
Redo Buffers 7868416 bytes
Database mounted.
SQL> alter system set db_recovery_file_dest_size=5G;
System altered.
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/oradata/TRU19C_DR/FRA';
System altered.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> !ps -ef|grep mrp
oracle 8327 1 0 07:09 ? 00:00:00 ora_mrp0_TRU19c_DR
oracle 10307 7976 0 07:34 pts/0 00:00:00 /bin/bash -c ps -ef|grep mrp
oracle 10309 10307 0 07:34 pts/0 00:00:00 grep mrp
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/oradata/TRU19C
_DR/FRA
db_recovery_file_dest_size big integer 5G
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
Till now we do not have any restore point
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
no rows selectedConvert to sanpshot standby using broker
verify configration
[oracle@cloud-node01 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Jun 19 07:38:41 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/tru19#123@TRU19c
Connected to "TRU19c"
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
tru19c - Primary database
TRU19c_DR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 5 seconds ago)
convert
DGMGRL> convert database 'TRU19c_DR' to snapshot standby;
Converting database "TRU19c_DR" to a Snapshot Standby database, please wait...
Database "TRU19c_DR" converted successfully
DGMGRL> show configuration;
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
tru19c - Primary database
TRU19c_DR - Snapshot standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 58 seconds ago)at database level you will see guarnteed restorepoint is created
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TRU19C READ WRITE SNAPSHOT STANDBY
SQL> SQL> col name for a50
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
NAME GUA
-------------------------------------------------- ---
SNAPSHOT_STANDBY_REQUIRED_06/19/2020 07:40:09 YESlet’s create a table and insert some rows for testing purpose
SQL> create table test(id number,name varchar2(20));
Table created.
SQL> insert into test values(1,'abc');
1 row created.
SQL> insert into test values(2,'pqr');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME
---------- --------------------------------------------------
1 abc
2 pqrconvert back to physical standby
DGMGRL> convert database 'TRU19c_DR' to PHYSICAL STANDBY;
Converting database "TRU19c_DR" to a Physical Standby database, please wait...
Operation requires shut down of instance "TRU19c_DR" on database "TRU19c_DR"
Shutting down instance "TRU19c_DR"...
Connected to "TRU19c_DR"
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "TRU19c_DR" on database "TRU19c_DR"
Starting instance "TRU19c_DR"...
Connected to an idle instance.
ORACLE instance started.
Connected to "TRU19c_DR"
Database mounted.
Connected to "TRU19c_DR"
Continuing to convert database "TRU19c_DR" ...
Database "TRU19c_DR" converted successfully
DGMGRL>
DGMGRL> SHOW CONFIGURATION;
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
tru19c - Primary database
TRU19c_DR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 33 seconds ago)
at database level we can see restorepoint is automatically dropped and data is also gone
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
-------------------------------------------------- -------------------- ----------------
TRU19C MOUNTED PHYSICAL STANDBY
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
no rows selected
DBATracker 