As per Oracle 12c documentation,  a PDB can

– have its own local temporary tablespace, or

– if it does not have its own  temporary tablespace, it can share the temporary tablespace with the CDB.

To demonstrate a PDB sharing the temporary tablespace of CDB,  the  first step is to create a PDB without a temporary tablespace. By default when a PDB is created from seed, it is created with its local temporary tablespace TEMP and it cannot be dropped as it is the default temporary tablespace of the PDB.

So, the only options I could think of were to

Method – I

• Create a non-CDB without temporary tablespace and the plug it into a CDB

Method – II

• Create a non-CDB / PDB with temporary tablespace,
• Generate its xml file using dbms_PDB
•  Edit the xml file to remove the entry for temporary tablespace
•   Plug in the non-CDB into a CDB

I will demonstrate the second method. For this purpose, I created a non-CDB orcl2 using DBCA so that it contained default temporary tablespace temp.

– Open the non-CDB in read only mode

ORCL2> shu immediate;
             startup mount;
             alter database open read only;

– Generate an XML metadata file for the non-CDB

ORCL2>exec dbms_pdb.describe ('/u01/app/oracle/oradata/orcl2/orcl2.xml');

– Edit the xml file to remove the entry for temp tablespace

[oracle@em12 ~]$ vi /u01/app/oracle/oradata/orcl2/orcl2.xml

– Use the xml file to plug in the non-CDB into  CDB2 as PDB_ORCL2

CDB$ROOT@CDB2>create pluggable database pdb_orcl2 using '/u01/app/oracle/oradata/orcl2/orcl2.xml'  nocopy;

– Connect to PDB_ORCL2 and run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean up the new PDB, removing any items that should not be present in a PDB.

CDB$ROOT@CDB2>connect sys/oracle@pdb_orcl2 as sysdba

PDB_ORCL2@CDB2> @?/rdbms/admin/noncdb_to_pdb

PDB_ORCL2@CDB2> alter pluggable database open;

– Check that data from non-CDB is available in the new PDB

PDB_ORCL2@CDB2>select count(empno) from scott.emp;

COUNT(EMPNO)
------------
14

– Verify that temporary tablespace has not been created in PDB_ORCL2

PDB_ORCL2@CDB2> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
EXAMPLE

– Verify that tempfile has not been created in PDB_ORCL2

PDB_ORCL2@CDB2> select name from v$tempfile;

no rows selected

So, I was able to create a PDB without temporary tablespace. Now I wanted to check if PDB_ORCL2 uses the temp tablespace of the CDB.

– First check that default temporary tablespace TEMP exists for the CDB

CDB$ROOT@CDB2> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

CDB$ROOT@CDB2>  select PROPERTY_NAME, PROPERTY_VALUE

from database_properties
where upper(PROPERTY_NAME) like '%TEMP%';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ---------------
DEFAULT_TEMP_TABLESPACE        TEMP

– Set pga_aggregate_target to its lowest allowed value of 10m to force a sort to spill to the temporary tablespace.

 CDB$ROOT@CDB2> alter system set pga_aggregate_target=10m;

– Issue a query in PDB_ORCL2 which will spill to temporary tablespace

PDB_ORCL2@CDB2> select * from dba_objects order by 1,2,3,4,5,6,7;
select * from dba_objects order by 1,2,3,4,5,6,7
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist

I expected the query to use temporary tablespace TEMP of CDB but it failed as it expected temp tablespace to exist locally for PDB as its default temporary tablespace is set to TEMP (as was in non-CDB)

PDB_ORCL2@CDB2> col property_name for a30
PDB_ORCL2@CDB2> col property_value for a15
PDB_ORCL2@CDB2> l
1  select PROPERTY_NAME, PROPERTY_VALUE from database_properties
2*  where upper(PROPERTY_NAME) like '%TEMP%'
PDB_ORCL2@CDB2> /

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ---------------
DEFAULT_TEMP_TABLESPACE        TEMP

 — I tried to modify the default temporary tablespace to cdb$root:temp but that attempt also failed.

PDB_ORCL2@CDB2> alter database default temporary tablespace cdb$root:temp;
alter database default temporary tablespace cdb$root:temp
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

Hence, I was able to create a PDB without temporary tablespace but could not make it use the temporary tablespace of CDB.

References:

Oracle documentation

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

Related Links:

Home

Database 12c Index

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

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

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

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

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

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)