Shulou(Shulou.com)06/02 Report--

Http://www.itpub.net/showthread.php?s=&threadid=720028

DBA Linux Shell 7 years ago, Oracle released the first commercial database on Linux. Since then, Oracle, Red Hat, and Novell/SUSE have been working together to change the Linux kernel to improve the performance of databases and applications. Because of this, Oracle Database 10g for Linux contains many enhancements that are closely related to the operating system. More than ever, DBA needs to understand and use this platform to best manage the system under its supervision.

In the past, there were differences in responsibilities between system administrators and DBA. But in practice, the difference is usually not obvious. Many IT departments employ people who can solve problems at both the database level and the operating system level. Of course, the Oracle database itself uses operating system resources and interacts closely with its environment. In addition, many system administrators and DBA find it necessary or convenient to automate tasks related to their work. Software installation, system resource monitoring, and system management involve repetitive and error-prone tasks, and automated processes can accomplish these tasks better than manual processes.

One way to automate these tasks is through shell scripts. Shell scripts have played an important role since the beginning of the Linux system installation. Various scripts are called when the system is started and shut down. Utilities from Oracle and other third-party vendors are also callable through shell scripts. Because these scripts can be developed quickly, they have traditionally been used to build application prototypes. System administrators have used the functionality implemented through shell scripts to provide solutions tailored to the specific requirements and characteristics of the systems they monitor.

In this article, I will introduce the functions that the "bash" shell script can achieve related to installing, running, and maintaining Oracle databases on the Linux platform. Note that this article is for beginners in Linux scripting or DBA;, which is relatively new to Linux, but not for most experienced Linux system administrators.

What is a Shell script?

A shell script is a text file that contains a sequence of commands. When you run a file (or script), the commands contained in the file are executed. The term shell refers only to the specific command-line user interface used to communicate with the Linux kernel. There are currently several different shell, including C shell (csh), Korn shell (ksh), Bourne shell (sh), and Bourne-Again shell (bash). Shell itself is a command that reads commands from a file or terminal, interprets them, and usually executes other commands. Bourne-Again shell incorporates the features of the other shell mentioned above, and this article uses this script to demonstrate.

The first line in the script file can be used to specify which shell to use to run the script. The following is the meaning of the first line contained in all script examples:

#! / bin/bash

Why use Shell scripts?

Because shell scripts are relevant to DBA's work, you may not immediately see the value of shell scripts, which has something to do with your work experience. If you've never used UNIX or a UNIX-like system before, you may be troubled by a large number of obscure commands. In addition, in addition to being a relational database, Oracle 10g provides a robust platform for processing database data and several methods for interacting with the operating system outside the database.

But you will find several reasons to explore the world of shell scripting, including:

Scripts that already exist must be supported. The system needs to be set up automatically before installing the Oracle software. For example, you can write a script to check the initial state of OS and report any prerequisites that must be met before installing the software. The script can also create related OS users and groups and set environment variables for users. You can use a running Oracle database to perform manual or scheduled tasks. However, some tasks need to be run when the database is not running. You can use scripts to stop or start the database (as well as listeners or related database processes). Such actions cannot be started from within the database. You need a mechanism to monitor the status of the database (for example, whether it is running and can be queried by the process). Such scripts can also monitor other processes and resources that are not specific to Oracle, providing more detailed information about the current operation of the system. Backups need to be automated. Oracle Recovery Manager (RMAN) is a utility for developing backup scripts that can be run on any platform. You can call Oracle Recovery Manager from a shell script and use it to perform various backup and restore activities. You may have a requirement that is not specific to a database. You may have multiple databases installed on one computer. It is recommended that you do not use a single database to meet this requirement, as that can cause potential security problems. In these cases, the shell script provides a way to meet this requirement without associating a process with a single database.

Under what circumstances do not use Shell scripts

The Oracle database contains features that go beyond the traditional definition of RDBMS. Like any other part of the software, it uses the resources provided by the operating system, but it "sees" and "changes" its environment to a far greater extent than other software. The fixed view of SQL and Oracle provides the system view from inside the database, while the shell script provides the system view from outside the database. Shell scripts are not the solution for all problems.

