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:

• Performance--The database that has been designed for performance from the very beginning will outperform any system that has not. Many critical performance items can only be configured in the design stage, as you will soon see.

• Backup--Often, the DBA is given only a short time to accomplish the required backup operations. By planning the data layout at the very beginning with this criterion in mind, these operations can more easily be accomplished.

• Recovery--Nobody plans for his system to crash, but it is an unfortunate fact of life that hardware and software components sometimes fail. Planning can facilitate the recovery process and can sometimes be the difference between recovering and not recovering.

• Function--The database layout has to take into account its ultimate function. Depending on what type of applications are being run and what the data looks like, there might be different design considerations.

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

• Database size--You must be able to support the amount of data you will be loading into the database.

• Performance--A physical disk drive can support only a certain number of I/Os before performance begins to suffer.

• Function--You might decide to lay out tablespaces based on their function. This allows different departments to have different backup schedules, and so on.

• Data protection--It is very important that some types of files be protected against media failure. Primarily, the redo log files and the archive log files need to be protected.

• Partitioning--Depending on what type and amount of partitioning you will be doing, the physical layout might vary.

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:

• The maximum size of a datafile is 32GB (gigabytes).

• The maximum number of datafiles per tablespace is 1,022.

• The maximum size of a tablespace is 32TB (terabytes).

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:

• Random I/O--60-70 I/Os per second per disk drive.

• Sequential I/O--100 I/Os per second per disk drive.

---

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:

• RAID-1--Also known as mirroring. The entire contents of a disk drive are duplicated on another disk drive. This is the fastest fault-tolerant method and offers the most protection. It is, however, the most costly because you must double your disk-space requirements.

• RAID-5--Also known as data guarding. In this method of fault tolerance, a distributed parity is written across all the disk drives. The system can survive the failure of one disk drive. RAID-5 is very fast for reading, but write performance is degraded. RAID-5 is typically too slow for the redo log files, which need fast write access. RAID-5 can be acceptable for datafiles and possibly for the archive log files.

---

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:

• DB_NAME--This parameter specifies the name of the database. The DB_NAME parameter is a string of eight or fewer characters. This name is typically the same as your Oracle SID (system identifier). The default database was built with DB_NAME = oracle.

• DB_DOMAIN--This parameter specifies the network domain where your server resides. This parameter, in conjunction with the DB_NAME parameter, is used to identify your database over the network. The default database was built with DB_DOMAIN = WORLD.

• CONTROL_FILE--This parameter specifies one or more control files to be used for this database. It is a very good idea to specify multiple control files, in case of disk or other failures.

• DB_BLOCK_SIZE--This parameter specifies the size of the Oracle data block. The data block is the smallest unit of space within the datafiles, or in memory. The DB_BLOCK_SIZE can make a difference in performance, depending on your application. The default size is 2,048 bytes, or 2KB. After the database is built, the block size cannot change.

• DB_BLOCK_BUFFER--This parameter specifies the number of blocks to be allocated in memory for database caching. This is very important for performance. Too few buffers causes a low cache-hit rate; too many buffers can take up too much memory and cause paging. This parameter can be changed after the database has been built.

• PROCESSES--This parameter specifies the maximum number of OS processes or threads that can be connected to Oracle. Remember that this must include five extra processes to account for the background processes.

• ROLLBACK_SEGMENT--This parameter specifies a list of rollback segments that is acquired at instance startup. These segments are in addition to the system rollback segment. This should be set after you create the rollback segments for your database.

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

• LICENSE_MAX_SESSIONS--This parameter specifies the maximum number of concurrent sessions that can connect into the Oracle instance.

• LICENSE_SESSION_WARNING--This is similar to LICENSE_MAX_SESSIONS in that it relates to the maximum number of sessions that can be connected into the instance. After LICENSE_SESSION_WARNING sessions have connected into the instance, you can continue to connect more sessions until LICENSE_MAX_SESSIONS has been reached, but you will receive a warning from Oracle that you are reaching your limit.

• LICENSE_MAX_USERS--This parameter specifies the maximum number of unique users that can be created in the database.

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:orantdatabaseinitORCL.ora] NOMOUNT;

---

NOTE: The brackets indicate an optional parameter. If the pfile parameter is not specified, c:orantdatabaseinitSID.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:

• SYSTEM

• USER

• ROLLBACK

• INDEX

