07 September, 2020

Creating a PDB as a Clone in a DataGuard environment

 In the 19c Primary database, where I want to create NEWPDB as a no-data clone of ORCLPDB1 :



SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID
----------
NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
----------
         2
PDB$SEED
READ ONLY

         3
ORCLPDB1
READ WRITE


SQL> alter pluggable database ORCLPDB1 close;

Pluggable database altered.

SQL>  alter pluggable database ORCLPDB1 open read only;

Pluggable database altered.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /opt/oracle/oradata
SQL>
SQL> create pluggable database newpdb
  2  from orclpdb1
  3  storage (maxsize 10G)
  4  service_name_convert=('ORCLPDB1','NEWPDB')
  5  user_tablespaces=NONE
  6  standbys=ALL
  7  no data
  8  /

Pluggable database created.

SQL>
SQL> select name
  2  from v$datafile
  3  where con_id =
  4  (select con_id
  5   from v$pdbs
  6   where name = 'NEWPDB')
  7  order by file#
  8  /

NAME
------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_system_hodnt79n_.dbf
/opt/oracle/oradata/ORCLCDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_sysaux_hodnt7bk_.dbf
/opt/oracle/oradata/ORCLCDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_undotbs1_hodnt7bm_.dbf

SQL>
SQL> select name
  2  from v$datafile
  3  where con_id =
  4  (select con_id
  5  from v$pdbs
  6  where name = 'ORCLPDB1')
  7  order by file#
  8  /

NAME
------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf

SQL>


Notice how the NEWPDB does NOT have a USERS datafile as I specified "user_tablespaces=NONE".
Also note that while the ORCLPDB1 had a different naming format, NEWPDB relies on what I have specified as "db_create_file_dest" to create Oracle Managed Files as datafiles  (incuding the Undo Tablespace datafile)
Also, the "no data" ensures that no user data is copied, only the database template(data dictionary) is copied. User-created tables and indexes are excluded.  (should we test to see which types of user-created objects are included/excluded ?)

SQL> alter pluggable database orclpdb1 close;

Pluggable database altered.

SQL> alter pluggable database orclpdb1 open read write;

Pluggable database altered.

SQL> alter pluggable database newpdb close;
alter pluggable database newpdb close
*
ERROR at line 1:
ORA-65020: pluggable database NEWPDB already closed


SQL> alter pluggable database newpdb open read write;

Pluggable database altered.

SQL>


The source PDB had to be Read Only but the cloned NEWDB is not OPEN when created.

SQL> select service_id, name, network_name, creation_date, pdb, con_id
  2  from v$services
  3  order by 1
  4  /

SERVICE_ID NAME                                                             NETWORK_NAME     CREATION_ PDB          CON_ID
---------- ---------------------------------------------------------------- ---------------- --------- -------- ----------
         1 SYS$BACKGROUND                                                                    17-APR-19 CDB$ROOT          1
         2 SYS$USERS                                                                         17-APR-19 CDB$ROOT          1
         5 ORCLCDBXDB                                                       ORCLCDBXDB       04-MAY-19 CDB$ROOT          1
         6 ORCLCDB                                                          ORCLCDB          04-MAY-19 CDB$ROOT          1
         8 orclpdb1                                                         orclpdb1         04-MAY-19 ORCLPDB1          3
        10 newpdb                                                           newpdb           07-SEP-20 NEWPDB            4

6 rows selected.

SQL>
SQL> select service_id, name, con_id
  2  from v$active_services
  3  order by service_id
  4  /

SERVICE_ID NAME                                                                 CON_ID
---------- ---------------------------------------------------------------- ----------
         1 SYS$BACKGROUND                                                            1
         2 SYS$USERS                                                                 1
         5 ORCLCDBXDB                                                                1
         6 ORCLCDB                                                                   1
         8 orclpdb1                                                                  3
        10 newpdb                                                                    4

6 rows selected.

SQL>


I have a Service called "newpdb" created for the new PDB.

How and when does the PDB propagate to the Standby ?

After I issue 

SQL> alter system archive log current;

System altered.

SQL>


The Standby alert log shows :

2020-09-07T23:46:09.815590+08:00
Recovery created pluggable database NEWPDB
2020-09-07T23:46:16.818755+08:00
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_system_hodnt79n_.dbf from /opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf
NEWPDB(4):Successfully added datafile 13 to media recovery
NEWPDB(4):Datafile #13: '/opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_system_hodnt79n_.dbf'
2020-09-07T23:46:27.355523+08:00
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_sysaux_hodnt7bk_.dbf from /opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf
NEWPDB(4):Successfully added datafile 14 to media recovery
NEWPDB(4):Datafile #14: '/opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_sysaux_hodnt7bk_.dbf'
2020-09-07T23:46:35.160960+08:00
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file /opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_undotbs1_hodnt7bm_.dbf from /opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf
NEWPDB(4):Successfully added datafile 15 to media recovery
NEWPDB(4):Datafile #15: '/opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_undotbs1_hodnt7bm_.dbf'
2020-09-07T23:46:37.523453+08:00
PR00 (PID:2275): Media Recovery Waiting for T-1.S-80 (in transit)
2020-09-07T23:46:37.557413+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 80 Reading mem 0
  Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


The datafiles at the Standby are also created as as Oracle Managed Files, relying on "db_create_file_dest".  But the alert log messages clearly show that it did a *local* copy of the datafiles from ORCLPDB1  instead of copying the new datafiles across the network.


(I've posted the alert log entries of the Primary database in the next blog post here).


UPDATE :  Also see this Video Demonstration of creating a PDB in a 21c DataGuard environment.

No comments: