Tag Archives: oracle 12c

Oracle 12.1.0.2c Cluster: New Location / Name For Alert Log

12c RAC, Uncategorized, ,

In my last post, I had presumed there is a bug since I discovered an empty clusterware alert log in its conventional location i.e.  $ORACLE_HOME/log/<hostname>in 12.1.0.2 standard cluster.

[grid@host01 ~]$ crsctl query crs activeversion;
Oracle Clusterware active version on the cluster is [12.1.0.2.0]

[root@host01 host01]# ls -l /u01/app/12.1.0/grid/log/host01/alerthost01.log

-rw-rw-r– 1 grid oinstall 0 Jun 15 14:10 /u01/app/12.1.0/grid/log/host01/alerthost01.log

But as commented by Ricardo Portillo Proni,  in oracle clusterware 12c,  the location of alert log has been changed to $ORACLE_BASE/diag/crs/<hostname>/crs/trace/

Hence, I could successfully  the alert log on node host01 in directory  $ORACLE_BASE/diag/crs/host01/crs/trace/

[grid@host01 trace]$ ls -l $ORACLE_BASE/diag/crs/host01/crs/trace/alert*

-rw-rw—- 1 root oinstall 812316 Aug 11 10:22 /u01/app/grid/diag/crs/host01/crs/trace/alert.log

Another noticeable thing is that name of clusterware alert log has been changed to alert.log as compared to alert<hostname>.log in 11g.

I would like to mention that I have verified the above only in 12.1.0.2 standard cluster.

In 12.1.0.1 flex cluster though, the location and name of  alert log location is same as in 11g i.e. $ORACLE_HOME/log/host01

[root@host01 host01]# crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [12.1.0.1.0]

[root@host01 host01]# ls -l $ORACLE_HOME/log/host01/alert*
-rw-rw-r-- 1 grid oinstall 497364 Aug 11 11:00 /u01/app/12.1.0/grid/log/host01/alerthost01.log

Conclusion:
12.1.0.2 standard cluster

  • Name of alert log : alert.log
  • location of alert log: $ORACLE_BASE/diag/crs/host01/crs/trace

12.1.0.1 flex cluster

  • Name of alert log : alert<hostname>.log
  • location of alert log: $ORACLE_HOME/log/host01

Hope it helps!

Pls refer to comments for further information.

References:
Oracle RAC 12c (12.1.0.2) Operational Best Practices (Oracle Presentation)
Oracle 12c RAC: Clusterware logs are now centralized

———————————————————————————————————————–

Related Links :

Home

12c RAC Index

Oracle 12.1.0.2 Standard Cluster: Empty Alert Log

12c: IMPROVE BACKUP PERFORMANCE USING IN-DATABASE ARCHIVING

Database 12c, Uncategorized,

Traditionally, the historical data needed for query purposes only  was removed from the database  and stored on tape, because

  •  Tape can store vast quantities of information for a very low cost.
  •  Huge amounts of old data will not be backed up again along with database backups.

This solution has major disadvantages such as:

  • The data is not instantly available when needed.
  •  Reloading the data into the database from the tape is expensive and time-consuming.

In-Database Archiving  stores operational and archival data in the same database so that  the data is still online and available and the cost is only slightly higher than storing this information on tape.

– Data is partitioned to distinguish active data from those in archived state.
– The partitions containing historical data
. are compressed and located on low cost storage tiers.
. are identified as read-only so that subsequent backups are not required after the initial database backup.

Demonstration:

– Create two tablespaces

  • active_tbs – to store active rows
  • archived_tbs – to store archived rows
SQL> create tablespace active_tbs;

create tablespace archived_tbs;

– Create table to store employees’ details

  • Partition key is column ORA_ARCHIVE_STATE
  • partition p_active – stores active rows (ora_archive_state = 0) in active_tbs tablespace in uncompressed format
  • partition p_archived – stores archived rows (ora_archive_state <> 0 ) in archived_tbs tablespace in compressed format
