One of the most important things that a developer does apart from just code development is, debugging. Isn’t it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere. As of now, any application has it’s own user defined error logging table(s).

Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1

A lot of times developers complain that they do not have privilege to create tables and thus they cannot log the errors in a user defined error logging table. In such cases, it’s a really helpful feature, at least during the unit testing of the code.

I made a small demonstration in SCOTT schema using the default error log table SPERRORLOG, hope this step by step demo helps to understand easily :

NOTE : SQL*Plus error logging is set OFF by default. So, you need to “set errorlogging on” to use the SPERRORLOG table.

SP2 Error

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc sperrorlog;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             VARCHAR2(1024)
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

SQL> truncate table sperrorlog;

Table truncated.

SQL> set errorlogging on;
SQL> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.27.29.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

ORA Error

SQL> truncate table sperrorlog;

Table truncated.

SQL> select * from dula;
select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.36.08.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

select * from dula
ORA-00942: table or view does not exist

Like shown above, you can capture PLS errors too.

If you want to execute it through scripts, you can do it like this, and later spool the errors into a file. I kept these three lines in the sperrorlog_test.sql file -

truncate table sperrorlog;
selct * from dual;
select * from dula;

SQL> @D:sperrorlog_test.sql;

Table truncated.

SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select TIMESTAMP, SCRIPT, STATEMENT, MESSAGE from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.17.000000 AM

D:sperrorlog_test.sql;
SP2-0734: unknown command beginning "D:sperror..." - rest of line ignored.


TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.27.000000 AM
D:sperrorlog_test.sql
selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.


TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.27.000000 AM
D:sperrorlog_test.sql
select * from dula
ORA-00942: table or view does not exist

SQL>

In addition to above, if you want to be particularly specific about each session’s error to be spooled into a file you could do this -

SQL> set errorlogging on identifier my_session_identifier

SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';

To spool the session specific errors into a file, just do this -

SQL> spool error.log
SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';
SQL> spool off

AIX is like an Unix environment awesome original, same to HP-Unix, and, if you have a plan to install Oracle RAC, you need to pay attention. I note some tips in this article to help.

1. Checking Operating System Packages

# lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat 
bos.perf.perfstat bos.perf.proctools rsct.basic.rte rsct.compat.clients.rte 
xlC.aix61.rte

If not, install on AIX source by smity. It's easy, but remember, some packaged requires your IBM's account to download.

2. Verify UDP and TCP Kernel Parameters

# /usr/sbin/no -a | fgrep ephemeral

If you expect your workload to require a high number of ephemeral ports, then update
the UDP and TCP ephemeral port range to a broader range. For example:

# /usr/sbin/no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500
# /usr/sbin/no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500

3. Checking Resource Limits:
To ensure that these resource limits are honored, confirm that the line login
session required /usr/lib/security/pam_aix is set in /etc/pam.conf.For example:

dtsession auth required /usr/lib/security/pam_aix
dtlogin session required /usr/lib/security/pam_aix
ftp session required /usr/lib/security/pam_aix
imap session required /usr/lib/security/pam_aix
login session required /usr/lib/security/pam_aix
rexec session required /usr/lib/security/pam_aix
rlogin session required /usr/lib/security/pam_aix
rsh session required /usr/lib/security/pam_aix
snapp session required /usr/lib/security/pam_aix
su session required /usr/lib/security/pam_aix
swrole session required /usr/lib/security/pam_aix
telnet session required /usr/lib/security/pam_aix
xdm session required /usr/lib/security/pam_aix
OTHER session required /usr/lib/security/pam_prohibit
websm_rlogin session required /usr/lib/security/pam_aix
websm_su session required /usr/lib/security/pam_aix
wbem session required /usr/lib/security/pam_aix

4. Tuning AIX System Environment
Confirm the aio_maxreqs value using the procedure for your release:
AIX 6.1 and 7.1:

# ioo -o aio_maxreqs
aio_maxreqs = 65536

The aio is Asynchronous Input Output is an exciting parameter, I tried to control and modified it many times, but it's strongly to do from Oracle advices,

Oracle document refer: https://docs.oracle.com/database/121/AXDBI/app_manual.htm#AXDBI7880

5. Tuning Virtual Memory Manager

vmo -p -o minperm%=3
vmo -p -o maxperm%=90
vmo -p -o maxclient%=90
vmo -p -o lru_file_repage=0
vmo -p -o strict_maxclient=1
vmo -p -o strict_maxperm=0

Note: You must restart the system for these changes to take effect

6. Increase System block size allocation

# /usr/sbin/chdev -l sys0 -a ncargs='128'

7. Configure SSH LoginGraceTime Parameter
On AIX systems, the OpenSSH parameter LoginGraceTime by default is commented
out, and the default behavior of OpenSSH on AIX can sometimes result in timeout
errors. To avoid these errors, complete the following procedure:
7.1. Log in as root.
7.2. Using a text editor, open the OpenSSH configuration file /etc/ssh/sshd_config.
7.3. Locate the comment line #LoginGraceTime 2m.
7.4. Uncomment the line, and change the value to 0 (unlimited). For example:
LoginGraceTime 0
7.5. Save /etc/ssh/sshd_config.
7.6. Restart SSH.

8. Setting priviledge to Oracle ASM Luns
Same to Solaris, HP-Unix. Remember, when you've got failure of ASM configuration, you need to flush out the disk's slice/partition by OS command "dd". And the slice/partition/LUN allocated from storage to IBM, has got different first alphabet to other platform. The alphabet begins by "r", example:

7.1 ORC and Voting disk
# chown grid:asmadmin /dev/rhdisk5 -> OCR
# chmod 660 /dev/rhdisk5 
# chown grid:asmadmin /dev/rhdisk6 -> Voting Disk
# chmod 660 /dev/rhdisk6
7.2 Datafile, Archivelog and Backup 
# chown grid:asmadmin /dev/rhdisk2
# chmod 660 /dev/rhdisk2
# chown grid:asmadmin /dev/rhdisk3
# chmod 660 /dev/rhdisk3
# chown grid:asmadmin /dev/rhdisk4
# chmod 660 /dev/rhdisk4
# chown grid:asmadmin /dev/rhdisk9
# chmod 660 /dev/rhdisk9
# chown grid:asmadmin /dev/rhdisk10
# chmod 660 /dev/rhdisk10

9. Enable simultaneous access to a disk device from multiple nodes
To enable simultaneous access to a disk device from multiple nodes, you must set the appropriate Object Data Manager (ODM) attribute, depending on the type of reserve attribute used by your disks. The following section describes how to perform this task using hdisk logical names

8.1. determine the reserve setting your disks use, enter the following command,where n is the hdisk device number

# lsattr -E -l hdiskn | grep reserve_

The response is either a reserve_lock setting, or a reserve_policy setting. If the attribute is reserve_lock, then ensure that the setting is reserve_lock = no. If the attribute is reserve_policy, then ensure that the setting is reserve_policy = no_reserve.

8.2. If necessary, change the setting with the chdev command using the following syntax, where n is the hdisk device number:

chdev -l hdiskn -a [ reserve_lock=no | reserve_policy=no_reserve ]

# chdev -l hdisk5 -a reserve_lock=no
# chdev -l hdisk5 -a reserve_policy=no_reserve

8.3. Enter commands similar to the following on any node to clear the PVID from each disk device that you want to use:

# /usr/sbin/chdev -l hdiskn -a pv=clear

When you are installing Oracle Clusterware, you must enter the paths to the appropriate device files when prompted for the path of the OCR and Oracle Clusterware voting disk. For example: /dev/rhdisk10

9.Configure Shell Limits
9.1. Add the following lines to the /etc/security/limits file:

default:
fsize = -1
core = 2097151
cpu = -1
data = -1
rss = -1
stack = -1
nofiles = -1

9.2.Enter the following command to list the current setting for the maximum number of process allowed by the Oracle software user:

/usr/sbin/lsattr -E -l sys0 -a maxuproc

If necessary, change the maxuproc setting using the following command:

/usr/sbin/chdev -l sys0 -a maxuproc=16384

10. Configure User Process Parameters (Verify that the maximum number of processes allowed for each user is set to 2048 or greater)
Enter the following command:

# smit chgsys

11. Configure Network Tuning Parameters:
To check the current values of the network tuning parameters:

# no -a | more

If the system is running in compatibility mode, then follow these steps to change the parameter values:

Enter commands similar to the following to change the value of each parameter:

# no -o parameter_name=value

For example:

# no -o udp_recvspace=655360

Add entries similar to the following to the /etc/rc.net file for each parameter that you changed in the previous step:

if [ -f /usr/sbin/no ] ; then
/usr/sbin/no -o udp_sendspace=65536
/usr/sbin/no -o udp_recvspace=655360
/usr/sbin/no -o tcp_sendspace=65536
/usr/sbin/no -o tcp_recvspace=65536
/usr/sbin/no -o rfc1323=1
/usr/sbin/no -o sb_max=4194304
/usr/sbin/no -o ipqmaxlen=512
fi

For the ISNO parameter tcp_sendspace, use the following command to set it:

# ifconfig en0 tcp_sendspace 65536

12. Automatic SSH configuration
By default, OUI searches for SSH public keys in the directory /usr/local/etc/, and ssh-keygen binaries in /usr/local/bin. However, on AIX, SSH public keys typically are located in the path /etc/ssh, and ssh-keygen binaries are located in the path /usr/bin. To ensure that OUI can set up SSH, use the following command to create soft links:

# ln -s /etc/ssh /usr/local/etc
# ln -s /usr/bin /usr/local/bin

In rare cases, Oracle Clusterware installation may fail during the "AttachHome" operation when the remote node closes the SSH connection. To avoid this problem, set the following parameter in the SSH daemon configuration file /etc/ssh/sshd_config on all cluster nodes to set the timeout wait to unlimited:

LoginGraceTime 0

13. Shell Limit
Adding these line in /etc/security/limits

default:
fsize = -1
core = 2097151
cpu = -1
data = -1
rss = -1
stack = -1
nofiles = -1

14. Create groups and users

# mkgroup -'A' id='1000' adms='root' oinstall
# mkgroup -'A' id='1031' adms='root' dba
# mkgroup -'A' id='1032' adms='root' oper
# mkgroup -'A' id='1020' adms='root' asmadmin
# mkgroup -'A' id='1022' adms='root' asmoper
# mkgroup -'A' id='1021' adms='root' asmdba
# mkuser id='1100' pgrp='oinstall' groups='dba,asmadmin,asmoper,asmdba' home='/portalgrid/grid' grid
# mkuser id='1101' pgrp='oinstall' groups='dba,oper,asmdba' home='/portaloracle/oracle' oracle

# mkdir -p /portalapp/app/11.2.0/grid
# mkdir -p /portalapp/app/grid
# mkdir -p /portalapp/app/oracle

# chown grid:oinstall /portalapp/app/11.2.0/grid <- GRID_HOME
# chown grid:oinstall /portalapp/app/grid <- GRID_BASE (ORACLE_BASE for Grid user)
# chown -R grid:oinstall /portalapp
# chown oracle:oinstall /portalapp/app/oracle
# chmod -R 775 /portalapp/

15. Setting the profile
15.1. Grid Profile

export TEMP=/tmp
export TMP=/tmp
export TMPDIR=/tmp
umask 022

export ORACLE_HOSTNAME=portal1
export ORACLE_BASE=/portalapp/app/grid
export ORACLE_HOME=/portalapp/app/11.2.0/grid
export GRID_HOME=/portalapp/app/11.2.0/grid
export CRS_HOME=/portalapp/app/11.2.0/grid
export ORACLE_SID=+ASM1

export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

16. Prevent Xserver does not display correct term

# startsrc -x

17. Create the following softlink needed for some Oracle utilites

# ln -s /usr/sbin/lsattr /etc/lsattr

To check existing capabilities, enter the following command as root; in this example,
the Grid installation user account is grid:

# /usr/bin/lsuser -a capabilities grid

To add capabilities, enter a command similar to the following:

# /usr/bin/chuser capabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE grid

18. Remember to run the Installation fixup scripts

$ ./runcluvfy.sh stage -pre crsinst -n node -fixup -verbose

With Oracle Clusterware 11g release 2, Oracle Universal Installer (OUI) detects when the minimum requirements for an installation are not met, and creates shell scripts,
called fixup scripts, to finish incomplete system configuration steps. If OUI detects an incomplete task, then it generates fixup scripts (runfixup.sh). You can run the fixup script after you click the Fix and Check Again Button.

19. In the installation progressing, when root.sh at node 2, can the error such as "CRS appear in node 1, did not attemp to stop cluster, re-join cluster, by pass and continue installation.
- If Xterm did not occur, then do: $ export ORACLE_TERM=dtterm
- Manually export ORACLE_BASE, ORACLE_HOME when make an installation before running runInstaller.sh
- If /tmp is too small <500MB, then make a private directory point to other directory, change owner to grid, oracle user, example:

A. Grid

# cd /portallog
# mkdir /portallog/tmp
# chown -R grid:oinstall /portallog/tmp

B. Oracle

# cd /portal
# mkdir tmp
# chown -R oracle:dba /portal/tmp 

C. Export

# export TMP=/portallog/tmp
# export TEMPDIR=/portallog/tmp
# export TMPDIR=/portallog/tmp

Hope this help.
End.
TAT