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.


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 ?)

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 

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).



27 August, 2020

Using SQL Developer to Copy Data to the Oracle Cloud

 

I have created a short video demonstrating a copy of selected objects from a 19c database accessible from my desktop to an Autonomous (ATP) Database in the Oracle Cloud using SQL Developer 20.2