- Oracle Shell Scripting
- Windows
- Powershell
- UNIX and Linux (Method 1)
- An Introduction to Linux Shell Scripting for DBAs
- Server/OS Information
- Как упростить исследование БД Oracle: «джентльменский набор» скриптов
- Скрипт «Информация о таблицах»
- Скрипт «Партиции и субпартиции»
- Скрипт «Атрибутный состав таблиц»
- Скрипт «Процедуры, функции и пакеты»
Oracle Shell Scripting
This article presents some basic techniques for creating Windows batch files and UNIX/Linux shell scripts that connect to SQL*Plus and RMAN.
Windows
To run a SQL script using SQL*Plus, place the SQL along with any SQL*Plus commands in a file and save it on your operating system. For example, save the following script in a file called «C:\emp.sql».
Next, create a batch file called «C:\get_emp.bat» containing the following command.
The resulting batch file can be run manually, by double-clicking on it, or scheduled using the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks) or the AT scheduler.
The method is very similar when using Recovery Manager (RMAN). As an example, place the following RMAN commands in a file called «C:\cmdfile.txt».
Next create a batch file called «C:\backup.bat» containing the following command.
This command can include a catalog= entry if a recovery catalog is used. Once again, resulting batch file can be run manually or scheduled.
Powershell
Powershell allows file redirection similar to UNIX/Linux shell scripting, so we can do something like the following for SQL*Plus.
The following example works for RMAN.
UNIX and Linux (Method 1)
The previous methods works equally well in UNIX and Linux environments. For example, save the following script in a file called «/u01/emp.sql».
Next, create a shell script called «/u01/get_emp.ksh» containing the following lines.
The following command makes the file executable for the file owner.
The resulting shell script can be run manually from the command line, or scheduled using CRON.
For RMAN, place the following RMAN commands in a file called «/u01/cmdfile.txt».
Next create a batch file called «/u01/backup.ksh» containing the following lines.
This command can include a catalog= entry if a recovery catalog is used. Once again, resulting shell script must be made executable using the following command.
Источник
An Introduction to Linux Shell Scripting for DBAs
by Casimir Saternos
Published November 2005
Learn some basic bash shell scripts for installing, running, and maintaining Oracle databases on Linux.
About seven years ago, Oracle released the first commercial database on Linux. Since then, Oracle, Red Hat, and Novell/SUSE have been steadily collaborating on changes to Linux kernel as they relate to database and application performance. For that reason, Oracle Database 10g for Linux includes enhancements that are closely related to the operating system. Now more than ever, DBAs need to have knowledge of and experience on this platform to best administer the systems under their watch.
There is a traditional pision of responsibilities between sysadmins and DBAs. However, in practice, the distinction is not always clear. Many IT shops employ inpiduals who address concerns at the database as well as the operating system levels. And of course the Oracle Database itself uses operating system resources and is designed to interact closely with its environment.
Furthermore, many sysadmins and DBAs find it necessary or convenient to automate tasks related to their work. 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.
One method that is used to automate such tasks is shell scripting. Shell scripts play a significant role in the Linux System from the time it is installed. Various scripts are called when the system is started up and shut down. Utilities by Oracle and other third-party vendors are invoked through shell scripts. Because they can be developed quickly, they have historically been used for prototyping applications. System Administrators have taken advantage of the functionality available through shell scripting to provide solutions that are tailored for the particular requirements and idiosyncrasies of the systems under their watch.
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. 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.
What Is a Shell Script?
A shell script is simply a text file containing a sequence of commands. When you run the file—or script—it executes the commands contained in the file. The term shell simply refers to the particular command-line user interface you use to communicate with the Linux kernel. Several different shells are available, including the C shell (csh), Korn shell (ksh), Bourne shell (sh), and Bourne-Again shell (bash). The shell itself is a command that reads lines from either a file or the terminal, interprets them, and generally executes other commands. The Bourne-Again shell incorporates features of the other shells mentioned and is the one that was used for the scripts this article demonstrates.
The first line in the script file can be used to dictate which shell will be used to run the script. This is the meaning of the first line found in all of the script examples:
Why Use Shell Scripts?
Depending on your background, you may not see any immediate value to shell scripting as it relates to the DBA’s work. If you do not have experience with UNIX or UNIX-like systems, the myriad of cryptic commands might be cause for concern. Besides, in addition to being a relational database, Oracle 10g provides a robust platform for processing data within the database as well as several methods of interacting with the OS outside of the database.
However, there are several reasons you might find yourself delving into the world of shell scripting, including the following:
- You are in a situation in which you must support already existing scripts.
- You want to automate system setup that occurs prior to the installation of the Oracle software. For instance, you could write a script to check the initial state of the OS and report any prerequisites that must be met before installation of the software. The script might also create relevant OS users and groups and set environmental variables for the users.
- A running Oracle database can be used to execute manual or scheduled tasks. However, some tasks need to be run when the database is not running. You can use a script to stop or start a database (as well as a listener or a related database process). Such an action cannot be initiated from within the database itself.
- You need a mechanism for monitoring the state of a database (i.e. if it is running and available to process queries). 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.
- You need to automate backups. Oracle Recovery Manager (RMAN) is a utility that allows you to develop backup scripts that can run on any platform. You can call Oracle Recovery Manager from a shell script and use it to perform a wide range of backup and recovery activities.
- You might have a requirement that is not specific to a single database. Perhaps you have several databases on a single machine. It may not be advisable to fulfill the requirement by using a single database, and it introduces potential security issues as well. 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.
When Not to Use Shell Scripts
Oracle Database includes functionality that extends beyond the traditional definition of an RDBMS. Like any other piece of software, it uses resources that are provided by the operating system, but it can “see” and “change” its environment to a much greater degree than other software. SQL and Oracle’s 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. Shell scripting is not the solution for every problem.
It important to recognize that many aspects of the operating system can be monitored and modified from within the database. Oracle’s 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). The location and other attributes of the files associated with the database can be determined in this manner as well. 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. 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 status of processes (v$process) and memory (v$sga, v$sgastat, and so on) can be queried as well. 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.
If you are considering scripting for a task that requires a good deal of database access, scripting is probably not your best option. Later in this article, there is a description of how to access the database by using SQL*Plus but in many cases, you are better off approaching the problem by using another language.
The charts below summarize information accessible from within the database itself:
Server/OS Information
| Server identification | Representative Query | Notes> | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Host name where the instance is running |
| Наименование колонки | Комментарий |
| SCHEMA_NAME | Наименование схемы данных (OWNER) |
| TABLE_NAME | Наименование таблицы |
| COMMENTS | Комментарий к таблице |
| HEIGHT | Количество строк в таблице (приблизительно) |
| WIDTH | Количество столбцов |
| DATETIME_COLUMNS | Столбцы с временнЫми типами данных и столбцы, исходя из наименования, предположительно являющиеся временнЫми метками (паттерны – %period%, %date%, %time%) |
| AVG_ROW_LEN | Средняя длина строки в байтах |
| PART_KEY | Столбцы по которым осуществлено партиционирование |
| SUBPART_KEY | Столбцы по которым осуществлено субпартиционирование |
Используемые системные таблицы: all_tab_columns, all_tab_comments, all_tab_statistics, all_part_key_columns, all_subpart_key_columns.
Запрос полезен для определения стратегии выгрузки данных из системы источника. Если на рассматриваемой таблице построен первичный ключ, то можно организовать выгрузку с последующим выделением «инкремента» по нему. При наличии метки времени — например, в технических полях с информацией о вставке данных или об обновлении — можно организовать выгрузку только измененных/добавленных записей за период времени. Информация о структуре партиций может пригодиться при создании аналогичной таблицы-«приемника».
Скрипт «Партиции и субпартиции»
| Наименование колонки | Комментарий |
| SCHEMA_NAME | Наименование схемы данных (OWNER) |
| TABLE_NAME | Наименование таблицы |
| PART_KEY | Столбцы по которым осуществлено партиционирование |
| PARTITION_NAME | Наименование партиции |
| PARTITION_POSITION | Номер партиции |
| PARTITION_HEIGHT | Количество строк в партиции |
| SUBPART_KEY | Столбцы по которым осуществлено субпартиционирование |
| SUBPARTITION_NAME | Наименование субпартиции |
| SUBPARTITION_POSITION | Номер субпартиции |
| SUBPARTITION_HEIGHT | Количество строк в субпартиции |
Используемые системные таблицы: all_tab_partitions, all_tab_subpartitions, all_part_key_columns, all_subpart_key_columns.
Скрипт будет полезен для получения характеристик (наименование, размеры) партиций при их непосредственном использовании в качестве источников данных.
Скрипт «Атрибутный состав таблиц»
| Наименование колонки | Комментарий |
| SCHEMA_NAME | Наименование схемы данных (OWNER) |
| TABLE_NAME | Наименование таблицы |
| COLUMN_ID | Порядковый номер столбца |
| COLUMN_NAME | Наименование столбца |
| DATA_TYPE | Тип данных |
| COLUMN_COMMENT | Комментарий к столбцу |
| SCALE | Масштаб (для типа данных NUMBER) |
| PRECISION | Точность (для типа данных NUMBER) |
| BYTE_LENGTH | Длина поля в байтах |
| ENCODING | Кодировка для текстовых типов данных (CHAR, VARCHAR2, NCHAR, NVARCHAR2) |
| CHAR_LENGTH | Максимальная длина строки для текстовых типов данных (CHAR, VARCHAR2, NCHAR, NVARCHAR2) |
| NOT_NULL | Флаг «Обязательно для заполнения» |
| IS_PRIMARY | Флаг «Входит в состав первичного ключа» |
| DEFAULT_VALUE | Значение «по умолчанию» |
| COLUMN_IMPACT | Список столбцов других таблиц, ссылающихся на столбец. |
| COLUMN_DEPEND | Список столбцов других таблиц, на которые ссылается столбец. |
| IS_CALCULATED | Значение столбца вычисляется «на лету» и не хранится в БД |
| ALGORITHM | Алгоритм, если IS_CALCULATED = ‘Y’ |
Используемые системные таблицы: all_tables, all_constraints, all_cons_columns, all_tab_columns, all_col_comments, v$nls_parameters.
Этот скрипт будет полезен для подготовки таблиц-«приемников» в хранилище данных, когда нужна подробная информация о таблице, ее взаимосвязях с другими таблицами, а также полном атрибутном составе. Через вспомогательную таблицу filter2 задается фильтрация таблиц, для которых осуществляется поиск ссылок (от и к). По умолчанию берутся таблицы из всех схем, кроме системных.
Скрипт «Процедуры, функции и пакеты»
| Наименование колонки | Комментарий |
| SCHEMA_NAME | Наименование схемы данных (OWNER) |
| NAME | Наименование процедуры/функции/пакета/заголовка пакета |
| BODY | Тело |
| TYPE | Тип (PACKAGE BODY, PACKAGE, FUNCTION, PROCEDURE) |
| WRAPPED | Флаг «Закодировано тело или нет (wrapped)» |
Используемые системные таблицы: all_source
При анализе источника может возникнуть задача по изучению потоков данных, циркулирующих в системе. Это практически невозможно сделать без кодовой базы пакетов, функций и процедур, особенно с неполной или отсутствующей документацией. Для удобства перечисленные объекты через скрипт можно представить в виде таблицы. Результат запроса с помощью консольной утилиты можно вывести в поток и перенаправить простейшим обработчиком (bash script) в файлы для дальнейшего изучения любимым редактором. Кроме того, на выходной поток можно «навесить» различные обработчики — beautifying, unwrapping и др.
Источник
