Oracle数据库维护培训胶片集成产品部.

Oracle数据库维护培训胶片集成产品部

Oracle 体系结构

目标通过这部分课程的学习，您将了解以下内容: 描述 Oracle 服务器的体系结构及其主要构件
目标通过这部分课程的学习，您将了解以下内容: 描述 Oracle 服务器的体系结构及其主要构件列举用户连接到 Oracle 实例所涉及的结构双机模式：RAC/HOT STANDBY Objectives This lesson introduces the Oracle server architecture by examining the physical, memory, process, and logical structures involved in establishing a database connection, creating a session, and executing SQL commands.

主要组件一览实例用户端进程系统全局区服务器进程程序全局区共享池库缓冲区数据块高速缓冲区重做日志缓冲区数据字典缓冲区
PMON SMON DBWR LGWR CKPT Others 程序全局区参数文件数据文件控制文件重做日志文件归档日志文件 Overview of Primary Components The Oracle architecture includes a number of primary components, which are discussed further in this lesson. Oracle server: There are several files, processes, and memory structures in an Oracle server; however, not all of them are used when processing a SQL statement. Some are used to improve the performance of the database, ensure that the database can be recovered in the event of a software or hardware error, or perform other tasks necessary to maintain the database. The Oracle server consists of an Oracle instance and an Oracle database. Oracle instance: An Oracle instance is the combination of the background processes and memory structures. The instance must be started to access the data in the database. Every time an instance is started, a System Global Area (SGA) is allocated and Oracle background processes are started. Background processes perform functions on behalf of the invoking process. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user. The background processes perform input/output (I/O) and monitor other Oracle processes to provide increased parallelism for better performance and reliability. Oracle database: An Oracle database consists of operating system files, also known as database files, that provide the actual physical storage for database information. The database files are used to ensure that the data is kept consistent and can be recovered in the event of a failure of the instance. Overview of Primary Components (continued) Other key files: Nondatabase files are used to configure the instance, authenticate privileged users, and recover the database in the event of a disk failure. User and server processes: The user and server processes are the primary processes involved when a SQL statement is executed; however, other processes may help the server complete the processing of the SQL statement. Other processes: Many other processes exist that are used by other options within Oracle, such as Advanced Queuing, Real Application Clusters, Shared Server, Advanced Replication, and so on. These processes are discussed within their respective courses. 密码文件数据库

Oracle服务器一个Oracle服务器: 是一个提供开放式的、全面的、综合性信息管理服务的数据库管理系统
Oracle Server The Oracle server can run on a number of different computers in one of the following ways: Client-Application Server-Server Client-Server Host-Based Client-Application Server-Server: (Three-tier) Users access the database from their personal computers (clients) through an application server, which is used for the application’s processing requirements. Client-Server: (Two-tier) Users access the database from their personal computer (client) over a network, and the database sits on a separate computer (server). Host-Based: Users are connected directly to the same computer on which the database resides.

一个Oracle 实例: Oracle实例是一种访问 Oracle 数据库的基本方式总是打开一个且只打开一个数据库
由内存结构和进程结构组成实例 SGA Shared pool 内存结构 Library cache Database buffer cache Redo log buffer cache Oracle Instance An Oracle instance consists of the System Global Area (SGA) memory structure and the background processes used to manage a database. An instance is identified by using methods specific to each operating system. The instance can open and use only one database at a time. Data Dict. cache 后台进程 PMON SMON DBWR LGWR CKPT Others

创建连接和会话服务器进程创建连接创建对话 Oracle 服务器用户进程数据库用户
创建连接创建对话数据库用户用户进程 Oracle 服务器服务器进程 Connecting to an Oracle Instance Before users can submit SQL statements to an Oracle database, they must connect to an instance. The user starts a tool such as SQL*Plus or runs an application developed using a tool such as Oracle Forms. This application or tool is executed as a user process. In the most basic configuration, when a user logs on to the Oracle server, a process is created on the computer running the Oracle server. This process is called a server process. The server process communicates with the Oracle instance on behalf of the user process that runs on the client. The server process executes SQL statements on behalf of the user. Connection A connection is a communication pathway between a user process and an Oracle server. A database user can connect to an Oracle server in one of three ways: The user logs on to the operating system running the Oracle instance and starts an application or tool that accesses the database on that system. The communication pathway is established using the interprocess communication mechanisms available on the host operating system. Connecting to an Oracle Instance (continued) The user starts the application or tool on a local computer and connects over a network to the computer running the Oracle instance. In this configuration, called client-server, network software is used to communicate between the user and the Oracle server. In a three-tiered connection, the user’s computer communicates over the network to an application or a network server, which is connected through a network to the machine running the Oracle instance. For example, the user runs a browser on a computer on a network to use an application residing on an NT server that retrieves data from an Oracle database running on a UNIX host. Sessions A session is a specific connection of a user to an Oracle server. The session starts when the user is validated by the Oracle server, and it ends when the user logs out or when there is an abnormal termination. For a given database user, many concurrent sessions are possible if the user logs on from many tools, applications, or terminals at the same time. Except for some specialized database administration tools, starting a database session requires that the Oracle server be available for use. Note: The type of connection explained here, where there is a one-to-one correspondence between a user and server process, is called a dedicated server connection. When using a shared server configuration, it is possible for multiple user processes to share server processes.

Oracle数据库 : Oracle 数据库是作为一个单元对待的数据的集合体由三种文件类型组成 Oracle 数据库数据文件控制文件
重做日志文件参数文件归档日志文件 An Oracle Database The general purpose of a database is to store and retrieve related information. An Oracle database has a logical and a physical structure. The physical structure of the database is the set of operating system files in the database. An Oracle database consists of three file types. Data files containing the actual data in the database Redo logs containing a record of changes made to the database to enable recovery of the data in case of failures Control files containing information necessary to maintain and verify database integrity Other Key File Structures The Oracle server also uses other files that are not part of the database: The parameter file defines the characteristics of an Oracle instance. For example, it contains parameters that size some of the memory structures in the SGA. The password file authenticates users privileged to start up and shut down an Oracle instance. Archived redo log files are offline copies of the redo log files that may be necessary to recover from media failures. 密码文件

Oracle数据库的物理存储是由实际的操作系统文件决定
物理结构 Oracle数据库的物理存储是由实际的操作系统文件决定控制文件数据文件重做日志文件控制文件数据文件 (包括数据字典) 标题联机重做日志文件 Physical Structure Other key files exist and are required to start up and use a database, for example: parameter files, configuration files, password files and so on. However, the physical structure of an Oracle database includes only three types of files: control files, data files, and redo log files.

Oracle的内存结构由以下两个内存区域构成:
系统全局区（SGA）: 在实例启动的时候分配,是Oracle实例中的一个基本成分程序全局区（PGA）: 服务器进程启动时分配

系统全局区（SGA）由几部分内存区域组成:
共享池数据块高速缓冲区重做日志缓冲区有两个可选择的内存结构能配置在系统全局区内: 大型池 JAVA池 System Global Area The SGA is also called the shared global area. It is used to store database information that is shared by database processes. It contains data and control information for the Oracle server and is allocated in the virtual memory of the computer where Oracle resides. The following statement can be used to view SGA memory allocations: SHOW SGA; Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes System Global Area (SGA) (continued) Dynamic SGA A dynamic SGA implements an infrastructure that allows the SGA configuration to change without shutting down the instance. This then allows the sizes of the database buffer cache, shared pool, and large pool to be changed without shutting down the instance. Conceivably, the database buffer cache, shared pool, and large pool could be initially under configured and would grow and shrink depending upon their respective work loads, up to a maximum of SGA_MAX_SIZE. Sizing the SGA The size of the SGA is determined by several initialization parameters. The parameters that most affect SGA size are: DB_CACHE_SIZE: The size of the cache of standard blocks. LOG_BUFFER: The number of bytes allocated for the redo log buffer cache. SHARED_POOL_SIZE: The size in bytes of the area devoted to shared SQL and PL/SQL. LARGE_POOL_SIZE: The size of the large pool; the default is zero. Note: Dynamic SGA and sizing are covered in further detail in the Oracle9i Performance Tuning course.

系统全局区系统全局区分配大小上限由SGA_MAX_SIZE参数决定. 系统全局区分配原则依赖其中的组件: 分配邻近的虚拟内存
System Global Area Unit of Allocation A granule is a unit of contiguous virtual memory allocation. The size of a granule depends on the estimated total SGA size whose calculation is based on the value of the parameter SGA_MAX_SIZE. 4 MB if estimated SGA size is < 128 MB 16 MB otherwise The components (buffer cache, shared pool, and large pool) are allowed to grow and shrink based on granule boundaries. For each component which owns granules, the number of granules allocated to the component, any pending operations against the component (e.g., allocation of granules via ALTER SYSTEM, freeing of granules via ALTER SYSTEM, corresponding self-tuning), and target size in granules will be tracked and displayed by the V$BUFFER_POOL view. At instance startup, the Oracle server allocates granule entries, one for each granule to support SGA_MAX_SIZE bytes of address space. As startup continues, each component acquires as many granules as it requires. The minimum SGA configuration is three granules (one granule for fixed SGA (includes redo buffers; one granule for buffer cache; one granule for shared pool).

共享存储区用来存储最近最多执行的SQL语句和最近最多使用的数据定义. 库缓冲区数据字典缓冲区它由两个主要的内存存结构组成:
大小由参数SHARED_POOL_SIZE决定. Shared pool Data dictionary cache Library Shared Pool The shared pool environment contains both fixed and variable structures. The fixed structures remain relatively the same size, whereas the variable structures grow and shrink based on user and program requirements. The actual sizing for the fixed and variable structures is based on an initialization parameter and the work of an Oracle internal algorithm. Sizing the Shared Pool Since the shared pool is used for objects that can be shared globally, such as reusable SQL execution plans; PL/SQL packages, procedures, and functions; and cursor information, it must be sized to accommodate the needs of both the fixed and variable areas. Memory allocation for the shared pool is determined by the SHARED_POOL_SIZE initialization parameter. It can be dynamically resized using ALTER SYSTEM SET. After performance analysis, this can be adjusted but the total SGA size cannot exceed SGA_MAX_SIZE. ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

库缓冲区存储最近使用的SQL和PL/SQL语句信息:
库缓存区库缓冲区存储最近使用的SQL和PL/SQL语句信息: 它能够使普遍使用的语句能够共享通过LRU算法进行管理由两种结构组成: 共享SQL 区域共享 PL/SQL 区域其中各自的大小由共享池内部指定 Library Cache The library cache size is based on the sizing defined for the shared pool. Memory is allocated when a statement is parsed or a program unit is called. If the size of the shared pool is too small, statements are continually reloaded into the library cache, which affects performance. The library cache is managed by a least recently used (LRU) algorithm. As the cache fills, less recently used execution paths and parse trees are removed from the library cache to make room for the new entries. If the SQL or PL/SQL statements are not reused, they eventually are aged out. The library cache consists of two structures: Shared SQL: The Shared SQL stores and shares the execution plan and parse tree for SQL statements run against the database. The second time that an identical SQL statement is run, it is able to take advantage of the parse information available in the shared SQL to expedite its execution. To ensure that SQL statements use a shared SQL area whenever possible, the text, schema, and bind variables must be exactly the same. Shared PL/SQL: The shared PL/SQL area stores and shares the most recently executed PL/SQL statements. Parsed and compiled program units and procedures (functions, packages, and triggers) are stored in this area.

数据字典缓冲区是数据库里最经常使用的对象定义的集合.
它包括数据文件名、表、索引、列、用户权限和其它数据库对象等信息. 在解析期间,服务器进程会查找数据字典获取允许接入的关联对象信息. 缓存数据字典信息在内存区能提高查询数据的响应时间. 它的大小由共享池的大小决定. Data Dictionary Cache The data dictionary cache is also referred to as the dictionary cache or row cache. Caching data dictionary information into memory improves response time. Information about the database (user account data, data file names, segment names, extent locations, table descriptions, and user privileges) is stored in the data dictionary tables. When this information is needed by the database, the data dictionary tables are read, and the data that is returned is stored in the data dictionary cache. Sizing the Data Dictionary The overall size is dependent on the size of the shared pool size and is managed internally by the database. If the data dictionary cache is too small, then the database has to query the data dictionary tables repeatedly for information needed by the database. These queries are called recursive calls and are slower than the queries that are handled by the data dictionary cache.

数据块高速缓冲区数据块高速缓冲区储存以前从数据文件中取出过的数据块的拷贝信息. 当你得到或修改数据时,它能使性能得到提高.
它通过LRU算法进行管理. 由DB_BLOCK_SIZE决定大小. Database buffer cache Database Buffer Cache When a query is processed, the Oracle server process looks in the database buffer cache for any blocks it needs. If the block is not found in the database buffer cache, the server process reads the block from the data file and places a copy in the database buffer cache. Because subsequent requests for the same block may find the block in memory, the requests may not require physical reads. The Oracle server uses a least recently used algorithm to age out buffers that have not been accessed recently to make room for new blocks in the database buffer cache.

数据块高速缓冲区由独立的子缓存组成: 数据库缓冲区可以动态的改变尺寸.
DB_CACHE_SIZE DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE 数据库缓冲区可以动态的改变尺寸. DB_CACHE_ADVICE 可以设置以收集统计信息用来预测不同的缓冲大小 Database Buffer Cache (continued) Sizing the Database Buffer Cache The size of each buffer in the buffer cache is equal to the size of an Oracle block, and it is specified by the DB_BLOCK_SIZE parameter. The database buffer cache consists of independent sub-caches for buffer pools and for multiple block sizes. The parameter DB_BLOCK_SIZE determines the primary block size, which is used for the SYSTEM tablespace. Three parameters define the sizes of the buffer caches: DB_CACHE_SIZE: Sizes the default buffer cache size only, it always exists and cannot be set to zero. DB_KEEP_CACHE_SIZE: Sizes the keep buffer cache, which is used to retain blocks in memory that are likely to be reused. DB_RECYCLE_CACHE_SIZE: Sizes the recycle buffer cache, which is used to eliminate blocks from memory that have little change of being reused. Multiple Block Sizes An Oracle database can be created with a standard block size and up to four non-standard block sizes. Non-standard block sizes can have any power-of-two value between 2 KB and 32 KB. Note: Multiple block sizing is covered further in the Oracle9i Performance Tuning course. Database Buffer Cache (Continued) Buffer Cache Advisory Parameter The buffer cache advisory feature enables and disables statistics gathering for predicting behavior with different cache sizes. The information provided by these statistics can help DBA size the buffer cache optimally for a given workload. The buffer cache advisory information is collected and displayed through the V$DB_CACHE_ADVICE view. The buffer cache advisory is enabled via the initialization parameter DB_CACHE_ADVICE. It is a dynamic parameter via ALTER SYSTEM. Three values (OFF, ON, READY) are available. DB_CACHE_ADVICE Parameter Values OFF: Advisory is turned off and the memory for the advisory is not allocated ON: Advisory is turned on and both cpu and memory overhead is incurred Attempting to set the parameter to this state when it is in the OFF state may lead to ORA Inability to allocate from the shared pool when the parameter is switched to ON. If the parameter is in a READY state it can be set to ON without error since the memory is already allocated. READY: Advisory is turned off but the memory for the advisory remains allocated. Allocating the memory before the advisory is actually turned on will avoid the risk of ORA If the parameter is switched to this state from OFF, it is possible than an ORA-4031 will be raised Note: Resizing the buffer caches dynamically, the database buffer advisory, and the use and interpretation of the V$DB_CACHE_ADVICE are covered further in the Oracle9i Performance Tuning course ALTER SYSTEM SET DB_CACHE_SIZE = 96M;

重做日志缓冲区重做日志缓冲区记录数据块的所有变化. 首要目的是恢复. 记录的信息可用来重做改变. 尺寸大小由LOG_BUFFER定义.
Redo log buffer cache Redo Log Buffer Cache The redo log buffer cache is a circular buffer that contains changes made to data file blocks. This information is stored in redo entries. Redo entries contain the information necessary to recreate the data prior to the change made by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Sizing the Redo Log Buffer Cache The size of the redo log buffer cache is defined by the initialization parameter LOG_BUFFER. Note: Sizing the Redo Log Buffer Cache is covered in further detail in the Oracle9i Performance Tuning course. Refer to the Managing Redo Log Files lesson for details regarding redo log files.

大型池大型池是在SGA内一种可选择的存贮区域,只配置在共享服务器环境中. 能减轻在共享池的负担. 用在UGA、备份和恢复等操作中.
不使用 LRU 列表算法. 大小由 LARGE_POOL_SIZE决定. Large Pool When users connect through the shared server, Oracle needs to allocate additional space in the shared pool for storing information about the connections between the user processes, dispatchers, and servers. The large pool relieves the burden on areas within the shared pool. The shared pool does not have to give up memory for caching SQL parse trees in favor of shared server session information, I/O, and backup and recover processes. The performance gain is from the reduction of overhead from increasing and shrinkage of the shared SQL cache. Backup and Restore Recovery Manager (RMAN) uses the large pool when the BACKUP_DISK_IO= n and BACKUP_TAPE_IO_SLAVE = TRUE parameters are set. If the large pool is configured but is not large enough, the allocation of memory from the large pool fails. RMAN writes an error message to the alert log file and does not use I/O slaves for backup or restore. Sizing the Large Pool The large pool is sized in bytes defined by the LARGE_POOL_SIZE parameter. ALTER SYSTEM SET LARGE_POOL_SIZE = 64M;

Java 区 Java 区是为了解开Java命令：如果安装并使用 Java 是必需的. 在数据库表格中,它存贮的方式非常像PL/SQL .
它的尺寸由JAVA_POOL_SIZE 参数决定. Java Pool The Java pool is an optional setting but is required if installing and using Java. Its size is set, in bytes, using the JAVA_POOL_SIZE parameter. In Oracle9i, the default size of the Java Pool is 24M.

程序全局区 PGA是为每个用户进程连接Oracle数据库分配的区域 PGA Dedicated server Shared server
Server process Session information sort area, cursor information sort area, cursor information Stack space Stack space User process SGA SGA Session information Program Global Area Components The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. In contrast to the SGA, which is shared by several processes, the PGA is an area that is used by only one process. In a dedicated server configuration, the PGA includes these components: Sort area: Used for any sorts that may be required to process the SQL statement Session information: Includes user privileges and performance statistics for the session Cursor state: Indicates the stage in the processing of the SQL statements that are currently used by the session Stack space: Contains other session variables Note: Some of these structures are stored in the SGA when using a shared server configuration. If using a shared server configuration, it is possible for multiple user processes to share server processes. If a large pool is created, the structures are stored in the large pool; otherwise, they are stored in the shared pool. Shared SQL areas Shared SQL areas

进程结构 Oracle进程是一种程序,依赖它要求信息的类型,执行一系列的步骤或者实施一项具体的任务.

用户进程用户进程是要求 Oracle 服务器交互的一种进程：它必须首先建立一个连接. 不直接和 Oracle 服务器交互.
Server process User process Connection established User Process A database user who needs to request information from the database must first make a connection with the Oracle server. The connection is requested using a database interface tool, such as SQL*Plus, and beginning the user process. The user process does not interact directly with the Oracle server. Rather it generates calls through the user program interface (UPI), which creates a session and starts a server process. Database user

Connection established
服务进程服务进程是直接和 Oracle 服务器交互的一段程序. 它响应用户要求，向服务器发起呼叫并返回结果给用户. 可用专用服务器模式,也可用共享服务器模式创建对话. Connection established Session created Database user User process Oracle server Server process Server Process Once a user has established a connection, a server process is started to handle the user processes requests. A server process can be either a dedicated server process or a shared server process. In a dedicated server environment, the server process handles the request of a single user process. Once a user process disconnects, the server process is terminated. In a shared server environment, the server process handles the request of several user processes. The server process communicates with the Oracle server using the Oracle Program Interface (OPI). Note: Allocation of server process in a dedicated environment versus a shared environment is covered in further detail in the Oracle9i Performance Tuning course.

后台进程保持物理和内存结构的关系. 必须的后台进程可选择的后台进程 DBWn PMON CKPT LGWR SMON RECO
ARCn LMON Snnn QMNn LMDn CJQ0 Pnnn LCKn Dnnn Background Processes The Oracle architecture has five mandatory background processes that are discussed further in this lesson. In addition to the mandatory list, Oracle has many optional background process that are started when their option is being used. These optional processes are not within the scope of this course, with the exception of the ARCn background process. Following is a list of some optional background processes: RECO: Recoverer QMNn: Advanced Queuing ARCn: Archiver LCKn: RAC Lock Manager—Instance Locks LMON: RAC DLM Monitor—Global Locks LMDn: RAC DLM Monitor—Remote Locks CJQ0: Snapshot Refresh Dnnn: Dispatcher Snnn: Shared Server Pnnn: Parallel Query Slaves

数据库复写器 DBWn 开始写: 检查点执行脏块上限数到达缓存区没有自由空间超时连接RAC要求表空间脱机表空间只读清空和删除表
Instance SGA Database buffer cache DBWn DBWn 开始写: 检查点执行脏块上限数到达缓存区没有自由空间超时连接RAC要求表空间脱机表空间只读清空和删除表表空间开始备份 Control files Data files Redo log files Database Writer The server process records changes to rollback and data blocks in the buffer cache. Database Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. It ensures that a sufficient number of free buffers—buffers that can be overwritten when server processes need to read in blocks from the data files—are available in the database buffer cache. Database performance is improved because server processes make changes only in the buffer cache. DBWn defers writing to the data files until one of the following events occurs: Incremental or normal checkpoint The number of dirty buffers reaches a threshold value A process scans a specified number of blocks when scanning for free buffers and cannot fine any. Timeout occurs. A ping request in Real Application Clusters environment. Placing a normal or temporary tablespace offline. Placing a tablespace in read only mode. Dropping or Truncating a table. ALTER TABLESPACE tablespace name BEGIN BACKUP Database

日志复写器 LGWR 开始写: 提交时三分之一缓冲区满的时候当有1MB日志的时候每三秒在DBWn写之前 Instance SGA
Redo log buffer DBWn LGWR Data files Control files Redo log files LOG Writer LGWR performs sequential writes from the redo log buffer cache to the redo log file under the following situations: When a transaction commits When the redo log buffer cache is one-third full When there is more than a megabyte of changes records in the redo log buffer cache Before DBWn writes modified blocks in the database buffer cache to the data files Every 3 seconds. Because the redo is needed for recovery, LGWR confirms the commit only after the redo is written to disk. LGWR can also call on DBWn to write to the data files. Note: DBWn does not write to the online redo logs. Database

系统监控进程回滚日志文件中的改变信息打开数据库,让用户进入回滚未提交的事务责任: 实例恢复: 每三秒接合空闲空间回收临时段
Instance Instance 责任: 实例恢复: 回滚日志文件中的改变信息打开数据库,让用户进入回滚未提交的事务每三秒接合空闲空间回收临时段 SGA SGA SMON SMON Data files Control files Redo log files System Monitor If the Oracle instance fails, any information in the SGA that has not been written to disk is lost. For example, the failure of the operating system causes an instance failure. After the loss of the instance, the background process SMON automatically performs instance recovery when the database is reopened. Instance recovery consists of the following steps: 1. Rolling forward to recover data that has not been recorded in the data files but that has been recorded in the online redo log. This data has not been written to disk because of the loss of the SGA during instance failure. During this process, SMON reads the redo log files and applies the changes recorded in the redo log to the data blocks. Because all committed transactions have been written to the redo logs, this process completely recovers these transactions. 2. Opening the database so that users can log on. Any data that is not locked by unrecovered transactions is immediately available. 3. Rolling back uncommitted transactions. They are rolled back by SMON or by the individual server processes as they access locked data. SMON also performs some space maintenance functions: It combines, or coalesces, adjacent areas of free space in the data files. It deallocates temporary segments to return them as free space in data files. Temporary segments are used to store data during SQL statement processing. Database

进程监控器处理失败后进行清理: 回滚事务释放锁释放其它资源重启死的dispatchs Instance SGA PMON
PGA area Process Monitor The background process PMON cleans up after failed processes by: Rolling back the user’s current transaction Releasing all currently held table or row locks Freeing other resources currently reserved by the user Restarts dead dispatchers Dispatchers are covered in further detail in the DBA Fundamentals II course.

检查点负责: 给 DBWn 发信号修改数据文件块头修改控制文件信息 Instance Instance SGA SGA DWW0
Redo Log Buffer LGWR SGA 负责: 给 DBWn 发信号修改数据文件块头修改控制文件信息 DBWn LGWR CKPT Data files Control files Redo log files Checkpoint An event called a checkpoint occurs when the Oracle background process DBWn writes all the modified database buffers in the SGA, including both committed and uncommitted data, to the data files. Checkpoints are implemented for the following reasons: Checkpoints ensure that data blocks in memory that change frequently are written to data files regularly. Because of the least recently used algorithm of DBWn, a data block that changes frequently might never qualify as the least recently used block and thus might never be written to disk if checkpoints did not occur. Because all database changes up to the checkpoint have been recorded in the data files, redo log entries before the checkpoint no longer need to be applied to the data files if instance recovery is required. Therefore, checkpoints are useful because they can expedite instance recovery. Checkpoint(continued) At a checkpoint, the following information is written: Checkpoint number into the data file headers Checkpoint number, log sequence number, archived log names, and system change numbers into the control file. CKPT does not write data blocks to disk or redo blocks to the online redo logs. Note: The CKPT process should not be confused with the checkpoint operation discussed in the Managing Redo Log Files lesson.

Archived Redo log files
归档进程(ARCn) 可选择的后台进程当ARCHIVELOG模式被设置时自动归档联机重做日志文件保存所有数据库变化 ARCn Data files Control files Redo log files Archived Redo log files The Archiver Process All other background processes are optional, depending on the configuration of the database; however, one of them, ARCn, is crucial to recovering a database after the loss of a disk. As online redo log files fill, the Oracle server begins writing to the next online redo log file. The process of switching from one redo log to another is called a log switch. The ARCn process initiates backing up, or archiving, of the filled log group at every log switch. It automatically archives the online redo log before the log can be reused, so that all of the changes made to the database are preserved. This enables the DBA to recover the database to the point of failure, even if a disk drive is damaged. Archiving Redo Log Files One of the important decisions that a DBA has to make is whether to configure the database to operate in ARCHIVELOG or in NOARCHIVELOG mode. NOARCHIVELOG Mode: In NOARCHIVELOG mode, the online redo log files are overwritten each time a log switch occurs. LGWR does not overwrite a redo log group until the checkpoint for that group is complete. This ensures that committed data can be recovered if there is an instance crash. During the instance crash, only the SGA is lost. There is no loss of disks, only memory. For example, an operating system crash causes an instance crash. Archiving Redo Log Files (continued) ARCHIVELOG Mode: If the database is configured to run in ARCHIVELOG mode, inactive groups of filled online redo log files must be archived before they can be used again. Since changes made to the database are recorded in the online redo log files, the database administrator can use the physical backup of the data files and the archived online redo log files to recover the database without losing any committed data because of any single point of failure, including the loss of a disk. Usually, a production database is configured to run in ARCHIVELOG mode. Archive log modes are covered in further detail in the DBA Fundamentals II course.

逻辑结构 Oracle体系的逻辑结构描述了数据库的物理空间怎样运用，包括表空间、段、片区和块. Tablespace Data file
Segment Blocks Extent Logical Structure A logical structure hierarchy exists as follows: An Oracle database is a group of tablespaces. A tablespace may consist of one or more segments. A segment is made up of extents. An extent is made up of logical blocks. A block is the smallest unit for read and write operations. Tablespaces, segments, extents, and blocks are discussed further in later lessons.

RAC方式原理结构图 RAC的全称是Real Application clusters，在RAC方式下，两台(或多台)服务器上各自运行一个数据库核心进程，但共同管理、操作一个数据库。客户端无论连接到哪个服务器都可以在数据库中进行操作，Oracle数据库可以自动实现负载均衡，使客户连接均匀的分担在两台主机上。当服务器A由于故障失效时，数据库系统本身并未停止工作，连接在服务器B上的客户端还可以继续进行正常工作。同时，服务器B上也不需要再启动新的数据库服务器进程。对于一些特殊应用中严格要求前端应用不能中断的情况，Oracle RAC方式还提供了一种“预连接(pre-connect)”方式，以这种方式连接的客户端当服务器端发生故障时，客户端与数据库服务器的连接不会中断，会被Oracle并行服务器软件自动转接到还在正常工作的其它服务器上，不需要重新输入用户名及口令。

RAC方式优点良好的可伸缩性高可用性先进的Cache Fusion技术降低硬件成本切换时间短缺点管理复杂对网络有较高要求
1、良好的可伸缩性 Oracle在集群中多个节点之间自动平衡用户负载。随着业务的增长，用户可以根据应用负载的增加而增加新的硬件设备。Oracle RAC 体系结构直接地利用新节点的CPU和内存资源。DBA无需用手工对数据重新分区。 2、高可用性该体系结构为客户提供了几乎连续的数据访问，提供了数据库恢复期间的数据块访问，使硬件和软件故障导致的业务中断最小化。系统具备对多个节点失败的容错能力，使部件失败屏蔽开最终用户。 3、先进的Cache Fusion技术减少磁盘的IO，提高速度。因为如果一个数据块被节点1读入了内存，如果节点2也要访问这个数据块，那么节点2直接从节点1的内存中读即可，不需要再读磁盘。 4、降低硬件成本充分利用硬件资源，因为两个节点都运行oracle的实例可以同时处理事务，降低硬件成本。可以使用两个或多个比较小的机器代替比较大的系统从而降低成本。 5、切换时间短由于两个节点的ORACLE数据库实例都已经启动，所以在作切换的时候无须象双机热备份的方式，备机在接管的时候要起ORACLE数据库实例。另外，运行正常的节点在接管故障节点的时候，即接管故障节点的客户端访问请求的时候，同时帮助故障机进行日志恢复（RECOVER）和事务回滚的操作（ROLLBACK），而不会象双机热备份的方式要在进行完日志恢复和事务回滚的操作后才能打开数据库，接受客户端的访问。

HOTSTANDBY(双机热备)方式原理结构图
如上图所示，在双机热备份方式下，数据库系统平时只能在一台服务器(例如服务器A) 上运行，另一台服务器无法直接访问数据库，自然也无法进行负载分担。当服务器A由于故障失效时，由相应的操作系统软件控制，将服务器A管理的存储设备(如硬盘)转交给服务器B控制，同时在服务器B上启动另一个数据库进程，管理数据库。备机接管主机需要进行以下一些过程： 1.把原来由主机控制的磁盘阵列由备机接管，即把磁盘陈列MOUNT到备机上。 2.启动ORACLE数据库实例，进行实例的初始化 3.MOUNT数据库，初始化数据文件 4.根据数据库的日志文件，对数据库进行恢复（RECOVER）。 5.根据数据库的回滚日志，对失败的事务进行回滚。 6.打开数据库，数据库为可用状态。根据原来在主机正在执行的事务量，这种切换并启动新的数据库核心的过程一般需要几十秒到几分钟。

HOTSTANDBY(双机热备)方式优点管理较为简单运行较为稳定缺点切换时间较长
在系统切换的过程中，客户端与服务器之间的数据库连接会中断，需重新连接备用机闲置，不能实现负载均衡 1、由于需要重新启动数据库核心进程，无法保证数据库系统连续不间断地运行 2、在系统切换的过程中，客户端与服务器之间的数据库连接会中断，需要重新进行数据库的连接和登录工作 3、由于数据库系统只能在一台服务器上运行，另一台服务器无法分担系统的负载，实际上造成了客户投资的浪费。

通过这部分课程的学习，您应该已经了解: 总结解释数据库文件 : 数据文件、控制文件、联机重做日志.
解释系统全局区内存结构: 数据块高速缓冲区、共享池、重做日志缓冲区. 解释主要的后台进程: DBWn, LGWR, CKPT, PMON, SMON, 和 ARCn 双机模式：RAC/HOT STANDBY Practice 1: Solutions 1 Which one of the following statements is true? a An Oracle server is a collection of data consisting of three file types. b A user establishes a connection with the database by starting an Oracle instance. c A connection is a communication pathway between the Oracle Server and the Oracle Instance. d A session starts when a user is validated by the Oracle server. 2 Which one of the following memory areas is not part of the SGA? a Database buffer cache b PGA c Redo log buffer cache d Shared Pool 3 Which two of the following statements are true about the Shared Pool? a The shared Pool consists of the Library Cache, Data Dictionary Cache, Shared SQL area, Java Pool, and Large Pool. b The Shared Pool is used to store the most recently executed SQL statements and the most recently used data. c The Shared Pool is used for object that can be shared globally. d The Library Cache consist of the Shared SQL and Shared PL/SQL areas. 4 Which one of the following memory areas is used to cache the data dictionary information? a Database Buffer Cache 5 The primary purpose of the Redo Log Buffer Cache is to record all changes to the database data blocks. a True b False Answer: True 6 The PGA is a memory region that contains data and control information for multiple server processes or multiple background processes. b False Practice 1: Solutions (continued) 7 Which one of the following processes is available when an Oracle instance is started. a User process b Background process c Server process d System process 8 Identify the six mandatory background processes. ________________________________________ 9 Which one of the following memory areas is used to cache the data dictionary 10 Match the process with its task. a Database Writer ___ Assists with writing to the data file headers b Log Writer ___ Responsible for instance recovery c System Monitor ___ Cleans up after failed processes d Process Monitor ___ Records database changes for recovery purposes e Checkpoint ___ Writes dirty buffers to the data files 11 The physical structure of an Oracle database consists of control files, data files, and redo log files. 12 Place the following structures in order of hierarchy beginning with database. a Tablespaces b Extent c Segment d database e block

管理表空间和数据文件

目标通过这部分课程的学习,您将了解以下内容 : 数据库的逻辑结构创建表空间改变表空间的大小为临时段分配空间改变表空间的状态
改变表空间的存储参数设置

概观 Database Data files Control files Redo log files Overview
A small database might need only the SYSTEM tablespace; however, Oracle recommends that you create additional tablespaces to store user data, user indexes, undo segments, and temporary segments separate from data dictionary. This gives you more flexibility in various database administration operations and reduces contention among dictionary objects and schema objects for the same data files. The DBA can create new tablespaces, resize data files, add data files to tablespaces, set and alter default segment storage settings for segments created in a tablespace, make a tablespace read-only or read-write, make a tablespace temporary or permanent, and drop tablespaces.

数据库的存储层次 Database Tablespace Data file 逻辑上 Segment 物理上 Extent
Database Architecture The Oracle database architecture includes logical and physical structures that make up the database. The physical structure includes the control files, online redo log files, and data files that make up the database. The logical structure includes tablespaces, segments, extents, and data blocks. The Oracle server enables fine-grained control of disk space use through tablespace and logical storage structures, including segments, extents, and data blocks. Tablespaces The data in an Oracle database are stored in tablespaces. An Oracle database can be logically grouped into smaller logical areas of space known as tablespaces. A tablespace can belong to only one database at a time. Each tablespace consists of one or more operating system files, which are called data files. Database Architecture (continued) A tablespace may consist of zero or more segments. Tablespaces can be brought online while the database is running. Except for the SYSTEM tablespace or a tablespace with an active undo segment, tablespaces can be taken offline, leaving the database running. Tablespaces can be switched between read-write and read-only status. Data Files Each tablespace in an Oracle database consists of one or more files called data files. These are physical structures that conform with the operating system on which the Oracle server is running. A data file can belong to only one tablespace. An Oracle server creates a data file for a tablespace by allocating the specified amount of disk space plus a small amount of overhead. The database administrator can change the size of a data file after its creation or can specify that a data file should dynamically grow as objects in the tablespace grow. Segments A segment is the space allocated for a specific logical storage structure within a tablespace. For example, all of the storage allocated to a table is a segment. A tablespace may consist of one or more segments. A segment cannot span tablespaces; however, a segment can span multiple data files that belong to the same tablespace. Each segment is made up of one or more extents. Extents Space is allocated to a segment by extents. One or more extents make up a segment. When a segment is created, it consists of at least one extent. As the segment grows, extents get added to the segment. The DBA can manually add extents to a segment. An extent is a set of contiguous Oracle blocks. An extent cannot span a data file but must exist in one data file. Data Blocks The Oracle server manages the storage space in the data files in units called Oracle blocks or data blocks. At the finest level of granularity, the data in an Oracle database is stored in data blocks. Oracle data blocks are the smallest units of storage that the Oracle server can allocate, read, or write. One data block corresponds to one or more operating system blocks allocated from an existing data file. The standard data block size for an Oracle database is specified by the DB_BLOCK_SIZE initialization parameter when the database is created. The data block size should be a multiple of the operating system block size to avoid unnecessary I/O. The maximum data block size is dependent on the operating system. Oracle block OS block

系统和非系统表空间系统表空间: 随着数据库创建被创建包含数据字典信息包含系统回滚段非系统表空间:
不同的segments分开存放（如：回滚段, 临时段, 应用数据）控制分配给用户对象的空间容量 Types of Tablespaces The DBA creates tablespaces for increased control and ease of maintenance. The Oracle server perceives two types of tablespaces: SYSTEM and all others. SYSTEM Tablespace Created with the database Required in all databases Contains the data dictionary, including stored program units Contains the SYSTEM undo segment Should not contain user data, although it is allowed Non-SYSTEM Tablespaces Enable more flexibility in database administration Separate undo, temporary, application data, and application index segments Separate data by backup requirements Separate dynamic and static data Control the amount of space allocated to user’s objects

表空间的管理本地管理的表空间 : 自由扩展信息被记录在本身的位图中位图中的每一位都对应一个数据块或一组数据块
位图中的标志位显示数据块使用或未使用状态信息字典管理的表空间: 缺省的表空间管理方式自由扩展信息被记录在数据字典中 Choosing a Space Management Method Tablespace extents can be managed with data dictionary tables or bitmaps. When you create a tablespace, you choose one of these methods of space management. You cannot alter the method at a later time. Locally Managed Tablespaces A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that data file. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, the Oracle server changes the bitmap values to show the new status of the blocks. Dictionary-Managed Tablespaces For a tablespace that uses the data dictionary to manage its extents, the Oracle server updates the appropriate tables in the data dictionary whenever an extent is allocated or deallocated.

本地管理的表空间简化了数据字典中的内容空间分配和回收不产生回滚信息没有接合邻近extents的要求
CREATE TABLESPACE userdata DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K; 简化了数据字典中的内容空间分配和回收不产生回滚信息没有接合邻近extents的要求 Locally Managed Tablespaces The LOCAL option of the EXTENT MANAGEMENT clause specifies that a tablespace is to be locally managed. By default a tablespace is locally managed. extent_management_clause :== [ EXTENT MANAGEMENT [ DICTIONARY | LOCAL [ AUTOALLOCATE | UNIFORM [SIZE integer[K|M]] ] ] ] where: DICTIONARY specifies that the tablespace is managed using dictionary tables. LOCAL specifies that tablespace is locally managed with a bitmap. If you specify LOCAL, you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY. AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. This is the default. Locally Managed Tablespaces (continued) UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes. Use K or M to specify the extent size in kilobytes or megabytes. The default size is 1 megabyte. The EXTENT MANAGEMENT clause can be used in various CREATE commands: For a permanent tablespace other than SYSTEM, you can specify EXTENT MANAGEMENT LOCAL in the CREATE TABLESPACE command. For a temporary tablespace, you can specify EXTENT MANAGEMENT LOCAL in the CREATE TEMPORARY TABLESPACE command. Advantages of Locally Managed Tablespaces Locally managed tablespaces have the following advantages over dictionary-managed tablespaces: Local management avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a undo segment or data dictionary table. Because locally managed tablespaces do not record free space in data dictionary tables, it reduces contention on these tables. Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace. Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

字典管理的表空间 Extents 在数据字典中管理每个存储在表空间的segments都可以有不同的存储参数设置
CREATE TABLESPACE userdata DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( initial 1M NEXT 1M ); Extents 在数据字典中管理每个存储在表空间的segments都可以有不同的存储参数设置有邻近接合extents的要求 Dictionary Managed Tablespaces Segments in dictionary managed tablespaces can have a customized storage, this is more flexible than locally managed tablespaces but much less efficient.

回滚段表空间用来存储回滚段信息不能包含其它对象 Extents 本地管理
在创建时仅仅能使用 DATAFILE and EXTENT MANAGEMENT 条件 CREATE UNDO TABLESPACE undo1 DATAFILE '/u01/oradata/undo101.dbf' SIZE 40M; Undo Tablespace An undo tablespace is used with automatic undo management. Automatic undo management is covered in the lesson “Managing Undo Data.” Unlike other tablespaces, the undo tablespace is limited to the DATAFILE. CREATE UNDO TABLESPACE tablespace [DATAFILE clause]

临时表空间用来做排序操作不能包含固定的对象最好使用本地表空间管理 CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M; Temporary Segments You can manage space for sort operations more efficiently by designating temporary tablespaces exclusively for sort segments. No permanent schema objects can reside in a temporary tablespace. Sort, or temporary, segments are used when a segment is shared by multiple sort operations. Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory. The sort segment of a given temporary tablespace is created at the time of the first sort operation of the instance. The sort segment expands by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance. CREATE TEMPORARY TABLESPACE Command Although the ALTER/CREATE TABLESPACE...TEMPORARY command can be used to create a temporary tablespace, it is recommended that the CREATE TEMPORARY TABLESPACE command be used. Temporary Segments (continued) Locally managed temporary tablespaces have temporary data files (tempfiles), which are similar to ordinary data files except that: Tempfiles are always set to NOLOGGING mode. You cannot make a tempfile read-only. You cannot rename a tempfile. You cannot create a tempfile with the ALTER DATABASE command. Tempfiles are required for read-only databases. Media recovery does not recover tempfiles. BACKUP CONTROLFILE does not generate any information for tempfiles. CREATE CONTROLFILE cannot specify any information about tempfiles. To optimize the performance of a sort in a temporary tablespace, set the UNIFORM SIZE to be a multiple of the parameter SORT_AREA_SIZE. Using Console to Create a Temporary Tablespace Launch the Console: % oemapp console Choose Launch Standalone Expand your working database from the Databases folder Expand Storage folder Select the Tablespaces folder, and select Create from the right mouse menu. Supply details in the General tab of the property sheet, and select the Temporary option in the Type region Click the Storage tab, and enter the storage information. Click Create. Note: You can also launch the Console from Windows NT Start menu

缺省临时表空间的限制直到新的缺省表空间被使用时才能删除不能下线. 不能将缺省的临时表空间修改成永久表空间
Restrictions on Default Temporary Tablespace Dropping a Default Temporary Tablespace You cannot drop the default temporary tablespace until after a new default is made available. The ALTER DATABASE command must be used to change the default temporary tablespace to a new default. The old default temporary tablespace is then dropped only after a new default temporary tablespace is made available. Users assigned to the old default temporary tablespace are automatically reassigned to the new default temporary tablespace. Changing to a Permanent Type Versus Temporary Type Because a default temporary tablespace must be either the SYSTEM tablespace or a Temporary tablespace, you cannot change the default temporary tablespace to a permanent type. Taking Default Temporary Tablespace Offline Tablespaces are taken offline to make that part of the database unavailable to other users (for example, an offline backup, maintenance, or making a change to an application that uses the tablespace). Becuase none of these situations apply to a temporary tablespace, you cannot take a default temporary tablespace offline.

删除表空间从数据字典中删除表空间信息通过 AND DATAFILES条件可在操作系统上删除表空间的数据文件:
DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE Command You can remove a tablespace from the database when the tablespace and its contents are no longer required with the following DROP TABLESPACE SQL command: DROP TABLESPACE tablespace [INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]] where: tablespace specifies the name of the tablespace to be dropped INCLUDING CONTENTS drops all the segments in the tablespace AND DATAFILES deletes the associated operating system files CASCADE CONSTRAINTS drops referential integrity constraints from tables outside the tablespace that refer to primary and unique keys in the tables in the dropped tablespace DROP TABLESPACE Command (continued) Guidelines A tablespace that still contains data cannot be dropped without the INCLUDING CONTENTS option. This option may generate a lot of undo when the tablespace contains many objects. After a tablespace has been dropped, its data is no longer in the database. When a tablespace is dropped, only the file pointers in the control file of the associated database are dropped. The operating system files still exist and must be deleted explicitly using the appropriate operating system command unless the AND DATAFILES clause is used. Even if a tablespace is switched to read-only, it can still be dropped, along with segments within it. It is recommended that you take the tablespace offline before dropping it to ensure that no transactions access any of the segments in the tablespace. Using the Console to Drop a Tablespace Launch the Console: % oemapp console Choose Launch Standalone Expand your working database from the Databases folder Expand Storage folder Expand the Tablespaces folder, and select the tablespace. Select Object > Remove from menu. Click Yes in the dialog box to confirm. Note: You can also launch the Console from Windows NT Start menu

