Tag Archives: 12c

12C : SQL MANAGEMENT BASE STORES PLAN ROWS TOO

The SQL management base (SMB) resides in the SYSAUX tablespace and stores SQL plan baselines, statement logs, plan histories, and SQL profiles.

  • In Oracle Database 11g, when DBMS_XPLAN.DISPLAY_PLAN_BASELINE is invoked to view a plan in SMB, the  execution plan is displayed by compiling the statement with the baseline, mainly using outlines. The plan rows are not stored in the SMB and hence the actual execution plan may not be reproduced.
  •  In Oracle Database12c, when a new plan is added to the plan history of a SQL statement, plan rows are also stored in the SMB . when DBMS_XPLAN.DISPLAY_PLAN_BASELINE is invoked to view a plan in SMB, the plan rows are displayed from the SMB. This facilitates easier diagnosability when plan cannot be reproduced.

Let’s demonstrate :

Overview:

11g Database

— Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id
— Issue a select statement which accesses hr.emp and load the execution plan into SMB
— verify that the plan stored in SMB is same as that was used when the statement was executed (Index access)
— Drop the primary key constraint and hence the index which was used during execution
— verify that the plan  that was used when the statement was executed cannot be reproduced as the index has been dropped

12c Database

— Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id
— Issue a select statement which accesses hr.emp and load the execution plan into SMB
— verify that the plan stored in SMB is same as that was used when the statement was executed (Index access)
— Drop the primary key constraint and hence the index which was used during execution
— verify that the plan  that was used when the statement was executed (index access) has been  reproduced even though the index has been dropped as the plan rows are stored in SMB

Implementation :

11g Database

– Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id

SQL>set line 500
    set pagesize 80

    drop table hr.emp purge;
    create table hr.emp as 
           select * from      hr.employees;
    alter table hr.emp 
    add constraint emp_pk primary key (employee_id);

 — Issue a select statement which accesses hr.emp using index

SQL>select employee_id, last_name
from hr.emp
where employee_id = 101;

– check that the statement is executed using the index

SQL>select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  2t38scsy1hct2, child number 0
-------------------------------------
select employee_id, last_name from hr.emp where employee_id = 101

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|         |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    27 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|         |
--------------------------------------------------------------------------------------

– Load the execution plan into SMB

SQL> DECLARE
l_plans_loaded  PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id=>'2t38scsy1hct2');
END;
/

– Check that the plan has been loaded into SMB

SQL> col SQL_HANDLE for a25
col plan_name for a30
select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
from dba_sql_plan_baselines
WHERE sql_text like '%select employee_id, last_name%';

SQL_HANDLE                PLAN_NAME                      ENA ACC FIX
------------------------- ------------------------------ --- --- ---
SYS_SQL_90c5335bf23b296d  SQL_PLAN_91j9mbgt3qabd447b331a YES YES NO

– verify that the plan stored in SMB is same as that was used when the statement was executed

SQL> set line 999
set pagesize 999

select *
from table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));

SQL handle: SYS_SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    27 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    27 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

– Drop the primary key constraint and hence the index which was used during execution

SQL>alter table hr.emp drop primary key;

-- verify that the plan  that was used when the statement was executed cannot be reproduced as the index has been dropped

SQL>select *
from  table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |   108 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     4 |   108 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

12c Database

– Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id

SQL>set line 500
    set pagesize 80

    drop table hr.emp purge;
    create table hr.emp as 
           select * from      hr.employees;
    alter table hr.emp 
    add constraint emp_pk primary key (employee_id);

 — Issue a select statement which accesses hr.emp using index

SQL>select employee_id, last_name
from hr.emp
where employee_id = 101;

– check that the statement is executed using the index

SQL>select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  2t38scsy1hct2, child number 0
-------------------------------------
select employee_id, last_name from hr.emp where employee_id = 101

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

– Load the execution plan into SMB

SQL> DECLARE
l_plans_loaded  PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id=>'2t38scsy1hct2');
END;
/

– Check that the plan has been loaded into SMB

SQL>col SQL_HANDLE for a25
col plan_name for a30
 select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
 from dba_sql_plan_baselines
 WHERE sql_text like '%select employee_id, last_name%';

