Cloning an Oracle Database with APEX applications

About a year ago I asked a question about cloning Oracle Databases in the APEX section of the Oracle Community. The question is here (no need to click the link, all of the content is also in this blog post):

https://community.oracle.com/thread/3786014

I haven't received much traction, so I'm reformulating it as a recommendation. Perhaps I'll get more feedback in this form. I'll also point out an element I think is a bug in APEX...you'll have to read to the end to see that.

It is a common practice to clone a production database and utilize the clone for testing or development purposes. This happens a great deal with eBusiness Suite implementations, but also with many other installations. Below is a sample list of steps (with limited guidance) that should be done to avoid side effects with APEX applications.

1. Use RMAN or your favorite technology to backup and restore the production database, but DO NOT START.
2. Change the database SID and name if not done above.
3. Set JOB_QUEUE_PROCESSES to 0.  This is a key step to make sure that when you start the database things don't start "happening."
4. Start the database.
5. Assuming you are running a runtime only environment in Production, you will likely want to install the APEX builder in this new clone. Run apxdvins.sql to upgrade the runtime into a full development environment.
6. Log into the INTERNAL workspace and modify instance settings: Instance URL, Image URL, SMTP server settings (if you wish to use a different SMTP server), Print Server settings, any other settings you want.
7. Navigate to the Manage Instance > Mail Queue and delete anything in the queue. The clone may have happened while things were in the queue.
8. Manage Instance > Interactive Report Descriptions: Delete all of the Interactive Report subscriptions. This is also a key step to ensure that you don't have emails going out to production users from your development or test environment.
9. Manage Instance > Session State: Purge all session state. There could be sensitive production data that you don't want left around in session state.
10. Modify any settings specific to your own applications, e.g. web service URLs, lookup values, etc.
11. Reset JOB_QUEUE_PROCESSES to appropriate value.

It would also be great to have Oracle provide a script that does the above things (with the exception of #10, of course).

I promised to call out a bug. Item #7 should delete all of the interactive report subscriptions, but it doesn't--at least not in APEX v5.0.4.00.12. The list of report subscriptions skips any application

where build_status = 'RUN_AND_HIDDEN'

This is (at least) packaged applications that have not been "unlocked." It turns out that deleting these subscriptions is NOT EASY. I originally thought a script like this might do it:

-- this would need to be done in each workspace because it uses the workspace views
begin

  for irRec in (select notify_id from APEX_APPLICATION_PAGE_IR_SUB) loop
  
    APEX_IR.DELETE_SUBSCRIPTION(
      p_subscription_id =>  irRec.notify_id);
    
  end loop;

end;

Unfortunately, APEX_APPLICATION_PAGE_IR_SUB doesn't see the subscriptions. It has the same issue as the page--it won't show subscriptions for applications with build_status = 'RUN_AND_HIDDEN'.

I tried a few other things, but in the end, the only way I could get rid of these was to just delete them from the underlying table, run as the APEX_050000 schema:

delete from wwv_flow_worksheet_notify;

Query to find users granted an ACL -- the natural question after seeing ORA-24247

You may have encountered ORA-24247: network access denied by access control list (ACL) and wondered "who has access to what from my database?" I extended a query from the Oracle documentation to give me the results I wanted: ACL Name, Username, host, lower port, upper port, and if granted connect and resolve.

with privs as (
  SELECT acl,  u.username, host, lower_port, upper_port, 
     DECODE(
         DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, u.username, 'connect'),
            1, 'GRANTED', 0, 'DENIED', null) conn_privilege,
     DECODE(
         DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, u.username, 'resolve'),
            1, 'GRANTED', 0, 'DENIED', null) res_privilege       
     FROM dba_network_acls a, dba_users u
     )
select *
  from privs
  where conn_privilege is not null
     or res_privilege is not null
  order by acl, username
     ;


It's nothing special, but can be a handy query.

Super Quick Oracle REST Service with OAuth2 and client_credentials

I had the need to allow system A to talk to system B via a REST service. The data was sensitive and powers above me requested that system A use OAuth2 to connect to system B. This REST service call does not involve an end user, it's system A pulling a CSV extract from system B. There are many ways to protect this, but the decision was to use OAuth2. Below is a cookbook on how to do this. This example assumes you have ORDS 3.x installed with the ORDS_PUBLIC_USER and ORDS_METADA schemas configured.

I am running all commands as the user ANTON.