It is important to realize that many aspects of the operating system can be monitored and modified from within the database. You can use the fixed view of Oracle (the view prefixed with v$) to determine the hostname of the computer (v$instance) or the name of the platform on which the database is running (v$database). You can also determine the location and other properties of files related to the database in this way. You can query the location and other properties of data files (v$datafile, dba_data_files), temporary files (v$tempfile, dba_temp_files), redo logs (v$logfile), archive logs (v$archived_log), and control files (v$controlfile) directly from the database. Information about the flashback recovery area ($recovery_file_dest) can be determined from this view and by looking at some init.ora parameters (db_recovery_file_dest, db_recovery_file_dest_size). You can also query the status of processes (v$process) and memory (v$sga, v$sgastat, and so on). There are various built-in PL/SQL packages and the ability to create Java and C database objects that allow additional access to the underlying OS.

If you are considering scripting a task that requires a lot of database access, scripting may not be the best choice. Later in this article, I'll show you how to use SQL*Plus to access a database, but in many cases, using other languages can better solve this problem.

The following table summarizes the information that can be accessed from the database:

Server / operating system Information

Server identification

Typical query

Notes

Name of the host on which the instance is running

Select host_name

From v$instance

You can also obtain this information by running the following command from bash:

Hostname

Or

Uname-n

Operating system platform

Select platform_name from v$database-(10g)

If you run uname-s, similar information will be returned

File information

Oracle file location

Typical query

Notes

Control file

Select name

From v$controlfile

The location of the database control file. The parameter control_files of init.ora also contains this information.

Data file

Select file_name

From Dba_data_files

Location of database data files

Temporary file

Select file_name

From Dba_temp_files

Location of temporary files in the database

Log file

Select member

From v$logfile

The location of the redo log

Archive log

Select name

From v$archived_log

The location where the redo log is archived. The parameter log_archive_dest_n of init.ora also contains this information. If the database is not in Archivelog mode, the query will not return results.

Flashback recovery area

Select name

From v$recovery_file_dest

The directory where Oracle 10g is installed as the flashback recovery area. The init.ora parameter db_recovery_file_dest also contains this information.

Other access points on the file system indicated by the parameter

Select *

From v$parameter

Where value like'% /%'

Or

Value like'% /%'

Depending on the installation and version of the Oracle database, the results of this query may vary greatly. Parameters that may be returned are:

Spfile

Standby_archive_dest

Utl_file_dir

Background_dump_dest user_dump_dest

Core_dump_dest

Audit_file_dest

Dg_broker_config_file1

Dg_broker_config_file2

Access the file system programmatically

Select directory_path from dba_directories

You can use Oracle UTL_FILE_DIR parameters and DIRECTORY database objects to access files other than standard database functions.

Process information

Processor / process

Typical query

Notes

Session process

Select p.spid, s.username, s.program

From v$process p, v$session s

Where p.addr=s.paddr order by 2, 3, 1

You can associate spid with ps-ef results to compare the information available in the database with the operating system information for a given process.

Processes related to parallelism

Select slave_name, status

From v$PQ_SLAVE

Many aspects of the Oracle database, such as loading, querying, object creation, recovery, and replication, can take advantage of parallelism to speed up divisible activity. The parameter parallel_threads_per_cpu sets the default parallelism of the instance.

Memory information

Memory

Typical query

Notes

Program global area

Select * from V$PGASTAT

The parameter pga_aggregate_target is used to configure memory for all dedicated server connections.

You can use Linux utilities such as vmstat and top to monitor memory usage.

System global area

Select * from v$sga

The SGA_MAX_SIZE and SGA_TARGET parameters are used to configure the dynamic memory allocation feature of Oracle database 10g. You can also use other parameters to manually allocate memory for special purposes.

At the same time, there are various Linux utilities that can be used to monitor memory allocation.

BASH script

