Category Archives: Database 12c

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

In Oracle 12c, you can connect to a PDB using two methods :

– Switch the container using Alter system set container …

– Use connect command to connect to PDB using network alias

Let’s compare the two methods :

The use of SET CONTAINER avoids the need to create a new connection from scratch.

If there is an existing connection to a PDB / CDB$root, the same connection can be used to connect to desired PDB / CDB$root.

– Connect to CDB

[oracle@em12 ~]$ sqlplus system/oracle@cdb1

CDB$ROOT@CDB1> sho con_name

CON_NAME
------------------------------
CDB$ROOT

– Check the PID for the process created on the operating system

[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep

oracle 23271 1 0 10:23 ? 00:00:00 oraclecdb1 (LOCAL=NO)

– Change the container to PDB1 using Set container

CDB$ROOT@CDB1> alter session set container=pdb1;

sho con_name

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

– Check that the operating system PID remains the same as earlier connection is reused and a new connection has not been created

[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep

oracle 23271 1 0 10:23 ? 00:00:00 oraclecdb1 (LOCAL=NO)

– Switch the container back to cdb$root using connect

CDB$ROOT@CDB1> conn system/oracle@cdb1
sho con_name

CON_NAME
------------------------------
CDB$ROOT

– Check that a new operating system PID has been created as a new  connection  has been created

[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep

oracle 23409 1 0 10:29 ? 00:00:00 oraclecdb1 (LOCAL=NO)

glogin.sql is not executed when Alter session set container is used

To demonstrate it, I have added following lines to my glogin.sql to display CDB/PDB name in SQL prompt:

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

- Let’s connect to PDB1 using “Connect” and verify that glogin.sql is executed and prompt displays CDB/PDB name

 

SQL> conn sys/oracle@pdb1 as sysdba
PDB1@CDB1> 

- 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

– Now let’s connect to PDB2 using Alter session set container and verify that glogin.sql is not executed and the same prompt as earlier is displayed

PDB1@CDB1>  alter session set container=pdb2;

 Session altered.
PDB1@CDB1> sho con_name
CON_NAME

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

 PDB2  
-- Let's connect to PDB2 using connect and verify that glogin.sql is executed as the prompt displays the PDB name PDB2

 PDB1@CDB1> connect sys/oracle@pdb2 as sysdba

 PDB2@CDB1> 

Pending transactions are not committed when Alter system set container is used

– Let’s start a transaction in PDB1

PDB1@CDB1> create table pdb1_tab(x number);
Table created.
PDB1@CDB1> insert into pdb1_tab values (1);
1 row created.

– Switch the container to PDB2

PDB1@CDB1> alter session set container=pdb2;

– Try to start another transaction on PDB2 – does not allow as an active transaction exists in the parent container PDB1

PDB1@CDB1> create table pdb2_tab (x number);

 create table pdb2_tab (x number)

 *

 ERROR at line 1:

 ORA-65023: active transaction exists in container PDB1

– In another session check that the transaction was not committed and no rows are visible in table pdb1_tab

CDB$ROOT@CDB1> conn system/oracle@pdb1

 PDB1@CDB1> select * from pdb1_tab;
no rows selected

 Alter session set container cannot be used by local users

 

– Try to give set container privilege to a local user HR in PDB2 – fails as common privilege cannot be granted to a local user and hence a local user cannot user alter session set container to connect to another PDB

PDB2@CDB1> connect system/oracle@pdb2

 PDB2@CDB1> grant set container to hr container=all;

 grant set container to hr container=all

 *

 ERROR at line 1:

 ORA-65030: one may not grant a Common Privilege to a Local User or Role

I hope this post was useful.

Your comments and suggestions are always welcome.

References :

http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm#ADMIN13970

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

Related Links:

Home

Database 12c Index

12c : Connecting To PDB’s With Same Name
12c: USE_SID_AS_SERVICE_listener_name

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

 

12c : Transportable Database

Oracle 12c introduces full transportable export/import,  an  exciting new feature that greatly simplifies the process of database migration. It  employs

  •  Oracle Data Pump export  to extract  all of the system, user, and application metadata needed for a database migration
  •   transportable tablespaces mechanism to move user and application data i.e. datafiles containing user and application data are physically copied to the target. This results in a migration that is very fast, even for very large volumes of data.

Thus, you can upgrade or migrate to Oracle Database 12c easily and quickly with a single import command. This requires that  your source database is at least Oracle Database 11g Release 2 (11.2.0.3).

This post focuses on the use of full transportable export/import  to migrate an 11.2.0.3 database to a pluggable database , although this feature can be used for migrations to a Oracle Database 12c non-CDB database as well. 

The steps for migrating from an 11.2.0.3 non-CDB  into a pluggable database are as follows:

  • Set the user and application tablespaces in the source database to be READ ONLY
  •  Export from the source database using expdp with the FULL=Y, VERSION=12.0 and TRANSPORTABLE=ALWAYS
  • Copy the  dump file and data files for tablespaces containing user/application data to the destination
  • Create a new PDB in the destination CDB using the create pluggable database command
  • Create a directory object in the destination PDB pointing to the folder containing dumpfile
  • Create an entry for the destination PDB in tnsnames.ora
  •   Using an account that has the DATAPUMP_IMP_FULL_DATABASE privilege, import into the target database using impdp with the FULL=Y and TRANSPORT_DATAFILES parameters
  • Restore the user tablespaces in source database to read/write mode

Current scenario:
Source
version : 11.2.0.3
Database
: orcl
Platform 32 bit RHEL 5.4
Tablespaces : system, sysaux, users, example, undotbs1, temp
Target  
container database : cdb1
pluggable database : pdb_orcl
version : 12.1.0.1
Platform 64 bit OEL 5.4
Tablespaces : system, sysaux

Objective :

– Upgrade orcl (11.2.0.3 32 bit linux) to pdb_orcl(12.1.0.1 64 bit linux) using expdp transportable database
- While import rename users tablespace to users_orcl in target db

– Implementation –

— Source Database (ORCL) —

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

– Issue export command 
. TRANSPORTABLE=ALWAYS and FULL=Y : These parameter values tell Data Pump to use full transportable rather than conventional export methods.
.  VERSION=12   : Since source database is  11g Release 2 (11.2.0.3), we  must also specify the Data Pump parameter VERSION=12 to denote the fact that the result of the export will be imported into an Oracle Database 12c Release 1 (12.1) or later database. This is the only time that a version number greater than the current version is allowed by the expdp command. If the source database is Oracle Database 12c, with COMPATIBLE=12.0 or higher, then the VERSION parameter is not required.

Note that command fails as user tablespaces have not been put in read only mode.

[oracle@node1 bin]$ expdp system/oracle full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log
Export: Release 11.2.0.3.0 - Production on Mon Mar 24 16:53:13 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y version=12.0 transportable=always
Estimate in progress using BLOCKS method...
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'EXAMPLE' is not read only
 ORA-29335: tablespace 'USERS' is not read only
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 16:53:20

– Set the user and application tablespaces in the source database to be READ ONLY

SQL> alter tablespace example read only;

              alter tablespace users read only;

– Issue expdp command again
After the export command completes, the export log file shows a list of all of the tablespace data files that need to be moved to the target.

[oracle@node1 bin]$ expdp system/oracle full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log

Export: Release 11.2.0.3.0 - Production on Tue Mar 25 09:38:23 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log
Estimate in progress using BLOCKS method...

...

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/u01/app/oracle/admin/orcl/dpdump/exporcl.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
 /u01/app/oracle/oradata/orcl/example01.dbf
 Datafiles required for transportable tablespace USERS:
 /u01/app/oracle/oradata/orcl/users01.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 09:51:17

— TARGET CONTAINER DATABASE (CDB1) –

– Find out names of  the datafiles of   seed  database in cdb1

SQL> select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/users01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf

– create pluggable database pdb_orcl

SQL> create pluggable database pdb_orcl admin user pdbadmin identified by oracle file_name_convert=('pdbseed','pdb_orcl');

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB_ORCL MOUNTED

-- check that pdb_orcl has  datafiles for system/sysaux tablespaces only

SQL> alter pluggable database pdb_orcl open;

     select file_name from cdb_data_files where con_id=4;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb_orcl/system01.dbf
 /u01/app/oracle/oradata/cdb1/pdb_orcl/sysaux01.dbf

– Add  tnsnames.ora entry for pdb_orcl

PDB_ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = em12.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb_orcl)
)
)

