Chapter 6. Common Tasks when Managing Container Database (CDB) and Pluggable Databases (PDB)
This section describes tasks that are commonly used when dealing with a CDB and PDBs. The tasks covered within this section are as follows:
- Connect to a CDB
- Connect to a PDB
- Managing a CDB
- Managing a PDB
- Location of Data files in a CDB & PDB
6.1. Connect to a CDB
As the oracle user:
Set the environment variable for ORACLE_HOME with the location of the Oracle home. This reference environment sets ORACLE_HOME to /u01/app/oracle/product/12.2.0/dbhome_1
$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1 $ echo $ORACLE_HOME /u01/app/oracle/product/12.2.0/dbhome_1
NoteAs a precaution, ensure not to include a trailing forward slash (/) when exporting the ORACLE_HOME.
Set the Oracle System ID (ORACLE_SID) used to identify the CDB database.
$ export ORACLE_SID=cdb $ echo ORACLE_SID cdb
Invoke the
sqlplusbinary to log into the Oracle instance as sysdba.$ $ORACLE_HOME/bin/sqlplus / as sysdba; SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 5 18:56:49 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Once connected, verify that the instance is connected to the root container, CDB$ROOT with a CON_ID is 1.
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show con_id CON_ID ------------------------------ 1
NoteThe CDB$ROOT connection ID is always set to one.
List all the available services and PDBs within the CDB:
SQL> select name, con_id from v$active_services; NAME CON_ID ------------------------------------------------ orclpdb2 4 SYS$BACKGROUND 1 SYS$USERS 1 cdbXDB 1 orclpdb1 3 cdb 1 6 rows selected.
6.2. Connect to a PDB
The syntax to connect to a PDB varies depending on whether or not there is an entry within the tnsnames.ora file for the PDB.
As the oracle user:
Without an entry to the tnsnames.ora file, the syntax to connect to a PDB named orclpdb1 is as follows:
$ $ORACLE_HOME/bin/sqlplus sys/<password>@localhost:1521/orclpdb1 as sysdba; SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 5 20:20:29 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
The value 1521, represents the Oracle Listener port.
With an entry to the tnsnames.ora file, the syntax to connect to a PDB named orclpdb1 is as follows:
$ $ORACLE_HOME/bin/sqlplus sys/<password>@PDB1 as sysdba;
A snippet of the entry found within the tnsnames.ora file is displayed below:
$ORACLE_BASE/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <FQDN_hostname>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclpdb1) ) )
6.3. Managing a CDB
The process of starting and shutting down a CDB database is similar to the steps done in previous Oracle database versions for traditional databases. The key difference is to verify that the connection is to the root container prior to shutting down or starting up the Oracle database.
As the oracle user:
-
Connect to the CDB database as a SYSDBA using
sqlplus. The steps are the same as shown in <connect_cdb>> steps one through three. Once connected, verify the instance is the root container CDB$ROOT:
SQL> show con_name; CON_NAME ------------------------------ CDB$ROOT
Shutdown the Oracle CDB database:
SQL> shutdown immediate;
Start the Oracle CDB database:
SQL> startup;
The startup command starts the instance, mounts the control files, and then opens the root container.
6.4. Managing a PDB
This section focuses on verifying the OPEN_MODE of a PDB, how to open and close a specific PDB, and how to open and close all PDBs within a CDB.
As the oracle user:
Verify the open_mode status of all the PDBs, while logged in as a SYSDBA in the CDB, use the following command
NAME OPEN_MODE -------------------------------------- PDB$SEED READ ONLY ORCLPDB1 MOUNTED ORCLPDB2 MOUNTED
When a PDB is closed, the OPEN_MODE is set to MOUNTED. To open a PDB and verify the new OPEN_MODE of READ WRITE, run the following SQL syntax while logged in as a SYSDBA in the CDB:
SQL> alter pluggable database orclpdb1 open; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------------------------- PDB$SEED READ ONLY ORCLPDB1 READ WRITE ORCLPDB2 MOUNTED
Open all the PDBs connected to a CDB and verify the new OPEN_MODE of READ WRITE, run the following SQL syntax while logged in as a SYSDBA in the CDB:
SQL> alter pluggable database all open; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------------------------- PDB$SEED READ ONLY ORCLPDB1 READ WRITE ORCLPDB2 READ WRITE
To drop a particular PDB i.e. orclpdb2, and its data files, execute the following SQL syntax while logged in as a SYSDBA in the CDB:
SQL> alter pluggable database orclpdb2 close immediate; Pluggable database altered. SQL> drop pluggable database orclpdb2 including datafiles; Pluggable database dropped.
To verify if the pluggable database with the name orclpdb2 has been dropped:
SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------------------------- PDB$SEED READ ONLY ORCLPDB1 READ WRITE
6.5. Location of Data files of PDB & CDB
The following section shows how to identify tablespace names, data files associated with the CDB and PDBs, including their temporary files.
- Connect to the CDB database as a SYSDBA using sqlplus. The steps are the same as shown in Section 6.1, “Connect to a CDB” steps one through three.
To identify the tablespaces associated with the CDB or any of the PDBs installed, use the following syntax where the
con_idvaries upon the database chosen. The example below uses thecon_idof 1 to show the CDB tablespaces.SQL> select tablespace_name, con_id from cdb_tablespaces where con_id =1; TABLESPACE_NAME CON_ID ------------------------------ ---------- SYSTEM 1 SYSAUX 1 UNDOTBS1 1 TEMP 1 USERS 1
To locate the data files from the CDB or PDBs installed, use the following syntax where the
con_idvaries upon the database chosen. The example below uses thecon_idof 1 to show the CDB data file locations.SQL> select file_name, con_id from cdb_data_files where con_id=1; FILE_NAME CON_ID ----------------------------------------------------- ----------- +DATA/CDB/DATAFILE/system.270.836232071 1 +DATA/CDB/DATAFILE/sysaux.273.836232077 1 +DATA/CDB/DATAFILE/undotbs1.262.836232081 1 +DATA/CDB/DATAFILE/users.275.836232097 1
To locate the temporary files from the CDB or PDBs installed, use the following syntax where the
con_idvaries upon the database chosen. The example below uses thecon_idof 1 to show the CDB data file locations.SQL> select file_name, con_id from cdb_temp_files where con_id =1 ; FILE_NAME CON_ID ----------------------------------------------------- ----------- +DATA/CDB/TEMPFILE/temp.278.836232081 1
Where did the comment section go?
Red Hat's documentation publication system recently went through an upgrade to enable speedier, more mobile-friendly content. We decided to re-evaluate our commenting platform to ensure that it meets your expectations and serves as an optimal feedback mechanism. During this redesign, we invite your input on providing feedback on Red Hat documentation via the discussion platform.