DBA Health check

Hi Friends , hope you are Doing well .

Here I going to tell you about basic check for database, table space , how to check alert log file , how to add data file in table space and some basic scripts which generally dba used in his daily routine .

Table space free space checking  : ---

col "Tablespace" for a22

                col "Used MB" for 99,999,999

                col "Free MB" for 99,999,999

                col "Total MB" for 99,999,999

                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 ;

Above script will give you the info about tatal space, free space for each tablespace in your database.

To add datafile in tablespace :----  

To add a datafile in tablespace  , first we need to find datafiles details associated to that particular tablespace .

select TABLESPACE_NAME,FILE_NAME,BYTES,STATUS  from dba_data_files where TABLESPACE_NAME like ‘<ts_name>’;

then check mount point space for the datafile and add next datafile to that table space with below command.

Alter tablespace <ts_name> add datafile <df_name> size 1g ;

it will add 1 gb datafile in that particular tablespace , you can change size as per the client requirement and mount point availabibility

 

To check alert log file

If you know the alert log file location well and good , otherwise you can find out the location of alert from below command

1)    Connect to sqlplus

] sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 11 18:07:04 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter dump;

NAME                                 TYPE        VALUE

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

background_core_dump                 string      partial

background_dump_dest                 string      /u002/app/oracle/product/diag/

                                                 rdbms/msiaad1/MSIAAD1/trace

core_dump_dest                       string      /u002/app/oracle/product/diag/

                                                 rdbms/msiaad1/MSIAAD1/cdump

max_dump_file_size                   string      20480

shadow_core_dump                     string      partial

user_dump_dest                       string      /u002/app/oracle/product/diag/

                                                 rdbms/msiaad1/MSIAAD1/trace

go to  background_dump_dest

cd    /u002/app/oracle/product/diag/ rdbms/msiaad1/MSIAAD1/trace

ls -ltr alert*

alert_ MSIAAD1.log     

go to vi   alert_ MSIAAD1.log     

then shift+G ----à you will get your curser in end of the file in vi editor and find out the issue or any alert which you have in  your database.

2^nd method to find alert log :-----

$ adrci

ADRCI: Release 11.2.0.4.0 - Production on Wed Jul 11 18:40:20 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u002/app/oracle/product"

adrci> show alert

you will get alert log file and look for any issue or alert in database.

unix/ Linux related basic dba check commands can found in "UNIX TAB in same blog.