Oracle Block recovey using RMAN

RMAN Block RECOVERY
perform Block Media Recovery using RMAN
ORA-01578: ORACLE data block corrupted
RMAN RECOVER BLOCK


SQL> select FILE_NAME from dba_data_files ;

FILE_NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/orcl/users01.dbf
/opt/app/oracle/oradata/orcl/undotbs01.dbf
/opt/app/oracle/oradata/orcl/sysaux01.dbf
/opt/app/oracle/oradata/orcl/system01.dbf
/opt/app/oracle/oradata/orcl/example01.dbf



SQL> create tablespace anujtest datafile '/opt/app/oracle/oradata/orcl/anujtest.dbf' size 10m
autoextend on next 5m extent management local segment space management auto ;

Tablespace created.


apt-lnxtst-01:ORCL\sys> create user anujtest identified by anujtest default tablespace anujtest quota unlimited on anujtest ;

User created.



grant connect , resource to anujtest ;


apt-lnxtst-01:ORCL\sys> conn anujtest/anujtest
Connected.


orcl\anujtest> connect / as sysdba
Connected.



apt-lnxtst-01:ORCL\sys>conn anujtest/anujtest
Connected.

orcl\anujtest> create table test as select * from all_objects ;
Table created.



SQL> connect / as sysdba
Connected.
SQL> col FILE_NAME format a50
SQL> select FILE_NAME,FILE_ID from dba_data_files;

FILE_NAME FILE_ID
-------------------------------------------------- ----------
/opt/app/oracle/oradata/orcl/users01.dbf 4
/opt/app/oracle/oradata/orcl/undotbs01.dbf 3
/opt/app/oracle/oradata/orcl/sysaux01.dbf 2
/opt/app/oracle/oradata/orcl/system01.dbf 1
/opt/app/oracle/oradata/orcl/example01.dbf 5
/opt/app/oracle/oradata/orcl/anujtest.dbf 6 <<<<<<<<<<<<-------

6 rows selected.


RMAN> connect target /

connected to target database: ORCL (DBID=1267852645)

RMAN> backup datafile 6 tag=good;

Starting backup at 29-APR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/opt/app/oracle/oradata/orcl/anujtest.dbf
channel ORA_DISK_1: starting piece 1 at 29-APR-11
channel ORA_DISK_1: finished piece 1 at 29-APR-11
piece handle=/opt/app/oracle/flash_recovery_area/ORCL/backupset/2011_04_29/o1_mf_nnndf_GOOD_6vnsol06_.bkp tag=GOOD comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-APR-11



RMAN> list backup;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 7.48M DISK 00:00:01 29-APR-11
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: GOOD
Piece Name: /opt/app/oracle/flash_recovery_area/ORCL/backupset/2011_04_29/o1_mf_nnndf_GOOD_6vnsol06_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 7522958 29-APR-11 /opt/app/oracle/oradata/orcl/anujtest.dbf


RMAN>Ctrl+d or exit

Recovery Manager complete.


SQL> col SEGMENT_NAME format a20



SQL> r
1 select segment_name , header_file , header_block from dba_segments
2 where segment_name = 'TEST'
3* and owner = 'ANUJTEST'

SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
TEST 6 130



to get file name



col name format a70
select a.name from v$datafile a , dba_segments b
where a.file#=b.header_file and b.segment_name='TEST'

NAME
----------------------------------------------------------------------
/opt/app/oracle/oradata/orcl/anujtest.dbf



The header of the table is in block 130, so if the block 131 or so are corrupted we will get an error when we try to select data from the table.
Lets corrupt block 12 using the "dd" command in Linux.



oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> dd of=anujtest.dbf bs=8192 conv=notrunc seek=130 << EOF
anuj Corrupted.
EOF

0+1 records in
0+1 records out
16 bytes (16 B) copied, 0.000109246 s, 146 kB/s


SQL> select count(*) from anujtest.test ; ----- because whole table in cache

COUNT(*)
----------
55632

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from anujtest.test ;
select count(*) from anujtest.test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/opt/app/oracle/oradata/orcl/anujtest.dbf'



oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 29 07:45:30 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /

connected to target database: ORCL (DBID=1267852645)

RMAN> BLOCKRECOVER DATAFILE 6 BLOCK 130;

Starting recover at 29-APR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/ORCL/backupset/2011_04_29/o1_mf_nnndf_GOOD_6vnsol06_.bkp
channel ORA_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/ORCL/backupset/2011_04_29/o1_mf_nnndf_GOOD_6vnsol06_.bkp tag=GOOD
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:08

Finished recover at 29-APR-11

RMAN>


now block recovered


SQL> select count(*) from anujtest.test ;

COUNT(*)
----------
55632

ORA-19809: limit exceeded for recovery files

if following error occurred on Oracle database or database hanged due to this error


Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc3_1202.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 44131328 bytes disk space from 4070572032 limit
ARC3: Error 19809 Creating archive log file to '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_%u_.arc'




check space is fill !!!!!!!!!

set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/



oracle@apt-amd-02:~> id
uid=1001(oracle) gid=1000(oinstall) groups=1000(oinstall)
oracle@apt-amd-02:~> rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 29 06:56:30 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


RMAN> connect target /

connected to target database: ORCL (DBID=1267852645)

RMAN> delete force archivelog until time 'sysdate -1'; ---- delete some archive log file

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
91 1 330 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_6vnqzs5c_.arc

92 1 331 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_331_6vnr0q87_.arc

93 1 332 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_332_6vnr0x7c_.arc


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_6vnqzs5c_.arc RECID=91 STAMP=749717522
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_331_6vnr0q87_.arc RECID=92 STAMP=749717587
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_332_6vnr0x7c_.arc RECID=93 STAMP=749717596
Deleted 3 objects


check all the file deleted ....

RMAN> delete force archivelog until time 'sysdate -1';

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
specification does not match any archived log in the repository

Unix Delete files by inode number

How to delete file using inode number?
Remove a file on linux using the inode number
remove a file with funny characters
Delete files by inode number

#pwd
/tmp

#ls -lia ----- i for inode

30401152 -rw-r--r-- 1 root root 0 2011-04-21 09:24 "la*
59544366 drwx------ 2 anujs users 6 2011-04-21 09:27 plugtmp-33


30401152 is inode number .

find /tmp -inum 30401152 -exec rm -i {} \;

or
apt-amd-02:/tmp # rm '"la*'

Oracle memory leak info

How to check if oracle instance is leaking memory

Oracle memory leak info


col name format a20
select sid, substr(name,1,30) name , value , value/(1024*1024) "MB"
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and name like '%a memory'
order by sid, name;

set linesize 200
col name format a20
col MACHINE format a15
col PROGRAM format a10
col MODULE format a10
col USERNAME format a10
col SCHEMANAME format a10
col OSUSER format a10

select b.sid, substr(name,1,30) name, value , value/(1024*1024) "MB", c.serial#,c.status,c.username,c.schemaname,c.osuser,c.machine,
-- c.terminal,
c.program,c.module,state,logon_time
from v$statname a, v$sesstat b,v$session c
where a.statistic# = b.statistic#
and name like '%a memory'
and b.sid=c.sid
and osuser!='oracle'
order by status,MB desc,sid, name;




set linesize 200
col name format a20
col MACHINE format a15
col PROGRAM format a10
col MODULE format a10
col USERNAME format a10
col SCHEMANAME format a10
col OSUSER format a10

select b.sid, substr(name,1,30), value , value/(1024*1024) "MB",s.sid ssid, s.serial#,
-- ,s.sid ssid,
s.status,s.username,s.schemaname,s.osuser,s.machine,
-- s.terminal,
--s.program,
s.module,state,logon_time,substr(p.spid,1,8) spid,
--substr(sa.sql_text,1,2000) txt
sa.sql_id
from v$statname a, v$sesstat b,v$session s, v$process p ,v$sqlarea sa
where a.statistic# = b.statistic#
and name like '%a memory'
and b.sid=s.sid
and osuser!='oracle'
and p.addr = s.paddr
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
order by status,MB desc,sid, name;


set long 4000
select sql_text from v$sqlarea where sql_id='&1'

oracle start up shutdown script on linux / unix

Oracle start up automatic script

Oracle start up shutdown script

Oracle start up automatically at the time of boot

Oracle Database start automatic on Linux

Automating Database Start up and Shutdown on Linux


1. Change in the oratab file from N to Y


oracle@apt-lnxtst-01:/opt/oracle/product/10.2/bin> cat /etc/oratab





# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/opt/oracle/product/10.2:N <<----- change N to Y





apt-lnxtst-01:/etc/init.d # ls -ltr /etc/oratab
-rw-rw-r-- 1 oracle root 710 2011-04-20 08:38 /etc/oratab




2.

as a root

cd /etc/init.d/

apt-lnxtst-01:/etc/init.d # cat dbora ----- create this file as a root

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.


# change the value as per your home -------------<<<<<<<<<<<
ORA_HOME=/opt/oracle/product/10.2

ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi

case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
touch /var/lock/subsys/dbora
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
rm -f /var/lock/subsys/dbora
;;
esac



apt-lnxtst-01:/etc/init.d # ls -ltr dbora
-rw-r--r-- 1 root root 1045 2011-04-20 08:49 dbora


3 .

Change the prmission of this file

apt-lnxtst-01:/etc/init.d # chmod 750 /etc/init.d/dbora



4.

apt-lnxtst-01:/etc/init.d # chkconfig --add dbora
dbora 0:off 1:off 2:off 3:on 4:off 5:on 6:off

or


Linux

# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

rpm info

Software Management with RPM
Quick Tips

rpm
-qa <-- list all packages (a)
-qi < --list package information (i)
-ql <-- list package files (l)
-qf <-- list package that owns the file (ex. rpm -qf /etc/inittab)
-V <-- file Verification
-ivh <-- new install
-Uvh <-- Update -F <--Freshing. Package willl be updated ONLY if already installed else ignored.
-E < --Erase

Unix Cron format

Cron format - crontab

crontab -l


1. minute (from 0 to 59)
2. hour (from 0 to 23)
3. day of month (from 1 to 31)
4. month (from 1 to 12)
5. day of week (from 0 to 6) (0=Sunday)


1  2  3  4  5  6
*  *  *  *  *  *
|  |  |  |  |  | 
|  |  |  |  |  +-- Year              (range: 1900-3000)
|  |  |  |  |
|  |  |  |  +---- Day of the Week    (range: 1-7, 1 standing for Monday)
|  |  |  |
|  |  |  +------ Month of the Year   (range: 1-12)
|  |  |
|  |  +-------- Day of the Month     (range: 1-31)
|  |
|  +---------- Hour                  (range: 0-23)
|
+------------ Minute                 (range: 0-59)



* * * * * /xxx/xxx/xx/anuj.sh


if 5 stars then



1. every minute
2. of every hour
3. of every day of the month
4. of every month
5. and every day in the week.



Execute every Friday 1AM

So if we want to schedule script to run at 1AM every Friday,


0 1 * * 5 /xxx/xxx/xx/anuj.sh

The script is now being executed when the system clock hits:

1. minute: 0
2. of hour: 1
3. of day of month: * (every day of month)
4. of month: * (every month)
5. and weekday: 5 (=Friday)
=================================================================

##┌───────────── minute (0 - 59)
# │ ┌───────────── hour (0 - 23)
# │ │ ┌───────────── day of month (1 - 31)
# │ │ │ ┌───────────── month (1 - 12)
##┌───────────── minute (0 - 59)
# │ ┌───────────── hour (0 - 23)
# │ │ ┌───────────── day of month (1 - 31)
# │ │ │ ┌───────────── month (1 - 12)
# │ │ │ │ ┌───────────── day of week (0 - 6) (Sunday to Saturday;
# │ │ │ │ │                                       7 is also Sunday)
# │ │ │ │ │
# │ │ │ │ │
# * * * * *  command to execute

======================================================================

Execute on Mon to friday


So if we want to schedule the script to Monday till Friday at 1 AM


0 1 * * 1-5 /bin/execute/this/script.sh

The script is now being executed when the system clock hits:

1. minute: 0
2. of hour: 1
3. of day of month: * (every day of month)
4. of month: * (every month)
5. and weekday: 1-5 (=Monday til Friday)



# Minute   Hour   Day of Month       Month          Day of Week        Command    
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)                
    0        2          12             *                *            /usr/bin/find




Examples:

*/30 * * * * /aptus/oracle/admin/aptdb/script/check_oracle_tablespace.sh -c aptdb -w 90 -c95 > /tmp/tablespace.nagios

5 5 * * * /aptus/oracle/admin/aptdb/script/scheduler.sh > /dev/null 2>&1

# 5 6 * * * /aptus/oracle/admin/aptdb/script/exp_solomon.sh > /dev/null 2>&1



Standard output (STDOUT) and standard errors (STDERR).

STDOUT is marked 1, STDERR is marked 2.

So the following statement tells Linux to store STDERR in STDOUT as well, creating one datastream for messages & errors:

2>&1


every hr ....


***-amd-02:/home/anujs/Downloads # crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.XXXXwcdEfa installed on Tue Sep 7 10:10:07 2010)
# (Cron version V5.0 -- $Id: crontab.c,v 1.12 2004/01/23 18:56:42 vixie Exp $)

59 * * * * echo " ">/var/log/warn > /dev/null 2>&1

59 * * * * echo " ">/var/log/messages > /dev/null 2>&1



cron and email confirmation .....

15 22 * * * /xxxx/oracle/admin/xxdb/scripts/Rmanbackup.sh 2>&1 | mail -s "RMAN BACKUP On (xxx-xxx-01) completed... " anuj.singh@xxxx.co.uk

RMAN Backup Report for SCN for Recovery

oracle@novagenesis$ rman target / log=rmanbackupinfo.log
RMAN> list backup ;

RMAN> list backup;

RMAN>exit;


cat rmanbackupinfo.log this will give all the SCN no of and valid backup info


This will provide last SCN number

SQL> set numwidth 18
SQL>select max(CHECKPOINT_CHANGE#) from v$backup_datafile;

MAX(CHECKPOINT_CHANGE#)
-----------------------
121937781

unix kill all process

unix kill process
awk kill


hosea:~ # ps -ef | grep cron | awk '{print $2}'|wc -l
3342

kill -9 `ps -ef | grep cron |grep -v grep | awk '{print $2}'`

Oracle ldap clear entry Flush LDAP Cache

Flushing LDAP Cache information for group
Flushing LDAP Cache information for passwd


uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall),6002(dba)
jtime76@apt-lnxtst-01:~> logout
apt-lnxtst-01:~ # nscd -i passwd
apt-lnxtst-01:~ # su - jtime76
jtime76@apt-lnxtst-01:~> id
uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall),6002(dba)
jtime76@apt-lnxtst-01:~> logout
apt-lnxtst-01:~ # nscd -i passwd
apt-lnxtst-01:~ # vi /etc/nscd.conf
apt-lnxtst-01:~ # /etc/init.d/nscd stop
Shutting down Name Service Cache Daemon done
apt-lnxtst-01:~ # /etc/init.d/nscd start
Starting Name Service Cache Daemon done

changed the value in ldap browser ( removed the group from



/ldapbrowser> ./lbe.sh








apt-lnxtst-01:~ # su - jtime76
jtime76@apt-lnxtst-01:~> id
uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall),6002(dba) ---- dba group is still there
jtime76@apt-lnxtst-01:~> logout
apt-lnxtst-01:~ # nscd -i group -------- then flush the group information using this command
apt-lnxtst-01:~ # nscd -i passwd -------- if you want to flush user information
apt-lnxtst-01:~ # su - jtime76
jtime76@apt-lnxtst-01:~> id
uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall)

sqlplus / as sysdba is hanged

sqlplus / as sysdba is not working


apt-lnxtst-01:~ # /etc/init.d/nscd stop
Shutting down Name Service Cache Daemon done



sqlplus / as sysdba will hang , if nscd service is not running on your server
( if you are using ldap )


apt-lnxtst-01:~ # su - oracle
oracle@apt-lnxtst-01:~> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 12 12:37:46 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

----------------this screen will stay like this --------------------------
--- hanged hanged hanged hanged hanged hanged hanged hanged hanged hanged hanged ----------


apt-lnxtst-01:/aptus/www/jtime76 # /etc/init.d/nscd start
Starting Name Service Cache Daemon done



now this will work

open a new terminal then

# su - oracle


oracle@apt-lnxtst-01:~> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 12 12:41:57 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

How to run SQLplus as different user non oracle user on unix

How to run SQLplus on non oracle user ?



jtime76@apt-lnxtst-01:~> cat .bashrc
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH:.
export LIBXCB_ALLOW_SLOPPY_LOCK=1
export TZ=GMT
# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib32
export LD_LIBRARY_PATH=$ORACLE_HOME/lib



jtime76@apt-lnxtst-01:~> ls -ltr /opt/oracle/product/10.2
total 58
-rwxr-xr-x 1 oracle oinstall 0 2005-06-07 17:50 root.sh.old
drwxr-x--- 3 oracle oinstall 72 2010-09-09 09:39 wwg



so add user name in the group . My user jtime

cat /etc/group
uucp:x:14:
wheel:x:10:
www:x:8:
xok:x:41:
users:x:100:
oinstall:!:6001:boadmin,jtime <<<<<<<<<<<<----
boadmin:!:6003:
mysql:!:106:
zimbra:!:6004:
postdrop:!:6005:
mailman:!:67:
sophosav:!:6006:
ldap:!:70:
winbind:!:161:
named:!:44:
jtime79:!:6007:



jtime76@apt-lnxtst-01:~> env|grep ora
LD_LIBRARY_PATH=/opt/oracle/product/10.2/lib
ORACLE_BASE=/opt/oracle
ORA_NLS32=/opt/oracle/product/10.2/nls/data
ORACLE_HOME=/opt/oracle/product/10.2

Anuj's glogin.sql file

 

location of file is cd $ORACLE_HOME/sqlplus/admin

Oracle glogin.sql file

glogin file ......

set verify off termout off head off feed off

col add new_value welcome

select UTL_INADDR.GET_HOST_NAME||':'||UTL_INADDR.GET_HOST_ADDRESS "add" from dual ;

column global_name new_value gname

set termout off

select UTL_INADDR.GET_HOST_NAME||'@'||lower(SYS_CONTEXT('USERENV','CURRENT_USER'))||'-Sql>' global_name from dual ;

set sqlprompt 'gname'

set verify on termout on head on feed on

prompt ************************************

prompt WELCOME TO &&welcome

prompt ************************************

prompt

set echo off serveroutput on size 100000 line 100 trims on

==============================================================================================

or 

cat glogin.sql

--

-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.

--

-- NAME

--   glogin.sql

--

-- DESCRIPTION

--   SQL*Plus global login "site profile" file

--

--   Add any SQL*Plus commands here that are to be executed when a

--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.

--

-- USAGE

--   This script is automatically run

--

set termout off

set long 5000

set pagesize 9999  linesize 180  longchunksize 180  arraysize 100  num 10

define gname=idle

column global_name new_value gname

--select lower(user) || '@' || sys_context('USERENV','INSTANCE_NAME') global_name from dual;

select lower(sys_context('USERENV','DATABASE_ROLE'))||':'||lower(user) || '@' || sys_context('USERENV','INSTANCE_NAME') global_name from dual;

select lower(sys_context('USERENV','DATABASE_ROLE'))||':'||lower(user) || '@' || sys_context('USERENV','DB_UNIQUE_NAME') || '-' ||sys_context('USERENV','INSTANCE_NAME') global_name from dual;

set sqlprompt '&gname sqlplus> '

-- host title sqlplus connected to &gname

alter session set nls_date_format='dd-mm-yy hh24:mi';

set tab off

set termout on

===========================

Output .. 

primary:sys@rac-rac2 sqlplus>

Install Oracle Instant Client for unix / Linux x86-64

Oracle Instant Client installation on unix 64 bit

Install Oracle thin client



Download following rpm from oracle website

oracle-instantclient11.2-basic-11.2.0.2.0.x86_64.rpm
oracle-instantclient11.2-devel-11.2.0.2.0.x86_64.rpm
oracle-instantclient11.2-sqlplus-11.2.0.2.0.x86_64.rpm


Instant Client Downloads site

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html



apt-amd-02:/home/anujs/Downloads # rpm -ivh oracle-instantclient11.2-basic-11.2.0.2.0.x86_64.rpm
Preparing... ########################################### [100%]
1:oracle-instantclient11.########################################### [100%]


apt-amd-02:/usr # rpm -ql oracle-instantclient11.2-basic
/usr/lib/oracle/11.2/client64/bin/adrci
/usr/lib/oracle/11.2/client64/bin/genezi
/usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1
/usr/lib/oracle/11.2/client64/lib/libnnz11.so
/usr/lib/oracle/11.2/client64/lib/libocci.so.11.1
/usr/lib/oracle/11.2/client64/lib/libociei.so
/usr/lib/oracle/11.2/client64/lib/libocijdbc11.so
/usr/lib/oracle/11.2/client64/lib/ojdbc5.jar
/usr/lib/oracle/11.2/client64/lib/ojdbc6.jar
/usr/lib/oracle/11.2/client64/lib/xstreams.jar



