Oracle Cheatsheet: March 2017

Friday, March 31, 2017

Restore Oracle database from RMAN backup

In SQL*Plus, sqlplus / as sysdba

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SID='*';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/flash_recovery_area' SID='*';

In RMAN, rman target /
DBID is the number from the source database

rman target /
SET DBID 2659406081;
startup nomount;
RESTORE SPFILE TO PFILE '/u01/app/oracle/initorcl.ora' FROM AUTOBACKUP;
SHUTDOWN ABORT;

STARTUP FORCE NOMOUNT PFILE='/u01/app/oracle/initorcl.ora';
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
catalog recovery area;

In SQL*Plus, sqlplus / as sysdba

create spfile from pfile='/u01/app/oracle/initorcl.ora';
alter database rename file 'C:\oradb\oradata\orcl\redo01.log' to 'C:\oradb\oradata\orcl\xredo01.log';
alter database rename file 'C:\oradb\oradata\orcl\redo02.log' to 'C:\oradb\oradata\orcl\xredo02.log';
alter database rename file 'C:\oradb\oradata\orcl\redo03.log' to 'C:\oradb\oradata\orcl\xredo03.log';

Back to RMAN, rman target /
Perform a SET UNTIL to limit recovery to the end of the archived redo logs. Note that recovery stops with an error if no SET UNTIL is specified.

run {
set until time "to_date('2017-03-30 13:00:00', 'yyyy-mm-dd hh24:mi:ss')";
restore database;
recover database;
}
ALTER DATABASE OPEN RESETLOGS;

Oracle 10g XE customization

In SQL*Plus, sqlplus / as sysdba

alter system set processes=300 scope=spfile;
alter system set open_cursors= 300;
alter system set job_queue_processes=20;
alter system set SHARED_SERVERS = 5;
alter system set memory_max_target=1G scope=spfile;
alter system set memory_target=1G scope=spfile;
alter system set sga_max_size=600M scope=spfile;

alter system set DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata';
EXEC DBMS_XDB.SETHTTPPORT(0);
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(TRUE);

Check the parameters

select * from v$resource_limit;

Amazon EC2 customization

TimeZone

ln -sf /usr/share/zoneinfo/Canada/Eastern /etc/localtime

Edit /etc/sysconfig/clock

ZONE="Canada/Eastern"

iptables
Amazon Linux would not let me bind port < 1024, so we have to use iptables to rediect 80 to 8080 and 443 to 8443. First, create the rules and save them to this file,iptables.dump

# Generated by iptables-save v1.4.18 on Fri Mar 31 03:06:30 2017
*filter
:INPUT ACCEPT [97187:40139959]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [121395:162261294]
-A INPUT -i eth0 -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -i eth0 -p tcp -m tcp --dport 8080 -j ACCEPT
-A INPUT -i eth0 -p tcp -m tcp --dport 443 -j ACCEPT
-A INPUT -i eth0 -p tcp -m tcp --dport 8443 -j ACCEPT
COMMIT
# Completed on Fri Mar 31 03:06:30 2017
# Generated by iptables-save v1.4.18 on Fri Mar 31 03:06:30 2017
*nat
:PREROUTING ACCEPT [1072:59746]
:INPUT ACCEPT [1601:90978]
:OUTPUT ACCEPT [8974:551695]
:POSTROUTING ACCEPT [8974:551695]
-A PREROUTING -i eth0 -p tcp -m tcp --dport 80 -j REDIRECT --to-ports 8080
-A PREROUTING -i eth0 -p tcp -m tcp --dport 443 -j REDIRECT --to-ports 8443
COMMIT
# Completed on Fri Mar 31 03:06:30 2017

Next, load the rules into iptables

iptables-restore < iptables.dump

Save the rules

service iptables save

In the default configuration, stopping or restarting the iptables service will discard the running configuration. This behavior can be changed by setting IPTABLES_SAVE_ON_STOP="yes" or IPTABLES_SAVE_ON_RESTART="yes" in /etc/sysconfig/iptables-config. If these values are set, the affected files are:
/etc/sysconfig/iptables

Tuesday, March 21, 2017

2-factor Authenication usiing Google Authenticator

Download the OATH-toolkit

$ http://download.savannah.gnu.org/releases/oath-toolkit/
$ tar -zxvf oath-toolkit-1.12.5.tar.gz
$ ./configure
$ make

Assume the the secret key is JBSWY3DPEHPK3PXP. Run the command on linux will give you the code. Google uses 30 seconds time step and 16 chars base32 secret key.