1. For this example I will create a data source for our query:
   -- create the table in the ANTON database schema
   create table anton_table (c1 varchar2(500), c2 varchar2(500) );
   
   -- insert some sample data
   insert into anton_table (c1, c2)
     select owner, table_name
       from all_tables
       where rownum <= 20;
   
   -- commit
   commit;
   
2. Enable REST on the ANTON schema:
   
   DECLARE
     PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
   
       ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                          p_schema => 'ANTON',
                          p_url_mapping_type => 'BASE_PATH',
                          p_url_mapping_pattern => 'anton',
                          p_auto_rest_auth => FALSE);
      
       commit;
   
   END;
   
   -- check to see that it worked
   select id, parsing_schema from user_ords_schemas;
   
   
3. Define a REST Module:
   
   begin
  ORDS.DEFINE_MODULE(
   p_module_name    => 'antonModule',
   p_base_path      => '/antonmodule',
   p_items_per_page => 25,
   p_status         => 'PUBLISHED',
   p_comments       => NULL );

  commit;
end;


  
4. Define a template. This is a URL pattern associated with the module "antonModule." Bind variables are contained in squiggly brackets: {bindVariableName}.
   
   

   begin  
     ORDS.DEFINE_TEMPLATE(
      p_module_name => 'antonModule',
      p_pattern     => '/sqltest/{abc}/{def}',
      p_priority    => 0,
      p_etag_type   => 'HASH',
      p_etag_query  => NULL,
      p_comments    => NULL );
   
     commit;
   end;
   
   This is interesting so take note! Notice my bind variables are abc and def. I tried using c1 and c2 but it seems that the bind variable names can not have numerals in them. Developer beware!
   
   
   
   

5. Define a REST Handler based upon a sql query that takes two bind variables (abc and def) and returns a CSV file:
   
   begin  
     ORDS.DEFINE_HANDLER(
      p_module_name => 'antonModule',
      p_pattern     => '/sqltest/{abc}/{def}',
      p_method      => 'GET',
      p_source_type => ords.source_type_csv_query,
      p_source      => q'[select c1, c2 from anton_table where c1 = :abc and c2 = :def  ]',
      p_items_per_page  => 25,
      p_mimes_allowed   => NULL ,
      p_comments  => NULL );  
   
     commit;
   end;
   
   This is worth repeating: Notice my bind variables are abc and def. I tried using c1 and c2 but it seems that the bind variable names can not have numerals in them. Developer beware!
   
6. At this point you can test your service:
   
   http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON_TABLE
   
   
7.  Define a privilege to protect it with OAuth2:
   
   declare
   
   l_roles       owa.vc_arr;
   l_patterns    owa.vc_arr;
   l_modules     owa.vc_arr;
   begin
    
     -- l_roles intentionally left empty
    
     -- populate arrays
     l_modules(1) := 'antonModule';
    
   
     ORDS.DEFINE_PRIVILEGE(
     p_privilege_name  => 'antonpriv',
     p_roles           => l_roles,
     p_patterns        => l_patterns,
     p_modules         => l_modules,
     p_label           => 'antonTestingPriv',
     p_description     => 'anton testing priv',
     p_comments        => null);
   
     commit;
   
   end; 
   
8. Now you will find it protected:
   
   http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON_TABLE
   
9. Create a client that is allowed to access it:
   
   begin
     OAUTH.create_client(
      p_name            => 'antonclient',
      p_grant_type      => 'client_credentials',
      p_owner           => 'anton',
      p_description     => NULL,
      --p_origins_allowed => NULL, -- param name depends on ORDS version
      p_allowed_origins => NULL,   -- param name depends on ORDS version
      p_redirect_uri    => 'http://localhost:8080/redirect',
      p_support_email   => 'anton@somewhere.com',
      p_support_uri     => 'http://localhost:8080/support',
      p_privilege_names => 'antonpriv');
     
      commit;
   end;
   
10. Get the client_id and client_secret. You will need to log in as a user that has access to select from the ords_metadata tables (e.g. ORDS_METADATA or SYSTEM).
    
    select * from ords_metadata.oauth_clients;
    
11. If you want to be able to do this from an http (not https) URL (which you should NEVER do in production--this is just for testing!!):
    
    -- turn off need for SSL
    1.    Locate the folder where the Oracle REST Data Services configuration is stored.
    2.    Edit the file named defaults.xml.
    3.    Add the following setting to the end of this file just before the </properties> tag.
    4.    <entry key="security.verifySSL">false</entry>
    5.    Save the file.
    6.    Restart Oracle REST Data Services if it is running.
    
