Search My Oracle Blog

Custom Search

05 August, 2017

12c MultiTenant Posts -- 7 : Adding Custom Service to PDB (nonRAC/GI)

Earlier I have already demonstrated adding and managing custom services in a RAC environment in a blog post and a video.

But what if you are running Single Instance and not using Grid Infrastructure?  The srvctl command in Grid Infrastructure is what you'd use to add and manage services in RAC and Oracle Restart environments.  But without Grid Infrastructure, you can fall back on DBMS_SERVICE.

The DBMS_SERVICE API has been available since Oracle 8i -- when Services were introduced.

Here is a quick demo of some facilities with DBMS_SERVICE.

1.  Adding a Custom Service into a PDB :

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 22:52:21 2017

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

Last Successful login time: Mon Jul 10 2017 22:22:30 +08:00

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

SQL> show con_id  

CON_ID
------------------------------
4
SQL> 
SQL> execute dbms_service.create_service('HR','HR');

PL/SQL procedure successfully completed.

SQL> execute dbms_service.start_service('HR');

PL/SQL procedure successfully completed.

SQL> 


Connecting to the service via tnsnames.

SQL> connect hemant/hemant@HR
Connected.
SQL> show con_id

CON_ID
------------------------------
4
SQL> 


2.  Disconnecting all connected users on the Service

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 23:02:47 2017

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

Last Successful login time: Sat Aug 05 2017 23:02:28 +08:00

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

SQL> 
SQL> execute dbms_service.disconnect_session(-
> service_name=>'HR',disconnect_option=>DBMS_SERVICE.IMMEDIATE);

PL/SQL procedure successfully completed.

SQL> 
In the HEMANT session connected to HR :
SQL> show con_id
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 5062
Session ID: 67 Serial number: 12744


SP2-1545: This feature requires Database availability.
SQL> 


(Instead of DBMS_SERVICE.IMMEDIATE, we could also specify DBMS_SERVICE.POST_TRANSACTION).


3.  Shutting down a Service without closing the PDB :

SQL> execute dbms_service.stop_service('HR');

PL/SQL procedure successfully completed.

SQL> 
SQL> connect hemant/hemant@HR
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL> 


Does restarting the Database, restart this custom service?

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size      8798312 bytes
Variable Size    343936920 bytes
Database Buffers   478150656 bytes
Redo Buffers      7974912 bytes
Database mounted.
Database opened.
SQL> alter pluggable databas all open;
alter pluggable databas all open
                *
ERROR at line 1:
ORA-02000: missing DATABASE keyword


SQL> alter pluggable database all open;

Pluggable database altered.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> connect hemant/hemant@HR
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL> 
SQL> connect system/oracle@NEWPDB
Connected.
SQL> execute dbms_service.start_service('HR');

PL/SQL procedure successfully completed.

SQL> connect hemant/hemant@HR
Connected.
SQL> 


I had to reSTART this custom service ('HR') after the PDB was OPENed.

Services is a facility that has been available since 8i non-OPS.  However, Services were apparently only being used by most sites in RAC environments.

Services allow you to run multiple "applications" (each application advertised as a Service) within the same (one) database.

Note that, in a RAC environment, srvctl configuration of Services can configure auto-restart of the Service.


UPDATE : A Video using a different PDB (in the same CDB) and ServiceName is posted here.
.
.
.

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

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com