Oracle9i Database Administration Fundamentals Ii

Oracle9i Database Administration Fundamentals II Electronic Presentation

D11297GC20 Production 2.0 August 2002 D37069 ®

Authors

Copyright © Oracle Corporation, 2002. All rights reserved.

Donna Keesling James Womack

This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:

Technical Contributors and Reviewers Lance Ashdown Tammy Bednar Louise Beijer Harald van Breederode Howard Bradley Senad Dizdar Steven George Joel Goodman Scott Gossett John Hibbard Stefan Lindblad Roman Niehoff Howard Ostrow Radhanes Petronilla Maria Jesus Senise Garcia Peter Sharman Ranbir Singh Sergey Stetsenko John Watson Steven Wertheimer Junichi Yamazaki Publisher May Lonn Chan-Villareal

Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of the Education Products group of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in DataGeneral, including Alternate III (June 1987). The information in this document is subject to change without notice. If you find any problems in the documentation, please report them in writing to Worldwide Education Services, Oracle Corporation, 500Oracle Parkway, Box SB-6, Redwood Shores, CA 94065. Oracle Corporation does not warrant that this document is error-free. Oracle and all references to Oracle Products are trademarks or registered trademarks of Oracle Corporation. All other products or company names are used for identification purposes only, and may be trademarks of their respective owners.

Networking Overview

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Explain the solutions in Oracle9i that can be used to manage complex networks • Describe Oracle networking add-on solutions

1-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Network Environment Challenges

• • • • •

1-3

Configuring the network environment Maintaining the network Tuning, troubleshooting, and monitoring the network Implementing security in the network Integrating legacy systems

Copyright © Oracle Corporation, 2002. All rights reserved.

Simple Network: Two-Tier

Network Client Server

• •

1-5

Network connects client and server Client and server speak the same language or protocol

Copyright © Oracle Corporation, 2002. All rights reserved.

Simple to Complex Network: N-Tier

Network Client

• • •

1-6

Network Middle tier

Server

Client can be a thin client or a PC Middle tier can contain applications and services Server holds actual data

Copyright © Oracle Corporation, 2002. All rights reserved.

Complex Network

TCP/IP SSL

TCP/IP SSL

TCP/IP TCP/IP

Named Pipes TCP/IP

1-7

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle9i Networking Solutions

• • • • •

1-8

Connectivity Directory Services Scalability Security Accessibility

Copyright © Oracle Corporation, 2002. All rights reserved.

Connectivity: Key Features of Oracle Net Services • • • • • •

1-9

Protocol independence Comprehensive platform support Integrated GUI administration tools Multiple configuration options Tracing and diagnostic toolset Basic security

Copyright © Oracle Corporation, 2002. All rights reserved.

Connectivity: Oracle Net Services

Administration and configuration

Client

1-10

Oracle Net

Oracle Net

Protocol

Protocol

Any platform

Any platform

Copyright © Oracle Corporation, 2002. All rights reserved.

Server

Connectivity: Database Connectivity with HTTP

Web browser

1-11

HTTP

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle9i

Directory Services: Directory Naming

• •

1-12

Process of resolving a network alias using an LDAP-compliant directory server Clients must be configured to use the LDAP compliant server

Copyright © Oracle Corporation, 2002. All rights reserved.

Directory Services: Oracle Internet Directory • •

Oracle’s LDAP compliant directory service Provides the following features: – Integrates tightly with Oracle9i – Simplifies network administration – Provides a secure and reliable directory structure

1-13

Copyright © Oracle Corporation, 2002. All rights reserved.

Scalability: Oracle Shared Server

• • • • •

1-14

Enables a large number of users to connect to a database simultaneously Database resources are shared resulting in efficient memory and processing usage Connections are routed via a dispatcher Server processes are not dedicated to each client Server processes serve client processes as needed

Copyright © Oracle Corporation, 2002. All rights reserved.

Scalability: Oracle Connection Manager

Oracle Connection Manager offers: • Multiplexing of connections • Cross-protocol connectivity • Network access control

1-15

Copyright © Oracle Corporation, 2002. All rights reserved.

Scalability: Oracle Connection Manager

2

1 3 Oracle Connection Manager

Server

1-17

Copyright © Oracle Corporation, 2002. All rights reserved.

Security: Advanced Security

•

Encryption – Encodes between network nodes – DES, RSA, 3DES

•

Authentication – Authenticates users through third-party services and Secure Sockets Layer (SSL) – Kerberos, Radius, CyberSafe

•

Data Integrity – Ensures data integrity during transmission – MD5, SHA

1-18

Copyright © Oracle Corporation, 2002. All rights reserved.

Advanced Security Encryption Corporate earnings are up 45% this quarter

1

Corporate earnings are up 45% this quarter

3

Oracle Advanced Security Installed Oracle Advanced Security Decrypt Installed Server

Client

2 Encrypt fdh37djf246gs’b[da,\ssk

1-19

Copyright © Oracle Corporation, 2002. All rights reserved.

Security: Oracle Net Services and Firewalls • •

• •

Oracle works with key firewall vendors to provide firewall support Oracle Net Firewall Proxy Kit allows firewall vendors to provide connection support for Oracle environments Oracle Net Firewall Proxy is based on Oracle Connection Manager Oracle supports two categories of firewalls: – Proxy-based firewalls – Stateful packet inspection firewalls

1-20

Copyright © Oracle Corporation, 2002. All rights reserved.

Accessibility: Heterogeneous Services

• •

1-21

Enables access of legacy data as if it resides in a single, local relational database Enables Oracle procedure calls to access nonOracle systems, services, or APIs

Copyright © Oracle Corporation, 2002. All rights reserved.

Accessibility: External Procedures

• • • •

1-22

Functions written in a 3GL language that can be called from PL/SQL Allows the developer more flexibility than SQL or PL/SQL provide Listener can listen for external procedure calls Connections to external procedure can be configured during or after server installation

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle Net Services Configuration and Administration Tools •

Oracle Net Manager – Used for configuring and managing Oracle Net – Client- or server-based tool – Integrated with Oracle Enterprise Manager

• •

Oracle Net Configuration Assistant Oracle Net Control utilities – Listener Control utility – Oracle Connection Manager Control utility – Oracle Names Control utility

1-23

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle Net Manager

1-24

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle Net Configuration Assistant

1-25

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Explain Oracle’s solutions for managing complex networks: – – – – –

•

Oracle Net Services HTTP Connectivity Oracle Internet Directory Oracle Shared Server Connection Manager

Describe Oracle’s add-on solutions: – Oracle Advanced Security – Heterogeneous Services

1-26

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle Net Architecture

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Explain the key components of the Oracle Net stack communication architecture • Explain Oracle Net’s role in client-server connections • Describe how Web client connections are established through Oracle networking products

2-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle Net Connections

•

Oracle Net is used to establish connections between applications on a network depending on the following: – – – –

•

The network configuration The location of the nodes The application The network protocol

The connections types can be: – Client-Server Application – Java Application – Web Client Application

2-3

Copyright © Oracle Corporation, 2002. All rights reserved.

Client-Server Application Connection

Oracle Net

2-4

Client

Oracle Database Server

Client Application (uses OCI)

Oracle RDBMS (uses OPI)

Two Task Common

Two Task Common

Oracle Net Foundation Layer

Oracle Net Foundation Layer

Oracle Protocol Support

Oracle Protocol Support

Network Protocol

Network Protocol

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle Net

Web Client Application Connections

Web browsers can connect to an Oracle server in the following ways: • Using an application Web server as a middle tier that is configured with either of the following: – JDBC Oracle Call Interface (OCI) driver – Thin JDBC driver

•

2-6

Connecting directly to an Oracle server by using HTTP

Copyright © Oracle Corporation, 2002. All rights reserved.

Web Client Application Connection: Application Web Server Middle-Tier Java application or applet HTTP

Client

Server

Oracle Net

Web browser Application Web server

2-7

Oracle server

Copyright © Oracle Corporation, 2002. All rights reserved.

Web Client Application Connection: Java Application Client Application Web Server (client) Java application

Oracle Database Server Oracle RDBMS

JDBC OCI Driver

2-8

Two-Task Common

Two-Task Common

Oracle Net Foundation Layer

Oracle Net Foundation Layer

Oracle Protocol Support

Oracle Protocol Support

Network Protocol

Network Protocol

Copyright © Oracle Corporation, 2002. All rights reserved.

Web Client Application Connection: Java Applet Client Oracle Database Server Application Web Server (client) Java Applet

Oracle RDBMS Two-Task Common

JDBC Thin driver

2-9

JavaTTC

Oracle Net Foundation Layer

JavaNet

Oracle Protocol Support

TCP/IP Network Protocol

TCP/IP Network Protocol

Copyright © Oracle Corporation, 2002. All rights reserved.

Web Connections Using HTTP

Client

Server HTTP

Web browser

2-10

Oracle server supporting HTTP

Copyright © Oracle Corporation, 2002. All rights reserved.

Web Browser Direct Connection

Oracle Database Server Oracle Client

RDBMS HTTP

Web browser

2-11

HTTP

Oracle Protocol Support

TCP/IP

TCP/IP

Copyright © Oracle Corporation, 2002. All rights reserved.

Connectivity Concepts and Terminology

• •

Database services Service Name – A logical representation of a database – The way a database is presented to clients

•

Connect Descriptor – Location of the database – Name of the database service

•

Listener – Receives client connection requests – Hands requests to the database server

2-12

Copyright © Oracle Corporation, 2002. All rights reserved.

Connectivity Concepts and Terminology

•

Service Registration – Database registers information with the listener – Service handlers available for each instance

•

Service Handlers – Connection points – Dispatcher or dedicated server

2-13

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle Net Configuration Models

•

Localized management – Local file on each computer in the network

•

Centralized management – LDAP-compliant directory server – Oracle Names server

2-14

Copyright © Oracle Corporation, 2002. All rights reserved.

Overview of Naming Methods

•

•

Naming methods are used by a client application to resolve a connect identifier to a connect descriptor when attempting to connect to a database service. Oracle Net provides five naming methods: – – – – –

2-15

Host naming Local naming Directory naming Oracle Names External naming

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle Net Configuration Files

ldap.ora

names.ora

listener.ora

2-16

tnsnames.ora

sqlnet.ora

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Explain the key components of the Oracle Net stack communication architecture • Explain Oracle Net Services role in client-server connections • Describe how Web client connections are established through Oracle networking products

2-18

Copyright © Oracle Corporation, 2002. All rights reserved.

Basic Oracle Net Server Side Configuration

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Identify how the listener responds to incoming connections • Describe Dynamic Service Registration • Configure the listener by using Oracle Net Manager • Control the listener by using the Listener Control Utility • Configure the listener for HTTP connections

3-2

Copyright © Oracle Corporation, 2002. All rights reserved.

The Listener Process Client

Server

Listener

tnsnames.ora sqlnet.ora

3-3

listener.ora

Copyright © Oracle Corporation, 2002. All rights reserved.

Connection Methods

When a connection request is made by a client to a server, the listener performs one of the following: • Spawns a server process and bequeaths (passes) the connection to it • Hands off the connection to a dispatcher in an Oracle Shared Server configuration • Redirects the connection to a dispatcher or server process

3-4

Copyright © Oracle Corporation, 2002. All rights reserved.

Spawn and Bequeath Connections Client

Server

3

2

1

Dedicated Server Process

Listener

3-5

Copyright © Oracle Corporation, 2002. All rights reserved.

Direct Hand-Off Connections

Shared Server Process

3

2

1

Dispatcher

Listener Shared Server Process

3-7

Copyright © Oracle Corporation, 2002. All rights reserved.

Redirected Session Client

Server

6

5

3

Server or dispatcher process

port

4 1

port

2 Listener

3-8

Copyright © Oracle Corporation, 2002. All rights reserved.

Service Configuration and Registration

The listener can be configured in two ways: • Dynamic service registration – Does not require configuration in listener.ora file – The listener relies on the PMON process

•

Static service configuration – Used for Oracle8 and earlier releases – Requires listener.ora configuration – Required for Oracle Enterprise Manager and other services

3-10

Copyright © Oracle Corporation, 2002. All rights reserved.

Static Service Registration: The listener.ora File Default settings for the listener.ora file with Oracle software installation: • Listener name LISTENER • Port 1521 • Protocols TCP/IP and IPC • SID name Default instance • Host name Default host name

3-12

Copyright © Oracle Corporation, 2002. All rights reserved.

Static Service Registration: The listener.ora File 1. LISTENER = 2. (ADDRESS_LIST = 3. (ADDRESS= (PROTOCOL= TCP)(Host= stcsun02)(Port= 1521))) 4. SID_LIST_LISTENER = 5. (SID_LIST = 6. (SID_DESC = 7. (ORACLE_HOME= /home/oracle) 8. (GLOBAL_DBNAME = ORCL.us.oracle.com) 9. (SID_NAME = ORCL)))

3-13

Copyright © Oracle Corporation, 2002. All rights reserved.

Static Service Registration: Create a Listener

3-16

Copyright © Oracle Corporation, 2002. All rights reserved.

Configuring Services

3-17

Copyright © Oracle Corporation, 2002. All rights reserved.

Dynamic Service Registration: Configure Registration To ensure that service registration is functional, the following initialization parameters must be configured: • SERVICE_NAMES • INSTANCE_NAME

3-19

Copyright © Oracle Corporation, 2002. All rights reserved.

Dynamic Service Registration: Registering Information with the Listener •

•

By default, PMON registers with a local listener on the server on the default local address of TCP/IP, port 1521 PMON can register with a non default listener if: – LOCAL_LISTENER initialization parameter is defined – LISTENERS attribute of the DISPATCHERS initialization parameter is defined for Oracle Shared Server

3-20

Copyright © Oracle Corporation, 2002. All rights reserved.

Configure the Listener for Oracle9i JVM: HTTP • •

•

3-21

The listener can be configured to accept connections from clients using HTTP. Use Static Listener Registration if the database is Oracle8i or an earlier version, even if Oracle9i listener is used. If both the listener and the database are Oracle9i, configuration occurs dynamically during service registration.

Copyright © Oracle Corporation, 2002. All rights reserved.

Listener Control Utility (LSNRCTL)

Listener Control Utility commands can be issued from the command-line or from the LSNRCTL prompt. • UNIX command-line syntax: •$ lsnrctl •

Prompt syntax: LSNRCTL>

•

Control a non-default listener: LSNRCTL> set current_listener listener02

3-23

Copyright © Oracle Corporation, 2002. All rights reserved.

LSNRCTL Commands

Use the following commands to control the listener: • Starting the listener: $lsnrctl START listener_name

•

Stopping the listener: $lsnrctl STOP listener_name

3-24

Copyright © Oracle Corporation, 2002. All rights reserved.

LSNRCTL SET and SHOW Modifiers

•

Change listener parameters with SET: LSNRCTL> SET trc_level ADMIN

•

Display the values of parameters with SHOW: LSNRCTL> SHOW trc_directory

3-26

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Configure the listener by using Oracle Net Manager • Control the listener by using the Listener Control Utility • Configure the listener for HTTP connections

3-28

Copyright © Oracle Corporation, 2002. All rights reserved.

Practice 3 Overview

This practice covers the following topics: • Configuring a non-default LISTENER • Starting and stopping your listener • Viewing the LISTENER log file

3-29

Copyright © Oracle Corporation, 2002. All rights reserved.

Naming Method Configuration

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Describe the difference between host naming and local service name resolution • Use Oracle Net Configuration Assistant to configure: – Host Naming method – Local naming method – Net service names

•

4-2

Perform simple connection troubleshooting

Copyright © Oracle Corporation, 2002. All rights reserved.

Overview of Naming Methods

•

•

Naming methods are used by a client application to resolve a connect identifier to a connect descriptor when attempting to connect to a database service. Oracle Net provides five naming methods: – – – – –

4-3

Host naming Local naming Directory naming Oracle Names External naming

Copyright © Oracle Corporation, 2002. All rights reserved.

Host Naming

Clients can connect to a server using a host name under the following conditions: • Connecting to an Oracle database service using Oracle Net Services Client software • Client and server are connecting using TCP/IP protocol • Host names are resolved through an IP address translation mechanism such as DNS or a local /etc/hosts file •

4-4

No advanced features such as Oracle Connection Manager or security options are used

Copyright © Oracle Corporation, 2002. All rights reserved.

Host Naming: Client Side Server

Client

TCP/IP

… names.directory_path = (HOSTNAME) sqlnet.ora

listener.ora

4-5

Copyright © Oracle Corporation, 2002. All rights reserved.

Host Naming: Server Side Server

Client

TCP/IP 1521 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = stc-sun02.us.oracle.com) (ORACLE_HOME = /u03/ora9i/rel12) (SID_NAME = TEST)

sqlnet.ora

4-6

listener.ora

Copyright © Oracle Corporation, 2002. All rights reserved.

Host Naming: Example

•

listener.ora file: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = stc-sun02.us.oracle.com) (ORACLE_HOME = /home1/user461/oracle) (SID_NAME = TEST)

•

Connecting from the client: sqlplus system/[email protected]

4-7

Copyright © Oracle Corporation, 2002. All rights reserved.

Configuring Host Naming Using Net Manager

4-8

Copyright © Oracle Corporation, 2002. All rights reserved.

Local Naming Client

Server

sqlnet.ora tnsnames.ora

4-9

listener.ora

Copyright © Oracle Corporation, 2002. All rights reserved.

Configuring the tnsnames.ora File Using Net Manager

4-10

Copyright © Oracle Corporation, 2002. All rights reserved.

Configuring the tnsnames.ora File Using Net Manager

4-11

Copyright © Oracle Corporation, 2002. All rights reserved.

Configuring the tnsnames.ora File Using Net Manager

4-12

Copyright © Oracle Corporation, 2002. All rights reserved.

Configuring the tnsnames.ora File Using Net Manager

4-13

Copyright © Oracle Corporation, 2002. All rights reserved.

Configuring the tnsnames.ora File Using Net Manager

4-14

Copyright © Oracle Corporation, 2002. All rights reserved.

Configuring the tnsnames.ora File Using Net Manager

4-15

Copyright © Oracle Corporation, 2002. All rights reserved.

Configuring the Local Naming Method Using Net Manager

4-16

Copyright © Oracle Corporation, 2002. All rights reserved.

Generated Files: tnsnames.ora # TNSNAMES.ORA Network Configuration # File:D:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora # Generated by Oracle configuration tools. U461 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ed-pdsun1)(PORT = 8461)) ) (CONNECT_DATA = (SERVICE_NAME = U461) ) )

4-17

Copyright © Oracle Corporation, 2002. All rights reserved.

Generated Files: sqlnet.ora # SQLNET.ORA Network Configuration File: D:\oracle\ora92\NETWORK\ADMIN\sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES)

4-19

Copyright © Oracle Corporation, 2002. All rights reserved.

Troubleshooting the Client Side

The following error codes are related to problems on the client side: ORA-12154 ORA-12198 ORA-12203 ORA-12533 ORA-12541

4-20

“TNS:could not resolve service name” “TNS:could not find path to destination” “TNS:unable to connect to destination” “TNS:illegal ADDRESS parameters” “TNS:no listener”

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Describe the difference between host naming and local service name resolution • Use Oracle Net Manager to configure: – Host naming method – Local naming method – Net service names

•

4-22

Perform simple connection troubleshooting

Copyright © Oracle Corporation, 2002. All rights reserved.

Practice 4 Overview

This practice covers the following topics: • Local Naming configuration • Configuration of net service names • Testing the configuration

4-23

Copyright © Oracle Corporation, 2002. All rights reserved.

Usage and Configuration of the Oracle Shared Server

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Identify the components of the Oracle Shared Server • Describe the Oracle Shared Server architecture • Configure the Oracle Shared Server • Identify and explain usefulness of related data dictionary views

5-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Server Configurations

• •

5-3

Dedicated server process Shared server process

Copyright © Oracle Corporation, 2002. All rights reserved.

Dedicated Server Processes

Client

Server Instance SGA

User process

Server process SMON DBWn PMON CKPT

5-4

Copyright © Oracle Corporation, 2002. All rights reserved.

LGWR ARCn

Oracle Shared Server Shared server processes

Instance SGA

Snnn Snnn Snnn

Dispatcher D001

User process

5-5

Dispatcher D002

User process

Dispatcher D003 Database server Client

Copyright © Oracle Corporation, 2002. All rights reserved.

Benefits of Oracle Shared Server

• • • • •

5-7

Reduces the number of processes against an instance Increases the number of possible users Achieves load balancing Reduces the number of idle server processes Reduces memory usage and system overhead

Copyright © Oracle Corporation, 2002. All rights reserved.

Using a Dedicated Server with Oracle Shared Server TEST.world = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = stc-sun02) (PORT = 1521) ) (CONNECT_DATA=(SERVICE_NAME=TEST.us.oracle.com) (SERVER=DEDICATED) ) )

5-8

Copyright © Oracle Corporation, 2002. All rights reserved.

Connecting

Listener

2

Dispatcher process

Dispatcher process

D001

D002

3

Database server Client

1 User process

5-9

User process

Copyright © Oracle Corporation, 2002. All rights reserved.

Processing a Request Shared server processes Snnn

4

Snnn

Request Queue

3

Snnn

2 SMON

DBWn

Dispatcher D001 Listener

1

PMON

CKPT

Dispatcher D002

LGWR

ARCn

Dispatcher D003 Database server

6 User process

5-10

Instance SGA Response queue for (D001) Response queue for (D002) Response queue 5 for (D003)

User process

Copyright © Oracle Corporation, 2002. All rights reserved.

client

The SGA and PGA

Dedicated Server: User session data is kept in the PGA PGA SGA

Shared pool and other memory structures

User Stack Cursor session space state data

Oracle Shared Server: User session data is held in the SGA SGA PGA

Shared pool User Cursor and other session memory state data structures

5-12

Stack space

Copyright © Oracle Corporation, 2002. All rights reserved.

Configuring Oracle Shared Server

•

Required initialization parameters – DISPATCHERS – SHARED_SERVERS

•

Optional initialization parameters – – – –

5-13

MAX_DISPATCHERS MAX_SHARED_SERVERS CIRCUITS SHARED_SERVER_SESSIONS

Copyright © Oracle Corporation, 2002. All rights reserved.

DISPATCHERS

Specifies the number of dispatchers initially started for a given protocol DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=2)\ (PROTOCOL=IPC)(DISPATCHERS=1)"

5-14

Dispatcher D001

Dispatcher D002

Dispatcher D003

TCP/IP

TCP/IP

IPC

Copyright © Oracle Corporation, 2002. All rights reserved.

MAX_DISPATCHERS

• •

Specifies the maximum number of dispatcher processes that can run simultaneously Issues the ALTER SYSTEM command to add more dispatchers than initially started MAX_DISPATCHERS = 5 Dispatcher D004 Dispatcher D001 TCP/IP

5-16

Dispatcher D002 TCP/IP

Dispatcher D003 IPC

Dispatcher D005

Copyright © Oracle Corporation, 2002. All rights reserved.

SHARED_SERVERS

Specifies the number of server processes created when an instance is started up SHARED_SERVERS = 6

5-18

S001

S003

S005

S002

S004

S006

Copyright © Oracle Corporation, 2002. All rights reserved.

MAX_SHARED_SERVERS

• •

Specifies the maximum number of shared servers that can be started Allows shared servers to be allocated dynamically based on the length of the request queue MAX_SHARED_SERVERS = 10

5-20

S001

S003

S005

S007

S009

S002

S004

S006

S008

S010

Copyright © Oracle Corporation, 2002. All rights reserved.

CIRCUITS

•

•

Specifies the total number of virtual circuits that are available for inbound and outbound network sessions Contributes to total SGA size

CIRCUITS = 100

5-21

Copyright © Oracle Corporation, 2002. All rights reserved.

SHARED_SERVER_SESSIONS

• •

Specifies the total number of Oracle Shared Server user sessions to allow Setting this parameter enables you to reserve user sessions for dedicated servers SHARED_SERVER_SESSIONS = 100

5-22

Copyright © Oracle Corporation, 2002. All rights reserved.

Related Parameters

Other initialization parameters affected by Oracle Shared Server that may require adjustment: • LARGE_POOL_SIZE • SESSIONS

5-23

Copyright © Oracle Corporation, 2002. All rights reserved.

Verifying Setup

• Verify that the dispatcher has registered with the listener when the instance was started by issuing: $ lsnrctl services

• Verify that you are connected using shared servers by making a single connection then query V$CIRCUIT view to show one entry per shared server connection.

5-25

Copyright © Oracle Corporation, 2002. All rights reserved.

Dynamic Views

• • • • • •

5-27

V$CIRCUIT V$SHARED_SERVER V$DISPATCHER V$SHARED_SERVER_MONITOR V$QUEUE V$SESSION

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Identify the components of the Oracle Shared Server • Describe the Oracle Shared Server architecture • Configure the Oracle Shared Server • Identify and explain usefulness of related data dictionary views

5-28

Copyright © Oracle Corporation, 2002. All rights reserved.

Practice 5 Overview

This practice covers the following topics: • Configuring Oracle Shared Server • Defining LOCAL_LISTENER for instance registration • Using the Listener Control utility to verify services • Verifying shared server configuration and performance using V$ views •

5-29

Verifying instance registration

Copyright © Oracle Corporation, 2002. All rights reserved.

Backup and Recovery Overview

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Describe the basics of database backup, restore, and recovery • List the types of failures that may occur in an Oracle environment • Define a backup and recovery strategy

6-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Backup and Recovery Issues

• • • •

6-3

Protect the database from numerous types of failures Increase Mean-Time-Between-Failures (MTBF) Decrease Mean-Time-To-Recover (MTTR) Minimize data loss

Copyright © Oracle Corporation, 2002. All rights reserved.

Categories of Failures

• • • • • •

6-4

Statement failure User process failure User error Network failure Instance failure Media failure

Copyright © Oracle Corporation, 2002. All rights reserved.

Causes of Statement Failures

• • • • •

6-6

Logic error in an application Attempt to enter invalid data into the table Attempt an operation with insufficient privileges Attempt to create a table but exceed allotted quota limits Attempt an INSERT or UPDATE to a table, causing an extent to be allocated, but with insufficient free space available in the tablespace

Copyright © Oracle Corporation, 2002. All rights reserved.

Resolutions for Statement Failures

6-7

• • • •

Correct the logical flow of the program Modify and reissue the SQL statement Provide the necessary database privileges Change the user’s quota limit by using the ALTER USER command

• •

Add file space to the tablespace Enable resumable space allocation

Copyright © Oracle Corporation, 2002. All rights reserved.

Causes of User Process Failures

• • •

6-8

The user performed an abnormal disconnect in the session The user’s session was abnormally terminated The user’s program raised an address exception, which terminated the session

Copyright © Oracle Corporation, 2002. All rights reserved.

Resolution of User Process Failures

• •

6-9

The PMON process detects an abnormally terminated user process PMON rolls back the transaction and releases any resources and locks being held by it

Copyright © Oracle Corporation, 2002. All rights reserved.

Possible User Errors

SQL> DROP TABLE employees;

SQL> TRUNCATE TABLE employees;

SQL> DELETE FROM employees; SQL> COMMIT; SQL> UPDATE employees 2> SET salary = salary * 1.5; SQL> COMMIT;

6-10

Copyright © Oracle Corporation, 2002. All rights reserved.

Resolution of User Errors

• • • • • • •

6-11

Train the database users Recover from a valid backup Import the table from an export file Use LogMiner to determine the time of error Recover with a point-in-time recovery Use LogMiner to perform object-level recovery Use FlashBack to view and repair historical data

Copyright © Oracle Corporation, 2002. All rights reserved.

Causes of Instance Failure User process

Instance SGA Shared pool

Server process PGA

User process

Server process

Locks

Large Pool

Data buffer

Redo log buffer

SMON

DBWn PMON CKPT

Shared SQL and PL/SQL Data dict. cache LGWR ARCn

PGA

Parameter file Password file

6-12

146 146 146 Datafile 1 Control Redo log file 1 files 146 145 Redo log Datafile 2 file 2 146 Datafile 3

Database

Copyright © Oracle Corporation, 2002. All rights reserved.

Recovery from Instance Failure

• • • • •

6-13

No special recovery action is needed from DBA Start the instance Wait for the “database opened” notification Notify users Check the alert log to determine the reason for the failure

Copyright © Oracle Corporation, 2002. All rights reserved.

Causes of Media Failures

• • •

6-15

Head crash on a disk drive Physical problem in reading from or writing to database files File was accidentally erased

Copyright © Oracle Corporation, 2002. All rights reserved.

Resolutions for Media Failures

• •

6-16

The recovery strategy depends on which backup method was chosen and which files are affected If available, apply archived redo log files to recover data committed since the last backup

Copyright © Oracle Corporation, 2002. All rights reserved.

Defining a Backup and Recovery Strategy

• • • •

6-17

Business requirements Operational requirements Technical considerations Management concurrence

Copyright © Oracle Corporation, 2002. All rights reserved.

Business Requirements

• • •

6-18

Mean time to recover Mean time between failure Evolutionary process

Copyright © Oracle Corporation, 2002. All rights reserved.

Operational Requirements

• • •

6-19

24-hour operations Testing and validating backups Database volatility

Copyright © Oracle Corporation, 2002. All rights reserved.

Technical Considerations

• • • • •

6-21

Resources: hardware, software, manpower, and time Physical image copies of the operating system files Logical copies of the objects in the database Database configuration Transaction volume that affects desired frequency of backups

Copyright © Oracle Corporation, 2002. All rights reserved.

Disaster Recovery Issues

•

How will your business be affected in the event of a major disaster, such as: – – – –

•

6-23

Earthquake, flood, or fire Complete loss of machine Malfunction of storage hardware or software Loss of key personnel, for example the database administrator

Do you have a plan for testing your strategy periodically?

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Evaluate potential failures in your environment • Develop a strategy dictated by business, operational, and technical requirements • Consider a test plan for a backup and recovery strategy

6-25

Copyright © Oracle Corporation, 2002. All rights reserved.

Instance and Media Recovery Structures

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Describe the Oracle processes, memory structures, and files relating to recovery • Identify the importance of checkpoints, redo log files, and archived log files • Describe ways to tune instance recovery

7-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Overview Instance SGA Shared pool

User process

Server process

Java Pool

Large Pool

Database

Redo log buffer

buffer cache

Shared SQL and PL/SQL Data dict. cache

PGA SMON

Parameter file Password file

7-3

DBWn PMON CKPT

LGWR ARCn

Datafile 1 Control file

Redo log file 1

Datafile 2

Redo log file 2

Datafile 3

Database

Copyright © Oracle Corporation, 2002. All rights reserved.

Archived log files

Large Pool

•

Can be configured as a separate memory area in the SGA to be used for: – Oracle backup and restore operations – I/O server processes – Session memory for the shared servers

•

7-6

Is sized by the LARGE_POOL_SIZE parameter

Copyright © Oracle Corporation, 2002. All rights reserved.

Database Buffer Cache, DBWn, and Data files Instance SGA Shared pool

User process

Server process

Java pool

Large pool

Database

Redo log buffer

buffer cache

PGA SMON

DBW0 PMON CKPT DBW1

Datafile 1

Parameter file Password file

7-9

Shared SQL and PL/SQL Data dict. cache

Control file

Datafile 2

LGWR ARCn

Redo log file 1 Redo log file 2

Datafile 3

Database

Copyright © Oracle Corporation, 2002. All rights reserved.

Archived log files

Redo Log Buffer, LGWR, and Redo Log Files Instance SGA Shared pool

User process

Server process

Java pool

Large pool

Database

Redo log buffer

buffer cache

PGA SMON

DBWn PMON CKPT

Datafile 1

Parameter file Password file

7-11

Shared SQL and PL/SQL Data dict. cache

Control file

LGWR ARCn

Redo log file 1 Redo log file 2

Datafile 2 Datafile 3

Database

Copyright © Oracle Corporation, 2002. All rights reserved.

Archived log files

Multiplexed Redo Log Files

Group 1

Group 2

Disk 1 (Member a)

log1a.rdo

log2a.rdo

log3a.rdo

Disk 2 (Member b)

log1b.rdo

log2b.rdo

log3b.rdo

7-14

Copyright © Oracle Corporation, 2002. All rights reserved.

Group 3

Redo Log Files in Enterprise Manager

7-16

Copyright © Oracle Corporation, 2002. All rights reserved.

Database Checkpoints

7-17

•

Checkpoints are used to determine where recovery should start

• •

Checkpoint position - where recovery starts Checkpoint queue - link list of dirty blocks

Copyright © Oracle Corporation, 2002. All rights reserved.

Types of Checkpoints •

Full checkpoint – All dirty buffers are written – SHUTDOWN NORMAL, IMMEDIATE, or TRANSACTIONAL – ALTER SYSTEM CHECKPOINT

•

Incremental checkpoint (Fast-Start checkpoint) – Periodic writes – Only write the oldest blocks

•

Partial checkpoint – Dirty buffers belonging to the tablespace – ALTER TABLESPACE BEGIN BACKUP – ALTER TABLESPACE tablespace OFFLINE NORMAL

7-18

Copyright © Oracle Corporation, 2002. All rights reserved.

CKPT Process Instance SGA Shared pool

User process

Server process

Java pool

Large pool

Database

Redo log buffer

buffer cache

PGA SMON

DBWn PMON CKPT

Datafile 1

Parameter file Password file

7-19

Shared SQL and PL/SQL Data dict. cache

Control file

LGWR ARCn

Redo log file 1 Redo log file 2

Datafile 2 Datafile 3

Database

Copyright © Oracle Corporation, 2002. All rights reserved.

Archived log files

Multiplexed Control Files Instance SGA Shared pool Java pool

User process

Server process

Large pool

Database

Redo log buffer buffer cache

PGA SMON

DBWn PMON CKPT

Datafile 1

Parameter file Password file

7-20

Shared SQL and PL/SQL Data dict. cache

Control files

LGWR ARCn

Redo log file 1 Redo log file 2

Datafile 2 Datafile 3

Database

Copyright © Oracle Corporation, 2002. All rights reserved.

Archived log files

Control Files in Enterprise Manager

7-22

Copyright © Oracle Corporation, 2002. All rights reserved.

ARCn Process and Archived Log Files Instance SGA Shared pool

User process

Server process

Java pool

Large pool

Shared SQL and PL/SQL

Database

Redo log buffer

Data dict. cache

buffer cache

PGA SMON

DBWn PMON CKPT

Datafile 1

Parameter file Password file

7-23

Control file

LGWR ARC0 ARC1

Redo log file 1 Redo log file 2

Datafile 2 Datafile 3

Database

Copyright © Oracle Corporation, 2002. All rights reserved.

Archived log files dest 2 Archived log files dest 1

Database Synchronization

• • • •

7-25

All datafiles (except offline and read-only) must be synchronized for the database to open. Synchronization is based on the current checkpoint number. Applying changes recorded in the redo log files synchronizes datafiles. Redo log files are automatically requested by the Oracle server.

Copyright © Oracle Corporation, 2002. All rights reserved.

Phases for Instance Recovery 1. Datafiles out-of-synch 2. Roll forward (redo) 3. Committed and noncommitted data in files 4. Roll back (undo) 5. Committed data in files SQL*Plus

Server process

Instance SGA Shared pool Java pool

Shared SQL and PL/SQL Data dict. cache

Large pool

Database

Redo log buffer buffer cache SMON

DBWn PMON CKPT

LGWR ARCn

PGA 146.5 Datafile 1

Undo

146.5 Control file

146.5 Undo Datafile 146.5 Datafile 3

Database 7-26

Copyright © Oracle Corporation, 2002. All rights reserved.

146.5 Redo log file 1 145 Redo log file 2

Checkpoint

Tuning Crash and Instance Recovery Performance • •

7-28

Tuning the duration of instance and crash recovery Tuning the phases of instance recovery

Copyright © Oracle Corporation, 2002. All rights reserved.

Tuning the Duration of Instance and Crash Recovery Methods to keep the duration of instance and crash recovery within user-specified bounds: • Set initialization parameters to influence the number of redo log records and data blocks involved in recovery. • Size the redo log file to influence checkpointing frequency. • Issue SQL statements to initiate checkpoints. • Parallelize instance recovery operations.

7-29

Copyright © Oracle Corporation, 2002. All rights reserved.

Initialization Parameters Influencing Checkpoints Parameter

Definition

FAST_START_MTTR_TARGET

Expected MTTR specified in seconds

LOG_CHECKPOINT_TIMEOUT

Amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log occurred

LOG_CHECKPOINT_INTERVAL Number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log

7-30

Copyright © Oracle Corporation, 2002. All rights reserved.

Controlling Instance Crash Recovery Time

7-32

Copyright © Oracle Corporation, 2002. All rights reserved.

V$INSTANCE_RECOVERY

• •

7-33

Used to monitor the mechanisms that are available to limit recovery I/O Statistics from this view to calculate which parameter has the greatest influence on checkpointing

Copyright © Oracle Corporation, 2002. All rights reserved.

Tuning the Phases of Crash and Instance Recovery • •

7-35

Tuning the roll forward phase Tuning the rollback phase

Copyright © Oracle Corporation, 2002. All rights reserved.

Tuning the Rolling Forward Phase

• •

7-36

Parallel block recovery RECOVERY_PARALLELISM specifies the number of concurrent recovery processes

Copyright © Oracle Corporation, 2002. All rights reserved.

Tuning the Rolling Back Phase

• •

7-37

Fast-start on-demand rollback Fast-start parallel rollback

Copyright © Oracle Corporation, 2002. All rights reserved.

Fast-Start On-Demand Rollback

Server process encountering data to be rolled back performs the following: • Rolls back the block containing the required row • Hands off further recovery, which may be in parallel, to SMON

Improved response

7-38

Copyright © Oracle Corporation, 2002. All rights reserved.

Fast-Start Parallel Rollback

SMON P000 P001 P002

Undo segment

7-39

P003

Copyright © Oracle Corporation, 2002. All rights reserved.

Tables

Controlling Fast-Start Parallel Rollback

FAST_START_PARALLEL_ROLLBACK parameter

7-40

Value

Maximum Parallel Recovery Servers

FALSE

None

LOW (default)

2 * CPU_COUNT

HIGH

4 * CPU_COUNT

Copyright © Oracle Corporation, 2002. All rights reserved.

Monitoring Parallel Rollback

•

•

7-41

V$FAST_START_SERVERS: Provides information about all the recovery slaves performing parallel transaction recovery V$FAST_START_TRANSACTIONS: Contains information about the progress of the transactions that Oracle is recovering

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Identify components of the instance and database that are significant to recovery • Tune crash and instance recovery

7-43

Copyright © Oracle Corporation, 2002. All rights reserved.

Practice 7 Overview

This practice covers the following topics: • Querying dynamic performance views to determine the current state and structure of the database • Explaining the use of specific initialization parameters • Mirroring of the control files and redo log files

7-44

Copyright © Oracle Corporation, 2002. All rights reserved.

User-Managed Recovery Procedures: RECOVER Command •

Recover a mounted database:

SQL> RECOVER DATABASE

Or SQL> RECOVER DATAFILE 2> ’/ORADATA/u03/users01.dbf’

• Recover an open database: •SQL> RECOVER TABLESPACE users Or SQL> RECOVER DATAFILE 2> ’/ORADATA/u03/users01.dbf’

12-16

Copyright © Oracle Corporation, 2002. All rights reserved.

Using Archived Redo Log Files During Recovery •

To change archive location, use the ALTER SYSTEM ARCHIVE LOG. . . command.

•

To apply redo log files automatically: – Issue the SET AUTORECOVERY ON command before starting media recovery. – Enter auto when prompted for an archived log file. – Use the RECOVER AUTOMATIC. . . command.

12-17

Copyright © Oracle Corporation, 2002. All rights reserved.

Restoring Datafiles to a New Location with User-Managed Procedures • •

12-19

Use operating system commands to restore the datafile to the new location. Use the ALTER DATABASE RENAME FILE command to record the change in the control file.

Copyright © Oracle Corporation, 2002. All rights reserved.

Complete Recovery Methods

•

Closed database recovery for: – System datafiles – Undo segment datafiles – Whole database

• • •

12-20

Open database recovery, with database initially opened (for file loss) Open database recovery with database initially closed (for hardware failure) Data file recovery with no datafile backup

Copyright © Oracle Corporation, 2002. All rights reserved.

Complete Recovery of a Closed Database

Closed database recovery is used for: • System tablespace datafiles • Rollback segment datafiles • Whole database

12-22

Copyright © Oracle Corporation, 2002. All rights reserved.

Closed Database Recovery: Example 146 Datafile 1

146 Control files

146 Datafile 2

146 Redo log file 1 145 Redo log file 2

146 Datafile 3

Closed Database 146 Datafile 1 146 Datafile 2

146 Control files

146 Redo log file 1 145 Redo log file 2

146 Datafile 3

1

Shut down the instance

2

Restore datafile 1 (Log Sequence 144)

146 144 146 Datafile 1 Control 3Redo log file 1 files 146 3Redo 145 Datafile 2 log 144 file 2 3 Archived 146 log file Datafile 3

Mounted Database

4

Open the database

Recovered Database

12-23

Copyright © Oracle Corporation, 2002. All rights reserved.

Open Database Recovery When the Database Is Initially Open Use this method when: • The database is currently open • The database will remain open during the recovery • The media failure does not affect the SYSTEM tablespace

12-25

Copyright © Oracle Corporation, 2002. All rights reserved.

Open Database Recovery: Example 146 Datafile 1

146 Control files

146 Datafile 2

146 Redo log file 1 145 Redo log file 2

146 Datafile 3

Open Database

1

Take datafile 2 offline

2

Restore datafile 2 (Log Sequence 144)

146 146 146 Datafile 1 Control Redo log files file 1 144 Datafile 2

146 Datafile 1 146 Datafile 2

146 Control files

146 Redo log file 1 145 Redo log file 2

146 Datafile 3

3 145 Redo log 3 file 2

146 Datafile 3

Open Database

4

Bring datafile 2 online

Recovered Database

12-26

3

Copyright © Oracle Corporation, 2002. All rights reserved.

144 Archived log file

Open Database Recovery When the Database Is Initially Closed Use this method when: • The database is currently closed • The database will be opened during recovery • The media failure does not affect the SYSTEM tablespace

12-28

Copyright © Oracle Corporation, 2002. All rights reserved.

Open Database Recovery: Example 146 Datafile 1

146 Control files

146 Datafile 2

146 Redo log file 1 145 Redo log file 2

146 Datafile 3

Closed Database 146 Datafile 1 146 Datafile 2

146 Control files

146 Redo log file 1 145 Redo log file 2

146 Datafile 3

1 2 3

Mount the database

4

Restore datafile 2

Take datafile 2 offline Open the database

146 146 146 Datafile 1 Control Redo log file 1 files 144 Datafile 2 146 Datafile 3

5 145 Redo log 5 file 2 5

Open Database

6

Bring datafile 2 online

Recovered Database

12-29

Copyright © Oracle Corporation, 2002. All rights reserved.

144 Archived log file

Recovery of a Datafile Without a Backup

• • •

12-31

Datafile is lost that was never backed up Cannot be used when it is a file from the SYSTEM tablespace Cannot be used if the control file has been recreated

Copyright © Oracle Corporation, 2002. All rights reserved.

Re-Creating Lost Datafiles Without Backup

• • •

Used when missing datafile cannot be restored because it had never been backed up. Description of missing datafile is still in data dictionary and control file. Re-create the datafile:

SQL> ALTER DATABASE CREATE DATAFILE ’filename’;

•

Re-create the datafile with a different filename:

SQL> ALTER DATABASE CREATE DATAFILE ’filename’ AS ’new file name’;

12-32

Copyright © Oracle Corporation, 2002. All rights reserved.

Recovery without a Backup: Example 146 Datafile 1

146 Control files

146 Datafile 2 146 Datafile 3 146 Datafile 4

146 Datafile 1 146 Datafile 2 146 Datafile 3

146 Redo log file 1 145 Redo log file 2

Take the datafile or tablespace offline

2

Re-create the datafile

146 146 146 Datafile 1 Control Redo log files file 1

Open Database 146 Control files

146 Redo log file 1 145 Redo log file 2

144 Datafile 2

3 145 Redo 3 file 2log

146 Datafile 3 Datafile 4

Open Database

4

146 Datafile 4

Recovered Database

12-33

1

Bring the datafile or tablespace online

Copyright © Oracle Corporation, 2002. All rights reserved.

3

144 Archived log file

Read-Only Tablespace Recovery

Read-Only

Case 1

Read-Only

Case 2

Read-Write Read-Only

Case 3

Backup 1

12-35

Read-Write

Backup 2

Recovery

Copyright © Oracle Corporation, 2002. All rights reserved.

Read-Only Tablespace Recovery Issues

Special considerations must be taken for read-only tablespaces when: • Re-creating a control file • Renaming datafiles • Using a backup control file

12-36

Copyright © Oracle Corporation, 2002. All rights reserved.

Loss of Control Files

You may need to create control files if: • All control files are lost because of a failure • The name of a database needs to be changed • The current settings in the control file need to be changed

12-37

Copyright © Oracle Corporation, 2002. All rights reserved.

Recovering Control Files

Methods to recover from loss of control file: • Use the current control file • Create a new control file • Use a backup control file

12-38

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Determine what type of recovery is required • Determine which files need to be restored and recovered • Recover a database in NOARCHIVELOG mode • Recover a database in ARCHIVELOG mode • Restore datafiles to different locations if the original location is unavailable

12-39

Copyright © Oracle Corporation, 2002. All rights reserved.

Practice 12 Overview

These practices cover the following topics: • Performing complete database recovery with the database in NOARCHIVELOG mode • Performing complete database recovery with the database in ARCHIVELOG mode

12-40

Copyright © Oracle Corporation, 2002. All rights reserved.

RMAN Complete Recovery

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Describe the use of RMAN for restoration and recovery • Perform complete recovery in ARCHIVELOG mode • Restore datafiles to different locations • Relocate and recover a tablespace by using archived redo log files

13-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Restoration and Datafile Media Recovery Using RMAN • •

13-3

Restore files from backup sets or image copies by using the RMAN RESTORE command Recover files by using the RMAN RECOVER command

Copyright © Oracle Corporation, 2002. All rights reserved.

Using RMAN to Recover a Database in ARCHIVELOG Mode rman target / RMAN> STARTUP MOUNT RMAN> RESTORE DATABASE; RMAN> RECOVER DATABASE; RMAN> ALTER DATABASE OPEN;

13-4

Copyright © Oracle Corporation, 2002. All rights reserved.

Using the Recovery Wizard

13-5

Copyright © Oracle Corporation, 2002. All rights reserved.

Using RMAN to Restore Datafiles to a New Location •

Use the SET NEWNAME command to restore the datafile to the new location. SET NEWNAME FOR DATAFILE 1 to ‘//system01.dbf’;

•

Use the SWITCH command to record the change in the control file. SWITCH DATAFILE ALL;

13-6

Copyright © Oracle Corporation, 2002. All rights reserved.

Restoring to a New Location

13-7

Copyright © Oracle Corporation, 2002. All rights reserved.

Using RMAN to Recover a Tablespace

Use the following RMAN commands to restore and recover a tablespace: • RESTORE TABLESPACE • RECOVER TABLESPACE run{ sql “alter tablespace users offline immediate”; restore tablespace users; recover tablespace users; sql “alter tablespace users online”; }

13-8

Copyright © Oracle Corporation, 2002. All rights reserved.

Tablespace Recovery

13-9

Copyright © Oracle Corporation, 2002. All rights reserved.

Using RMAN to Relocate a Tablespace

• • •

13-10

Use the SET NEWNAME command to restore the files. Use the SWITCH command to record the new names in the control file. Use the RECOVER TABLESPACE command to recover the datafiles of the tablespace.

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Recover a database in ARCHIVELOG mode • Restore datafiles to different locations if the original location is unavailable

13-12

Copyright © Oracle Corporation, 2002. All rights reserved.

Practices 13-1 and 13-2 Overview

These practices cover the following topics: • Using RMAN to recover a tablespace • Using RMAN to recover relocated datafiles

13-13

Copyright © Oracle Corporation, 2002. All rights reserved.

User-Managed Incomplete Recovery

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Describe the steps of incomplete recovery • Perform an incomplete database recovery • Identify the loss of current online redo log files

14-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Incomplete Recovery Overview User process

Instance SGA Shared pool

Server process PGA

User process

Server process

Locks

Large Pool

Data buffer

Redo log buffer

SMON

Shared SQL and PL/SQL Data dict. cache

DBWn PMON CKPT

LGWR ARCn

PGA

Parameter file Password file

14-3

1 1 1 Datafile 1 Control Redo log files file 1 1 0 Redo log Datafile 2 file 2 1 Datafile 3

Database

Copyright © Oracle Corporation, 2002. All rights reserved.

Archived log files

Situations Requiring Incomplete Recovery • • •

Complete recovery fails because an archived log is lost. All unarchived redo log files and a datafile are lost. User error – An important table was dropped. – Invalid data was committed in a table.

•

14-4

Current control file is lost and a backup control file must be used to open the database.

Copyright © Oracle Corporation, 2002. All rights reserved.

Types of Incomplete Recovery

•

There are three types of incomplete recovery: – Time-based recovery – Cancel-based recovery – Change-based recovery

•

You may need to recover using a restored control file when: – Control files are lost – Performing incomplete recovery to a point when the database structure is different than the current

14-5

Copyright © Oracle Corporation, 2002. All rights reserved.

Incomplete Recovery Guidelines

• • • •

14-7

Follow all steps carefully. Take whole database backups before and after recovery. Always verify that the recovery was successful. Back up and remove archived logs.

Copyright © Oracle Corporation, 2002. All rights reserved.

Incomplete Recovery and the Alert Log

• •

14-9

Check the alert log before and after recovery Contains error information, hints, and SCNs

Copyright © Oracle Corporation, 2002. All rights reserved.

User-Managed Procedures for Incomplete Recovery 1. Shut down and back up the database. 2. Restore all datafiles. Do not restore the control file, redo logs, password file, or parameter file. 3. Mount the database. 4. Recover the datafiles to a point before the time of failure. 5. Open the database with RESETLOGS. 6. Perform a closed database backup.

14-10

Copyright © Oracle Corporation, 2002. All rights reserved.

RECOVER Command Overview

•

Recover a database until cancel:

RECOVER DATABASE until cancel

•

Recover a database until time:

RECOVER DATABASE until time ‘2002-07-24:14:22:03’

•

Recover using backup control file:

RECOVER DATABASE until time ‘2002-07-24:14:22:03’ using backup controlfile

14-11

Copyright © Oracle Corporation, 2002. All rights reserved.

Time-Based Recovery: Example

Scenario: • The current time is 12:00 p.m. on March 9, 2002. • The EMPLOYEES table has been dropped. • • •

14-12

The table was dropped at approximately 11:45 a.m. Database activity is minimal because most staff are currently in a meeting. The table must be recovered.

Copyright © Oracle Corporation, 2002. All rights reserved.

UNTIL TIME Recovery 146 146 146 Datafile 1 Control Redo log File 1 Files 146 145 DatafileEMP 2 Redo log File 2 146 Datafile 3

1 2 3

Recovered Database

14-13

Restore all datafiles Mount the database 144 146 146 Datafile 1 Control Redo log File 1 Files 144 145 Datafile EMP 2 Redo log File 2 144 Datafile 3

Database 1 1 1 Datafile 1 Control Redo log File 1 Files 1 0 DatafileEMP 2 Redo log File 2 1 Datafile 3

Shut down and back up

Restored Database

5

Open with Resetlogs

6

Back up the database

Copyright © Oracle Corporation, 2002. All rights reserved.

4

144

Archived log file

Cancel-Based Recovery: Example

Scenario: • The current time is 12:00 p.m. on March 9,2002. • The EMPLOYEES table was dropped while someone was trying to fix bad blocks. • Log files exist on the same disk. • The table was dropped at approximately 11:45 a.m. • Staff are currently in a meeting.

14-15

Copyright © Oracle Corporation, 2002. All rights reserved.

Cancel-Based Recovery: Example

Findings: • Redo logs are not multiplexed. • One of the online redo logs is missing. • The missing redo log is not archived. • The redo log contained information from 11:34 a.m. • Twenty-six minutes of data will be lost. • Users can recover their data.

14-16

Copyright © Oracle Corporation, 2002. All rights reserved.

Using a Backup Control File During Recovery Scenario: • The current time is 12:00 p.m. on March 9, 2002. • The tablespace containing the EMPLOYEES table has been dropped. • The error occurred around 11:45 a.m. • Many employee records were updated this morning, but not since 11:00 a.m. • Backups are taken every night.

14-18

Copyright © Oracle Corporation, 2002. All rights reserved.

Using a Backup Control File During Recovery Findings: • The backup from last night contains datafiles and control files that are required for recovery. • The EMP_TS tablespace has one datafile. • • •

14-19

The current log sequence number is 61. You confirm that the tablespace was dropped at 11:44:54 a.m. on March 9, 2002. Datafile number 4 is offline.

Copyright © Oracle Corporation, 2002. All rights reserved.

Loss of Current Redo Log Files

If the database is closed: • Attempt to open the database. • Find the current log sequence number. • Recover the database using UNTIL CANCEL.

14-21

• •

Drop and re-create log files if necessary. Open the database using RESETLOGS.

•

Perform a whole-database backup.

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Perform incomplete database recovery • Recover from the loss of current online redo log files

14-23

Copyright © Oracle Corporation, 2002. All rights reserved.

Practices 14-1 and 14-2 Overview

These practices cover the following topics: • Recovery from user failure • Recovery with lost archived redo log files

14-24

Copyright © Oracle Corporation, 2002. All rights reserved.

RMAN Incomplete Recovery

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Perform an incomplete database recovery by using UNTIL TIME •

15-2

Perform an incomplete database recovery by using UNTIL SEQUENCE

Copyright © Oracle Corporation, 2002. All rights reserved.

Incomplete Recovery of a Database Using RMAN 1. 2. 3. 4.

Mount the database. Allocate multiple channels for parallelization. Restore all datafiles. Recover the database by using UNTIL TIME, UNTIL SEQUENCE, or UNTIL SCN. 5. Open the database by using RESETLOGS. 6. Perform a whole database backup.

15-3

Copyright © Oracle Corporation, 2002. All rights reserved.

Specifying the Restore Time

15-4

Copyright © Oracle Corporation, 2002. All rights reserved.

RMAN Incomplete Recovery UNTIL TIME: Example RMAN> 2> 3> 4> 5> 6> 7>

15-5

RUN { ALLOCATE CHANNEL c1 TYPE DISK; ALLOCATE CHANNEL c2 TYPE DISK; SET UNTIL TIME = ‘2001-12-09:11:44:00'; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS; }

Copyright © Oracle Corporation, 2002. All rights reserved.

Specifying the Sequence

15-7

Copyright © Oracle Corporation, 2002. All rights reserved.

RMAN Incomplete Recovery UNTIL SEQUENCE: Example RMAN> 2> 3> 4> 5> 6> 7>

15-8

RUN { SET UNTIL SEQUENCE 120 THREAD 1; ALTER DATABASE MOUNT; RESTORE DATABASE; RECOVER DATABASE; # recovers through log 119 ALTER DATABASE OPEN RESESTLOGS; }

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Perform an incomplete database recovery by using UNTIL TIME •

15-9

Perform an incomplete database recovery by using UNTIL SEQUENCE

Copyright © Oracle Corporation, 2002. All rights reserved.

Practice 15 Overview

This practice covers recovery with lost archived redo log files.

15-10

Copyright © Oracle Corporation, 2002. All rights reserved.

RMAN Maintenance

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Perform cross-checking of backups and copies • Update the repository when backups have been deleted • Change the availability status of backups and copies • Make a backup or copy exempt from the retention policy • Catalog backups made with operating system commands

16-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Cross Checking Backups and Copies

Use the CROSSCHECK command to: • • •

16-3

Ensure repository information is synchronized with actual files Check the status of a backup or copy Update the repository when files have been deleted with operating system commands

Copyright © Oracle Corporation, 2002. All rights reserved.

Deleting Backups and Copies

Use the DELETE command to:

16-5

• •

Delete physical backups and image copies Update repository status to DELETED

•

Remove records from the recovery catalog

Copyright © Oracle Corporation, 2002. All rights reserved.

The DELETE Command

•

Delete a specific backup set:

DELETE BACKUPSET 102;

•

Delete an expired backup without the confirmation:

DELETE NOPROMPT EXPIRED BACKUP OF TABLESPACE •users;

•

Delete all backups, copies, and archived redo log files based on the configured retention policy:

DELETE OBSOLETE;

16-6

Copyright © Oracle Corporation, 2002. All rights reserved.

Deleting Backups and Copies

Use the BACKUP … DELETE INPUT command to: • •

16-7

Delete input files upon successful creation of the backup set Delete archived redo log files, datafile copies, and backup sets

Copyright © Oracle Corporation, 2002. All rights reserved.

Changing the Availability of RMAN Backups and Copies •

•

16-8

Change the status of a backup or copy to Unavailable with the CHANGE … UNAVAILABLE command. Return the status to Available with the CHANGE … AVAILABLE command.

Copyright © Oracle Corporation, 2002. All rights reserved.

Changing the Availability Status

•

Change the status of a specific datafile: CHANGE DATAFILECOPY ’/DB01/BACKUP/users01.dbf’ UNAVAILABLE;

•

Change the status of a control file backup: CHANGE BACKUP OF CONTROLFILE UNAVAILABLE; CHANGE BACKUP OF CONTROLFILE AVAILABLE;

•

Change the status of archived redo log files: CHANGE COPY OF ARCHIVELOG SEQUENCE BETWEEN 230 AND 240 UNAVAILABLE;

16-9

Copyright © Oracle Corporation, 2002. All rights reserved.

Exempting a Backup or Copy from the Retention Policy • •

16-10

Use the CHANGE … KEEP command to exempt a backup or copy from the retention policy. Use the CHANGE … NOKEEP command to cancel the exemption.

Copyright © Oracle Corporation, 2002. All rights reserved.

The CHANGE … KEEP Command

•

Create a long-term backup:

CHANGE BACKUPSET 123 KEEP FOREVER NOLOGS;

•

Make a datafile exempt from the retention policy for 60 days:

CHANGE DATAFILECOPY ’/DB01/BACKUP/users01.dbf’ KEEP UNTIL ’SYSDATE+60’;

16-11

Copyright © Oracle Corporation, 2002. All rights reserved.

Cataloging Archived Redo Log Files and User-Managed Backups You can use the CATALOG command to add information to the repository about: • An operating system datafile copy • An archived redo log copy • A control file copy

16-12

Copyright © Oracle Corporation, 2002. All rights reserved.

The CATALOG Command

•

Catalog a backup taken with an operating system command: CATALOG DATAFILECOPY ’/DB01/BACKUP/users01.dbf’;

•

Catalog archived redo log files: CATALOG ARCHIVELOG ’/ORADATA/ARCHIVE1/arch_12.arc’, ’/ORADATA/ARCHIVE1/arch_13.arc’;

16-13

Copyright © Oracle Corporation, 2002. All rights reserved.

Uncataloging RMAN Records

Use the CHANGE … UNCATALOG command to: • Update the record in the repository to DELETED status • Delete a specific backup or copy record from the recovery catalog

16-14

Copyright © Oracle Corporation, 2002. All rights reserved.

The CHANGE … UNCATALOG Command

•

Remove records for deleted archived redo log files: ARCHIVELOG … UNCATALOG; CHANGE

•

Remove records for a deleted datafile:

CHANGE DATAFILECOPY '/DB01/BACKUP/users01.dbf' UNCATALOG;

16-15

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Perform cross-checking of backups and copies • Update the repository when backups have been deleted • Change the availability status of backups and copies • Make a backup or copy exempt from the retention policy • Catalog backups made with operating system commands

16-16

Copyright © Oracle Corporation, 2002. All rights reserved.

Practice 16 Overview

This practice covers the following topics: • Performing cross checking • Cataloging files in the repository

16-17

Copyright © Oracle Corporation, 2002. All rights reserved.

Recovery Catalog Creation and Maintenance

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Describe the contents of the recovery catalog • List the RMAN features which require the recovery catalog • Create the recovery catalog • Maintain the recovery catalog by using RMAN commands • Use RMAN to register, resynchronize, and reset a database

17-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

• • •

17-3

Query the recovery catalog to generate reports and lists Create, store, and run scripts Describe methods for backing up and recovering the recovery catalog

Copyright © Oracle Corporation, 2002. All rights reserved.

Overview Restore/Recovery Backup

Target database

Control file

Recovery Manager (RMAN)

Reporting REPORT LIST

Catalog Maintenance Register Resynchronize Reset Change/Delete/Catalog Backup /Restore/Recover

17-4

Copyright © Oracle Corporation, 2002. All rights reserved.

Enterprise Manager

Stored scripts

Catalog database

Recovery Catalog Contents

The recovery catalog is an optional repository containing information on: • Datafile and archived redo log file backup sets and backup pieces • Datafile copies • Archived redo log files • The physical structure of the target database

17-5

Copyright © Oracle Corporation, 2002. All rights reserved.

Recovery Catalog Contents

The recovery catalog can also contain: • Persistent RMAN configuration settings • Stored job scripts

17-6

Copyright © Oracle Corporation, 2002. All rights reserved.

Benefits of Using a Recovery Catalog

The following features are available only when you use a recovery catalog: • Metadata about multiple target databases in one catalog • Metadata about multiple incarnations of a single target database • Historical metadata • Reporting on the target database at a noncurrent time

17-7

Copyright © Oracle Corporation, 2002. All rights reserved.

Creating Recovery Catalog 1. Create tablespace 2. Create catalog owner 3. Grant privileges 4. Create catalog

Catalog database

5. Connect to target database 6. Register target database

Control file

17-8

Copyright © Oracle Corporation, 2002. All rights reserved.

Target database

Connecting Using a Recovery Catalog

•

Example on UNIX $ ORACLE_SID=db01; export ORACLE_SID $ rman target / RMAN> connect catalog rman_db01/rman_db01@catdb

•

Example on Windows NT C:\> set ORACLE_SID=db01 C:\> rman target / RMAN> connect catalog rman_db01/rman_db01@catdb

17-11

Copyright © Oracle Corporation, 2002. All rights reserved.

Recovery Catalog Maintenance Restore / Recovery Backup

Target database

Control file

Recovery Manager (RMAN)

Reporting REPORT LIST

Catalog Maintenance Register Resynchronize Reset Change/Delete/Catalog Backup /Restore/Recover

17-12

Copyright © Oracle Corporation, 2002. All rights reserved.

Enterprise Manager

Stored scripts

Catalog database

Catalog Maintenance

17-13

Copyright © Oracle Corporation, 2002. All rights reserved.

Resynchronization of the Recovery Catalog Resynchronization of the recovery catalog happens: • Automatically with BACKUP and COPY commands • Manually with RESYNC CATALOG command Catalog database RMAN >

Target Control file database

17-14

Copyright © Oracle Corporation, 2002. All rights reserved.

Using RESYNC CATALOG for Resynchronization Issue the RESYNC CATALOG command when you: • • •

Add or drop a tablespace Add or drop a datafile Relocate a database file $ rman target / catalog rman/rman@catdb RMAN> RESYNC CATALOG;

17-15

Copyright © Oracle Corporation, 2002. All rights reserved.

Resetting a Database Incarnation

•

Use the RESET DATABASE command: – To direct RMAN to create a new database incarnation record – To distinguish between opening with RESETLOGS and an accidental restore operation of an old control file

•

17-16

Open with RESETLOGS after RESET DATABASE

Copyright © Oracle Corporation, 2002. All rights reserved.

Recovery Catalog Reporting Restore/Recovery Backup

Target database

Control file

Recovery Manager (RMAN)

Reporting REPORT LIST

Catalog Maintenance Register Resynchronize Reset Change Backup /Restore/Recover

17-18

Copyright © Oracle Corporation, 2002. All rights reserved.

Enterprise Manager

Stored scripts

Catalog database

Viewing the Recovery Catalog

Data dictionary views: • RC_DATABASE • RC_DATAFILE • RC_STORED_SCRIPT • RC_STORED_SCRIPT_LINE • RC_TABLESPACE

17-19

Copyright © Oracle Corporation, 2002. All rights reserved.

Stored Scripts Restore/Recovery Backup

Target database

Control file

Recovery Manager (RMAN)

Reporting REPORT LIST

Catalog Maintenance Register Resynchronize Reset Change Backup /Restore/Recover

17-21

Copyright © Oracle Corporation, 2002. All rights reserved.

Enterprise Manager

Stored scripts

Catalog database

Script Examples

•

Use CREATE SCRIPT to store a script. RMAN> create script Level0Backup { backup incremental level 0 format ‘/u01/db01/backup/%d_%s_%p’ fileperset 5 (database include current controlfile); sql ‘alter system archive log current’; }

•

Use EXECUTE SCRIPT to run a script. RMAN > run {execute script Level0Backup;}

17-22

Copyright © Oracle Corporation, 2002. All rights reserved.

Managing Scripts

•

Use REPLACE SCRIPT to rewrite a script RMAN> REPLACE SCRIPT Level0Backup { … fileperset 3 … }

•

Use DELETE SCRIPT to remove a script RMAN> DELETE SCRIPT Level0Backup;

•

Use PRINT SCRIPT to display a script RMAN> PRINT SCRIPT Level0Backup;

17-23

Copyright © Oracle Corporation, 2002. All rights reserved.

Backup of Recovery Catalog

• • •

Whole database backup of the database containing the recovery catalog Tablespace backup of the tablespace containing the recovery catalog Export: – If catalog database is not very large, you can export the database at regular intervals. – If catalog database is large, export the schema containing the recovery catalog.

17-24

Copyright © Oracle Corporation, 2002. All rights reserved.

Recovering the Recovery Catalog

• • •

17-25

Create a database from a previous backup of the recovery catalog database. Relocate the catalog into another database and import the data. Import the entire database from an export.

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned that: • Before using the recovery catalog, you must register the target database • You should resynchronize the catalog frequently using the control file • Scripts can be stored in the recovery catalog

17-26

Copyright © Oracle Corporation, 2002. All rights reserved.

Practice 17 Overview

This practice covers the following topics: • Creating the recovery catalog • Registering a target database with the recovery catalog • Listing the incarnation of a target database • Storing a script in the recovery catalog and executing it

17-27

Copyright © Oracle Corporation, 2002. All rights reserved.

Export and Import Utilities

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Describe the uses of the Export and Import utilities • Describe Export and Import concepts and structures • Perform simple Export and Import operations • List guidelines for using Export and Import

18-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle Export and Import Utilities

Oracle database Export

Oracle database

18-3

Import

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle Export and Import Utility Overview

You can use these utilities to do the following: • Archive historical data • Save table definitions to protect them from user error failure • Move data between machines and databases or between different versions of the Oracle server • Transport tablespaces between databases

18-4

Copyright © Oracle Corporation, 2002. All rights reserved.

Methods of Invoking the Export and Import Utilities • • • •

18-6

Command-line interface An interactive dialog Parameter files Oracle Enterprise Manager

Copyright © Oracle Corporation, 2002. All rights reserved.

Export Modes

Table Mode

User Mode

Tablespace Mode

Table definitions

Tables definitions

Table definitions

Table data (all or Tables data selected rows)

Tables definitions Tables data

Grants

Owner’s table grants

Owner’s grants

Owner’s table indexes

Owner’s indexes Indexes

Table constraints

Tables constraints

Table constraints Triggers

18-7

Full Database Mode

Copyright © Oracle Corporation, 2002. All rights reserved.

Grants Indexes Tables constraints

Invoking Export •

Syntax: exp keyword = value, value2, … ,valuen

•

Examples: exp hr/hr TABLES=employees,departments rows=y file=exp1.dmp exp system/manager OWNER=hr direct=y file=expdat.dmp exp \'username/password AS SYSDBA\' TRANSPORT_TABLESPACE=y TABLESPACES=ts_emp log=ts_emp.log

18-9

Copyright © Oracle Corporation, 2002. All rights reserved.

Using the Export Wizard

18-12

Copyright © Oracle Corporation, 2002. All rights reserved.

Direct-Path Export Concepts Oracle Server

Export Generate SQL commands

Two-Task SQL Buffer common command cache (TTC) processing manager

Write blocks

Export TTC buffer

Dump file

Evaluating buffer

Buffer cache

Direct Path Direct Path Conventional Path

18-13

Copyright © Oracle Corporation, 2002. All rights reserved.

Read blocks

Direct-Path Export Features

• • •

18-14

The type of Export is indicated on the screen output, export dump file, and the log file. Data is already in the format that Export expects, avoiding unnecessary data conversion. Uses an optimized SQL SELECT statement.

Copyright © Oracle Corporation, 2002. All rights reserved.

Direct-Path Export Restrictions

• • •

18-15

The direct-path option cannot be invoked interactively. Client-side and server-side character sets must be the same. The BUFFER parameter has no affect.

Copyright © Oracle Corporation, 2002. All rights reserved.

Specifying Direct-Path Export

•

As command line argument to the Export expcommand: userid=hr/hr full=y direct=y

•expAsparfile=<Parameter a keyword in a parameter file: file> Parameter file …..(Other Parameters) DIRECT = Y …...(Other Parameters)

18-16

Copyright © Oracle Corporation, 2002. All rights reserved.

Uses of the Import Utility for Recovery

• • • •

18-17

Create table definitions Extract data from a valid Export file Import from a complete or cumulative Export file Recover from user-error failures

Copyright © Oracle Corporation, 2002. All rights reserved.

Import Modes

18-18

Mode

Description

Table

Import specified tables into a schema.

User

Import all objects that belong to a schema

Tablespace

Import all definitions of the objects contained in the tablespace

Full Database

Import all objects from the export file

Copyright © Oracle Corporation, 2002. All rights reserved.

Invoking Import •

Syntax: imp keyword = value or keyword = value, value2, … value n

•

Examples: imp hr/hr TABLES=employees,departments rows=y file=exp1.dmp imp system/manager FROMUSER=hr file=exp2.dmp imp \'username/password AS SYSDBA\' TRANSPORT_TABLESPACE=y TABLESPACES=ts_employees

18-19

Copyright © Oracle Corporation, 2002. All rights reserved.

Using the Import Wizard

18-21

Copyright © Oracle Corporation, 2002. All rights reserved.

Invoking Import as SYSDBA

•

You need to invoke Import as SYSDBA under the following conditions: – At the request of Oracle technical support – When importing a transportable tablespace set

•

To invoke Import as SYSDBA: imp \’username/password AS SYSDBA\’

18-22

Copyright © Oracle Corporation, 2002. All rights reserved.

Import Process Sequence

1. 2. 3. 4. 5. 6.

18-23

New tables are created Data is imported Indexes are built Triggers are imported Integrity constraints are enabled on the new tables Any bitmap, functional, and/or domain indexes are built

Copyright © Oracle Corporation, 2002. All rights reserved.

Globalization Support Considerations

•

• •

18-24

The Export file identifies the character encoding scheme that is used for the character data in the file. The Import utility translates data to the character set of its host system. A multibyte character set Export file must be imported into a system that has the same characteristics.

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Describe the uses of Export and Import • Describe Export and Import concepts and structures • Perform simple Export and Import operations • List guidelines for using Export and Import

18-25

Copyright © Oracle Corporation, 2002. All rights reserved.

Practice 18 Overview

This practice covers the following topics: • Using the Export utility • Using the Import utility

18-26

Copyright © Oracle Corporation, 2002. All rights reserved.

Workshop

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: • Document a database configuration by using the Database Configuration Worksheet • Configure an Oracle9i database to support stated business requirements • Recover a failed database while minimizing down time and data loss

19-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

• •

Enable and use trace output for troubleshooting Identify and troubleshoot: – Listener problems – Client configuration issues

19-3

Copyright © Oracle Corporation, 2002. All rights reserved.

Workshop Methodology

• • • • • •

19-4

Group-oriented and interactive Intensive hands-on diagnosis and problem resolution Variety of failure scenarios Recovery solutions Variety of configuration errors Develop troubleshooting skills

Copyright © Oracle Corporation, 2002. All rights reserved.

Workshop Approach

•

Physical investigation: – – – –

•

Use views and tools to derive information View trace files and log files View command output and log files Use views and tools to confirm proper database configuration

Database configuration: – Archiving is enabled – Control files and log files are mirrored – Control file is backed up

19-6

Copyright © Oracle Corporation, 2002. All rights reserved.

Business Requirements

• • • •

19-7

Twenty-four hour availability Peak usage varies across all time zones Daily backups are required Complete database recovery is required

Copyright © Oracle Corporation, 2002. All rights reserved.

Resolving a Database Failure

• • • •

19-8

Phase I: Diagnose the problem Phase II: Restore appropriate files Phase III: Recover the database Phase IV: Back up the database

Copyright © Oracle Corporation, 2002. All rights reserved.

Resolving a Network Failure

• • • • •

19-10

Use OS utilities like ping and telnet to test network connectivity. Use Oracle Net utilities to test service connectivity. Check log files initially to diagnose problems. Use tracing only when needed. Trim the trace files regularly because of their potential growth.

Copyright © Oracle Corporation, 2002. All rights reserved.

Enable Tracing

• •

Oracle Net Manager Edit sqlnet.ora file

TRACE_DIRECTORY_CLIENT = /u01/user01/NETWORK/LOG NAMES.DEFAULT_DOMAIN = us.oracle.com TRACE_UNIQUE_CLIENT = on TRACE_FILE_CLIENT = client.trc TRACE_LEVEL_CLIENT = SUPPORT NAMES.DIRECTORY_PATH= (TNSNAMES)

19-11

Copyright © Oracle Corporation, 2002. All rights reserved.

Using Trace Files

Trace files will give you a better understanding of: • The flow of packets between network nodes • Which component of Oracle Net is failing • Pertinent error codes

19-12

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

• • • • • •

19-14

Instructor-facilitated workshop Group-oriented Hands-on approach Simulated “real-world” environment Minimize down time and data loss Use tools and diagnostics to determine the type of failure

Copyright © Oracle Corporation, 2002. All rights reserved.

Loading Data into a Database

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives

After completing this lesson you should be able to do the following: • Demonstrate usage of direct-load insert operations • Describe the usage of SQL*Loader • Perform basic SQL*Loader operations • List guidelines for using SQL*Loader and directload INSERT

D-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Data Loading Methods Other applications

Export

Oracle database

SQL*Loader

Import Oracle database Direct-load INSERT

D-3

Copyright © Oracle Corporation, 2002. All rights reserved.

Direct-Load INSERT

Direct-load INSERT can be performed in the following ways: • Normal (serially) or in parallel • Into partitioned tables, nonpartitioned tables, or single partitions of a table • With or without logging of redo data

D-4

Copyright © Oracle Corporation, 2002. All rights reserved.

Serial Direct-Load INSERT

INSERT /*+ APPEND */ INTO emp NOLOGGING SELECT * FROM t_employees; COMMIT; EMPLOYEES table

Used block Free space after delete D-6

Server process

High-water mark Blocks used by inserted rows

Copyright © Oracle Corporation, 2002. All rights reserved.

Parallel Direct-Load INSERT ALTER SESSION ENABLE PARALLEL DML; INSERT /*+PARALLEL(hr.employees,2) */ INTO hr.employees NOLOGGING SELECT * FROM hr.old_employees;

EMPLOYEES table

Used block Free space after delete D-7

Slave process

Slave process

High-water mark Temporary segments

Copyright © Oracle Corporation, 2002. All rights reserved.

SQL*Loader Loader control file Input datafiles

Parameter file (optional) Discarded

SQL*Loader Field processing Accepted Record selection

Rejected

Bad file

Selected Oracle server Discard file (optional)

Rejected Inserted Log file

Database datafiles D-9

Copyright © Oracle Corporation, 2002. All rights reserved.

Conventional and Direct Path Loads Instance SGA

Shared pool

Array insert Conventional

Table

Data save

Space used only by conventional load D-11

Direct path

High-water mark

Copyright © Oracle Corporation, 2002. All rights reserved.

Comparing Direct and Conventional Path Loads

D-13

Conventional Load

Direct Path Load

Uses COMMITs to make changes permanent

Uses data saves

Redo log entries always generated

Generates redo only under specific conditions

Enforces all constraints

Enforces only primary key, unique, and NOT NULL

INSERT triggers fire

INSERT triggers do not fire

Can load into clustered tables

Cannot load into clustered tables

Other users can make changes to tables

Other users cannot make changes to tables

Copyright © Oracle Corporation, 2002. All rights reserved.

Parallel Direct-Path Load Temporary segments load1.dat load1.ctl load2.dat load2.ctl load3.dat load3.ctl

SQL*Loader SQL*Loader

SQL*Loader

Table

High-water mark D-15

Copyright © Oracle Corporation, 2002. All rights reserved.

Using SQL*Loader $sqlldr hr/hr \ > control=case1.ctl \ > log=case1.log direct=Y case1.ctl

SQL*Loader

EMPLOYEES table

D-16

Copyright © Oracle Corporation, 2002. All rights reserved.

case1.log

SQL*Loader Control File

The loader control file tells SQL*Loader: • Where to find the load data • The data format • Configuration details – Memory management – Record rejection – Interrupted load handling details

•

D-18

How to manipulate the data

Copyright © Oracle Corporation, 2002. All rights reserved.

Control File Syntax Considerations

• • •

•

D-21

The syntax is free-format Syntax is case insensitive Comments extend from the two hyphens ( -- ) that mark the beginning of the comment to the end of the line The CONSTANT keyword is reserved

Copyright © Oracle Corporation, 2002. All rights reserved.

Input Data and Datafiles

• • •

SQL*Loader reads data from one or more files specified in the control file From SQL*Loader’s perspective, the data in the datafile is organized as records A datafile can be in one of three formats: – Fixed-record format – Variable-record format – Stream-record format

D-22

Copyright © Oracle Corporation, 2002. All rights reserved.

Logical Records

SQL*Loader can be instructed to follow one of the following two logical record-forming strategies: • Combine a fixed number of physical records to form each logical record • Combine physical records into logical records while a certain condition is true

D-25

Copyright © Oracle Corporation, 2002. All rights reserved.

Data Conversion

During a conventional path load, data fields in the datafile are converted into columns in the database in two steps: • The field specifications in the control file are used to interpret the format of the datafile and convert it to a SQL INSERT statement using that data •

D-26

The Oracle database server accepts the data and executes the INSERT statement to store the data in the database

Copyright © Oracle Corporation, 2002. All rights reserved.

Discarded or Rejected Records

•

Bad file – SQL*Loader rejects records when the input format is invalid – If the Oracle database finds that the row is invalid, the record is rejected and SQL*Loader puts it in the bad file

•

Discard file – This can be used only if it has been enabled – This file contains records that were filtered out because they did not match any record-selection criteria specified in the control file

D-27

Copyright © Oracle Corporation, 2002. All rights reserved.

Log File Contents

• • • • • • •

D-29

Header Information Global Information Table Information Datafile Information Table Load Information Summary Statistics Additional statistics for direct path loads and multithreading Information

Copyright © Oracle Corporation, 2002. All rights reserved.

SQL*Loader Guidelines

• • •

Use a parameter file to specify commonly used command line options Place data within the control file only for a small, one-time load Improve performance by: – Allocating sufficient space – Sorting the data on the largest index – Specifying different files for temporary segments for parallel loads

D-31

Copyright © Oracle Corporation, 2002. All rights reserved.

Summary

In this lesson, you should have learned how to: • Describe the usage of SQL*Loader • Perform basic SQL*Loader operations • Demonstrate proficiency using direct-load INSERT operations • List guidelines for using SQL*Loader and directload INSERT

D-32

Copyright © Oracle Corporation, 2002. All rights reserved.