Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Teach Yourself Oracle 8 In 21 Days

Teach Yourself Oracle 8 In 21 Days -- Ch 6 -- Administering Data-bases and Datafiles


Teach Yourself Oracle 8 In 21 Days

Previous chapterNext chapterContents


- Day 6 -

Administering Data-bases and Datafiles

Today you will begin to look at some of the basic tasks the Oracle DBA must perform. Specifically, you will look at the database-creation procedure as well as some of the tuning parameters that must be set.



The database-creation operation is split into the tasks of the DBA and the tasks of the end user or application developer. These tasks are split based on what level they access the DBMS.

The Oracle DBA is responsible for all tasks that relate to the DBMS at the low level. Operations that involve the management of datafiles, redo log files, control files, tablespaces, extents, and segments are the responsibility of the DBA, as are the tasks of creating the tables, indexes, clusters, and views (with certain installations, some of these tasks might be performed by or in conjunction with the application development team). In any case, these responsibilities are addressed separately.

Tasks Involved in Creating a Database

Creating a database involves one Oracle DDL statement, and perhaps weeks or months of preparation to be ready for that one step. To create a database, you must know a lot about the data that will be put into the database, the data-access patterns, and the database's volume of activity. All these factors are used to determine the layout of the datafiles and redo log files. These are the responsibility of the Oracle DBA.

Under Windows NT, you must create the instance before you create the database. Because Oracle runs as a service under NT, the instance is necessary for the database-creation phase. An instance can be created, modified, or deleted through the NT Instance Manager. This should not be confused with the Enterprise Manager instance-management tool. Procedures on how to create this bootstrap instance were covered yesterday.

Creating the database actually occurs in two separate--but related--steps. The first step involves the actual database-creation command. This command creates the redo log files, the control files, and the datafiles necessary to create the SYSTEM tablespace. The SYSTEM tablespace contains the SYSTEM rollback segment, the data dictionary, stored procedures, and other structures necessary to run the Oracle instance.



The second phase involves adding tablespaces, tables, indexes, and so on that are used to store your specific data. The first phase described here is covered today; the remaining tasks necessary to finish creating your database will be described tomorrow. It is only when these additional tablespaces are added and your tables are created that your database is complete.

It is important that the DBA and end user work together in defining the database, because the physical layout and the actual data should be configured in an optimal manner. If you underconfigure the hardware or create a poor database layout, you will see a severe degradation in performance.

Tasks of the DBA

The DBA is responsible for all the low-level formatting of the database. I refer to this as formatting because that is basically what these steps do. When you format a disk, it is checked and zeroed out; likewise, when you create a tablespace and datafile, Oracle essentially checks out the disk space and lays down its internal format on the physical disk.

The DBA is responsible for creating the database, adding datafiles, and managing the control files and redo log files necessary for the proper function of the Oracle RDBMS. The DBA is also responsible for allocating these resources to the end user so that he or she can properly use them. The DBA or developer must then build tables, indexes, and clusters on these tablespaces. After the tables have been built and loaded, the user can then access this data.

Tasks of the User or Developer

It is the responsibility of the developer to relay to the DBA what the structure of the data should be and how it will be accessed. In this way, the DBA can have all of the information necessary to properly lay out the database. It is the responsibility of both the DBA and the application developer to work together to provide a stable and usable environment for the end user.

Designing the Database

Designing the database can be quite complex and time consuming, but well worth the effort. Any mistakes at this point can be very costly in terms of performance and stability of the system in the long run. A well-designed system takes into account the following factors:

Physical Database Layout

As part of the design considerations mentioned previously, the physical layout of the database is very important. You should consider several factors when designing the physical layout, including

So you gain a complete understanding of how and why the physical database design might vary based on function, let's review a few basic factors.

Database Size

The size of the database is a key factor in how the physical layout is designed. For very small databases, this might not be much of an issue, but for very large databases it can be a major issue. You must make sure that you have not only enough space for the datafiles themselves, but also for associated indexes. In some cases, you might need to have a large temporary area to copy input files to before they are loaded into the database. Oracle has a few restrictions on the size of the components of the database:

