Chapter 40:The Hitchhiker’s Guide to Database Administration821is actively used by the pot

Chapter 40: The Hitchhiker’s Guide to Database Administration 821

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 40

Blind Folio 40:821

is actively used by the database is backed up. These files provide a complete image of the

database as it existed at the moment it was shut down.

NOTE

You should not rely on an offline backup performed following a

shutdown abort, since it may be inconsistent. If you must perform

a shutdown abort, you should restart the database and perform a

normal shutdown or a shutdown immediate or a shutdown

transactional prior to beginning your offline backup.

The following files should be backed up during cold backups:

■ All datafiles

■ All control files

■ All online redo logs

You may optionally choose to back up the database initialization parameter file, particularly

if the backup will serve as the basis for a disaster recovery process.

Having all of these files backed up while the database is closed provides a complete image

of the database as it existed at the time it was closed. The full set of these files could be retrieved

from the backups at a later date and the database would be able to function. It is not valid to

perform a file system backup of the database while it is open unless an online backup is being

performed (as discussed later in this chapter).

Ideally, all of the datafiles are located in directories at the same level on each device. For

example, all database files may be stored in an instance-specific subdirectory under an /oracle

directory for each device (such as /db01/oracle/MYDB). Directories such as these should contain

all of the datafiles, redo log files, and control files for a database. The only file you may optionally

add to the offline backup that will not be in this location is the production initialization parameter

file, which should be in either the /app/oracle/admin/INSTANCE_NAME/pfile subdirectory under the

Oracle software base directory or the /database directory under the Oracle software home directory.

If you use the directory structure in the prior example, your backup commands are greatly

simplified, since you will be able to use wildcards in the filenames. After shutting down the

database, back up the files to the backup destination area (either a tape or a separate disk area).

NOTE

If necessary, you can also back up the init.ora and config.ora files at

the same time.

Since offline backups involve changes to the database’s availability, they are usually

scheduled to occur at night.

Offline backups are very reliable. To reduce their impact on the database’s availability,

you may use online backups. As described in the following section, online backups use Oracle’s

ARCHIVELOG mode to allow consistent file system backups during database usage.

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:39 PM

Color profile: Generic CMYK printer profile

Composite Default screen

822 Part VI: Hitchhiker’s Guides

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 40

Blind Folio 40:822

Online Backups

You can use online backups for any database that is running in ARCHIVELOG mode. In this mode,

the online redo logs are archived, creating a full log of all transactions within the database.

Oracle writes to the online redo log files in a cyclical fashion; after filling the first log file, it

begins writing to the second log until that one fills, and then begins writing to the third. Once the

last online redo log file is filled, the LGWR (Log Writer) background process begins to overwrite

the contents of the first redo log file.

When Oracle is run in ARCHIVELOG mode, the ARC0-ARC9 (Archiver) background processes

make a copy of each redo log file before overwriting it. These archived redo log files are usually

written to a disk device. The archived redo log files may also be written directly to a tape device,

but this tends to be very operator-intensive.

You can perform file system backups of a database while that database is open, provided the

database is running in ARCHIVELOG mode. An online backup involves setting each tablespace

into a backup state, backing up its datafiles, and then restoring the tablespace to its normal state.

NOTE

When using the Oracle-supplied RMAN utility, you do not have to

place each tablespace into a backup state. The utility will put the

tablespace into and take it out of the backup state automatically.

The database can be fully recovered from an online backup, and can, via the archived

redo logs, be rolled forward to any point in time. When the database is then opened, any

committed transactions that were in the database at that time will have been restored and

any uncommitted transactions will have been rolled back.

While the database is open, the following files are backed up:

■ All datafiles

■ All archived redo log files

■ One control file, via the alter database command

Online backup procedures are very powerful for two reasons. First, they provide full

point-in-time recovery. Databases that are not running in ARCHIVELOG mode can only be

recovered to the point in time when the backup occurred. Second, they allow the database

to remain open during the file system backup. Thus, even databases that cannot be shut

down due to user requirements can still have file system backups.

Getting Started

To make use of the ARCHIVELOG capability, the database must first be placed in ARCHIVELOG

mode. The following listing shows the steps needed to place a database in ARCHIVELOG