SQL>create table emp
(id    number,
name  varchar2(30),
retired  char(1)) row archival
partition by list(ora_archive_state)
 (partition p_active values(0)  tablespace active_tbs,
  partition p_archived values (default) tablespace   archived_tbs    compress);

– Populate table –

SQL>begin
     for i in 1..5 loop
       insert into emp values (i, 'EMP'||i, 'N');
     end loop;
     for i in 6..10 loop
        insert into emp values (i, 'EMP'||i, 'Y');
     end loop;
     end;
     /

– Check that all the records are currently located in active_tbs as by default ora_archive_state=0

SQL>  col name for a10
      col retired for a7
      col ora_archive_state for a15
      select id, name, retired, ora_archive_state
      from emp partition (p_active);

ID NAME       RETIRED ORA_ARCHIVE_STA
———- ———- ——- —————
1 EMP1       N       0
2 EMP2       N       0
3 EMP3       N       0
4 EMP4       N       0
5 EMP5       N       0
6 EMP6       Y       0
7 EMP7       Y       0
8 EMP8       Y       0
9 EMP9       Y       0
10 EMP10      Y       0

10 rows selected.

– Try to archive the records of retired employees (retired = Y)
— We get error message as ora_archive_state is the partitining key and changing its value would cause rows to move from p_active to p_archived

SQL> update emp set ora_archive_state = 1 where retired='Y';
update emp set ora_archive_state = 1 where retired='Y'
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

– Enable row movement and then archive the records of retired employees (retired = Y)

SQL> alter table emp enable row movement;

     update emp set ora_archive_state = 1 
     where retired='Y';

5 rows updated.

– Check that partition p_active contains records of employees which have not retired (retired = ‘N’)

SQL> select id, name, retired, ora_archive_state from emp partition (p_active);

ID NAME       RETIRED ORA_ARCHIVE_STA
---------- ---------- ------- ---------------
1 EMP1       N       0
2 EMP2       N       0
3 EMP3       N       0
4 EMP4       N       0
5 EMP5       N       0

– Check if records of employees which have retired (retired = ‘Y’)have moved to partition p_archived
— No records are displayed because by default only active rows are displayed

SQL> select id, name, retired, ora_archive_state from emp partition (p_archived);

no rows selected

– Enable visibility of archived rows also

SQL> alter session set row archival visibility = all;

– Check that records of employees which have retired (retired = ‘Y’)have moved to partition p_archived 

SQL> select id, name, retired, ora_archive_state from emp partition (p_archived);

ID NAME       RETIRED ORA_ARCHIVE_STA
---------- ---------- ------- ---------------
6 EMP6       Y       1
7 EMP7       Y       1
8 EMP8       Y       1
9 EMP9       Y       1
10 EMP10      Y       1

– Since archived records will be accessed for read only, change the tablespace archived_tbs to read only

SQL> alter tablespace archived_tbs read only;

-- Try to modify data in archived records – disallows as the tablespace is read only

SQL> update emp set name = 'emp6' where id = 6;
update emp set name = 'emp6' where id = 6
*
ERROR at line 1:
ORA-00372: file 12 cannot be modified at this time
ORA-01110: data file 12:

'+DATA/CDB1/E7CFA41503C950F1E0438602A8C06FD7/DATAFILE/archived_tbs.279.827852189'

– Backup tablespace archived_tbs after it has been made read only

RMAN> connect target sys/oracle@pdb1

      backup tablespace archived_tbs;

-- Exclude archived_tbs from whole database backups

RMAN> connect target sys/oracle@cdb1

      configure exclude for tablespace pdb1:archived_tbs;

Tablespace PDB1:ARCHIVED_TBS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

I hope information in this post was useful.

Your comments and suggestions are always welcome!

—————————————————————————————–

Related Links:

Home

Database 12c Index