apt-amd-02:/home/anujs/Downloads # rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.2.0.x86_64.rpm
Preparing... ########################################### [100%]
1:oracle-instantclient11.########################################### [100%]
apt-amd-02:/home/anujs/Downloads # rpm -ql oracle-instantclient11.2-sqlplus
/usr/bin/sqlplus64
/usr/lib/oracle/11.2/client64/bin/sqlplus
/usr/lib/oracle/11.2/client64/lib/glogin.sql
/usr/lib/oracle/11.2/client64/lib/libsqlplus.so
/usr/lib/oracle/11.2/client64/lib/libsqlplusic.so


when you install following rpm you will get following error ....

apt-amd-02:/home/anujs/Downloads # rpm -ivh oracle-instantclient-devel-10.2.0.4-1.x86_64.rpm
error: Failed dependencies:
oracle-instantclient-basic >= 10.2.0.4 is needed by oracle-instantclient-devel-10.2.0.4-1.x86_64




So use --nodeps for this rpm


apt-amd-02:/home/anujs/Downloads # rpm -ivh oracle-instantclient-devel-11.1.0.1-1.x86_64.rpm --nodeps
Preparing... ########################################### [100%]
1:oracle-instantclient-de########################################### [100%]



Create tnsnames.ora file in /etc


apt-amd-02:/usr/lib/oracle/11.2/client64/bin # cat /etc/tnsnames.ora
anujc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
)
(CONNECT_DATA =
(sid = orcl)
)
)


set following variable or add into .bash_profile file

export TNS_ADMIN=/etc
export ORACLE_HOME=/usr/lib/oracle/11.2
export LD_LIBRARY_PATH=$ORACLE_HOME/client64/lib
PATH=$PATH:$HOME/bin:/sbin:$ORACLE_HOME/client64/bin


apt-amd-02:/usr/lib/oracle/11.2/client64/bin # pwd
/usr/lib/oracle/11.2/client64/bin


apt-amd-02:/usr/lib/oracle/11.2/client64/bin # sqlplus scott/tiger@anujc

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 4 15:39:31 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
T TABLE



Now connect to different database .


apt-amd-02:/usr/lib/oracle/11.2/client64/bin # sqlplus scott/tiger@anuj

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 4 16:45:38 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
apt-amd-02:/usr/lib/oracle/11.2/client64/bin # cat /etc/tnsnames.ora
anujc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
)
(CONNECT_DATA =
(sid = orcl)
)
)


anuj =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210)(PORT = 1521))
)
(CONNECT_DATA =
(SID = anujdb)
)
)

How to set CONNECT_DATA with SID in oracle for tnsnames.ora file

tnsnames.ora CONNECT_DATA SID



anuj2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))
)
(CONNECT_DATA =
(SID = anujdb)
)
)



anuj3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))
)
(CONNECT_DATA =
(ORACLE_SID = anujdb)
)
)


oracle@novagenesis$ tnsping anuj2

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 04-APR-2011 11:31:19

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:
/aptus/oracle/product/10g/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210)(PORT = 1521))) (CONNECT_DATA = (SID = anujdb)))
OK (0 msec)


SQL> connect scott/tiger@anuj2
Connected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Install squirrel thin client on unix for Oracle

Install squirrel client for oracle
squirrel client for oracle on unix
Thin client (squirrel) for Oracle


First download the squirrel client in this site http://www.squirrelsql.org/


squirrel-sql-3.2.1-install.jar




apt-amd-02:/home/anujs/Downloads # which java
/usr/bin/java

To Install jar file

apt-amd-02:/home/anujs/Downloads # java -jar squirrel-sql-3.2.1-install.jar

then go to this dir , you will able to see this

