Today you will look at the basics. Here you will see some of the objects that must be set up by the DBA (including the Oracle DBA login account, the DBA roles, and the Oracle SID) as well as some of the tasks that the DBA must perform, including

• Setting up Enterprise Manager

• Using Enterprise Manager

• Using Server Manager

• Starting up and shutting down the Oracle instance

All these are basic functions that need to be covered before you move on to topics such as creating a database. This day lays a foundation for many of the days to come. It is important that you completely understand these concepts.

The Oracle DBA

On Day 1, "Starting Out with Oracle," you were presented with a list of the duties and responsibilities of the Oracle DBA. Let's refresh your memory with some of the key duties and responsibilities:

• Installing and upgrading Oracle products

• Installing and maintaining your company's applications

• Creating databases, tablespaces, tables, views, and indexes to the specification of the application developers

• Creating user accounts and monitoring system security

• Monitoring space used in the database and planning for future growth

• Monitoring system performance and making changes as necessary to maintain the required performance levels

• Maintaining the integrity of the data in the database

• Planning and implementing a sound backup and recovery strategy

• All these duties are part of being an Oracle DBA.

The DBA Account

To accomplish these tasks, the DBA must be given special privileges. These privileges allow the DBA to run commands that other Oracle users are not allowed to perform. These privileges are maintained within Oracle itself. As part of the installation of the Oracle RDBMS, several accounts are created with these special privileges. These accounts and their privileges are described here.

INTERNAL

The INTERNAL account is provided mainly for backward compatibility with earlier versions of Oracle, but is still used for key functions such as starting up and shutting down the instance. The INTERNAL account appears as user SYS if you look at the connected sessions, but the INTERNAL account has additional key features: It can start up or shut down the instance. The INTERNAL account is available even when a database has not been created and when no instances are started up.

SYS

The SYS account is automatically created whenever a database is created. This account is used primarily to administer the data dictionary. This account is granted the DBA role, as well as CONNECT and RESOURCE roles.

SYSTEM

The SYSTEM account is also automatically created whenever a database is created. This account is used primarily to create tables and views important to the operation of the RDBMS. This account has been granted the DBA role.

Administrative Users

I recommend that you create individual user accounts and grant the DBA role to those users who will be acting as the DBA. In this way, fewer people access the same account, thus avoiding confusion. Also, if auditing is enabled, there is a record of who made these system changes.

---

TIP: Avoid using the default administrative accounts. If DBAs are authorized with the proper roles, they can perform the tasks they need to do and maintain their individual accounts. This allows you to determine which DBA or DBAs modified the system and who is currently active on it.

---

The DBA Roles

Several roles are available and are assigned to the DBAs. As you will see on Day 10, "Administering User Accounts," these roles are sets of privileges assigned to a particular Oracle role. Each role can then be assigned to a user, thus giving that user all the privileges needed for that particular task. The use of roles is covered in detail on Day 10, but it is appropriate to go over the roles and privileges assigned to the Oracle DBA here.

DBA

The DBA role consists of most of the other Oracle roles and privileges. By assigning the DBA role to a user, there is virtually no task that user cannot do. This role should be assigned to trusted users who are active DBAs for this system.

OSOPER

The OSOPER role is one of two special operating system roles. These roles are assigned to special accounts that need OS authentication. It is necessary to have OS authentication for some accounts because Oracle authentication can be done only when the database is open. If the database is shut down, Oracle cannot validate the user permissions.

The OSOPER role allows the user to perform the following operations:

• STARTUP and SHUTDOWN

• ALTER DATABASE MOUNT

• ALTER DATABASE OPEN

• ALTER DATABASE BACKUP

• ALTER DATABASE RECOVER

• ALTER DATABASE ARCHIVE LO

After the database is up and running, other users can be authenticated through Oracle security. These operations require a special authentication method because the database is not available.

OSDBA

The OSDBA role includes the permissions granted to the OSOPER role with some additional permissions. These additional permissions include the CREATE DATABASE command and all system privileges with the ADMIN OPTION. The ADMIN OPTION allows the user to grant these permissions to other roles or users. Without the ADMIN OPTION, you cannot propagate these permissions and roles.

Administrator Authentication

Authenticating the user can be done either through OS authentication using OS accounts and groups or through the use of Oracle password files. Which of these is right for your installation is up to you. These methods are covered in detail on Day 10; until then, all examples are done using Oracle password files.

The SID

As you learned on Day 2, "Exploring the Oracle Architecture," an Oracle instance is an Oracle database, the Oracle processes or threads, and the memory it uses. The instance is the logical term that refers to the components necessary to access the data in the database.

Each Oracle instance is identified by a SID (system identifier), which uniquely identifies this instance and is used by the Oracle utilities and networking components to connect you to the correct instance.

A SID is up to four alphanumeric characters in length and is required in order to connect to an Oracle instance. The SID is set by the ORACLE_SID environment variable. From the NT command line, you can set the SID with the following syntax:

Set ORACLE_SID=ORCL

The default SID value is set in the NT Registry. If you do not set the SID using the environment variable, the Registry entry is used. The TNSNAMES.ORA file also resolves the SID with a service name. As you will see tomorrow, when you create a network entry using a utility such as the Oracle Network Configuration wizard, you will be prompted for a service name that you pick, a network type and address, and an Oracle SID. This service name is used to resolve both the network and the SID. Even the Oracle service name includes the SID as an identifier.