As you can see, Oracle allows you to create and maintain very large databases. You might think this is an incredible size for a database and no system will ever achieve this size. Well, I can remember when a 10MB disk drive was huge for a PC. If industry trends continue the way they've been going, I would not be surprised to see systems with 32TB tablespaces in the near future.

As you saw on Day 4, "Properly Sizing Your Database and Planning for Growth," it is not only necessary to build your system with today's requirements in mind, but also to plan for the future. Systems can increase in size at incredible rates, and you must be ready for it.

Performance

An important factor to remember when designing the physical layout of your database is the performance of the various components in the system. The load on the system caused by numerous users requesting data will generate a certain amount of disk I/O.

The disk drives that comprise the system can service only so many I/Os per second before the service time (the time it takes for an I/O to complete) starts increasing. In fact, it is recommended that for a standard 7200 RPM SCSI disk drive, you run it at only the following rates:


NOTE: With a sequential I/O, the data that is requested is either on the same track as the last data accessed or on an adjacent track.
With a random I/O, the data that is requested is on another track on the disk drive, which requires the disk arm to move, thus causing a seek. This track seek takes much more time to complete than the actual reading of the data.

Taking these factors into account, you should isolate the sequentially accessed data and spread out the randomly accessed data as much as possible. A hardware or software disk array is a good way to spread out these randomly accessed I/Os. By determining the amount of I/O traffic that will be generated, you can decide how many disk drives are required. A lack of disk drives can cause severe performance problems. In many cases, you will find that you are required to use many more disk drives for performance reasons than you would for size requirements.


TIP: The redo log files are sequentially accessed, as are the archive log files. These files should be isolated from randomly accessed files in order to increase performance.

Function

You might also find that you want to separate your database into different tablespaces based on function. That way, maintenance operations and backups can be done on a per-department basis. For example, you can put accounting and sales on different tablespaces so they can be backed up separately.

You will also find that different types of operations have different characteristics. For example, an OLTP system that has a large number of updates is very sensitive to the placement of the redo logs due to performance considerations. This type of system might also be continuously creating archive log files that need to be protected and backed up. This requires some planning.

On the other hand, a decision support system (DSS) that primarily queries might not need a high-performance redo log volume, and archiving might occur only once per day. In that case, you might want to design your database layout to favor the datafiles.

Data Protection

The primary job of the DBA is to protect the data in the system. As part of this job, you the DBA must determine how to protect that data. As you saw on Day 2, "Exploring the Oracle Architecture," every change that Oracle makes to the database is written to the redo log files and, in turn, these redo log files are archived. These redo log files and archive log files can be used, in conjunction with a recent backup, to recover the database to the point of system failure. This is, of course, assuming that the redo log files and archive log files are intact.

It is therefore necessary to protect the redo log files and archive log files from media failure. This can be accomplished either via hardware or software fault tolerance. I prefer hardware fault tolerance in the form of a RAID (redundant array of inexpensive disks) subsystem, but software fault tolerance is also very good.

There are several options available with RAID controllers; the most popular are RAID-1 and RAID-5. Each has advantages and disadvantages, as shown here:


TIP: It is a good idea to put your operating system and redo log files on separate RAID-1 volumes. This provides the best level of protection and performance.

Typically, the archive log files can reside on a RAID-5 volume because performance is not critical. If you find that you are having trouble keeping up on the archive log writes, you might need to move them to RAID-1.

Your datafiles can reside on a non-fault-tolerant disk volume if you are limited on budget and can afford to have your system down in the event of a disk failure. As long as you have a good backup, you lose no data.

Partitioning

You might also decide to adjust the physical layout of your database based on the partitioning method you have chosen. Oracle has introduced a new partitioning method with Oracle8. Various partitions can be allocated to Oracle tables based on ranges of data. Because the partitioning is actually done at the tablespace level and the tablespaces are made up of datafiles, it is important to plan your partitioning before you build your datafiles.

