Category Archives: Uncategorized

CDB Fleet in Oracle Database 18c

Oracle database 18c  introduces a new CDB Fleet feature  which allows many CDBs to be managed as one. A CDB fleet is a collection of CDBs and hosted PDBs that you can monitor and manage as one logical CDB from a centralized location.

There are two possible roles within a CDB Fleet:

  • Lead CDB: Only one CDB in the Fleet may be designated as the Lead CDB. The lead CDBis the central location for monitoring and managing all the CDBs in the fleet.
  • Member CDB: The CDBs registered with a lead CDB are called member CDBs. There can be one or more member CDB’s in a CDB fleet.

For every  member CDB,  proxy PDBs for the member CDB and its PDB(s) are automatically created in the lead CDB,.  Consequently, all the member CDBs  and their PDBs are now “visible” in the lead CDB . This enables management and monitoring of the entire estate of PDBs in the fleet, physically distributed across various CDBs, from the lead CDB.

Advantages

Reporting, monitoring, and management of the entire CDB fleet through a single interface:

  • Provides massive scalability of the underlying infrastructure
  • Reduces capital and operational costs
  • Provides greater efficiencies to the business.

Related Links:

‘STUB’ Status In DBA_PDBS

References:

https://docs.oracle.com/cd/E96517_01/newft/database-new-features-guide.pdf

https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/administering-cdb-fleet.html#GUID-5951E81B-4351-4FA4-9F7B-52D2FEB0428D

‘STUB’ STATUS in DBA_PDBS

While exploring CDB  fleet in Oracle database 18c, I configured a CDB fleet having LCDB as the lead CDB and MCDB1 as a member CDB which hosts a PDB MPDB11.  As a result, proxy PDBs were automatically created for MCDB1 and MPDB11 in the lead CDB LCDB. When I queried STATUS column of DBA_PDBS  from root of LCDB, I encountered the value ‘STUB’ in  STATUS column in DBA_PDBS  as shown below:

SYS_LCDB>SELECT pdb_id, pdb_name, status, is_proxy_pdb "IS_PROXY_PDB" FROM dba_pdbs;
PDB_ID PDB_NAME STATUS IS_PROXY_PDB
---------- ---------- ------ ------------
3 LPDB1 NORMAL NO
2 PDB$SEED NORMAL NO
4 mcdb1 STUB YES
5 MPDB11 STUB YES 

