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.