Because Oracle supports only range partitioning, whether you partition your data is dependent on your application and data. If you can take advantage of partitioning, you will definitely see some advantages in terms of reduced downtime and increased performance.

Creating the Instance

As you saw yesterday, before you can create the Oracle database under Windows NT or even start up the Oracle instance, you must create an instance. Follow the steps in the previous chapter to create the Oracle instance; start up the instance, and then you can create the database. Because Oracle functions as a service under NT, you cannot create a database without creating the instance.

Creating the Database

When you create a database, you are primarily creating the redo log files, the control files, and the SYSTEM tablespace. This SYSTEM tablespace is where important structures such as the data dictionary are kept. The data dictionary keeps track of all of the datafiles, the database schema, and all other pertinent database information. After you create this initial database, you will create more tablespaces and assign your schema to those tablespaces. So let's continue creating the initial database.

After the instance has been created, you can create the database. Creating the database is done either through Enterprise Manager or with the CREATE DATABASE DDL command. Although Enterprise Manager is quite convenient and easy to use, I prefer to script the creation procedure into a SQL file. By doing this, you can easily run this creation procedure over and over again and modify it for other purposes. This also provides you with a record of how this procedure was done.

Setup

There are a few initial setup steps that should be completed before you begin the actual creation process. These steps are designed to help you create the right configuration as well as to protect yourself from potential future problems. These steps involve the following:

1. Backing up any existing databases on the system

2. Creating the init.ora file

3. Starting up the Oracle instance

If you follow these steps, you should be ready to successfully create an Oracle database. Let's look at these steps.

Backing Up Existing Databases

This is purely a precautionary step. It is always a good idea to back up all your databases on a regular basis. It is also recommended that you back up your databases prior to any major system changes, such as the creation of a new database.

No matter how careful you are in preparing for the database creation, there is always some danger in making major changes to the system. Because it is possible that a mistake could affect existing control files, redo log files, or datafiles, this precaution might save you quite a bit of work.

If some unforeseen event causes data loss in an existing database, the recovery process will be facilitated by having a fresh backup. This is just a precaution, and one that is well worth the time and effort.

Creating the init.ora File

It is necessary to create a new parameter file for each new database. The parameter file, also known as the init.ora file, contains important information concerning the structure of your database. All the Oracle tuning parameters are described in Appendix B, "Oracle Tuning Parameters," but a few parameters are critical to the creation of the database:

The following parameters should also be set, based on your licensing agreement with Oracle:

After these parameters are set, you can move on to the next phase: starting up the Oracle instance.

Starting Up the Oracle Instance with NOMOUNT

Before you start up the Oracle instance, check your SID. This will indicate which database you will connect to. You should typically set your SID to the same name as in the DB_NAME parameter. When your application connects into Oracle, it uses the SID to determine which database (if there is more than one) to connect to. Depending on the application and your network, the SID might be used to connect you to a particular database on a particular system via SQL*Net.

This is similar to starting up the instance as shown yesterday, except that to create a database, the instance be must be started with the NOMOUNT option (this is because no database associated with that instance is available to mount). After the SID has been checked, you can then start the Oracle instance. This can be accomplished in two ways: by using the Oracle Instance Manager or by using Server Manager. Both methods are presented here.

Starting the Instance with the Instance Manager

The first phase is to start up the Oracle Instance Manager. In this example, you will be using the instance named DOGS that you created yesterday:

1. Log in to the Instance Manager with the INTERNAL user, the password that you gave it, and the DOGS service name. The Instance Manager starts with the status screen visible.

2. Switch to the Startup screen by clicking the Startup tab. The Startup screen is shown in Figure 6.1.

Figure 6.1.

The Startup screen.

3. When the Startup screen is visible, click the No Mount option.

4. Specify a parameter file by typing its name or by clicking the Browse button and finding the parameter file you just created (see Figure 6.2).

Figure 6.2.

Browsing for the parameter file.


5.
Click the Startup button. This starts the Oracle instance without mounting or opening a database. After the instance starts, you will move on to the next step: creating the database.


NOTE: It is possible to modify the parameter file within the Instance Manager by clicking the Initialization Parameters entry on the left side of the Instance Manager screen, shown in Figure 6.3. After you've selected the parameter you want to modify, double-click it; a change screen will be displayed (see Figure 6.4). Be sure to save your changes to a parameter file.

Figure 6.3.

Modifying initialization parameters within the Instance Manager.

Figure 6.4.

Modifying a parameter.

Starting the Instance with Server Manager

The way I prefer to build a database is by scripting it into a command file. That way, I will have a permanent record of what I have done to create the database. The first command in my script will be to start the Oracle instance in NOMOUNT mode as follows:

connect internal/oracle

startup [pfile=c:\orant\database\initORCL.ora] NOMOUNT;


NOTE: The brackets indicate an optional parameter. If the pfile parameter is not specified, c:\orant\database\initSID.ora will be used (where SID is the value of your SID environment variable).

By scripting, you can reuse this in the event you need to re-create the database or as a template for other database creations.

Creating the Database

After you have created the instance, you can move on to the next stage: creating the database itself. As with the instance, it is possible to create the database both from a graphical tool (in this case, the NT Instance Manager) or from the command line or script using the Oracle Server Manager. Here you will look at both methods. I prefer character-based creation because it can be scripted and thus re-used.

Creating the Database with the NT Instance Manager

As shown yesterday, when you create the instance with the GUI version of the Database Assistant, a database can be created for you. When you create a database with the Database Assistant, the NT bootstrap instance, database, and SYSTEM tablespace are created, and the CATALOG.SQL and CATPROC.SQL scripts are automatically executed.


NOTE: The Database Assistant is new with Oracle8 for NT. This is a nice tool that can really help you create and delete databases.

So you can have more influence over the creation of the database, I will show you the Custom configuration (see Figure 6.5).

1. As with the Typical option, the first decision you are asked to make is whether you will use advanced replication (see Figure 6.6). If you choose to add the replication option, default users and stored procedure packages will be added.

Figure 6.5.

Using the Custom option on the Oracle Database Assistant.

Figure 6.6.

You can select advanced replication support.


2.
In the next screen, you can choose the size of the database (you get an option to modify this) and can change the character set and compatability parameter, as shown in Figure 6.7.

Figure 6.7.

Choosing the relative database size.


3.
The next screen, shown in Figure 6.8, allows you to select the database name, SID, initialization parameter filename, and internal password. For this example, I am creating the dogs database with the SID name dogs.

Figure 6.8.

Specifying the database name and SID.


4.
After you set the database name and SID, the other screens will have defaults set with those names being used. In the next screen, shown in Figure 6.9, you are asked to set the control filenames (dogs is used by default) and some maximum parameters.

Figure 6.9.

Setting maximums and control files.


5.
The next screen provides five tabs that allow you to change various datafile sizes and attributes for the following tablespaces:
The SYSTEM tablespace screen is shown in Figure 6.10.

Figure 6.10.

The SYSTEM tab with SYSTEM tablespace information.


6.
The other tabs have the same or similar options for you to set the name of the tablespace, its location and size, the extents for percent increase, whether to turn on auto extent, and the minimum and maximum settings.

7. When you are satisfied with the configuration of these tablespaces, click Next. This invokes the log file creation screen, where you can set up your redo log files (see Figure 6.11).

Figure 6.11.

The redo log file parameters.


8.
The next screen, shown in Figure 6.12, sets up the checkpoint interval and archiving.

Figure 6.12.

Setting up the archive log and archive information.



NOTE: I highly recommend that you set up your database to archive. You have the option of setting up the archive destination in the screen shown in Figure 6.12.
9. The next screen, shown in Figure 6.13, lets you specify inititialization parameters that affect the SGA, including the following


