0345 222 96000345 222 9600

Written by Peter Homes, Xynomix Technical Consultant

 

This is the first article in a mini-series taken from our two day Container database (CDB) training course. In coming weeks, I will cover some of the key things you will need to be aware of when administering a Container database. However, this article covers the most fundamental question most DBAs ask when being confronted by the brave new world of administering a Container database, namely “How can I implement CDBs with the least amount of pain?”

 

There probably hasn’t been a less popular new Oracle database feature since Oracle brought in the Cost-Based Optimiser (CBO) back in the days of Oracle 7. The strategy back then was to be to deprecate the old, comfortable and well-understood technology, in favour of the new. We are now seeing this approach being reused for the introduction of CDBs: Oracle announced the deprecation of non-Container databases in Oracle 12.1 (https://docs.oracle.com/database/121/UPGRD/deprecated.htm#BABDBCJI), calling time on the conventional single-database instance.

 

When considering a strategy for adopting Container database technology, the first thing to be aware of is licensing. When using Standard Edition, you should be aware that you are NEVER licensed to implement more than one user pluggable database (PDB) in any single instance. With Enterprise Edition, you aren’t licensed to implement more than one user PDB in any single instance UNLESS you purchase the Oracle Multitenant pack (see https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87). This means that, in the overwhelming majority of cases, it will only be permissible to create more than just a single user PDB within a Container database. Not that the technology will prevent you from creating additional PDBs (in 12.1 the Standard Edition of Oracle did prevent a second user PDB from being created, however I have been told that this is not the case any longer in 12.2+). Those who aren’t careful will end up with a significant bill from Oracle’s LMS audit team!

 

So, in summary, you are compelled to adopt Multitenant technology going forward, but will almost certainly be limited to creating a single user PDB within your new or upgraded database instance. What then is the best approach to take, to minimise the changes you, as a DBA, will need to make when adopting Multitenant technology? The below list is our recommendation of how best to set up a single user-PDB Container database.

 

  • Install Oracle 12.2 or above (i.e. don’t install 12.1)
  • Set the parameter MAX_PDBS to 1 (available from 12.2 onwards)
  • Set the parameter NONCDB_COMPATIBLE to TRUE
  • Use Local UNDO tablespaces (available from 12.2 onwards)
  • Carry out all backups and restores at the root Container level

 

I must stress at this point that, if you are one of those organisations, that have paid for the additional Multitenant Pack, then you should stop reading here and disregard this article, the above 5 points are not for you. If however, you don’t have the Multitenant pack, then please read on while I explain the above 5 points in a little more detail.

 

Install Oracle 12.2 or above (i.e. don’t install 12.1)

 

I mentioned the introduction of the cost based optimiser (CBO) in Oracle 7.0 earlier. It is worth taking a lesson from history: The CBO was awful in Oracle 7.0, slightly better in Oracle 7.1, almost usable in Oracle 7.3 and just about safe to use in Oracle 8i. In short, it takes time for a new major technology to evolve to be useful. I expect it will be the same with the CDB feature. Oracle 12.2 is already a massive step forward from 12.1, with the introduction of local UNDO tablespaces. I have not seen Oracle 18 yet, however there are bound to be major improvements in that too. The lesson is therefore not to rush into adopting Container technology too soon. Oracle are bound to support non-Container databases for quite a while to come, so make use of that time to make the transition as painless as possible.

 

Set the parameter MAX_PDBS to 1

 

The MAX_PDBS parameter was introduced in Oracle 12.2 and, assuming that you haven’t purchased the Multitenant option (why are you still reading this article if you have?!), setting this parameter is the first thing you should do after creating your CDB database or converting your database to a CDB. Do it even if you are using Oracle Standard Edition! The reason? MAX_PDBS sets the limit of the number of user PDBs you are able to create. As Oracle is being good enough to give you the tools to adhere to their Multitenant licencing, there will be no excuses, I imagine, for breaching it.

 

In the below example I have a Container database, which already contains a PDB called PDBORCL. By default MAX_PDBS is set to 4098. I’m not sure why Oracle don’t set the default to 1, but there you go! Setting MAX_PDBS is very straightforward as you can see and does a good job in protecting against licence infringements.

 

SQL> alter system set max_pdbs=1 scope=both;

NAME         TYPE     VALUE

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

max_pdbs     integer  4098

 

SQL> create pluggable database PDB2 admin user PDB2admin

identified by myadminpwd;

create pluggable database PDB2 admin user PDB2admin identified by myadminpwd;

ERROR at line 1:

ORA-65010: maximum number of pluggable databases created

 

Set the parameter NONCDB_COMPATIBLE to TRUE

 

If you are running a Container database with just a single PDB, then this parameter is likely to be your best friend! When you set NONCDB_COMPATIBLE parameter to TRUE you are telling your Container database environment to behave as much like a non-Container database as possible. This makes an enormous amount of sense for CDBs containing a single PDB, where you want a SYSDBA connection to the PDB to behave in the same way as a SYSDBA connection to the root container. If you are running a database (with the Multitenant pack) that contains multiple PDBs then you should leave this parameter set to FALSE.

 

To change the NONCDB_COMPATIBLE parameter, you must be connected to the root container and restart the database.

 

sqlplus / as sysdba

SQL> alter system set NONCDB_COMPATIBLE=TRUE scope=spfile;

SQL> shutdown immediate;

SQL> startup

 

The below examples illustrate the changes in behaviour when NONCDB_COMPATIBLE is set to TRUE. In this example environment, we have set ORACLE_SID set to a local Container database, containing a single PDB called PDBORCL.

 

We can use sqlplus / as sysdba to connect to the root container and the EZConnect string sqlplus /@localhost:1521/PDBORCL to connect directly to our PDBORCL PDB (on port 1521).

 

1.  Setting NONCDB_COMPATIBLE to TRUE allows you to carry out some root container-level commands from within a user PDB:

With NONCDB_COMPATIBLE set to FALSE, it is not possible to switch logfiles from within a PDB

 

% sqlplus /@localhost:1521/PDBORCL as sysdba

SQL> alter system switch logfile;

*

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database

 

With NONCDB_COMPATIBLE set to TRUE, this is now possible.

 

% sqlplus /@localhost:1521/PDBORCL as sysdba

SQL> alter system switch logfile;

System altered.

 

2.  Setting NONCDB_COMPATIBLE to true allows you to set instance-wide initialisation parameters from within a PDB.

With NONCDB_COMPATIBLE set to FALSE, setting parameters, in particular memory parameters, applies only to the PDB itself.  In the below example

 

% sqlplus /@localhost:1521/PDBORCL as sysdba

SQL> alter system set sga_target=1G;

SQL> alter session set container=cdb$root;

SQL> show parameter sga_target;

 

The change to SGA_TARGET within the PDBORCL PDB had no impact on the global instance setting.

 

NAME         TYPE         VALUE

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

sga_target   big integer  0

 

With NONCDB_COMPATIBLE set to TRUE, setting memory parameters, amongst others, changes their instance-wide values.

 

% sqlplus /@localhost:1521/PDBORCL as sysdba

SQL> alter system set sga_target=1G;

SQL> alter session set container=cdb$root;

SQL> show parameter sga_target;     

 

The parameter change made within the PDB has been applied across the whole instance

 

NAME         TYPE         VALUE

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

sga_target   big integer  1G

 

Use Local UNDO tablespaces (available from 12.2 onwards)

 

The local UNDO feature was new in Oracle 12.2 and was perhaps the most significant enhancement to container databases in that version.  If you are running Oracle 12.2 and above it makes sense to use Local UNDO tablespaces as they enable so much beneficial functionality including:

 

  • The ability to flashback a PDB?
  • The ability to recover a PDB to Point-In-Time (in 12.1 you needed to recover to an auxiliary instance)
  • The ability to clone an open PDB (in 12.1 you need to make the PDB read-only before cloning it

 

Note that once you activate local UNDO, the setting is automatically applied instance wide.  All existing PDBs, including PDB$SEED will automatically have a local UNDO tablespace created for them.

 

Activating local UNDO tablespaces on a container database

 

To see whether local UNDO has been activated run the below commands (in the example below, local UNDO has yet to be activated)

 

% sqlplus / as sysdba

SQL> col PROPERTY_VALUE format a50

SQL> select property_name, property_value from database_properties

    where property_name = 'LOCAL_UNDO_ENABLED';

 

PROPERTY_NAME        PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED   FALSE

 

In order to activate local UNDO, the database must be restarted in upgrade mode.

 

% sqlplus / as sysdba

 

SQL> shutdown immediate;

SQL> startup upgrade

SQL> alter database LOCAL UNDO on;

SQL> shutdown immediate;

SQL> startup;

SQL> select property_name, property_value from database_properties

    where property_name = 'LOCAL_UNDO_ENABLED';

 

PROPERTY_NAME        PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED   TRUE

 

In our example, because we have Oracle Managed Files activated, the UNDO tablespaces are automatically created for all existing PDBs underneath the DB_CREATE_FILE_DEST location:

 

SQL> col pdb_name format a30 trunc

SQL> select pdb.name PDB_NAME, tbs.name TABLESPACE_NAME  

from v$tablespace tbs, v$pdbs pdb

where tbs.con_id=pdb.con_id and tbs.name like 'UNDO%'

order by 1;

 

PSB_NAME              TABLESPACE_NAME

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

PDB$SEED              UNDO_1

PDBORCL               UNDO_1  

 

Carry out all backups and restores at the root Container level

 

In a Container database environment it is possible to run a database backup from the root container or from within a PDB itself.   There are two significant differences between these two options, that you need to be aware of:

 

  • When running a database backup from the root container, by default, you will back up not only the root container, but also every PDB underneath it.  When running a backup from within a PDB, you can only backup the PDB itself.
  • When running a backup from within a PDB in 12.1 or 12.2, you cannot backup archive log files.

 

It is worth showing a couple of examples to illustrate, making use of the REPORT SCHEMA command within RMAN, which is really useful for showing the extent of any BACKUP DATABASE command (i.e. all the files that will be backed up by a BACKUP DATABASE command)

 

When we connect to our PDB directly in RMAN, running REPORT SCHEMA shows that our backup scope is limited to that PDB. That is, the only files we will be backing up are those that belong to the PDB itself.

 

% rman target /@localhost:1521/PDBORCL

RMAN> report schema;                                

 

Shows the data files I will back up with a BACKUP DATABASE command.

 

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name ORCL

 

List of Permanent Datafiles

===========================

File Size (MB) Tablespace    RB segs     Datafile Name

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

7     420       SYSTEM        NO          D:\ORADATA\ORCL\270FC32DDE97499392FA6D82805DA669\DATAFILE\O1_MF_SYSTEM_D8HD4WVY_.DBF

8     895       SYSAUX        NO          D:\ORADATA\ORCL\270FC32DDE97499392FA6D82805DA669\DATAFILE\O1_MF_SYSAUX_D8HD4XM4_.DBF

9      5        USERS         NO          D:\ORADATA\ORCL\270FC32DDE97499392FA6D82805DA669\DATAFILE\O1_MF_USERS_D8HD6OVY_.DBF

11    301       UNDO_1        NO          D:\ORADATA\ORCL\270FC32DDE97499392FA6D82805DA669\DATAFILE\O1_MF_UNDO_1_F4DYTGZ0_.DBF

 

List of Temporary Files

=======================

File Size(MB)  Tablespace         Maxsize(MB)  Tempfile Name

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

3      56      TEMP               32767        D:\ORADATA\ORCL\270FC32DDE97499392FA6D82805DA669\DATAFILE\O1_MF_TEMP_D8HD4XMN_.DBF

 

If we need to restore this PDB after a disk media failure, we will have to first need to have a functioning root container.  The best way to guarantee this is to run all database backups from the root container itself. Any BACKUP DATABASE command run run from the root container will, by default,  also back up all the PDBs defined within that instance.

 

Another reason for exclusively running backups from the root container, is that backups, run from within a PDB, will never back up archive logs. The PDB simply doesn’t know about archive logs. &This is true whatever the setting of NONCDB_COMPATIBLE. Not having the archive logs backed up alongside the database can be a serious hindrance to recovery. I will illustrate this in the below example, where we attempt to backup archive logs from within a PDB.  Note that NONCDB_COMPATIBLE has already been set to TRUE, to show that this parameter cannot help us in this case.

 

% rman target /@localhost:1521/PDBORCL

RMAN> backup archivelog all;           

  

Attempting to back up archive log from within a PDB.

 

Starting backup at 14-MAR-18

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=271 device type=DISK

specification does not match any archived log in the repository

backup cancelled because there are no files to backup

Finished backup at 14-MAR-18

 

The command doesn’t return an error, it just cannot see any of the archive logs that need backing up. Were we to have run the same commands, connected to the root container, then we would have successfully backed up all available archive logs.

 

Conclusion

 

Container databases are here whether you like it or not. I suspect that many will wisely delay the move to container technology a little to ensure that it is as mature as possible. For those who are committed to migrating to Containers sooner rather than later; hopefully the 5 points above will make the process as smooth and easy as possible. Keep an eye out for Parts 2 and 3 of this series for more useful hints and tips on living with Container databases.

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
8 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.