Oracle DBA Place

Creating trace file, writing to alert.log and retrieve using Amazon RDS for Oracle

noreply@blogger.com (Yossi Nixon) — Mon, 06 Jan 2020 15:00:00 +0000

Since in Oracle RDS environment, we don't have a direct access to the operating system
I have recently been asked to provide a workaround from the database.
A procedure that:

1. Write to the alertlog
2. Generate a trace file (no matter its content).
3. Let the user know how to query
a. The alertlog
b. The trace file
Oracle RDS implementation enable us this functionality without a need to create objects or have a special grants.

SET SERVEROUTPUT ON VERIFY OFF FEED OFF LINES 300 PAGES 200
DECLARE
full_file_name VARCHAR2 (512);
file_name VARCHAR2 (512);
MESSAGE VARCHAR2 (1024);
ALERT_TABLE VARCHAR2 (30);
rds_exists NUMBER;
TABLE_MISSING EXCEPTION;
PRAGMA EXCEPTION_INIT (TABLE_MISSING, -942);
FUNCTION FIND_VIEW (object_name VARCHAR2)
RETURN NUMBER
IS
BEGIN
EXECUTE IMMEDIATE 'select count(1) from ' || object_name;
RETURN 1;
EXCEPTION
WHEN TABLE_MISSING
THEN
RETURN 0;
END FIND_VIEW;
BEGIN
SELECT COUNT (1)
INTO rds_exists
FROM all_users
WHERE username = 'RDSADMIN';
IF FIND_VIEW ('X$DBGALERTEXT') = 1
THEN
ALERT_TABLE := 'X$DBGALERTEXT';
ELSE
IF FIND_VIEW ('ALERTLOG') = 1
THEN
ALERT_TABLE := 'ALERTLOG';
ELSE
ALERT_TABLE := '/* no acess to alert table X$DBGALERTEXT or ALERTLOG */';
END IF;
END IF;

EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
EXECUTE IMMEDIATE 'ALTER SESSION SET tracefile_identifier = ''&&identifier''';
EXECUTE IMMEDIATE 'ALTER SESSION SET statistics_level = ALL';
EXECUTE IMMEDIATE 'ALTER SESSION SET events ''10046 trace name context forever, level 12''';
EXECUTE IMMEDIATE 'select 1 from dual';
SELECT VALUE
INTO full_file_name
FROM V$DIAG_INFO
WHERE NAME = 'Default Trace File';
MESSAGE := 'Errors in file ' || full_file_name || ':';
file_name :=
SUBSTR (full_file_name,
( INSTR (full_file_name,
'/',
-1,
1)
+ 1),
LENGTH (full_file_name));
sys.DBMS_SYSTEM.ksdind (10);
sys.DBMS_SYSTEM.ksdwrt (sys.DBMS_SYSTEM.alert_file, MESSAGE);
sys.DBMS_SYSTEM.ksdfls;
DBMS_OUTPUT.put_line (CHR (10));
DBMS_OUTPUT.put_line (' ---------- Line will added to alert log -------------------------');
DBMS_OUTPUT.put_line (MESSAGE);
DBMS_OUTPUT.put_line (CHR (10));
DBMS_OUTPUT.put_line ('Query alert.log file using the following command:');
DBMS_OUTPUT.put_line ('------------------------------------------------');
DBMS_OUTPUT.put_line (' SELECT TO_CHAR (ORIGINATING_TIMESTAMP, ''YYYY-MM-DD HH24:MI:SS'') AS ORIGINATING_TIMESTAMP, MESSAGE_TEXT');
DBMS_OUTPUT.put_line (' FROM ' || ALERT_TABLE);
DBMS_OUTPUT.put_line (' WHERE originating_timestamp > (SYSDATE - 5 / 1440)');
DBMS_OUTPUT.put_line ('ORDER BY RECORD_ID;');
DBMS_OUTPUT.put_line (CHR (10));
IF rds_exists = 1
THEN
DBMS_OUTPUT.put_line ('Retrieving the generated trace file (on RDS) using the following command:');
DBMS_OUTPUT.put_line ('----------------------------------------------------------------');
DBMS_OUTPUT.put_line ('SELECT text FROM table(rdsadmin.rds_file_util.read_text_file(''BDUMP'',''' || file_name || ''')) where rownum < 20;');
DBMS_OUTPUT.put_line (CHR (10));
END IF;
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context off''';
END;
/
EXIT

Connect to Oracle Database server through ssh tunnel

noreply@blogger.com (Yossi Nixon) — Mon, 28 Oct 2019 15:00:00 +0000

Last week I needed to connect a database from remote using VPN.
The VPN is connected to a secured network, so I couldn’t connect directly to the database.
Since I am using Toad for Oracle which is based on oracle client – I needed a transparent solution for that connection.

At the past I wrote a post about Using ssh X11 tunnel through a bastion host to connect to a database server , at that post I described how to forward SSH and X11.
Now I will describe a way to connect to the database using SQL*Net (usually port 1521) on Windows.

Download plink from https://www.putty.org , you can download putty package or just standalone plink.
Run the following command line:

plink -N -L localport:dbserver:dbport getwayuser@getwayserver

Do this on both machines (REMOTE PC Windows machine and the server you have access to - Gateway) to chain the SSH tunnels.

Example:

Gateway server (assuming Linux):

ssh -N -L1521:dbserver:1521 dbserveruser@dbserver

Your PC:

plink -N -L 1521:getwayserver:1521 getwayuser@getwayserver

The tnsnames.ora entry must look like you are running a local database, e.g.

dboverssh =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = crm)
)
)

Using ssh X11 tunnel through a bastion host to connect to a database server

noreply@blogger.com (Yossi Nixon) — Tue, 01 Oct 2019 16:00:00 +0000

Hi,

I succeeded running Oracle dbca, xclock & virt-viewer using Bastion over VPN (with root) 😊

Theoretical Steps:

First, connect to machine B and forward [localPort] to C:22 through B

A$ ssh -L [localPort]:C:22 B

Next, connect to C from A through this newly-created tunnel using [localPort], forwarding X11

A$ ssh -X -p [localPort] localhost

Now we can run X11 programs on C and have them display on A

C$ xclock

[localPort] can be any port that you are not already listening to on A, I often use 2222 for simplicity.
X11Forwarding should be enabled on server C
AllowTcpForwarding should be enabled on server B

Actual Steps

First, connect to machine B and forward [localPort] to C:22 through B

A$ ssh -L [localPort]:C:22 B

Next, connect to C from A through this newly-created tunnel using [localPort], forwarding X11
From A using putty to localhost using [localPort] enable X11 forwarding to localhost:0.0
Now we can run X11 programs on C and have them display on A
C using putty:

C$ export DISPLAY=127.0.0.1:10.0
C$ xclock

How to install Oracle Linux in kvm without a need to X server

noreply@blogger.com (Yossi Nixon) — Tue, 24 Sep 2019 16:00:00 +0000

Hi,
I managed to install Oracle Linux on kvm guest without a need to X console (no GUI, vnc or X11)
1. We can see the PXE menu
2. We can select image to install
3. We need to add console=ttyS0 to the PXE (cobbler is supporting this feature the same way as grub enable you to add parameter to the boot)

Disclaimer:
I tested this on Oracle Linux 7.5 using libvirt 1.5

