Nice script to check TBS usage

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)",
(NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024) "Used (M)",
TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select
tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name like '&TABLESPACE%' and d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)", NVL(t.bytes, 0)/1024/1024 "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files
group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like
'TEMPORARY'
order by 2
/

bumping one particular sequence

----------------checking the nextval in seq---------
conn platform/platform
select SCDB_SUBSCRIPTIONS_ID_SEQ.nextval from dual;

NEXTVAL
----------
140
----------------checking max id in table which is using seq----------

conn scdb_user/scdb_user

select max(id) from SCDB_SUBSCRIPTIONS;

MAX(ID)
----------
50236


note: here table id is more than sequence.
________________________________________________-


so difference b/w table id and seqnnextval should be incremented

50236
- 140
________
50096

SQL> alter sequence platform.SCDB_SUBSCRIPTIONS_ID_SEQ increment by 50096;

Sequence altered.

SQL> select SCDB_SUBSCRIPTIONS_ID_SEQ.nextval from dual;

NEXTVAL
----------
50237


SQL> conn scdb_user/scdb_user

SQL> select max(id) from SCDB_SUBSCRIPTIONS;

MAX(ID)
----------
100333

note: here scdb_user is accessing the sequence of platform user to insert a row in SCDB_SUBSCRIPTIONS table
_____________________________________________________________________________________________________________-

Changing hostname on RHEL

Changing hostname on RHEL:

1. Change the HOSTNAME line in /etc/sysconfig/network

2. Change the hostname (FQDN and alias) in /etc/hosts

3. Run /bin/hostname new_hostname for the hostname change to take effect immediately.

4. Run /sbin/service syslog restart for syslog to log using the new hostname.

A reboot is not required to change the system hostname.

Oracle Networking Notes

1>--HOST NAMING METHOD--

--PRIMARY DATABASE------ --------CLIENT-------

1>LISTINER SHOULD BE RUNNING 1>SQLNET.ORA SHOULD BE CONFIGURED WITH
NAMES.DIRECTORY_PATH= (HOST)

--PATH FOR SQLNET.ORA FILE-
LINUX:$ORACLE_HOME/NETWORK/ADMIN
WINDOWS:\oracle\product\10.1.0\db_1\NETWORK\ADMIN

2>HOST FILE SHOULD BE REGISTERED WITH
PRIMARY DATABASE IP AND GLOBAL_DB_NAME

IP GLOBAL_DB_NAME
10.60.10.95 DKDB

--PATH FOR HOST FILE---
LINUX:CD /ETC/HOSTS
WINDOWS:C:\WINDOWS\system32\drivers


----CONNECTING FROM CLIENT---
eg:SQLPLUS SYSTEM/DKDB@DKDB
SQL>CONN SYSTEM/DKDB@DKDB


2>--EZCONNECT NAMING METHOD--

--PRIMARY DATABASE------ -------CLIENT-------

1>LISTINER SHOULD BE RUNNING 1>SQLNET.ORA SHOULD BE CONFIGURED WITH
NAMES.DIRECTORY_PATH= (EZCONNECT)

--PATH FOR SQLNET.ORA FILE--
LINUX:$ORACLE_HOME/NETWORK/ADMIN
WINDOWS:\oracle\product\10.1.0\db_1\NETWORK\ADMIN


--CONNECTING FROM CLIENT--
eg:SQLPLUS SYSTEM/DKDB@10.60.10.95:1521/DKDB
SQL>CONN SYSTEM/DKDB@10.60.10.95:1521/DKDB



3>--TNS NAMING METHOD--

--PRIMARY DATABASE------ -------CLIENT-------

1>LISTINER SHOULD BE RUNNING 1>SQLNET.ORA SHOULD BE CONFIGURED WITH
NAMES.DIRECTORY_PATH= (TNSNAMES)

--PATH FOR SQLNET.ORA FILE--
LINUX:$ORACLE_HOME/NETWORK/ADMIN
WINDOWS:\oracle\product\10.1.0\db_1\NETWORK\ADMIN

2>TNSNAMES.ORA SHOULD BE CONFIGURED WITH
DKDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.60.10.95)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dkdb)
)
)

