ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

It can be caused by including (connect_date) clause in (address_list) clause.

tnsping works find but sqlplus gives error: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA.

remove address_list clause or put connect_data out of address_list.

chk asm disk mapping blkid

blkid as root will give block device in the server. It will provide the label and device mapping.

unix command grep

cat file | grep pattern -A4

-A4 print 4 line after the match line
-B4 print 4 line before the match lin
-C4 print 4 line of context.

REP_51002 informatica

REP_51002 Database driver event...Error occurred loading library [libclntsh.so.10.1: cannot open shared object file: No such file or directory]Database driver event...Error occurred loading library [libpmora8.so]


This was cuased by environment setup and oracle client installation.

1. make user put $ORACLE_HOME/lib into variable LD_LIBRARY_PATH
2. check if the missing lib file (libclntsh.so.10.1) is in $ORACLE_HOME/lib
ls libclntsh.so*
if you don't have the exact name, you might have file like libclntsh.so.11.1, which is higher then the missing one.

create s symbolic
ln -s libclntsh.so.11.1 libclntsh.so.10.1

you should be able to clear the error.

oracle autotrace

set autotrace on
set autotrace off
set autotrace on statistics
set autotrace on explainplan
set autotrace on traceonly


SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default.
• SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows only the optimizer
execution path.
• SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows only the SQL statement execution statistics.
• SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
• SET AUTOTRACE TRACEONLY: This is like SET AUTOTRACE ON, but it suppresses the printingof the user’s query output, if any.


Setting Up AUTOTRACE in SQL*Plus
AUTOTRACE is a facility within SQL*Plus that shows you the explain plan of the queries you’ve
executed and the resources they used. This book makes extensive use of the AUTOTRACE
facility.
There is more than one way to get AUTOTRACE configured. This is what I like to do to get
AUTOTRACE working:
1. cd [ORACLE_HOME]/rdbms/admin
2. log into SQL*Plus as SYSTEM
3. Run @utlxplan
4. Run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
5. Run GRANT ALL ON PLAN_TABLE TO PUBLIC;
You can replace the GRANT TO PUBLIC with some user if you want. By making the PLAN_
TABLE public, you let anyone trace using SQL*Plus (not a bad thing, in my opinion). This
prevents each and every user from having to install his or her own plan table. The alternative
is for you to run @utlxplan in every schema from which you want to use AUTOTRACE.
The next step is creating and granting the PLUSTRACE role:
1. cd [ORACLE_HOME]/sqlplus/admin
2. Log in to SQL*Plus as SYS or as SYSDBA
3. Run @plustrce
4. Run GRANT PLUSTRACE TO PUBLIC;
Again, you can replace PUBLIC in the GRANT command with some user if you want.

reference:
book: Expert Oracle database architecture 9i and 10g programming technologies.

oracle easy connection

sqlplus username/password@hostname:port/sid

1.if you don't provide password, it may give you below error
ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

in linux
use sqlplus username@\"hostname:port/sid\"

2. you can provide the whole tns info for command line connection
for example
sqlplus username/password@(description = (address=(protocol = TCP)(HOST=hostname)(port = 1521))(connect_data=(server = dedicated)(service_name = sid)))

in linux, put quote on because parentheses is taken differently in shell.

sqlplus 'username/password@(description = (address=(protocol = TCP)(HOST=hostname)(port = 1521))(connect_data=(server = dedicated)(service_name = sid)))'





reference:
http://oradim.blogspot.com/2009/07/sqlplus-ezconnect-password-prompt-and.html

sudo

configure file: /etc/soduers


%admin all=(all) all

--%admin: all member of group admin
--all: all the host/IP
--(all): run as all user
--all : all commands

%user 192.168.1.0/255.255.255.0=(root) NOPASSWD:/sbin/ifconfig

--%user: all member of group user
--192.168.1.0/255.255.255.0: IP range 192.168.1.1 -- 192.168.1.254
--(root): run as user root
--NOPASSWD:/sbin/ifconfig nopassword need to run /sbin/ifconfig


reference:
http://ubuntuforums.org/showthread.php?t=1132821

shell script -- select OPTION in OPTIONLIST

it is a usefule statement for bash shell script.

select OPTION in OPTIONLIST
do
echo "You pick $OPTION of $REPLY "
break;
done

the value of your pick will be put into OPTION and option number to REPLY

ex:

#!/usr/bash

OLIST="a b c d e f g h i j k l m n o p q r s t u v w x y z"

select OPTION in $OLIST
do
echo "You pick $OPTION of $REPLY"
break;
done


output:
sh select_t.sh
1) a 4) d 7) g 10) j 13) m 16) p 19) s 22) v 25) y
2) b 5) e 8) h 11) k 14) n 17) q 20) t 23) w 26) z
3) c 6) f 9) i 12) l 15) o 18) r 21) u 24) x
#? 25
You pick y of 25