mode. Run SQLPLUS and mount the database (providing its name in place of “mydb” in these

examples), then alter it as shown here:

SQL> connect system/manager as sysdba

SQL> startup mount mydb;

SQL> alter database archivelog;

SQL> alter database open;

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:39 PM

Color profile: Generic CMYK printer profile

Composite Default screen

Chapter 40: The Hitchhiker’s Guide to Database Administration 823

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 40

Blind Folio 40:823

The following command will display the current ARCHIVELOG status of the database from

within SQLPLUS:

archive log list

NOTE

To see the currently active online redo log and its sequence number,

query the V$LOG dynamic view.

To change a database back to NOARCHIVELOG mode, use the following set of commands

after shutting down the database:

SQL> connect system/manager as sysdba

SQL> startup mount mydb;

SQL> alter database noarchivelog;

SQL> alter database open;

A database that has been placed in ARCHIVELOG mode will remain in that mode until it is

placed in NOARCHIVELOG mode. The location of the archived redo log files is determined by

the settings in the database’s parameter file. The parameters to note in Oracle9i are as follows

(with sample values):

log_archive_dest_1 = /db01/oracle/arch/CC1/arch

log_archive_dest_state_1 = ENABLE

log_archive_start = TRUE

log_archive_format = arch%s.arc

In this example, the archived redo log files are being written to the directory

/db01/oracle/arch/CC1. The archived redo log files will all begin with the letters “arch,”

followed by a sequence number. For example, the archived redo log file directory may

contain the following files:

arch_170.arc

arch_171.arc

arch_172.arc

Each of these files contains the data from a single online redo log. They are numbered

sequentially, in the order in which they were created. The size of the archived redo log files

varies, but does not exceed the size of the online redo log files.

If the destination directory of the archived redo log files runs out of space, then ARCH will

stop processing the online redo log data and the database will temporarily hang. This situation

can be resolved by adding more space to the archived redo log file destination disk or by backing

up the archived redo log files and then removing them from this directory.

NOTE

Never delete archived redo log files until you have backed them up

and verified that you can restore them successfully.

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:39 PM

Color profile: Generic CMYK printer profile

Composite Default screen

824 Part VI: Hitchhiker’s Guides

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 40

Blind Folio 40:824

Although the initialization parameter LOG_ARCHIVE_START parameter may be set to TRUE,

the database will not be in ARCHIVELOG mode unless you have executed the alter database

archivelog command shown earlier in this section. Once the database is in ARCHIVELOG mode,

it will remain in that mode through subsequent database shutdowns and startups until you

explicitly place it in NOARCHIVELOG mode via the alter database noarchivelog command.

Performing Online Database Backups

Once a database is running in ARCHIVELOG mode, you can back it up while it is open and

available to users. This capability allows round-the-clock database availability to be achieved

while still guaranteeing the recoverability of the database.

Although online backups can be performed during normal working hours, they should be

scheduled for the times of the least user activity for several reasons. First, the online backups will

use operating system commands to back up the physical files, and these commands will use the

available I/O resources in the system (impacting the system performance for interactive users).

Second, while the tablespaces are being backed up, the manner in which transactions are written

to the archived redo log files changes. When you put a tablespace in “backup” mode, the DBWR

process writes all of the blocks in the buffer cache that belong to any file that is part of the tablespace

back to disk. When the blocks are read back into memory and then changed, they will be copied

to the log buffer the first time that a change is made to them. As long as they stay in the buffer

cache, they will not be recopied to the online redo log file. This will use a great deal more space

in the archived redo log file destination directory.

The command file for a hot backup has three parts:

1. A tablespace-by-tablespace backup of the datafiles, which in turn consists of

a. Setting the tablespace into backup state

b. Backing up the tablespace’s datafiles

c. Restoring the tablespace to its normal state

2. Backup of the archived redo log files, which consists of

a. Recording which files are in the archived redo log destination directory

b. Backing up the archived redo log files, then (optionally) deleting or compressing them

3. Backup of the control file via the alter database backup controlfile command.

NOTE

The online backup process is automated via the RMAN utility.

You should create a script to perform the backups. The script should run at the operating

system level, with SQLPLUS commands executed for Steps 1a, 1c, and 3.

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:40 PM

