Oracle: DataGuard:

 

DataGuard Standby Database do not allow DDL or DML. I am going to convert my Physical Standby to Snapshot so it can be fully updatable standby database while Primary can be taken down for patching purposes. 

Standby is Read Only with Apply and cannot be updated.

SQL> alter session set container=pdb1;

Session altered.

SQL> select name, open_mode from v$database;

NAME OPEN_MODE

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

ORCL READ ONLY WITH APPLY

SQL> select name,database_role,open_mode,db_unique_name from v$database;

NAME DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME

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

ORCL PHYSICAL STANDBY READ ONLY WITH APPLY ORCLSTB

SQL> create table crm (id number);

create table crm (id number)

*

ERROR at line 1:

ORA-16000: database or pluggable database open for read-only access

Converting from Physical Standby to Snapshot Standby

SQL> !hostname

Standby.localdomain

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

SQL> select name, open_mode from v$pdbs;

NAME

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

OPEN_MODE

----------

PDB1

READ ONLY

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

3 PDB1 READ ONLY NO

SQL> shutdown immediate;

Pluggable Database closed.

SQL> show pdbs; 

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 MOUNTED

SQL> select name, open_mode from v$database;

NAME OPEN_MODE

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

ORCL READ ONLY WITH APPLY

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

-- Take the database to Mount Mode.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1560278096 bytes

Fixed Size 9135184 bytes

Variable Size 973078528 bytes

Database Buffers 570425344 bytes

Redo Buffers 7639040 bytes

Database mounted.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

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

ARCH CONNECTED 0 0 0 0

DGRD ALLOCATED 0 0 0 0

DGRD ALLOCATED 0 0 0 0

ARCH CONNECTED 0 0 0 0

ARCH CONNECTED 0 0 0 0

ARCH CONNECTED 0 0 0 0

6 rows selected.

-- stop and cancel the log applying process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/oracle/fast_recovery_

area

db_recovery_file_dest_size big integer 12732M

-- Converting the database to Snapshot

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL> ALTER DATABASE OPEN READ WRITE;

Database altered.

SQL> select name,database_role,open_mode,db_unique_name from v$database;

NAME DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME

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

ORCL SNAPSHOT STANDBY READ WRITE ORCLSTB

SQL> select name,open_mode from v$pdbs;

NAME

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

OPEN_MODE

----------

PDB$SEED

READ ONLY

PDB1

READ WRITE

SQL> col name format a30;

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

-- Testing the Standby

-- from the standby db

SQL> conn sys@pdb1 as sysdba

Enter password: 

Connected.

SQL> show con_name

CON_NAME

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

PDB1

SQL> create user test identified by test;

User created.

SQL> create table test(id number);

Table created.

SQL> insert into test values(100); 

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ID

----------

100

Note: if converted back to Physical Standby, all the changes will be wiped out like it never happened. So, technically, this is just a temporarily letting the database serviceable while Primary is down.

To revert to Primary Database, just redo the same step with the following command. Again, the db has to be in Mount Mode.

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Reference: Other reference: https://docs.oracle.com/cd/B13789_01/server.101/b10823/manage_ps.htm