oracle health check

## SQL - HealthCheck###
set echo off
set heading off
set lines 500 pages 500

prompt Health Check Report for Database  
select chr(9)||chr(9)||chr(9)|| name from V$database;
prompt Instance Name
select  INSTANCE_NAME, to_char(STARTUP_TIME,'dd-MON-yyyy hh24:mi') STARTUP_TIME from V$instance;

set heading on
col OWNER for a15
col OBJECT_NAME for  a30
break on owner on object_name
prompt ############################################################################
prompt Invalid Objects Currently in database
prompt ############################################################################

select owner, object_name, object_type from dba_objects where status <> 'VALID'
order by 1,2,3;

clear breaks

prompt ############################################################################
prompt Blocking Session in Database
prompt ############################################################################

select sid, serial#, username, status, event, BLOCKING_INSTANCE, BLOCKING_SESSION, BLOCKING_SESSION_STATUS, FINAL_BLOCKING_INSTANCE, FINAL_BLOCKING_SESSION_STATUS, FINAL_BLOCKING_SESSION
from v$session
where blocking_session is not null;

prompt ############################################################################
prompt Details of Blocking SIDs
prompt ############################################################################

select sid, serial#, username, status, event from V$session
where sid in (select distinct BLOCKING_SESSION from v$session
where blocking_session is not null);

prompt ############################################################################
prompt Sessions > 20MB PGA
prompt ############################################################################


column PGA_ALLOC_MEM format 99,990
column PGA_USED_MEM format 99,990
column inst_id format 99
column username format a15
column program format a40
column logon_time format a20

select s.inst_id, s.sid, s.username, s.logon_time, s.program, PGA_USED_MEM/1024/1024 PGA_USED_MEM, PGA_ALLOC_MEM/1024/1024 PGA_ALLOC_MEM
from gv$session s
, gv$process p
Where s.paddr = p.addr
and s.inst_id = p.inst_id
and PGA_USED_MEM/1024/1024 > 20  -- pga_used memory over 20mb
order by PGA_USED_MEM;

prompt ############################################################################
prompt Current Wait Events in the Database
prompt ############################################################################


col wait_class for a40
col event for a60
select wait_class, event, count(*)  from v$session
where username is not null
group by wait_class,event
order by 3,1,2;


prompt ############################################################################
prompt Detailed Sess Waits - (sesswaits.sql)
prompt ############################################################################


col seconds_in_wait heading "Wait|(Sec.)" format 9,999,999
select event,
       sid,BLOCKING_SESSION,sql_id,
       p1,
--       p1text,
       p2,
--       p2text,
SECONDS_IN_WAIT
from v$session
where event not in ('SQL*Net message from client',
                'SQL*Net message to client',
                'pipe get',
                'pmon timer',
                'rdbms ipc message',
                'Streams AQ: waiting for messages in the queue',
                'Streams AQ: qmn coordinator idle wait',
                'Streams AQ: waiting for time management or cleanup tasks',
                'PL/SQL lock timer',
                'Streams AQ: qmn slave idle wait',
                'jobq slave wait',
                'queue messages',
                'io done',
                'i/o slave wait',
                'sbtwrite2',
                'async disk IO',
                'smon timer')
order by event, p1,p2 ;


prompt ############################################################################
prompt Wait Events in Last 7 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*)  from v$active_session_history
where sample_time > sysdate - 1/192
group by wait_class,event
order by 3,1,2;


prompt ############################################################################
prompt Wait Events in Last 15 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*)  from v$active_session_history
where sample_time > sysdate - 1/96
group by wait_class,event
order by 3,1,2;


prompt ############################################################################
prompt Wait Events in Last 60 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*)  from v$active_session_history
where sample_time > sysdate - 1/24
group by wait_class,event
order by 3,1,2;

prompt ############################################################################
prompt Current IO Functions Statistics
prompt ############################################################################


col function_name    format a25         heading "File Type"
col reads            format 99,999,999  heading "Reads"
col writes           format 99,999,999  heading "Writes"
col number_of_waits  format 99,999,999  heading "Waits"
col wait_time_sec    format 999,999,999 heading "Wait Time|Sec"
col avg_wait_ms      format 999.99      heading "Avg|Wait ms"

set lines 80
set pages 10000