$ ./oathtool --totp -s 30s -b JBSWY3DPEHPK3PXP
431234

Finally, we need to generate the QR code for Google Authenticator

https://dan.hersam.com/tools/gen-qr-code.html

Fire up Google Authenticator or 1Password, scan the QR code on the screen and you are all set.

Amazon EC2 Oracle RMAN backup to S3

$cat oracle_backup.sh

#!/bin/bash

SCRIPT_HOME=/u01/app/oracle/scripts
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
LOCAL_FLASH_AREA=/u01/app/oracle/fast_recovery_area/XE

. $ORACLE_HOME/bin/oracle_env.sh

rm -f $SCRIPT_HOME/oracle_backup.log

$ORACLE_HOME/bin/rman target / cmdfile $SCRIPT_HOME/oracle_backup.rcv log $SCRIPT_HOME/oracle_backup.log
s3cmd --no-progress sync --delete-removed $LOCAL_FLASH_AREA s3://cftc-rman-backup/
s3cmd put $SCRIPT_HOME/oracle_backup.log s3://cftc-rman-backup

$ cat oracle_backup.rcv

list db_unique_name all;
show all;
RUN {
  crosscheck backup;
  crosscheck copy;
  delete noprompt expired archivelog all;
  delete noprompt expired backup;
  delete noprompt obsolete device type disk;
}
RUN {
  sql "alter system archive log current";
  RECOVER COPY OF DATABASE WITH TAG 'ORAXE_INC_UPD'
    UNTIL TIME 'SYSDATE - 7';
  BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'ORAXE_INC_UPD'
    DATABASE;
  sql "alter system switch logfile";
}
exit;

Amazon EC2 Oracle Snapshot Backup

The idea is first put oracle database in backup mode, start the ec2 snapshot process. At this point, we can put the database back to normal already.
I add 60 seconds wait to be on the safe side.

$ cat ebs_volume_snapshot.sh

#!/bin/bash
SCRIPT_HOME=/u01/app/oracle/scripts
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

sqlplus -S / as sysdba << ABC1
alter database begin backup;
exit;
ABC1

$SCRIPT_HOME/ec2-automate-backup.sh -v "vol-11ba0c5d vol-3cba0c70" -k 3  -c $SCRIPT_HOME/cron-primer.sh

sleep 60

sqlplus -S / as sysdba << ABC2
alter database end backup;
exit;
ABC2

$ cat cron-primer.sh

#!/bin/bash -
# EC2_HOME required for EC2 API Tools
export EC2_HOME=/opt/aws/apitools/ec2
# JAVA_HOME required for EC2 API Tools
export JAVA_HOME=/usr/lib/jvm/jre
export PATH=/bin:/opt/aws/bin/
export AWS_ACCESS_KEY=XXXXXXXXXXXXX
export AWS_SECRET_KEY=YYYYYYYYYYYYYYYYYYYYYY

You can find the ec2-automate-backup script here.

Monday, March 20, 2017

JasperReportsIntegration on windows Tomcat 8 & 9 High CPU usage

After deployment of the war file, edit the following file and change Context attribute reloadable from true to false.
This fix the CPU spike every 12 seconds issue on windows box.

C:\tomcat9\webapps\JasperReportsIntegration\META-INF\context.xml

<Context path="/JasperReportsIntegration" debug="5" reloadable="false"
         crossContext="true">

Friday, March 17, 2017

Tomcat 9 JDK8 server.xml for HTTPS with SNI Support

This configure file is case sensitive.
SSLCertificateKeyFile is the private key.

<Connector port="443" protocol="org.apache.coyote.http11.Http11NioProtocol" 
  SSLEnabled="true" scheme="https" secure="true"
  maxHttpHeaderSize="32767"
  maxThreads="150" 
  URIEncoding="UTF-8"
  compression="on"
  defaultSSLHostConfigName="cmeportal.oneconceptonline.ca">
<SSLHostConfig hostName="cmeportal.oneconceptonline.ca"
  honorCipherOrder="true"
  disableSessionTickets="true"
  ciphers="TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384,
TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256,
TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384,
TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384,
TLS_DHE_DSS_WITH_AES_256_GCM_SHA384,
TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384,
TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,
TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256,
TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256,
TLS_DHE_DSS_WITH_AES_128_GCM_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384,
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384,
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384,
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384,
TLS_DHE_DSS_WITH_AES_256_CBC_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA,
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA,
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA,
TLS_DHE_DSS_WITH_AES_256_CBC_SHA,
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256,
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256,
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256,
TLS_DHE_DSS_WITH_AES_128_CBC_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA,
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA,
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA,
TLS_DHE_DSS_WITH_AES_128_CBC_SHA,
TLS_ECDHE_ECDSA_WITH_RC4_128_SHA,
TLS_ECDH_ECDSA_WITH_RC4_128_SHA,
TLS_ECDH_RSA_WITH_RC4_128_SHA,
TLS_EMPTY_RENEGOTIATION_INFO_SCSVF"
>
<Certificate 
     CertificateFile="${catalina.home}\conf\ssl\cmeportal.oneconceptonline.ca.crt"
     CertificateKeyFile="${catalina.home}\conf\ssl\cmeportal.oneconceptonline.ca.pem"
     CertificateChainFile="${catalina.home}\conf\ssl\gd_bundle-g2-g1.crt"
     type="RSA" />
</SSLHostConfig>
<SSLHostConfig hostName="*.conceptdynamics.zone"
  honorCipherOrder="true"
  disableSessionTickets="true"
  ciphers="TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384,
TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256,
TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384,
TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384,
TLS_DHE_DSS_WITH_AES_256_GCM_SHA384,
TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384,
TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,
TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256,
TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256,
TLS_DHE_DSS_WITH_AES_128_GCM_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384,
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384,
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384,
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384,
TLS_DHE_DSS_WITH_AES_256_CBC_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA,
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA,
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA,
TLS_DHE_DSS_WITH_AES_256_CBC_SHA,
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256,
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256,
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256,
TLS_DHE_DSS_WITH_AES_128_CBC_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA,
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA,
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA,
TLS_DHE_DSS_WITH_AES_128_CBC_SHA,
TLS_ECDHE_ECDSA_WITH_RC4_128_SHA,
TLS_ECDH_ECDSA_WITH_RC4_128_SHA,
TLS_ECDH_RSA_WITH_RC4_128_SHA,
TLS_EMPTY_RENEGOTIATION_INFO_SCSVF"
>
<Certificate 
     CertificateFile="${catalina.home}\conf\ssl\conceptdynamics.zone.crt"
     CertificateKeyFile="${catalina.home}\conf\ssl\conceptdynamics.zone.pem"
     CertificateChainFile="${catalina.home}\conf\ssl\gd_bundle-g2-g1.crt"
     type="RSA" />
</SSLHostConfig>
</Connector>

Tuesday, March 14, 2017

SSL Certificate with OpenSSL

Generate CSR

openssl req -new -newkey rsa:2048 -nodes -keyout jes.jmetrics.com.pem -out jes.jmetrics.com.csr

Install the Certificate

SSLCertificateFile jes.jmetrics.com.crt
SSLCertificateKeyFile jes.jmetrics.com.pem
SSLCertificateChainFile rapidssl.crt

View Public Key

openssl x509 -text -in jes.jmetrics.com.crt

View Private Key

openssl rsa -text -in jes.jmetrics.com.pem

Show SHA1 Fingerprint

openssl x509 -noout -fingerprint -sha1 -inform pem -in jes.jmetrics.com.crt

Export Private Key from Java KeyStore

keytool -importkeystore -srckeystore jes.jmetrics.com.keystore -srcstoretype jks -destkeystore jes.jmetrics.com.p12 -deststoretype PKCS12 -srcalias jes.jmetrics.com
openssl pkcs12 -in jes.jmetrics.com.p12 -nocerts -nodes

Now, we can copy and paste the private key on the screen into the PEM file.

SSL Certificate with Java Keystore

Generate CSR

keytool -genkeypair -alias cftc-csid.ca -keyalg RSA -keysize 2048 -validity 365 -keypass xxxx -keystore keystore.jks -storepass xxxx
keytool -certreq -alias cftc-csid.ca -file cftc-csid.ca.csr -keystore keystore.jks -storepass xxxx

Import the Certificate from CA

keytool -import -trustcacerts -alias root -file cftc-csid_ca.ca-bundle  -keystore keystore.jks -storepass xxxx
keytool -import -trustcacerts -alias cftc-csid.ca -file cftc-csid_ca.crt  -keystore keystore.jks -storepass xxxx

List all keys

keytool -v -list -keystore keystore.jks -storepass xxxx

Delete a key

keytool -delete -keystore keystore.jks -storepass xxxx