Back Up Next

Chapter 25 *

Oracle8: Networking, Security, and Migration *

Oracle8 Networking Enhancements *

Oracle8 Networking New Features *

Exercises *

Configuring the Oracle8 Network *

Exercises *

Oracle8 Network Security *

Exercises *

Using Names and Simplifying Management *

Exercises *

New Oracle8 Security Features *

Managing the SYS Schema *

Exercises *

Authenticating Users with Enhanced Password Management *

Exercises *

Creating Profiles and Users with Password Management *

Exercises *

Using Dictionary Views for Password Management *

Exercises *

Migration to Oracle8 *

Using the Migration Utility *

Exercises *

Migrating from Oracle7 to Oracle8 *

Step 1: Prepare for Migration *

Step 2: Make Room on Disk *

Step 3: Develop the Test Plan *

Step 4: Maintain the Source *

Step 5: Perform the Migration *

Step 6: Back Up, Test, Modify, Open *

Exercises *

Chapter Summary *

Two-Minute Drill *

 

Chapter 25

Oracle8: Networking, Security, and Migration

In this chapter, you will understand and demonstrate knowledge in the following areas:

Oracle8 networking enhancements
New Oracle8 security features
Migration to Oracle8

This chapter covers the final aspects of Oracle8 new concepts. The final areas of understanding for Oracle8 covered in this Guide for the OCP Exam 5 are the networking enhancements and security features. Also, this chapter identifies the requirements and methods for migrating Oracle7 databases to Oracle8 using the Oracle migration utility. The purpose of this chapter is twofold. First, it is meant to cover the material in these areas that will be tested on OCP Exam 5. Second, it will help you with preparation for migrating to Oracle8.

Oracle8 Networking Enhancements

In this section, you will cover the following topics related to Oracle8 networking enhancements:

Oracle8 networking new features
Configuring the Oracle8 network
Oracle8 network security
Using names and simplifying management

Oracle8 offers new features for the DBA to manage connectivity between database application clients and the server. In Unit IV, in the presentation of the client/server architecture, the foundations of SQL*Net were identified. Oracle8 builds on the foundation laid by SQL*Net with Net8—the new networking tool for Oracle8 client/server applications. This section will cover the new features of Net8 and the requirements for configuring the client/server network with Net8. The new networking tool offers new features for network security as well. Finally, Net8 handles network naming and simplified management.

Oracle8 Networking New Features

The new features of Net8 are focused into several areas, including scalability, connectivity, security, performance, configuration, and administration. Net8 supports database systems with substantial user populations and provides backward compatibility with SQL*Net version 2. Net8 is designed to provide object-oriented connectivity in a utility that combines the features of Transparent Network Substrate, Secure Network Services, and Oracle Names with a new messaging layer for enhanced client/server communication. There are several challenges facing client/server systems related to scalability for large user communities. To handle the difficulties faced by client/server applications, Net8 offers features for multiplexing, connection pooling, concentration, and naming to offset or eliminate the performance degradation suffered by the addition of users. Net8 offers the DBA the ability to create a connection between Rdb and Oracle Express OLAP systems, and more compatibility with level 2 open database connectivity standards (ODBC) for direct interface to PL/SQL stored procedures.

A new component of Net8 is the Oracle Security server. It is designed to offer several improvements for database and client/server security. One of the new features of the Oracle Security server is the support of authentication using digital signatures. This usage allows for the users of the Oracle database to sign on once and have access to many different Oracle and third-party products. Further, once authenticated, the clients and servers in the Oracle network can use encryption on their SQL*Net data--but not user data--between the client and the server. The new performance features in Net8 include TNS raw transport, information for Oracle Names stored in the client, and optimization for the dispatcher process.

Exercises

  1. Identify the new name for the Oracle networking utility.
  2. What are some of the new networking features available with Oracle8 related to scalability? Connectivity? Authentication? Performance?

Configuring the Oracle8 Network

There are several options in Net8 designed to manage the configuration of the database network services centrally, easily, and quickly. To configure the Oracle network, the DBA can use the Net8 Assistant or the Net8 Easy Config tools. Oracle Enterprise Manager can be used to start Net8 Assistant. The Oracle Names tool handles dynamic discovery of nodes in the network. Client profiles can be defined to simplify the identification of each client in the network. For smaller Oracle installations, the DBA can use the default configuration provided with Net8. Finally, the DBAs can use the Net8 configuration utility or "wizard" to configure the full network.

One of Net8’s new features for connecting user processes to shared servers in the multithreaded server (MTS) configuration is designed to reduce network traffic. Recall in the discussion of client/server configuration that the MTS architecture consists of a SQL*Net listener, which hears user requests from across the network and passes those requests to the dispatcher. The dispatcher brokers access to shared servers, and the shared servers read information from datafiles on behalf of user processes. Net8 adds a new component called the Connection Manager, which is designed to concentrate several user connections through one single transport. Connection Manager can work in conjunction with MTS. There are several advantages to using Connection Manager, namely that it can act as a firewall for checking authorization for use and as a multiprotocol interchange (MPI) to send information sent on one network protocol to another protocol. In addition, the Connection Manager has the benefit of concentrating and multiplexing data from several user connections into one carrier. Figure 25-1 illustrates the principle of usage for Connection Manager.

Fig25-01.jpg (35756 bytes)

Figure 1: Using Connection Manager with the multithreaded server

The feature of multiplexing is used in Net8 as well. Since multiple user sessions connect to the database server via one physical link, the scalability of database usage can increase. The initial connection request requires its own physical transport, while the subsequent connection from the same process uses the same physical transport. So, only the new request for connection from a new client produces a higher level of network traffic, after which that new connection can be multiplexed in with existing connections.

