2008 June « My confrontations with oracle

June 30, 2008

ORA-12541: TNS:no listener ??

I created a new RAC database. It was on 10.2.0.3 and the database was in shared server mode and had 2 nodes. I created a service name ‘test’ and the listener and the instances were up and running on both the nodes.

I had the below in my tnsnames.ora on my PC.
rac_dev =
(DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST=3.208.169.202)(PORT= 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST=3.208.169.206)(PORT= 1521))
     (LOAD_BALANCE = no)
     (CONNECT_DATA =
        (SERVICE_NAME = test)
        (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
     )
)
I could tnsping using the tnsalias.

C:\WINNT>tnsping rac_dev

Used parameter files:D:\Oracle10gAS_Home\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=3.208.169.202)(PORT= 1521)) (ADDRESS = (PROTOCOL = TCP)
(HOST=3.208.169.206)(PORT= 1521)) (LOAD_BALANCE = yes) (FAILOVER=ON) (CONNECT_DATA = (SERVICE_NAME = test)
(SERVER = DEDICATED) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))
OK (40 msec)

C:\WINNT>
Then i tried to connect using sqlplus

C:\Documents and Settings\Administrator>sqlplus user/pwd@rac_dev
SQL*Plus: Release 10.1.0.5.0 – Production on 3 18:17:26 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-12541: TNS:no listener
Oops!!.
I googled for “ORA-12541″ and found many scenarios and i checked all.
But still it didn’t take me anywhere.

Then i traced my sql*plus client by adding the below to the sqlnet.ora file on my PC

TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = client
TRACE_DIRECTORY_CLIENT = <any valid DIRECTORY path>
TRACE_TIMESTAMP_CLIENT = ON

and tried sqlplus user/pwd@rac_de and got the tracefiles generated.vv

Halfway down inside the tracefiles i found

Few more scrolls down and i found

But here the port is different. Its 32886.
Down again i found

[07-11ŒŽ-2015 12:12:47:156] nscall: connecting…
[07-11ŒŽ-2015 12:12:47:156] nsc2addr: entry
[07-11ŒŽ-2015 12:12:47:156] nsc2addr: (ADDRESS=(PROTOCOL=tcp)(HOST=3.208.169.202)(PORT=32886))
[07-11ŒŽ-2015 12:12:47:156] nttbnd2addr: entry
[07-11ŒŽ-2015 12:12:47:156] nttgetport: entry
[07-11ŒŽ-2015 12:12:47:156] nttgetport: port resolved to 32886
[07-11ŒŽ-2015 12:12:47:156] nttgetport: exit
[07-11ŒŽ-2015 12:12:47:156] nttbnd2addr: using host IP address: 3.208.169.202

and found that the client was trying to connect to port 32886 on 3.208.169.202 (when i had mentioned 1521 in the tnsentry).

But Why?
If the database is in SHARED MODE, all connections that does not have “SERVER=DEDICATED” in the tnsentry will try to make a shared connection.

In my case, the dispatchers were listening on ports between 32000 and 33000 and the firewall was not opened to these ports but only to 1521.

So, while the connection was initiated to port 1521, the listener when found my database in SHARED SERVER MODE and since i didnt have SERVER=DEDICATED in the tnsentry, it was redirected it to port 32886 to use the dispatchers and since the port 32886 was blocked by the firewall sql*plus client on my PC failed to establish the connection and resulted in “ORA-12541: TNS:no listener”

Then i changed my tnsentry to
rac_dev =
(DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST=3.208.169.202)(PORT= 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST=3.208.169.206)(PORT= 1521))
     (LOAD_BALANCE = yes) (FAILOVER=ON)
     (CONNECT_DATA =
     (SERVICE_NAME = test)
     (SERVER=DEDICATED)
        (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
     )
)

Comments (3)

June 24, 2008

Hardening Oracle Security ??

Filed under: oracle — John Jacob @ 2:51 pm
Tags: oracle security

The other day i found this site which has listed most of the things that should be done(from security point of view) after an Oracle Installation.

Comments (1)

June 10, 2008

Who is referencing my table ??

Filed under: oracle — John Jacob @ 1:28 pm
Tags: oracle, oracle foreign key constraint

Today my developers were puzzled at ORA-02449 while trying to drop a table.

