Oracle Useful Commands.....Unlimited Edition ;)

Error:

ORA-19815: WARNING: db_recovery_file_dest_size of 65779059917 bytes is 85.06% used

Oracle ASM server will naturally generate this error when the available space falls below 15% at db_recovery_file_dest Area

Please Check


SQL> SELECT object_type, message_type, message_level,reason, suggested_action FROM dba_outstanding_alerts;

db_recovery_file_dest_size of 65779059917 bytes is 85.06% used and has 9828093133 remaining bytes available.

Fix:

sql>SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .04 0 1
ONLINELOG .65 0 8
ARCHIVELOG 17.61 0 329
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 71.63 71.5 1180

This will tell us the next course of action instead of leading us to sheer increasing db_recovery_file_dest_size on FRA diskgroup

SQL> select NAME,STATE,TYPE ,TOTAL_MB,FREE_MB,USABLE_FILE_MB from v$asm_diskgroup;

NAME STATE TYPE TOTAL_MB FREE_MB USABLE_FILE_MB
------------------------------ ----------- ------ ---------- ---------- --------------
dbname_DATA1 CONNECTED EXTERN 243957 64945 64945
dbname_FRA1 CONNECTED EXTERN 69702 11218 11218
dbname_DATA1 MOUNTED EXTERN 522765 367338 367338
dbname_FRA1 MOUNTED EXTERN 34851 24176 24176

The main culprits of this issue are FLASHBACKLOG and ARCHIVELOG file types occupying 71 and 17% respectively
From the alertlog the flashback logs seems to be removed by oracle server itself whenever on demand...

Therefore I tried backing up archivelogs with delete input option and now the space released with no similar outstanding alerts in the ASM box

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .04 0 1
ONLINELOG .65 0 8
ARCHIVELOG 3.48 0 65
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 70.39 70.27 1160

Useful SQL Server commands

SQLServer :

xp_fixeddrives Returns the list of hard drives and free space

dbcc sqlperf (logspace) Chk Logspace.

Shrink Database and log:

dbcc shrinkdatabase (tempdb)

sp_helpdb d_dbdba002

dbcc shrinkfile ('L001')

dbcc shrinkfile ('MSDBLog')

Chk Blocking on the server :

Blocking in Sql Server:

select spid,cmd,blocked from sysprocesses where blocked>0
sp_who2"SPID"
select spid,blocked,cmd,memusage,physical_io from sysprocesses order by memusage desc
select spid,blocked,cmd,memusage,physical_io from sysprocesses order by physical_io desc

xp_cmdshell "tlist" - Displays the host processes with ‘pid’s

Restarting job from commandshell
xp_cmdshell "nc_restart servername jobname"

select paramname, paramvalue from d_dbdba002..task_params where paramname = 'DUMP_GENERATIONS'

sp_whodo n_cycle

select @@errorlog
select @@boottime

nc_task "DBAUS"

Sqltext capture

dbcc traceon(3604)
dbcc inputbuffer(121)

Db Uptime
select crdate from sysdatabases where dbid=2
Process count
select suser_name(suid),count(*) from sysprocesses group by suid

xp_readerrorlog

select loginname , createdate,updatedate,accdate from master..syslogins where loginname like '%username%'

--SQLserve Bkp compression issues

select databasename, backup_time, compression
from d_dbdba002..backups
where backup_time >= ''
order by backup_time
go

update d_dbdba002..backups
set compression = 'zip'
where backup_time = '2009-02-14 21:32:27.880' --the milli seconds.
and compression != 'zip'
go

--Update the file name.
update d_dbdba002..backup_files
set filename = filename + '.gz'
where backup_time = '2009-02-14 21:32:27.880'
and filename not like '%.gz'

dbcc opentran

sp_monitor - statistics about sqlserver

sp_tempdbspace - returns the total size and the space used by the tempdb database

xp_ServiceControl ‘START’,’SQLServerAgent’ - to start or stop sql server agent > sql 2k and above

sp_MStablespace - returns the number of rows in a table and the space the table and index use

select * from ::fn_virtualservernodes( ) -- returns the list of nodes on which the virtual server can run

select serverproperty('option')
MachineName – Returns the hostname on which server currently running
IsClustered - Returns 1 if server in cluster, else 0.


select has_dbaccess('da name')-- Returns 1 if user has access to the specified database, else 0.

select databaseproperty ('db name', 'option')

Returns 1 for True, 0 for false, else specific.
Useful options:
IsInLoad
IsOffline
IsAutoUpdateStatistics
IsInRecovery
IsSuspect
IsTruncLog
IsAutoShrink
Status


Tsadmin -- to login to host and check active loggedin sessions


How to find out when was an index rebuilt last?

SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'object name' AND o.id = i.id
go

sp_helpdb
sp_helpdb tempdb
dump tran tempdb with truncate_only