select
   function_name,
   small_read_reqs + large_read_reqs reads,
   small_write_reqs + large_write_reqs writes,
   wait_time/1000 wait_time_sec,
   case when number_of_waits > 0 then
          round(wait_time / number_of_waits, 2)
       end avg_wait_ms
from
   v$iostat_function
order by
    wait_time desc;


set heading off
prompt ############################################################################
prompt Load Average For Server
prompt ############################################################################
 select 'Load Average - ' ||   value  || ' NUM_CPUS  - ' || (select   value   from v$osstat where stat_name = 'NUM_CPUS') || ' LA/pCPU - ' || value/(select   value   from v$osstat where stat_name = 'NUM_CPUS')
   from v$osstat
 where stat_name = 'LOAD';

set pagesize 60
column "Tablespace" heading "Tablespace Name" format a30
column "Size" heading "Tablespace|Size (mb)" format 9999999.9
column "Used" heading "Used|Space (mb)" format 9999999.9
column "Left" heading "Available|Space (mb)" format 9999999.9
column "PCTFree" heading "% Free" format 999.99

ttitle left "Tablespace Space Allocations"
break on report
-- compute sum of "Size", "Left", "Used" on report
select /*+ RULE */
t.tablespace_name,
NVL(round(((sum(u.blocks)*p.value)/1024/1024),2),0) Used_mb,
t.Tot_MB,
NVL(round(sum(u.blocks)*p.value/1024/1024/t.Tot_MB*100,2),0) "USED %"
from v$sort_usage u,
v$parameter p,
(select tablespace_name,sum(bytes)/1024/1024 Tot_MB
from dba_temp_files
group by tablespace_name
) t
where p.name = 'db_block_size'
and u.tablespace (+) = t.tablespace_name
group by
t.tablespace_name,p.value,t.Tot_MB
order by 1,2;

prompt ############################################################################
PROMPT ======================= Total TEMP_TS consuming =======================
prompt ############################################################################
select tablespace, sum(blocks)*8192/1024/1024 consuming_TEMP_MB from
v$session, v$sort_usage where tablespace in (select tablespace_name from
dba_tablespaces where contents = 'TEMPORARY') and session_addr=saddr
group by tablespace;

prompt ############################################################################
PROMPT ======================= Sessions consuming TEMP_TS more than 10 MB =======================
prompt ############################################################################
select sid, tablespace,
sum(blocks)*8192/1024/1024 consuming_TEMP_MB from v$session,
v$sort_usage where tablespace in (select tablespace_name from
dba_tablespaces where contents = 'TEMPORARY') and session_addr=saddr
group by sid, tablespace having sum(blocks)*8192/1024/1024 > 10
order by sum(blocks)*8192/1024/1024 desc ;

prompt ############################################################################
PROMPT ======================= Current Locked Objects =======================
prompt ############################################################################

 col owner for a25
 col object_name for a35
 col oracle_username for a25
col os_user_name for a25

 SELECT B.Owner, B.Object_Name,b.object_type, A.Oracle_Username, A.OS_User_Name, A.SESSION_ID, A.LOCKED_MODE
 FROM V$Locked_Object A, All_Objects B
 WHERE A.Object_ID = B.Object_ID

How to view Current Transaction Status (ROLLBACK or ONGOING)

Oracle Database is how to find out the current Transaction Status in Oracle i.e it is in rollback / Ongoing ?


col username for a15
col tr_status for a15
col COMMAND_NAME for a20

select ss.sid, ss.serial#, ss.username, st.used_ublk, st.used_urec, ss.status, decode(st.flag,7683,'ONGOING',7811,'ROLLBACK', st.flag) tr_status,  sqt.command_name, ss.sql_id, ss.prev_sql_id
from v$session ss , v$transaction st, V$sqlcommand sqt
where ss.saddr = st.ses_addr
and sqt.command_type = ss.command
order by 3;

V$FAST_START_TRANSACTIONS

rac OLR permission change

changing permission of olr

cat -n /etc/oraInst.loc

to verify integrity of olr

cluvfy comp olr -verbose

sudo root

sudo /u01/app/12.1.0.2/GRID/bin/ocrcheck -local

ls -ltrah /u01/../cdata/rac131.olr

-rw-------. 1 oracle oinstall 481M