Color profile: Generic CMYK printer profile

Composite Default screen

Chapter 40: The Hitchhiker’s Guide to Database Administration 825

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 40

Blind Folio 40:825

When the datafiles are being backed up, you may back them up directly to tape or to disk.

If you have enough disk space available, choose the latter option, since it will greatly reduce the

time necessary for the backup procedures to complete.

Recovery Manager

Beginning in Oracle8.0, a Recovery Manager toolset called RMAN has been supplied to enable

you to back up and recover your databases in an automated manner using either a command-line

mode or the Recovery Manager from within the Oracle Enterprise Manager. You can use either

approach to back up, restore, and recover database files.

Recovery Manager keeps track of backups either through a Recovery Catalog or by placing

the required information into the control file for the database being backed up. Recovery

Manager adds new backup capabilities that are unavailable in the other Oracle backup utilities.

There are four components within the Recovery Manager: the RMAN executable, one or more

target databases, the Recovery catalog database, and the Media management software. The only

components that you must have are the RMAN executable and a target database. Since RMAN

automatically stores its metadata in the target database’s control file, you do not have to have a

recovery catalog.

The most significant new capability provided via Recovery Manager is the ability to perform

incremental physical backups of datafiles. During a full (called a level 0) datafile backup, all of

the blocks ever used in the datafile are backed up. During a cumulative (level 1) datafile backup,

all of the blocks used since the last full datafile backup are backed up. An incremental (level 2)

datafile backup backs up only those blocks that have changed since the most recent cumulative

or full backup. You can define the levels used for incremental backups.

The ability to perform incremental and cumulative backups of datafiles may greatly improve

the performance of backups. The greatest performance improvements will be realized by very

large databases in which only a small subset of a large tablespace changes. Using the traditional

backup methods, you would need to back up all of the datafiles in the tablespace. Using

Recovery Manager, you only back up the blocks that have changed since the last backup.

During database recovery using Recovery Manager, you need to know which files are current,

which are restored, and the backup method you plan to use. If you use the recover catalog,

Recovery Manager stores its catalog of information in an Oracle database—and you need to back

up that database or else you may lose your entire backup and recovery catalog of information.

Recovery Manager is only used by DBAs, who need to make the decisions regarding the

Recovery Manager architecture for your environment (for example, deciding on the location of

the recovery catalog). You should work with your DBA to understand the recovery options in

use and their implications for database availability and recovery time.

Performing a Backup with Oracle Enterprise Manager (OEM)

To perform any level backup using the OEM tool, connect to the OEM Server Manager console,

select the appropriate database and right-click to bring up the database options. From the

database options, select the Backup option from the Backup Manager menu. The Backup Wizard

will activate.

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:40 PM

Color profile: Generic CMYK printer profile

Composite Default screen

To perform a backup operation, the target database must be running and available. After

the initial Welcome screen, you are prompted to select a Strategy choice. You can select a

predefined backup strategy or customize your own backup strategy. OEM displays the Backup

Frequency options screen with three choices:

1. A Decision Support System (DSS) with a backup frequency of once a week

2. A moderately updated system (OLTP) that is not very large with a backup frequency

of every day

3. A frequently updated, medium to large database with a backup frequency of full

backups weekly and incremental backups nightly

The default option is a DSS system backed up once a week on Sunday. Once you have

selected a strategy, you will be prompted for the time to execute the backup, and the databases

to back up. To perform an immediate backup, you can choose the Customize option from the

initial Strategy screen.

Where to Go from Here

In this chapter, you’ve seen a high-level overview of the topics that production DBAs deal with

every day. In addition to the topics discussed here, DBAs monitor databases, tune databases, install

software, maintain database connectivity, and many other tasks. If you are interested in learning

more about those tasks, see your Oracle documentation set or the Oracle9i DBA Handbook

(Oracle Press, 2001). The more developers understand about database administration, the more

likely they are to build applications that take advantage of the database’s inherent capabilities.

826 Part VI: Hitchhiker’s Guides

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 40

Blind Folio 40:826

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:40 PM

Color profile: Generic CMYK printer profile

Composite Default screen

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 41

Blind Folio 41:827

CHAPTER

41

