27 December, 2021

DataPump Export using SQL Developer

 I have published a video demonstration of using SQL Developer to run a DataPump export.

Although I did not choose the "Export read-consistent view of data" (at 04:00 in the video), I strongly recommend using this option when exporting multiple tables and/or multiple schemas in a database with concurrent transactions while the export is running.

For example :

Transaction T1 might Insert into Table Y and then Delete from Table X. If DataPump does an export of Table X before the Delete and/or an export of Table Y after the Insert, the exported data would be inconsistent as far as these two tables are concerned.

Transaction T1 might Update Table G Some minutes later, Transaction T2 might Insert/Update/Delete data in Table H based on the (committed) rows in Table G. If DataPump exports Tables G and H before the DML on Table H (and after the Update at Table G), you would get inconsistent data in the export dump because the data in the dump would reflect two different points in time and not be read-consistent.

This can also happen with Materialized View Refresh's.





05 December, 2021

Refreshable Clone PDB -- 3 The ArchiveLog and ArchParLog files

 In my previous blog post about Datafile Name mapping for a Refreshable Clone PDB, I had presented only a portion of the trace file for the ALTER PLUGGABLE DATABASE RO_PDB REFRESH command.

Here is the full listing of that trace file (I have already explained the AFN mapping of FILE#s)


Trace file /opt/oracle/diag/rdbms/cdb2/CDB2/trace/CDB2_ora_6159.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
Build label:    RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715
ORACLE_HOME:    /opt/oracle/product/19c/dbhome_1
System name:    Linux
Node name:      ora19cs2
Release:        4.14.35-1902.10.4.1.el7uek.x86_64
Version:        #2 SMP Mon Jan 27 14:13:38 PST 2020
Machine:        x86_64
Instance name: CDB2
Redo thread mounted by this instance: 1
Oracle process number: 59
Unix process pid: 6159, image: oracle@ora19cs2


*** 2021-12-04T17:39:25.431585+08:00 (RO_PDB(5))
*** SESSION ID:(377.22911) 2021-12-04T17:39:25.431608+08:00
*** CLIENT ID:() 2021-12-04T17:39:25.431613+08:00
*** SERVICE NAME:(CDB2) 2021-12-04T17:39:25.431617+08:00
*** MODULE NAME:(sqlplus@ora19cs2 (TNS V1-V3)) 2021-12-04T17:39:25.431622+08:00
*** ACTION NAME:() 2021-12-04T17:39:25.431626+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-12-04T17:39:25.431630+08:00
*** CONTAINER ID:(5) 2021-12-04T17:39:25.431634+08:00

JIT: pid 6159 requesting stop

*** 2021-12-04T17:39:48.052938+08:00 (RO_PDB(5))
Started Serial Media Recovery
This is Foreign Recovery:- HotClone:1 Pre-Plugin:0 HotClone_for_Refresh:1. The Redo going to be applied in Current PDB(Target) will be from Foreign PDB(Source) and there will be file# conversion. Source AFN is from Foreign DB and Target AFN is on Current(local) DB. Dumping Source PDB:3 to Target PDB:5 AFN mapping.
Source AFN:26 = Target AFN:47
Source AFN:32 = Target AFN:49
Source AFN:12 = Target AFN:46
Source AFN:31 = Target AFN:48
Source AFN:9 = Target AFN:43
Source AFN:11 = Target AFN:45
Source AFN:10 = Target AFN:44
Dumping database incarnation table:
Resetlogs 0 scn and time: 0x0000000000f22944 11/30/2021 22:58:02
Dumping PDB pathvec - index 0
   0000 : pdb 5, dbinc 3, pdbinc 0
          db rls 0x0000000000f22944 rlc 1090018682
          incscn 0x0000000000000000 ts 0
          br scn 0x0000000000000000 ts 0
          er scn 0x0000000000000000 ts 0
   0001 : pdb 5, dbinc 2, pdbinc 0
          db rls 0x0000000000e88404 rlc 1084312848
          incscn 0x0000000000000000 ts 0
          br scn 0x0000000000000000 ts 0
          er scn 0x0000000000000000 ts 0
Recovery target incarnation = 3, activation ID = 0
Influx buffer limit = 39160 min(50% x 78320, 100000)
Start recovery at thread 1 ckpt scn 16121330 logseq 37 block 41766

*** 2021-12-04T17:39:48.430170+08:00 (RO_PDB(5))
Media Recovery add redo thread 1

*** 2021-12-04T17:39:48.494955+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_37_jtpcbxyv_.arc

*** 2021-12-04T17:39:48.868915+08:00 (RO_PDB(5))
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T17:39:51.447683+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/foreign_archivelog/ORCLPDB1/2021_12_04/o1_mf_1_38_jtpfttmz_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T17:40:04.041793+08:00 (RO_PDB(5))
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 89402Kb
-- Redo read_disk statistics --
Read rate (SYNC): 89402Kb in 15.61s => 5.59 Mb/sec
Total redo bytes: 89402Kb Longest record: 24Kb, moves: 78/213168 moved: 0Mb (0%)
Longest LWN: 2052Kb, reads: 3089
Last redo scn: 0x0000000000f7e789 (16246665)
Change vector header moves = 31243/386616 (8%)
----------------------------------------------

*** 2021-12-04T17:40:04.041915+08:00 (RO_PDB(5))
Media Recovery drop redo thread 1

*** 2021-12-04T17:40:07.389420+08:00 (RO_PDB(5))
KCBR: Number of read descriptors = 1024
KCBR: Media recovery blocks read (ASYNC) = 650
KCBR: Influx buffers flushed = 40 times
KCBR: Reads = 30 reaps (5 null, 8 wait), 2 all
KCBR: Redo cache copies/changes = 15025/15025

*** 2021-12-04T17:40:07.389466+08:00 (RO_PDB(5))
Completed Media Recovery
----- Abridged Call Stack Trace -----
(deleted by me)


The interesting parts are :
"Start recovery at thread 1 ckpt scn 16121330 logseq 37 block 41766"
and
"Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_37_jtpcbxyv_.arc"
and
"Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/foreign_archivelog/ORCLPDB1/2021_12_04/o1_mf_1_38_jtpfttmz_.arc"

It identifies the SCN from which it needs to recover transactions from the source CDB (ORCLCDB) and PDB (ORCLPDB1).  In a MultiTenant environment, all the PDBs share the same Redo Thread -- so any ArchiveLog may have transactions of multple PDBs.
For the purpose of refresh RO_PDB in CDB2, it identifies the entries from the source CDB ArchiveLogs.  For Log Sequence#28, it explicitly identifies the ORCLPDB1 (not the whole CDB) entries as "foreign archivelog".  However, this "foreign archivelog" is later automaticallly purged by ORCLCDB as I can see in the ORCLCDB alert log entry

2021-12-04T17:40:07.555215+08:00
Deleted Oracle managed file /opt/oracle/FRA/ORCLCDB/ORCLCDB/foreign_archivelog/ORCLPDB1/2021_12_04/o1_mf_1_38_jtpfttmz_.arc


However, sometimes the Refresh requires to read Redo Entries where an ArchiveLog has not been completely generated.  For example, a later Refresh at 22:41 required a partial ArchiveLog for Sequence#41

Start recovery at thread 1 ckpt scn 16246668 logseq 0 block 0

*** 2021-12-04T22:41:32.365818+08:00 (RO_PDB(5))
Media Recovery add redo thread 1

*** 2021-12-04T22:41:32.370629+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_38_jtpzhl2s_.arc

*** 2021-12-04T22:41:32.470154+08:00 (RO_PDB(5))
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T22:41:39.259689+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_39_jtpzhxck_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T22:41:42.187854+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_40_jtpzhy18_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T22:41:42.773949+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/product/19c/dbhome_1/dbs/archparlog_1_41_f1dfc534_1084312848.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!


Sequence#41 was still an Online Redo Log at ORCLCDB and had not yet been archived (In fact, the source ORCLPDB FRA was full and ORCLPDB was unable to Archive Log files). So, it seems that for Sequence#41, a Partial Extract was obtained so as to Refresh the Clone PDB.
This doesn't seem to be documented.

04 December, 2021

Refreshable Clone PDB -- 2 Datafile Names

 In my previous post, I demonstrated creating a Refreshable Clone PDB.  How do you handle datafile names ? You can use the FILE_NAME_CONVERT to change file names based on substring matching of the file names.  Or you could simply rely on the good-old (old nowadays !) feature / parameter DB_CREATE_FILE_DEST

In the ORCLPDB1 Pluggable Database at ORCLDB (Source),  these are my datafiles :

SQL> connect sys/manager@orclcdb as sysdba
Connected.
SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /opt/oracle/oradata
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> col file_name format a98
SQL> select file_id, file_name
  2  from dba_data_files
  3  order by file_id
  4  /

   FILE_ID FILE_NAME
---------- --------------------------------------------------------------------------------------------------
         9 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
        10 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
        11 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
        12 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
        26 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf
        31 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf
        32 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf

7 rows selected.

SQL>



Note that DB_CREATE_FILE_DEST can be modified at any time. Here you can see that it was  modified before the the last 3 tablespaces/datafiles were added to the database.  (The datafiles are automatically sized at an initial 100MB, autoextensible to 32GB).

So, what are the file names in the RO_PDB at CDB2 ?


SQL> connect sys/manager@cdb2 as sysdba
Connected.
SQL> alter pluggable database ro_pdb 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> alter session set container=RO_PDB;

Session altered.

SQL> col file_name format a98
SQL> select file_id, file_name
  2  from dba_data_files
  3  order by file_id
  4  /

no rows selected

SQL>


HEY ! Why I can't I see the data files in RO_PDB ?  This is likely Oracle Bug#30536162.  See MOS Document "Some Data File Missing From DBA_DATA_FILES or CDB_DATA_FILES in Refreshable PDB (Doc ID 2610743.1)".   The workaround is to use V$DATAFILE at the Instance Level.
Let me check that in both instances.

At the source :

SQL> connect sys/manager@ORCLCDB as sysdba
Connected.
SQL> select file#, name
  2  from v$datafile
  3  where con_id =
  4    (select con_id
  5     from v$pdbs
  6     where name = 'ORCLPDB1')
  7  order by 1
  8  /

 FILE# NAME
------ --------------------------------------------------------------------------------------------------
     9 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
    10 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
    11 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
    12 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
    26 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf
    31 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf
    32 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf

7 rows selected.

SQL>


And, at the target :

SQL> connect sys/manager@cdb2 as sysdba
Connected.
SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /opt/oracle/oradata
SQL>
SQL> select file#, name
  2  from v$datafile
  3  where con_id =
  4    (select con_id
  5     from v$pdbs
  6     where name = 'RO_PDB')
  7  order by 1
  8  /

     FILE# NAME
---------- --------------------------------------------------------------------------------------------------
        43 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_system_jtncqq6j_.dbf
        44 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_sysaux_jtncqq6n_.dbf
        45 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_undotbs1_jtncqq6o_.dbf
        46 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_users_jtncqq6r_.dbf
        47 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_data_min_jtncqq6s_.dbf
        48 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_hemant_d_jtncqq6t_.dbf
        49 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_hemant_d_jtncqq6v_.dbf

7 rows selected.

SQL>


Unlike the source ORCLPDB1 in ORCLCDB, the datafiles in RO_PDB at CDB2 are all OMF files because I had configured DB_CREATE_FILE_DEST before creating RO_PDB.

Why are the FILE_IDs (FILE# in V$DATAFILE) different between the Source PDB and the Target PDB ?  This is because the Source Container Database and Target Container Database may have different numbers of Pluggable Databases, each with different numbers of datafiles.  So, when the Target Pluggable Database is created from the Source PDB, it is allocated FILE# values as they are available in the target Container Database.

For example, these are the numbers at the Source (4 CON_IDs, 18 Datafiles) :

SQL> select con_id, file#
  2  from v$datafile
  3  order by 1,2
  4  /

    CON_ID  FILE#
---------- ------
         1      1
         1      3
         1      4
         1      7
         2      5
         2      6
         2      8
         3      9
         3     10
         3     11
         3     12
         3     26
         3     31
         3     32
         4     27
         4     28
         4     29
         4     30

18 rows selected.

SQL>
SQL> select con_id
  2  from v$pdbs
  3  where name = 'ORCLPDB1'
  4  /

    CON_ID
----------
         3

SQL>


And these are at the Target (5 CON_ID, 23 datafiles)

SQL> select con_id, file#
  2  from v$datafile
  3  order by 1,2
  4  /

    CON_ID      FILE#
---------- ----------
         1          1
         1          3
         1          4
         1          7
         2          5
         2          6
         2          8
         3          9
         3         10
         3         11
         3         12
         3         26
         4         27
         4         28
         4         29
         4         30
         5         43
         5         44
         5         45
         5         46
         5         47
         5         48
         5         49

23 rows selected.

SQL> select con_id
  2  from v$pdbs
  3  where name = 'RO_PDB'
  4  /

    CON_ID
----------
         5

SQL>


The Source ORCLPDB1 is CON_ID=3 at ORCLCDB and the Target RO_PDB is CON_ID=5 at CDB2.

If I refresh RO_PDB from the source

SQL> alter pluggable database ro_pdb close;

Pluggable database altered.

SQL> alter pluggable database ro_pdb refresh;

Pluggable database altered.

SQL> alter pluggable database ro_pdb open read only;

Pluggable database altered.

SQL>


I find this in the session's trace file at CDB2 (the Target doing the Refresh) :

Trace file /opt/oracle/diag/rdbms/cdb2/CDB2/trace/CDB2_ora_6159.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
Build label:    RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715
ORACLE_HOME:    /opt/oracle/product/19c/dbhome_1
System name:    Linux
Node name:      ora19cs2
Release:        4.14.35-1902.10.4.1.el7uek.x86_64
Version:        #2 SMP Mon Jan 27 14:13:38 PST 2020
Machine:        x86_64
Instance name: CDB2
Redo thread mounted by this instance: 1
Oracle process number: 59
Unix process pid: 6159, image: oracle@ora19cs2


*** 2021-12-04T17:39:25.431585+08:00 (RO_PDB(5))
*** SESSION ID:(377.22911) 2021-12-04T17:39:25.431608+08:00
*** CLIENT ID:() 2021-12-04T17:39:25.431613+08:00
*** SERVICE NAME:(CDB2) 2021-12-04T17:39:25.431617+08:00
*** MODULE NAME:(sqlplus@ora19cs2 (TNS V1-V3)) 2021-12-04T17:39:25.431622+08:00
*** ACTION NAME:() 2021-12-04T17:39:25.431626+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-12-04T17:39:25.431630+08:00
*** CONTAINER ID:(5) 2021-12-04T17:39:25.431634+08:00

JIT: pid 6159 requesting stop

*** 2021-12-04T17:39:48.052938+08:00 (RO_PDB(5))
Started Serial Media Recovery
This is Foreign Recovery:- HotClone:1 Pre-Plugin:0 HotClone_for_Refresh:1. The Redo going to be applied in Current PDB(Target) will be from Foreign PDB(Source) and there will be file# conversion. Source AFN is from Foreign DB and Target AFN is on Current(local) DB. Dumping Source PDB:3 to Target PDB:5 AFN mapping.
Source AFN:26 = Target AFN:47
Source AFN:32 = Target AFN:49
Source AFN:12 = Target AFN:46
Source AFN:31 = Target AFN:48
Source AFN:9 = Target AFN:43
Source AFN:11 = Target AFN:45
Source AFN:10 = Target AFN:44
.... and there is more information in the trace file which I will cover in my next Blog Post


The Source FILE# values (from ORCLPDB1 at ORCLCDB) are : 26, 32, 12, 31, 9, 11, 10
The corresponding Target FILE# values (in RO_PDB at CDB2) are : 47, 49, 46, 48, 43, 45, 44

To match them, check that the single SYSTEM tablespace datafile  is FILE#9 in ORCLPDB1 and FILE#43 in RO_PDB.
The single USERS tablespace datafile is FILLE#12 in ORCLPDB1 and FILE#46 in RO_PDB.
The two HEMANT_DATA tablespace datafile are FILE#s 31, 32 in ORCLPDB1 and FILE#s 48, 49 in RO_PDB.
Quite unfortunately, it seems that the mapping information in the Trace File is not ordered by FILE#

The trace file for the ALTER PLUGGABLE DATABASE RO_PDB has more information which I will cover in the next Blog Post.