chown  root:oinstall

shell script

$#  Stores the number of command-line arguments that were passed to the shell program

$? Stores the exit value of the last command that was executed. 

$@ treats each quoted arguments as separate arguments .

 $* will consider the entire set of positional parameters as a single string.

interview questions

1)  what is ocr
2) what is scan listener and scan ip
3) what the thing present in cdb and pdb
4) if cdb goes down will pdb alive
5) upgrade steps
6) dataguard switch over
7)cache fusion
8) global enque service global cache service
9) steps for clusteware upgrade
10) what is cluvfy
11) upgrade process from 11g to 18c
12) we have three oracle binaries and the central inventory is lost then what to do.
13) if archive log is lost how to perform the backup successfully.
14)

what is the difference between $#
how to sort a number
how to find a particular word
how to replace last five words
how to run a job from foreground to background in linux
what is for loop and while loop
what is break statement

max no of process execeed 300

2. Check Current Setting of Parameters
 show parameter sessions;
show parameter processes;
show parameter transactions;

3. If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
A basic formula for determining these parameter values is as follows:

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

4. These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
sql> alter system set processes=500 scope=spfile;
sql> alter system set sessions=555 scope=spfile;
sql> alter system set transactions=610 scope=spfile;


set pagesize 500
set heading off
spool kill_inactive_session
select 'alter system kill session '||''''||sid||','||serial#||''''||' ;' from v$session where status='INACTIVE';

SQL> SET lines 200 pages 1000
SQL> SELECT *
     FROM v$resource_limit
     WHERE resource_name='processes';

Backup and restore in sql server command

Backup with copy only
+++++++++++++++++

backup database BR_EINVOICE_CONNECTOR25  to
disk =N 'F:\BKup01\MSSQL11.SQLD\MSSQL\Backup\ManualBackups\BR_EINVOICE_CONNECTOR25_1.bak',
disk =N 'F:\BKup01\MSSQL11.SQLD\MSSQL\Backup\ManualBackups\BR_EINVOICE_CONNECTOR25_2.bak',
disk =N 'F:\BKup01\MSSQL11.SQLD\MSSQL\Backup\ManualBackups\BR_EINVOICE_CONNECTOR25_3.bak',
WITH  COPY_ONLY,compression,stats=10;

or

BACKUP DATABASE [BR_eInvoice_COLD_SEARCH] TO 
DISK = N'L:\Bkup01\MSSQL11.SQLX\MSSQL\Backup\ManualBackups\Xnet_content_NAEU1_1.bak'
WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'BR_eInvoice_COLD_SEARCH-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO


backup normally


backup database BR_eInvoice_COLD_SEARCH  to disk='F:\BKup01\MSSQL11.SQLD\MSSQL\Backup\ManualBackups\BR_eInvoice_COLD_SEARCH_1.bak'
with compression,stats=10;





Restore comman in sql server 


restore database [Contacts] from disk = 'J:\AZRWUS2UIS_SQLD\BAckup\ManualBackups\Contacts_1.bak'
with replace,stats=10,norecovery,
move 'Contacts_Data' to 'I:\MSSQL_SQLI\MSSQL11.SQLD\MSSQL\DATA\Contacts_Data.mdf',
move 'Contacts_Log' to 'I:\MSSQL_SQLI\MSSQL11.SQLD\MSSQL\DATA\Contacts_Log.ldf'

for logshipping restore
++++++++++++++

restore database [BR_eInvoice_COLD_SEARCH] from disk = 'F:\BKup01\MSSQL11.SQLD\MSSQL\Backup\ManualBackups\BR_eInvoice_COLD_SEARCH_1.bak'
with replace,stats=10,norecovery,
move 'BR_eInvoice_COLD_SEARCH' to 'I:\MSSQL_SQLI\MSSQL11.SQLD\MSSQL\DATA\BR_eInvoice_COLD_SEARCH.mdf',
move 'BR_eInvoice_COLD_SEARCH _log' to 'I:\MSSQL_SQLI\MSSQL11.SQLD\MSSQL\DATA\BR_eInvoice_COLD_SEARCH_1.ldf',
standby='I:\MSSQL_SQLI\MSSQL11.SQLD\MSSQL\DATA\BR_eInvoice_COLD_SEARCH.tuf'