NL-00305: the specified path name does not exist

I've got this strange error when static listener configuration was used. After modifying listener.ora the listener was unable to start. The static configuration was as the following :

 sid_list_listener =
 (sid_list=
   (sid_desc=
     (global_dbname = cdbrest3)
     (oracle_home = /u01/app/oracle/product/19/db_1913)
   )
 )

The issue was resolved after adding SID_NAME into configuration :

 sid_list_listener =
 (sid_list=
   (sid_desc=
     (sid_name = cdbrest3)
     (global_dbname = cdbrest3)
     (oracle_home = /u01/app/oracle/product/19/db_1913)
   )
 )


As result the listener was able for being reloaded or restarted.

Good Luck !

How to resolve issues with half network links online in Oracle SuperCluster M8 using Oracle Quad 10Gb/dual 40Gb Ethernet Adapter

Recently I had an issue with Oracle Quad 10Gb/dual 40Gb Ethernet Adapter - the operating system shown only half of its connected links as up (online), another half was shown as down. Actually another half of links (which was down from Solaris OS) was connected and links also had green constant light, both from the switch and adapter sides.  

root@hostname:~# dladm show-phys                                                           
LINK            MEDIA         STATE      SPEED  DUPLEX    DEVICE
net0            Ethernet      down       0      unknown   i40e0
net1            Ethernet      down       0      unknown   i40e1
net2            Ethernet      up         10000  full      i40e2
net3            Ethernet      down       0      unknown   i40e3
net4            Ethernet      down       0      unknown   i40e4
net5            Ethernet      down       0      unknown   i40e5
net6            Ethernet      up         10000  full      i40e6
net7            Ethernet      down       0      unknown   i40e7
net8            Ethernet      up         1000   full      vnet0
net9            Ethernet      up         1000   full      vnet1
net10           Infiniband    up         32000  unknown   ibp0
net11           Infiniband    up         32000  unknown   ibp1
net14           Infiniband    up         32000  unknown   ibp2
net15           Infiniband    up         32000  unknown   ibp3

Here we've got the LDom with two CMIOU. Each CMIOU provides 4 i40e devices, because each ethernet adapter was configured to separate 4 (4x10Gb=40Gb) links. Actually net0, net2, net4 and net6 were physically connected and all of those must be in up state, but we can see that net0 and net4 were down. So I was recommended by support to downgrade the firmware of those adapters, power cycle the server and upgrade aftermath (plus power cycling again). 

Every operation with firmware requires power cycle of PDom. Take it into account when planning your work.

The sequence of steps was as :

1. Shutdown any non-primary domains first (use ldm list to identify).

# init 0

2. Save LDom configuration if needed, boot info factory-default SP config

# ldm add-config backup
# ldm set-config factory-default

# ldm list-config

3. Shutdown primary domain.

# init 0

4. Inside ILOM power off the host (or the platform, it depends on your hardware) using stop for HOST (or SYS) target.

dd

5. Configure boot from iso over the network (ILOM) :

-> set /Servers/PDomains/PDomain_0/SP/services/kvms/host_storage_device/ mode=disabled
-> set /Servers/PDomains/PDomain_0/SP/services/kvms/host_storage_device/remote/ server_URI=nfs://10.xx.xx.xxx:/private/boot.iso
-> set /Servers/PDomains/PDomain_0/SP/services/kvms/host_storage_device/ mode=remote
-> set /SP/cli timeout=0
-> show /Servers/PDomains/PDomain_0/SP/services/kvms/host_storage_device/

Carefully modify and set the parameters, do not insert extra spaces, dashes, semicolons etc. Also take into account the necessity to create network available NFS v3 server. 

Another option to configure iso file to boot from is to use the BUI (not in this post).

6. Start the HOST target

7. In OpenBoot prompt, check the availability of rcdrom device for boot and boot from it :

{0} ok devalias                                                                              
fallback-miniroot        /pci@304/pci@1/usb@0/storage@2/disk@0                               
rcdrom                   /pci@304/pci@1/usb@0/storage@2/disk@0                               
virtual-console          /virtual-devices/console@1                                          
name                     aliases                                                             
{0} ok boot rcdrom                                                                           
Boot device: /pci@304/pci@1/usb@0/storage@2/disk@0  File and args: 

You might receive new faulty events like inability to use interconnect from host to SP etc. It's explainable, just take them into account.

