Search This Blog

Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Wednesday, August 26, 2026

Turbocharging Nginx on Oracle Cloud ARM: Zero-Copy HTTPS with Kernel TLS (kTLS)

Turbocharging Nginx on Oracle Cloud ARM: Zero-Copy HTTPS with Kernel TLS (kTLS)

If you run high-concurrency web applications, media assets, or content management systems like WordPress on Oracle Cloud Infrastructure (OCI) Ampere Altra (ARM64) instances, you are already benefiting from high core counts and efficient compute.

However, serving static assets over HTTPS standardly incurs an invisible system tax: redundant memory copying and excessive CPU context switches.

By offloading symmetric TLS record processing directly to the Linux kernel via Kernel TLS (kTLS), you unlock true zero-copy sendfile() over HTTPS, slashing CPU overhead and maximizing throughput on Ubuntu 24.04 LTS.


The Hidden Bottleneck of User-Space HTTPS

In a traditional Nginx setup serving encrypted traffic:

[Disk / Page Cache] ──(Copy 1)──> [User Space: Nginx / OpenSSL]
                                           │
                                    (Encrypts Data)
                                           │
[Network Interface (NIC)] <──(Copy 2)── [Kernel Socket Buffer]
  1. The kernel reads requested files from disk or page cache into user-space memory buffers.
  2. OpenSSL in user space encrypts the payload block by block.
  3. Encrypted buffers are copied across the user/kernel space boundary into socket buffers.
  4. The network stack finally transmits the packets over the wire.

Because OpenSSL processes symmetric encryption in user space, the Linux kernel's high-performance sendfile() syscall is disabled for HTTPS. Every static asset—images, cached HTML, CSS, JavaScript, and binaries—triggers double memory copies and CPU cache invalidations.


How kTLS Solves the Problem

Kernel TLS cleanly separates the control plane from the data plane:

  • Handshake (Control Plane): Remains entirely in user space. OpenSSL handles certificate validation, key exchange, and session negotiation.
  • Data Plane (Encryption/Decryption): Once session keys are derived, OpenSSL passes them to the kernel via socket options (setsockopt(..., SOL_TLS, ...)). The socket switches to the tls Upper Layer Protocol (ULP).
[Disk / Page Cache] ──(In-Kernel Zero-Copy sendfile)──> [Kernel Crypto: ARM CE] ──> [NIC]

With kTLS active, Nginx issues a standard sendfile() syscall over HTTPS. The kernel reads directly from page cache, applies symmetric encryption (AES-GCM or ChaCha20-Poly1305) in-kernel using ARMv8 Neoverse N1 Cryptographic Extension instructions, and streams packets directly to the network interface. Zero user-space memory copies.


Prerequisites on Ubuntu 24.04 LTS (ARM64)

  • OS: Ubuntu 24.04 LTS (Kernel 6.8+ with CONFIG_TLS=m enabled by default)
  • Hardware: OCI Ampere Altra (ARMv8.2+ with hardware-accelerated AES/SHA Cryptographic Extensions)
  • Software: Nginx with OpenSSL 3.x

Step-by-Step Implementation & Common Pitfalls

1. Load and Persist the Kernel Module

The tls module is included in Ubuntu 24.04 but must be loaded:

# Load module immediately
sudo modprobe tls

# Persist module across reboots
echo "tls" | sudo tee /etc/modules-load.d/ktls.conf

Verify that the module is loaded:

lsmod | grep tls

2. The Nginx Configuration Pitfall

Many online guides suggest adding this directive to /etc/nginx/nginx.conf:

ssl_conf_cmd Options KTLS;

On standard Ubuntu 24.04 builds, running nginx -t will often throw an error:

[emerg] unknown directive "ssl_conf_cmd" in /etc/nginx/nginx.conf
nginx: configuration file /etc/nginx/nginx.conf test failed

The Fix: Do not place ssl_conf_cmd in Nginx. Instead, configure OpenSSL 3.x system-wide so all Nginx worker processes automatically use kTLS.

3. Enable kTLS via OpenSSL 3.x Configuration

Open /etc/ssl/openssl.cnf:

sudo nano /etc/ssl/openssl.cnf

Step A: Locate the existing [openssl_init] block (around line 43) and add ssl_conf = ssl_sect:

[openssl_init]
providers = provider_sect
ssl_conf = ssl_sect

Step B: Scroll to the very bottom of the file and append the SSL section:

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
CipherString = DEFAULT:@SECLEVEL=2
Options = KTLS

Save and exit the file.

4. Configure Nginx and Restart

Ensure your /etc/nginx/nginx.conf has sendfile and modern TLS protocols enabled:

http {
    sendfile on;
    tcp_nopush on;
    tcp_nodelay on;

    ssl_protocols TLSv1.2 TLSv1.3;
    ssl_ciphers ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384;
    ssl_prefer_server_ciphers off;

    # ... remaining server configurations ...
}

Test configuration and restart the service:

sudo nginx -t && sudo systemctl restart nginx

Verifying kTLS in Production

Generate test HTTPS traffic against your server:

curl -k -o /dev/null https://127.0.0.1/

1. Check Kernel TLS Statistics

Inspect the kernel's real-time TLS statistics:

cat /proc/net/tls_stat

Key counters to watch:

  • TlsTxSw: Increments with every TLS record encrypted via software crypto routines in the kernel.
  • TlsCurrTxSw: Shows currently active kernel TLS transmit sockets.

2. Inspect Active Socket Offload

Run ss to verify that active connections use the TLS Upper Layer Protocol:

ss -ti '( sport = :443 or dport = :443 )'

Look for ulp:tls in the socket options output.


Performance Comparison

Metric Standard TLS (User Space) Kernel TLS (kTLS Zero-Copy)
Data Path 2 Buffer Copies (Kernel $\rightarrow$ User $\rightarrow$ Kernel) 0 Copies (Direct Page Cache $\rightarrow$ NIC)
sendfile() Syscall Disabled for HTTPS Fully Supported
CPU Overhead High %usr (User space encryption) Low %usr, shifts cleanly to %sys
Throughput Memory bandwidth bound Line-rate network bound

Summary

Enabling kTLS on Oracle Cloud ARM instances is a zero-cost optimization that yields substantial returns for static file delivery. By fixing the OpenSSL 3.x system configuration on Ubuntu 24.04, you eliminate user-space copy bottlenecks and allow the ARM Neoverse crypto engine to process encrypted traffic at native line rates.

Monday, April 7, 2025

Disable Kernel modules in Alpine Linux Cloud image for Oracle Cloud

Download Alpine Linux Cloud Image from: https://www.alpinelinux.org/cloud/
Upload the image to OCI and create an instance from it.

I disabled some Kernel modules for security and saving memory.

Remove old drivers from: /etc/mkinitfs/mkinitfs.conf
From:
features="ata base ide scsi usb virtio ext4 nvme"
To:
features="base scsi virtio ext4"


Add 'blacklist drm' to /etc/modprobe.d/blacklist.conf
echo 'blacklist drm' >> /etc/modprobe.d/blacklist.conf
mkinitfs -c /etc/mkinitfs/mkinitfs.conf -b / 
reboot
The grub modprobe.blacklist=drm, does not work either.
updating /etc/default/grub
GRUB_CMDLINE_LINUX_DEFAULT="quiet splash modprobe.blacklist=drm"


I will try [cloud-init] Bootstrap image next time.

Reference:
https://dev.alpinelinux.org/~clandmeter/other/forum.alpinelinux.org/forum/kernel-and-hardware/blacklist-drivers-builtin-kernel.html
https://wiki.alpinelinux.org/wiki/Initramfs_init
https://wiki.alpinelinux.org/wiki/Xen_PCI_Passthrough

Thursday, June 6, 2024

Get SPF DNS record for Oracle Cloud Email Delivery service

Follow Oracle's Offical guide Configuring SPF

For Americas Region SPF Record
v=spf1 include:rp.oracleemaildelivery.com ~all
The following is an example of a command used to view an SPF record:
dig -t TXT +short syd1.rp.oracleemaildelivery.com
Example output:
"v=spf1 ip4:192.168.0.2/25 -all"


For Canada Regions and Availability Domains

Region Name Region Identifier Region Location Region Key Availability Domains
Canada Southeast (Montreal) ca-montreal-1 Montreal, Canada YUL 1
Canada Southeast (Toronto) ca-toronto-1 Toronto, Canada YYZ 1


For Toronto Region:
v=spf1 include:yyz1.rp.oracleemaildelivery.com ~all
Run this command to get the IP address range:
dig -t TXT +short yyz1.rp.oracleemaildelivery.com
Output:
"v=spf1 ip4:192.29.72.0/25 ~all"


Update the SPF DNS record with IP address has a benefit of spf flattening to Fix “Too Many DNS Lookups”

My DNS settings:
"v=spf1 ip4:192.29.72.0/25 -all"

Monday, May 13, 2024

How to Deleting a Tenancy and Oracle Cloud Account

