Category Archives: CDB

Determining SID(s) from Database Server

July 15, 2017 Larry Catt Leave a comment

Oracle Databases implemented on Unix and Linux have a file /etc/oratab file which determines the current SID, Oracle Homes, or
active status which exist on the server, if a proper installation is done. This procedure uses the file to extract
current SID(s).

1. Logon to your Oracle Server as the Oracle software owner.

[root@linux2 ~]# su – oracle
Last login: Wed Oct 5 15:16:55 EDT 2016 on pts/2
[oracle@linux2 ~]$

2. Execute the command: egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $1}’

[oracle@linux2 ~]$ egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $1}’
cdb1
[oracle@linux2 ~]$

3. For this system there is only one database with a SID of ‘cdb1’.

Larry Catt
OCP

12c, CDB, Multitenant, Users

Creating Common Users in Oracle Multitenant Databases

January 14, 2017 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. This article looks at the creation of Common users and their use in the multitenant databases.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Oct 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 Oct 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 cdb_dba

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.

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, dbca, script

Create CDB in silent mode

October 10, 2016 Larry Catt Leave a comment

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

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. -gdbName = the name of the database you wish to create.
2. -sid = the SID you wish to use for the database.
3. -SysPassword = password for the SYS user.
4. -SystemPassword = password for the SYSTEM user.
5. -datafileDestination = location of the data file storage.
6. -storageType = type of storage used for files.
7. -characterSet = character set of database.

You will have to also supply the tags for –silent option, -createDatabase, -templateName, and –createAsContainerDatabase.

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

dbca -silent \

-createDatabase -templateName General_Purpose.dbc \

-gdbName <db name> -sid <SID name> \

-createAsContainerDatabase true \

-SysPassword <SYS Password> -SystemPassword <SYSTEM Password> \

-datafileDestination <Directory Name> \

-storageType <Type of storage> -characterSet <Character Set>

Example execution.

[oracle@linux2 database]$ dbca -silent \

> -createDatabase -templateName General_Purpose.dbc \

> -gdbName cdb1 -sid cdb1 \

> -createAsContainerDatabase true \

> -SysPassword password12 -SystemPassword password12 \

> -datafileDestination /u01/oradata \

> -storageType FS -characterSet AL32UTF8

Copying database files

1% complete

3% complete

11% complete

18% complete

26% complete

33% complete

37% complete

Creating and starting Oracle instance

40% complete

45% complete

46% complete

47% complete

52% complete

57% complete

58% complete

59% complete

62% complete

Completing Database Creation

66% complete

70% complete

74% complete

85% complete

96% complete

100% complete

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

[oracle@linux2 database]$

NOTE: The following step should only be used if your system only has one SID and ORACLE_HOME. Update profile with the ORACLE_HOME and ORACLE_SID environmental variables. Add the ORACLE_HOME/bin path to your PATH variable. You can use the following two statements update the .bash_profile file located in your oracle software owner’s home file.

egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print “export ORACLE_HOME=”$2″”}’ >> ~/.bash_profile

egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print “export ORACLE_SID=”$1″”}’ >> ~/.bash_profile

echo “export PATH=$PATH:$ORACLE_HOME/bin” >> ~/.bash_profile

[oracle@linux2 app]$ egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print “export ORACLE_SID=”$1″”}’ >> ~/.bash_profile

[oracle@linux2 app]$

[oracle@linux2 app]$ echo “export PATH=$PATH:$ORACLE_HOME/bin” >> ~/.bash_profile

[oracle@linux2 app]$

[oracle@linux2 app]$ cat ~/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export ORACLE_HOME=/opt/app/oracle/product/12.1.0.2/db_1

export ORACLE_SID=cdb1

export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin

[oracle@linux2 app]$

Now re-execute the .bash_profile file to update environmental variables with the command: . ~/.bash_profile

[oracle@linux2 app]$ . ~/.bash_profile

[oracle@linux2 app]$

Verify that the variables have been updating by echo the environmental variables of $ORACLE_HOME, $ORACLE_SID, $PATH.

[oracle@linux2 app]$ echo $ORACLE_HOME

/opt/app/oracle/product/12.1.0.2/db_1

[oracle@linux2 app]$ echo $ORACLE_SID

cdb1

[oracle@linux2 app]$ echo $PATH

/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin

[oracle@linux2 app]$

Create the file tnsnames.ora with the following statement:

echo -e “CDB1 =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS_LIST =\n” \

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

” )\n” \

” (CONNECT_DATA =\n” \

” (SERVER = DEDICATED)\n” \

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

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

” )\n” \

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

Execution Example:

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

