Category Archives: PDB

12c, dbca, linux, Multitenant, Oracle, PDB

Create PDB in silent mode

December 12, 2016 Larry Catt Leave a comment

In Oracle 12c RDBMS you can use the Database Configuration Assistant to create a PDB from shell scripts or command line in an existing CDB. You can provide all of the necessary parameters for the PDB creation through a response file or at the command prompt. In this example we will provide all required command line option to create new PDB called pdb1 in the CDB1 container.

Logon to your Oracle server as the Oracle software owner.

[root@linux2 ~]# su – oracle

Last login: Wed Oct 5 15:10:16 EDT 2016 on pts/2

[oracle@linux2 ~]$

Define the following parameter with values:
1. -sourceDB = the name of the CDB database you wish to create your PDB in.
2. -pdbName = the name of the new PDB database.
3. -pdbAdminPassword = password for the PDB admin user.

You will have to also supply the tags: –silent and -createPluggableDatabase

Update the below DBCA command with the values defined in step 2.

dbca -silent -createPluggableDatabase -sourceDB cdb1 -pdbName pdb1 -pdbAdminPassword password

Example execution.

[oracle@linux2 admin]$ dbca -silent -createPluggableDatabase -sourceDB cdb1 -pdbName pdb1 -pdbAdminPassword password

Creating Pluggable Database

4% complete

12% complete

21% complete

38% complete

85% complete

Completing Pluggable Database Creation

100% complete

Look at the log file “/opt/app/oracle/cfgtoollogs/dbca/cdb1/pdb1/cdb1.log” for further details.

[oracle@linux2 admin]$

View the status of the listener process to validate that the new PDB has been picked up by the listener with the command: lsnrctl status

[oracle@linux2 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 20-OCT-2016 10:21:44

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 19-OCT-2016 14:06:54

Uptime 0 days 20 hr. 14 min. 50 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Services Summary…

Service “cdb1” has 2 instance(s).

Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

Instance “cdb1”, status READY, has 1 handler(s) for this service…

Service “cdb1XDB” has 1 instance(s).

Instance “cdb1”, status READY, has 1 handler(s) for this service…

Service “pdb1” has 1 instance(s).

Instance “cdb1”, status READY, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 admin]$

Update the tnsnames.ora file with the reference to newly created PDB1 with the command:

echo -e “PDB1 =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS_LIST =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” )\n” \

” (CONNECT_DATA =\n” \

” (SERVER = DEDICATED)\n” \

” (SERVICE_NAME = pdb1)\n” \

” )\n” \

” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

Example execution:

[oracle@linux2 admin]$ echo -e “PDB1 =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS_LIST =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” )\n” \

> ” (CONNECT_DATA =\n” \

” (SERVER = DEDICATED)\n” \

” (SERVICE_NAME = pdb1)\n” \

” )\n” \

” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora> ” (SERVER = DEDICATED)\n” \

> ” (SERVICE_NAME = pdb1)\n” \

> ” )\n” \

> ” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

[oracle@linux2 admin]$

Test connection to new PDB with the command: tnsping pdb1

[oracle@linux2 admin]$ tnsping pdb1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 20-OCT-2016 10:32:32

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))

OK (0 msec)

[oracle@linux2 admin]$

This completes the creation of new PDB in silent mode.

Larry Catt

OCP

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

Users accounts in Multitenant Database

November 19, 2016 Larry Catt Leave a comment

Oracle 12c multitenant database consist of a CDB which houses one or more PDB which house application data. Now this design has direct implications on user accounts and how they are implemented. This article attempts to briefly cover the implementation of user accounts in the Oracle 12c multitenant architecture.

Oracle 12c multitenant database have two class of user accounts: Common and Local. Common users belong to the root container (CDB) and have full access to PDBs within the CDB. Local users belong a particular PDB and do not have access to the root container (CDB) which houses them. They following rules govern User accounts in the multitenant database architecture:

Common user have the same identity in the root and every PDB.
Common users can connect to root and PDB.
Common users connected to root can perform ALTER PLUGGABLE DATABASE, CREATE USER/ROLE that affect multiple PDBs.
Local users apply to only one PDB.
Local users cannot connect to CDB their PDB is contained in.
Local users cannot cross PDBs but same username can exist in two different PDB in same CDB.
Most privileges only apply in current container. IE user must first connect to container to query tables in that container and they cannot do it from the root.

Larry Catt

OCP

12c, CDB, Multitenant, PDB, Users

Container Clause for Common Users in Oracle Multitenant Databases

September 8, 2016 Larry Catt Leave a comment

