Crontab

DATE=$(date +"%d%m%y")

export ORACLE_SID=

export ORACLE_HOME=/oracle/product/database/10.2.0/PMIS


export PATH=/oracle/product/database/10.2.0/PMIS/bin


exp pmis/ file=/scaned-images/exp_pmis_$DATE.dmp log=/scaned-images/exp_pmis_$DATE.log statistics=none

find -H /export/home/Backup/ . -type f -name "*.dmp" -print -exec gzip {} \;

user_responsiblity_apps

SELECT fu.user_name "User Name",
frt.responsibility_name "Responsibility Name",
furg.start_date "Start Date",
furg.end_date "End Date",
fr.responsibility_key "Responsibility Key",
fa.application_short_name "Application Short Name"
FROM fnd_user_resp_groups_direct furg,
applsys.fnd_user fu,
applsys.fnd_responsibility_tl frt,
applsys.fnd_responsibility fr,
applsys.fnd_application_tl fat,
applsys.fnd_application fa
WHERE furg.user_id = fu.user_id
AND furg.responsibility_id = frt.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.application_id = fat.application_id
AND fr.application_id = fat.application_id
AND frt.language = USERENV('LANG')
-- AND UPPER(fu.user_name) = UPPER('AMOHSIN') --
-- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
ORDER BY frt.responsibility_name;


-------------
select u.user_name, a.application_name, r.responsibility_name, r.responsibility_id, wu.end_date
from apps.fnd_user u, apps.fnd_responsibility_tl r, apps.fnd_application_tl a, apps.wf_local_roles ur,
apps.wf_user_role_assignments wu
where u.user_name=wu.user_name and wu.role_name=ur.name
and ur.display_name=r.responsibility_name
and a.application_id=r.application_id
--and wu.user_name='RBEHARI'
and
(wu.end_date is null or wu.end_date >= sysdate) and
a.language='US'
order by u.user_name, r.responsibility_name

--------------------

SELECT
fuser.USER_NAME USER_NAME
, per.FULL_NAME FULL_NAME
, per.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, frt.RESPONSIBILITY_NAME RESPONSIBILITY
FROM
FND_USER fuser
, PER_PEOPLE_F per
, FND_USER_RESP_GROUPS furg
, FND_RESPONSIBILITY_TL frt
WHERE
fuser.EMPLOYEE_ID = per.PERSON_ID
AND fuser.USER_ID = furg.USER_ID
AND (to_char(fuser.END_DATE) is null
OR fuser.END_DATE > sysdate)
AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
AND (to_char(furg.END_DATE) is null
OR furg.END_DATE > sysdate)
AND frt.LANGUAGE = 'US'
ORDER BY
fuser.USER_NAME;

--------------

-- Take a count of invalid objects
-- check for locks

