Dataguard : Convert physical standby database to snapshot PDB database 12c version
Scenario:
We have 2 node RAC with non rac physical standby database
- Login physical standby database and check the current status
[oracle@srv4 rmanbkp]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 15 12:38:12 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @d
NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
RAC racdr racdr MOUNTED PHYSICAL STANDBY NO 0
- Check switchover status of standby database
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
- Checking sync status of standby database
SQL> @/home/oracle/standby
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC MOUNTED PHYSICAL STANDBY
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 32
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 39
ARCH CLOSING 40
ARCH CLOSING 38
RFS IDLE 0
RFS IDLE 41
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
RFS IDLE 0
RFS IDLE 0
RFS IDLE 33
MRP0 APPLYING_LOG 33
15 rows selected.
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 40 40 0
2 32 32 0
- Stop MRP0 process and enable flashback on in the database
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
- Increase retention target and db recovery file size as per requirements
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 10G SCOPE = BOTH;
System altered.
SQL> ALTER SYSTEM SET db_flashback_retention_target = 10000 SCOPE = BOTH;
System altered.
- Shut down standby database (optional ) and convert physical standby database into snapshot database Also open the database.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2432696320 bytes
Fixed Size 8795664 bytes
Variable Size 738200048 bytes
Database Buffers 1677721600 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 RACPDB MOUNTED
SQL> alter database convert to snapshot standby;
Database altered.
SQL> @d
NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
RAC racdr racdr MOUNTED SNAPSHOT STANDBY YES 3276602
- Open now snapshot database it will be read write mode
SQL> alter database open;
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/datafile/system.257.1088276431
/u01/app/oracle/oradata/racdr/datafile/sysaux.258.1088276477
/u01/app/oracle/oradata/racdr/datafile/undotbs1.259.1088276501
/u01/app/oracle/oradata/racdr/4700a987085b3dfae05387e5e50a8c7b/datafile/system.269.1088276619
/u01/app/oracle/oradata/racdr/4700a987085b3dfae05387e5e50a8c7b/datafile/sysaux.268.1088276619
/u01/app/oracle/oradata/racdr/datafile/users.260.1088276503
/u01/app/oracle/oradata/racdr/4700a987085b3dfae05387e5e50a8c7b/datafile/undotbs1.270.1088276619
/u01/app/oracle/oradata/racdr/datafile/undotbs2.272.1088276835
/u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/system.280.1088277275
/u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/sysaux.281.1088277275
/u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/undotbs1.279.1088277275
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/undo_2.319.1091217683
/u01/app/oracle/oradata/RACDR/D0706A7D1C5F1532E0534738A8C0B3F6/datafile/o1_mf_soetbs_jvk2n7gk_.dbf
13 rows selected.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/tempfile/temp.267.1088276611
/u01/app/oracle/oradata/racdr/d07045b084567d8be0534738a8c057cb/tempfile/temp.271.1088276639
/u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/tempfile/temp.282.1088277333
SQL> @d
NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
RAC racdr racdr READ WRITE SNAPSHOT STANDBY YES 3276602
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CLOSING 1
ARCH CLOSING 33
LNS CONNECTED 0
DGRD ALLOCATED 0
RFS IDLE 0
RFS IDLE 42
RFS IDLE 0
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
RFS IDLE 34
12 rows selected.
- In our case we have pdb standby database login to pdb and open in read write mode
SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
2 PDB$SEED READ ONLY 744488960
3 RACPDB MOUNTED 0
SQL> alter session set container=RACPDB;
Session altered.
SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
3 RACPDB MOUNTED 0
SQL> ALTER PLUGGABLE DATABASE OPEN READ WRITE;
Pluggable database altered.
SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
3 RACPDB READ WRITE 4091543552
- For snapshot checking create table and insert the values on table
SQL> create table soe.test1(id varchar2(255));
Table created.
SQL> insert into soe.test1 values(1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from soe.test1;
ID
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
1
1
1
1
SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
3 RACPDB READ WRITE 4091543552
SQL> commit;
Commit complete.
SQL> @d
NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
RAC racdr racdr READ WRITE SNAPSHOT STANDBY YES 3276602
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
- Here we can see pdb seed in read only mode and pdb is now in read write mode.
[oracle@srv4 rmanbkp]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 15 13:34:06 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID
----------
NAME
----------------------------------------------------------------
OPEN_MODE TOTAL_SIZE
---------- ----------
2
PDB$SEED
READ ONLY 744488960
3
RACPDB
READ WRITE 4091543552
CON_ID
----------
NAME
--------------------------------------------------------------------------------
OPEN_MODE TOTAL_SIZE
---------- ----------
SQL> set lines 200
SQL> /
CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
2 PDB$SEED READ ONLY 744488960
3 RACPDB READ
WRITE 4091543552
- Shutdown down snapshot database database(optional) and convert back into physical standby database
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2432696320 bytes
Fixed Size 8795664 bytes
Variable Size 738200048 bytes
Database Buffers 1677721600 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 RACPDB MOUNTED
SQL> @d
NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
RAC racdr racdr MOUNTED SNAPSHOT STANDBY YES 3276602
SQL> alter database convert to physical standby;
Database altered.
SQL> @d
NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
RAC racdr racdr MOUNTED PHYSICAL STANDBY YES 0
SQL> @/home/oracle/standby
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC MOUNTED PHYSICAL STANDBY
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 42
ARCH CLOSING 35
ARCH CLOSING 43
RFS IDLE 0
RFS IDLE 0
RFS IDLE 44
RFS IDLE 36
10 rows selected.
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 43 40 3
2 35 1 34
- Start MRP0 process again to make sync with primary database
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> @/home/oracle/standby
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC MOUNTED PHYSICAL STANDBY
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 36
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 44
ARCH CLOSING 45
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 46
RFS IDLE 37
MRP0 APPLYING_LOG 46
11 rows selected.
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 45 45 0
2 36 36 0
SQL>
No comments:
Post a Comment