Connection pooling is used to reduce the burden on the network as well. Connection pooling allows the server to define a maximum number of connection sockets that can be active at any time. If one connection is inactive and another connection request is made, then the inactive connection is temporarily disabled in order to process the new connection request. The same process happens over and over and recycling sockets only as they are needed, limiting resources required while maximizing overall availability for user connections.

Some enhancements have been made to the Net8 Listener as well. The Net8 listener now supports both two-tier and N-tier client/server configurations. Instead of having clients make their remote procedure call (RPC) and then wait for the reply, Net8 allows the client to make the call to a third tier and terminate, allowing for asynchronous messaging and fault tolerance. If the server is unavailable or connection is terminated, the request is still queued somewhere and made available to the server when activity is reestablished. This design is beneficial for applications where scaling and load balancing are more important than a fast, connection-oriented design approach requiring more network bandwidth to support more real-time connections.

Net8 supports faster connections via TNS raw transport features. Instead of sending data across the network with a complete Net8 header and no advanced networking information, Net8 has been streamlined to automatically remove the Net8 header and use low-level protocol API calls instead. Basic connections between client and server will run faster because less data is transported from client to server at a low level, bypassing unnecessary layers of network protocol within Net8.

Exercises

  1. What utilities and methods can be used to configure the Net8 network services in Oracle8?
  2. What is the Connection Manager? How does it improve scalability in network connectivity without increasing network traffic? What is Oracle Names, and how does it improve network configuration?
  3. What is connection pooling? What is multiplexing? What enhancements have been made to the Net8 listener process? What is TNS raw transport?

Oracle8 Network Security

To handle the increased demand for network security created with the explosion of the Web, wireless connections, and an overall increase in networking, Oracle offers several features to allow DBAs and organizations to take advantage of emerging security technologies while adding its own technological advancements to the mix. The Advanced Networking Option allows DBAs and security administrators to configure clients and servers to use encryption of data sent across a network. In addition, Net8 supports the use of checksums to detect altered or corrupted packets. Net8, in conjunction with the Advanced Networking Option, supports the use of RSA Data Security RC4 and the data encryption standard (DES) algorithms. The features of Connection Manager that handle firewalls work in conjunction with the Advanced Networking option also, which allows for secure data transfer across multiple network protocols.

Oracle offers a new product that works with Net8, the Oracle database, and Oracle clients called the Oracle Security server. This product uses public-key user authentication in an Oracle network to create digital signatures for one-time login to all areas of the Oracle network accessible to a user based on their signature. At the time of login, the user receives a set of attributes that are designed by the DBA and security administrator. These attributes are a list of privileges and roles to a component or "principal" on the Oracle network. This principal is the Oracle database, Web server, users, or other items. Finally, the DBA can set up access to distributed stored procedures on remote servers with the use of external authentication. In Oracle7, the authentication required in stored procedures or embedded SQL required the password information to be hard-coded into the source.

The Oracle Security server allows for central management of authentication data in a distributed environment based on X.509 version 1 certificates, based on public/private-key cryptography. The DBA or security administrator can use Oracle Security Manager to set up this framework. This tool is part of Oracle Enterprise Manager. Furthermore, the security management and usage can be designed into Oracle applications with the use of the security manager API called Oracle Security Server Toolkit.

Digital signatures can be administered in the following way. First, the user requests connection to the database from the Oracle Security server. The principle database or Web server sends Oracle Security Server a certificate containing an identifier for the user, a public key, and a signature of ownership. Oracle Security server responds to the user by sending its own key information and verifies the client’s identification. The client will be considered authenticated if their identification checks out OK by the Oracle Security server. In this way the client is also assured that the Oracle database is authenticated. This form of third-party authentication allows both the client and the server to know that both are who they say they are. Figure 25-2 illustrates the principle of third-party authentication.

Fig25-02.jpg (26125 bytes)

Figure 2: Third-party authentication using Oracle Security server

Exercises

  1. What is Oracle Security server? Describe third-party authentication.
  2. What is the Advanced Networking option? How does the Connection Manager use the Advanced Networking option?

Using Names and Simplifying Management

With the use of an Oracle Names server, the DBA or security administrator can enjoy simplified configuration of network services. All destination network services such as the listener, the database, Connection Manager, and others have a name they use to register themselves with the Names server. The Names server should listen on a known port for these other services to connect and register themselves automatically. If a Names server is used, the client can find any required services available in the Oracle network by identifying the service required to all Names servers on the Oracle network. The Names server then looks in its list of names to resolve a network address based on that name. The list of Names servers available to a client is stored in the sdns.ora file on a client. The Names server then sends the address back to the client. With the resolved destination address, the client can then initiate contact with the appropriate resource on the network.

The Net8 Assistant can be used to configure the two types of server networks available, the Workgroup server and Enterprise server. In Workgroup server, the client connects to only one server, allowing for the definition of default information and eliminating the need to configure Net8. For Enterprise server, since clients may connect to many servers, the Net8 Assistant can use applets created from Network Manager functions to set up this type of network, or the applets themselves may be used individually.

To configure the client, there are several new options. One of these options is to change nothing at all. Oracle has attempted to set client defaults to fair values in order to enable operation in a variety of situations. Alternately, the clients and services may be able to configure by themselves automatically in the presence of a TCP/IP network working in conjunction with a Names server. Both client and server simply find the Names server and identify themselves. The Names server can manage client profiles centrally as well with the use of a client profile editor. If a default profile is present on the Names Server, Net8 on the client will load the client profile at start time from the Names server. This feature eliminates client configuration altogether. In environments where listeners on the database server using the MTS option use all defaults, the listener.ora file is unnecessary—simplifying configuration as well.

Client configuration of Net8 is simplified with the use of an installation guidance software interface, called a "wizard." This wizard is invoked when Net8 is first installed on the client. It locates all Names servers and places them in order, then reads client profile information from the Names server if the Names server is present to configure that client. The user has the option to override a default setting from the profile with a specific setting in an interactive fashion. Further, the default settings from the profile may be rejected altogether in favor of default settings provided by Net8. Finally, the configuration files can be modified later for enhancements, if need be.