In Oracle 12c multitenant databases, a user defined in the CDB is considered a Common user. Common users with correct privileges can access all PDBs in the CDB. The restriction of which database a user can and cannot access in the multitenant database architecture is governed by the CONATINER clause. This article looks at the creation of Common users and the use of the container clause to allow access to multiple PDBs.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Jul 26 10:07:02 EDT 2016 on pts/2

[oracle@linux2 ~]$

Logon to oracle as the sys user.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 26 14:30:43 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Create the user c##cdb_dba. NOTE: users names in the root container must begin with c##… or C##…

SQL> create user c##cdb_dba identified by password;

User created.

Just as in a traditional Oracle database a user has no privileges until they are granted and what a user can do is limited by these privileges. But an additional clause exists in CDB and PDB which determines where a common user can use their privileges, this clause is CONTAINER.

In this first example, we will use the clause CONTAINER=CURRENT, meaning that the privilege is available in the root container only.

SQL> grant all privileges to c##cdb_dba container=CURRENT;

Grant succeeded.

Now logon to the user c##cdb_dba and attempt to create another user account and see that you can connect to the root containe.

SQL> connect c##cdb_dba/password

Connected.

SQL>

In this database we have two PDBs: PDB1 and PDB2, attempt to connect to both PDBs and not that the operation fails. But you can reconnect to root CDB CDB$ROOT.

SQL> alter session set container=PDB1;

ERROR:

ORA-01031: insufficient privileges

SQL> alter session set container=PDB2;

ERROR:

ORA-01031: insufficient privileges

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL>

Reconnect as the sys user and grant “all privileges” with a container = ALL. Meaning that you can connect to both root, pdb1 and pdb2. At the root level you can specify ALL and CURRENT for the clause CONTAINER. Now you can connect to all containers in PDB

SQL> grant all privileges to c##cdb_dba container=all;

Grant succeeded.

SQL> connect c##cdb_dba/password

Connected.

SQL> alter session set container=pdb1;

Session altered.

SQL> alter session set container=pdb2;

Session altered.

SQL> alter session set container=cdb$root;

Session altered.

SQL>

Larry Catt

OCP

12c, PDB

Connection to PDB

August 6, 2016 Larry Catt Leave a comment

In Oracle 12c multitenant databases CDBs are containers databases which hold oracle internals, supplied accounts and processes and client PDBs pluggable databases for applications. PDBs are services within a CDB and thus are connected to through the CDB they are contained inside of or through oracle networking. This procedure demonstrates the connection through both processes.

Connection via CDB

Connect to your oracle database server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Jun 24 11:43:05 EDT 2016 on pts/1

[oracle@linux2 ~]$

Verify that you have environment has the appropriate CDB SID set with command echo $ORACLE_SID. If it is not the appropriate SID change which the export ORACLE_SID= statement.

[oracle@linux2 ~]$ echo $ORACLE_SID

cdb1

[oracle@linux2 ~]$ export ORACLE_SID=cdb1

[oracle@linux2 ~]$ echo $ORACLE_SID

cdb1

[oracle@linux2 ~]$

Now you can connect directly into the CDB referenced by the environmental parameter ORACLE_SID with the command sqlplus / as sysdba. NOTE: You are connecting with the administrative account

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 24 12:26:00 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Once you are connected to the CDB, you can now change your session to connect to the PDB of interest. In this example we will connect to pdb1. You do this with the command alter session set container=<PDB_name>l

SQL> show con_name;

CON_NAME

——————————

CDB$ROOT

SQL> alter session set container = pdb1;

Session altered.

SQL> show con_name

CON_NAME

——————————

PDB1

SQL>

This completes connecting the PDB from CDB.

Connection via Oracle networking

Connect to your oracle database server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Jun 24 11:43:05 EDT 2016 on pts/1

[oracle@linux2 ~]$

View the database connection available in the listener process with command lsnrctl status.

[oracle@linux2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 24-JUN-2016 14:03:44

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 24-JUN-2016 14:02:17

Uptime 0 days 0 hr. 1 min. 26 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Services Summary…

Service “cdb1” has 2 instance(s).

Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

Instance “cdb1”, status READY, has 1 handler(s) for this service…

Service “cdb1XDB” has 1 instance(s).

Instance “cdb1”, status READY, has 1 handler(s) for this service…

Service “pdb1” has 1 instance(s).

Instance “cdb1”, status READY, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 ~]$

See the PDB pdb1 in the container cdb1. Connect directly to the PDB via oracle listener by referencing this listener server.

