How to display a SYSTIMESTAMP data type as a TIMESTAMP WITH TZ

To display a value of datatype SYSTIMESTAMP as TIMESTAMP WITH TIME ZONE, use TO_CHAR first, then convert to TIMESTAMP WITH TIMEZONE.

The last expression adds one hour, which can be done after the conversion between the datatypes is complete:

select  TO_CHAR(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
        TO_TIMESTAMP_TZ(to_char(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
        TO_TIMESTAMP_TZ(to_char(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')+1/24
from dual;

systimestamp	TIMESTAMP_TZ	TIMESTAMP_TZ + 1hr
2017/02/01 10:36:28.158921 +01:00	01/02/2017 10:36:28,158921000 +01:00	01.02.2017 11:36:28

How to use gzip and tar together

To compress and tar a directory in one command, use the following syntax:

# tar -czvf mytarfile.tar.gz mydir

which will tar and compress the directory "mydir" with its contents.

To extract and untar a .tar.gz file in one command, use the following syntax:

# tar -zxvf mytarfile.tar.gz

How to confirm that the parameter INBOUND_CONNECT_TIMEOUT_listener_name is working

From the documentation:

Purpuose: To specify the time, in seconds, for the client to complete its connect request to the listener after the network connection had been established.

and

If the listener does not receive the client request in the time specified, then it terminates the connection. In addition, the listener logs the IP address of the client and an ORA-12525:TNS: listener has not received client's request in time allowed error message to the listener.log file.

My listener.ora file:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

inbound_connect_timeout_listener=3

Verify that the parameter is set:

LSNRCTL> show inbound_connect_timeout
LISTENER parameter "inbound_connect_timeout" set to 3
LSNRCTL>

Verify that logging is set and the location of the logfile:

LSNRCTL> show log_file
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain.com)(PORT=1521)))
LISTENER parameter "log_file" set to /u01/oracle/diag/tnslsnr/myserver/listener/alert/log.xml
The command completed successfully
LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain.com)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully

From a remote client, execute telnet against the listener port, in my case the default port 1521.

Use the "time" command in Linux to see how long the command executes:

[vk@myclient]$ time telnet myserver.mydomain.com 1521
Trying 192.168.0.122...
Connected to myserver.mydomain.com
Escape character is '^]'.
Connection closed by foreign host.

real    0m3.010s
user    0m0.001s
sys     0m0.001s

From the listener's log.xml file, we can see the message clearly:

<msg time='2017-01-23T11:09:12.118+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='myserver.mydomain.com'
 host_addr='192.168.0.122'>
 <txt>23-JAN-2017 11:09:12 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.25)(PORT=49264)) * establish * <unknown sid> * 12525
 </txt>
</msg>
<msg time='2017-01-23T11:09:12.119+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='myserver.mydomain.com'
 host_addr='192.168.0.122'>
 <txt>TNS-12525: TNS:listener has not received client's request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred
 </txt>
</msg>

From Oracle 10g and onwards, thet the default setting for INBOUND_CONNECT_TIMEOUT in listener.ora is 60 seconds
If set to zero, you disable the connect timeout functionality altogether. In such a situation, the telnet session above would simply "hang" and wait for its connection request to be completed.

Oracle recommends setting the INBOUND_CONNECTION_TIMEOUT in listener.ora in conjunction with INBOUND_CONNECT_TIMEOUT in sqlnet.ora.
Set the sqlnet.ora value slightly higher than the listener.ora value.

Sources:

http://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF210
http://docs.oracle.com/database/121/NETRF/listener.htm#NETRF312

How to solve ORA-02391 exceeded simultaneous SESSIONS_PER_USER limit

The error definition:

//  *Cause: An attempt was made to exceed the maximum number of
//          concurrent sessions allowed by the SESSION_PER_USER clause
//          of the user profile.
//  *Action: End one or more concurrent sessions or ask the database
//           administrator to increase the SESSION_PER_USER limit of
//           the user profile.

You need to adjust the SESSIONS_PER_USER of the profile the user is assigned to.

alter profile general_users LIMIT SESSIONS_PER_USER <num> | default | unlimited;

num = a fixed number of sessions that is the upper bound for the number of concurrent session the user can create
Default = the user is subject to the limits on those resources defined by the DEFAULT profile
unlimited = the user assigned this profile can use an unlimited amount of this resource

How to find users in the database with default passwords

select d.username,account_status
from dba_users_with_defpwd d inner join dba_users u
on d.username = u.username
where u.account_status = 'OPEN';
exit

Output in my case:

USERNAME                                 ACCOUNT_STATUS
---------------------------------------- --------------------
CTXSYS                                   OPEN
MGDSYS                                   OPEN

These accounts should have their password changed for security reasons.

List temporary tablepaces and their tempfiles

select t.file#,ts#,status,t.name "temp file name",n.name "tablespace name", n.bigfile,t.bytes/1024/1024 "Size MB"
from v$tempfile t join v$tablespace n
using(ts#);

FILE#	TS#	STATUS	temp file name	tablespace name	BIGFILE	Size MB
1	3	ONLINE	/u02/oradata/proddb01/temp01.dbf	TEMP	YES	256
2	66	ONLINE	/u02/oradata/proddb01/user_temp.dbf	USER_TEMP	YES	256
3	70	ONLINE	/u02/oradata/proddb01/temp_d1u_01.dbf	BATCH_TEMP	YES	256

How to manually set the date and time on an AIX server

Old, incorrect date:

[root@myserver:/] date
Wed Jul 12 10:11:36 CEST 2017

Change it to the current date:

[root@myserver:/] date 010510152017
Thu Jan  5 10:15:31 CET 2017

Where

01=month
05=day
1015=time and minute
2017=year


Let's check it, immediately after we have set it:

[root@myserver:/] date
Thu Jan  5 10:16:27 CET 2017

The ntpd deamon has already adjusted the time slightly.
Note that ntpd will only adjust the server time if the local time of the server is within 1000 sec (16 minutes) of the current time. From the xntpd man pages:


Note: When operating in a client mode, the xntpd daemon will exit with an error if no configured servers are within 1000 seconds of local system time. Use the date or ntpdate command to set the time of a bad skewed system before starting xntpd.