OCI: ORDS 18.x redirection and logging

The tips below are applicable for ORDS 18.x standalone, for example after a deployment on an OCI dbsystem.

#1 To configure the access.log, update the file standalone.properties available from the /u01/app/oracle/product/ords/config/ords/standalone/standalone.properties configuration file to add the following property

standalone.access.log=/tmp/ords

Where /tmp/ords is going to be a directory that will hold the daily logfiles. Then restart the ords standalone process.

 

#2 To implement some redirect for the internal ords port to be accessible from the standard https port 443, instead of the native port 8443, create the file https (for example) in /etc/xinetd.d, with the following content:

service jetty-https
{
disable = no
type = UNLISTED
socket_type = stream
protocol = tcp
wait = no
redirect = localhost 8443
port = 443
user = nobody
}

Then restart the xinetd.d service

service xinetd restart

Check that the firewall and the ingress rules allow inbound access to 443.

Advertisements

APEX: ORA-02291: integrity constraint (APEX_180100.WWV_FLOW_FND_GU_INT_G_FK) violated – parent key not found

Getting the error ORA-02291: integrity constraint (APEX_180100.WWV_FLOW_FND_GU_INT_G_FK) violated – parent key not found while importing an apex workspace.

Issue has to do with users groups that are not part of the import. This is the bug 16781538, still unresolved with 18.1.

Solution is to edit the import file, locate the users that belong to group and change in the www_flow_fnd_user_api. create_fnd_user call(s) that create issues, for example:

  p_group_ids => '1860188076423573:1860279868423578:1860329781423578:'

to

  p_group_ids => ''

 

AWRW: AWR Datawarehouse cleanup

I have one AWR Warehouse that has been running for a few years now. Time for a cleanup after EMCC 13.3 upgrade.

The EMCC UI is confusing as it show the view of the AWR Warehouse instance, instead of filtering whatever database information relevant for the current EMCC instance we are looking at, remember that the AWR Warehouse can be connected to many EMCC.

Since the UI does not offer all capabilities to troubleshoot, Here are a few tips:

#1 Use the emcli from all EMCC clients.

To view all source db:

$ORACLE_HOME/bin/emcli awrwh_list_src_dbs

To delete a source db:

$ORACLE_HOME/bin/emcli awrwh_remove_src_db -target_name=<dbname> -target_type=<rac_database or database_instance>'

Unfortunately the command above will hit the EMCC metadata, when, when a target is deleted in EMCC, the snapshots located in the AWR Warehouse are not deleted. So this command has many chance to fail with an error target not found, when the database is long gone.

Solution is to cleanup the AWR Warehouse database directly.

Connect with dbsmp.

To view all source databases, check the table CAW_SRC_DBS. Possibly, identify the em_id that corresponds to obsolete EMCC instances.

To delete a repository (remember that the AWR Warehouse can connect AWRs from many EMCC):

SQL> execute mgmt_caw_load.cleanup(<the emid>);

To mark a database as deleted:

SQL> execute mgmt_caw_load.mark_db_deleted<the em id>, '<db_name>','rac_database' ir 'oracle_database');

Do not forget to commit.

TNS-12537 TNS-12560 TNS-00507

To address the following error while start a listener on OL6, set LD_BIND_NOW to 1. This variable may have to be set in the context of a EMCC agent.

$ lsnrctl start LISTENER_DBCLONE

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 02-AUG-2018 15:27:07
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait...
TNS-12537: TNS:connection closed
TNS-12560: TNS:protocol adapter error
TNS-00507: Connection closed
Linux Error: 29: Illegal seek

 

$ export LD_BIND_NOW=1

 

$ lsnrctl start LISTENER_DBCLONE

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 02-AUG-2018 15:28:08

Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/<myhost>/listener_dbclone/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<myhost>.<mydomain>)(PORT=1523)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<myhost>.<mydomain>)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DBCLONE
...
The command completed successfully

 

WLS: Entropy setup on OL

I was getting some hanging, clearly related to entropy while trying to upgrade an EMCC instance from 13.2 to 13.3.

This note is about to check and increase the entropy on a OL6.9 compute.

 

#1 Verify the entropy values

cat /proc/sys/kernel/random/entropy_avail

Recommended value should be higher than 1000

 

#2 Install rng

yum install rng-utils

 

#3 Start the rng process

# rngd -r /dev/urandom -o /dev/random -t 1

This command can be moved on /etc/rc.local

 

#4 Verify that the entropy is increased:

cat /proc/sys/kernel/random/entropy_avail