[oracle@linux2 ~]$ sqlplus system/password@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 24 14:57:43 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>SQL> show con_name

CON_NAME

——————————

PDB1

SQL>

This completes connection to PDB via oracle networking.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Oracle 12c – Use Resource Manager for a CDB and PDB

July 22, 2016 Larry Catt Leave a comment

Resource manager determines how resources are shared amongst multiple competing tasks within oracle. Resource Management in a CDB environment has two components: CDB level – Manage resources amongst multiple PDBs and has the ability to allocate/limit resources to specific PDB. PDB level – Manages resources within a single PDB. Through using the package DBMS_RESOURCE MANGER, the administrator is able to create Plans, Groups, and Allocations of resources.

Resource Allocation:

Create Resource Plans.
Create Consumer Groups.
Assign PDBs to Groups.
Each PDB is allocated a portion of the total system resources in the CDB.
Inside a PDB resources are allocated to individual sessions in the PDB.
Specify which PDBs shares of resources based on their importance.
Limit CPU usage of individual PDB.
Limit number of parallel servers a PDB can use.
Limit resource usage of different sessions in a single PDB.
Monitor resource usage of PDBs.

Larry Catt

OCP

12c, Multitenant, PDB

Startup Single PDB

July 13, 2016 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 startup of a single PDB.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Jun 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 Jun 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

SQL>

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

You have four options for PDBs to choose from:
1. MOUNTED – PDB closed but CDB open.
2. OPEN MIGRATE – PDB is open for upgrade and patching.
3. OPEN READ ONLY – PDB is open for reads but not writes.
4. OPEN READ WRITE – PDB is open for all operations.

All of this modes can be moved to and from with the SQL statement: ‘ALTER PLUGGABLE DATABASE <PDB_NAME> <OPTION>; In this step we will move PDB1 from mounted to open read write. NOTE: read write is optional.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

SQL>

You can close the PDB with close option.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 MOUNTED

SQL>

You can actually specify the read write option and it will be accepted.

SQL> alter pluggable database pdb1 open read write;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

SQL>

You cannot move from read write to simply read option, you have to shut down the PDB first.

SQL> alter pluggable database pdb1 open read only;

alter pluggable database pdb1 open read only

ERROR at line 1:

ORA-65019: pluggable database PDB1 already open

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ ONLY

SQL>

The other options are self-evident. This completes startup of single PDB within its CDB

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Startup Single PDB

June 25, 2016 Larry Catt Leave a comment

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Jun 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 Jun 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

SQL>

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

You have four options for PDBs to choose from:
1. MOUNTED – PDB closed but CDB open.
2. OPEN MIGRATE – PDB is open for upgrade and patching.
3. OPEN READ ONLY – PDB is open for reads but not writes.
4. OPEN READ WRITE – PDB is open for all operations.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

SQL>

You can close the PDB with close option.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 MOUNTED

SQL>

You can actually specify the read write option and it will be accepted.

SQL> alter pluggable database pdb1 open read write;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

SQL>

You cannot move from read write to simply read option, you have to shut down the PDB first.

SQL> alter pluggable database pdb1 open read only;

alter pluggable database pdb1 open read only

ERROR at line 1:

ORA-65019: pluggable database PDB1 already open

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ ONLY

SQL>

The other options are self-evident. This completes startup of single PDB within its CDB

Larry Catt

OCP

12c, network, PDB, tnsnames

Configure tnsnames.ora file for PDB

June 6, 2016 Larry Catt Leave a comment

Oracle 12c multitenant database normally uses the same oracle networking files as other Oracle RDBMS implementations. This article covers the configuration of the tnsnames.ora file for connection to a PDB database cdb1.

Connect to your oracle database server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Apr 24 10:45:22 EDT 2016 on pts/1

[oracle@linux2 ~]$

You can use the following code to create the appropriate tnsnames.ora entries in the file $ORACLE_HOME/network/admin/tnsnames.ora or manually insert into the file knowing the machine IP, Port, SID, and service name.

NOTE: You have to edit the parameter SERVICE_NAME and Alias, in below example they are pdb1 and PDB1 respectively. PDBs do not have SID listings because they are Services which are contained within a CDB and only CDBs have SIDs in multitenant architecture.

echo -e “PDB1 =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS_LIST =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” )\n” \

” (CONNECT_DATA =\n” \

” (SERVER = DEDICATED)\n” \

” (SERVICE_NAME = pdb1)\n” \

” (SID = “$ORACLE_SID”)\n” \

” )\n” \

” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

Execution Example:

[oracle@linux2 admin]$ echo -e “PDB1 =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS_LIST =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” )\n” \