As an alternative for organizations that feel they don’t need the security options offered with Oracle Security server or the ease of configuration provided by the Names server, and are running their Oracle database client/server application over TCP/IP, the native hostname adapter can be used. The hostname adapter is usable only if the Net8 listener serves only one database. The hostname adapter works in conjunction with the local naming system on the TCP/IP network--either DNS or NIS--and returns an IP address for the hostname of the server requested by the user. No additional security services can be requested using this option. Organizations that use the native hostname adapter may do so based on having a smaller Oracle database user community, reducing or alleviating the need for additional security services, or to reduce the complexity of configuring extended security and naming options.

As a final note, Net8 uses Oracle Trace as an assistant to identifying routes taken for connections for diagnostic and troubleshooting purposes. This allows the DBA to trace a client session completely, from client to server and back again. If there is a problem with networking between client and server, Oracle Trace will identify its symptoms. Net8 does not store Oracle Trace internally; it is an external component. This reduces the overall footprint for Net8 on the client. Oracle Trace offers the following features for usage. It extracts error messages the client may have encountered and places the text for the error in a log file. It also identifies packet information and type sent between client and server, and traces statistics for client and server. In short, Oracle Trace allows for problem diagnostics without costing too much in terms of storage or execution. Net8 also offers its own applications programming interface (API) for use of the Oracle network features for connectivity needs in database and nondatabase applications. The Net8 API is called Net8 OPEN, and it offers a common interface to all industry standard network protocols. As long as Net8 is used on the network, the applications using Net8 OPEN can develop an application for one machine and port the software to another without modifying network calls.

Exercises

  1. What value does having a Names server add when configuring an Oracle network? What file is used to store information about the Names servers available to the client? What is a wizard?
  2. What two options are available for client configuration to use default settings? What is a client profile and where is it stored?
  3. What is a native hostname adapter? When might it be used to configure the network? What conditions must be TRUE for its use? Is Oracle Trace executable software stored in Net8? What is the name of Net8’s API?

New Oracle8 Security Features

In this section, you will cover the following topics related to New Oracle8 security features:

Managing the SYS schema
Authenticating users with enhanced password management
Creating profiles and users with password management
Using dictionary views for password management

The Oracle Security server is not the only enhancement made to Oracle8 to support more robust database security. There have been several changes made to the Oracle8 security architecture within the database as well. Oracle8 also allows the DBA to manage security better in many areas, such as the management of the SYS schema, expiration of passwords, enhanced user creation features and profiling to handle password expiration, and changes to dictionary views to support enhanced password management. These are areas that many DBAs may have felt Oracle lacked security in in the past, but that Oracle8 now covers. This section will provide a full explanation for usage of these features to help you prepare for OCP and for Oracle8 migration.

Managing the SYS Schema

As an enhancement to the security of the SYS schema, the following changes have been made to Oracle8. First, the password provided by the DBA to access the database as internal or with sysdba privileges must match the password for SYS as it exists on the password file. Recall from Chapter 6 that the password file is used to maintain the integrity of passwords used by DBAs to connect to Oracle to perform privileged operations.

A major change in the management of the SYS schema has to do with the any privilege. Recall from earlier discussion that many system or object privileges can be granted with maximum scope by adding the any keyword to the privilege grant. For example, the following select any table privilege grant statement issued in Oracle7 will allow user SPANKY to see data in the SYS.AUD$ table:

GRANT SELECT ANY TABLE TO SPANKY;

In Oracle8, however, the scope of the any keyword no longer extends into the SYS schema, thereby eliminating unwanted side effects of granting a privilege to a user with the any keyword in order to allow that user to look at a SYS-owned object. This configuration is possible with a new initialization parameter in the init.ora file, O7_DICTIONARY_ACCESSIBILITY. By default, this parameter is set to TRUE to allow the any keyword to extend object privileges over the SYS schema. However, the DBA can set this parameter to FALSE, and instead manage the objects in the SYS schema by granting object privileges for the SYS schema explicitly. Alternately, access to objects in the SYS schema is managed using new roles in Oracle8 designed specifically for the purpose of managing SYS. There are three roles for managing SYS schema objects: the SELECT_CATALOG_ROLE, the EXECUTE_CATALOG_ROLE, and the DELETE_CATALOG_ROLE. These roles manage the ability to select data, execute procedures, functions or packages, and delete data from SYS-owned schema objects. These roles can be granted to users only by user SYS.

Exercises

  1. What changes are made in Oracle8 to handle login as user SYS?
  2. What changes are made in Oracle8 to the scope of the any keyword to minimize its use in managing SYS schema objects?
  3. What new roles are available for the management of SYS schema objects?

Authenticating Users with Enhanced Password Management

Four new features exist in Oracle8 to handle password management more effectively. These features are account locking, password aging and expiration, password history, and password complexity requirements. These new features are designed to make it harder than ever to hack the Oracle8 database as an authorized user without knowledge of the user’s password. This protects the integrity of assigned usernames as well as the overall data integrity of the Oracle database.

Though not required to enable password management in Oracle8, the DBA can run the utlpwdmg.sql script as SYS to support the functionality of password management. This script can be found in the rdbms/admin subdirectory under the Oracle software home directory. This script makes some additions to the DEFAULT profile identified in Chapter 10 for use with password management. These changes will be explained later. When the password management script is run, all default password management settings placed in the DEFAULT profile are enforced at all times on the Oracle8 database. This is unlike other resource limits, which still require that the RESOURCE_LIMIT initialization parameter be set to TRUE before the instance is started.

