SCAN Listener info

Have not touched scan listeners for ages so some very quick background commands to show scan listener info. That long took me ages to remember this so hence the note.

[oracle@dbb1 admin]$ srvctl config scan

[oracle@dbb1 admin]$ srvctl config scan_listener

[oracle@dbb1 admin]$ srvctl status scan_listener

from the above - 
(ip addresses randomised)

[oracle@dbb1 admin]$ nslookup dbb-scan.s002.ldn.oraclevcn.com

Non-authoritative answer:

Name: dbb-scan.s002.ldn.oraclevcn.com
Address: 10.157.196.159
Name: dbb-scan.s002.ldn.oraclevcn.com
Address: 10.157.196.141
Name: dbb-scan.s002.ldn.oraclevcn.com
Address: 10.157.196.230

Run again - the ip addresses will change i.e order first node "hits" second node

[oracle@dbb1 admin]$ nslookup dbb-scan.s002.ldn.oraclevcn.com

Non-authoritative answer:
Name: dbb-scan.s002.ldngisprd.oraclevcn.com
Address: 10.157.196.141
Name: dbb-scan.s002.ldngisprd.oraclevcn.com
Address: 10.157.196.230
Name: dbb-scan.s002.ldngisprd.oraclevcn.com
Address: 10.157.196.159

Run "ip a" to see ip address info at unix level

[oracle@prdg2gis-dbb1 admin]$ ip a

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;