kill all sessions for a user

set head off 
spool tmp.tmp 
select 'Alter system kill session '''||sid||','||serial#||''';' 
from v$session where username='USER_TO_KILL_IN_CAPS' ;
spoof off
@tmp.tmp

Opatch Helpers

How to tar the oracle home

cd /opt/oracle/product/10.2.0
tar -cvf - . |compress > /opt/oracle/admin/10203.beforeupgrade.tar.Z

cd /opt/oracle/oraInventory
tar -cvf - . |compress > /opt/oracle/admin/inventory_bkp_beforeupgrade.tar.Z






export PATH=$PATH:$ORACLE_HOME/OPatch

unzip p12879912_10204_.zip
cd 12879912
opatch napply -skip_subset -skip_duplicate 

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> -- Execute the next statement only if this is the first 10.2.0.4 CPU applied or this is the first CPU applied since CPUApr2011.
SQL> @utlrp.sql
SQL> QUIT 

SELECT * FROM registry$history where ID = '6452863';
 

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql
SQL> QUIT 

1. cd $ORACLE_HOME/cpu/view_recompile
   sqlplus /nolog
   SQL> CONNECT / AS SYSDBA
   SQL> SHUTDOWN IMMEDIATE
   SQL> STARTUP UPGRADE
   SQL> @view_recompile_jan2008cpu.sql
   SQL> SHUTDOWN;
   SQL> STARTUP;
   SQL> QUIT
   

2. If the database is
   
   
   

   cd $ORACLE_HOME/rdbms/admin
   sqlplus /nolog
   SQL> CONNECT / AS SYSDBA
   SQL> @utlrp.sql
   

   Then, manually recompile any invalid
    
3.  
4. select action_time, action, version, id, comments from dba_registry_history order by action_time; 

 When i had faced the file copy failed alert , this below step was followed 

cp /backup/oracle/patching/12879912/8568398/files/lib/libjox10.a /opt/oracle/product/10.2.0/lib/libjox10.a
524     cd /opt/oracle/product/10.2.0/lib/
525     ls -lrt libjo*
526     mv libjox10.a libjox10.a_orig
527     history
oracle @ gvi0aitmt04p:itmdwp1
[/opt/oracle/product/10.2.0/lib]$ cp /backup/oracle/patching/12879912/8568398/files/lib/libjox10.a /opt/oracle/product/10.2.0/lib/libjox10.a
 

Copy failed from '//lib/libjox10.a'..

then it was sucess with the warnings 

 check opatch utility information

cd $ORACLE_HOME/OPatch/opatch lsinventory

Rman point in time recovery

Day before yesterday i completed a build and handovered to the client , i never know that the client was waiting to hand over and to try a app upgrade, unfortunately that went wrong and he approached for a recover point in time, luckly i had sheduled the rman job on the cron ( ushually this will be sheduled in a automated tool and that will take nearly  a week time to set up)
i have taken the db in mount state .

Shutdown immediate;
startup nomount;



 Refference :

export NLS_LANG = american_america.us7ascii
export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"


connect catalog xx/xx@xx
run {
allocate channel t1 type 'sbt_tape' maxpiecesize 4000m parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin/tdpo.xxx.opt)';
allocate channel t2 type 'sbt_tape' maxpiecesize 4000m parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin/tdpo.xxx.opt)';
sql 'alter session set NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"';
SET UNTIL TIME 'Feb 14 2012 21:00:00';
restore database;
recover database;
release channel t1;
release channel t2;
}

The opt files were by default for my environment pls change accordingly and as per ur environment

i had completed the restore and recover ..

Logs for your refference:


connected to recovery catalog database

RMAN>

RMAN> run {
2> allocate channel t1 type 'sbt_tape' maxpiecesize 4000m parms 'ENV=

(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin/tdpo.metap5.opt)';
3> allocate channel t2 type 'sbt_tape' maxpiecesize 4000m parms 'ENV=

(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin/tdpo.metap5.opt)';
sql 'alter session set NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"';
4> 5> SET UNTIL TIME 'Feb 14 2012 21:00:00';
restore database;
6> 7> recover database;
8> release channel t1;
9> release channel t2;
10> }

