Quick Detection of Oracle Database Availability and table space capacity

DocumentMany people have such knowledge that the command required to check Oracle availability is much more than the command for ping, ps-ef, grep, and other Oracle commands. Some people think that it requires a test that uses SQL * Net to verify that the listener has been enabled and run to access Oracle, which is frequently used by users. By actually logging on to the actual environment, you can confirm that the instance environment can accept the logon. If you only perform the above checks, how can you know whether the logon is not accepted, is it because you need to wait for the document log?

In addition to Oracle activation and availability, we also need to check to ensure that it can be used, so that we can also detect the tablespace capacity.

Check script:

Assume that all Oracle environments have been set up.

Suppose all the extensions have reached the maximum limit.

Assume that the Left and Right tablespaces do not have free space to run.

The following script can be interrupted through crontab at any time you want. In addition, if the above exception occurs, you can also write a letter or email to the support staff for help.

If you have any other tests, this script allows you to easily modify and use them. I used this Monitororcl script as a template and added features at the end.

Press crontab to call the query_oracle_instances.sh script:

#! /Bin/ksh./u01/home/oracle/. profile/u01/app/oracle/admin/monitororcl 'cat/u01/app/oracle/admin/Get_Oracle_Instance_List 'exit

The Get_Oracle_Instance_List script is as follows:

Instance_name1 tnsname1 sys_password_for_this_instanceinstance_name2 tnsname2 sys_password_for_this_instanceinstance_name3 tnsname3 sys_password_for_this_instance

The following is the MONITORORCL script:

#! /Bin/ksh # script: Rick Stehno # script will monitor to see if Oracle is up while ["$1 "! = ""] Do ORACLE_INSTANCE = $1 ORACLE_TNS = $2 USR_ID = sys USR_PASS = $3 # echo "Instance: [$ ORACLE_INSTANCE] "# echo" TNS [$ ORACLE_TNS] "# echo" PASS: [$ USR_PASS] "LOGFIL =/u01/app/oracle/admin/monitordev1.out policy_list = userid1@mobilephone.com, userid2, userid3@pagercompany.com # Check that key segments do not reach the maximum sqlplus-s <$ LOGFIL 2>/dev/null $ USR_ID/$ USR_PASS @ $ ORACLE_TNS set pages 0 select distinct 'yes' from dba_segme ETS where extents> = (max_extents-5) and segment_name not like '1.% '; EOF1 grep-I' ^ ORA-'$ LOGFIL>/dev/null if [$? -Eq 0] then echo "$0 failed: check $ ORACLE_INSTANCE for problems" |/bin/mailx-s "$ {ORACLE_INSTANCE }: script failed "$ policy_list exit 1 fi MAXEXTENTS_REACHED = 'awk' {print $1} '$ LOGFIL' if [" $ MAXEXTENTS_REACHED "=" YES "] then echo" $0 failed: $ ORACLE_INSTANCE max extents reached "|/bin/mailx-s" $ {ORACLE_INSTANCE }: max extents reached "$ policy_list exit 1 fi # Check whether the next segment sqlplus-s can be allocated <$ L OGFIL 2>/dev/null $ USR_ID/$ USR_PASS @ $ ORACLE_TNS set pages 0 select distinct 'yes' from dba_segments ds where next_extent> (select max (bytes) from dba_free_space where tablespace_name = ds. tablespace_name); EOF2 grep-I '^ ORA-' $ LOGFIL>/dev/null if [$? -Eq 0] then echo "$0 failed: check $ ORACLE_INSTANCE for problems" |/bin/mailx-s "$ {ORACLE_INSTANCE }: script failed "$ policy_list exit 1 fi POSSIBLE_NEXTEXT_FAIL = 'awk' {print $1} '$ LOGFIL' if [" $ POSSIBLE_NEXTEXT_FAIL "=" YES "] then echo" $0 failed: $ ORACLE_INSTANCE cannot extend segment "|/bin/mailx-s" $ {ORACLE_INSTANCE }: max extents reached "$ policy_list exit 1 fi shift 3 # echo" shift done "done echo" Successful completion of $0 "'date' exit 0