改变表空间的大小增加数据文件改变数据文件大小: 自动手动 Tablespace APP_DATA 100M 100M 200M
Increasing the Tablespace Size You can enlarge a tablespace in two ways: Change the size of a data file, either automatically or manually. Add a data file to a tablespace. 100M 200M 100M app_data_01.dbf app_data_02.dbf app_data_03.dbf

手动改变数据文件大小 Tablespace APP_DATA ALTER DATABASE
DATAFILE '/u03/oradata/userdata02.dbf' RESIZE 200M; Tablespace APP_DATA 100M The ALTER DATABASE DATAFILE RESIZE Command Instead of adding space to the database by adding data files, the DBA can change the size of a data file. Use the ALTER DATABASE command to manually increase or decrease the size of a data file: ALTER DATABASE [database] DATAFILE ‘filename’[, ‘filename’]... RESIZE integer[K|M] where: integer is the absolute size, in bytes, of the resulting data file If there are database objects stored above the specified size, then the data file size is decreased only to the last block of the last objects in the data file. 100M 200M 200M 100M app_data_01.dbf app_data_02.dbf app_data_03.dbf app_data_04.dbf

在表空间中增加一个数据文件 Tablespace APP_DATA ALTER TABLESPACE app_data
ADD DATAFILE '/u01/oradata/userdata03.dbf' SIZE 200M; Tablespace APP_DATA 100M 100M 200M The ALTER TABLESPACE ADD DATAFILE Command You can add data files to a tablespace to increase the total amount of disk space allocated for the tablespace with the ALTER TABLESPACE ADD DATAFILE command: ALTER TABLESPACE tablespace ADD DATAFILE filespec [autoextend_clause] [, filespec [autoextend_clause]]... Using the ALTER TABLESPACE ADD DATAFILE Command (continued) Using the Console to Add a Data file Launch the Console: % oemapp console Choose Launch Standalone Expand your working database from the Databases folder Expand Storage folder Expand the Tablespaces folder. Right click the tablespace, and select Add Datafile. In the General tab of the property sheet, enter the file information. Click Create. Note: You can also launch the Console from Windows NT Start menu 100M app_data_01.dbf app_data_02.dbf app_data_03.dbf

移动数据文件（修改表空间）表空间必须为下线状态。目标数据文件必须存在. ALTER TABLESPACE userdata RENAME
DATAFILE '/u01/oradata/userdata01.dbf' TO '/u01/oradata/userdata01.dbf'; Methods for Moving Data Files Depending on the type of tablespace, the database administrator can move data files using one of the following two methods: The ALTER TABLESPACE Command The following ALTER TABLESPACE command is applied only to data files in a non-SYSTEM tablespace that does not contain active undo or temporary segments: ALTER TABESPACE tablespace RENAME DATAFILE 'filename'[, 'filename']... TO 'filename'[, 'filename']... Use the following process to rename a data file: 1. Take the tablespace offline. 2. Use an operating system command to move or copy the files. 3. Execute the ALTER TABLESPACE RENAME DATAFILE command. 4. Bring the tablespace online. 5. Use an operating system command to delete the file if necessary. The source filenames must match the names stored in the control file.