Account locking works in the following way. Account locking allows Oracle8 to lock out an account when users attempt to log into the database unsuccessfully on several attempts. Maximum allowed number of failed attempts is defined per user or by group. The number of failed attempts is specified by the DBA or security officer in ways that will be defined shortly, and tracked by Oracle such that if the user fails to log into the database in the specified number of tries, Oracle locks out the user automatically. In addition, a time period for automatic user lockout can be defined such that the failed login attempt counter will reset after that time period, and the user may try to log into the database again. Alternately, automatic lockout can be permanent, disabled only by the security administrator or DBA. User accounts can also be locked manually if the security administrator or DBA so desires. In this situation, the only way to unlock the account is manual.

A password is also aged in the Oracle8 database. The DBA or security administrator can set a password to have a maximum lifetime in the Oracle database. Once a threshold time period passes, the user must change their password or they will not be able to access the Oracle database. A grace period can be defined, during which the user must change their password. If the time of the grace period passes and the user doesn’t change their password, the account is then locked and only the security administrator can unlock it. A useful technique for creating new users is to create them with expired passwords, such that the user enters the grace period on first login and must change their password during that time.

A potential problem arises when users are forced to change their passwords. Sometimes users try to "fool" the system by changing their expired password to something else, then immediately changing the password back. To prevent this, Oracle8 supports a password history feature that keeps track of recently used passwords and disallows their use for a specified amount of time or number of changes. The interval is defined within the user profile, and information on how to set it will be presented shortly.

Finally, and perhaps most important to the integrity of an Oracle user’s account, is the feature of password complexity verification. There are many commonly accepted practices in the area of creating a password, such as making sure it has a certain character length, that it is not a proper name or word in the dictionary, that it is not all numbers or all characters, and so on. Too often, however, users don’t heed these mandates and create passwords that are easy to decode using any of a number of products available for decoding encrypted password information. To prevent users from unwittingly subverting the security of the database, Oracle8 supports the automatic verification of password complexity with the use of a PL/SQL function to be applied during user or group profile creation to prevent users from creating passwords of insufficient complexity. The checks provided by the default function include making sure the minimum password length is four characters and not the same as the username. Also, the password must contain at least one letter, number, and punctuation character, and the password must be different from the previous password defined by at least three characters.

If this level of complexity verification provided by the given PL/SQL function is not high enough, a PL/SQL function of sufficient complexity may be defined by the organization, subject to certain restrictions. The overall call syntax must conform to the details in the following code listing. In addition, the new routine must be assigned as the password verification routine in the user’s profile or the DEFAULT profile. In the create profile statement, the following must be present: password_verify_function user_pwcmplx_fname, where user_pwcmplx_fname is the name of the user-defined password complexity function. Some other constraints on the definition of this function include that an appropriate error must be returned if the routine raises an exception; if the verification routine becomes invalid, an appropriate error should be returned; and that the verification function will be owned by SYS and used in system context. The call to the PL/SQL complexity verification function must conform to the following parameter passing and return value requirements:

USER_PWCMPLX_FNAME
( user_id_parm IN VARCHAR2(30),
new_passwd_parm IN VARCHAR2(30),
old_passwd_parm IN VARCHAR2(30)
) RETURN BOOLEAN;

To show the coding used in a password complexity function, the following example is offered. This example is a simplified and modified block of code similar to the password verification function provided with Oracle8. The function will check three things: that the new password is not the same as the username, that the new password is six characters long, and that the new password is not the same as the old one. When the DBA creates a username, the verification process is called to determine if the password is appropriate. If the function returns TRUE, then the DBA will be able to create the username. If not, the user creation will fail. This example is designed to give you some groundwork for coding your own password complexity function; bear in mind, however, that the function is greatly simplified for example purposes only in the following:

FUNCTION my_pwver (
x_user IN VARCHAR2(30),
x_new_pw IN VARCHAR2(30),
x_old_pw IN VARCHAR2(30)
)RETURN BOOLEAN IS
BEGIN
IF LENGTH(x_new_pw) < 6 THEN
RAISE_APPLICATION_ERROR(-20001, ‘New password too short.’);
ELSIF x_new_pw = x_user THEN
RAISE_APPLICATION_ERROR(-20002, ‘New password same as username’);
ELSIF x_new_pw = x_old_pw THEN
RAISE_APPLICATION_ERROR(-20003, ‘New password same as old’);
ELSE
RETURN(TRUE);
END IF;
END;

Exercises

  1. Define and describe the four new features for user account protection in Oracle8.
  2. What process is used to enable account protection?
  3. On what feature of Oracle used to manage resource usage do the new account protection features depend?

Creating Profiles and Users with Password Management

Creating user profiles has already been discussed in Chapter 10. Oracle7 uses profiles to limit resource usage in the database, a feature that is only enforced when the RESOURCE_LIMIT initialization parameter is set to TRUE. In Oracle8, there are several new options that are specified with user profiles for password administration that are always enforced. These options include failed_login_attempts, password_life_time, password_reuse_time, password_reuse_max, password_lock_time, and password_grace_time. After the utlpwdmg.sql script is run, these options will have default values specified for them. The DBA can specify new options using num to define time in days or number of attempts for the option. The other password option specified is the password_verify_function, for which a function_name value is defined. Alternately, the unlimited or default keywords can substitute for a num or function_name value on these options. A more complete explanation of each option is listed below, along with its default value:

failed_login_attempts Number of unsuccessful attempts at login a user can make before account locks. Default is 3.
password_life_time Number of days a password will remain active. Default is 60.
password_reuse_time Number of days before the password can be reused. Default is 1,800 (approx. 5 years).
password_reuse_max Number of times the password must be changed before one can be reused. Default is unlimited.
password_lock_time Number of days after which Oracle will unlock a user account locked automatically when the user exceeds failed_login_attempts. Default is 1/1,440 (1 minute).
password_grace_time Number of days during which an expired password must be changed by the user or else Oracle permanently locks the account. Default is 10.
password_verify_function Function used for password complexity verification. Default is called verify_function( );

In addition to assigning a user profile that takes advantage of the preceding features, the DBA or security administrator can perform several password management operations with the create user or alter user statements. These operations include manually locking or unlocking the user account with the alter user account lock or alter user account unlock statements, respectively. On username creation, or at any time later, the DBA or security administrator may expire a password with the create user password expire or alter user password expire statements. Some examples for the usage of these statements are included in the following example:

ALTER USER spanky ACCOUNT UNLOCK;
ALTER USER athena ACCOUNT LOCK;
ALTER USER dinah PASSWORD EXPIRE;

CREATE USER stacy IDENTIFIED BY attorney
DEFAULT TABLESPACE users_01
PASSWORD EXPIRE;

Exercises

  1. Identify and describe the options that are included in the DEFAULT user profile to support password management? What must be done by the DBA in order to enforce these resource limits?
  2. What are the options in the create user or alter user statements that are used to manage passwords and user accounts?

Using Dictionary Views for Password Management

There have been some changes to existing dictionary views and some additional views created in the Oracle database data dictionary to support the use of password management. The DBA_ and USER_USERS dictionary views have the following new columns on them for password management: ACCOUNT_STATUS, which may be locked, open, or expired; GRACE_DATE, which identifies the date by which the user must change their password or the account will be locked; LOCK_DATE, which is the date the account was locked (NULL for open accounts); and EXPIRY_DATE, which tells the date for account expiration.

The DBA_PROFILES view has been changed to show information about the user profile parameters. The column added is RESOURCE_TYPE, which identifies if the resource is a kernel or password resource. Also, a new view has been created for the Oracle8 data dictionary—USER_PASSWORD_LIMITS--which has the two columns, RESOURCE_NAME and LIMIT, to show both the name of the password resource and the limit defined for it.

TIP: Some Oracle tools have been changed to support password expiration, including Server Manager (line mode) and SQL*Plus.

Exercises

  1. What are the names of the dictionary views that are changed to support password management? Which view has been added to support password management?
  2. What tools have been modified to support password management?

Migration to Oracle8

In this section, you will cover the following topics on migration to Oracle8:

Using the migration utility
Migrating from Oracle7 to Oracle8

Understanding how to migrate from Oracle7 to Oracle8 is perhaps the most important feature that the OCP Exam 5 can present. This section is designed to identify the concepts and steps required for converting Oracle7 applications to Oracle8, and to identify the tools available for the purpose of migration from Oracle7 to Oracle8.

Using the Migration Utility

By now, you should have a clear idea about the new features in Oracle8, and perhaps even some idea of how to incorporate these new features into your existing Oracle applications. Now, the features of the migration process are covered. In order to make room for the new features provided by Oracle8, there are many changes that are made to the data dictionary, both in terms of what the data the dictionary contains and the structure of the dictionary. Several new views are added, while others are changed. In addition, there are new features in the underlying physical structure of the Oracle8 database, the control files, redo logs, and datafiles. In order for the migration to be successful, these things must be changed. The migration process is designed to handle all of these things.

For your part as the DBA handling migration, you need to meet with people to define the following roles and responsibilities. The DBA will perform the actual migration, first backing up the Oracle7 database, then executing the migration to Oracle8, and then backing up the Oracle8 migrated database. The application developers will review the differences between Oracle7 and Oracle8 and ensure the applications are compliant. A schedule should be developed by the DBA in conjunction with the application developers and the users who will be involved with acceptance testing to ensure that critical functionality is maintained. All of these things will move smoothly with the presence of a test plan.

The migration of the Oracle7 database to Oracle8 is designed to be as easy as possible with the presence of the Oracle8 migration utility. This utility is designed to be automatic, fast, and easy on storage requirements and DBA intervention. It migrates an Oracle database by rebuilding the data dictionary in the SYSTEM tablespace and updating the structure of the datafile and the header blocks, but not the data in the database itself. Recall in the discussion of new ROWID formats in Chapter 22 that data stored in the database in Oracle7 restricted ROWID format must be migrated by the developer or the DBA. Oracle8 migration utility does not handle this. In addition, the Oracle8 migration utility is not designed to migrate databases from Oracle8 backward to Oracle7. A separate migration tool will be available for upgrade to future releases of Oracle8 as those new releases are made available.

Migration of the Oracle8 database can be handled in other ways as well. First, Oracle8 database migration can be managed using IMPORT/EXPORT, with the benefit of being able to migrate specific parts of the database independently. In contrast, the Oracle8 migration utility migrates the entire database at once. IMPORT/EXPORT also allows the DBA to migrate an Oracle8 database back to Oracle7, and compress data in the database as well. The database can also be reorganized into different tablespace configurations with IMPORT/EXPORT. However, there are disadvantages with this method. For one, IMPORT/EXPORT require additional space for storage of Oracle data in the intermediate binary format. Also, the database conversion might take many hours or days, requiring advanced scheduling and downtime.

Another method that can be used for Oracle7 migration to Oracle8 is to perform a table copy using the SQL*Plus copy or the create as select statement, as covered in Unit I. This method also allows the DBA to migrate specific parts of the database at a time, and also allows the DBA to perform database and tablespace reorganization. And it has the added advantage of allowing release migration. However, this method is even slower than IMPORT/EXPORT, and both the Oracle7 and Oracle8 databases must be available at the same time and for the duration of the migration activity.

Exercises

  1. What are the basic features of an Oracle8 database migration? What are the responsibilities of the DBA, the application developers, and the users?
  2. What is the migration utility? What are two other methods for conducting database migration from Oracle7 to Oracle8? What are the advantages and disadvantages for each?

Migrating from Oracle7 to Oracle8