allocated channel: t1
channel t1: sid=1088 devtype=SBT_TAPE
channel t1: Tivoli Data Protection for Oracle: version 5.2.0.0

allocated channel: t2
channel t2: sid=1087 devtype=SBT_TAPE
channel t2: Tivoli Data Protection for Oracle: version 5.2.0.0

sql statement: alter session set NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"

executing command: SET until clause

Starting restore at 15-FEB-12

channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/metap5/system_01.dbf
restoring datafile 00004 to /u02/oradata/metap5/metap5_tools_01.dbf
restoring datafile 00005 to /u02/oradata/metap5/metap5_users_01.dbf
restoring datafile 00007 to /u02/oradata/metap5/metap5_meta_index_01.dbf
restoring datafile 00008 to /u03/oradata/metap5/metap5_meta_index_03.dbf
restoring datafile 00010 to /u03/oradata/metap5/metap5_meta_index_035dbf
restoring datafile 00013 to /u03/oradata/metap5/metap5_meta_data_04.dbf
restoring datafile 00015 to /u03/oradata/metap5/metap5_meta_data_06.dbf
restoring datafile 00017 to /u03/oradata/metap5/metap5_meta_data_08.dbf
restoring datafile 00019 to /u03/oradata/metap5/metap5_meta_data_10.dbf
channel t1: reading from backup piece df_METAP5_775200607_9_1
channel t1: restored backup piece 1
piece handle=df_METAP5_775200607_9_1 tag=TAG20120214T053006
channel t1: restore complete, elapsed time: 00:02:56
channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u02/oradata/metap5/undo_01.dbf
restoring datafile 00003 to /u01/oradata/metap5/sysaux_01.dbf
restoring datafile 00006 to /u01/oradata/metap5/metap5_meta_data_01.dbf
restoring datafile 00009 to /u03/oradata/metap5/metap5_meta_index_04.dbf
restoring datafile 00011 to /u03/oradata/metap5/metap5_meta_index_034dbf
restoring datafile 00012 to /u03/oradata/metap5/metap5_meta_data_02.dbf
restoring datafile 00014 to /u03/oradata/metap5/metap5_meta_data_05.dbf
restoring datafile 00016 to /u03/oradata/metap5/metap5_meta_data_07.dbf
restoring datafile 00018 to /u03/oradata/metap5/metap5_meta_data_09.dbf
restoring datafile 00020 to /u03/oradata/metap5/metap5_meta_data_11.dbf
channel t1: reading from backup piece df_METAP5_775200606_8_1
channel t1: restored backup piece 1
piece handle=df_METAP5_775200606_8_1 tag=TAG20120214T053006
channel t1: restore complete, elapsed time: 00:03:16
Finished restore at 15-FEB-12

Starting recover at 15-FEB-12

starting media recovery

channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=45
channel t1: reading from backup piece arc_METAP5_775201925_12_1
channel t1: restored backup piece 1
piece handle=arc_METAP5_775201925_12_1 tag=TAG20120214T055205
channel t1: restore complete, elapsed time: 00:00:55
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=44
channel t1: reading from backup piece arc_METAP5_775201925_11_1
channel t1: restored backup piece 1
piece handle=arc_METAP5_775201925_11_1 tag=TAG20120214T055205
channel t1: restore complete, elapsed time: 00:00:03
archive log filename=/opt/oracle/admin/metap5/arch/metap544_1_773984687.arclog thread=1

sequence=44
archive log filename=/opt/oracle/admin/metap5/arch/metap545_1_773984687.arclog thread=1

