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

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.


No comments: