Converting Physical Standby to Logical Standby when PDB In place 

Of course we have seen many articles to convert the Physical standby to Logical standby and vice versa, From 12c during the conversion there are few changes involved when handling multitenant, Really there are no more new steps involved, we will see how to do that

  • Before converting to Logical standby, ensure no recovery(MRP) is running on standby. If its running then terminate recovery process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL>
  • Support a Logical standby database: To work logical standby database the supplementary logging should be enabled on primary database. But the important thing is all the pluggable database should be in open status.
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
 BEGIN DBMS_LOGSTDBY.BUILD; END;
*
 ERROR at line 1:
 ORA-65024: Pluggable database is not open.
 ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7214
 ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7228
 ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7384
 ORA-06512: at line 1
 ORA-06512: at "SYS.DBMS_INTERNAL_LOGSTDBY", line 450
 ORA-06512: at "SYS.DBMS_LOGSTDBY", line 206
 ORA-06512: at line 1
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.
SQL>
  • Issue the actual conversion command, you can mention db_name “india” or which ever you prefer so that it will recover until the SCN that the dictionary was built.
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY india;
Database altered.
SQL>
Killing 3 processes (PIDS:4424,4440,4422) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 25964
 Begin: Standby Redo Logfile archival
 End: Standby Redo Logfile archival
 RESETLOGS after incomplete recovery UNTIL CHANGE 2816937
 Tue Jun 30 15:39:48 2015
 Waiting for all non-current ORLs to be archived...
 Tue Jun 30 15:39:48 2015
 All non-current ORLs have been archived.
 Resetting resetlogs activation ID 3793820920 (0xe2211cf8)
 Online log /u02/app/oracle/fast_recovery_area/INDIA/onlinelog/o1_mf_1_bm4gf193_.log: Thread 1 Group 1 was previously cleared
 Online log /u02/app/oracle/fast_recovery_area/INDIA/onlinelog/o1_mf_2_bm4gf28y_.log: Thread 1 Group 2 was previously cleared
 Online log /u02/app/oracle/fast_recovery_area/INDIA/onlinelog/o1_mf_3_bm4gf2tn_.log: Thread 1 Group 3 was previously cleared
 Standby became primary SCN: 2816935
 Tue Jun 30 15:39:49 2015
 Setting recovery target incarnation to 3
 Tue Jun 30 15:39:49 2015
 AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
 RECOVER TO LOGICAL STANDBY: Complete - Database shutdown required after NID finishes
*** DBNEWID utility started ***
 DBID will be changed from 3793852408 to new DBID of 3060822149 for database MCDB
 DBNAME will be changed from MCDB to new DBNAME of INDIA
 Starting datafile conversion
 Datafile conversion complete
 Database name changed to INDIA.
 Modify parameter file and generate a new password file before restarting.
 Database ID for database INDIA changed to 3060822149.
 All previous backups and archived redo logs for this database are unusable.
 Database has been shutdown, open with RESETLOGS option.
 Succesfully changed database name and ID.
 *** DBNEWID utility finished succesfully ***
  • Current status of Logical standby and the PDBs associated to the container database as
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
 ------------------------------ ----------
 PDB$SEED MOUNTED
 MPDB MOUNTED
SQL>
SQL> select status from v$Instance;
STATUS
 ------------
 STARTED
SQL>
  • Ensure the database is in Read-Write mode, thus recovery can be started
SQL> alter database mount;
 alter database mount
 *
 ERROR at line 1:
 ORA-00750: database has been previously mounted and dismounted
 SQL>
 SQL> shut immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup mount
 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
 ORACLE instance started.
Total System Global Area 730714112 bytes
 Fixed Size 2292672 bytes
 Variable Size 536872000 bytes
 Database Buffers 188743680 bytes
 Redo Buffers 2805760 bytes
 Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL>
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
SQL>
  • Check the Latest status of Logical standby and the PDB’s status
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 INDIA READ WRITE LOGICAL STANDBY
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
 ------------------------------ ----------
 PDB$SEED READ ONLY
 MPDB READ WRITE
SQL>

If Data Guard Broker is using, then ensure the configuration is in good status.

DGMGRL> show configuration;
Configuration - ckpt12c
Protection Mode: MaxPerformance
 Databases:
 canada - Primary database
 india - Physical standby database (disabled)
 ORA-16674: standby database type has changed
Fast-Start Failover: DISABLED
Configuration Status:
 SUCCESS
DGMGRL>
DGMGRL> remove database india;
 Removed database "india" from the configuration
 DGMGRL> show configuration;
Configuration - ckpt12c
Protection Mode: MaxPerformance
 Databases:
 canada - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
 SUCCESS
DGMGRL>
DGMGRL> add database india as connect identifier is india maintained as logical;
 Database "india" added
 DGMGRL>
DGMGRL> enable database india;
 Enabled.
 DGMGRL> show configuration;
Configuration - ckpt12c
Protection Mode: MaxPerformance
 Databases:
 canada - Primary database
 india - Logical standby database
Fast-Start Failover: DISABLED
Configuration Status:
 SUCCESS
DGMGRL>
 DGMGRL> show database india;
Database - india
Role: LOGICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 0 seconds ago)
 Apply Lag: 0 seconds (computed 0 seconds ago)
 Apply Rate: 73.10 MByte/s
 Instance(s):
 drmcdb
Database Status:
 SUCCESS
DGMGRL>

Summary: In fact there are no major changes, But during enabling supplemental logging all the  PDB’s at primary database should be in OPEN status and no matter what is the status of PDB at standby database level during actual conversion to Logical standby.