12c : In-Database Archiving
12c : Performance Issue with In-Database Archiving
12c: Solution To Performance Issue With In-database Archiving

 

———————————————————————————————

12c: DISPLAY CDB/PDB NAME IN SQL PROMPT

Database 12c, Uncategorized,

While playing with 12c, I had to repeatedly issue commands to identify the CDB/PDB I was currently in. So, I decided to modify my glogin.sql to set my SQL prompt to reflect my current CDB/PDB.

- Edit glogin.sql file

$vi $ORACLE_HOME/sqlplus/admin/glogin.sql

-- Add following lines to it

define gname=idle
column global_name new_value gname
set heading off
set termout off
col global_name noprint
select upper(sys_context ('userenv', 'con_name') || '@' || sys_context('userenv', 'db_name')) global_name from dual;
set sqlprompt '&gname> '
set heading on
set termout on

– Test that script is working as expected

– Connect to container database CDB1

SQL> conn sys/oracle@cdb1 as sysdba

- Verify that the prompt displays current container (CDB$ROOT) and container database (CDB1)

CDB$ROOT@CDB1> sho con_name
CDB$ROOT

CDB$ROOT@CDB1> sho parameter db_name
db_name                              string      cdb1

- Connect to pluggable database PDB1

CDB$ROOT@CDB1> conn sys/oracle@pdb1 as sysdba

- Verify that the prompt displays current container (PDB1) and container database (CDB1)

PDB1@CDB1> sho con_name
PDB1

PDB1@CDB1> sho parameter db_name
db_name                              string      cdb1

Note:  SQL prompt will not change and hence will not reflect current PDB name if Alter Session set container =…. is used to modify current container .

Happy Prompting!

——————————————————–

Related Links:

Home

Database 12c Index

——————————————————–

 

12c: PLUG IN 12c NON-CDB AS PDB

Database 12c, Uncategorized, , ,

In this post, I will demonstrate the conversion of a 12.1.0.1 non-CDB  to a PDB. We will plug in  12.1.0.1 non-cdb named ncdb12c into a CDB called CDB2

Current scenario:

Source 12.1.0.1 non-cdb  : ncdb12c
Target CDB                               : CDB2
Target PDB                              : ncdb12c

To get data from a Non-CDB to a PDB you have to do a convert of a Non-CDB to PDB. The conversion process is similar to unplug and plug of a pdb.

Overview:

– Log into ncdb12c as sys
– Get the database in a consistent state by shutting it down cleanly.
– Open the database in read only mode
– Run DBMS_PDB.DESCRIBE to create an XML file  describing the database.
– Shut down ncdb12c
– Connect to target CDB (CDB2)
– Check whether non-cdb (NCDB12c) can be plugged into CDB(CDB2)
– Plug-in Non-CDB (NCDB12c) as PDB(NCDB12c) into target CDB(CDB2).
– Access the PDB and run the noncdb_to_pdb.sql script.
– Open the new PDB in read/write mode.

Implementation:

- Log into ncdb12c as sys and check that it is a non CDB

NCDB12C>sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      ncdb12c

NCDB12C>select name, cdb from v$database;

NAME      CDB
--------- ---
NCDB12C   NO

NCDB12C>select instance_name from v$instance;

INSTANCE_NAME
----------------
NCDB12C

-- Find out names of datafiles for NCDB12c (needed for FILE_NAME_CONVERT)

NCDB12C>select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ncdb12c/system01.dbf
/u01/app/oracle/oradata/ncdb12c/sysaux01.dbf
/u01/app/oracle/oradata/ncdb12c/undotbs01.dbf
/u01/app/oracle/oradata/ncdb12c/users01.dbf

To convert it to be a PDB we first need to get the database in a consistent state and then run DBMS_PDB.DESCRIBE to create an XML file to describe the database.

- Shutdown the database cleanly and open it in read only mode

SQL> shutdown immediate;
startup mount;
alter database open read only;

