Oracle Recommended Patches -- Oracle Database
(Doc ID 756671.1)
Master Note for Oracle GoldenGate Core Product
Patch Sets (Doc ID 1645495.1)
EXCLUDE(or)INCLUDE=TABLE:\"IN \(\'EMP\',
\'DEP\'\)\"
INCLUDE= role,profile,tablespace
$ ps -ef|grep oracle|grep -v ora_
|grep -v LOCAL=NO|grep -v LSNR|grep -v sshd|grep -v LOCAL=YES|grep -v bash|grep
-v oemagent
$ export TMOUT=0 -----> To avoid closing of putty session
from closing after sometime
$ env | more à lists everything set to the database
$ host –d à gives Domain name
Cat /etc/oratab ---> shows HOME location
$ unalias ls à to get rid of
the colors for the directories
$export ORALCE_HOME=
$ export PATH=$PATH: $ORACLE_HOME/bin
To get IP Address of host:
$ Host machine_name where machine_name=hostname
Ex:
Host
dev41.dxx.xxx.wxxxx.com
dev41.dxx.xxx.wxxxx.com has address 10.0.8.138
To see the Current memory and SWAP usage
$ free
To see the current HUGE pagesize
grep
Huge /proc/meminfo
To see the OS release version
cat
/etc/redhat-release (For
only Linux)
cat
/etc/issue
(General )
How to know Linux machine is 32 or 64 bit?
# uname -m
If i386 or i686 then
it is 32 bit
if x86_64 then it is 64bit
Check Kernel:
rpm -qa
--queryformat '%{installtime} (%{installtime:date}) %{n}-%{v}-%{r}\n' | sort -n
| grep -e kernel-devel -e oracleasm | tail -6
Find my HP-Unix machine is 32 or 64 bit?
Method 1:
#
getconf KERNEL_BITS
Method 2:
#
ps -ef |grep RT
Method 3:
# file /stand/vmunix
If 64 bit
it will show something like: ELF-64 executable object file
How to know Sun Solaris machine is 32 or 64 bit?
# isainfo -v
Find SGA size:
SELECT name,value/1024/1024 "SGA
(MB)" FROM v$sga;
SELECT sum(value)/1024/1024 "TOTAL SGA
(MB)" FROM v$sga;
Select POOL, Round(bytes/1024/1024,0)
Free_Memory_In_MB From V$sgastat Where Name Like '%free memory%';
select * from v$sgainfo;
To
find the no. of CPU’s in a
db:
SQL > Show parameter cpu (or)
$ cat /proc/cpuinfo (see the
processors) (or)
$ cat /proc/cpuinfo | grep
"processor"|wc -l
(LINUX) (or) lscpu
$ ioscan
-C processor | grep processor | wc -l
(HP-UX)
$ psrinfo
-v|grep "Status of processor"|wc -l (SOLARIS)
$ lsdev
-C|grep Process|wc -l (AIX)
Delete OS files older than
‘X’ days :
find /path/to/files* -mtime +5 -exec rm {} \;
Possible error (if there are Huge files): /bin/rm: Argument list too long
Sol: find
/path/to/files* -mtime +5 | xargs rm
Total DATABASE info:
select dbid, name, instance_name, host_name, created, log_mode, open_mode from v$database, v$instance;
Archive log generation on on daily basis:
select trunc(COMPLETION_TIME,'DD') Day, thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from
Gv$archived_log group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
Find what SQL’s is Currently Running:
select session.sid, session.username, optimizer_mode, hash_value,
address, cpu_time, elapsed_time,
sql_text from v$sqlarea sqlarea, v$session session
where session.sql_hash_value =
sqlarea.hash_value
and session.sql_address = sqlarea.address
and session.username is not
null ;
To count all OBJECTS in particular Schema:
SQL>select object_type,count(*) from dba_objects where
owner='SACORP' group by object_type;
OBJECT_TYPE COUNT(*)
------------------
----------
PACKAGE BODY 4
PROCEDURE 61
VIEW
3
TABLES
24
To LIST all OBJECTS in All schemas in
database:
select
owner,object_type,count(*) from dba_objects where owner in (select distinct
owner from dba_segments) group by
object_type,owner order by 1;
To
count all OBJECTS in particular Tablespace:
select
OWNER,SEGMENT_TYPE,count(*) from DBA_SEGMENTS where TABLESPACE_NAME
='Tablespace_name' group by
OWNER,SEGMENT_TYPE order by 1;
Objects
created from past ‘x’ days:
SQL> select object_name, object_type from dba_objects where
sysdate-created <x;
SQL > select OWNER,OBJECT_TYPE,count(*) from dba_objects where sysdate-created <x group by
OWNER,OBJECT_TYPE;
How
to check the last DML happened in a table
SELECT MAX(ORA_ROWSCN) FROM
yourtable_name;
select scn_to_timestamp(input from above query) from dual;
To check invalid objects:
SQL> select object_type,count(*) from dba_objects where
status='INVALID' group by object_type;
To check USED & FREE space of ARCHIVELOG destination/FRA:
Archive log list
select
space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;
SELECT
FILE_TYPE "Type",PERCENT_SPACE_USED "%
Used",PERCENT_SPACE_RECLAIMABLE "% Reclaim",
NUMBER_OF_FILES
"# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;
REDO LOG:
Redo Logs info:
set linesize
300
column
REDOLOG_FILE_NAME format a50
SELECT a.GROUP#, a.THREAD#,
a.SEQUENCE#, a.ARCHIVED, a.STATUS,
b.MEMBER
AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a JOIN v$logfile
b ON a.Group#=b.Group# ORDER BY a.GROUP# ASC;
Nohup
$ Nohup run_sql.sh indexes & (output creates as indexes.out)
$ nohup
sqlplus / as sysdba @move_lob.sql &
To see MAXDATAFILE parameter in control file
SQL> select records_total
from
v$controlfile_record_section
where type = 'DATAFILE';
SQL> show parameter db_files;
SQL > Alter system set
db_files=700 scope=both;
SQL > Create pfile from spfile; (wrong, throws an error)
SQL> create
pfile=’/var/backup/corppsdb/abc.ora’ from spfile ; (right)
Copying CONTROL FILE
We copy control file using CP command to some location it will create
but we can’t open or edit
So we can copy control file using,
SQL> Alter database backup control file to trace (or)
SQL> Alter database backup control file to ‘../../..’
SQL> sho parameter
NLS_LENGTH_SEMANTICS;
SQL
> ALTER SYSTEM
SET NLS_LENGTH_SEMANTICS=CHAR;
or
SQL
> ALTER SYSTEM
SET NLS_LENGTH_SEMANTICS=BYTE;
Create MAX EXTENTS size to unlimited
Spool newrun.sql
SQL> select 'alter'||'
'||object_type||' '||object_name||' '||'move storage (maxextents unlimited);'
from dba_objects where object_type in ('TABLE','INDEX') and owner = 'SYSFC';
Set head off
set pagesize 50000
:
SQL> @newrun.sql
Total no. of USERS connected to database :
SQL>
select username, osuser from v$session where username is not null;
Or
select
count(*) from v$session where username is not null and username != 'SYS';
Total
connections to DB:
Set
linesize 280;
select
count(*),INST_ID,username,osuser,status,machine from gv$session group by
INST_ID,username,osuser,status,machine order by count(*),INST_ID;
To see the udump,bdump,cdump locations in
10g :
SQL>
show parameter user_dump_dest; (similarly all)
To see the udump,bdump,cdump locations in
11g:
$ env |grep diag
To see the last lines in logfile or Trace
file :
SQL>
tail -20 alertlog(or)tracefile (to display last 20
lines)
To see Tablespace and its datafiles:
SQL>
select FILE_NAME, TABLESPACE_NAME, STATUS from dba_data_files where
TABLESPACE_NAME='CORP_DAT2' ;
To see all data files, Control files and
redo logs including TEMP file in one query:
SQL>
select name from v$datafile
union select name from v$tempfile;
SQL> select name from
v$controlfile;
SQL> select member from v$logfile;
While writing spool
SQL> set echo off
SQL> set verify off
SQL> set heading off
SQL> set pagesize 5000
SQL> set feedback off
SQL> select 'alter index'||'
'||INDEX_NAME||' '||'storage (maxextents 2147483645);' from user_indexes where
MAX_EXTENTS is NULL;
================================================================
INDEXES:
List
all indexes on a table:
Select owner,
table_name, index_name, column_name FROM user_ind_columns Order by owner,
table_name, column_name Where table_name=’TABLE_NAME’;
To see Tables, indexes and their
Tablespace :
select INDEX_NAME, TABLE_NAME,
TABLESPACE_NAME from user_indexes;
Moving
index to another Tablespace:
alter index <index_name>
rebuild tablespace
<tablespace_name>;
How
to find if index is LOCAL or GLOBAL in oracle:
select index_name, locality from
all_part_indexes where table_name='tab_name’;
Find
no. of objects of a user in each tablespace
select
OWNER,SEGMENT_TYPE,TABLESPACE_NAME,count(*) from dba_segments where
OWNER='DDS_DISTRIBUTE_MODE' group by OWNER,SEGMENT_TYPE,TABLESPACE_NAME;
To make index extent size to unlimited:
Alter index Ind_name rebuild online storage
(MAXEXTENTS Unlimited);
Script
for the same :
Spool runme.sql
Select 'alter index'||'
'||INDEX_NAME||' '||'rebuild online storage(MAXEXTENTS Unlimited);' from user_indexes;
or
select 'alter index'||'
'||INDEX_NAME||' '||'rebuild online storage(MAXEXTENTS Unlimited);' from user_indexes where
max_extents is null;
TABLE :
Count Number of COLUMNS in a Table:
select count(column_name) from user_tab_columns where
table_name=’TABLE_NAME’;
To change table data type
alter table
table_name modify( name
varchar2(32)); (changed from 30 to 32)
Finding SIZE of a TABLE:
select
sum(BYTES)/1024/1024/1024 GB from user_segments
where SEGMENT_NAME=’tab_name’;
Find LOCK on a TABLE:
select
a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT
OWNER", b.object_name,b.object_type,a.locked_mode from
(select
object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select
object_id, owner, object_name,object_type from dba_objects) b where a.object_id=b.object_id
Moving Table to another Tablespace:
SQL> alter table tab_name
move tablespace <new_tablespace_name>;
=======================================================================
LINUX
Sqlplus commands from LINUX: echo “sql_statement” | sqlplus –s username/passwd
Ex: echo "select count(*) from tab;" | sqlplus -s / as sysdba
COUNT(*)
----------
4740
tar -czvf
name-of-archive.tar.gz /path/to/directory-or-file
z: Compress the archive with gzip.
-c: Create -f : Filename
Count specific word in Linux file:
grep -o 'CREATE SEQUENCE' abc.txt | wc –l
Find command : find –name abc.sql
To see LISTENER status : $ ps -fu oracle |grep tns
To see all HOME locations : $ env | grep HOME (use CAPS)
$ env |
grep ORACLE_HOME
$ echo $ORACLE_HOME
Similarly for ASM : $ env |grep ASM
To see all SID’s : $ env |grep SID
$ env |grep ORACLE_SID (for
only Oracle_sid)
To see everything :
$ env | grep ORACLE (use CAPS)
Ex: > env |grep ORACLE
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0.2
ORACLE_HOSTNAME=vcd04124.ute.fedex.com
ORACLE_SID=VCD04124
ORACLE_UNQNAME=VCD0412
To know which shell are we using in
database : env | grep SHELL
NOTE : Use coraenv when using the C Shell and oraenv when using a Bourne, Korn or Bash shell.
Running .oraenv
à .
./.oraenv
Running
.profile
à . .profile
Find SIZE of files/directories
$ du
-sh * | sort -n (Display all
sizes of files in a current path only)
$ du -sh (Displays total size of the directory
where you are in)
$ df -h (shows all the used and free sizes on mount points)
df -h /var/backup (to see for a particular mount point)
$ stat (file(or)directory name) (gives the complete
status(timestamp) of that file/directory)
To know how many hours I was
connected to the server till today
$ ac -d (‘ac’ is an LINUX command )
To know connect time for all the users
$ ac -p
To know the connect time for a specific user (ex: basupally)
$ ac -d
basupally
$ w
Gives complete info of the session connected like
16:11:34 up 54 days, 20:09, 2
users, load average: 0.29, 0.24, 0.19
USER TTY FROM LOGIN@ IDLE
JCPU PCPU WHAT
cb858037 pts/0
199.81.99.89 15:22 34:58
0.01s 0.01s sshd: cb858037 [priv]
cb858037 pts/1
199.81.99.89 15:23 0.00s
0.01s 0.01s sshd: cb858037 [priv]
To know about any User
$ Finger userid (ex: finger
cb858037)
$ chmod 777 *.log (for files)
$ chmod -R 777
/var/back/omega
(giving rwx permissions to a
directory OMEGA)
To Run DBCA:
$ Export DISPLAY=ipaddress:0 (ip
address from cmd promptà ipconfig)
Vi
editor Commands
1)
Replacing a word in entire file :%s/old/new/g
2)
Replacing a word in single line only :s/old/new/g
3)
Replacing a word in line ‘n’ :ns/old/new
4)
To go to the END of a line ‘:$’ or ‘G’
5)
To go to the FIRST line ‘gg’
6)
To find CURRENT LINE number :.= or (ctl g
-> gives line #, file name,Total lines in a file)
7)
Count the word ABC in entire vi file :%s/ABC/ABC/g
8)
Count a string from outside file : grep “abc efg“ file_name | wc –l
grep -c "abc efg” file_name
more /etc/profile | grep -i
ulimit
9)
To give numbering to each line :set nu
10)
To turn off
the numbering :set nonu
11)
To open a
file with cursor at last line vi + filename
12)
To open a file with cursor at line number
25 vi +25
filename
13)
To open a file with cursor at first occurrence
of WORD ‘abc’ vi /abc filename
Edit multiple
files at a time :
$ vi
ipom.xml pom.xml ppom.xml tpom.xml
Opens 1st file(ipom)-->edit and save (:w) --> :n (to
go to another next file) --> :n (go to next file) and so on
--> :N (to go to Previous
file)
User Management
Find User privileges:
SQL> select * from dba_sys_privs where grantee='SCHEMA_NAME';
select
TABLE_NAME,PRIVILEGE,GRANTOR from dba_TAB_PRIVS
where grantee='SCHEMA_NAME';
Find user roles:
SQL> select * from dba_role_privs where grantee='SCHEMA_NAME';
To see the current Active Roles of any schema, then log in into that schema and query
this:
SQL> select * from session_roles;
Find privileges granted to a ROLE:
SELECT * FROM role_sys_privs where
role=’ROLE_NAME’;
SELECT * FROM role_tab_privs where
role=’ROLE_NAME’;
Roles Granted to a ROLE :
SELECT * FROM role_role_privs where
role=’ROLE_NAME’;
Get Oracle User DDL with dbms_metadata
set
long 200000 pages 0 lines 131
column
meta format a121 word_wrapped
select
dbms_metadata.get_ddl('USER', '&&username') meta from dual;
select
dbms_metadata.get_granted_ddl('OBJECT_GRANT', '&&username') meta from
dual;
select
dbms_metadata.get_granted_ddl('ROLE_GRANT', '&&username') meta from
dual;
select
dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&&username') meta from
dual;
About
DATABASE
How to Calculate the Size of the Database (Doc ID
1360446.1)
(Used space):
----------------
select sum(bytes)/1024/1024/1024
GB from dba_segments;
(Free space):
---------------
select sum(bytes)/1024/1024/1024
GB from dba_free_space;
(Total database size):
---------------------------
select sum(bytes)/1024/1024/1024 GB from
dba_data_files;
+
(Temp size):
---------------
SQL> select
SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree
from v$temp_space_header;
(Or)
SELECT SUM (a.log_space
+ b.data_space + c.tempspace) "Total_DB_Size (G)" FROM (SELECT ROUND (SUM
(BYTES/1024/1024/1024), 2) data_space FROM dba_data_files)
b, (SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2)
log_space FROM v$log) a,
(SELECT NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0)
tempspace FROM dba_temp_files) c;
DROP all
USERS like name:
BEGIN
FOR i IN (
SELECT t.username
FROM DBA_USERS t
WHERE t.username LIKE 'QA_%')
LOOP
EXECUTE IMMEDIATE 'DROP USER '|| i.username||' CASCADE';
END LOOP;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
/
Oracle server uptime:
SQL> SELECT TO_CHAR
(startup_time, 'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" from
SYS.v_$instance;
Or $ uptime
Find all Child table FK’s for a parent PK:
select
a.owner,a.table_name, a.constraint_name from sys.all_constraints a,(select
owner,constraint_name from sys.all_constraints where owner = ‘OWNER’ and table_name = ‘TABLE_NAME’ and constraint_type in
('P','U')) b where a.constraint_type = 'R' and a.r_constraint_name =
b.constraint_name and a.r_owner = b.owner;
Find
SCHEMA size:
SELECT
sum(bytes)/1024/1024 MB FROM dba_segments where owner=’owner_name’;
Tablespace
used and free space:
SELECT
SUBSTR (df.NAME, 1, 50) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes
/ 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM
v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) and
dfs.tablespace_name=’tablespace_name’
GROUP
BY dfs.file_id, df.NAME, df.file#, df.bytes ORDER BY file_name;
Find
used and free space in TABLESPACES:
select * from DBA_TABLESPACE_USAGE_METRICS;
All
schema sizes in Database :
select
owner, sum(bytes)/1024/1024 MB from dba_segments group by owner order by MB desc;
Find
schema and its tablespace:
SQL> select USERNAME, DEFAULT_TABLESPACE
from dba_users where
USERNAME='SYSFMSO';
Find
schema and all related tablespaces:
select owner,tablespace_name from dba_tables
where owner='CVM_SCHEMA' group by owner,tablespace_name;
Finding
BIGFILE tablespace in database:
select name, bigfile from v$tablespace;
Find
total number of procedures in a database:
Select count(*) from dba_procedures;
To count the number of data files in a database
SQL> select
count(file_name) from dba_data_files;
To find out the path to your current
session’s trace file
SQL>
select value from v$diag_info where name = 'Default Trace
File';
Find
redo size generated per day
SQL> select trunc(completion_time)
rundate,count(*) logswitch ,round((sum(blocks*block_size)/1024/1024))
"REDO PER DAY (MB)" from v$archived_log group by
trunc(completion_time) order by 1;
To findouts
Trace file of different session:
select instance_name || ‘_ora_’ || spid ||
‘.trc’ filename from v$process p, v$session s, v$instance
where p.addr = s.paddr and s.sid = 170;
Find
my own SID:
select
sid from v$mystat where rownum <=1;
(OR) select
distinct sid from v$mystat;
To
check all the spfile parameters
SQL> SHOW
PARAMETER;
Startup database with
pfile or spfile
SQL>
CONNECT sys/password AS SYSDBA
SQL> startup pfile='<pfile location>';
Analyze
all tables and indexes that are owned by the ‘ABC’ user:
execute
DBMS_UTILITY.ANALYZE_SCHEMA('ABC','ESTIMATE') (before 8i DBMS_UTILITY is
used)
To
analyze single table
ANALYZE TABLE
SCOTT.RA_INTERFACE_DISTRIBUTIONS_ALL
COMPUTE STATISTICS;
CREATE OR REPLACE procedure SYS.flush_buffer_and_shared_pool
as
begin
execute immediate 'alter system flush
shared_pool';
execute immediate 'alter system flush
buffer_cache';
dbms_output.put_line('!!!!...Shared pool and buffer cache
has been flushed successfully..!!!!');
end flush_buffer_and_shared_pool;
/
Created this procedure to do both @same time
Running the above procedure as follows:
SQL > Exec SYS.flush_buffer_and_shared_pool (from
sys user)
Gather
stats on the entire database...
execute dbms_stats.gather_database_stats;
exec dbms_stats.delete_database_stats;
Gather stats for a single schema...
execute
dbms_stats.gather_schema_stats('SCOTT');
exec dbms_stats.delete_schema_stats('SCOTT');
(or)
exec dbms_stats.gather_schema_stats(ownname=>'USER_NAME',OPTIONS=>'GATHER
AUTO');
Gather stats for a schema table...
exec
dbms_stats.gather_table_stats('<owner>', '<table_name>');
exec dbms_stats.delete_table_stats('SCOTT', 'EMPLOYEES');
exec dbms_stats.delete_index_stats('SCOTT', 'EMPLOYEES_PK');
exec dbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
options => 'GATHER AUTO' )
Find
TABLE modifications done after gathering stats
Select TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP
from DBA_TAB_MODIFICATIONS where TABLE_OWNER='SCHEMA_NAME’;
To
restrict the out of a query use rownum parameter
Ex: select * from user_tables where rownum <5 ; (Displays
only 4 rows)
select * from user_tables where
rownum < 9; (Displays only 8 rows)
Convert
SCN to Timestamp:
SQL> select scn_to_timestamp(SCN_number) from dual;
Conn / as sysdba
(if u want to run anything as DEMO user and if
you don’t know the passwd then do this)
SQL> alter session set current_schema=DEMO;
SQL> select
sys_context('USERENV','SESSION_USER')
current_user,sys_context('USERENV','SESSION_SCHEMA') current_schema from dual;
CURRENT_USER
--------------------------------------------------------------------------------
CURRENT_SCHEMA
--------------------------------------------------------------------------------
SYS
DEMO
List
autoextensible datafiles
SQL> Select
file_name from dba_data_files
where autoextensible = 'YES'
Datapump job status:
SELECT owner_name, job_name, operation, job_mode,state,
attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER
BY 1,2;
Temporary Tablespace :
Check
all TEMP tablespace space in database:
SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 /
1024 mb_used, D.mb_total - SUM
(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A, (SELECT B.name,
C.block_size, SUM (C.bytes) /
1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY
B.name, C.block_size) D
WHERE A.tablespace_name = D.name GROUP by
A.tablespace_name, D.mb_total
/
Checking
Default Temporary tablespace:
select property_value from database_properties
where property_name =
'DEFAULT_TEMP_TABLESPACE';
To see Temporary segment usage: v$tempseg_usage
To
check if anyone doing sort operations:
SQL>
SELECT COUNT(*) FROM v$sort_usage WHERE tablespace = 'TEMP'
LIST
all Temp files;
select
FILE_NAME, BYTES/1024/1024/1024 GB from dba_temp_files;
DIFFERENCES:
DBA_ views: These views are
built on Data dictionary they're not
available if the database is not mounted and opened.
V$ views : These views
tend to run against the instance, and therefore may be available if the
database is not mounted, or is not mounted and opened, depending on the nature
of the view.
***DATAPUMP PARALLEL option is only available in Enterprise Edition of Oracle Database ***
-- locate Data
Pump master tables:
SELECT
o.status, o.object_id, o.object_type, o.owner||'.'||object_name
"OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND
o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ORDER BY
4,2;
EXCLUDE=SCHEMA:"IN\(\'SYS\',\'SYSMAN\',\'SYSTEM\',\'XDB\',\'PUBLIC\',\'OUTLN\',\'ORDSYS\',\'OLAPSYS\',\'DBSNMP\',\'APEX_030200\',\'CTXSYS\',\'EXFSYS\',\'FLOWS_FILES\',\'MDSYS\',\'ORDDATA\',\'OUTLN\'\)"
RMAN:
Full bkup:
run {
2> backup as backupset tag 'QA1_FULL'
database FORMAT '/usr/mware/full_%u_%s_%p' include current controlfile PLUS
ARCHIVELOG;
3> }
Rman TARGET
SYS/pwd NOCATALOG debug trace=rman.trc log=’/pullpath/rman.log’
$ORACLE_HOME/bin/rman
target sys/VCOMUPP@VCOMUPPB auxiliary sys/VCOMUPP@VCOMUPSS << _END_
>> standby_6.log
RUN
{
CONFIGURE
DEVICE TYPE DISK PARALLELISM 2;
DUPLICATE
TARGET DATABASE
FOR
STANDBY
FROM
ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
}
exit
_END_
The following query shows the
status RMAN jobs.
select OPERATION, START_TIME, END_TIME, OBJECT_TYPE, STATUS
from v$RMAN_STATUS order by START_TIME;
RMAN> Delete archivelog all completed before 'SYSDATE-7';
COLD Backup:
RMAN
> run {
backup
full tag full_cold_backup
format
‘/var/backup/corppsdb/rman/db_t%t_s%s_p%p’
database;
}
Rman running job status :
SELECT
SID, SERIAL#, CONTEXT, SOFAR,TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT
LIKE '%aggregate%' AND TOTALWORK != 0
AND SOFAR <> TOTALWORK ;
Tape backup status:
select name, start_time, end_time, INPUT_TYPE,status,
time_taken_display ELAPSED, compression_ratio COMPR,
input_bytes_display INB,
output_bytes_display OUTB, input_bytes_per_sec_display INBPERSEC,
output_bytes_per_sec_display OUTBPERSEC
from V$RMAN_BACKUP_JOB_DETAILS,
V$database where output_device_type = 'SBT_TAPE'
order by session_stamp desc;
To see
any Dictionary view use DICT view:
Example
SQL>
select table_name from dict where table_name like '%BACKUP%';
DATAGUARD (STANDBY) :
select DB_UNIQUE_NAME, DATABASE_ROLE,STATUS,OPEN_MODE from
v$database,v$instance;
Check standby
redo logs:
select * from v$logfile where type='STANDBY';
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS
FROM V$STANDBY_LOG;
Use the
following query to confirm that Data Guard is in active mode:
SELECT 'Using Active Data Guard' ADG FROM
V$MANAGED_STANDBY M, V$DATABASE D WHERE M.PROCESS LIKE 'MRP%' AND
D.OPEN_MODE='READ ONLY';
ADG
-----------------------
Using Active Data Guard
no rows selected ----> then Active Data Guard is not enabled
To determine if
Redo Apply has recovered all redo that has been received from the
primary, query
the V$DATAGUARD_STATS view.
SQL> SELECT * FROM
V$DATAGUARD_STATS WHERE NAME=’apply lag’;
To see if the
MRP is running or not
SQL> SELECT PROCESS, STATUS FROM
V$MANAGED_STANDBY;
To start Redo
Apply, issue the following statement:
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
To stop recovery
of a standby database:
SQL> ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
On primary:
SELECT THREAD#
"Thread",SEQUENCE# "Last Sequence Generated" FROM
V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$ARCHIVED_LOG GROUP BY
THREAD#) ORDER BY 1;
On Standby:
set line 280
SELECT name "Database
name", STATUS "MRP process",ARCH.THREAD# "Thread",
ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last
Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#)
"Difference" FROM (select name from v$database) name,(SELECT STATUS FROM V$MANAGED_STANDBY where process like
'%MRP%') process,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE
(THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG
GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE
(THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY
GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Check Archive
gap on Standby:
SELECT THREAD#, LOW_SEQUENCE#,
HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
Check the max
log sequence on Primary DB and on Standby DB
SELECT
Max(sequence#) FROM v$log_history;
# check the last
log applied on STANDBY
SELECT thread#, Max(sequence#) "Last Standby Seq Applied" FROM v$archived_log
WHERE applied = 'YES' GROUP BY thread# ORDER BY 1;
On Primary check
ERROR MESSAGE to standby:
SELECT gvi.thread#, timestamp, message FROM
gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND
severity in ('Error','Fatal') and rownum <11 ORDER BY timestamp, thread#;
Recovery speed:
set
linesize 400
col Values
for a65
col
Recover_start for a21
select
to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss')
"Recover_start",to_char(item)||' = '||to_char(sofar)||'
'||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi')
"Values" from v$recovery_progress where start_time=(select
max(start_time) from v$recovery_progress);
ASM:
Export
ORACLE_SID=+ASM
To see DISK
GROUP free and Used space:
select GROUP_NUMBER, NAME,TOTAL_MB,
FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;
To see DISK free and Used space in a DISK GROUP:
select disk_number "Disk #", free_mb
from v$asm_disk where group_number =
x order by 2;
To view ASM
operation(after add/drop/resize):
SQL> select group_number,
operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;
ASM disk
path and header_status:
select path, header_status, mode_status from
v$asm_disk;
Database TUNING:
Find table
having STALE stats or not:
select
OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where TABLE_NAME=’TABLE_NAME’;
Begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname
=> 'SCOTT',
tabname
=> 'EMP',
degree
=> 2,
cascade
=> TRUE,
METHOD_OPT
=> 'FOR COLUMNS SIZE AUTO',
estimate_percent
=> DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
There
are several values for the options parameter that we need to know about:
gather – re-analyzes
the whole schema.
gather
empty – Only
analyze tables that have no existing statistics.
gather
stale – Only
re-analyze tables with more than 10% modifications (inserts, updates, deletes).
gather
auto – This
will re-analyze objects which currently have no statistics and objects with
stale statistics. Using gather auto is
like combining gather stale and gather empty.
Monitor all current
sql executions in database:
SET
HEAD ON PAGES 50000 ECHO OFF VERIFY OFF FEED ON LINESIZE 400 TRIMSPOOL ON AUTOTRACE OFF
COLUMN
percent FOR 999.99
COLUMN
message FOR A90
COLUMN
"Start Time" FOR A23
COLUMN
"Actual Time" FOR A23
COLUMN
username FOR A10
COLUMN
sql_id FOR A15
SELECT
a.sid, a.username,s.status,
a.sql_id,s.OSUSER,s.program,s.state,s.SECONDS_IN_WAIT ,
TO_CHAR(start_time,'DD-Mon-YYYY
HH24:MI:SS') "Start Time",
TO_CHAR(SYSDATE ,'DD-Mon-YYYY HH24:MI:SS') "Actual Time",
message,
(sofar/DECODE(NVL(totalwork,1),0,1,totalwork))* 100 percent
FROM v$session_longops a, v$session s WHERE
a.sid=s.sid and
DECODE(sofar/DECODE(NVL(totalwork,1),0,1,totalwork),0,1,sofar/DECODE(NVL(totalwork,1),0,1,totalwork))
* 100 <> 100;
List all STALE
objects in databases:
SET
SERVEROUTPUT ON
DECLARE
ObjList
dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList,
options=>'LIST STALE');
FOR i
in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname
|| '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END
LOOP;
END;
/
Query to check
blocking sessions: (use GV$ for RAC db)
SELECT s1.username || '@' ||
s1.machine
|| ' ( SID=' || s1.sid || ' ) is
blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2 ;
Script to Check
Maximum Processes (check if database is hitting ORA-00020: maximum number of
processes)
select resource_name,
current_utilization/limit_value*100,current_utilization,
max_utilization,limit_value from v$resource_limit
where resource_name in ('processes','sessions');
Find out the
locks in the table
select
a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT
OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID,
ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner,
object_name,object_type from dba_objects) b
where a.object_id=b.object_id;
Find LOCK on
object:
select
owner||'.'||object_name obj,oracle_username||' ('||s.status||')' oruser,os_user_name
osuser,
machine
computer,l.process unix,
''''||s.sid||','||s.serial#||''''
ss,r.name rs,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from
v$locked_object l, dba_objects o, v$session s, v$transaction t, v$rollname r
where
l.object_id = o.object_id and s.sid=l.session_id and s.taddr=t.addr
and
t.xidusn=r.usn and o.object_name=’OBJECT_NAME’
order
by osuser, ss, obj;
SELECT
O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE,S.PORT
, S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT
L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND
L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND
S.SQL_ADDRESS = SQ.ADDRESS;
FIND how much
undo rollbacked:
select
start_time,used_urec,used_ublk from v$transaction;
SELECT
s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec FROM v$session s, v$transaction t
WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;
Find last DDL
& DML time on a object:
select
(select last_ddl_time from dba_objects where object_name='T' and
owner='SYS') "DDL Time",
decode(maxscn,0,'N/A',scn_to_timestamp(maxscn)) "DML Time"
from (select nvl(max(ora_rowscn),0) maxscn from cvm_schema.CI_CUST_HIER_SUMMARY);
Scheduler job
status:
select owner, job_name, job_class, enabled, next_run_date,
repeat_interval from dba_scheduler_jobs where job_name=
Check PLSQL DDL:
select
text from dba_source where name ='Procedure_name’;
SQL text for
particular SID:
select
a.sid,a.program,b.sql_text from v$session a, v$sqltext b
where a.sql_hash_value
= b.hash_value and a.sid=144 order
by a.sid,hash_value,piece;
Total cursors
open, by session
select
a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session
s
where
a.statistic# = b.statistic# and
s.sid=a.sid and b.name = 'opened cursors
current' order by 1;
Kill
particular OSUSER sessions
select
'Alter system kill session '''||sid||','||serial#||''';' from v$session where
osuser='username' ;
or
begin
for sessions in ( select sid,serial#
from v$session where
username = 'QA_SR')
loop
execute immediate 'alter system kill
session '''||sessions.sid||','||sessions.serial#||'''';
end loop;
end;
/
Getting BIND
variable values:
select
* from V$SQL_BIND_CAPTURE where
sql_id='8f6haaamzf';
Finding bind
variable values :
select
sn.BEGIN_INTERVAL_TIME,sn.END_INTERVAL_TIME,sb.NAME,sb.VALUE_STRING from
DBA_HIST_SQLBIND sb,DBA_HIST_SNAPSHOT sn where sb.sql_id='8f6hssspf' and
sb.WAS_CAPTURED='YES' and sn.snap_id=sb.snap_id
order by sb.snap_id,sb.NAME;
Query to show
sql_ids related to SQL Profiles:
select
distinct p.name
sql_profile_name,s.sql_id from dba_sql_profiles p, DBA_HIST_SQLSTAT s where
p.name=s.sql_profile;
AWR:
-- This
causes the repository to refresh every 15 minutes & and retain all data for
2 weeks.
Exec
dbms_workload_repository.modify_snapshot_settings (retention=>20160, interval=> 15);
Create Manual
snapshot: EXEC
dbms_workload_repository.create_snapshot;
======================================================================================
RAC:
$GRID_HOME/log/<hostname>/alert<<hostname>>.log
$GRID_HOME/log/<hostname>/cssd/ocssd.log
ASM alert log
location:
. oraenv
+ASM
Adrci
Show alert
$ORACLE_BASE/diag/asm/+asm/+ASM{instance number}/trace/ alert_+ASM
{instance number}.log
OS logs: /var/log/messages
rpm –qa | grep oracleasm
./crsctl stop crs (OR) ./crsctl
stop crs –f
crsctl check crs
crsctl check cluster –all (lists all on all the nodes)
crsctl query css votedisk
crs_stat –t (or) crsctl status resource –t
Check autostart of CRS and HAS:
./crsctl config has(or)crs
Enable autostart of CRS and HAS:
./crsctl enable has(or)crs
Oracle RESTART:
crsctl start has – to manually
start the Oracle Restart stack when running disabled or after manually stopping
it
crsctl stop has [-f] – to manually
stop the Oracle Restart stack. The -f option
crsctl enable has – to enable the
stack for automatic startup at server reboot
crsctl disable has – to disable the
stack for automatic startup at server reboot
crsctl config has – to display the
configuration of Oracle Restart
crsctl check has –