– Copy the  export dump file and the data files for example and users tablespaces   from source to target

— copy example01.dbf and users01.dbf belonging to orcl to folder /u01/app/oracle/oradata/cdb1/pdb_orcl  on target machine

— copy exporcl.dmp containing  metadata  to the folder /u01/app/oracle/admin/cdb1 on target machine

– Create a directory object on the destination PDB (PDB_ORCL)  pointing to location of exporcl.dmp i.e. /u01/app/oracle/admin/cdb1

SQL> alter session set container=pdb_orcl;

sho con_name

CON_NAME
------------------------------
PDB_ORCL

SQL> create directory dump_dir as '/u01/app/oracle/admin/cdb1';

– Invoke full transportable import on the destination PDB

Specify following parameters :
- the dumpfile name
– full = Y and
– TRANSPORT_DATAFILES : to secify  the list of user tablespace data files to be transported

[oracle@em12 admin]$ impdp system/oracle@pdb_orcl full=y directory=dump_dir dumpfile=exporcl.dmp transport_datafiles='/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf', '/u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf' remap_tablespace=users:users_orcl logfile=import_orcl.log

Import: Release 12.1.0.1.0 - Production on Tue Mar 25 11:41:44 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Source timezone version is +00:00 and target timezone version is -07:00.
Starting "SYSTEM"."SYS_IMPORT_FULL_02": system/********@pdb_orcl full=y directory=dump_dir dumpfile=exporcl.dmp transport_datafiles=/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf, /u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf remap_tablespace=users:users_orcl logfile=import_orcl.log

....
Job "SYSTEM"."SYS_IMPORT_FULL_02" completed with 3395 error(s) at Tue Mar 25 12:07:27 2014 elapsed 0 00:25:40

– Check that target PDB’s metadata is updated with datafiles for example and users tablespaces  

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdb_orcl/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb_orcl/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf
 /u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf

–  Check that the application tablespaces are automatically placed in read/write mode on the destination database and users tablespace has been renamed to users_orcl

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
EXAMPLE ONLINE
USERS_ORCL ONLINE

 — Check that user HR has been created on the target PDB also 

SQL> conn hr/hr@pdb_orcl
    select tname from tab;

TNAME
--------------------------------------------------------------------------------
COUNTRIES
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
EMP_DETAILS_VIEW

– Restore user tablespaces to read-write mode on the source database

SQL> alter tablespace USERS read write;

alter tablespace example read write;
              select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE

References: 

http://docs.oracle.com/cd/E16655_01/server.121/e17636/transport.htm#ADMIN13727
Full Transportable Export / Import – Things to know
Full Transportable Export / Import – Migrating an 11.2.0.4 Database to Oracle Database 12c – Into the Cloud

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

Related Links:

Home

Database 12c

12c: Transport Database Over Network

 

 

 

 

 

ORA-65023: active transaction exists in container CDB$ROOT

I received this error message in the following scenario:

– Connect to CDB

SQL> conn / as sysdba
sho con_name

CON_NAME
------------------------------
CDB$ROOT

– Start a transaction

SQL> create table test(c number);
     insert into test values(1) ;

– Change the container to PDB without completing the transaction

SQL> alter session set container=pdb2;

– Try to shutdown the pluggable database

– It causes ORA-65023 because  on switching the container (not   making a connection to  PDB using connect), pending transactions in the original container are still in a pending state

SQL> shu immediate;
ORA-65023: active transaction exists in container CDB$ROOT

That’s what oracle documentation says for this error:

Cause

A statement attempted to create a new transaction in the current container while there was an active transaction in another container.

Action

Switch to the container with the active transaction and commit, rollback or detach the active transaction before attempting to issue any statement that will attempt to create a new transaction in another container.

– Switch back to original container (cdb$root) and commit the transaction

SQL> alter session set container=cdb$root;
commit;

– Switch the container to PDB and close the PDB

— The attempt succeeds as there is no pending transaction in the parent container.

SQL> alter session set container=pdb2;
shu immediate;
Pluggable Database closed.

SQL> sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB2                           MOUNTED

References:

Oracle Documentation

http://www.oracle-base.com/articles/12c/multitenant-runing-scripts-cdb-and-pdb-12cr1.php

 

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

Related Links:

Home

Database 12c

 

 

12c : DDL log does not identify the source PDB

In 12c, the location of DDL logs has changed from alert log to DDL log.

Oracle Database 11g

If instance parameter ENABLE_DDL_LOGGING = TRUE

DDL statements are logged into the alert log.

Oracle Database 12c

If instance parameter ENABLE_DDL_LOGGING = TRUE

DDL statement logging has been moved from alert log to DDL log
i.e. $ADR_HOME/log/ddl/log.xml
This file  contains only the DDL statements and dates.
– Let’s demonstrate

–Check that by default DDL logging is disabled

SQL> sho parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE

– Enable DDL logging

SQL> alter system set enable_ddl_logging=true;

sho parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     TRUE

– Remove all the trace files including alert log

[oracle@em12 ~]$ cd /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/

rm -rf *

ls

– check that there is no DDL log file in /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl

[oracle@em12 ddl]$ cd /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl
ls

– Issue a DDL while connected to CDB1

SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT

SQL>  create table test(x number);

– check that alert log has not been created as DDL’s are no longer logged to alert log

[oracle@em12 log]$ ls /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/

– check that ddl log file log.xml has been created in $ORACLE_BASE/diag/rdbms/cdb1/cdb1/log/ddl($ADR_HOME/log/ddl)

[oracle@em12 ddl]$ ls /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl/

log.xml

– check that the contents of the file give info about the DDL that was issued
Note that name of the database issuing the DDL (CDB$ROOT) is not there in the log

[oracle@em12 ddl]$ cat log.xml

<msg time='2014-03-05T13:08:33.207+05:30' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'
version='1'>
<txt>create table test(x number)
</txt>
</msg>

– Issue another DDL from PDB1

SQL> alter session set container=pdb1;
create table test(x number);

– check that the contents of the file give info about this DDL also
   Again note that name of the database issuing the DDL(PDB1) is not there in the log

[oracle@em12 ddl]$ cat log.xml

<msg time='2014-03-05T13:08:33.207+05:30' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'
version='1'>
<txt>create table test(x number)
</txt>
</msg>
<msg time='2014-03-05T13:09:11.104+05:30' org_id='oracle' comp_id='rdbms'
msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'>
<txt> create table test(x number)
</txt>
</msg>

It can be seen that it is not possible to identify  from this log whether the  DDL was issued from CDB$ROOT or which PDB (in case CDB has multiple PDB’s)

In 11g or 12c non-CDB, this issue is not there since the alert log belongs to one database only.

References:

Oracle Documentation

Oracle 12c: DDL Logging : Will it serve the purpose?

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

Related Links:

Home

Database 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

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

UNABLE TO ACCESS EM EXPRESS FOR NON-CDB

I recently faced the issue that I could not access EM express for a non CDB .

- I found out the https port no. for EM express using following query:

select dbms_xdb_config.gethttpsport from dual;

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

- Tried to access  EM express by entering following URL in the browser:

https://<hostname>:5500/em

- Got the following error message:

Unable to connect

Firefox can’t establish a connection to the server at em12.example.com:5501.

- I checked if my database was registered with default listener

[oracle@em12 bin]$ lsnrctl stat

Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12.example.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service em12rep” has 1 instance(s).
Instance “em12rep”, status READY, has 1 handler(s) for this service…
Service “em12repXDB” has 1 instance(s).
Instance “em12rep”, status READY, has 1 handler(s) for this service…
The command completed successfully

– The output clearly shows that my database orcl is not registered with the default listener running on port 1521.

- I checked the parameter local_listener for my database

SQL> sho parameter local_listener

NAME                                 TYPE        VALUE
———————————— ———– ——————————
local_listener                       string      LISTENER_ORCL

– Checked the entry for LISTENER_ORCL in $ORACLE_HOME/network/admin/tnsnames.ora

– It showed a listener named LISTENER_ORCL  at port 1522

[oracle@em12 bin]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = em12.example.com)(PORT = 1522))

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = em12.example.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

– Created a listener LISTENER_ORCL in $OARCLE_HOME at port 1522 by adding following lines to listener.ora

[oracle@em12 bin]$ vi $ORACLE_HOME/network/admin/listener.ora

LISTENER_ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = em12.example.com)(PORT = 1522))
  )

– Started listener LISTENER_ORCL

[oracle@em12 bin]$ lsnrctl stat listener_orcl

– Registered database with LISTENER_ORCL

SQL> alter system register;

– Checked that database has been successfully registered with LISTENER_ORCL

[oracle@em12 bin]$ lsnrctl services listener_orcl

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=em12.example.com)(PORT=1522)))
Services Summary…
Service “orcl” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “orclXDB” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
Handler(s):
“D000″ established:8 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: em12.example.com, pid: 7014>
(ADDRESS=(PROTOCOL=tcp)(HOST=em12.example.com)(PORT=59034))
The command completed successfully

- Tried to access  EM express by entering following URL in the browser:

https://<hostname>:5500/em

I could successfully access database login page.

Conclusion:

If you want to access EM-express for a database registered with non-default listener (say LISTENER1),
– Create an entry for listener (LISTENER1) in tnnames.ora
– Set parameter LOCAL_LISTENER to listener (LISTENER1)

 

I hope this post was helpful.

Your comments and suggstions are always welcome.

References:
http://www.oracle.com/technetwork/database/manageability/emx-intro-1965965.html#A5
http://www.oracle.com/technetwork/database/manageability/emx-cdb-1965987.html

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

Related Links:

Home

Database 12c Index

12c: Access EM Express for CDB / PDB / Non-CDB

Error : NMO not setuid-root (Unix Only)

 

ERROR: NMO not setuid-root (Unix-only)

I got this error when I was trying to specify Host Credentials in EM 12c cloud control.

Cause: root.sh had not been executed from agent home

Solution: I executed root.sh from agent home and the problem was resolved.

[root@em12 dbhome_1]# /u01/app/oracle/product/agent/core/12.1.0.2.0/root.sh

I hope this information was useful.

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

Related Links:

Home

Database 12c Index

12c : Access EM Express for CDB / PDB / Non-CDB
12c: Unable To Access EM Express For Non-CDB