You will be seeing more of the use of the Oracle SID in the next few days; for now, it is enough to think of the SID as a unique identifier and a way to connect to an Oracle instance.

The Oracle Enterprise Manager

The Oracle Enterprise Manager is a new tool from Oracle that allows the DBA to graphically administer one or more Oracle instances. By allowing many operations to be performed graphically, the presentation of data can be simplified and more meaningful.

Enterprise Manager allows the administrator to manage one or more Oracle instances either locally or via the network. Enterprise Manager consists of two main components: the graphical console and the intelligent agents.

The Enterprise Manager console is the graphical tool that allows you to graphically administer the Oracle instances. This console communicates to the various systems it administers via the intelligent agents that run on these systems. These intelligent agents allow the console to communicate with the instances. The agents use the SNMP (Simple Network Management Protocol) to take requests from the console and communicate those requests to the Oracle system running on these systems.

Enterprise Manager allows the DBA to perform the following tasks from a central location:

• Tune and administer one or more Oracle databases.

• Distribute software to both clients and servers.

• Monitor events from multiple instances.

• Perform backup and recovery operations from a single location.

• Perform standard DBA tasks such as user administration.

The Oracle Enterprise Manager is a very powerful and flexible tool that can help in many of your daily DBA duties.

---

NOTE: In many cases I prefer to graphically administer the Oracle system, but in some cases, I still prefer the character-based commands that can be run via the Oracle Server Manager. Throughout this book, both the graphical and character-based administrative methods are shown. I will point out where I feel one tool is more appropriate than another and why, but it is your preference that is important. You will have to decide which tool or set of tools is right for you.

---

In this section, you will learn how to configure Enterprise Manager and how to invoke its major functions. The individual tools that comprise Enterprise Manager, such as the Instance Manager, the Storage Manager, the Schema Manager, and so on, are presented separately in the lesson where most appropriate.

Configuring Enterprise Manager

The Oracle Enterprise Manager is installed as part of the Oracle server installation process or can be installed as part of the administrator client installation. After Enterprise Manager is installed, you can connect to it by using the NT toolbar and selecting Enterprise Manager from Enterprise Manager program group.

If this is the first time you've invoked Enterprise Manager, you will probably see a screen indicating that the repositories for Enterprise Manager and Software Manager are not installed (see Figure 5.1).

Figure 5.1.

Enterprise Manager and Software Manager repositories are not installed.

In this case, click OK and allow Enterprise Manager to create the repositories. This operation will take some time. During this period you will see indications that the repositories are being created, as shown in Figure 5.2.

Figure 5.2.

The Oracle Repository Manager screen.

When the creation operation is complete, Enterprise Manager will start; you will see the four default panes shown in Figure 5.3.

Figure 5.3.

The Enterprise Manager.

The four panes displayed are

• The Navigator pane--This pane provides a tree-type listing of the various objects available to Enterprise Manager. Through this list, you can launch various administrative operations (described later today).

• The Map pane--Though blank at startup, the Map pane allows you to create a geographical representation of the systems to be administered. This graphical display allows you to drill down into various sites and administer systems worldwide.

• The Job pane--This pane allows you to view and administer various jobs that are running in the system. Through this pane, you can schedule jobs to run on various nodes in the system at various times. This provides you with a way of scheduling routine operations from a single console.

The Event pane--This pane is used to view system events that occur on any node that you are administering from this console. Events are occurrences that trigger some kind of action. This action can be a simple alert or can be some type of action.

---

NOTE: When Enterprise Manager is invoked, the Administrator toolbar is also invoked (as seen in the center of the Enterprise Manager). This toolbar allows quick access to the Enterprise Manager utilities. Some administrators like to move the toolbar or remove it altogether. This is up to you. I like to use it on occasion, but usually I remove it.

---

Using the Enterprise Manager

The Enterprise Manager is distinguished from Enterprise Manager applications in this book in that the applications are presented in the section that applies to that application's function. For example, the Schema Manager is covered in the chapter that covers the Oracle schema, the Backup Manager is covered in the chapters covering backup and recovery, and so on. Today's focus is on configuring Enterprise Manager and using the functions associated with the Navigator, Map, Job, and Event panes.

General

A few general setup parameters can be modified with Enterprise Manager. These pertain primarily to how Enterprise Manager looks and acts.

The View Menu

The View drop-down menu can be used to modify the display. Select the View menu as shown in Figure 5.4.

Figure 5.4.

The View menu.

From here you can uncheck the various panes that you do not want to view. For example, if you unselect the Show Map Pane button, the Map pane will be removed from the screen.

The Navigator Menu

The Navigator drop-down menu can be used to invoke the Discover New Services wizard. To access the wizard, select Navigator | Discovery, as shown in Figure 5.5.

Figure 5.5.

The Navigator menu.

Depending on whether a database is selected in the Navigator pane, you will see several available options. One of the options is for the Discover New Services wizard. This wizard finds the available services on specified systems. To use this wizard for this purpose, do the following:

1. Select Discover New Services Wizard, and you will see the first screen of the Discover New Services wizard, as shown in Figure 5.6.

Figure 5.6.

The first screen of the Discover New Services wizard.

2. Click Next to move to the next screen. This screen allows you to specify node names for the discovery process to run on. After the discovery process has run, Enterprise Manager can communicate with that node and manage the various instances available on that node.

3. Type the node name, then click Add. This node will then be ready to be discovered when you click the Finish button (see Figure 5.7).

4. After you click the Next button, you are asked for the time interval at which you want discovery to occur. You can retrieve this information immediately or on a regular basis.

5. Finally, you are presented with a summary of your choices. If you are satisified that everything is correct, you can proceed with the discovery by clicking Finish.

Figure 5.7.

The Discover New Services wizard Add Nodes screen allows you to add nodes to be discovered.

6. After you click the Finish button, the Discover New Services wizard proceeds to discover that node. When it has completed, you will see the discovered status in the Service Discovery Status screen, shown in Figure 5.8.

Figure 5.8.

The Service Discovery Status screen shows you the discovery process in action.

---

NOTE: Using the Discover New Services wizard is the best way to configure the Enterprise network topology. Enterprise Manager uses a combination of the discovery feature, the Oracle intelligent agents, and the TOPOLOGY.ORA file described later today.

---

File Menu

The File menu is important to the configuration in that it is where the user preferences setup is found. When you select the user preferences setup, you will see a screen that displays the services found in the discovery process (see Figure 5.9).

Figure 5.9.

The User Preferences screen.

The User Preferences screen allows you to configure the username, password, and role assigned to the connection that the Enterprise Manager will use to that service. This allows you to keep separate passwords and DBA accounts for each system on your network, but still administer all of them from a common console.

Setting the user preferences now will save you a lot of time and aggravation later. It will allow you to connect directly to these services without having to go through the entire login procedure.

Using the Navigator Pane

The Navigator pane is probably where you will do most of your work. As I mentioned, the Navigator pane provides a tree-like presentation with the following top-level branches:

• Databases--This branch shows all the databases known by Enterprise Manager (either by discovery or with the TOPOLOGY.ORA file).

• Groups--These allow you to arrange objects with similar functions together, thus allowing you to administer these objects together.

• Listeners--The known listeners to which Enterprise Manager can connect.

• Nameservers--The nameservers of which Enterprise Manager is aware.

• Nodes--The nodes known to Enterprise Manager.

• Parallel servers--The parallel-server systems known to Enterprise Manager.

An example of the Navigator pane with the first-level trees expanded is shown in Figure 5.10. Note that all the other panes except the Navigator pane are closed in this figure. Most of the objects here are the default database objects from the installation procedure.

Figure 5.10.

The Navigator pane.

Using the Map Pane

The Map pane is designed to facilitate the administration of systems located worldwide. The Map pane allows you to create groups of systems that can be administered by drilling down on the map. After Enterprise Manager groups are set up, you can drill down into a group by simply clicking the map location.

To create a map, perform the following steps:

1. Select the Map pane from the View | Map Pane menu.

2. Select Map | Create Map.

3. At this point, you will see the Create a New Map screen, shown in Figure 5.11. Fill in the name of the map, choose a bitmap file for the map, and click OK.

After the map is created, you will see a picture of the map (see Figure 5.12). By creating a group, you can then move the icon of the group to the location on the map where those systems reside.

The map can be very useful if you are administering a large number of systems that are geographically disparate. You can even draw your own graphics that depict a building or a floor in a building where these systems reside. Take some time and play around with the Map pane. Most of the features are fairly self-explanatory and easy to use.

Figure 5.11.

Creating a map.

Figure 5.12.

The map.

---

TIP: Play around with the Map pane. See what kind of configurations you can put together. Using the Navigator pane, you can drag and drop databases into the groups you have created, and they will show up in the Map pane.

---

Using the Job Pane

The Job pane allows you to create and monitor jobs you have scheduled for one or more systems. This pane can be very useful in scheduling regular activities such as coalescing tablespaces, gathering statistics, or any other type of job that you would like to schedule.

The Job pane allows you to schedule all types of jobs, including

• SQL scripts

• SQL commands

• DBA commands, such as DDL statements

• OS commands and scripts

• Administrative tasks

• Software distribution

The Job pane is described in much more detail on Day 15, "Managing Job Queues and Using Oracle Auditing."

Using the Event Pane

The Event pane is used for monitoring events anywhere under the administration of Enterprise Manager. Enterprise Manager uses SNMP (Simple Network Management Protocol) to allow the intelligent agents to signal the console if an event has occurred.

Enterprise Manager allows you to configure the system to monitor whatever you want, and to alert you if anything it is monitoring has passed a threshold. Enterprise Manager can be configured to alert you via the console itself, e-mail, pager, and so on.

This allows you to set up Enterprise Manager to monitor your installation even when you are not there.

Using Server Manager

Server Manager provides a character-based interface into the Oracle instance. Invoke Server Manager by running it directly from the NT command line or by clicking the executable from NT Explorer. Server Manager is available under all operating systems in one form or another.

You can invoke Server Manager from the command prompt by typing the program name as shown here:

D> Svrmgr30

After Server Manager has been invoked, you will see the initial connection information, as shown in Figure 5.13.

Figure 5.13.

Server Manager.

As you can see, Server Manager provides a command-line interface that can be used to directly input SQL statements. Because Server Manager is designed as an administrative tool, it supports a superset of the SQL commands that are accepted through other SQL interfaces such as SQL*Plus. An example of some of these additional commands is the show parameter command, which can be used to show the current value of the Oracle initialization parameter that is requested. Listing 5.1 contains an example of this.

INPUT:

Listing 5.1. Using Enterprise Manager to display some tunable parameters.

D:>svrmgr30

Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production 

SVRMGR> connect internal
Password:
Connected.
SVRMGR> show parameter block