This section covers the process of migrating from Oracle7 to Oracle8. Before conducting database migration, ensure the following aspects are complete. Everyone involved should be familiar with Oracle8’s new features to the extent they need to be. The migration method should be planned and documented, along with the test plan for ensuring that the Oracle8 database is complete and a plan for migrating back to Oracle7 if there is a problem. If possible, test the migration strategy itself, perhaps by first migrating a test or development environment, then the production environment. Preparation of the Oracle7 database is important—use the shutdown normal procedure and execute a full offline backup. The overall database migration should consist of the following steps.

Step 1: Prepare for Migration

The first part of this step is to understand the new features of Oracle8. This is the step you are engaged in by obtaining OCP certification in Oracle8. You will need to know how Oracle8 relates to the particulars of your organization’s database. After understanding the overall impact, you must decide how to conduct the migration, either with the migration utility, IMPORT/EXPORT, or table copies. Finally, identify issues that represent actions either for you, the developers, or the users. These actions include changing object names because they contain new reserved words, identifying and changing applications that use parts of the data dictionary that may have changed, determining migration strategy for columns of type ROWID, initialization parameter changes, and organizational mandates for password management.

Step 2: Make Room on Disk

There are new requirements for Oracle8with respect to storage, memory, and processing power. First, Oracle8 requires three times as much disk space as Oracle7, and a minimum of 16M of RAM--with 32M recommended for organizations using Oracle8 in conjunction with Oracle client/server development tools. More details about these and other platform requirements can be found in operating-system-specific documentation for Oracle8. As mentioned in the previous discussion, there may be additional space requirements during the database migration, depending on which migration tool you use. The CHECK_ONLY command line parameter in the Oracle8 migration utility can be used to identify if enough space is available for storing both versions of the data dictionary in the SYSTEM tablespace. The IMPORT/EXPORT migration option requires additional space to store Oracle data in the intermediate binary format, depending on how much data is exported as well as space in the Oracle7 database. The create table as select statement or copy table command requires the same amount of disk space to store the table in both databases.

Step 3: Develop the Test Plan

This step is critical to determine the success of both your migration strategy and your migration results. There are several different categories of tests you may want to develop as part of your test plan strategy. The test of the migration strategy should involve creating a test database out of a portion of your target database. You may also want to develop some sort of minimal acceptance test to perform after the migration is complete to ensure that none of the Oracle7 database functionality or components were lost during the migration. However, don’t expect to detect any serious problems with this strategy—a functional test of all components individually in the database environment and an integration test to identify how the components work together in the Oracle8 environment would be better for this purpose. Finally, to determine if Oracle8 adds value to the databases of the organization, some performance and stress testing should be planned to determine if SQL runs faster, if the database can withstand normal usage, and other things.

Step 4: Maintain the Source

In rare situations, you may find that the Oracle8 migration must be backed out in favor of the Oracle7 version of your organization’s data, and that further analysis is required. To make this backout possible, the DBA should ensure that a complete offline backup is taken before migration begins of the Oracle7 database, its RBMS software, parameter files, and all other files.

Step 5: Perform the Migration

In this step, the actual migration of the database is performed. First, the DBA should delete parameters from the current init.ora file that are obsolete in Oracle8. Next, the Oracle8 migration utility can be used for database migration or, alternately, migration can be done with the IMPORT/EXPORT or copying of tables. The Oracle8 executable software must be installed in order to complete the migration. If table copies are used, the DBA should ensure the appropriate links are made between the Oracle7 and Oracle8 database.

INSTALL MIGRATION UTILITY

Use of the migration utility is as follows. The DBA must install the migration utility with the ORAINST utility found in the Oracle8 software distribution. Software for the migration utility is installed in the Oracle software home directory and the bin and dbs directories under that directory. At the time of installation, there will be an option to select the utility to install. Select the migration utility O7 -> O8 option for installation. This installation will add some new files that are required for migration and to prepare the new environment.

RUN MIGRATION UTILITY

There are several parameters required for running the migration utility. The following list of parameters are for use with the migration utility during database migration. At the operating system prompt, the DBA issues the mig command, followed by these parameters in the format PARAMETER=value:

CFILE Identifies the database control file to the migration utility.
PFILE Identifies the database parameter file to the migration utility.
CNVFILE Identifies the convert.ora file to the migration utility if standard naming is not used.
MIGFILE Identifies the migrate.bsq file to the migration utility if standard naming is not used.
DBNAME The name of the database to be converted.
NEW_DBNAME The new name of the database after conversion, if desired.
MULTIPLIER Identifies the multiplication factor for increasing cluster index size.
SPOOL Identifies an output file for documenting the migration utility run time.
NO_SPACE_TIMECHECK Do not check for space and time conditions in the Oracle7 to Oracle8 migration.
CHECK_ONLY Do not perform the migration--only check to make sure there is enough space in the Oracle7 database for all database objects to be converted.

INSTALL ORACLE8 SOFTWARE

As part of migration, the DBA installs the Oracle8 software with the Oracle8 installer provided as part of the migration utility. To avoid creating a new instance and database as part of the Oracle8 software installation, the DBA must select the Install/Upgrade option.

PREPARE ORACLE8 DATABASE

To prepare the database for conversion, the DBA must do several things. First, the Oracle7 control files must be deleted. The conversion will build new Oracle8 control files for the database. The DBA should also verify the filesystem layout is correct for Oracle8 software and database files, including datafiles and redo logs. The following parameters in the init.ora file must be changed as well. Set the TRANSACTIONS_PER_ROLLBACK_SEGMENT to 21 or less, and set COMPATIBLE to 8.0.0.0.0 or remove it.

EXECUTE ORACLE8 CONVERSION

There are some things that must be executed within Server Manager. First, the DBA should start Server Manager in line mode. The DBA should then connect as INTERNAL and start but not mount the instance, and leave the database closed. Then, the alter database convert command should be issued. If some failure has occurred prior to this step, do NOT issue this command. Instead, restore the Oracle7 database as it existed preconversion and start again. The following code block illustrates the commands to issue from Server Manager:

CONNECT INTERNAL
STARTUP NOMOUNT
ALTER DATABASE CONVERT;

COMPLETE ORACLE8 CONVERSION

After converting the database, the DBA should open it with the alter database open resetlogs command. The DBA should then run the following scripts to complete conversion of the Oracle8 database:

cat8000.sql Can be found in the rdbms/admin directory under the Oracle software home directory. This script converts SYSTEM tables from Oracle7 to Oracle8.
catalog.sql Also found in the rdbms/admin directory under the Oracle software home directory. This script has the same function in Oracle8 as it does in Oracle7—it creates the data dictionary views.
catproc.sql Also found in the rdbms/admin directory under the Oracle software home directory. This script has the same function in Oracle8 as it does in Oracle7—it enhances the data dictionary and executes other scripts to support PL/SQL.

There may be other scripts the DBA needs to run at this time to support other options on the Oracle database. The DBA should identify the appropriate scripts and run them at this time. After identifying and executing the appropriate scripts, the DBA should drop the MIGRATE user created as part of Oracle8 database migration, as well as any objects created in this user’s schema, with the drop user MIGRATE cascade statement.

Step 6: Back Up, Test, Modify, Open

Following the conversion to Oracle8, the DBA should shut down the database normally and take a full backup. Then, the DBA should make the database available for users and developers to test the effects of the conversion on the online and batch applications. The DBA may need to change some connectivity options such as values in tnsnames.ora, or the developers may need to make these changes in the applications themselves. Once it is determined that the migration works properly, the DBA may need to convert database administration scripts used to manage the instance. This may happen over a period of time following the conversion, or even before the conversion. If there is a problem with the database conversion, the DBA will need to correct the problem, and perhaps perform the migration from Oracle7 again. This potential underscores the importance of taking a backup before and after migration.

Exercises

  1. What are the six steps for database migration from Oracle7 to Oracle8?
  2. Identify some of the parameters that can be used when running the migration utility? Which parameter helps to determine if there is enough space in the Oracle7 database to make the conversion?
  3. What statement is issued in Server Manager to convert the database from Oracle7 to Oracle8? What SQL script is run to convert SYSTEM tables to Oracle8 after database conversion? What must happen to the MIGRATE user?

Chapter Summary

This chapter covers three important areas of understanding for Oracle8. These areas include the changes to networking that have been made by Net8 and the new security options available for network communication using Oracle databases over unsecured network connections. In addition, the new Oracle8 internal security options for managing the SYS schema and user accounts are presented. The final, and perhaps most important, area of Oracle8 presented in this chapter is the steps and factors required for migrating to Oracle8 from Oracle7. These areas are important both for passing OCP Exam 5 and for the greater purpose of using Oracle8 in your organization.

The first area is enhancements to networking. Oracle introduces the use of many new features with the Net8 product, designed to build on the foundation for networking that was established with SQL*Net in Oracle7. A higher degree of support for foreign data sources such as Rdb or Oracle Express has been integrated into Net8, along with compliance for ODBC level 2 support of stored procedures. Net8 is also designed to provide improved scalability by introducing a new component called Connection Manager, which can be used in conjunction with the multithreaded server (MTS) architecture (although Connection Manager is an independent product). This feature is designed to concentrate many user connections to the database into one physical transport. This feature reduces network traffic while supporting a substantially higher number of users. The initial user connection requires its own physical transport with the server, while subsequent communication can take place within the Connection Manager. User connections are managed more tightly with the use of connection pooling and multiplexed messages within Connection Manager.

This utility also has the capacity to operate in conjunction with the enhanced security options available in Oracle8. There are some significant enhancements in the Oracle8 network security architecture with the introduction of the Advanced Networking Option and the Oracle Security server. Advanced Networking option allows Oracle to work in conjunction with the latest technologies available for network security, such as digital signatures and external authentication. The Oracle Security server provides third-party authentication based on public/private-key encryption within the Oracle network. Other areas of enhancement are changes made to the listener process to support N-tier network configuration supporting asynchronous communication between clients and servers over large networks. However, in recognition of smaller organizations and those organizations not ready to deploy advanced networking, Net8 allows the DBA to configure it to use TNS raw transport to bypass many networking features, allowing unsecured two-tier client/server applications to operate and communicate faster.

To improve configuration of the Net8 Oracle network, Oracle has implemented advanced use of Oracle Names servers. These servers are designed to allow other services within the Oracle network to register on a Names server automatically. Clients connecting to the Oracle network can then find those services simply by referring to the Names server for assistance. The client can identify what Names servers are available by looking in the sdns.ora file. The Names server can also carry client profile information so that new clients needn’t be configured onsite—they simply download their default configuration from the Names server and confirm those defaults with the user. Many new features for Net8, including Net8 itself and the Oracle Security server, offer an applications programming interface (API) so that developers can integrate the use of the new features into their applications. Oracle Trace can be used in association with Net8 to diagnose and resolve network connectivity issues. For those organizations not ready to deploy Net8 advanced security, the Names server, and other Net8 features, the native hostname adapter can be used to support access to Oracle via TCP/IP domain names and open security.

In addition to the Oracle Security server and external network security options working in conjunction with the Advanced Networking Option, Oracle8 offers some advancements to internal security for the SYS schema and user accounts. Oracle recommends DBAs no longer connect as internal without having the sysdba privilege. The DBA must also be part of the OSDBA group if external networking is used, or the password for SYS provided must match what is in the password file if Oracle password authentication is used. The any keyword used to grant maximum scope for object privileges no longer includes access to objects in the SYS schema if the new O7_DICTIONARY_ACCESSIBILITY initialization parameter is set to FALSE. New roles are available for managing access to objects in the SYS schema, called SELECT_CATALOG_ROLE for select access to SYS objects, EXECUTE_CATALOG_ROLE for execute privileges on stored procedures owned by SYS, and DELETE_CATALOG_ROLE for delete privileges on SYS-owned objects like the AUD$ table.