获取表空间信息表空间信息: 数据文件信息: 临时文件信息: DBA_TABLESPACES V$TABLESPACE
DBA_DATA_FILES V$DATAFILE 临时文件信息: DBA_TEMP_FILES V$TEMPFILE

总结通过这部分内容的学习，您应该已经了解: 怎样使用表空间分开存放数据改变表空间大小: 增加数据文件扩展数据文件使用本地管理表空间
使用临时表空间

练习练习包括以下几个主题: 创建表空间修改表空间
Practice 8: Managing Tablespaces and Data Files 1 Create permanent tablespaces with the following names and storage: a DATA01 data dictionary managed. b DATA02 locally managed with uniform sized extents (Ensure that every used extent size in the tablespace is a multiple of 100 KB.) c INDX01 locally managed with uniform sized extents of 4K ( Enable automatic extension of 500 KB when more extents are required with a maximum size of 2 MB. ) d RONLY for read-only tables with the default storage. DO NOT make the tablespace read only at this time. Display the information from the data dictionary. 2 Allocate 500K more disk space to tablespace DATA02. Verify the result. 3 Relocate tablespace INDX01 to subdirectory u06. 4 Create a table in tablespace RONLY. Make tablespace RONLY read-only. Attempt to create an additional table. Drop the first created table. What happens and why? 5 Drop tablespace RONLY and the associated datafile. Verify it. 6 Set DB_CREATE_FILE_DEST to $HOME/ORADATA/u05 in memory only. Create tablespace DATA03 size 5M. Do not specify a file location. Verify the creation of the data file.

管理重做日志文件

目标通过这部分课程的学习,您将了解以下内容 : 在线重做日志文件的作用在线重做日志文件的结构控制日志文件切换和检查点执行
多路复用在线重做日志文件

使用重做日志文件重做日志文件记录数据所有的修改信息并提供一种介质失败时的恢复机制. 重做日志文件分组管理.
一个Oracle数据库要求至少有两组重做日志文件. 组中每个日志文件被称作一个组成员. Database Purposes of the Redo Log Files Redo log files provide the means to redo transactions in the event of a database failure. Every transaction is written synchronously to the redo log files in order to provide a recovery mechanism in case of media failure. (With exceptions such as: direct loads and direct reads done with the NOLOGGING option.) This includes transactions that have not yet been committed, undo segment information, and schema and object management statements. Redo log files are used in a situation such as an instance failure to recover committed data that has not been written to the data files. The redo log files are used only for recovery. Redo log files

重做日志文件的结构 Group 1 Group 2 Group 3 Disk 1 Member Member Member Disk 2
Structure of the Redo Log Files The database administrator can set up the Oracle database to maintain copies of online redo log files to avoid losing database information due to a single point of failure. Online Redo Log Groups A set of identical copies of online redo log files is called an online redo log group. The LGWR background process concurrently writes the same information to all online redo log files in a group. The Oracle server needs a minimum of two online redo log file groups for the normal operation of a database. Online Redo Log Members Each online redo log file in a group is called a member. Each member in a group has identical log sequence numbers and the same size. The log sequence number is assigned each time the Oracle server starts writing to a log group to identify each redo log file uniquely. The current log sequence number is stored in the control file and in the header of all data files. Structure of the Redo Log Files (continued) Creating Initial Redo Log Files The initial set of online redo log groups and members are created during the database creation. The following parameters limit the number of online redo log files: The MAXLOGFILES parameter in the CREATE DATABASE command specifies the absolute maximum of online redo log groups. The maximum and default value for MAXLOGFILES is dependent on your operating system. The MAXLOGMEMBERS parameter used in the CREATE DATABASE command determines the maximum number of members per group. The maximum and default value for MAXLOGMEMBERS is dependent on your operating system.

重做日志文件怎样工作重做日志文件循环使用当一个重做日志文件充满时, LGWR 将日志写入到下个重做日志组. 这个被叫做日志切换
检查点执行动作被触发相关信息写入控制文件 How Redo Logs Work The Oracle server sequentially records all changes made to the database in the redo log buffer. The redo entries are written from the redo log buffer to one of the online redo log groups called the current online redo log group by the LGWR process. LGWR writes under the following situations: When a transaction commits When the redo log buffer becomes one-third full When there is more than a megabyte of changed records in the redo log buffer Before the DBWn writes modified blocks in the database buffer cache to the data files Redo logs are used in a cyclic fashion. Each redo log file group is identified by a log sequence number that is overwritten each time the log is reused. Log Switches LGWR writes to the online redo log files sequentially. When the current online redo log group is filled, LGWR begins writing to the next group. This is called a log switch. When the last available online redo log file is filled, LGWR returns to the first online redo log group and starts writing again. How Redo Logs Work (continued) Checkpoints During a checkpoint: A number of dirty database buffers covered by the log being checkpointed are written to the data files by DBWn. The number of buffers being written by DBWn is determined by the FAST_START_MTTR_TARGET parameter, if specified. Note: The FAST_START_MTTR_TARGET parameter is covered in detail in the Oracle9i DBA Fundamentals II course. The checkpoint background process CKPT updates the headers of all data files and control files to reflect that it has completed successfully. Checkpoints can occur for all data files in the database or for only specific data files. A checkpoint occurs, for example, in the following situations: At every log switch When an instance has been shut down with the normal, transactional, or immediate option When forced by setting the initialization parameter FAST_START_MTTR_TARGET. When manually requested by the database administrator When the ALTER TABLESPACE [OFFLINE NORMAL|READ ONLY|BEGIN BACKUP] cause checkpointing on specific data files. Information about each checkpoint is recorded in the alert_SID.log file if the LOG_CHECKPOINTS_TO_ALERT initialization parameter is set to TRUE. The default value of FALSE for this parameter does not log checkpoints.

强迫日志切换并执行检查点日志切换能使用命令 ALTER SYSTEM SWITCH LOGFILE. 检查点被迫使执行:
设置 FAST_START_MTTR_TARGET 参数 ALTER SYSTEM CHECKPOINT 命令 ALTER SYSTEM CHECKPOINT; Forcing Log Switches and Checkpoints Log switches and checkpoints are automatically done at certain points in the operation of the database as identified previously, but a DBA can force a log switch or a checkpoint to occur. Forcing Log Switches A log switch can be forced using the following SQL command: ALTER SYSTEM SWITCH LOGFILE; Forcing Checkpoints A checkpoint can be forced using the following SQL command: ALTER SYSTEM CHECKPOINT; A checkpoint can be forced using the FAST_START_MTTR_TARGET parameter. FAST_START_MTTR_TARGET is a shortcut to the deprecated parameters FAST_START_IO_TARGET and LOG_CHECKPOINT_TIMEOUT. FAST_START_MTTR_TARGET = 600 indicates that instance recovery should not take more than 600 seconds, and the database will adjust the other parameters to this goal. FAST_START_IO_TARGET and LOG_CHECKPOINT_TIMEOUT must not be used if FAST_START_MTTR_TARGET is used.

增加在线重做日志组 ALTER DATABASE ADD LOGFILE GROUP 3 ('$HOME/ORADATA/u01/log3a.rdo', '$HOME/ORADATA/u02/log3b.rdo') SIZE 1M; log1a.rdo log2a.rdo log3a.rdo Adding Redo Log Groups In some cases you might need to create additional log file groups. For example, adding groups can solve availability problems. To create a new group of online redo log files, use the following SQL command: ALTER DATABASE [database] ADD LOGFILE [GROUP integer] filespec [, [GROUP integer] filespec]...] You specify the name and location of the members with the file specification. The value of the GROUP parameter can be selected for each redo log file group. If you omit this parameter, the Oracle server generates its value automatically. log1b.rdo log2b.rdo log3b.rdo Group 1 Group 2 Group 3

增加在线重做日志组成员 ALTER DATABASE ADD LOGFILE MEMBER
'$HOME/ORADATA/u04/log1c.rdo' TO GROUP 1, '$HOME/ORADATA/u04/log2c.rdo' TO GROUP 2, '$HOME/ORADATA/u04/log3c.rdo' TO GROUP 3; log2c.rdo log2a.rdo Group 1 log1c.rdo log1b.rdo log1a.rdo Group 2 log2b.rdo Group 3 log3c.rdo log3b.rdo log3a.rdo Adding Redo Log Members You can add new members to existing redo log file groups using the following ALTER DATABASE ADD LOGFILE MEMBER command: ALTER DATABASE [database] ADD LOGFILE MEMBER [ 'filename' [REUSE] [, 'filename' [REUSE]]... TO {GROUP integer |('filename'[, 'filename']...) } ]... Use the fully specified name of the log file members; otherwise the files are created in a default directory of the database server. If the file already exists, it must have the same size, and you must specify the REUSE option. You can identify the target group either by specifying one or more members of the group or by specifying the group number. Adding Redo Log Members (continued) Using Storage Manager to Maintain Groups and Members Launch Storage Manager from the Console to manage redo log groups and members. Launch the Console %oemapp console Choose to Launch standalone Expand your working database from the databases folder Expand Storage folder Right-click the Redo Log Groups folder from the navigator tree, and select Create. Enter your redo log group information, and specify the members. Click Create. Note: You can also launch the Console from Windows NT Start menu

删除在线重做日志组 ALTER DATABASE DROP LOGFILE GROUP 3; log1a.rdo log2a.rdo
Dropping a Redo Log Group To increase or decrease the size of online redo log groups, add new online redo log groups (with the new size) and then drop the old ones. An entire online redo log group can be dropped with the following ALTER DATABASE DROP LOGFILE command: ALTER DATABASE [database] DROP LOGFILE {GROUP integer|('filename'[, 'filename']...)} [,{GROUP integer|('filename'[, 'filename']...)}]... Restrictions An instance requires at least two groups of online redo log files. An active or current group cannot be dropped. When an online redo log group is dropped, the operating system files are not deleted. log1a.rdo log2a.rdo log3a.rdo Group 1 Group 2 Group 3

删除在线重做日志组成员 ALTER DATABASE DROP LOGFILE MEMBER '$HOME/ORADATA/u04/log3c.rdo'; log1a.rdo log1a.rdo log1b.rdo log1b.rdo Dropping a Redo Log Member You may want to drop an online redo log member because it is invalid. Use the following ALTER DATABASE DROP LOGFILE MEMBER command if you want to drop one or more specific online redo log members: ALTER DATABASE [database] DROP LOGFILE MEMBER 'filename'[, 'filename']... Restrictions If the member you want to drop is the last valid member of the group, you cannot drop that member. If the group is current, you must force a log file switch before you can drop the member. If the database is running in ARCHIVELOG mode and the log file group to which the member belongs is not archived, then the member cannot be dropped. When an online redo log member is dropped, the operating system file is not deleted. Using Storage Manager to Drop Redo Log Groups and Members Launch Storage Manager from the Console to manage redo log groups and members. Launch the Console %oemapp console Choose to Launch standalone Expand your working database from the databases folder Expand Storage folder Expand the Redo Log Groups folder, and select a redo log group you want to remove. Select Object—>Remove from the menu bar to remove a redo log group. Note: You can also launch the Console from Windows NT Start menu log1c.rdo log2c.rdo Group 1 Group 2

清空, 重新指定位置或更名在线重做日志文件清空在线重做日志文件:
重新指定位置、更名在线重做日志文件能通过增加新日志文件，删除旧日志文件代替. ALTER DATABASE CLEAR LOGFILE '$HOME/ORADATA/u01/log2a.rdo'; Clearing Online Redo Log Files If a redo log file is corrupted in all members, the database administrator can solve this problem by reinitializing these log files using ALTER DATABASE CLEAR LOGFILE: ALTER DATABASE [database] CLEAR [UNARCHIVED] LOGFILE {GROUP integer|('filename'[, 'filename']...)} [,{GROUP integer|('filename'[, 'filename']...)}]... Using this command is equivalent to adding and dropping an online redo log file. But you can issue this command even if there are only two log groups with one file each and even if the cleared group is available but not archived. Restrictions You can clear an online redo log file whether it is archived or not. However, when it is not archived, you must include the keyword UNARCHIVED. This makes backups unusable if the online redo log file is needed for recovery. Relocating and Renaming Redo Log Files The locations of the online redo log files can be changed by adding new log files and dropping the old log files. Another method ALTER DATABASE RENAME FILE is available, but this requires the database to be placed in MOUNT mode. Therefore, it is much easier to add new ones and drop old ones. Using Storage Manager to Relocate or Rename Redo Log Members Launch Storage Manager from the Console to manage redo log groups and members. Launch the Console %oemapp console Choose to Launch standalone Expand your working database from the databases folder Expand Storage folder Expand the Redo Log Groups folder, and select a redo log group. Modify the redo log member information to rename or relocate members. Click Apply. Note: You can also launch the Console from Windows NT Start menu

获取日志组及其成员信息通过查询下列数据字典获取日志组及其成员信息 V$LOG V$LOGFILE
Obtaining Log Group and Member Information V$LOG The following query returns information about the online redo log file from the control file: SQL> SELECT group#, sequence#, bytes, members, status 2 FROM v$log; GROUP# SEQUENCE# BYTES MEMBERS STATUS CURRENT INACTIVE 2 rows selected. The following items are the most common values for the STATUS column: UNUSED indicates that the online redo log group has never been written to. This is the state of an online redo log file that was just added. CURRENT indicates the current online redo log group. This implies that the online redo log group is active. ACTIVE indicates that the online redo log group is active but is not the current online redo log group. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived. Obtaining Log Group and Member Information (continued) CLEARING indicates the log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE command. After the log is cleared, the status changes to UNUSED. CLEARING_CURRENT indicates that the current log file is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch, such as an I/O error writing the new log header. INACTIVE indicates that the online redo log group is no longer needed for instance recovery. It may or may not be archived. V$LOGFILE To obtain the names of all the members of a group, query the dynamic performance view V$LOGFILE. SQL> SELECT member FROM V$LOGFILE; MEMBER /u01/home/db03/ORADATA/u03/log02a.rdo /u01/home/db03/ORADATA/u03/log01a.rdo The value of the STATUS column could be one of the following: INVALID indicates that the file is inaccessible. STALE indicates that contents of the file are incomplete. DELETED indicates that the file is no longer used. Blank indicates that the file is in use.

归档日志文件装满的在线重做日志文件能被归档. 归档在线重做日志文件两种优势:
恢复:一套一致的数据文件完整备份与在线重做日志文件、归档日志文件一起能保证所有已经提交的事务恢复到先前状态. 备份: 能在数据库打开的情况下进行. 缺省数据库以非归档模式创建. Archived Redo Log Files One of the important decisions that a database administrator has to make is whether the database is configured to operate in ARCHIVELOG mode or in NOARCHIVELOG mode. NOARCHIVELOG In NOARCHIVELOG mode, the online redo log files are overwritten each time an online redo log file is filled, and log switches occur. LGWR does not overwrite a redo log group until the checkpoint for that group is completed. ARCHIVELOG If the database is configured to run in ARCHIVELOG mode, inactive groups of filled online redo log files must be archived. Because all changes made to the database are recorded in the online redo log files, the database administrator can use the physical backup and the archived online redo log files to recover the database without losing any committed data. There are two ways in which online redo log files can be archived: Manually Automatically: Recommend method Archived Redo Log Files (continued) ARCHIVELOG (continued) The LOG_ARCHIVE_START initialization parameter indicates whether archiving should be automatic or manual when the instance starts up. TRUE indicates that archiving is automatic. ARCn initiate archiving of the filled log group at every log switch. FALSE, the default value, indicates that the database administrator archives filled redo log files manually. The database administrator must manually execute a command each time you want to archive an online redo log file. All or specific online redo log files can be archived manually.

归档日志文件归档日志文件能通过ARCn或手工SQL归档得到.
控制文件中记录有归档日志名称、日志序列号，在线日志文件成功归档时最高、最低的SCN. 一个装满的在线日志文件需要在检查点发生、在线日志文件被ARCn归档完成以后才能重新使用，否则等待. 归档日志文件能多路复用. Archived Redo Log Files Information about archived logs can be obtained from V$INSTANCE. SQL> SELECT archiver 2 FROM v$instance; ARCHIVE STOPPED 1 row selected. Note: Archiving is covered in detail in the DBA Fundamentals II course. Archived Redo Log Files (continued) Using Instance Manager to Obtain Archive Information Launch Instance Manager from the Console, and obtain information about archiving Launch the Console %oemapp console Choose to Launch standalone Expand your working database from the databases folder Expand on Instance in the navigator tree and click Configuration Click the Recovery tab on the detail side of the Console to obtain archive information Note: You can also launch the Console from Windows NT Start menu

