Install stress-ng
wget http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/e/epel-release-7-13.noarch.rpm
rpm -Uvh epel-release*rpm
yum install -y stress-ng
rpm -e epel-release-7-13.noarch
Start Stress Test
#To run 8 CPU stressors with a timeout of 60 seconds and a summary at the end of operations.
stress-ng --cpu 8 --timeout 600 --metrics-brief
Create /usr/local/bin/warmup. Instance specific setting goes here.
cat << EOF > /usr/local/bin/warmup
#!/bin/sh
# warmup: sync displayName data into our index and mark as healthy for load balancer
DISPLAY_NAME=`curl -H "Authorization: Bearer Oracle" -Ls http://169.254.169.254/opc/v2/instance/displayName`
cat << EOF > /opt/oracle/ords/config/ords/standalone/doc_root/index.html
<h3>$DISPLAY_NAME</h3>
<img style="max-width: 100%; height: auto; width: auto;" src="oci.jpg">
EOF
Create /etc/systemd/system/warmup.service
cat << EOF > /etc/systemd/system/warmup.service
[Unit]
After=network.target
[Service]
ExecStart=/usr/local/bin/warmup
[Install]
WantedBy=default.target
EOF
Setup warmup as service
systemctl start warmup
systemctl enable warmup
systemctl status warmup
Reboot the instance and ensure everything comes up. If okay, then Create Custom Image from this instance, ords-as-v1
Create instance-config-ords-as-v1.json, fix compartmentId, ssh_authorized_keys, imageId
cat << EOF > instance-config-ords-as-v1.json
{
"instanceType": "compute",
"launchDetails": {
"compartmentId": "ocid1.compartment.oc1..aaaaaaaarocn3npultgruh5iwghhvor6s3kairokq4mil5bp52va6qkk7x6a",
"createVnicDetails": {
},
"metadata": {
"ssh_authorized_keys": "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDuPoZBbXjC/5ojt+ECoIj5KGmLHWPPreWcjkp/1metOBgRv8f6W7w615+kRcrrdtyB5Tk6MzIs6CmF8RZ1BkkSankhHG62aKkqXn7T9VDenvEHaJpJqQRkhkYzJKyYqL+04O942gSgv8Kpw1IpFWvznfelf30xaxQzcLa0tMjYvOmqTmeAndEM3E1ZVMcEq3r3OlTTjCyfBPsdRPV2hFClvQziueRrUF61lhLotPUkCKxc6Iie+OpqW5hhU8vypFT0MAB6hoTH7EO7BGmQWInQlO3Pt4m7q9dNSee731TzRceDFa5cC/uigeAFgjEY8lwM5CFrcgMW3n3B3BpfumQv PC@PCLAPPY"
},
"shape": "VM.Standard.E3.Flex",
"shapeConfig": {
"memoryInGBs": 16.0,
"ocpus": 1.0
},
"sourceDetails": {
"bootVolumeSizeInGBs": null,
"imageId": "ocid1.image.oc1.ca-toronto-1.aaaaaaaabpzh6hlkg7jrdmm4eghhzhb3pwbk7pevvkn2cxqm3pk7s46fjn5q",
"sourceType": "image"
}
}
}
EOF
Create instance configuration via OCI
oci compute-management instance-configuration create --instance-details file://instance-config-ords-as-v1.json --compartment-id ocid1.compartment.oc1..aaaaaaaarocn3npultgruh5iwghhvor6s3kairokq4mil5bp52va6qkk7x6a --display-name instance-config-ords-as-v1
# Get instance configuration details sample in JSON
oci compute-management instance-configuration create --generate-param-json-input instance-details
# Get instance configuration details
oci compute-management instance-configuration get --instance-configuration-id ocid1.instanceconfiguration.oc1.ca-toronto-1.aaaaaaaaxljbut4sn5hwqybubuarqr2lb5omshiog27bta4dcyvtydldetaa
From Instance Configurations, create Instance Pool, assign Load Balancer and create Autoscaling Configuration
We can run stress-ng on one instance, bring the CPU to 100% and wait to see auto scaling bring up a new instance within the Instance Pool.
For standalone ORDS, Oracle use Jetty. Jetty is a very capable webserver that on my laptop scale to 200+ rest calls per second. There deeper details on it's scaling abilities here: http://www.eclipse.org/jetty/documentation/current/high-load.html The best advantage is it simply works, scales, easy to get up and running. The disadvantage is mainly it's a purpose built and configured web server for ORDS. If someone needs more general web server features, it'd best to use WLS / Tomcat / Glassfish.
Download the following software to the Compute Instance
Install software via yum
yum-config-manager --enable ol7_oci_included
yum update -y
yum install -y ords
yum install -y java
yum install -y jq
yum install -y oracle-release-el7
#yum search oracle-instant
yum install -y oracle-instantclient19.9-basic.x86_64
yum install -y oracle-instantclient19.9-tools.x86_64
Allow access to Port 8080
firewall-cmd --zone=public --add-port 8080/tcp --permanent
firewall-cmd --zone=public --add-port 8443/tcp --permanent
#firewall-cmd --permanent --zone=public --add-service=http
#firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --reload
Install SQLcl and ADMIN wallet
unzip sqlcl-20.4.1.351.1718.zip -d /opt
unzip Wallet_PROD.zip -d /usr/lib/oracle/19.9/client64/lib/network/admin
ln -s /opt/sqlcl/bin/sql /usr/lib/oracle/19.9/client64/bin/sql
add these to ~oracle/.bash_profile
export PATH=/usr/lib/oracle/19.9/client64/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/19.9/client64/lib
export TNS_ADMIN=/usr/lib/oracle/19.9/client64/lib/network/admin
Install APEX and Patch Set
RELEASE=20.2.0.00.20
mkdir -p /opt/oracle/apex/images/$RELEASE
unzip apex_20.2.zip -d /tmp/
cp -R /tmp/apex/images/* /opt/oracle/apex/images/$RELEASE
rm -rf /tmp/apex
unzip p32006852_2020_Generic.zip -d /tmp/
cp -R /tmp/32006852/images/* /opt/oracle/apex/images/$RELEASE
rm -rf /tmp/32006852
Create alternate ORDS_PUBLIC_USER2 user
sql admin@prod_low
create user ords_public_user2 identified by "Opt12345678901234567890!";
grant connect to ORDS_PUBLIC_USER2;
begin
ords_admin.provision_runtime_role(
p_user => 'ORDS_PUBLIC_USER2'
, p_proxy_enabled_schemas => true
);
end;
/
Setup enviornment variables
ORDS_CONFIG_DIR=/opt/oracle/ords/config
ORDS_USER=ORDS_PUBLIC_USER2
ORDS_PASSWORD=Opt12345678901234567890!
SERVICE_NAME=prod_low
WALLET_BASE64=`base64 -w 0 Wallet_PROD.zip`
Create directories
mkdir -p $ORDS_CONFIG_DIR/ords/conf
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/doc_root
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/etc
mkdir -p $ORDS_CONFIG_DIR/ords/standalone/logs
Create $ORDS_CONFIG_DIR/ords/conf/apex_pu.xml
cat << EOF > $ORDS_CONFIG_DIR/ords/conf/apex_pu.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<entry key="db.username">$ORDS_USER</entry>
<entry key="db.password">!$ORDS_PASSWORD</entry>
<entry key="db.wallet.zip.service">$SERVICE_NAME</entry>
<entry key="db.wallet.zip"><![CDATA[$WALLET_BASE64]]></entry>
</properties>
EOF
Create $ORDS_CONFIG_DIR/ords/defaults.xml
cat << EOF > $ORDS_CONFIG_DIR/ords/defaults.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<entry key="plsql.gateway.enabled">true</entry>
<entry key="jdbc.InitialLimit">20</entry>
<entry key="jdbc.MinLimit">20</entry>
<entry key="jdbc.MaxLimit">50</entry>
<entry key="jdbc.MaxStatementsLimit">20</entry>
</properties>
EOF
Create $ORDS_CONFIG_DIR/ords/standalone/etc/jetty-http.xml
cat << EOF > $ORDS_CONFIG_DIR/ords/standalone/etc/jetty-http.xml
<?xml version="1.0"?>
<!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure.dtd">
<Configure id="Server" class="org.eclipse.jetty.server.Server">
<Ref id="Handlers">
<Call name="addHandler">
<Arg>
<New id="RequestLog" class="org.eclipse.jetty.server.handler.RequestLogHandler">
<Set name="requestLog">
<New id="RequestLogImpl" class="org.eclipse.jetty.server.NCSARequestLog">
<Set name="filename"><Property name="jetty.logs" default="/opt/oracle/ords/config/ords/standalone/logs/"/>ords-access-yyyy_mm_dd.log</Set>
<Set name="filenameDateFormat">yyyy_MM_dd</Set>
<Set name="retainDays">90</Set>
<Set name="append">true</Set>
<Set name="extended">false</Set>
<Set name="logCookies">false</Set>
<Set name="LogTimeZone">GMT</Set>
</New>
</Set>
</New>
</Arg>
</Call>
</Ref>
</Configure>
EOF
Edit /opt/oracle/ords/config/ords/standalone/standalone.properties
jetty.port=8080
standalone.context.path=/ords
standalone.doc.root=/opt/oracle/ords/config/ords/standalone/doc_root
standalone.scheme.do.not.prompt=true
standalone.static.context.path=/i
standalone.static.path=/opt/oracle/apex/images
jetty.secure.port=8443
#ssl.cert=leavemealone.com.pem
#ssl.cert.key=leavemealone.com.key
#ssl.host=pws.leavemealone.com
Configure ORDS
ords configdir $ORDS_CONFIG_DIR
/etc/ords/ords.conf ORDS_BASE_PATH=/opt/oracle
Test run ORDS
ords standalone
wget http://localhost:8080/i/20.2.0.00.20/apex_version.txt
Auto Start ORDS
systemctl start ords
systemctl enable ords
systemctl status ords
Switch APEX static resources repository to Oracle Content Delivery Network (CDN).
BEGIN
apex_instance_admin.set_parameter (
p_parameter => 'IMAGE_PREFIX',
p_value => 'https://static.oracle.com/cdn/apex/20.2.0.00.20/');
COMMIT;
END;
sql user/password@//hostname/service_name
sql user/password@jdbc:oracle:oci:@(description=(address_list=(address=.......ODS)))
sql user/password@(description=(address_list=(address=.......ODS)))
Get-ADUser -Filter {(ObjectClass -eq "user") -and (Enabled -eq $true)} -property name,objectGUID,accountExpires,c,co,company,department,displayName,givenName,homePhone,l,mail,mailNickname,manager,physicalDeliveryOfficeName,postalCode,sAMAccountName,sn,st,streetAddress,telephoneNumber,title,userPrincipalName,facsimileTelephoneNumber,whenChanged,whenCreated,mobile,distinguishedName -SearchBase "DC=leavemealone,DC=local" | Select-object name,objectGUID,accountExpires,c,co,company,department,displayName,givenName,homePhone,l,mail,mailNickname,manager,physicalDeliveryOfficeName,postalCode,sAMAccountName,sn,st,streetAddress,telephoneNumber,title,userPrincipalName,facsimileTelephoneNumber,whenChanged,whenCreated,mobile,distinguishedName | ConvertTo-Json | Out-File $ad_user_json -encoding UTF8
# rm -rf /usr/ports/.* /usr/ports/*
# svnlite co svn://svn.freebsd.org/ports/tags/RELEASE_11_3_0 /usr/ports
# pkg version -l "<"
# portmaster -G devel/pkgconf
crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
ora.DATA.dg
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
ora.net1.network
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
ora.ons
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
ora.proxy_advm
OFFLINE OFFLINE rac1 STABLE
OFFLINE OFFLINE rac2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac2 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE rac1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE rac1 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE rac1 192.168.50.10,STABLE
ora.asm
1 ONLINE ONLINE rac1 Started,STABLE
2 ONLINE ONLINE rac2 Started,STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE rac1 STABLE
ora.mgmtdb
1 ONLINE ONLINE rac1 Open,STABLE
ora.orcl.db
1 ONLINE ONLINE rac1 Open,HOME=C:\oracle\
product\12.2.0\dbhom
e_1,STABLE
2 ONLINE ONLINE rac2 Open,HOME=C:\oracle\
product\12.2.0\dbhom
e_1,STABLE
ora.qosmserver
1 ONLINE ONLINE rac1 STABLE
ora.rac1.vip
1 ONLINE ONLINE rac1 STABLE
ora.rac2.vip
1 ONLINE ONLINE rac2 STABLE
ora.scan1.vip
1 ONLINE ONLINE rac2 STABLE
ora.scan2.vip
1 ONLINE ONLINE rac1 STABLE
ora.scan3.vip
1 ONLINE ONLINE rac1 STABLE
--------------------------------------------------------------------------------
srvctl config database -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: C:\oracle\product\12.2.0\dbhome_1
Oracle user: nt authority\system
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.298.976826851
Password file: +DATA/ORCL/PASSWORD/pwdorcl.282.976826257
Domain: leavemealone.local
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
Database instances: orcl1,orcl2
Configured nodes: rac1,rac2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
srvctl config scan
SCAN name: RAC-scan, Network: 1
Subnet IPv4: 10.10.2.0/255.255.255.0/NIC1, static
Subnet IPv6:
SCAN 1 IPv4 VIP: 10.10.2.111
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 2 IPv4 VIP: 10.10.2.110
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 3 IPv4 VIP: 10.10.2.109
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac2
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac1
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac1
cluvfy comp ocr -n all -verbose
Verifying OCR Integrity ...PASSED
Verification of OCR integrity was successful.
CVU operation performed: OCR integrity
Date: May 30, 2018 11:54:26 PM
CVU home: C:\oracle_grid_home\bin\..\
User: leavemealone\scammisuli
ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 2124
Available space (kbytes) : 407444
ID : 2115914722
Device/File Name : +DATA
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user
BEGIN
DBMS_SCHEDULER.create_credential (credential_name => 'MAILSERVER_CREDENTIAL',
username => 'uuuuuuuu',
password => 'xxxxxxxx');
END;
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute ('email_server', 'vtordc01.leavemealone.local:25');
DBMS_SCHEDULER.set_scheduler_attribute ('email_sender', 'service@leavemealone.ca');
DBMS_SCHEDULER.set_scheduler_attribute ('email_server_credential', 'MAILSERVER_CREDENTIAL');
END;
BEGIN
DBMS_SCHEDULER.add_job_email_notification (job_name => 'ADHOC_04',
recipients => 'prtg.alert@gmail.com',
events => 'JOB_STARTED,JOB_SUCCEEDED,JOB_FAILED,JOB_STOPPED');
END;
SELECT job_name,
recipient,
event,
filter_condition
FROM user_scheduler_notifications;
RACPROD =
(DESCRIPTION=
(FAILOVER = ON)
(ADDRESS=
(PROTOCOL=TCP)
(HOST=rac-scan.leavemealone.local)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=prod.leavemealone.local)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 10)(DELAY = 5))
)
)
sqlplus accdb@racprod
sqlcl accdb@jdbc:oracle:oci:@(DESCRIPTION=(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prod.leavemealone.local)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 10)(DELAY = 5))))
jdbc:oracle:oci:@(DESCRIPTION=(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prod.leavemealone.local)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 10)(DELAY = 5))))
srvctl add service -d orcl -s prodtaf.leavemealone.local -m BASIC -e SELECT -z 180 -w 5 -j LONG -r "ORCL1,ORCL2"
-pdb prod
srvctl start service -d orcl -s prodtaf.leavemealone.local
srvctl config service -d orcl -s prodtaf.leavemealone.local
Service name: prodtaf.leavemealone.local
Server pool:
Cardinality: 2
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 180
TAF failover delay: 5
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: prod
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: orcl1,orcl2
Available instances:
CSS critical: no
RACPROD =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=rac-scan.leavemealone.local)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=prodtaf.leavemealone.local)
)
)
sqlplus accdb@racprod
sqlcl accdb@jdbc:oracle:oci:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prodtaf.leavemealone.local)))
jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prodtaf.leavemealone.local)))
SELECT inst_id,
machine,
program,
username,
logon_time,
failover_type,
failover_method,
failed_over
FROM gv$session
ORDER BY logon_time DESC;
Run the script connected to SQLcl as the owner (parsing schema) of the application.
We need to repeat this script for each application.
DECLARE
l_workspace_id NUMBER;
BEGIN
SELECT workspace_id
INTO l_workspace_id
FROM apex_workspaces
WHERE workspace = 'OPTRUST';
--
apex_application_install.set_workspace_id (l_workspace_id);
apex_application_install.set_application_id (&1);
apex_application_install.generate_offset;
apex_application_install.set_schema ('OPTRUST');
END;
/
@dynamic_action_plugin_be_apexrnd_aop_convert_da.sql
@dynamic_action_plugin_be_apexrnd_aop_da.sql
@process_type_plugin_be_apexrnd_aop.sql
COMMIT;
DECLARE
acl_path VARCHAR2 (4000);
v_hostname VARCHAR2 (200) := 'xxx.leavemealone.local';
v_user VARCHAR2 (30) := 'APEX_050100';
BEGIN
SELECT acl
INTO acl_path
FROM dba_network_acls
WHERE HOST = v_hostname AND lower_port IS NULL AND upper_port IS NULL;
IF dbms_network_acl_admin.check_privilege (acl_path, v_user, 'connect') IS NULL
THEN
dbms_network_acl_admin.add_privilege (acl_path, v_user, TRUE, 'connect');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
dbms_network_acl_admin.create_acl ('send_mail.xml', 'send_mail ACL', v_user, TRUE, 'connect');
dbms_network_acl_admin.assign_acl ('send_mail.xml', v_hostname, 25, 25);
END;
/
COMMIT;
BEGIN
DBMS_NETWORK_ACL_ADMIN.append_host_ace (
host => 'xxx.leavemealone.local',
lower_port => 25,
upper_port => 25,
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'APEX_050100',
principal_type => xs_acl.ptype_db));
END;
/
COMMIT;
SELECT * FROM dba_network_acls;
SELECT * FROM dba_network_acl_privileges order by principal;
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege ('/sys/acls/power_users.xml',
'APEX_050100',
TRUE,
'connect');
DBMS_NETWORK_ACL_ADMIN.add_privilege ('/sys/acls/send_mail.xml',
'APEX_050100',
TRUE,
'connect');
END;
SELECT 'BEGIN DBMS_NETWORK_ACL_ADMIN.append_host_ace (
HOST => '''
|| dna.HOST
|| ''',
lower_port => '
|| dna.lower_port
|| ',
upper_port => '
|| dna.upper_port
|| ',
ace =>
xs$ace_type (privilege_list => xs$name_list (''connect''),
principal_name => '''
|| dnap.principal
|| ''',
principal_type => xs_acl.ptype_db)); END;
/' v_sql
FROM dba_network_acls dna, dba_network_acl_privileges dnap
WHERE dna.aclid = dnap.aclid AND dnap.principal = 'APEX_200200';