24 September, 2023

Restoring a PDB from another CDB can increase your SCN

 I start with a 19c CDB called "ORCLCDB" and PDB "ORCLPDB1" that is present in it.


SQL> col name format a16
SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
2778483057 ORCLCDB             20906515

SQL> select dbid, guid, name, open_mode from v$pdbs;

      DBID GUID                             NAME             OPEN_MODE
---------- -------------------------------- ---------------- ----------
2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED         READ ONLY
4057974068 8812E29864F47615E0530100007FA424 ORCLPDB1         READ WRITE
 181927604 C07D1F2BA98C23D0E0530100007F7D34 RMANCAT          MOUNTED

SQL>
SQL> alter pluggable database orclpdb1 close;

Pluggable database altered.

SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Sep 24 09:39:55 2023
Version 19.12.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057)

RMAN> backup pluggable database orclpdb1 tag for_migration;

Starting backup at 24-SEP-23
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00034 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_tpcctab_jyl0mzsp_.dbf
input datafile file number=00009 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
input datafile file number=00026 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf
input datafile file number=00032 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf
channel ORA_DISK_1: starting piece 1 at 24-SEP-23
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00011 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
input datafile file number=00010 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
input datafile file number=00012 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
input datafile file number=00031 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf
channel ORA_DISK_2: starting piece 1 at 24-SEP-23
channel ORA_DISK_1: finished piece 1 at 24-SEP-23
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2023_09_24/o1_mf_nnndf_FOR_MIGRATION_ljz5825q_.bkp tag=FOR_MIGRATION comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_2: finished piece 1 at 24-SEP-23
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2023_09_24/o1_mf_nnndf_FOR_MIGRATION_ljz5828m_.bkp tag=FOR_MIGRATION comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03
Finished backup at 24-SEP-23

Starting Control File and SPFILE Autobackup at 24-SEP-23
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2023_09_24/o1_mf_s_1148377605_ljz585bw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-SEP-23

RMAN>
RMAN> quit


Recovery Manager complete.
oracle19c>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 24 09:48:04 2023
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter pluggable database orclpdb1 unplug into '/opt/oracle/product/19c/dbhome_1/orclpdb1.xml';

Pluggable database altered.

SQL> !ls -l /opt/oracle/product/19c/dbhome_1/orclpdb1.xml
-rw-r--r--. 1 oracle oinstall 12583 Sep 24 09:50 /opt/oracle/product/19c/dbhome_1/orclpdb1.xml

SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>


Now I go to the target CDB CDB2 and identify that there is no conflicting PDB.  I also find that it has a lower SCN.

SQL> col name format a16
SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
 837065713 CDB2                20664227

SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
 837065713 CDB2                20664231

SQL>
SQL> select * from v$pdbs
  2  /

    CON_ID       DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY
---------- --- --------------------------------------------------------------------------- ---------- ---------- ---------- --------
SNAPSHOT_PARENT_CON_ID APP APP APP APPLICATION_ROOT_CON_ID APP PRO LOCAL_UNDO   UNDO_SCN UNDO_TIME CREATION_ DIAGNOSTICS_SIZE
---------------------- --- --- --- ----------------------- --- --- ---------- ---------- --------- --------- ----------------
 PDB_COUNT AUDIT_FILES_SIZE   MAX_SIZE MAX_DIAGNOSTICS_SIZE MAX_AUDIT_SIZE LAST_CHANGE TEM
---------- ---------------- ---------- -------------------- -------------- ----------- ---
TENANT_ID
------------------------------------------------------------------------------------------------------------------------------------
UPGRADE_LEVEL GUID_BASE64
------------- ------------------------------
         2 2054948555 2054948555 88129263B99F4BBDE0530100007F7BDF
PDB$SEED
READ ONLY  NO  24-SEP-23 09.26.31.678 AM +08:00                                               1997190  957349888       8192 ENABLED
                       NO  NO  NO                          NO  NO           1        280           04-MAY-19                0
         0                0          0                    0              0 COMMON USER NO

            1 iBKSY7mfS73gUwEAAH973wA=

         3 2205532678 2205532678 E6BD9C73839C10BEE0550A00275FC834
