ORA-609 TNS-12537 and TNS-12547 or TNS-12170 in DB Alert.log

In alert log file following error reported. We are going to resolve this error.

ERROR:-

Alert log shows below error.

Fatal NI connect error 12537, connecting to:

 (LOCAL=NO)

Thu Oct 29 11:14:13 2020

Thu Oct 29 11:14:13 2020

TNS-12537: TNS:connection closed

 VERSION INFORMATION:

        TNS for Linux: Version 11.2.0.4.0 - Production

        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production

        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production

  Time: 29-OCT-2020 10:07:58

  Time: 29-OCT-2020 10:07:58

  Tracing not turned on.

  Tns error struct:

  Tracing not turned on.

    ns main err code: 12537

  Tns error struct:

TNS-12537: TNS:connection closed

opiodr aborting process unknown ospid (24729) as a result of ORA-609

TNS-12537: TNS:connection closed

    nt secondary err code: 0

    nt secondary err code: 0

    ns secondary err code: 12560

    nt main err code: 0

    nt secondary err code: 0

VERSION INFORMATION:

        TNS for Linux: Version 11.2.0.4.0 - Production

        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production

   TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production

    ns main err code: 12537

    ns main err code: 12537

    ns main err code: 12537

    ns main err code: 12537

    ns main err code: 12537

  Time: 29-OCT-2020 11:14:23

The above error will eliminate by increasing SQLNET.INBOUND_CONNECT_TIMEOUT in sqlnet.ora and listener file.

To change INBOUND_CONNECT_TIMEOUT follow below steps.

$cd $TNS_ADMIN     --> ($RDBMS_HOME/network/admin)

sqlnet.ora :- 

The default setting for the SQLNET.INBOUND_CONNECT_TIMEOUT parameter is 60 seconds.  

SQLNET.INBOUND_CONNECT_TIMEOUT =180

listener.ora:-

INBOUND_CONNECT_TIMEOUT_ACE = 160

Once you update the value need to be reload the listener.

$lsnrctl reload <listener_name>

Supporting doc:- 

Alert.log shows ORA-609 with TNS-12537: TNS:connection closed (Doc ID 1538717.1)

ORA-1116, ORA-1110 and ORA-27092 Errors in oracle database

In alert log file below error are reported.

ERROR:- 

Enter Password:

REP-1401: 'cf_policyformula': Fatal PL/SQL error occurred.

ORA-01116: error in opening database file 95

ORA-01110: data file 95: '/data03/db/apps_st/data/a_txn_data47.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 23: Too many open files in system

Additional information: 3

REP-0069: Internal error

REP-57054: In-process job terminated:Terminated with error: 

REP-1401: 'cf_policyformula': Fatal PL/SQL error occurred.

ORA-01116: error in opening database file 95

ORA-01110: data file 95: '/data03/db/apps_st/data/a_txn_data47.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 23: Too many open files in system

Additional information: 3

ORA-01122: database file 154 failed verification check
ORA-01110: data file 154: '/data05/db/apps_st/data/a_queue07.dbf'
ORA-01565: error in identifying file '/data05/db/apps_st/data/a_queue07.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 23: Too many open files in system
Additional inf

 ulimit -Sa

core file size          (blocks, -c) 0

data seg size           (kbytes, -d) unlimited

scheduling priority             (-e) 0

file size               (blocks, -f) unlimited

pending signals                 (-i) 1031260

max locked memory       (kbytes, -l) 125829120

max memory size         (kbytes, -m) unlimited

open files                      (-n) 4024

pipe size            (512 bytes, -p) 8

POSIX message queues     (bytes, -q) 819200

real-time priority              (-r) 0

stack size              (kbytes, -s) 1024

cpu time               (seconds, -t) unlimited

max user processes              (-u) 16384

virtual memory          (kbytes, -v) unlimited

file locks                      (-x) unlimited

Increase the user resource limits for the Unix user that owns the third party application

To do this, use the ulimit command

For example, to set the soft 'data' and 'stack' limits to unlimited use the commands:

% ulimit -d unlimited

% ulimit -s unlimited

To set the hard 'data' and 'stack' limits to unlimited use the commands:

% ulimit -Hd unlimited

% ulimit -Hs unlimited

To set permanently at user level .

$vi /etc/security/limits.conf

# oracle-rdbms-server-11gR2-preinstall setting for stack soft limit

oracle   soft   stack    unlimited

# oracle-rdbms-server-11gR2-preinstall setting for stack hard limit

oracle   hard   stack    unlimited

$sysctl -p

Finally verify the value.

 ulimit -Sa

core file size          (blocks, -c) 0

data seg size           (kbytes, -d) unlimited

scheduling priority             (-e) 0

file size               (blocks, -f) unlimited

pending signals                 (-i) 1031260

max locked memory       (kbytes, -l) 125829120

max memory size         (kbytes, -m) unlimited

open files                      (-n) 4024

pipe size            (512 bytes, -p) 8

POSIX message queues     (bytes, -q) 819200

real-time priority              (-r) 0

stack size              (kbytes, -s) unlimited

cpu time               (seconds, -t) unlimited

max user processes              (-u) 16384

virtual memory          (kbytes, -v) unlimited

file locks                      (-x) unlimited

Need to configure RMAN backup jobs in Windows servers

We are Taking Oracle Database Rman backup Daily on our Production Instance. In this post we will learn how to configure RMAN backup jobs in Windows Servers.

Create .cmd file with rman backup script.

1) backup_daily_db1.cmd

run{

Allocate channel ch1 type disk format '\path of taking backup_\%d_data_%U';

backup incremental level=0 database tag='Incremental_Backup';

Release channel ch1;

}

Create .bat extension file. 

2) backup_daily_db1.bat

set ORACLE_SID=TEST

rman target sys/****@TEST cmdfile=\Path of cmd file\backup_daily_db1.cmd 

log=D:\Path of log keeping\backup_complete_db1_%date:~4,2%_%date:~7,2%_%date:~10%.log

Schedule the above .bat script in windows scheduler.Launch the Task Scheduler from start menu.

👉On the right panel , click on create task.

👉Give a name to your task.

👉Click on trigger tab and new to schedule on specific time.

👉Click on Action tab and provide the patch for .bat .

👉Click on OK and task will be created.We can run the task by right clicking on task name.

How to setup password less ssh login in linux server

SSH is an open-source and most trusted network protocol that is use to login to remote server. It is use to transfer file from one host to remote host over the network using SCP protocol.

In this article , we will discuss how to setup password-less login on RedHat linux, OE linux and solaris.

First of all login on source server 192.168.0.10 and generate pair of public keys using below command.

Source server 192.168.0.10

Destination server 192.168.0.11

Step 1:- Generate public authentication  keys on 192.168.0.10.

[root@backup ~]# ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

4d:05:39:fc:42:84:b6:e7:29:55:ee:32:dh:8b:ff:19 root@backup.xx-xxxx.com

Step 2:- Create .ssh Directory on – 192.168.0.11

ssh root@192.168.0.11 mkdir -p .ssh

Step 3: Copy/Upload Generated Public Keys to – 192.168.0.11

cat .ssh/id_rsa.pub | ssh root@192.168.0.11 'cat >> .ssh/authorized_keys'

Step 4:- Set permission on 192.168.0.11

The most common issue is permission on remote server. The permissions of the directories should be 

chmod 700 .ssh; chmod 640 .ssh/authorized_keys

Step 5 :- Login from 192.168.0.10 to 192.168.0.11 without password.

$ssh root@192.168.0.11