Oracle Database Administration: December 2014

--SELECT SYSDATE FROM DUAL;
--SHOW USER
PROMPT
PROMPT -----------------------------------------------------------------------|
PROMPT

SET TERMOUT ON
SET VERIFY OFF
SET FEEDBACK ON
PROMPT
PROMPT Checking database name and archive mode, dbid
PROMPT
column NAME format A9
column LOG_MODE format A12
SELECT NAME,CREATED, LOG_MODE, DBID FROM V$DATABASE;

PROMPT
PROMPT -----------------------------------------------------------------------|
PROMPT
PROMPT
PROMPT Checking Time since last RMAN backup
PROMPT

select (sysdate-min(t))*24 from
(
select max(b.CHECKPOINT_TIME) t
from v$backup_datafile b, v$tablespace ts, v$datafile f
where INCLUDED_IN_DATABASE_BACKUP='YES'
and f.file#=b.file#
and f.ts#=ts.ts#
group by f.file#
);

PROMPT
PROMPT -----------------------------------------------------------------------|
PROMPT
PROMPT
PROMPT Checking Tablespace name and status
PROMPT
column TABLESPACE_NAME format a30
column STATUS format a10
set pagesize 400
SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT
PROMPT
PROMPT Checking free space in tablespaces
PROMPT
column tablespace_name format a30
SELECT tablespace_name ,sum(bytes)/1024/1024 "MB Free" FROM dba_free_space WHERE
tablespace_name <>'TEMP' GROUP BY tablespace_name;
PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT
PROMPT
PROMPT Checking freespace by tablespace
PROMPT

column dummy noprint
column pct_used format 999.9       heading "%|Used"
column name    format a16      heading "Tablespace Name"
column bytes   format 9,999,999,999,999    heading "Total Bytes"
column used    format 99,999,999,999   heading "Used"
column free    format 999,999,999,999 heading "Free"
break   on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report
set linesize 132
set termout off
select a.tablespace_name                                              name,
       b.tablespace_name                                              dummy,
       sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )      bytes,
       sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
       sum(a.bytes)/count( distinct b.file_id )              used,
       sum(a.bytes)/count( distinct b.file_id )                       free,
       100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
               (sum(a.bytes)/count( distinct b.file_id ) )) /
       (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT

PROMPT
PROMPT Checking Size and usage in GB of Flash Recovery Area
PROMPT
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 ;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT

PROMPT
PROMPT Checking free space In Flash Recovery Area
PROMPT

column FILE_TYPE format a20

select * from v$flash_recovery_area_usage;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT
PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT

PROMPT
PROMPT Checking last sequence in v$archived_log
PROMPT
clear screen
set linesize 100

column STANDBY format a20
column applied format a10

--select max(sequence#), applied from v$archived_log where applied = 'YES' group by applied;
SELECT name as STANDBY, SEQUENCE#, applied, completion_time from v$archived_log WHERE DEST_ID = 2 AND NEXT_TIME > SYSDATE -1;
prompt
prompt----------------Last log on Primary--------------------------------------|
prompt
select max(sequence#) from v$archived_log where NEXT_TIME > sysdate -1;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT
PROMPT
PROMPT Checking switchover status
PROMPT
select switchover_status from v$database;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT

PROMPT
PROMPT Checking for 20 Largest items in Database
PROMPT
column SEGMENT_NAME format A50

SELECT * FROM (
SELECT
    OWNER, SEGMENT_NAME, BYTES/1024/1024 SIZE_MB
FROM
    DBA_SEGMENTS
ORDER BY
    BYTES/1024/1024 DESC ) WHERE ROWNUM <= 20;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT

PROMPT
PROMPT Checking for Failed jobs
PROMPT
SELECT
OWNER,
LOG_DATE,
JOB_NAME,
STATUS --,
-- REQ_START_DATE,
-- ACTUAL_START_DATE,
-- RUN_DURATION
FROM
DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE
STATUS <> 'SUCCEEDED'
AND
LOG_DATE > SYSDATE -7;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT


PROMPT
PROMPT Checking for invalid objects
PROMPT
column owner format A15
column object_name format A30 heading 'Object'
column object_id format 999999 heading "Id#"
column object_type format A15
column status format A8
select owner, object_name, object_id, object_type, status
from dba_objects where status != 'VALID' and object_type != 'SYNONYM';

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT
PROMPT How large is the database
PROMPT
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select     round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,     round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
     round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,     round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select     bytes
     from     v$datafile
     union     all
     select     bytes
     from      v$tempfile
     union      all
     select      bytes
     from      v$log) used
,     (select sum(bytes) as p
     from dba_free_space) free
group by free.p;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT
PROMPT
PROMPT Checking the recycle Bin
PROMPT
SELECT
OWNER, SUM(SPACE) AS TOTAL_BLOCKS
FROM
DBA_RECYCLEBIN GROUP BY OWNER
ORDER BY OWNER;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT

PROMPT
PROMPT Checking database versions
PROMPT
column BANNER format A64
select * from v$version;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT

PROMPT
PROMPT Checking control file(s)
PROMPT
column STATUS format a7
column NAME format a68
column IS_RECOVERY_DEST_FILE format a3
set linesize 132
SELECT * FROM V$CONTROLFILE;

PROMPT
PROMPT ------------------------------------------------------------------------|
PROMPT
PROMPT
PROMPT Checking redo logs and group(s)
PROMPT
column member format a90
SELECT group#, member FROM v$logfile;

SPOOL OFF

Setting up the DATABASE ENVIRONMENT -

oracle@server123:/data/oracle/data $ . oraenv -- (PRESS ENTER it will ask for the database Name of which environment needs to be set)
.
ORACLE_SID = [rdbms1120] ?

Enter Database Name : ORACLE_DATABASE

and it will set the Environment (the given HOME Location and BIN directory for execution of default scripts / Programmes)

Check Listener running for the database

$: use " ps -ef|grep tns "

LOCAL AUTHENTICATION DISABLED :

when local authentication is disabled , access can be granted using password file (create password file with password ) and set the environment connect using
sqlplus sys as sysdba
password = (Passwoord which is in the password file )

- service check at OS level : -------------------------------------- srvctl status database -d DATABASE_NAME srvctl status database -d DATABASE_NAME -v

- long Running Query: --------------------------------------

select b.spid,a.sid, a.serial#,a.username, a.osuser from v$session a, v$process b where a.paddr= b.addr and a.sid='&sid' order by a.sid; select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text from v$sqltext_with_newlines t,V$SESSION s where t.address =s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE' and s.username <> 'SYSTEM' order by s.sid,t.piece /

- EXPORT PARFILE --------------------------------------
userid="/ as sysdba" SCHEMAS=SPC_FV DIRECTORY=DATA_PUMP_DIR LOGFILE=SPC_FV.log DUMPFILE=SPC_FV_%u.dmp
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')" parallel=7

restore Point --------------------------------------

create restore point restore_point_name; - SCP scp - r destination_server_name:/Location

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

Privileges - user

select privilege from dba_sys_privs where grantee='SAMPLE' order by 1;

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

find & kill session :

select ' alter system kill session '||''''||SID||','||SERIAL#||''''||';' from gv$session where username IN ('Username');

--------------------------------------
SELECT s.* ,'alter system kill session ''' || s.sid || ', ' || s.serial# || ', @' || s.inst_id || ''';' FROM gv$session s WHERE status = 'INACTIVE' AND s.USERNAME = 'USERNAME'

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

select 'drop '||object_type ||' '|| owner||'.'||object_name||';' from dba_objects where owner in ('OWNERNAME');

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

CREATE RESTORE POINT RESTOREPOINT_NAME;

Oracle Database Administration

Wednesday, December 17, 2014

Oracle DBA - Techincal Guide for DBAs

Total Pageviews