The Hitchhiker’s Guide

to XML in Oracle

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:41 PM

Color profile: Generic CMYK printer profile

Composite Default screen

X ML (eXtensible Markup Language) is a standardized syntax for describing hierarchical

data. Rather than being a programming language, XML is a universal format for

structured documents and data. Its tag-based syntax will be familiar to those

familiar with HTML (HyperText Markup Language) and gives it the flexibility to

handle complex data. Whereas HTML tells a Web browser how to present data,

XML tells applications what the data means.

XML is well suited to solving data interchange problems among heterogeneous systems;

database-resident data is easily accessed, converted, and stored. In this chapter, you will see an

overview of the XML access methods available in Oracle9i. XML and its toolsets are evolving;

see http://www.w3.org for the most current information.

NOTE

Thanks to Mike Holder of TUSC for his contributions to this chapter.

Document Type Definitions,

Elements, and Attributes

In XML, application-specific tags or “elements” wrap around the data they describe. The

syntax for these tags is defined in a special kind of XML document called a Document Type

Definition (DTD). The DTD defines the structure for a valid XML document. The structure is

strictly hierarchical.

Each XML document is an instantiation of an “infoset”—the abstract data model consisting

of a document and its information items. Information items are mostly elements, attributes, and

content. For example, an XML infoset may contain the following:

<book>

<title>MY LEDGER</title>

<chapter num="1">

<title>Beginning</title>

<text>&chapter1;</text>

</chapter>

</book>

In this example, the tags <book> and </book> define the starting and ending points of the

infoset. The XML document describes a book named “MY LEDGER”. That book has chapters,

and the first chapter’s title is “Beginning”. The text of the chapter is not stored inside the XML

document; instead, a pointer is created to an external file.

In addition to being a tag in the file, “title” is an element. It has content, in this example,

“MY LEDGER”. As shown in this example, you can set the content of elements by providing

values between tags (for the title) or within the tag (as in the chapter number setting). Elements

can contain content, child elements, or both, or they may be empty. For data interchange, you

should avoid creating elements that contain both content and child elements. To create an

empty element, its tag would be in the format:

<name/>

828 Part VI: Hitchhiker’s Guides

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 41

Blind Folio 41:828

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:41 PM

Color profile: Generic CMYK printer profile

Composite Default screen

NOTE

Element names are case-sensitive.

Each element has exactly one parent, and a parent can have any number of children. The

strict hierarchical nature of XML means that if an element starts within another element (such as

“chapter” within “book”), its ending tag must precede the ending tag for the parent element.

As shown in the book example, elements have attributes. For example, the chapter element

has an attribute named num. The attribute value is enclosed in double quotes:

<chapter num="1">

NOTE

Like element names, attribute names are case-sensitive.

In general, applications use element attributes for specific items (such as the chapter number)

and element content for the bulk of the data (in this case, the chapter text).

An XML document is said to be well-formed if the following conditions are met:

■ Every start tag has a matching end tag.

■ Elements do not overlap.

■ There is one root element.

■ Attribute values are always within quotes.

■ An element cannot have two attributes within the same name.

■ Comments and processing instructions do not appear inside tags.

■ There are no unescaped < or & signs in an element’s or attribute’s character data.

These are the basic syntax rules. If an XML document conforms to these rules, it may still

not be valid. To be valid, the XML document must conform to the rules of a Document Type

Definition (DTD). The DTD is a formal way of describing what elements and entities may appear

in an XML document, and what each element’s contents and attributes are.

For example, consider the following XML document.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE CustomerList SYSTEM "Example1.dtd">

<CustomerList>

<Customer preferredCustomer="">

<ID>12345</ID>

<Address>

<Name>TUSC</Name>

<Street>377 E BUTTERFIELD RD</Street>

<City>LOMBARD</City>

<State>IL</State>

<Country>USA</Country>

Chapter 41: The Hitchhiker’s Guide to XML in Oracle 829

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 41

Blind Folio 41:829

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:42 PM

Color profile: Generic CMYK printer profile

Composite Default screen

<ZIP_CODE>60148</ZIP_CODE>

<PHONE>630-960-2909</PHONE>

</Address>

<CreditRating>EXCELLENT</CreditRating>

