Convert standby in a snapshot database

Here the article to how to create a dataguard with broker:
https://ezdba.wordpress.com/2017/05/15/dataguard-setup-with-broke
— convert the physical standby database to a snapshot database
and open it for read-write operations.

— Primary: Boston hostname(vddl-ocme1-rac-01)
— Standby: London hostname(vddl-ocm-odg-01)

1.) Terminal windows with LONDON variables

[oracle@vddl-ocm-odg-01 ~]$ . oraenv
ORACLE_SID = [oracle] ? london
The Oracle base remains unchanged with value /opt/oracle
[oracle@vddl-ocm-odg-01 ~]$

sqlplus / as sysdba

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

NAME TYPE VALUE
———————————— ———– ——————————
db_flashback_retention_target integer 1440

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /opt/oracle/oradata/london/arc
db_recovery_file_dest_size big integer 11105M

select file_type,number_of_files,percent_space_used
from v$recovery_area_usage;

FILE_TYPE NUMBER_OF_FILES PERCENT_SPACE_USED
———————– ——————– ——————–
CONTROL FILE 0 0
REDO LOG 4 1.8
ARCHIVED LOG 27 7.6
BACKUP PIECE 2 .31
IMAGE COPY 0 0
FLASHBACK LOG 6 2.7
FOREIGN ARCHIVED LOG 0 0
AUXILIARY DATAFILE COPY 0 0

2.) Stop the RECOVERY

alter database recover managed standby database cancel;

3.) Convert to snapshot

alter database convert to snapshot standby;
SQL> select database_role from v$database;

DATABASE_ROLE
—————-
SNAPSHOT STANDBY

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

SQL> select name, storage_size from v$restore_point;

NAME STORAGE_SIZE
—————————————- ——————–
SNAPSHOT_STANDBY_REQUIRED_06/19/2017 10: 52428800
33:40

SQL> select open_mode from v$database;

OPEN_MODE
——————–
MOUNTED

4.) Open the database

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

5.) Test

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
——————– —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 DEV1 MOUNTED
SQL>
SQL>
SQL> alter pluggable database dev1 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
——————– —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 DEV1 READ WRITE NO
SQL>
SQL>
SQL> alter session set container=dev1;

Session altered.

SQL>
SQL> create table misc1 (x varchar2(50) );

Table created.

SQL> insert into misc1 values (‘Test Row’);

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.

INSTANCE_NAME
—————-
london

6.) Check the Primary database: Boston

. oraenv
boston

SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

MAX(SEQUENCE#) THREAD#
——————– ——————–
74 1

alter system switch logfile;

7.) Check the Sequence on standby snapshot database london.

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

MAX(SEQUENCE#) THREAD#
——————– ——————–
75 1

Note: Here we have a sequence bigger than primary because on this configuration i am using
FAR SYNC also.

8.) Convert back to standby:

. oraenv

london

sqlplus / as sysdba

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 520093696 bytes
Fixed Size 2926272 bytes
Variable Size 356518208 bytes
Database Buffers 155189248 bytes
Redo Buffers 5459968 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

CON_ID CON_NAME OPEN MODE RESTRICTED
——————– —————————— ———- ———-
2 PDB$SEED MOUNTED
3 DEV1 MOUNTED

SQL> select file_type,number_of_files,percent_space_used
from v$recovery_area_usage; 2

FILE_TYPE NUMBER_OF_FILES PERCENT_SPACE_USED
———————– ——————– ——————–
CONTROL FILE 0 0
REDO LOG 4 1.8
ARCHIVED LOG 29 7.67
BACKUP PIECE 2 .31
IMAGE COPY 0 0
FLASHBACK LOG 6 2.7
FOREIGN ARCHIVED LOG 0 0
AUXILIARY DATAFILE COPY 0 0

8 rows selected.

SQL>
SQL>
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
——————– —————————— ———- ———-
2 PDB$SEED MOUNTED
3 DEV1 MOUNTED
SQL> alter database open;

Database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
——————– —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 DEV1 MOUNTED
SQL> alter pluggable database dev1 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
——————– —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 DEV1 READ ONLY NO
SQL> alter session set container=dev1;

Session altered.

SQL> select * from misc1;
select * from misc1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.

INSTANCE_NAME
—————-
london

1 row selected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 520093696 bytes
Fixed Size 2926272 bytes
Variable Size 356518208 bytes
Database Buffers 155189248 bytes
Redo Buffers 5459968 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect;

Database altered.
exit;