sequence=45
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=46
channel t1: reading from backup piece arc_METAP5_775289526_16_1
channel t1: restored backup piece 1
piece handle=arc_METAP5_775289526_16_1 tag=TAG20120215T061206
channel t1: restore complete, elapsed time: 00:01:06
archive log filename=/opt/oracle/admin/metap5/arch/metap546_1_773984687.arclog thread=1

sequence=46
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-FEB-12

released channel: t1

released channel: t2

Tablespaces

     Tablespace 

      A tablespace is a logical storage unit in an oracle database (its not a physical file), But tablespace inturn consists of atleast one datafile from the database. and that datafile is physicaly located in the file system on the database server , 

Our actual data's (Table,Index) will be segregated on this datafile . 

The above details are very basic understanding of an oracle tablespace and datafile. But i am not intended to write about the datafiles/tablespaces here . I am trying to have some space given here help me/others when we perform some task on the datafile and tablespaces . 

 At first , There are three types of tablespaces in oracle 

• Permanent Tablespaces
• Undo Tablespaces
• Temporary Tablespaces

Tasks that may involve to deal with permenant tablespaces were written below , if i miss anything here pls post back to add them, bcz i am trying to have some site to guide all the tasks that would help others / me . 

At First to check the tablespace usage :

I felt the below query helps to figure out what tablespaces that may require our attention to add more space . 

set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files group by tablespace_name union all
 select  tablespace_name || '  **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4

/

Lets say if we need to see what are the files associated with each tablespace 

Query the below one ,

set lines 100
col file_name format a70
select file_name
,      ceil(bytes / 1024 / 1024) "size MB"
from   dba_data_files
where  tablespace_name like '&TSNAME'

/

Tablespaces that are >=80% full, and how much to add to make them 80% again