reference:
http://muffinresearch.co.uk/archives/2007/11/08/using-select-for-multiple-choices-in-shell-scripts/

compare tables metadata of two schemas

1. find different table column definition of schema1 from shcema2
select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
)

2. find difference table column definition of schema2 from schema1

select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
);

3. find difference table column definition both of schema1 from shcema2 and of schema2 from schema1

select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
)
union
select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
);


4 to run it in a sql script

accept SCHEMA1 prompt ' input schema 1 name: '
accept SCHEMA2 prompt ' input schema 2 name: '

select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
)
union
select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
);

Find missing index

We might miss some indexes when we do export and import. How could we find the missing several ones from hundreds of indexes.

We can not use index name to identify the missing ones since system generated index have different name in original and imported schemas.

We can not use table_name, index_column and column_position to identify them since a table column can be used by multiple indexes.

1. we need compare number of (table_name, index_column and column_position)

Query:
select table_name, column_name, column_position, count(*) from dba_ind_columns
where index_owner='ORIGINAL_SCHEMA' group by table_name, column_name, column_position
minus
select table_name, column_name, column_position, count(*) from dba_ind_columns
where index_owner='IMPORTED_SCHEMA' group by table_name, column_name, column_position;

EXAMPLE:

SQL> select table_name, column_name, column_position, count(*) from dba_ind_columns
2 where index_owner='ORIGINAL_SCHEMA' group by table_name, column_name, column_position
3 minus
4 select table_name, column_name, column_position, count(*) from dba_ind_columns
5 where index_owner='IMPORTED_SCHEMA' group by table_name, column_name, column_position;

TABLE_NAME COLUMN_NAME COLUMN_POSITION COUNT(*)
------------------------------ -------------------- --------------- ----------
TABLE1 ID 1 4

SQL> select table_name, column_name, column_position, count(*) from dba_ind_columns
2 where index_owner='IMPORTED_SCHEMA' group by table_name, column_name, column_position
3 minus
4 select table_name, column_name, column_position, count(*) from dba_ind_columns
5 where index_owner='ORIGINAL_SCHEMA' group by table_name, column_name, column_position;

TABLE_NAME COLUMN_NAME COLUMN_POSITION COUNT(*)
------------------------------ -------------------- --------------- ----------
TABLE1 ID 1 3


2. find exact index name
SQL> select index_name, table_name, column_name, column_position from dba_ind_columns
2 where index_owner='ORIGINAL_SCHEMA' and table_name = 'TABLE1';

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------- ---------------
SYS_C002474232 TABLE1 ID 1
IND_TABLE1_1 TABLE1 OID 1
IND_TABLE1_2 TABLE1 EDATE 1
IND_TABLE1_3 TABLE1 ID 1
IND_TABLE1_3 TABLE1 EDATE 2
IND_TABLE1_4 TABLE1 ID 1
IND_TABLE1_4 TABLE1 OID 2
IND_TABLE1_5 TABLE1 OID 1
IND_TABLE1_5 TABLE1 EFDATE 2
IND_TABLE1_6 TABLE1 ID 1
IND_TABLE1_6 TABLE1 OID 2

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------- ---------------
IND_TABLE1_6 TABLE1 EDATE 3

12 rows selected.

SQL> select index_name, table_name, column_name, column_position from dba_ind_columns
2 where index_owner='IMPORTED_SCHEMA' and table_name = 'TABLE1';

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------- ---------------
IND_TABLE1_2 TABLE1 EDATE 1
IND_TABLE1_3 TABLE1 ID 1
IND_TABLE1_3 TABLE1 EDATE 2
IND_TABLE1_4 TABLE1 ID 1
IND_TABLE1_4 TABLE1 OID 2
IND_TABLE1_5 TABLE1 OID 1
IND_TABLE1_5 TABLE1 EDATE 2
IND_TABLE1_6 TABLE1 ID 1
IND_TABLE1_6 TABLE1 OID 2
IND_TABLE1_6 TABLE1 EDATE 3
IND_TABLE1_1 TABLE1 OID 1

11 rows selected.

The missing index is SYS_C002474232

3. get index info for creation.

SQL> select index_name, index_type, uniqueness from dba_indexes where index_name ='SYS_C002474232';

INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
SYS_C002474232 NORMAL UNIQUE


4. create missing index.

create index id_table1 on imported_schema.table1 (ID);

ora-00471

this message happens with database crash.

when a background process like DBW0 terminated abnormally, PMON will terminated the database.

the root cause need be found from alert log, trace file and etc.

ora-00028

this error is for a session was killed.