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