实习日志第4天·学习笔记DB Instance

今日大雪。。。车堵的要命- -"，直接奔迟到。。。悲剧，车上浪费了好多时间

 

大章节五——Oracle实例结构

内容分为4块，1.实例的介绍，2.内存结构，3.进程结构，4.应用和网络体系结构

 

13. The Oracle Database Instance

本章主要介绍数据库实例，参数以及实例的诊断文件，实例的生成，还有打开和关闭数据库的过程。

·介绍实例instance

·实例的startup和shutdown

·checkpiont

·实例恢复instance recovery

·参数文件parameter files

·诊断文件diagnostic files

 

Introduction to the Oracle Database Instance

数据库实例就是一堆管理数据文件的内存结构（memory structures）

实例对数据库数据进行管理，并为多用户提供服务。

Database Instance Structure

当实例起来时，数据库会分配一个内存空间（SGA）并启动几个后台进程（background processes）

SGA的作用是：

1.Maintaining internal data structures that are accessed by many processes and threads concurrently

2.Caching data blocks read from disk

3.Buffering redo data before writing it to the online redo log files

4.Storing SQL execution plans

 

SGA可以被oracle进程共享使用，这包括服务器进程（server processes）和后台进程background processes

 

Database Instance Configurations

1.Single-instance configuration  一数据库对一实例的关系（一对一）

2.Oracle Real Application Clusters (Oracle RAC) configuration   一对多实例的关系（一对多）

但是注意：一个实例只能对应一个数据库a database instance is associated with only one database at a time. 一个实例不能同时启动两个数据库

During this period, an instance can associate itself with one and only one database.the instance can mount a database only once, close it only once, and open it only once. After a database has been closed or shut down, you must start a different instance to mount and open this database. 在一个实例启动后，Oracle 将令此实例与指定的数据库（只能有一个）关联共同工作。此外，每个数据库实例只能被使用一次。Close 和shutdown数据库后再启动DB要另外建个实例（重新启动DB）。

 

Oracle System Identifier (SID)

The system identifier (SID) is a unique name for an Oracle database instance on a specific host.

 

Overview of Instance Startup and Shutdown

Startup

数据库的启动过程

can use the SQL*Plus STARTUP command, Oracle Enterprise Manager (Enterprise Manager), or the SRVCTL utility to perform. these steps.

Connection with Administrator Privileges 只能由以管理员权限连接数据库的用户启动和停止数据库。普通用户不能改变数据库当前状态

1.被授予administrator privilege（管理员特权）连接数据库的用户

2.拥有SYSDBA和SYSOPER系统权限的用户，且数据库使用密码文件来对管理员进行验证

当用SYSDBA系统权限连接数据库时，其所处的方案schema由SYS用户拥有。用SYSOPER权限连接时，将处于public schema.   SYSOPER是SYSDBA权限的子集

实例的启动

当数据库要启动实例时，将执行以下步骤：

1.在默认的指定位置寻找server parameter file（spfile），如果没有，则寻找initialization parameter file（pfile）

2.读取参数文件的值

3.根据初始化参数文件的设置分配SGA

4.启动数据库后台进程background processes

5.打开警报日志文件(alert log)，跟踪文件(trace files)，并将参数设置按语法写入alert log

在以上过程中，数据库并不与实例关联。即：数据库NOMOUNT状态。可执行DB的创建（creation）和某些备份和恢复操作

数据库Mounted的过程

The instance mounts a database to associate the database with this instance.实例mount数据库就是为了让数据库与该实例管理。要mount数据库，实例从参数文件中CONTROL_FILES初始化参数(CONTROL_FILES initialization parameter)获得到控制文件的名字并打开控制文件（control files）。然后在opening DB阶段利用控制文件找到并打开重做日志文件和数据文件并使数据库能够访问

在mounted database阶段，数据库（database）是关闭的，并且只有数据库管理员DBA能够访问。DBA在此阶段只能做一些DB维护操作，普通的操作是不适用的

如果数据库想要允许多个instance装载此数据库，注意CLUSTER_DATABASE参数的值是否设置成ture

数据库opened的过程

Opening a mounted database makes it available for normal database operations.此阶段使每个合法的用户都能连接数据库进行操作

在open数据库时，DB会执行以下操作：

·打开表空间（除了undo tablespaces）的数据文件（根据上次关闭时的状态offline or online不变，）

·打开联机重做日志文件

·Acquires an undo tablespace获得一个undo 表空间

   会根据UNDO_TABLESPACE initialization parameter指定一个或多个undo表空间

 

注意Read-Only Mode只读模式

默认的是read/write mode打开数据库.

只读模式可以改变数据库状态或执行恢复而不产生重做信息（redo），在只读模式下打开可以执行的操作是：

·data files数据文件可以设置offline或online，但是permanent tablespace永久表空间状态不能offline

·offline的数据文件和表空间可以进行恢复操作（be recovered）

·控制文件可以用于记录数据库状态变化（The control file remains available for updates about the state of the database.）

·创建的临时表空间是可读写的（Temporary tablespaces created with the CREATE TEMPORARY TABLESPACE statement are read/write.）

·Writes to operating system audit trails, trace files, and alert logs can continue.审计文件，跟踪文件和警报日志文件仍旧可写

Database File Checks任何一个日志文件或重做日志文件不存在或无法恢复一致性，DB会报错（returns an error），可能需要介质恢复

 

Shutdown

当执行维护或者其他管理员任务时，就有可能需要手动的shutdown数据库

can use the SQL*Plus SHUTDOWN command or Enterprise Manager to perform. these steps.

当一个打开的（open）数据库关闭时，数据库会自动执行以下操作：

1.database closed

  此时DB is mounted，但是online data files and redo log files are closed

2. Database unmounted

实例还启动着，但是不再与控制文件相关联（The instance is started, but is no longer associated with the control file of the database.）

3. Database instance shut down    实例关闭

 

Shutdown Modes

有4种模式，只有SYSDBA和SYSOPER权限的用户才能shutdown DB

Abort，immediate，transactional，normal

Shutdown abort最快，但是后来的open DB会花点时间，因为为了保证数据文件的一致性必须进行实例恢复（instance recovery）

其余3中关闭方式不需要进行实例恢复

Normal为默认的关闭数据库方式

Database is Closed过程

除了abort，其余3中关闭方式都为正常关闭方式，遵循shutdown步骤执行。而abort为Abnormal Shutdown（异常关闭），If a SHUTDOWN ABORT or abnormal termination occurs, then the instance of an open database closes and shuts down the database instantaneously. Oracle Database does not write data in the buffers of the SGA to the data files and redo log files.

Database Is Unmounted过程

After a database is unmounted, Oracle Database closes the control files of the database. At this point, the instance remains in memory.在数据库unmounted之后，控制文件会被关闭。但是在设施，实例仍在内存中被保留

Instance Is Shut Down过程

the SGA is removed from memory and the background processes are terminated.

注意：有些特别的情况下关闭实例不会完全成功，可能会有些内存会进程没有被关闭，这样会导致之后打开数据库实例的操作失败。此时可以采用shutdown abort命令或EM强行关闭数据库

 

 

Overview of Checkpoints

检查点是对数据库关闭，实例恢复和数据库操作的一致性的重要途径(A checkpoint is a crucial mechanism in consistent database shutdowns, instance recovery, and Oracle Database operation generally)

The checkpoint position acts as a pointer to the redo stream

is stored in the control file and in each data file header.

检查点的作用：

·Reduce the time required for recovery in case of an instance or media failure

·Ensure that dirty buffers in the buffer cache are written to disk regularly

·Ensure that all committed data is written to disk during a consistent shutdown

什么时候启动检查点（When Oracle Database Initiates Checkpoints）

The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file.

很多情况都需要检查点checkpiont，比如：

·Thread checkpoints

The set of thread checkpoints on all instances in a database is a database checkpoint

   Thread checkpoints occur in the following situations:

– Consistent database shutdown

– ALTER SYSTEM CHECKPOINT statement

– Online redo log switch

– ALTER DATABASE BEGIN BACKUP statement

·Tablespace and data file checkpoints

A tablespace checkpoint is a set of data file checkpoints, one for each data file in the tablespace. These checkpoints occur in a variety of situations, including making a tablespace read-only or taking it offline normal, shrinking a data file, or executing ALTER TABLESPACE BEGIN BACKUP.

·Incremental checkpoints

DBWn checks at least every three seconds to determine whether it has work to do

·Other types of checkpoints include instance and media recovery checkpoints and checkpoints when schema objects are dropped or truncated.

 

Overview of Instance Recovery

实例恢复是指根据联机重做日志文件（online redo log）的内容，从最近的检查点开始对数据文件（data files）进行重建修复其变化（reconstruct）。当之前的关闭不一致时，重新打开数据库时，实例恢复会自动进行。

Purpose of Instance Recovery：目的

确保数据库是一致的

发生数据库不一致inconsistent状态的原因。（an instance of an open database fails，a SHUTDOWN ABORT statement or abnormal termination）

注意：

Instance recovery uses only online redo log files and current online data files to synchronize the data files and ensure that they are consistent.

何时执行实例恢复（When Oracle Database Performs Instance Recovery）

· The database opens for the first time after the failure of a single-instance database or all instances of an Oracle RAC database. his form. of instance recovery is also called crash recovery.

· Some but not all instances of an Oracle RAC database fail.

The SMON background process performs instance recovery, applying online redo automatically.

实例恢复由SMON进程执行，同时会自动的提供联机重做日志文件

Importance of Checkpoints for Instance Recovery实例恢复是检查点的重要性

Instance recovery uses checkpoints to determine which changes must be applied to the data files.

only changes with SCNs lower than the checkpoint position are guaranteed to be on disk.（The checkpoint position guarantees that every committed change with an SCN lower than the checkpoint SCN is saved to the data files.）

 

