LOCAL LISTENER & ORA-00119 & ORA-00132

Oracle's PMON by default checks for listeners on the default port 1521.
If any listener on port 1521 is running PMON will register the database with tnat listener.Otherwise it will try every possible port.

In order to help PMON to find LISTENER(if not running on 1521) and initialization parameter needs to be configured. The parameter is "local_listener".
If "local_listener" has any value then an entry in tnsnames.ora is needed specifying that local listener port. Please follow below:

if local_listener is set as below:

SQL> show parameter local_listener

NAME TYPE VALUE
-------------- ------- ---------------------
local_listener string LISTENER_MYDB

An entry in tnsnames.ora will be as below (PORT should be the listener port created to serve the target DB):

LISTENER_MYDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = myservername_or_ip)(PORT = 1522))

Now if init parameter local_listener is set (not null) and there is no corresponding enrtry in tnsnames.ora then below will be shown:

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_MYDB'

http://www.orafaq.com/node/30
http://www.shutdownabort.com/errors/ORA-00119.php

WFMLRSVCApp.ear file missing 11g Installation

Last week I was installing oracle 11gR2.

There were row zip files:

linux_11gR2_database_1of2.zip
linux_11gR2_database_2of2.zip

First time they were extracted in two different folder Disk1 and Diks2.
Then all the contents of one folder were copied to another folder.
Under this condition I started my installation.
After some time i got an error like

File not found .......... WFMLRSVCApp.ear

I stopped installation and tried as below:

Without unzipping into two different folders I copied those two installation zip files to a single folder and unzip them in the same folder as below:

unzip linux_11gR2_database_1of2.zip
unzip linux_11gR2_database_2of2.zip

At this time my installation was running perfectly.
thanks to link

sqlplus Permission denied!!

Last week I installed a new 11gR2 database on RHEL5.4
Installation was completed and I was about to run sqlplus for the first time.

On a terminal I typed sqlplus /nolog
but I got error like below:
error while loading shared libraries: /lib/lib.....so: cannot restore segment prot after reloc: Permission denied

The reason is that SELinux is running and it is in enforcing mode
To solve this problem please request your system admin
or if you have root password then do below:

# getenforce (to see the current mode)
Enforcing

# setenforce 0 (to change the mode to )

# getenforce (to see the current mode)
Permissive


You will find more info here

db_32K_cache_size

A few days ago I was asked to create a replica of a tablespace on production database.
OS of production was HP-UX and my test environment was Redhat 5.4(x86).
I created a database there and collect the exact tablespace creation script.
I run below tablespace creation script:

create tablespace MYTBS datafile '/data01/oradata/MYTBS/mytbs01.dbf' size 50M blocksize 32k;

and got below error:

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

I thought that above error occurred as i didn't had 32K cache configured.

I i tried to configure as below:

SQL> alter system set db_32K_cache_size=5M;
alter system set db_32K_cache_size=5M
*
ERROR at line 1: ORA-32017: failure in updating SPFILE
ORA-00382: 32768 not a valid block size, valid range [..]

Then I search for it and found that not all cache size can be configured on all system.
It is Operating System dependent. As my OS was RHEL5.1 it was not possible to create db_32K_cache_size.

Please read metalink/MOS doc id 138242.1 for details

Who is locking an Object?

Today I had to update a column of a configuration table. So I executed below query to update that column:

update admuser.cfg_control
set IMP_DIR='/system/cfg/'
where FUNCTION_ID = 121;

After waiting some time there was no result query was still going on. This is unusual, because there are only about 250 records in that table. That means, there was some one who hat locked the table. In order to find out the session locking an object I executed below query:

select *
from v$session
where sid in
( select session_id
from v$locked_object
where object_id in
( select object_id
from dba_objects
where object_name = 'CFG_CONTROL'
and owner = 'ADMUSER')
);

After that I knew that who was locking that table and I could kill the session or could tell the specific user to logout and then execute the update.
Table v$locked_object has a column locked_mode which describes in number which type of lock is on that table.

locked_mode in table v$locked_object:
0 = none
1 = null
2 = Row- Share
3 = Row- exclusive
4 = Share
5 = Share /Row- exclusive
6 = exclusive