PDBTMP
MOUNTED                                                                                      17541716  999292928       8192 ENABLED
                       NO  NO  NO                          NO  NO           1        280           21-AUG-22                0
         0                0          0                    0              0 COMMON USER NO

            1 5r2cc4OcEL7gVQoAJ1/INAA=


I then have the backups and the "closed" datafiles from the source CDB copied over to the target.  (The CREATE PLUGGABLE DATABASE command will not be using the RMAN Backups but the PDB Describle XML file and the "closed" datafiles).

oracle19c>pwd
/tmp/From_Source
oracle19c>ls -l
total 1882384
-rw-r-----. 1 oracle oinstall 1146109952 Sep 24 09:56 o1_mf_nnndf_FOR_MIGRATION_ljz5825q_.bkp
-rw-r-----. 1 oracle oinstall  758202368 Sep 24 09:56 o1_mf_nnndf_FOR_MIGRATION_ljz5828m_.bkp
-rw-r-----. 1 oracle oinstall   23232512 Sep 24 09:55 o1_mf_s_1148377605_ljz585bw_.bkp
-rw-r--r--. 1 oracle oinstall      12583 Sep 24 09:59 orclpdb1.xml
oracle19c>


I now prepare to copy the PDB to the target CDB2.. First I check for compatibility.. And then I plug in the PDB with a new name "ORCLPDB1_NEW"

SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
 837065713 CDB2                20664231
SQL> set serveroutput on
SQL> declare
  2   compatible constant varchar2(3) :=
  3     case dbms_pdb.check_plug_compatibility(
  4       pdb_descr_file=>'/tmp/From_Source/orclpdb1.xml',
  5       pdb_name=>'ORCLPDB1')
  6     when true then 'YES'
  7     else 'NO'
  8  END;
  9  begin
 10    dbms_output.put_line(compatible);
 11  end;
 12  /
YES

PL/SQL procedure successfully completed.

SQL>
SQL> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /opt/oracle/oradata

SQL>
SQL> create pluggable database orclpdb1_new
  2  using '/tmp/From_Source/orclpdb1.xml'
  3  copy;  --- copy will copy the datafiles to the location based on db_create_file_dest and CDB database name

Pluggable database created.

SQL>
SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
 837065713 CDB2                20910195

SQL>
SQL> select dbid, guid, name, open_mode from v$pdbs;

      DBID GUID                             NAME             OPEN_MODE
---------- -------------------------------- ---------------- ----------
2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED         READ ONLY
2205532678 E6BD9C73839C10BEE0550A00275FC834 PDBTMP           MOUNTED
4057974068 8812E29864F47615E0530100007FA424 ORCLPDB1_NEW     READ WRITE

SQL>


The SCN of the target CDB2 has now been set to the much higher 20910195 (that was 20664231 before I "plugged in" ORCLDB1 as ORCLDB1_NEW).  This is because it "read" the SCN from the headers of the datafiles that were plugged in.


I can go back to the source and drop the PDB.

SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
2778483057 ORCLCDB             20910076

SQL> select dbid, guid, name, open_mode from v$pdbs;

      DBID GUID                             NAME             OPEN_MODE
---------- -------------------------------- ---------------- ----------
2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED         READ ONLY
4057974068 8812E29864F47615E0530100007FA424 ORCLPDB1         MOUNTED
 181927604 C07D1F2BA98C23D0E0530100007F7D34 RMANCAT          MOUNTED

SQL> 
SQL> drop pluggable database orclpdb1 including datafiles;

Pluggable database dropped.

SQL> 
SQL> select dbid, guid, name, open_mode from v$pdbs;

      DBID GUID                             NAME             OPEN_MODE
---------- -------------------------------- ---------------- ----------
2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED         READ ONLY
 181927604 C07D1F2BA98C23D0E0530100007F7D34 RMANCAT          MOUNTED

SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>


So, I have "migrated" ORCLPDB1 from ORCLCDB to CDB2 and, in the process, have found that the SCN in CDB2 got reset to a much higher value, because it "inherited" the higher Checkpoint SCN that was in the datafiles of ORCLPDB1.