Try to clean up your Oracle Cloud Account and Tenancy?
You can delete your Account / Tenancy by following the steps mentioned in the link below:

  1. While signed in as an OCI administrator, open the Tenancy Details page.
  2. Otherwise, if not signed in as an administrator, the Request tenancy deletion button isn't available.
    Click Request tenancy deletion.
  3. The Request tenancy deletion confirmation is displayed
  4. Confirm deletion in Enter the tenancy name to confirm deletion by entering the name of the tenancy to delete.
    Click Request tenancy deletion.
  5. A message is displayed to indicate that you successfully created a work request to delete the tenancy.
  6. Under Work requests, a Tenancy deletion requested operation appears, where you can view the status of the deletion request.
    The work request operation displays the state, percent completion, date, and time accepted, and when the operation started and finished.
    You can also click the Delete Tenancy work request to view the Delete tenancy work request details page.
    You also receive an email notifying you that the tenancy deletion process has completed.
Reference:
Deleting a Tenancy and Cloud Account
Viewing the Tenancy Details Page

Friday, November 25, 2022

Growing a ext4 disk on a cloud / VMware VM

Normal I use GParted CD boot a VM to growing a Linux ext4 disk partition. Or using LVM. It's way easier on Windows VM. I tried cloud-utils tool on a Debian 10 VM on VMware ESXi 7. Just a bare ext4 partition without LVM. It should works on any other Cloud plantform like: AWS, Oracle or Google. Install cloud-utils after increase the disk size from the Cloud Control Console:
sudo apt-get install -y cloud-utils
################### ### Before ########## ###################
df -h
	Filesystem      Size  Used Avail Use% Mounted on
	/dev/sda1        16G   11G  4.2G  72% /
	/dev/sdb1       295G  247G   33G  89% /data
################### ### grow /dev/sdb1 ### ###################
echo 1 > /sys/class/block/sdb/device/rescan
growpart /dev/sdb 1
	CHANGED: partition=1 start=2048 old: size=629143552 end=629145600 new: size=880801759,end=880803807

resize2fs /dev/sdb1
	resize2fs 1.44.5 (15-Dec-2018)
	Filesystem at /dev/sdb1 is mounted on /data; on-line resizing required
	old_desc_blocks = 38, new_desc_blocks = 53
	The filesystem on /dev/sdb1 is now 110100219 (4k) blocks long.
################### ### grow /dev/sda1 ### ###################
echo 1 > /sys/class/block/sda/device/rescan
growpart /dev/sda 1
	CHANGED: partition=1 start=2048 old: size=33550336 end=33552384 new: size=83883999,end=83886047

resize2fs /dev/sda1
	resize2fs 1.44.5 (15-Dec-2018)
	Filesystem at /dev/sda1 is mounted on /; on-line resizing required
	old_desc_blocks = 2, new_desc_blocks = 5
	The filesystem on /dev/sda1 is now 10485499 (4k) blocks long.
################### ### After ########### ###################
df -h
	Filesystem      Size  Used Avail Use% Mounted on
	/dev/sda1        40G   11G   27G  29% /
	/dev/sdb1       413G  247G  146G  63% /data

Wednesday, March 3, 2021

Delete trillion records from a large table

Try to clean a large table (non partition, over 5 trillion records) on a old Oracle 10g Database. [TYPE_ID] in the PK. First try:

DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 1000;

COMMIT;

999 rows deleted. Elapsed: 00:00:11.42		90/second

DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 10000;

COMMIT;

9999 rows deleted. Elapsed: 00:00:19.54		526/second

DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 100000;

COMMIT;

99999 rows deleted. Elapsed: 00:04:47.26	348/second


DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 20000;

COMMIT;

19999 rows deleted. Elapsed: 00:00:40.08	500/second

DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 5000;

COMMIT;

4999 rows deleted. Elapsed: 00:00:10.04		499/second


DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 9000;

COMMIT;

8999 rows deleted. Elapsed: 00:00:21.52		409/second

DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 50000;

COMMIT;

49999 rows deleted. Elapsed: 00:01:51.70	447/second
I wrote a quick loop with commit every 10000 rows:
BEGIN
LOOP 
  DELETE FROM LARGE_TABLE_T WHERE TYPE_ID = 199 AND ROWNUM < 10000;
  COMMIT;
END LOOP;
COMMIT;
END;
Not bad, Eh?
I let it run on the sqlplus from server and keep try different way.

Found another way faster solution after few tweaks:
DECLARE

  CURSOR c_delete_rowids
  IS
	SELECT ROWID
    FROM LARGE_TABLE_T
	WHERE TYPE_ID = 199
	AND ROWNUM <= 1000000;

  v_count number(4) default 0;

BEGIN

  FOR rec IN c_delete_rowids
  LOOP
  
    delete from LARGE_TABLE_T where rowid = rec.rowid;
	v_count := v_count + 1;

	IF v_count >= 1000 then
		v_count := 0;
		COMMIT;
	END IF;

  END LOOP;

  COMMIT;