12. Test getting a bearer token
    
    curl -i -d "grant_type=client_credentials" --user "[client_id]:[client_secret]" http://localhost:8080/ords/anton/oauth/token
    
    You should receive a response like this:
    
    HTTP/1.1 200 OK
    Content-Type: application/json
    X-Frame-Options: SAMEORIGIN
    Transfer-Encoding: chunked
    
    {"access_token":"Zc9b9HDoP9rUKB189Bf1Yg..","token_type":"bearer","expires_in":3600}
    
    
    Notes:
    -- curl is a tool for making http(s) requests from the command line
    --  -i allows you do to this as http -- though you SHOULD use https
    --  -d allows you to pass in data
    --  --user allows you to pass a user:password for basic authentication
    --  then pass the appropriate URL to get a bearer token
    
13. Test getting your CSV
    
    curl -i --header "Authorization: Bearer [token from step 10]" http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON_TABLE --output anton.csv

LOBs Over a Database Link

There are lots of pluses and minuses to db links, but they are certainly easy and used in the right context they work very well. I admit that I sometimes use them when there is a better technical solution--just because it is so easy and the better solution may not be worth the time.

The case of LOBs over db links can be tricky. You can't select a lob over a db link in SQL or PL/SQL:

select my_blob
  from my_table@mylink;

results in

ORA-22992: cannot use LOB locators selected from remote tables

There are several techniques that work. You CAN do

insert into my_local_table (the_blob)
  select my_blob
  from my_table@mylink;

There is another interesting technique here:

https://technology.amis.nl/2012/07/02/select-a-blob-across-a-database-link-without-getting-ora-22992/


We recently had a requirement to just show the first few hundred characters of a lob over a db link. It was a complicated query and the developer wrote something like this:


select local.c1, local.c2, remote.c3
    , dbms_lob.substr(remote.my_blob, 200, 1) blob200
  from local_table local
  inner join remote_table@mylink remote on remote.c2 = local.c2;

This worked fine in the development and test environment. In production it gave the ORA-22992. It depended on how the optimizer chose to run the query. If the dbms_lob.substr ran on the remote database it was fine, but if it had to pull the blob to the local db it was a problem.


We solved it by forcing the the dbms_lob.substr to run on the remote node:

select local.c1, local.c2, remote.c3
    , dbms_lob@mylink.substr(remote.my_blob, 200, 1) blob200
  from local_table local
  inner join remote_table@mylink remote on remote.c2 = local.c2;

apex_web_service.make_rest_request not working with POST

I recently encountered a web service that I was unable to use with POST and apex_web_service. I was using a statement like this:

declare
    l_clob       CLOB;
BEGIN

    l_clob := apex_web_service.make_rest_request(
        p_url => 'http://myMachine/myService',
        p_http_method => 'POST',
        p_parm_name => apex_util.string_to_table('param1:param2'),
        p_parm_value => apex_util.string_to_table('xyz:xml'));

END;

I've used this many times in the past, but this particular service would not recognize the parameters passed in p_parm_name and p_parm_value. I was able to use curl with the same transaction.

curl -X POST -d "param1=xyz&param2=xml" http://myMachine/myService

I must say, it was VERY frustrating. I finally enabled full logging on Apache using mod_dumpio. 

(Apache 2.4.x)
# uncomment
LoadModule dumpio_module modules/mod_dumpio.so

# add
    <IfModule dumpio_module>
#LogLevel debug
LogLevel dumpio:trace7   # Apache 2.4
DumpIOInput On
DumpIOOutput On
#DumpIOLogLevel debug   # does not work in 2.4
    </IfModule>

I reviewed the logs to find out if apex_web_service.make_rest_request was doing something different than curl. I say "if," but clearly something had to be different. In the logs I found this line from curl but not from apex_web_service:

mod_dumpio:  dumpio_in (data-HEAP): Content-Type: application/x-www-form-urlencoded\r\n

I was able to get things working by adding the Content-Type header as shown below.


declare
    l_clob       CLOB;
BEGIN
  apex_web_service.g_request_headers(1).name := 'Content-Type';
  apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded'; 

    l_clob := apex_web_service.make_rest_request(
        p_url => 'http://myMachine/myService',
        p_http_method => 'POST',
        p_parm_name => apex_util.string_to_table('param1:param2'),
        p_parm_value => apex_util.string_to_table('xyz:xml'));

END;


I hope this helps someone!