总结通过这部分课程的学习，您应该已经了解: 在线重做日志文件的作用怎样获取在线重做日志文件信息控制日志切换和检查点执行
多路复用管理多个在线重做日志文件

练习这部分练习包括以下主题: 创建和增加重做日志文件组及其成员. 删除重做日志文件组及其成员.
Practice 7: Maintaining Redo Log Files 1 List the number and location of existing log files and display the number of redo log file groups and members your database has. Hints: - Query the dynamic view V$LOGFILE. - Use the dynamic view V$LOG. 2 In which database mode is your database configured? Is archiving enabled? - Query the dynamic view V$DATABASE. - Query the dynamic view V$INSTANCE. 3 Add a redo log member to each group in your database located on u04, using the following naming conventions: Add member to Group 1: log01b.rdo Add member to Group 2: log02b.rdo Verify the result. - Execute the ALTER DATABASE ADD LOGFILE MEMBER command to add a redo log member to each group. - Query the dynamic performance view V$LOGFILE to verify the result. 4 Add a redo log group in your database with two members located on u03 and u04 using the following naming conventions: Add Group 3: log03a.rdo and log03b.rdo - Execute the ALTER DATABASE ADD LOGFILE command to create a new group. - Query the Dynamic View V$LOGFILE to display the name of the new members of the new group. - Query the Dynamic View V$LOG to display the number of redo log file groups and members. Practice 7: Maintaining Redo Log Files (Continued) 5 Remove the redo log group created in step 4. - Execute the ALTER DATABASE DROP LOGFILE GROUP command to remove the log group. - Query the Dynamic View V$LOG to verify the result. - Remove the operating system files for the group. 6 Resize all online redo log files to 1024 KB. (Because we cannot resize log files, we have to add new logs and drop the old.) - Execute the ALTER DATABASE ADD LOGFILE GROUP command to add two new groups with the size 1024 KB. - Query the Dynamic View V$LOG to check the active group. - Execute the ALTER SYSTEM SWITCH LOGFILE command to force log switches and change the group stage to inactive. The number of log switches required will vary. - Execute the ALTER DATABASE DROP LOGFILE command to remove the inactive groups.

管理用户和权限

目标通过这部分课程的学习,您将了解以下内容: 创建新的数据库用户修改和删除存在的数据库用户监控已存在用户的信息识别系统和对象权限
授予和回收权限

数据库模式模式是一种命名的对象集合一个用户被创建，对应的模式就被创建一个用户仅仅对应一个模式用户名其实就等同于模式名
Schema Objects Tables Triggers Constraints Indexes Views Sequences Stored program units Synonyms User-defined data types Database links 模式是一种命名的对象集合一个用户被创建，对应的模式就被创建一个用户仅仅对应一个模式用户名其实就等同于模式名 What Is a Schema? A schema is a named collection of objects such as tables, views, clusters, procedures, and packages associated with a particular user. When a database user is created, a corresponding schema with the same name is created for that user. A user can be associated only with a schema of the same name, and therefore username and schema are often used interchangeably. The slide shows some of the objects that users can own in an Oracle database.

创建用户所涉及内容确定用户需要将对象存储在哪个表空间决定每个表空间中该用户的使用限额. 指派缺省表空间和临时表空间. 开始创建一个用户.
赋予权限和角色给用户.

创建用户: 数据库级鉴别设定初始口令: CREATE USER aaron IDENTIFIED BY soccer
DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp QUOTA 15m ON data PASSWORD EXPIRE; Syntax Use the following command to create a new user: CREATE USER user IDENTIFIED {BY password | EXTERNALLY} [ DEFAULT TABLESPACE tablespace ] [ TEMPORARY TABLESPACE tablespace ] [ QUOTA {integer [K | M ] | UNLIMITED } ON tablespace [ QUOTA {integer [K | M ] | UNLIMITED } ON tablespace ]...] [ PASSWORD EXPIRE ] [ ACCOUNT { LOCK | UNLOCK }] [ PROFILE { profile | DEFAULT }] Syntax (continued) where: user: is the name of the user BY password: specifies user authenticated by the database and needs to supply password while logging on EXTERNALLY: specifies that the user is authenticated by the operating system GLOBALLY AS: specifies that the user is authenticated globally DEFAULT TEMPORARY TABLESPACE: identifies the default or temporary tablespace for the user if a temporary tablespace has not been assigned to the user. QUOTA: defines the maximum space allowed for objects owned by the user in the tablespace tablespace (Quota can be defined as integer bytes or kilobytes and megabytes. The keyword UNLIMITED is used to specify that the objects owned by the user can use as much space as is available in the tablespace. By default, no user has any quota on any tablespace.) PASSWORD EXPIRE: forces the user to reset the password when the user logs on to the database using SQL Plus (This option is valid only if the user is authenticated by the database.) ACCOUNT LOCK/UNLOCK: can be used to lock or unlock the user’s account explicitly (UNLOCK is the default.) PROFILE: is used to control resource usage and to specify the password control mechanism to be used for the user Note: Refer to the Managing Profiles lesson for information on creating profiles. A password authentication method is mandatory. If a password is specified, it is maintained by the Oracle server in the data dictionary. Password control mechanisms provided by the Oracle server are available when users are authenticated by the server. Once the password expiry is set, when the user logs on using SQL Plus, the user receives the following message at logon, and is prompted to enter a new password: ERROR: ORA-28001: the account has expired Changing password for PETER Old password: New password: Retype new password: Password changed How to Use Oracle Enterprise Manager to Create a New User Launch the Console %oemapp console Choose to Launch standalone You can also launch the Console from Windows NT Start menu. Expand your working database from the databases folder Expand the Security folder Select the Users folder and select Create from the right mouse menu. Enter user information in the General page of the property sheet. Specify quotas using the Quotas page. Click Create. You can also select a user and then select Object—>Create Like from the menu bar to create a user with the same quotas and privileges as an existing database user. Oracle Security Manager automatically grants the CONNECT role to any user who is created using the tool. Note: Refer to the Managing Roles lesson for information on the CONNECT role.

删除用户使用 CASCADE 条件删除用户及其用户中包含的所有对象. 当前正连接到Oracle服务器的用户不能被删除
DROP USER aaron; 使用 CASCADE 条件删除用户及其用户中包含的所有对象. 当前正连接到Oracle服务器的用户不能被删除 DROP USER aaron CASCADE; Syntax DROP USER user [CASCADE] Guidelines The CASCADE option drops all objects in the schema before dropping the user. This must be specified if the schema contains any objects. A user who is currently connected to the Oracle server cannot be dropped.

获取用户相关信息获取用户相关信息能通过查询以下数据字典. DBA_USERS DBA_TS_QUOTAS
Tablespace Quotas Use the following query to find the default_tablespace for all users. SELECT username, default_tablespace FROM dba_users; USERNAME DEFAULT_TABLESPACE SYS SYSTEM SYSTEM SYSTEM OUTLN SYSTEM DBSNMP SYSTEM HR EXAMPLE OE EXAMPLE

系统权限有100多种 ANY 关键字是指用户在任何模式下都具有 GRANT 命令由于授予权限 REVOKE 命令回收权限
System Privileges The privileges can be classified as follows: Privileges enabling system wide operations; for example, CREATE SESSION, CREATE TABLESPACE Privileges enabling management of objects in a user’s own schema; for example, CREATE TABLE Privileges enabling management of objects in any schema; for example, CREATE ANY TABLE Privileges can be controlled with the DDL commands GRANT and REVOKE, which add and revoke system privileges to the user or to a role (for Roles, see the lesson Managing Roles)

系统权限举例 Category Examples
INDEX CREATE ANY INDEX ALTER ANY INDEX DROP ANY INDEX TABLE CREATE TABLE CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE DELETE ANY TABLE SESSION CREATE SESSION ALTER SESSION RESTRICTED SESSION TABLESPACE CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE UNLIMITED TABLESPACE System Privileges: Examples There is no CREATE INDEX privilege. CREATE TABLE includes the CREATE INDEX and the ANALYZE commands. The user must have a quota for the tablespace or must have been granted UNLIMITED TABLESPACE. Privileges such as CREATE TABLE, CREATE PROCEDURE, or CREATE CLUSTER include the dropping of these objects. UNLIMITED TABLESPACE cannot be granted to a role. For truncating a table in another schema, the DROP ANY TABLE privilege is necessary.

授予系统权限 GRANT CREATE SESSION TO emi;
GRANT CREATE SESSION TO emi WITH ADMIN OPTION; Granting System Privileges Use the SQL statement GRANT to grant system privileges to users. The grantee can further grant the system privilege to other users with the ADMIN option. Exercise caution when granting system privileges with the ADMIN option. Such privileges are usually reserved for security administrator and rarely granted to other users. GRANT {system_privilege|role} [, {system_privilege|role} ]... TO {user|role|PUBLIC} [, {user|role|PUBLIC} ]... [WITH ADMIN OPTION] Granting System Privileges (continued) where: system_privilege specifies the system privilege to be granted role specifies the role name to be granted PUBLIC grants system privilege to all users WITH ADMIN OPTION enables the grantee to further grant the privilege or role to other users or roles Using Enterprise Manager to grant System Privileges Launch the Console: %oemapp console Choose to Launch the Console standalone. Expand your working database from the databases folder Right-click on the your working database and click Connect. Supply the username, password and service name for your working database and click OK. Expand the Security folder. Expand the Users folder and select the user who needs the privilege. Click the System Privileges tab on the detail side of the console. Select the system privileges that you want to grant. Optionally, check the Admin Option box and click Apply. Note: You can also launch the Console from the Windows NT Start menu.

回收系统权限 REVOKE CREATE TABLE FROM emi; Revoking System Privileges
System privileges can be revoked using the SQL statement REVOKE. Any user with the ADMIN OPTION for a system privilege can revoke the privilege from any other database user. The revoker does not have to be the user that originally granted the privilege. REVOKE {system_privilege|role} [, {system_privilege|role} ]... FROM {user|role|PUBLIC} [, {user|role|PUBLIC} ]... Note: The REVOKE command can only revoke privileges that have been granted directly with a GRANT command. Revoking system privileges may have an effect on some dependent objects. For example, if SELECT ANY TABLE is granted to a user, and that user has created any procedures or views that use a table in some other schema, revoking the privilege invalidates the procedures or views.

授予对象权限 GRANT EXECUTE ON dbms_output TO jeff;
GRANT UPDATE ON emi.customers TO jeff WITH GRANT OPTION; Granting Object Privileges GRANT { object_privilege [(column_list)] [, object_privilege [(column_list)] ]... |ALL [PRIVILEGES]} ON [schema.]object TO {user|role|PUBLIC}[, {user|role|PUBLIC} ]... [WITH GRANT OPTION] where: object_privilege specifies the object privilege to be granted column_list specifies a table or view column (This can be specified only when granting the INSERT, REFERENCES, or UPDATE privileges.) ALL grants all privileges for the object that have been granted WITH GRANT OPTION ON object identifies the object on which the privileges are to be granted WITH GRANT OPTION enables the grantee to grant the object privileges to other users or roles Granting Object Privileges (continued) Use the GRANT statement to grant object privileges. To grant privileges, the object must be in your schema or you must have been given the privilege WITH GRANT OPTION. By default, if you own an object, all privileges on that object are automatically acquired. Use caution when granting privileges on your objects to other users when security is a concern. Using Enterprise Manager to grant Object Privileges In this example, user HR grants ‘UPDATE’ on EMPLOYEES table to user Jeff. Launch the Console: %oemapp console Choose to Launch the Console standalone. Expand your working database from the databases folder Right-click on the your working database and click Connect. Connect as user HR by supplying the username, password and service name and click OK. Expand the Security folder. Expand the Users folder and select the user who needs the privilege. Click the Object Privileges tab on the detail side of the console. Expand user HR and expand the Tables folder Click the Table on which Object Privileges are to be granted, say EMPLOYEES table Select UPDATE from the Available Privileges field and click the down arrow Optionally, check the Grant Option box and click Apply.

回收对象权限 REVOKE SELECT ON emi.orders FROM jeff;
Revoking Object Privileges The REVOKE statement is used to revoke object privileges. To revoke an object privilege, the revoker must be the original grantor of the object privilege being revoked. Use the following command to revoke an object privilege: REVOKE { object_privilege [, object_privilege ]... | ALL [PRIVILEGES] } ON [schema.]object FROM {user|role|PUBLIC} [, {user|role|PUBLIC} ]... [CASCADE CONSTRAINTS] Revoking Object Privileges (continued) where: object_privilege specifies the object privilege to be granted ALL revokes all object privileges that are granted to the user ON identifies the object on which the object privileges are revoked FROM identifies users or roles from which the object privileges are revoked CASCADE CONSTRAINTS drops any referential integrity constraints that the revoke has defined using REFERENCES or ALL privileges Restriction Grantors can revoke privileges from only those users to whom they have granted privileges. Using Enterprise Manager to Revoke Object Privileges In this example, user HR is revoking the Object Privilege. Launch the Console: %oemapp console Choose to Launch the Console standalone. Expand your working database from the databases folder Right-click on the your working database and click Connect. Connect as user HR by supplying the username, password and service name and click OK. Expand the Security folder. Expand the Users folder and select the user from whom the privilege is to be revoked Click the Object Privileges tab on the detail side of the console. Select the object privilege that is to be revoked and click the Up arrow Click Apply.

获取权限信息查询数据字典 DBA_SYS_PRIVS SESSION_PRIVS DBA_TAB_PRIVS DBA_COL_PRIVS
Obtaining Privileges Information DBA_SYS_PRIVS lists system privileges granted to users and roles SESSION_PRIVS lists the privileges that are currently available to the user DBA_TAB_PRIVS lists all grants on all objects in the database DBA_COL_PRIVS describes all object column grants in the database.

总结通过这部分课程的学习，您应该已经了解: 创建用户时指定适当的口令机制控制用户对空间的使用控制系统和对象权限

练习这部分练习包括以下主题: 创建用户显示用户在数据字典中的相关信息移去用户表空间配额
Practice 15: Managing Users 1 Create user Bob with a password of CRUSADER. Make sure that any objects and temporary segments created by Bob are not created in the system tablespace. Also, ensure that Bob can log in and create objects up to one megabyte in size in USERS and INDX tablespaces. Hint: Ensure that the temporary tablespace temp is assigned. Use the lab15_01.sql script to grant Bob the ability to create sessions. 2 Create a user Emi with a password of MARY. Make sure that any objects and sort segments created by Emi are not created in the system tablespace. 3 Display the information on Bob and Emi from the data dictionary. Hint: This can be obtained by querying DBA_USERS. 4 From the data dictionary, display the information on the amount of space that Bob can use in tablespaces. Hint: This can be obtained by querying DBA_TS_QUOTAS. 5 a As user BOB change his temporary tablespace. What happens? Why? b As Bob, change his password to SAM. 6 As SYSTEM, remove Bob’s quota on his default tablespace. 7 Remove Emi’s account from the database. Hint: Because Emi owns tables, you need to use the CASCADE option. 8 Bob has forgotten his password. Assign him a password of OLINK and require that Bob change his password the next time he logs on.

问题和解答

管理一个ORACLE实例

目标通过这部分课程的学习,您将了解以下内容 : 创建和管理初始化参数文件启动和关闭实例监控和使用诊断文件

初始化参数文件 Oracle Instance SGA spfiledb01.ora SQL> CONNECT / AS SYSDBA
Redo log buffer Data buffer cache Shared pool Data dict. cache Library cache DBW0 SMON PMON CKPT LGWR Others spfiledb01.ora Initialization Parameter Files To start an instance, the Oracle server must read the initialization parameter file. SQL> CONNECT / AS SYSDBA SQL> STARTUP