TIP: For OLTP, a block size of 2,048 is usually very good. For DSS applications, a larger block size might improve performance.
10. The next screen, shown in Figure 6.14, allows you to specify where trace files for user and background processes will be written.

11. In the last screen (see Figure 6.15), you are asked whether you want to execute the commands necessary to create the database that you specified or write them to a file. If you write them to a file, you will have a permanent record of the actions taken. If you simply want to create the database, click Finish.

Figure 6.13.

Setting SGA parameter information.

Figure 6.14.

Setting the locations of trace files.

Figure 6.15.

Create the database or save the information to a batch script.


12.
After you click Finish, you are given one last chance to confirm the database creation, as shown in Figure 6.16.


Figure 6.16.

Setting the database creation in motion.


13.
Finally you'll see a confirmation screen, as shown in Figure 6.17.

Figure 6.17.

The Oracle Database Assistant confirmation screen.


14.
After you click OK, database creation begins. The progress of the process is shown in a Progress dialog.

I tend to prefer command-line database creation because it allows more flexibility and the additional feature of saving your creation scripts. This allows you to use these scripts again or as a template for new database creations.

Creating the Database with Server Manager

To create the database with Server Manager, you must type it manually or, as I prefer, use a SQL script. The database is created with the CREATE DATABASE command.

The Syntax for CREATE DATABASE

The syntax for this command is as follows:

SYNTAX:

