How to use the new SYSBACKUP privilege to perform backup and recovery tasks without accessing to table data

The SYSBACKUP is a new privilege introduced in the Oracle Database 12c release that allows a user to perform any backup or recovery operations.

It is heartily suggested to create a new user with the SYSBACKUP privilege granted and perform daily backup activities with that new user, instead of using directly the SYSBACKUP user created by default during the database installation: the SYSBACKUP user should even remain locked.

Before proceeding I will need to create (more...)

How to rename everything on Oracle Database (redolog members, tablespaces, schema objects, PDBs, partitions, constraints)

Few days ago I was wondering how many RENAME clause are available on Oracle Database. I have summarized them here:
  1. Renaming redolog members;
  2. Renaming tablespaces;
  3. Renaming datafiles of a single offline tablespace;
  4. Renaming constraints;
  5. Renaming schema objects (tables, views, sequences, private synonyms, indexes, triggers);
  6. Renaming table columns;
  7. Renaming table and index partitions (subpartitions);
  8. Restoring and Renaming table from the Recycle Bin;
  9. Changing the domain in a global database name for a CDB;
  10. Renaming a PDB;
(more...)

RMAN and SQL statement enhancements in Oracle Database 12c

One of the most useful enhancement Oracle has introduced within the release 12c in RMAN is the possibility to execute more SQL statements.

Of course RMAN is primarily used for backup and recovery operations, but sometimes I need to run SQL commands: so in 11g or lower I have to open another terminal/tab or quit the current RMAN session and start a new one with sqlplus.

In Oracle Database 12c instead most of the sql (more...)

How to identify which pdbs a backup set belong to

I want to see what useful information are returned by few RMAN commands such as report schema or list backup when using the default RMAN configuration and getting some backups.

When connected to the ROOT container issuing the RMAN report schema command is quite simple identify how many pluggable databases are plugged into the container database and, above all, the relationship with their datafiles and locations.

In my case the container database, named CDB001, contains (more...)

How to recover a table in a pluggable database from a backup

I'm going to test some useful recovery scenarios for the Oracle Database 12c release.
An interesting option introduced with this release is the possibility to restore even a single table, without impacting other objects, tablespace or database when that table is no more in the recycle bin.

The current release I'm using is Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit based on a Linux platform.

Let's start with an (more...)

How to solve the error ‘ERROR: NMO Not Setuid-root (Unix-only)’ on Oracle Enterprise Manager Cloud Control while contacting the EM Agent

I've just finished to setup at home an Oracle Real Application Clusters virtual environment formed by two nodes.

I would like to monitor and eventually execute some commands from another machine where I've installed the latest (at the moment of this post of course) available Enterprise Manager Cloud Console 12c that is Oracle Enterprise Manager Cloud Control 12c Release 3 Plug-in Update 1 (12.1.0.3) which contains the original 12c Release 3 (12. (more...)

Differences in default RMAN configuration settings between 12c and 11g

I wanted to write a post on configuring the RMAN environment using the SHOW and CONFIGURE commands in Oracle Database 12c, but after few commands I decided to investigate about the differences on default RMAN settings between the versions 10g, 11g and 12c.

Here are part of my results: the (more...)

How to create a PDB from a Non-CDB using Oracle Data Pump

Beside the DBMS_PDB package you can use Oracle Data Pump to create a pluggable database from a non-container database: it consists to export the non-CDB and import into an empty and already created pluggable database.

In my case the non-container database I want to transform into a pluggable database is (more...)

The first bug on Oracle Database 12c: how to create a PDB by cloning a remote existing pluggable database

In this post I want to describe how to create a PDB by cloning a remote existing pluggable database: at the end of this process you should have a new PDB (in my case PDB103) contained in a target CDB (CDB001) cloned from a source PDB (PDB003) contained in a (more...)

How to create a PDB from a Non-CDB using DBMS_PDB package

To test this scenario I have recently created using DBCA a non container database named ORCL. CDB001 and CDBTEST are instead two container databases.
[oracle@vsi08devpom ~]$ ps -ef|grep smon
oracle    4081     1  0 Jul15 ?        00:01:30 ora_smon_CDB001
oracle   11394     1  0 10:20 ?        00:00:00 ora_smon_ORCL
oracle   12586  9609  0 10:46 pts/0    00:00:00  (more...)

How to unplug a PDB and plug it back into the same CDB

You have just unplugged a PDB and want to know how to eventually plug it back into the same container.
Let's assume I have a pluggable database named PDB003 and I am just able to unplug it using the following commands:
SQL> alter pluggable database pdb003 close immediate;

Pluggable database  (more...)

How to create a pluggable database by unplugging an existing PDB and plugging it into another container

So you want create a new pluggable database into the CDBTEST container located into vsi08devpom machine (@vsi08devpom in this post) and you want the exact copy of PDB003 pluggable database contained into CDB001 located into my local machine (@localhost in this post).

Why not simply unplug it from my local (more...)

How to create a pluggable database by cloning an existing local PDB

Using the CREATE PLUGGABLE DATABASE ... FROM command you can clone an existing pluggable database (the source pdb) to create a new pdb (the clone pdb).
The source pdb could be in the current local container or it can be located in a remote container (in a next post): during a (more...)

How to change the open mode of all pluggable databases

When you need to modify the open mode of all your PDBs at the same time (look at this post if you want to change the open mode of only a specific pluggable database) you can use ALTER PLUGGABLE DATABASE command and the ALL option.
As usual you have to (more...)

Pluggable database: limitations of the open mode of the CDB imposed on the open mode of PDBs

The open mode of the container database imposes limitations on the open mode of PDBs.
For example, the root must be open before any PDBs can be open. Therefore, you might need to change the open mode of the root before changing the open mode of a PDB. Let's start.

(more...)

How to change the open mode of listed PDBs

The clauses of the ALTER PLUGGABLE DATABASE statement that modify the mode of a PDB are:
- OPEN READ WRITE [RESTRICTED] [FORCE]: it opens the PDB in read/write mode;
- OPEN READ ONLY [RESTRICTED] [FORCE]: it opens the PDB in read-only mode;
- OPEN UPGRADE [RESTRICTED]: it (more...)

How to create a pluggable database PDB in a multitenant container database CDB using the files of the seed PDB$SEED

The new architecture provided by Oracle Database 12c enables an Oracle database to function as a multitenant container database (CDB):
it can include zero, one, or many pluggable databases (PDBs), that is a portable collection of schemas, schema objects and nonschema objects.

A multitenant container database (CDB) is formed by (more...)

Installing Oracle Database 12c as Container Database with three Pluggable Databases

Here is a video recorded during the creation of the first database. Go on if you want to see all the screenshots.

So we have already installed the software of the Oracle Database 12c release 1 (have a look at this post) and now we want to create (more...)

How to configure the HTTPS Port for EM Database Express 12c

Configuring the HTTPS Port for EM Database Express 12c

If you used dbca to configure your database and enabled to configure also the EM Express you can find the EM Express URL provided by DBCA in the latest GUI screen.
If you forget the URL you can use the following (more...)

How to install Oracle Instant Client 12c and Oracle SQL Plus on Ubuntu

So you want to use your Ubuntu distribution and connect to an Oracle database.
Here you can find the steps to install and configure the Oracle Instant Client on Ubuntu 12.04.

First of all you need to go to the following link http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html and choose the (more...)