OUTPUT:

NAME                                TYPE    VALUE
----------------------------------  ------  -----------------------------
db_block_buffers                    integer 100
db_block_checkpoint_batch           integer 8
db_block_checksum                   boolean FALSE
db_block_lru_extended_statistics    integer 0
db_block_lru_latches                integer 1
db_block_lru_statistics             boolean FALSE
db_block_size                       integer 2048
db_file_multiblock_read_count       integer 8
delayed_logging_block_cleanouts     boolean TRUE
hash_multiblock_io_count            integer 8
log_block_checksum                  boolean FALSE
SVRMGR>

ANLYSIS:

In this example, I requested the value of all of the parameters that had the word block in them. As you can see, I got back a number of parameters with block in their names.

This is useful for determining the default values of all the Oracle parameters on your system. By using the command SHOW PARAMETERS with no qualifiers, you will see a listing of all the Oracle tunable parameters (see Listing 5.2).

INPUT:

Listing 5.2. See a listing of the Oracle tunable parameters.

D:>svrmgr30

Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production 

SVRMGR> connect internal
Password:
Connected.
SVRMGR> show parameters

OUTPUT:

NAME                                TYPE    VALUE
----------------------------------  ------  -----------------------------
O7_DICTIONARY_ACCESSIBILITY         boolean TRUE
allow_partial_sn_results            boolean FALSE
always_anti_join                    string  NESTED_LOOPS
aq_tm_processes                     integer 0
arch_io_slaves                      integer 0
audit_trail                         string  NONE
.
. Parameters Omitted due to length
.
timed_os_statistics                 string  off
timed_statistics                    boolean FALSE
transaction_auditing                boolean TRUE
transactions                        integer 66
transactions_per_rollback_segment   integer 11
user_dump_dest                      string  %RDBMS80%trace
utl_file_dir                        string
SVRMGR>

ANLYSIS:

This represents all the Oracle tunable parameters. Throughout this book, you will see most of these parameters again, and a full listing of all these parameters appears in Appendix B, "Oracle Tuning Parameters."

When this book shows an operation done with a SQL statement, it is being done via Server Manager. Server Manager provides the flexibility to use SQL commands with all the available options. These commands can be put into a script file, with the extension .SQL and can be run from within Server Manager. Simply put an @ before the filename of the SQL script and it will be run. It is not necessary to add the .SQL extension to the name when running it. For example, to run a SQL script called build.sql in the c:database directory, you can use the syntax shown in Listing 5.3 within Server Manager.

Input/Output:

Listing 5.3. Using Server Manager to invoke a SQL script.

D:>svrmgr30

Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production


SVRMGR> connect internal
Password:
Connected.
SVRMGR> @c:databasebuild
...
... Data Returned
...
SVRMGR> 

As you will see throughout the book, there are certain operations that I prefer to do within a SQL script. By scripting these operations, I have certain advantages, including the following:

• A permanent record of exactly how the operation was done.

• A template for future work. After I have scripted a database build, I can modify that script for use on other databases.

• This script can be used to re-create the database in the event of some sort of failure.

---

TIP: By keeping your build scripts, you will be able to quickly rebuild your database in the event of a catastrophic failure.

---

Throughout this book, you will see numerous examples in which Server Manager is needed for some operations.

Managing a Bootstrap Instance Under NT

With the NT operating system, the Oracle bootstrap instance must be created before the database can be created. Because it is a service, the instance is a little different from the Oracle instance on other operating systems. A service called OracleServiceSID (where SID is the system identifier) is created when the instance is created. This service or instance service is used to bootstrap the more traditional Oracle instance. Because the Oracle server is a service under NT, you cannot connect to Oracle or create the database without this service's being started. The instance is created via the NT Instance Manager. This utility should not be confused with the Enterprise Manager's Instance Manager; they are separate utilities.

Creating an Instance with the Oracle Database Assistant

When you invoke the Database Assistant from the Oracle for Windows NT workgroup, you will see the Database Assistant Welcome screen. This will remain until the Database Assistant has loaded. After the Database Assistant has loaded, you will be prompted with the initial choice as to the function you want to perform (see Figure 5.14).

You have the choice of creating or deleting a database. For the sake of example, let's create a database. To do so, perform the following steps:

1. You can perform either a typical or a custom database creation, as shown in Figure 5.15. A typical database creation creates the standard starter database, whereas the custom installation lets you change parameters such as instance and datafile names. In this example, let's perform the typical database creation. The custom database creation is shown oon Day 6, "Administering Databases and Datafiles."

Figure 5.14.

Database Assistant.

Figure 5.15.

Performing a typical database creation with Database Assistant.

2. As shown in Figure 5.16, you will be asked whether you want to add replication support to this database. If you will be using advanced replication, select the Advanced Replication button. This adds the extra stored-procedure packages used by advanced replication. Replication is covered in detail on Day 18, "Administering Oracle Replication."

3. You will be asked whether you can take advantage of the timesaving feature of copying database files from your CD-ROM rather than creating them, as shown in Figure 5.17. If you do not have the CD-ROM available, you need to create the files.

Figure 5.16.

Select Advanced Replication if you want to add replication support to this database.

Figure 5.17.

Create new database files using Database Assistant.

4. Click the Finish button; the database will be created for you. As usual, you have one last chance to change your mind, as shown in Figure 5.18. If the directory looks correct, click Yes; the database will be created.

Figure 5.18.

Confirm that you want to proceed with the database-creation process.

5. One final screen asks for input and informs you that the database named oracle will be created with the SID orcl. If this is suitable, click OK (see Figure 5.19).

Figure 5.19.

This dialog identifies the new database's name and SID.

Creating the Instance from the Command Line

Creating the instance from the command line uses the same program as the GUI version, but with command-line arguments. The Instance Manager is actually the program named ORADIM80.EXE. To obtain help on the various parameters of ORADIM80, use the command ORADIM80 -?.

Creating the Instance

To create an instance using ORADIM80.EXE, run it with the following arguments:

ORADIM80 -NEW -SID SID -INTPWD password [-MAXUSERS number] 
[-STARTMODE AUTO or MANUAL] [-PFILE pfile_name]

The values for SID and password are mandatory, whereas the values for MAXUSERS, STARTMODE, and PFILE have defaults. Here is an example of creating an instance called dogs:

ORADIM80 -NEW -SID dogs -INTPWD oracle

This will create the instance dogs with a default PFILE of initDOGS.ora and manual startup.

Starting and Stopping the Instance

The instance can be started or stopped from the command line through the use of the following arguments to ORADIM80.EXE:

ORADIM80 -STARTUP -SID SID -PFILE pfile_name 
[-USRPWD password] -STARTTYPE SRVC or INST

or

ORADIM80 -SHUTDOWN -SID SID -PFILE pfile_name 
[-USRPWD password] -SHUTTYPE SRVC or INST -SHUTMODE a,i,n

where the shutdown modes are a=abort, i=immediate, and n=normal.

Modifying the Instance

At times, it becomes necessary to modify the instance you have created. This can be done with the NT Instance Manager or with the ORADIM80.EXE program. Both methods use the executable ORADIM80.EXE. If you do not provide command-line options, the graphical tool will be invoked.

---

NOTE: The Oracle Database Assistant can be used only to create and delete a database, not to modify one.

---

Modifying an Instance with the GUI

You can edit an instance by selecting the instance name and then clicking the Edit button (see Figure 5.20).

Figure 5.20.

Select Edit in the Instance Manager.

This will take you to the Edit Instance screen. You have several options available from the Edit screen; from here, you can change the administrative password or the startup mode by clicking a button or typing a value.

Editing the Instance with ORADIM80.EXE

To edit an instance, use the same command with the -EDIT flag like so:

ORADIM80 -EDIT -SID SID -INTPWD password [-MAXUSERS number] 
[-STARTMODE AUTO or MANUAL] [-PFILE pfile_name]

The parameters are the same.

Deleting an Instance Under NT

It is occasionally necessary to delete an instance under Windows NT. This can be accomp-lished via the NT Instance Manager or with the ORADIM80 command, as demonstrated previously.

Deleting an Instance

To delete an instance, select the instance you want to delete and click OK. This deletes the instance and its services from the system.

Deleting the Instance with ORADIM80.EXE

Using ORADIM80.EXE, you can delete an instance or a service. This is done using the -DELETE option like so:

ORADIM80 -DELETE -SID SID

or

ORADIM80 -DELETE -SRVC service

Starting Up the Instance

The Oracle instance can be started manually, automatically, through a graphical tool, or from the command line. In this section, you will look at all the different ways the Oracle instance can be started.

Starting the Instance Automatically

The Oracle instance can be configured to start automatically when NT Server is booted. Configure the instance to start this way by specifying the automatic startup option when the instance is created, as shown in the section called "Creating an Instance with the Oracle Database Assistant." After the instance has been created, you can edit the service OracleStartSID by editing the service properties from the NT Service Manager program.

After you select the service with NT Service Manager, click the Startup button. Here you can change the startup type from Automatic to Manual, as shown in Figure 5.21.

Figure 5.21.

Changing the startup type to Manual.

This invokes the Oracle bootstrap instance at startup, but does not start the main Oracle instance. This can be accomplished as shown in the next section.

Starting the Instance with Enterprise Manager

After the bootstrap instance has been created, you can use Enterprise Manager's Instance Manager to start up and shut down the instance. Invoke the Instance Manager by clicking the Instance tab on the Oracle Enterprise Manager toolbar or through the Enterprise Manager program group, or by using Enterprise Manager. You will be presented with the Instance Manager user information screen shown in Figure 5.22.

Figure 5.22.

The Instance Manager Login Information screen.

Here you need to log on with the internal username and the appropriate password. If you are connecting to an instance that is not the default instance as assigned in the NT Registry, you should specify the service that connects to the desired Oracle instance.

After you connect, you will see the Instance Manager startup screen on the right side and database information screen on the left side, as shown in Figure 5.23. In this example, you will see that the Oracle instance is currently not up.

Figure 5.23.

The Instance Manager main screen provides information about the state of the instance.

To get to the Startup screen, click the Startup tab on the right side of the screen; you will then see the Startup screen, shown in Figure 5.24.

From this screen, you can specify the startup options. The available options are

• No Mount--Starts the instance but does not mount a database.

• Mount--Starts the instance and mounts the database, but does not open the database.

• Mount and Open (default)--Starts, mounts, and opens the database.

Figure 5.24.

The Startup screen.

• Force--Forces the startup, even if there are problems.

• Restrict--Starts the database in restricted mode. Only users with RESTRICT SESSION privilege can access the database.

