Working on Different Database Issues

Working on Different Database Issues

1.Working on Agent/Listner/DB down Incident Ticket
------------------------------------------------------------------:
Login to Server and DB where the actual issue is. You can find the server name and DB name from the ticket.
ps -ef |grep emagent --- Check the Agent availability. If it is running then fine other wise you need to start the agent.
echo $AGENT_HOME     --- You can find the agent location in general agent location is same as ORACLE_HOME location.
emctl start agent    --- From the agent location run this command.
ps -ef | grep tns    --- Check the Listener availability. You can also use LSNRCTL>status command to check the listener. If it is running then fine other wise you need to start the listener.
LSNRCTL>start      --- From the LISTENER prompt run this command.
tnsping dbname       --- Check the DB is ping. Login to the database. Check the instance status and database open_mode. If the instance is OPEN and database is in READ WRITE mode then fine other wise you need to startup the database.
uptime               --- From the oracle user run this command to check the database uptime.
last reboot   --- From the orale user run this command to check the server reboot time.

2.Working on Metrics Level High Incident Ticket
------------------------------------------------------------:
Login to Server and DB where the actual issue is. Check the Current_utilization of process if it is less than threshold value then fine (Make the % Calculation of current_utilization out of limit_value) otherwise you need to increase the limit_value.
Select resource_name, current_utilization,  max_utilization,  limit_value from v$resource_limit
where resource_name in ('sessions', 'processes');

Note: When the number of sessions increases (activity is high) the matrics level suddenly increases after the sessions finished their jobs matrics level goes down so perform some more check before increasing the limit_value.

###INACTIVE SESSIONS COUNT WITH USERNAME  AND STATUS
select s.username,s.status, count(s.program) Count
from gv$session s,v$process p
where p.addr=s.paddr AND s.status='INACTIVE'
group by s.username, s.status order by 2 desc;

select  count(s.status) "INACTIVE SESSIONS > 1HOUR"
from gv$session s, v$process p
where p.addr=s.paddr and s.last_call_et > 3600 and s.status='INACTIVE';

3.Working on Tablespace full Incident Ticket
-------------------------------------------------------:
Login to the server and database check tablespace usage if the threshold is up > 85% then add a datafile to the tablespace. Before add datafile you must check filesystem /DG usage as datafile is autoextend.

select tablespace_name, used_percent from dba_tablespace_usage_metrics where used_percent > 85;

select df.tablespace_name "Tablespace", totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace
from dba_data_files group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments
group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name ;

ALTER TABLESPACE xyz ADD DATAFILE '/u01/oradata/xyz/xyz01.dbf' SIZE 30G;   ### in case of normal file system.

From the below query you can get the datafile size and location of particular tablespace.
COLUMN tablespace_name FORMAT A20
COLUMN file_id FORMAT A5
COLUMN file_name FORMAT A40
select substr(tablespace_name,1,15) as Tablespace, substr(file_id,1,5) as File_id, substr(file_name,1,40) as File_Name,
bytes/1024/1024/1024 as Size_GB from dba_data_files
where tablespace_name = 'XYZ';

ALTER TABLESPACE xxxxx ADD DATAFILE SIZE 30G;   ### in case of OMF file system.

### in case of Big file tablespace
You need to resize the tablespace. First check the tablespace size information from the above query then calculate resize value.
Resize value = Total_Size + Expected Size to increase

SELECT TABLESPACE_NAME, BIGFILE FROM DBA_TABLESPACES where tablespace_name = 'XYZ';

Alter Tablespace XYZ resize <resize_value>; ### resize value must be greater than Total_size

### in case of Containers and Pluggable database
Find the name of container database and you must into that container database before using alter tablespace command.
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
ALTER SESSION SET CONTAINER = pdb1;
ALTER TABLESPACE xxxxx ADD DATAFILE SIZE 30G;   ### in case of OMF file system.

### in case of Temp tablespace full alert
Check the TEMP tablespace usage details. If the TEMP tablespace usage is above threshold value then perform some more check to confirm who is using temp segemnt.