set pages 999 lines 100
col "Tablespace" for a50
col "Size MB"  for 999999999
col "%Used"  for 999
col "Add (80%)"  for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from    dba_data_files group by tablespace_name) tsu
,  (select ts.tablespace_name
 ,       nvl(sum(bytes)/1024/1024, 0) free_mb
 from    dba_tablespaces ts, dba_free_space fs
 where   ts.tablespace_name = fs.tablespace_name (+)
 group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4
/

User quotas on all tablespaces

col quota format a10
select username
,      tablespace_name
,      decode(max_bytes, -1, 'unlimited'
       , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from   dba_ts_quotas
where  tablespace_name not in ('TEMP')
/

List all objects in a tablespace

 

set pages 999
col owner format a15
col segment_name format a40
col segment_type format a20
select owner
,      segment_name
,      segment_type
from   dba_segments
where  lower(tablespace_name) like lower('%&tablespace%')
order by owner, segment_name
/

 

 

Show all tablespaces used by a user

select tablespace_name
, ceil(sum(bytes) / 1024 / 1024) "MB"
from dba_extents
where owner like '&user_id'
group by tablespace_name
order by tablespace_name
/

 

Create a temporary tablespace

create temporary tablespace temp
tempfile '' size 500M
/

Alter a databases default temporary tablespace

alter database default temporary tablespace temp
/

Show segments that are approaching max_extents

col segment_name format a40
select owner
, segment_type
, segment_name
, max_extents - extents as "spare"
, max_extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 10
order by 4
/

List the contents of the temporary tablespace(s)

set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid 
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr
/

Alter tablespace: Backups

ALTER TABLESPACE my_data_tbs BEGIN BACKUP;
ALTER TABLESPACE my_data_tbs END BACKUP;

Alter tablespace: Data Files and Tempfiles

ALTER TABLESPACE mytbs
ADD DATAFILE '/ora100/oracle/mydb/mydb_mytbs_01.dbf' SIZE 100M;
12 Portable DBA: Oracle
ALTER TABLESPACE mytemp
ADD TEMPFILE '/ora100/oracle/mydb/mydb_mytemp_01.dbf'
SIZE 100M;
ALTER TABLESPACE mytemp AUTOEXTEND OFF;
ALTER TABLESPACE mytemp AUTOEXTEND ON NEXT 100m MAXSIZE 1G;

Alter tablespace: Rename

ALTER TABLESPACE my_data_tbs RENAME TO my_newdata_tbs;

Alter tablespace: Tablespace Management

ALTER TABLESPACE my_data_tbs DEFAULT
STORAGE (INITIAL 100m NEXT 100m FREELISTS 3);
ALTER TABLESPACE my_data_tbs MINIMUM EXTENT 500k;
ALTER TABLESPACE my_data_tbs RESIZE 100m;
ALTER TABLESPACE my_data_tbs COALESCE;
ALTER TABLESPACE my_data_tbs OFFLINE;
ALTER TABLESPACE my_data_tbs ONLINE;
ALTER TABLESPACE mytbs READ ONLY;
ALTER TABLESPACE mytbs READ WRITE;
ALTER TABLESPACE mytbs FORCE LOGGING;
ALTER TABLESPACE mytbs NOLOGGING;
ALTER TABLESPACE mytbs FLASHBACK ON;
ALTER TABLESPACE mytbs FLASHBACK OFF;
ALTER TABLESPACE mytbs RETENTION GUARANTEE;
ALTER TABLESPACE mytbs RETENTION NOGUARANTEE;

Create tablespace: Permanent Tablespace

CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m FORCE LOGGING BLOCKSIZE 8k;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 10G;

Create tablespace: Temporary Tablespace

CREATE TABLESPACE temp_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_temp_tbs_01.tmp'
SIZE 100m;
create tablespace: Undo Tablespace
CREATE TABLESPACE undo_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_undo_tbs_01.tmp'
SIZE 1g RETENTION GUARANTEE;

To drop user objects

set trimspool on
set pagesize 0
set line 1000
set feed off
set verify off

spool genera_dropallbmc_smgr.sql
select 'prompt Conectando como &&OWNER' || chr(10) || 'connect &&OWNER' DROP_OBJECTS
from dual
union all
select 'spool dropall.log' DROP_OBJECTS
from dual
union all
Select
trim(case
when object_type = 'TABLE' then 'drop ' || object_type
|| ' ' || owner || '.' || object_name ||' cascade constraints'
when object_type = 'PACKAGE BODY' then 'prompt PACKAGES BODY'
when object_type = 'INDEX' then 'prompt INDEXES'
when object_type = 'DATABASE LINK' then 'drop ' || object_type
|| ' ' || object_name
else 'drop ' || object_type || ' ' || owner || '.' || object_name
end || ';')
from dba_objects
where owner = '&&OWNER'
and object_type not like '%PARTITION%'
union all
select 'drop public synonym '||synonym_name||';'
from dba_synonyms
where table_owner = '&&OWNER'
and owner = 'PUBLIC'
union all
select 'spool off'
from dual
;
spool off

exit

To grab the index details

set pagesize 300
COLUMN owner FORMAT A10
COLUMN index_name FORMAT A35
COLUMN tablespace_name FORMAT A15

Select owner, index_name, tablespace_name
From dba_indexes
Where owner NOT IN('SYSTEM','DBSNMP', 'ORDSYS', 'OUTLN','SYS')
and table_type = 'TABLE'
Order By owner, index_name, tablespace_name;

Session counts

SELECT name, value
FROM v$parameter
WHERE name = 'sessions'


SELECT COUNT(*)
FROM v$session

SELECT
'Currently, '
|| (SELECT COUNT(*) FROM V$SESSION)
|| ' out of '
|| VP.VALUE
|| ' connections are used.' AS USAGE_MESSAGE
FROM
V$PARAMETER VP
WHERE VP.NAME = 'sessions'


SELECT
'Currently, '
|| (SELECT COUNT(*) FROM V$SESSION)
|| ' out of '
|| DECODE(VL.SESSIONS_MAX,0,'unlimited',VL.SESSIONS_MAX)
|| ' connections are used.' AS USAGE_MESSAGE
FROM
V$LICENSE VL