Remote Listener

Registering Information with a Remote Listener:
A remote listener is a listener residing on one computer that redirects connections to a database instance on another computer. Remote listeners are typically used in an Oracle Real Application Clusters (Oracle RAC) environment. You can configure registration to remote listeners, such as in the case of Oracle RAC, for dedicated server or shared server environments.

Registering Information with a Remote Listener in a Dedicated Server Environment:
In a dedicated server environment, you must enable the PMON background process to register with a remote listener. You achieve this goal by configuring the REMOTE_LISTENER parameter in the initialization parameter file. The syntax of the REMOTE_LISTENER initialization parameter is as follows:

REMOTE_LISTENER=listener_alias

listener_alias is resolved to the listener protocol addresses through a naming method such as a tnsnames.ora file on the database host.

To dynamically update the REMOTE_LISTENER initialization parameter, use the SQL statement ALTER SYSTEM SET. If you set the parameter to null with the statement that follows, then PMON de-registers information with the remote listener with which it had previously registered information, as in the following example:

ALTER SYSTEM SET REMOTE_LISTENER=''

To register information with a remote listener in a dedicated server environment:
On the host where the remote listener resides, configure the listener.ora file with the protocol addresses of the remote listener.
For example, assume that a remote listener listens on port 1521 on host sales2-server.
On the database to which you want requests to be redirected, set the REMOTE_LISTENER parameter in the database initialization parameter file to the alias of the remote listener.

For example, suppose that a database resides on host sales1-server. To redirect requests to the database on sales1-server, you can set the REMOTE_LISTENER parameter in the initialization file for the database on host sales1-server as follows:

REMOTE_LISTENER=listener_sales2

Resolve the listener name alias for the REMOTE_LISTENER setting through a tnsnames.ora file on the database host.

For example, in the tnsnames.ora on sales1-server, you can resolve the remote listener alias listener_sales2 as follows:

listener_sales2=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))
)

Registering Information with a Remote Listener in a Shared Server Environment:
In a shared server environment, you can use the same registration technique as for a dedicated server environment. Alternatively, you can set the LISTENER attribute of the DISPATCHERS parameter in the initialization parameter file to register the dispatchers with any listener.

The LISTENER attribute overrides the REMOTE_LISTENER parameter. Because the REMOTE_LISTENER parameter and the LISTENER attribute enable PMON to register dispatcher information with the listener, you need not specify both the parameter and the attribute if the listener values are the same.

The syntax of the LISTENER attribute is as follows:
DISPATCHERS="(PROTOCOL=tcp)(LISTENER=listener_alias)"

To register information with a remote listener in a shared server environment: On the host where the remote listener resides, configure the listener.ora file with the protocol addresses of the remote listener.

For example, assume that a remote listener listens on port 1521 on host sales2-server.
On the database to which you want requests to be redirected, configure the LISTENER attribute of the DISPATCHERS parameter.

For example, suppose that a database resides on host sales1-server. To redirect requests to the database on sales1-server, set the DISPATCHER parameter in the initialization file for the database on host sales1-server as follows:
DISPATCHERS="(PROTOCOL=tcp)(LISTENER=listeners_sales2)"

Resolve the listener name alias for the LISTENER attribute through a tnsnames.ora file on the database host.

For example, in the tnsnames.ora on sales1-server, you can resolve the remote listener alias listener_sales2 as follows:
listeners_sales2=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))
)

'ORA-12547: TNS: lost contact' messages after chanign kernel.shmax parameter

OTN Discussion Forums : Repeated 'ORA-12547: TNS: lost contact' ...: "'ORA-12547: TNS: lost contact' messages after"

Truncate Table

create or replace PROCEDURE truncate_table (v_tab_name in VARCHAR2 ) is

/*internal procedure to truncate table */

CURSOR rcons_cur(pconsname IN VARCHAR2) IS

(SELECT *

FROM user_cons_columns

WHERE (constraint_name,table_name) IN (

(SELECT constraint_name, table_name

FROM user_constraints

WHERE constraint_type = 'R'

AND r_constraint_name = pconsname))

);

/*type to store list of tables and foreign keys */

TYPE cons_col_tab_type IS TABLE OF user_cons_columns%ROWTYPE;

TabColList cons_col_tab_type;

exec_sql VARCHAR2(2000);

BEGIN

/*select all constraints referenced by this table */

FOR pcons_rec IN

(SELECT constraint_name,column_name

FROM user_cons_columns

WHERE TABLE_NAME = v_tab_name

AND constraint_name =

(SELECT constraint_name

FROM user_constraints

WHERE TABLE_NAME = v_tab_name

AND constraint_type = 'P')

and rownum =1 )

LOOP

/* find all tables that references current table */

OPEN rcons_cur (pcons_rec.constraint_name);

FETCH rcons_cur bulk collect INTO TabColList;

CLOSE rcons_cur;

/* enumerate table of constraints */

/*check if collection has elements */

if TabColList.exists(1) then

for i in TabColList.first .. TabColList.last

loop

/*truncate child table first*/

exec_sql := 'ALTER TABLE '||TabColList(i).TABLE_NAME ||' DISABLE CONSTRAINT '||TabColList(i).CONSTRAINT_NAME;

dbms_output.put_line(exec_sql);

execute immediate(exec_sql);

end loop;

/*Truncate table */

exec_sql := 'TRUNCATE TABLE ' ||v_tab_name;

dbms_output.put_line(exec_sql);

execute immediate(exec_sql);

for i in TabColList.first .. TabColList.last

loop

begin

exec_sql := 'ALTER TABLE '||TabColList(i).TABLE_NAME ||' ENABLE CONSTRAINT '||TabColList(i).CONSTRAINT_NAME;

execute immediate(exec_sql);

dbms_output.put_line(exec_sql);

exception when others then

dbms_output.put_line(' could not execute This code ' ||exec_sql);

end;

end loop;

else

exec_sql := 'TRUNCATE TABLE ' ||v_tab_name;

dbms_output.put_line(exec_sql);

execute immediate(exec_sql);

end if;

END LOOP;

END ; -- procedure

Loss of SPFILE 11g

Easier Recovery from Loss of SPFILE, 11g new feature [ID 464781.1]

about 7 hours ago from web

With Oracle 11.1.0.7 or above, you can now 'CREATE pfile/spfile from MEMORY'. Read on MOS [ID 784133.1].

about 7 hours ago from web

Health Check on the Database

How to Perform a Health Check on the Database [ID 122669.1] Applicable from Oracle v7 to v11.

about 7 hours ago from web

ORA-4031 Error

Troubleshooting and Diagnosing ORA-4031 Error [Video] [ID 396940.1]

about 7 hours ago from web

https://support.oracle.com/CSP/main/article?cmd=show&type=ATT&id=396940.1:FAQVIDEO&inline=1

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

If you see the following error when trying to restore your Oracle backups (through RMAN):

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2

the cause is possibly your are trying to restore your database to an NFS mount point which is not mounted correctly.

The following is for Solaris 10:

Change your /etc/vfstab entry

From:

Nfs_Host:/nfs/oracle – /oradata nfs – yes -

To:

Nfs_Host:/nfs/oracle – /oradata nfs – yes rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,proto=tcp,suid

Then, unmount and re-mount your volumes through /etc/vfstab

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=781349.1

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=359515.1