Sunday, May 31, 2009

ICAS4125B Monitor and administer a database

Database Monitoring

Database Management

Applications Manager is a Database Server monitoring tool that can help monitor a heterogeneous database server environment that may consist of Oracle database, MS SQL, Sybase, IBM DB2 and MySQL databases. It also helps database administrators (DBAs) and system administrators by notifying about potential database performance problems. For database server monitoring, Applications Manager connects to the database and ensures it is up. Applications Manager is also an agentless monitoring tool that executes database queries to collect performance statistics and send alerts, if the database performance crosses a given threshold. With out-of-the box reports, DBAs can plan inventory requirements and troubleshoot incidents quickly.

Database Server Monitoring Software Needs to

  • Ensure high availability of database servers
  • Keep tab on the database size, buffer cache size, database connection time
  • Analyze the number of user connections to the databases at various time
  • Analyze usage trends
  • Help take actions proactively before critical incidents occur.

Oracle Monitoring & Management

Oracle Performance Monitoring with Oracle Reports, Alerting & Dashboards

Most business critical applications are database driven and needs Oracle Monitoring. The Oracle database management capability helps database administrators to seamlessly detect, diagnose and resolve Oracle performance issues and monitor Oracle 24X7. The database server monitoring tool is an agentless monitoring software that provides out-of-the-box performance metrics and helps you visualize the health and availability of an Oracle Database server farm. Database administrators can login to the web client and visualize the status and Oracle performance metrics.

Applications Manager also provides out-of-the-box Oracle reports that help analyze the database server usage, Oracle database availability and database server health.

Additionally the grouping capability helps group your databases based on the business process supported and helps the operations team to prioritize alerts as they are received. The following figure 1 is shown the oracle monitoring that will help you to understand in overall.


Figure 1: Oracle monitoring

Oracle Monitoring capability:

  • Response Time
  • User Activity
  • Status
  • Table Space Usage
  • Table Space Details
  • Table Space Status
  • SGA Performance
  • SGA Details
  • SGA Status
  • Performance of Data Files
  • Session Details
  • Session Waits
  • Buffer Gets
  • Disk Reads
  • Rollback Segment
  • Queries, Locks and more
Oracle Management Capabilities
  • Out-of-the-box management of Oracle availability and performance.
  • Monitors performance statistics such as user activity, status, table space, SGA performance, session details, etc. Alerts can be configured for these parameters.
  • Based on the thresholds configured, notifications and alerts are generated. Actions are executed automatically based on configurations.
  • Performance graphs and reports are available instantly. Reports can be grouped and displayed based on availability, health, and connection time.
  • Delivers both historical and current Oracle performance metrics, delivering insight into the performance over a period of time.
Overview of Administering an Oracle Database

This section presents an overview of the environment and tasks of an Oracle Database administrator (DBA). It also discusses DBA security and how you obtain the necessary administrative privileges.

The following topics are discussed:

  • Types of Oracle Database Users
  • Tasks of a Database Administrator
  • Selecting an Instance with Environment Variables
  • Identifying Your Oracle Database Software Release
  • Database Administration Security and Privileges
  • Database Administrator Authentication
  • Creating and Maintaining a Password File
  • Server Manageability

Types of Oracle Database Users

The types of users and their roles and responsibilities depend on the database site. A small site can have one database administrator who administers the database for application developers and users. A very large site can find it necessary to divide the duties of a database administrator among several people and among several areas of specialization.

This section contains the following topics:

  • Database Administrators
  • Security Officers
  • Network Administrators
  • Application Developers
  • Application Administrators
  • Database Users
Database Administrators

Each database requires at least one database administrator (DBA). An Oracle Database system can be large and can have many users. Therefore, database administration is sometimes not a one-person job, but a job for a group of DBAs who share responsibility.

A database administrator's responsibilities can include the following tasks:

  • Installing and upgrading the Oracle Database server and application tools

  • Allocating system storage and planning future storage requirements for the database system

  • Creating primary database storage structures (tablespaces) after application developers have designed an application

  • Creating primary objects (tables, views, indexes) once application developers have designed an application

  • Modifying the database structure, as necessary, from information given by application developers

  • Enrolling users and maintaining system security

  • Ensuring compliance with Oracle license agreements

  • Controlling and monitoring user access to the database

  • Monitoring and optimizing the performance of the database

  • Planning for backup and recovery of database information

  • Maintaining archived data on tape

  • Backing up and restoring the database

  • Contacting Oracle for technical support

Security Officers

In some cases, a site assigns one or more security officers to a database. A security officer enrolls users, controls and monitors user access to the database, and maintains system security. As a DBA, you might not be responsible for these duties if your site has a separate security officer. Please refer to Oracle Database Security Guide for information about the duties of security officers.

Network Administrators

Some sites have one or more network administrators. A network administrator, for example, administers Oracle networking products, such as Oracle Net Services. Please refer to Oracle Database Net Services Administrator's Guide for information about the duties of network administrators.

Application Developers

Application developers design and implement database applications. Their responsibilities include the following tasks:

  • Designing and developing the database application

  • Designing the database structure for an application

  • Estimating storage requirements for an application

  • Specifying modifications of the database structure for an application

  • Relaying this information to a database administrator

  • Tuning the application during development

  • Establishing security measures for an application during development

Application developers can perform some of these tasks in collaboration with DBAs. Please refer to Oracle Database Application Developer's Guide - Fundamentals for information about application development tasks.

Application Administrators

An Oracle Database site can assign one or more application administrators to administer a particular application. Each application can have its own administrator.

Database Users

Database users interact with the database through applications or utilities. A typical user's responsibilities include the following tasks:

  • Entering, modifying, and deleting data, where permitted

  • Generating reports from the data

Tasks of a Database Administrator

The following tasks present a prioritized approach for designing, implementing, and maintaining an Oracle Database:

Task 1: Evaluate the Database Server Hardware

Task 2: Install the Oracle Database Software

Task 3: Plan the Database

Task 4: Create and Open the Database

Task 5: Back up the Database

Task 6: Enroll System Users

Task 7: Implement the Database Design

Task 8: Back up the Fully Functional Database

Task 9: Tune Database Performance

Task 10: Download and Install Patches

Task 11: Roll Out to Additional Hosts

These tasks are discussed in the sections that follow.

Task 1: Evaluate the Database Server Hardware

Evaluate how Oracle Database and its applications can best use the available computer resources. This evaluation should reveal the following information:

  • How many disk drives are available to the Oracle products

  • How many, if any, dedicated tape drives are available to Oracle products

  • How much memory is available to the instances of Oracle Database you will run (see your system configuration documentation)

Task 2: Install the Oracle Database Software

As the database administrator, you install the Oracle Database server software and any front-end tools and database applications that access the database. In some distributed processing installations, the database is controlled by a central computer (database server) and the database tools and applications are executed on remote computers (clients). In this case, you must also install the Oracle Net components necessary to connect the remote machines to the computer that executes Oracle Database.

Task 3: Plan the Database

As the database administrator, you must plan:

  • The logical storage structure of the database

  • The overall database design

  • A backup strategy for the database

It is important to plan how the logical storage structure of the database will affect system performance and various database management operations. For example, before creating any tablespaces for your database, you should know how many datafiles will make up the tablespace, what type of information will be stored in each tablespace, and on which disk drives the datafiles will be physically stored. When planning the overall logical storage of the database structure, take into account the effects that this structure will have when the database is actually created and running. Consider how the logical storage structure of the database will affect:

  • The performance of the computer executing running Oracle Database

  • The performance of the database during data access operations

  • The efficiency of backup and recovery procedures for the database

Plan the relational design of the database objects and the storage characteristics for each of these objects. By planning the relationship between each object and its physical storage before creating it, you can directly affect the performance of the database as a unit. Be sure to plan for the growth of the database.

In distributed database environments, this planning stage is extremely important. The physical location of frequently accessed data dramatically affects application performance.

During the planning stage, develop a backup strategy for the database. You can alter the logical storage structure or design of the database to improve backup efficiency.

It is beyond the scope of this book to discuss relational and distributed database design. If you are not familiar with such design issues, please refer to accepted industry-standard documentation.

Task 4: Create and Open the Database

After you complete the database design, you can create the database and open it for normal use. You can create a database at installation time, using the Database Configuration Assistant, or you can supply your own scripts for creating a database.

Task 5: Back Up the Database

After you create the database structure, carry out the backup strategy you planned for the database. Create any additional redo log files, take the first full database backup (online or offline), and schedule future database backups at regular intervals.

Task 6: Enroll System Users

After you back up the database structure, you can enroll the users of the database in accordance with your Oracle license agreement, and grant appropriate privileges and roles to these users.

Task 7: Implement the Database Design

After you create and start the database, and enroll the system users, you can implement the planned logical structure database by creating all necessary tablespaces. When you have finished creating tablespaces, you can create the database objects.

Task 8: Back Up the Fully Functional Database

When the database is fully implemented, again back up the database. In addition to regularly scheduled backups, you should always back up your database immediately after implementing changes to the database structure.

Task 9: Tune Database Performance

Optimizing the performance of the database is one of your ongoing responsibilities as a DBA. Oracle Database provides a database resource management feature that helps you to control the allocation of resources among various user groups.

Task 10: Download and Install Patches

After installation and on a regular basis, download and install patches. Patches are available as single interim patches and as patchsets (or patch releases). Interim patches address individual software bugs and may or may not be needed at your installation. Patch releases are collections of bug fixes that are applicable for all customers. Patch releases have release numbers. For example, if you installed Oracle Database 10.2.0.0, the first patch release will have a release number of 10.2.0.1.

Task 11: Roll Out to Additional Hosts

After you have an Oracle Database installation properly configured, tuned, patched, and tested, you may want to roll that exact installation out to other hosts. Reasons to do this include the following:

  • You have multiple production database systems.

  • You want to create development and test systems that are identical to your production system.

Instead of installing, tuning, and patching on each additional host, you can clone your tested Oracle Database installation to other hosts, saving time and eliminating inconsistencies. There are two types of cloning available to you:

  • Cloning an Oracle home—Just the configured and patched binaries from the Oracle home directory and subdirectories are copied to the destination host and "fixed" to match the new environment. You can then start an instance with this cloned home and create a database.

    You can use the Enterprise Manager Clone Oracle Home tool to clone an Oracle home to one or more destination hosts. You can also manually clone an Oracle home using a set of provided scripts and Oracle Universal Installer.

  • Cloning a database—The tuned database, including database files, initialization parameters, and so on, are cloned to an existing Oracle home (possibly a cloned home).

    You can use the Enterprise Manager Clone Database tool to clone an Oracle database instance to an existing Oracle home.

Selecting an Instance with Environment Variables

Before you attempt to use SQL*Plus to connect locally to an Oracle instance, you must ensure that environment variables are set properly. When multiple database instances exist on one server, or when an Automatic Storage Management (ASM) instance exists on the same server as one or more database instances, the environment variables determine which instance SQL*Plus connects to. (This is also true when there is only one Oracle instance on a server.)

For example, each Oracle instance (database or ASM) has a unique system identifier (SID). To connect to an instance, you must at a minimum set the ORACLE_SID environment variable to the SID of that instance. Depending on the operating system, you may need to set other environment variables to properly change from one instance to another.

Solaris Example

The following Solaris example sets the environment variables that are required for selecting an instance. When switching between instances with different Oracle homes, the ORACLE_HOME environment variable must be changed.

% setenv ORACLE_SID SAL1
% setenv ORACLE_HOME /u01/app/oracle/product/10.1.0/db_1
% setenv LD_LIBRARY_PATH /usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/lib

Most UNIX installations come with two scripts, oraenv and coraenv, that can be used to easily set these environment variables. For more information, see Administrator's Reference for UNIX Systems.

Windows Example

On Windows, you must set only the ORACLE_SID environment variable to select an instance before starting SQL*Plus.

SET ORACLE_SID=SAL1

Identifying Your Oracle Database Software Release

Because Oracle Database continues to evolve and can require maintenance, Oracle periodically produces new releases. Not all customers initially subscribe to a new release or require specific maintenance for their existing release. As a result, multiple releases of the product exist simultaneously.

As many as five numbers may be required to fully identify a release. The significance of these numbers is discussed in the sections that follow.

Release Number Format

To understand the release nomenclature used by Oracle, examine the following example of an Oracle Database server labeled "Release 10.1.0.1.0".

Figure 1-1 Example of an Oracle Database Release Number


Note: This following is a text description of the figure 1-1 that displays a release number and notes the significance of each digit. The release number is 10.1.0.1.0.

The significance of each number (reading from left to right) is as follows:

  • 10 is the major database release number

  • 1 is the database maintenence release number

  • 0 is the application server release number

  • 1 is the component-specific release number

  • 0 is the platform-specific release number

Major Database Release Number
The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.
Database Maintenance Release Number
The second digit represents a maintenance release level. Some new features may also be included.
Application Server Release Number
The third digit reflects the release level of the Oracle Application Server (OracleAS).
Component-Specific Release Number
The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.
Platform-Specific Release Number
The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.
Checking Your Current Release Number
To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query follows. (You can also query the V$VERSION view to see component-level information.) Other product release levels may increment independent of the database server.
COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15
SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION     STATUS
---------------------------------------- ----------- -----------
NLSRTL                                   10.2.0.1.0  Production
Oracle Database 10g Enterprise Edition   10.2.0.1.0  Prod
PL/SQL                                   10.2.0.1.0  Production
...
It is important to convey to Oracle the results of this query when you report problems with the software.
Database Administrator Security and Privileges
To perform the administrative tasks of an Oracle Database DBA, you need specific privileges within the database and possibly in the operating system of the server on which the database runs. Access to a database administrator's account should be tightly controlled.
This section contains the following topics:
  • The Database Administrator's Operating System Account
  • Database Administrator Usernames
The Database Administrator's Operating System Account
To perform many of the administrative duties for a database, you must be able to execute operating system commands. Depending on the operating system on which Oracle Database is running, you might need an operating system account or ID to gain access to the operating system. If so, your operating system account might require operating system privileges or access rights that other database users do not require (for example, to perform Oracle Database software installation). Although you do not need the Oracle Database files to be stored in your account, you should have access to them.
Database Administrator Usernames
Two user accounts are automatically created when Oracle Database is installed:
  • SYS (default password: CHANGE_ON_INSTALL)
  • SYSTEM (default password: MANAGER)
Create at least one additional administrative user and grant to that user an appropriate administrative role to use when performing daily administrative tasks. Do not use SYS and SYSTEM for these purposes.
SYS
When you create an Oracle Database, the user SYS is automatically created and granted the DBA role.
All of the base tables and views for the database data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.)
Ensure that most database users are never able to connect to Oracle Database using the SYS account.
SYSTEM
When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.
The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to non-administrative users.
The DBA Role
A predefined DBA role is automatically created with every Oracle Database installation. This role contains most database system privileges. Therefore, the DBA role should be granted only to actual database administrators.
Database Administrator Authentication
As a DBA, you often perform special operations such as shutting down or starting up a database. Because only a DBA should perform these operations, the database administrator usernames require a secure authentication scheme.
This section contains the following topics:
  • Administrative Privileges
  • Selecting an Authentication Method
  • Using Operating System Authentication
  • Using Password File Authentication
Administrative Privileges
Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.
SYSDBA and SYSOPER
The following operations are authorized by the SYSDBA and SYSOPER system privileges:
System PrivilegeOperations Authorized
SYSDBA
  • Perform STARTUP and SHUTDOWN operations
  • ALTER DATABASE: open, mount, back up, or change character set
  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SPFILE
  • ALTER DATABASE ARCHIVELOG
  • ALTER DATABASE RECOVER
  • Includes the RESTRICTED SESSION privilege
Effectively, this system privilege allows a user to connect as user SYS.
SYSOPER
  • Perform STARTUP and SHUTDOWN operations
  • CREATE SPFILE
  • ALTER DATABASE OPEN/MOUNT/BACKUP
  • ALTER DATABASE ARCHIVELOG
  • ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIMECHANGECANCELCONTROLFILE requires connecting as SYSDBA.)
  • Includes the RESTRICTED SESSION privilege
This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.

The manner in which you are authorized to use these privileges depends upon the method of authentication that you use.
When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.
Connecting with Administrative Privileges: Example
This example illustrates that a user is assigned another schema (SYS) when connecting with the SYSDBA system privilege. Assume that the sample user oe has been granted the SYSDBA system privilege and has issued the following statements:
CONNECT oe/oe
CREATE TABLE admin_test(name VARCHAR2(20));

Later, user oe issues these statements:
CONNECT oe/oe AS SYSDBA
SELECT * FROM admin_test;
User oe now receives the following error:
ORA-00942: table or view does not exist

Having connected as SYSDBA, user oe now references the SYS schema, but the table was created in the oe schema.
Selecting an Authentication Method
The following methods are available for authenticating database administrators:
  • Operating system (OS) authentication
  • A password file
Your choice will be influenced by whether you intend to administer your database locally on the same machine where the database resides, or whether you intend to administer many different databases from a single remote client. Figure 1-2 illustrates the choices you have for database administrator authentication schemes.
Figure 1-2 Database Administrator Authentication Methods


Note: his is a text description of above illustration that shown a flow diagram of the choices available for database authentication schemes, depending on whether you are administering the database from a remote client or locally from the same machine where the database resides. If you are administering the database locally, or from a remote client with a secure connection, then you have the following choices:
  • Operating system authentication
  • Password file authentication
If you are administering the database from a remote client, and you do not have a secure connection, then your only choice is to use password file authentication.
If you are performing remote database administration, consult your Oracle Net documentation to determine whether you are using a secure connection. Most popular connection protocols, such as TCP/IP and DECnet, are not secure.
Nonsecure Remote Connections
To connect to Oracle Database as a privileged user over a nonsecure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database usernames that have been granted the SYSDBA or SYSOPER system privilege. This form of authentication is discussed in "Using Password File Authentication".
Local Connections and Secure Remote Connections
You can connect to Oracle Database as a privileged user over a local connection or a secure remote connection in two ways:
  • If the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege, then you can connect and be authenticated by a password file.
  • If the server is not using a password file, or if you have not been granted SYSDBA or SYSOPER privileges and are therefore not in the password file, you can use operating system authentication. On most operating systems, authentication for database administrators involves placing the operating system username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted SYSDBA privileges. A similar group, OSOPER, is used to grant SYSOPER privileges to users.
Using Operating System Authentication
This section describes how to authenticate an administrator using the operating system.
OSDBA and OSOPER
Two special operating system groups control database administrator connections when using operating system authentication. These groups are generically referred to as OSDBA and OSOPER. The groups are created and assigned specific names as part of the database installation process. The specific names vary depending upon your operating system and are listed in the following table:
Operating System GroupUNIX User GroupWindows User Group
OSDBAdbaORA_DBA
OSOPERoperORA_OPER

The default names assumed by the Oracle Universal Installer can be overridden. How you create the OSDBA and OSOPER groups is operating system specific.
Membership in the OSDBA or OSOPER group affects your connection to the database in the following ways:
  • If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.
  • If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER system privilege.
  • If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER, the CONNECT command fails.
Preparing to Use Operating System Authentication
To enable operating system authentication of an administrative user:
  1. Create an operating system account for the user.
  2. Add the account to the OSDBA or OSOPER operating system defined groups.
Connecting Using Operating System Authentication
A user can be authenticated, enabled as an administrative user, and connected to a local database by typing one of the following SQL*Plus commands:
CONNECT / AS SYSDBA
CONNECT / AS SYSOPER
For a remote database connection over a secure connection, the user must also specify the net service name of the remote database:
CONNECT /@net_service_name AS SYSDBA
CONNECT /@net_service_name AS SYSOPER
Using Password File Authentication
This section describes how to authenticate an administrative user using password file authentication.
Preparing to Use Password File Authentication
To enable authentication of an administrative user using password file authentication you must do the following:
  1. If not already created, create the password file using the ORAPWD utility:
    ORAPWD FILE=filename PASSWORD=password ENTRIES=max_user
  2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default).
  3. Connect to the database as user SYS (or as another user with the administrative privileges).
  4. If the user does not already exist in the database, create the user.
  5. Grant the SYSDBA or SYSOPER system privilege to the user: 

    GRANT SYSDBA to oe;

    This statement adds the user to the password file, thereby enabling connection AS SYSDBA.
Connecting Using Password File Authentication
Administrative users can be connected and authenticated to a local or remote database by using the SQL*Plus CONNECT command. They must connect using their username and password and the AS SYSDBA or AS SYSOPER clause. For example, user oe has been granted the SYSDBA privilege, so oe can connect as follows:
CONNECT oe/oe AS SYSDB
However, user oe has not been granted the SYSOPER privilege, so the following command will fail:
CONNECT oe/oe AS SYSOPER
Creating and Maintaining a Password File
You can create a password file using the password file creation utility, ORAPWD. For some operating systems, you can create this file as part of your standard installation.
This section contains the following topics:
  • Using ORAPWD
  • Setting REMOTE_LOGIN_PASSWORDFILE
  • Adding Users to a Password File
  • Maintaining a Password File
Using ORAPWD
When you invoke this password file creation utility without supplying any parameters, you receive a message indicating the proper use of the command as shown in the following sample output:
> orapwd
Usage: orapwd file= password= entries= force=

