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

2022-08-06 15:21:03

满足如下条件即可远程启停库
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

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

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

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

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)))

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

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

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

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

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

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

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*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:37:11 2020

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

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

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

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

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*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:47:30 2020

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

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

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.

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

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

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

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

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>

码农公寓

相关文章