---------------------------------------------------------------------------------------------------------------------------------------------------------------
一:当在手工建库没有创建用户密码和密码文件
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>
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>
---------------------------------------------------------------------------------------------------------------------------------------------------------------