where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBAs and OPERs (opt),
force - whether to overwrite existing file (opt)
There are no spaces around the equal-to (=) character.
The following command creates a password file named acct.pwd that allows up to 30 privileged users with different passwords. In this example, the file is initially created with the password secret for users connecting as SYS.
orapwd FILE=acct.pwd PASSWORD=secret ENTRIES=30
The parameters in the ORAPWD utility are described in the sections that follow.
FILE
This parameter sets the name of the password file being created. You must specify the full path name for the file. The contents of this file are encrypted, and the file cannot be read directly. This parameter is mandatory.
The types of filenames allowed for the password file are operating system specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file. For name and location information for the Unix and Linux operating systems, see Administrator's Reference for UNIX-Based Operating Systems. For Windows, see Platform Guide for Microsoft Windows. For other operating systems, see your operating system documentation.
If you are running multiple instances of Oracle Database using Oracle Real Application Clusters, the environment variable for each instance should point to the same password file.
PASSWORD
This parameter sets the password for user SYS. If you issue the ALTER USER statement to change the password for SYS after connecting to the database, both the password stored in the data dictionary and the password stored in the password file are updated. This parameter is mandatory.
ENTRIES
This parameter specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.
Entries can be reused as users are added to and removed from the password file. If you intend to specify REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this parameter is required.
FORCE
This parameter, if set to Y, enables you to overwrite an existing password file. An error is returned if a password file of the same name already exists and this parameter is omitted or set to N.
Setting REMOTE_LOGIN_ PASSWORDFILE
In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are:
  • NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.
  • EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.
  • SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of a Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.
    This option is useful if you are administering multiple databases or a RAC database.
If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.
Adding Users to a Password File
When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.
A user's name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.
Creating a Password File and Adding New Users to It
Use the following procedure to create a password and add new users to it:
  1. Follow the instructions for creating a password file as explained in "Using ORAPWD".
  2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default.)
  3. Connect with SYSDBA privileges as shown in the following example:
    CONNECT SYS/password AS SYSDBA
  1. Start up the instance and create the database if necessary, or mount and open an existing database.
  2. Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate. See "Granting and Revoking SYSDBA and SYSOPER Privileges", later in this section.
Granting and Revoking SYSDBA and SYSOPER Privileges
If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:
GRANT SYSDBA TO oe;
Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:
REVOKE SYSDBA FROM oe;
Because SYSDBA and SYSOPER are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user's SYSDBA or SYSOPER system privileges. These privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles.












Posted by



at





No comments:
  

































          

        

          

        

Sunday, April 26, 2009


          

        









ICAS4108B Complete database back-up and recovery











Database Backup and Recovery
Most of database systems have incorporated backup and recovery tools into their interfaces and infrastructure it is a good idea to understand what the backup and recovery process involves, beyond the work flow of using the tools. With the growing dependency in the workplace on information and general, and the information in your database specifically, there has never been a time when safe backups and reliable recoveries were more important. Are you maintaining your databases properly? Do they participate in a backup and recovery routine that checks on the health of the data? If you are not 100% sure of this, then you need to find out.

Database Backup and Recovery Needs

It is not just the data files that need to be part of the backup process. You must also backup the transaction logs of the database as well. Without the transaction logs the data files are useless in a recovery event. How often you choose to perform these backup routines is really dependent on the data requirements of your company. If you do not know what those are then you need to find out either by referring to them or asking for them to be created through a research and investigation process.
Backup and Recovery and Database Failure
Failure can happen for any number of reasons. There are three main ones that recur enough to be worth incorporating into your backup and recovery plan. User error is the number one reason for data damage, loss, or corruption. Included in this type of failure is an application modifying or destroying the data on its own or through a user choice. Recovery and restore to the point in time before the corruption occurred.
This returns the data to a clean position at the cost of any other changes that were being made to the data since the point the corruption took place. Any lost work will need to be re-entered or processes repeated if necessary.
Media failure can also cause data loss or damage. Media failure can happen when the media the data files or transaction logs are stored on fail. Most databases will be stored on computer hard drives or across groups of hard drives on designated servers. Hard drives are mechanical devices, just like automobiles, and are made up of parts and pieces that work together. Mechanical devices are known for failure and will need to be replaced once, or if, the data has been retrieved from them.
Backup and Recovery and Disaster
The third reason for database failure is a disastrous or catastrophic event. This can be in the form of fire, flood, or any naturally occurring storm. It can also happen through electrical outage, a virus, or the deliberate hacking of your data. Any of these can corrupt or cause the loss of your data. The true disaster will be the lack of data backup and or the lack of a recovery plan. Without data backup recovery is impossible. And without a recovery plan there is no guarantee that your data backup will make it through the recovery process.

Purpose of Complete Database Recovery
This chapter assumes that some or all of your datafiles are lost or damaged. Typically, this situation is caused by a media failure or accidental deletion. Your goal is to return the database to normal operation by restoring the damaged files from RMAN backups and recovering all database changes.
Scope of This Chapter
This chapter explain how to use complete recovery to fix the most common database problems. This chapter makes the following assumptions:
  • You have lost some or all datafiles and your goal is to recover all changes, but you have not lost all current control files or an entire online redo log group.
  • Your database is using the current server parameter file.
  • You have the complete set of archived redo logs and incremental backups needed for recovery of your datafile backups. Every datafile either has a backup, or a complete set of online and archived redo logs goes back to the creation of a datafile with no backup.
    RMAN can handle lost datafiles without user intervention during restore and recovery. When a datafile is lost, the possible cases can be classified as follows:
    • The control file knows about the datafile, that is, you backed up the control file after datafile creation, but the datafile itself is not backed up. If the datafile record is in the control file, then RESTORE creates the datafile in the original location or in a user-specified location. The RECOVER command can then apply the necessary logs to the datafile.
    • The control file does not have the datafile record, that is, you did not back up the control file after datafile creation. During recovery, the database will detect the missing datafile and report it to RMAN, which will create a new datafile and continue recovery by applying the remaining logs. If the datafile was created in a parent incarnation, then it will be created during the restore or recovery phase as appropriate.
  • You are not restoring and recovering an encrypted tablespace.
    If you perform media recovery on an encrypted tablespace, then the Oracle wallet must be open when performing media recovery of this tablespace. See Oracle Database Administrator's Guide to learn about encrypted tablespaces.
  • Your database runs in a single-instance configuration.
    While RMAN can restore and recover databases in Oracle RAC and Data Guard configurations, these scenarios are beyond the scope of this manual.
  • You are using the RMAN client rather than Oracle Enterprise Manager.
    Enterprise Manager provides access to RMAN through a set of wizards. These wizards lead you through a variety of recovery procedures based on an analysis of your database, your available backups, and your data recovery objectives.
    By using Enterprise Manager, you can perform the simpler restore and recovery scenarios outlined in this chapter. You can also use more sophisticated restore and recovery techniques such as point-in-time recovery and database flashback, which allow for efficient repair of media failures and user errors. In most cases, using Enterprise Manager is simpler than using the RMAN command-line client directly
Preparing for Complete Database Recovery
While RMAN simplifies most database restore and recovery tasks, you must still plan your database restore and recovery strategy based on which database files have been lost and your recovery goal. This section contains the following topics:
Identifying the Database Files to Restore or Recover
The techniques for determining which files require restore or recovery depend upon the type of file that is lost.
Identifying a Lost Control File
It is usually obvious when the control file of your database is lost. The database shuts down immediately when any of the multiplexed control files becomes inaccessible. Also, the database reports an error if you try to start it without a valid control file at each location specified in the CONTROL_FILES initialization parameter.
Loss of some but not all copies of your control file does not require you to restore a control file from backup. If at least one control file remains intact, then you can either copy an intact copy of the control file over the damaged or missing control file, or update the initialization parameter file so that it does not refer to the damaged or missing control file. After the CONTROL_FILES parameter references only present, intact copies of the control file, you can restart your database.
If you restore the control file from backup, then you must perform media recovery of the whole database and then open it with the OPEN RESETLOGS option, even if no datafiles need to be restored. This technique is described in "Performing Recovery with a Backup Control File".
Identifying Datafiles Requiring Media Recovery
When and how to recover depends on the state of the database and the location of its datafiles.
Identifying Datafiles with RMAN
An easy technique for determining which datafiles are missing is to run a VALIDATE DATABASE command, which attempts to read all specified datafiles. For example, start the RMAN client and run the following commands to validate the database (sample output included).
Example 17-1 BACKUP VALIDATE DATABASE
RMAN> VALIDATE DATABASE;