Instance Recovery Phases实例恢复阶段（的过程）

The first phase of instance recovery is called cache recovery or rolling forward。After the roll forward, any changes that were not committed must be undone. rolling back or transaction recovery

 

Overview of Parameter Files

要启动数据库实例，oracle DB必须读取服务器参数文件（server parameter file，spfile，推荐的）或文本初始化参数文件（text initialization parameter file，pfile，以前使用的）

Initialization Parameters

初始化参数是个关系实例的正常操作的配置参数

Oracle数据库提供多个初始化参数可以根据不同的环境来选择优化它的操作

大多数初始化参数的功能：

·Parameters that name entities such as files or directories

·Parameters that set limits for a process, database resource, or the database itself

·Parameters that affect capacity, such as the size of the SGA (these parameters are called variable parameters)

Basic and Advanced Initialization Parameters

In most cases, you must set and tune only the approximately 30 basic parameters to obtain reasonable performance. (The basic parameters set characteristics such as the database name, locations of the control files, database block size, and undo tablespace.)10g中为32 个基本参数

In rare situations, modification to the advanced parameters may be required for optimal performance.

 

Server Parameter Files服务器参数文件

A server parameter file is a repository for initialization parameters that is managed by Oracle Database.服务器参数文件是储存oracle数据库管理的初始化参数的文件

注意Spfile的关键点：

1.Only one server parameter file exists for a database. This file must reside on the database host.

   一个数据库只能有一个spfile，且文件需要在数据库的主机内

2.The server parameter file is written to and read by only by Oracle Database, not by client applications.   只能通过oracle数据库才能读写服务器参数文件spfile。

3.The server parameter file is binary and cannot be modified by a text editor.

是二进制文件，不能用文本编辑器修改

4.Initialization parameters stored in the server parameter file are persistent. Any changes made to the parameters while a database instance is running can persist across instance shutdown and startup.  此文件驻留在执行 Oracle 数据库的服务器中，而且永远存在，不受数据库关闭或启动的影响

A server parameter file is initially built from a text initialization parameter file

using the CREATE SPFILE statement.

 

Text Initialization Parameter Files文本初始化参数文件

A text initialization parameter file is a text file that contains a list of initialization parameters.

Pfile的关键点：

1.When starting up or shutting down a database, the text initialization parameter file must reside on the same host as the client application that connects to the database. （与spfile的区别，客户端存在pfile也可以启动远程的数据库。如图13-7）

2.A text initialization parameter file is text-based, not binary.

3.Oracle Database can read but not write to the text initialization parameter file. To change the parameter values you must manually alter the file with a text editor.

4.Changes to initialization parameter values by ALTER SYSTEM are only in effect for the current instance. You must manually update the text initialization parameter file and restart the instance for the changes to be known. 修改pfile必须重启实例才能生效

 

Modification of Initialization Parameter Values修改初始化参数文件的值

Static parameters include DB_BLOCK_SIZE, DB_NAME, and COMPATIBLE. Dynamic

parameters are grouped into session-level parameters, which affect only the current user session, and system-level parameters, which affect the database and all sessions.

The scope of a parameter change depends on when the change takes effect. When an instance has been started with a server parameter file, you can use the ALTER SYSTEM SET statement to change values for system-level parameters as follows:（当用spfile启动实例时，可以用ALTER SYSTEM SET改变系统级别的参数的值）

·SCOPE=MEMORY   只有实例会变化，但是重启后所做的改变不存在

·SCOPE=SPFILE     所做的变更写入spfile中，但不影响现在的instance，重启后生效

·SCOPE=BOTH      现在的实例会变化，同时写入spfile，重启后仍有效。默认的

数据库会把初始化参数的新值和旧值都写入警报日志文件alert log里

 

Overview of Diagnostic Files诊断文件

Oracle DB为了预防、诊断和解决数据库问题而包括了一个错误诊断的结构的内容

Automatic Diagnostic Repository

stores database diagnostic data such as trace files, the alert log, and Health Monitor reports.

The ADR base is the ADR root directory.

 

Alert Log

每个数据库都有一个alert log

Alert log内容包括以下：

·All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock

errors (ORA-60)

·Administrative operations such as DDL statements and the SQL*Plus commands

STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER

·Several messages and errors relating to the functions of shared server and

dispatcher processes

·Errors during the automatic refresh of a materialized view

 

Trace Files

A trace file is an administrative file that contain diagnostic data used to investigate problems.

The SQL trace facility also creates trace files. must execute the appropriate procedures in the DBMS_MONITOR package or use Oracle Enterprise Manager.

A dump is a special type of trace file.

Trace file names are platform-dependent and use the extension  .trc

Typically, database background process trace file names contain the Oracle SID, the background process name, and the operating system process number.

例如：mytest_ora_10304.trc.

Sometimes trace files have corresponding trace map (.trm) files.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24005010/viewspace-684376/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24005010/viewspace-684376/