select a.tablespace_name tablespace,d.TEMP_TOTAL_MB,sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB,
round(100 * ( (d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024)/ d.TEMP_TOTAL_MB)) "Pct. Free"
from v$sort_segment a, (Select   b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
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.TEMP_TOTAL_MB;

###Sort space used by the session
select S.username, S.osuser, P.spid, s.program, SUM(T.blocks)*TBS.block_size/1024/1024 mb_used, T.tablespace, Count(*) sort_ops
from v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
where T.session_addr = S.saddr and S.paddr = P.addr and T.tablespace = TBS.tablespace_name
Group by S.username, S.osuser, P.spid, S.program, TBS.block_size, T.tablespace order by P.spid;

Note: Temp space is used by the sessions once the sessions finishes their jobs it is automatically reclaimed temp space.

4.Archive Area Full or FLASH drive full incident ticket
--------------------------------------------------------------------:
Login to the server and validate the FLASH mount point usage, if the usage below threshold (85%) fine otherwise you need to take archive backup with delete input command. To remove all archive which is already backed up from the archive area.

rman target / connect catalog catalog/catalog@rman
Backup archive log all delete input;

5.FILESYTEM Full Alert (DATA/TEMP/FLASH/ASMFS
-----------------------------------------------------------------------:
Login to the server where the actual issue is.Validate /u01 mount point if the threshold value < 85% then fine otherwise you need to perform some clean up better to move older logs to other location.

In case of ASM file system. you need to add ASM disk to the diskgroup to increase the diskgroup size. Reach to the storage team or create a ticket for storage team to provide same size of luns then create ASM disk of same size. Finally add these disks to the diskgroup.
asmcmd> lsdg   --To check the diskgroup size information
asmcmd> lsdsk  --To check the ASM disks. Same check you can perform from ASM instance prompt.
/etc/init.d/oracleasm listdisks   ### to check the ASM all disk present on the server.

SQL> SELECT GROUP_NUMBER, NAME, SECTOR_SIZE, BLOCK_SIZE, ALLOCATION_UNIT_SIZE,STATE,TYPE TOTAL_MB,FREE_MB FROM V$ASM_DISKGROUP;
SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;
SQL> select mount_status,header_status,mode_status,state,total_mb,free_mb,name,path,label from v$asm_disk where group_number = 1 order by name;

SQL> connect / as sysasm
ALTER DISKGROUP DATA ADD DISK 'ORCL:DISK074';

If this were a RAC installation, the disks would only be stamped by one node. The other nodes would just scan for the disks to make sure new disk is accessible to all node.
/etc/init.d/oracleasm scandisks

ALTER DISKGROUP DATA ADD DISK 'ORCL:DATA_0001';  ### to add new disk to diskgroup

ALTER DISKGROUP DATA RESIZE ALL SIZE 100G;  ### to resize the existing disk size.

6.Working on DB User Unlock Incident
-------------------------------------------------:
Login to the server and database where the actual issue is. Check current status (last lock date) of the user then unlock the user

ALTER USER username ACCOUNT unlock;

In case of reset the user password. Get the information of user and password from dba_users view.

ALTER user username identified by *****; ### paste the same password getting from dba_users in case you do not want to change the password.

7.Working on Flash recovery area full incident
---------------------------------------------------------:
Login to the server and Db where the actual issue is and check the flash area usage.

select name, ceil( space_limit / 1024 / 1024) size_mb,
ceil( space_used / 1024 / 1024) used_mb,decode( nvl( space_used, 0),0, 0, ceil ( ( space_used / space_limit) * 100) ) pct_used
from v$recovery_file_dest order by name desc;

Perform some more check to confirm who is using the flash recovery area before increasing the flash_recovery size.

select * from v$flash_recovery_area_usage;
show parameter db_recovery_file_dest_size;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 6000G SCOPE=BOTH;

8.Working on database block corruption incident
-----------------------------------------------------------:
Logon to the server and database where the actual issue is and perform check block corruption.

select file#, BLOCK#, CORRUPTION_CHANGE# , CORRUPTION_TYPE from V$DATABASE_BLOCK_CORRUPTION;

SELECT DISTINCT owner, segment_name FROM v$database_block_corruption dbc
JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1
ORDER BY 1,2;

Note: Block corruption is automatically repaired by standby database other wise you need to perform recovery of that particualr block.

Recover datafile block <corrupt_block>;