1 2 Previous Next 22 Replies Latest reply on Dec 20, 2017 6:46 AM by SUPRIYO DEY Go to original post
      • 15. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
        AndrewSayer

        Still waiting for output of

         

        show parameter local_listener

         

        From sqlplus while you are connected as sysdba.

         

        We can see that the instance has started, we can see that a listener has started, we can see that the listener is not the listener your instance expected / the listener wasn’t hardcoded to map to your instance.

        • 16. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
          SUPRIYO DEY

          Try to register database service with dynamic registartion

          sqlplus / as sysdba

          sql> alter system register;

           

          lsnrctl status

          • 17. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
            ziutek

            alert_xe.log

            ***********************************************************************

             

            Fatal NI connect error 12638, connecting to:

            (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

             

              VERSION INFORMATION:

            TNS for 64-bit Windows: Version 11.2.0.2.0 - Production

            Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production

              Time: 18-GRU-2017 03:16:50

              Tracing not turned on.

              Tns error struct:

              ns main err code: 12638

             

            TNS-12638: Credential retrieval failed

              ns secondary err code: 0

              nt main err code: 0

              nt secondary err code: 0

              nt OS err code: 0

            Mon Dec 18 13:25:49 2017

            Starting ORACLE instance (normal)

            LICENSE_MAX_SESSION = 0

            LICENSE_SESSIONS_WARNING = 0

            Picked latch-free SCN scheme 3

            Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

            Autotune of undo retention is turned on.

            IMODE=BR

            ILAT =18

            LICENSE_MAX_USERS = 0

            SYS auditing is disabled

            Starting up:

            Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.

            Using parameter settings in server-side pfile I:\PROGRAM\ORACLEDB\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\INITXE.ORA

            System parameters with non-default values:

              sessions = 172

              spfile = "I:\PROGRAM\ORACLEDB\APP\ORACLE\PRODUCT\11.2.0\SERVER\DBS\SPFILEXE.ORA"

              memory_target = 1G

              control_files = "I:\PROGRAM\ORACLEDB\APP\ORACLE\ORADATA\XE\CONTROL.DBF"

              compatible = "11.2.0.0.0"

              db_recovery_file_dest = "I:\program\oracledb\app\oracle\fast_recovery_area"

              db_recovery_file_dest_size= 10G

              undo_management = "AUTO"

              undo_tablespace = "UNDOTBS1"

              remote_login_passwordfile= "EXCLUSIVE"

              dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"

              shared_servers = 4

              local_listener = "(ADDRESS=(PROTOCOL=TCP)(HOST=qwerty-Komputer)(PORT=1522))"

              job_queue_processes = 4

              audit_file_dest = "I:\PROGRAM\ORACLEDB\APP\ORACLE\ADMIN\XE\ADUMP"

              db_name = "XE"

              open_cursors = 300

              diagnostic_dest = "I:\PROGRAM\ORACLEDB\APP\ORACLE"

            Mon Dec 18 13:26:31 2017

            PMON started with pid=2, OS id=3536

            Mon Dec 18 13:26:31 2017

            PSP0 started with pid=3, OS id=3540

            Mon Dec 18 13:26:32 2017

            VKTM started with pid=4, OS id=3556 at elevated priority

            VKTM running at (10)millisec precision with DBRM quantum (100)ms

            Mon Dec 18 13:26:32 2017

            GEN0 started with pid=5, OS id=3560

            Mon Dec 18 13:26:32 2017

            DIAG started with pid=6, OS id=3564

            Mon Dec 18 13:26:32 2017

            DBRM started with pid=7, OS id=3568

            Mon Dec 18 13:26:32 2017

            DIA0 started with pid=8, OS id=3572

            Mon Dec 18 13:26:32 2017

            MMAN started with pid=9, OS id=3576

            Mon Dec 18 13:26:32 2017

            DBW0 started with pid=10, OS id=3580

            Mon Dec 18 13:26:32 2017

            LGWR started with pid=11, OS id=3584

            Mon Dec 18 13:26:32 2017

            CKPT started with pid=12, OS id=3588

            Mon Dec 18 13:26:32 2017

            SMON started with pid=13, OS id=3592

            Mon Dec 18 13:26:32 2017

            RECO started with pid=14, OS id=3596

            Mon Dec 18 13:26:32 2017

            MMON started with pid=15, OS id=3600

            Mon Dec 18 13:26:32 2017

            starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

            starting up 4 shared server(s) ...

            ORACLE_BASE from environment = I:\program\oracledb\app\oracle

            Mon Dec 18 13:26:33 2017

            alter database mount exclusive

            Mon Dec 18 13:26:32 2017

            MMNL started with pid=16, OS id=3604

            Successful mount of redo thread 1, with mount id 2844189641

            Database mounted in Exclusive Mode

            Lost write protection disabled

            Mon Dec 18 13:26:43 2017

            Completed: alter database mount exclusive

            alter database open

            Beginning crash recovery of 1 threads

            Started redo scan

            Completed redo scan

            read 362 KB redo, 78 data blocks need recovery

            Started redo application at

            Thread 1: logseq 16, block 12514

            Recovery of Online Redo Log: Thread 1 Group 2 Seq 16 Reading mem 0

              Mem# 0: I:\PROGRAM\ORACLEDB\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_F2YB9S86_.LOG

            Completed redo application of 0.22MB

            Completed crash recovery at

            Thread 1: logseq 16, block 13239, scn 778048

            78 data blocks read, 78 data blocks written, 362 redo k-bytes read

            Mon Dec 18 13:26:47 2017

            Thread 1 advanced to log sequence 17 (thread open)

            Thread 1 opened at log sequence 17

              Current log# 1 seq# 17 mem# 0: I:\PROGRAM\ORACLEDB\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_F2YB9QHO_.LOG

            Successful open of redo thread 1

            Mon Dec 18 13:26:47 2017

            SMON: enabling cache recovery

            Mon Dec 18 13:26:56 2017

            [3632] Successfully onlined Undo Tablespace 2.

            Undo initialization finished serial:0 start:122164 end:123521 diff:1357 (13 seconds)

            Verifying file header compatibility for 11g tablespace encryption..

            Verifying 11g file header compatibility for tablespace encryption completed

            SMON: enabling tx recovery

            Database Characterset is AL32UTF8

            Opening with Resource Manager plan: INTERNAL_PLAN_XE

            Mon Dec 18 13:26:57 2017

            Starting background process VKRM

            Mon Dec 18 13:26:57 2017

            VKRM started with pid=23, OS id=3828

            replication_dependency_tracking turned off (no async multimaster replication found)

            Starting background process QMNC

            Mon Dec 18 13:27:03 2017

            QMNC started with pid=24, OS id=3952

            Mon Dec 18 13:27:08 2017

            Completed: alter database open

            Mon Dec 18 13:32:29 2017

            Starting background process CJQ0

            Mon Dec 18 13:32:29 2017

            CJQ0 started with pid=29, OS id=3276

            Mon Dec 18 13:32:45 2017

            db_recovery_file_dest_size of 10240 MB is 0.98% used. This is a

            user-specified limit on the amount of space that will be used by this

            database for recovery-related files, and does not reflect the amount of

            space available in the underlying filesystem or ASM diskgroup.

            Mon Dec 18 13:33:04 2017

            Starting background process SMCO

            Mon Dec 18 13:33:04 2017

            SMCO started with pid=27, OS id=832

            show parameter local_listener

            SQL> show parameter local_listener

             

            NAME TYPE VALUE

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

            local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=q

              werty-Komputer)(PORT=1522))

             

            sql> alter system register;

            SQL> alter system register;

             

            System altered.

            lsnrctl status


            C:\Users\qwerty>lsnrctl status

             

            LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 18-GRU-2017 13:51

            :17

             

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

             

            Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

            STATUS of the LISTENER

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

            Alias LISTENER

            Version TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Produ

            ction

            Start Date 18-GRU-2017 13:25:36

            Uptime 0 days 0 hr. 25 min. 47 sec

            Trace Level off

            Security ON: Local OS Authentication

            SNMP OFF

            Listener Log File I:\program\oracledb\app\oracle\diag\tnslsnr\qwerty-Kom

            puter\listener\alert\log.xml

            Listening Endpoints Summary...

              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=qwerty-Komputer)(PORT=1521)))

            The listener supports no services

            The command completed successfully

             

            • 18. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
              AndrewSayer

              Connect as sysDBA again and run

               

              alter system set local_listener=‘

              (ADDRESS=(PROTOCOL=TCP)(HOST=q

                werty-Komputer)(PORT=1521))’;

               

              (after fixing the formatting)

               

              Then Alter system register;

               

              Then instance should be registered with expected listener and you should be able to connect through it.

              • 20. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
                SUPRIYO DEY

                LOCAL_LISTENER PARAMATER IS NOT REQUIRED WHILE USING DEFAULT PORT FOR THE DB . THERE MAY BE SOMETHING WRONG THAT IS CAUSING DB SERVICE NOT BEING REGISTERED WITH THE LISTENER.THIS PARAMETER IS REQUIRED WHEN NON DEFAULT PORT IS USED.

                • 21. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
                  EdStevens

                  SUPRIYO DEY wrote:

                   

                  LOCAL_LISTENER PARAMATER IS NOT REQUIRED WHILE USING DEFAULT PORT FOR THE DB . THERE MAY BE SOMETHING WRONG THAT IS CAUSING DB SERVICE NOT BEING REGISTERED WITH THE LISTENER.THIS PARAMETER IS REQUIRED WHEN NON DEFAULT PORT IS USED.

                  Is there a question here? 

                   

                  Is your caps-lock key stuck?  WHEN YOU TYPE IN ALL CAPS IT LOOKS LIKE YOU ARE SHOUTING!

                  1 2 Previous Next