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