12 Days of #Cloud: Day 4
DBaaS Monitor Console is part of the Oracle Database Cloud Service. DBaaS Monitor Console provides information on the database and operating system status and resource usage. If you are managing the database with Oracle Enterprise Manager Cloud Control or if you are familiar with Linux commands, then the status reports and resource usage charts in DBaaS Monitor may not be of much use. But the idea of Cloud Database is to minimize your need to access the Linux VM.
Login to the Database Monitor Console using the “dbaas_monitor” username and the administration password for the DBaaS Instance. Before you login, remember to open the HTTPS port 443 or create an SSH port forwarding tunnel. As soon as you login, you get to see an excellent overview of your database instance showing database status, listener status, last backup status, storage usage, memory usage and CPU usage.
As you see in the Database menu, the monitor console gives some useful information that can be consumed by techies and non-techies. I hope in future, we will see more useful reports. Also, hopefully in the future, DBassS monitor console will be enhanced to send alerts on few critical metrics.
- Manage: This is the only menu item where you can make any change to the instance. This is where you manage the PDBs.
- Listener: Listener status and “lsnrctl status” output.
- Storage: CDB and PDB storage summary as well as tablespace-wise.
- Backups: RMAN backup status
- Alerts: Aler log messages in an easy to filter framework.
- Sessions: Database sessions from CDB and PDB.
- Waits: Wait events and affected sessions.
- Parameters: Database parameters
- Real Time SQL Monitor: Status of SQL statements running in the database
The OS menu shows Operating System metrics. The menu is pretty much self explanatory.
OK, that was an introduction to DBaaS Monitor Console.
Let me get to the subject of this blog – Managing PDBs.
Let me choose “Manage” option from the “Database” menu.
The screen shows CDB and all the PDBs in the database and their open status.
At the CDB and PDB, you have a little menu icon to take action on the CDB or PDB. You can also see the option to create a new PDB.
The PDB menu has options to
- Modify State: Open or Close a PDB
- Clone: To duplicate to another PDB
- Unplug: Create XML file and detach the PDB from CDB
- Drop: Delete the PDB
- Connection Details: Shows the database connect details – to connect using SQL*Plus and JDBC.
Before taking action (clone, drop, unplug or modify state), you can click on the “Show SQL” button to verify and understand the SQL statements.
When you are performing the operations, remember to choose the option of “Clone TDE Key” or “Export TDE Key” (depending on the action).
SQL Statements for PDB Clone:
BEGIN
BEGIN
EXECUTE IMMEDIATE 'ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "ABCDEF123#"';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
/
ALTER SESSION SET CONTAINER="PDBHR"
/
BEGIN
BEGIN
EXECUTE IMMEDIATE 'ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "ABCDEF123#"';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
/
ALTER SESSION SET CONTAINER=CDB$ROOT
/
BEGIN
EXECUTE IMMEDIATE 'CREATE PLUGGABLE DATABASE "PDBHRARCHIVE" FROM "PDBHR"
STORAGE UNLIMITED
TEMPFILE REUSE
FILE_NAME_CONVERT=NONE
KEYSTORE IDENTIFIED BY "ABCDEF123#"';
END;
/
ALTER PLUGGABLE DATABASE "PDBHRARCHIVE" OPEN READ WRITE
/
ALTER SESSION SET CONTAINER="PDBHRARCHIVE"
/
ALTER SESSION SET CONTAINER=CDB$ROOT
/
BEGIN
BEGIN
EXECUTE IMMEDIATE 'ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "ABCDEF123#" CONTAINER=ALL';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM V$ENCRYPTION_WALLET';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE "PDBHRARCHIVE" CLOSE IMMEDIATE';
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE "PDBHRARCHIVE" OPEN READ WRITE';
END;
/
HOST rm -f /tmp/dbm-clone-1YWGI.p12
/
SQL statements for Plug in a PDB
BEGIN
EXECUTE IMMEDIATE 'CREATE PLUGGABLE DATABASE "PDBHR2"
AS CLONE
USING ''/home/oracle/pdbhrarchive.xml''
SOURCE_FILE_NAME_CONVERT=NONE
NOCOPY
STORAGE UNLIMITED
TEMPFILE REUSE';
END;
/
ALTER PLUGGABLE DATABASE "PDBHR2" OPEN READ WRITE
/
BEGIN
BEGIN
EXECUTE IMMEDIATE 'ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "ABCDEF123#"';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
/
ALTER SESSION SET CONTAINER="PDBHR2"
/
BEGIN
BEGIN
EXECUTE IMMEDIATE 'ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "ABCDEF123#"';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE IMMEDIATE 'ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "ABCDEF123#" FROM ''/home/oracle/pdbhrarchive.tdekey'' IDENTIFIED BY "ABCDEF123#" WITH BACKUP';
END;
/
ALTER SESSION SET CONTAINER=CDB$ROOT
/
BEGIN
BEGIN
EXECUTE IMMEDIATE 'ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "ABCDEF123#" CONTAINER=ALL';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM V$ENCRYPTION_WALLET';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE "PDBHR2" CLOSE IMMEDIATE';
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE "PDBHR2" OPEN READ WRITE';
END;
/
So, do you have to use DBaaS Monitor Console to manage a PDB graphically? Of course not, SQL Developer is your friend.
And, if you use OEM Cloud Control, you have one more tool to manage PDBs.
Enjoy PDB!
When you create a new Cloud DBaaS instance, Oracle Cloud Service provision a VM and required file systems. These are the file systems:
