Monitoring Autonomous Databases

Recently had a connection issue with Apex via an autonomous database build. Raised an SR with Oracle and was fixed pretty quick.

Since no server backend, only option is to use sqlplus to read the alert log, this worked :

SELECT ORIGINATING_TIMESTAMP, MESSAGE_LEVEL, MESSAGE_TEXT, PROBLEM_KEY
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%ORA-%' AND ORIGINATING_TIMESTAMP > sysdate-7
ORDER BY ORIGINATING_TIMESTAMP DESC;

Adjust as approp. Will add more to this post as time goes on.

Autonomous database - database link

Quick notes on creating a database link in an autonomous database.

Please note, 1521/tcp can be used for the connection and a tcps connection is only needed for a connection to a database with a public endpoint. Spent an ages trying to setup a tcps connection using a wallet (battle for another day) when figured 1521 would suffice. On searching the web, lots of sites mentioning tcps but for the database I was connecting to 1521 worked fine. Just create the credential and the link. 

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'TEST_CRED',
username => 'TESTUSER',
password => 'pass123'

);
END;

BEGIN
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
db_link_name => 'TEST_LINK',
hostname => 'test-dbs.oci.test.co.uk',
port => '1521',
service_name => ''test2.s003.ldntiertest.oraclevcn.com,
ssl_server_cert_dn => NULL,
credential_name => 'TEST_CRED',
directory_name => NULL,
public_link => TRUE,
private_target => TRUE);
END;

PL/SQL multi values example

Wanted to compare a sequence value with a max value in a corresponding table.

The declare -n option not available in the bash shell on the server (surprised as new OCI build and thought bash would be greater than 4.2) but this worked in ksh. 

#!/bin/ksh

monitor=(
[sys1]=(seqname="SEQ_SAMPLE_KEY0" tablename="SAMPLE" rowname="ID_NUMERIC" )
[sys2]=(seqname="SEQ_TEST_TEST_NUMBER" tablename="TEST" rowname="TEST_NUMBER")
[sys3]=(seqname="SEQ_ACCESS_LOG_KEY0" tablename="ACCESS_LOG" rowname="SESSION_ID")
[sys4]=(seqname="SEQ_AUDITOR_DATA" tablename="AUDIT_DATA" rowname="DATA")
[sys5]=(seqname="SEQ_AUDITOR_EVENT" tablename="AUDIT_EVENT" rowname="EVENT")
[sys6]=(seqname="SEQ_AUDITOR_TRANSACTION" tablename="AUDIT_TRANSACTION" rowname="TRANSACTION")
)

for sys in ${!monitor[*]}; do
exit | sqlplus -silent user/password@TESTDB << EOF

set head off
set feedback off
set lines 200
set serveroutput on

DECLARE

x integer;
y integer;

CURSOR cur1 is select last_number from dba_sequences where sequence_name = '${monitor[$sys].seqname}';

CURSOR cur2 is select max(${monitor[$sys].rowname}) from ${monitor[$sys].tablename};

BEGIN

OPEN cur1;
OPEN cur2;

FETCH cur1 into x;
FETCH cur2 into y;

dbms_output.put_line ('${monitor[$sys].seqname}' || ' is ' || x || ' but max(' ||'${monitor[$sys].rowname}' || ')' || ' from '||'${monitor[$sys].tablename}' ||' is ' || y );

IF (x > y) then dbms_output.put_line('sequence is greater');
IF (x < y) then dbms_output.put_line('sequence is lower');
END IF;
ELSE
dbms_output.put_line('values the same');
END IF;

CLOSE cur1;
CLOSE cur2;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

END;
/
EOF

done

SEQ_SAMPLE_KEY0 is 2867468 but max(ID_NUMERIC) from SAMPLE is 2867467
sequence is greater
SEQ_TEST_TEST_NUMBER is 22019029 but max(TEST_NUMBER) from TEST is 22019028
sequence is greater
etc....