[Linux] SSL Certificates, Private Keys and CSRs with OpenSSL

OpenSSL is an open-source implementation of the Secure Sockets Layer (SSL v2/v3) and Transport Layer Security (TLS) protocols.

Generate a Private Key and a CSR
This begins the process of generating two files: the Private-Key file for the decryption of your SSL Certificate, and a certificate signing request (CSR) file (used to apply for your SSL Certificate).
 
# openssl req -new -newkey rsa:2048 -nodes -keyout server.key -out server.csr

Generating SSL Certificates
If we would like to use an SSL certificate to secure a service but we do not require a CA-signed certificate, a valid (and free) solution is to sign your own certificates. We can Generate a Self-Signed Certificate from an Existing Private Key that we create before. This command creates a self-signed certificate (server.crt) from an existing private key (server.key).

# openssl req -key server.key -new -x509 -days 1095 -out server.crt

The -x509 option tells req to create a self-signed cerificate. The -days 1095 option specifies that the certificate will be valid for 1095 days (3 Years). A temporary CSR is generated to gather information to associate with the certificate.

[Apache] Redirect HTTP To HTTPS

If we want to redirect our web site to always be sent over SSL (HTTP TO HTTPS). We can do this :

1. Using Virtual Host
Just add this into apache config

<VirtualHost *:80>
    ServerName example.com
    Redirect / https://example.com/
</VirtualHost>

<VirtualHost _default_:443>
   ServerName secure.example.com
   DocumentRoot /usr/local/apache2/htdocs
   SSLEngine On
# etc...
</VirtualHost>

2. Using .htaccess

Redirect permanent /login https://mysite.example.com/login

3. Using mod_rewrite
This config can be used on .htaccess or httpd.conf

RewriteEngine On
# This will enable the Rewrite capabilities

RewriteCond %{HTTPS} !=on
# This checks to make sure the connection is not already HTTPS

RewriteRule ^/?(.*) https://%{SERVER_NAME}/$1 [R,L]
# This rule will redirect users from their original location, to the same location but using HTTPS.

[Linux] Transfer Files Or Folder Using SCP

It is very easy to transfer files on linux operating system, we can use scp (secure copy). scp allows files / folder to be copied to, from, or between different hosts. It uses ssh for data transfer and provides the same authentication and same level of security as ssh.

1. Copy from local host to remote host
scp /path/to/source-file user@remotehost:/path/to/destination-folder/

2. Copy from remote host to local host
 scp username@remotehost:filename /some/local/folder

3. Copy from remote host to remote host
scp username@remotehost1:/some/remote/folder/filename \ username@remotehost2:/some/remote/folder/

[Oracle EBS] Query To Find Application Name And Short Name

This query can be used to lists all the applications related information and to find the APPLICATION_SHORT_NAME of a module (eg. Payables, Receivables, Order Management, etc.)

SELECT fa.application_id           "Application ID",
       fat.application_name        "Application Name",
       fa.application_short_name   "Application Short Name",
       fa.basepath                 "Basepath"
  FROM fnd_application     fa,
       fnd_application_tl  fat
 WHERE fa.application_id = fat.application_id
   AND fat.language      = USERENV('LANG')
   --AND fat.application_name = 'General Ledger' 
 ORDER BY fat.application_name;

[Oracle EBS] Change EBS User Password Using PL/SQL

If you have a locked EBS user account or need to change the user passwords using FND_USER_PKG.changePassword API with PL / SQL then you can use the following script

declare
begin
if FND_USER_PKG.changePassword ('USERNAME','NEW_PASSWORD') 
then
 dbms_output.put_line(1); 
else 
 dbms_output.put_line(2) ;
end if;
end;

Don't forget to commit.

[Oracle Siebel CRM] Query Last User Active

Oracle Siebel CRM 8.1 use dba_users table for the application user. But the user activitiy history is stored into table at siebel schema. Here the query to get the user last active / last login date.

