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.
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.
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.
No comments:
Post a Comment