SQL_HANDLE                PLAN_NAME                      ENA ACC FIX
 ------------------------- ------------------------------ --- --- ---
 SQL_90c5335bf23b296d      SQL_PLAN_91j9mbgt3qabd447b331a YES YES NO

– verify that the plan stored in SMB is same as that was used when the statement was executed

SQL> set line 999
set pagesize 999

select *
from table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL handle: SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

– Drop the primary key constraint and hence the index which was used during execution

SQL>alter table hr.emp drop primary key;

-- verify that the plan  that was used when the statement was executed has been  reproduced even thoughthe index has been dropped as the plan rows are stored in SMB

SQL>select *
from table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));

PLAN_TABLE_OUTPUT
----------------------------------
--------------
SQL handle: SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

I hope this post was useful.

Your comments and suggestions are always welcome!

—————————————————————-

Related Links:

Home

Database Index
Database 12c Index
Parameters Deprecated  in 12c
Undocumented Parameters in 12c

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

12c: DISPLAY CDB/PDB NAME IN SQL PROMPT

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

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: ACCESS EM EXPRESS FOR CDB / PDB / Non-CDB

Let’s see how to access EM express for various types of databases.

Oracle 12c has introduced multitenant architecture in which we can have different types of databases:

– CDB’s
– PDB’s
– Non CDB’s

Let’s see how to access EM express for various types of databases:

Accessing EM express for CDB’s

At the root container, data is displayed for the entire CDB, and all actions taken impact all containers.  Data pertaining to a PDB, such as a user or a SQL statement, is qualified with the container name.  The actions a user can perform at the root container include:

– viewing and setting initialization parameters;
– administering common users and privileges; and
– managing memory configuration, undo information and redo logs.

- Find out the port on which EM Express is configured:

Three methods are available:

1. When dbca completes, note down the  the port on which EM Express is configured as displayed.

2.Find out status of the listener

[oracle@em12c /]$ lsnrctl status
(output trimmed)
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=em12c.oracle.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))

3. Login to CDB as issue the  sql query as follows:

SQL> conn sys/oracle@cdb1 as sysdba
select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
5500

- Access  EM express by entering following URL in the browser:

https://<hostname>:5500/em

 

Accessing EM express for PDB’s

When a user is connected to a PDB, data is displayed only for the container in question, and actions only impact that container.

- Login to PDB

SQL> alter session set container=pdb1;

sho con_name

CON_NAME
------------------------------
PDB1

- Configure the port (5501) for EM express as follows:

SQL> exec dbms_xdb_config.sethttpsport(5501);

- Confirm that EM has been configured on specified port (1501)

SQL> select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
5501

- Access  EM express by entering following URL in the browser:

https://<hostname>:5501/em

Accessing EM express for Non-CDB’s

For a non- CDB, and all actions a user can perform in a non-CDB are available:

– viewing and setting initialization parameters;
– administering common users and privileges;
– managing memory configuration, undo information and redo logs.
– managing tablespaces etc.

- Find out the port on which EM Express is configured:

Two methods are available:

1. When dbca completes, note down the  the port on which EM Express is configured as displayed.

2.  Login to non-CDB (Note container name as Non consolidated)

SQL> conn sys/oracle@ncdb as sysdba

sho con_name

CON_NAME
------------------------------
Non Consolidated

- Issue the  sql query as follows:

select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
5502

- Access  EM express by entering following URL in the browser:

https://<hostname>:5502/em

Above method will work if non-CDB is registered with the default listener running on port 1521. In case non-CDB is registered with the non-default listener, please follow the method as given in this post.

References:
http://www.oracle.com/technetwork/database/manageability/emx-intro-1965965.html#A5
http://www.oracle.com/technetwork/database/manageability/emx-cdb-1965987.html
http://docs.oracle.com/database/121/ADMQS/em_manage.htm#ADMQS033
How to Configure EM Express For A Single PDB?

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

Related Links:

Home

Oracle 12c Index

12c: Unable To Access EM Express For Non-CDB
Error : NMO not setuid-root (Unix Only)

 

12c: CONNECTING TO PDB’S WITH SAME NAME

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