Oracle database network configuration files - listener.ora and tnsnames.ora

DB Network Configuration file 

tnsnames.ora

[oracle@pri admin]$ pwd
/u01/app/oracle/product/18.0.0/db/network/admin


[oracle@pri admin]$

[oracle@pri admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/18.0.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

IND =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.68)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = IND)
    )
  )

##LISTENER_IND =
##  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.68)(PORT = 1521))

USA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.69)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = USA)
    )
  )

listener.ora

[oracle@pri admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/18.0.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.68)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = IND)
      (ORACLE_HOME = /u01/app/oracle/product/18.0.0/db)
      (SID_NAME = IND)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = USA)
      (ORACLE_HOME = /u01/app/oracle/product/18.0.0/db)
      (SID_NAME = USA)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle




[oracle@pri admin]$

[oracle@pri ~]$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 10-APR-2019 22:46:01

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.43.68)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production

Start Date                10-APR-2019 17:03:30

Uptime                    0 days 5 hr. 42 min. 30 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/18.0.0/db/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/pri/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.68)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "IND" has 1 instance(s).

  Instance "IND", status UNKNOWN, has 1 handler(s) for this service...

Service "USA" has 1 instance(s).

  Instance "USA", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@pri ~]$

Oracle 18c client installation

Oracle 18c client installation 

*  Download oracle client software .

* unzip the software 

follow below steps for installation :-

setup run by administrator 

you can use different user as you requirement 

Add database details in tnanames.ora

C:\Users\ANURAG>
C:\Users\ANURAG>sqlplus hr/hr@ind

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Apr 10 22:42:30 2019
Version 18.3.0.0.0

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

Last Successful login time: Wed Apr 10 2019 20:48:43 +05:30

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
IND       READ WRITE


SQL>

ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], []

 Resolve   ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], []


[oracle@pri ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Apr 10 16:05:24 2019
Version 18.5.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1526725696 bytes
Fixed Size                  8657984 bytes
Variable Size             503316480 bytes
Database Buffers         1006632960 bytes
Redo Buffers                8118272 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],
[], [], [], [], [], [], [], []


SQL>


in alert log
===============
Database mounted in Exclusive Mode
Lost write protection disabled
.... (PID:3113): Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST [krsd.c:17733]
2019-04-10T16:35:57.598943+05:30
Completed: ALTER DATABASE   MOUNT
2019-04-10T16:35:57.703170+05:30
ALTER DATABASE OPEN
Ping without log force is disabled:
  instance mounted in exclusive mode.
2019-04-10T16:35:58.087355+05:30
Beginning crash recovery of 1 threads
 Thread 1: Recovery starting at checkpoint rba (logseq 34 block 1464), scn 0
2019-04-10T16:35:58.236926+05:30
Started redo scan
2019-04-10T16:35:59.829966+05:30
Hex dump of (file 3, block 72034) in trace file /u01/app/oracle/diag/rdbms/ind/IND/trace/IND_ora_3118.trc