初始化参数文件访问实例的入口存在两种类型参数: 显性的: 在文件中有个入口隐性的: 在文件中没有入口，但被指派了缺省的值
在不同的环境下可使用不同的初始化文件优化单个数据库的执行. 改变文件中参数设置并使其生效依赖使用的初始化参数文件类型; 静态初始化参数文件 PFILE 延续性初始化参数文件 SPFILE 延续性的初始化参数文件 SPFILE 所谓延续性是指对初始化参数的在线修改可以直接作用到参数文件中，这样所有被修改的参数将永久生效。 Initialization Parameter Files To start an instance, the Oracle server reads the initialization parameter file. Two types of initialization parameter files exist: Static parameter file, PFILE, commonly referred to as initSID.ora Persistent parameter file, SPFILE, commonly referred to as spfileSID.ora Parameter File Contents A list of instance parameters The name of the database the instance is associated with Allocations for memory structures of the System Global Area (SGA) What to do with filled online redo log files The names and locations of control files Information on undo segments

PFILE initSID.ora PFILE 是一个能被操作系统编辑器修改的文本文件. 手动对该文件进行修改.
下次数据库启动修改才能生效. 它的缺省路径是 $ORACLE_HOME/dbs. PFILE The PFILE is a text file that can be maintained using a standard operating system editor. The parameter file is read only during instance startup. If the file is modified, the instance must be shut down and restarted in order to make the new parameter values effective. Some parameters are dynamic, which means that they can be modified while the instance is running. Changes to dynamic parameters are not reflected in the PFILE. By default, the PFILE is located in the $ORACLE_HOME/dbs directory on a Unix machine and named initSID.ora. A sample init.ora file is created by the Universal Installer during installation. This sample init.ora file can be used to create an instance-specific initSID.ora.

PFILE 举例 # Initialization Parameter File: initdb01.ora db_name = db01
instance_name = db01 control_files = ( /u03/oradata/db01/control01db01.ctl, /u03/oradata/db01/control02db01.ctl) db_block_size = 4096 db_block_buffers = 500 shared_pool_size = # 30M Shared Pool db_files = 1024 max_dump_file_size = 10240 background_dump_dest = /u05/oracle9i/admin/db01/bdump user_dump_dest = /u05/oracle9i/admin/db01/udump core_dump_dest = /u05/oracle9i/admin/db01/cdump undo_management = auto undo_tablespace = undtbs . . . Rules for Specifying Parameters Specify the values in the following format: keyword=value. All parameters are optional. The server has a default value for each parameter. This value may be operating system dependent, depending on the parameter. Parameters can be specified in any order. Comment lines begin with the # symbol. Enclose parameters in double quotation marks to include character literals. Additional files can be included with the keyword IFILE. If case is significant for the operating system, then it is also significant in filenames. Multiple values are enclosed in parentheses and separated by commas. Note: Develop a standard for listing parameters; either list them alphabetically or group them by functionality. The PFILE varies from instance to instance and not does necessarily look like the preceding example.

SPFILE spfileSID.ora 是一个不用重启数据库而能将改变生效的二进制文件保存在ORACLE服务器端
能通过ALTER SYSTEM 命令直接对参数进行修改能指定改变临时在内存中生效还是从现在开始永久生效修改的值能被删除重置到实例缺省的值 SPFILE ORACLE默认就是此SPFILE，所以可以通过SQL*PLUS或OEM无需指定初始化参数在客户端远程启动数据库 SPFILE, new to Oracle9i, is a binary file. The file is not meant to be modified manually and must always reside on the server side. By default, the file is located in $ORACLE_HOME/dbs and has a default name in the format of spfileSID.ora. Once the file is created it is maintained by the Oracle server. The SPFILE provides the ability to make changes to the database persistent across shutdown and startup. The ALTER SYSTEM command is used to change the value of instance parameters. The SCOPE setting determines the scope of the change. MEMORY: Changes the parameter value only in the currently running instance SPFILE: Changes the parameter value in the SPFILE only BOTH: Changes the parameter value in the currently running instance and the SPFILE ALTER SYSTEM SET parameter = value [SCOPE = MEMORY|SPFILE|BOTH] The SPFILE can be modified with the ALTER SYSTEM command when the instance starts using the SPFILE or using the PFILE with the parameter SPFILE. ALTER SYSTEM SET undo_tablespace = 'UNDO2';

创建 SPFILE SPFILE能使用命令CREATE SPFILE命令从initSID.ora中创建，并且在数据库开启前或开启后都能执行
CREATE SPFILE FROM PFILE; Creating an SPFILE An SPFILE is created from an initSID.ora file using the CREATE SPFILE command. This can be executed before or after the database is open. CREATE SPFILE [='SPFILE-NAME'] FROM PFILE[='PFILE-NAME'] Where: SPFILE-NAME: Name of the SPFILE to be created: if not specified, the default SPFILE name is assumed. PFILE-NAME: Name of a PFILE, must be available on the server side. The CREATE SPFILE command requires the SYSDBA role to execute. The syntax can be reversed to create a PFILE from an SPFILE. Creating an SPFILE (continued) How to Modify the SPFILE Configuration Launch the Console: % oemapp console Choose Launch Standalone Expand your working database from the databases folder Expand Instance folder and click Configuration In the General tab, click All Initialization Parameters Modify a parameter in the value column. Click OK. Note: The Console can also be launched from the Windows NT Start menu.

SPFILE 举例 *.background_dump_dest='$ORACLE_HOME/admin/db01/bdump'
*.compatible='9.0.0' *.control_files='/u03/oradata/db01/ctrl01db01.ctl','/u03/oradata/db01/ctrl02db01.ctl' *.core_dump_dest='$ORACLE_HOME/admin/db01/cdump' *.db_block_buffers=500 *.db_block_size=4096 *.db_files=40 *.db_name='db01' *.instance_name='db01' *.remote_login_passwordfile='exclusive' *.shared_pool_size= # 30M Shared Pool *.undo_management='AUTO' db01.undo_tablespace='UNDOTBS01' db02.undo_tablespace='UNDOTBS02' . . . Rules Applying to SPFILE The comments specified on the same lines as a parameter setting in the PFILE are maintained in the SPFILE. All other comments are ignored. Although the text of an SPFILE is easily viewed in Unix the SPFILE is binary. Rules Applying to SPFILE (continued) Parameters That Should Be Specified Technical Note The default values depend on the version of the Oracle server. Commonly Modified Parameters

启动一个数据库 NOMOUNT OPEN STARTUP MOUNT NOMOUNT 实例启动 SHUTDOWN SHUTDOWN
Starting Up a Database When starting the database, you select the state in which it starts. The following scenarios describe different stages of starting up an instance. Starting the Instance (NOMOUNT) Usually you would start an instance without mounting a database only during database creation or the re-creation of control files. Starting an instance includes the following tasks: Reading the initialization file from $ORACLE_HOME/dbs in the following order: First spfileSID.ora. If not found then spfile.ora initSID.ora Specifying the PFILE parameter with STARTUP overrides the default behavior Allocating the SGA Starting the background processes Opening the alertSID.log file and the trace files The database must be named with the DB_NAME parameter either in the initialization file or in the STARTUP command. SHUTDOWN SHUTDOWN

启动一个数据库 MOUNT OPEN STARTUP MOUNT 控制文件打开 NOMOUNT 实例启动 SHUTDOWN SHUTDOWN
Starting Up a Database Mounting the Database To perform specific maintenance operations, you start an instance and mount a database but do not open the database. For example, the database must be mounted but not open during the following tasks: Renaming data files Enabling and disabling redo log archiving options Performing full database recovery Mounting a database includes the following tasks: Associating a database with a previously started instance Locating and opening the control files specified in the parameter file Reading the control files to obtain the names and status of the datafiles and redo log files. (However, no checks are performed to verify the existence of the data files and online redo log files at this time.) SHUTDOWN SHUTDOWN

启动一个数据库 OPEN OPEN STARTUP 实例中控制文件描述的所有文件被打开 MOUNT 控制文件打开 NOMOUNT 实例启动
Starting Up a Database Opening the Database Normal database operation means that an instance is started and the database is mounted and open; with normal database operation, any valid user can connect to the database and perform typical data access operations. Opening the database includes the following tasks: Opening the online data files Opening the online redo log files If any of the data files or online redo log files are not present when you attempt to open the database, the Oracle server returns an error. During this final stage, the Oracle server verifies that all the data files and online redo log files can be opened and checks the consistency of the database. If necessary, the System Monitor background process (SMON) initiates instance recovery. SHUTDOWN SHUTDOWN

STARTUP 命令启动实例并打开数据库: STARTUP
STARTUP PFILE=$ORACLE_HOME/dbs/initdb01.ora Starting Up To start up an instance, use the following command: STARTUP [FORCE] [RESTRICT] [PFILE=filename] [OPEN [RECOVER][database] |MOUNT |NOMOUNT] Note: This is not the complete syntax. where: OPEN enables users to access the database MOUNT mounts the database for certain DBA activities but does not provide user access to the database NOMOUNT creates the SGA and starts up the background processes but does not provide access to the database PFILE=parfile enables a nondefault parameter file to be used to configure the instance Starting Up (continued) FORCE aborts the running instance before performing a normal startup RESTRICT enables only users with RESTRICTED SESSION privilege to access the database RECOVER begins media recovery when the database starts Initialization Parameter File By default, when the database is started, the Oracle server looks in $ORACLE_HOME/dbs for an initialization parameter file. The Oracle server first attempts to read file spfilesid.ora and if not found then file initsid.ora. If neither file is located, an error message is received. Automating Database Startup On Unix Automating database startup and shutdown can be controlled by the entries in a special operating system file; for example, oratab in the /var/opt/oracle directory. Note: Refer to the installation guide for your operating system for more information. Troubleshooting Attempting to start the Oracle Utilities without starting these services results in the following error message: ORA-12547: TNS: lost contact

The ALTER DATABASE Command
改变数据库的状态（UNMOUNT TO MOUNT）: ALTER DATABASE db01 MOUNT; 打开数据库: ALTER DATABASE db01 OPEN; Changing the Status of the Database To open the database from STARTUP NOMOUNT to a MOUNT stage or from MOUNT to an OPEN stage, use the ALTER DATABASE command: ALTER DATABASE { MOUNT | OPEN } To prevent data from being modified by user transactions, the database can be opened in read-only mode. To start up an instance, use the following command: ALTER DATABASE OPEN [READ WRITE| READ ONLY] where: READ WRITE opens the database in read-write mode, so that users can generate redo logs READ ONLY restricts users to read-only transactions, preventing them from generating redo log information

关闭数据库关闭模式: NORMAL TRANSACTIONAL IMMEDIATE ABORT NO YES 关闭模式允许新的接入
等待直到当前的会话都结束等待直到当前的事务都结束强制执行 checkpoint 并关闭所有文件 A x I x o T x o N x o 关闭模式: NORMAL TRANSACTIONAL IMMEDIATE ABORT x o NO Shutting Down the Database Shut down the database to make operating system offline backups of all physical structures and to have modified static initialization parameters take effect. To shut down an instance you must connect as SYSOPER or SYSDBA and use the following command: SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ] YES

Shutdown Transactional
关闭对象 During a Shutdown Normal, Shutdown Transactional or Shutdown Immediate 这种方式关闭: 数据块高速缓冲区内容写入数据文件未提交的事务回滚释放资源这之后重新启动数据库: 不用做实例恢复 Shutdown Options Shutdown Normal Normal is the default shutdown mode. Normal database shutdown proceeds with the following conditions: No new connections can be made. The Oracle server waits for all users to disconnect before completing the shutdown. Database and redo buffers are written to disk. Background processes are terminated, and the SGA is removed from memory. Oracle closes and dismounts the database before shutting down the instance. The next startup does not require an instance recovery. Shutdown Transactional A transactional shutdown prevents clients from losing work. A transactional database shutdown proceeds with the following conditions: No client can start a new transaction on this particular instance. A client is disconnected when the client ends the transaction that is in progress. When all transactions have finished, a shutdown immediately occurs. Shutdown Options (continued) Shutdown Immediate Immediate database shutdown proceeds with the following conditions: Current SQL statements being processed by Oracle are not completed. The Oracle server does not wait for users currently connected to the database to disconnect. Oracle rolls back active transactions and disconnects all connected users. 一致性的数据库 (干净的数据库)

关闭对象这之后重新启动数据库: 这样关闭: 改变的信息将被重新写入重做日志文件必须使用回滚段来回滚未提交的改变
释放资源 During a Shutdown Abort or Instance Failure Startup Force 这样关闭: 已经修改的数据块缓冲区的内容不写入数据文件未提交的事务不被回滚 Shutdown Options Shutdown Abort If the normal and immediate shutdown options do not work, you can abort the current database instance. Aborting an instance proceeds with the following conditions: Current SQL statements being processed by the Oracle server are immediately terminated. Oracle does not wait for users currently connected to the database to disconnect. Database and redo buffers are not written to disk. Uncommitted transactions are not rolled back. The instance is terminated without closing the files. The database is not closed or dismounted. The next startup requires instance recovery, which occurs automatically. Shutdown Options (continued) How to Use the Console to Start Up or Shut Down Launch the Console: % oemapp console Choose Launch Standalone Expand your working database from the Databases folder Expand Instance folder and click Configuration From the General tab, select an Instance State, and click Apply. Note: You should be connected to the database with SYSDBA privileges to perform startup or shutdown. You can also launch the Console from Windows NT Start menu 不一致的数据库 (脏数据库)

通过检查诊断文件来管理实例诊断文件包含实例运行过程中所遇到的重要的事件信息. 作为日常管理数据库的基础，能为解决问题提供帮助.
存在以下几种诊断文件: 警告日志文件（alertSID.log）后台跟踪文件用户跟踪文件

警告日志文件警告日志文件记录了数据库运行中的一些操作命令和主要事件结果. 它被用来诊断日常数据库运行错误信息. 每条都对应有时间戳信息.
它所在路径定义在初始化参数BACKGROUND_DUMP_DEST中.

后台跟踪文件后台跟踪文件记录有被后台进程检测到的错误信息. 当后台进程遇到错误时才被创建
它所在路径定义在初始化参数BACKGROUND_DUMP_DEST中

用户跟踪文件用户跟踪文件当用户进程通过服务器进程连接到Oracle服务器时产生。
一个用户跟踪文件包含跟踪到的SQL执行语句或用户通讯错误信息. 当用户会话过程中遇到错误信息时产生. 它的路径定义在 USER_DUMP_DEST中. 它的大小定义在 MAX_DUMP_FILE_SIZE中，缺省为10M. User Trace Files User trace files contain statistics for traced SQL statements, which are useful for SQL tuning. In addition, user trace files contain user error messages. Naming convention for user trace file: sid_ora_PID.trc (db01_ora_23845.trc) on Unix. Its location is defined by the USER_DUMP_DEST initialization parameter. The default location on Unix is $ORACLE_HOME/rdbms/log.

打开或关闭用户跟踪用户跟踪文件能在会话或实例级别使用下列命令、参数打开或关闭: 会话级别使用 ALTER SESSION 命令: ALTER SESSION SET SQL_TRACE = TRUE 会话级别也可以通过执行DBMS 包: dbms_system.SET_SQL_TRACE_IN_SESSION 实例级别可通过设置初始化文件中的参数: SQL_TRACE = TRUE

总结通过这部分内容的学习，您应该了解: 创建和管理初始化参数文件启动和关闭实例、数据库监控和使用诊断文件