You also have the option of accessing an internally saved configuration file (available only if the instance is started), or specifying a parameter file. Specifying the parameter file allows you to use the parameter file you created for this instance. This is covered in more detail on Day 6.

Clicking the Startup button starts the Oracle instance with the options you have specified.

Starting the Instance with Server Manager

From Server Manager or from a SQL script, you can use the command STARTUP.

The Syntax for STARTUP

The STARTUP command is used to start an Oracle instance and has the following syntax:

SYNTAX:

STARTUP
[PFILE=parameter_file]
[MOUNT or NOMOUNT]
[PEN]
[EXCLUSIVE]
[RESTRICT]
[FORCE]
[RECOVER]

These options, which are approximately the same as with the Instance Manager described previously, are

• PFILE=parameter_file--This specifies which parameter file to use. This file is often referred to as the init.ora file.

• NOMOUNT--Starts the instance but does not mount a database.

• MOUNT database]--Starts the instance and mounts the database named database, but does not open the database.

• OPEN database]--Starts, mounts, and opens the database named database. This is the default parameter.

• FORC--Forces the startup, even if there are problems.

• RESTRICT--Starts the database in restricted mode. Only users with RESTRICT SESSION privilege can access the database.

• EXCLUSIVE--Allows only one instance to mount the database.

• PARALLE/SHARED --These parameters are synonymous. They allow multiple instances to mount the database in a parallel-server environment. This parameter has an additional qualifier, RETRY, which specifies that if a database is busy being recovered by another instance, this instance will try to open the database every five seconds.

As you can see, the startup options are the same whether you are using the Instance Manager or the character-based Server Manager. The advantage of using Server Manager is that this can be scripted into the same file as the database-creation scripts.

Shutting Down the Instance

Because much of the data that has been changed in the Oracle database might still be residing in the database buffer cache, a disorderly database shutdown can result in a significant recovery period when the database is started up again. To avoid this, an orderly shutdown should always be performed. Four different types of shutdowns can be performed by Oracle:

• Normal--This type of shutdown is by far the most highly recommended way to shut down the instance. Once a normal shutdown has been initiated, no new users can log on to the database and the instance will wait until all users have disconnected before the instance is terminated.

• Immediate--With an immediate shutdown, all currently active transactions are terminated and any uncommitted transactions are rolled back. All connected users are simultaneously disconnected from the instance. Upon completion of these tasks, the Oracle instance is terminated.

• Transactional--The transactional option allows currently running transactions to complete. As soon as a transaction has completed or aborted, the connection is terminated. During this time, no new connections are allowed.

• Abort--The abort shutdown essentially aborts the instance. All activity is terminated, and the instance aborted. The result of an abort shutdown is a database recovery on startup.

The abort shutdown should be used only as a last resort when no other options are available. The immediate shutdown should be used only when it is critical that the instance be shut down as soon as possible. You will typically use the normal shutdown option.