The script is either called as part of the automated process (without human intervention) or run interactively (the user is prompted to perform the action). As long as you have execute permission for the file, you can type the name of the file from the command line to run it. If you do not have execute permission for a file, but you do have read permission for it, you can run the script by preceding it with sh.

If the script is designed to run without user input, it can be called using a variety of optional methods. You can run scripts in the background, even if you are disconnected, by entering commands in the following form:

Nohup / path_to_dir/myscript_here.sh &

This is useful for scripts that take a long time to complete. The at command can be used to execute scripts in the future, while cron can be used to plan scripts to be executed repeatedly.

The following example shows important aspects of providing view output (using echo), loops, conditional logic, and variable assignments.

Print_args.sh . Parameters are words to the right of the command name and passed to the script. To access the first parameter, use the $1 variable. The $0 variable contains the name of the script itself. The $# variable contains the number of parameters in the script. A convenient way to iterate over all the parameters passed is to use the while loop and the shift command. This command allows you to iterate over all the parameters in the parameter list (instead of keeping an infinite loop).

While [$#-ne 0]

Do

Echo $1

Shift

Done

If the script takes the file name as a parameter (or prompts the user for a file name) and reads the file later, it is recommended that you check its accessibility and readability. For example, a restore script that involves selecting backup control files may prompt the user to select the backup control files that will be used to restore files later in the script.

If [!-r $1]; then # not exists and is readable

Echo "File $1 does not exist or is not readable."

Exit

Fi

Character sequence

If [!-r $1]

Is the part that actually performs the test. If the content between the square brackets turns out to be true, the command between if and fi is executed. The actual test is shown between square brackets. The exclamation point is used to reverse the test performed. The-r option checks whether the file is readable. What is being tested in this particular example is the first parameter passed to the script. By using another test (- d), you can check whether a given entry is a directory (see is_a_directory.sh).

Do_continue.sh . This example is a simple, typical sequence of commands that can be used to read user input for a variety of purposes. Before running a process that may result in data loss or other bad results under conditions that cannot be determined from within the script, it is recommended that you add a prompt to ask the user if you really want the script to execute the next command. The following example asks the user whether to continue, reads a variable named doContinue from the command line, and evaluates the user's input. If the user enters something other than "y", the user is told that the script "will exit" and that other scripts after the if code block (fi) are not executed.

DoContinue=n

Echo-n "Do you really want to continue? (YPao)"

Read doContinue

If ["$doContinue"! = "y"]; then

Echo "Quitting..."

Exit

Fi

Only users with the appropriate permissions and environment can run a given script. It is useful to check the user who is trying to run the script in the script. If you enclose a command in single quotation marks (') characters, the result of the command is returned to the script. The following example uses whoami in a script to retrieve the currently logged-in user and later uses the date command to display the date.

Echo "You are logged in as' whoami'"

If ['whoami'! = "oracle"]; then

Echo "Must be logged on as oracle to run this script."

Exit

Fi

Echo "Running script at 'date'"

Scripts written to interact with Oracle databases sometimes require entering confidential information such as database passwords. The stty-echo command turns off the screen response so that the information entered for subsequent read commands is not displayed on the screen. After reading the secret information and storing it in a variable (pw in the following example), you can use stty echo to turn on the display again.

Stty-echo

Echo-n "Enter the database system password:"

Read pw

Stty echo

Oracle script

Some files are located in a fixed location for a given Oracle installation. You can get the Oracle listing by looking at the / etc/oraInst.loc file. The / etc/oratab file identifies the database (and other Oracle programs) installed on the server.

Get_inv_location.sh . This script is not as intuitive as the previous example. By dividing the script into sets of commands, you will better understand the composition of the script.

To determine the location of the manifest, you will feed the results of the cat command (showing the contents of the file) to grep, a utility that prints lines that match a given pattern. You will search for lines that contain the text inventory_loc.

Cat / etc/oraInst.loc | grep inventory_loc

If there are multiple manifest locations due to multiple installations, you need to exclude lines commented out with #. The-v option excludes rows that contain a given pattern.

Cat / etc/oraInst.loc | grep-v "#" | grep inventory_loc

The result of the command will be as follows:

Inventory_loc=/u01/oraInventory

You can use the > redirect command to redirect standard output to a file. If the file does not exist, it is created. If the file already exists, overwrite it.

Cat / etc/oraInst.loc | grep-v "#" | grep inventory_loc > tmp

Once you have a record indicating the location of the repository, you need to delete the part before the equal sign of the record. This time, you feed the results of the cat command to awk, a mode scanning and processing language commonly used to split variable-length fields, which is actually tokenizing the string. The-F option instructs awk to use the equal sign as the delimiter. Then, print the second tag ($2) of the string, which represents everything to the right of the equal sign. The result is the list location we are looking for (/ u01/oraInventory).

Cat tmp | awk-F ='{print $2}'

Because there is no need to keep a temporary file (tmp), you can delete it.

Rm tmp

List_oracle_homes.sh . If you want to determine the ORACLE_HOME of a given database, there are several options. You can log in as a database user and echo the $ORACLE_HOME variable. You can also search for the / etc/oratab file and select the name associated with a given instance. The database entries in this file are in the following form

$ORACLE_SID:$ORACLE_HOME::

The following single line of code outputs the ORACLE_HOME of the entry (ORACLE_SID is TESTDB):

Cat / etc/oratab | awk-FRV'{if ($1mm = "TESTDB") print $2}'

But what if you need to perform actions on each ORACLE_HOME listed in the / etc/orainst file? You can use the following code snippet to iterate over such a list.

Dblist='cat / etc/oratab | grep-v "#" | awk-FRV'{print $2}'

For ohome in $dblist; do

Echo $ohome

Done

The dblist variable is used as an array. All ORACLE_HOME paths are saved by this variable. The for loop iterates over the list, assigns each entry to the variable ohome, and then sends it to standard output.

Search_log.sh . Oracle products generate various logs that you may want to monitor. The database alert log contains messages that are critical to database operations. Log files are also generated when the product is installed or uninstalled and when patches are applied. The following script iterates over the file passed to it as an argument. If any lines containing ORA- are found, an e-mail is sent to the specified recipient.

Cat $1 | grep ORA- > alert.err

If ['cat alert.err | wc-l'-gt 0]

Then

Mail-s "$0 $1 Errors" administrator@yourcompany.com

< alert.err fi 执行的具体测试是统计文件 alert.err（在您重定向到 alert.err 时写入）中存在的单词数。如果单词数 (wc) 大于 (-gt) 零，则执行 if 代码块。这该示例中，您使用 mail（也可以使用 send mail）发送邮件。邮件标题包含所执行的脚本 ($0)、搜索的日志名称 ($1)，邮件正文是与初始搜索 (ORA-) 匹配的行。 可以使用 ORACLE_HOME、ORACLE_BASE 和 ORACLE_SID 等环境变量找到不在 Linux 环境中固定位置的资源。如果管理 Oracle 电子商务套件 11i 应用程序实例，则可以使用许多其他环境变量来定位资源。这些变量包括 APPL_TOP、TWO_TASK、CONTEXT_NAME 以及 CONTEXT_FILE 等。要查看您环境中的完整列表，执行以下命令并查看生成的文件 (myenv.txt)： env >

Myenv.txt

You can use various combinations of these environment variables as the location of the file you are searching for. For example, you can specify the alert log location as

$ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log

Based on the principles introduced in this script, you can write a larger script and plan to execute it on a regular basis, which will search for the contents of the alert log (or other files of interest) and send an email if any errors occur. You can then move the contents of the log to another file so that only the latest error messages are emailed.

Oracle Recovery Manager script. Oracle Recovery Manager (RMAN) is a utility that can be used to manage database backups and restores. Because all backup scripts written can be run by RMAN, which reduces the amount of platform-specific code, it significantly simplifies the management of multiple platforms. RMAN can be called by the underlying operating system and accept the passed script. For example, a cold (cold.sh) backup might consist of the following script:

#! / bin/bash

Rman target /

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.