• TEMPORARY

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

• Shared pool size, in bytes (SHARED_POOL_SIZE)

• Block buffers (DB_BLOCK_BUFFERS)

• Log buffer, in bytes (LOG_BUFFER)

• Processes (PROCESSES)

• Block size, in bytes (DB_BLOCK_SIZE)

---

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

• database--The name of the database to be created. This is up to eight characters long.

• CONTROLFILE REUSE--This optional parameter specifies that any existing control files be overwritten with this new information. Without this parameter, the CREATE DATABASE command would fail if the control files exist.

• LOGFILE--This parameter is followed by the log-file name. This specifies the name of the redo log file. You can specify the log-file group with the optional GROUP parameter, or a log-file group number will be assigned automatically.

• MAXLOGFILE--This parameter specifies a maximum number of log-file groups that can be created for this database.

• MAXLOGMEMBER--This parameter specifies a maximum number of log-file members in a log-file group.

• MAXLOGHISTORE--This is a parallel-server parameter that specifies a maximum number of archive log files to be used in recovery in a parallel-server environment.

• MAXDATAFILE--This parameter specifies the maximum number of files that can be added to a database before the control file automatically expands.

• MAXINSTANCES--This parameter specifies a maximum number of instances that the database can have open simultaneously.

• ARCHIVELO--This parameter specifies that the database will be run in ARCHIVELOG mode. In ARCHIVELOG mode, a redo log group must be archived before it can be reused. ARCHIVELOG mode is necessary for recovery.

• NOARCHIVELO--This parameter specifies that the database will be run in NOARCHIVELOG mode. In NOARCHIVELOG mode, the redo log groups are not archived. This is the default setting.

• EXCLUSIVE--This parameter specifies that the database is mounted in EXCLUSIVE mode after it has been created. In EXCLUSIVE mode, only one instance can mount the database.

• CHARACTER SET--This parameter specifies that the data in the database will be stored in the charset character set.

• NATIONAL CHARACTER SET--This parameter specifies that the National Character Set used to store data in the NCHAR, NCLOB, and NVARCHAR2 columns will use the charset character set.

• DATAFILE--This parameter specifies that the file identified by file_specification will be used as a datafile.

File specification is made up of the following:

• `filename' SIZE number (K or M)--The file specification is used to define the name and the initial size in K (kilobytes) or M (megabytes) of the datafile.

• [REUSE]--This parameter allows you to use the name of an existing file.

The following options are available to the DATAFILE parameter:

• AUTOEXTEND OFF--Specifies that the autoextend feature is not enabled.

• AUTOEXTEND ON--Specifies that the autoextend feature is enabled.

The following options are available to the AUTOEXTEND ON parameter:

• NEXT--Specifies the number K (kilobytes) or M (megabytes) automatically added to the datafile each time it autoextends.

• MAXSIZE UNLIMITED--Specifies that the maximum size of the extended datafile is unlimited. It continues to grow until it runs out of disk space or reaches the maximum file size.

• MAXSIZEnumber (K or M)--Specifies that the maximum size that the datafile can autoextend to is number K (kilobytes) or M (megabytes).

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:databaselog1a.dbf', `e:databaselog1b.dbf') SIZE 100K,
GROUP 2 ( `d:databaselog2a.dbf', `e:databaselog2b.dbf') SIZE 100K
DATAFILE `d:databasedata1.dbf' SIZE 10M,
`d:databasedata2.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:ORANTRDBMS80ADMINCATALOG;
...
Much data returned
...
@D:ORANTRDBMS80ADMINCATPROC;
...
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:

• database--This specifies the name of the database to be created and is a character string up to eight characters in length.

• MOUNT--This parameter is used to mount an unmounted database.

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

• MOUNT STANDBY DATABASE--This is used to mount a standby database. The standby database will be described in detail on Days 16, "Understanding Effective Backup Techniques," and 17, "Recovering the Database."

• MOUNT EXCLUSIVE--This is used to mount the database in EXCLUSIVE mode. EXCLUSIVE mode specifies that only one instance can mount the database. This is the default mode for the ALTER DATABASE MOUNT command.

• MOUNT PARALLEL--This is used to mount the database in PARALLEL mode. PARALLEL mode allows other instances to mount the database in a parallel-server environment.

Other optional parameters to the ALTER DATABASE command are

• CONVERT --This option is used to convert an Oracle7 data dictionary to the Oracle8 data dictionary.

• OPEN--This parameter opens the database for normal use. Optionally, you can specify the additional parameter RESETLOGS or NORESETLOGS.

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

• OPEN RESETLOGS--With the RESETLOG parameter set, the redo logs are essentially reset to sequence number 1. This basically discards all information in the redo logs, thus starting over. The RESETLOGS command is required after an incomplete recovery done with the RECOVER UNTIL option of media recovery or after a backup control file. A backup should be taken immediately after an ALTER DATABASE RESETLOGS command. This is described in more detail on Days 16 and 17.

• OPEN NORESETLOGS--This is the default operation of the ALTER DATABASE OPEN command, specifying not to reset the redo logs.

Other optional parameters to the ALTER DATABASE command are

• ACTIVATE STANDBY DATABASE --This parameter is used to make a standby database into the current active database. The standby database is described in detail on Days 16 and 17.

• ARCHIVELO --This specifies that this database is running in ARCHIVELOG mode. In ARCHIVELOG mode, each redo log group is archived to an archive log file before it can be reused. ARCHIVELOG mode is essential for data recovery in the event of media failure.

• NOARCHIVELO--This specifies that the database is not running in ARCHIVELOG mode. Running in NOARCHIVELOG mode is very dangerous because media recovery might not be possible. See Days 16 and 17 for more details.

• RECOVER--The recovery parameters are shown immediately after this section.

• ADD LOGFILE logfile'--This parameter is used to specify the addition of log files named `logfile' to the database. By specifying the THREAD option, you can add this log file to a specific parallel server thread; omitting the THREAD parameter will cause the redo log group to be added to your current instance. You can also specify the value of the GROUP parameter. If you omit the GROUP value, one is assigned automatically. You can specify one or more log-file groups with this parameter.

• ADD LOGFILE MEMBER filename'--This parameter adds members named `filename' to existing log-file groups. The optional parameter REUSE must be included if the file `filename' already exists. You specify the group that you are adding to in one of several different ways.

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

• TO GROUP number--This can be used if you know the log-file group identification parameter.

• TO GROUP `filename'--You can also add to the log-file group by specifying the name or names of all members of the existing log-file group.

Other optional parameters to the ALTER DATABASE command include

• DROP LOGFILE --This parameter drops all members of a log-file group. You specify the group that you are dropping in one of two ways: by specifying the GROUP or by specifying members of the group as described here.

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

• GROUP number--If you know the group identifier, you can drop the log-file group by specifying it.

• `filename'--You can add to the log-file group by specifying the name or names of all members of the existing log-file group.

Other optional parameters to the ALTER DATABASE command are

• DROP LOGFILE MEMBER `filename'--This command is used to drop a member or members of a log-file group. The member to be dropped is specified by the log-file member's filename. One or more members can be specified.

• CLEAR LOGFILE --This command is used to drop and re-create a log file. This can be used in the event of a problem with an existing log file. By using the optional UNARCHIVED qualifier, you can clear a log file that has logging information in it without having to first archive that logging information. If you use the UNARCHIVED qualifier, you will probably make your database unrecoverable in the event of media failure. You specify the log files that you are clearing in one of two ways: by specifying the GROUP or by specifying members of the group as described here.

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

• GROUP number--If you know the group identifier, you can drop the log-file group by specifying it.

• filename'--You can add to the log-file group by specifying the name or names of all members of the existing log-file group.

• UNRECOVERABLE DATAFILE--This option to CLEAR LOGFILES is used if the tablespace has a datafile that is offline. This requires that the tablespace and the datafile be dropped after the CLEAR LOGFILES operation has finished.

Other optional parameters to the ALTER DATABASE command are

• RENAME FILE `filename' TO `filename'--This command is used to rename datafiles or log files. This only changes the name in the control file, not on disk.

• CREATE STANDBY CONTROLFILE AS`control_file_name'--This command is used to create a standby control file called control_file_name. The optional REUSE qualifier allows you to specify the name of an existing file that will be reused.

• BACKUP CONTROLFILE --This command is used to create a backup of the control file. This can be accomplished in the following two ways.

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

• TO `filename'--By assigning the backup control file to a filename, the control file will be backed up to this file. If the file already exists, the optional REUSE qualifier must be used.

• TO TRACE--This optional parameter writes SQL to a trace file that can be used to re-create the control files. You can specify the qualifiers RESETLOGS or NORESETLOGS, which will add SQL to open the database with these options. The SQL statements are complete enough to start up the database, re-create the control files, and recover and open the database appropriately.

---

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

• RENAME GLOBAL NAME TO--This command allows you to rename the database name, domain name, or both.

• RESET COMPATABILITY--This command resets the compatability level of the database to an earlier version of Oracle after the instance is restarted.

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.

• ENABLE [PUBLIC] THREAD number--This parallel-server command is used to enable a thread of redo log groups identified by number. The addition of the PUBLIC qualifier allows this log file thread to be used by any instance.

• DISABLE THREADnumber--This command disables a log file thread group identified by number, making it unavailable to any instance.

• CREATE DATAFILE filename'--This parameter is used to create a datafile that was lost due to media failure and was not backed up.

• ASfilespec--This option of the CREATE DATAFILE command is used to specify the filespec specification parameters.

• DATAFILE filename'--The ALTER DATABASE database DATAFILE command has several different functions that allow you to change the state of database datafiles.

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

• ONLINE--Brings the datafile online.

• OFFLINE [DROP--Takes the datafile offline. When the database is running in NOARCHIVELOG mode, the drop command takes it offline.

• RESIZE number(K or M)--This is used to resize a datafile to number K (kilobytes) or M (megabytes).

• AUTOEXTEND OFF or ON--This command is used to alter a datafile to have autoextend either on or off. With autoextend on, the file will increase in size based on the AUTOEXTEND parameters.

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

• NEXT number (K or M)--This option specifies that the database will grow in increments of number K (kilobytes) or M (megabytes) whenever space requirements force the datafile to grow.

• MAXSIZE UNLIMITED--This parameter specifies that the maximum size of the datafile is governed only by disk space and OS datafile limitations. On NT, a datafile can grow to 32GB in size.

• MAXSIZEnumber (K or M)--This option specifies that the maximum size a datafile will grow to is number K (kilobytes) or M (megabytes).

Another optional parameter to the ALTER DATABASE command is

• END BACKUP --This option specifies that media recovery should not be done when an online backup was interrupted by an instance failure.

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

• AUTOMATIC--This qualifier specifies that the recovery process automatically figures out the names of the redo log files that it needs to apply in order to perform media recovery.

• FROM path'--This qualifier allows you to specify the location of archive log files. This is useful because you do not always keep the archive log files in the directory where they were originally generated.

• STANDBY--This recovers the standby database.

• DATABASE--This is the default option. It indicates that the database should be recovered.

• UNTIL ?--The UNTIL parameters are very important to the recovery of the database if you are recovering from a software or operator problem. These parameters allow you to recover up until a specific point.

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

• UNTIL CANCEL--The database will be recovered until you submit an ALTER DATABASE database RECOVER CANCEL command.

• UNTIL TIME time'--This command performs a time-based recovery. It recovers all transactions that have finished until `time'. The qualifier is given in the form `YYYY-MM-DD:HH24:MI:SS'. This can be quite useful if you know when the suspected SQL statement that caused the failure occurred.

• UNTIL CHANGEnumber--This performs a recovery up until the last transaction before the system change number.

Other optional parameters to the ALTER DATABASE database RECOVER command are

• USING BACKUP CONTROLFILE--This specifies that the recovery should be done using a backup control file.

• TABLESPACEtablespace--This performs recovery only on the specified tablespace(s).

• DATAFILE filename'--This performs recovery only on the specified datafile.

• LOGFILE filename'--This performs recovery using the specified log file.

• CONTINUE [DEFAULT--This continues recovery after it has been interrupted. CONTINUE DEFAULT is similar, but uses Oracle-generated default values.

• CANCEL--This cancels the UNTIL CANCEL-based recovery.

• PARALLEL (DEGREEnumber)--This specifies the degree of parallelism to use during the recovery process. The number of parallel processes is determined by the value of number.

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:backupcntrlDOGS.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:

• Use diskperf--Turn on diskperf by using the NT command diskperf -y. By turning on diskperf, you will see much more information about your disk I/O rates when you run perfmon.

• Monitor I/O--Use perfmon to look at PhysicalDisk. Of great importance is the reads and writes per second (throughput) and the seconds/read and seconds/write (latency).

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.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.