An excellent article by Franck Pachot :
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
22 September, 2020
12 September, 2020
Checking the new PDB as a no-data-clone
In the previous post, I had created a NEWDB as a "NO DATA" clone (even also explicitly excluding all USER_TABLESPACES).
FYI, these are the entries in the Primary database alert log (entries in the Standby are shown in the previous post) :
2020-09-07T23:35:32.840917+08:00
create pluggable database newpdb
from orclpdb1
storage (maxsize 10G)
service_name_convert=('ORCLPDB1','NEWPDB')
user_tablespaces=NONE
standbys=ALL
no data
2020-09-07T23:35:35.215231+08:00
ORCLPDB1(3): MDSYS.SDO_COORD_OP_PARAM_VALS (PARAM_VALUE_FILE) - CLOB populated
2020-09-07T23:36:04.284729+08:00
NEWPDB(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database NEWPDB with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000118
****************************************************************
NEWPDB(4):Autotune of undo retention is turned on.
NEWPDB(4):Undo initialization recovery: err:0 start: 786885 end: 786904 diff: 19 ms (0.0 seconds)
NEWPDB(4):[2298] Successfully onlined Undo Tablespace 2.
NEWPDB(4):Undo initialization online undo segments: err:0 start: 786905 end: 786994 diff: 89 ms (0.1 seconds)
NEWPDB(4):Undo initialization finished serial:0 start:786885 end:786996 diff:111 ms (0.1 seconds)
NEWPDB(4):Database Characterset for NEWPDB is AL32UTF8
NEWPDB(4):JIT: pid 2298 requesting stop
2020-09-07T23:36:05.350701+08:00
NEWPDB(4):Buffer Cache flush started: 4
NEWPDB(4):Buffer Cache flush finished: 4
Completed: create pluggable database newpdb
from orclpdb1
storage (maxsize 10G)
service_name_convert=('ORCLPDB1','NEWPDB')
user_tablespaces=NONE
standbys=ALL
no data
2020-09-07T23:40:15.148214+08:00
alter pluggable database orclpdb1 close
2020-09-07T23:40:15.165239+08:00
ORCLPDB1(3):JIT: pid 2298 requesting stop
ORCLPDB1(3):Buffer Cache flush started: 3
ORCLPDB1(3):Buffer Cache flush finished: 3
Pluggable database ORCLPDB1 closed
Completed: alter pluggable database orclpdb1 close
2020-09-07T23:40:22.786209+08:00
alter pluggable database orclpdb1 open read write
ORCLPDB1(3):Autotune of undo retention is turned on.
2020-09-07T23:40:23.211240+08:00
ORCLPDB1(3):Endian type of dictionary set to little
ORCLPDB1(3):Undo initialization recovery: err:0 start: 1045210 end: 1045246 diff: 36 ms (0.0 seconds)
ORCLPDB1(3):[2298] Successfully onlined Undo Tablespace 2.
ORCLPDB1(3):Undo initialization online undo segments: err:0 start: 1045246 end: 1045562 diff: 316 ms (0.3 seconds)
ORCLPDB1(3):Undo initialization finished serial:0 start:1045210 end:1045596 diff:386 ms (0.4 seconds)
2020-09-07T23:40:23.793691+08:00
ORCLPDB1(3):Database Characterset for ORCLPDB1 is AL32UTF8
2020-09-07T23:40:26.180462+08:00
ORCLPDB1(3):Opening pdb with no Resource Manager plan active
ORCLPDB1(3):joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 2298 cid 3
Pluggable database ORCLPDB1 opened read write
Completed: alter pluggable database orclpdb1 open read write
2020-09-07T23:40:34.702216+08:00
alter pluggable database newpdb close
ORA-65020 signalled during: alter pluggable database newpdb close...
2020-09-07T23:40:44.050260+08:00
alter pluggable database newpdb open read write
NEWPDB(4):Autotune of undo retention is turned on.
2020-09-07T23:40:44.313418+08:00
NEWPDB(4):Endian type of dictionary set to little
NEWPDB(4):Undo initialization recovery: err:0 start: 1066307 end: 1066330 diff: 23 ms (0.0 seconds)
NEWPDB(4):[2298] Successfully onlined Undo Tablespace 2.
NEWPDB(4):Undo initialization online undo segments: err:0 start: 1066330 end: 1066564 diff: 234 ms (0.2 seconds)
NEWPDB(4):Undo initialization finished serial:0 start:1066307 end:1066592 diff:285 ms (0.3 seconds)
NEWPDB(4):Deleting old file#9 from file$
NEWPDB(4):Deleting old file#10 from file$
NEWPDB(4):Deleting old file#11 from file$
NEWPDB(4):Deleting old file#12 from file$
NEWPDB(4):Adding new file#13 to file$(old file#9). fopr-1, newblks-35840, oldblks-19200
NEWPDB(4):Adding new file#14 to file$(old file#10). fopr-1, newblks-47360, oldblks-15360
NEWPDB(4):Adding new file#15 to file$(old file#11). fopr-1, newblks-33280, oldblks-12800
NEWPDB(4):Marking tablespace #5 offline since it has been requested to be skipped as part of the USER_TABLESPACES clause. The tablespace cannot be brought online and needs to be dropped and recreated if it needs to brought online.
2020-09-07T23:40:45.094463+08:00
NEWPDB(4):Successfully created internal service NEWPDB at open
****************************************************************
Post plug operations are now complete.
Pluggable database NEWPDB with pdb id - 4 is now marked as NEW.
****************************************************************
2020-09-07T23:40:45.313606+08:00
NEWPDB(4):Pluggable database NEWPDB dictionary check beginning
NEWPDB(4):Pluggable Database NEWPDB Dictionary check complete
NEWPDB(4):Database Characterset for NEWPDB is AL32UTF8
2020-09-07T23:40:50.931146+08:00
NEWPDB(4):Opening pdb with no Resource Manager plan active
NEWPDB(4):joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 2298 cid 4
Pluggable database NEWPDB opened read write
Completed: alter pluggable database newpdb open read write
2020-09-07T23:44:00.682159+08:00
Control autobackup written to DISK device
handle '/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_09_07/o1_mf_s_1050536639_hodob0c4_.bkp'
But let's now check if NEWPDB has any left-over objects from the Data Dictionary of ORCLPDB1.
SQL> select con_id, name, open_mode
2 from v$pdbs
3 order by 1
4 /
CON_ID NAME OPEN_MODE
---------- ---------------- ----------
2 PDB$SEED READ ONLY
3 ORCLPDB1 READ WRITE
4 NEWPDB MOUNTED
SQL> alter pluggable database newpdb open read write;
Pluggable database altered.
SQL> select con_id, name, open_mode
2 from v$pdbs
3 order by 1
4 /
CON_ID NAME OPEN_MODE
---------- ---------------- ----------
2 PDB$SEED READ ONLY
3 ORCLPDB1 READ WRITE
4 NEWPDB READ WRITE
SQL>
SQL> select con_id, name, creation_time
2 from v$pdbs
3 order by 1
4 /
CON_ID NAME CREATION_
---------- ---------------- ---------
2 PDB$SEED 04-MAY-19
3 ORCLPDB1 04-MAY-19
4 NEWPDB 07-SEP-20
SQL>
First get the list from ORCLPDB1 :
SQL> alter session set container=ORCLPDB1; Session altered. SQL> SQL> select username, created, oracle_maintained 2 from dba_users 3 where username = 'HEMANT' 4 / USERNAME CREATED O ---------------- --------- - HEMANT 07-MAY-19 N SQL> SQL> l 1 select object_type, trunc(created), status, count(*) 2 from dba_objects 3 where owner = 'HEMANT' 4 group by object_type, trunc(created), status 5* order by 1,2 SQL> / OBJECT_TYPE TRUNC(CRE STATUS COUNT(*) -------------------- --------- ------- ---------- INDEX 18-AUG-19 VALID 3 INDEX 16-SEP-19 VALID 1 INDEX 29-SEP-19 VALID 3 INDEX 12-OCT-19 VALID 2 INDEX 19-OCT-19 VALID 2 INDEX 27-OCT-19 VALID 3 INDEX 28-OCT-19 VALID 2 INDEX 12-NOV-19 VALID 4 INDEX 12-JUL-20 VALID 2 JOB 12-OCT-19 VALID 1 MATERIALIZED VIEW 18-AUG-19 VALID 1 MATERIALIZED VIEW 16-SEP-19 VALID 1 MATERIALIZED VIEW 25-SEP-19 VALID 1 MATERIALIZED VIEW 29-SEP-19 VALID 2 MATERIALIZED VIEW 12-OCT-19 VALID 2 MATERIALIZED VIEW 27-OCT-19 VALID 2 MATERIALIZED VIEW 12-NOV-19 VALID 1 TABLE 07-MAY-19 VALID 1 TABLE 18-AUG-19 VALID 4 TABLE 16-SEP-19 VALID 1 TABLE 25-SEP-19 VALID 1 TABLE 29-SEP-19 VALID 3 TABLE 12-OCT-19 VALID 2 TABLE 19-OCT-19 VALID 3 TABLE 27-OCT-19 VALID 4 TABLE 28-OCT-19 VALID 3 TABLE 12-NOV-19 VALID 4 TABLE 26-MAR-20 VALID 1 TABLE 20-MAY-20 VALID 1 TABLE 12-JUL-20 VALID 1 TABLE 10-AUG-20 VALID 1 TABLE 12-AUG-20 VALID 2 TABLE 14-AUG-20 VALID 4 TABLE 18-AUG-20 VALID 3 TABLE PARTITION 07-MAY-19 VALID 4 35 rows selected. SQL>
Now get the list from NEWPDB :
SQL> alter session set container=NEWPDB; Session altered. SQL> select username, created, oracle_maintained 2 from dba_users 3 where username = 'HEMANT' 4 / USERNAME CREATED O ---------------- --------- - HEMANT 07-MAY-19 N SQL> SQL> l 1 select object_type, trunc(created), status, count(*) 2 from dba_objects 3 where owner = 'HEMANT' 4 group by object_type, trunc(created), status 5* order by 1,2 SQL> / OBJECT_TYPE TRUNC(CRE STATUS COUNT(*) -------------------- --------- ------- ---------- INDEX 18-AUG-19 VALID 3 INDEX 16-SEP-19 VALID 1 INDEX 29-SEP-19 VALID 3 INDEX 12-OCT-19 VALID 2 INDEX 19-OCT-19 VALID 2 INDEX 27-OCT-19 VALID 3 INDEX 28-OCT-19 VALID 2 INDEX 12-NOV-19 VALID 4 INDEX 12-JUL-20 VALID 2 JOB 12-OCT-19 VALID 1 MATERIALIZED VIEW 18-AUG-19 VALID 1 MATERIALIZED VIEW 16-SEP-19 VALID 1 MATERIALIZED VIEW 25-SEP-19 VALID 1 MATERIALIZED VIEW 29-SEP-19 VALID 2 MATERIALIZED VIEW 12-OCT-19 VALID 2 MATERIALIZED VIEW 27-OCT-19 VALID 2 MATERIALIZED VIEW 12-NOV-19 VALID 1 TABLE 07-MAY-19 VALID 1 TABLE 18-AUG-19 VALID 4 TABLE 16-SEP-19 VALID 1 TABLE 25-SEP-19 VALID 1 TABLE 29-SEP-19 VALID 3 TABLE 12-OCT-19 VALID 2 TABLE 19-OCT-19 VALID 3 TABLE 27-OCT-19 VALID 4 TABLE 28-OCT-19 VALID 3 TABLE 12-NOV-19 VALID 4 TABLE 26-MAR-20 VALID 1 TABLE 20-MAY-20 VALID 1 TABLE 12-JUL-20 VALID 1 TABLE 10-AUG-20 VALID 1 TABLE 12-AUG-20 VALID 2 TABLE 14-AUG-20 VALID 4 TABLE 18-AUG-20 VALID 3 TABLE PARTITION 07-MAY-19 VALID 4 35 rows selected. SQL>
That's interesting. Let's explicitly connect to each PDB and verify.
ORCLPDB1 first
Now, NEWPDB :
So, NEWPDB has the object definitions but really has no data.
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> select table_name
2 from user_tables
3 order by 1
4 /
TABLE_NAME
------------------------------
EMPLOYEES
HEMANT_SOURCE_TBL
MLOG$_HEMANT_SOURCE_TBL
MLOG$_MY_LARGE_SOURCE
MLOG$_NEW_SOURCE_TABLE
MLOG$_SOURCE_TABLE
MLOG$_SOURCE_TABLE_1
MV_1
MV_2
MV_3
MV_DEPT
MV_EMP
MV_FAST_NOT_POSSIBLE
MV_OF_SOURCE
MY_LARGE_SOURCE
MY_TARGET
MY_TRANSACTIONS
NEW_MV
NEW_MV_2_1
NEW_MV_2_2
NEW_SOURCE_TABLE
NOROWDEP
ROWDEPENDENCIES
RUPD$_HEMANT_SOURCE_TBL
RUPD$_MY_LARGE_SOURCE
RUPD$_SOURCE_TABLE
RUPD$_SOURCE_TABLE_1
SOURCE_TABLE
SOURCE_TABLE_1
SOURCE_TABLE_2
TABLE_A
TABLE_B
T_1
T_2
T_3
T_4
WIDGETS_LIST
X
XYZ
39 rows selected.
SQL>
SQL> select count(*) from employees
2 /
COUNT(*)
----------
10000
SQL> select count(*) from my_target
2 /
COUNT(*)
----------
997
SQL>
Now, NEWPDB :
SQL> connect hemant/hemant@newpdb
Connected.
SQL> select table_name
2 from user_tables
3 order by 1
4 /
TABLE_NAME
------------------------------
EMPLOYEES
HEMANT_SOURCE_TBL
MLOG$_HEMANT_SOURCE_TBL
MLOG$_MY_LARGE_SOURCE
MLOG$_NEW_SOURCE_TABLE
MLOG$_SOURCE_TABLE
MLOG$_SOURCE_TABLE_1
MV_1
MV_2
MV_3
MV_DEPT
MV_EMP
MV_FAST_NOT_POSSIBLE
MV_OF_SOURCE
MY_LARGE_SOURCE
MY_TARGET
MY_TRANSACTIONS
NEW_MV
NEW_MV_2_1
NEW_MV_2_2
NEW_SOURCE_TABLE
NOROWDEP
ROWDEPENDENCIES
RUPD$_HEMANT_SOURCE_TBL
RUPD$_MY_LARGE_SOURCE
RUPD$_SOURCE_TABLE
RUPD$_SOURCE_TABLE_1
SOURCE_TABLE
SOURCE_TABLE_1
SOURCE_TABLE_2
TABLE_A
TABLE_B
T_1
T_2
T_3
T_4
WIDGETS_LIST
X
XYZ
39 rows selected.
SQL>
SQL> select count(*) from employees;
COUNT(*)
----------
0
SQL> select count(*) from my_target;
COUNT(*)
----------
0
SQL>
So, NEWPDB has the object definitions but really has no data.
07 September, 2020
Creating a PDB as a Clone in a DataGuard environment
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.
Subscribe to:
Posts (Atom)