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 ?)
The source PDB had to be Read Only but the cloned NEWDB is not OPEN when created.
I have a Service called "newpdb" created for the new PDB.
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
The Standby alert log shows :
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.
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.
No comments:
Post a Comment