Similar to the instance startup, the instance shutdown can be done graphically (through Enterprise Manager's tools) or via the character-based Server Manager. Both options are presented here.

Shutting Down the Instance Automatically

The Oracle instance can be configured to start automatically when the NT Server is booted, as shown previously. When the instance is configured this way, NT automatically shuts down services that were automatically started. In this way, you might see quite a delay when shutting down NT, but an orderly database shutdown is occurring.

Shutting Down the Instance with the Instance Manager

From the Instance Manager, click the Shutdown tab on the right side of the screen. This invokes the Shutdown screen shown in Figure 5.25.

Figure 5.25.

The Shutdown screen.

Here you can choose Normal, Immediate, or Abort. Normal is the preferred option, although in the event of an urgent shutdown, the Immediate option should be selected. SHUTDOWN ABORT should be used only as a last resort.

Shutting Down the Instance with Server Manager

Shutting down the database with Server Manager can be accomplished through the use of the SHUTDOWN command. This can be useful when you want to shut down the instance from within a script.

The Syntax for SHUTDOWN

The SHUTDOWN command is used to shut down an Oracle instance and has the following syntax:

SYNTAX:

SHUTDOWN
[NORMAL]
or [IMMEDIATE]
or [TRANSACTIONAL]
or [ABORT]

These options, approximately the same as with the Instance Manager, are

• NORMAL--The default option. Shuts down the instance normally.

• IMMEDIATE--Shuts down the instance immediately, as described previously.

• TRANSACTIONAL--Allows currently running transactions to complete and shuts down the instance, as described previously.

• ABORT--Aborts the instance, as described previously.

I like using the SHUTDOWN command from Server Manager when I am scripting an operation such as a database creation. In this way, the entire procedure can be scripted.

Configuring the Network

After the instance has been created, you must configure the network so you can connect to it. The network configuration serves two purposes:

• To allow a remote process to connect to the database. This is done via the LISTENER.ORA configuration. This is essentially the server side of your network configuration.

• To allow a connection into the instance via an alias. In this manner, you can connect to various databases with different SID values by connecting to the service that points to that SID. There are two ways to configure the network service: through the Network Topology Generator that comes with Enterprise Manager, or through the Oracle Network Configuration wizard that comes with Oracle for NT.

Let's look at the LISTENER.ORA configuration, then at the client-side configuration.

LISTENER.ORA Configuration

The LISTENER.ORA file is created for each network protocol at installation time. This file defines not only the network protocols, but the Oracle SIDs that can be accessed through the listener. By default, the SID defined in the LISTENER.ORA file is ORAC (from the starter database). The initial LISTENER.ORA file looks like this:

 1: ################
 2: # Filename......: listener.ora
 3: # Node..........: local.world
 4: # Date..........: 24-MAY-94 13:23:20
 5: ################
 6: LISTENER =
 7:   (ADDRESS_LIST =
 8:         (ADDRESS=
 9:           (PROTOCOL= IPC)
10:           (KEY= oracle.world)
11:         )
12:         (ADDRESS=
13:           (PROTOCOL= IPC)
14:           (KEY= ORCL)
15:         )
16:         (ADDRESS=
17:           (COMMUNITY= NMP.world)
18:           (PROTOCOL= NMP)
19:           (SERVER= EDW)
20:           (PIPE= ORAPIPE)
21:         )
22:         (ADDRESS=
23:           (COMMUNITY= TCP.world)
24:           (PROTOCOL= TCP)
25:           (Host= edw)
26:           (Port= 1521)
27:         )
28:         (ADDRESS=
29:           (COMMUNITY= TCP.world)
30:           (PROTOCOL= TCP)
31:           (Host= edw)
32:           (Port= 1526)
33:         )
34:   )
35: STARTUP_WAIT_TIME_LISTENER = 0
36: CONNECT_TIMEOUT_LISTENER = 10
37: TRACE_LEVEL_LISTENER = ADMIN
38: SID_LIST_LISTENER =
39:   (SID_LIST =
40:     (SID_DESC =
41:       (SID_NAME = ORCL)
42:     )
43:   )
44: PASSWORDS_LISTENER = (oracle)

To add an additional SID onto the SID list, simply copy the section of the LISTENER.ORA that describes the SID as shown here and paste it to the LISTENER.ORA file after the ORCL SID definition. The following should be placed on line 43:

(SID_DESC =
      (SID_NAME = ORCL)
    )

Be sure to keep the parentheses correct. Change the SID value to the value of the SID that you have added to your system.

Oracle Net8 Easy Config

In order to administer the TNSNAMES.ORA file, you can use the Oracle Net8 Easy Config utility found in the Oracle for NT tools:

1. When you invoke the Net8 Easy Config utility, you will be greeted with the main screen of the Oracle Service Name wizard. From here you can choose to create a new service or to modify, delete, or test an existing configuration. If you are adding a new service, type the service name and click Next (see Figure 5.26).

Figure 5.26.

Choose to modify, delete, or test a configuration.

2. You will be prompted for the network protocol for this service, as shown in Figure 5.27. Highlight the protocol that you want and click Next.

3. The next screen, shown in Figure 5.28, prompts you for the hostname (if you chose TCP/IP protocol). Enter the hostname of the system. You can change the port number if you want, but that is not recommended.

4. The next screen, shown in Figure 5.29, prompts you for the SID of the instance being configured. If the instance name is correct, click Next.

Figure 5.27.

The Oracle Service Name wizard allows you to choose a network protocol to use for this service.

Figure 5.28.

The Oracle Service Name wizard allows you to change the hostname and port number.

Figure 5.29.

The Oracle Service Name wizard allows you to specify the SID.

5. In the screen shown in Figure 5.30, you can test the newly created service. Although you can skip this step, I don't recommend it. Just click the Test Service button.

Figure 5.30.

The Oracle Service Name wizard lets you decide whether you want to test the connection.

6. Test the connection in the Connection Test screen. If the test is successful, you will be duly informed (see Figure 5.31).

Figure 5.31.

The Connection Test screen allows you to test the connection before saving it.

7. After you finish testing the service, return to the previous screen and click Next to see the final screen of the wizard (see Figure 5.32). If everything was successful, click the Finish button to save the configuration.

Figure 5.32.

Finishing with the Oracle Service Name wizard.

After you click Finish, the application will exit. Your new network configuration is ready to use. You can also manage the network through the Oracle Net8 Assistant.

Modifying the State of the System

During the normal operation of the RDBMS, it is often necessary to modify the state of the instance. These operations actually modify the state of the instance or cause events to occur in the Oracle instance.

Modifying the instance can be done only using the ALTER SYSTEM command. There is currently no way to run these commands via a graphical utility.

Modifying the System using the ALTER SYSTEM Command

Using the ALTER SYSTEM command, you can alter the state of the system or cause some event to occur in the Oracle instance.

The Syntax for ALTER SYSTEM

The ALTER SYSTEM command is run with the following syntax:

SYNTAX:

ALTER SYSTEM
[ENABLE RESTRICTED SESSION]
[DISABLE RESTRICTED SESSION]
[FLUSH SHARED_POOL]
[CHECPOINT [GLOBAL or LOCAL]]
[CHECK DATAFILES [GLOBAL or LOCAL]]
[SWITCH LOGFILE]
[ENABLE DISTRIBUTED RECOVERY]
[DISABLE DISTRIBUTED RECOVERY]
[ARCHIVE LOG archive_log_clause]
[KILL SESSION `SID, serial_no']
[SET ??]

where the options that can be used by the set parameter are

SET
[RESOURCE_LIMIT = TRUE or FALSE]
[GLOBAL_NAMES = TRUE or FALSE]
[SCAN_INSTANCES = number]
[CACHE_INSTANCES = number]
[MTS_SERVERS = number]
[MTS_DISPATCHERS = `protocol' , `number']
[LICENSE_MAX_SESSIONS = number]
[LICENSE_SESSIONS_WARNING = number]
[LICENSE_MAX_USERS = number]
[REMOTE_DEPENDENCIES_MODE = TIMESTAMP]
or  [REMOTE_DEPENDENCIES_MODE = SIGNATURE]

The parameters used to alter the system are defined as follows:

• ENABLE RESTRICTED SESSION --Sets the mode of the instance so that only users with the RESTRICTED SESSION permission can log on.

• DISABLE RESTRICTED SESSION --Disables the restricted session, allowing all users to log on.

---

TIP: This can be very useful for temporarily restricting new users from logging onto the system if you think there might be some sort of problem or a load issue. By restricting the sessions, you are not causing problems to current users; you are simply preventing new users from logging on. You can reverse this without shutting down the instance.

---

• FLUSH SHARED_POOL --Clears all data from the shared pool.

• CHECKPOINT [GLOBA or LOCAL]--Initiates a checkpoint. In a parallel-server environment, CHECKPOINT LOCAL checks only its own log group, whereas CHECKPOINT GLOBAL checks all log groups.

---

TIP: If you anticipate that the system might need to be shut down or you feel you might be in danger of losing power, you can checkpoint the system, thus shortening any recovery time you might need in the event of a failure.

---

• CHECK DATAFILES--Verifies that your instance can access all online datafiles. In the parallel-server environment, the GLOBAL qualifier additionally verifies that all instances can access all online datafiles. The LOCAL qualifier specifies only your instance.

• SWITCH LOGFILE--Initiates a log switch; a log switch automatically triggers a checkpoint.

• ENABLE DISTRIBUTED RECOVERY --Enables distributed recovery in a single-process environment.

• DISABLE DISTRIBUTED RECOVERY --Disables distributed recovery.

• ARCHIVE LOGarchive_log_clause--The ARCHIVE LOG clause is described in detail in Days 16, "Understanding Effective Backup Techniques," and 17, "Recovering the Database," which cover backup and recovery.

• KILL SESSION SID, serial_no'--Kills a session. Values of both SID and serial_no must be provided. These are obtained from the V$SESSION view.

---

TIP: This can be useful for stuck or nonresponding sessions. This can also be done through the Instance Manager.

---

• SET ??--The SET parameter is used to set a number of Oracle parameter values. These values as shown in the ALTER SYSTEM syntax are described in detail in the Oracle documentation.

Summary

This lesson covers quite a bit of material that introduces the Oracle administrative duties and tools. The day begins by looking at the various duties of the DBA. It later presents the various default accounts used by the DBA to connect to the Oracle instance and how they are used.

A major part of this chapter looks at the tools of the trade. I am really happy with the work Oracle has done to create Enterprise Manager and the applications that go with it. Along with Enterprise Manager are tools such as Instance Manager, Storage Manager, Schema Manager, Security Manager, and Backup Manager. You will see these in more detail later on in this book.

You saw how to invoke Enterprise Manager and how to configure the different panes, as well as how to use the Discover New Services wizard to determine the services available for administration by Enterprise Manager.

For those of you running on the Windows NT platform, this chapter demonstrates how to create the Oracle bootstrap instance. Because Oracle is a service under NT, it needs this bootstrap instance to allow you to connect, and to start up and shut down the Oracle instance.

You also learned how to use the various network tools to configure the TOPOLOGY.ORA and TNSNAMES.ORA files, as well as how to configure the LISTENER.ORA file. These are the key components to the Oracle networking system.

Finally, you learned how to alter the instance. This is not typically done, but is occasionally necessary to initiate a checkpoint by hand or to manually archive a redo log file. In any case, it is good to know how to perform these operations when necessary.

What's Next?

Tomorrow you will learn how to create an Oracle database. This is the first of two days that take you through the process of creating redo log files, control files, and datafiles, and building the initial database. In the second of these days, you will learn how to build on that initial starter database and add your own tablespaces and datafiles. After you finish Days 6 and 7, "Administering Tablespaces," you will be able to create your own database.

Q&A

Q What is the primary duty of the DBA?

A The primary duty of the DBA is to protect the database and provide continuous (if required) access to that data for the user community.

Q Should all DBAs use the SYS account?

A A lot of installations have their DBAs use the SYS account, but I think it is better to provide all the DBAs with accounts that have the proper level of permissions. User permissions are discussed on Day 10.

Q Is it better to use the graphical tools or Server Manager?

A Both tools have their place. The Enterprise Manger tools are absolutely great. I really like using them, but there are times when I just can't get the job done without using Server Manager.

Q Why does an instance need to be created before the database can be created?

A This is unique to Windows NT. The Windows NT system uses services to run the Oracle instance. The bootstrap instance must be created so Instance Manager or Server Manager can connect to it to start up the full instance.

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 quiz questions in Appendix A, "Answers."

Quiz

1. What two accounts are created by default when a database is created?

2. What are some duties of the DBA?

3. How can you protect the system against data loss?

4. What two OS roles are created by default when a database is created?

5. What is the bootstrap instance?

6. What is the TNSNAMES.ORA file for?

7. How do you administer the TNSNAMES.ORA file?

8. What tool is used to modify the state of the system (that is, cause a checkpoint to occur, and so on)?

Exercises

1. Create a new Oracle instance using the NT Instance Manager.

2. Create a new Oracle instance using the ORADIM80 command.

3. Add this instance to the TNSNAMES.ORA file using the Oracle Network Configuration wizard.

4. Add this instance to the TOPOLOGY.ORA file using the Network Topology Generator.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.