exec dbms_pdb.describe (PDB_DESCR_FILE=>'/u01/app/oracle/oradata/ncdb12c/ncdb12c.xml');

ho ls -l /u01/app/oracle/oradata/ncdb12c/ncdb12c.xml

-rw-r--r-- 1 oracle asmadmin 3994 Jul 24 16:15 /u01/app/oracle/oradata/ncdb12c/ncdb12c.xml

- Shutdown the source non-CDB (NCDB12c)

SQL> shutdown immediate;
exit

Now we can plug NONCDB into a existing CDB database cdb2

– connect to target CDB (CDB2)

SQL>conn sys/oracle@cdb2 as sysdba

select name, CDB from v$database;

NAME       CDB
---------- ---
CDB2       YES

– Check whether non-cdb (NCDB12c) can be plugged into CDB(CDB2)

SQL>
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/u01/app/oracle/oradata/ncdb12c/ncdb12c.xml',
pdb_name => 'NCDB12C')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

NO

- If the scripts returns NO, then check PDB_PLUG_IN_VIOLATIONS view

SQL> col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NCDB12C';

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
NCDB12C    Non-CDB to WARNING   PDB plugged in is a non-CDB,        PENDING
PDB                 requires noncdb_to_pdb.sql be run.

NCDB12C    Parameter  WARNING   CDB parameter sga_target mismatch:  PENDING
Previous 394264576 Current
1895825408

NCDB12C    Parameter  WARNING   CDB parameter pga_aggregate_target  PENDING
mismatch: Previous 131072000
Current 629145600

There are WARNING’s only.  We can continue.
– Make the target directory for datafiles and create pluggable database 

SQL> ho mkdir -p /u02/app/oracle/oradata/cdb2/ncdb12c

–  Plug-in Non-CDB (NCDB12c) as PDB (NCDB12c):

I am plugging the database in to a CDB on the same server with COPY clause and hence using  FILE_NAME_CONVERT.

SQL>  CREATE PLUGGABLE DATABASE ncdb12c USING '/u01/app/oracle/oradata/ncdb12c/ncdb12c.xml'
COPY
file_name_convert=('/u01/app/oracle/oradata/ncdb12c','/u02/app/oracle/oradata/cdb2/ncdb12c');

Pluggable database created.

– check that datafiles for the plugagble database ncdb12c have been created in the location specified in FILE_NAME_CONVERT

