We all know that besides some interesting performance issues, recovery issues or RCA in a Cluster env. DBA job is pretty much concise to a set of basic tasks AND A HELL LOT OF READING. In this post I’ll try to cover Basic DBA commands to manage a PDB and CDB in Oracle 12c so that we can at least be aware of the syntactic changes in 12c.
Establish a Connection to a CDB/PDB
You connect to the root or a PDB through a database service. When you create a PDB, a default service with the same name as the PDB is created. Using this service name, you can connect to a PDB either with the easy connect syntax or by using the Oracle Net Service name from the tnsnames.ora file. By default when you create a PDB a service with same name is created automatically so there is no need to create this service manually.
If you do not use a service name when connecting to the database the username is resolved in context of the CDB root. To view these details query the CDB_SERVICE view:-
1 | select name, network_name, pdb from cdb_service; |
OR
1 | SHOW CON_NAME |
Now when you are connected with the Database you have to switch between containers, for that you need to know/recall which container you are connected to, for that :-
1 | select sys_contect ('USERENV'), 'CON_NAME') from dual; |
And then you can switch container using:-
1 | ALTER SESSION SET CONTAINER=<CON_NAME>; |
Before proceeding further lets understand a small change in 12c i.e. COMMON USERS & LOCAL USERS. As the name suggests common users (name starts with C##) can switch between containers (including ROOT) whereas local users are restricted to a single PDB.
Proceeding further I assume that you are aware of 12c architecture or have read my earlier post Oracle 12c Architecture.
Syntactic changes in DDLs in 12c
Now we’ll see what are the syntactic change in 12c:-
1) When executing DDL in current container:-
1 | Create user username ...... ....... container = current; |
2) When executing DDL in ALL containers:-
1 | Create user C##username ..... ....... container = ALL; |
Similarly this container clause is used to distinguish between current container or overall across all the containers.
Start up and shut down a CDB/PDB
Starting UP a CDB is similar as it was in 11g, Startup nomount, mount and then open. However the difference in PDB is once you startup CDB the PDBs remain in mount mode unless manually opened.
Hence When you open a CDB, the root is opened, but you need to issue separate statements to open the PDBs, unless you have triggers in place that automatically open the PDBs when you issue the STARTUP DATABASE command.
Now coming to PDB, the open modes of PDB are different to those of CDB or as it were in 11g database and as they have no individual instance of their own it seems logical.
A PDB can be in any of the following modes:
- OPEN READ WRITE: The PDB allows user queries and transactions.
- OPEN READ ONLY: The PDB allows queries but no DMLs that modify data.
- OPEN MIGRATE: In this mode, you can only execute a database up grade script in the PDB.
- MOUNTED: This mode allows access to DBAs. Users can’t make any changes or even view any data.
eg:-
1 2 3 | Alter pluggable database pdb1, pdb4, pdb5 open read write;Alter pluggable database all open read write;Alter pluggable database all except pdb1 close immediate; |
Here you can see the syntactic change with Alter <strong>Pluggable</strong> Database …
When you issue the SHUTDOWN command to shut down a CDB, the following is the sequence of events:
All the PDBs in that CDB are closed.
The CDB is closed.
The CDB is dismounted.
The instance is shut down.
you can check the open_mode for pdbs from:-
1 | select name, open_mode from v$pdbs;</em> |
CON_ID
In 12c most of the data dictionary views have included a column of CON_ID to identify the container in context, CON_ID refers as follows:-
- CON_ID=0 Data pertaining to the entire CDB.
- CON_ID=1 Data pertaining to the root only.
- CON_ID=2 Data pertaining to the seed.
- CON_ID=3-254 Data pertaining to the appropriate PDB.
Managing Tablespace in 12c
In a multitenant database, there’s one set of table spaces in the root container (ROOT$PDB), and each PDB also has its own set of tablespaces. Each PDB will have its own SYSTEM and SYSAUX tablespaces, as well as the usual set of tablespaces for storing application data. The root container’s tablespaces store Oracle-provided metadata, which contains common objects across the CDB, including all of its member PDBs. These common objects are visible in the PDBs through object links to the objects. Object links thus enable object sharing across containers.
The UNDO tablespace is common to all PDBs, and you can’t create a dedicated UNDO tablespace for a PDB. There’s a single active UNDO tablespace per CDB.
To see all the tablespace & File_name in a container query cdb_tablespaces:-
1 2 | select tablespace_name, con_id from cdb_tablespaces;</em>select file_name, con_id from cdb_data_files;</em> |
Create tablespace is same as it was in 11g you just go to the container where you want to create tablespace issue the same create tablespace command and BINGO..!!
However as you can by now logically think, to assign default tablespace to a PDB issue:-
1 | alter pluggable database default tablespace <tablespace_name>;</em> |
Miscellaneous
Let’s review the most useful views for monitoring CDBs and PDBs:
[CDB|DBA]_PDBS: Shows information about the PDBs in a CDB.
CDB_PROPERTIES: Shows the properties of each container.
[CDB|DBA]_PDB_HISTORY: Shows the history of each PDB.
[CDB|DBA]_CONTAINER_DATA: Shows the user-level and object-level
CONTAINER_DATA: attributes specified in the CDB.
[USER|ALL|DBA|CDB]_OBJECTS: Shows information about database objects, with the SHARING column showing the type of links (metadata link or object link).
[ALL|DBA|CDB]_SERVICES: Database service information pertaining to each PDB.
[USER|ALL|DBA|CDB]_VIEWS: Uses the CONTAINER_DATA column to show whether or not a view is a container data object.
[USER|ALL|DBA|CDB]_USERS: The column named COMMON shows whether a user is a common or local user.
[USER|ALL|DBA|CDB]_ROLES: The column COMMON shows whether a role or privilege is commonly or locally granted. You’re likely to use the following five views frequently when managing CDBs and PDBs:
[G|V]$DATABASE: Shows database information.
G|V$CONTAINERS: Shows information about the root and all the PDBs
in the current CDB.G|V$PDBS: Shows information about all PDBs associated with the current CDB.
RC_PDBS: Recovery catalog views show details about PDB backups.
G|V$SYSTEM_PARAMETER: Shows information about initialization parameters
Note the types of information you can view with the DBA_XXX, ALL_XXX, and USER_XXX views in a multitenant database environment:
DBA_XXXThese views show all the objects in the root container or a PDB. If you connect to the root, the views show only objects stored in the root; if you connect to a PDB, they show only objects contained in that PDB.
ALL_XXX These views show all objects that a current user in a PDB can access.
USER_XXXThese views show all objects owned by the current user in a PDB.
Following are some set of queries useful in your basic DBA tasks, would suggest to run these in your 12c env. to understand the output and get familiar with it:-
How to Tell If the Database Is a CDB
1 | select name, cdb, con_id from v$database; |
Viewing Information About Containers
1 | select name, con_id, db_id from v$containers; |
Viewing Information About the PDBs
1 | select pdb_id, pdb_name, status from cdb_pdbs; |
Finding the Open Mode of a PDB
1 | select name, open_mode, restricted, open_time from v$pdbs; |
Viewing the History of PDBs
1 | select db_name, con_id, pdb_name, operation, cloned_from_pdb_name from cdb_pdb_property; |
Viewing all the tablespaces in a CDB
1 | select tablespace_name, con_id from cdb_tablespaces;</em> |
No comments:
Post a Comment