DBConsole 11g (part 3) – “Transparent Tablespace Encryption and Alerting”

0

Triggered a little bit by a post of Christo Kutrovsky from The Pythian Group, this post will be about one of the new features on tablespace level: Transparent Tablespace Encryption

Until now you were able to encrypt a column or table. When a user inserts data into an encrypted column, transparent data encryption automatically encrypts the data. When users select the column, the data is automatically decrypted. After the selection, the data is re-encrypted.

Transparent Tablespace Encryption

Oracle 11g adds now the possibility to encrypt a complete tablespace, instead of only a column or a table. Tablespace encryption makes use of encryption keys in a wallet outside the database.

Creation of encryption keys and control of security can be handled via the Oracle Enterprise Manager DBConsole.

 

Via a login into the local DBConsole, clicking on the server section, you can find under "Security" the URL for "Transparent Data Encryption". Clicking this link the following page is shown.

....

 

I created a new wallet to store keys in a directory I created called c:\oracle\admin\BETA\WALLET.

I manually added the parameter ENCRYPTION_WALLET_LOCATION in my sqlnet.ora file

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\Oracle\admin\BETA\WALLET)))

Be aware that the wallet is closed after a restart of the database…

To manually create and open the master you issue the following command:

connect / as sysdba<br />alter system set key identified by &quot;[Your Password Goes Here]&quot;;

To open the wallet (ewallet.p12)after database startup, you can issue the following command:

alter system set wallet open identified by &quot;[Your Password]&quot;;

Now you can create a secured tablespace via the tablespaces overview page (via the "server" section and then under "storage" or direct via the URL on the "Transparent Data Encryption" page).

Click on the tablespaces page on the "create" button.
 
In the general tab enter (at least) a name for the tablespace ("SECURED") and add a datafile ("secure_01.dbf").

Now click now on "Encryption Options". In this screen you can choose for an Encryption Algorithm. I didn’t change anything and used the default value "AES192" and then clicked continue.


 
Back in the "tablespace" section I checked the "Encryption" check box.

Always interested in the manual stuff, before I clicked on the OK-button, I checked "Show SQL".

The interesting addition here is, of course, the string

ENCRYPTION USING 'AES192' DEFAULT STORAGE (ENCRYPT)

Clicking back in the "Tablespace" section on the OK-button created the enabled encryption tablespace SECURED.

Easy.

DBMS_SERVER_ALERT

When now zooming into specific tablespace level, in my case I zoomed in on the just created "SECURED" tablespace, I noticed that this page has 3 tabs in "edit" mode. Two of them I knew, the other one was new for me…

So I clicked on the "Thresholds" link and was presented with the following screen.

Default values checked are the "Use Database Default Thresholds" for "Space Used (%)" and "Free Space (MB)". I altered both thresholds, so that would enable me to see the SQL statements.

Setting both thresholds on value "disabled" gave me the following SQL statements.

As you could see in the thresholds section there is also a button to change default database behavior. Clicking on the "Modify" button gave me the following page.

So what does this DBMS_SERVER_ALERT do?

In the (beta) documentation it is explained as follows:

Oracle 11g can create Server Generated Alerts, for example for "physical reads per second" or the ones shown before (space used / free space). The can be based thresholds or triggered by an event, for example "snapshot to old". When an event or a threshold is triggered:

An alert message is sent to the predefined persistent queue ALERT_QUE owned by the user SYS. Oracle Enterprise Manager reads this queue and provides notifications about outstanding server alerts, and sometimes suggests actions for correcting the problem.

The alerts are displayed on the Enterprise Manager Database Home page and can be configured to send email or pager notifications to selected administrators. If an alert cannot be written to the alert queue, a message about the alert is written to the Oracle Database alert log.

Background processes periodically flush the data to the Automatic Workload Repository to capture a history of metric values. The alert history table and ALERT_QUE are purged automatically by the system at regular intervals.

Via DBMS_SERVER_ALERT.SET_THRESHOLD (as seen in the screenshots) you are manually able to set or disable these thresholds.

The DBMS_SERVER_ALERT package is described in the database as:

SQL&gt; desc DBMS_SERVER_ALERT

FUNCTION EXPAND_MESSAGE RETURNS VARCHAR2<br />&nbsp;Argument Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; In/Out Default?<br />&nbsp;------------------------------ ----------------------- ------ --------<br />&nbsp;USER_LANGUAGE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;MESSAGE_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;ARGUMENT_1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;ARGUMENT_2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;ARGUMENT_3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;ARGUMENT_4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;ARGUMENT_5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN

PROCEDURE GET_THRESHOLD<br />&nbsp;Argument Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; In/Out Default?<br />&nbsp;------------------------------ ----------------------- ------ --------<br />&nbsp;METRICS_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;WARNING_OPERATOR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OUT<br />&nbsp;WARNING_VALUE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OUT<br />&nbsp;CRITICAL_OPERATOR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OUT<br />&nbsp;CRITICAL_VALUE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OUT<br />&nbsp;OBSERVATION_PERIOD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OUT<br />&nbsp;CONSECUTIVE_OCCURRENCES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OUT<br />&nbsp;INSTANCE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;OBJECT_TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;OBJECT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN

PROCEDURE SET_THRESHOLD<br />&nbsp;Argument Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; In/Out Default?<br />&nbsp;------------------------------ ----------------------- ------ --------<br />&nbsp;METRICS_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;WARNING_OPERATOR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;WARNING_VALUE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;CRITICAL_OPERATOR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;CRITICAL_VALUE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;OBSERVATION_PERIOD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;CONSECUTIVE_OCCURRENCES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;INSTANCE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;OBJECT_TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BINARY_INTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN<br />&nbsp;OBJECT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN

FUNCTION VIEW_THRESHOLDS RETURNS THRESHOLD_TYPE_SET

Using the VIEW_THRESHOLDS function shows:

SQL&gt; select DBMS_SERVER_ALERT.VIEW_THRESHOLDS() from dual;