SQL> ho ls /u02/app/oracle/oradata/cdb2/ncdb12c/*
/u02/app/oracle/oradata/cdb2/ncdb12c/sysaux01.dbf
/u02/app/oracle/oradata/cdb2/ncdb12c/system01.dbf
/u02/app/oracle/oradata/cdb2/ncdb12c/temp01.dbf
/u02/app/oracle/oradata/cdb2/ncdb12c/users01.dbf

– check that newly created PDB NCDB12c is in mounted state

SQL>  select name, open_mode from v$pdbs;

NAME       OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
PDB1       MOUNTED
NCDB12C    MOUNTED

– check that status of newly created PDB NCDB12c is NEW

SQL> col pdb_name for a15
select pdb_name, status from dba_pdbs where pdb_name = 'NCDB12C';

PDB_NAME        STATUS
--------------- -------------
NCDB12C         NEW

–Run noncdb_to_pdb.sql script, complete the following steps:

- Access the newly creatd PDB.

SQL> alter session set container=ncdb12c;

sho con_name

CON_NAME
------------------------------
NCDB12C

- Run the noncdb_to_pdb.sql script:

NCDB12c>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

– connect to CDB and verify that warnings for sga_target and pga_aggregate_target mismatch have been resolved

SQL>conn sys/oracle@cdb2 as sysdba

col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NCDB12C';

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
NCDB12C    Parameter  WARNING   CDB parameter sga_target mismatch:  RESOLVED
Previous 394264576 Current
1895825408

NCDB12C    Parameter  WARNING   CDB parameter pga_aggregate_target  RESOLVED
mismatch: Previous 131072000
Current 629145600

NCDB12C    Non-CDB to ERROR     PDB plugged in is a non-CDB,        PENDING
PDB                 requires noncdb_to_pdb.sql be run.

You must open the new PDB in read/write mode for Oracle Database to complete the integration of the new PDB into the CDB. An error is returned if you attempt to open the PDB in read-only mode. After the PDB is opened in read/write mode, its status is NORMAL.

– Open the new PDB in read/write mode.

SQL>shu immediate;
alter pluggable database ncdb12c open;

– check that the status of NCDB12c changes to OPEN now

SQL>select pdb_name, status from dba_pdbs;

PDB_NAME        STATUS
--------------- -------------
PDB1            NORMAL
PDB$SEED        NORMAL
NCDB12C         NORMAL

– verify that warning for requires noncdb_to_pdb.sql be run has also been resolved

SQL> col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NCDB12C';SQL> SQL> SQL>

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
NCDB12C    Parameter  WARNING   CDB parameter sga_target mismatch:  RESOLVED
Previous 394264576 Current
1895825408

NCDB12C    Parameter  WARNING   CDB parameter pga_aggregate_target  RESOLVED
mismatch: Previous 131072000
Current 629145600

NCDB12C    Non-CDB to ERROR     PDB plugged in is a non-CDB,        RESOLVED
PDB                 requires noncdb_to_pdb.sql be run.
Note:

If an error is returned during PDB creation, then the PDB being created might be in an UNUSABLE state. You can check a PDB’s state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped, and it must be dropped before a PDB with the same name as the unusable PDB can be created.

References:

Oracle documentation

————————————————————————————————

Related Links:

Home

Oracle 12c Index

Clone An Existing PDB As Non-SYS User

 

——————————————————————————————————————

12c: CONNECTING TO PDB’S WITH SAME NAME

Database 12c, Uncategorized, ,

When you create a PDB, the database automatically creates and starts a service inside the CDB.The service has the  same name as the PDB. It is possible that the name of the service will collide with an existing service name which is registered with the same listener. For example if two or more CDBs on the same computer system use the same  listener, and the newly established PDB has the same service name as another PDB in these CDBs, then a collision occurs.

You must not attempt to operate a PDB that causes a collision with an existing service name. To avoid incorrect connections, two methods are possible:

– configure a separate listener for each CDB on the computer system or

– ensure that all service names for PDBs are unique on the computer system .

Firstly, I will demonstrate that a connection that specifies the default service name of a PDB can connect randomly to any of the PDBs with the same service name.  Then I will demonstrate both the methods to  avoid incorrect connections.

Current scenario:

I have two CDB’s (CDB1 and CDB2) on the same computer system.
Pluggable database PDB1 exists in both the CDB’s  CDB1 and CDB2

There are two listeners running in database home
listener1 on port 1523
listener2 on port 1524

Overview:

— Register both the CDB’s (and hence PDB’s) with listener1 running on port 1523.
— Verify that if we repeatedly  connect to service PDB1, we are randomly connected to different pdb’s   (PDB1@CDB1 and PDB1@CDB2).
— Register PDB1@CDB2 with listener2 on port 1524 and verify that now we can connect to the right pdb

— Create service spdb11 for PDB1@CDB1 and spdb12 for PDB1@CDB2 and verify that now we can connect to the right pdb using respective service.

Implementation:

Method – I :  Configure a separate listener for each CDB on the computer system or

– Register both the CDB’s with listener1 running on port 1523

CDB1>alter system set local_listener='em12c.oracle.com:1523';

sho parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      em12c.oracle.com:1523

CDB2>alter system set local_listener='em12c.oracle.com:1523';

sho parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      em12c.oracle.com:1523

– check that both the CDB’s and PDB1 in both the CDB’s are registered with listener1 (port 1523)

[oracle@em12c ~]$ lsnrctl stat listener1
(output trimmed)
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/em12c/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb2" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 2 instance(s).
 Instance "cdb1", status READY, has 1 handler(s) for this service...
  Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

It can be seen that  PDB PDB1 has same name in both the CDB’s (CDB1 and CDB2) and default service for both the PDB’s (PDB1@CDB1 and PDB1@CDB2) are registered with the listener  on the same port (1523).

– Verify that if we repeatedly  connect to service PDB1, we are randomly connected to different pdb’s   (PDB1@CDB1 and PDB1@CDB2)

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb2

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb1

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb2

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb1

Hence, to connect to the right PDB, we should register different PDB’s with listeners running on different ports.

– Let’s register PDB1@CDB2 with listener2 on port 1524

CDB2>alter system set local_listener='em12c.oracle.com:1524';

sho parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      em12c.oracle.com:1524

– check that  CDB1  and pdb1@CDB1  are registered with listener1 (port 1523)

[oracle@em12c ~]$ lsnrctl stat listener1

(output trimmed)

Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/em12c/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

– check that  CDB2  and PDB1@CDB2  are registered with listener2 (port 1524)

[oracle@em12c ~]$ lsnrctl stat listener2

(output trimmed)

Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/em12c/listener2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1524)))
Services Summary...
Service "cdb2" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
 Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

– Verify that now we can connect to the right pdb

– connect to PDB1@CDB1 (listener1, port 1523)

SQL> conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb1

SQL> conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb1

-- connect to PDB1@CDB2 (listener2, port 1524)

SQL> conn system/oracle@em12c:1524/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb2

SQL> conn system/oracle@em12c:1524/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb2

Hence,  to avoid  incorrect connections, one method is that we should configure a separate listener for each CDB on a computer system.

But this  approach is not easily transferable  to an Oracle RAC environment and hence it woule be better to use the second method i.e.  using services to make a PDB accessible in general, especially in RAC.

Method-II :Using Services to access a specific PDB

- Create and start service spdb1 for PDB1@CDB1

CDB1>alter session set container=pdb1;

PDB1@CDB1> exec dbms_service.create_service(‘spdb1′, ‘spdb1′);

exec dbms_service.start_service(‘spdb1′);

select name, network_name from dba_services;

NAME            NETWORK_NAME
————— —————
pdb1            pdb1
spdb1           spdb1

– Add the following entry to tnsnames.ora

NPDB11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = em12c)(PORT = 1523))
)
(CONNECT_DATA =
(SERVICE_NAME = spdb1)
)
)

- Create and start service spdb12 for PDB1@CDB2

CDB2>alter session set container=pdb1;

PDB1@CDB2>exec dbms_service.create_service (‘spdb12′, ‘spdb12′);

exec dbms_service.start_service (‘spdb12′);

select name, network_name from dba_services;
NAME            NETWORK_NAME
————— —————
pdb1                pdb1
spdb12           spdb12

– Add the following entry to tnsnames.ora

NPDB12 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = em12c)(PORT = 1523))
)
(CONNECT_DATA =
(SERVICE_NAME = spdb12)
)
)

– Test connecting to specific PDB using services

– Connect to PDB1@CDB1

PDB1@CDB2>conn system/oracle@npdb11
sho con_name

CON_NAME
——————————
PDB1
PDB1@CDB2>sho parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      cdb1

– Connect to PDB1@CDB2
PDB1@CDB2>conn system/oracle@npdb12
sho con_name

CON_NAME
——————————
PDB1
PDB1@CDB2>sho parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      cdb2

References:

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6009.htm
http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdblogic.htm#CIHDEDCC
————————————————————————————-

Related Links:

Home

Oracle 12c Index

12c : Connecting to CDB/PDB – Set Container vs Connect
12c: USE_SID_AS_SERVICE_listener_name