M.Sc. (CS)/ (SE) SY Oracle Database Administration
UNIT V
Backup & Recovery & Networked ORACLE
5.1 Capabilities
• There are three standard methods of backing up an Oracle database: export, offline backups, and online backups.
• An export is logical backup of the database; the other two backup methods are physical file backups.
• A robust backup strategy includes both physical and logical backups. In general, production databases rely on physical backups as their primary backup method, while logical databases serve as the secondary method. For development databases and for small data movement processing, logical backups offer a feasible solution.
5.2 Logical Backups
• Logical backup of the database involves reading a set of database records and writing them to a file. These records are read independently of their physical location. In Oracle, the Export utility performs this type of database backup. To recover using the file generated from an export, Oracle’s Import utility is used. 5.2.1 The Export / Import Process
• Oracle’s Export utility queries the database, including the data dictionary, and writes the output to a binary file called an export dump file. We can export full database, specific users, or specific tables. During exports, we may choose whether or not to export data dictionary information associated with tables, such as the grants, indexes, and constraints associated with them. The file written by Export will contain the commands necessary to completely re-create all of the chosen objects and data.
• As of Oracle9i, we can perform tablespace-level exports to export all of the objects contained in a tablespace. Any indexes defined on the exported tables would also be exported.
1 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• Tablespace-level exports use the tablespaces clause of the Export utility. Once data has been exported, it may be imported via Oracle’s Import utility. The import utility reads the binary export dump file created by Export and executes the commands found there.
• We can import either all part of the exported data. If we import entire export dump file from a Full export then all of the database objects-including tablespaces, datafiles, and users-will be created during the import. However it is often useful to pre-create tablespaces and users in order to specify the physical distribution of objects in the database.
2 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.3 Physical Backups
• Physical backups involve copying the files that constitute the database. These backups are also referred to as file system backups since they involve using OS file backup commands. Oracle supports two different types of physical file backups: the offline and online backup (also known as the cold and hot backups respectively).
5.3.1 Offline Backup
• Consistent offline backups occur when database has been shut down normally (i.e. not due to instance failure). While the database is offline, the following files should be backed up:
⮚ All datafiles
⮚ All control files
⮚ All online redo logs
⮚ The init.ora and spfile.ora( optional)
• It is easiest way to backup the datafiles if the database file architecture uses a consistent directory structure (OFA). Having all of these files backed up while the database is closed provides a complete image of the database as it existed at the time it was closed. The full set of these files could be retrieved from the backups at a later date and the database would be able to function.
• It is not valid to perform a file system backup of the database while it is open unless an online backup is being performed. Offline backups that occur following database aborts will also be considered inconsistent and may require more effort to use during recoveries.- if they are usable.
3 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.3.2 Online Backups
• We can use online backups for any database that is running in ARCHIVELOG mode. In this mode, the online redo logs files are archived, creating a log of all transaction within the database. Oracle writes to the online redo log files in a cyclical fashion: after the first log file, it begins writing to the second until that one fills, and then begins writing to the third. Once the last online redo log file is filled, the LGWR (Log Writer) background process begins to overwrite the contents of the first redo log file.
• When Oracle is run in ARCHIVELOG mode the ARCH (Archiver) background process makes a copy of each redo log file before overwriting it. These archived redo logs files are usually written to a disk device. The archived redo log files may also be written directly to a tape device, but this tends to be very operator intensive. Most production databases, particularly those that support transaction processing applications, must be run in ARCHIVELOG mode. We can perform file system backups of a database while that database is open, provided the database is running in ARCHIVELOG mode.
• An online backup involves setting each tablespace into a backup state, backing up its datafiles, and then restoring the tablespace to its normal state. The database can be fully recovered from an online backup, and can, via the archived redo logs, be rolled forward to any point in time. (Means after database crash, running transaction’s data will be available in archived redo log file.)
• When the database is then opened, any committed transactions that were in the database at that time will have been restored, and any uncommitted transaction will have been rolled back. While the database is open, the following files can be backed up:
⮚ All datafiles
⮚ All archived redo log files
⮚ One control file, via the alter database
4 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• Online backup procedures are very powerful for two reasons.
⮚ First, they provide full point-in-time recovery.
⮚ Second, they allow the database to remain open during the file system backup.
• Even databases that cannot be shutdown due to user requirements can still have file system backups. Keeping the database open also keeps the System Global Area (SGA) of the database instance from being reset during database startups. Keeping the memory from being reset will improve the database’s performance since it will reduce the number of physical I/Os required by the database.
5 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.3 Implementations
5.3.1 Implementations of export
• Export: The Export utility has four levels of functionality: Full mode, Tablespace mode, User Mode, and Table mode. In Full mode, the full database is exported. The entire data dictionary is read and the DDL needed to re-create the full database is written to the export dump file. This file includes creation commands for all tablespaces, all users, and all of the objects, data, and privileges in their schemas. In Tablespace mode, all of the objects contained in the specified tablespace will be exported including the definition of indexes on the contained objects, even if they are in another tablespace. In user mode, a user’s objects are exported, as well as the data within them. All grants and indexes created by users on the user’s objects are also exported. Grants and indexes created by users other than the owner are not exported. In Table mode, a specified table is exported. The table’s structure, indexes and grants are exported along with or without its data.
• Table mode can also export the full set of tables owned by a user (by specifying the schema owner but no table names). We can also specify partitions of a table to export. The run-time options that can be specified for Export are listed as shown below:
6 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
7 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• A number of the parameters conflict with each other or may result in inconsistent instructions for Export.
• For e.g. setting full=y and owner=hr would fail, since full parameter calls for Full export, while the owner parameter specifies a User export.
• We can display the Export parameters online via the following command: exp help=y
⮚ Examples of export command is as shown below
8 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.3.2 Implementations of Import
• Import: The import utility reads the export dump file and runs the commands stored there. Import may be used to selectively bring back objects or users from the export dump file. We can run Import either interactively or via command files. The run-time options that can be specified for import and their default values are shown below
9 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• A number of the import parameters conflict with each other or may result in inconsistent instructions for import. For e.g. setting full=y and owner=hr would fail, since the full parameter calls for a Full import , while the owner parameters specifies a User import.
• The destroy parameter is very useful for DBAs who run multiple databases on a single server. Since Full database exports record the entire data dictionary, the tablespace and datafile definitions are written to the export dump file.
• The datafile definitions will include the full path name for the files. If this export dump file is used to migrate data to a separate database on the same server, a problem may arise.
Some examples of import command are
10 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.4 Implementing Offline Backups
• An offline backup (sometimes called a cold backup) is a physical backup of the database files, made after the database has been shut down via either shutdown normal, a shutdown immediate, or a shut transactional.
• While the database is shut down, each of the files i.e. actively used by the database is backed up. These files provide a complete image of the database as it existed at the moment it was shut down.
• The following file should be backed up during cold backups:
⮚ All datafiles
⮚ All control files
⮚ All online redo logs
• We can optionally choose to backup the database initialization parameter file, particularly if the backup will serve as the basis for a disaster recovery process. • To simplify the backup process, use a consistent directory structure for the datafiles (OFA).
• If we use the OFA , our backup commands will be greatly simplified. The following listing shows a sample example of UNIX tar command which is used here to backup files to a tape driver called /dev/tape.
• Because the directory structure is consistent the following command will backup database’s datafiles, redo log files, and control files.
• The -cvf flag creates a new tar saveset.
11 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.5 Implementing Online Backups
• Consistent offline backups can only be performed while the database is shut down. However, we can perform physical file backups of a database while the database is open, provided the database is running in ARCHIVELOG mode, and the backup is performed correctly. These backups are referred to as online backups, also known as hot backups. Oracle writes to the online redo log file in a cyclical fashion: after the first log file, it begins writing to the second until that one fills, and then begins writing to the third.
• Once the last online redo log file is filled, the LGWR (Log Writer) background process begins to overwrite the contents of the first redo log file. When Oracle is run in ARCHIVELOG mode the ARCH (Archiver) background process makes a copy of each redo log file before overwriting it. These archived redo logs files are usually written to a disk device. The archived redo log files may also be written directly to a tape device, but this tends to be very operator intensive.
5.5.1 Getting Started
• To make use of the ARCHIVELOG capability, the database must first be placed in ARCHIVELOG mode.
• The following listing shows the steps needed to place a database in ARCHIVELOG mode. First versions prior to Oracle9i, use the Server Manager in place of SQL*Plus.
• To change a database back to NOARCHIVELOG mode, use the following set of commands after shutting down the database:
12 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• A database that has been placed in ARCHIVELOG mode will remain in that mode until it is placed in NOARCHIVELOG mode. The location of the archived redo log files is determined by the settings in the database’s parameter file. The parameters to note in Oracle9i are as follows:
• In this example, the archived redo log files are being written to the directory d:\msc\arch. The archived redo log files will all begin with the letters “arch”, followed by a sequence number.
• For e.g., the archived redo log file directory may contain the following files: arch_170.arc
arch_171.arc
arch_172.arc
• Each of these files contains the data from a single online redo log file. They are numbered sequentially, in the order in which they were created. The size of the archived redo log files varies, but does not exceed the size of the online redo log files.
• If the destination directory of the archived redo log files runs out of space, then ARCH will stop processing the online redo log data and the database will stop itself. • This situation can be resolved by adding more space to the archived redo log file
destination disk or by backing up the archived redo log files and then removing them from this directory.
• In a database i.e. currently running, we can show the current ARCHIVELOG settings (including the destination directory) via the archive log list command within Server Manager. We can also query the parameter settings from the V$PARAMETER dynamic view performance view.
13 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• Although the log_archive_start parameter may be set to TRUE, the database will not be in ARCHIVELOG mode unless we have executed the alter database archivelog command. Once the database is in ARCHIVELOG mode, it will remain in that mode through subsequent database shutdowns and startups until we explicitly place it in NOARCHIVELOG mode via alter database noarchivelog command.
5.5.2 Performing Online Database Backups
• Once a database is running in ARCHIVELOG mode, we can back it up while it is open & available to users. This capability allows round-the-clock database availability to be achieved while still guaranteeing the recoverability of the database. Although hot backups can be performed during normal working hours, they should be scheduled for the times of the least user activity for several reasons.
⮚ First, the hot backups will use Operating System commands to back up the physical files, and these commands will use the available I/O resources in the system.
⮚ Second, while the tablespaces are being backed up, the manner in which transaction are written to the archived redo log files changes. When we put a database in “hot backup” mode, the DBWR process writes all of the blocks in the buffer cache that belong to any file that is part of the tablespace back to disk.
• The command file for a hot backup has three parts:
1. A tablespace-by-tablespace- backup of the datafiles, which in turn consists of ⮚ Setting the tablespace into backup state
⮚ Backing up the tablespace’s datafiles
⮚ Restoring the tablespace to its normal state.
2. Backup of the archived redo log files, which consists of
⮚ Recording which files are in the archived redo log destination directory ⮚ Backing up the archived redo log files, then (optionally) deleting or compressing them.
3. Backup of the control file via the alter database backup controlfile command.
14 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.6 Integration of Backup Procedures
• Since there are multiple methods for backing up the Oracle database, there is no need to have a single point of failure in backup strategy.
• Depending on database’s characteristics, one method should be chosen, and at least one of the remaining methods should be used as a primary backup method. 5.6.1 Integration of Logical and Physical Backups
• Which backup method is appropriate to use as the primary backup method for database.
• When deciding, we should take into account the characteristics of each method:
• Offline backups are the least flexible method of backing up the database if the database is running in NOARCHIVELOG mode. Offline backups are point-in-time snapshots of the database; and since they are physical backup, DBAs cannot selectively recover logical objects (such as tables) from them.
• Although there are times when they are appropriate (such as for disaster recovery), offline backups should normally be used as a fallback position in the event that the primary backup method fails. If the database is running in ARCHIVELOG mode, we can use the offline backups as the basis for a media recovery, but an online backup would normally be more appropriate for that situation. Of the two remaining methods, which one is more appropriate? For production environments, the answer is almost always online backups.
15 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• An online backup, with database running in ARCHIVELOG mode, allows to recover the database to the point in time immediately preceding a system fault or a user error. Using an Export-based strategy would limit us to only being able to go back to the data as it existed the last time the data was exported.
• Consider the size of the database and what objects we will likely be recovering. Given a standard recovery scenario - such as the loss of a disk - how long will it take for the data to be recovered? If a file is lost, the quickest way to recover it is usually via a physical backup, which again favors online backups over exports.
• If the database is small, transaction volume is very low, and availability is not a concern, then offline backups may serve our needs. If we are only concerned about one or two tables, then use Export to selectively back them up.
• However, if the database is large, then the recovery time needed for Export/sImport may be prohibitive (excessive). For large, low transaction environments, offline backups may be appropriate. Regardless of our choice for primary backup method, the final implementation should include a physical backup and some sort of logical backup, either via Export or via replication. This redundancy is necessary because these methods validate different aspects of the database: Export validates that the data is logically sound, physical backups i.e. physically sound. Three sample integration of these methods are shown below:
• As shown in these solutions, a good database backup strategy integrates logical and physical backups. The frequency and type of backup performed will vary based on the database’s usage characteristics.
16 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.7 About Recovery Manager
• Recovery Manager (RMAN) is an Oracle utility that can backup, restore, and recover database files. The product is a feature of the Oracle database server and does not require separate installation.
• RMAN uses database server sessions to perform backup and recovery. It stores metadata about its operations in the control file of the target database and, optionally, in a recovery catalog schema in an Oracle database. The RMAN environment consists of the utilities and databases that play a role in a backup and recovery strategy.
• A typical environment uses the following:
⮚ RMAN executable
⮚ Target database
⮚ Recovery catalog database (optional)
⮚ Media management software (optional)
• Of these components, only the RMAN executable and target database are required. RMAN automatically stores its metadata in the target database control file, so the recovery catalog is optional. Nevertheless, maintaining a recovery catalog is strongly encouraged. If we create a recovery catalog on a separate machine, and if the production machine fails completely, then the catalog has all the restore and recovery data that we need.
5.7.1 RMAN Executable and Target Database
• The RMAN executable is generally located in the $ORACLE_HOME/bin directory on a UNIX system and in ORACLE_HOME\bin in a Windows environment, but the location may vary based on the OS involved. To manually run RMAN, enter following command at the OS prompt.
C:\> rman
RMAN>
• The target database is the database that RMAN is backing up, restoring, or recovering.
17 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.7.2 About the Recovery Catalog
• The RMAN recovery Catalog is a set of metadata that RMAN uses to store information about the target database and its backup and recovery operations. Among other things, RMAN stores information about:
⮚ Backup sets and pieces
⮚ Image copies
⮚ Archived redo logs
⮚ The target database schema
⮚ Persistent configuration settings
• We can access this metadata by issuing LIST, REPORT, and SHOW commands in the RMAN interface, or by using SELECT statements on the catalog views (only if we use a recovery catalog). We can either create a recovery catalog in which to store the repository, or let RMAN store the repository exclusively in the target database control file.
5.7.3 Starting and Exiting RMAN
• RMAN is a client executable that is installed with the Oracle database server. We can connect it to a target database, and then use server sessions on the target database to back up, restore, and recover database files. We have the following options for starting RMAN:
• In both cases, RMAN displays an RMAN> prompt at which we can enter commands or run a command file.
18 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• For example:
• RMAN> @some_command_file.rcv
# runs specified command file
• RMAN can connect to the following types of databases.
• The following syntax shows the most common RMAN command-line options:
19 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• To quit RMAN and terminate the program, type EXIT or QUIT at the RMAN prompt. For example:
RMAN> EXIT
5.7.4 Configuring the RMAN Environment
• To see the configurable parameters, issue a show all command from the RMAN prompt with the target database running. The configurable parameters with their default values are as shown below.
• We can use the command configure to override the default parameters persistently. That is the parameters can be set once & then used for subsequent jobs. One of the parameters we can set is the RETENTION POLICY, the parameter defaults to REDUNDANCY, which defines a fixed number of backups to be retained. Any backups in excess of this number can be deleted. The default value of 1 says that as soon as a new backup is created, the old one is no longer needed & can be deleted.
• The other option for RETENTION POLICY is RECOVERY WINDOW, specified in days, to define a period of time in which point-in-time recovery must be possible. RECOVERY WINDOW is best way to how long backups should be retained, but does not really control how many copies of the backup are created or kept. The REDUNDANCY option enables us to specify a number of backups to be cataloged before any backup is considered to be obsolete.
• There are two commands associated with RETENTION POLICY. The first command is
20 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• This command tells RMAN that there is no retention policy. Backups are never expired using this command. The other command is
• This command resets the RETNTION POLICY value to REDUNDANCY with a value 1. We can configure persistent (determined) settings in the RMAN environment. The configuration setting is done once and is used by RMAN to perform all subsequent operations. Display the preconfigured settings as follows: SHOW ALL;
5.7.5 RMAN Commands & their Usage
• There are many commands that we can use in RMAN to back up, restore, and recover database. The commands are as shown below.
21 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.7.6 Backing Up and Copying Database Files
• Use the BACKUP command to back up files to the configured default device by using the configured channels. For example, the following command backs up the database and all archived logs:
22 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• RMAN stores its backups in backup sets. A backup set is a logical structure that contains one or more backup pieces, which are the physical files containing the data. A backup set usually contains only one backup piece. Only RMAN can create and restore backup sets.
5.7.7 Backing Up Individual Files
• We can also back up individual tablespaces, database files, server parameter files, and backup sets with various options, as in these examples:
• Backup Options RMAN has a number of BACKUP command options that control all aspects of backup set generation.
5.7.8 Restoring and Recovering Database Files
• The RESTORE and RECOVER commands manage all aspects of RMAN restore and recovery.
5.7.8.1 Recovering the Whole Database
• Use the RESTORE DATABASE and RECOVER DATABASE commands on the whole database. Only run these commands when the database is mounted. For example:
23 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.7.8.2 Recovering Individual Tablespaces
• Use the RESTORE TABLESPACE and RECOVER TABLESPACE commands on individual tablespaces when the database is open. Take the tablespace that needs recovery offline, restore and then recover the tablespace, and bring the recovered tablespace online. This example recovers tablespace USERS:
5.7.9 Generating Lists and Reports
• An advantage of using a recovery catalog instead of relying on the subset of information stored in the target database control files is that we can use full complement of parameters available to generate lists of information about backup sets, proxy copies, and image copies contained within the repository. We can generate lists of some of the information even if NOCATALOG has been specified. We can also obtain reports on files that requires backup, have become outdated, or are unavailable.
24 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.7.9.1 Using the list command
• Of the enhancements for Oracle9i RMAN, the commands used for catalog maintenance are LIST, CROSSCHECK, DELETE EXPIRED and CHANGE. There are a number of changes that have been made to improve consistency across these commands, & they all now use similar syntax and the same output formats and status codes when possible. Using the LIST command, we can have output written to either the terminal screen or the message log. We can organize the output BY BACKUP or BY FILE , and we can specify the level of detail to be presented: either VERBOSE (WORDY)or SUMMARY . The list command enables us to view the following information:
⮚ Expired backups and copies within the RMAN catalog
⮚ Datafile backups and copies that can be used in a restore operation. ⮚ Archive logs, backups sets and pieces, control file copies, datafile copies and proxy copies.
• To obtain a list of all of the backups available in the catalog, in the default VERBOSE (wordy)mode, we can issue the following command:
25 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.7.9.2 Using the report command
• The RMAN report is used to perform detailed analysis of the RMAN Repository and present the output to either the user’s terminal or the message log file. • We can also issue the REPORT command from the RMAN.
• We can request to find the following information:
⮚ Files requiring backup.
⮚ Files that have not been backed up for a specific period of time.
⮚ Unrecoverable files.
⮚ Files to delete because they are outdated.
⮚ The physical schema of the database as a previous time.
• A new option, RECOVERY WINDOW, is available for the REPORT NEED BACKUP command and is used to specify a window of time in which the database must be recoverable.
• For e.g. if Retention Policy is such that , we must have backups that are less than three days old, we can issue the following command:
• The result will display any files that need to be backed up because they are more than three days old. We can also use the RECOVERY WINDOW option with the REPORT OBSOLETE command to display files that are obsolete because they are older than the specified window.
26 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.8 Overview of Oracle Net (SQL * Net and Net 8):
• Using Oracle Net distributes the workload associated with database applications. Since many database queries are performed via applications, a server-based application forces the server to support both the CPU requirements of the application and the I/O requirements of the database as shown in the following fig.5.1 (a)
Application Program & database
Server
Fig 5.1 (a) Server based Application
• Using a client/server configuration (also referred to as two tier architecture) allows this load to be distributed between two machines. The first, called the client, supports the application that initiates the request from the database. The back-end machines on which the database resides is called the server. The client bears the burden of presenting the data, while the database server is dedicated to supporting queries, not applications. This distribution of resource requirements is shown in the following fig. 5.1 (b)
Application Program
& Net8/Oracle Net
Database&
Net8/Oracle Net
Network
Client Server
Fig 5.1 (b) Client/Server Architecture
• When the client sends a database request to the server , the server receives and executes the SQL statements i.e. passed to it. The results of the SQL statement, plus any error conditions that are returned, are then send back to the client. The
27 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
client/server configuration needs a fairly robust workstation with a large hard drive (8 GB or more) and large memory requirements (usually 256 MB of RAM). The resources required have caused the client/server configuration to sometimes be called fat-client architecture. The more common, cost effective architecture used with Oracle Net is a thin-client configuration (also referred to as three tier architecture). The application code is housed and executed using Java Scripts on a separate server from the database server.
• The client resource requirements become very low and the cost is reduced dramatically. The application code becomes isolated from the database. Fig 5.2 shows the thin client configuration.
• The client connects to the application server. Once the client is validated, display management code is downloaded to the client in the form of Java Applets. • A database request is sent from the client through the application server to the database server; the database server then receives and executes the SQL statement i.e. passed to it. The results of the SQL statement, plus any error conditions that are returned, are then sent back to the client through the application server. Client Java Applet
Application Program & Net8/Oracle Net Network
Application Server Network
Oracle Net Oracle RDBMS
Fig 5.2 Thin Client Architecture
Oracle Server
28 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• In some versions of the three-tier architecture, some of the application processing is performed on the application server and the rest is performed on the database server. The advantages of a thin-client architecture is that we have low resource requirements and maintenance on the client side, medium resource requirements & central maintenance on the application server, & high resource but lower maintenance requirements on one or more back-end database servers.
• In addition to client/server and thin-client implementations, server/server configurations are often needed. In this type of environment, databases on separate servers shares data with each other. We can then physically isolate each server from every other server without logically isolating the servers. Each server supports clients applications, but it also has the ability to communicate with other servers in the network. This architecture is shown in fig 5.3
Oracle Database & Net8/Oracle Net Oracle Database & Net8/Oracle Net
Server Server
Fig 5.3 Server/ Server architecture
• When one of the server sends a database request to another server, the sending server acts like a client. The receiving server executes the SQL statement that is passed to it, & returns the results plus error conditions to the server.
• Oracle Net allows these architectures to become reality. When run on the clients and servers, Oracle Net allows database requests made from one database (or application) to be passed to another database on a separate server.
• In most cases machines can function both as clients & servers; the only exceptions are operating systems with single-user architectures, such as network appliances. In such cases, those machines can only functions as clients.
29 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• Each object in a database is uniquely identified by its owner & name. For Ex. There will only be one table named EMPLOYEE owned by the user HR; there cannot be two tables of the same name & type within the same schema.
• Within distributed databases, two additional layers of object identification must be added.
⮚ First, the name of the instance that accesses the database must be identified. Next, the name of the server on which that instance resides must be identified. ⮚ Putting together these four parts of the object’s name - its server, its instance, its
owner, & its name – results in a global object name. In order to access a remote table, the table’s global object name must be known. DBAs & application administrators can set up access paths to automate the selection of all four parts of the global object name.
• The foundation of Oracle Net is the Transparent Network Substrate (TNS), which resolves all server-level connectivity issues. Oracle Net relies on configuration files on the client & the server to manage the database connectivity. If the client & server use different communication protocols, the Oracle Connection Manager manages the connections. The combination of Oracle Connection Manager & the TNS allow Oracle Net connections to be made independent of the Operating System & communication Protocols run by each server. Oracle Net also has the capability to send & receive data requests in an asynchronous manner; this allows it to support the shared server (formerly known as the Multithreaded Server MTS) architecture.
30 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
ORACLE Kernel or
Application
ORACLE NET TNS Protocol Specific
driver
Protocol SoftwareFig 5.4 Oracle Net architecture
31 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.9 Connect Descriptors
• The server & instance portions of an object’s global object name in Oracle Net are identified by means of a connect descriptors. A connect descriptors specifies the communications protocols, server name, & instance name to use when performing query. The format for an Oracle Net Connect Descriptor is shown in the following listing. The Ex. Shown here uses the TCP/IP protocol, & specifies a connection to an instance named ORCL on a server named PC1. The keywords are protocol specific.
• In this connect descriptor, the protocol is set to TCP/IP, the server (HOST) is set to PC1, and the port on that host that should be used for the connection is port 1521(which is the Oracle registered port assignment for Oracle Net). The instance name is specified in a separate part of the descriptor as the SID assignment. The structure for this descriptor is consistent across all protocols.
32 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.10 Service Names
• Users are not expected to type in a connect descriptor each time they want to access remote data. Instead, the DBA can set up service names (aliases), which refer to these connect descriptors. Service names are stored in a file called tnsnames.ora. This file should be copied to all servers on the database network. Every client & application server should have a copy of this file. On the server, the tnsnames.ora should be located in the directory specified by TNS_ADMIN environment variable. The file is usually located in a common directory, such as for a Windows NT/2000 server or client, in the \network\admin subdirectory under Oracle software home directory.
• A Sample entry in the tnsnames.ora file is shown in the following listing. This example assigns a service name of ORCL to the connect descriptor given above.
• A user wishing to connect to the ORCL instance on the PC1 server can now use the ORCL service name, as shown below
• The @ sign tells the database to use the service name that follows it to determine which database to log into. If the username & password are correct for that database, then a session is opened there and the user can begin using the database.
• Service names create aliases for connect descriptors, so we do not need to give the service name the as the instance.
33 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.11 Listeners
• Each database servers on the network must contain a listener.ora. The listener.ora file lists the names and addresses of all of the listener processes on the machine & the instances they support. Listener processes receive connections from Oracle Net & Net8 clients
• A listener.ora file has four parts:
⮚ Header Section
⮚ Protocol address list
⮚ Instance definitions
⮚ Operational parameters
⮚ The listener.ora file is automatically generated by the Network Manager (Oracle Net) & Net8 Assistant tools.
• We can edit the resulting file as long as we follow its syntax rules. The following listing shows sample sections of a listener.ora file.
34 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• The first portion of this listing contains the protocol address list –one entry per instance. The protocol address list defines the protocol addresses on which a listener is accepting connections, including an interprocess calls (IPC) address definition section. In this case, the listener is listening for connections to the service identified as ORCL.world as well as any requests coming from the PC1 machine on PORT 1521 using the TCP/IP protocol. The .world suffix is the default domain name for Oracle Net Connections. As of Net8, the default domain name was changed to be a NULL string.
• The second portion of the listing, beginning with the SID_LIST_LISTENER clause, identifies the global database name as defined in the init.ora file for that database, the Oracle software home directory for each instance the listener is servicing, and the instance name or SID. The GLOBAL_DBNAME comprises the database name & database domain. The SID_LIST descriptor is retained for static database registration, backward compatibility with earlier versions, & for use by the Oracle Enterprise Manager. In Oracle 8i & Oracle 9i, databases dynamically register with the listener on database startup.
35 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.12 Using Oracle Net8 Assistant
• In Oracle9i, a new tool, the Oracle Net Configuration Assistant, has been added to replace the Oracle Net8 toolset. The Oracle Net Configuration Assistant performs the initial network configuration steps after the Oracle Software installation & automatically creates the default, basic configuration files.
• The tool has a graphical user interface for configuring the following elements. ⮚ Listener
⮚ Naming Methods
⮚ Local net service names
⮚ Directory usage
• In the Oracle8i, Net8 version, net service name replace the use of service name aliases. For the net service name ORCL on the PC1 server, the net service name maps to a connect server using the following syntax:
• In this example, the service name & the instance name match. However, they can be different names. The client will connect to a server using the net service name just as the client connected using the service name in earlier versions.
36 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• The following fig 5.5 shows the initial screen of the Oracle Net Configuration Assistant.
Fig 5.5 Oracle Net Configuration Assistant Welcome Screen
Configuring the Listener
• Using the Oracle Net Configuration Assistant, we can configure a listener easily & quickly. When we select the Listener Configuration options, we are given choice to add, reconfigure, delete, or rename a listener. After selecting the Add option, the first step is to select a listener name. Fig 5.6 shows the Listener Name Screen with the default listener name, LISTENER, displayed.
37 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
Fig 5.6 Listener Configuration, Listener Name Screen
• After selecting a listener name, we must select a protocol. The default protocol selected is TCP. Fig 5.7 shows the protocol selection screen.
Fig 5.7 Listener Configuration, Select Protocol Screen
38 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• Once a protocol has been selected, we must designate a port number on which the new listener will listen. The default port number is 1571, but we are given option to designate another port. As shown in fig 5.8 the port 1526 has been designated for the new listener.
Fig 5.8 Listener Configuration, TCP/IP Protocol Screen
• The next three screens, not shown here, are a prompt to configure another listener, a request to indicate a listener we want to start, and a confirmation that the listener configuration is completed for this listener.
39 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.13 MultiProtocol Interchange
Data Access across Transfer Protocols
• Oracle's MultiProtocol Interchange can be used with SQL*Net to enable transparent data access across protocols, allowing a client using one protocol to communicate with a server using a different protocol. This way, clients and servers running different network protocols can communicate using only their native protocols. This eliminates the need to purchase and maintain multiple protocol stacks. All of the advanced Oracle7 capabilities, such as basic replication, the advanced replication option, stored procedure calls, and automatic transaction recovery mechanisms, can operate transparently across any number of protocol boundaries.
• Multiple Interchanges can be combined to provide multistage protocol conversion, all transparent to programmers and users alike. Applications simply ask for services by name, and SQL*Net automatically calculates the most efficient route to take through the network and establishes the connection. When the network topology changes, such as when a new server is added to the network, users and applications are completely unaware of the change, because SQL*Net transparently calculates a new route automatically at request time. Where there are multiple possible routes, SQL*Net will use the most efficient route based on high-level weighting provided by the network administrator.
40 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
Client
Application Table 2
Community A Based on
Protocol A Server 2
Server 1
Community B Based on Protocol B
Table 3
Table 1 Server 3
Multi
protocol
Interchange
Community C
Based on
Protocol C
Fig 5.9 Multiprotocol Interchange
41 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.14 Dedicated Server Processes, Oracle Shared Server
• Oracle Database creates server processes to handle the requests of user processes connected to an instance. A server process can be either of the following: • A dedicated server process, which services only one user process
• A shared server process, which can service multiple user processes • Our database is always enabled to allow dedicated server processes, but we must specifically configure and enable shared server by setting one or more initialization parameters.
5.14.1 Dedicated Server Processes
• Following Fig 5.10, Oracle Database Dedicated Server Processes illustrates how dedicated server processes work. In this diagram two user processes are connected to the database through dedicated server processes.
User
ProcessUser
Process
Client
Workstation
Database Server
Program
Interface
Application Code
Oracle
Server Code
Dedicated
Server Process
Application Code
Oracle
Server Code
System Global Area Fig 5.10 Oracle Database Dedicated Server Processes
42 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• In the following situations, however, users and administrators should explicitly connect to an instance using a dedicated server process:
⮚ To submit a batch job (for example, when a job can allow little or no idle time for the server process)
⮚ To use Recovery Manager (RMAN) to back up, restore, or recover a database • To request a dedicated server connection when Oracle Database is configured for shared server, users must connect using a net service name that is configured to use a dedicated server. Specifically, the net service name value should include the SERVER=DEDICATED clause in the connect descriptor.
5.14.2 Shared Server Processes
• Consider an order entry system with dedicated server processes. A customer phones the order desk and places an order, and the clerk taking the call enters the order into the database. For most of the transaction, the clerk is on the telephone talking to the customer. A server process is not needed during this time, so the server process dedicated to the clerk's user process remains idle. The system is slower for other clerks entering orders, because the idle server process is holding system resources. Shared server architecture eliminates the need for a dedicated server process for each connection.
43 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.15 Benefits of Oracle Shared Server
1. ORACLE Shared server allows us to support the same or greater number of connections without requiring additional hardware. As a result, ORACLE shared server tends to decrease the overall memory and process requirements on the server.
2. Because clients are sharing processes, the total number of processes is reduced. This translates into resource savings on the server.
3. Shared Server also allows for connection pooling. Connection pooling enables the database server to disconnect an idle ORACLE Shared Server connection to service an incoming request. The idle connection is still active and is reenabled once the client makes the next request. The connection pooling feature of ORACLE shared server allows it to handle a larger number of requests without having to start additional dispatcher process.
4. ORACLE connection manager is a facility provided by ORACLE that controls access to database services and multiplex connections in an ORACLE environment. The access control component of ORACLE connection manager allows us to configure rules that allow or disallow fulfillment of a connection request.
5. The multiplexing components acts as a concentrator feature. It funnels multiple client sessions through a shared network connection from the ORACLE connection manager server to the database server.
44 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.16 Client Server applications
• Consider the example of a query tool operating on a PC. The PC is connected via a network card to a TCP/IP network & is running a TCP/IP software package & ORACLE Net or Net8. The database that it will be accessing resides on a UNIX server on the same network. This configuration is depicted in Fig 5.11.
MS WINDOWS TCP/IP Software Net8/Oracle Net Query tool
Network
UNIX
TCP/IP
Oracle RDBMS Net8/Oracle Net
Client Server
Fig 5.11 Client/Server Configuration
• When users run the tool on the PC, a username, password, & service name for a database must be specified.
• When users are connected to the database, they may then query from the tables available there. Every time a query is executed, the SQL statement for the query is sent to the server & executed. The data is then returned via Oracle Net or Net8 & displayed on the client PC.
45 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
5.17 Database links
• For frequently used connections to remote database, database links should be established. Database links specify the connect descriptors to be used for a connection, & may also specify the username to connect to in the remote database. A database link is typically used to create local objects (such as views or synonyms) that access remote databases via server/server communications. The following example creates a private database link called HR_LINK:
• The create database link command, as shown in this example, has three parameters: ⮚ The name of the (HR_LINK)
⮚ The account to connect to (HR)
⮚ The service name (ORCL)
• A public link can be created by adding the keyword public to the create database link command, as shown in the following example.
• If the ORCL instance is moved to a different server, then the database links can be redirected to ORCL’s new location simply by distributing a tnsnames.ora file that contains the modification or by reversing the listing in directory server.
• To use these links, simply add them as suffixes to table names in commands. The following example creates a local view of a remote table, using the HR_LINK database link:
46 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• The from clause in this example refers to EMPLOYEE@HR_LINK. Since the HR_LINK database link specifies the server name, instance name, & owner name, the global object name for the table is known. If no account name had been specified, the user’s account name would have been used instead.
• In this example, a view was created in order to limit the records that users could retrieve. If no such restriction is necessary, a synonym can be used instead. This is shown in the following example.
47 |
No comments:
Post a Comment