• 当手工成功建库并设置好服务器的监听后,使用网络连接方式失败原因分析:



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

    一:当在手工建库没有创建用户密码和密码文件

    1:切换到oracle用户后。查看监听是否已经起来

      1 
      2 [oracle@oracle ~]$ lsnrctl
      3 
      4 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 19-OCT-2017 20:49:28
      5 
      6 Copyright (c) 1991, 2011, Oracle.  All rights reserved.
      7 
      8 Welcome to LSNRCTL, type "help" for information.
      9 
     10 LSNRCTL> status
     11 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
     12 STATUS of the LISTENER
     13 ------------------------
     14 Alias                     LISTENER
     15 Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
     16 Start Date                18-OCT-2017 23:34:43
     17 Uptime                    0 days 21 hr. 14 min. 47 sec
     18 Trace Level               off
     19 Security                  ON: Local OS Authentication
     20 SNMP                      OFF
     21 Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
     22 Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
     23 Listening Endpoints Summary...
     24   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
     25   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
     26 Services Summary...
     27 Service "prod2" has 1 instance(s).
     28   Instance "prod2", status READY, has 1 handler(s) for this service...
     29 The command completed successfully
     30 [oracle@oracle ~]$
     31 

    如果发现没有起来;请参考下列步骤,否则葫芦

      1 
      2 [oracle@oracle ~]$ lsnrctl
      3 
      4 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 19-OCT-2017 22:20:05
      5 
      6 Copyright (c) 1991, 2011, Oracle.  All rights reserved.
      7 
      8 Welcome to LSNRCTL, type "help" for information.
      9 
     10 LSNRCTL> help
     11 The following operations are available
     12 An asterisk (*) denotes a modifier or extended command:
     13 
     14 start               stop                status
     15 services            version             reload
     16 save_config         trace               spawn
     17 change_password     quit                exit
     18 set*                show*
     19 
     20 LSNRCTL> status
     21 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
     22 TNS-12541: TNS:no listener
     23  TNS-12560: TNS:protocol adapter error
     24   TNS-00511: No listener
     25    Linux Error: 111: Connection refused
     26 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
     27 TNS-12541: TNS:no listener
     28  TNS-12560: TNS:protocol adapter error
     29   TNS-00511: No listener
     30    Linux Error: 2: No such file or directory
     31 LSNRCTL> start
     32 Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
     33 
     34 TNSLSNR for Linux: Version 11.2.0.3.0 - Production
     35 System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
     36 Log messages written to /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
     37 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
     38 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
     39 
     40 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
     41 STATUS of the LISTENER
     42 ------------------------
     43 Alias                     LISTENER
     44 Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
     45 Start Date                19-OCT-2017 22:22:39
     46 Uptime                    0 days 0 hr. 0 min. 20 sec
     47 Trace Level               off
     48 Security                  ON: Local OS Authentication
     49 SNMP                      OFF
     50 Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
     51 Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
     52 Listening Endpoints Summary...
     53   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
     54   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
     55 Services Summary...
     56 Service "prod2" has 1 instance(s).
     57   Instance "prod2", status READY, has 1 handler(s) for this service...
     58 The command completed successfully
     59 LSNRCTL>
    View Code

    2:查看监听文件和 连接服务文件的 配置信息,

      1 oracle@oracle admin]$ pwd
      2 /u01/app/oracle/product/11.2.0/db_1/network/admin
      3 [oracle@oracle admin]$ ls
      4 listener.ora  samples  shrept.lst  tnsnames.ora
      5 [oracle@oracle admin]$ cat tnsnames.ora
      6 # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
      7 # Generated by Oracle configuration tools.
      8 
      9 ORCL1 =
     10   (DESCRIPTION =
     11     (ADDRESS_LIST =
     12       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.242.10)(PORT = 1521))
     13     )
     14     (CONNECT_DATA =
     15       (SERVICE_NAME = prod2)
     16     )
     17   )
     18 
     19 [oracle@oracle admin]$ cat listener.ora
     20 # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
     21 # Generated by Oracle configuration tools.
     22 
     23 LISTENER1 =
     24   (DESCRIPTION =
     25     (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1522))
     26   )
     27 
     28 ADR_BASE_LISTENER1 = /u01/app/oracle
     29 
     30 LISTENER =
     31   (DESCRIPTION_LIST =
     32     (DESCRIPTION =
     33       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
     34     )
     35     (DESCRIPTION =
     36       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     37     )
     38   )
     39 
     40 ADR_BASE_LISTENER = /u01/app/oracle
     41 
     42 [oracle@oracle admin]$




    3:在手工建库的时候没有创建密码文件同时再进行远程连接的时候没有启动数据库;

      1 [oracle@oracle ~]$ sqlplus sys/oracle@ORCL1
      2 
      3 SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 19 21:01:40 2017
      4 
      5 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      6 
      7 ERROR:
      8 ORA-01017: invalid username/password; logon denied
      9 
     10 
     11 Enter user-name: sys
     12 Enter password:
     13 ERROR:
     14 ORA-01034: ORACLE not available
     15 ORA-27101: shared memory realm does not exist
     16 Linux Error: 2: No such file or directory
     17 Process ID: 0
     18 Session ID: 0 Serial number: 0
     19 
     20 
     21 Enter user-name: sys
     22 Enter password:
     23 ERROR:
     24 ORA-01034: ORACLE not available
     25 ORA-27101: shared memory realm does not exist
     26 Linux Error: 2: No such file or directory
     27 Process ID: 0
     28 Session ID: 0 Serial number: 0
     29 
     30 
     31 SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
     32 [oracle@oracle ~]$

    4:启动数据库后,再进行登录:还是继续报错误

      1 [root@oracle ~]# su - oracle
      2 [oracle@oracle ~]$ export ORACLE_SID=prod2
      3 [oracle@oracle ~]$ sqlplus / as sysdba;
      4 
      5 SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 19 21:02:51 2017
      6 
      7 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      8 
      9 
     10 Connected to:
     11 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
     12 With the Partitioning, OLAP, Data Mining and Real Application Testing options
     13 
     14 SQL> startup force
     15 ORACLE instance started.
     16 
     17 Total System Global Area 1071333376 bytes
     18 Fixed Size                  1349732 bytes
     19 Variable Size             624953244 bytes
     20 Database Buffers          440401920 bytes
     21 Redo Buffers                4628480 bytes
     22 Database mounted.
     23 Database opened.
     24 SQL> exit
     25 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
     26 With the Partitioning, OLAP, Data Mining and Real Application Testing options
     27 [oracle@oracle ~]$ sqlplus  sys/oracle@ORCL1
     28 
     29 SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 19 21:04:08 2017
     30 
     31 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
     32 
     33 ERROR:
     34 ORA-01017: invalid username/password; logon denied
     35 
     36 
     37 Enter user-name: sys
     38 Enter password:
     39 ERROR:
     40 ORA-01017: invalid username/password; logon denied
     41 

    5: 创建数据库sid用户密码文件和sys用户密码:

      1 [oracle@oracle dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/network/
      2 [oracle@oracle network]$ ls
      3 admin  doc  install  jlib  lib  log  mesg  tools  trace
      4 [oracle@oracle network]$ clear
      5 
      6 [oracle@oracle network]$ pwd
      7 /u01/app/oracle/product/11.2.0/db_1/network
      8 [oracle@oracle network]$ ls
      9 admin  doc  install  jlib  lib  log  mesg  tools  trace
     10 [oracle@oracle network]$ cd admin/
     11 [oracle@oracle admin]$ ls
     12 listener.ora  samples  shrept.lst  tnsnames.ora
     13 [oracle@oracle admin]$ cd ..
     14 [oracle@oracle network]$ ls
     15 admin  doc  install  jlib  lib  log  mesg  tools  trace
     16 [oracle@oracle network]$ cd ..
     17 [oracle@oracle db_1]$ ls
     18 apex        cfgtoollogs  css     deinstall    EMStage  instantclient  jdev  log      nls     opmn                     oui    precomp   scheduler     srvm             usm
     19 assistants  clone        ctx     demo         has      inventory      jdk   md       oc4j    oracle.example.com_orcl  owb    racg      slax          sysman           utl
     20 bin         config       cv      diagnostics  hs       j2ee           jlib  mesg     odbc    oracore                  owm    rdbms     sqldeveloper  timingframework  wwg
     21 ccr         crs          dbs     dv           ide      javavm         ldap  mgw      olap    oraInst.loc              perl   relnotes  sqlj          ucp              xdk
     22 cdata       csmig        dc_ocm  emcli        install  jdbc           lib   network  OPatch  ord                      plsql  root.sh   sqlplus       uix
     23 [oracle@oracle db_1]$ cd d
     24 dbs/         dc_ocm/      deinstall/   demo/        diagnostics/ dv/
     25 [oracle@oracle db_1]$ cd dbs/
     # /u01/app/oracle/product/11.2.0/db_1/dbs
     26 [oracle@oracle dbs]$ ls
     27 hc_orcl.dat  hc_prod2.dat  hc_prod.dat  init.ora  initprod2.ora  initprod.ora  lkORCL  lkPROD  lkPROD2  orapworcl  spfileorcl.ora  spfileprod2.ora  spfileprod.ora
     28 [oracle@oracle dbs]$ orapwd file=orapwprod2 entries=30
     29 
     30 Enter password for SYS:
     31 [oracle@oracle dbs]$ ls
     32 hc_orcl.dat  hc_prod2.dat  hc_prod.dat  init.ora  initprod2.ora  initprod.ora  lkORCL  lkPROD  lkPROD2  orapworcl  orapwprod2  spfileorcl.ora  spfileprod2.ora  spfileprod.ora
     33 [oracle@oracle dbs]$ pwd
     34 /u01/app/oracle/product/11.2.0/db_1/dbs
     35 [oracle@oracle dbs]$
     36 [oracle@oracle dbs]$


    6:重新进行远程访问式登录

      1 With the Partitioning, OLAP, Data Mining and Real Application Testing options
      2 [oracle@oracle ~]$ sqlplus sys/oracle@ORCL1 as sysdba;
      3 
      4 SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 19 21:09:25 2017
      5 
      6 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      7 
      8 ERROR:
      9 ORA-01031: insufficient privileges
     10 
     11 
     12 Enter user-name: sys
     13 Enter password:
     14 ERROR:
     15 ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
     16 
     17 
     18 Enter user-name: sys  as sysdba;
     19 SP2-0306: Invalid option.
     20 Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
     21 where <logon> ::= <username>[/<password>][@<connect_identifier>]
     22       <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
     23 SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
    #创建密码文件后再次进行访问: 
    
     26 [oracle@oracle ~]$ sqlplus sys/oracle@ORCL1 as sysdba;
     27 
     28 SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 19 21:15:33 2017
     29 
     30 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
     31 
     32 
     33 Connected to:
     34 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
     35 With the Partitioning, OLAP, Data Mining and Real Application Testing options
     36 
     37 SQL> select name from v$database;
     38 
     39 NAME
     40 ---------
     41 PROD2
     42 
     43 SQL>














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

  • 相关阅读:
    oc 基本基础类型之NSString
    oc 内存管理
    自定义的init方法和重写的init方法
    property属性
    iOS 开发朗读文字
    获取当前最顶层的ViewController
    二维码扫描的简单封装
    OC百度导航类的封装
    OC上传图片的封装(配合AFNetWorkiing)
    集成百度地图报错41个解决方法(转)
  • 原文地址:https://www.cnblogs.com/ios9/p/7695991.html
Copyright © 2020-2023  润新知