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 !