8. Logon into the system. I used Oracle VTS Image (MOS patch 26091982). Username and password are jack/jack. For root the password is solaris.

9. Create network link for connecting to NFS to download firmware (if it's not in iso already). Use network and link from working LDom.

# ipadm create-addr -T static -a local=10.xx.xx.xxx/24 net1

10. Mount NFS share :

# mount -F nfs -o rw,bg,hard,nointr,rsize=1048576,wsize=1048576,vers=3,proto=tcp,forcedirectio,nocto 10.xx.xx.xxx:/export/test-share /mnt/tst

11. Perform other manipulations which you're needed (downgrade/upgrade firmware, reconfigure equipment etc.)

12. Revert back LDom configuration via ILOM, and disable booting from remote iso, for example :

-> set /Servers/PDomains/PDomain_0/host/bootmode config=backup

-> set /Servers/PDomains/PDomain_0/sp/services/kvms/host_storage_device/ mode=miniroot

13. Shutdown OS and stop HOST target. Wait till status_detail of HOST target will be 'Host is off'. Then start the HOST target

14. The operating system should be loaded. Check the status of the links :

# dladm show-phys
LINK            MEDIA         STATE      SPEED  DUPLEX    DEVICE
net0            Ethernet      up         10000  full      i40e0
net1            Ethernet      down       0      unknown   i40e1
net2            Ethernet      up         10000  full      i40e2
net3            Ethernet      down       0      unknown   i40e3
net4            Ethernet      up         10000  full      i40e4
net5            Ethernet      down       0      unknown   i40e5
net6            Ethernet      up         10000  full      i40e6
net7            Ethernet      down       0      unknown   i40e7
net8            Ethernet      up         1000   full      vnet0
net9            Ethernet      up         1000   full      vnet1
net10           Infiniband    up         32000  unknown   ibp0
net11           Infiniband    up         32000  unknown   ibp1
net14           Infiniband    up         32000  unknown   ibp2
net15           Infiniband    up         32000  unknown   ibp3

 

Looks good :) Repeat these steps for upgrading firmware (if needed). 

That's it :) Good Luck !

Recover Oracle 12.1 physical standby database until time - another "method"

For newer versions of Oracle database (more then 12.1) it's easy to use 'alter database recover ... until time' to rollup physical standby database a little bit. For 12.1, the standby database manual recover using the same command was possible using only 'until change' clause.

However, using 'recover' command of sqlplus, it's possible to rollup the standby database until time, take a look :

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 20 14:14:34 2021

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

> recover automatic standby database until time '2021-12-19:08:00:00'
Media recovery complete.

Good Luck !

Timeout parameters from Oracle Net - extraction from docs

sqlnet.ora	tnsnames.ora	Def. value	server side	Description
SQLNET.EXPIRE_TIMEOUT			YES	TCP keepalive setting (in minutes)
SQLNET.INBOUND_CONNECT_TIMEOUT		60 s	YES	To specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information. INBOUND_CONNECT_TIMEOUT_lsnr_name  from listener.ora should be less than parameter from sqlnet.ora
SQLNET.OUTBOUND_CONNECT_TIMEOUT	CONNECT_TIMEOUT (overrides parameter SQLNET.OUTBOUND_CONNECT_TIMEOUT from sqlnet.ora)			Superset of the TCP connect timeout interval. Without this parameter, a client connection request to the database server may block for the default TCP connect timeout duration (60 seconds) when the database server host system is unreachable.
TCP.CONNECT_TIMEOUT	TRANSPORT_CONNECT_TIMEOUT (overrides TCP.CONNECT_TIMEOUT from sqlnet.ora)	60 s		To specify the time, in seconds, for a client (per address) to establish a TCP connection
SQLNET.SEND_TIMEOUT				Specify the time, in seconds, for a database server to complete a send operation to clients, or for clients to complete a send operation after connection establishment.
SQLNET.RECV_TIMEOUT				Specify the time, in seconds, for a database server to wait for client data after connection establishment. A client must send some data within the specified time interval.

There are several parameter In the upper table which defines the behavior of server (client) processes  regarding when to cut off a client connection during establishing link between client and server processes. 

Generally the connection establishment goes through the following 3 phases (when TCP protocol is used) and time of connection establishment is consist of sum of times : initial connection establishment using TCP transport, time spent on authentication operations and time spent on establishing the connection with requested service from database.