DBMS_SERVER_ALERT.VIEW_THRESHOLDS()(OBJECT_TYPE, OBJECT_NAME, METRICS_ID, INSTAN<br />--------------------------------------------------------------------------------<br />THRESHOLD_TYPE_SET(THRESHOLD_TYPE(5, NULL, 9001, 'database_wide', 1, 3, '0', 3,<br />'0', 1, 1, 2147483647), THRESHOLD_TYPE(1, NULL, 2018, 'beta4', 1, 4, '100', NULL<br />, NULL, 1, 2, 0), THRESHOLD_TYPE(1, NULL, 2119, 'beta4', 1, 0, '90', 0, '97', 1,<br />&nbsp;3, 0), THRESHOLD_TYPE(1, NULL, 2104, 'beta4', 1, 0, '1200', NULL, NULL, 1, 3, 0<br />), THRESHOLD_TYPE(9, NULL, 4000, 'beta4', 1, 0, '0', NULL, NULL, 1, 15, 0), THRE<br />SHOLD_TYPE(4, 'Administrative', 1000, 'beta4', 1, 0, '10', NULL, NULL, 1, 3, 416<br />6625743), THRESHOLD_TYPE(4, 'Administrative', 1001, 'beta4', 1, 0, '30', NULL, N<br />ULL, 1, 3, 4166625743), THRESHOLD_TYPE(4, 'Application', 1000, 'beta4', 1, 0, '1<br />0', NULL, NULL, 1, 3, 4217450380), THRESHOLD_TYPE(4, 'Application', 1001, 'beta4<br />', 1, 0, '30', NULL, NULL, 1, 3, 4217450380), THRESHOLD_TYPE(4, 'Cluster', 1000,<br />&nbsp;'beta4', 1, 0, '30', NULL, NULL, 1, 3, 3871361733), THRESHOLD_TYPE(4, 'Cluster'<br />, 1001, 'beta4', 1, 0, '50', NULL, NULL, 1, 3, 3871361733), THRESHOLD_TYPE(4, 'C<br />ommit', 1000, 'beta4', 1, 0, '30', NULL, NULL, 1, 3, 3386400367), THRESHOLD_TYPE<br />(4, 'Commit', 1001, 'beta4', 1, 0, '50', NULL, NULL, 1, 3, 3386400367), THRESHOL<br />D_TYPE(4, 'Concurrency', 1000, 'beta4', 1, 0, '10', NULL, NULL, 1, 3, 3875070507<br />), THRESHOLD_TYPE(4, 'Concurrency', 1001, 'beta4', 1, 0, '30', NULL, NULL, 1, 3,<br />&nbsp;3875070507), THRESHOLD_TYPE(4, 'Configuration', 1000, 'beta4', 1, 0, '10', NULL<br />, NULL, 1, 3, 3290255840), THRESHOLD_TYPE(4, 'Configuration', 1001, 'beta4', 1,<br />0, '30', NULL, NULL, 1, 3, 3290255840), THRESHOLD_TYPE(4, 'Network', 1000, 'beta<br />4', 1, 0, '10', NULL, NULL, 1, 3, 2000153315), THRESHOLD_TYPE(4, 'Network', 1001<br />, 'beta4', 1, 0, '30', NULL, NULL, 1, 3, 2000153315), THRESHOLD_TYPE(4, 'Other',<br />&nbsp;1000, 'beta4', 1, 0, '10', NULL, NULL, 1, 3, 1893977003), THRESHOLD_TYPE(4, 'Ot<br />her', 1001, 'beta4', 1, 0, '30', NULL, NULL, 1, 3, 1893977003), THRESHOLD_TYPE(5<br />, NULL, 9000, 'database_wide', 1, 4, '85', 4, '97', 1, 1, 2147483647))

Checking default values in DBA_THRESHOLDS shows

SQL&gt; desc DBA_THRESHOLDS<br />&nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Null?&nbsp;&nbsp;&nbsp; Type<br />&nbsp;----------------------------------------- -------- ------------------<br />&nbsp;METRICS_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(64)<br />&nbsp;WARNING_OPERATOR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(12)<br />&nbsp;WARNING_VALUE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(256)<br />&nbsp;CRITICAL_OPERATOR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(12)<br />&nbsp;CRITICAL_VALUE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(256)<br />&nbsp;OBSERVATION_PERIOD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER<br />&nbsp;CONSECUTIVE_OCCURRENCES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER<br />&nbsp;INSTANCE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(16)<br />&nbsp;OBJECT_TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(64)<br />&nbsp;OBJECT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(513)<br />&nbsp;STATUS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(7) 

SQL&gt; set pages 5000<br />SQL&gt; set lines 80<br />SQL&gt;<br />SQL&gt; col metrics_name for a35<br />SQL&gt; col warning_value for a7<br />SQL&gt; col critical_value for a8<br />SQL&gt; col consecutive_occurrences for 999<br />SQL&gt;<br />SQL&gt; SELECT metrics_name<br />&nbsp; 2&nbsp; ,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; warning_value<br />&nbsp; 3&nbsp; ,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; critical_value<br />&nbsp; 4&nbsp; ,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; consecutive_occurrences<br />&nbsp; 5&nbsp; FROM&nbsp;&nbsp; DBA_THRESHOLDS<br />&nbsp; 6&nbsp; where&nbsp; instance_name='database_wide'<br />&nbsp; 7&nbsp; and&nbsp;&nbsp;&nbsp; status='VALID'<br />&nbsp; 8&nbsp; ;

METRICS_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WARNING CRITICAL CONSECUTIVE_OCCURRENCES<br />----------------------------------- ------- -------- -----------------------<br />Tablespace Bytes Space Usage&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />Tablespace Space Usage&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 85&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 97&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1

2 rows selected.

Conclusion

Oracle 11g now allows us to encrypt on tablespace level. I think this is a very welcome asset, one that DBA people will like: it will make Transparent Encryption easier to maintain. Be aware that the alerting mechanism, via DBMS_SERVER_ALERT, has more features then described in this small post. Applying them can make life easier. In general, features come with a cost and you will have to decide if they are appropriate for your situation. For example, the encryption and decryption process will spend CPU time.

Disclaimer

The information demonstrated and shared here is based on Oracle beta software. The following is intended to outline Oracle’s general product direction.  It is intended for information purposes only, and may not be incorporated into any contract.  It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.  The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

 

 

Share.

About Author

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance. He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

Comments are closed.