---CONNECTING FROM CLIENT-----
eg:SQLPLUS SYSTEM/DKDB@DKDB
SQL> CONN SYSTEM/DKDB@DKDB

To increase DB process

You might also want to check if the number of connections reaches the database's PROCESSES parameter using following Unix command:

ps -ef | grep oracleSID | grep -v grep | wc -l

or

ps aux | grep oracleSID | grep -v grep | wc -l

If the value found is close to the value of the PROCESSES database parameter value, you might want to augment this value using following command:

alter system set processes=new value [scope=spfile];

A database bounce is required to let the new value become active.

crontab clone db

#30 20 * * 5 /home/oracle/clone_scripts/clone_fopsfat1.sh

-------------------clone_fopsfat1.sh-----------------

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
TMP=/tmp; export TMP
ORACLE_HOME_LISTENER=$ORACLE_BASE ;export ORACLE_HOME_LISTENER
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

export ORACLE_SID=fopsfat1
lsnrctl stop listenerfat1

cd /data/fopsfat1
rm *.*
cd /data/fops
cp *.* /data/fopsfat1/
cd /data/fopsfat1
rm *.ctl
rm temp01.dbf
cd /data/app/oracle/admin/fopsfat1
rm -rf udump bdump cdump adump
mkdir udump bdump cdump adump

sqlplus 'sys/fopsdb2008 as sysdba' @/home/oracle/clone_scripts/fopsfat1conttrc.sql

exit
---------------fopsfat1conttrc.sql--------------------------


SHUTDOWN ABORT;
STARTUP NOMOUNT pfile='$ORACLE_HOME/dbs/initfopsfat1.ora'
CREATE CONTROLFILE SET DATABASE "fopsfat1" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 887
LOGFILE
GROUP 1 '/data/fopsfat1/redo01.log' SIZE 50M,
GROUP 2 '/data/fopsfat1/redo02.log' SIZE 50M,
GROUP 3 '/data/fopsfat1/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/data/fopsfat1/system01.dbf',
'/data/fopsfat1/undotbs01.dbf',
'/data/fopsfat1/sysaux01.dbf',
'/data/fopsfat1/users01.dbf',
'/data/fopsfat1/admin_data_01.dbf',
'/data/fopsfat1/admin_data_02.dbf',
'/data/fopsfat1/load_data_01.dbf',
'/data/fopsfat1/navdata_data_01.dbf',
'/data/fopsfat1/spatial_data_01.dbf',
'/data/fopsfat1/statw_data_01.dbf',
'/data/fopsfat1/statw_data_02.dbf',
'/data/fopsfat1/tools_data_01.dbf',
'/data/fopsfat1/weather_data_01.dbf'
CHARACTER SET WE8ISO8859P15
;

alter database open resetlogs;

-- re-create incarnation records.
ALTER TABLESPACE TEMP ADD TEMPFILE '/data/fopsfat1/temp01.dbf'
SIZE 1097M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

alter user system account unlock;

alter user system identified by fopsdb2008;

!lsnrctl start listenerfat1

exit
------------------------------

Note: 1.add a entry in oratab file
2.create a pfile and edit pfile whit new db name and file locations
3.create a password file
4.edit the contents inside controlfile trace and rename it.

crontab monitor sessions blocking

#* * * * * /data/app/oracle/product/10.2.0/db_1/kill/monitor.sh > /dev/null 2>&1


-----------------------------------monitor.sh----------------------------------

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
TMP=/tmp; export TMP
ORACLE_HOME_LISTENER=$ORACLE_BASE ;export ORACLE_HOME_LISTENER
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

sqlplus system/fopsdb2008@fopsfat1 @/data/app/oracle/product/10.2.0/db_1/kill/monitor.sql

-----------------monitor.sql-----------------------------
set linesize 120

set echo off

set heading off

spool on

spool /data/app/oracle/product/10.2.0/db_1/kill/monitor_lock.spool


select s1.username,s1.osuser || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;


select sysdate from dual;

spool off

!cat /data/app/oracle/product/10.2.0/db_1/kill/monitor_lock.spool >> /data/app/oracle/product/10.2.0/db_1/kill/monitor.txt
exit