<SalesRepID></SalesRepID>

<RegionID>5</RegionID>

<Comments>THIS IS A TEST XML DOCUMENT</Comments>

<ShippingMethod>M</ShippingMethod>

</Customer>

</CustomerList>

The first line is an XML declaration, which starts with “<?” and ends with “?>“:

<?xml version="1.0" encoding="UTF-8"?>

The version attribute should be set to 1.0 for now. The encoding attribute is optional; its default

value is UTF-8. You can also set a third attribute, standalone. In this case, the XML document is not

standalone; it has a related DTD, as specified in the second line:

<!DOCTYPE CustomerList SYSTEM "Example1.dtd">

The related DTD file Example1.dtd for this example is shown in the following listing:

<?xml version="1.0" encoding="UTF-8"?>

<!ELEMENT CustomerList (Customer*)>

<!ELEMENT Customer (ID, Address, CreditRating*, SalesRepID*,

RegionID, Comments*, ShippingMethod)>

<!ATTLIST Customer preferredCustomer CDATA #IMPLIED>

<!ELEMENT ID (#PCDATA)>

<!ELEMENT Address (Name, Street*, City, County,

State, Country, ZIP_CODE, PHONE)>

<!ELEMENT Name (#PCDATA)>

<!ELEMENT Street (#PCDATA)>

<!ELEMENT City (#PCDATA)>

<!ELEMENT County (#PCDATA)>

<!ELEMENT State (#PCDATA)>

<!ELEMENT Country (#PCDATA)>

<!ELEMENT ZIP_CODE (#PCDATA)>

<!ELEMENT PHONE (#PCDATA)>

<!ELEMENT CreditRating (#PCDATA)>

<!ELEMENT SalesRepID (#PCDATA)>

<!ELEMENT RegionID (#PCDATA)>

<!ELEMENT Comments (#PCDATA)>

<!ELEMENT ShippingMethod (#PCDATA)>

The DTD is analogous to the control file specifications for external tables. It tells applications

what they will find in the XML document—the element names and the hierarchy of elements. An

XML document can have only one DTD.

The syntax for the element declarations is

<!ELEMENT element_name (content_model)>

830 Part VI: Hitchhiker’s Guides

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 41

Blind Folio 41:830

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:42 PM

Color profile: Generic CMYK printer profile

Composite Default screen

where content_model specifies what children an element can or must have and the order they

are in. The simplest content model is #PCDATA, which says that the element can contain only

parsed character data. For example:

<!ELEMENT Name (#PCDATA)>

In this example, the CustomerList element contains zero or more “Customer” elements, as

indicated by the * suffix:

<!ELEMENT CustomerList (Customer*)>

The allowable suffixes are

* Permits zero or more occurrences

+ Permits one or more occurrences

? Permits zero or one occurrences

As shown in the Address element specification, you can specify a sequence of multiple

elements by separating them with commas:

<!ELEMENT Address (Name, Street*, City, County,

State, Country, ZIP_CODE, PHONE)>

You can provide multiple options within the specification, separated by the vertical bar (|)

character. For example, a point on a two-dimensional graph may be specified via its horizontal

and vertical measurements or its distance and angle from the center:

<!ELEMENT Point ((x,y) | (distance, angle))>

For real-world applications, DTDs can quickly become very complex. You can have attribute

lists, external references, entity attributes, IDs, conditional inclusions, and other structures within

your DTD. Refer to XML documentation for a thorough review of DTD structures and options.

NOTE

Before creating a DTD for a standard business operation, see

if one is already available for your purpose. Web sites such as

http://www.xml.org/xml/registry.jsp provide many examples of DTDs.

These registries provide a valuable resource for the documentation of

industry standard DTDs and their components.

XML Schema

The XML Schema specification is an alternative to DTDs. It includes data typing, which is

particularly helpful when working with relational databases. Whereas DTDs are not extensible

(they’re just control files), XML Schema is extensible in the following ways:

■ Parts of schemas can be reused in other schemas.

■ Complex structures can be reused in other schemas.

Chapter 41: The Hitchhiker’s Guide to XML in Oracle 831

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 41

Blind Folio 41:831

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:43 PM

Color profile: Generic CMYK printer profile

Composite Default screen