24 July, 2017

New Video on Latches

I have published a new YouTube Video on Latches in Oracle.
.
.
.


No comments:

10 July, 2017

12c MultiTenant Posts -- 6 : Partial (aka Subset) Cloning of PDB

Note : This is a 12.2 feature.

Normally, if you clone a PDB, you'd get a full copy with all the tablespaces.  Now, in 12.2, you can exclude non-essential tablespaces by specifying USER TABLESPACES -- those that you want cloned.  (SYSTEM, SYSAUX and Local UNDO will certainly be cloned).

Let me start with the "NEWPDB" PDB (that I've used in previous examples) that has one more schema and tablespace:

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 10 21:52:55 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Mon Jul 10 2017 11:04:00 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select tablespace_name from dba_tablespaces order by 1;

TABLESPACE_NAME
------------------------------
HRDATA
MYDATA
SYSAUX
SYSTEM
TEMP
UNDOTBS1

6 rows selected.

SQL> col owner format a8           
SQL> col segment_name format a30
SQL> col tablespace_name format a8
SQL> select owner, segment_name, tablespace_name
  2  from dba_segments
  3  where tablespace_name like '%DATA'
  4  order by 1,2
  5  /

OWNER  SEGMENT_NAME   TABLESPA
-------- ------------------------------ --------
HEMANT  BIN$UVb24iaCIE/gUwEAAH/WaQ==$0 MYDATA
HEMANT  BIN$UVb24iaIIE/gUwEAAH/WaQ==$0 MYDATA
HEMANT  HKC_STORE_FILE   MYDATA
HEMANT  I    MYDATA
HEMANT  OBJ_LIST   MYDATA
HEMANT  SYS_IL0000073525C00003$$ MYDATA
HEMANT  SYS_IL0000073532C00003$$ MYDATA
HEMANT  SYS_IL0000073535C00003$$ MYDATA
HEMANT  SYS_LOB0000073525C00003$$ MYDATA
HEMANT  SYS_LOB0000073532C00003$$ MYDATA
HEMANT  SYS_LOB0000073535C00003$$ MYDATA
HEMANT  T    MYDATA
HR  EMPLOYEES   HRDATA

13 rows selected.

SQL> 
SQL> select * from hr.employees;

EMPLOYEE_ID FIRST_NAME      LAST_NAME
----------- ------------------------------ ------------------------------
HIRE_DATE DEPARTMENT_ID     SALARY EMAIL_ID
--------- ------------- ---------- ---------------------------------------------
   1 Hemant      Chitale
06-JUL-17       1      15000 hemant@mydomain.com


SQL>


Besides, the HEMANT objects in the MYDATA tablespace, I now have HR owning an EMPLOYEES table in the HRDATA tablespace.

Now, I want to clone the NEWPDB tablespace but want to exclude HR data.

First, I set a target location for the datafiles.

$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 10 21:57:33 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter db_create_file

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest       string
SQL> alter session set db_create_file_dest='/u02/oradata';

Session altered.

SQL>


Next, I create my Partial (or SubSet) Clone PDB:

SQL> create pluggable database NONHR from NEWPDB user_tablespaces=('MYDATA');

Pluggable database created.

SQL> 
SQL> select con_id, file#, name
  2  from v$datafile
  3  order by 1,2
  4  /

    CON_ID FILE#
---------- ----------
NAME
--------------------------------------------------------------------------------
  1     1
/u01/app/oracle/oradata/orcl12c/system01.dbf

  1     3
/u01/app/oracle/oradata/orcl12c/sysaux01.dbf

  1     7
/u01/app/oracle/oradata/orcl12c/users01.dbf

  1    15
/u01/app/oracle/oradata/orcl12c/undotbs2.dbf

  2     5
/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf

  2     6
/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf

  2     8
/u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

  3     9
/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf

  3    10
/u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf

  3    11
/u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf

  3    12
/u01/app/oracle/oradata/orcl12c/orcl/users01.dbf

  3    13
/u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf

  3    14
/u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf

  4    16
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di

  4    17
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2

  4    18
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1

  4    19
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

  4    20
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-HRDATA_FNO-20_0ds7a8e5

  5    21
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_system_dp72
3vp5_.dbf

  5    22
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_sysaux_dp72
3vsz_.dbf

  5    23
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_undotbs1_dp
723vt1_.dbf

  5    24
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_mydata_dp72
3vt3_.dbf


22 rows selected.

SQL> 
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
ORCL
READ WRITE

  4
NEWPDB
READ WRITE

  5
NONHR
MOUNTED


SQL> 
SQL> alter pluggable database nonhr open;

Pluggable database altered.

SQL>


I can identify the new PDB "NONHR" as CON_ID=5.
This is a Subset Clone that is a Copy with Data.

Let's create the TNSNAMES.ORA entry for NONHR:

NONHR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = nonhr)
    )
  )


Let's now connect to NONHR and confirm its contents.

SQL> connect system/oracle@NONHR
Connected.
SQL> show con_id

CON_ID
------------------------------
5
SQL> show con_name

CON_NAME
------------------------------
NONHR

SQL> select tablespace_name   
  2  from dba_tablespaces
  3  order by 1 
  4  /

TABLESPACE_NAME
------------------------------
HRDATA
MYDATA
SYSAUX
SYSTEM
TEMP
UNDOTBS1

6 rows selected.

SQL> select file_name from dba_data_files
  2  where tablespace_name = 'HRDATA'
  3  /

no rows selected

SQL> select owner, segment_name, segment_type
  2  from dba_segments
  3  where tablespace_name = 'HRDATA'
  4  /

no rows selected

SQL> 
SQL> select tablespace_name, file_name
  2  from dba_data_files
  3  order by 1
  4  /

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
MYDATA
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_mydata_dp72
3vt3_.dbf

SYSAUX
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_sysaux_dp72
3vsz_.dbf

SYSTEM
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_system_dp72
3vp5_.dbf

UNDOTBS1
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_undotbs1_dp
723vt1_.dbf


SQL> 
SQL> select segment_name, segment_type
  2  from dba_segments
  3  where owner = 'HR'
  4  /

no rows selected

SQL> select username      
  2  from dba_users
  3  where username = 'HR'
  4  /

USERNAME
--------------------------------------------------------------------------------
HR

SQL> 
SQL> select object_name, object_type
  2  from dba_objects
  3  where owner = 'HR'
  4  /

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
EMPLOYEES
TABLE


SQL>
SQL> select owner, segment_name
  2  from dba_segments
  3  where tablespace_name = 'MYDATA'
  4  /

OWNER  SEGMENT_NAME
-------- ------------------------------
HEMANT  BIN$UVb24iaCIE/gUwEAAH/WaQ==$0
HEMANT  BIN$UVb24iaIIE/gUwEAAH/WaQ==$0
HEMANT  HKC_STORE_FILE
HEMANT  I
HEMANT  OBJ_LIST
HEMANT  SYS_IL0000073525C00003$$
HEMANT  SYS_IL0000073532C00003$$
HEMANT  SYS_IL0000073535C00003$$
HEMANT  SYS_LOB0000073525C00003$$
HEMANT  SYS_LOB0000073532C00003$$
HEMANT  SYS_LOB0000073535C00003$$
HEMANT  T

12 rows selected.

SQL> 
SQL> select count(*) from hemant.obj_list;

  COUNT(*)
----------
    145282

SQL>


So, what has been copied to the NONHR PDB?  The HRDATA Tablespace, but not the DataFile.  The HR User and Table (definition only, no data), but not the Segment.
However, for the MYDATA Tablespace that was identified as a USER_TABLESPACE in the CREATE PLUGGABLE DATABASE statement, the Tablespace, Datafile, User, Table and Segment have all been copied.