Starting validate at 20-OCT-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=90 device type=DISK
could not read file header for datafile 7 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 10/20/2007 13:05:43
RMAN-06056: could not access datafile 7
The output in Example 17-1 indicates that datafile 7 is inaccessible. You can then run the REPORT SCHEMA command to obtain the tablespace name and filename for datafile 7 as follows (sample output included):
RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name RDBMS

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    450      SYSTEM               ***     +DATAFILE/tbs_01.f
2    86       SYSAUX               ***     +DATAFILE/tbs_ax1.f
3    15       UD1                  ***     +DATAFILE/tbs_undo1.f
4    2        SYSTEM               ***     +DATAFILE/tbs_02.f
5    2        TBS_1                ***     +DATAFILE/tbs_11.f
6    2        TBS_1                ***     +DATAFILE/tbs_12.f
7    2        TBS_2                ***     +DATAFILE/tbs_21.f

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    40       TEMP                 32767       +DATAFILE/tbs_tmp1.f
Identifying Datafiles with SQL
Although VALIDATE DATABASE is a good technique for determining whether files are inaccessible, you may want to use SQL queries to obtain more detailed information.
To determine whether datafiles require media recovery:
  1. Start SQL*Plus and connect to the target database instance. For example, issue the following commands to connect to trgt:
    % sqlplus
    SQL> CONNECT SYS/password@trgt AS SYSDBA
  2. Determine the status of the database by executing the following SQL query:
    SELECT STATUS FROM V$INSTANCE;
    If the status is OPEN, then the database is open. Nevertheless, some datafiles may require media recovery.
  3. Query V$DATAFILE_HEADER to determine the status of your datafiles. Run the following SQL statements to check the datafile headers:
    COL FILE# FORMAT 999
    COL STATUS FORMAT A7
    COL ERROR FORMAT A10
    COL TABLESPACE_NAME FORMAT A10
    COL NAME FORMAT A30

    SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
    FROM   V$DATAFILE_HEADER
    WHERE  RECOVER = 'YES'
    OR     (RECOVER IS NULL AND ERROR IS NOT NULL);
    Each row returned represents a datafile that either requires media recovery or has an error requiring a restore. Check the RECOVER and ERROR columns. RECOVER indicates whether a file needs media recovery, and ERROR indicates whether there was an error reading and validating the datafile header.
    If ERROR is not NULL, then the datafile header cannot be read and validated. Check for a temporary hardware or operating system problem causing the error. If there is no such problem, you must restore the file or switch to a copy.
    If the ERROR column is NULL and the RECOVER column is YES, then the file requires media recovery (and may also require a restore from backup).
    Note:
    Because V$DATAFILE_HEADER only reads the header block of each datafile, it does not detect all problems that require the datafile to be restored. For example, this view cannot tell whether a datafile contains corrupt data blocks.
  4. Optionally, query V$RECOVER_FILE to list datafiles requiring recovery by datafile number with their status and error information. For example, execute the following query:
    SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
    FROM   V$RECOVER_FILE;
    Note:
    You cannot use V$RECOVER_FILE with a control file restored from backup or a control file that was re-created after the time of the media failure affecting the datafiles. A restored or re-created control file does not contain the information needed to update V$RECOVER_FILE accurately.
    To find datafile and tablespace names, you can also perform useful joins using the datafile number and the V$DATAFILE and V$TABLESPACE views. For example:
    COL DF# FORMAT 999
    COL DF_NAME FORMAT A35
    COL TBSP_NAME FORMAT A7
    COL STATUS FORMAT A7
    COL ERROR FORMAT A10
    COL CHANGE# FORMAT 99999999

    SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
        d.STATUS, r.ERROR, r.CHANGE#, r.TIME
    FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
    WHERE t.TS# = d.TS#
    AND d.FILE# = r.FILE#;
    The ERROR column identifies the problem for each file requiring recovery.
Determining the DBID of the Database
In situations requiring the recovery of your server parameter file or control file from autobackup, you need to know the DBID. You should record the DBID along with other basic information about your database.
If you do not have a record of the DBID of your database, then you can find it in the following places without opening your database:
  • The DBID is used in forming the filename for the control file autobackup. Locate this file, and then refer to "Configuring the Control File Autobackup Format" to determine where the DBID appears in the filename.
  • If you have any text files that preserve the output from an RMAN session, then the DBID is displayed by the RMAN client when it starts up and connects to your database. Typical output follows:
    % rman TARGET /

    Recovery Manager: Release 11.1.0.6.0 - Production on Wed Jul 11 17:51:30 2007

    Copyright (c) 1982, 2007, Oracle.  All rights reserved.

    connected to target database: PROD (DBID=36508508
Previewing Backupa Used in Restore Operations
You can apply RESTORE ... PREVIEW to any RESTORE operation to create a detailed list of every backup to be used in the requested RESTORE operation, as well as the necessary target SCN for recovery after the RESTORE operation is complete. This command accesses the RMAN repository to query the backup metadata, but does not actually read the backup files to ensure that they can be restored.
As an alternative to RESTORE ... PREVIEW, you can use the RESTORE ... VALIDATE HEADER command. In addition to listing the files needed for restore and recovery, the RESTORE ... VALIDATE HEADER command validates the backup file headers to determine whether the files on disk or in the media management catalog correspond to the metadata in the RMAN repository.
When planning your restore and recovery operation, use RESTORE ... PREVIEW or RESTORE ... VALIDATE HEADER to ensure that all required backups are available or to identify situations in which you may want to direct RMAN to use or avoid specific backups.
To preview backups to be used in a restore operation:
  1. Run a RESTORE command with the PREVIEW option.
    For example, run one of the following commands:
    RESTORE DATABASE PREVIEW;
    RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
    If the report produced by RESTORE ... PREVIEW provides too much information, then specify the SUMMARY option as shown in the following example:
    RESTORE DATABASE PREVIEW SUMMARY;
    If satisfied with the output, then stop here. If the output indicates that RMAN will request a backup from a tape that you know is temporarily unavailable, then continue with this procedure. If the output indicates that a backup is stored offsite, then skip to "Recalling Offsite Backups".
  2. If needed, use the CHANGE command to set the backup status of any temporarily unavailable backups to UNAVAILABLE.
    "Updating a Backup to Status AVAILABLE or UNAVAILABLE" explains how to perform this task.
  3. Optionally, run RESTORE ... PREVIEW again to confirm that the restore will not attempt to use unavailable backups.
Recalling Offsite Backups
Some media managers provide status information to RMAN about which backups are offsite. An offsite backup is stored in a remote location, such as a secure storage facility, and cannot be restored unless the media manager retrieves the media.
Offsite backups are marked as AVAILABLE in the RMAN repository even though the media must be retrieved from storage before the backup can be restored. If RMAN attempts to restore a offsite backup, then the restore job fails.
You can use RESTORE ... PREVIEW to identify offsite backups. The command output indicates whether backups are stored offsite, as shown by the text at the end of the sample output in Example 17-2.
Example 17-2 RESTORE ... PREVIEW Output
List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       2.25M      SBT_TAPE    00:00:00     21-MAY-07
     BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20070521T144258
     Handle: 0aii9k7i_1_1   Media: 0aii9k7i_1_1

List of Archived Logs in backup set 9
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
---- ------- ---------- --------- ---------- ---------
1    1       392314     21-MAY-07 392541     21-MAY-07
1    2       392541     21-MAY-07 392545     21-MAY-07
1    3       392545     21-MAY-07 392548     21-MAY-07
1    4       392548     21-MAY-07 395066     21-MAY-07
1    5       395066     21-MAY-07 395095     21-MAY-07
1    6       395095     21-MAY-07 395355     21-MAY-07

List of remote backup files
============================
     Handle: aii9k7i_1_1   Media: 0aii9k7i_1_1
validation succeeded for backup piece
Finished restore at 21-MAY-07
released channel: dev1
You can use RESTORE ... PREVIEW RECALL to instruct the media manager to make offsite backups available.
To recall offsite backups:
  1. If backups are stored offsite, then execute a RESTORE ... PREVIEW command with the RECALL option.
    The following example initiates recall for the offsite archived log backups shown in Example 17-2 (sample output included):
    RESTORE ARCHIVELOG ALL PREVIEW RECALL;
    The following sample output indicates that RMAN initiated a recall:
    List of Backup Sets
    ===================


    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    9       2.25M      SBT_TAPE    00:00:00     21-MAY-07
         BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20070521T144258
         Handle: VAULT0aii9k7i_1_1   Media: /tmp,VAULT0aii9k7i_1_1

    List of Archived Logs in backup set 9
    Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
    ---- ------- ---------- --------- ---------- ---------
    1    1       392314     21-MAY-07 392541     21-MAY-07
    1    2       392541     21-MAY-07 392545     21-MAY-07
    1    3       392545     21-MAY-07 392548     21-MAY-07
    1    4       392548     21-MAY-07 395066     21-MAY-07
    1    5       395066     21-MAY-07 395095     21-MAY-07
    1    6       395095     21-MAY-07 395355     21-MAY-07

    Initiated recall for the following list of remote backup files
    ==========================================================
         Handle: VAULT0aii9k7i_1_1   Media: /tmp,VAULT0aii9k7i_1_1
    validation succeeded for backup piece
    Finished restore at 21-MAY-07
    released channel: dev1
  2. Run the RESTORE ... PREVIEW command. If necessary, return to the previous step until no backups needed for the restore are reported as offsite.
Validating Backups Before Restoring Them
While the procedures in "Previewing Backups Used in Restore Operations" indicate which backups will be restored, they do not verify that the backups are actually usable. You can run RMAN commands to test the availability of usable backups for any RESTORE operation, or test the contents of a specific backup for use in RESTORE operations. The contents of the backups are actually read and checked for corruption. You have the following validation options:
  • RESTORE ... VALIDATE tests whether RMAN can restore a specific object from a backup. RMAN chooses which backups to use.
  • VALIDATE BACKUPSET tests the validity of a backup set that you specify.
Restoring Archived Redo Logs Needed for Recovery
RMAN restore archived redo log files from backup automatically as needed to perform recovery. You can also restore archived redo logs manually to save the time needed to restore these files later during the RECOVER command, or if you want to store the restored archived redo log files in some new location.
By default, RMAN restores archived redo logs with names constructed using the LOG_ARCHIVE_FORMAT and the LOG_ARCHIVE_DEST_1 parameters of the target database. These parameters are combined in a platform-specific fashion to form the name of the restored archived log.
Restoring Archived Redo Logs to a New Location
You can override the default location for restored archived redo logs with the SET ARCHIVELOG DESTINATION command. This command manually stages archived logs to different locations while a database restore is occurring. During recovery, RMAN knows where to find the newly restored archived logs; it does not require them to be in the location specified in the initialization parameter file.
To restore archived redo logs to a new location:
  1. After connecting to the target database, ensure the database is mounted or open.
  2. Perform the following operations within a RUN block:
    1. Specify the new location for the restored archived redo logs using SET ARCHIVELOG DESTINATION.
    2. Either explicitly restore the archived redo logs or execute commands that automatically restore the logs.
    The following example explicitly restores all backup archived logs to a new location:
    RUN
    {
    SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore';
    RESTORE ARCHIVELOG ALL;
    # restore and recover datafiles as needed
    .
    .
    .
    }
    The following example sets the archived log destination and then uses RECOVER DATABASE to restore archived logs from this destination automatically:
    RUN
    {
    SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore';
    RESTORE DATABASE;
    RECOVER DATABASE; # restores and recovers logs automatically
    }
Restoring Archived Redo Logs to Multiple Locations
You can specify restore destinations for archived logs multiple times in one RUN block, in order to distribute restored logs among several destinations. (You cannot, however specify multiple destinations simultaneously to produce multiple copies of the same log during the restore operation.) You can use this feature to manage disk space used to contain the restored logs.
This example restores 300 archived redo logs from backup, distributing them across the directories /fs1/tmp, /fs2/tmp, and /fs3/tmp:
RUN
{
# Set a new location for logs 1 through 100.
SET ARCHIVELOG DESTINATION TO '/fs1/tmp';
RESTORE ARCHIVELOG FROM SEQUENCE 1 UNTIL SEQUENCE 100;
# Set a new location for logs 101 through 200.
SET ARCHIVELOG DESTINATION TO '/fs2/tmp';
RESTORE ARCHIVELOG FROM SEQUENCE 101 UNTIL SEQUENCE 200;
# Set a new location for logs 201 through 300.
SET ARCHIVELOG DESTINATION TO '/fs3/tmp';
RESTORE ARCHIVELOG FROM SEQUENCE 201 UNTIL SEQUENCE 300;
# restore and recover datafiles as needed
.
.
.
}
When you issue a RECOVER command, RMAN finds the needed restored archived logs automatically across the destinations to which they were restored, and applies them to the datafiles.
Performing Complete Database Recovery
This section assumes that you have already performed the tasks in "Preparing for Complete Database Recovery". This section describes the basic outline of complete database recovery, which is intended to encompass a wide range of different scenarios.
About Complete Database Recovery
You use the RESTORE and RECOVER commands to restore and recover the database. During the recovery, RMAN automatically restores backups of any needed archived redo logs. If backups are stored on a media manager, then channels must be configured in advance or a RUN block with ALLOCATE CHANNEL commands must be used to enable access to backups stored there.
If RMAN restores archived redo logs to the flash recovery area during a recovery, then it automatically deletes the restored logs after they applying them to the datafiles. Otherwise, you can use the DELETE ARCHIVELOG command to delete restored archived redo logs from disk when they are no longer needed for recovery. For example, you can enter the following command:
RECOVER DATABASE DELETE ARCHIVELOG;
Restoring Datafiles to a Nondefault Location
If you cannot restore datafiles to their default locations, then you must update the control file to reflect the new locations of the datafiles. Use the RMAN SET NEWNAME command within a RUN command to specify the new filename. Afterward, use a SWITCH command, which is equivalent to using the SQL statement ALTER DATABASE RENAME FILE, to update the names of the datafiles in the control file. SWITCH DATAFILE ALL updates the control file to reflect the new names for all datafiles for which a SET NEWNAME has been issued in a RUN command.
Decryption of Backups
If RMAN is restoring encrypted backups, then RMAN automatically decrypts backup sets when their contents are restored. Transparently encrypted backups require no intervention to restore, as long as the encrypted wallet is open and available.
Password-encrypted backups require the correct password to be entered before they can be restored. You must enter the encryption password with the SET DECRYPTION command. If restoring from a group of backups that were created with different passwords, then specify all of the required passwords on the SET DECRYPTION command. RMAN will automatically use the correct password with each backup set.
Performing Complete Recovery of the Whole Database
This scenario assumes that database trgt has lost most or all of its datafiles. It also assumes that the database uses a flash recovery area.
Note: After restore and recovery of a whole database, when the database is opened, any missing temporary tablespaces recorded in the control file are re-created with their previous creation size, AUTOEXTEND, and MAXSIZE attributes. Only temporary tablespaces that are missing are re-created. If a tempfile exists at the location recorded in the RMAN repository but has an invalid header, then RMAN does not re-create the tempfile.
If the tempfiles were originally created as Oracle-managed files, then they are re-created in the current DB_CREATE_FILE_DEST location. Otherwise, they are re-created at their previous locations. If RMAN is unable to re-create the file due to an I/O error or some other cause, then the error is reported in the alert log and the database open operation continues.
To restore and recover the whole database:
  1. Start RMAN and connect to the target database.
    For example, enter the following command:
    % rman
    RMAN> CONNECT SYS/password@trgt
    RMAN displays the database status when it connects: not started, not mounted, not open (when mounted but not open), or none (database is open).
  2. If the database is not mounted, then mount but do not open the database.
    For example, enter the following command:
    STARTUP MOUNT;
  3. Use the SHOW command to see which channels are preconfigured.
    For example, enter the following command (sample output is included):
    SHOW ALL;


    RMAN configuration parameters for database with db_unique_name PROD1 are:
    .
    .
    .
    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
    CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
    CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  "SBT_LIBRARY=/usr/local/oracle/backup/lib/libobk.so";
    If the necessary devices and channels are already configured, then no action is necessary. Otherwise, you can use the CONFIGURE command to configure automatic channels, or include ALLOCATE CHANNEL commands within a RUN block.
  4. If restoring password-protected encrypted backups, then specify the password.
    Use the SET DECRYPTION IDENTIFIED BY command to specify a password for password-protected backups. The following example specifies the password mypassword to restore encrypted backups:
    SET DECRYPTION IDENTIFIED BY mypassword;
  5. Restore and recover the database. Do one of the following:
    • If you are restoring all datafiles to their original locations, then execute RESTORE DATABASE and RECOVER DATABASE sequentially at the RMAN prompt.
      For example, enter the following commands if automatic channels are configured (sample output included):
      RMAN> RESTORE DATABASE;

      Starting restore at 20-JUN-06
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: SID=35 device type=DISK
      allocated channel: ORA_SBT_TAPE_1
      channel ORA_SBT_TAPE_1: SID=34 device type=SBT_TAPE
      channel ORA_SBT_TAPE_1: Oracle Secure Backup

      channel ORA_DISK_1: starting datafile backup set restore
      channel ORA_DISK_1: specifying datafile(s) to restore from backup set
      channel ORA_DISK_1: restoring datafile 00001 to /disk1/oracle/dbs/tbs_01.f
      channel ORA_DISK_1: restoring datafile 00002 to /disk1/oracle/dbs/tbs_ax1.f
      .
      .
      .
      Finished restore at 20-JUN-06
      RMAN> RECOVER DATABASE;

      Starting recover at 20-JUN-06
      using channel ORA_DISK_1
      allocated channel: ORA_SBT_TAPE_1
      channel ORA_SBT_TAPE_1: SID=34 device type=SBT_TAPE
      channel ORA_SBT_TAPE_1: Oracle Secure Backup

      starting media recovery

      channel ORA_DISK_1: starting archived log restore to default destination
      channel ORA_DISK_1: restoring archived log
      archived log thread=1 sequence=5
      channel ORA_DISK_1: restoring archived log
      archived log thread=1 sequence=6
      .
      .
      .
      channel ORA_DISK_1: reading from backup piece /disk1/oracle/work/orcva/TKRM/backupset/2007_06_20/o1_mf_annnn_TAG20070620T113128_29jhr197_.bkp
      channel ORA_DISK_1: piece handle=/disk1/oracle/work/orcva/TKRM/backupset/2007_06_20/o1_mf_annnn_TAG20070620T113128_29jhr197_.bkp tag=TAG20070620T113128
      channel ORA_DISK_1: restored backup piece 1
      channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
      archived log file name=/disk1/oracle/work/orcva/TKRM/archivelog/2007_06_20/o1_mf_1_5_29jhv47k_.arc thread=1 sequence=5
      channel default: deleting archived log(s)
      .
      .
      .
      media recovery complete, elapsed time: 00:00:15
      Finished recover at 20-JUN-06

      If you manually allocate channels, then you must issue the RESTORE and RECOVER commands together within a RUN block as shown in the following example:
      RUN
      {
      ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
      RESTORE DATABASE;
      RECOVER DATABASE;
      }
    • If you are restoring some datafiles to new locations, then execute RESTORE DATABASE and RECOVER DATABASE sequentially in a RUN command. Use the SET NEWNAME to rename datafiles, as described in "Restoring Datafiles to a Nondefault Location".
      The following example restores the database, specifying new names for three of the datafiles, and then recovers the database:
      RUN
      {
      SET NEWNAME FOR DATAFILE 2 TO '/disk2/df2.dbf';
      SET NEWNAME FOR DATAFILE 3 TO '/disk2/df3.dbf';
      SET NEWNAME FOR DATAFILE 4 TO '/disk2/df4.dbf';
      RESTORE DATABASE;
      SWITCH DATAFILE ALL;
      RECOVER DATABASE;
      }
  6. Examine the output to see if media recovery was successful. If so, open the database.
    For example, enter the following command:
    ALTER DATABASE OPEN;
Performing Complete Recovery of a Tablespace
In the basic scenario, the database is open, and some but not all of the datafiles are damaged. You want to restore and recover the damaged tablespace while leaving the database open so that the rest of the database remains available. This scenario assumes that database trgt has lost tablespace tbs_3.
To restore and recover a tablespace:
  1. Start RMAN and connect to the target database.
    For example, enter the following command:
    % rman
    RMAN> CONNECT SYS/password@trgt
    RMAN displays the database status when it connects: not started, not mounted, not open (when mounted but not open), or none (database is open).
  2. If the database is open, then take the datafile requiring recovery offline.
    For example, enter the following command to take tbs_3 offline:
    SQL "ALTER TABLESPACE tbs_3 OFFLINE IMMEDIATE";
  3. Use the SHOW command to see which channels are preconfigured.
    For example, enter the following command (sample output is included):
    SHOW ALL;

    RMAN configuration parameters for database with db_unique_name PROD1 are:
    .
    .
    .
    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
    CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
    CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  "SBT_LIBRARY=/usr/local/oracle/backup/lib/libobk.so";
    If the necessary devices and channels are already configured, then no action is necessary. Otherwise, you can use the CONFIGURE command to configure automatic channels, or include ALLOCATE CHANNEL commands within a RUN block.
  4. If restoring password-protected backups, then specify the password.
    Use the SET DECRYPTION IDENTIFIED BY command to specify a password for password-protected backups. The following example specifies the password mypassword to restore encrypted backups:
    SET DECRYPTION IDENTIFIED BY mypassword;
  5. Restore and recover the tablespace. Do one of the following:
    • If you are restoring datafiles to their original locations, then run the RESTORE TABLESPACE and RECOVER TABLESPACE commands at the RMAN prompt.
      For example, enter the following command if automatic channels are configured (sample output included):
      RMAN> RESTORE TABLESPACE tbs_3;

      Starting restore at 20-JUN-06
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: SID=37 device type=DISK
      allocated channel: ORA_SBT_TAPE_1
      channel ORA_SBT_TAPE_1: SID=38 device type=SBT_TAPE
      channel ORA_SBT_TAPE_1: Oracle Secure Backup

      channel ORA_DISK_1: starting datafile backup set restore
      channel ORA_DISK_1: specifying datafile(s) to restore from backup set
      channel ORA_DISK_1: restoring datafile 00012 to /disk1/oracle/dbs/tbs_31.f
      channel ORA_DISK_1: restoring datafile 00013 to /disk1/oracle/dbs/tbs_32.f
      channel ORA_DISK_1: restoring datafile 00021 to /disk1/oracle/dbs/tbs_33.f
      channel ORA_DISK_1: reading from backup piece /disk1/oracle/work/orcva/TKRM/backupset/2007_06_20/o1_mf_nnndf_TAG20070620T105435_29jflwor_.bkp
      channel ORA_DISK_1: piece handle=/disk1/oracle/work/orcva/TKRM/backupset/2007_06_20/o1_mf_nnndf_TAG20070620T105435_29jflwor_.bkp tag=TAG20070620T105435
      channel ORA_DISK_1: restored backup piece 1
      channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
      Finished restore at 20-JUN-06
      RMAN> RECOVER TABLESPACE tbs_3;

      Starting recover at 20-JUN-06
      using channel ORA_DISK_1
      using channel ORA_SBT_TAPE_1

      starting media recovery

      archived log for thread 1 with sequence 27 is already on disk as file /disk1/oracle/work/orcva/TKRM/archivelog/2007_06_20/o1_mf_1_27_29jjmtc9_.arc
      archived log for thread 1 with sequence 28 is already on disk as file /disk1/oracle/work/orcva/TKRM/archivelog/2007_06_20/o1_mf_1_28_29jjnc5x_.arc
      .
      .
      .
      channel ORA_DISK_1: starting archived log restore to default destination
      channel ORA_DISK_1: restoring archived log
      archived log thread=1 sequence=5
      channel ORA_DISK_1: restoring archived log
      archived log thread=1 sequence=6
      channel ORA_DISK_1: restoring archived log
      archived log thread=1 sequence=7
      .
      .
      .
      channel ORA_DISK_1: reading from backup piece /disk1/oracle/work/orcva/TKRM/backupset/2007_06_20/o1_mf_annnn_TAG20070620T113128_29jhr197_.bkp
      channel ORA_DISK_1: piece handle=/disk1/oracle/work/orcva/TKRM/backupset/2007_06_20/o1_mf_annnn_TAG20070620T113128_29jhr197_.bkp tag=TAG20070620T113128
      channel ORA_DISK_1: restored backup piece 1
      channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
      archived log file name=/disk1/oracle/work/orcva/TKRM/archivelog/2007_06_20/o1_mf_1_5_29jkdvjq_.arc thread=1 sequence=5
      channel default: deleting archived log(s)
      archived log file name=/disk1/oracle/work/orcva/TKRM/archivelog/2007_06_20/o1_mf_1_5_29jkdvjq_.arc RECID=91 STAMP=593611179
      archived log file name=/disk1/oracle/work/orcva/TKRM/archivelog/2007_06_20/o1_mf_1_6_29jkdvbz_.arc thread=1 sequence=6
      channel default: deleting archived log(s)
      .
      .
      .
      media recovery complete, elapsed time: 00:00:01
      Finished recover at 20-JUN-06
    • If you are restoring some datafiles to new locations, then execute RESTORE TABLESPACE and RECOVER TABLESPACE in a RUN command. Use the SET NEWNAME to rename datafiles, as described in "Restoring Datafiles to a Nondefault Location".
      The following example restores the datafiles in tablespaces tbs_3 to a new location, then performs recovery. Assume that the old datafiles were stored in the /disk1 path and the new ones will be stored in the /disk2 path.
      RUN
      {
      # specify the new location for each datafile
      SET NEWNAME FOR DATAFILE '/disk1/oracle/dbs/tbs_31.f' TO
                              '/disk2/tbs_31.f';
      SET NEWNAME FOR DATAFILE '/disk1/oracle/dbs/tbs_32.f' TO
                              '/disk2/tbs_32.f';
      SET NEWNAME FOR DATAFILE '/disk1/oracle/dbs/tbs_33.f' TO
                              '/disk2/tbs_33.f';
      RESTORE TABLESPACE tbs_3;
      SWITCH DATAFILE ALL;   # update control file with new filenames
      RECOVER TABLESPACE tbs_3;
      }
  6. Examine the output to see if recovery was successful. If so, bring the recovered tablespace back online.
    For example, enter the following command:
    SQL "ALTER TABLESPACE tbs_3 ONLINE";
Performing Complete Recovery of a Datafile After Switching to a Copy
If you have image copies of the inaccessible datafiles in the flash recovery area, then you can use the SWITCH DATAFILE ... TO COPY command to point the control file at the datafile copy and then use RECOVER to recover lost changes. When the original location can be used again, you can switch datafile back to the original location. Because you do not need to restore backups, this recovery technique takes less time than traditional restore and recovery.
Note:
A SWITCH TABLESPACE ... TO COPY command is also supported for cases when all datafiles in a tablespace are lost and copies of all datafiles exist.
In the basic scenario, the database is open, and some but not all of the datafiles are damaged. During the course of the day, a datafile goes missing due to storage failure. You need to repair this file, but cannot afford the time to do a restore and recovery from a backup. You decide to use a recent image copy backup as the new file, thus eliminating restore time. This scenario assumes that database trgt has lost datafile 4.
To switch to a datafile copy and perform recovery:
  1. Start RMAN and connect to the target database.
    For example, enter the following command:
    % rman
    RMAN> CONNECT SYS/password@trgt
    RMAN displays the database status when it connects: not started, not mounted, not open (when mounted but not open), or none (database is open).
  2. If the database is open, then take the tablespace requiring recovery offline.
    For example, enter the following command to take datafile 4 offline:
    SQL "ALTER DATABASE DATAFILE 4 OFFLINE";
  3. Switch the offline datafile to the latest copy.
    For example, enter the following command to point the control file to the latest image copy of datafile 4:
    SWITCH DATAFILE 4 TO COPY;
  4. Recover the datafile with the RECOVER DATAFILE command.
    For example, enter the following command (sample output included):
    RECOVER DATAFILE 4;

    RMAN automatically restores archived redo logs and incremental backups. Because the database uses a flash recovery area, RMAN automatically deletes them after they have been applied.
  5. Examine the output to see if recovery was successful. If so, bring the recovered datafile back online.
    For example, enter the following command to bring datafile 4 online:
    SQL "ALTER DATABASE DATAFILE 4 ONLINE";










Posted by



at





No comments:
  

































        

      









Subscribe to:
Posts (Atom)