Before Oracle DBA Scripts, If you dont know Oracle database and want to learn, click following link to start learning Oracle database with this tutorial. 
You can determine information about the flash recovery area ($recovery_file_dest) from this view, as well as by looking at some init.ora parameters (db_recovery_file_dest, db_recovery_file_dest_size). The following one-liner prints out the ORACLE_HOME of an entry with an ORACLE_SID of TESTDB: cat /etc/oratab | awk -F: '{if ($1=="TESTDB") print $2 }'. This time you pipe the results of the cat command to awk (a pattern-scanning and processing language often used to split up variable-length fields), which essentially tokenizes the string. Casimir Saternos is an Oracle Certified DBA, IBM Certified Enterprise Developer, and Sun Certified Java Programmer based in Allentown, Pa. Were sorry. uniq. The F option directs awk to use the equal sign as the delimiter. If you are administering an Oracle E-Business Suite 11i application instance, you have numerous other environmental variables that can be used to locate resources. You need killing all processes of any user , then you can use following code. If a script takes a filename as an argument (or prompts a user for a filename) and the file will be read later in the script, it is advisable to check whether it is accessible and readable. Because they can be developed quickly, they have historically been used for prototyping applications. You can change *aud extension to the *trc or *trm. If any lines are found that contain ORA-, an e-mail message is sent to a designated recipient. If you create routing file like above then you can restart network service like below. Note that this article is designed for Linux scripting beginners or DBAs who are relatively new to Linux; most experienced Linux sysadmins will not find it helpful. # If the database is a ordinary Primary database, it runs DB_INFO,Checks presence of dataguard [if so runs PRIM_DR], Tablespace, # RMAN_REPORT or RMAN_REPORT_CMD, GATHER_STATS_RPT, DB_ALERT, AWR_REPORT, INDEX_REBUILD, lsnrctl_status, echo "This Database don't have a Data Guard" >>, echo "Data gaurd is available for this database, echo "Executing Datagaurd Configuration on Primary" >>, echo "Checking Index Fragmentation report of, echo "Pulling up the Report for last 24 hours, should recieve in seperate mail" >>, # If the database is "Physical Standby" in mounted state, it runs DB_INFO, PRIM_DR, DB_ALERT, echo "Gathering the Standby Database information, ##########################################, # If the database is "Physical Standby" in OPEN mode, it runs DB_INFO, PRIM_DR,RMAN_REPORT,DB_ALERT,AWR_REPORT. to_char(a.START_TIME,'mm/dd/yy hh24:mi') start_time.
However, what if you have a requirement that an operation needs to be performed on each ORACLE_HOME listed in the /etc/orainst file? These are required escape sequences within the string: The first slash escapes the second slash, which escapes the dollar sign. You are searching for lines that contain the literal inventory_loc. Learn more about bidirectional Unicode characters. Oracle Recovery Manager (RMAN) is a utility that can be used to administer database backup and recovery. The < The tnsping utility can be used to see if a given Oracle connection identifier can contact the listener. Perhaps you have several databases on a single machine. Many aspects of Oracle Database, such as loading, querying, object creation, recovery, and replication, can take advantage of parallelism to speed up activities that can be broken down. If the contents between the brackets evaluate to true, the commands that appear between if and fi will be executed. It is imperative that only users with the correct permissions and environment run a given script. In this case, you are using mail ( send mail might also be used) to send a message. These include APPL_TOP, TWO_TASK, CONTEXT_NAME, and CONTEXT_FILE, to name a few. Location of the database control files. An administrator will develop unique solutions to meet the needs of the particular system. You can add default gateway to the linux like below. Arguments are words to the right of the command name that are passed into the script. To review, open the file in an editor that reveals hidden Unicode characters. The at command can be used to execute a script in the future, and cron can be used to schedule scripts to execute on a recurring basis. This is the meaning of the first line found in all of the script examples: Depending on your background, you may not see any immediate value to shell scripting as it relates to the DBAs work. The sample scripts 10gchecks_kernel.sh and 10gchecks.sh simply display current and recommended versions and settings based on the Oracle documentation. To access the first parameter, you use the $1 variable. Location of archived redo logs. Oracle DBA Scripts All in One for Oracle DBA SysDBASoft. You want to automate system setup that occurs prior to the installation of the Oracle software. The init.ora parameter control_files also contains this information. The query is executed and SQL*Plus is exited. Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles. search_log.sh. For example, an alert log location might be designated as, $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log.
where begin_interval_TIME > SYSDATE - 1.08 order by 1 asc) where rownum = 1; select 'define report_type=text' from dual; select '@?/rdbms/admin/awrrpt.sql' from dual; /^TARGET/ { C=$2 ; if (C == "ONLINE") C=" ONLINE" }, /^STATE/ { D=$2 ; S="" ; if (D != "OFFLINE") S=" ", ###########################################################, set pages 0 head off lines 200 feedback off, select '# ---------------------------------------------------------'||chr(10)||, '# -- Below OBJECTS are analyzed recently (within 10days) --'||chr(10)||, '# ---------------------------------------------------------'||chr(10), select Last_Analyzed, rtrim (xmlagg (xmlelement ("o",OWNER||':'||NO_OF_OBJECTS|| ' ,')).extract ('//text()'),',') OWNERS. The script might also create relevant OS users and groups and set environmental variables for the users. The installation of software, monitoring of system resources, and management of systems involve repetitive and error-prone tasks are better addressed through automated processes than manual procedures. We suggest you try the following to help find what youre looking for: Learn some basic bash shell scripts for installing, running, and maintaining Oracle databases on Linux. Oracles fixed views (the views with a v$ prefix) can be used to determine the host name of the machine (v$instance) or the platform name on which the database is running (v$database). dbms_output.put_line('# ---------------------------------------------------- #'); dbms_output.put_line('Tread No. The following examples cover the essential aspects of providing output to view (using echo), looping, conditional logic, and variable assignment. For that reason, Oracle Database 10g for Linux includes enhancements that are closely related to the operating system. exec_sql.sh. if it is running and available to process queries). Check the spelling of your keyword search. The first command, ps (using the -ef options for a full listing of all processes), finds all of the processes running on the server. It contains not real files but, rather, runtime system information that can be viewed as if it resided in files. The charts below summarize information accessible from within the database itself: You can also obtain this information by running the following from bash: The results of this query can vary significantly, depending on your installation and version of Oracle Database. Nightly backups are commonly scheduled and run automatically. 
You need to automate backups. You might run this utility to check for connection problems: Database exports and imports (traditional and data pump) are also good candidates for the scripting of repeating processes. However, there are several reasons you might find yourself delving into the world of shell scripting, including the following: Oracle Database includes functionality that extends beyond the traditional definition of an RDBMS. This example is a simple representative sequence of commands that can be used to read user input for various purposes. If you do not have experience with UNIX or UNIX-like systems, the myriad of cryptic commands might be cause for concern. Other points of access on the file system indicated by parameters. Parameters that may be returned include: The spid can be correlated with ps ef results to compare the information available within the database with OS information for a given process. Under such circumstances, shell scripting provides a means for fulfilling the requirement in a manner that does not associate the process with a single database. For example, a recovery script that involves the selection of a backed-up control file might prompt the user to make a selection that will be used later in the script to restore the file. If there is more than one inventory location due to multiple installs, you want to exclude lines commented out with a # . The stty echo command turns off the screen echo, so that the information entered for the subsequent read command will not appear on the screen. Shell scripting can be an effective tool for quickly automating repetitive and error-prone administration tasks. The parameter pga_aggregate_target is used to configure memory for all dedicated server connections. As mentioned earlier, it is possible to query the database from a shell script provided the user has access to sqlplus. decode(bitand(i.property, 1), 0,' ', 1, 'x','?') s.archived_thread#, s.archived_seq#, s.applied_thread#, s.applied_seq#. * seg.bytes/(1024*1024)))*100/(seg.bytes/(1024*1024)) NEW_IDX_percentage, (100-((1 + i.pctfree$/100) * (i.rowcnt * (sum(h.avgcln) + 11) / (i.leafcnt * (8192 - 66 - i.initrans * 24)), * seg.bytes/(1024*1024)))*100/(seg.bytes/(1024*1024))) Benifit_percent, i.type# in (1,4,6) and -- exclude special types, seg.blocks > i.leafcnt -- if i.leafcnt > seg.blocks then statistics are not up-to-date. 
If you are considering scripting for a task that requires a good deal of database access, scripting is probably not your best option. You then print the second token of this string ( $2 ), which is everything to the right of the equal sign. This command is what lets you iterate through all the arguments in the argument list (rather than remaining in an infinite loop). do_continue.sh. It might be better to rewrite the script in PL/SQL, Perl (which uses syntax similar to that used in shell scripting), Python, Java, or another language of your choice. There is a traditional pision of responsibilities between sysadmins and DBAs. Oracle DBA Scripts All in One post will be updated with new Oracle DBA Scripts. 7 Comments. There are various built-in PL/SQL packages as well as the ability to create Java and C database objects that allow for additional access to the underlying OS. I will share all Oracle DBA Scripts ( Oracle RAC, Dataguard, Performance Tuning, Monitoring and etc.. ) in this tutorial series. You can determine various aspects of the system by looking at the /proc virtual or pseudo file system. echo "Checking if RMAN Backup is running for the Standby: echo "Pulling up the AWR Report for last 24 hours, should recieve in seperate mail" >>, #############################################, # If the database is PRIMARY and in MOUNTED state, it runs DB_ALERT, request the dba to check the database manually. Many IT shops employ inpiduals who address concerns at the database as well as the operating system levels. A useful check in a script tests the user who is attempting to run the script. In this article, I will introduce functionality available through "bash" shell scripting that is relevant to installing, running, and maintaining Oracle databases on a Linux platform. Cannot retrieve contributors at this time. floor((1 - i.pctfree$/100) * i.leafcnt - i.rowcnt * (sum(h.avgcln) + 11) / (8192 - 66 - i.initrans * 24) ) xtrblk, (1 + i.pctfree$/100) * (i.rowcnt * (sum(h.avgcln) + 11) / (i.leafcnt * (8192 - 66 - i.initrans * 24)), ((1 + i.pctfree$/100) * (i.rowcnt * (sum(h.avgcln) + 11) / (i.leafcnt * (8192 - 66 - i.initrans * 24)). decode(bitand(i.property, 1), 0,' ', 1, 'x','? You can query the version of a package by using the rpm command with the q option. You can delete all trc extension files like following if their argument list too long. There is no particular reason to allow the temporary file (tmp) to remain, so it can be removed. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. s.protection_mode, s.standby_logfile_count. You can call Oracle Recovery Manager from a shell script and use it to perform a wide range of backup and recovery activities. The v option excludes lines that contain the given pattern. The shell itself is a command that reads lines from either a file or the terminal, interprets them, and generally executes other commands. Your email address will not be published. You can access the third Oracle DBA scripts related to Blocking Sessions and Lock Kill Scripts with following link. For example, execute following command to transfer all contents of /u01/backup to the 192.168.63.63 server for the first time. Several different shells are available, including the C shell (csh), Korn shell (ksh), Bourne shell (sh), and Bourne-Again shell (bash). See the RMAN documentation to construct a backup that is relevant for your situation. The exclamation point serves to negate the test you are doing. The underscore character needs to appear in quotes, and the backslash is used before each of the quotes to escape those quotes (because the entire string appears within a set of double quotes). As mentioned previously, if you are going to be writing something that requires extensive database access, shell scripting is not the best option. Database Monitoring. You can log on as the database user and echo the $ORACLE_HOME variable. Database Installation. Use synonyms for the keyword you typed, for example, try "application" instead of "software.". After the sensitive information has been read and stored in a variable ( pw in the example below), the display can be turned back on with stty echo. This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository. round(a.OUTPUT_BYTES/(1024*1024)) as OUTPUT_MB, round(a.elapsed_seconds/3600,2) elapsed_hrs, trunc(a.start_time) >= trunc(sysdate) - 2, export NLS_DATE_FORMAT="DD/MON/YYYY hh24:mi:ss". Any backups that are older than those specified in the retention policy are then deleted. One method that is used to automate such tasks is shell scripting. d.transmit_mode, d.affirm, d.async_blocks. The location and other attributes of datafiles (v$datafile, dba_data_files), temp files (v$tempfile, dba_temp_files), redo logs (v$logfile), archive logs (v$archived_log), and control files (v$controlfile) can be queried directly from the database. The title of the message contains the script being executed ( $0), the name of the log being searched ( $1), and the lines that matched our initial search ( ORA-) as the body of the message. The /etc/oratab file identifies databases (and other Oracle programs) installed on the server. Many of the steps involved in the setup of a database can be automated. dbms_output.put_line('---------------------------------------------------------------------------------------'); dbms_output.put_line('Dest ID: '||r1.id||pad10||'Status: '||r1.stats); dbms_output.put_line('DB Name: '||r1.db_unique_name||pad10||'DB Mode: '||r1.database_mode); dbms_output.put_line('Recovery Mode: '||r1.recovery_mode); dbms_output.put_line('Protection Mode: '||r1.protection_mode); dbms_output.put_line('SRL Count: '||r1.standby_logfile_count||pad10||'SRL Active: '||r1.standby_logfile_active); dbms_output.put_line('Archived Thread#: '||r1.archived_thread#||pad10||'Archived Seq#: '||r1.archived_seq#); dbms_output.put_line('Applied Thread#: '||r1.applied_thread#||pad10||'Applied Seq#: '||r1.applied_seq#); dbms_output.put_line('Destination: '||r1.destination); dbms_output.put_line('Archiver: '||r1.archiver); dbms_output.put_line('Transmit Mode: '||r1.transmit_mode); dbms_output.put_line('Affirm: '||r1.affirm); dbms_output.put_line('Asynchronous Blocks: '||r1.async_blocks); dbms_output.put_line('Net Timeout: '||r1.net_timeout); dbms_output.put_line('Delay (Mins): '||r1.delay_mins); dbms_output.put_line('Reopen (Secs): '||r1.reopen_secs); dbms_output.put_line('Register: '||r1.register); dbms_output.put_line('Binding: '||r1.binding); -- dbms_output.put_line('Compression: '||r1.compression); set pagesize 9999 feed off ver off trims on, select thread#, low_sequence#, high_sequence# from gv, select l.thread# thread,max(r.sequence#) lr, max(l.sequence#) ls. As long as you have executable permission for a file, you can run it from the command line by typing its name. If you do not have executable permission for the file but do have read permission, you can run the script by preceding it with sh. ", echo "Checking CRS Status. Such a command could be used in the context of a script that would do comparisons and respond accordingly (even updating the system itself). echo "Sorry Intelligent not working here for the Database: ## BUG: This is not suppose to remove right here. The last instance of awk then uses the underscore character as the delimiter to search for and print the database name that owns the SMON process. July 4, 2019 Now more than ever, DBAs need to have knowledge of and experience on this platform to best administer the systems under their watch. Every system has distinct quirks and foibles and a unique configuration. The directory being used in an Oracle 10g installation for the flash recovery area. A running Oracle database can be used to execute manual or scheduled tasks. You signed in with another tab or window. Furthermore, many sysadmins and DBAs find it necessary or convenient to automate tasks related to their work. SQL and Oracles fixed views provide a picture of the system from inside the database, whereas shell scripting provides a view of the system from outside of the database. A variety of logs are generated by Oracle products, and you might be interested in monitoring them. Scripts written to interact with Oracle Database sometimes require the entry of sensitive information such as a database password. If a script is designed to be run without user input, several options are available for calling it. (select owner, to_char(trunc(last_analyzed),'mm-dd-yy') Last_Analyzed, count(*) NO_OF_OBJECTS from dba_tables where last_analyzed > SYSDATE -100 and, OWNER not in ('SYS','SYSMAN','SYSTEM','SCOTT','MGMT_VIEW','ODM','ODM_MTR','OE','OLAPSYS','DBSNMP','PERFSTAT','XDB'), select '# ------------------------------------------------------------------'||chr(10)||, '# -- Below OBJECTS are not analyzed recently (older than 10days) --'||chr(10)||, '# ------------------------------------------------------------------'||chr(10), select rtrim (xmlagg (xmlelement ("o",OWNER||':'||count(*)|| ' ,')).extract ('//text()'),',') OWNERS, dba_tables where last_analyzed < SYSDATE -10 and, ############################################################, column total_bytes format 999999999999999. select x.tablespace, x.free_gb,x.size_gb,x.maxsize_gb, trunc( (1- (x.free_gb/x.maxsize_gb))*100, 2) percent_used, round((1- (x.free_gb/x.maxsize_gb))*100,0) Percent from, ,sum((a.maxsize-a.bytes+b.free)/1024/1024/1024) free_gb, ,sum(a.maxsize/1024/1024/1024) maxsize_gb, ,sum(decode(autoextensible,'YES',maxbytes,bytes)) maxsize, where a.tablespace_name=b.tablespace_name(+), where trunc( (1- (x.free_gb/x.maxsize_gb))*100, 2) > 80. order by trunc( (1- (x.free_gb/x.maxsize_gb))*100, 2); (including all the auto extensible datafiles) of, #########################################################. Other Oracle utilities can be run from within shell scripts as well. The init.ora parameters log_archive_dest_n also contain this information. Again, not pretty, but functional.
If the user enters anything other than y, that person is informed that the script is quitting and it exits without executing the remainder of the script following the end of the if block ( fi). cat /etc/oraInst.loc |grep -v "#"|grep inventory_loc. If the word count ( wc) is greater than ( -gt) zero, the contents of the if block will execute. The dblist variable is being used as an array. However, some tasks need to be run when the database is. About seven years ago, Oracle released the first commercial database on Linux. RMAN is then used to shut down the database, start up and mount the database, and proceed to back up the server parameter file and the contents of the database. Utilities by Oracle and other third-party vendors are invoked through shell scripts. Database entries in this file are of the form. Mehmet Salih Deveci You can create related directory and mount this volume group with this directory. For instance, /proc/meminfo contains memory information for the system, and grep MemTotal /proc/meminfo displays the total memory of the system. You can run a script in the background and continue even if you disconnect, by entering a command in the following form: This can be useful for running scripts that take a long time to complete. If you want to list all the databases that are currently running on a server, you can run the following command: echo "ps -ef | grep smon|grep -v grep|awk '{print $8}'| awk -F \"_\" '{print$3}'". If you would like to determine the ORACLE_HOME for a given database, you have several options. is what actually performs the test. The database alert log contains messages that are critical to database operations. It may not be advisable to fulfill the requirement by using a single database, and it introduces potential security issues as well. The actual test appears between the brackets. You may want to delete files older than 3 days, you can use following query. Some files reside in a fixed location for a given Oracle install. The parameter parallel_threads_per_cpu sets an instances default degree of parallelism. The following example retrieves the currently logged-on user, by using whoami, and displays the date, by using the date command later in the script. This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To see a complete list in your environment, execute the following command and examine the resulting file (myenv.txt): Various combinations of these environmental variables can be used as the location of a file being searched. The crs bin path is=, ##################################################################################, echo "Checking Filesystem space on server, #######################################################################################################################, # Checking the number instance currently up and looping into each individual instance and calling the necessary functions, ps -ef | grep pmon | grep -v grep |grep -v ipmon | cut -d'_' -f3 >, cat /tmp/inst_list | while read inst_name, It will only checks alert log and pulls the last 24 hours awr report for this instance" >>, ###############################################################################, # Setting the environment vairable of ORACLE DATABASE following same method of .oraenv, ## If c_oratab file found then choose it otherwise look for the default oratab, Or if you are using /tmp/c_oratab please check it\n" >>, # Put new ORACLE_HOME in path and remove old one, esac #anything in next case statement, # Checking and logging the Database Version, # Checking if the Instance is ASM and running ASM related functions, set heading off head off feed off feedback off lines 500 pagesize 0. select group_number, substr(name,1,20) DISK_NAME, state, type, total_mb, free_mb, round((1- (DECODE(total_mb,0,0,free_mb/total_mb)))*100,0) from v, # Checking the database open type, other instances name, archive destination and bdump location, set linesize 200 heading off head off feed off feedback off pagesize 0. select decode(OPEN_MODE, 'READ WRITE','READ_WRITE','READ ONLY','READ_ONLY','MOUNTED','MOUNTED') open_mode, decode(DATABASE_ROLE,'PHYSICAL STANDBY','PHYSICAL_STANDBY','PRIMARY','PRIMARY') ROLE from v, ##############################################. The next, grep, searches for SMON (the Oracle System Monitor background process), which indicates that the database is running. You can install VNC Server on Oracle Linux with below yum packages. We could not find a match for your search. It important to recognize that many aspects of the operating system can be monitored and modified from within the database. You want to remove entries that refer to the grep command itself, which is being run. The Oracle UTL_FILE_DIR parameter and DIRECTORY database objects can be used to access files that are not a part of standard database functioning. Such a script could also monitor other processes and resources that are not Oracle-specific to provide a fuller picture of what is occurring in the system. Once you have the record that indicates the inventory location, you want to remove the portion of the record prior to the equal sign. By breaking down the script into its component commands, you will get a better understanding of what is being accomplished. a.STATUS || ' ' || COMMAND_ID || ' ' || a.INPUT_TYPE as status. The Bourne-Again shell incorporates features of the other shells mentioned and is the one that was used for the scripts this article demonstrates. round(a.INPUT_BYTES/(1024*1024)) as INPUT_MB. However, in practice, the distinction is not always clear. The examples in this article provide an introduction to the possibilities available but are far from comprehensive.