CREATE DATABASE [[database]
[CONTROLFILE REUSE]]
LOGFILE [GROUP group_number] logfile
[, [GROUP group_number] logfile] ...
[MAXLOGFILES number]
[MAXLOGMEMBERS number]
[MAXLOGHISTORY number]
[MAXDATAFILES number]
[MAXINSTANCES number]
[ARCHIVELOG or NOARCHIVELOG]
[EXCLUSIVE]
[CHARACTER SET charset]
[NATIONAL CHARACTER SET charset]
DATAFILE file_specification [AUTOEXTEND OFF | ON 
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">;[NEXT number K | M] [MAXSIZE UNLIMITED | number K | M]
[, DATAFILE file_specification [AUTOEXTEND OFF | ON 
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">;[NEXT number K | M] [MAXSIZE UNLIMITED | number K | M]]

The various parameters and variables are

File specification is made up of the following:

The following options are available to the DATAFILE parameter:

The following options are available to the AUTOEXTEND ON parameter:

The CREATE DATABASE command might seem to be quite complex, but it is not really that difficult. It is not necessary to use all the optional parameters, but as you gain experience, you might decide to use them. An example of creating a database is shown here:

CREATE DATABASE dogs CONTROLFILE REUSE
LOGFILE
GROUP 1 ( `d:\database\log1a.dbf', `e:\database\log1b.dbf') SIZE 100K,
GROUP 2 ( `d:\database\log2a.dbf', `e:\database\log2b.dbf') SIZE 100K
DATAFILE `d:\database\data1.dbf' SIZE 10M,
`d:\database\data2.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 50M;

It is not necessary to create all the datafiles at database-creation time. In fact, if you are creating a large number of datafiles, it is more efficient to create the datafiles in parallel using ALTER TABLESPACE ADD DATAFILE.

The CREATE DATABASE command serializes its operations. So if you specify two datafiles, the second will not be created and initialized until the first one has completed. The operation of adding datafiles can, however, be accomplished in parallel. This will reduce the time necessary to create the database.

Creating the Catalogs

After the database has been created, two scripts (CATALOG.SQL and CATPROC.SQL) should be run to create the data dictionary views. These views are important to the operation of the system as well as for the DBA. These catalog scripts can be run within the Server Manager by using the @ character to indicate that you are running a SQL script, as shown here:

@D:\ORANT\RDBMS80\ADMIN\CATALOG;
...
Much data returned
...
@D:\ORANT\RDBMS80\ADMIN\CATPROC;
...
Much data returned
...

You will see the SQL script text as it is running. This process is quite time consuming and will display a very large amount of data.


NOTE: Running the CATALOG.SQL and CATPROC.SQL scripts will take a significant amount of time; don't worry if it seems like it is taking forever.

CATALOG.SQL

The Oracle SQL script CATALOG.SQL creates many of the views used by the system and by the DBA. These include the V$ tables that are referenced throughout the book. Also created by this script are the DBA_, USER_, and SYS_ views. Synonyms are also created, and many grants are done by this script. All these views, synonyms, and permissions are very important to the operation of the system.

CATPROC.SQL

The CATPROC.SQL script is also extremely important to the function of the system. This script sets up the database for the procedural option. The CATPROC.SQL script runs many other SQL scripts, including ones that set up permissions, insert stored procedures into the system, and load a number of packages into the database.

If you run the CATALOG.SQL and CATPROC.SQL scripts, your system will be configured and ready to create tables and load the database. Nonetheless, there might be other options you want to set or parameters you want to alter. These can be accomplished through the use of the ALTER DATABASE command, as shown in the next section.

Modifying the Database

Many of the tasks involved in modifying the Oracle database, tablespaces, and datafiles can be done via the Oracle Enterprise Manager tools or through the use of DDL statements via the Oracle Server Manager. Both methods are described in this section. As you will see, the Oracle Enterprise Manager simplifies the task by providing you with choices, but is somewhat limited in functionality.

Modifying the Database with Enterprise Manager

Modifying the Database with Enterprise Manager cannot be done with the current Enterprise Manager. Thankfully, the ALTER DATABASE command is quite flexible and offers several options that cannot be performed with a graphical tool. It is because of the large number of different parameters that are available with the ALTER DATABASE command that the Server Manager is still such a valuable and powerful tool.

Modifying the Database with the ALTER DATABASE Command

Modifying the database from Server Manager is accomplished via the ALTER DATABASE command. This command is used to alter various parameters and specifications on the database itself, and can be typed into Server Manager or run as a SQL script. The syntax of the ALTER DATABASE command is as follows.

ALTER DATABASE [database]
[MOUNT [STANDBY DATABASE] [EXCLUSIVE | PARALLEL]]
[CONVERT]
[OPEN [RESETLOGS | NORESETLOGS]]
[ACTIVATE STANDBY DATABASE]
[ARCHIVELOG | NOARCHIVELOG]
[RECOVER recover_parameters]
[ADD LOGFILE [THREAD number] [GROUP number] logfile
[, [GROUP number] logfile] ...]
[ADD LOGFILE MEMBER `filename' [REUSE] 
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">;[, `filename' [REUSE] ...][TO GROUP number] or
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">;[`filename' [, `filename'] ...]
[, `filename' [REUSE] [, `filename' [REUSE] ...
[TO GROUP number] or [`filename' [, `filename'] ...]]
[DROP LOGFILE [GROUP number] or [`filename'  [, `filename'] ...]
[, GROUP number] or [`filename'  [, `filename'] ...]]
[DROP LOGFILE MEMBER `filename' [, `filename'] ...]
[CLEAR [UNARCHIVED] LOGFILE
[GROUP number] or [`filename'  [, `filename'] ...]
[, GROUP number] or [`filename'  [, `filename'] ...]
[UNRECOVERABLE DATAFILE]]
[RENAME FILE `filename' [, `filename'] ... TO `filename' [, `filename'] ...
[CREATE STANDBY CONTROLFILE AS `control_file_name' [REUSE]]
[BACKUP CONTROLFILE
[TO `filename' [REUSE]] or [TO TRACE [RESETLOGS or NORESETLOGS]]
[RENAME GLOBAL NAME TO database[.domain] ...]
[RESET COMPATABILITY]
[SET [DBLOW = value] or [DBHIGH = value] or [DBMAC ON or OFF]]
[ENABLE [PUBLIC] THREAD number]
[DISABLE THREAD number]
[CREATE DATAFILE `filename' [, `filename'] ...
AS filespec [, filespec] ...]
DATAFILE `filename' [, `filename'] ...
ONLINE or OFFLINE [DROP] or RESIZE number (K or M)
or AUTOEXTEND OFF or ON [NEXT number (K or M)] [MAXSIZE UNLIMITED or number
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">; (K or M)]
or END BACKUP]

The various parameters and variables for the ALTER DATABASE command are as follows:

The various options to the ALTER DATABASE database MOUNT command are as follows:

Other optional parameters to the ALTER DATABASE command are

The options to the ALTER DATABASE database OPEN command are as follows:

Other optional parameters to the ALTER DATABASE command are

The various options to the ALTER DATABASE database ADD LOGFILE MEMBER command are as follows:

Other optional parameters to the ALTER DATABASE command include

The various options to the ALTER DATABASE database DROP LOGFILE command are as follows:

Other optional parameters to the ALTER DATABASE command are

The various options to the ALTER DATABASE database CLEAR LOGFILE command are as follows:

Other optional parameters to the ALTER DATABASE command are

The various options to the ALTER DATABASE database CLEAR LOGFILE command are as follows:


TIP: By running the ALTER DATABASE database BACKUP CONTROLFILE TO TRACE command after your database has been altered in any way, you will have a method of re-creating the control files if necessary. This is part of a good recovery plan.

Other optional parameters to the ALTER DATABASE command are

SET--The following trusted Oracle parameters are modified via the SET command SET DBLOW = value, SET DBHIGH = value, SET DBMAC ON or OFF. Trusted Oracle is not covered in this book. See the Trusted Oracle Administration Guide from Oracle for more information.

The various options to the ALTER DATABASE database DATAFILE `filename' command are as follows:

The various options to the ALTER DATABASE database DATAFILE `filename' AUTOEXTEND ON command are as follows:

Another optional parameter to the ALTER DATABASE command is

The parameters and options to the RECOVER clause are

RECOVER [AUTOMATIC] [FROM `path']
[[STANDBY] DATABASE]
[UNTIL CANCEL] or [UNTIL TIME `time'] 
" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">;or [UNTIL CHANGE number] or [USING BACKUP CONTROLFILE] ...]
[TABLESPACE tablespace [,tablespace] ....]
[DATAFILE `filename' [, `filename'] ....]
[LOGFILE `filename']
[CONTINUE [DEFAULT]]
[CANCEL]
[PARALLEL parallel_definition]

The various parameters and variables for the RECOVER option are

The various options to the ALTER DATABASE database RECOVER UNTIL ?? command are as follows:

Other optional parameters to the ALTER DATABASE database RECOVER command are

The recovery process is key to the stability of Oracle and your database. This topic is covered in much more detail on Days 16 and 17.

Let's look at a few examples of using the ALTER DATABASE command to perform regular maintenance tasks.

Changing to Use ARCHIVELOG Mode

If you are not running in ARCHIVELOG mode, you are in danger of losing data in the event of a system failure. To alter the database to run in ARCHIVELOG mode, use the following syntax:

ALTER DATABASE dogs ARCHIVELOG;

Performing a Timed Recovery

It is sometimes necessary to perform a timed recovery. If a certain SQL statement caused a system failure, you should recover until just before that statement was issued. If a SQL statement that caused data loss was inadvertently run, you can recover until just before that statement was issued. Here is an example of how to perform a timed recovery:

ALTER DATABASE dogs RECOVER UNTIL TIME `1999-07-04:15:03:00';

This statement recovers the database until 3:03 p.m. on July 4, 1999.

Open a Closed Database

Databases are often brought up and mounted but not opened for maintenance. To open a closed database, use the following syntax:

ALTER DATABASE dogs OPEN;

Backing Up a Control File

Backing up control files is an important operation. Here is an example of how to use ALTER DATABASE to back up your control files:

ALTER DATABASE dogs BACKUP CONTROLFILE TO `C:\backup\cntrlDOGS.dbf;

Backing Up a Control File to Trace

Backing up your control file to trace generates a SQL script that can be used to re-create the control file in the event of an emergency recovery. Use this syntax:

ALTER DATABASE dogs BACKUP CONTROLFILE TO TRACE;

Followup

Even after the database and datafiles have been created, your job is not over. You must watch the system carefully to make sure that you don't run out of space or other resources. As you saw on Day 4, capacity planning and sizing are not easy jobs. By anticipating and solving problems before they become critical, you will avoid costly setbacks. You must periodically monitor the system from the OS and the Oracle perspectives to avoid these types of problems.

Monitoring the Datafiles

To make sure you are not running out of space, you can use Enterprise Manager's Storage Manager utility. If you click the Datafiles icon on the left, you will see a list of datafiles, the size of each file, and how much it is used on the right. This is a quick and easy way of determining whether you are running out of space in your datafiles. You can manually check this by looking at several different system views and by adding up individual free spaces. The Oracle Storage Manager simplifies this task.

Load Balancing

It is important that you not overdrive any of the disk drives or disk arrays in your system. This can severely hurt your performance. The I/O rates at which your system is running can be monitored with the NT Performance Monitor. I will not spend much time on the Performance Monitor, but I do want to mention a few points that you should watch out for:

If you see a disk drive or disk array (a disk array looks like a big disk drive to the OS) that has many more I/Os per second per disk than the others, you might have a balance problem.


TIP: The I/Os per disk drive per second should not exceed 60-79 on the data volumes. On an array, divide the number of I/Os by the number of drives to get the I/Os per drive.
A typical disk drive should take about 20-30 milliseconds to read or write to the drive. If your seconds/read or seconds/write is much higher, you are probably overloading your disks.

Summary

Today you learned how to create a database on the server. This procedure involves creating an instance to be able to connect into Oracle and then creating the database itself. You also learned how to configure the network components to connect to this database. Finally, you learned how to modify that database as necessary.

You have seen how to accomplish all these steps with the graphical administration tools that are part of Oracle for NT and part of the Oracle Enterprise Manager. As I have recommended throughout this chapter, there are some tasks for which I prefer to employ the graphical tools, and others that I prefer to script (so I can reuse the SQL statements). You will have to determine which ones you prefer.

What's Next?

Tomorrow, you will see how to create tablespaces using the database and datafiles you created today. The tablespace is the building block upon which tables, clusters, and indexes are built. Tablespaces are also key to the new Oracle8 partitioned tables and indexes.

Q&A

Q Can I use the Oracle Enterprise Manager to create an instance?

A Unfortunately, the Oracle Enterprise Manager does not have that facility because it is necessary primarily on the NT system (since Oracle is a service). On other operating systems, it is not necessary to create an instance before creating the database.

Q What is a log-file group?

A A log-file group is a set of log files that is used to store system redo information. By having a group of log files, Oracle will automatically mirror redo information to the log files in the groups.

Q What tasks are involved in creating a database?

A Under Windows NT, the first step is to create an Oracle instance using the NT Instance Manager. The second step is to create the database itself. The third step is to create the entry in the LISTENER.ORA file.

Workshop

The workshop provides quiz questions to help you solidify your understanding of the material covered and exercises to provide you with experience in using what you've learned. Find the answers to the quiz questions in Appendix A, "Answers."

Quiz

1. How many I/Os per second per disk drive can a standard disk drive service?

2. What is an SID?

3. Is the size of a datafile permanently fixed?

4. Can you have more than one instance on a system?

5. How many databases can you create with one SID?

6. How many databases can you create on one system?

Exercises

1. Create a database using the CREATE DATABASE command.

2. Repeat Exercise 1 using three separate datafiles.


Previous chapterNext chapterContents


© Copyright, Macmillan Computer Publishing. All rights reserved.