Wednesday, December 2, 2020

root sqlplus / as sysdba ORA-12546: TNS:permission denied nt main err code: 516

One of the causes of such error I would like to describe below. 

It might sound crazy, but there is software of some vendors, and such software requires logon to an Oracle instance as sysdba from root OS account. 

I stuck into the error :


ERROR:
ORA-12546: TNS:permission denied

Addind root to group dba didn't help to resolve the ORA-12546, but adding root to oinstall did :). Actually root must be the member of group dba to avoid the

ERROR:
ORA-01017: invalid username/password; logon denied

You haven't to add root to these groups, you can simply use newgrp command (newgrp [-] oinstall , then newgrp [-] dba). After such 'manipulations' you will be able to logon as sysdba.

Good Luck !

Monday, November 30, 2020

Subtraction DATE from DATE in Oracle database - implementation feature or what ?

Recently I used lag function to find interval of creating new objects of the same type in the database. Particularly I was interested by the time interval between such creations. The original query was

SELECT
    file#
    ,creation_time
    , (creation_time - lag (creation_time) over (order by creation_time)) as interval
FROM
    v$datafile a
    , v$tablespace b
WHERE
    a.ts# = b.ts#
    AND b.name = 'XXX'
ORDER BY
    2 desc
/

It turned out that it is possible to use interval expression during subtraction :

SELECT
    file#
    ,creation_time
    , (creation_time - lag (creation_time) over (order by creation_time)) day (3) to second as interval
FROM
    v$datafile a
    , v$tablespace b
WHERE
    a.ts# = b.ts#
    AND b.name = 'BARS_739_ADD'
ORDER BY
    2 desc
/

The result is more presentable than in previous query. 

The Oracle SQL Language documentation says than the NUMBER datatype will be generated as result of "DATE - DATE" operation. With help dump function, I saw some mysterious (internal ?) datatype 14, not 2 (number datatype). You could also use such "feature" when subtracting DATE datatype values and if trunc or round functions are not of your suitable option.

Good Luck !

Monday, November 23, 2020

How to allow row to break across lines in Oracle iLOM host console

When you used to access to host console via iLOM, you probably noticed that long command lines (over 80 symbols) do not break to new continuation line, the new characters just go over existed, already typed characters. It looks like the terminal string

[root@xxx-xxxx-xxx ~]# ddddddddddddddddddddddddddddddddddddddddddddddddddddd

transforms into

aaaat@xxx-xxx-xxx ~]# dddddddddddddddddddddddddddddddddddddddddddddddddddddaaa 

To resolve the issue, set tty parameters corresponding to your terminal, for example :

[root@xxx-xxxx-xxx ~]# stty rows 45 columns 158

You should also reopen iLOM console for proper terminal functioning.


Good Luck !

Saturday, November 21, 2020

Some features of implementation when using TNSNAMES connection descriptors

 1. You can double parameters in row, Oracle will you only last value. For example, when you use

             (ADDRESS=
                (PROTOCOL=tcp)
                (HOST=localhost)
                (port=1520)
                (PORT=1521)
              )

, the port 1521 which is the last in the group of values of the same group (address), and that one will be used in the connection (1520 will not be tried at all). This behavior is expected though, remember several addresses in the address_list group, but in case of address_list the client tries to establish the connection with every address until succeed, and in our case the client simply goes via the last parameter in the list.

2. You can use some abstract (not existed and not considered by oracle) parameters and values for debugging or for other purposes, for example,

            (CONNECT_DATA=
                (service_name = pdb1.db-01.site)
                (instance_name = cdb121)
                (instance_name = cdb_manual)
                (aaa = bbb)
            )

Here abstract parameter aaa is used. The listener entry in this case could be like this :

22-NOV-2020 09:22:31 * (CONNECT_DATA=(service_name=pdb1.db-01.site)(instance_name=cdb121)(instance_name=cdb_manual)(global_name=cdb121aaa)(aaa=bbb)(CID=(PROGRAM=sqlplus@db-01)(HOST=db-01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=21799)) * establish * pdb1.db-01.site * 0

As we can see sqlplus established connection with instance_name=cdb_manual (the last of the group of instance_name parameters inside of tns descriptor, you can confirm it with help of select sys_context from dual statement)

3. Will be continued...

Good Luck !

Wednesday, October 21, 2020

dgmgrl validate database spfile and ORA-12154

I've got this error during execution of "validate database verbose db_name spfile ;" on Oracle 12.2. 

The error encountered when property DGConnectIdentifier for the database was set to easyconnect string with shared or dedicated connection type (like 'hostname/service_name:dedicated') or expanded tns entry was used (like '(description=...)'). 

 DataGuard Broker build the connection string with additional useless fragments of database dispatchers setting making the connection string invalid :

Unable to connect to database using hostname/service_name:dedicatedf)(connections=100)(sessions=100)

The error had gone when property DGConnectIdentifier was set to easyconnect string without ":dedicated" piece or to the name of the tns entry (from tnsnames.ora or from ldap).

Update : the issue can occur when validated database also has got dispatchers parameter set to non-default value.

Good Luck !

Friday, October 9, 2020

Bash : empty variable value outside of while loop using pipelines for input

Instead of the following which returns empty value of a :

$> a=0 ; find /dev/shm/ -uid 54321 -ls | awk '{print $7}' | while read line ; do let a+=${line}; done ; echo "a="${a}

try to use bash's process substitution like this :

a=0 ; while read line ; do let a+=$line ; done < <(find /dev/shm/ -uid 54321 -ls | awk '{print $7}') ; echo "a="$a

This will prevent using newly created shells for pipelines, using the current shell for all operands. Good Luck !

Monday, August 24, 2020

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.