As per oracle documentation (https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/DBA_PDBS.html#GUID-439126EA-A6B6-45B8-AAFA-37EE4356BBEF), the value ‘STUB’ is not listed. However, I came across an oracle tutorial showing how to configure a CDB fleet which mentions:

The STUB value in the STATUS column defines the CDB and its PDBs as members in the fleet.

On further exploring, I learnt that the proxy PDBs created for member CDBs/PDBs are not real proxies as   data files for SYSTEM, SYSAUX and UNDO tablespaces are not created locally in the lead CDB.  Consequently,  it is not possible to switch to or open the proxy PDBs in the lead CDB. Hence, they have a status of ‘STUB’ in status column of DBA_PDBS in the lead CDB.

Summary:

In a CDB fleet,

  • Proxy PDBs are automatically created for member CDBs and their PDBs in the lead CDB .
  • Proxy PDBs created for member CDBs/PDBs are not real proxies as   data files for SYSTEM, SYSAUX and UNDO tablespaces are not created locally in the lead CDB.
  • It is not possible to switch to or open the proxy PDBs in the lead CDB. Hence, they have a status of ‘STUB’ in status column of DBA_PDBS in the lead CDB.

Related Links:

CDB Fleet In Oracle Database 18c

References:

https://docs.oracle.com/en/database/oracle/oracle-database/18/qlaas/

pdb_to_apppdb.sql returns ORA-65021- A workaround

While exploring Oracle Multitenant Application Containers, I learnt that in order to convert a regular PDB  to an application PDB

  • Clone a regular PDB  into an application root
  • Connect to the cloned  PDB and execute the $ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql to convert the cloned regular PDB  to an application PDB

However, when I connected to cloned PDB remotely using @… and executed the script pdb_to_apppdb.sql, I got ORA-65021 :

SQL>@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb

 

 

.

.

.

.

 

SQL>create or replace view sys.cdb$common_root_objects&pdbid sharing=object as

2  select u.name owner, o.name object_name, o.type# object_type, o.namespace nsp,

3         o.subname object_subname, o.signature object_sig,

4         decode(bitand(o.flags, &sharing_bits),

5                &edl+&mdl, 'EDL', &dl, 'DL', 'MDL') sharing

6    from sys.obj$ o, sys.user$ u

7   where o.owner#=u.user# and bitand(o.flags, &sharing_bits) <> 0

8     and bitand(o.flags,&fedobjflag)=&fedobjflag;

old   1: create or replace view sys.cdb$common_root_objects&pdbid sharing=object as

new   1: create or replace view sys.cdb$common_root_objects4 sharing=object as

old   4:        decode(bitand(o.flags, &sharing_bits),

new   4:        decode(bitand(o.flags, (65536+131072+4294967296)),

old   5:               &edl+&mdl, 'EDL', &dl, 'DL', 'MDL') sharing

new   5:               4294967296+65536, 'EDL', 131072, 'DL', 'MDL') sharing

old   7:  where o.owner#=u.user# and bitand(o.flags, &sharing_bits) <> 0

new   7:  where o.owner#=u.user# and bitand(o.flags, (65536+131072+4294967296)) <> 0

old   8:    and bitand(o.flags,&fedobjflag)=&fedobjflag

new   8:    and bitand(o.flags,134217728)=134217728

create or replace view sys.cdb$common_root_objects4 sharing=object as

*

ERROR at line 1:

ORA-65021: illegal use of SHARING clause

After various repeated trials, I realized that connecting to cloned PDB using “Alter session set container ..“ results in successful execution.

Here is the full article where I encountered this error and found out the workaround.

I faced similar issue while creating application seed from application root and resolved it by connecting to the  application seed  created from application root using Alter session set container …

Conclusion:  In order to execute the script $ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql to convert the cloned regular PDB  to an application PDB, connect to the target regular PDB by switching the container. (Do not connect remotely using @)

References:

I Am Speaking at OTN Yathra 2017

The Oracle ACE directors and Oracle Volunteers  in the region are organizing their third evangelist event called ‘OTNYathra 2017’ during the month of June 2017 in a time period of 3 weeks. This yathra, managed by ACE directors and Oracle Volunteers of the region, is to travel across the country covering over six major IT focused cities (Chennai, Bangalore, Hyderabad,  Pune, Mumbai and Gurgaon) . It aims  to bring the Oracle community together, giving them awareness and improve the level of knowledge and increase the networking opportunities in the region.

I will be speaking at this year’s OTNYathra  about “RAC server Pools: What’s new in Oracle 12c?“.

Oracle Clusterware 11g release 2 (11.2) laid the foundation of policy based cluster management by introducing  server pools as a means for specifying resource placement and administering server allocation and access. However, originally, server pools were restricted to a set of basic attributes characterizing servers as belonging to a given pool. There was no way to distinguish between types of servers; all servers were considered to be equal in relation to their processors, physical memory, and other characteristics. This can lead to  sub-optimal performance of some applications if the servers  assigned to the server pools hosting those applications do not meet the applications’ requirements.

Oracle Grid Infrastructure 12c enhances the use of server pools by introducing server attributes e.g. memory, CPU_count etc. which can be associated with each server. Server pools can be configured so that their members belong to a category of servers, which share a particular set of attributes. Moreover, the administrators can maintain a library of policies and switch between them as required rather than manually reallocating servers to various server pools based on workload.

My presentation discusses in detail the new features of RAC server pools in 12c.

My session will be held on Sunday 25th June, 2017   from 1.30 pm to 2.30 pm in
Room 1, Fidelity International, Unitech Cyber Park, Tower D, Sector 39, Durga Colony, Sector 39, Gurugram, Haryana 122001, India
Hope to meet you there!!

Oracle Database 12.2.0.1 – Application PDB unable to sync – An update

In my last post, I had demonstrated that without OMF, If an  application creates new datafiles, application PDB’s always fails to sync with the  application.  When I mentioned it to Tim, he raised an SR with Oracle who have clarified that it is  Bug 21933632. Although  it is not documented yet but OMF is mandatory if using the Application Container feature. If it is attempted to sync application PDB’s  with an  application that creates   non-OMF datafile(s),   sync  will run into  problem while trying to replay a create tablespace or similar statement with a hard-coded file name.”

References:

Multitenant: Application Containers in Oracle Database 12c Release 2

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

Related Links:

Home

Oracle 12c Index

Oracle Database 12.2.0.1 – Application PDB unable to sync

Oracle Database 12.2.0.1 – Application PDB unable to sync

Recently, Tim Hall posted an excellent article about Application containers, a new feature in Oracle Database 12c Release 2 (12.2.0.1). With application containers, it is now possible to create a single master application definition in the application root of the application container. The common objects defined in the master application definition are visible to all application PDBs within the application container when the application PDBs have been synchronized with the application root.

Tim has demonstrated the concept of applilcation containers using Oracle Managed Files (OMF) . I tried out the same example without OMF and discovered that without OMF it works only if the application doesn’t use any new tablespaces/datafiles. If the application creates new datafiles, application PDB’s always fails to sync with the  application.

Here is my example:

– Create a new application root container appcon1 (non-OMF – Using FILE_NAME_CONVERT)

CDB$ROOT>conn / as sysdba

         CREATE PLUGGABLE DATABASE appcon1 AS APPLICATION CONTAINER
         ADMIN USER app_admin IDENTIFIED BY Password1
         FILE_NAME_CONVERT = ('/pdbseed/', '/appcon1/');

– Connect to the application root container and create an application PDB apppdb1 from CDB seed

CDB$ROOT>ALTER PLUGGABLE DATABASE appcon1 OPEN;
         ALTER SESSION SET container = appcon1;

APPCON1>CREATE PLUGGABLE DATABASE apppdb1
        ADMIN USER pdb_admin IDENTIFIED BY Password1
        FILE_NAME_CONVERT = ('/pdbseed/', '/appcon1/apppdb1/');

– Open the application PDB apppdb1

APPCON1>ALTER PLUGGABLE DATABASE apppdb1 open;
        SHOW PDBS

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 APPCON1 READ WRITE NO
5 APPPDB1 READ WRITE NO

-- Connected to the application root container, create a simple application ref_app

APPCON1>ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN INSTALL '1.0';

– Create application components

. Create a tablespace ref_app_ts

APPCON1>CREATE TABLESPACE ref_app_ts
        DATAFILE '/u01/app/oracle/oradata/orclcdb/appcon1/app_tbs1.dbf'
        SIZE 1M AUTOEXTEND ON NEXT 1M;

.  Create a common application user ref_app_user with default tablespace ref_app_ts created above

APPCON1>CREATE USER ref_app_user IDENTIFIED BY ref_app_user
        default tablespace ref_app_ts
        quota unlimited on ref_app_ts
        CONTAINER=ALL;

– Grant appropriate privileges to common appication user ref_app_user

APPCON1>GRANT CREATE SESSION, CREATE TABLE
-- , unlimited tablespace
TO ref_app_user;

– Create and populate a table reference_data owned by ref_app_user whose data will be shared across all the application PDB’s synced with the application ref_app

APPCON1>CREATE TABLE ref_app_user.reference_data SHARING=DATA (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT t1_pk PRIMARY KEY (id)
);

APPCON1>INSERT INTO ref_app_user.reference_data
SELECT level, 'Description of ' || level
FROM dual
CONNECT by level <= 5;
COMMIT;

– After all the application components have been defined, end the installation process.

APPCON1>ALTER PLUGGABLE DATABASE APPLICATION ref_app END INSTALL '1.0';

-- Verify that application ref_app version 1.0 has been created in application root appcon1 as listed in the DBA_APPLICATIONS view.

APPCON1>COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10

SELECT app_name,
app_version,
app_status
FROM dba_applications
WHERE app_name = 'REF_APP';

APP_NAME APP_VERSIO APP_STATUS
-------------------- ---------- ---------------
REF_APP 1.0 NORMAL

– Connect to application PDB apppdb1 and verify it cannot access common application table ref_app_user.reference_data as it has not been synced with the application ref_app yet

APPCON1>CONN / AS SYSDBA

ALTER SESSION SET container = apppdb1;
APPPDB1>DESC ref_app_user.reference_data;

ERROR:
ORA-04043: object ref_app_user.reference_data does not exist

– When an attempt is made to sync the application PDB apppdb1 with the application ref_app, ORA-01537 is encountered.

APPPDB1>ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;

ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC
*
ERROR at line 1:
ORA-01537: cannot add file
'/u01/app/oracle/oradata/orclcdb/appcon1/app_tbs1.dbf' - file already part of database

However, above command succeeds if tablespace ref_app_ts not created and ref_app_user give unlimited tablespace privilege.

Please go through following update on above issue.

References:
Multitenant: Application Containers in Oracle Database 12c Release 2

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

Related Links:

Home

Oracle 12c Index

Oracle Database 12.2.0.1 – Application PDB unable to sync – An update