This behaviour, in my opinion, is similar to the SCN Synchronization that occurs when you use Database Links.

Also be aware of this when you use monitoring scripts that check for or rely on SCN values.

I can verify that all the datafiles (that are OPEN) in CDB2 can get stamped with the new SCN.

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

    CON_ID      FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE#
---------- ---------- -------------- ------------------
         1          1              0           20658011
         1          3              0           20658011
         1          4              0           20658011
         1          7              0           20658011
         2          5              0           13768423
         2          6              0           13768423
         2          8              0           13768423
         3         58              0           20657657
         3         59              0           20657657
         3         60              0           20657657
         4         69              0           20908595
         4         70              0           20908595
         4         71              0           20908595
         4         72              0           20908595
         4         73              0           20908595
         4         74              0           20908595
         4         75              0           20908595
         4         76              0           20908595

18 rows selected.

SQL> alter system checkpoint;

System altered.

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

    CON_ID      FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE#
---------- ---------- -------------- ------------------
         1          1              0           20912767
         1          3              0           20912767
         1          4              0           20912767
         1          7              0           20912767
         2          5              0           13768423
         2          6              0           13768423
         2          8              0           13768423
         3         58              0           20657657
         3         59              0           20657657
         3         60              0           20657657
         4         69              0           20912767
         4         70              0           20912767
         4         71              0           20912767
         4         72              0           20912767
         4         73              0           20912767
         4         74              0           20912767
         4         75              0           20912767
         4         76              0           20912767

18 rows selected.

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

    CON_ID NAME             OPEN_MODE
---------- ---------------- ----------
         2 PDB$SEED         READ ONLY
         3 PDBTMP           MOUNTED
         4 ORCLPDB1_NEW     READ WRITE

SQL> alter pluggable database pdbtmp open;

Pluggable database altered.

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

    CON_ID      FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE#
---------- ---------- -------------- ------------------
         1          1              0           20912767
         1          3              0           20912767
         1          4              0           20912767
         1          7              0           20912767
         2          5              0           13768423
         2          6              0           13768423
         2          8              0           13768423
         3         58              0           20912888
         3         59              0           20912888
         3         60              0           20912888
         4         69              0           20912767
         4         70              0           20912767
         4         71              0           20912767
         4         72              0           20912767
         4         73              0           20912767
         4         74              0           20912767
         4         75              0           20912767
         4         76              0           20912767

18 rows selected.

SQL> alter system checkpoint;

System altered.

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

    CON_ID      FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE#
---------- ---------- -------------- ------------------
         1          1              0           20912967
         1          3              0           20912967
         1          4              0           20912967
         1          7              0           20912967
         2          5              0           13768423
         2          6              0           13768423
         2          8              0           13768423
         3         58              0           20912967
         3         59              0           20912967
         3         60              0           20912967
         4         69              0           20912967
         4         70              0           20912967
         4         71              0           20912967
         4         72              0           20912967
         4         73              0           20912967
         4         74              0           20912967
         4         75              0           20912967
         4         76              0           20912967

18 rows selected.

SQL>
SQL> alter session set container=ORCLPDB1_NEW;

Session altered.
SQL> col name format a54
SQL> select name from v$datafile;

NAME
------------------------------------------------------
/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_system_ljz7d02h_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_sysaux_ljz7d02l_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_undotbs1_ljz7d02m_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_users_ljz7d02o_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_data_min_ljz7d02p_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_hemant_d_ljz7d02r_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_hemant_d_ljz7d02s_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_tpcctab_ljz7d02t_.dbf


8 rows selected.

SQL>



CON_ID=1 is Root and the datafiles get the new SCN after a Checkpoint.   Note these datafiles still had the "older" SCN 20658011 before I issued the Checkpoint.
CON_ID=2  is the SEED PDB so it does not get updated.
CON_ID=3 is PDTMP  at 20657657 and is stamped to 2091288 after I OPEN it.
CON_ID=4 is the ORCLPDB1_NEW that I plugged in today.
All the non-SEED datafiles are stamped again (to 20912967) after another Checkpoint.