sysdba用户远程启停oracle数据库ORA-12514: TNS:listener does not currently know of service requested

满足如下条件即可远程启停库
1.创建sysdba用户,赋予connect和sysdba权限
2.remote_login_passwordfile为 EXCLUSIVE
3.加入静态监听

Last login: Tue Nov  3 11:01:16 2020 from 192.168.52.1
[root@saperp ~]# su - oracle
Last login: Fri Oct 30 14:44:06 CST 2020 on pts/0
[oracle@saperp ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:34:02 2020

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             436211688 bytes
Database Buffers          805306368 bytes
Redo Buffers                8892416 bytes
Database mounted.
Database opened.
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

SQL> create user sysdba identified by sys;

User created.

SQL> grant sysdba to sysdba;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
SYSDBA                         TRUE  FALSE FALSE

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@saperp ~]$ ps -ef|grep ora_smon
oracle     1939      1  0 09:34 ?        00:00:00 ora_smon_erp
oracle     2073   1883  0 09:35 pts/0    00:00:00 grep --color=auto ora_smon

[oracle@saperp admin]$ more sqlnet.ora
# sqlnet.ora Network Configuration File: /home/db/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /home/db/oracle
 

[oracle@saperp admin]$ cat tnsnames.ora 
erp =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.155)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = erp)
    )
  )
[oracle@saperp ~]$ tnsping erp

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-NOV-2020 09:35:19

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/home/db/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.155)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = erp)))
TNS-12541: TNS:no listener
[oracle@saperp ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-NOV-2020 09:35:22

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

Starting /home/db/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /home/db/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /home/db/oracle/diag/tnslsnr/saperp/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=saperp)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                24-NOV-2020 09:35:22
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/db/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /home/db/oracle/diag/tnslsnr/saperp/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=saperp)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@saperp ~]$ tnsping erp

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-NOV-2020 09:35:24

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/home/db/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.155)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = erp)))
OK (0 msec)
[oracle@saperp ~]$ sqlplus sysdba/sys@erp as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:35:41 2020

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


Enter user-name: 
[oracle@saperp ~]$ tnsping erp

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-NOV-2020 09:36:03

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/home/db/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.155)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = erp)))
OK (0 msec)
[oracle@saperp ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:36:09 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      erp
db_unique_name                       string      erp
global_names                         boolean     FALSE
instance_name                        string      erp
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      erp
SQL> grant connect to sysdba;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@saperp ~]$ sqlplus sysdba/sys@erp as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:37:11 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@saperp ~]$ sqlplus sysdba/sys@erp as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:37:53 2020

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 
[oracle@saperp ~]$ sqlplus /@erp as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:38:21 2020

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: 
[oracle@saperp ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:38:31 2020

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             436211688 bytes
Database Buffers          805306368 bytes
Redo Buffers                8892416 bytes
Database mounted.
Database opened.
SQL> show parameter pass;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@saperp ~]$ sqlplus sysdba/sys@erp as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:41:01 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup force
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
SQL> !ps -ef|grep ora_smon
oracle     2491   2474  0 09:41 pts/1    00:00:00 /bin/bash -c ps -ef|grep ora_smon
oracle     2493   2491  0 09:41 pts/1    00:00:00 grep ora_smon

SQL> !oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener.  This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action: 
//  - Wait a moment and try to connect a second time.
//  - Check which services are currently known by the listener by executing:
//    lsnrctl services <listener name>
//  - Check that the SERVICE_NAME parameter in the connect descriptor of the
//    net service name used specifies a service known by the listener.
//  - If an easy connect naming connect identifier was used, check that
//    the service name specified is a service known by the listener.
//  - Check for an event in the listener.log file.

SQL> show parameter name;
ERROR:
ORA-03114: not connected to ORACLE


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@saperp ~]$ sqlplus sys/sys@erp as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:47:30 2020

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: 
[oracle@saperp ~]$ sqlplus sys/sys^Crp as sysdba
[oracle@saperp ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-NOV-2020 09:47:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                24-NOV-2020 09:35:22
Uptime                    0 days 0 hr. 12 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/db/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /home/db/oracle/diag/tnslsnr/saperp/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=saperp)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@saperp ~]$ cd $ORACLE_HOME/network/admin
[oracle@saperp admin]$ ll
total 20
-rw-r-----  1 oracle oinstall 110 Jun  4 14:12 GLOBALS
-rw-r--r--. 1 oracle oinstall 367 Jan 15  2020 listener.ora
drwxr-xr-x. 2 oracle oinstall  64 Jan 15  2020 samples
-rw-r--r--. 1 oracle oinstall 835 Sep 13  2019 shrept.lst
-rw-r--r--. 1 oracle oinstall 219 Jan 15  2020 sqlnet.ora
-rw-r--r--  1 oracle oinstall 177 May  2  2020 tnsnames.ora
[oracle@saperp admin]$ more listener.ora 
# listener.ora Network Configuration File: /home/db/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

ADR_BASE_LISTENER = /home/db/oracle

[oracle@saperp admin]$ tnsping erp

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-NOV-2020 09:48:10

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/home/db/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.155)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = erp)))
OK (0 msec)
[oracle@saperp admin]$ vi listener.ora 
# listener.ora Network Configuration File: /home/db/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = saperp)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST =
     (SID_DESC =
      ( GLOBAL_DBNAME = erp)
      ( ORACLE_HOME =/home/db/oracle/product/11.2.0/db_1)
      ( SID_NAME = erp)
    )
  )

ADR_BASE_LISTENER = /home/db/oracle

~
~
~
"listener.ora" 21L, 545C written                                                          
[oracle@saperp admin]$ cat listener.ora 
# listener.ora Network Configuration File: /home/db/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = saperp)(PORT = 1521))
    )
  )
SID_LIST_LISTENER= 
  (SID_LIST = 
     (SID_DESC = 
      ( GLOBAL_DBNAME = erp) 
      ( ORACLE_HOME =/home/db/oracle/product/11.2.0/db_1) 
      ( SID_NAME = erp) 
    ) 
  )

ADR_BASE_LISTENER = /home/db/oracle

[oracle@saperp admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-NOV-2020 09:49:53

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@saperp admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-NOV-2020 09:49:55

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

Starting /home/db/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /home/db/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /home/db/oracle/diag/tnslsnr/saperp/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=saperp)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                24-NOV-2020 09:49:55
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/db/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /home/db/oracle/diag/tnslsnr/saperp/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=saperp)(PORT=1521)))
Services Summary...
Service "erp" has 1 instance(s).
  Instance "erp", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@saperp admin]$ sqlplus sysdba/sys@erp as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:50:02 2020

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             436211688 bytes
Database Buffers          805306368 bytes
Redo Buffers                8892416 bytes
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             436211688 bytes
Database Buffers          805306368 bytes
Redo Buffers                8892416 bytes
Database mounted.
Database opened.
SQL> 

上一篇:mysql 链接时报错:1251-Client does not support authentication protocol requested by server


下一篇:Linux客户端连接报错:Cannot assign requested address