exp using query and imp in another DB with different table & tablespace

Yesterday I was requested to export some records from production to test DB using a query. There are two options:

- Do it with and exported CSV fole and sqlloader but there were about 300 columns in that table.I dont like this plan (arranging 300 columns in control file and exporting CSV file).
- Export using emp and use "query" parameter to export.

I choose 2nd option. But thare are also some limitations:

- My source and target table have different names.
- Source & destination tablespace have different names.


After some experiment & googling I got below solutions:

- As the terget DB is a test DB then we can temporarily change the name of the target table.
- Use "ignore=y" parameter to eliminate tablespace problem.


Assume,

- Production DB name is PROD. Table name is tableA (having partition ZERO & NONEZERO) under user tst (source)

- Test DB name is TEST. Table name is tableB (having partition ZERO & NONEZERO) under user prd (target)


My export par file(exp.par) was as below:

tables=prd.tableA:NONZERO
query="where customer_id in (8, 2, 82, 994) and start_time_timestamp > to_date('24sep09235959', 'ddmonyyhh24miss')"
statistics=none
log=exp.log
file=exp.dmp


then I used below command to export:

exp prd@PROD parfile=exp.par

My impport par file(imp.par) was as below:

fromuser=prd
touser=tst
tables=tableA:NONZERO
ignore=y
log=imp.log
file=exp.dmp


Now the importent things are:

- using ignore=y in parfile and
- To change the target table name tableB to tableA

- Make a note about objects depending on tableB, may become invalid


Then,

$sqlplus tst@test

SQL>Alter table tableB rename to tableA;


Now execute below command to import from a privileged user (to import another schema's data):

imp privileged_user@TEST parfile=imp.par


Then,

sqlplus tst@test

Alter table tableA rename to tableB;


Recompile all the objects became invalid because of rename operation on table tableB in TEST DB.


done!!

CREATE VIEW FAILED with DBA Privilege

Today one of our developer came to me and informed me that he is getting below error:

ERROR at line 1:

ORA-01031: insufficient privileges

He could select the table from the user where he wanted to create the view he also informed me that the user had given DBA privilege.

I was wondering what could be the reason ?

If any user has DBA privilege it will get both SELECT ANY TABLE & CREATE ANY VIEW privileges.

So there is no way that the view creation would fail.

Then I had simulated the whole process as below:

SQL> conn / as sysdba

Connected.

SQL>

SQL> create user user1 identified by user1;

User created.

SQL> grant create session to user1;

Grant succeeded.

SQL> grant create table to user1;

Grant succeeded.

SQL> alter user user1 quota 5M on users;

User altered.

SQL> create user dbauser identified by dbauser;

User created.

SQL> conn user1

Enter password:

Connected.

SQL> create table tbl(names varchar2(10)) tablespace users;

Table created.

SQL> insert into tbl values('aaa');

1 row created.

SQL> insert into tbl values('bbb');

1 row created.

SQL> insert into tbl values('ccc');

1 row created.

SQL> select * from tbl;

NAMES

----------

aaa

bbb

ccc

SQL> conn / as sysdba

Connected.

SQL> grant dba to dbauser;

Grant succeeded.

SQL> conn dbauser

Enter password:

Connected.

SQL> select * from user1.tbl;

NAMES

----------

aaa

bbb

ccc

SQL> create view usertbl as select * from user1.tbl;

create view usertbl as select * from user1.tbl

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> conn / as sysdba

Connected.

SQL> grant select any table to dbauser;

Grant succeeded.

SQL> conn dbauser

Enter password:

Connected.

SQL> create view usertbl as select * from user1.tbl;

View created.

SQL>

(after revoking SELECT ANY TABLE, i tried with 'GRANT SELECT ON USER1.TBL TO DBAUSER' it allowed DBAUSER to create view)

Above was surprising to me. Then searching the web I found below:

"In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role. The reason is that privileges granted to roles cannot be inherited via objects."

http://stackoverflow.com/questions/735778/unable-to-create-a-simple-view-on-oracle-table

http://www.freelists.org/post/oracle-l/Why-does-this-create-view-statement-fail,3