echo "Purpose: To spool out the invalid objects before and after applying the patch"
if [ $# -ne 2 ]; then
echo "Usage: $0 "
exit 1;
fi
genInvList(){
USER=apps
PASS=$1
TYPE=$2
DATEFMT=`date +%Y%m%d-%H%M`

if [ "x$ORACLE_SID" = "x" ]; then
if [ "x$TWO_TASK" = "x" ]; then
echo "set up the env first, duh!"
exit
else
DB=`echo $TWO_TASK`
fi
else
DB=`echo $ORACLE_SID`
fi


sqlplus -s /nolog<<_END_
conn ${USER}/${PASS}@${DB}
set pages 10000
col owner for a14
col object_type for a20
col status for a10
col OBJECT_NAME for a30
spool ${DB}-${TYPE}-invalid-${DATEFMT}.lst
select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual;
select instance_name,status from v\$instance;
select owner, object_type, object_name
from dba_objects
where status = 'INVALID'
order by owner, object_type, object_name;
select owner, count(status)
from dba_objects
where status = 'INVALID'
group by owner;

select 'Total Invalids: '||count(*) from dba_objects where status != 'VALID';
spool off
exit
_END_
}
genInvList $1 $2



sh invalid.sh apps pre

recreate UNDO Tablespace

How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g
How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g?

Solution:

1. Determine the size of your undo tablespace

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';

SUM(BYTES)/1024/1024/1024
-------------------------
12.09375

2. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\UNDOTBS02.DBF' size 400M;

Tablespace created.

3. Edit your init.ora file and change the parameter "undo_tablespace=UNDOTBS2" so it points to the newly created tablespace.
Change undo_tablespace=UNDOTBS2
Change undo_management=MANUAL

Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.

4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.
5. Startup the database
6. Confirm the new tablespace is in use:

SQL> show parameter undo_tablespace

NAME TYPE VALUE
------------- ----------- ------------
undo_tablespace string UNDOTBS2

7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.
SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ --------------------------- ------------------------------ -----------
PUBLIC _SYSSMU3$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU2$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19$ UNDOTBS2 OFFLINE

....etc.

If the old segments are online, then they must be taken offline:

SQL>alter rollback segment "_SYSSMU3$" offline;
SQL>alter rollback segment "_SYSSMU2$" offline;

This should be executed for all online rollback segments in the old tablespace.

8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:
SQL>drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

9. Edit your init.ora file do the following changes.

undo_management='AUTO'
undo_tablespace='UNDOTBS2'

10. Shutdown the database (shutdown immediate) and restart it.

11. Monitor the alert log simultaneously during all steps.

Reference Metalink Note: [ID 431652.1]

recreate TEMP Tablespace

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g
How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

1. Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M';

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp tablespace

a. Find Session Number from V$SORT_USAGE:
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

b. Find Session ID from V$SESSION:

If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
OR
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

c. Kill Session:

Now kill the session with IMMEDIATE.

ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

4. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.

Script – Database structure and file location

***************************************************
Script – Database structure and file location
****************************************************

set pagesize 500
set linesize 130
Prompt
Prompt Control Files Location >>>>
col name format a60 heading "Control Files"

select name
from sys.v_$controlfile
/

Prompt
Prompt Redo Log File Locations >>>>
Prompt

col Grp format 9999
col member format a50 heading "Online REDO Logs"
col File# format 9999
col name format a50 heading "Online REDO Logs"
break on Grp
select group#,member
from sys.v_$logfile
/


Prompt Data Files Locations >>>>

col Tspace format a25
col status format a3 heading Sta
col Id format 9999
col Mbyte format 999999999
col name format a50 heading "Database Data Files"
col Reads format 99,999,999
col Writes format 99,999,999

break on report
compute sum label 'Total(MB)' of Mbyte on report

select F.file_id Id,
F.file_name name,
F.bytes/(1024*1024) Mbyte,
decode(F.status,'AVAILABLE','OK',F.status) status,
F.tablespace_name Tspace
from sys.dba_data_files F
order by tablespace_name;

Full Database Backup script Through Rman

Execute Rman script:
------------------------

today=$(date +'%m-%d-%C%y;%H:%M')
echo $today
ORACLE_SID=testdb
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.
export ORACLE_HOME PATH ORACLE_SID
now=$(date +"%d-%m-%Y")
mkdir /u01/Rman_Backup/FULLDB_testdb_$now
export DAY='date +%d'
export NUM='expr $DAY / 7'

$ORACLE_HOME/bin/rman << EOF
connect target /
run {
allocate channel ch00 type disk;
allocate channel ch01 type disk;
#allocate channel ch02 type disk;
#allocate channel ch03 type disk;
#allocate channel ch04 type disk;
#allocate channel ch05 type disk;
#allocate channel ch06 type disk;
set command id to 'hot_database_backup';
backup
as compressed backupset
incremental level = 0
check logical
database tag='WEEKLY_BACKUP'
FORMAT '/u01/Rman_Backup/FULLDB_testdb_$now/testdb_%c_%d_S_%s_P_%p_T_%t' ;
backup
format '/u01/Rman_Backup/FULLDB_testdb_$now/c_%d_%Y%M%D.%s.%p.%t'
current controlfile;
release channel ch00;
release channel ch01;
#release channel ch02;
#release channel ch03;
#release channel ch04;
#release channel ch05;
#release channel ch06;
}
delete noprompt obsolete ;
list backup;
crosscheck backup;
delete noprompt expired backup;
delete noprompt expired copy;
delete noprompt expired archivelog all;
EOF
today=$(date +'%m-%d-%C%y;%H:%M')
echo $today