总结练习练习包括以下主题: 创建 SPFILE文件通过不同的方式启动和关闭数据库
Practice 3: Managing an Oracle Instance 1 Connect to the database as user SYS AS SYSDBA and shutdown the database. 2 With the database shutdown, create an SPFILE from the PFILE. Place the SPFILE in directory $HOME/ADMIN/PFILE with the file name format spfileSID.ora, use your instance name in place of SID. Create the SPFILE from the PFILE located in $HOME/ADMIN/PFILE. 3 From the operating system, view the SPFILE. 4 Connect as user SYS AS SYSDBA, and start the database using the SPFILE. 5 Shutdown the database and open it in read-only mode. Connect as user HR password HR and and insert the following into the REGIONS table. INSERT INTO regions VALUES ( 5, ‘Mars’ ); What happens? Put the database back in read-write mode. 6 Connect as user HR password HR and insert the following row into the table REGIONS; do not commit or exit. In a new telnet session start SQL*Plus. Connect as SYS AS SYSDBA and perform a shut down transactional. What happens? Rollback the insert in the HR session and exit, what happens? Note: Parameter UNDO_RETENTION, which will be discussed in Managing Undo Data, is set to 15 minutes by default. Because of this parameter it could be 15 minutes before the database shuts down after the ROLLBACK command is executed. Connect to another session as SYS AS SYSDBA and execute the following command to speed up the process. ALTER SYSTEM SET undo_retention=0 SCOPE=MEMORY; 7 Make sure the database is started. Keep the two SQL*Plus sessions open, one session as user SYS AS SYSDBA and one as user HR. As user SYS AS SYSDBA enable restricted session. As user HR select from the regions table. Is the select successful? In the HR session exit SQL*Plus then reconnect as HR. What happens? Disable restricted session.

SQL*Plus使用介绍 Schedule: Topic Timing Lecture 20 minutes
Total 20 minutes

目标通过这部分课程的学习,您将了解以下内容 : 登录到 SQL*Plus环境编辑SQL 命令 SQL*Plus 命令格式化并输出结果
结合脚本文件执行 Lesson Aim You may want to create SELECT statements that can be used again and again. This lesson also covers the use of SQL*Plus commands to execute SQL statements. You learn how to format output using SQL*Plus commands, edit SQL commands, and save scripts in SQL*Plus.

SQL*Plus主要内容登录到 SQL*Plus. 描述表的结构. 编辑SQL语句. 在SQL*Plus中执行SQL.
SQL*Plus is an environment in which you can do the following: Execute SQL statements to retrieve, modify, add, and remove data from the database Format, perform calculations on, store, and print query results in the form of reports Create script files to store SQL statements for repetitive use in the future SQL*Plus commands can be divided into the following main categories:

登录到 SQL*Plus sqlplus [username[/password [@database]]] 从 Windows 环境:
通过命令行: sqlplus [username[/password Logging In to SQL*Plus How you invoke SQL*Plus depends on which type of operating system or Windows environment you are running. To log in through a Windows environment: 1. Select Start > Programs > Oracle for Windows NT > SQL*Plus. 2. Fill in the username, password, and database name. To log in through a command line environment: 1. Log on to your machine. 2. Enter the SQL*Plus command shown in the slide. In the syntax: username your database username. password your database password (if you enter your password here, it is visible.) @database the database connect string. Note: To ensure the integrity of your password, do not enter it at the operating system prompt. Instead, enter only your username. Enter your password at the Password prompt. After you log in to SQL*Plus, you see the following message (if you are using SQL*Plus version 9i): SQL*Plus: Release Development on Tue Jan 9 08:44: (c) Copyright 2000 Oracle Corporation. All rights reserved.

显示表的结构内容 SQL> DESCRIBE departments Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) Displaying Table Structure (continued) The example in the slide displays the information about the structure of the DEPARTMENTS table. In the result: Null? specifies whether a column must contain data; NOT NULL indicates that a column must contain data Type displays the data type for a column The following table describes the data types:

SQL*Plus 编辑命令 A[PPEND] text C[HANGE] / old / new C[HANGE] / text /
CL[EAR] BUFF[ER] DEL DEL n DEL m n SQL*Plus Editing Commands SQL*Plus commands are entered one line at a time and are not stored in the SQL buffer. Guidelines If you press [Enter] before completing a command, SQL*Plus prompts you with a line number. You terminate the SQL buffer either by entering one of the terminator characters (semicolon or slash) or by pressing [Enter] twice. The SQL prompt then appears.

SQL*Plus 编辑命令 I[NPUT] I[NPUT] text L[IST] L[IST] n L[IST] m n R[UN] n
n text 0 text SQL*Plus Editing Commands (continued) Note: You can enter only one SQL*Plus command per SQL prompt. SQL*Plus commands are not stored in the buffer. To continue a SQL*Plus command on the next line, end the first line with a hyphen (-). Instructor Note Show students the use of the commonly used editing commands, such as A[PPEND], C[HANGE], DEL, L[IST], and R[UN].

使用 LIST, n, and APPEND SQL> LIST 1 SELECT last_name
2* FROM employees SQL> 1 1* SELECT last_name SQL> A , job_id 1* SELECT last_name, job_id Using LIST, n, and APPEND Use the L[IST] command to display the contents of the SQL buffer. The * beside line 2 in the buffer indicates that line 2 is the current line. Any edits that you made apply to the current line. Change the number of the current line by entering the number of the line you want to edit. The new current line is displayed. Use the A[PPEND] command to add text to the current line. The newly edited line is displayed. Verify the new contents of the buffer by using the LIST command. Note: Many SQL*Plus commands including LIST and APPEND can be abbreviated to just their first letter. LIST can be abbreviated to L, APPEND can be abbreviated to A. Instructor Note Show students the use of the abbreviated editing commands, using uppercase and lowercase—for example, A for APPEND, or L for LIST. SQL> L 1 SELECT last_name, job_id 2* FROM employees

使用 CHANGE 命令 SQL> L 1* SELECT * from employees
SQL> c/employees/departments 1* SELECT * from departments SQL> L 1* SELECT * from departments Using the CHANGE Command Use L[IST] to display the contents of the buffer. Use the C[HANGE] command to alter the contents of the current line in the SQL buffer. In this case, replace the employees table with the departments table. The new current line is displayed. Use the L[IST] command to verify the new contents of the buffer.

SQL*Plus 文件命令 SAVE filename GET filename START filename @ filename
EDIT filename SPOOL filename EXIT SQL*Plus File Commands SQL statements communicate with the Oracle server. SQL*Plus commands control the environment, format query results, and manage files. You can use the commands described in the following table:

使用 SAVE 和START 命令 SQL> L
1 SELECT last_name, manager_id, department_id 2* FROM employees SQL> SAVE my_query Created file my_query SQL> START my_query LAST_NAME MANAGER_ID DEPARTMENT_ID King Kochhar ... 20 rows selected. SAVE Use the SAVE command to store the current contents of the buffer in a file. In this way, you can store frequently used scripts for use in the future. START Use the START command to run a script in SQL*Plus. EDIT Use the EDIT command to edit an existing script. This opens an editor with the script file in it. When you have made the changes, exit the editor to return to the SQL*Plus command line.

小结 SQL*Plus 环境下: 执行SQL 语句编辑SQL 语句格式化输出与脚本文件交互 Summary
SQL*Plus is an execution environment that you can use to send SQL commands to the database server and to edit and save SQL commands. You can execute commands from the SQL prompt or from a script file.

逻辑备份恢复简介

目标逻辑备份的概念 Oracle 数据库逻辑备份使用工具 Oracle 数据库逻辑备份形式

用途物理备份的补充不同版本数据库的迁移不同平台数据库的迁移

与物理备份的比较占用较小的空间更为灵活，有四种方式需要确认字符集 Categories of Failures
Different types of failures may occur in an Oracle database environment. These include: Statement failure User process failure User error Instance failure Media failure Network failure Each type of failure requires a varying level of involvement by the DBA to recover effectively from the situation. In some cases, recovery depends on the type of backup strategy that has been implemented. For example, a statement failure requires little DBA intervention, whereas a media failure requires the DBA to employ a tested recovery strategy.

备份工具备份工具 EXPORT (使用命令EXP) 恢复工具 IMPORT(使用命令IMP) Statement Failure
Statement failure occurs where there is a logical failure in the handling of a statement in an Oracle program. Types of statement failures include: A logical error occurs in the application. The user attempts to enter invalid data into the table, perhaps violating integrity constraints. The user attempts an operation with insufficient privileges, such as an insert on a table using only SELECT privileges. The user attempts to create a table but exceeds the user’s allotted quota limit. The user attempts an INSERT or UPDATE on a table, causing an extent to be allocated, but insufficient free space is available in the tablespace. Note: When a statement failure is encountered, it is likely that the Oracle server or the operating system will return an error code and a message. The failed SQL statement is automatically rolled back, then control is returned to the user program. The application developer or DBA can use the Oracle error codes to diagnose and help resolve the failure.

备份形式全库备份按用户备份按表备份表空间备份

Oracle Export 和 Import 工具作用
归档历史数据存储表的定义在不同平台及版本间转换数据在不同数据库间移植表空间 Export and Import Utility Overview The Export utility provides a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations. The Export utility can provide a logical backup of: Database objects A tablespace An entire database The Import utility is used to read a valid Export file for moving data into a database. Redo log history cannot be applied to objects that are imported from an export file, therefore data loss may occur, but can be minimized. The DBA can use the Export and Import utilities to complement normal operating system backups by using them to: Create a historical archive of a database object or entire database; for example, when a schema is modified to support changing business requirements. Save table definitions in a binary file. This may be useful for creating and maintaining a baseline of a given schema structure. Move data from one Oracle database version to another, such as upgrading from Oracle8i to Oracle9i.

运行EXPORT工具的方法以交互式对话的方式命令行参数文件通过OEM启动 Export Methods
You can invoke Export and specify parameters by using any of the following methods: Command-line entries Interactive Export prompts Parameter files Oracle Enterprise Manager To use Export, you must have the CREATE SESSION privilege on an Oracle database. To export tables owned by another user, you must have the EXP_FULL_DATABASE role enabled. This role is granted to all DBAs. If you do not have the system privileges contained in the EXP_FULL_DATABASE role, you cannot export objects contained in another user’s schema. Note: Many options are only available by using the command-line interface. However, you can use a parameter file with command line.

Export 模式表模式 Table definitions Table data (all or selected rows)
Owner’s table grants Owner’s table indexes Table constraints 用户模式 Tables definitions Tables data Owner’s grants Owner’s indexes Tables constraints 表空间模式 Table definitions Grants Indexes Table constraints Triggers 全数据库模式 Tables definitions Tables data Grants Indexes Tables constraints Table Mode Table mode exports specified tables in the user’s schema, rather than exporting all tables. A privileged user can export specified tables owned by other users. User Mode User mode exports all objects for a user’s schema. Privileged users can export all objects in the schemas of a specified set of users. This mode can be used to complement a full database export. Tablespace Mode You can use transportable tablespaces to move a subset of an Oracle database and plug it into another Oracle database, essentially moving tablespaces between the databases. Moving data by way of transportable tablespaces can be much faster than performing either an import/export of the same data, because transporting a tablespace only requires the copying of data files and integrating the tablespace structural information. You can also use transportable tablespaces to move index data, thereby avoiding the index rebuilds you would have to perform when importing table data. Full Database Mode Full database mode exports all database objects, except those in the SYS schema. Only privileged users can export in this mode. Note: This is a partial treatment. For a full treatment of modes and objects, see Oracle9i Utilities Guide, Part No. A

Export 命令语法举例 exp keyword = (value, value2, … ,valuen)
exp hr/hr TABLES=(employees,departments) rows=y file=exp1.dmp exp system/manager OWNER=hr direct=y file=expdat.dmp exp system/manager TRANSPORT_TABLESPACE=y TABLESPACES=(ts_emp) log=ts_emp.log Command Line Export You can copy database data to an operating system file by using the command line mode of the Export utility. This file is only readable by the Import utility. Example Create an export file named exp1.dmp that includes the tables EMPLOYEES and DEPARTMENTS from HR schema including rows: $ exp hr/hr tables=(employees,departments) rows=y file=exp1.dmp Create a fast export file named expdat.dmp that includes all objects for HR schema: $ exp system/manager owner=hr direct=y If file is not specified, the default export file name is expdat.dmp. Create an export file named expdat.dmp that includes all definitions of objects belonging to the tablespace ts_employees and generate a log file named ts_employees.log: $ exp system/manager TRANSPORT_TABLESPACE=y TABLESPACES=(ts_employees) LOG=ts_employees.log Create an export file named expcum1.dmp that includes all definitions and data modified in the database since the last cumulative or complete export. $ exp system/manager FULL=y INCTYPE=cumulative FILE=expcum1.dmp exp system/manager FULL=y inctype=cumulative file=expcum1.dmp

使用 Import 工具恢复获取建表定义从一个有效的输出文件中获取数据如有用户错误，可以从相应文件中恢复 Import Utility
The Import utility reads the object definitions and table data from an Export dump file. It inserts the data objects into an Oracle database. Import functionality includes: Creating table definitions since the table definitions are stored in the Export file. Choosing to import data without rows will create just the table definitions. Extracting data from a valid export file by using the Table, User, Tablespace, or Full Import modes Importing data from a complete or cumulative Export file Recovering from user failure errors where a table is accidentally dropped or truncated by using one the previously mentioned methods

Import 模式模式描述 Table 获取指定的表. User 获取一个用户的所有对象 Tablespace 获取一个表空间的所有对象
模式描述 Table 获取指定的表. User 获取一个用户的所有对象 Tablespace 获取一个表空间的所有对象 Full Database 获取输出文件的所有对象 IMPORT Modes Table Mode Table mode imports all specified tables in the user’s schema, rather than all tables. A privileged user can import specified tables owned by other users. User Mode User mode imports all objects for a user’s schema. Privileged users can import all objects in the schemas of a specified set of users. Tablespace Mode Tablespace mode allows a privileged user to move a set of tablespaces from one Oracle database to another. Full Database Mode Full database mode imports all database objects, except those in the SYS schema. Only privileged users can import in this mode.

Import 工具的命令行语法 imp keyword = value or keyword = (value, value2, … value n) 举例 imp hr/hr TABLES=(employees,departments) rows=y file=exp1.dmp imp system/manager FROMUSER=hr file=exp2.dmp Examples Import into HR schema, the tables EMPLOYEES and DEPARTMENTS, including rows, by using the Export file named exp1.dmp. $ imp hr/hr tables=(employees,departments) rows=y \ file=exp1.dmp Import all objects belonging to the HR schema, including rows, by using the export file named exp2.dmp. $ imp system/manager FROMUSER=hr file=exp2.dmp Import all definitions of objects belonging to the tablespace ts_employees, by using the export file named expdat.dmp. $ imp system/manager TRANSPORT_TABLESPACE=y TABLESPACES=ts_employees If the file parameter is not specified, Import looks for the default file; expdat.dmp. Note: Command-line mode options are similar to interactive mode options but offer more functionality. imp system/manager TRANSPORT_TABLESPACE=y TABLESPACES=ts_employees

Import 处理顺序新表产生导入数据建立索引导入触发器导入一致性约束导入其余形式的索引
Import Process Sequence When importing a table, the export file is read and the table and data are created in the following order: 1. New tables are created 2. Data is imported 3. Indexes are built 4. Triggers are imported 5. Integrity constraints are enabled on the new tables 6. Any bitmap, functional, and/or domain indexes are built This sequence prevents data from being rejected due to the order in which tables are imported. This sequence also prevents redundant triggers from firing twice on the same data (once when it is originally inserted and again during the import). The order in which you import tables may be important if you do not import all the objects that a user owns. For, example, if the table with the foreign key has a referential check on the table with the primary key, and the foreign key table is imported first, then all rows that reference the primary key that have not been imported will be rejected if the constraints were enabled. For a full database Export this is not a problem.

国际化语言支持 Export 文件定义字符集 Import 工具把原字符集转化为本数据库的字符集多字节的字符集必须对应相同特征的字符集
National Language Support Considerations When moving data from one Oracle database by one character set to a database with a different character set, ensure the data conversion is handled appropriately. You can do this by setting the NLS_LANG environment variable to the character set definition of the database from which the data is being exported. Not setting this correctly could cause unwanted conversion of characters in the data, possibly causing loss of data. Examples Converting from a 7-bit ASCII character set, such as American English, to an 8-bit character set such as Danish, no conversion is needed because all characters have an equivalent character in the Danish alphabet. When converting from an 8-bit ASCII character set, such as Danish, to a 7-bit character set such as American English, the extra Danish characters that are not found in the American alphabet may be converted to question marks (?). In this case the question marks are substituted for the unknown Danish characters which, is the appropriate result. In the 8-bit to 8-bit data movement, whether characters are lost depends upon the specifics of the languages used to enter the data. For example, the Spanish alphabet has letters that are not in the Danish alphabet, so moving data from a Spanish database to a Danish one might result in possible conversion and therefore possible loss of those characters.

总结和练习 Export 和 Import 工具的使用 Export 和 Import 工具概念和结构

数据库的日常检查

数据库的日常检查检查数据库状态确认数据库是否在open状态，正常提供服务。 oracle% sqlplus "/as sysdba"
SQL> select status, instance_role from v$instance; STATUS INSTANCE_ROLE OPEN PRIMARY_INSTANCE

数据库的日常检查数据库版本检查数据库应该打了最新的patchset; 8i要求是8.1.7.4；
SQL>select * from v$version;

数据库的日常检查查看SGA区域 SGA区各项buffer之和应该小于系统物理内存的一半检查数据库SGA区：
SQL>show sga 检查系统物理内存： Solaris: $ /usr/sbin/prtconf |grep -i "Memory size" HP: $ grep Physical /var/adm/syslog/syslog.log AIX: $ /usr/sbin/lsattr -E -l sys0 -a realmem

数据库的日常检查回滚段竞争检查 8i:返回值应该低于2%。如果该值过高，8i需要手工建立更多的回滚段。
SQL> select 'The average of waits/gets is '|| round((sum(waits) / sum(gets)) * 100,2)||'%' From v$rollstat; 9i:Nospace值应该为0。如果持续存在非零值，建议增加回滚表空间的大 SQL>select distinct nospaceerrcnt as nospace From v$undostat Where begin_time>sysdate-(1/12);

数据库的日常检查查看日志文件数据库运行时，可以通过查询v$log_history来观察log switch的切换时间。
推荐联机日志最好是业务非高峰期每小时切换一次，业务高峰期20分钟左右切换一次。 SQL>select to_char(first_time,'yyyy-mm-dd hh24:mi:ss') change_time from v$log_history; CHANGE_TIME :02:52 :10:45 :17:01

数据库的日常检查查看表空间查看各个表空间所占的大小，剩余空间，使用空间百分比等信息
特别是当业务表空间的剩余空间低于50M或者使用空间百分比大于90%时，需要考虑增加表空间的大小。 SQL> select a.tablespace_name, a.bytes totalbytes, sum(b.bytes) freebytes from dba_data_files a, dba_free_space b where a.file_id = b.file_id group by a.tablespace_name, a.bytes ; TABLESPACE_NAME TOTALBYTES FREEBYTES CWMLITE

数据库的日常检查检查job状态 Broken列应该为N；如果Broken列为Y，检查oracle告警日志,分析job失败的原因。解决后运行：exec dbms_job.run(:job); SQL>select job,what,to_char(next_date,’yyyy/mm/dd hh24:mi:ss’),broken,failures from dba_jobs;

数据库的日常检查查看数据库连接用户查看当前数据库连接的用户进程的具体信息，包括连接的数据库用户名，机器名，运行的程序名，进程状态。
select username,machine,program,status from v$session where osuser <> 'oracle'; USERNAME MACHINE PROGRAM STATUS CORPRING CHINA\MENGWEI INACTIVE SYSTEM CHINA\H dbsnmp.exe INACTIVE

Network 配置

目标监听管理本地服务名配置处理简单的连接问题数据库故障紧急处理

监听进程客户端服务端 Listener tnsnames.ora sqlnet.ora listener.ora
Characteristics of the Listener Process The listener is a process running on a node that listens for incoming connections on behalf of a database or a number of databases. The following are the characteristics of a listener: A listener process can listen for more than one database Multiple listeners can listen on behalf of a single database to perform load balancing The listener can listen for multiple protocols The default name of the listener in Oracle Net is LISTENER The name of the listener must be unique per listener.ora file Note: Oracle9i databases requires a release 9.0 listener. Previous versions of the listener are not supported. However, it is possible to use a release 9.0 listener with previous versions of the Oracle database. sqlnet.ora listener.ora

配置 listener.ora 文件 listener.ora 的缺省配置: Listener name LISTENER
Port Protocols TCP/IP and IPC SID name 缺省实例 Host name 缺省主机名 The listener.ora File The listener.ora file is used to configure the listener for static service registration. The listener.ora file must reside on the machine or node on which the listener is to reside. The listener.ora file contains configuration information for the following: The listener name The listener address Databases that use the listener Listener parameters

listener.ora 例子 1. LISTENER = 2. (ADDRESS_LIST =
3. (ADDRESS= (PROTOCOL= TCP)(Host= stc-sun02)(Port= 1521)) ) 4. SID_LIST_LISTENER = 5. (SID_LIST = (SID_DESC = (ORACLE_HOME= /home/oracle) (GLOBAL_DBNAME = ORCL.us.oracle.com) (SID_NAME = ORCL) sample additional SID description ... listener.ora File Contents The default listener.ora file contains the following parameters: The name of the listener. The default name is LISTENER. The ADDRESS_LIST parameter contains a block of addresses at which the listener listens for incoming connections. Each of the addresses defined in this block represents a different way by which a listener receives a connection. The TCP address identifies incoming TCP connections from clients on the network attempting to connect to port The clients use the port defined in their tnsnames.ora file to connect to this listener. Based on the SID_LIST defined for this listener, the listener specifies the database to which to connect. Please note that is possible to configure multiple listeners here as long as they have unique names and unique ports on the node where they are configured. Each listener configured will have its own SID_LIST but a single database can be serviced by multiple listeners. A listener can listen for more than one database on a machine. The SID_LIST_LISTENER_NAME block or parameter is where these SIDs are defined. The SID_LIST parameter is defined if more than one SID is defined.

监听工具 (LSNRCTL) 工具可通过下面命令使用 UNIX 命令语法: 提示符: 管理非缺省的监听
$ lsnrctl <command name> LSNRCTL> <command name> Windows NT Platform Command Line Syntax On the Windows NT operating system, use the following command to start the Listener Control utility: C:\> lsnrctl command When the lsnrctl command is issued, the command will work against the default listener “listener” unless the SET LISTENER command is executed. Another way to control different listeners is to use the listener name as a command modifier: $ lsnrctl start listener02 LSNRCTL> set current_listener listener02

监听程序的管理查看监听程序的状态： % lsnrctl status 从其他集群节点上测试监听程序：
% tnsping tns_alias 启动监听程序进程： % lsnrctl start 关闭监听程序进程： % lsnrctl stop 如果数据库配置文件listener.ora中配置的监听名称不是缺省的LISTENER时，启动、关闭监听必须带上监听名称，例如：lsnrctl start listener_rac1

配置本地网络服务名 Net Service Name Configuration
After selecting Local as the Naming Method, service names can now be configured by selecting the Local Net Service Name Configuration option button from the Oracle Net Services Configuration Assistant.

服务名添加 Add a Net Service Name
You use the next window to create, reconfigure, delete, rename, or test a net service name. In this example, the Add option button is chosen.

指定oracle版本 Specifying the Database Version
Specify whether the database or service is Oracle8i or later. Earlier Oracle versions require extra configuration on the listener side while Oracle8i or 9i databases and services do not.

输入数据库服务名 Specify the Service Name
For your Oracle8i or Oracle9i database, you must next enter the database service name.

网络协议 Select the Network Protocol
The network protocol to be used by the connection must now be specified. The protocols available in the configuration assistant reflect only those protocols that have been previously installed. Uninstalled protocols are not present in the protocol list presented by the Oracle Net Service Configuration Assistant. Note: With the introduction of Oracle9i, SPX is no longer a supported protocol.

主机名和 Listener 端口 Configuring the Host Name and Port Number
Enter the host name and the port number, and click Next. Host Name Enter the fully qualified name of the machine on which the database you want to connect to and communicate with resides. Port Number Enter the number of the port on which the Oracle Net listener monitors connection requests to the server (host). By default, the Configuration Assistant sets the listener port to If required, an alternative port number can be specified.

测试和连接 Test the Service Information
The connection information can now be tested. Click Yes, perform a test then click on Finish to proceed.

连接测试结果 Test Result If the data entered is correct, the connection should be made successfully. If not, the Details window should provide useful diagnostic information to troubleshoot the connection. Please note that the default username used for the connection is scott. If you have no such user you should click Change Login and enter a valid username and password combination then retry the connection. If the connection is successful, click Next to continue. Do not click Cancel because the service information is not yet saved. Note: The service name can also be tested from the command line by using the tnsping utility. For example: $ tnsping U01 TNS Ping Utility for Solaris: Version 9 - Production on 10-MAY-2001 Used parameter files: /u01/user01/NETWORK/ADMIN/sqlnet.ora /u01/user01/NETWORK/ADMIN/tnsnames.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=stc-sun02)(PORT=1701)) OK (0 msec)

自己命名网络服务名 Choosing the Net Service Name
Enter a name for the net service name next. The Configuration Assistant defaults the name to the database service name that was entered initially. A more meaningful or descriptive name can be entered if you want. Click Next to continue.

保存 Saving the Net Service Name
When you select the No option button and click on Next, the service name is saved by default to the tnsnames.ora file located in the $ORACLE_HOME/network/admin directory.

tnsnames.ora MY_SERVICE.US.ORACLE.COM = (DESCRIPTION = (ADDRESS_LIST =
# TNSNAMES.ORA Network Configuration File:/u03/ora9i/rel12/network/admin/tnsnames.ora # Generated by Oracle configuration tools. MY_SERVICE.US.ORACLE.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stc-sun02.us.oracle.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = TEST.us.oracle.com) The tnsnames.ora File The tnsnames.ora file is used to store net service names. The default location is $ORACLE_HOME/network/admin on UNIX and %ORACLE_HOME%\network\ admin on NT. The content of the tnsnames.ora is as follows: Parameter Description MY_SERVICE.US. … Net service name and domain name. Keyword for describing the connect descriptor. Descriptions are always specified the same way. DESCRIPTION ADDRESS Keyword for the address specification. If multiple addresses are specified, use the keyword ADDRESS_LIST prior to the ADDRESS PROTOCOL Specifies the protocol used. HOST Protocol-specific information for TCP/IP-Specifies the host name of the server or IP address. Can differ for another protocol. PORT Protocol specific information for TCP/IP-Specifies the port number on which the server side listener is listening. CONNECT_DATA Specifies the database service to which to connect.

sqlnet.ora # SQLNET.ORA Network Configuration File: /u03/ora9i/rel12/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DEFAULT_DOMAIN = us.oracle.com NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME) SQLNET.EXPIRE_TIME=0 sqlplus SQL*Plus: Release Beta on Tue Feb 27 10:11: (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release Beta JServer Release Beta SQL> The sqlnet.ora File The sqlnet.ora file controls the behavior of Oracle Net Services. The default location is $ORACLE_HOME/network/admin on UNIX and %ORACLE_HOME%\network\admin on NT. The default location can be overridden by defining the TNS_ADMIN environment variable. The NAMES.DIRECTORY_PATH parameter controls how Oracle Net Services resolves net service names into connect descriptors. Multiple methods can be represented as a comma-separated list enclosed by parentheses. Net services attempts to resolve service names using each method listed working from left to right. Once the naming methods and service names have been configured and tested successfully, you can connect to the server from the client by using any Oracle client tool.

客户端常见问题处理常见错误: ORA-12154 “TNS:could not resolve service name”
ORA “TNS:could not find path to destination” ORA “TNS:unable to connect to destination” ORA “TNS:illegal ADDRESS parameters” ORA “TNS:no listener” Troubleshooting The following describes common errors and how they can be resolved. ORA-12154: “TNS:could not resolve service name” Cause Oracle Net Services cannot locate the connect descriptor specified in the tnsnames.ora configuration file. Actions 1. Verify that a tnsnames.ora file exists and that it is accessible. 2. Verify that the tnsnames.ora file is in the location specified by the TNS_ADMIN environment variable. 3. In your tnsnames.ora file, verify that the service name specified in your connection string is mapped to a connect descriptor in the tnsnames.ora file. Also, verify that there are no syntax errors in the file. 4. Verify that there are no duplicate copies of the sqlnet.ora file. 5. If you are connecting from a login dialog box, verify that you are not placing an at symbol before your connection service name.

数据库故障紧急处理

数据库故障紧急处理错误信息查看工具oerr
oracle提供了一个实用程序oerr,利用它可以得到oracle错误信息的说明。UNIX平台下可以使用oerr工具快速获得oracle错误信息的详细描述。 oerr工具的具体语法为：oerr 错误类型错误代码例如：查询ORA-2336, 键入 "oerr ora 2236"

数据库故障紧急处理 Oracle常见错误 ORA-1652到ORA-1655
ORA-165X错误信息是指表空间没有足够的空间可分配，包括ora-1652、ora-1653、ora-1654和ora-1655。 ORA-01555（snapshot too old） 9i引入了undotablespace，只要把undotablespace设的足够大，并把undo_retention设的足够大（大于完成大事务所需的时间），就有可能彻底解决ORA-1555错(但太大会引起系统性能下降)

数据库故障紧急处理 Oracle常见错误 ORA-04031
如果shared_pool_size参数值已经很大（大于350M），那ORA-4031错发生的原因很可能是因为oracle数据库的BUG或是应用程序未能很好的使用绑定变量(bind variable)。清空当前的shared_pool，可以暂时解决ora-4031错的问题 ORA-600内部错误第一个参数提供了实际错误信息（例如1579代表块中断错误），而其他参数提供相关其他信息（例如上述块中断错误中，显示Oracle想要读的块号和实际读的块号） ORA-600错误会导致系统生成一个或多个跟踪文件

数据库故障紧急处理业务恢复处理 RAC数据库出现故障时，要迅速查看数据库告警日志文件，确认出现问题的原因，影响的范围，故障的现象。比如：
数据库故障是出现一次还是反复出现数据库故障是否能够比较容易的恢复，绕过，解决数据库故障发生后业务是否能正常运行如果RAC数据库的某个节点数据库异常终止服务，但其他节点正常运行时，可以先尝试手工启动出现问题的数据库，恢复数据库到运行状态。

数据库故障紧急处理手工收集现场信息错误现象的具体描述数据库告警日志文件：alert_{ORACLE_SID}.log
数据库跟踪文件：*.TRC 初始化参数文件：SPFILE不能直接阅读，需要转为文本网络配置文件：listener.ora, tnsnames.ora,sqlnet.ora 数据库版本操作系统的类型和版本

数据库故障紧急处理使用ORACLE RDA收集现场信息
RDA是由ORACLE开发的一个shell程序，用于全面收集oracle软件的环境信息，供远程故障诊断工程师参考。 RDA安装：为RDA建立一个独立的目录，不要在同一目录中包含有就版本的RDA。脚本执行完毕将在RDA_Output目录下产生htm和txt文档形式的信息报告，以及这些文档的一个.tar.Z的打包。使用RDA需要注意的问题：确定数据库是否已经起来并能够连接上确认FTP过程使用BIN方式，并且下载没有错误。确定tar解开后的shell文件具有可执行权限。

总结主机名方法本地服务名方法使用配置助理处理简单常见问题数据库故障紧急处理

数据库推荐书目介绍几本oracle入门的好书 Oracle8i 备份恢复手册 Oracle8I初学者指南
Oracle8i PLSQL程序设计 Oracle8数据库管理员手册 Oracle9 statspack高性能调整 OCP的教学用书 ORACLE随机手册

数据库推荐网站 http://tahiti.oracle.com oracle的官方文档网站
oracle的技术支持网站。有海量的Knowledge Base，各种问题解决经验。 oracle的官方网站，可以在这里down oracle的软件、官方文档和获得最新的消息国内人气最强的数据库等IT技术论坛中国Oracle官方论坛

问题和解答

Oracle数据库维护培训胶片集成产品部.

Similar presentations

Presentation on theme: "Oracle数据库维护培训胶片集成产品部."— Presentation transcript:

Similar presentations

About project

反馈

请登录

Auth with social network:

Oracle数据库维护培训胶片 集成产品部.

Similar presentations

Presentation on theme: "Oracle数据库维护培训胶片 集成产品部."— Presentation transcript:

Similar presentations

About project

反馈

Oracle数据库维护培训胶片集成产品部.

Presentation on theme: "Oracle数据库维护培训胶片集成产品部."— Presentation transcript: