SSH escape sequences and disconnect from session

The default character of invoking ssh escape sequence is '~' (from new line of course). Twice typing of such character send the escape character itself. In situations where several sequential connections to terminal (or to console, like Oracle Solaris Zone console via zlogin -C) are made, you should know one 'trick' to disconnect only from last connection made. 

Let me explain below. Let's start with session A - a session of your local machine. Here B - proxy vpn machine, C - Solaris server, D - newly installed Oracle Solaris Zone, not configured yet. 

A -- ssh_via_vpn_to_B --> B -- ssh_to_server_C --> C -- login_to_zone_console --> D

When you type exit ssh sequence '~.' you immediately close your first ssh session from A to B.In order to close only zlogin session and stay in session on server C, you should type '~~~.' Doing it you transmit escape character to the appropriate deeper session in your 'connection stack'. Take it in your mind. 

P.S. this doesn't work when ssh jumping or configured ssh proxy connection is used.

remmina throws "cannot connect to the 127.0.0.1 rdp server" over ssh tunnel

Hello !

    Remmina is one of my favorite desktop rdp/vnc client. Recently it's stopped to connect to MS Windows 2018 terminal server over ssh. The real cause was issued by xfreerdp which is called by Remmina. The error from freerdp was as :

[10:54:28:752] [8718:8719] [INFO][com.freerdp.core] - ERRINFO_LICENSE_HWID_DOESNT_MATCH_LICENSE (0x00000104):The Client Access License ([MS-RDPELE] section 1.1) stored by the client has been modified.
[10:54:28:752] [8718:8719] [ERROR][com.freerdp.core] - rdp_set_error_info:freerdp_set_last_error_ex  ERRINFO_LICENSE_HWID_DOESNT_MATCH_LICENSE [0x00010104]

Running xfreerdp over strace lead to the file in the directory ~/.config/freerdp/licenses. I've renamed it, and after that the problem has gone (new license file was created).

Good Luck !!!

Oracle Net Configuration Assistant (netca) command line interface (CLI)

Everything is clear about using netca in graphical mode. Understandable questions and "transparent" interface do they job perfectly, although for comprehensive Oracle Net setup the Net Manager is more preferable (imho). But netca CLI has some specificities. 

There are several options of using netca CLI.

To configure listener and naming services via netca response file :

netca -silent -responsefile

The example of such file can be as following (the empty and commented lines was removed) :

[GENERAL]
RESPONSEFILE_VERSION="12.1"
CREATE_TYPE="CUSTOM"
LOG_FILE=""/tmp/netca.log""
[oracle.net.ca]
INSTALLED_COMPONENTS={"javavm","net8","cman","aso","client","server"}
INSTALL_TYPE=""custom""
LISTENER_NUMBER=0
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"EZCONNECT","TNSNAMES","ONAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"ASM"}
NSN_SERVICE={"+ASM"}
NSN_PROTOCOLS={"TCP;db-01;1521"}

In the above some fields has 2 double quotes in a row in their values. It's required, else run-time error can occur.

The following lines are required :
[GENERAL]
RESPONSEFILE_VERSION="12.1"
CREATE_TYPE="CUSTOM"
[oracle.net.ca]

The listener only created when one of installed components is "server".

If you set INSTALL_TYPE=""minimal"" or ""typical"", then sqlnet.ora with NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
is created and default listener named LISTENER on 1521 port also is created with appropriate entries in listener.ora file (if it was already there, the file is left as is).

Creation of TNS entries is possible only when INSTALL_TYPE=""custom"".

You can override entries from response file or add some more parameters using command line options. Thus, you can change install_type on the way, listener_name and listener port (only in custom install_type) etc.

netca -h
Usage: netca [-silent] { }

Perform network configuration by specifying the following arguments:
    [-silent]
        -responsefile ]
        [-local {Perform configuration on only local node}]
    -instype
        [-listener ]
        [-lisport ]
        [-lps ]
        [-lpe ]
        [-netnum ]
        [-nostartlsnr {Do not start listener}]
    [-crsupgrade {Upgrade default listener from lower version database home to Grid Infrastructure home (only for RAC)}]
    [-inscomp ]
    [-insprtcl ]
    [-orahome ]
    [-orahnam ]
    [-log ]
    [-h|-help {Print usage}]

As we can see there are no options for tnsnames.ora or sqlnet.ora entries. When install type is custom, tns entries are taken from response file.

You can add tns entries and listeners by running netca with another content of response file or command line options. The entries are added into listener.ora and tnsnames.ora.

Naming methods are updating only in custom install type from the response file.

P.S. When I tried default value for NSN_NAMES={"EXTPROC_CONNECTION_DATA"} in the response file, tns entries were not created. If this name is changed in one char only, tnsnames.ora is populated :)

P.P.S. netca creates appropriate listener configuration in grid infrastructure if it's running on the server.

Examples :

1. Does network service name settings according to response file parameters and add new listener bbb to those already registere, try to set listening port to first free between 1500 and 1505, without starting listener bbb after creating it.

$ netca -silent -responsefile /install/oracle/response/netca5.rsp -instype custom -listener bbb -lps 1500 -lpe 1505 -nostartlsnr

2. Create default listener LISTENER (-instype typical) on default port and start it not accounting it's existence.

$ netca -silent -responsefile /install/oracle/response/netca5.rsp -instype typical -lps 1500 -lpe 1505 -nostartlsnr

How to print and type non-english locale symbols over ssh on Solaris ?

In short, it depends on the locale in both cases. This small note is not about how to configure the locale and terminal settings. There are too many dependent factors : supporting fonts on the source of connection, locale settings on source of connection, settings of the terminal, using unicode etc. Shortly:

1. To display locale symbols on the terminal, you have to set either LANG, or LC_CTYPE, or LC_ALL session variables, corresponding to your locale. You may be also required to use settings of individual program (vi(m)), implementing interim iconv operations etc.

2. To enter/type non-english symbols over ssh, you have to send locale variables from the source to the destination, using setting SendEnv in ssh client config file (SendEnv LC_* LANG for example). Without this I was unable to input non-english characters inside ssh session to Oracle Solaris (sparc) . On Linux systems all worked without it.

3. There could be other methods (echo -e \uHHHH etc.), it highly depends on your needs.

4. Do not forget to enable 'stty defeucw' setting for the terminal session. Without it sqlplus, for example, treat one non-english symbol as number of bytes (2 for russian symbols) during deleting it via backspace.

Good Luck !

How to get current or custom time in different time zone in Oracle DB ?

To get current time in another time zone use the following :

SQL> select systimestamp at time zone 'ZONE_NAME' from dual ;

ZONE_NAME can be gotten from v$timezone_names view.

To get custom time of different timezone in local time zone do the following :

SQL> select to_char ( to_timestamp_tz ('2021-07-22 02:00 pm us/eastern','yyyy-mm-dd hh:mi pm tzr') at time zone 'europe/minsk', 'dd.mm.yyyy hh24:mi:ss') from dual

Thus, here we can see the local time (time in Minsk in my case) when there is 02:00 PM 22.07.2021 at US Eastern time. 

Here also the other samples :

select to_char (from_tz (to_timestamp ('2021-09-08 07:00am','yyyy-mm-dd hh:miam')  ,'America/Los_Angeles') at time zone 'Europe/Minsk', 'dd.mm.yyyy hh24:mi:ss') as local_time
from dual
/

select to_char (from_tz (cast (to_date ('2021-09-08 07:00am','yyyy-mm-dd hh:miam') as timestamp), 'america/los_angeles') at time zone 'europe/minsk', 'dd.mm.yyyy hh24:mi:ss') as local_time
from dual
/

Good Luck !

How does Oracle database represent numbers ?

Let consider it on small examples : we take natural number 412 and dump it like the following :


SQL> l
  1  select dump(412,16) dump_result from dual
  2  union all
  3* select dump(-412,16) dump_result from dual
SQL>

DUMP_RESULT
------------------------
Typ=2 Len=3: c2,5,d
Typ=2 Len=4: 3d,61,59,66


Oracle represents its numbers as the form 0.(base_100_mantissa)*(100**(base_100_exponent)).

base_100 here means numbers (unique symbols) from 00 to 99.

In both cases first byte contains the information about sign and exponent, the next are the value of mantissa.

The highest bit of the first byte is sign - 1 for positive and 0 for negative numbers.

The exponent (last 7 bits of the first byte) is in the form (base_100_exponent+64) for positive and (255-((base_100_exponent)+64),highest bit is ignored) for negative numbers.

In case of negative numbers the last byte 0x66 (102) is added. Zero is stored as 1 byte of 0x80 (128).
Positive values of bytes of mantissa is being written as (value+1), negative as (101-value).

1. 412

DUMP_RESULT
------------------------
Typ=2 Len=3: c2,5,d

1.1. c2 is 0b11000010

The highest bit is 1 - number is positive.
The exponent is 0b1000010-0b1000000 (64)=0b10 (2)


1.2. Bytes of base_100_mantissa - 0x5 and 0xd. 0x5 is 4+1, 0xd is 12+1 (i.e. similar to our number 412). But we have base_100_mantissa, so 5 transforms to 05 for Oracle.

So, the number 412 is presented as 0.0513*(100**2)

2. -412

DUMP_RESULT
------------------------
Typ=2 Len=4: 3d,61,59,66

Last 0x66 - another evidence of negative number.

2.1. 3d is 0b111101 (61)

The highest bit is 0 - number is negative.
The exponent=255-61-64=130 --> 0b10000010. The highest bit is ignored, so the exponent is 0b10 (2).

2.2. Bytes of base_100_mantissa - 0x61 (97) and 0x59 (89). 101-4=97, 101-12=89. But we have base_100_mantissa, so 4 transforms to 04 for Oracle.

So, the number -412 is presented as -0.0412*(100**2)

We can see Oracle added some tricks to present its numbers. I guess it can be for comparison, sorting, ordering etc.

Let's check our "transformation" :

SQL> select utl_raw.cast_to_number (hextoraw('c2050d')) "check" from dual
/

  check
----------
       412

SQL> select utl_raw.cast_to_number (hextoraw('3d615966')) "check" from dual
/

     check
----------
      -412


Good Luck !

Oracle RMAN - catalog backup from the sbt_tape device

Sometimes you need simply re-catalog (or something else) the backup piece of RMAN backup made to the tape device. The documentation doesn't direct you to the options mention here, but it works. Try it !

The 'catalog command of Oracle RMAN have option 'device'. Look :

RMAN> catalog ;                                                                                                                                                                             

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "archivelog, backuppiece, backup, controlfilecopy, datafilecopy, db_recovery_file_dest, device, recovery, start"
RMAN-01007: at line 1 column 9 file: standard input

When you're trying to catalog a backuppiece from the tape, the error appears :

RMAN> catalog device type sbt_tape backuppiece 'arc_db_68u7hhgd_1_1' ;

RMAN-06470: DEVICE TYPE is supported only when automatic channels are used

Configure automatic channel with parameters you're using and you will get a success :

RMAN> configure channel device type sbt_tape parms 'SBT_LIBRARY=/u01/app/oracle/hpe/HPE-Catalyst-RMAN-Plugin/bin/libisvsupport_rman.so ENV=(CONFIG_FILE=/u01/app/oracle/hpe/HPE-Catalyst-RMAN-Plugin/config/plugin_db.conf)' ;

RMAN> catalog device type sbt_tape backuppiece 'full_db_sb_9cu82od1_1_1' ;                                                                                                              
                                                                                                                                                                                            
allocated channel: ORA_SBT_TAPE_1                                                                                                                                                           
channel ORA_SBT_TAPE_1: SID=560 device type=SBT_TAPE                                                                                                                                        
channel ORA_SBT_TAPE_1: HPE StoreOnce Catalyst Plugin for RMAN                                                                                                                              
cataloged backup piece                                                                                                                                                                      
backup piece handle=full_db_sb_9cu82od1_1_1 RECID=3539 STAMP=1016455268

You can query the result and continue to work with backup as you need.

Good Luck !