Therefore, NONHR does not have the HR data!   I can drop the User and Tablespace.

SQL> drop tablespace hrdata including contents;

Tablespace dropped.

SQL> drop user hr;

User dropped.

SQL>


However, HR is still present in NEWPDB where NONHR was cloned from:

SQL> connect system/oracle@NEWPDB
Connected.
SQL> select owner, segment_name
  2  from dba_segments
  3  where tablespace_name = 'HRDATA'
  4  /

OWNER  SEGMENT_NAME
-------- ------------------------------
HR  EMPLOYEES

SQL> select * from hr.employees;

EMPLOYEE_ID FIRST_NAME      LAST_NAME
----------- ------------------------------ ------------------------------
HIRE_DATE DEPARTMENT_ID     SALARY EMAIL_ID
--------- ------------- ---------- ---------------------------------------------
   1 Hemant      Chitale
06-JUL-17       1      15000 hemant@mydomain.com


SQL> show con_id

CON_ID
------------------------------
4
SQL> show con_name

CON_NAME
------------------------------
NEWPDB
SQL> 
SQL> select tablespace_name, file_name
  2  from dba_data_files
  3  order by 1
  4  /

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
HRDATA
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-HRDATA_FNO-20_0ds7a8e5

MYDATA
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

SYSAUX
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2

SYSTEM
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di

UNDOTBS1
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1


SQL>


So, 12.2 introduces the ability to create a clone PDB database that is a SubSet (i.e. selected User Tablespaces data) of an existing PDB.

(Note : NEWPDB is in /u03 where it was moved from /u02 earlier as a Relocated Database while NONHR is in /u02 where it was created with OMF based on DB_CREATE_FILE_DEST).
.
.
.

1 comment:

08 July, 2017

New Video on 11g Nested Loop (enhancement)

I've published a new Video on the Optimizer Nested Loop Enhancement in 11g :  11g Nested Loop

.
.
.
No comments:

02 July, 2017

12c MultiTenant Posts -- 5 : Flashback a PDB

12.2 allows FLASHBACK DATABASE for a Pluggable Database.

Note that ALTER DATABASE FLASHBACK ON is not enabled by default in a newly created database -- you must issue this command at the CDB level to enable Flashback for all the Pluggable Databases.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
OBJ_LIST
HKC_STORE_FILE
T

SQL> select count(*) from obj_list;

  COUNT(*)
----------
    145282

SQL> select  count(*) from hkc_store_file;

  COUNT(*)
----------
  1

SQL> truncate table obj_list;

Table truncated.

SQL> drop table hkc_store_file;

Table dropped.

SQL>


Now, let me Flashback the PDB.

SQL> connect / as sysdba
Connected.
SQL> alter pluggable database newpdb close;

Pluggable database altered.

SQL> flashback pluggable database newpdb to timestamp sysdate-3/1440;

Flashback complete.

SQL> alter pluggable database newpdb open;
alter pluggable database newpdb open
*
ERROR at line 1:
ORA-01113: file 19 needs media recovery
ORA-01110: data file 19:
'/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4'


SQL> alter pluggable database newpdb open resetlogs;

Pluggable database altered.

SQL>


Let me test the data.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select count(*) from obj_list;

  COUNT(*)
----------
    145282

SQL> select count(*) from hkc_store_file;

  COUNT(*)
----------
  1

SQL>


Yes, the FLASHBACK DATABASE is successful.

What are the pre-requisites ?

1.  ALTER DATABASE FLASHBACK ON  at the CDB
2.  ARCHIVELOG at the CDB
3.  LOCAL UNDO enabled -- highly recommended else a subsequent Point In Time Recovery of the CDB may prevent OPENing the PDB
4.  OPEN RESETLOGS for the PDB

.
.
.

No comments:
Subscribe to: Posts (Atom)