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.

Using SQL Developer to Copy Data to the Oracle Cloud

 

I have created a short video demonstrating a copy of selected objects from a 19c database accessible from my desktop to an Autonomous (ATP) Database in the Oracle Cloud using SQL Developer 20.2

ORA_ROWSCN and RowDependencies

 The last three posts in this blog have been on SCNs and the ORA_ROWSCN function with brief mentions about the RowDependencies extended attribute of a table defnition.

Here is a quick demonstration of how RowDependencies works.

I setup two tables, without and with RowDepedencies, with 5 rows each (ensuring that all 5 fit into 1 datablock) and read the SCN values returned by ORA_ROWSCN 

This is the first table where I don't specify RowDependencies :

22:39:35 SQL> create table norowdep (id number, inserted_scn number, inserted_timestamp timestamp);

Table created.

22:40:09 SQL> insert into nororwdep
22:40:21   2  select 1, current_scn, systimestamp from v$database;
insert into nororwdep
            *
ERROR at line 1:
ORA-00942: table or view does not exist


22:40:37 SQL> insert into norowdep
22:40:45   2  select 1, current_scn, systimestamp from v$database;

1 row created.

22:40:52 SQL> commit;

Commit complete.

22:40:54 SQL> !sleep 5

22:41:02 SQL>
22:41:09 SQL> insert into norowdep
22:41:10   2  select 2, current_scn, systimestamp from v$database;

1 row created.

22:41:19 SQL> commit;

Commit complete.

22:41:22 SQL> !sleep 5

22:41:30 SQL> insert into norowdep
22:41:34   2  select 3, current_scn, systimestamp from v$database;

1 row created.

22:41:42 SQL> commit;

Commit complete.

22:41:47 SQL> !sleep 5

22:41:54 SQL> insert into norowdep
22:41:56   2  select 4, current_scn, systimestamp from v$database;

1 row created.

22:42:05 SQL> commit;

Commit complete.

22:42:10 SQL> !sleep 5

22:42:18 SQL> insert into norowdep
22:42:19   2  select 5, current_scn, systimestamp from v$database;

1 row created.

22:42:29 SQL> commit;

Commit complete.

22:42:31 SQL> !sleep 5

22:42:41 SQL> select id, inserted_scn, inserted_timestamp, ora_rowscn
22:43:09   2  from norowdep
22:43:16   3  order by 1
22:43:19   4  /

        ID INSERTED_SCN INSERTED_TIMESTAMP                                                          ORA_ROWSCN
---------- ------------ --------------------------------------------------------------------------- ----------
         1      6780419 18-AUG-20 10.40.52.802908 PM                                                   6785773
         2      6782540 18-AUG-20 10.41.19.887569 PM                                                   6785773
         3      6783619 18-AUG-20 10.41.42.647586 PM                                                   6785773
         4      6784694 18-AUG-20 10.42.05.374764 PM                                                   6785773
         5      6785769 18-AUG-20 10.42.29.422846 PM                                                   6785773

22:43:20 SQL>

Here Oracle returns the same SCN value  as ORA_ROWSCN for all 5 rows -- it has read this from the Block, instead of reading for each row.

This is the second table with RowDependencies :

22:47:31 SQL> create table rowdependencies (id number, inserted_scn number, inserted_timestamp timestamp) rowdependencies;

Table created.

22:47:56 SQL>
22:48:07 SQL> insert into rowdependencies
22:48:13   2  select 1, current_scn, systimestamp from v$database;

1 row created.

22:48:17 SQL> commit;

Commit complete.

22:48:19 SQL> !sleep 5
insert into rowdependencies
22:48:27 SQL>
22:48:30   2
22:48:31 SQL> insert into rowdependencies
22:48:33   2  select 2, current_scn, systimestamp from v$database;

1 row created.

22:48:42 SQL> commit;

Commit complete.

22:48:43 SQL> !sleep 5

22:48:51 SQL> insert into rowdependencies
22:48:53   2  select 3,, current_scn, systimestamp from v$database;
select 3,, current_scn, systimestamp from v$database
         *
ERROR at line 2:
ORA-00936: missing expression


22:49:00 SQL> insert into rowdependencies
22:49:06   2  select 3, current_scn, systimestamp from v$database;

1 row created.

22:49:12 SQL> commit;

Commit complete.

22:49:14 SQL> !sleep 5

22:49:21 SQL> insert into rowdependencies
22:49:22   2  select 4, current_scn, systimestamp from v$database;

1 row created.

22:49:35 SQL> commit;

Commit complete.

22:49:37 SQL> insert into rowdependencies
22:49:47   2  select 5, current_scn, systimestamp from v$database;

1 row created.

22:49:56 SQL> commit;

Commit complete.

22:49:57 SQL> !sleep 5

22:50:06 SQL> select id, inserted_scn, inserted_timestamp, ora_rowscn
22:50:17   2  from rowdependencies
22:50:23   3  order by id
22:50:26   4  /

        ID INSERTED_SCN INSERTED_TIMESTAMP                                                          ORA_ROWSCN
---------- ------------ --------------------------------------------------------------------------- ----------
         1      6804210 18-AUG-20 10.48.17.485841 PM                                                   6804228
         2      6805303 18-AUG-20 10.48.42.081454 PM                                                   6805308
         3      6806387 18-AUG-20 10.49.12.242874 PM                                                   6806394
         4      6807475 18-AUG-20 10.49.35.500547 PM                                                   6807478
         5      6808541 18-AUG-20 10.49.56.098645 PM                                                   6808546

22:50:26 SQL>

For this table, eachrow has a different ORA_ROWSCN, although it is not the actual database SCN at the time of the INSERT.  (I have already explained the reason for this in my previous posts here and here).

ORA_ROWSCN does *not* return the actual SCN of the database as of the time of the INSERT or UPDATE DML  but only as of the time of the COMMIT.  However, if you don't specify RowDependencies at the table level, it will return the block level SCN.

Note further :  Caveat : ORA_ROWSCN does not necessarily return the *exact* SCN as of the time of the COMMIT. Because of the complexities of the SCN, the actual value returned might still be slightly different.