> ” (DESCRIPTION =\n” \

> ” (ADDRESS_LIST =\n” \

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

> ” )\n” \

> ” (CONNECT_DATA =\n” \

> ” (SERVER = DEDICATED)\n” \

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

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

> ” )\n” \

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

[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)

)

[oracle@linux2 admin]$

Create the file listener.ora with the following statement:

echo -e “SID_LIST_LISTENER =\n” \

” (SID_LIST =\n” \

” (SID_DESC =\n” \

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

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

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

” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

” )\n” \

“\nLISTENER =\n” \

” (DESCRIPTION_LIST =\n” \

” (DESCRIPTION =\n” \

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

” ))\n” \

“\nADR_BASE_LISTENER = /opt/app/oracle\n” \

“\nLOGGING_LISTENER=ON\n” \

“\nTRACE_LEVEL_SERVER=SUPPORT\n” \

“\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

Execution example:

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

> ” (SID_LIST =\n” \

> ” (SID_DESC =\n” \

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

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

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

> ” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

> ” )\n” \

> “\nLISTENER =\n” \

> ” (DESCRIPTION_LIST =\n” \

> ” (DESCRIPTION =\n” \

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

> ” ))\n” \

> “\nADR_BASE_LISTENER = /opt/app/oracle\n” \

> “\nLOGGING_LISTENER=ON\n” \

> “\nTRACE_LEVEL_SERVER=SUPPORT\n” \

> “\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

[oracle@linux2 admin]$ cat listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = cdb1)

(SERVICE_NAME = cdb1)

(GLOBAL_DBNAME = cdb1)

(ORACLE_HOME = /opt/app/oracle/product/12.1.0.2/db_1)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

))

ADR_BASE_LISTENER = /opt/app/oracle

LOGGING_LISTENER=ON

TRACE_LEVEL_SERVER=SUPPORT

SQLNET.INBOUND_CONNECT_TIMEOUT=240

[oracle@linux2 admin]$

Stat the listener process with the command: lsnrctl start

[oracle@linux2 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 19-OCT-2016 14:06:54

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

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 0 hr. 0 min. 0 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 1 instance(s).

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

The command completed successfully

[oracle@linux2 admin]$

Test connect to the new database with command: tnsping cdb1

[oracle@linux2 admin]$ tnsping cdb1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 19-OCT-2016 14:15:55

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 = cdb1) (SID = cdb1)))

OK (0 msec)

[oracle@linux2 admin]$

This completes the creation of CDB in silent mode.

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, 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, CDB

Connection to CDB

July 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. Connections to CDBs can be from the OS layer by supplying the SID via environmental variables or directly and through oracle networking. This procedure demonstrates the connection through both processes.

Connection via OS layer

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 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>

This completes connecting the CDB from OS layer

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 ~]$

List the values in your local tnsnames.ora file to find the appropriate Alias for the CDB you which to connect to with the command: cat $ORACLE_HOME/network/admin/tnsnames.ora.

[oracle@linux2 ~]$ cat $ORACLE_HOME/network/admin/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 ~]$

In this example, we want to connect to cdb1 referenced by CDB1. Supply the sqlplus connection string with the tag: @CDB1 to complete your connection as shown below.

[oracle@linux2 ~]$ sqlplus sys/password@CDB1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 24 12:41:24 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>

This completes connection to CDB via oracle networking.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Startup Single PDB

June 25, 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 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, network

Configure tnsnames.ora file for CDB

May 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 CDB 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.

echo -e “CDB1 =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS_LIST =\n” \

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

” )\n” \

” (CONNECT_DATA =\n” \

” (SERVER = DEDICATED)\n” \

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

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

” )\n” \

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

Execution Example:

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

> ” (DESCRIPTION =\n” \

> ” (ADDRESS_LIST =\n” \

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

> ” )\n” \

> ” (CONNECT_DATA =\n” \

> ” (SERVER = DEDICATED)\n” \

> ” (SERVICE_NAME = “$ORACLE_SID”)\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)

)

As long as the listener process and database is up and running, you can test the connection through the tnsping utility by suppling the reference name in the tnsnames.ora file. In this example the reference name is CDB1, as shown below.

[oracle@linux2 ~]$ tnsping CDB1

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

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 = cdb1) (SID = cdb1)))

OK (0 msec)

[oracle@linux2 ~]$

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

Larry Catt

OCP

Catt Solutions

Category Archives: CDB

Determining SID(s) from Database Server

Creating Common Users in Oracle Multitenant Databases

Users accounts in Multitenant Database

Create CDB in silent mode

Container Clause for Common Users in Oracle Multitenant Databases

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

Connection to CDB

Startup Single PDB

Startup CDB

Configure tnsnames.ora file for CDB

Oracle tips and tricks.