Skip to Main Content
  • Questions
  • Accessing table one from multiple DB servers

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Michael.

Asked: March 22, 2017 - 2:42 pm UTC

Last updated: March 25, 2017 - 3:26 am UTC

Version: 11/12

Viewed 1000+ times

You Asked

We have 100+ Oracle servers with versions 11/12c and Oracle Cloud Control 13c connected to them all.
I was asked to prepare scripts to execute "CIS Oracle 12c benchmark" scripts automatically. Some of them will "harden" the DB, by changing parameters, and some will alert for noncompliance, for example #1.3 - drop default users:

SELECT USERNAME
FROM ALL_USERS
WHERE USERNAME IN ('BI','HR','IX','OE','PM','SCOTT','SH'); 


But we have SAP application which works with user "HR". So we must put the SAP DB server in the exclude list.
We decided to use Cloud Control 13c (CC13c) jobs mechanism to accomplish this task.
I will create "Control config table" in the CC13c server, which will control the "exclude list".
I have encountered an issue with accessing "Control config table" and target DB server.
Each job execution should be started with checking, if the particular DB server is in the "exclude list" or not for current paragraph.

What's the problem? lets use DB_link! - I said, but the answer was NO! Neither creating ANY DB object in target DB is allowed.
I am considering:
1) "one-time" creation of <XXX> directory in each target server for local/shared location
2) export "Control config table" from CC13c to CSV file
3) copy to each target/shared location
4) load the data from CSV file to memory using UTL_FILE

Any advise will be much appreciated!

and Connor said...

1 through 3 sound fine. But in terms of easy loading, I'd just have the config as an xml file, then you can access it directly with SQL, eg


config.xml
==========
<servers>
  <server>
    <hostid>7369</hostid>
    <hostname>host1</hostname>
    <os>WINDOWS</os>
  </server>
  <server>
    <hostid>7499</hostid>
    <hostname>host2</hostname>
    <os>SOLARIS</os>
  </server>
  <server>
    <hostid>7521</hostid>
    <hostname>host3</hostname>
    <os>LINUX</os>
  </server>
</servers>

SQL> with config as
  2   ( select xmltype(bfilename('TEMP','confg.xml'), nls_charset_id('UTF8')) xml_data from dual )
  3  SELECT xt.*
  4  FROM   config x,
  5         XMLTABLE('/servers/server'
  6           PASSING x.xml_data
  7           COLUMNS
  8             "HOSTID"    VARCHAR2(4)  PATH 'hostid',
  9             "HOSTNAME"  VARCHAR2(10) PATH 'hostname',
 10             "OS"        VARCHAR2(9)  PATH 'os'
 11           ) xt;

HOST HOSTNAME   OS
---- ---------- ---------
7369 host1      WINDOWS
7499 host2      SOLARIS
7521 host3      LINUX



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Question

A reader, March 24, 2017 - 7:33 am UTC

Could be possible using say a view and share it for the pdb?
Connor McDonald
March 25, 2017 - 3:26 am UTC

I think they have multiple db's, but yes, if they were pdb's you could centralise certain objects for this purpose.

Reworked solution

Michael Sharfman, March 26, 2017 - 10:42 am UTC

Thanks for the response.
After some internal DBA team discussion we decided to go a bit other way - put all the script into CC13c, where the Control Table resists, and all the checks will be performed via DB_LINK FROM THE CC13c to Target,(first offer was from target to CC13c, to select data from control table) we have convinced the manager :-)

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here