END;
/
Which delete 1,000,000 records in 1 minute.
SQL> 13:26:21 SQL> 13:26:21 SQL> 13:26:21 SQL> 13:26:21   2  13:26:21   3  13:26:21   4  13:26:21   5  13:26:21   6  13:26:21   7  13:26:21   8  13:26:21   9  13:26:21  10  13:26:21  11  13:26:21  12  13:26:21  13  13:26:21  14  13:26:21  15  13:26:21  16  13:26:21  17  13:26:21  18  13:26:21  19  13:26:21  20  13:26:21  21  13:26:21  22  13:26:21  23  13:26:21  24  13:26:21  25  13:26:21  26  13:26:21  27  13:26:21  28  13:26:21  29  13:26:21  30  13:26:21  31
PL/SQL procedure successfully completed.

Elapsed: 00:01:02.75
To protect the archive log not run crazy, I call it from a shell script and sleep 100 every loop. 1.5 trillion records has been delete around 3 days. Super!!!

Wednesday, July 6, 2016

Oracle Account locked ORA-28000

After Someone type the wrong password in the application, the user been locked in Oracle Database.


SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jul 2 11:35:54 2016

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

ERROR:
ORA-28000: the account is locked


To unlock the Account:

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jul 2 11:36:01 2016

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> alter user account unlock;


Permanent Disable Account lock function.


$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jul 2 11:36:01 2016

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> ALTER PROFILE "DEFAULT" LIMIT failed_login_attempts unlimited;

Wednesday, March 9, 2016

Debian Jessie PHP access Oracle Database

Need Setup a PHP Web Server can access to Oracle Database.

      Configuring PHP with OCI8
    1. First Install Apache and PHP



apt-get install php5-mysql apache2 libapache2-mod-php5 php-pear unzip libaio1



    1. Then download 2 packages from Oracle:



Oracle Instant Client Downloads for Linux x86-64
Instant Client Package - Basic Lite: Smaller version of the Basic package, with only English error messages and Unicode, ASCII, and Western European character set support
Download instantclient-basiclite-linux.x64-12.1.0.2.0.zip (31,302,454 bytes) (cksum - 3592144768)

Instant Client Package - SDK: Additional header files and an example makefile for developing Oracle applications with Instant Client
Download instantclient-sdk-linux.x64-12.1.0.2.0.zip (667,174 bytes) (cksum - 1047596065)

Oracle_Instant_Client

 


    1. Upload these 2 packages to server, uncompress them put it under same folder:



unzip instantclient-basiclite-linux.x64-12.1.0.2.0.zip
unzip instantclient-sdk-linux.x64-12.1.0.2.0.zip
mkdir -p /usr/lib/oracle/12.1/client64/lib
mv instantclient_12_1/* /usr/lib/oracle/12.1/client64/lib/

Link libclntsh.so.12.1 libclntsh.so
cd /usr/lib/oracle/12.1/client64/lib/
ln -s libclntsh.so.12.1 libclntsh.so

Make sure the sdk/ folder and files also under same directory:

ls -alt /usr/lib/oracle/12.1/client64/lib/
lrwxrwxrwx 1 root root       17 Mar  9 11:43 libclntsh.so -> libclntsh.so.12.1
drwxr-xr-x 3 root root 4096 Mar 9 11:33 ..
drwxrwxr-x 5 root root 4096 Jul 7 2014 sdk
-rwxrwxr-x 1 root root 6990875 Jul 7 2014 libclntshcore.so.12.1
-rwxrwxr-x 1 root root 58793741 Jul 7 2014 libclntsh.so.12.1
-r-xr-xr-x 1 root root 1768370 Jul 7 2014 libipc1.so
-r-xr-xr-x 1 root root 544150 Jul 7 2014 libmql1.so
-rwxrwxr-x 1 root root 2576030 Jul 7 2014 libocci.so.12.1
-r-xr-xr-x 1 root root 156353 Jul 7 2014 libocijdbc12.so
-r--r--r-- 1 root root 3692096 Jul 7 2014 ojdbc6.jar
-r--r--r-- 1 root root 3698857 Jul 7 2014 ojdbc7.jar
-rw-rw-r-- 1 root root 71202 Jul 7 2014 xstreams.jar
-rwxrwxr-x 1 root root 29404 Jul 7 2014 adrci
-rw-rw-r-- 1 root root 450 Jul 7 2014 BASIC_LITE_README
-rwxrwxr-x 1 root root 43944 Jul 7 2014 genezi
-r-xr-xr-x 1 root root 6213011 Jul 7 2014 libnnz12.so
-rwxrwxr-x 1 root root 6005681 Jul 7 2014 libociicus.so
-r-xr-xr-x 1 root root 337137 Jul 7 2014 libons.so
-rwxrwxr-x 1 root root 118491 Jul 7 2014 liboramysql12.so
-rwxrwxr-x 1 root root 227410 Jul 7 2014 uidrvci

Optional add it into LD_LIBRARY by create file: /etc/ld.so.conf.d/x86_64-oracle.conf
# Oracle Instant Client
/usr/lib/oracle/12.1/client64/lib

run ldconfig to apply the changes.
# ldconfig



    1. Installing OCI8 from PECL



http://pecl.php.net/package/oci8

Use the OCI8 extension to access Oracle Database. PHP OCI8 2.1 builds with PHP 7.
Use 'pecl install oci8-2.0.10' to install OCI8 for PHP 5.2 - PHP 5.6.
Use 'pecl install oci8-1.4.10' to install PHP OCI8 1.4 for PHP 4.3.9 - PHP 5.1.

Debian Jessi comes with PHP 5.6, when it ask the path for library give it the directory where the library located:
# pecl install oci8-2.0.10
downloading oci8-2.0.10.tgz ...
Starting to download oci8-2.0.10.tgz (Unknown size)
.....done
running: phpize
Configuring for:
Please provide the path to the ORACLE_HOME directory.
Use 'instantclient,/path/to/instant/client/lib' if you're compiling with Oracle Instant Client [autodetect] :

instantclient,/usr/lib/oracle/12.1/client64/lib

Build process completed successfully
Installing '/usr/lib/php5/20131226/oci8.so'
install ok: channel://pecl.php.net/oci8-2.0.10
configuration option "php_ini" is not set to php.ini location
You should add "extension=oci8.so" to php.ini

Create oci8.ini file for php: /etc/php5/mods-available/oci8.ini
extension=oci8.so

cd /etc/php5/cli/conf.d/
ln -s ../../mods-available/oci8.ini
cd /etc/php5/apache2/conf.d/
ln -s ../../mods-available/oci8.ini

service apache2 restart

References:
http://php.net/manual/en/oci8.installation.php
http://www.oracle.com/technetwork/articles/technote-php-instant-084410.html


In case you got error message when start Apache:

# cat error.log
PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php5/20131226/oci8.so' - libaio.so.1: cannot open shared object file: No such file or directory in Unknown on line 0
[Mon Oct 17 16:06:28.407599 2016] [mpm_prefork:notice] [pid 21276] AH00163: Apache/2.4.10 (Debian) configured -- resuming normal operations
[Mon Oct 17 16:06:28.407640 2016] [core:notice] [pid 21276] AH00094: Command line: '/usr/sbin/apache2'

Make sure the oci8.so file exist:
# ls /usr/lib/php5/20131226/oci8.so

And Install the Libaio:
# apt-get install libaio1

Thursday, January 30, 2014

Oracle Linux: Maximize Performance / Minimize Downtime

Oracle Linux: Maximize Performance / Minimize Downtime
http://medianetwork.oracle.com/video/player/2921003278001

Oracle’s Greg Marsden provides a large number of specific tips and tricks for getting the most from Oracle Linux.
Really helpful information about Large Memory Linux system.
Example for 1TB RAM system.

Key Note:
NUMA
Swap Tuning
Kernel UEK
kdump
Serial console
HugePages
...


Highly Recommend!

Wednesday, December 4, 2013

ORA-27086: unable to lock file - already in use Netapp NFS

My 10.2 database crashed suddenly.
Can't login by # sqlplus / as sysdba
Can't shutdown it

I have to reboot the server.

Got error after OS is back:


Wed Dec 04 22:51:55 EST 2013
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle_drives/oracle_sys/control01.ctl'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Wed Dec 04 22:51:55 EST 2013
ORA-205 signalled during: ALTER DATABASE MOUNT...


This is a classic symptom of a Netapp problem, which likes to hold file locks open on NFS mounts.
There is a standard procedure for clearing those locks; see, for instance, document 429912.1 on Metalink.

ORA-01157 ORA-01110 ORA-27086 after crash prevents database from opening (Doc ID 429912.1)
Modified: 01-Mar-2013

As root on the NetApp, from the prompt:

priv set advanced
lock status -h
sm_mon -l
lock status -h


Thanks Team!!!
I am able to bring it back on line after that.

This Server changed the Hostname about 6 months ago.
The Netapp promtp still shows the old Hostname.
That could be the reason.

Reference:https://forums.oracle.com/thread/653757

Please Notice part of the reference I don't agree with:
Do not copy control*.ctl files before you know what are you doing.
In my case, I did not touch any control or data file.
Only restart the Oracle instance several times.

Best Luck!

Thursday, August 29, 2013

Rotating Oracle Listener.log file

Oracle database logs doesn’t rotate by it self.
As time goes by, the server may hold logs that are too big to read and takes too much storage space.
This can get your server to a maximum capacity or cause the listener can't start.

The best solution is to use logrotate:

There are 2 log files needs to be rotate:
Alert log and Listener log

Create new logrotate rules by edit a files under /etc/logrotate.d/

/etc/logrotate.d/oracle-alert

# Logrotate file for Oracle Alert

/home/oracle/admin/[SID]/bdump/alert_[SID].log {
missingok
weekly
rotate 4
compress
delaycompress
create 640 oracle oinstall
}


/etc/logrotate.d/oracle-listener

# Logrotate file for Oracle Listener

/home/oracle/product/10.2.0/network/log/listener.log {
missingok
daily
rotate 31
compress
delaycompress
create 644 oracle oinstall
prerotate
su - oracle -c "lsnrctl set Log_status off"
endscript
postrotate
su - oracle -c "lsnrctl set Log_status on"
endscript
}


Notice:delaycompress
This command can be useful if you want the archived logs to be compressed, but not right away.
With "delaycompress" active an archived log won't be compressed until the next time the log is rotated.
Note that "delaycompress" only works if you also have "compress" in your config.

To test it out:
/usr/sbin/logrotate -vdf /etc/logrotate.conf

To force run it:
/usr/sbin/logrotate -vf /etc/logrotate.conf

Reference:
http://articles.slicehost.com/2010/6/30/understanding-logrotate-on-centos-part-1
http://articles.slicehost.com/2010/6/30/understanding-logrotate-on-centos-part-2

Wednesday, August 28, 2013

Is it safe to use ROWID to locate a row doing update?

If the strings length changes a lot, the rowid could change.

it will be safer to do it with PK together:


update t
set...
where rowid = :x and primary_key = :pk;



Reference:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53140678334596

Thursday, August 22, 2013

Setup OCI8 Extension for PHP by pecl php-pear with Oracle instant Client

1.Install php-pear pecl

# yum install php-pear gcc


2.Install oracle-instantclient-devel


# rpm -ivh oracle-instantclient-devel-10.2.0.5-1.i386.rpm


3.Compile PHP OCI8 Extension


# pecl install oci8
Please provide the path to the ORACLE_HOME directory. Use 'instantclient,/path/to/instant/client/lib' if you're compiling with Oracle Instant Client [autodetect] : instantclient,/usr/lib/oracle/10.2.0.5/client/lib

running: make INSTALL_ROOT="/var/tmp/pear-build-root/install-oci8-1.4.10" install
Installing shared extensions: /var/tmp/pear-build-root/install-oci8-1.4.10/usr/lib/php/modules/
running: find "/var/tmp/pear-build-root/install-oci8-1.4.10" -ls
4411004 4 drwxr-xr-x 3 root root 4096 Aug 22 04:10 /var/tmp/pear-build-root/install-oci8-1.4.10
4411033 4 drwxr-xr-x 3 root root 4096 Aug 22 04:10 /var/tmp/pear-build-root/install-oci8-1.4.10/usr
4411034 4 drwxr-xr-x 3 root root 4096 Aug 22 04:10 /var/tmp/pear-build-root/install-oci8-1.4.10/usr/lib
4411035 4 drwxr-xr-x 3 root root 4096 Aug 22 04:10 /var/tmp/pear-build-root/install-oci8-1.4.10/usr/lib/php
4411036 4 drwxr-xr-x 2 root root 4096 Aug 22 04:10 /var/tmp/pear-build-root/install-oci8-1.4.10/usr/lib/php/modules
4411032 408 -rwxr-xr-x 1 root root 412333 Aug 22 04:10 /var/tmp/pear-build-root/install-oci8-1.4.10/usr/lib/php/modules/oci8.so

Build process completed successfully
Installing '/var/tmp/pear-build-root/install-oci8-1.4.10//usr/lib/php/modules/oci8.so'
install ok: channel://pecl.php.net/oci8-1.4.10
You should add "extension=oci8.so" to php.ini


4.Create /etc/php.d/oci8.ini

; Enable OCI8 extension module
extension=oci8.so

[OCI8]

; Connection: Enables privileged connections using external
; credentials (OCI_SYSOPER, OCI_SYSDBA)
;oci8.privileged_connect = Off

; Connection: The maximum number of persistent OCI8 connections per
; process. Using -1 means no limit.
;oci8.max_persistent = -1

; Connection: The maximum number of seconds a process is allowed to
; maintain an idle persistent connection. Using -1 means idle
; persistent connections will be maintained forever.
;oci8.persistent_timeout = -1

; Connection: The number of seconds that must pass before issuing a
; ping during oci_pconnect() to check the connection validity. When
; set to 0, each oci_pconnect() will cause a ping. Using -1 disables
; pings completely.
;oci8.ping_interval = 60

; Connection: Set this to a user chosen connection class to be used
; for all pooled server requests with Oracle 11g Database Resident
; Connection Pooling (DRCP). To use DRCP, this value should be set to
; the same string for all web servers running the same application,
; the database pool must be configured, and the connection string must
; specify to use a pooled server.
;oci8.connection_class =

; High Availability: Using On lets PHP receive Fast Application
; Notification (FAN) events generated when a database node fails. The
; database must also be configured to post FAN events.
;oci8.events = Off

; Tuning: This option enables statement caching, and specifies how
; many statements to cache. Using 0 disables statement caching.
;oci8.statement_cache_size = 20

; Tuning: Enables statement prefetching and sets the default number of
; rows that will be fetched automatically after statement execution.
;oci8.default_prefetch = 100

; Compatibility. Using On means oci_close() will not close
; oci_connect() and oci_new_connect() connections.
;oci8.old_oci_close_semantics = Off

Friday, August 16, 2013

ORA-06564 While removing Oracle Streams

Try to remove Streams setting:


SQL> exec DBMS_STREAMS_ADM.remove_streams_configuration();

ORA-06564: object "MDS"."BIN$2m0kNCkdsv7gQKjAuL4Hww==$0" does not exist


ORA-06564 errors are misleading while removing oracle stream configuration using DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION().

When removing oracle stream some of objects are reference but not exist with original name.
It is due to referred objects are moved to recycle bin with new name with standard naming convention ‘BIN$==$0’.
When object moved to recycle bin then only name changed and not object_id of it causing DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION procedure to fail.
This is observed in Oracle 10g and 11g as well.

Solution

Purge the object from the recycle bin.

Login as owner, here I am using MDS as example:

# sqlplus MDS
PURGE TABLE "BIN$2m0kNCkdsv7gQKjAuL4Hww==$0";


Run again DBMS_STREAMS_ADM.remove_streams_configuration()
It may possible that package fails again for different object.

Remove recycle bin until clean:

PURGE TABLE "BIN$2m0kNCdpsv7gQKjAuL4Hww==$0";
PURGE TABLE "BIN$2m0kNCrksv7gQKjAuL4Hww==$0";
PURGE TABLE "BIN$2m0kNCeasv7gQKjAuL4Hww==$0";
PURGE TABLE "BIN$2m0kNCn+sv7gQKjAuL4Hww==$0";
PURGE TABLE "BIN$2m0kNCqZsv7gQKjAuL4Hww==$0";


Purge all objects of that schema OR purge dba_recyclebin using sysdba if you don’t need recovery of them.

# sqlplus / as sysdba
SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> exec DBMS_STREAMS_ADM.remove_streams_configuration();

PL/SQL procedure successfully completed.

Move Rename Oracle Control File


SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oracle_drives/cca_data/SCAORA1/controlfile/o1_mf_904ml2jz_.ctl
/oracle_drives/oracle_fra/SCAORA1/controlfile/o1_mf_904ml2lw_.ctl

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oracle_drives/cca_data/SCAORA
1/controlfile/o1_mf_904ml2jz_.
ctl, /oracle_drives/oracle_fra
/SCAORA1/controlfile/o1_mf_904
ml2lw_.ctl

SQL> ALTER SYSTEM SET control_files='/oracle_drives/oracle_sys/control01.ctl','/oracle_drives/oracle_fra/control02.ctl' SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ! mv /oracle_drives/cca_data/SCAORA1/controlfile/o1_mf_904ml2jz_.ctl /oracle_drives/oracle_sys/control01.ctl

SQL> ! mv /oracle_drives/oracle_fra/SCAORA1/controlfile/o1_mf_904ml2lw_.ctl /oracle_drives/oracle_fra/control02.ctl

SQL> startup
ORACLE instance started.

Total System Global Area 4,2950E+10 bytes
Fixed Size 2175824 bytes
Variable Size 4,2346E+10 bytes
Database Buffers 587202560 bytes
Redo Buffers 14598144 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oracle_drives/oracle_sys/control01.ctl
/oracle_drives/oracle_fra/control02.ctl

Move Rename Oracle System datafile


$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 16 10:46:20 2013

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME
--------------------------------------------------------------------------------
/oracle_drives/cca_data/SCAORA1/datafile/o1_mf_system_904ml42n_.dbf

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4,2950E+10 bytes
Fixed Size 2175824 bytes
Variable Size 4,2346E+10 bytes
Database Buffers 587202560 bytes
Redo Buffers 14598144 bytes
Database mounted.
SQL> ! cp /oracle_drives/cca_data/SCAORA1/datafile/o1_mf_system_904ml42n_.dbf /oracle_drives/oracle_sys/system001.dbf;

SQL> alter database rename file '/oracle_drives/cca_data/SCAORA1/datafile/o1_mf_system_904ml42n_.dbf' to '/oracle_drives/oracle_sys/system001.dbf';

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME
--------------------------------------------------------------------------------
/oracle_drives/oracle_sys/system001.dbf

Friday, August 9, 2013

Add tnsping into Oracle Instant Client on CentOS RHEL OEL

tnsping is a very handy tool from Oralce.
But it does not include with Oracle Instant Client!

To add it, you will need a Oracle Client software installed on the other machine with same OS (Notice:32/64 differently)

Then copy 3 files to the Oracle Instant Client folder:


scp root@192.168.1.4:/u01/app/oracle/product/10.2.0/db_1/bin/tnsping /usr/lib/oracle/10.2.0.5/client/bin/
mkdir -p /usr/lib/oracle/10.2.0.5/client/network/mesg
scp root@192.168.1.4:/u01/app/oracle/product/10.2.0/db_1/network/mesg/tnsus.msb /usr/lib/oracle/10.2.0.5/client/network/mesg/
scp root@192.168.1.4:/u01/app/oracle/product/10.2.0/db_1/network/mesg/tnsus.msg /usr/lib/oracle/10.2.0.5/client/network/mesg/



/etc/sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES)

Wednesday, August 7, 2013

Installing oracle instant client on CentOS RHEL OEL 5.9

This is a guide of Install sqlplus with the minimal oracle-software fingerprint.

I try to not install the Oracle Client in my computers, it modify lots of registry in Windows, and in linux you need XWindow GUI or remote XWindow plus set lots of environment variables and deal with Oracle Universal Installer (OUI) that depends on many packages, versions, java … etc.

1.First Download the package from Oracle Instant Client

2.Install the 2 packages matchs your OS, I am using OEL 5.9 x86 as example:

rpm -ivh oracle-instantclient-basic-10.2.0.5-1.i386.rpm
rpm -ivh oracle-instantclient-sqlplus-10.2.0.5-1.i386.rpm


3.You should have a structure and files like:

/usr/lib/oracle/10.2.0.5/client/bin/genezi
sqlplus
/usr/lib/oracle/10.2.0.5/client/lib/glogin.sql
libsqlplusic.so
libsqlplus.so
libocci.so.10.1
libocijdbc10.so
ojdbc14.jar
libclntsh.so.10.1
libnnz10.so
libociei.so


4.Add lib to ldconfig

vi /etc/ld.so.conf.d/oracle.conf
/usr/lib/oracle/10.2.0.5/client/lib
re-run: #//sbin/ldconfig
to active the changes


5.create a tnsnames.ora at /etc:

PRODUCT_ORA1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora1)
)
)


6.To test it out:

/usr/lib/oracle/10.2.0.5/client/bin/sqlplus sys@PRODUCT_ORA1

Monday, April 29, 2013

Oracle ORA-28001: the password has expired

Just happened today on an new Oracle Build.

In Oracle Database, A remote DBA face this because password have reached 180 Default limit for Password life time.
The life of a password is defined as 180 days by default.

To check which user got Expired password:

select username, account_status from dba_users where ACCOUNT_STATUS LIKE '%EXPIRED%';


First connect as DBA:

$ sqlplus / as sysdba


To unlock an user:
SQL> alter user [user_name] ACCOUNT UNLOCK;


To reset the password:
SQL> alter user [user_name] identified by [password];


To Keep same Password:

SQL> select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;
Enter value for username: scott
old 3: where d.username = upper('&&username')
new 3: where d.username = upper('scott')


C
--------------------------------------------------------------------------------
alter user "SCOTT" identified by values 'F894844C34402B67';


SQL> alter user "SCOTT" identified by values 'F894844C34402B67';


On a production system, has to change this limit to unlimited to solve this and to avid this in future.

ALTER PROFILE DEFAULT LIMIT password_life_time UNLIMITED;

Thursday, February 28, 2013

Oracle How to found current running SQL

SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address
order by address, piece;

select sid,user_name,sql_text
from v$open_cursor
where sid in (select sid from (select sid,serial#,username,program
from v$session
where status='ACTIVE'));

select s.USERNAME, sq.SQL_TEXT, s.LAST_CALL_ET
from v$open_cursor oc, v$sql sq, v$session s
where oc.HASH_VALUE = sq.HASH_VALUE
and s.SID = oc.SID
and s.STATUS = 'ACTIVE';