It worse to notice that these parameters and dependencies between them works when a real Oracle client is trying to establish the connection, not just, let's say, a tnsping utility, which is trying to reach out the listener on the other side of connection (and SQLNET.OUTBOUND_CONNECT_TIMEOUT will not be relevant then).

In so called transport phase data are sent over the network using TCP transport. Here TCP.CONNECT_TIMEOUT (TRANSPORT_CONNECT_TIMEOUT) parameters define the timeout of connecting to every trying address from connection description. If not set then 60 seconds its default value. You may get the error TNS-12543 TNS:destination host unreachable sooner then 60 seconds, when network and routing configuration allows to figure out (using broadcast or multicast packages) that requested address is not available in existing network. You get an answer TNS-12535: TNS:operation timed out after 60 seconds when, for example, there is no allowed direct network access to requested listener port and broadcast packages are forbidden between networks. 

After that the authentication services are entering into the game and parameters 
SQLNET.OUTBOUND_CONNECT_TIMEOUT (CONNECT_TIMEOUT from tnsnames.ora) on the client side and
SQLNET.INBOUND_CONNECT_TIMEOUT on the server side define when to close the connection with client. INBOUND_CONNECT_TIMEOUT defines the time during which the client must provide all the information necessary for authentication and it's solely server parameter.
OUTBOUND_CONNECT_TIMEOUT is the client parameter which specifies the time in seconds during which the client must establish the connection with database.

Next, after successful connection establishing, SQLNET.SEND_TIMEOUT and SQLNET.RECV_TIMEOUT are taking into account if they're set. They oblige the server or client process to send data during specified time (in seconds). If they're not, the connection is disconnected. Oracle do not recommend to set SQLNET.RECV_TIMEOUT on the server side.

As regarding the connection failover, it's another story :)

Good Luck !

(ur=a) meaning in the connection description

Hi, in this small so-called post I would like to thank Christian Gohmann from the Oracle Community for the answer :

"

UR=A stands for "User restricted = accepted". This means, that user are still able to connect to a database in restricted mode. Without this setting you will encounter the following error.

ORA-12526: TNS:listener: all appropriate instances are in restricted mode

Of course your user needs the RESTRICTED SESSION privilege.

"

Thank you Ghristian for saving my time !

ORA-01017: invalid username/password; logon denied - another silly case

It took some time to resolve it, although the cause was actually simple and silly. It occurred only on 12.1 CDB. 

Suddenly I was unable to logon as a common user locally into the root container without specifying the password in the command line. It was a test system, on production it worked well though. The connection over the network was OK : 

> sqlplus c##ddi

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 13 17:42:16 2021

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

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


Enter user-name: c##ddi
Enter password:  
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C
> sqlplus c##ddi@\"ip_address/cdbm02_dev\"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 13 17:41:28 2021

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

Enter password:
Last Successful login time: Mon Dec 13 2021 17:22:17 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

C##DDI@ip_address/cdbm02_dev@CDB$ROOT hostname 13.12.21 17:41:31 > Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
>

The solution was to add a slash (/) after an username (:-D) :

> sqlplus c##ddi/

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 13 17:44:30 2021

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

Enter password:  
Last Successful login time: Mon Dec 13 2021 17:41:30 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

C##DDI@cdbm02_dev@CDB$ROOT hostname 13.12.21 17:44:37 > Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

But the real cause of this was operating system PATH, which led to the place of  installed Oracle Instant Client, version 18.3. Starting sqlplus from ORACLE_HOME resolved the issue :

> $ORACLE_HOME/bin/sqlplus c##ddi      

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 13 17:48:31 2021

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

Enter password:  
Last Successful login time: Mon Dec 13 2021 17:44:37 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

C##DDI@cdbm02_dev@CDB$ROOT hostname 13.12.21 17:48:37 >

Good Luck !

 

PERCENT_SPACE_RECLAIMABLE is 0 for ARCHIVED LOG in v$flash_recovery_area_usage

Recently, after creating new physical standby from duplicated database I found that PERCENT_SPACE_RECLAIMABLE on standby is always 0, while on primary it is equal to PERCENT_SPACE_USED. Archivelog deletion policy was the same on both databases : 

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

So in fresh, newly created physical standby I would be in trouble when recovery area free space will come to the end.

The solution was to open that standby database in 'read only' mode, after that PERCENT_SPACE_RECLAIMABLE had been refreshed to right value. After bouncing the standby into MOUNT state and after running it in normal mode  the free space in the flash recovery area was shown correctly.

Good Luck !