Category Archives: PDB

Instance Parameter for CDB and PDB

November 10, 2015 Larry Catt Leave a comment

In Oracle 12c multitenant databases CDB and PDBs use initialization parameters to control their behavior. Initialization Parameters for CDB and PDB have the following characteristics:

CDB will have one SPFILE.
PDB does not have an SPFILE.
PDB inherit initialization parameters from their CDB.
Most initialization parameters can be changed at the PDB level, these parameters that can have a value of TRUE in the column ISPDB_MODIFIABLE column of V$SYSTEM_PARAMETER view.
Changing initialization parameters use the ALTER SYSTEM command with scope of SPFILE or BOTH and they survive system reboot.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Automatic Startup all PDB in a CDB

November 6, 2015 Larry Catt Leave a comment

In Oracle 12c multitenant databases consist of a container database CDB which contain one or more pluggable databases PDB. Startup of the PDB must be done after the CDB it is contained in has been started. This procedures covers the implementation of a trigger to startup all PDBs in a CDB.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Oct 24 14:55:04 EDT 2016 on pts/1

[oracle@linux2 ~]$

Logon to your CDB where the PDB you wish to start is stored.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 09:41:06 2016

Connected to an idle instance.

SQL> startup open

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL>

View the available PDBs within the CDB with SQL statement: select name, open_mode from v$pdbs.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 MOUNTED

PDB2 MOUNTED

SQL>

NOTE: MOUNTED is the closed state for a PDB while it CDB is open.

Create a trigger which executes after the CDB starts with the following create trigger statement.

create trigger PDB_OPEN

after startup on database

begin

execute immediate ‘alter pluggable database all open’;

end;

/SQL> 2 3 4 5 6

Trigger created.

SQL>

Now shutdown the CDB with the command: shutdown immediate.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Restart the CDB with command: startup open.

SQL> startup open

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL>

Check the status of PDBs with SQL statement: select name, open_mode from v$pdbs.

SQL> select name, open_mode from v$pdbs;

PDB$SEED READ ONLY

PDB1 READ WRITE

PDB2 READ WRITE

SQL>

Note: All PDBs are now open after restart of CDB without the need to manually issue a command from SQL prompt.
This completes startup of all PDB within its CDB automatically.

Larry Catt

OCP

12c, dbca, Multitenant, PDB

Oracle 12c Create PDB

November 2, 2015 Larry Catt Leave a comment

In Oracle 12c, Container Databases (CDB) were introduced which allowed for multiple Pluggable Database (PDB) to be placed in a single oracle instance to reduce the amount of resource consumed. This procedure covers the creation of a PDB in an already existing CDB using Oracle Database Configuration Assistant in a Windows environment.

Logon your windows server as the administrator.

Open Oracle Database Configuration Assistant.

At the Database Operation Page select “Create Database”

At the Manage Pluggable Database Page select “Create a Pluggable Database”

At the Database List Page, select the Container Database CDB you would like your PDB to be created in.

At the Create Pluggable Database page, select “Create a new Pluggable Database” and press Next.

At the Pluggable Database Options page, Enter the name of the Pluggable Database, Storage options, and Password Credentials. NOTE: This is an administrator account inside of the new PDB, not administrator account of OS or CDB. Press Next button.

At the Summary Page, Press the Finish button.

The PDB will now be created in Windows.

A confirmation screen will appear once the install is completed and press the OK button.

Back at the Progress Page, press the Close button to terminate Database Configuration Assistant.

This completes creation of Oracle 12c PDB in an existing CDB with Database Configuration Assistant.

Larry Catt

OCP

12c, CDB, Multitenant, PDB, privileges, Users

Privileges of Local Users in Oracle Multitenant Database

October 2, 2015 Larry Catt Leave a comment

In Oracle Multitenant Databases, local users only have access and privileges to a single PDB and not the CDB. You can have the same user name across multiple PDBs housed in the same CDB, but the users share no privileges and only shares a common name. This procedure give a brief overview of local user privileges in Oracle 12c Multitenant Databases. Listed below are the stated restriction on local user privileges.