> ” (CONNECT_DATA =\n” \

> ” (SERVER = DEDICATED)\n” \

> ” (SERVICE_NAME = pdb1)\n” \

> ” (SID = “$ORACLE_SID”)\n” \

> ” )\n” \

> ” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

[oracle@linux2 admin]

After execution or editing, you can view the contains of the tnsnames.ora file with the cat command as shown below:

[oracle@linux2 admin]$ cat tnsnames.ora

CDB1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cdb1)

(SID = cdb1)

)

PDB1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb1)

(SID = cdb1)

)

[oracle@linux2 admin]$

As long as the listener processes is started, the container database is up and running, and the respective PDB is up; you can test the connection through the tnsping utility by suppling the reference alias name in the tnsnames.ora file. In this example the reference name is PDB1, as shown below.

[oracle@linux2 admin]$ tnsping PDB1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 24-APR-2016 11:56:34

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1) (SID = cdb1)))

OK (0 msec)

[oracle@linux2 admin]$

This completes setup of tnsnames.ora file for PDB connection in Oracle 12c.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Startup CDB

May 27, 2016 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 CDB can be done with none of the PDBs it contains being started. This procedures covers the startup of a CDB.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

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

[oracle@linux2 ~]$

No CDB in non-RAC system acts the same as any Oracle single instance database with three startup modes: NOMOUNT, MOUNT, and OPEN. Please see related article about modes. You can check for running CDB instances from the OS layer by searching for the SMON process. This does not show what startup mode the instance is in.

[oracle@linux2 ~]$ ps -ef|grep smon

oracle 2660 2395 0 09:34 pts/1 00:00:00 grep –color=auto smon

oracle 3849 1 0 Apr24 ? 00:00:02 ora_smon_cdb1

[oracle@linux2 ~]$

This shows that there is one instance started in some mode called cdb1. In this example our CDB database is cdb1. NOTE: You cannot validate any PDB in this manner. PDBs are services within a CDB and cannot be viewed via OS layer.

Since this CDB is up and running, we will first demonstrate the shutdown process which is identical to non-CDB databases in single instance mode with three options: IMMEDIATE, ABORT, NORMAL. Please see related articles about modes. You have to connect to CDB as the SYSDBA privileged user to perform shutdown as shown below.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 25 09:39:29 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@linux2 ~]$

Now to startup the CDB, again you have to connect with SYSDBA privileged user, as shown below. Open is the default mode.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 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>

Note you have to startup the listener process to enable oracle networking, but as far as the CDB is concerned, the database is ready for use.

This completes startup of CDB in Oracle 12c.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Startup all PDB in a CDB

April 8, 2016 Larry Catt Leave a comment

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Mar 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 Mar 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.

You have four options for PDBs to choose from:
1. MOUNTED – PDB closed but CDB open.
2. OPEN MIGRATE – PDB is open for upgrade and patching.
3. OPEN READ ONLY – PDB is open for reads but not writes.
4. OPEN READ WRITE – PDB is open for all operations.

All of this modes can be moved to and from with the SQL statement: ‘ALTER PLUGGABLE DATABASE all <OPTION>; In this step we will move all the PDBs from mounted to open read write. NOTE: read write is optional.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

PDB2 READ WRITE

SQL>

You can close all the PDB with close option.

SQL> alter pluggable database all close;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 MOUNTED

PDB2 MOUNTED

SQL>

You can actually specify the read write option and it will be accepted.

SQL> alter pluggable database all open read write;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

PDB2 READ WRITE

SQL>

You cannot move from read write to simply read option with all PDB and you cannot move all PDB into read only mode. You can only open all PDBs in read write mode and close all PDBs with all option.

SQL> alter pluggable database all open read;

alter pluggable database all open read

ERROR at line 1:

ORA-00922: missing or invalid option

SQL> alter pluggable database all close;

Pluggable database altered.

SQL> alter pluggable database all open read;

alter pluggable database all open read

ERROR at line 1:

ORA-00922: missing or invalid option

SQL>

There are no other options to move all PDBs open mode. This completes startup and shutdown of all PDB within its CDB.

Larry Catt

OCP

Catt Solutions

Category Archives: PDB

Create PDB in silent mode

Users accounts in Multitenant Database

Container Clause for Common Users in Oracle Multitenant Databases

Connection to PDB

Oracle 12c – Use Resource Manager for a CDB and PDB

Startup Single PDB

Startup Single PDB

Configure tnsnames.ora file for PDB

Startup CDB

Startup all PDB in a CDB

Oracle tips and tricks.