apt-amd-02:/home/anujs/Downloads # cd /usr/local/squirrel-sql-3.2.1
apt-amd-02:/usr/local/squirrel-sql-3.2.1 # ls -ltr
total 1924
-rw-r--r-- 1 root root 400 2010-12-29 21:56 update-log4j.properties
-rw-r--r-- 1 root root 308 2010-12-29 21:56 log4j.properties
-rw-r--r-- 1 root root 23 2010-12-29 21:56 addpath.bat
-rw-r--r-- 1 root root 1922111 2010-12-29 23:59 squirrel-sql.jar
drwxr-xr-x 2 root root 24 2011-04-01 11:26 update
drwxr-xr-x 2 root root 71 2011-04-01 11:26 icons
drwxr-xr-x 5 root root 4096 2011-04-01 11:26 doc
drwxr-xr-x 2 root root 4096 2011-04-01 11:26 lib
drwxr-xr-x 10 root root 4096 2011-04-01 11:26 plugins
-rw-r--r-- 1 root root 3244 2011-04-01 11:26 squirrel-sql.bat
-rwxr-xr-x 1 root root 4529 2011-04-01 11:26 squirrel-sql.sh
drwxr-xr-x 2 root root 53 2011-04-01 11:26 Uninstaller
-rw-r--r-- 1 root root 4587 2011-04-01 11:26 .installationinformation


then download this jar file from oracle site

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-90769.html

-rw-r--r-- 1 anujs users 1988051 2011-04-01 12:38 ojdbc6.jar


copy this file to in this dir

cp ojdbc6.jar /usr/local/squirrel-sql-3.2.1/lib/


To run squirrel client

apt-amd-02:/usr/local/squirrel-sql-3.2.1 # ./squirrel-sql.sh


jdbc:oracle:thin:@server:1521:database_name

jdbc:oracle:thin:@192.168.2.251:1521:orcl

How to set SERVICE_NAME in oracle for tnsnames.ora

How to set SERVICE_NAME in oracle for tnsnames.ora file


col NAME format a30
col VALUE format a30
SQL> select name, value from v$parameter where name like 'service%' or name like 'db_domain' or name like 'db_unique_name' ;

NAME VALUE
------------------------------ ------------------------------
db_domain
service_names aptdb
db_unique_name aptdb



Default value DB_UNIQUE_NAME.DB_DOMAIN if defined




ANUJ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = APTDB) <<<<<------------- Default value will be DB_UNIQUE_NAME.DB_DOMAIN
)
)



=======================================================================




col NAME format a30
col VALUE format a30
select name, value from v$parameter where name like 'service%' or name like 'db_domain' or name like 'db_unique_name'

NAME VALUE
------------------------------ ------------------------------
db_domain anuj.co.uk
service_names orcl.anuj.co.uk
db_unique_name orcl



default value DB_UNIQUE_NAME.DB_DOMAIN



ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apt-amd-02.anuj.co.uk)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.anuj.co.uk) <<------Default value will be DB_UNIQUE_NAME.DB_DOMAIN
)
)



anuj1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.anuj.co.uk) <<<--Default value will be DB_UNIQUE_NAME.DB_DOMAIN
)
)


===================================

Example file for listener.ora

oracle@novagenesis$ cat listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10g/network/admin/listener.ora
# Generated by Oracle configuration tools.



LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210 )(PORT = 1521))
)
)

#APTDB =
# (DESCRIPTION_LIST =
# (DESCRIPTION =
# (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.4 )(PORT = 1521))
# )
# )


# to enable trace file
#TRACE_LEVEL_CLIENT = SUPPORT
#TRACE_TIMESTAMP_CLIENT = ON
#TRACE_LEVEL_LISTENER=16
#TRACE_TIMESTAMP_LISTENER=TRUE
#TRACE_DIRECTORY_LISTENER=/opt/oracle/product/10g/network/admin
#TRACE_FILELEN_LISTENER=500000
#TRACE_FILENO_LISTENER=10

======================================================


you can find out variable name following way as well


SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SQL> select sys_context('userenv','DB_NAME') from dual;




SQL> alter user sys identified by sys ;

User altered.




You have to connect sys user through tnsnames other wise you will get wrong result .


oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 16 08:56:48 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
SYS$USERS




so connect this way .

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus sys/sys@anuj1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 16 08:25:23 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
orcl.ptus.co.uk

or


SQL> select ora_database_name from dual;

ORA_DATABASE_NAME
--------------------------------------------------------------------------------
ORCL.PTUS.CO.UK





oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus sys/sys@anuj1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 16 08:25:23 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
orcl.ptus.co.uk



SQL> select ora_database_name from dual;

ORA_DATABASE_NAME
--------------------------------------------------------------------------------
ORCL.PTUS.CO.UK




SQL> show parameter name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
service_names string orcl.ptus.co.uk


SQL> show parameter domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string ptus.co.uk





service_name = db_unique_name.db_domain