Month: September 2017

How to Copy ASM Password File From Primary to Standby

It is a prerequisite for both primary and secondary databases have same copy of password file, and stored in ASM diskgroup preferred

From 12.1.0.2 on, since primary database password file is stored in ASM, so it needs to be manually copied onto standby database ASM for dataguard configuration. Otherwise the below errors will occur:

*** 2017-09-06 14:34:29.216
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'
ORA-01017: invalid username/password; logon denied
OCI_DBVER attribute value retrieval failed error=1017

*** 2017-09-06 14:34:30.323
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'
*** 2017-09-06 14:34:30.323737 4929 krsh.c
Error 1017 received logging on to the standby

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

How Long a Session Has been Idle or Inactive In Oracle Database ?

SQL to find how long a session has been ACTIVE or IDLE, very handy.

SYMPTOMS

There are up to one thousand sessions connected to Oracle Database from a Weblogic connection pool. User wanted to know how long those sessions have been idle ( INACTIVE), so the Weblogic connection pool can be reviewed and reconfigured properly.

SOLUTION

The below query can be run to meet this requirement:

SQL>select USERNAME,
           MACHINE,
           STATUS,
           LOGON_TIME,
           LAST_CALL_ET INACTIVE_SECONDS 
    from  gv$session 
    order by LAST_CALL_ET desc; 

USERNAME   MACHINE    STATUS    LOGON_TIME         INACTIVE_SECONDS
---------- ---------- -------   -----------------  ----------------
USERAPP    machine1  INACTIVE   20170917-17:04:17             3523
USERAPP    machine2  INACTIVE   20170918-02:57:03             2068
USERAPP    machine3  INACTIVE   20170917-17:52:09             1141
...
..
.
USERAPP    machine1 INACTIVE   20170917-17:17:01               176
USERAPP    machine1 I NACTIVE   20170917-17:17:01              176

REFERENCES

According to Oracle doc :

LAST_CALL_ETNUMBERIf the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.

The below query can also get the results as per Oracle Doc ID 365693.1.

SQL>select sid, seconds_in_wait 
      from v$session_wait
     where state='WAITING' 
       and event='SQL*Net message from client'
 order by seconds_in_wait desc;

Data Guard Not Applying Logs After RAC Standby Database Restarted

It looks like a bug. Property “PreferredApplyInstance” specifies the standby instance to apply logs

11.2.0.4 RAC standby database is not applying archivelogs after instances are all restarted.  It is configured in Data Guard management.

After investigation, there is nothing special, but only “PreferredApplyInstance” is configured for RAC node1 ‘STBTEST1’.

Original :

PreferredApplyInstance ='STBTEST1'

Modify “PreferredApplyInstance” to empty:

PreferredApplyInstance=''

Restarted RAC standby database instances , all archivelogs are applied to database from the node which is chosen randomly by Data Guard.

ORA-03135: connection lost contact to Standby Database

Newly built 12.1.0.2 DataGuard got errors.

Primary Database alert.log:

Mon Sep 11 11:35:59 2017
TT01: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
TT01: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Mon Sep 11 11:35:59 2017
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_tt01_30967.trc:
ORA-03135: connection lost contact
Error 3135 for archive log file 42 to 'STBTEST'
Mon Sep 11 11:35:59 2017
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_tt01_30967.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 42 thread 1 sequence 852 (3135)

Standby Database Alert.log

Fatal NI connect error 12170.

VERSION INFORMATION:
 TNS for Linux: Version 12.1.0.2.0 - Production
 Oracle Bequeath NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
 TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
 Time: 06-SEP-2017 16:50:33
 Tracing not turned on.
 Tns error struct:
 ns main err code: 12535

TNS-12535: TNS:operation timed out
 ns secondary err code: 12608
 nt main err code: 0
 nt secondary err code: 0
 nt OS err code: 0
 Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.xx)(PORT=63637))
Wed Sep 06 16:50:34 2017
RFS[37]: Possible network disconnect with primary database

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

The ORA-16698 occurred when creating a new Data Guard Broker configuration on 12.1.0.2 RAC database.

DGMGRL>CREATE CONFIGURATION 'RACTEST_DG' as PRIMARY DATABASE IS 'RACTEST' CONNECT IDENTIFIER IS RACTEST;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Also Make sure do the same before you configure Data Guard Broker by using Oracle OEM. Otherwise it will fail without any errors provided, for which it might take you some time to investigate.