When restarting a database, if you can’t acess it from outside, you may have to associate the newly restarted database with the listener. The Pmon process that is started with the instance is responsible for registration of oracle server with listener.

Pmon process wakes up at every 60 seconds and provide information to the listener. If any problem arises and your Pmon process fails then it’s not possible to register information to listener periodically. In this case you can do Manual service registration using command:

This command forces the registration of database information to the listener running on the server.

Tags:

Forcing ASMM component to shrink

#oracle #database

Here's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more

20 Mar 2019 - 2 minute read

How to extract Oracle user DDL

#database #oracle

In some cases, you need to duplicate an Oracle user, along with all it's privileges. Doing this manually can be tedious. Hopefully, we can use the data dictionary to extract the data we need and dump it into a SQL file to modify and replay. Here's how to do it: ```sql set head off set pages 0 set long 9999999 spool user_script.sql SELECT DBMS... Read more

20 Feb 2018 - 1 minute read

Restart a hung Oracle database

#oracle

On rare occasions, mostly during a high server load peak or a process failure, an Oracle instance may not accept any connection. Either from regular users as well as SYSDBA. This situation is called a **hung database** and must be quickly resolved as the database isn't accessible for your users anymore. As you cannot connect to the hung database... Read more

20 May 2017 - 1 minute read

SQL*Net more data from client

#oracle #network

The error *SQL*Net more data from client* usually happens when there's a large amount of data sent from client (or other database in case of dblinks), which doesn't fit into single SDU size Oracle packet. The server process knows that the call hasn't ended and there is more data/packets to come before the call ends. Also, the *large amount of d... Read more

20 Aug 2015 - 1 minute read

Getting the server IP Address from SQL*plus

#system #network

Here’s a small script to get the IP address of the host server directly from the SQL*plus command line. DECLARE v_host_name v$instance.host_name%type; v_ip_address varchar2(50); BEGIN SELECT host_name INTO v_host_name FROM v$instance; dbms_output.put_line('the database server name is ' || v_host_name); SELECT UTL_INADDR.GET_HOST_ADDRESS(v_host... Read more

20 Nov 2014 - less than 1 minute read

Registering with the listener