After the server restarts, it is found that ORA-12514: TNS: the listener cannot recognize the service requested in the connection descriptor

Problem: the Oracle main service and listener service have been started. You can connect normally with SQL Plus. You can connect with PL SQL Developer and report an error: ORA-12514: TNS: the listener cannot recognize the service requested in the connection descriptor at present

Go to the server to check the oracle service and find that there are two services. Strangely, both services have been started, but only one of them is running in the process of task manager. Because the service used by our program is ORCL, all of them cannot be connected

There is only one in the process

Solution steps:

Step 1: view the registry

1. Press Win+R, enter regedit,
2. Enter the registry and then enter HKEY_ LOCAL_ Make \ software \ Oracle and click KEY_OraDb11g_home1，
3. Here's a look, ha ha, Oracle_ When did the SID data value become lingjingHT (the instance name that is no longer used), and the list of services on the computer is clearly oracle11g named by itself at the time of initial installation (Figure 1 below)
4. And changed it back. ORACLE_SID, set to your database SID. (Figure II below)

The modified SID is as follows:

 

After modifying the registry, the oracle database server was restarted, and the same problem was found

Step 2: modify the listening configuration file

1. End: OracleOraDb11g_home2TNSListener {process;
2. Open D: \ devinstall \ oracle11gr2 \ administrator \ product \ 11.2 0\dbhome_ 1. For the listener.ora file in the \ network \ admin (different machine directories may be different), add a frame; (if you add an error, the oracleoradb11g_home2tnsslistener service cannot be started)
3. Start OracleOraDb11g_home2TNSListener successfully connected after the service.

 

The red box is new

Restart the service after adding the service (some people may be able to connect normally at this step)

Step 3: sqlplus connects to the database and connects to the idle routine

The problem here is related to two services in my database, because I switched to the current service

ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

Solution: as follows, I only implemented it

[startup pfile='E:\app\Administrator\admin\orcl\pfile\init.ora.10192012163956';]

The database is back to normal

SQL> startup;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
 
SQL> startup pfile='E:\app\Administrator\admin\orcl\pfile\init.ora.10192012163956';
ORACLE Routine started.
 
Total System Global Area 3423965184 bytes
Fixed Size                  2180544 bytes
Variable Size            1862273600 bytes
Database Buffers         1543503872 bytes
Redo Buffers               16007168 bytes
 Database loading completed.
The database is already open.
SQL> show parameter sga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3280M
sga_target                           big integer 0
SQL> show parameter memory
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 3280M
memory_target                        big integer 3280M
shared_memory_address                integer     0
SQL> create spfile from pfile;
create spfile from pfile
*
Error on line 1:
ORA-01078: Failed to process system parameters
LRM-00109: could not open parameter file
'E:\APP\Administrator\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITZJGSID.ORA'
 
 
SQL> create spfile from memory;
 
File created.
 
SQL> shutdown abort
ORACLE The routine has been closed.
SQL> startup
ORACLE Routine started.
 
Total System Global Area 3423965184 bytes
Fixed Size                  2180544 bytes
Variable Size            1879050816 bytes
Database Buffers         1526726656 bytes
Redo Buffers               16007168 bytes
 Database loading completed.
The database is already open.
SQL> show parameter sga;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3280M
sga_target                           big integer 0
SQL> show parameter memory;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 3280M
memory_target                        big integer 3280M
shared_memory_address                integer     0 
 
SQL> select count(1) from v$session;
 
  COUNT(1)
----------
        31
 
SQL>