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