$ oerr ora 02449
02449, 00000, “unique/primary keys in table referenced by foreign keys”
// *Cause: An attempt was made to drop a table with unique or
// primary keys referenced by foreign keys
// *Action: Before performing the above operations the table, drop the
// foreign key constraints in other tables. You can see what
// constraints are referencing a table by issuing the following
// command:
// SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = “tabnam”;

They thought that they could drop the parent table if the child tables are empty and it did not work.
Jeniffer in her page has mentioned on how to go about when the question ‘who is referencing my table?’ comes to your mind.

select 'alter table '||a.owner||'.'||a.table_name||
 ' disable constraint '||a.constraint_name||';'
 from all_constraints a, all_constraints b
 where a.constraint_type = 'R'
 and a.r_constraint_name = b.constraint_name
 and a.r_owner  = b.owner
 and b.table_name = '<table to be dropped>';

Finally they had to drop the Foreign Keys of the Child tables before dropping the parent table.

Comments (1)

June 5, 2008

Table Compression in Oracle

Filed under: oracle — John Jacob @ 5:15 pm
Tags: alter table table_name compress, compression, Table Compression in Oracle

Recently i had to move a schema of size 70GB to another database[Oracle 10.2.0.3]. That was a readonly schema and never had any DML on any of the tables. In the new database i did compress the tables and the size was brought down from 70Gb to 22Gb.

1]Divided the tables based on the size.
2]Created the tablesapces.
3]Pre-created the tables on respective tablespaces.
4]Imported the tables.
5]Compressed and moved big tables from APP_BIG_TABLES_TEMP to APP_BIG_TABLES.
6]Rebuild the primary key indexes of the moved tables.
7]Droped APP_BIG_TABLES_TEMP.
8]Created the indexes.

The tables where create with PCTFREE 0 to take maximum advantage of the space in each extents as i was sure that never an UPDATE would happen on any of these tables.

Compress statement with “move”
======================
ALTER TABLE <table_name> MOVE TABLESPACE <to new tablespace> COMPRESS NOLOGGING PARALLEL (degree N);
eg: ALTER TABLE SALES_INFORMATION MOVE TABLESPACE APP_BIG_TABLES COMPRESS NOLOGGING PARALLEL (degree 8);

COMPRESS alone will only compress the tables, to recalim the space use MOVE in the statement.
My Server had 32 CPUs, but i just used 8. I should leave the rest for other applications.

To move tables with LOB segments:
ALTER TABLE <TABLE_NAME> MOVE TABLESPACE <new_tablespace> LOB(<lob_column_1) STORE AS (TABLESPACE <new_tablespace) LOB(<lob_column_2) STORE AS (TABLESPACE <new_tablespace);

Why did not i compress and move the table in the same tablespace?
COMPRESS and MOVE of tables in the same tablespace will not bring down the datafiles high water mark and i will not be able to resize the datafiles to a smaller size. So i found it easy to drop the very big APP_BIG_TABLES_TEMP once the tables were compressed and moved to APP_BIG_TABLES.

BEFORE COMPRESS
================
OBJECT_TYPE COUNT SIZE(in GB)
—————— ———- ———-
INDEX 110 7.73681641
TABLE 55 62.6555786
———-
sum 70.392395
AFTER COMPRESS
===============
OBJECT_TYPE COUNT SIZE(in GB)
—————— ———- ———-
INDEX 110 2.375
TABLE 55 19.8398438
———-
sum 22.2148438

Comments (2)

June 2, 2008

Oracle Recovery Scenarios

Filed under: oracle — John Jacob @ 2:28 am
Tags: oracle datafile missing, oracle recovery scenarios, recover datafile

The other day i found this Click Me, a document by Alejandro Vargas about the Oracle recovery scenarios. This would be useful for beginners.

Click for more Recovery Scenarios from University of Arkansas

Comments (1)

My confrontations with oracle

June 30, 2008

ORA-12541: TNS:no listener ??

June 24, 2008

Hardening Oracle Security ??

June 10, 2008

Who is referencing my table ??

June 5, 2008

Table Compression in Oracle

June 2, 2008

Oracle Recovery Scenarios

Archives

Blog Stats

Meta

Recent Posts

Blogroll

My Visitors

June 2008
S	M	T	W	T	F	S
« May				Jul »
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30