printtbl8.sql - oracle sqlplus print output vertically

Put the following as printtbl8.sql in $ORACLE_HOME/rdbms/admin/printtbl8.sql:

set serveroutput on
set linesize 200
    l_theCursor    integer default dbms_sql.open_cursor;
    l_columnValue    varchar2(4000);
    l_status        integer;
    l_descTbl    dbms_sql.desc_tab;
    l_colCnt        number;
    procedure execute_immediate( p_sql in varchar2 )
        l_status := dbms_sql.execute(l_theCursor);
    execute_immediate( 'alter session set nls_date_format=
                        ''dd-mon-yyyy hh24:mi:ss'' ');
    dbms_sql.parse(    l_theCursor,
                    replace( '&1', '"', ''''),
                    dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor,
                            l_colCnt, l_descTbl );
    for i in 1 .. l_colCnt loop
        dbms_sql.define_column( l_theCursor, i,
                                l_columnValue, 4000 );
    end loop;
    l_status := dbms_sql.execute(l_theCursor);
    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i,
                                l_columnValue );
                ( rpad( l_descTbl(i).col_name,
         35 ) || ': ' || l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;
    execute_immediate( 'alter session set nls_date_format=
                        ''dd-MON-yy'' ');
    when others then
        execute_immediate( 'alter session set
                        nls_date_format=''dd-MON-yy'' ');

Now you can have a test in oracle sqlplus:

SQL> @?/rdbms/admin/printtbl8.sql 'select name,LOG_MODE,OPEN_MODE from v$database'
old 17: replace( '&1', '"', ''''),
new 17: replace( 'select name,LOG_MODE,OPEN_MODE from v$database', '"', ''''),


PL/SQL procedure successfully completed.

Cool, right?

resolved - file filelists.xml.gz [Errno 5] OSError: [Errno 2] No such file or directory [Errno 256] No more mirrors to try

Today below error prompted when running yum install some packages in linux:

file://localhost/tmp/common1/x86_64/redhat/50/base/ga/Server/repodata/filelists.xml.gz: [Errno 5] OSError: [Errno 2] No such file or directory: '/tmp/common1/x86_64/redhat/50/base/ga/Server/repodata/filelists.xml.gz'
Trying other mirror.
Error: failure: repodata/filelists.xml.gz from base: [Errno 256] No more mirrors to try.
You could try running: package-cleanup --problems
package-cleanup --dupes
rpm -Va --nofiles --nodigest

After some checking(yum clean all, download repo to /etc/yum.repos.d, etc), I finally found it's caused by the following entries in /etc/yum.conf:

name=Red Hat Linux - Base

After I commented them, yum install can work now.


resolved - Starting MySQL.The server quit without updating PID file (/var/lib/mysql/

Today when I tried to start mysql it failed with below error:

[root@testvm ~]# /etc/init.d/mysql start
Starting MySQL.The server quit without updating PID file (/var/lib/mysql/

First I had a check of /var/lib/mysql/testvm.err, and it had below entries:

2015-04-03 00:11:39 2925 [Note] InnoDB: Using CPU crc32 instructions
/usr/sbin/mysqld: Can't create/write to file '/tmp/ibDvk6bb' (Errcode: 13 - Permission denied)
2015-04-03 00:11:39 7f28af6c6720 InnoDB: Error: unable to create temporary file; errno: 13
2015-04-03 00:11:39 2925 [ERROR] Plugin 'InnoDB' init function returned error.
2015-04-03 00:11:39 2925 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-04-03 00:11:39 2925 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-04-03 00:11:39 2925 [ERROR] Aborting

I had a check of /tmp permission, and it's not correct:

[root@testvm ~]# ls -ld /tmp
drwx------ 19 root root 4096 Apr 3 07:15 /tmp

So I changed permission for /tmp to 777 with sticky bit:

[root@testvm ~]# chmod 1777 /tmp

[root@testvm ~]# ls -ld /tmp
drwxrwxrwt 19 root root 4096 Apr 3 07:15 /tmp

However, when I tried start mysql, it failed again with below errors in /var/lib/mysql/testvm.err:

2015-04-03 00:20:42 18724 [ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!
2015-04-03 00:20:42 18724 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2015-04-03 00:20:42 18724 [ERROR] Plugin 'InnoDB' init function returned error.
2015-04-03 00:20:42 18724 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-04-03 00:20:42 18724 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-04-03 00:20:42 18724 [ERROR] Aborting

So it's all about InnoDB engine. As InnoDB was not required in our env, so I determined to disable InnoDB:

[root@testvm ~]# vi /etc/my.cnf

Later, the start of mysql succeeded.

change NIC configuration to make new VLAN tag take effect

Sometimes you may want to add vlan tag to existing NIC, and after the addition, you'll need to change DNS names bound to the old tag with new IPs in the newly added vlan tag. After all these two steps done, you'll need to make changes on the hosts(take linux for example) to make these changes into effect.

In this example, I'm going to move the old v118_FE to the new VLAN v117_FE.

ifconfig v118_FE down
ifconfig bond0.118 down
cd /etc/sysconfig/network-scripts
mv ifcfg-bond0.118 ifcfg-bond0.117
vi ifcfg-bond0.117
mv ifcfg-v118_FE ifcfg-v117_FE
vi ifcfg-v117_FE
ifup v117_FE
ifup bond0.117

resolved - VPN Service not available, The VPN agent service is not responding. Please restart this application after a while.

Today when I tried to connect to VPN through Cisco AnyConnect Secure Mobility Client, the following error dialog prompted:


VPN Service not available
VPN Service not available

And after I clicked "OK" button, the following dialog prompted:

The VPN agent service is not responding
The VPN agent service is not responding

So all of the two dialogs were complaining about "VPN service" not available/not responding. So I ran "services.msc" in windows run and found below:

vpn service
vpn service

When I checked, the service "Cisco AnyConnect Secure Mobility Agent" was stopped, and the "Startup type" was "Manual". So I changed "Startup type" to "Automatic", click "Start", then "OK" to save.

After this, Cisco AnyConnect Secure Mobility Client was running ok and I can connect through it to VPN.

resolved - ORA-01013: user requested cancel of current operation

ORA-01013: user requested cancel of current operation may occur in the following occasions:

  • All events blocking the shutdown do not occur within one hour, the shutdown operation aborts with the following message: ORA-01013: user requested cancel of current operation.
  • This message is also displayed if you interrupt the shutdown process, for example by pressing CTRL-C.

resolved - ext3: No journal on filesystem on disk

Today I met below error when trying to mount a disk:

[root@testvm ~]# mount /scratch
mount: wrong fs type, bad option, bad superblock on /dev/xvdb1,
missing codepage or other error
In some cases useful info is found in syslog - try
dmesg | tail or so

First I ran fsck -y /dev/xvdb1, but after it's done, the issue was still there(sometimes fsck -y /dev/xvdb1 could resolve this though). So as it suggested, I ran a dmesg | tail:

[root@testvm scratch]# dmesg | tail
Installing knfsd (copyright (C) 1996
NFSD: Using /var/lib/nfs/v4recovery as the NFSv4 state recovery directory
NFSD: starting 90-second grace period
ext3: No journal on filesystem on xvdb1
ext3: No journal on filesystem on xvdb1
ext3: No journal on filesystem on xvdb1
ext3: No journal on filesystem on xvdb1

So from here we can see that the root cause for mounting failure was "ext3: No journal on filesystem on xvdb1". I first ran "fsck -y /dev/xvdb1", and try mount again. But the issue was still there. So I tried with adding ext3 journal on that disk:

[root@testvm qgomsdc1]# tune2fs -j /dev/xvdb1
tune2fs 1.39 (29-May-2006)
Creating journal inode:

This filesystem will be automatically checked every 20 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

After this, the mount succeeded.

resolved - ORA-00020: maximum number of processes (1000) exceeded

Today I encountered ORA-12516 error when trying to access oracle database:

[root@client-doxer ~]# sqlplus tauser/

SQL*Plus: Release Production on Tue Mar 17 07:31:04 2015

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

ORA-12516: TNS:listener could not find available handler with matching protocol

Enter user-name:

Then I had a try of connecting using VIP instead of scan name, but it failed too:

[root@client-doxer ~]# sqlplus tauser/

SQL*Plus: Release Production on Tue Mar 17 07:37:22 2015

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

ORA-12516: TNS:listener could not find available handler with matching protocol

Enter user-name:

Then on the database server, I had a check of service qainfac1:

[root@rac01 crsd]# /u01/app/ status res -t|grep -A5 ora.qainf1.db
1 ONLINE ONLINE rac01 Open
2 OFFLINE OFFLINE Instance Shutdown

So one instance is running fine. I tried sqlplus connection from local server:

[oracle@rac01 ~]$ sqlplus / as sysdba

SQL*Plus: Release Production on Tue Mar 17 07:45:39 2015

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

ORA-00020: maximum number of processes (1000) exceeded

Enter user-name: ^C

That's it, "ORA-00020: maximum number of processes (1000) exceeded". Then it's going to be a question of adjusting parameter PROCESSES. As parameter PROCESSES cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance and the change takes effect in subsequent instances, so a bounce of instance is needed to activiate the new setting:

SQL> set lines 200
SQL> col NAME for a30
SQL> col VALUE for a40
------------------------------ ---------------------------------------- ----- --------- -----
processes 1500 FALSE FALSE FALSE

SQL> show parameter processes;

------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 3
gcs_server_processes integer 2
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 1000

In trace file /u01/app/oracle/diag/rdbms/qainf1/qainf12/trace/alert_qainf12.log, I can see below errors:

Unable to allocate flashback log of 51094 blocks from
current recovery area of size 214748364800 bytes.
Recovery Writer (RVWR) is stuck until more space
is available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.

Here's Fast Recovery Area info:

SQL> show parameter db_recovery_file_dest;

------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 200G

And here's ASM diskgroup info:

[oracle@rac01 ~]$ export ORACLE_SID=+ASM2
[oracle@rac01 ~]$ export ORACLE_HOME=/u01/app/
[oracle@rac01 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@rac01 ~]$ sqlplus / as sysasm

SQL*Plus: Release Production on Wed Mar 18 02:27:13 2015

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

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> set lines 200;
SQL> select name, total_mb, free_mb, total_mb-free_mb used_mb from v$asm_diskgroup;

------------------------------ ---------- ---------- ----------
DATA 4681689 533392 4148297

I had a check of restore points:

SQL> col NAME for a20
SQL> col time for a40
SQL> col SCN for 999999999999999
SQL> col STORAGE_SIZE for 999999999999999

-------------------- -------------- ---------------------------------------- --------------------- --- ------------
GRPT_BF_UPGR 14035000000000 03-MAR-15 PM 2 YES 214310000000

And I dropped the restore point to free space it's no longer needed to keep the restore point:

SQL> drop restore point GRPT_BF_UPGR;

After this, 214G space released from FRA and I can startup DB then set processes parameter to 1500(kill some processes with root by "ps -ef|grep <sid>" if sqlplus won't work even on local server):

SQL> alter system set processes=1500 scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
SQL> alter database open;
SQL> select LOG_MODE,flashback_on from v$database;
------------ ------------------

sendmail DSN: Data format error

If you met error when sending mail using sendmail(or linux mail/mailx command), then you should check /var/log/maillog for details. For example:

Mar 5 02:39:10 testhost1 sendmail[15281]: t252dAZr015281: from=root, size=78, class=0, nrcpts=1, msgid=<>, relay=root@localhost
Mar 5 02:39:10 testhost1 sendmail[15282]: t252dA8Z015282: from=<>, size=393, class=0, nrcpts=1, msgid=<>, proto=ESMTP, daemon=MTA, relay=localhost.localdomain []
Mar 5 02:39:10 testhost1 sendmail[15281]: t252dAZr015281:, ctladdr=root (0/0), delay=00:00:00, xdelay=00:00:00, mailer=relay, pri=30078, relay=[] [], dsn=2.0.0, stat=Sent (t252dA8Z015282 Message accepted for delivery)
Mar 5 02:39:10 testhost1 sendmail[15284]: t252dA8Z015282: to=<>, ctladdr=<> (0/0), delay=00:00:00, xdelay=00:00:00, mailer=esmtp, pri=120393, [], dsn=5.6.0, stat=Data format error
Mar 5 02:39:10 testhost1 sendmail[15284]: t252dA8Z015282: t252dA8Z015284: DSN: Data format error
Mar 5 02:39:10 testhost1 sendmail[15284]: t252dA8Z015284: to=<>, delay=00:00:00, xdelay=00:00:00, mailer=local, pri=31660, dsn=2.0.0, stat=Sent

From here, you can see that after relaying, the mail finally failed with DSN code 5.6.0(Delivery Status Notification extension of SMTP). So you should check the code details:

5 Permanent or Fatal error. This can be caused by a non existent email address, DNS problem, or your email was blocked by the receiving server.
X.6.0 - Other or undefined media error

X.6.0 Other or undefined media error Not given Something about the content of a message caused it to be considered undeliverable and the problem cannot be well expressed with any of the other provided detail codes.


For more info abotu DSN code, you can check or or for details.

TCP Window Scaling - values about TCP buffer size

TCP Window Scaling(TCP socket buffer size, TCP window size)

/proc/sys/net/ipv4/tcp_window_scaling #1 is to enable window scaling
/proc/sys/net/ipv4/tcp_rmem - memory reserved for TCP rcv buffers. minimum, initial and maximum buffer size
/proc/sys/net/ipv4/tcp_wmem - memory reserved for TCP send buffers
/proc/sys/net/core/rmem_max - maximum receive window
/proc/sys/net/core/wmem_max - maximum send window

The following values (which are the defaults for 2.6.17 with more than 1 GByte of memory) would be reasonable for all paths with a 4MB BDP or smaller:

echo 1 > /proc/sys/net/ipv4/tcp_moderate_rcvbuf #autotuning enabled. The receiver buffer size (and TCP window size) is dynamically updated (autotuned) for each connection. (Sender side autotuning has been present and unconditionally enabled for many years now).
echo 108544 > /proc/sys/net/core/wmem_max
echo 108544 > /proc/sys/net/core/rmem_max
echo "4096 87380 4194304" > /proc/sys/net/ipv4/tcp_rmem
echo "4096 16384 4194304" > /proc/sys/net/ipv4/tcp_wmem

Advanced TCP features

cat /proc/sys/net/ipv4/tcp_timestamps #more is here(allow more accurate RTT measurements for deriving the retransmission timeout estimator; protect against old segments from the previous incarnations of the TCP connection; allow detection of unnecessary retransmissions. But enabling it will also allow you to guess the uptime of a target system.)
cat /proc/sys/net/ipv4/tcp_sack

Here are some background knowledge:

  • The throughput of a communication is limited by two windows: the congestion window and the receive window(TCP congestion window is maintained by the sender, and TCP window size is maintained by the receiver). The former tries not to exceed the capacity of the network (congestion control) and the latter tries not to exceed the capacity of the receiver to process data (flow control). The receiver may be overwhelmed by data if for example it is very busy (such as a Web server). Each TCP segment contains the current value of the receive window. If for example a sender receives an ack which acknowledges byte 4000 and specifies a receive window of 10000 (bytes), the sender will not send packets after byte 14000, even if the congestion window allows it.
  • TCP uses what is called the "congestion window", or CWND, to determine how many packets can be sent at one time. The larger the congestion window size, the higher the throughput. The TCP "slow start" and "congestion avoidance" algorithms determine the size of the congestion window. The maximum congestion window is related to the amount of buffer space that the kernel allocates for each socket. For each socket, there is a default value for the buffer size, which can be changed by the program using a system library call just before opening the socket. There is also a kernel enforced maximum buffer size. The buffer size can be adjusted for both the send and receive ends of the socket.
  • To get maximal throughput it is critical to use optimal TCP send and receive socket buffer sizes for the link you are using. If the buffers are too small, the TCP congestion window will never fully open up. If the receiver buffers are too large, TCP flow control breaks and the sender can overrun the receiver, which will cause the TCP window to shut down. This is likely to happen if the sending host is faster than the receiving host. Overly large windows on the sending side is not usually a problem as long as you have excess memory; note that every TCP socket has the potential to request this amount of memory even for short connections, making it easy to exhaust system resources.
  • More about TCP Buffer Sizing is here.
  • More about /proc/sys/net/ipv4/* Variables is here.