Oracle DBA day to day activity

Posted on Posted in Uncategorized

Tablespace size and uses

set linesize 230

SELECT Total.name "TABLESPACE_NAME",
 nvl(Free_space, 0) FREE_SPACE,
 nvl(total_space-Free_space, 0) USED_SPACE,
 total_space,round((nvl(free_space,0)/nvl(total_space,0)*100),2) "PCTFREE" ,round((nvl(total_space-free_space,0)/nvl(total_space,0)*100),2) "PCTUSED"
 FROM
 (select tablespace_name, sum(bytes/1024/1024) Free_Space
 from sys.dba_free_space
 group by tablespace_name
 ) Free,
 (select b.name, sum(bytes/1024/1024) TOTAL_SPACE
 from sys.v_$datafile a, sys.v_$tablespace B
 where a.ts# = b.ts#
 group by b.name
 ) Total
 WHERE Free.Tablespace_name(+) = Total.name
 order by 6;
RMAN Catalog resync
$rman TARGET /
RMAN> CONNECT CATALOG rman@SHA_PROD

allocate channel for maintenance type disk;
crosscheck backup of database;
delete expired backup of archivelog all;
change archivelog all crosscheck;
RESYNC CATALOG;
release channel;

Objects used information

select p.object_name c1,p.operation c2,p.options c3,p.sql_id c4,count(1) c5 from dba_hist_sql_plan p,dba_hist_sqlstat s 
where p.object_owner = 'HR' and p.operation like '%TABLE%' and p.sql_id = s.sql_id group by p.object_name,p.operation,p.options,p.sql_id order by 1,2,3;

SQL STATEMENT TIME wise

SELECT e.SID,SUBSTR(osuser,0,7) osuser,SUBSTR(username,0,8) username,substr(status,1,1) status,SUBSTR(machine,0,11) machine,
TO_CHAR(ROUND(value/1024),9999) || ' KB' memory,TO_CHAR((sysdate-logon_time)*24*60,999999.99) minutes,q.sql_text FROM v$session e,v$sesstat s,v$statname n,v$sql q WHERE s.statistic# = n.statistic#
AND n.name = 'session uga memory max' AND e.sid = s.sid AND q.hash_value(+) = e.sql_hash_value order by machine,minutes;

Session information loginwise

select s.username,s.module, s.osuser, p.program,s.logon_time,s.terminal, p.spid from v$session s, v$process p where s.paddr = p.addr
order by 5;
select sid,serial#,username,status,to_char(logon_time,'dd-mm-yyyy:hh24:mi:ss') from v$session where status='ACTIVE' order by 4;


Active Session Information:

SET pagesize 999
SET linesize 150
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A30
 
SELECT s.sid,
 s.serial#,
 p.spid,
 s.username,
 s.program,
 s.logon_time
FROM v$session s
 JOIN v$process p ON p.addr = s.paddr
WHERE s.type != 'BACKGROUND'
 and s.username='SHA'
 and s.status='ACTIVE';
Find Long Operation (program running in database)

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;

spool kill.sql

select 'kill -9 ' || spid from v$process where addr in (
select paddr from v$session where (username not like ' ' 
and username not like 'SYS') and program like 'XXX' and username like 'HR');

Setting DISPLAY in SUDO

1. Login as yourself (userid)
2. See your DISPLAY settings and xauth and note what it is:
$ echo $DISPLAY
$ xauth list
3. Change to another user (shadb)
$ sudo su – shadb
4. Set the display to the same as in step 2:
$ DISPLAY=’whatever_host:whatever_number’;export DISPLAY
5. Add this to the authority with xauth command (use the ‘xauth list’ output from step 2)
$ xauth add ‘copy/paste the output of xauth list before’

Find DB Link user password:

select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u where d.username = upper('&&username') and u.user# = d.user_id;

ADRCI Basic Command:
adrci> purge -age 10080 -type ALERT
adrci> purge -age 10080 -type TRACE
adrci> purge -age 10080 -type incident
adrci> purge -age 10080 -type hm
adrci> purge -age 10080 -type utscdmp
adrci> purge -age 10080 -type cdump
** Also you may want to purge all files at once
adrci> purge -age 10080
Find candidate Objects for Purge:

select 'alter database '||a.name||' datafile '''||b.file_name||'''' || ' resize '||greatest(trunc(bytes_full/.7) ,(bytes_total-bytes_free))
from v$database a, dba_data_files b ,(Select tablespace_name,sum(bytes) bytes_full From dba_extents Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total From dba_data_files Group by tablespace_name) d ,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id,max(block_id) max_data_block_id from dba_extents group by tablespace_name,file_id) a ,dba_free_space b
where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id and b.block_id > a.max_data_block_id) e Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name And bytes_full/bytes_total < .7 And b.tablespace_name = e.tablespace_name And b.file_id = e.file_id;

Create DDL for Purge:

select 'alter database RTD1D datafile '''||b.file_name||'''' ||' resize '||greatest(trunc(bytes_full/.7),(bytes_total-bytes_free))||';'||chr(10)||
'--tablespace was '||trunc(bytes_full*100/bytes_total)||'% full now '||trunc(bytes_full*100/greatest(trunc(bytes_full/.7),(bytes_total-bytes_free)))||'%'
from v$database a,dba_data_files b,(Select tablespace_name,sum(bytes) bytes_full From dba_extents Group by tablespace_name) c,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files Group by tablespace_name) d,(Select a.tablespace_name,a.file_id,b.bytes bytes_free From (select tablespace_name,file_id,max(block_id) max_data_block_id
from dba_extents group by tablespace_name,file_id) a,dba_free_space b where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name And b.tablespace_name = d.tablespace_name And bytes_full/bytes_total < .7 And b.tablespace_name = e.tablespace_name And b.file_id = e.file_id;
;

Remove crontab enrty after taking backup of it:

crontab -l > crontab.save
crontab crontab_blank 
cat crontab.save > crontab
crontab -l 
crontab crontab.save

Kill session running longer then 600 sec:

select 'kill -9 ' || p.spid from v$process p,v$session s
where s.paddr=p.addr and s.username='HR' and status='INACTIVE' and LAST_CALL_ET > 600;

PORT CHECK (Given port are open or not)
 
telnet <host_name> <portnumner> --We have to run this from Client
netstat -an | grep <portnumber> -- We have to run this from Server
SQL> conn system/password@//sha.com:1522/sha1d -- Connect sample

ADRCI

adrci> purge -age 10080 -type ALERT
adrci> purge -age 10080 -type TRACE
adrci> purge -age 10080 -type incident
adrci> purge -age 10080 -type hm
adrci> purge -age 10080 -type utscdmp
adrci> purge -age 10080 -type cdump

#!/bin/ksh
#
# run adrci to shorten the ADR retention policies:
#
# SHORTP_POLICY = 168 hours (7 days) instead of the default of 720 hours (30 days) for automatic purging of:
# TRACE
# CDUMP
# UTSCDMP
# IPS
#
# LONGP_POLICY = 8760 hours (1 year) which is the default, for automatic purging of:
# ALERT
# INCIDENT
# SWEEP
# STAGE
# HM
#

#set -x
Base=$(basename $0 '.sh')
RunTime=$(date "+%Y%m%d%H%M")
RunLog=${DB_RUNLOGS}/${Base}.${RunTime}.out

exec > $RunLog 2>&1

adrci << EOF
set home diag/rdbms/sha1/sha1
show control
set control (SHORTP_POLICY = 168)
set control (LONGP_POLICY = 8760)
show control

Database parameter setting:

-- Reduce the retention of optimizer statistics history in the sysaux -- tablespace from a default of 31 days to 6 days. set echo on set termout on spool $DB_RUNLOGS/DbmsStatsAlterStatsHistoryRetention.out connect / as sysdba select dbms_stats.get_stats_history_retention from dual; exec dbms_stats.alter_stats_history_retention(6); select dbms_stats.get_stats_history_retention from dual; exit -- Reduce the retention of unused SQL plan baselines in the SQL management base -- in the sysaux tablespace from the default of 53 weeks to the minimum of -- 6 weeks. These SQL plan baselines consume a lot of space. The purge happens -- weekly as an automated task in the maintenance window. set echo on set termout on spool $DB_RUNLOGS/DbmsSpmConfigure.out connect / as sysdba select * from dba_sql_management_config; exec dbms_spm.configure('PLAN_RETENTION_WEEKS', 6); select * from dba_sql_management_config; exit -- Alter memory parameters. -- The parameter memory_target (or sga_max_size) should be smaller than the minimum of OS parameters -- "process.max-address-space" and "project.max-shm-memory". -- Otherwise you will get "ORA-27102: out of memory". set echo on set termout on spool one.out connect / as sysdba alter system set db_cache_size=512M scope=both; alter system set java_pool_size=0 scope=both; alter system set large_pool_size=64M scope=both; alter system set memory_max_target=6144M scope=spfile; alter system set memory_target=6144M scope=spfile; alter system set pga_aggregate_target=256M scope=both; alter system set shared_pool_size=512M scope=both; alter system set streams_pool_size=32M scope=both; spool $DB_RUNLOGS/AlterParameters.out connect / as sysdba alter system set audit_trail=none scope=spfile; alter system set control_file_record_keep_time=14 scope=both; alter system set cursor_sharing=force scope=both; --alter system reset dispatchers scope=spfile sid='*'; alter system set log_archive_dest='/opt/oracle/admin/sha1/arch' scope=both; alter system set log_archive_format='sha1_arch%s_%t_%r.log' scope=spfile; alter system set nls_date_format='DD-MON-YYYY' scope=spfile; alter system set open_cursors=500 scope=both; alter system set os_authent_prefix='' scope=spfile; alter system set processes=1500 scope=spfile; alter system set query_rewrite_integrity='trusted' scope=both; alter system set recyclebin='off' scope=spfile; alter system set undo_retention=1800 scope=both; exit

 

LOG MINER


DB Level:
---------

alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;


Table level: 
------------
alter table '||owner||'.'||table_name||' add supplemental log data (primary key) columns;
alter table '||owner||'.'||table_name||' add supplemental log data (all) columns;


SQL> SELECT supplemental_log_data_min MIN, 
supplemental_log_data_pk PK, 
supplemental_log_data_ui UI, 
supplemental_log_data_fk FK,
supplemental_log_data_all "ALL" 
FROM v$database;


Specify a LogMiner dictionary.

Use the DBMS_LOGMNR_D.BUILD procedure or specify the dictionary when you start LogMiner (in Step 3), or both, depending on the type of dictionary you plan to use.

Specify a list of redo log files for analysis.

Use the DBMS_LOGMNR.ADD_LOGFILE procedure, or direct LogMiner to create a list of log files for analysis automatically when you start LogMiner (in Step 3).

Start LogMiner.

Use the DBMS_LOGMNR.START_LOGMNR procedure.

Request the redo data of interest.

Query the V$LOGMNR_CONTENTS view. (You must have the SELECT ANY TRANSACTION privilege to query this view.)

End the LogMiner session.

Use the DBMS_LOGMNR.END_LOGMNR procedure.


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
 STARTTIME => '01-Jan-2003 08:30:00', -
 ENDTIME => '01-Jan-2003 08:45:00', -
 OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
 DBMS_LOGMNR.CONTINUOUS_MINE);



EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', - 
 '/oracle/database/', -
 DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);



EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);



ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME => '11-Aug-2016 12:40:00',ENDTIME => '11-Aug-2016 15:40:00',OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +DBMS_LOGMNR.CONTINUOUS_MINE);

DICT_FROM_ONLINE_CATALOG — See Using the Online Catalog
DICT_FROM_REDO_LOGS — See Start LogMiner
CONTINUOUS_MINE — See Redo Log File Options
COMMITTED_DATA_ONLY — See Showing Only Committed Transactions
SKIP_CORRUPTION — See Skipping Redo Corruptions
NO_SQL_DELIMITER — See Formatting Reconstructed SQL Statements for Reexecution
PRINT_PRETTY_SQL — See Formatting the Appearance of Returned Data for Readability
NO_ROWID_IN_STMT — See Formatting Reconstructed SQL Statements for Reexecution
DDL_DICT_TRACKING — See Tracking DDL Statements in the LogMiner Dictionary

======================================================================================
SELECT OPERATION, SQL_REDO, SQL_UNDO
 FROM V$LOGMNR_CONTENTS
 WHERE SEG_OWNER = 'OE' AND SEG_NAME = 'ORDERS' AND
 OPERATION = 'DELETE' AND USERNAME = 'RON';

Manual DB creation:
[oracle@srac1 dbs]$ cat create.sql 
CREATE DATABASE orcl
USER SYS IDENTIFIED BY oracle123
USER SYSTEM IDENTIFIED BY oracle123
LOGFILE GROUP 1 ('/u02/datafile/orcl/redo01.log') SIZE 100M,
GROUP 2 ('/u02/datafile/orcl/redo02.log') SIZE 100M,
GROUP 3 ('/u02/datafile/orcl/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL DATAFILE '/u02/datafile/orcl/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u02/datafile/orcl/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users DATAFILE '/u02/datafile/orcl/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u02/datafile/orcl/temp01.dbf' SIZE 20M REUSE
UNDO TABLESPACE UNDOTBS1 DATAFILE '/u02/datafile/orcl/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

PFILE:

[oracle@srac1 dbs]$ cat create.sql 
CREATE DATABASE orcl
USER SYS IDENTIFIED BY oracle123
USER SYSTEM IDENTIFIED BY oracle123
LOGFILE GROUP 1 ('/u02/datafile/orcl/redo01.log') SIZE 100M,
GROUP 2 ('/u02/datafile/orcl/redo02.log') SIZE 100M,
GROUP 3 ('/u02/datafile/orcl/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL DATAFILE '/u02/datafile/orcl/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u02/datafile/orcl/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users DATAFILE '/u02/datafile/orcl/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u02/datafile/orcl/temp01.dbf' SIZE 20M REUSE
UNDO TABLESPACE UNDOTBS1 DATAFILE '/u02/datafile/orcl/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
[oracle@srac1 dbs]$ cat initorcl.ora
orcl.__db_cache_size=1073741824
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=419430400
orcl.__sga_target=1476395008
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=352321536
orcl.__streams_pool_size=0
*.audit_file_dest='/u02/datafile/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/datafile/orcl/control01.ctl','/u02/datafile/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u02/datafile/orcl'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u02/datafile/orcl/adump'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=419430400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1468006400
*.undo_retention=1500
*.undo_tablespace='UNDOTBS1'


Leave a Reply

Your email address will not be published. Required fields are marked *

÷ 5 = 1