Reading datafile '/u01/app/oracle/oradata/IND/sysaux01.dbf' for corrupt data at rdba: 0x00c11962 (file 3, block 72034)
Reread (file 3, block 72034) found same corrupt data (logically corrupt)
Write verification failed for File 3 Block 72034 (rdba 0xc11962)
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
2019-04-10T16:36:02.015670+05:30
Errors in file /u01/app/oracle/diag/rdbms/ind/IND/trace/IND_ora_3118.trc  (incident=69873):
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/ind/IND/incident/incdir_69873/IND_ora_3118_i69873.trc
2019-04-10T16:36:06.342365+05:30
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2019-04-10T16:36:06.342847+05:30
Aborting crash recovery due to error 600
2019-04-10T16:36:06.343449+05:30
Errors in file /u01/app/oracle/diag/rdbms/ind/IND/trace/IND_ora_3118.trc:
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []
2019-04-10T16:36:06.344570+05:30
Errors in file /u01/app/oracle/diag/rdbms/ind/IND/trace/IND_ora_3118.trc:
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...
2019-04-10T16:36:07.019702+05:30
Dumping diagnostic data in directory=[cdmp_20190410163607], requested by (instance=1, osid=3118), summary=[incident=69873].
2019-04-10T16:36:23.886376+05:30
TMON (PID:3112): STARTING ARCH PROCESSES
Starting background process ARC0
Starting background process ARC1
2019-04-10T16:36:23.938449+05:30
ARC0 started with pid=35, OS id=3121
Starting background process ARC2
2019-04-10T16:36:23.974871+05:30
ARC1 started with pid=36, OS id=3124
Starting background process ARC3
2019-04-10T16:36:24.003033+05:30
ARC2 started with pid=37, OS id=3126
TMON (PID:3112): ARC0: Archival started
TMON (PID:3112): ARC1: Archival started
TMON (PID:3112): ARC2: Archival started
2019-04-10T16:36:24.026025+05:30
ARC0 (PID:3121): Becoming a 'no FAL' ARCH
ARC0 (PID:3121): Becoming the 'no SRL' ARCH
2019-04-10T16:36:24.026857+05:30
ARC3 started with pid=38, OS id=3128
2019-04-10T16:36:24.036139+05:30
TMON (PID:3112): ARC3: Archival started
TMON (PID:3112): STARTING ARCH PROCESSES COMPLETE
2019-04-10T16:36:24.054208+05:30
TT00 (PID:3130): Gap Manager starting




Solution;

[oracle@pri ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Apr 10 16:05:24 2019
Version 18.5.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1526725696 bytes
Fixed Size                  8657984 bytes
Variable Size             503316480 bytes
Database Buffers         1006632960 bytes
Redo Buffers                8118272 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],
[], [], [], [], [], [], [], []


SQL> shut abort;
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1526725696 bytes
Fixed Size                  8657984 bytes
Variable Size             503316480 bytes
Database Buffers         1006632960 bytes
Redo Buffers                8118272 bytes
Database mounted.
SQL>
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
IND       READ WRITE

SQL>

Deadlocks in Oracle

Deadlock
==========

A deadlock occurs when two or more sessions are waiting for data locked by each other, resulting in all the sessions being blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock

session 1
=============

[oracle@pri ~]$ sqlplus hr/hr

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Feb 27 19:01:57 2019
Version 18.5.0.0.0

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

Last Successful login time: Wed Feb 27 2019 18:34:28 +05:30

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 Sarita
         2 Sarita
         3 Guggu

SQL> update test set name='Sarita' where id=3;

1 row updated.

SQL> update test set name='Guggu'  where id=1;



session 2
------------

[oracle@pri ~]$ sqlplus hr/hr

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Feb 27 19:02:03 2019
Version 18.5.0.0.0

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

Last Successful login time: Wed Feb 27 2019 19:01:58 +05:30

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 Sarita
         2 Sarita
         3 Guggu

SQL>  update test set name='Guggu'  where id=1;

1 row updated.

SQL> update test set name='Sarita' where id=3;
update test set name='Sarita' where id=3
                *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


SQL>

Query
---------


SELECT /*+ ORDERED */
  S.SID,
  S.USERNAME,
  S.PROGRAM,
  S.STATUS,
  SW.EVENT,
  SW.WAIT_TIME WT,
  SW.STATE,
  SW.SECONDS_IN_WAIT S_I_W,
  S.SQL_ID,
  S.SQL_ADDRESS,
  S.SQL_HASH_VALUE,
  S.SQL_CHILD_NUMBER,
  S.ROW_WAIT_OBJ# OBJ#,
  S.ROW_WAIT_FILE# FILE#,
  S.ROW_WAIT_BLOCK# BLOCK#,
  S.ROW_WAIT_ROW# ROW#,
  SW.P1,
  SW.P2,
  SW.P3
FROM
  V$SESSION_WAIT SW,
  V$SESSION S
WHERE
  S.USERNAME IS NOT NULL
  AND SW.SID=S.SID
  AND SW.EVENT NOT LIKE '%SQL*Net%'
  AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');



SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
  id1, id2, lmode, request, type
 FROM V$LOCK
WHERE (id1, id2, type) IN
 (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;