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 {} \;
Tuesday 28 June 2016
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;
--------------
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;
--------------
Tuesday 31 May 2016
-- 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
-- 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
Saturday 14 May 2016
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]
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.
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;
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
------------------------
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
Rman Backup and Restore Steps
Rman Restore Backup Steps:
--------------------------
On Source Database:
-------------------
*********************************************************************
Datafiles Information:
-----------------------
set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.lst
select 'set newname for datafile ' || file_id || ' to ''/u01/oradata/dhana/' ||
substr(file_name,instr(file_name,'/',-1)+1) || ''';'
from dba_data_files;
spool off
exit;
Redo log Location:
------------------------
set head off pages 0 feed off echo off verify off
set lines 200
spool rename_logfiles.lst
select 'alter database rename file '||''''||member||''''||' to '||''''||'/u01/oradata/dhana/'||substr(member,instr(member,'/',-1)+1)||''''||';' from
v$logfile ;
spool off
exit;
Temp files Information:
-------------------------
set head off pages 0 feed off echo off verify off
set lines 200
spool rename_tempfiles.lst
select 'set newname for tempfile '||''''||name||''''||' to ''/u01/oradata/dhana/' ||
substr(name,instr(name,'/',-1)+1) || ''';'
from v$tempfile;
spool off
exit;
********************************************************************
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; 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; } 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 ************************************************************************** Create pfile from spfile; cp init<>.ora
need to create same database name.
scp -r FULLDB_testdb_14-05-2016 oracle@192.168.2.142:/home/oracle
scp -r files oracle@192.168.2.142:/home/oracle
----------------------------------------------------------------------------
Target Machine
--------------
chmod -R 777 FULLDB_testdb_14-05-2016/
After making the necessary changes to the PFILE we now will bring the database back up in NOMOUNT mode so we can restore the control files. When
bringing the database up in NOMOUNT mode we will use the PFILE we edited earlier.
need to create necessary folders for control and datafiles.
add this parameter also must db_unique_name=dhana
mkdir -p /u01/app/oracle/admin/dhana/adump
mkdir -p /u01/oradata/dhana
mkdir -p /u01/oradata/dhana/arc
. oraenv
set source database sid now.
. oraenv
ORACLE_SID = [final] ? testdb
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0/dbhome_1
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdhana.ora';
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2252824 bytes
Variable Size 180359144 bytes
Database Buffers 125829120 bytes
Redo Buffers 4718592 bytes
SQL>exit
$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 14 00:35:01 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (not mounted)
RMAN> restore controlfile from '/home/oracle/FULLDB_testdb_14-05-2016/c_TESTDB_20160514.34.1.911779959';
Starting restore at 14-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/dhana/control01.ctl
output file name=/u01/oradata/dhana/control02.ctl
Finished restore at 14-MAY-16
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> catalog start with '/home/oracle/FULLDB_testdb_14-05-2016';
Example Output:
searching for all files that match the pattern /home/oracle/FULLDB_testdb_14-05-2016
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_33_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_32_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/c_TESTDB_20160514.34.1.911779959
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_33_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_32_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/c_TESTDB_20160514.34.1.911779959
RMAN> list backup;
RMAN> run {
2> @rename_datafiles.lst
3> set newname for datafile 4 to '/u01/oradata/dhana/users01.dbf';
4> set newname for datafile 3 to '/u01/oradata/dhana/undotbs01.dbf';
5> set newname for datafile 2 to '/u01/oradata/dhana/sysaux01.dbf';
6> set newname for datafile 1 to '/u01/oradata/dhana/system01.dbf';
7> **end-of-file**
8> restore database;
9> switch datafile all;
10> recover database;
11> }
need to add set newname for tempfile '/u01/oradata/testdb/temp01.dbf' to '/u01/oradata/dhana/temp01.dbf';
SQL> @rename_logfiles.lst;
Database altered.
Database altered.
Database altered.
SQL> alter database open resetlogs;
Database altered.
********************************************************
Change database name using NID
cd $ORACLE_HOME/dbs
$ orapwd file=orapwdhana password=****** entries=5
nid target=sys/admin123 dbname=dhana
sqlplus / as sysdba
SQL> startup nomount;
SQL> ALTER SYSTEM SET db_name=dhana SCOPE=SPFILE; (using pfile change db name manually)
SQL> shut IMMEDIATE
SQL> startup mount;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> SELECT name, open_mode FROM v$database;
****************************************************
--------------------------
On Source Database:
-------------------
*********************************************************************
Datafiles Information:
-----------------------
set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.lst
select 'set newname for datafile ' || file_id || ' to ''/u01/oradata/dhana/' ||
substr(file_name,instr(file_name,'/',-1)+1) || ''';'
from dba_data_files;
spool off
exit;
Redo log Location:
------------------------
set head off pages 0 feed off echo off verify off
set lines 200
spool rename_logfiles.lst
select 'alter database rename file '||''''||member||''''||' to '||''''||'/u01/oradata/dhana/'||substr(member,instr(member,'/',-1)+1)||''''||';' from
v$logfile ;
spool off
exit;
Temp files Information:
-------------------------
set head off pages 0 feed off echo off verify off
set lines 200
spool rename_tempfiles.lst
select 'set newname for tempfile '||''''||name||''''||' to ''/u01/oradata/dhana/' ||
substr(name,instr(name,'/',-1)+1) || ''';'
from v$tempfile;
spool off
exit;
********************************************************************
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; 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; } 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 ************************************************************************** Create pfile from spfile; cp init<>.ora
need to create same database name.
scp -r FULLDB_testdb_14-05-2016 oracle@192.168.2.142:/home/oracle
scp -r
----------------------------------------------------------------------------
Target Machine
--------------
chmod -R 777
After making the necessary changes to the PFILE we now will bring the database back up in NOMOUNT mode so we can restore the control files. When
bringing the database up in NOMOUNT mode we will use the PFILE we edited earlier.
need to create necessary folders for control and datafiles.
add this parameter also must db_unique_name=dhana
mkdir -p /u01/app/oracle/admin/dhana/adump
mkdir -p /u01/oradata/dhana
mkdir -p /u01/oradata/dhana/arc
. oraenv
set source database sid now.
. oraenv
ORACLE_SID = [final] ? testdb
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0/dbhome_1
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdhana.ora';
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2252824 bytes
Variable Size 180359144 bytes
Database Buffers 125829120 bytes
Redo Buffers 4718592 bytes
SQL>exit
$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 14 00:35:01 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (not mounted)
RMAN> restore controlfile from '/home/oracle/FULLDB_testdb_14-05-2016/c_TESTDB_20160514.34.1.911779959';
Starting restore at 14-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/dhana/control01.ctl
output file name=/u01/oradata/dhana/control02.ctl
Finished restore at 14-MAY-16
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> catalog start with '/home/oracle/FULLDB_testdb_14-05-2016';
Example Output:
searching for all files that match the pattern /home/oracle/FULLDB_testdb_14-05-2016
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_33_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_32_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/c_TESTDB_20160514.34.1.911779959
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_33_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/testdb_1_TESTDB_S_32_P_1_T_911779903
File Name: /home/oracle/FULLDB_testdb_14-05-2016/c_TESTDB_20160514.34.1.911779959
RMAN> list backup;
RMAN> run {
2> @rename_datafiles.lst
3> set newname for datafile 4 to '/u01/oradata/dhana/users01.dbf';
4> set newname for datafile 3 to '/u01/oradata/dhana/undotbs01.dbf';
5> set newname for datafile 2 to '/u01/oradata/dhana/sysaux01.dbf';
6> set newname for datafile 1 to '/u01/oradata/dhana/system01.dbf';
7> **end-of-file**
8> restore database;
9> switch datafile all;
10> recover database;
11> }
need to add set newname for tempfile '/u01/oradata/testdb/temp01.dbf' to '/u01/oradata/dhana/temp01.dbf';
SQL> @rename_logfiles.lst;
Database altered.
Database altered.
Database altered.
SQL> alter database open resetlogs;
Database altered.
********************************************************
Change database name using NID
cd $ORACLE_HOME/dbs
$ orapwd file=orapwdhana password=****** entries=5
nid target=sys/admin123 dbname=dhana
sqlplus / as sysdba
SQL> startup nomount;
SQL> ALTER SYSTEM SET db_name=dhana SCOPE=SPFILE; (using pfile change db name manually)
SQL> shut IMMEDIATE
SQL> startup mount;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> SELECT name, open_mode FROM v$database;
****************************************************
Monday 11 April 2016
useful
Oracle Database Upgrade Path Reference List [ID 730365.1]
---------------------------------------------------------
set lines 230
set echo off
set term off
set trimspool on
set verif off
set feed off
set pagesize 100
column filename format a20
column tablespace format a15
column status format a10 trunc
column autoextend format a10
-------------------------
=/mnt/dbbackup/bemsvrmbdb02/billsvr11_1_BILLSVR1_S_527_P_1_T_855345927 tag=WEEKLY_BACKUP
channel ch1: restore complete, elapsed time: 01:25:37
Finished restore at 11-OCT-14
recover database;
Starting recover at 11-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=538 devtype=DISK
starting media recovery
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA1/billsvr1/datafile/system.285.860647991'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/11/2014 09:40:28
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 2 seq 283705 lowscn 5983102243733 found to restore
RMAN-06025: no backup of log thread 2 seq 283704 lowscn 5983102129256 found to restore
RMAN-06025: no backup of log thread 1 seq 440755 lowscn 5983102293248 found to restore
RMAN-06025: no backup of log thread 1 seq 440754 lowscn 5983102264068 found to restore
SELECT codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');
---------------------------------------------------------------------------
APEX_VMV:
------------
Apex
http://192.168.0.141:8080/apex
Apex
http://192.168.0.141:8080/apex/f?p=100:LOGIN_DESKTOP:5252404193772
Internal
admin
India12*
http://192.168.0.75:8080/apex/f?p=4550:1:2504728357192:::::
crm
admin
>CRM admin pwd ?
Vmv12*
-------------------------------------------------------------------------------------------------------------
impdp fintemp/fintemp dumpfile=EXPDP_FINANCE1_CRC_080614.DMP logfile=impdp_crc_fintemp_080615.log directory=expdump remap_schema=finance:fintemp TRANSFORM=oid:n:type
impdp boltest/boltest dumpfile=EXPDP_BOLFINANCE_250715.DMP logfile=impdp_boltest_finance_270515.log directory=expdump remap_schema=finance:boltest TRANSFORM=oid:n:type
SELECT username, PASSWORD, 'alter user ' || username || ' identified by values '|| '''' || PASSWORD || '''' ||';' alter_user_sql
FROM dba_users WHERE username = 'SYSMAN'
select 'alter table ' ||table_name|| ' add ||column_name||,table_name,data_type from user_tab_columns where data_type like 'NVARCHAR2';
------------------------------------------------------------------------------------
http://www.dba-oracle.com/t_save_reset_oracle_user_password.htm
------------------
http://oh-bee-eye.blogspot.in/2012/06/oracle-database-arabic-support.html
----------------------------------------------------------------------------------------
Spool charfix.log
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
SHUTDOWN IMMEDIATE;
STARTUP;
-- yes, 2 times startup/shutdown . This is not a typo
SHUTDOWN IMMEDIATE;
STARTUP;
spool off
--------------------------------------------------------------------------------------------------
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username='BOLPROD';
get SID
select
a.sid,
a.serial#,
b.sql_text
from
v$session a,
v$sqlarea b
where
a.sql_address=b.address
and
sid=<>;
--------------------------
SELECT MACHINE, PROGRAM, COUNT(*) FROM V$SESSION GROUP BY MACHINE, PROGRAM;
------------
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
------------------------------
LOCK:
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
column ctime heading "Seconds"
select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||'.'||substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request,
l.ctime
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/
-----------------
Total Size
select DF.TOTAL/1048576 "DataFile Size Mb",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';' from gv$session where username='ABILLITY_PROD';
DELETE FROM table_name;
or
DELETE * FROM table_name;
-------------------
alter system set audit_trail = db|NONE scope=spfile;
alter system set audit_trail='DB','EXTENDED' scope=spfile;
select value from v$parameter where name = 'audit_file_dest';
SQL> audit all by finance by access;
Audit succeeded.
SQL> audit select table,update table,delect table,insert table by finance by access;
audit select table,update table,delect table,insert table by finance by access
*
ERROR at line 1:
ORA-00956: missing or invalid auditing option
SQL> audit select table,update table,delete table,insert table by finance by access;
Audit succeeded.
SQL> audit execute procedure by finance by access;
Audit succeeded.
SQL> select count(*) from aud$;
COUNT(*)
----------
344273
SQL> select count(*) from aud$ where ntimestamp# > sysdate - 21;
COUNT(*)
----------
53550
SQL> delete from aud$ where ntimestamp# > sysdate - 21;
53550 rows deleted.
----------------------------------
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'VMVERP');
END;
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
-------------------------------------------------
find . -mtime +0 # find files modified greater than 24 hours ago
find . -mtime 0 # find files modified between now and 1 day ago
# (i.e., in the past 24 hours only)
find . -mtime -1 # find files modified less than 1 day ago (SAME AS -mtime 0)
find . -mtime 1 # find files modified between 24 and 48 hours ago
find . -mtime +1 # find files modified more than 48 hours ago
The following may only work on GNU?
find . -mmin +5 -mmin -10 # find files modified between
# 6 and 9 minutes ago
find / -mmin -10 # modified less than 10 minutes ago
find /path of directory -name "*.log" -mtime +90 -exec ls -ltr {} \; it will list the files older than 3 months
find /path of directory -name "*.log" -mtime +90 -exec rm -f {} \; it will remove the files older than 3 months
---------------------------------------------------------------------------------------------
Database link
CREATE [PUBLIC] DATABASE LINK
CONNECT TO
IDENTIFIED BY
USING '';
create public database link CRCATTENDANCE connect to finlink identified by finlink using 'VMVERP';
create public database link CRCINTERFACE connect to crcinterface identified by crcinterface using 'VMVERP';
create public database link CRCINTERFACE connect to sierra identified by sierra using 'VMVERP';
select * from@
CRC-finuat:-
-- Create database link
create database link CRCATTENDANCE
connect to HRMS identified by hrms
using 'CRCATTENDANCE';
---------------------------------------------------------------------------------------------------
crctest/******** directory=expdump dumpfile=EXPDP_TEST1.DMP logfile=impdp_tables_test1.log remap_schema=crctest:finlink
308195--old
328742--new
TMP_DAILY_TNS
--------------------------------------------------
St courier
9176989263-->
------------------------
------------------------
97910
73561
---------------------
---------------------
-------------------------------------------------------------
https://www.udacity.com/course/viewer#!/c-ud837/l-4027328704/m-4329550789
https://www.udacity.com/course/developing-android-apps--ud853
https://www.udacity.com/course/advanced-android-app-development--ud855
https://www.udacity.com/course/ux-design-for-mobile-developers--ud849
https://www.udacity.com/course/html5-game-development--cs255
-------------------------------------------------
Row Locks:
select
sid,
serial#,machine
from
v$session
where
sid in (
select
session_id
from
dba_dml_locks )
-------------------------------
select
session_id
from
dba_dml_locks
where
name = '';
--------------------------
SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id FROM dba_objects where object_name='TT_TICKETITEM' and object_type='TABLE' and owner='ABILLITY_PROD') ;
select 'alter system kill session '||''''||a.SESSION_ID||','||b.serial#||',@'||b.inst_id||''';' FROM GV$LOCKED_OBJECT a, gv$session b
where a.session_id=b.sid AND OBJECT_ID=(select object_id FROM dba_objects where object_name='TT_TICKETITEM' and object_type='TABLE' and owner='ABILLITY_PROD')
-----------------------------------------------
-- Utilisation (MB) du FRA
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;
-- FRA Occupants
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
-- Location and size of the FRA
show parameter db_recovery_file_dest
-- Size, used, Reclaimable
SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;
-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;
-- Or change the FRA to a new location (new archives will be created to this new location):
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';
-----------------------------------------------------------------------------
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
/
-------------------------------------------------
SQL> select TABLE_NAME from dict where table_name like 'DBA_S%';
DBA_SCHEDULER_PROGRAMS
DBA_SCHEDULER_JOBS
DBA_SCHEDULER_JOB_CLASSES
DBA_SCHEDULER_WINDOWS
DBA_SCHEDULER_PROGRAM_ARGS
DBA_SCHEDULER_JOB_ARGS
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOB_RUN_DETAIL
DBA_JOBS
--scheduled job run details--------------------
SQL> set linesize 800
SQL> select LOG_ID,LOG_DATE,OWNER,JOB_NAME,STATUS,ERROR#,REQ_START_DATE,ACTUAL_START_DATE,RUN_DURATION,SESSION_ID,CPU_USED,ADDITIONAL_INFO from DBA_SCHEDULER_JOB_RUN_DETAILS;
--------------------------------------------------
--scheduled job logs------
SQL> select LOG_ID,LOG_DATE,OWNER, JOB_NAME,STATUS,USER_NAME,CLIENT_ID,GLOBAL_UID,ADDITIONAL_INFO from DBA_SCHEDULER_JOB_LOG;
-------------------------DBA JOBS---------------
SQL> SELECT LOG_USER,SCHEMA_USER,LAST_DATE, LAST_SEC,THIS_DATE,THIS_SEC, NEXT_DATE, NEXT_SEC,TOTAL_TIME,BROKEN,INTERVAL,FAILURES,WHAT,INSTANCE FROM DBA_JOBS;
----------------------------------------------------SCHEDULED JOBS--------------
SQL> SELECT OWNER,CLIENT_ID, PROGRAM_OWNER, PROGRAM_NAME,SCHEDULE_OWNER,START_DATE END_DATE,ENABLED,STATE,RUN_COUNT,LAST_START_DATE,LAST_RUN_DURATION,
NEXT_RUN_DATE, MAX_RUN_DURATION,COMMENTS FROM DBA_SCHEDULER_JOBS;
SELECT job_name,
destination,
TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
run_duration,
status,
error#
FROM user_scheduler_job_run_details
------------------------------------------------------------------------------
http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_4.shtml
--------------------------------------------------------------------------
TABLESPACE_NAME,Autoextend Size
***********************************
select
file_name "Filename",
tablespace_name "Tablespace",
round(bytes/1024/1024 ,2) "Current Size (MB)",
autoextensible "Autoextend",
round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
where TABLESPACE_NAME='ABILLITY_TABLES';
---------------------------------------------
/bin/find /opt/DB_ARCH/ -type f -mtime +3 -name "arch*.arc" -print -exec rm {} \;' 2>&1 >>/opt/DB_ARCH/archives.deleted.log
-----------------------------------------------------------------------------------------------
To find LAsT DLL:
select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects order by last_ddl_time DESC;
-------------------------------------------------
read *Rman Backup Fails with ORA-19510 and ORA-27045 : No space left on device [ID 283681.1]*
RMAN-03009: failure of backup command on ch00 channel at 07/15/2015 10:14:59
ORA-19510: failed to set size of 2407114 blocks for file "/mnt/dbbackup/bemsvrmbdb01/billsvr1_1_BILLSVR1_S_3208_P_1_T_885073430" (blocksize=8192)
ORA-27045: unable to close the file
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
-----------------------------------------------------------
Nagios
Username: nagiosadmin
Password: n30lqk
----------------------------------------------------------------
Add datafile to tablespace ASM diskgroup DATAGROUP
SQL> alter tablespace USER-TABLESPACE add datafile '+DATAGROUP' size 10G;
Tablespace altered.
SQL>
-----------------------------------------------------------
2 server active node
11gr2 rac
linux or windows
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
9i database:
---------------------------------------------------------------------------------
select * from app_user for update
select * from user_organization_dtl c where c.u_id=0 for update
select * from user_constraints t where t.constraint_name = 'FK_U_ID'
---------------------
iptables -A OUTPUT -d 216.58.220.46 -p tcp --dport 443 -j REJECT
--------------------
Cleanup trace files more than 7 days old
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7 -exec rm {} \;
root> find $DBA/$ORACLE_SID/udump/*.trc -mtime +7 -exec rm {} \;
root> find $DBA/$ORACLE_SID/cdump/*.trc -mtime +7 -exec rm {} \;
Note that the first part of this script (before the –exec) displays all trace files that are more than 7 days old.
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7
find *.trc -mtime +30 -exec rm {} \;
-------------------------------------------------------
ASM Commands:-
. oraenv
+ASM
asmcmd
lsct
ls
pwd
du
find +DATA *
ls -s
ls -l
-------------------------------
BI:
http://test.vmvsystem.com:9704/analytics
http://192.168.0.110:9704/analytics Administrator/Administrator
ssue 2: When you access the Dashboard from the OBIEE Page, you may encounter 500 Internal Error: - servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details. I resolved the issue like this: 1. Edit ./oc4j_bi/j2ee/home/applications/analytics/analytics/WEB-INF/web.xml Replace the port as 9703 instead of 9710 2. Go to $ORACLE_BI_HOME/setup/ $run-sa.sh stop wait for 10 seconds $run-saw.sh stop wait for 10 seconds $run-sa.sh start $run-saw.sh start. Now click on the application Dashboard. It will work fine.
/u01/app/oracle/product/OracleBI/setup
/u01/app/oracle/product/OracleBI/oc4j_bi/bin
oc4j -start
---------------------------------------------
---------------------------------------------------------
set lines 230
set echo off
set term off
set trimspool on
set verif off
set feed off
set pagesize 100
column filename format a20
column tablespace format a15
column status format a10 trunc
column autoextend format a10
-------------------------
=/mnt/dbbackup/bemsvrmbdb02/billsvr11_1_BILLSVR1_S_527_P_1_T_855345927 tag=WEEKLY_BACKUP
channel ch1: restore complete, elapsed time: 01:25:37
Finished restore at 11-OCT-14
recover database;
Starting recover at 11-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=538 devtype=DISK
starting media recovery
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA1/billsvr1/datafile/system.285.860647991'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/11/2014 09:40:28
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 2 seq 283705 lowscn 5983102243733 found to restore
RMAN-06025: no backup of log thread 2 seq 283704 lowscn 5983102129256 found to restore
RMAN-06025: no backup of log thread 1 seq 440755 lowscn 5983102293248 found to restore
RMAN-06025: no backup of log thread 1 seq 440754 lowscn 5983102264068 found to restore
SELECT codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');
---------------------------------------------------------------------------
APEX_VMV:
------------
Apex
http://192.168.0.141:8080/apex
Apex
http://192.168.0.141:8080/apex/f?p=100:LOGIN_DESKTOP:5252404193772
Internal
admin
India12*
http://192.168.0.75:8080/apex/f?p=4550:1:2504728357192:::::
crm
admin
>CRM admin pwd ?
Vmv12*
-------------------------------------------------------------------------------------------------------------
impdp fintemp/fintemp dumpfile=EXPDP_FINANCE1_CRC_080614.DMP logfile=impdp_crc_fintemp_080615.log directory=expdump remap_schema=finance:fintemp TRANSFORM=oid:n:type
impdp boltest/boltest dumpfile=EXPDP_BOLFINANCE_250715.DMP logfile=impdp_boltest_finance_270515.log directory=expdump remap_schema=finance:boltest TRANSFORM=oid:n:type
SELECT username, PASSWORD, 'alter user ' || username || ' identified by values '|| '''' || PASSWORD || '''' ||';' alter_user_sql
FROM dba_users WHERE username = 'SYSMAN'
select 'alter table ' ||table_name|| ' add ||column_name||,table_name,data_type from user_tab_columns where data_type like 'NVARCHAR2';
------------------------------------------------------------------------------------
http://www.dba-oracle.com/t_save_reset_oracle_user_password.htm
------------------
http://oh-bee-eye.blogspot.in/2012/06/oracle-database-arabic-support.html
----------------------------------------------------------------------------------------
Spool charfix.log
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
SHUTDOWN IMMEDIATE;
STARTUP;
-- yes, 2 times startup/shutdown . This is not a typo
SHUTDOWN IMMEDIATE;
STARTUP;
spool off
--------------------------------------------------------------------------------------------------
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username='BOLPROD';
get SID
select
a.sid,
a.serial#,
b.sql_text
from
v$session a,
v$sqlarea b
where
a.sql_address=b.address
and
sid=<>;
--------------------------
SELECT MACHINE, PROGRAM, COUNT(*) FROM V$SESSION GROUP BY MACHINE, PROGRAM;
------------
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
------------------------------
LOCK:
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
column ctime heading "Seconds"
select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||'.'||substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request,
l.ctime
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/
-----------------
Total Size
select DF.TOTAL/1048576 "DataFile Size Mb",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';' from gv$session where username='ABILLITY_PROD';
DELETE FROM table_name;
or
DELETE * FROM table_name;
-------------------
alter system set audit_trail = db|NONE scope=spfile;
alter system set audit_trail='DB','EXTENDED' scope=spfile;
select value from v$parameter where name = 'audit_file_dest';
SQL> audit all by finance by access;
Audit succeeded.
SQL> audit select table,update table,delect table,insert table by finance by access;
audit select table,update table,delect table,insert table by finance by access
*
ERROR at line 1:
ORA-00956: missing or invalid auditing option
SQL> audit select table,update table,delete table,insert table by finance by access;
Audit succeeded.
SQL> audit execute procedure by finance by access;
Audit succeeded.
SQL> select count(*) from aud$;
COUNT(*)
----------
344273
SQL> select count(*) from aud$ where ntimestamp# > sysdate - 21;
COUNT(*)
----------
53550
SQL> delete from aud$ where ntimestamp# > sysdate - 21;
53550 rows deleted.
----------------------------------
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'VMVERP');
END;
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
-------------------------------------------------
find . -mtime +0 # find files modified greater than 24 hours ago
find . -mtime 0 # find files modified between now and 1 day ago
# (i.e., in the past 24 hours only)
find . -mtime -1 # find files modified less than 1 day ago (SAME AS -mtime 0)
find . -mtime 1 # find files modified between 24 and 48 hours ago
find . -mtime +1 # find files modified more than 48 hours ago
The following may only work on GNU?
find . -mmin +5 -mmin -10 # find files modified between
# 6 and 9 minutes ago
find / -mmin -10 # modified less than 10 minutes ago
find /path of directory -name "*.log" -mtime +90 -exec ls -ltr {} \; it will list the files older than 3 months
find /path of directory -name "*.log" -mtime +90 -exec rm -f {} \; it will remove the files older than 3 months
---------------------------------------------------------------------------------------------
Database link
CREATE [PUBLIC] DATABASE LINK
CONNECT TO
IDENTIFIED BY
USING '
create public database link CRCATTENDANCE connect to finlink identified by finlink using 'VMVERP';
create public database link CRCINTERFACE connect to crcinterface identified by crcinterface using 'VMVERP';
create public database link CRCINTERFACE connect to sierra identified by sierra using 'VMVERP';
select * from
CRC-finuat:-
-- Create database link
create database link CRCATTENDANCE
connect to HRMS identified by hrms
using 'CRCATTENDANCE';
---------------------------------------------------------------------------------------------------
crctest/******** directory=expdump dumpfile=EXPDP_TEST1.DMP logfile=impdp_tables_test1.log remap_schema=crctest:finlink
308195--old
328742--new
TMP_DAILY_TNS
--------------------------------------------------
St courier
9176989263-->
------------------------
------------------------
97910
73561
---------------------
---------------------
-------------------------------------------------------------
https://www.udacity.com/course/viewer#!/c-ud837/l-4027328704/m-4329550789
https://www.udacity.com/course/developing-android-apps--ud853
https://www.udacity.com/course/advanced-android-app-development--ud855
https://www.udacity.com/course/ux-design-for-mobile-developers--ud849
https://www.udacity.com/course/html5-game-development--cs255
-------------------------------------------------
Row Locks:
select
sid,
serial#,machine
from
v$session
where
sid in (
select
session_id
from
dba_dml_locks )
-------------------------------
select
session_id
from
dba_dml_locks
where
name = '
--------------------------
SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id FROM dba_objects where object_name='TT_TICKETITEM' and object_type='TABLE' and owner='ABILLITY_PROD') ;
select 'alter system kill session '||''''||a.SESSION_ID||','||b.serial#||',@'||b.inst_id||''';' FROM GV$LOCKED_OBJECT a, gv$session b
where a.session_id=b.sid AND OBJECT_ID=(select object_id FROM dba_objects where object_name='TT_TICKETITEM' and object_type='TABLE' and owner='ABILLITY_PROD')
-----------------------------------------------
-- Utilisation (MB) du FRA
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;
-- FRA Occupants
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
-- Location and size of the FRA
show parameter db_recovery_file_dest
-- Size, used, Reclaimable
SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;
-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;
-- Or change the FRA to a new location (new archives will be created to this new location):
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';
-----------------------------------------------------------------------------
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
/
-------------------------------------------------
SQL> select TABLE_NAME from dict where table_name like 'DBA_S%';
DBA_SCHEDULER_PROGRAMS
DBA_SCHEDULER_JOBS
DBA_SCHEDULER_JOB_CLASSES
DBA_SCHEDULER_WINDOWS
DBA_SCHEDULER_PROGRAM_ARGS
DBA_SCHEDULER_JOB_ARGS
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOB_RUN_DETAIL
DBA_JOBS
--scheduled job run details--------------------
SQL> set linesize 800
SQL> select LOG_ID,LOG_DATE,OWNER,JOB_NAME,STATUS,ERROR#,REQ_START_DATE,ACTUAL_START_DATE,RUN_DURATION,SESSION_ID,CPU_USED,ADDITIONAL_INFO from DBA_SCHEDULER_JOB_RUN_DETAILS;
--------------------------------------------------
--scheduled job logs------
SQL> select LOG_ID,LOG_DATE,OWNER, JOB_NAME,STATUS,USER_NAME,CLIENT_ID,GLOBAL_UID,ADDITIONAL_INFO from DBA_SCHEDULER_JOB_LOG;
-------------------------DBA JOBS---------------
SQL> SELECT LOG_USER,SCHEMA_USER,LAST_DATE, LAST_SEC,THIS_DATE,THIS_SEC, NEXT_DATE, NEXT_SEC,TOTAL_TIME,BROKEN,INTERVAL,FAILURES,WHAT,INSTANCE FROM DBA_JOBS;
----------------------------------------------------SCHEDULED JOBS--------------
SQL> SELECT OWNER,CLIENT_ID, PROGRAM_OWNER, PROGRAM_NAME,SCHEDULE_OWNER,START_DATE END_DATE,ENABLED,STATE,RUN_COUNT,LAST_START_DATE,LAST_RUN_DURATION,
NEXT_RUN_DATE, MAX_RUN_DURATION,COMMENTS FROM DBA_SCHEDULER_JOBS;
SELECT job_name,
destination,
TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
run_duration,
status,
error#
FROM user_scheduler_job_run_details
------------------------------------------------------------------------------
http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_4.shtml
--------------------------------------------------------------------------
TABLESPACE_NAME,Autoextend Size
***********************************
select
file_name "Filename",
tablespace_name "Tablespace",
round(bytes/1024/1024 ,2) "Current Size (MB)",
autoextensible "Autoextend",
round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
where TABLESPACE_NAME='ABILLITY_TABLES';
---------------------------------------------
/bin/find /opt/DB_ARCH/ -type f -mtime +3 -name "arch*.arc" -print -exec rm {} \;' 2>&1 >>/opt/DB_ARCH/archives.deleted.log
-----------------------------------------------------------------------------------------------
To find LAsT DLL:
select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects order by last_ddl_time DESC;
-------------------------------------------------
read *Rman Backup Fails with ORA-19510 and ORA-27045 : No space left on device [ID 283681.1]*
RMAN-03009: failure of backup command on ch00 channel at 07/15/2015 10:14:59
ORA-19510: failed to set size of 2407114 blocks for file "/mnt/dbbackup/bemsvrmbdb01/billsvr1_1_BILLSVR1_S_3208_P_1_T_885073430" (blocksize=8192)
ORA-27045: unable to close the file
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
-----------------------------------------------------------
Nagios
Username: nagiosadmin
Password: n30lqk
----------------------------------------------------------------
Add datafile to tablespace ASM diskgroup DATAGROUP
SQL> alter tablespace USER-TABLESPACE add datafile '+DATAGROUP' size 10G;
Tablespace altered.
SQL>
-----------------------------------------------------------
2 server active node
11gr2 rac
linux or windows
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
9i database:
---------------------------------------------------------------------------------
select * from app_user for update
select * from user_organization_dtl c where c.u_id=0 for update
select * from user_constraints t where t.constraint_name = 'FK_U_ID'
---------------------
iptables -A OUTPUT -d 216.58.220.46 -p tcp --dport 443 -j REJECT
--------------------
Cleanup trace files more than 7 days old
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7 -exec rm {} \;
root> find $DBA/$ORACLE_SID/udump/*.trc -mtime +7 -exec rm {} \;
root> find $DBA/$ORACLE_SID/cdump/*.trc -mtime +7 -exec rm {} \;
Note that the first part of this script (before the –exec) displays all trace files that are more than 7 days old.
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7
find *.trc -mtime +30 -exec rm {} \;
-------------------------------------------------------
ASM Commands:-
. oraenv
+ASM
asmcmd
lsct
ls
pwd
du
find +DATA *
ls -s
ls -l
-------------------------------
BI:
http://test.vmvsystem.com:9704/analytics
http://192.168.0.110:9704/analytics Administrator/Administrator
ssue 2: When you access the Dashboard from the OBIEE Page, you may encounter 500 Internal Error: - servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details. I resolved the issue like this: 1. Edit ./oc4j_bi/j2ee/home/applications/analytics/analytics/WEB-INF/web.xml Replace the port as 9703 instead of 9710 2. Go to $ORACLE_BI_HOME/setup/ $run-sa.sh stop wait for 10 seconds $run-saw.sh stop wait for 10 seconds $run-sa.sh start $run-saw.sh start. Now click on the application Dashboard. It will work fine.
/u01/app/oracle/product/OracleBI/setup
/u01/app/oracle/product/OracleBI/oc4j_bi/bin
oc4j -start
---------------------------------------------
Common scripts
-- - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - -
-- NAME: DBUPGDIAG.SQL
-- Version: 1.2
-- Executed as SYS as sysdba
-- ------------------------------------------------------------------------
-- AUTHOR:
-- Raja Ganesh and Agrim Pandit - Oracle Support Services - DataServer Group
-- Copyright 2008, Oracle Corporation
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is intended to provide a user friendly output to diagonise
-- the status of the database before (or) after upgrade. The script will
-- create a file called db_upg_diag__.log in your local
-- working directory. This does not make any DDL / DML modifications.
--
-- This script will work in both Windows and Unix platforms from database
-- version 9.2 or higher.
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is NOT
-- supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts on a test instance initially.
-- ------------------------------------------------------------------------
--
--
col TODAY NEW_VALUE _DATE
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,'fmMonth DD, YYYY') TODAY from DUAL;
select version from v$instance;
set termout on
set echo off
set feedback off
set head off
set verify off
Prompt
PROMPT Enter location for Spooled output:
Prompt
DEFINE log_path = &1
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,'dd_Mon_yyyy_hhmi') timecol,'.log' spool_extension FROM
sys.dual;
column output new_value dbname
SELECT value || '_' output FROM v$parameter WHERE name = 'db_name';
spool &log_path/db_upg_diag_&&dbname&×tamp&&suffix
set linesize 150
set pages 100
set trim on
set trims on
col Compatible for a35
col comp_id for a12
col comp_name for a40
col org_version for a11
col prv_version for a11
col owner for a12
col object_name for a40
col object_type for a40
col Wordsize for a25
col Metadata for a8
col 'Initial DB Creation Info' for a35
col 'Total Invalid JAVA objects' for a45
col 'Role' for a30
col 'User Existence' for a27
col "JAVAVM TESTING" for a15
Prompt
Prompt
set feedback off head off
select LPAD('*** Start of LogFile ***',50) from dual;
select LPAD('Oracle Database Upgrade Diagnostic Utility',44)||
LPAD(TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'),26) from dual;
Prompt
Prompt ===============
Prompt Hostname
Prompt ===============
select host_name from v$instance;
Prompt
Prompt ===============
Prompt Database Name
Prompt ===============
select name from v$database;
Prompt
Prompt ===============
Prompt Database Uptime
Prompt ===============
SELECT to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup Time"
FROM v$instance;
Prompt
Prompt =================
Prompt Database Wordsize
Prompt =================
SELECT distinct('This is a ' || (length(addr)*4) || '-bit database') "WordSize"
FROM v$process;
Prompt
Prompt ================
Prompt Software Version
Prompt ================
SELECT * FROM v$version;
Prompt
Prompt =============
Prompt Compatibility
Prompt =============
SELECT 'Compatibility is set as '||value Compatible
FROM v$parameter WHERE name ='compatible';
Prompt
Prompt ================
Prompt Archive Log Mode
Prompt ================
Prompt
archive log list
Prompt
Prompt ================
Prompt Auditing Check
Prompt ================
Prompt
set head on
show parameter audit
Prompt
Prompt ================
Prompt Cluster Check
Prompt ================
show parameter cluster_database
Prompt
DOC
################################################################
If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
upgrading the database
################################################################
#
Prompt
Prompt ===========================================
Prompt Tablespace and the owner of the aud$ table
Prompt ===========================================
select owner,tablespace_name from dba_extents where segment_name='AUD$' group by owner,tablespace_name;
Prompt
Prompt ============================================================================
Prompt count of records in the sys.aud$ table where dbid is null- Standard Auditing
Prompt ============================================================================
Prompt
set head off
select count(*) as Records from sys.aud$ where dbid is null;
Prompt
Prompt
Prompt ============================================================================================
Prompt count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
Prompt ============================================================================================
set head off
select count(*) from system.aud$ where dbid is null;
Prompt
Prompt
Prompt =============================================================================
Prompt count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
Prompt =============================================================================
set head off
select count(*) from sys.fga_log$ where dbid is null;
Prompt
Prompt
prompt
Prompt ==========================================
Prompt Oracle Label Security is installed or not
Prompt ==========================================
set head off
SELECT case count(schema)
WHEN 0 THEN 'Oracle Label Security is NOT installed at database level'
ELSE 'Oracle Label Security is installed '
END "Oracle Label Security Check"
FROM dba_registry
WHERE schema='LBACSYS';
Prompt
Prompt ================
Prompt Number of AQ Records in Message Queue Tables
Prompt ================
Prompt
SET SERVEROUTPUT ON SIZE 100000
declare
V_COUNT NUMBER;
cursor c1 is
select owner,queue_table from dba_queue_tables where owner in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP','WMSYS');
begin
for c in c1
loop
execute immediate 'select count(1) from ' || c.owner || '.' || c.queue_table into v_count;
dbms_output.put_line(c.owner || ' - ' || c.queue_table || ' - ' || v_count);
end loop;
END;
/
Prompt
Prompt ================
Prompt Time Zone version
Prompt ================
Prompt
SELECT version from v$timezone_file;
Prompt
Prompt ================
Prompt Local Listener
Prompt ================
Prompt
select substr(value,1,50) "Local Listener" from v$parameter where name='local_listener';
Prompt
Prompt ================
Prompt Default and Temporary Tablespaces By User
Prompt ================
Prompt
set head on
COLUMN USERNAME FORMAT A28
COLUMN TEMPORARY_TABLESPACE FORMAT A22
COLUMN DEFAULT_TABLESPACE FORMAT A22
SELECT username, temporary_tablespace,default_tablespace FROM DBA_USERS;
Prompt
Prompt
Prompt ================
Prompt Component Status
Prompt ================
Prompt
SET SERVEROUTPUT ON;
DECLARE
ORG_VERSION varchar2(12);
PRV_VERSION varchar2(12);
P_VERSION VARCHAR2(10);
BEGIN
SELECT version INTO p_version
FROM registry$ WHERE cid='CATPROC' ;
IF SUBSTR(p_version,1,5) = '9.2.0' THEN
DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)||
RPAD('Status',10) ||RPAD('Version', 15));
DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')||
RPAD(' ',10,'-') ||RPAD(' ',15,'-'));
FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name,
dr.status Status,SUBSTR(dr.version,1,15) version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) ||
RPAD(SUBSTR(x.comp_name,1,35),35)||
RPAD(x.status,10) || RPAD(x.version, 15));
END LOOP;
ELSIF SUBSTR(p_version,1,5) != '9.2.0' THEN
DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)||
RPAD('Status',10) ||RPAD('Version', 15)||
RPAD('Org_Version',15)||RPAD('Prv_Version',15));
DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')||
RPAD(' ',10,'-')||RPAD(' ',15,'-')||RPAD(' ',15,'-')||
RPAD(' ',15,'-'));
FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status,
SUBSTR(dr.version,1,11) version,org_version,prv_version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) ||
RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) ||
RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15));
END LOOP;
END IF;
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt
Prompt
Prompt ======================================================
Prompt List of Invalid Database Objects Owned by SYS / SYSTEM
Prompt ======================================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYS','SYSTEM');
Prompt
DOC
################################################################
If there are no Invalid objects below will result in zero rows.
################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYS','SYSTEM')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ================================
Prompt List of Invalid Database Objects
Prompt ================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects
WHERE status='INVALID'
AND owner in
('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP');
Prompt
DOC
################################################################
If there are no Invalid objects below will result in zero rows.
################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ======================================================
Prompt Count of Invalids by Schema
Prompt ======================================================
Prompt
select owner,object_type,count(*) from dba_objects where status='INVALID'
group by owner,object_type order by owner,object_type ;
Prompt ==============================================================
Prompt Identifying whether a database was created as 32-bit or 64-bit
Prompt ==============================================================
Prompt
DOC
###########################################################################
Result referencing the string 'B023' ==> Database was created as 32-bit
Result referencing the string 'B047' ==> Database was created as 64-bit
When String results in 'B023' and when upgrading database to 10.2.0.3.0
(64-bit) , For known issue refer below articles
Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
Upgrading Or Patching Databases To 10.2.0.3
Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
###########################################################################
#
Prompt
SELECT SUBSTR(metadata,109,4) "Metadata",
CASE SUBSTR(metadata,109,4)
WHEN 'B023' THEN 'Database was created as 32-bit'
WHEN 'B047' THEN 'Database was created as 64-bit'
ELSE 'Metadata not Matching'
END "Initial DB Creation Info"
FROM sys.kopm$;
Prompt
Prompt ===================================================
Prompt Number of Duplicate Objects Owned by SYS and SYSTEM
Prompt ===================================================
Prompt
Prompt Counting duplicate objects ....
Prompt
SELECT count(1)
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
from dba_objects
where owner = 'SYS'
AND owner = 'SYSTEM'
AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH')) ;
Prompt
Prompt =========================================
Prompt Duplicate Objects Owned by SYS and SYSTEM
Prompt =========================================
Prompt
Prompt Querying duplicate objects ....
Prompt
SELECT object_name, object_type, subobject_name, object_id
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
FROM dba_objects
WHERE owner = 'SYS'
AND owner = 'SYSTEM'
AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH')) ;
Prompt
DOC
################################################################################
If any objects found please follow below article.
Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Read the Exceptions carefully before taking actions.
################################################################################
#
Prompt
Prompt ========================
Prompt Password protected roles
Prompt ========================
Prompt
DOC
################################################################################
In version 11.2 password protected roles are no longer enabled by default so if
an application relies on such roles being enabled by default and no action is
performed to allow the user to enter the password with the set role command, it
is recommended to remove the password from those roles (to allow for existing
privileges to remain available). For more information see:
Note 745407.1 : What Roles Can Be Set as Default for a User?
################################################################################
#
Prompt
Prompt Querying for password protected roles ....
Prompt
break on "Password protected Role"
select r.ROLE "Password protected Role",
p.grantee "Assigned by default to user"
from dba_roles r, dba_role_privs p
where r.PASSWORD_REQUIRED = 'YES' and p.GRANTED_ROLE = r.role
and p.default_role = 'YES'
and p.grantee <> 'SYS' and r.role not in
(select role from dba_application_roles);
Prompt
Prompt ================
Prompt JVM Verification
Prompt ================
Prompt
SET SERVEROUTPUT ON
DECLARE
V_CT NUMBER;
P_VERSION VARCHAR2(10);
BEGIN
-- If so, get the version of the JAVAM component
EXECUTE IMMEDIATE 'SELECT version FROM registry$ WHERE cid=''JAVAVM''
AND status <> 99' INTO p_version;
SELECT count(*) INTO v_ct FROM dba_objects
WHERE object_type LIKE '%JAVA%' AND owner='SYS';
IF SUBSTR(p_version,1,5) = '8.1.7' THEN
IF v_ct>=6787 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,5) = '9.0.1' THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,5) = '9.2.0' THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,6) = '10.1.0' THEN
IF v_ct>=13866 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,6) = '10.2.0' THEN
IF v_ct>=14113 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - NOT Installed. Below results can be ignored');
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt ================================================
Prompt Checking Existence of Java-Based Users and Roles
Prompt ================================================
Prompt
DOC
################################################################################
There should not be any Java Based users for database version 9.0.1 and above.
If any users found, it is faulty JVM.
################################################################################
#
Prompt
SELECT CASE count(username)
WHEN 0 THEN 'No Java Based Users'
ELSE 'There are '||count(*)||' JAVA based users'
END "User Existence"
FROM dba_users WHERE username LIKE '%AURORA%' AND username LIKE '%OSE%';
Prompt
DOC
###############################################################
Healthy JVM Should contain Six Roles.
If there are more or less than six role, JVM is inconsistent.
###############################################################
#
Prompt
SELECT CASE count(role)
WHEN 0 THEN 'No JAVA related Roles'
ELSE 'There are '||count(role)||' JAVA related roles'
END "Role"
FROM dba_roles
WHERE role LIKE '%JAVA%';
Prompt
Prompt Roles
Prompt
SELECT role FROM dba_roles WHERE role LIKE '%JAVA%';
set head off
Prompt
Prompt =========================================
Prompt List of Invalid Java Objects owned by SYS
Prompt =========================================
SELECT CASE count(*)
WHEN 0 THEN 'There are no SYS owned invalid JAVA objects'
ELSE 'There are '||count(*)||' SYS owned invalid JAVA objects'
END "Total Invalid JAVA objects"
FROM dba_objects
WHERE object_type LIKE '%JAVA%'
AND status='INVALID'
AND owner='SYS';
Prompt
DOC
#################################################################
Check the status of the main JVM interface packages DBMS_JAVA
and INITJVMAUX and make sure it is VALID.
If there are no Invalid objects below will result in zero rows.
#################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE object_type LIKE '%JAVA%'
AND status='INVALID'
AND owner='SYS';
set feedback off
Prompt
DOC
#################################################################
If the JAVAVM component is not installed in the database (for
example, after creating the database with custom scripts), the
next query will report the following error:
select dbms_java.longname('foo') "JAVAVM TESTING" from dual
*
ERROR at line 1:
ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
If the JAVAVM component is installed, the query should succeed
with 'foo' as result.
#################################################################
#
Prompt
set heading on
select dbms_java.longname('foo') "JAVAVM TESTING" from dual;
set heading off
Prompt
SET FEEDBACK ON HEAD ON serveroutput on
Prompt ===================================
Prompt Oracle Multimedia/InterMedia status
Prompt ===================================
Prompt
DECLARE
v_count NUMBER;
v_version varchar2(200);
v_user_count number;
v_status VARCHAR2(200);
v_xdb_installed NUMBER;
v_xdk_installed NUMBER;
v_javavm_installed NUMBER;
TYPE string_tt IS TABLE OF VARCHAR2 (100)
INDEX BY BINARY_INTEGER;
v_user string_tt;
BEGIN
v_count := 0;
v_version := '';
v_user_count := 0;
v_status := '';
v_xdb_installed := 0;
v_xdk_installed := 0;
v_javavm_installed := 0;
SELECT 1,version,status
INTO v_count, v_version, v_status
FROM dba_registry
WHERE comp_id='ORDIM';
IF v_count > 0 then
DBMS_OUTPUT.PUT_LINE ('.');
DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is installed and listed with the following version: '||v_version||' and status: '||v_status);
DBMS_OUTPUT.PUT_LINE ('.');
/* check if all users are installed.*/
v_user(1) := 'ORDSYS';
v_user(2) := 'ORDPLUGINS';
v_user(3) := 'MDSYS';
v_user(4) := 'SI_INFORMTN_SCHEMA';
v_user(5) := 'ORDDATA';
DBMS_OUTPUT.PUT_LINE('Checking for installed Database Schemas...');
FOR i IN v_user.first .. v_user.last LOOP
SELECT COUNT(username)
INTO v_user_count
FROM dba_users
WHERE username = v_user(I);
/* ORDDATA user only exists starting 11.2 so no test if v_version is different */
IF v_user(i) = 'ORDDATA' AND SUBSTR(V_VERSION,1,6) NOT IN ('11.2.0','12.1.0') THEN v_user_count :=2;
END IF;
/* SI_INFORMTN_SCHEMA user only exists starting 11.2 so no test if v_version is different */
IF v_user(i) = 'SI_INFORMTN_SCHEMA' AND SUBSTR(V_VERSION,1,2) NOT IN ('10','11','12') THEN v_user_count :=2;
END IF;
CASE v_user_count
WHEN 0 THEN DBMS_OUTPUT.PUT_LINE (v_user(I)||' user does not exist.');
WHEN 2 THEN NULL; -- user does not exist in that version
ELSE DBMS_OUTPUT.PUT_LINE (v_user(I)||' user exists.');
END CASE;
END LOOP;
DBMS_OUTPUT.PUT_LINE('.');
/* Prerequisites Check*/
DBMS_OUTPUT.PUT_LINE ('Checking for Prerequisite Components...');
/* for versions >= 10.2 we will verify, if XDB and XDK are installed and valid */
SELECT COUNT(1)
INTO v_javavm_installed
FROM dba_registry
WHERE comp_id='JAVAVM';
IF v_javavm_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('JAVAVM is not installed or not valid');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM installed and listed as valid');
END IF;
IF SUBSTR(V_VERSION,1,2) IN ('11','12') OR
SUBSTR(V_VERSION,1,6) = ('10.2.0') THEN
SELECT COUNT(1)
INTO v_xdk_installed
FROM dba_registry
WHERE comp_id='XML';
IF v_xdk_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDK is not installed or not valid');
ELSE
DBMS_OUTPUT.PUT_LINE('XDK installed and listed as valid');
END IF;
SELECT COUNT(1)
INTO v_xdb_installed
FROM dba_registry
WHERE comp_id='XDB';
IF v_xdb_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDB is not installed or not valid');
ELSE
DBMS_OUTPUT.PUT_LINE ('XDB installed and listed as valid');
END IF;
END IF;
/* for versions >= 11 we run validate_ordim */
DBMS_OUTPUT.PUT_LINE ('Validating Oracle Multimedia/interMedia...(no output if component status is valid)');
IF SUBSTR(V_VERSION,1,2) IN ('11','12') THEN
EXECUTE IMMEDIATE 'begin validate_ordim; end;';
ELSIF SUBSTR(V_VERSION,1,2) IN ('8.','9.','10') AND v_status <> 'VALID' THEN
DBMS_OUTPUT.PUT_LINE('Please run $ORACLE_HOME/ord/im/admin/imchk.sql to display details about invalid interMedia installation');
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is NOT installed at database level');
END;
/
set feedback off head off
select LPAD('*** End of LogFile ***',50) from dual;
set feedback on head on
Prompt
spool off
Prompt
set heading off
set heading off
set feedback off
select 'Upload db_upg_diag_&&dbname&×tamp&&suffix from "&log_path" directory'
from dual;
set heading on
set feedback on
Prompt
-- - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - -
-- NAME: DBUPGDIAG.SQL
-- Version: 1.2
-- Executed as SYS as sysdba
-- ------------------------------------------------------------------------
-- AUTHOR:
-- Raja Ganesh and Agrim Pandit - Oracle Support Services - DataServer Group
-- Copyright 2008, Oracle Corporation
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is intended to provide a user friendly output to diagonise
-- the status of the database before (or) after upgrade. The script will
-- create a file called db_upg_diag_
-- working directory. This does not make any DDL / DML modifications.
--
-- This script will work in both Windows and Unix platforms from database
-- version 9.2 or higher.
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is NOT
-- supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts on a test instance initially.
-- ------------------------------------------------------------------------
--
--
col TODAY NEW_VALUE _DATE
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,'fmMonth DD, YYYY') TODAY from DUAL;
select version from v$instance;
set termout on
set echo off
set feedback off
set head off
set verify off
Prompt
PROMPT Enter location for Spooled output:
Prompt
DEFINE log_path = &1
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,'dd_Mon_yyyy_hhmi') timecol,'.log' spool_extension FROM
sys.dual;
column output new_value dbname
SELECT value || '_' output FROM v$parameter WHERE name = 'db_name';
spool &log_path/db_upg_diag_&&dbname&×tamp&&suffix
set linesize 150
set pages 100
set trim on
set trims on
col Compatible for a35
col comp_id for a12
col comp_name for a40
col org_version for a11
col prv_version for a11
col owner for a12
col object_name for a40
col object_type for a40
col Wordsize for a25
col Metadata for a8
col 'Initial DB Creation Info' for a35
col 'Total Invalid JAVA objects' for a45
col 'Role' for a30
col 'User Existence' for a27
col "JAVAVM TESTING" for a15
Prompt
Prompt
set feedback off head off
select LPAD('*** Start of LogFile ***',50) from dual;
select LPAD('Oracle Database Upgrade Diagnostic Utility',44)||
LPAD(TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'),26) from dual;
Prompt
Prompt ===============
Prompt Hostname
Prompt ===============
select host_name from v$instance;
Prompt
Prompt ===============
Prompt Database Name
Prompt ===============
select name from v$database;
Prompt
Prompt ===============
Prompt Database Uptime
Prompt ===============
SELECT to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup Time"
FROM v$instance;
Prompt
Prompt =================
Prompt Database Wordsize
Prompt =================
SELECT distinct('This is a ' || (length(addr)*4) || '-bit database') "WordSize"
FROM v$process;
Prompt
Prompt ================
Prompt Software Version
Prompt ================
SELECT * FROM v$version;
Prompt
Prompt =============
Prompt Compatibility
Prompt =============
SELECT 'Compatibility is set as '||value Compatible
FROM v$parameter WHERE name ='compatible';
Prompt
Prompt ================
Prompt Archive Log Mode
Prompt ================
Prompt
archive log list
Prompt
Prompt ================
Prompt Auditing Check
Prompt ================
Prompt
set head on
show parameter audit
Prompt
Prompt ================
Prompt Cluster Check
Prompt ================
show parameter cluster_database
Prompt
DOC
################################################################
If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
upgrading the database
################################################################
#
Prompt
Prompt ===========================================
Prompt Tablespace and the owner of the aud$ table
Prompt ===========================================
select owner,tablespace_name from dba_extents where segment_name='AUD$' group by owner,tablespace_name;
Prompt
Prompt ============================================================================
Prompt count of records in the sys.aud$ table where dbid is null- Standard Auditing
Prompt ============================================================================
Prompt
set head off
select count(*) as Records from sys.aud$ where dbid is null;
Prompt
Prompt
Prompt ============================================================================================
Prompt count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
Prompt ============================================================================================
set head off
select count(*) from system.aud$ where dbid is null;
Prompt
Prompt
Prompt =============================================================================
Prompt count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
Prompt =============================================================================
set head off
select count(*) from sys.fga_log$ where dbid is null;
Prompt
Prompt
prompt
Prompt ==========================================
Prompt Oracle Label Security is installed or not
Prompt ==========================================
set head off
SELECT case count(schema)
WHEN 0 THEN 'Oracle Label Security is NOT installed at database level'
ELSE 'Oracle Label Security is installed '
END "Oracle Label Security Check"
FROM dba_registry
WHERE schema='LBACSYS';
Prompt
Prompt ================
Prompt Number of AQ Records in Message Queue Tables
Prompt ================
Prompt
SET SERVEROUTPUT ON SIZE 100000
declare
V_COUNT NUMBER;
cursor c1 is
select owner,queue_table from dba_queue_tables where owner in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP','WMSYS');
begin
for c in c1
loop
execute immediate 'select count(1) from ' || c.owner || '.' || c.queue_table into v_count;
dbms_output.put_line(c.owner || ' - ' || c.queue_table || ' - ' || v_count);
end loop;
END;
/
Prompt
Prompt ================
Prompt Time Zone version
Prompt ================
Prompt
SELECT version from v$timezone_file;
Prompt
Prompt ================
Prompt Local Listener
Prompt ================
Prompt
select substr(value,1,50) "Local Listener" from v$parameter where name='local_listener';
Prompt
Prompt ================
Prompt Default and Temporary Tablespaces By User
Prompt ================
Prompt
set head on
COLUMN USERNAME FORMAT A28
COLUMN TEMPORARY_TABLESPACE FORMAT A22
COLUMN DEFAULT_TABLESPACE FORMAT A22
SELECT username, temporary_tablespace,default_tablespace FROM DBA_USERS;
Prompt
Prompt
Prompt ================
Prompt Component Status
Prompt ================
Prompt
SET SERVEROUTPUT ON;
DECLARE
ORG_VERSION varchar2(12);
PRV_VERSION varchar2(12);
P_VERSION VARCHAR2(10);
BEGIN
SELECT version INTO p_version
FROM registry$ WHERE cid='CATPROC' ;
IF SUBSTR(p_version,1,5) = '9.2.0' THEN
DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)||
RPAD('Status',10) ||RPAD('Version', 15));
DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')||
RPAD(' ',10,'-') ||RPAD(' ',15,'-'));
FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name,
dr.status Status,SUBSTR(dr.version,1,15) version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) ||
RPAD(SUBSTR(x.comp_name,1,35),35)||
RPAD(x.status,10) || RPAD(x.version, 15));
END LOOP;
ELSIF SUBSTR(p_version,1,5) != '9.2.0' THEN
DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)||
RPAD('Status',10) ||RPAD('Version', 15)||
RPAD('Org_Version',15)||RPAD('Prv_Version',15));
DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')||
RPAD(' ',10,'-')||RPAD(' ',15,'-')||RPAD(' ',15,'-')||
RPAD(' ',15,'-'));
FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status,
SUBSTR(dr.version,1,11) version,org_version,prv_version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) ||
RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) ||
RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15));
END LOOP;
END IF;
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt
Prompt
Prompt ======================================================
Prompt List of Invalid Database Objects Owned by SYS / SYSTEM
Prompt ======================================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYS','SYSTEM');
Prompt
DOC
################################################################
If there are no Invalid objects below will result in zero rows.
################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYS','SYSTEM')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ================================
Prompt List of Invalid Database Objects
Prompt ================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects
WHERE status='INVALID'
AND owner in
('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP');
Prompt
DOC
################################################################
If there are no Invalid objects below will result in zero rows.
################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ======================================================
Prompt Count of Invalids by Schema
Prompt ======================================================
Prompt
select owner,object_type,count(*) from dba_objects where status='INVALID'
group by owner,object_type order by owner,object_type ;
Prompt ==============================================================
Prompt Identifying whether a database was created as 32-bit or 64-bit
Prompt ==============================================================
Prompt
DOC
###########################################################################
Result referencing the string 'B023' ==> Database was created as 32-bit
Result referencing the string 'B047' ==> Database was created as 64-bit
When String results in 'B023' and when upgrading database to 10.2.0.3.0
(64-bit) , For known issue refer below articles
Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
Upgrading Or Patching Databases To 10.2.0.3
Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
###########################################################################
#
Prompt
SELECT SUBSTR(metadata,109,4) "Metadata",
CASE SUBSTR(metadata,109,4)
WHEN 'B023' THEN 'Database was created as 32-bit'
WHEN 'B047' THEN 'Database was created as 64-bit'
ELSE 'Metadata not Matching'
END "Initial DB Creation Info"
FROM sys.kopm$;
Prompt
Prompt ===================================================
Prompt Number of Duplicate Objects Owned by SYS and SYSTEM
Prompt ===================================================
Prompt
Prompt Counting duplicate objects ....
Prompt
SELECT count(1)
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
from dba_objects
where owner = 'SYS'
AND owner = 'SYSTEM'
AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH')) ;
Prompt
Prompt =========================================
Prompt Duplicate Objects Owned by SYS and SYSTEM
Prompt =========================================
Prompt
Prompt Querying duplicate objects ....
Prompt
SELECT object_name, object_type, subobject_name, object_id
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
FROM dba_objects
WHERE owner = 'SYS'
AND owner = 'SYSTEM'
AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH')) ;
Prompt
DOC
################################################################################
If any objects found please follow below article.
Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Read the Exceptions carefully before taking actions.
################################################################################
#
Prompt
Prompt ========================
Prompt Password protected roles
Prompt ========================
Prompt
DOC
################################################################################
In version 11.2 password protected roles are no longer enabled by default so if
an application relies on such roles being enabled by default and no action is
performed to allow the user to enter the password with the set role command, it
is recommended to remove the password from those roles (to allow for existing
privileges to remain available). For more information see:
Note 745407.1 : What Roles Can Be Set as Default for a User?
################################################################################
#
Prompt
Prompt Querying for password protected roles ....
Prompt
break on "Password protected Role"
select r.ROLE "Password protected Role",
p.grantee "Assigned by default to user"
from dba_roles r, dba_role_privs p
where r.PASSWORD_REQUIRED = 'YES' and p.GRANTED_ROLE = r.role
and p.default_role = 'YES'
and p.grantee <> 'SYS' and r.role not in
(select role from dba_application_roles);
Prompt
Prompt ================
Prompt JVM Verification
Prompt ================
Prompt
SET SERVEROUTPUT ON
DECLARE
V_CT NUMBER;
P_VERSION VARCHAR2(10);
BEGIN
-- If so, get the version of the JAVAM component
EXECUTE IMMEDIATE 'SELECT version FROM registry$ WHERE cid=''JAVAVM''
AND status <> 99' INTO p_version;
SELECT count(*) INTO v_ct FROM dba_objects
WHERE object_type LIKE '%JAVA%' AND owner='SYS';
IF SUBSTR(p_version,1,5) = '8.1.7' THEN
IF v_ct>=6787 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,5) = '9.0.1' THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,5) = '9.2.0' THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,6) = '10.1.0' THEN
IF v_ct>=13866 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,6) = '10.2.0' THEN
IF v_ct>=14113 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - NOT Installed. Below results can be ignored');
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt ================================================
Prompt Checking Existence of Java-Based Users and Roles
Prompt ================================================
Prompt
DOC
################################################################################
There should not be any Java Based users for database version 9.0.1 and above.
If any users found, it is faulty JVM.
################################################################################
#
Prompt
SELECT CASE count(username)
WHEN 0 THEN 'No Java Based Users'
ELSE 'There are '||count(*)||' JAVA based users'
END "User Existence"
FROM dba_users WHERE username LIKE '%AURORA%' AND username LIKE '%OSE%';
Prompt
DOC
###############################################################
Healthy JVM Should contain Six Roles.
If there are more or less than six role, JVM is inconsistent.
###############################################################
#
Prompt
SELECT CASE count(role)
WHEN 0 THEN 'No JAVA related Roles'
ELSE 'There are '||count(role)||' JAVA related roles'
END "Role"
FROM dba_roles
WHERE role LIKE '%JAVA%';
Prompt
Prompt Roles
Prompt
SELECT role FROM dba_roles WHERE role LIKE '%JAVA%';
set head off
Prompt
Prompt =========================================
Prompt List of Invalid Java Objects owned by SYS
Prompt =========================================
SELECT CASE count(*)
WHEN 0 THEN 'There are no SYS owned invalid JAVA objects'
ELSE 'There are '||count(*)||' SYS owned invalid JAVA objects'
END "Total Invalid JAVA objects"
FROM dba_objects
WHERE object_type LIKE '%JAVA%'
AND status='INVALID'
AND owner='SYS';
Prompt
DOC
#################################################################
Check the status of the main JVM interface packages DBMS_JAVA
and INITJVMAUX and make sure it is VALID.
If there are no Invalid objects below will result in zero rows.
#################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE object_type LIKE '%JAVA%'
AND status='INVALID'
AND owner='SYS';
set feedback off
Prompt
DOC
#################################################################
If the JAVAVM component is not installed in the database (for
example, after creating the database with custom scripts), the
next query will report the following error:
select dbms_java.longname('foo') "JAVAVM TESTING" from dual
*
ERROR at line 1:
ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
If the JAVAVM component is installed, the query should succeed
with 'foo' as result.
#################################################################
#
Prompt
set heading on
select dbms_java.longname('foo') "JAVAVM TESTING" from dual;
set heading off
Prompt
SET FEEDBACK ON HEAD ON serveroutput on
Prompt ===================================
Prompt Oracle Multimedia/InterMedia status
Prompt ===================================
Prompt
DECLARE
v_count NUMBER;
v_version varchar2(200);
v_user_count number;
v_status VARCHAR2(200);
v_xdb_installed NUMBER;
v_xdk_installed NUMBER;
v_javavm_installed NUMBER;
TYPE string_tt IS TABLE OF VARCHAR2 (100)
INDEX BY BINARY_INTEGER;
v_user string_tt;
BEGIN
v_count := 0;
v_version := '';
v_user_count := 0;
v_status := '';
v_xdb_installed := 0;
v_xdk_installed := 0;
v_javavm_installed := 0;
SELECT 1,version,status
INTO v_count, v_version, v_status
FROM dba_registry
WHERE comp_id='ORDIM';
IF v_count > 0 then
DBMS_OUTPUT.PUT_LINE ('.');
DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is installed and listed with the following version: '||v_version||' and status: '||v_status);
DBMS_OUTPUT.PUT_LINE ('.');
/* check if all users are installed.*/
v_user(1) := 'ORDSYS';
v_user(2) := 'ORDPLUGINS';
v_user(3) := 'MDSYS';
v_user(4) := 'SI_INFORMTN_SCHEMA';
v_user(5) := 'ORDDATA';
DBMS_OUTPUT.PUT_LINE('Checking for installed Database Schemas...');
FOR i IN v_user.first .. v_user.last LOOP
SELECT COUNT(username)
INTO v_user_count
FROM dba_users
WHERE username = v_user(I);
/* ORDDATA user only exists starting 11.2 so no test if v_version is different */
IF v_user(i) = 'ORDDATA' AND SUBSTR(V_VERSION,1,6) NOT IN ('11.2.0','12.1.0') THEN v_user_count :=2;
END IF;
/* SI_INFORMTN_SCHEMA user only exists starting 11.2 so no test if v_version is different */
IF v_user(i) = 'SI_INFORMTN_SCHEMA' AND SUBSTR(V_VERSION,1,2) NOT IN ('10','11','12') THEN v_user_count :=2;
END IF;
CASE v_user_count
WHEN 0 THEN DBMS_OUTPUT.PUT_LINE (v_user(I)||' user does not exist.');
WHEN 2 THEN NULL; -- user does not exist in that version
ELSE DBMS_OUTPUT.PUT_LINE (v_user(I)||' user exists.');
END CASE;
END LOOP;
DBMS_OUTPUT.PUT_LINE('.');
/* Prerequisites Check*/
DBMS_OUTPUT.PUT_LINE ('Checking for Prerequisite Components...');
/* for versions >= 10.2 we will verify, if XDB and XDK are installed and valid */
SELECT COUNT(1)
INTO v_javavm_installed
FROM dba_registry
WHERE comp_id='JAVAVM';
IF v_javavm_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('JAVAVM is not installed or not valid');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM installed and listed as valid');
END IF;
IF SUBSTR(V_VERSION,1,2) IN ('11','12') OR
SUBSTR(V_VERSION,1,6) = ('10.2.0') THEN
SELECT COUNT(1)
INTO v_xdk_installed
FROM dba_registry
WHERE comp_id='XML';
IF v_xdk_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDK is not installed or not valid');
ELSE
DBMS_OUTPUT.PUT_LINE('XDK installed and listed as valid');
END IF;
SELECT COUNT(1)
INTO v_xdb_installed
FROM dba_registry
WHERE comp_id='XDB';
IF v_xdb_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDB is not installed or not valid');
ELSE
DBMS_OUTPUT.PUT_LINE ('XDB installed and listed as valid');
END IF;
END IF;
/* for versions >= 11 we run validate_ordim */
DBMS_OUTPUT.PUT_LINE ('Validating Oracle Multimedia/interMedia...(no output if component status is valid)');
IF SUBSTR(V_VERSION,1,2) IN ('11','12') THEN
EXECUTE IMMEDIATE 'begin validate_ordim; end;';
ELSIF SUBSTR(V_VERSION,1,2) IN ('8.','9.','10') AND v_status <> 'VALID' THEN
DBMS_OUTPUT.PUT_LINE('Please run $ORACLE_HOME/ord/im/admin/imchk.sql to display details about invalid interMedia installation');
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is NOT installed at database level');
END;
/
set feedback off head off
select LPAD('*** End of LogFile ***',50) from dual;
set feedback on head on
Prompt
spool off
Prompt
set heading off
set heading off
set feedback off
select 'Upload db_upg_diag_&&dbname&×tamp&&suffix from "&log_path" directory'
from dual;
set heading on
set feedback on
Prompt
-- - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - -
tbs_one_dtls_growth_script
tbs_all_dtls_growth_script
table size
Daily
Rman checking through sql
*****************************
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
---------------------------------
with backup size:
set serveroutput on
set linesize 150
set pagesize 300
col time_taken_display for a9
col output_bytes_display for a12
select session_key,
input_type,
status,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
output_bytes_display,
time_taken_display
from v$rman_backup_job_details
order by session_key desc;
-------------------------------------
select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
from dba_objects where STATUS='INVALID';
--------------------------
RMAN>crosscheck archivelog all;
RMAN>delete noprompt expired archivelog all;
delete archivelog all completed before 'sysdate-1';
RMAN>backup database plus archivelog;
starting backup...........
--------------------------------------------
RMAN>delete archivelog all;
-----------------------------
select name from v$asm_diskgroup;
-------------------------------------
select name,total_mb,free_mb from v$asm_diskgroup;
-----------------
AWR:
select dbid from v$database;
DBID
----------
3191997282
SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC;
SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(3191997282,1,51534, 51538 ));
SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(3191997282,2,51535, 51539 ));
output:copy and save as html file
------------------------------------------
select segment_name,segment_type,bytes/1024/1024/1024 from user_segments where tablespace_name='ABILLITY_TABLES'
order by 3 desc
---------------------
select INSTANCE_NUMBER,INSTANCE_NAME from gv$instance;
------------------
select sum(bytes)/1024/1024/1024 from dba_segments;
-------------
select sum(bytes)/1024/1024/1024 from dba_data_files;
-------------
select opname,time_remaining from v$session_longops where sofar<>totalwork
-------------------------
shirnk datafile
==============================================================================
shirnk datafile
===========
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
=======================================
DATA FILE SHIRNK ...
-------------------
column tablespace_name format a10
column file_name format a32
column file_mb format 9999990
column hwm_mb format 9999990
column used_mb format 9999990
column shrnk_mb format 9999990
break on report
compute sum of file_mb on report
compute sum of hwm_mb on report
compute sum of used_mb on report
compute sum of shrnk_mb on report
select a.*
, file_mb-hwm_mb shrnk_mb
from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_mb,
b.hwm*d.block_size/1024/1024 hwm_mb,
b.used*d.block_size/1024/1024 used_mb
from
dba_data_files a,
(select file_id,max(block_id+blocks-1) hwm,sum(blocks) used
from dba_extents
group by file_id) b,
dba_tablespaces d
where a.file_id = b.file_id
and a.tablespace_name = d.tablespace_name
) a
order by a.tablespace_name,a.file_name;
-------------------------------------------------------------
*****************************
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
---------------------------------
with backup size:
set serveroutput on
set linesize 150
set pagesize 300
col time_taken_display for a9
col output_bytes_display for a12
select session_key,
input_type,
status,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
output_bytes_display,
time_taken_display
from v$rman_backup_job_details
order by session_key desc;
-------------------------------------
select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
from dba_objects where STATUS='INVALID';
--------------------------
RMAN>crosscheck archivelog all;
RMAN>delete noprompt expired archivelog all;
delete archivelog all completed before 'sysdate-1';
RMAN>backup database plus archivelog;
starting backup...........
--------------------------------------------
RMAN>delete archivelog all;
-----------------------------
select name from v$asm_diskgroup;
-------------------------------------
select name,total_mb,free_mb from v$asm_diskgroup;
-----------------
AWR:
select dbid from v$database;
DBID
----------
3191997282
SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC;
SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(3191997282,1,51534, 51538 ));
SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(3191997282,2,51535, 51539 ));
output:copy and save as html file
------------------------------------------
select segment_name,segment_type,bytes/1024/1024/1024 from user_segments where tablespace_name='ABILLITY_TABLES'
order by 3 desc
---------------------
select INSTANCE_NUMBER,INSTANCE_NAME from gv$instance;
------------------
select sum(bytes)/1024/1024/1024 from dba_segments;
-------------
select sum(bytes)/1024/1024/1024 from dba_data_files;
-------------
select opname,time_remaining from v$session_longops where sofar<>totalwork
-------------------------
shirnk datafile
==============================================================================
shirnk datafile
===========
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
=======================================
DATA FILE SHIRNK ...
-------------------
column tablespace_name format a10
column file_name format a32
column file_mb format 9999990
column hwm_mb format 9999990
column used_mb format 9999990
column shrnk_mb format 9999990
break on report
compute sum of file_mb on report
compute sum of hwm_mb on report
compute sum of used_mb on report
compute sum of shrnk_mb on report
select a.*
, file_mb-hwm_mb shrnk_mb
from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_mb,
b.hwm*d.block_size/1024/1024 hwm_mb,
b.used*d.block_size/1024/1024 used_mb
from
dba_data_files a,
(select file_id,max(block_id+blocks-1) hwm,sum(blocks) used
from dba_extents
group by file_id) b,
dba_tablespaces d
where a.file_id = b.file_id
and a.tablespace_name = d.tablespace_name
) a
order by a.tablespace_name,a.file_name;
-------------------------------------------------------------
Subscribe to:
Posts (Atom)