Montag, 13. April 2020

Agent has been blocked manually. Unblock the Agent.

As it's easter weekend (at the time I write this blog) and it's a nice tradition here to hide some small items (often colored eggs or sweets) so other can find them, this story perfectly matches.
In Enterprise Manager 13c I had an agent with status

Agent has been blocked manually. Unblock the Agent.

Unfortunately In the Agents drop down menu, there is no (sub-) entry to unblock the agent. A well hidden entry!
After several attempts I was hinted where to look. It's in the "Setup" => "Manage Cloud Control" => "Agents" area:

There all agents are listed, and when selected the blocked one, it can be unblocked in the top action list.


I did not find a useful entry in the documentation (but that's probably my lack of search-foo). At least there is a Note EM 12c: How to Block or Unblock an Enterprise Manager 12c Cloud Control Agent if Agent Status is Shown as Blocked in EM Console or Emctl Status Agent Command Shows Heartbeat Status : Agent Is Blocked ? (Doc ID 1392601.1)




Mittwoch, 8. April 2020

Γνῶθι σεαυτόν


Sometimes it might be interesting to understand, what's the SQL statement which is currently executed.
In an Oracle instance, for any other session it's quite simple by accessing some v$ views. But from "within" the statement, it's not straight forward. Nevertheless it's possible, by a creative combination of some features.

Here is the example.

First let's create a proper user:

CREATE USER know IDENTIFIED BY "thyself";

GRANT connect, resource    TO know;

GRANT UNLIMITED TABLESPACE TO know;

GRANT    CREATE VIEW       TO know;

GRANT imp_full_database    TO know;

That's slightly more than really needed - so if you evern require this funcitonality, please be careful with required permissions!
In this example, we do not even need a table.
But a Package to keep some variables:

CREATE OR REPLACE PACKAGE other_color_injector AS
-- package OCI
    text_keeper   VARCHAR2(4000 CHAR) := '--';
    current_color VARCHAR2(100 CHAR)  := 'white';
    FUNCTION rls_hook (
        p_schema  IN  VARCHAR2,
        p_object  IN  VARCHAR2
    ) RETURN VARCHAR2;

END other_color_injector;
/

CREATE OR REPLACE PACKAGE BODY other_color_injector AS

    FUNCTION rls_hook (
        p_schema  IN  VARCHAR2,
        p_object  IN  VARCHAR2
    ) RETURN VARCHAR2 AS
        PRAGMA autonomous_transaction;
        chk_color VARCHAR2(100 CHAR);
    BEGIN
        text_keeper := sys_context('userenv', 'CURRENT_SQL');

        -- first word in /* comment 
        chk_color := regexp_substr(regexp_substr(text_keeper, '/\*.*\*'), '[[:alpha:]]+');
        dbms_output.put_line(' SQL Text:' || text_keeper || '---');
        IF chk_color IS NOT NULL THEN
            current_color := chk_color;
        END IF;
        RETURN NULL;
    END;

END other_color_injector;
/

And a function which gives us some feedback about the SQL in which it's called:
CREATE OR REPLACE TYPE rain_tab IS    TABLE OF VARCHAR2(100 CHAR);

create or replace FUNCTION rain_bow 
-- RETURN t_tf_tab 
return rain_tab
PIPELINED AS
BEGIN
    PIPE ROW ( other_color_injector.current_color );   

  RETURN;
END;
/

create or replace view cloud as select column_value as color from rain_bow();


The view is somehow important for the next step whihc puts all the pieces together:


BEGIN
    dbms_rls.add_policy(object_schema =>   'KNOW', 
                        object_name =>     'CLOUD', 
                        policy_name =>     'RAIN', 
                        function_schema => 'KNOW', 
                        policy_function => 'OTHER_COLOR_INJECTOR.RLS_HOOK',
                        statement_types => 'select', 
                        update_check => false);
END;
/

With this objects in place, a beautiful, but maybe confusing result can be created:
Enter user-name: know/thyself@pdb1

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select * from cloud;

COLOR
-------------------------------------------
white

SQL> select /* red */  * from cloud;

COLOR
-------------------------------------------
red

SQL> select * from cloud;

COLOR
-------------------------------------------
red

SQL> select /* green */  * from cloud;

COLOR
-------------------------------------------
green

SQL> select * from cloud;

COLOR
-------------------------------------------
green

SQL>

The important part is this line in OTHER_COLOR_INJECTOR.RLS_HOOK:
text_keeper := sys_context('userenv', 'CURRENT_SQL');

SYS_CONTEXT has access to CURRENT_SQL (and some other useful parameters) -  but only in fine grained audot / RLS events. So all the other objects are required to make this information available in my view rain_bow.

I also created a small twitter quiz where I asked for methods to identify the current SQL and create values based on the SQL.
Rene Jeruschkat suggested a solution based on latest v$sqlstats:


Mathias Rogel suggested to get the latest cursor from v$open_cursor which belongs to the current session:


Both are great solutions and show: there is quite often more than one way to solve a problem!


If you are curious about the greek letters in the title: Γνῶθι σεαυτόν they can be translated to know thyself - something which is often hard - not only for SQL statements.