index

Previous

Table of Contents

Next

Chapter 1
Database Automation In The Real World

With the release of Oracle7 into the relational database world, Oracle Corporation changed the direction of system development based on Oracle. Once forced to rely on Oracle*Forms to include procedural logic that enforced complex business rules, developers could now enforce these rules inside stored PL/SQL objects.

This was a paradigm shift for Oracle Corporation and for systems based on Oracle databases. Code stored within the Oracle7 database enforced system logic, reducing redundant code spread over multiple applications. This has led to an era of systems development in which the use of database triggers and other stored PL/SQL objects are planned in the design phase of systems development.

Despite the importance of this paradigm shift, the use of stored PL/SQL objects is only one half of database automation. The other half of database automation, predating even prepackaged software, involves the use of scripts to accomplish a variety of tasks , such as:

   Backup and restoration

   Creating/maintaining users

   Dynamic code generation

   Testing

   Other miscellaneous purposes

This chapter provides numerous detailed examples of scripts that accomplish a variety of purposes, as well as numerous examples of stored PL/SQL objects. Familiarity with the actual tasks performed in the examples is useful, but not absolutely necessary.

Scripting

The oldest form of system automation involves the use of scripts and other mechanisms to batch jobs. Every major operating system provides some capacity to develop scripts, from the simple DOS batch programming to the potentially unlimited applications of awk, sed, and perl on Unix systems. On Oracle systems, the developer can utilize both SQL*Plus and PL/SQL in addition to any scripting languages supported by the operating system.

Within the context of an Oracle database, scripting usually follows one of several predominant types. More detailed information about the development of scripts using SQL and PL/SQL can be found in Chapter 3.

Backup And Restoration

Of all the tasks accomplished with scripts in an Oracle database, the automation of system backups (and to a lesser extent recovery of a database) is probably the most important. Most Oracle installations ( especially those IS shops that use hot backups) automate the process of performing system backups .

Listing 1.1 is a generic script for a cold backup of an Oracle database. The OS-level commands that perform the backup of files have been excluded.

Listing 1.1 A generic cold backup script for an Oracle database.

 # # Set up the environment variables. # ORACLE_SID=registrar_db; export ORACLE_SID ORACLE_HOME=/dbhost/database/oracle/v722; export ORACLE_HOME # # Shutdown the database. # svrmgrl connect internal shutdown immediate # # Backup the database control files, redo logs, and dbf files. # <OS commands to backup files here> # # Restart the database. # svrmgrl connect internal startup 

Listing 1.2 is a generic script for a hot backup of an Oracle database. Again, the OS-level commands that perform the backup of files have been excluded.

Listing 1.2 A generic hot backup script for an Oracle database.

 # # Set up the Oracle environment variables. # ORACLE_SID=registrar_db; export ORACLE_SID ORACLE_HOME=/dbhost/database/oracle/v722; export ORACLE_HOME # # Shut down the database. # svrmgrl lmode=Y connect internal # # Back up each tablespace individually. # alter tablespace SYSTEM begin backup; <OS command to backup the.dbf files containing the SYSTEM tablespace> alter tablespace SYSTEM end backup; alter tablespace ROLLBACK begin backup; <OS command to backup the .dbf files containing the rollback tablespace> alter tablespace ROLLBACK end backup; alter tablespace APPLICATION begin backup; <OS command to backup the .dbf files containing the application tablespace> alter tablespace APPLICATION end backup; alter tablespace INDEXES begin backup; <OS command to backup the .dbf files containing the application tablespace> alter tablespace INDEXES end backup; # # Turn off archive log and back up the archive log files. # archive log stop exit # # Store a list of existing archive log files for export. # <OS command to generate a list of files> svrmgrl lmode=y connect internal archive log start exit # # Make sure the archive log files also get backed up. # <OS command to backup the archive log files> <OS command to remove the archive log files that were backed up> # # Make a copy of the controlfile to be backed up. # svrmgrl lmode=y alter database backup controlfile to <path for controlfile backup> exit <OS command to backup the controlfile copy> 

Creating And Maintaining Users

Before Oracle7 introduced roles, Oracle DBAs had to grant rights on every table within a database to every user. Simply creating a new user was an arduous task, even with customized scripts that emulated the functionality that would come later with roles. Updating and maintaining grants was also a time-consuming task for DBAs. Listing 1.3 shows a sample script used to create a new user in an Oracle database.

Listing 1.3 A sample script to create a new user in an Oracle database.

 GRANT CONNECT TO '&&1' IDENTIFIED BY '&&1'; GRANT RESOURCE TO '&&1'; GRANT SELECT ON STUDENTS TO '&&1'; GRANT SELECT ON STUDENT_FINANCIAL_AID TO '&&1': GRANT SELECT ON ENROLLED_COURSES TO '&&1'; GRANT SELECT ON DEGREE_PLANS TO '&&1'; 

Obviously, this is a very simple example. In systems that contained hundreds of tables, these scripts often took hours to write and debug; executing the scripts for just one user took a significant amount of time. Furthermore, direct user grants had to be stored in the data dictionary, consuming storage space and memory and thereby using resources that can now be better used to improve performance.

An alternate method of handling this problem was to create an application that stored the privileges for each user inside one or more tables. Once the data was entered (usually via Oracle*Forms), an SQL script could generate the proper grant statements. Unfortunately, someone had to come up with a way to populate this table.

Previous

Table of Contents

Next