SELECT LOGIN, MAX(TANGGAL) LAST_TRANS_DATE
FROM (
SELECT S.LOGIN, MAX(TRUNC(PEL.LAST_UPD)) TANGGAL
FROM SIEBEL.S_ASSET_RDNG PEL, SIEBEL.S_USER S
WHERE PEL.LAST_UPD_BY =S.ROW_ID
AND EXISTS (SELECT 1 FROM DBA_USERS D WHERE D.USERNAME = S.LOGIN AND D.ACCOUNT_STATUS='OPEN')
GROUP BY S.LOGIN
UNION
SELECT S.LOGIN, MAX(TRUNC(PEL.LAST_UPD)) TANGGAL
FROM SIEBEL.S_ORG_EXT PEL, SIEBEL.S_USER S
WHERE PEL.LAST_UPD_BY =S.ROW_ID
AND EXISTS (SELECT 1 FROM DBA_USERS D WHERE D.USERNAME = S.LOGIN AND D.ACCOUNT_STATUS='OPEN')
GROUP BY S.LOGIN
UNION
SELECT UPPER(SRVR_USER_NAME) USERNAME, MAX(TRUNC(CREATED)) TANGGAL_LAST   FROM siebel.s_srm_task_hist
WHERE  UPPER(SRVR_USER_NAME) IN (SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS ='OPEN')
GROUP BY UPPER(SRVR_USER_NAME)
--ORDER BY 2
UNION
select c.username,MAX(TRUNC(a.SAMPLE_TIME))
from DBA_HIST_ACTIVE_SESS_HISTORY a,  dba_users c
where a.user_id=c.user_id
AND C.ACCOUNT_STATUS='OPEN'
AND C.USERNAME IN (SELECT LOGIN FROM SIEBEL.S_USER)
GROUP BY C.USERNAME
)
GROUP BY LOGIN

[Oracle EBS] Archive and Purge GL Balances and Journals

We can archive and purge account balances, as well as journal batches, entries, lines, and associated journal references for one or more accounting periods, provided the periods are permanently closed.

To Archive, navigate to Responsibility General Ledger Super User - Setup - System - Purge

The Archive process is move data from GL table to GL archive tables.  Data from tables is copied as below :

• GL_balances --> GL_archive_balances
• GL_batches --> GL_archive_batches
• GL_je_headers --> GL_archive_headers
• GL_je_lines --> GL_archive_lines
• GL_import_references  --> GL_archive_references

Once the process completes, export the archive tables data to a safe place and the purge the data
References :
- Archiving Account Balances and Journal Detail
- Purging Archived Account Balances and Journals

[Oracle EBS] Purge Concurrent Request and/or Manager Data

One of the important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications. When you experience these space problems, a specific request called “Purge Concurrent Requests And/Or Manager Data” should be scheduled to run on a regular basis. This request to purge can be configured  the request data from the FND tables as well as the log files and output files on accumulate on disk.

When the tables FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES reaches above 3000-4000 rows, the performance diminishes. You have to run Purge Concurrent Request and/or Manager Data program on a regular basis depending on the amount of request being run.

Query Find Size in KB for FND_CONCURRENT_REQUESTS 
SELECT (BYTES/1024)/1024 "Size in KB" from dba_segments where SEGMENT_NAME='FND_CONCURRENT_REQUESTS';

SELECT (BYTES/1024)/1024 "Size in KB" from dba_segments WHERE SEGMENT_NAME='FND_CONCURRENT_PROCESSES';

How to run Purge Concurrent Request and/or Manager Data in oracle EBS

1. Log in to Application as System Administrator responsibility.
2. Navigate to Request> Run> Single Request
3. Query up Purge Concurrent Requests.

ENTITY = ALL   : Purge of concurrent requests, concurrent managers, request log files, manager log files and report output files. The following tables are purged
-  Fnd_Concurrent_Processes
-  Fnd_Dual
-  Fnd_Concurrent_Requests,
-  Fnd_Run_Requests
-  Fnd_Conc_Request_Arguments
-  Fnd_Dual
-  Fnd_Context_Env
-  Deletes concurrent requests’ log and out files from OS

ENTITY = MANAGER   : Purge of concurrent managers and manager log files.
The following tables are purged 
-  Fnd_Concurrent_Processes
-  Fnd_Dual
-  Deletes concurrent manager log files from OS

ENTITY = REQUEST   : Purge of concurrent requests, request log files and output files.
The following tables are purged
– Fnd_Concurrent_Requests,
– Fnd_Run_Request
– Fnd_Conc_Request_Arguments
– Fnd_Dual
– Deletes concurrent requests’ log and out files from OS

MODE : 
- AGE : Number of days.
- COUNT : Number of records

[Oracle] How To Execute INSERT Statements From File

(en)
How to execute a sql script on oracle? Use this command :

SQL> @<path>\<filename>

(id)
Cara untuk melakukan insert data ke database Oracle dari file (bisa berupa .txt maupun .sql) yang berisi insert statement, bisa menggunakan command berikut ini :

SQL> @<path>\<filename>

[Oracle EBS] Query Apply Patch Listing

Query untuk melihat list apply patch :

SELECT
    patch_name, patch_type, maint_pack_level, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC