Connecting to a Pluggable database using SID

 

To connect to a PDB, you have to use the service name of the pluggable database.  But what when there is an older legacy application that can only use an SID to connect to a database?  And what if we just migrated an older legacy database to a PDB? You might see an error like this.

 

C:\Users\179818>sqlplus /nolog

 

SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 17 15:16:35 2017

 

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

 

idle> conn demo/demo@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LT035221)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = PDB1)))

Connected.

demo@PDB1>

demo@PDB1> conn demo/demo@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LT035221)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SID = PDB1)))

ERROR:

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

 

 

Warning: You are no longer connected to ORACLE.

demo@PDB1>

 

 

How to overcome this error, while migrating to Oracle 12c pluggable database? There is a parameter called USE_SID_AS_SERVICE_listener that can be used to tell the listener to handle SID like service names. So the listener will interpret the SID given in the connection string as a service name and will then connect to the database.

 

 

To implement this change, add the following line the listener.ora configuration file and restart the listener.

 

 

USE_SID_AS_SERVICE_listener=on

 

 

 

Here is the content of the listener log after making the necessary changes.

 

 

C:\Users\Vnameit>type d:\app\Vnameit\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora

# listener.ora Network Configuration File: D:\app\Vnameit\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

 

USE_SID_AS_SERVICE_LISTENER_PDB1=on

 

SID_LIST_LISTENER_PDB1=

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PDB1)

      (ORACLE_HOME = D:\app\Vnameit\virtual\product\12.2.0\dbhome_1)

      (SID_NAME = PDB1)

    )

  )

 

LISTENER_PDB1=

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1527))

      (ADDRESS = (PROTOCOL = TCP)(HOST = LT035221)(PORT = 1521))

    )

  )

 

C:\Users\Vnameit>

 

 

Now, restarting the listener

 

 

LSNRCTL>

LSNRCTL> stop LISTENER_PDB1

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1527)))

The command completed successfully

LSNRCTL>

LSNRCTL> start LISTENER_PDB1

Starting tnslsnr: please wait...

 

TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production

System parameter file is D:\app\Vnameit\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora

Log messages written to D:\app\Vnameit\virtual\diag\tnslsnr\LT035221\listener_pdb1\alert\log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1527ipc)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LT035221.cts.com)(PORT=1521)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1527)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER_PDB1

Version                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production

Start Date                17-APR-2017 16:19:19

Uptime                    0 days 0 hr. 0 min. 4 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   D:\app\Vnameit\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora

Listener Log File         D:\app\Vnameit\virtual\diag\tnslsnr\LT035221\listener_pdb1\alert\log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1527ipc)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LT035221.cts.com)(PORT=1521)))

Services Summary...

Service "PDB1" has 1 instance(s).

  Instance "PDB1", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

LSNRCTL>

 

 

After listener restart able to connect to the pluggable database using both Service name and SID.

 

 

C:\Users\179818>sqlplus /nolog

 

SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 17 16:23:04 2017

 

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

 

idle> conn demo/demo@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LT035221)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = PDB1)))

Connected.

demo@PDB1>

demo@PDB1> conn demo/demo@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LT035221)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SID = PDB1)))

Connected.

demo@PDB1>

Online conversion of non-partitioned table to partitioned table

Converting a non-partitioned table to a partitioned can be a pain; to do this online one could use dbms_redefinition which has improved with latter releases.

 

However, with Oracle database 12.2 this is now an even easier, more flexible method of performing such a conversion.

 

Let’s create a table and couple of index on it.

 

demo@ORA12C> create table t

  2  as

  3  select *

  4  from all_objects;

 

Table created.

 

demo@ORA12C> create index t_idx_01 on t(object_name,created);

 

Index created.

 

demo@ORA12C> create index t_idx_02 on t(object_type,object_id);

 

Index created.

 

demo@ORA12C> alter table t

  2  add constraint t_pk

  3  primary key(object_id);

 

Table altered.

 

 

To convert a non-partitioned table to a partitioned table online, we can now use this new extension to the ALTER TABLE syntax.

 

 

demo@ORA12C> alter table t modify

  2  partition by range(created)

  3  ( partition p2015 values less than

  4     (to_date('01-Jan-2016','dd-mon-yyyy')) ,

  5    partition p2016 values less than

  6     (to_date('01-Jan-2017','dd-mon-yyyy')) ,

  7    partition p2017 values less than

  8     (to_date('01-Jan-2018','dd-mon-yyyy')) ) ONLINE;

 

Table altered.

 

 

We now have a table that is range partitioned based on the CREATED column and this conversion was performed online.

 

 

demo@ORA12C> select table_name,partitioned

  2  from user_tables

  3  where table_name ='T';

 

TABLE_NAME PAR

---------- ---

T          YES

 

demo@ORA12C> select table_name,partition_name

  2  from user_tab_partitions

  3  where table_name ='T';

 

TABLE_NAME PARTITION_

---------- ----------

T          P2015

T          P2016

T          P2017

 

demo@ORA12C> select table_name,partitioning_type,status

  2  from user_part_tables

  3  where table_name ='T';

 

TABLE_NAME PARTITION STATUS

---------- --------- --------

T          RANGE     VALID

 

 

It is not only the table is now partitioned, but all index remains valid. Any index having the partition key column, will implicitly converted to a LOCAL partitioned index.

 

 

demo@ORA12C> select index_name,partitioned,status

  2  from user_indexes

  3  where table_name ='T';

 

INDEX_NAME PAR STATUS

---------- --- --------

T_IDX_01   YES N/A

T_IDX_02   NO  VALID

T_PK       NO  VALID

 

demo@ORA12C> select index_name,partition_name,status

  2  from user_ind_partitions

  3  where index_name like 'T%';

 

INDEX_NAME PARTITION_ STATUS

---------- ---------- --------

T_IDX_01   P2015      USABLE

T_IDX_01   P2016      USABLE

T_IDX_01   P2017      USABLE

 

demo@ORA12C> select index_name,partitioning_type,locality

  2  from user_part_indexes

  3  where table_name ='T';

 

INDEX_NAME PARTITION LOCALI

---------- --------- ------

T_IDX_01   RANGE     LOCAL

 

demo@ORA12C>

 

 

 

As part of the table conversion syntax, we have the option to also update all the associated indexes and partition them in any manner we may want. For example:

 

demo@ORA12C> alter table t modify

  2  partition by range(created)

  3  ( partition p2015 values less than

  4     (to_date('01-Jan-2016','dd-mon-yyyy')) ,

  5    partition p2016 values less than

  6     (to_date('01-Jan-2017','dd-mon-yyyy')) ,

  7    partition p2017 values less than

  8     (to_date('01-Jan-2018','dd-mon-yyyy')) ) ONLINE

  9  update indexes(

 10     t_pk global partition by hash(object_id)

 11        ( partition hp1, partition hp2) );

 

Table altered.

 

demo@ORA12C>

 

 

In this example, not only we are converting the non-partitioned table to be partitioned, but we are also explicitly converting the primary key index into a global hash partitioned index.

 

If we look at the definition of these indexes, we see that they also have all been converted to partitioned indexes online along with the table.

 

 

demo@ORA12C> select index_name,partitioned,status

  2  from user_indexes

  3  where table_name ='T';

 

INDEX_NAME PAR STATUS

---------- --- --------

T_IDX_01   YES N/A

T_IDX_02   NO  VALID

T_PK       YES N/A

 

demo@ORA12C> select index_name,partition_name,status

  2  from user_ind_partitions

  3  where index_name like 'T%';

 

INDEX_NAME PARTITION_ STATUS

---------- ---------- --------

T_IDX_01   P2015      USABLE

T_IDX_01   P2016      USABLE

T_IDX_01   P2017      USABLE

T_PK       HP1        USABLE

T_PK       HP2        USABLE

 

demo@ORA12C> select index_name,partitioning_type,locality

  2  from user_part_indexes

  3  where table_name ='T';

 

INDEX_NAME PARTITION LOCALI

---------- --------- ------

T_IDX_01   RANGE     LOCAL

T_PK       HASH      GLOBAL

 

demo@ORA12C>

 

 

 

For those of you with the partitioning database options, the ability in 12.2 to now so easily convert a non-partitioned table to be partitioned, along with its associated indexes is just brilliant.