# virt-install --hvm --connect qemu:///system --network=bridge:virbr0 --pxe --graphics none --name Oracle_Linux_7.4-x86_64 --ram=756 --vcpus=1 --os-type=linux --os-variant=rhel7 --disk path=/tmp/rhel7-machine.img,size=5 --console pty,target_type=serial --boot 'menu=on,useserial=on'
Starting install...
Allocating 'rhel7-machine.img' | 5.0 GB 00:00:00
Connected to domain Oracle_Linux_7.4-x86_64
Escape character is ^]
Google, Inc.
Serial Graphics Adapter 04/30/14
SGABIOS $Id: sgabios.S 8 2010-04-22 00:03:40Z nlaredo $ (mockbuild@) Wed Apr 30 12:14:33 UTC 2014
Term: 80x24
4 0
SeaBIOS (version 1.11.0-2.el7)
Machine UUID 705ffc3b-a5ee-41e3-b380-2c0407f74bc2
iPXE (http://ipxe.org) 00:03.0 C100 PCI2.10 PnP PMM+2F394490+2F2F4490 C100

Press ESC for boot menu.
Booting from ROM...
iPXE (PCI 00:03.0) starting execution...ok
iPXE initialising devices...ok
iPXE 1.0.0+ (4e85b27) -- Open Source Network Boot Firmware -- http://ipxe.org
Features: DNS HTTP iSCSI TFTP AoE ELF MBOOT PXE bzImage Menu PXEXT
net0: 52:54:00:dd:23:6f using virtio-net on 0000:00:03.0 (open)
[Link:up, TX:0 TXE:0 RX:0 RXE:0]
Configuring (net0 52:54:00:dd:23:6f)............................... ok
net0: 10.20.2.14/255.255.255.0 gw 10.20.2.254
Next server: 192.168.1.47
Filename: pxelinux.0
tftp://192.168.1.47/pxelinux.0... ok
pxelinux.0 : 16794 bytes [PXE-NBP]
PXELINUX 3.86 2010-04-01 Copyright (C) 1994-2010 H. Peter Anvin et al
!PXE entry point found (we hope) at 9C2C:0160 via plan A
UNDI code segment at 9C2C len 0802
UNDI data segment at 9CB0 len 2CE0
Getting cached packet 01 02 03
My IP address seems to be 0A14020E 10.20.2.14
ip=10.20.2.14:192.168.1.47:10.20.2.254:255.255.255.0
TFTP prefix:
Trying to load: pxelinux.cfg/3bfc5f70-eea5-e341-b380-2c0407f74bc2
Trying to load: pxelinux.cfg/01-52-54-00-dd-23-6f
Trying to load: pxelinux.cfg/0A14020E
Trying to load: pxelinux.cfg/0A14020
Trying to load: pxelinux.cfg/0A1402
Trying to load: pxelinux.cfg/0A140
Trying to load: pxelinux.cfg/0A14
Trying to load: pxelinux.cfg/0A1
Trying to load: pxelinux.cfg/0A
Trying to load: pxelinux.cfg/0
Trying to load: pxelinux.cfg/default

PRESS Tab and add console=ttyS0 at the end

> /images/Oracle_Linux_7.4-x86_64/vmlinuz initrd=/images/Oracle_Linux_7.4-x86_64
/initrd.img ksdevice=bootif lang= kssendmac text ks=http://192.168.1.47/cblr/s
vc/op/ks/profile/Oracle_Linux_7.4-x86_64 BOOTIF=01-52-54-00-56-f5-ff console=tty
S0
Automatic boot in 19 seconds...

Loading /images/Oracle_Linux_6.9-x86_64/vmlinuz.................................
..................................
Loading /images/Oracle_Linux_6.9-x86_64/initrd.img..............................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
..................................................ready.
Probing EDD (edd=off to disable)... ok
.
.
.
[ OK ] Reached target Network is Online.
Starting Notify NFS peers of a restart...
Starting OpenSSH server daemon...
Starting Postfix Mail Transport Agent...
Starting Logout off all iSCSI sessions on shutdown...
[ OK ] Started Notify NFS peers of a restart.
[ OK ] Started Logout off all iSCSI sessions on shutdown.
[ OK ] Started OpenSSH server daemon.
Starting Availability of block devices...
[ OK ] Started Availability of block devices.
[ OK ] Started Dynamic System Tuning Daemon.
[ OK ] Started Postfix Mail Transport Agent.
[ OK ] Started GSSAPI Proxy Daemon.
[ OK ] Reached target NFS client services.
[ OK ] Reached target Remote File Systems (Pre).
[ OK ] Reached target Remote File Systems.
Starting Permit User Sessions...
Starting LSB: Starts the Spacewalk Daemon...
Starting Crash recovery kernel arming...
[ OK ] Started Permit User Sessions.
Starting Terminate Plymouth Boot Screen...
[ OK ] Started Command Scheduler.
Starting Command Scheduler...
[ OK ] Started Job spooling tools.
Starting Job spooling tools...
Starting Wait for Plymouth Boot Screen to Quit...
Oracle Linux Server 7.4
Kernel 4.1.12-94.3.9.el7uek.x86_64 on an x86_64
localhost login:

rlwrap and auto completion in sqlplus

noreply@blogger.com (Yossi Nixon) — Wed, 18 Sep 2019 16:00:00 +0000

It is a while I am following the blog of Mariami Kupatadze, it seems that our background and daily work is around the same area and interest as mine.
This week she posted a simple Linux adjustment (sqlplus backspace – ^H, delete – [[D^, add command history) for better way of using sqlplus.
Since I am also a geek of environment improvements that can ease daily work, I want to add a nice variation.

An example of basic rlwrap usage:

rlwrap sqlplus / as sysdba

or use sqlplus as alias with rlwrap:

alias sqlplus='rlwrap sqlplus'

After some usage you will notice that default history file is created here ~/.sqlplus_history
I usually copy this file to ~/sqlplus.wordlist and edit it with wanted completion word.

My personal file looks this way:

abort
active
affirm
all_roles
alter
alternate
applied
applied_seq#
applied_thread#
apply
apply_finish
apply_lag
archived
archived_seq#
archived_thread#
archiver
async_blocks
attribute
availability
begin
binding
block#
blocks
blocksize
both
bytes
cancel
checkpoint
clear
clob
close
columns
compression
computed
connect
connectdb
convert
count(*)
create
creator
+DATA
database
database_role
datum_time
dba_registry
db_create_file_dest
db_create_online_log_des
dbms_output
db_recovery_file_dest
db_unique_name
declare
decode
delay
delay_mins
desc
descr
dest_id
destination
dest_name
dict
disable
disconnect
distinct
drop
dual
error
escape
estimated
exception
execute
exit
fail_block
fail_date
fail_sequence
failure_count
fal_server
finish
first_change#
flush
force
from
group
group#
gv$instance
gv$log
gv$standby_log
immediate
in-memory
inner
instance_name
instance_role
instanceval
inst_id
into
is_recovery_dest_file
isspecified
join
lag
last_change#
last_time
level
lgwr
like
log_archive_dest
log_archive_max_processes
logfile
log_num_standby
log_size
loop
lpad
managed
max_connections
max_failure
maximize
member
members
message
mount
MRP0
name
net_timeout
next_change#
no_data_found
nomount
null
number
onlinelog
online_logfile
open
open_mode
optional
order
pages
param
parameter
parent_dbun
parent_instance
parent_instancedest_role
pga_aggregate_target
prior
process
protection_level
protection_mode
put_line
recover
redo
register
registrar
reopen
reopen_secs
reset
resetlogs_change#
schedule
scope
select
sequence#
serveroutput
session
sessionval
sga_target
show
shutdown
sid='*'
size
spfile
spool
standby
standby_archive_dest
standby_file_management
standby_file_management='AUTO'
standby_file_management='MANUAL'
start
startup
startup_time
status
stopped
substr
switch
sync
sys_connect_by_path
sysdba
system
table
table_name
target
term
then
thread#
time_computed
timestamp
to_char
to_number
transmit_mode
transport_lag
true
truncate
type
union
unit
used
utl_raw.cast_to_varchar2(hextoraw(value_raw))
valid_for
valid_for=(online_logfile,all_roles)
valid_now
valid_role
valid_type
value
varchar2
v$archive_dest
v$archive_dest_status
v$archived_log
v$archive_gap
v$archive_processes
v$archive_status
v$database
v$dataguard_config
v$dataguard_stats
v$dataguard_status
verify
v$log
v$logfile
v$log_history
v$managed_standby
v$parameter
v$spparameter
v$standby_log
where
while
with
v$dnfs_files
v$dnfs_servers
v$dnfs_channels
@archive_dest
@archived_log
@backup
@dataguard_process
@space
@database
@dataguard_stats
@dataguard_status
@disks
@gap
@managed_standby
@recovery_area_usage
@recovery_progress
@help
@instance
@resize_logs
@asm_orphans
@resize_srls
@resize_logs

Please notice that at the end I added some of my personal scripts.

Next step is to define the sqlplus alias with the created wordlist file and some more options:

alias sqlplus="rlwrap -pBlue -i -b '()=\!<>&+-*|:;,' -f ~/sqlplus.wordlist sqlplus"

-p prompt color (a different color then my bash prompt)
-i Ignore case when completing
-b Consider the specified characters as word-breaking
-f completion word list

Now you can enjoy a prompt with its own color, and the most important - your own words for tab completion.

Bye the way, SQLcl has also "auto-complete object names or keywords using the tab key" and it solves history, backspace and delete problems, but I am not sure about your own words :)

One of the benefits of using rlwrap, is that it enables searching via historical commands using Ctrl+R this way:

At the sqlplus prompt, hold down the Ctrl key and push R. You’ll receive a message that reads (reverse-i-search)`’: followed by the cursor. Type the first letter of a command that you issued before to find it. For instance, if you’ve used the select command, then push s. If you’ve used shutdown, then this might come up as well.
Push Ctrl+R again to cycle through similarly named commands. If you have a long history, then you might find a number of commands with similar names. Once you find the command that you want to run, push the enter key and your prompt will look like you’ve just typed it in and ran it. You won’t have to type the full command but instead only a letter or two.
You can type a few letters to find a longer command that you might have issued in the past and it too will come up right away on the command line, and you can then push enter to run it like normal. It makes the process of finding something in your history much easier. Once a command is on the line you can also use the arrow keys and edit it before running it.

Sources:
Installing rlwrap
How to Use CTRL R in Linux
Oracle SQLcl
rlwrap - Linux man page
DBA-010

Oracle 19c Updates on Active Data Guard using DML Redirection

noreply@blogger.com (Yossi Nixon) — Sun, 17 Feb 2019 07:38:00 +0000

Previously, you could only update Global Temp Tables on Active Data Guard standby database. From Oracle 19c, you can also update regular tables.

Updates on Active Data Guard:

Update will be redirected to the primary
Primary makes update, generates & sends redo for that update to all standbys
Active Data Guard session sees the update in redo apply and resumes

Preserves ACID properties for ADG session

Redirected update only visible to session before commit; visible to all sessions after commit

For “Mostly Read, Occasional Updates” applications – for example, recording user logins for auditing purposes
Enabled by ADG_REDIRECT_DML at system or session level

The following setup is based on Active Data guard configuration is MaxAvailability, but the behavior is the same when configuring MaxPerformance

A step by step guide to demonstrate this feature:

Current configuration

Basic setup

The setup is primary database sends its redo logfiles through a far sync instance to a remote standby database.

DGMGRL> show configuration

Configuration - PRODCONF

Protection Mode: MaxAvailability
Members:
orcl - Primary database
fdb - Far sync instance
sdb - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 49 seconds ago)

Redo Route configuration

Primary

The redo log files are transferred to the far sync instance, if the latter will not be available, the transport will be done to the standby as second priority.

DGMGRL> show database orcl RedoRoutes
RedoRoutes = '(LOCAL : ( FDB PRIORITY=1, SDB PRIORITY=2 ) )'

Far Sync

Far sync receives the redo logfiles and transfers them to the standby asynchronously

DGMGRL> show far_sync fdb RedoRoutes
RedoRoutes = '( ORCL : SDB ASYNC)'

LogXptMode configuration

Far sync

Far sync instance receives the redo logfiles synchronously

DGMGRL> show far_sync fdb LogXptMode
LogXptMode = 'SYNC'

Standby

Standby database receives the redo logfiles asynchronously

DGMGRL> show database sdb LogXptMode
LogXptMode = 'ASYNC'

The standby is in sync and there is no lag

DGMGRL> show database sdb

Database - sdb

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 4.00 KByte/s
Real Time Query: ON
Instance(s):
orcl

Database Status:
SUCCESS

Prepare the primary database

Connect as sys

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:15:51 2018
Version 19.1.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta
Version 19.1.0.0.0

Enable the feature ADG_REDIRECT_DML

SYS@orcl:SQL> alter system set ADG_REDIRECT_DML=TRUE scope=both;

System altered.

Connect to pluggable database

SYS@orcl:SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SYS@orcl:SQL> alter session set container=pdb;

Session altered.

Create a test user

SYS@orcl:SQL> create user test_user identified by test_user;

User created.
SYS@orcl:SQL> grant connect, create session, create table to test_user;
Grant succeeded.
SYS@orcl:SQL> alter user test_user quota unlimited on users;

User altered.

Connect to the test user

SYS@orcl:SQL> connect test_user/test_user@pdb_svc
Connected.
Create sample tables
TEST_USER@primary_host/pdb_svc:SQL> create table regular_table(col1 number);

Table created.
TEST_USER@primary_host/pdb_svc:SQL> create global temporary table global_tt1(col1 number);

Table created.

Connect to the standby database

Connect as sys

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:15:51 2018
Version 19.1.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta
Version 19.1.0.0.0

Enable the feature ADG_REDIRECT_DML

SYS@sdb:SQL> alter system set ADG_REDIRECT_DML=TRUE scope=both;

System altered.

Connect to the test user

$ sqlplus test_user/test_user@standby_host:1521/pdb_svc

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:24:28 2018
Version 19.1.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Last Successful login time: Thu Dec 27 2018 13:20:05 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta
Version 19.1.0.0.0

Do the magic 😊

TEST_USER@standby_host:1521/pdb_svc:SQL> set timing on

Check that inserting to a regular table is working on the standby database

TEST_USER@standby_host:1521/pdb_svc:SQL> insert into regular_table(col1) values(1);

1 row created.

Elapsed: 00:00:01.23

Look above at the elapsed time of the insert – this is the time in my environment that the standby asked from the primary to perform the insert and then the updated blocks are transferred from the primary to the standby.

TEST_USER@standby_host:1521/pdb_svc:SQL> select * from regular_table;

COL1
----------
1

Elapsed: 00:00:00.03
TEST_USER@standby_host:1521/pdb_svc:SQL> commit;

Commit complete.

Elapsed: 00:00:01.06

Look above at the elapsed time of the commit

Check that inserting to a global temporary table is working on the standby database

TEST_USER@standby_host:1521/pdb_svc:SQL> insert into global_tt1(col1) values(1);

1 row created.

Elapsed: 00:00:00.02

Look above at the elapsed time of the insert – much faster then working with a normal table.

TEST_USER@standby_host:1521/pdb_svc:SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

Look above at the elapsed time of the commit – look like it didn’t take time at all

TEST_USER@standby_host:1521/pdb_svc:SQL> select * from global_tt1;

no rows selected

Elapsed: 00:00:00.00

Check that we can create global temporary table on the standby database

TEST_USER@standby_host:1521/pdb_svc:SQL> create global temporary table global_tt2(col1 number);

Table created.

Elapsed: 00:00:01.07
TEST_USER@standby_host:1521/pdb_svc:SQL> insert into global_tt2(col1) values(1);

1 row created.

Elapsed: 00:00:00.01

Check if primary database is updated

TEST_USER@primary_host/pdb_svc:SQL> select * from regular_table;

COL1
----------
1
TEST_USER@primary_host/pdb_svc:SQL> desc global_tt2
Name Null? Type
----------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------
COL1 NUMBER

Yossi

Oracle Database Auto Discovery

noreply@blogger.com (Yossi Nixon) — Mon, 07 Jan 2019 15:00:00 +0000

Hi,

I was asked by our development team to provide the best way to identify database parameters from database host, I was surprised to find so many options.

Identifying all instances on the current machine

Option 1:

$ ps -ef |grep smon | grep -v grep
oracle 3025 1 0 2016 ? 00:00:48 asm_smon_+ASM
oracle 11459 1 0 17:24 ? 00:00:00 ora_smon_fdb

oracle SID is fdb and process id is 11459

Option 2:

$ pgrep -lf _pmon_
3025 asm_pmon_+asm
11459 ora_pmon_fdb

oracle SID is fdb and process id is 11459

Option 3:

cleaner way for sid:

$ ps -ef |grep 'ora_smon_.*$' | grep -v grep | awk -F_ '/ora_smon/{print $NF}'
fdb

oracle SID is fdb

Option 4:

When we already know ASM home (grid infrastructure) we can use the cluster commands:

$ /oracle/product/12.1.0.2/grid/bin/crsctl stat res -t -w "TYPE = ora.database.type"|awk '/^ora./ {l=$0;} !/^ora./ { if ( l > "" ) l=l " " $0; print l;l="";}'|grep ${HOSTNAME%%.*}
ora.pdb.db 1 ONLINE ONLINE primary_host Open,STABLE

Option 5:

$ cat /etc/oratab | grep -v ^'#' | grep -v ^$ | awk -F ":" ' { print $1 }' | grep -v ASM | uniq
fdb

Identify oracle_home

Option 1:

Using process id we already found in the previous run (look above for option 1 or 2)

$ grep -z ^ORACLE_HOME /proc/11459/environ
ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1

option 2:

$ pwdx 11459
11459: /oracle/product/12.1.0.2/dbhome_1/dbs

option 3:

$ strings /proc/11459/environ | grep ORACLE_HOME
ORACLE_HOME_LISTNER=/oracle/product/12.1.0.2/grid
ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1

option 4:

$ cat /etc/oratab | grep -v ^'#' | grep -v ^$ | awk -F ":" ' { print $2 }' | grep -v ASM | uniq
/oracle/product/12.1.0.2/grid/oracle/product/12.1.0.2/dbhome_1

If needed more information than connection to the remote instances should be created via SQL*Net for this we should have sys password

Checking from the Far sync - What is the name of the Primary Database

select INST_ID,SOURCE_DB_UNIQUE_NAME from gv$dataguard_stats where NAME='transport lag';
SOURCE
DB
INST UNIQUE
ID NAME
---- -------
1 pdb

Checking from the Far sync - What is the name of the Standby Database

SQL> SELECT db_unique_name FROM v$archive_dest WHERE destination IS NOT NULL and TARGET not in ('LOCAL','PRIMARY');

DB

UNIQUE

NAME

-------

sdb

Identifying primary and standby database hosts and ports via tnsping (from previous results)

$ tnsping pdb

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 06-FEB-2017 18:04:00

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:
/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =primary_host)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdb)))
OK (0 msec)

Please let me know if you have some other tricks

Creating SNMP Plug-In for Enterprise Manager Cloud Control

noreply@blogger.com (Yossi Nixon) — Mon, 31 Dec 2018 15:00:00 +0000

Putting it In my previous blog (Enterprise Manager Cloud Control, SNMP, and Plug-Ins –
The Background), I shared my frustration in trying to create
a plug-in in OEM. Here are the technical steps for a very simple plug-in.

Preparing Plug-in

To build your own plug-in, you need to download a development kit:

To download EDK using UI, from the Setup menu, selectExtensibility, and then select Development Kit.OR
To download EDK using EMCLI, run the following steps:

Log in to OEM:

$ /oracle/em/middleware/bin/emcli login -username=sysmanEnter password :
Login successful

Download the EDK zip archive:

$ emcli get_ext_dev_kitDownloading edk.zipFile saved as edk.zipFile(s) downloaded successfully

Unpack the downloaded EDK ZIP archive to a directory on your local
system. For example:

$ unzip 13.3.0.0.0_edk_partner.zip

There are several restrictions and guidelines you should know before you create your plug-in.
Most of them can be found at the following link: About Plug-in Metadata

Restrictions and Guidelines

Each plug-in must be assigned a version. The plug-in versioning syntax is as follows: a.b.c.d.e, where:

a.b = The version of the Enterprise Manager Extensibility Development Kit (EDK) used for development (13.1, 13.2, and so on)

c = The developer-assigned plug-in version; this value must be incremented with each plug-in release on the same Enterprise Manager Cloud Control release
d = Indicates whether the plug-in is a beta version or a production version; the value 0 indicates beta and 1 or later indicates production
e = For future use; the default value is 0

Putting it all together, the following example shows the first version of a plug-in created for Enterprise Manager Cloud Control 13c: 13.1.1.1.0

Each time you deploy a new plug-in version, you must increase the version number.
I created a script using the sed command to increase the number:

sed -i "s/13.3.0.${PREV_VER}.0/13.3.0.${CURR_VER}.0/g" plugin.xml agent/plugin_registry.xml
find ${STAGE_DIR} -type f -name "*xml"|xargs grep -l META_VER | xargs sed -i "s/META_VER=\"13.${PREV_VER}/META_VER=\"13.${CURR_VER}/g"

Plug-ins are identified by a unique plug-in identifier (ID). The plug-in ID has three parts:

Vendor ID (8 characters). For example: test
Product ID (8 characters). For example: switch
Plug-in Tag (4 characters). For example: xkey.

The content in the following files should be identical with each other:

agent/default_collection/test_switch_xkey.xml oms/metadata/default_collection/test_switch_xkey.xml
agent/metadata/test_switch_xkey.xml
oms/metadata/test_switch_xkey.xml
oms/metadata/targetType/test_switch_xkey.xml

Folder Structure

The structure of the folders and files should be like this:

├── agent

│ ├── default_collection

│ │ └── test_switch_xkey.xml

│ ├── metadata

│ │ └── test_switch_xkey.xml

│ └── plugin_registry.xml

├── discovery

│ ├── test_switch_xkey_snmp_discovery.pl

│ └── test_switch_xkey_snmp_discovery.xml

├── oms

│ └── metadata

│ ├── test_switch_xkey.xml

│ ├── default_collection

│ │ └── test_switch_xkey.xml

│ ├── discovery

│ └── targetType

│ └── test_switch_xkey.xml

└── plugin.xml

Metadata File

A basic plug-in requires metadata for the plug-in itself, including information such as the name and version that is used by Oracle Management Service and Management Agents, definition of a metric indicating whether the monitored target is up, and definition of the frequency at which metric data should be collected. Metadata file example:

<?xml version="1.0"?>
<TargetMetadata META_VER="13.73" TYPE="test_switch_xkey">
  <Display>
      <Label NLSID="hs_displayname">SNMP Plugin</Label>
  </Display>
  <Metric NAME="SnmpTrap" TYPE="TABLE">
      <Display>
          <Label NLSID="MY_threshold">Snmp Trap</Label>
      </Display>
      <CategoryValue CLASS="Default" CATEGORY_NAME="Fault"/>
      <TableDescriptor>
          <ColumnDescriptor NAME="TimeOfEvent" TYPE="STRING" IS_KEY="TRUE">
              <Display>
                  <Label NLSID="timeOfEvent_1">Name</Label>
                      <Description NLSID="timeOfEvent">Time Of Event</Description>
                          <Unit NLSID="EM_SYS_STANDARD_NA_NA">NA</Unit>
                      <UnitCategory>NA</UnitCategory>
                      </Display>
                  </ColumnDescriptor>
                  <ColumnDescriptor NAME="EventDetails" TYPE="STRING" IS_KEY="FALSE">
                      <Display>
                          <Label NLSID="eventDetails_1">EventDetails</Label>
                              <Description NLSID="eventDetails">Trap Event Details</Description>
                          <Unit NLSID="EM_SYS_STANDARD_NA_NA">NA</Unit>
                          <UnitCategory>NA</UnitCategory>
</Display>
                  </ColumnDescriptor>
          </TableDescriptor>
          <PushDescriptor RECVLET_ID="SNMPTrap">
              <Property NAME="MatchTrapOID" SCOPE="GLOBAL">1.3.6.1.4.1.29284.1.1.5.3</Property>
              <Property NAME="MatchAgentAddr" SCOPE="INSTANCE">ipaddress</Property>
              <Property NAME="KeyTimeOfEventOID" SCOPE="GLOBAL">1.3.6.1.4.1.29284.1.1.1.11.0</Property>
              <Property NAME="ContextTimeOfEventOID" SCOPE="GLOBAL">1.3.6.1.4.1.29284.1.1.1.11.0</Property>
              <Property NAME="DataEventDetailsOID" SCOPE="GLOBAL">1.3.6.1.4.1.29284.1.1.1.13.0</Property>
              <Property NAME="ContextStatusOID" SCOPE="GLOBAL">1.3.6.1.4.1.29284.1.1.1.3.0</Property>
              <Property NAME="SeverityCodeOID" SCOPE="GLOBAL">1.3.6.1.4.1.29284.1.1.1.3.0;0=CLEAR,4=WARNING,5=CRITICAL,6=CRITICAL</Property>
              <CredentialRef NAME="monCreds">monCredentials</CredentialRef>
          </PushDescriptor>
      </Metric>
      <CredentialInfo>
          <CredentialSet NAME="monCredentials" USAGE="MONITORING">
              <Display>
                  <Label NLSID="hs_monCredentials">Credentials</Label>
              </Display>
                  <AllowedCredType TYPE="SNMPV1Creds" />
              </CredentialSet>
      </CredentialInfo>
      <InstanceProperties>
      <InstanceProperty NAME="ipaddress" CREDENTIAL="FALSE" OPTIONAL="TRUE">
          <Display>
              <Label NLSID="hs_ipaddress">Sender’s IP Address</Label>
          </Display>
      </InstanceProperty>
  </InstanceProperties>
</TargetMetadata>

A few points:

A key column will be updated when there is a new trap with the same key.
MatchAgentAddr is dynamic. That is, it is not hard coded. When you deploy the plug-in, you can enter the sender IP address.
The credential (community) is also dynamic and can be set when deploying the plug-in.
If all of your traps have a default trap ID and you have the same fields in all of your traps, you can have only one PushDescriptor. Otherwise, you will need to define for each of the traps an XML section of PushDescriptor.
A severity level that is set to CLEAR the alert is recommended.

Default Collection

The default collection metadata file for a target type defines the following:
The metric data (including configuration collection metric data) to be collected from targets and written to the Management Repository.
The frequency at which this metric data is collected.
Thresholds that, when exceeded, will cause a Metric Alert event to be raised.
An optional message to display when a threshold is exceeded

Default collection example:

<?xml version="1.0"?>
<TargetCollection TYPE="test_switch_xkey" META_VER="13.73">
<CollectionItem NAME="SnmpTrap">
    <MetricColl NAME="SnmpTrap">
      <Condition PUSH="TRUE"
        COLUMN_NAME="EventDetails"
        CRITICAL="NONE" OPERATOR="NE"
        MESSAGE="SNMP Trap - The value for %columnName% is %value%%%. - Alert"
        CLEAR_MESSAGE="SNMP Trap - The value for %columnName% is %value%%%. - Cleared"
        MESSAGE_NLSID="snmp_trap_alert" />
    </MetricColl>
</CollectionItem>
</TargetCollection>

Note that the value of the TYPE attribute and theMETA_VER attribute in the default collection metadata file must match the TYPE and the META_VER values defined in the target type metadata file to create an association between them.

Plug-in Deployment

Creating the plug-in is done via several commands. Importing and deploying the plug-in can be done via the command line or by web interface. It is handy and easy to use a script to deploy automatically - this is the way I chose.

Set Your Environment

I used the Java from OMS database home:
$ export JAVA_HOME=/oracle/product/12.2.0.1/dbhome_1/jdk $ export PATH=/oracle/product/12.2.0.1/dbhome_1/jdk/bin:$PATH

Validate that the plug-in files are valid:

$ /oracle/em/middleware/sysman/edk/bin/empdk validate_plugin -stage_dir /home/oracle/stage -format text -tmp_dir /tmp -out_dir /tmp

Create the plug-in:

/oracle/em/middleware/sysman/edk/bin/empdk create_plugin -stage_dir /home/oracle/stage -conn_desc <oms_database>:<oms_database port>:<oms_service_name> -repos_user sysman -out_dir /tmp

Import the plug-in to OEM:

/oracle/em/middleware/bin/emcli import_update -file=/tmp/13.3.0.73.0_test.switch.xkey_2000_0.opar -omslocal

Deploy the plug-in to the OMS:

/oracle/em/middleware/bin/emcli deploy_plugin_on_server -plugin=test.switch.xkey:13.3.0.73.0 -sys_password=<change_on_install>

Verify the deployment progress of the plug-in:

$ /oracle/em/middleware/bin/emcli get_plugin_deployment_status -plugin=test.switch.xkey

Wait for “Status“ to be: Success

Deploy the plug-in to the agent:

/oracle/em/middleware/bin/emcli deploy_plugin_on_agent -agent_names=" <monitoring server>:3872" -plugin="test.switch.xkey"

Verify the deployment progress of the plug-in:

$ /oracle/em/middleware/bin/emcli get_plugin_deployment_status -plugin=test.switch.xkey

Wait for “Status“ to be: Success

Plug-in Undeployment

Undeploy plug-in from the agent:

$ /oracle/em/middleware/bin/emcli undeploy_plugin_from_agent -plugin="test.switch.xkey:13.3.0.73.0" -agent_names="<monitoring server>:3872" -delete_targets

Verify the Undeployment progress of the plug-in:

$ /oracle/em/middleware/bin/emcli get_plugin_deployment_status -plugin=test.switch.xkey

Wait for “Status“ to be: Success

Undeploy the plug-in from the OMS:

$ /oracle/em/middleware/bin/emcli undeploy_plugin_from_server -plugin="test.switch.xkey:13.3.0.73.0" -sys_password=<change_on_install>

Verify the UNdeployment progress of the OMS

$ /oracle/em/middleware/bin/emcli get_plugin_deployment_status -plugin=test.switch.xkey

Wait for “Status“ to be: Success

Adding Targets

The last step in plug-in deployment is to add a target. To add a target on a managed host by specifying the target monitoring properties, follow these steps:

From the Setup menu, select Add Target , then select Add Targets Manually. Cloud Control displays the Add Targets Manually page.
On the Add Targets Manually page, select Add Targets Declaratively by Specifying Target Monitoring Properties.

Add Target Name
Community is usually: public
Enter the IP address of the sender

Example:

Testing and Debugging

Send SNMP trap to the agent
Check if the agent received the trap, and filter the matching metric.

$ tailf -100 /oracle/em/agent/agent_inst/sysman/log/gcagent_sdk.trc

2018-12-14 21:01:36,449 [151:F60C8AF1] INFO - =====SNMPRecvlt received V2 notification=====:

2018-12-14 21:01:36,449 [151:F60C8AF1] INFO - SNMPV2 notification from host 10.14.1.50
2018-12-14 21:01:36,449 [151:F60C8AF1] INFO - Notification Details:
2018-12-14 21:01:36,449 [151:F60C8AF1] INFO - CommandResponderEvent[securityModel=2, securityLevel=1, maxSizeResponsePDU=65535, pduHandle=PduHandle[0], stateReference=StateReference[msgID=0,pduHandle=PduHandle[0],securityEngineID=null,securityModel=null,securityName=public,securityLevel=1,contextEngineID=null,contextName=null,retryMsgIDs=null], pdu=TRAP[requestID=0, errorStatus=Success(0), errorIndex=0, VBS[1.3.6.1.2.1.1.3.0 = 1:02:47.22; 1.3.6.1.6.3.1.1.4.1.0 = 1.3.6.1.4.1.29284.1.1.2.1.47; 1.3.6.1.4.1.29284.1.1.1.11.0 = 07:e2:0c:0e:12:3b:24:00; 1.3.6.1.4.1.29284.1.1.1.18.0 = Axxana; 1.3.6.1.4.1.29284.1.1.1.19.0 = env14; 1.3.6.1.4.1.29284.1.1.1.20.0 = collector; 1.3.6.1.4.1.29284.1.1.1.13.0 = Black Box external power recovered. trace id: 0x7fe898004a00; 1.3.6.1.4.1.29284.1.1.1.4.0 = 5; 1.3.6.1.4.1.29284.1.1.1.3.0 = 3; 1.3.6.1.4.1.29284.1.1.1.12.0 = 10.14.1.50]], messageProcessingModel=1, securityName=public, processed=false, peerAddress=10.14.1.50/36940, transportMapping=org.snmp4j.transport.DefaultUdpTransportMapping@71d78ff, tmStateReference=null]AF1] INFO - oid 3: 1.3.6.1.4.1.29284.1.1.1.18.0 val : Axxana
2018-12-14 21:01:36,450 [151:F60C8AF1] INFO - oid 4: 1.3.6.1.4.1.29284.1.1.1.19.0 val : env14
2018-12-14 21:01:36,450 [151:F60C8AF1] INFO - oid 5: 1.3.6.1.4.1.29284.1.1.1.20.0 val : collector
2018-12-14 21:01:36,450 [151:F60C8AF1] INFO - oid 6: 1.3.6.1.4.1.29284.1.1.1.13.0 val : Black Box external power recovered. trace id: 0x7fe898004a00
2018-12-14 21:01:36,450 [151:F60C8AF1] INFO - oid 7: 1.3.6.1.4.1.29284.1.1.1.4.0 val : 5
2018-12-14 21:01:36,450 [151:F60C8AF1] INFO - oid 8: 1.3.6.1.4.1.29284.1.1.1.3.0 val : 3
2018-12-14 21:01:36,450 [151:F60C8AF1] INFO - oid 9: 1.3.6.1.4.1.29284.1.1.1.12.0 val : 10.14.1.50
2018-12-14 21:01:36,451 [151:F60C8AF1] INFO - Matching metric found ...
metric name : SnmpTrap MatchTrapOID: 1.3.6.1.4.1.29284.1.1.2.1.47 MatchAgentAddress : 10.14.1.50
2018-12-14 21:01:36,451 [151:F60C8AF1] INFO - Trap received is for sending Data
2018-12-14 21:01:36,451 [151:F60C8AF1] INFO - Sending datapoint for metric , test_switch_xkey.cc_snmp,SnmpTrap

Sample output in the web interface:

References

Enterprise Manager Cloud Control Extensibility Programmer's Reference
https://docs.oracle.com/cd/E73210_01/EMPRF/title.htm
Defining the Plug-in
https://docs.oracle.com/cd/E73210_01/EMPRF/GUID-B80B66E6-D07B-4890-A4CE-A7ECF7874946.htm#EMPRF11232
Creating Target Metadata Files
https://docs.oracle.com/cd/E73210_01/EMPRF/GUID-C6918D3A-B696-4EAC-8C8F-0DCD17773346.htm#EMPRF11253
Plug-in Builder
https://docs.oracle.com/cd/E73210_01/EMPRF/GUID-6A94EE77-D7AA-4A30-83AA-B627C41D7264.htm#EMPRF12910
Using Receivelets
https://docs.oracle.com/cd/E73210_01/EMPRF/GUID-39F8E13B-387A-4DA2-A3BD-DEB2BF339683.htm#EMPRF12663
Enterprise Manager Troubleshooting Guide for Third-Party Database Plug-ins
https://docs.oracle.com/cd/E26854_01/em.121/e35212/ch1_common_issues.htm#EMPTG106

Enterprise Manager Cloud Control, SNMP, and Plug-Ins – The Background

noreply@blogger.com (Yossi Nixon) — Mon, 24 Dec 2018 15:00:00 +0000

Hi,
I have been using Oracle Enterprise Manager (OEM or Cloud Control) since it was a client application. (I’m not sure whether it was in the era of Oracle 7 or Oracle 8). In the early stages, OEM was compared to other central monitoring systems (at the time, it was HP OpenView, CA Unicenter, and the like). As a DBA who was responsible for dozens of Oracle databases, I needed a main system like this. Since we had the OEM for "free" as part of a site license, we used it heavily. The problem was that the main IT monitoring tool in our company was another system that already monitored appliances, networks, and applications. So, we decided to keep the OEM to the DBAs and send notifications to this system. (It was monitored 24/7 by a control center.) Like today, we could send alerts by email, run an external script, or send an SNMP trap. We chose the SNMP trap since it is in the core of most monitoring systems and simple to define.

OEM needed a lot of attention and maintenance, and most DBAs didn’t want to waste time setting this tool. Over the years I worked as an OEM integrator and specialist for installing and defining metrics and reports for other companies.

In my current company, we have an appliance that can send SNMP traps to a central monitoring system. Usually we are being asked to send an SNMP trap to a "normal" system, which does not need any adjustments or development for receiving the events. Recently, a customer asked us to send the traps to OEM. I thought it should be just to set up a target IP and port and maybe to load the MIB file (a translation of the ID of the traps to messages).

Oracle has enhanced the OEM significantly in each version, and now it is lighter and simpler and has connectivity to many systems. I believe that now it can replace other systems. For connecting external systems, you can use plug-ins like Microsoft SQL Server, Apache Tomcat, and Juniper Netscreen Firewall. (A full list can be found here: https://www.oracle.com/technetwork/oem/grid-control/system-monitoring-connectors-082031.html). And if you don’t have it, build your own plug-in. Receiving SNMP from an external system can be defined as part of a plug-in definition.

I tried to follow the latest documentation. I realized that there are separate sections for a plug-in and for SNMP receivelets (the part that receives the SNMP and maps it into OEM columns) that are not correlated. I used the same examples from the documentation and couldn’t make it work. The most detailed document, with all information in one place, was published by Oracle for version 9!! I looked for a cookbook where maybe someone already documented the steps, and I found nothing. I’ve posted some questions in several forums and have not gotten a response. I opened an SR, and the support representative could not find anybody to help me ☹. Is nobody using this feature???

Fortunately, at the last OpenWorld, I met the product manager of OEM, who helped me and guided me with several of his employees and colleagues.

On this adventure I learned the world of SNMP, traps, MIB files, and of course the OEM plug-in development environment.

These days I am working on writing all the steps on how to receive SNMP to OEM. I hope I will make others’ experience simpler.

Stay tuned.
Yossi Nixon

Setup additional local DNS - to add scan addresses

noreply@blogger.com (Yossi Nixon) — Mon, 17 Dec 2018 15:00:00 +0000

In this document I demonstrate how to add local DNS server to be added additionally to an existing DNS, I had this need for creating some additional IP addresses in the DNS for Real Application Cluster (VIP and SCAN)

My Testing Environment

My Private DNS Server

IP Address: 10.20.2.100
Host Name: localdns
OS: Oracle Linux Server release 7.5

Client Machine to use DNS

IP Address: 10.33.1.120
Host Name: dbhost
OS: Oracle Linux Server release 7.4

Current Domain DNS

Primary: 192.168.1.10
Secondary: 192.168.1.1 1

DNS entry to add

IP Address: 10.33.1.123
Host Name: dbhost-scan

Verify that the above IP address is not being used via ping command

$ ping 10.33.1.123 -c1 -w 1 >/dev/null 2>&1 ; if [ $? -eq 1 ]; then echo "This IP is Not used"; else echo "This IP is used"; fi
This IP is Not used

Install required packages

$ sudo yum install bind* -y

Define zone files in master configuration ‘named.conf‘ file

$ sudo vim /etc/named.conf

Change 127.0.01 to any and at the end zone sections.

Add the zone section at the end, pay attention for the prefix of in-addr.arpa (it is the reverse of the first 3 numbers of the DNS entry we want to add - important for reverse lookup)

//
// named.conf
//
// Provided by Red Hat bind package to configure the ISC BIND named(8) DNS
// server as a caching only nameserver (as a localhost DNS resolver only).
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//
// See the BIND Administrator's Reference Manual (ARM) for details about the
// configuration located in /usr/share/doc/bind-{version}/Bv9ARM.html

options {
listen-on port 53 { any; };
listen-on-v6 port 53 { ::1; };
directory "/var/named";
dump-file "/var/named/data/cache_dump.db";
statistics-file "/var/named/data/named_stats.txt";
memstatistics-file "/var/named/data/named_mem_stats.txt";
allow-query { any; };

/*
- If you are building an AUTHORITATIVE DNS server, do NOT enable recursion.
- If you are building a RECURSIVE (caching) DNS server, you need to enable
recursion.
- If your recursive DNS server has a public IP address, you MUST enable access
control to limit queries to your legitimate users. Failing to do so will
cause your server to become part of large scale DNS amplification
attacks. Implementing BCP38 within your network would greatly
reduce such attack surface
*/
recursion no;

dnssec-enable yes;
dnssec-validation yes;

/* Path to ISC DLV key */
bindkeys-file "/etc/named.iscdlv.key";

managed-keys-directory "/var/named/dynamic";

pid-file "/run/named/named.pid";
session-keyfile "/run/named/session.key";
};

logging {
channel default_debug {
file "data/named.run";
severity dynamic;
};
};

zone "mydomain.local" IN {
type master;
file "named.mydomain.fwd";
allow-update { none; };
};
zone"1.33.10.in-addr.arpa" IN {
type master;
file "named.mydomain.rev";
allow-update { none; };
};

include "/etc/named.rfc1912.zones";
include "/etc/named.root.key";

Create Zone Files

$ sudo cp /var/named/named.localhost /var/named/named.mydomain.fwd
$ sudo cp /var/named/named.loopback /var/named/named.mydomain.rev

Edit these zones files

Forward zone configuration

$ sudo vi /var/named/named.mydomain.fwd

Make changes as per your need.

@ IN SOA localdns.mydomain.local. root.mydomain.local. (
2014090401 ; serial
3600 ; refresh
1800 ; retry
604800 ; expire
86400 ) ; minimum
; Name server's
@ IN NS localdns.mydomain.local.
; Name server hostname to IP resolve.
@ IN A 10.20.2.100
; Hosts in this Domain
@ IN A 10.33.1.123
dbhost-scan IN A 10.33.1.123
localdns IN A 10.20.2.100

Reverse zone configuration

$ sudo vi /var/named/named.mydomain.rev

Make changes as per your need.

$TTL 86400
@ IN SOA localdns.mydomain.local. root.mydomain.local. (
2014090402 ; serial
3600 ; refresh
1800 ; retry
604800 ; expire
86400 ) ; minimum
; Name server's
@ IN NS localdns
@ IN PTR mydomain.local.
; Name server hostname to IP resolve.
localdns IN A 10.20.2.100
;Hosts in Domain
dbhost-scan IN A 10.33.1.123
123 IN PTR dbhost-scan.mydomain.local.
100 IN PTR localdns.mydomain.local.

Change the group to named

$ sudo chgrp named /var/named/named.mydomain.fwd
$ sudo chgrp named /var/named/named.mydomain.rev

check for the errors in zone files

$ sudo named-checkconf /etc/named.conf
$ sudo named-checkzone localdns.mydomain.local /var/named/named.mydomain.fwd
$ sudo named-checkzone localdns.mydomain.local /var/named/named.mydomain.rev

run,enable and check named service

$ sudo chkconfig named on
$ sudo service named start
$ sudo service named status

Add new dns to be resolved on client machine (dbhost)

$ sudo vi /etc/resolv.conf

Add the red entry to the file

# Generated by NetworkManager
search mydomain.local
nameserver 192.168.1.10
nameserver 192.168.1.11
nameserver 10.20.2.100
options timeout:1
options attempts:2

Check our new DNS

$ nslookup 10.33.1.123
Server: 10.20.2.100
Address: 10.20.2.100#53

123.1.33.10.in-addr.arpa name = dbhost-scan.mydomain.local.

$ nslookup dbhost-scan
Server: 10.20.2.100
Address: 10.20.2.100#53

Name: dbhost-scan.mydomain.local
Address: 10.33.1.123

$ nslookup dbhost-scan.mydomain.local
Server: 10.20.2.100
Address: 10.20.2.100#53

Name: dbhost-scan.mydomain.local
Address: 10.33.1.123

Yossi

Oracle open world 2017 short summary

noreply@blogger.com (Yossi Nixon) — Wed, 12 Dec 2018 09:07:00 +0000

Hi,

The main subject in this conference was Autonomous Database, it's engine is used by another buzzword called: Machine Learning.

Oracle claim they are investing in autonomous to prevent data theft, but it seems as convenience for handling only one version in the cloud.

They will use the same "Machine Learning" engine that they used for the optimizer and memory management decisions (This engine is well known for bad decisions).

18c is an alias for 12.2.0.2 and planned to be available:

Autonomous Database for Data Warehouses - by December 2017
Autonomous Database for OLTP – by June 2018

A companion solution for the "Machine Learning" will be used via the OEM (Cloud Control) to gather:

Infrastructure logs: Network, Server, Storage, VM, OS
Platform logs: Database, Java, Analytics, etc.
Application logs: ERP, CX, HCM, Custom, etc.

The logs will be gathered to detect and connect anomalous events.

SLA Guarantees 99.995% reliability and availability (less than 30 minutes a year) – i.e: online patching, upgrade, tuning and maintenance.

See my post about new features in Oracle 18c https://oracledba.blogspot.com/2017/10/oracle-18c-new-features-for-active-data.html

Yossi Nixon

Oracle Linux ASM docker recipe

noreply@blogger.com (Yossi Nixon) — Mon, 10 Dec 2018 15:00:00 +0000

General information

In this setup we are:

Installing docker
Creating Non-root user (ynixon) with sudo and docker privileges
ASM device: /dev/sdb1
Enabling sqlnet + ssh to the container
Default ASM port is 1521
ssh port 2222
Passwords for root + grid os users in the container are “ynixon”
Password for sys ASM user is “ynixon”
Grid software is 12.2 without any patches
Container Operating system is Oracle Linux 7.5
Within the container, there is no use of UDEV / ASMLIB or ASMFD – the asm_diskstring='/dev/asm*' ,'/dev/*'
All test done on regular Ubuntu 14.04
There is a crontab job to keep 15 days of trace files + remove audit files.

Prepare host for ASM device

Make sure the device has permissions of the same container ids by applying UDEV rules

$ vi /etc/udev/rules.d/100-asm.rules
KERNEL=="sdb1", NAME="ASM_DISK", OWNER="54421", GROUP="54421", MODE="0660"

udevadm trigger --sysname-match=sdb1 --verbose

Verify the device has ASM lables

DISK_GROUP=$( blkid | grep oracleasm | sed 's/.*LABEL=\"$[^\"]*$\" TYPE=\"oracleasm\"/\1/')
if [ -z "$DISK_GROUP" ]
then
echo "device /dev/sdb1 has not asm metadata"
else
echo "device /dev/sdb1 has diskgroup $DISK_GROUP"
fi

Install Docker

As root

$ curl -fsSL https://get.docker.com/ | sh

$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
$ apt-get update
$ apt-cache policy docker-ce
$ apt-get install -y docker-ce

$ service docker status

Add users

As root
Equivalent user ids to the docker to follow (will be identified from outside)

$ groupadd -g 54422 asmadmin
$ useradd -u 54421 -g 54422 grid

A dedicated user to manage the docker

$ adduser ynixon -g 54422
$ echo "ynixon:ynixon" | chpasswd
$ usermod -aG docker ynixon
$ usermod -aG sudo ynixon
$ sed -i '/PasswordAuthentication/d' /etc/ssh/sshd_config ; echo "PasswordAuthentication no" >> /etc/ssh/sshd_config
sed -i '/PubkeyAuthentication/d' /etc/ssh/sshd_config ; echo "PubkeyAuthentication yes" >> /etc/ssh/sshd_config
sed -i '/ChallengeResponseAuthentication/d' /etc/ssh/sshd_config ; echo "ChallengeResponseAuthentication no" >> /etc/ssh/sshd_config
$ service ssh reload

Building a new image

If you already have an image file skip to Load image.
In this step we will create a new image from scratch, and pack it at the end.
You can run /depo/build.sh + /depo/export.sh or run the following manual steps:

Prepare the files

As root

$ mkdir /depo/
$ chown -R root:54422 /depo/
$ chmod 775 /depo
$ git clone https://github.com/ynixon/OracleASMdocker.git /depo/

Ensure you download the file linuxx64_12201_grid_home.zip and copy it to /depo/ folder

List of the files

Script	Description
adrci_script.sh	A script to that will run for crontab to delete trace files periodically
build.sh	A script to build a docker image
clean.sh	A script to clean all docker containers and an image
crontab.setup	A cronjob script for grid user that will delete trace files periodically
disks.sql	An SQL script for listing disks
docker_descendants.py	A script to check docker images dependencies
docker_folder/Dockerfile	Build instruction for docker image
export.sh	Export a docker image and compress it
glogin.sql	setting SQL*Plus prettier output
grants.sql	grants for the build process
init+ASM.ora	The initialization file for ASM instance
install_grid.sh	Install script for grid
linuxx64_12201_grid_home.zip	Oracle 12.2 Grid software (no patches) download it separately
listener.ora	Listener file
oracle_asm	Logrotate for oracle logfiles
rlwrap-0.42-1.el7.x86_64.rpm	Handy tool to enable history in SQL*Plus, asmcmd
run.sh	Start a container
status.sh	Images and Container status

Run build image

As root/ynixon

$ cd /depo/docker_folder
$ docker build -t ynixon/docker_grid_asm .

Run image detached (at background)

$ docker run --rm --privileged --detach --name asm_grid_build -h gridserver -p 1521:1521 -p 2222:22 --shm-size 2048m -e TZ=UTC -v /sys/fs/cgroup:/sys/fs/cgroup:ro --volume /depo:/software --volume /boot:/boot --device=/dev/sdb1 ynixon/docker_grid_asm

Install grid software

$ docker exec -it asm_grid_build su - grid -c '/software/install_grid.sh'

Save the image with the grid

$ docker commit -m "oracle linux 7.5 standalone grid infrastructure with ASM" -a "Yossi Nixon" `docker ps -lq` ynixon/ynixon_asm_server

Remove intermediate image (without the grid)

$ docker rm -f asm_grid_build

Optional – check the the new image

Run the container at the background

$ docker run --privileged --detach --name asm_grid -h gridserver -p 1521:1521 -p 2222:22 --shm-size 2048m -e TZ=UTC -v /sys/fs/cgroup:/sys/fs/cgroup:ro --volume /depo:/software --volume /boot:/boot --device=/dev/sdb1 --restart always ynixon/ynixon_asm_server

Connect to the new container to verify the environment

$ docker exec -it --user grid asm_grid bash -l

Export the image and compress it

$ docker save -o /depo/export/docker_ynixon_asm_server.tar ynixon/ynixon_asm_server:latest
$ gzip docker_ynixon_asm_server.tar

Load image

Copy the file docker_ynixon_asm_server.tar.gz to /tmp

Uncompress the file

$ gunzip /tmp/docker_ynixon_asm_server.tar.gz

Load the file into the local container repository

$ docker load -i /depo/export/docker_ynixon_asm_server.tar

Run a container based on the loaded image

$ docker run --privileged --detach --name asm_grid -h gridserver -p 1521:1521 -p 2222:22 --shm-size 2048m -e TZ=UTC -v /sys/fs/cgroup:/sys/fs/cgroup:ro --volume /boot:/boot --device=/dev/sdb1 --restart always ynixon/ynixon_asm_server

Test Connection from remote machines:

$ ssh root@ -p 2222$ sqlplus sys/ynixon@:1521/+ASM as sysasm

Deploy Web Interface – Portainer

$ docker volume create portainer_data
$ docker run -d -p 9000:9000 -v /var/run/docker.sock:/var/run/docker.sock -v portainer_data:/data portainer/portainer

Open browser at: http://<docker server>:9000/

Yossi

Bypassing the Listener and Connecting the Instance without a Password from Java Code

noreply@blogger.com (Yossi Nixon) — Mon, 03 Dec 2018 15:00:00 +0000

In the past I wrote a post about Bypassing the listener and connecting to ASM without a password from Python code

Now, I've been asked to prove that it can be done in Java. The solution is almost the same.

Java sample code using OCI:

$ vi OracleCon.java
import java.sql.*;
class OracleCon {
public static void main(String args[]) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(
"jdbc:oracle:oci:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=BEQ) (PROGRAM=oracle) (ARGV0=oracle+ASM) (ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') (ENVS = 'ORACLE_HOME=/oracle/product/12.1.0.2/grid,ORACLE_SID=+ASM'))) (CONNECT_DATA= (SID=+ASM)))", "sys as sysdba", "change on install");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from v$instance");
while (rs.next())
System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3));
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}

I used Java for Oracle software (1.6):

$ export PATH=$ORACLE_HOME/jdk/jre/bin:$ORACLE_HOME/jdk/bin:$PATH
$ export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/ojdbc6.jar
$ javac OracleCon.java
$ java OracleCon
1 +ASM myhost

Yep... it is working :)
Yossi

Automatic restart in Grid Infrastructure for Far Sync

noreply@blogger.com (Yossi Nixon) — Mon, 26 Nov 2018 15:00:00 +0000

Hi,
Checking if the Far Sync instance is configured to restart automatically after reboot / shutdown of the host / cluster:

$ crsctl stat res ora.fdb1.db -f |grep AUTO_START
AUTO_START=restore

"restore" means that after restart, the database will remain at the previous state it was before.
Changing the cluster to startup the database in any case.

$ crsctl stat res ora.fdb1.db -f |grep AUTO_START
AUTO_START=always

Changing database to start always after reboot

$ crsctl modify resource ora.fdb1.db -attr AUTO_START=always -unsupported

Modify the startup state:

$ srvctl modify database -o mount

View modified configuration

$ srvctl config database -d fdb1 -all
Database unique name: fdb1
Database name: fdb1
Oracle home: /oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile:
Password file:
Domain:
Start options: mount
Stop options: immediate
Database role: FAR_SYNC
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
Database is enabled
OSDBA group:
OSOPER group:
Database instance: fdb1

For understanding the differences between srvctl and crsctl see: Oracle srvctl: Management policy Vs crsctl: AUTO_START (in Oracle Restart)

Yossi

Query Historical messages of RMAN

noreply@blogger.com (Yossi Nixon) — Mon, 19 Nov 2018 15:00:00 +0000

Hi,

Recently I realized that this very small and useful feature of RMAN is not known to many of us.
I have just learned about this option just two years ago.
I am talking about the ability to query from database (in-memory view) 32768 lines of RMAN output, even if it was used by crontab or any 3rd-party vendor.
So it is a sample sql command from v$rman_output, in the following example I filter just the last 7 days.

SQL> col OUTPUT for a135 trunc
SQL> set pages 0
SQL> select output from gv$rman_output where session_recid in (select session_recid from v$rman_status where start_time > sysdate-7) order by recid ;
connected to target database: PDB11 (DBID=3890652951, not open)

crosscheck archivelog all;
delete noprompt archivelog like '+DATA%';
delete noprompt archivelog like '+RECO%';
delete noprompt archivelog like '/DB%';
delete noprompt obsolete;
crosscheck copy;
delete noprompt expired copy;
exit;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13056.843.927192449 RECID=26052 STAMP=927192449
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13057.826.927192453 RECID=26055 STAMP=927192453
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13058.841.927193943 RECID=26056 STAMP=927193943
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_17/thread_1_seq_13059.783.928188033 RECID=26057 STAMP=928188041
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_20/thread_1_seq_13060.846.928423541 RECID=26058 STAMP=928423543
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_20/thread_1_seq_13061.825.928423543 RECID=26059 STAMP=928423544
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13062.689.928577549 RECID=26060 STAMP=928577549
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13063.1457.928579241 RECID=26061 STAMP=928579241
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13064.543.928579251 RECID=26067 STAMP=928579250
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13065.1502.928579257 RECID=26071 STAMP=928579256
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13066.1269.928579751 RECID=26073 STAMP=928579751
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13067.634.928579887 RECID=26074 STAMP=928579887
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13068.627.928579891 RECID=26078 STAMP=928579890
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13069.659.928580081 RECID=26080 STAMP=928580080
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13070.610.928581933 RECID=26081 STAMP=928581933
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13071.308.928581937 RECID=26083 STAMP=928581936
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13072.649.928581943 RECID=26086 STAMP=928581943
Crosschecked 17 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13070.610.928581933 thread=1 sequence=13070
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13071.308.928581937 thread=1 sequence=13071
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13072.649.928581943 thread=1 sequence=13072
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13056.843.927192449 RECID=26052 STAMP=927192449
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13057.826.927192453 RECID=26055 STAMP=927192453
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13058.841.927193943 RECID=26056 STAMP=927193943
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_17/thread_1_seq_13059.783.928188033 RECID=26057 STAMP=928188041
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_20/thread_1_seq_13060.846.928423541 RECID=26058 STAMP=928423543
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_20/thread_1_seq_13061.825.928423543 RECID=26059 STAMP=928423544
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13062.689.928577549 RECID=26060 STAMP=928577549
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13063.1457.928579241 RECID=26061 STAMP=928579241
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13064.543.928579251 RECID=26067 STAMP=928579250
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13065.1502.928579257 RECID=26071 STAMP=928579256
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13066.1269.928579751 RECID=26073 STAMP=928579751
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13067.634.928579887 RECID=26074 STAMP=928579887
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13068.627.928579891 RECID=26078 STAMP=928579890
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13069.659.928580081 RECID=26080 STAMP=928580080
Deleted 14 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
specification does not match any archived log in the repository
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
specification does not match any archived log in the repository
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
no obsolete backups found
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
specification does not match any datafile copy in the repository
validation failed for control file copy
control file copy file name=/tmp/farsync_control.ctl RECID=15 STAMP=928579660
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13070.610.928581933 RECID=26081 STAMP=928581933
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13071.308.928581937 RECID=26083 STAMP=928581936
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13072.649.928581943 RECID=26086 STAMP=928581943
Crosschecked 4 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any archived log in the repository
deleted control file copy
control file copy file name=/tmp/farsync_control.ctl RECID=15 STAMP=928579660
Deleted 1 EXPIRED objects

Remember, that if your database was just bounced, this view will be empty.
In the above example you can see the cron job that we run on our lab (crosscheck and deletes)

My impressions from Oracle OpenWorld 2018

noreply@blogger.com (Yossi Nixon) — Mon, 12 Nov 2018 15:00:00 +0000

Hi,

This was my third time at Oracle OpenWorld, and still I was thrilled like the first time. Howard Street was dressed in big, red banners everywhere—just take a picture and share it in a social network.

This time I was also there as part of the Oracle ACE program, a community of Oracle experts and advocates. We all met on several occasions during the conference, giving me the opportunity to meet in person with the people I follow and whose sessions I attend.

This year’s main keynote by Larry Ellison was about Oracle Generation 2 Cloud, which was built from the ground up for better performance, pricing, and security. I assume that Oracle’s architecture is indeed better than the competitors, as Larry kept saying, because Oracle hired the best minds in the industry - people with deep experience in cloud technology - to build Generation 2. I just hope that Oracle’s implementation will be as good as its design. Oracle announced that version 18c, and later 19c, will be fully independent and autonomous, while in the past they’ve used other words like Automatic…. and Dynamic… Even though Oracle continues to add more automatic features in each version, we DBAs still have much more work to do.

A good opportunity in this kind of conference was to ask Oracle's Senior Directors questions about software, bugs, and even future releases. I made several contacts who are helping me currently.

Beyond being in many useful technical sessions, I also joined my company’s booth - Axxana. We shared our Phoenix product, which ensures zero data loss and rapid recovery at any distance and in any topology - at a fraction of the cost of traditional disaster recovery solutions.

An integral part of OpenWorld is the night of festivities at AT&T Park, which was called this year CloudFest.18. I enjoyed the company, food, one song of the band Portugal The Man, and one song of Beck.

Yossi Nixon

Orphaned Files in ASM

noreply@blogger.com (Yossi Nixon) — Tue, 06 Nov 2018 15:00:00 +0000

Hi,
In our lab environments we test Data Guard on a daily basis, and we frequently “play” with failover, switchover, and flashback . The output of this playground is that we have some leftovers in the ASM; we call these leftovers orphan files.
To solve this, I created SQL to query ASM views against database views.
This query should run on the database (not ASM).

SET VERIFY OFF

SET LINESIZE 200
SET SERVEROUTPUT ON
SET PAGESIZE 50000

DECLARE
cmd CLOB;
BEGIN
FOR c IN (SELECT name Diskgroup
FROM V$ASM_DISKGROUP)
LOOP
FOR l
IN (SELECT 'rm ' || files files
FROM
(SELECT '+' || c.Diskgroup || files files, TYPE
FROM ( SELECT UPPER
(
SYS_CONNECT_BY_PATH (aa.name, '/')
)
files
, aa.reference_index
, b.TYPE
FROM (SELECT file_number
, alias_directory
, name
, reference_index
, parent_index
FROM v$asm_alias) aa
, (SELECT parent_index
FROM (SELECT parent_index
FROM v$asm_alias
WHERE group_number =
(SELECT group_number
FROM v$asm_diskgroup
WHERE name =
c.Diskgroup)
AND alias_index = 0)) a
, (SELECT file_number, TYPE
FROM (SELECT file_number, TYPE
FROM v$asm_file
WHERE group_number =
(SELECT group_number
FROM v$asm_diskgroup
WHERE name =
c.Diskgroup)))
b
WHERE aa.file_number = b.file_number(+)
AND aa.alias_directory = 'N'
AND b.TYPE IN
('DATAFILE'
, 'ONLINELOG'
, 'CONTROLFILE'
, 'TEMPFILE')
START WITH aa.PARENT_INDEX = a.parent_index
CONNECT BY PRIOR aa.reference_index =
aa.parent_index)
WHERE SUBSTR
(
files
, INSTR (files, '/', 1, 1)
, INSTR (files, '/', 1, 2)
- INSTR (files, '/', 1, 1)
+ 1
) =
(SELECT dbname
FROM (SELECT '/'
|| UPPER (db_unique_name)
|| '/'
dbname
FROM v$database))
MINUS
(SELECT UPPER (name) files, 'DATAFILE' TYPE
FROM v$datafile
UNION ALL
SELECT UPPER (name) files, 'TEMPFILE' TYPE
FROM v$tempfile
UNION ALL
SELECT UPPER (name) files, 'CONTROLFILE' TYPE
FROM v$controlfile
WHERE name LIKE '+' || c.Diskgroup || '%'
UNION ALL
SELECT UPPER (name), 'CONTROLFILE' TYPE
FROM v$datafile_copy
WHERE deleted = 'NO'
UNION ALL
SELECT UPPER (MEMBER) files, 'ONLINELOG' TYPE
FROM v$logfile
WHERE MEMBER LIKE '+' || c.Diskgroup || '%')))
LOOP
DBMS_OUTPUT.put_line (l.files);
END LOOP;
END LOOP;
END;
/

Sample output:

rm +DATA/MYDB/CONTROLFILE/BACKUP.11645.952252647

Personally, I am not running this script automatically. I consider it as a report of deletion candidates.
Run the commands in the ASM instance using asmcmd, for example:

ASMCMD> rm +DATA/MYDB/CONTROLFILE/BACKUP.11645.952252647

Disclaimer: Use at your own risk.

Yossi

Data Guard Broker 19c

noreply@blogger.com (Yossi Nixon) — Thu, 01 Nov 2018 13:56:00 +0000

Hi,
In an earlier version of Data Guard, when the broker had problems, one of the automatic answers I got from support was to recreate the broker configuration.
In other words:

Drop the configuration.
Create the configuration.

If your broker is simple, this is not a huge request. Actually, you could follow Step By Step How to Recreate Data Guard Broker Configuration (Doc ID 808783.1).
But in my case, using Active Data Guard with Far Sync, with many fine-tuned configurations, it was not so convenient. I had a number of ways I tried to be efficient:

I could write down all broker commands - to be able to run them again next time.
For versions 11.2.0.4 and 12.1.0.2, I found a way to query the broker and re-create a current configuration creation script (Extract Data Guard Commands).
I also had some issues trying to Drop/remove Far-Sync Configurations from broker 12.1.
On version 12.2.0.1, I found out that the broker metadata was changed, so I had to update my previous script for Extract Data Guard Commands on Oracle 12.2.
Finally on Oracle 19c, there will be new commands to export and import a broker configuration 😊

dgmgrl> export configuration to ‘meta.xml’
dgmgrl> import configuration from ‘meta.xml’

Here is a full listing, shown at Oracle OpenWorld 2018, of new features promised to be in Oracle 19c:

SLOB

noreply@blogger.com (Yossi Nixon) — Tue, 07 Aug 2018 14:30:00 +0000

SLOB is an Oracle I/O workload generation tool kit, supports testing extreme REDO logging I/O (minimal amount of CPU overhead)
The SLOB package can be downloaded from Kevin Closson's Blog: SLOB Resources
SLOB 2.4.2 requires Linux with Oracle Client

Installation

Unzip the SLOB tar.gz file in the desired directory ($SLOB).
Create tablespace for SLOB data (the script creates IOPS)

$ cd $SLOB
$ sqlplus / as sysdba @ misc/ts

Load SLOB data by running setup.sh script located in the $SLOB directory. Using two mandatory parameters:

Tablespace into which SLOB will create and load the test schemas
The number of schemas to create and load

$ ./setup.sh IOPS 8

configuration

One time compilation

$ cd $SLOB/wait_kit
$ make

Edit runtime parameter in slob.conf configuration file

$ cd $SLOB
$ vi slob.conf

Execute slob using number of SLOB schemas

$ cd $SLOB
$ ./runit.sh 8

We can edit the slob.conf file to modify some parameters.

UPDATE_PCT - Percentage of SLOB update operations
SCAN_PCT - percentage of short scan SELECT operations on a short scan table
RUN_TIME - Wall-clock duration of a SLOB test in seconds
WORK_LOOP - SLOB test duration based on iterations
SCALE - Number of database blocks / Size
SCAN_TABLE_SZ - size of the short scan table(s)
WORK_UNIT - scope of blocks being manipulated by each operation
REDO_STRESS - HEAVY -> generate significant amounts of redo logging
LOAD_PARALLEL_DEGREE - number of Oracle Database sessions concurrently inserting data
THREADS_PER_SCHEMA – sessions against each schema
DATABASE_STATISTICS_TYPE - “awr” or “statspack.”
ADMIN_SQLNET_SERVICE – separate service for admin operations
SQLNET_SERVICE_BASE – service for load balance and round robin connection
SQLNET_SERVICE_MAX - appended to SQLNET_SERVICE_BASE in a RAC testing scenario
DBA_PRIV_USER – sys / system or any DBA user
SYSDBA_PASSWD – password of the above user

Results & tools

iostat.out - input/output statistics: Read Thoughput (MB/sec), Write Throughput (MB/sec) and information about queue lengths.
mpstat.out - CPU utilization (processors related statistics)
vmstat.out - virtual memory statistics
misc/awr_info.sh - extracts interesting information from the awr.txt file

$ misc/awr_info.sh awr_pdb.txt
FILE|SESSIONS|ELAPSED|DB CPU|DB Tm|EXECUTES|LIO|PREADS|READ_MBS|PWRITES|WRITE_MBS|REDO_MBS|DFSR_LAT|DPR_LAT|DFPR_LAT|DFPW_LAT|LFPW_LAT|TOP WAIT|
awr_pdb.txt||61|0.6|1.0|695|52106|1997| 16.6|0| 53|34.8| 250|0|0|0| 192|DB CPU 33.3 54.7|

Swingbench Short cookbook commands

noreply@blogger.com (Yossi Nixon) — Mon, 23 Jul 2018 14:00:00 +0000

In this example we are using Swingbench 2.6 with Order Entry stress test - using a configuration file (SOE_Server_Side_V2.xml)

Connection String:
The connection will always be defined to pluggable database (not CDB) - if installed.

//hostname/service_name valid for driver type: Oracle oci Driver & Oracle jdbc Driver (oewizard,sbutil use only this option) .
Make sure the service name is recognized by the listener (lsnrctl services | grep Service)
Tnsnames alias valid only for driver type: Oracle oci Driver – requires Oracle 12.2 client

Creating the schema:

cd swingbench/bin
$ ./oewizard -dbap change_on_install -ts SOE -nopart -u soe -p soe -cl -df +DATA -create -scale 1 -cs //db_server/db_service

Running:
If you need to use Connection Pooling:

Change the driver type to "Oracle oci Driver"
Change the format of the connection string to tns alias
For further needed changes, look at: Application Continuity in Oracle Database 12c (12.1.0.2)

charbench - character mode benchmark
minibench - graphical minimal mode benchmark
swingbench - graphical full mode benchmark (need Xming)

Swingbench
Main parameters:

-uc: user count
-r: results file
-rt: run time for the benchmark
-cs:connection string
-a: run automatically
-cpuuser: os username of the user used to monitor cpu
-cpupass: os password of the user used to monitor cpu
-cpuloc: location/hostname of the cpu monitor

When running swingbench or minibench with "-a" parameter it starts automatically, you may want to omit this parameter, to look or change some parameters.
See more Swingbench parameters at the end of this document.

Run XLaunch (Xming) or equivalent application

$ export DISPLAY=your windows ip:0.0
$ cd swingbench/bin
$ ./swingbench -u soe -p soe -min 10 -max 200 -stats full -c ../configs/SOE_Server_Side_V2.xml -ld 500 -f -dbap change_on_install -dbau "sys as sysdba" -r scale1_100user.xml -uc 100 -rt 0:05 -cs //db_server/db_service -cpuuser db_os_owner -cpupass db_os_owner_password -cpuloc db_server -a

Minibench

$ ./minibench -u soe -p soe -min 10 -max 200 -stats full -c ../configs/SOE_Server_Side_V2.xml -ld 500 -f -dbap change_on_install -dbau "sys as sysdba" -r scale1_100user.xml -uc 100 -rt 0:05 -cs //db_server/db_service -cpuuser db_os_owner -cpupass db_os_owner_password -cpuloc db_server -a

Charbench

$ ./charbench -u soe -p soe -min 10 -max 200 -stats full -c ../configs/SOE_Server_Side_V2.xml -ld 500 -f -dbap change_on_install -dbau "sys as sysdba" -r scale1_100user.xml -uc 100 -rt 0:05 -cs //db_server/db_service -cpuuser db_os_owner -cpupass db_os_owner_password -cpuloc db_server

See swingbench database tables

$ ./sbutil -soe -u soe -p soe -cs //db_server/db_service -tables

Compress tables:

$ ./sbutil -soe -u soe -p soe -cs //db_server/db_service -dup 1 -ac -sort

Format result output to pdf

$ ./results2pdf -c results.xml

The formatting may not work properly in Linux. The solution will be to use the windows version:

C:\swingbench\winbin\results2pdf.bat -c results.xml

Combine main data of several loads to one csv

$ ./utils/parse_results.py -r scale1_400user.xml scale1_300user.xml scale1_200user.xml scale1_100user.xml -o results.csv

Compare several runs:

$ ./bmcompare -r scale1_100user.xml,scale1_200user.xml

Swingbench 2.6 Walkthrough - This screencast shows a complete walk through of Swingbench 2.6 from running a wizard to selecting a benchmark to run.

Running swingbench simultanically from several hosts

Clusteroverview Walkthrough

On coordinator_server

$ ./coordinator -g &

On other machines

$ ./charbench -c ../configs/SOE_Server_Side_V2.xml -dt thin -dbap change_on_install -uc 100 -r scale1_100user.xml -rt 0:05 -cs //db_server/db_service -g group1 -co coordinator_server -bg &

$ ./charbench -c ../configs/SOE_Server_Side_V2.xml -dt thin -dbap change_on_install-uc 100 -r scale2_100user.xml -rt 0:05 -cs //db_server/db_service -g group2 -co coordinator_server -bg &

On coordinator_server / PC

$ ./clusteroverview -rt 00:02 -co coordinator_server -c ../configs/clusteroverview.xml

Swingbench parameters:

$ ./swingbench -h
Application : Swingbench
Author : Dominic Giles
Version : 2.6.0.1082

usage: parameters:

-D	use value for given environment variable
-a	run automatically
-be	end recording statistics after. Value is in the form hh:mm.sec
-bs	start recording statistics after. Value is in the form hh:mm.sec
-c	specify config file
-cf	the location of a crendentials file for Oracle Exadata Express
-cm	maximise the charts of swingbench/minibench at startup
-co	specify/override coordinator in configuration file.
-com	specify comment for this benchmark run (in double quotes)
-cpuloc	specify/overide location/hostname of the cpu monitor.
-cpupass	specify/overide os password of the user used to monitor cpu.
-cpuuser	specify/overide os username of the user used to monitor cpu.
-cs	override connect string in configuration file
-dbap	the password of admin user (used for collecting DB Stats)
-dbau	the username of admin user (used for collecting DB stats)
-debug	turn on debugging. Written to standard out
-debugf	turn on debugging. Witten to debug.log.
-debugfine	turn on finest level of debugging
-debugg	turn on debugging via graphical window.
-di	disable transactions(s) by short name, comma separated
-dim	dimensions of swingbench/minibench
-dt	override driver type in configuration file(thin, oci, ttdirect, ttclient)
-dumptx	output transaction response times to file
-dumptxdir	directory for transaction response times files
-en	enable transactions(s) by short name, comma separated
-env	display environment configuration
-f	force data collection and run termination regardless of state
-g	distributed group identifier
-h,--help	print this message
-i	run interactively (default)
-intermax	override minimum inter transaction sleep time (default = 0)
-intermin	override minimum inter transaction sleep time (default = 0)
-ld	specify/overide the logon delay(milliseconds)
-max	override maximum intra transaction think time in configuration file
-min	override minimum intra transaction think time in configuration file
-p	override password in configuration file
-pos	starting position of swingbench/minibench
-r	specify results file
-rr	specify/overide refresh rate for charts in secs
-rt	specify/overide run time for the benchmark. Value is in the form hh:mm.sec
-stats	specify level result stats detail (full or simple)
-t </p></td><td width="452" style="border: 1px solid rgb(221, 221, 221); padding: 7px 10px; vertical-align: top; min-width: 8px;"><p style="padding: 0px;">set the window title for swingbench and minibench</p></td></tr> <tr><td width="245" style="border: 1px solid rgb(221, 221, 221); padding: 7px 10px; vertical-align: top; min-width: 8px;"><p style="padding: 0px;"> -u <username></p></td><td width="452" style="border: 1px solid rgb(221, 221, 221); padding: 7px 10px; vertical-align: top; min-width: 8px;"><p style="padding: 0px;">override username in configuration file</p></td></tr> <tr><td width="245" style="border: 1px solid rgb(221, 221, 221); padding: 7px 10px; vertical-align: top; min-width: 8px;"><p style="padding: 0px;"> -uc <user count></p></td><td width="452" style="border: 1px solid rgb(221, 221, 221); padding: 7px 10px; vertical-align: top; min-width: 8px;"><p style="padding: 0px;">override user count in configuration file.</p></td></tr> <tr><td width="245" style="border: 1px solid rgb(221, 221, 221); padding: 7px 10px; vertical-align: top; min-width: 8px;"><p style="padding: 0px;"> -wc</p></td><td width="452" style="border: 1px solid rgb(221, 221, 221); padding: 7px 10px; vertical-align: top; min-width: 8px;"><p style="padding: 0px;">wait until all session have disconnected from the database</p></td></tr> </tbody></table></div><p style="margin-top: 10px; padding: 0px; color: rgb(9, 30, 66); background-color: rgb(255, 255, 255);"><br /> </p><p style="margin-top: 10px; padding: 0px; color: rgb(9, 30, 66); background-color: rgb(255, 255, 255);">Resource: <a href="http://www.dominicgiles.com/swingbench.html">http://www.dominicgiles.com/swingbench.html</a></p><p style="margin-top: 10px; padding: 0px; color: rgb(9, 30, 66); background-color: rgb(255, 255, 255);"><br /> </p>

Steps To Shutdown / Startup The Exadata

noreply@blogger.com (Yossi Nixon) — Mon, 16 Jul 2018 13:55:00 +0000

ShutDown

Log in to the first database server as root.
Change to the OneCommand directory

# cd /opt/oracle.SupportTools/onecommand

Note whether the Grid Infrastructure is currently enabled for autostart, so that this state can be restored later:

# dcli -g dbs_group -l root /u01/app/12.1.0.2/grid/bin/crsctl config crs

Disable the Grid Infrastructure for autostart on the database servers if the previous step indicated it is currently enabled for autostart.

# dcli -g dbs_group -l root /u01/app/12.1.0.2/grid/bin/crsctl disable crs

Note: This is step is [Optional] and it can required during maintenance operation like “firmware patches” which requires to reboot the Compute Node several times.

Stop the Grid Infrastructure stack on the database servers (compute nodes):

# dcli -g dbs_group -l root "/u01/app/12.1.0.2/grid/bin/crsctl stop crs"

Verify that the Grid Infrastructure stack has shutdown successfully on the database servers. The following command should show no output if the GI stack has shutdown:

# dcli -g dbs_group -l root "ps -ef | grep diskmo[n]"

[Optional] Disable email/SNMP alerts if applicable. If alerts will be enabled, first note the notification method in use so that it can be restored later. The following command will show the current notification method:

# dcli -g cell_group -l root "su - celladmin -c \"cellcli -e list cell attributes name,notificationMethod\""

Now set the notification method to null to disable alerts:

# dcli -g cell_group -l root "su - celladmin -c \"cellcli -e alter cell notificationMethod=null\""

Shutdown all the cells:

# dcli -g cell_group -l root "su - celladmin -c \"cellcli -e alter cell shutdown services all \""

Power off all cells:

# dcli -g cell_group -l root poweroff

Copy the /opt/oracle.SupportTools/onecommand/dbs_group to /opt/oracle.SupportTools/onecommand/dbs_group2:

# cp /opt/oracle.SupportTools/onecommand/dbs_group /opt/oracle.SupportTools/onecommand/dbs_group2

Remove the “first compute node name” from the /opt/oracle.SupportTools/onecommand/dbs_group2 file (using any editor, e.g. vi).
Power off all the compute nodes (except compute node # 1):

# dcli -g dbs_group2 -l root poweroff

Power off the compute node #1:

# poweroff

Now, both Cell Servers & Compute nodes are down for maintenance.

Startup

Power “compute node #1” on, by using the power button on the front panel of the Exadata Storage Servers.
Change to the OneCommand directory

# cd /opt/oracle.SupportTools/onecommand

Power the cells on, either by using the power button on the front panel of the Exadata Storage Servers, or by executing the following script from the first database server (from Compute node #1):

for host in `cat cell_group`; do
echo ${host}: `ipmitool -H ${host}-ilom -U root -P welcome1 chassis power on`
done

Wait until all the Cell servers are up, then power the compute nodes on, either by using the power button on the front panel of the Exadata Storage Servers, or by executing the following script from the first database server:

for host in `cat dbs_group2`; do
echo ${host}: `ipmitool -H ${host}-ilom -U root -P welcome1 chassis power on`
done

Verify that all Exadata Storage Servers have booted successfully:

# dcli -g cell_group -l root hostname

Verify all the cells are up (MS, RS & CELLSRV services must be running):

# dcli -g cell_group -l root "su - celladmin -c \"cellcli -e list cell detail \""

Reenable email/SNMP alerts if they were disabled during the pre-maintenance steps. The example below illustrates the command to use if both email and SMTP alerting used, but adjust the command as necessary to restore the notification method noted during the pre-maintenance steps.

# dcli -g cell_group -l root "su - celladmin -c \"cellcli -e alter cell notificationMethod=\'mail,snmp\'\""

Start the Grid Infrastructure stack on the first database server

# /u01/app/12.1.0.2/grid/bin/crsctl start crs

Wait until the Grid Infrastructure stack has started successfully on the first database server. To check the status of the Grid Infrastructure stack, run the following command and verify that the “ora.asm” instance is started. Note that the command below will continue to report that it is unable to communicate with the Grid Infrastructure software for several minutes after issuing the “crsctl start crs” command above:

# /u01/app/12.1.0.2/grid/bin/crsctl status resource -t

Start the Grid Infrastructure stack on all remaining database servers with a 1-minute delay between servers. Note that the command below will issue the start command for all database servers, including the first database server where it is already started. The output for the first server will therefore indicate that the software is already started, but this message can be safely ignored:

for h in `cat dbs_group2`; do
echo "Starting CRS on host $h"
ssh $h /u01/app/12.1.0.2/grid/bin/crsctl start crs
echo "Waiting 60 seconds…"
sleep 60
done

Monitor the startup progress (this could take several minutes):

# /u01/app/12.1.0.2/grid/bin/crsctl status resource -t

Reenable the Grid Infrastructure for autostart if it was found to be enabled for autostart at the beginning of the pre-maintenance steps.

# dcli -g dbs_group -l root /u01/app/12.1.0.2/grid/bin/crsctl enable crs

Note: If dcli/ssh is not enabled due to security reasons, then you will need to connect and execute the above steps on each node at the time.

Install Oracle Internet Directory (OID) in Standalone mode

noreply@blogger.com (Yossi Nixon) — Wed, 04 Jul 2018 18:05:00 +0000

Oracle Internet Directory (OID) is an LDAP server which uses an Oracle database as a datastore. Client machines can use the OID for all TNS lookups. With OID. Once all client machines are configured correctly any modifications to the TNS lookups can be done from a central location reducing the amount of client machine administration.

This guide is about installing and using Oracle Internet Directory in Standalone mode, no need for Oracle Fusion Middleware Infrastructure.

Step-by-step guide

Download Oracle Internet Directory (Part of identity management) from
http://www.oracle.com/technetwork/middleware/id-mgmt/downloads/index.html

Install OID, follow instructions:
https://docs.oracle.com/en/middleware/lifecycle/12.2.1.3/inoim/installing-product-software.html#GUID-5D47117E-3983-4BED-BADB-CB7DDD63DB34

Starting the Installation Program:

./fmw_12.2.1.3.0_oid_linux64.bin

Configure the Domain:

$ORACLE_HOME/oracle_common/common/bin/config.sh

Start the Servers for Standalone Oracle Internet Directory:

DOMAIN_HOME/bin/startNodeManager.sh

Perform Initial Oracle Internet Directory Setup:

$ORACLE_HOME/oracle_common/common/bin/wlst.sh

nmConnect(username='wls_user',password='password',domainName='base_domain')

oid_setup(orcladminPassword='password',odsPassword='password',realmDN='dc=us,dc=oracle,dc=com')

Start the Repository Creation Utility:

$ORACLE_HOME/oracle_common/bin/rcu

fix permissions (Doc ID 947285.1):

vi ldifFile

dn: cn=oid1,cn=osdldapd,cn=subconfigsubentry
changetype: modify
replace: orclAnonymousBindsFlag
orclAnonymousBindsFlag: 1

ldapmodify -D cn=orcladmin -q -p 3060 -h oid -f ldifFile

Modify the file sqlnet.ora in $ORACLE_HOME/network/admin directory need to have the following line:

NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES,EZCONNECT)

Create the file ldap.ora in the $ORACLE_HOME/network/admin directory location.
This file has information of the server where the LDAP directory resides along with the port numbers for normal and secure connections

DEFAULT_ADMIN_CONTEXT = "dc=us,dc=oracle,dc=com"
DIRECTORY_SERVERS = (oid.mydomain.local:3060:3131)
DIRECTORY_SERVER_TYPE = OID
NAMES.DEFAULT_DOMAIN = mydomain.local

Add tns entry in OID (TESTTNS will be the tns alias):

vi add_entry.ldif

dn: cn=TESTTNS,cn=OracleContext,dc=us,dc=oracle,dc=com
objectclass: top
objectclass: orclNetService
cn: TESTTNS
orclNetDescString: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oid)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=oid)))
orclnetdescname=000:cn=DESCRIPTION_0

Load the ldif file:

ldapadd -h oid -p 3060 -c -D "cn=orcladmin" -f add_entry.ldif -w mypassword

We will now check a connection to the alias TESTTNS ensuring that the SQL*Net connection details are now obtained from the OID LDAP directory and not using information stored in the local tnsnames.ora file.

Note: the output of the tnsping command shows that the LDAP directory was accessed to resolve the alias

tnsping TESTTNS

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 02-JUL-2018 14:41:49

Copyright (c) 1997, 2016, Oracle. All rights reserved.

Used parameter files:
/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oid.mydomain.local)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=oid)))
OK (0 msec)

Managing AWR in Active Data Guard Standby Databases

noreply@blogger.com (Yossi Nixon) — Mon, 07 May 2018 15:43:00 +0000

Problem
- Automatic Workload Repository (AWR) snapshots cannot be taken in a read-only standby environment.
- Performance monitoring and analysis is limited to basic STATSPACK functionality .

Solution
In Oracle Database 12.2, the AWR framework is enhanced to support capture of remote snapshots from any generic database, including Active Data Guard (ADG) databases. This framework is called the Remote Management Framework (RMF).
• A target catalog database collects snapshots from the remote databases (sources).
• Snapshots can be collected automatically or manually.
• AWR tables on the catalog database accumulate snapshot data from all sources via database links.
• Source databases must be registered on the catalog via new DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE API

These are the basic steps for setting up the RMF topology for generating AWR on the physical standby:
1. Configure database nodes to add to the topology.
2. Create the topology.
3. Register database nodes with the topology.
4. Create remote snapshots.
5. Generate the AWR.

In this example, we set the repository in the primary database; therefore it is called the target system. The standby database is called the source system.
In the following example the primary database name is: pdb and the standby database name is: sdb. For clarify, the sqlprompt was changed from SQL> to the database role Primary/Standby (for example: set sqlprompt "PRIMARY> ")
The RMF APIs are declared in the PL/SQL package DBMS_UMF. All the AWR-related operations in RMF can be performed only by the SYS$UMF user. Since the SYS$UMF user is locked by default, it must be unlocked before deploying the RMF topology:

PRIMARY> alter user sys$umf identified by sysumf account unlock;

Create the database link between the primary database and the standby database and vice versa:

PRIMARY> create DATABASE LINK DBLINK_pdb_to_sdb CONNECT TO sys$umf IDENTIFIED BY sysumf using 'sdb';
PRIMARY> create DATABASE LINK DBLINK_sdb_to_pdb CONNECT TO sys$umf IDENTIFIED BY sysumf using 'pdb';

Each database node in a topology must be assigned a unique name (default is DB_UNIQUE_NAME):

PRIMARY> exec DBMS_UMF.configure_node ('PDB');

Since the standby database is remote to the target system (the primary database), we can register it via the corresponding database link:

STANDBY> exec DBMS_UMF.configure_node ('sdb', 'DBLINK_sdb_to_pdb');

Create the RMF topology and designate the node on which it is executed as the destination node for that topology:

PRIMARY> exec DBMS_UMF.create_topology ('Topology_1');
PRIMARY> select * from DBA_UMF_TOPOLOGY;
PRIMARY> select * from DBA_UMF_REGISTRATION;

PRIMARY> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'sdb');
PRIMARY> select * from DBA_UMF_SERVICE;

Create a remote snapshot using the RMF:

PRIMARY> exec DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT('sdb');

Create AWR Report.

PRIMARY> @?/rdbms/admin/awrrpti.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the name of the format at the prompt. Default value is 'html'.

'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report

Enter value for report_type:

Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
3810102760 1 PDB sdb Local_host
* 3393159014 1 PDB pdb Remote_Host
Enter value for dbid: 3810102760

Source:
Gathering Database Statistics - https://docs.oracle.com/database/122/TGDBA/gathering-database-statistics.htm#TGDBA232
DBMS_UMF - https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_UMF.html
DBMS_WORKLOAD_REPOSITORY - https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_WORKLOAD_REPOSITORY.html
Oracle Active Data Guard: Power, Speed, Ease, and Protection - http://www.oracle.com/technetwork/database/availability/con6531-oracle-active-data-guard-3334919.pdf

Extract Data Guard Commands on Oracle 12.2

noreply@blogger.com (Yossi Nixon) — Thu, 08 Feb 2018 16:33:00 +0000

Hi,

These days I am starting to work on Oracle 12.2, leaving behind the old versions.
Since my main domain is Oracle Data Guard, I posted a blog about Oracle 12c Release 2 New Features for Active Data Guard, one of a big new feature I missed is that the internal Data Guard broker configuration was changed dramatically on 12.2.
Last week, I tried to "Extract Data Guard Commands", and realized it is not working as before :(
So I sat down and adjusted it to work on 12.2, this time I enhanced the output with some more important information.

Run the following script as sysdba
displayconfig122.sql:

PROMPT disable FAST_START FAILOVER;
PROMPT disable configuration;
PROMPT remove configuration;
DECLARE
rid INTEGER;
indoc VARCHAR2 (4000);
outdoc VARCHAR2 (4000);
p INTEGER;
z XMLTYPE;
y CLOB;
v_xml XMLTYPE;
tout VARCHAR2 (4000);
db_type VARCHAR2 (10);
db_headers_commands CLOB;
db_commands CLOB;
general_commands CLOB;
db_commands_RedoRoutes CLOB;
v_last_role VARCHAR2 (50) := NULL;
v_new_role VARCHAR2 (50);
v_protect_mode_no NUMBER;
protect_mode VARCHAR2 (50);
v_fast_start_failover VARCHAR2 (50);
v_enabled VARCHAR2 (50);
BEGIN
indoc := '';
y := NULL;
rid := dbms_drs.do_control (indoc);
outdoc := NULL;
p := 1;
WHILE (outdoc IS NULL)
LOOP
outdoc := dbms_drs.get_response (rid, p);
y := y || TO_CLOB (outdoc);
END LOOP;
BEGIN
WHILE (outdoc IS NOT NULL)
LOOP
p := p + 1;
outdoc := dbms_drs.get_response (rid, p);
y := y || TO_CLOB (outdoc);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
z := XMLType (y);
dbms_drs.delete_request (rid);
SELECT XMLQUERY ('/DRC' PASSING z RETURNING CONTENT) INTO v_xml FROM DUAL;
FOR l
IN (WITH drc_data
AS (SELECT xt.*
FROM XMLTABLE ('/DRC'
PASSING v_xml
COLUMNS conf PATH '/DRC/@name'
, protect_mode PATH '/DRC/@protect_mode'
, fast_start_failover PATH '/DRC/@fast_start_failover'
, enabled PATH '/DRC/@enabled'
, valuess XMLTYPE PATH '/DRC/SITE') xt)
, site_data
AS (SELECT conf
, protect_mode
, fast_start_failover
, enabled
, xt2.*
FROM drc_data dd
LEFT OUTER JOIN XMLTABLE ('/SITE' PASSING dd.valuess COLUMNS name PATH '/SITE/@name', id PATH '/SITE/@site_id') xt2
ON 1 = 1)
SELECT *
FROM site_data)
LOOP
v_protect_mode_no := l.protect_mode;
v_fast_start_failover := l.fast_start_failover;
v_enabled := l.enabled;
indoc := '';
y := NULL;
rid := dbms_drs.do_control (indoc);
outdoc := NULL;
p := 1;
WHILE (outdoc IS NULL)
LOOP
outdoc := dbms_drs.get_response (rid, p);
y := y || TO_CLOB (outdoc);
END LOOP;
BEGIN
WHILE (outdoc IS NOT NULL)
LOOP
p := p + 1;
outdoc := dbms_drs.get_response (rid, p);
y := y || TO_CLOB (outdoc);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
z := XMLType (y);
SELECT XMLQUERY ('/RESOURCE' PASSING z RETURNING CONTENT) INTO v_xml FROM DUAL;
FOR q
IN (WITH resource_data
AS (SELECT xt.*
FROM XMLTABLE ('/RESOURCE'
PASSING v_xml
COLUMNS res_id PATH '/RESOURCE/@res_id', valuess XMLTYPE PATH '/RESOURCE/PROPERTY_LIST/VALUE') xt)
, values_data
AS (SELECT res_id, xt2.*
FROM resource_data dd
LEFT OUTER JOIN
XMLTABLE ('/VALUE'
PASSING dd.valuess
COLUMNS name PATH '/VALUE/@name', VALUE PATH '/VALUE/@value', property_type PATH '/VALUE/@property_type') xt2
ON 1 = 1)
SELECT *
FROM values_data
WHERE VALUE IS NOT NULL
AND name NOT IN ('ENABLED'
, 'STATUS'
, 'ERROR_NUM'
, 'ERROR_TEXT'
, 'ClusterDatabase'
, 'DbChangeCritical'
, 'DbIsCritical'
, 'DbDisplayName'
, 'GUIProperty3'
, 'IncarnationTable'
, 'SRLStatus'
, 'SidName'
, 'HostName'
, 'SRLStatus'
, 'DataGuardSyncLatency'
, 'InstanceName'))
LOOP
v_last_role := v_new_role;
SELECT UTL_RAW.cast_to_varchar2 (HEXTORAW (value_raw))
INTO v_new_role
FROM x$drc
WHERE attribute = 'role' AND object_id = q.res_id;
IF v_last_role <> v_new_role OR v_last_role IS NULL
THEN
CASE v_new_role
WHEN 'PRIMARY'
THEN
outdoc :=
'create configuration ' || l.conf || ' as primary database is ''' || l.name || ''' connect identifier is ''' || l.name || ''';';
db_type := 'database';
db_headers_commands := db_headers_commands || CHR (10) || outdoc;
WHEN 'PHYSICAL'
THEN
outdoc := 'add database ''' || l.name || ''' as connect identifier is ''' || l.name || ''';';
db_type := 'database';
db_headers_commands := db_headers_commands || CHR (10) || outdoc;
WHEN 'FAR_SYNC_INSTANCE'
THEN
outdoc := 'add far_sync ''' || l.name || ''' as connect identifier is ''' || l.name || ''';';
db_type := 'far_sync';
db_headers_commands := db_headers_commands || CHR (10) || outdoc;
END CASE;
END IF;
IF db_type = 'far_sync'
AND q.name IN ('DelayMins'
, 'ApplyInstanceTimeout'
, 'ApplyLagThreshold'
, 'ApplyParallel'
, 'StandbyFileManagement'
, 'ArchiveLagTarget'
, 'DbFileNameConvert'
, 'LsbyMaxSga'
, 'LsbyMaxServers'
, 'ApplyInstances'
, 'LsbyMaxEventsRecorded'
, 'StaticConnectIdentifier')
THEN
NULL;
ELSE
IF q.name = 'RedoRoutes'
THEN
db_commands_RedoRoutes :=
'edit '
|| db_type
|| ' '
|| l.name
|| ' set property '
|| q.name
|| ' = '''
|| q.VALUE
|| ''';'
|| CHR (10)
|| db_commands_RedoRoutes;
ELSE
--DBMS_OUTPUT.put_line ( 'edit ' || db_type || ' ' || l.name || ' set property ' || q.name || ' = ''' || q.VALUE || ''';' );
db_commands :=
db_commands || CHR (10) || 'edit ' || db_type || ' ' || l.name || ' set property ' || q.name || ' = ''' || q.VALUE || ''';';
END IF;
END IF;
END LOOP;
dbms_drs.delete_request (rid);
END LOOP;
indoc := '';
y := NULL;
rid := dbms_drs.do_control (indoc);
outdoc := NULL;
P := 1;
WHILE (outdoc IS NULL)
LOOP
outdoc := dbms_drs.get_response (rid, P);
y := y || TO_CLOB (outdoc);
END LOOP;
BEGIN
WHILE (outdoc IS NOT NULL)
LOOP
p := p + 1;
outdoc := dbms_drs.get_response (rid, p);
y := y || TO_CLOB (outdoc);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
z := XMLTYPE (y);
SELECT XMLQUERY ('/RESOURCE/PROPERTY_LIST/VALUE' PASSING z RETURNING CONTENT) INTO v_xml FROM DUAL;
FOR C IN (SELECT name, VALUE
FROM XMLTABLE ('/VALUE' PASSING v_xml COLUMNS NAME PATH '/VALUE/@name', VALUE PATH '/VALUE/@value')
WHERE VALUE IS NOT NULL
AND NAME NOT IN ('ENABLED'
, 'STATUS'
, 'ERROR_NUM'
, 'ERROR_TEXT'
, 'MIV'
, 'PRIMARY_SITE_ID'
, 'HEALTH_CHECK_INTERVAL'
, 'HEALTH_CHECK_ENABLED'
, 'DRC_UNIQUE_ID'
, 'DRC_UNIQUE_ID_SEQUENCE'
, 'EXT_COND'
, 'OVERALL_PROTECTION_MODE'
, 'ObserverHB'
, 'FSFO_MIV'
, 'MANAGED_STANDBY_MASK'
, 'RoleChangeHistory'
, 'FastStartFailoverConditions'
, 'FastStartFailoverTgtSwitchInt'
, 'FastStartFailoverOBID1'
, 'FastStartFailoverOBID2'
, 'FastStartFailoverOBID3'
, 'ObserverVersion1'
, 'ObserverVersion2'
, 'ObserverVersion3'
, 'FastStartFailoverMode'
, 'Configuration_Name'
, 'ObserverName1'
, 'ObserverName2'
, 'ObserverName3'))
LOOP
general_commands := general_commands || CHR (10) || 'edit configuration set property ' || c.NAME || ' = ''' || c.VALUE || ''';';
END LOOP;
dbms_drs.delete_request (rid);
DBMS_OUTPUT.put_line (db_headers_commands);
DBMS_OUTPUT.put_line (db_commands);
DBMS_OUTPUT.put_line ('EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;');
IF v_enabled = 'yes'
THEN
DBMS_OUTPUT.put_line ('enable configuration;');
END IF;
DBMS_OUTPUT.put_line (db_commands_RedoRoutes);
CASE v_protect_mode_no
WHEN 3
THEN
protect_mode := 'MAXPERFORMANCE';
WHEN 2
THEN
protect_mode := 'MAXAVAILABILITY';
WHEN 1
THEN
protect_mode := 'MAXPROTECTION';
END CASE;
IF v_protect_mode_no <> 3
THEN
DBMS_OUTPUT.put_line ('EDIT CONFIGURATION SET PROTECTION MODE AS ' || protect_mode || ';');
END IF;
IF v_fast_start_failover = 'ENABLED'
THEN
DBMS_OUTPUT.put_line ('ENABLE FAST_START FAILOVER;');
END IF;
DBMS_OUTPUT.put_line ('show configuration;');
DBMS_OUTPUT.put_line (general_commands);
END;
/

Enjoy

Yossi Nixon

Chief Database Architect

Twitter: @YossiNixon

Linkedin: https://www.linkedin.com/in/ynixon/

Coloring alert.log output via tail and less

noreply@blogger.com (Yossi Nixon) — Mon, 29 Jan 2018 17:00:00 +0000

Making my day to day job easier and convenient I like having some utilities and some aliases.

We, the DBAs nation, have a need to look at the alert.log file frequently. So basically, I did the following:
- Created a script to tail my alert.log file.
- Colored the important words.
- Added an alias to that script

$ vi tail_alert.sh

#!/bin/bash

export SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
source ${SCRIPT_DIR}/platform.env

ECHO_RED="\E[1;40;31m"
ECHO_STD="\E[1;40;37m"

LIGHT_RED=`echo -e '\033[1;40m\033[1;31m'`
RED=`echo -e '\033[1;40m\033[0;31m'`
LIGHT_PURPLE=`echo -e '\033[1;40m\033[0;35m'`
GREEN=`echo -e '\033[1;40m\033[32m'`
NORMAL=`echo -e '\033[0m'`

if [ "$1" = "" ]
then
ALERT_FILE=${ORACLE_BASE}/diag/${DIR_TYPE}/${ORACLE_UNQNAME,,}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log
if [ ! -s ${ALERT_FILE} ]
then
echo
echo -e "${ECHO_RED}please provide alert.log file name${ECHO_STD}"
echo
exit
fi
else
ALERT_FILE="$1"
fi

WARNING_PATTERN="ORA-\|annot\| not\|WARNING\|cannot allocate\|not complete\|LGWR: Attempting destination\|network reconnect\|LGWR: Destination LOG_ARCHIVE_DEST_\|is UNSYNCHRONIZED\|All online logs need archiving\|Examine archive trace files for archiving errors\|hung\|Killing\|Terminating\|WARN:\|Possible network disconnect\|Archive log rejected\|request rejected\|dead\|failed\|shutting down\|shutdown\|disabled"
ERROR_PATTERN="ORA-[^:][^ ]*\|TNS-[^:][^ ]*\|Error\|Archival stopped\|Fatal"
GOOD_PATTERN="continu\|STARTING\|Completed:\|COMPLETE\|Starting ORACLE instance\|Success\|succeeded"

tail -n30 -f ${ALERT_FILE} | ${SED} "s/${WARNING_PATTERN}/${LIGHT_PURPLE}&${NORMAL}/gi;s/${ERROR_PATTERN}/${LIGHT_RED}&${NORMAL}/gi;s/${GOOD_PATTERN}/${GREEN}&${NORMAL}/gi"

The last step to make it the easiest way to run, is adding an alias to the script

alias talert='/home/oracle/scripts/tail_alert.sh'

The same trick for just viewing the file via less

$ vi less_alert.sh

#!/bin/bash

export SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
source ${SCRIPT_DIR}/platform.env

ECHO_RED="\E[1;40;31m"
ECHO_STD="\E[1;40;37m"

LIGHT_RED=`echo -e '\033[1;40m\033[1;31m'`
RED=`echo -e '\033[1;40m\033[0;31m'`
LIGHT_PURPLE=`echo -e '\033[1;40m\033[0;35m'`
GREEN=`echo -e '\033[1;40m\033[32m'`
NORMAL=`echo -e '\033[0m'`

if [ "$1" = "" ]
then
ALERT_FILE=${ORACLE_BASE}/diag/${DIR_TYPE}/${ORACLE_UNQNAME,,}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log
if [ ! -s ${ALERT_FILE} ]
then
echo
echo -e "${ECHO_RED}please provide alert.log file name${ECHO_STD}"
echo
exit
fi
else
ALERT_FILE="$1"
fi

WARNING_PATTERN="ORA-\|annot\| not\|WARNING\|cannot allocate\|not complete\|LGWR: Attempting destination\|network reconnect\|LGWR: Destination LOG_ARCHIVE_DEST_\|is UNSYNCHRONIZED\|All online logs need archiving\|Examine archive trace files for archiving errors\|hung\|Killing\|Terminating\|WARN:\|Possible network disconnect\|Archive log rejected\|request rejected\|dead\|failed\|shutting down\|shutdown\|disabled"
ERROR_PATTERN="ORA-[^:][^ ]*\|TNS-[^:][^ ]*\|Error\|Archival stopped\|Fatal"
GOOD_PATTERN="continu\|STARTING\|Completed:\|COMPLETE\|Starting ORACLE instance\|Success\|succeeded"

cat ${ALERT_FILE} | ${SED} "s/${WARNING_PATTERN}/${LIGHT_PURPLE}&${NORMAL}/gi;s/${ERROR_PATTERN}/${LIGHT_RED}&${NORMAL}/gi;s/${GOOD_PATTERN}/${GREEN}&${NORMAL}/gi" | less -R

And again adding an alias to the script

alias alert='/home/oracle/scripts/less_alert.sh'

Sample output:

Enjoy

Oracle DBA Place

Creating trace file, writing to alert.log and retrieve using Amazon RDS for Oracle

Connect to Oracle Database server through ssh tunnel

Using ssh X11 tunnel through a bastion host to connect to a database server

Theoretical Steps:

Actual Steps

How to install Oracle Linux in kvm without a need to X server

rlwrap and auto completion in sqlplus

Oracle 19c Updates on Active Data Guard using DML Redirection

Current configuration

Basic setup

Redo Route configuration

Primary

Far Sync

LogXptMode configuration

Far sync

Standby

Prepare the primary database

Connect as sys

Enable the feature ADG_REDIRECT_DML

Connect to pluggable database

Create a test user

Connect to the test user

Connect to the standby database

Connect as sys

Enable the feature ADG_REDIRECT_DML

Connect to the test user

Do the magic 😊

Check that inserting to a regular table is working on the standby database

Check that inserting to a global temporary table is working on the standby database

Check if primary database is updated

Oracle Database Auto Discovery

Identifying all instances on the current machine

Option 1:

Option 2:

oracle SID is fdb and process id is 11459

Option 3:

Option 4:

Option 5:

Identify oracle_home

Option 1:

option 2:

option 3:

option 4:

Checking from the Far sync - What is the name of the Primary Database

Checking from the Far sync - What is the name of the Standby Database

SQL> SELECT db_unique_name FROM v$archive_dest WHERE destination IS NOT NULL and TARGET not in ('LOCAL','PRIMARY'); DB UNIQUE NAME ------- sdb

Identifying primary and standby database hosts and ports via tnsping (from previous results)

Creating SNMP Plug-In for Enterprise Manager Cloud Control

Preparing Plug-in

Log in to OEM:

Download the EDK zip archive:

Restrictions and Guidelines

Folder Structure

Metadata File

A few points:

Default Collection

Plug-in Deployment

Set Your Environment

Validate that the plug-in files are valid:

Create the plug-in:

Import the plug-in to OEM:

Deploy the plug-in to the OMS:

Verify the deployment progress of the plug-in:

Deploy the plug-in to the agent:

Verify the deployment progress of the plug-in:

Plug-in Undeployment

Undeploy plug-in from the agent:

Verify the Undeployment progress of the plug-in:

Undeploy the plug-in from the OMS:

Verify the UNdeployment progress of the OMS

Adding Targets

Testing and Debugging

References

Enterprise Manager Cloud Control, SNMP, and Plug-Ins – The Background

Setup additional local DNS - to add scan addresses

My Testing Environment

My Private DNS Server

Client Machine to use DNS

Current Domain DNS

SQL> SELECT db_unique_name FROM v$archive_dest WHERE destination IS NOT NULL and TARGET not in ('LOCAL','PRIMARY');

DB

UNIQUE

NAME

-------

sdb