User account security has also been enhanced with the implementation of advanced password management. User accounts can now be locked to prevent unauthorized use. Passwords can now be set to expire after a period of time, and a history of passwords can be kept in order to prevent password reuse. Finally, passwords can be verified for sufficient complexity. Password management can be implemented with the execution of the utlpwdmg.sql script. Password management is handled with user profiles and with create user and alter user statements. Unlike resource limits, which are enforced only when the RESOURCE_LIMIT initialization parameter is set to TRUE, password limits in user profiles are always enforced.

Account locking can be accomplished manually by the DBA or the security administrator with the alter user account lock statement and unlocked with the alter user account unlock statement. Alternately, Oracle can automatically lock a user account if someone unsuccessfully attempts to log in more times than allowed by a threshold set by the failed_login_attempts clause set for the user profile. The duration of automatic lockout can be indefinite or it can be for a limited period of time defined by password_lock_time. A password will expire after a period of time as defined in the profile by password_life_time. After a password has expired, the user must change their password within a period of time defined by password_grace_time. If this time period expires before the user changes their password, the user account is locked. Other options are specified in the create profile statement, as defined in the chapter.

Password complexity is determined with the use of a stored PL/SQL procedure. One is provided in Oracle8, called verify_password( ). It verifies that the password created for a user is at least four characters long and contains one alphabet, one number, and one punctuation character. An organization can create its own password verification routine as long as it conforms to certain formats and is defined for use in the user profiles. More about these requirements is defined in the chapter. There are several new data dictionary views available in Oracle8 to support the use of password management. Columns have been added to the USER_ and DBA_USERS views, as well as to the DBA_PROFILES view, and a new view called USER_PASSWORD_LIMITS has been added to the data dictionary. Finally, some tools have been enhanced to support password management, including Server Manager and SQL*Plus.

Finally, migration to Oracle8 is covered in this chapter. The migration to Oracle8 can be performed in three ways. The first is using the Oracle8 migration utility. This tool converts existing Oracle7 databases to Oracle8 and is designed to run quickly, even on large databases. However, the migration utility only allows the DBA to convert the database up to Oracle8, not downward from Oracle8 to Oracle7. It also provides no support for database reorganization. An alternative to using the migration utility is to install Oracle8 separately and migrate the Oracle7 database using EXPORT/IMPORT. Though it requires additional space to store data from the database in intermediate binary format and more time to perform the conversion, EXPORT/IMPORT also allows the DBA to convert an Oracle8 database back to Oracle7 in the event of migration problems, and allows for partial database migration as well. A third alternative is to copy tables from Oracle7 databases to their Oracle8 counterparts. This option also allows downward migration, but takes longer, requires a great deal of space, and requires both the Oracle7 and Oracle8 versions of the database to exist simultaneously and for a connection to exist between them.

There are roles and responsibilities required of everyone in converting the Oracle8 database. The DBA is responsible for converting the database components, while the application developers are required to understand the impact that Oracle8 might have on the applications. Finally, the users should be involved in testing the applications to ensure no functionality is lost. There are six steps for migrating from Oracle7 to Oracle8. They are preparing to migrate, securing required resources, building a test plan, preserving the source database, migrating the source database, and finalizing the migration of the source database.

The actual process of migrating the database consists of several subtasks. They include installing the migration utility (if the DBA is using the migration utility), running the migration utility, and installing Oracle8 software. After these tasks, the DBA must prepare the database by removing the control file and altering the TRANSACTIONS_PER_ROLLBACK_SEGMENT to 21 and setting COMPATIBLE to 8.0.0.0.0 or blank. The actual conversion of the database is done next by connecting as internal, starting the instance using the startup nomount statement, and issuing the alter database convert statement. When conversion is over, the DBA should run cat8000.sql, catalog.sql, and catproc.sql, and then issue the drop user MIGRATE cascade statement. After running the migration, the DBA can back up the new Oracle8 database, execute any testing plans to ensure the migration went smooth, and then convert any database administration scripts to use Oracle8 new features. Connectivity to the database may have to be changed by modifying the tnsnames.ora file.

Two-Minute Drill

Net8 networking offers enhancements in the areas of scalability, connectivity, security, performance, and configuration.
Scalability improvements are provided with the use of Connection Manager to concentrate and multiplex multiple user connections through a single physical transport. The number of sockets to support user connections on a database server is also minimized with connection pooling.
Connectivity is improved with support for ODBC level 2 driver for use of stored procedures and with support for foreign data sources like Rdb and Oracle Express OLAP.
Security is improved with the use of Oracle Security server global users and roles, one-time login, public/private-key encryption, support for security servers available from other vendors, digital signatures, and the Advanced Networking Option.
Network configuration is simplified with the use of Oracle Names servers to maintain information about the services available on the Oracle network and to support client profile distribution to manage client configuration centrally.
New features for Oracle8 internal security over the SYS schema and password management of password information are also available.
The new password features include account locking, password aging, password history, and password complexity verification.
New options have been added to the user profile to support password management. These features are enforced even when resource limits are not.
The locking and unlocking of accounts is handled with the alter user statement.
The USER_, DBA_USERS, and DBA_PROFILES views have been changed to support password management, and the USER_PASSWORD_LIMITS view has been added in support of password management as well.
There are six steps to migrate an Oracle database: prepare to migrate, secure necessary disk resources and memory, develop the test plan, back up the Oracle7 database, migrate to Oracle8, and backup the new database.
There are three tools available for migrating the Oracle database to Oracle8: the migration utility, IMPORT/EXPORT, and copying tables.