A privilege granted locally can only be used in that container, even if granted by common user.
Both common and local users can grant privileges locally.
Both common and local users can grant privileges to other common or local users.
Grantor must connect to container and specify CONTAINER=CURRENT clause.
Common and Local user can grant privileges locally if they have appropriate privileges.

A privilege granted locally can only be used in that container, even if granted by common user.

As the common user c##dba logon to pdb1 and pdb2 creating users user_local.

SQL> connect c##dba/password@pdb1

Connected.

SQL> create user user_local identified by password;

User created.

SQL> connect c##dba/password@pdb2

Connected.

SQL> create user user_local identified by password;

User created.

SQL>

Now connect to pdb1 as c##dba and grant the user user_local the DBA role. Connect as user_local and create an additional local user user_test. NOTE: The user user_local can successfully connect to pdb1 and create an additional local user in this PDB.

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to user_local;

Grant succeeded.

SQL> connect user_local/password@pdb1

Connected.

SQL> create user user_test identified by password;

User created.

SQL>

Now attempt to connect to pdb2 with local user user_local. NOTE: The user user_local does not even have permission to connect to pdb2, even though the user exists in pdb2.

SQL> connect user_local/password@pdb2

ERROR:

ORA-01045: user USER_LOCAL lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL>

Both common and local users can grant privileges locally.

Using local_user created above from pdb1, you can grant privileges to the newly created user user_test logon as local_user.

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

SQL>

Both common and local users can grant privileges to other common or local users.

Using local_user created above from pdb1, you can grant privileges to the newly created user user_test logon as local_user.

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

SQL>

Grantor must connect to container and specify CONTAINER=CURRENT clause or specify no CONTAINER clause. CONTAINER=ALL is not available in PDB.

As the user_local grant resource to user_test without a container clause and CONTAINER=ALL clause.

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

SQL> grant resource to user_test container=all;

grant resource to user_test container=all

ERROR at line 1:

ORA-65029: a Local User may not grant or revoke a Common Privilege or Role

SQL>

Common and Local user can grant privileges locally if they have appropriate privileges.

Local user_local can create additional users in PDB pdb1 once the user has been granted the DBA role.

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to user_local;

Grant succeeded.

SQL> connect user_local/password@pdb1

Connected.

SQL> create user user_test identified by password;

User created.

SQL>

This completes the overview of privileges for local users in Oracle 12c Multitenant Database.

Larry Catt

OCP

12c, CDB, dbca, PDB

Oracle 12c Create CDB with PDB

September 18, 2015 Larry Catt Leave a comment

In Oracle 12c, Container Databases (CDB) were introduced which allowed for multiple Pluggable database (PDB) to be placed in a single oracle instance to reduce the amount of resource consumed. This procedure covers the creation of a CDB with a PDB using Oracle Database Configuration Assistant in a Windows environment.

Logon your windows server as the administrator.

Open Oracle Database Configuration Assistant.

At the Database Operation Page select “Create Database”

At the Creation Mode Page select “Advanced Mode”

At the Database Template Page select the Desired type of database, in This example we select “General Purpose or Transaction Processing”

At the Database Identification Page enter the Global Database Name and SID, then select “Create as Container Database”, “Create a Container Database with one or more PDBs”, enter Number of PDBs = 1, and Name your PDB.

At the Management Options Page, select Configure Enterprise Manger (EM) Database Express if desired.

At the Database Credentials Page, select Use the Same Administrative Password for All Accounts and enter a valid password. Additionally enter a password the administrative account on the windows system.

On the Network Configuration Page, select the Listener to be used by the new database.

At the Storage Location Page, select the appropriate storage for your database.

At the Database Options page select nothing and Press Next button.

At the Initialization Parameters Page, select the defaults and Press the Next button.

At the Creation Options Page, select Create Database and Generate Database Creation Scripts (giving a valid destination directory) and press Next button.