Oracle学习基本知识点总结

 以下是我总结的OCP教程的知识点，以备参考之用！

 

1, What’s Oracle Server?

·         It’s a database management system that provides an open, comprehensive, integrated approach to information management.

·         Consists of an Oracle instance and an Oracle database

2,What is Oracle Flashback Technology?

·         Oracle Flashback Query.

·         Oracle Flashback Versions Query.

·         Oracle Flashback Transaction Query.

·         Oracle Flashback Table.

·         Oracle Flashback Drop.

·         Oracle Flashback Database.

·         Oracle Flashback Restore Points.

3,Oracle Enterprise Manager Grid Control (OEMGC) is a HTML-based user interface supporting the administrator. Includes:

·         Oracle Management Service (OMS).

·         Oracle Management Agents.

·         Oracle Management Repository.

4,Oracle Database 10g with RAC(Real Application Cluster)

·         Multiple Oracle instances running on multiple computer

·         Oracle Clusterware

·         Shared-everything database

5,Oracle Database 10g with Data Guard

·         Production (primary) database

·         Standby database: a copy of production database

·         Physical standby database

·         Logical standby database

·         Switch role of databases in case of failure

6, The difference between physical and logical standby database.

·         Datafiles

·         Oracle redo logs

·         Physical standby database:directly applies redo to its datafiles

·         Logical standby database: convert DML and DDL operations to SQL statements

7, Overview of Oracle Primary Components

 

8,Oracle Instance

·         Is a means to access an Oracle database

·         Always opens one and only one database

·         Consists of memory(SGA) and background process

9,Connect to an Oracle instance

·         Establishing a user connection

·         Creating a session (Through Server Process)

10,Oracle Database

Physical Structure:

·         Mainly: Consists of three file types: Data files, Redo log files, Control files

·         Others: Parameter file, Password file, Archived log files

Memory Structure:

Oracle’s memory structure consists of two memory areas known as：

·         System Global area (SGA) ：Allocated at instance startup, and is a fundamental component of Oracle instance

·         Program Global Area (PGA) ：Allocated when the server process is started

 

11,SGA:System Global Area

The SGA consists of serveral memory structures：

·         Shared Pool

·         Database Buffer Cache

·         Redo Log Buffer

·         Other structures（for example, lock and latch management, statistical data）

There are two additional memory structures that can be configured within the SGA：

·         Large Pool

·         Java Pool

·         Stream Pool (10G)

When the size of SGA is less than 128 ,the granule is 4M, otherwise the granule is 16M. SGA_MAX_SIZE controls the size of SGA.

 

12,Shared Pool

Used to store：

·         Most recently executed SQL statements

·         Most recently used data definitions

It consists of two key performance-related memories:  Library Cache and Data Dictionary Cache Sized by the parameter: SHARED_POOL_SIZE

How to change the size: ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

 

13,Library Cache(库高速缓存 )

?       Stores information about the most recently used SQL and PLSQL statements

?       Enables the sharing of commonly used statements

?       Is managed by a least recently used (LRU) algorithm

?       Consists of two structures：

–      Shared SQL area

–      Shared PL/SQL area

?       Size determined by the Shared Pool sizing

 

 

14,Data Dictionary Cache(数据字典高速缓存 )

?       A collection of most recently used definitions in database

?       Includes information about database files, tables,indexes, columns, users, privileges, and other database objects

?       During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access

?       Caching  data dictionary information into memory improves response time on queries and DML

?       Size determined by the Shared Pool sizing

 

15,Database Buffer Cache(数据库缓冲区高速缓存 )

?       Stores copies of data blocks that have been retrieved from the data files

?       Enables great performance gains when you obtain and update data

?       Managed through an LRU algorithm

?       DB_BLOCK_SIZE determines primary block size

如果未在数据库缓冲区高速缓存中找到这个块，服务器进程就从数据文件读取这个块，并在数据库缓冲区高速缓存中放置一个副本.

If the data blocks are not found in the Database Buffer Cache, The server processes are responsible to read the needed blocks from the data files, and then save a copy of them in the Database Buffer Cache for the future used.

缓冲区的dirty data size由参数 FAST_START_MTTR_TARGET 决定的（如果已指定）。缺省值为零。

 

?Consists of independent subcaches：

–DB_CACHE_SIZE

–DB_KEEP_CACHE_SIZE

–DB_RECYCLE_CACHE_SIZE

?Can be dynamically resized

?ALTER SYSTEM SET DB_CACHE_SIZE = 96M;

?DB_CACHE_ADVICE set to gather statistics for predicting different cache size behavior

?Statistics displayed by V$DB_CACHE_ADVICE

–DB_CACHE_SIZE：只调整缺省缓冲区高速缓存的大小，这个参数始终存在且不能设置为零 ?????

–DB_KEEP_CACHE_SIZE：调整保留缓冲区高速缓存的大小，用于保留内存中很可能会重新使用的块

–DB_RECYCLE_CACHE_SIZE：调整循环缓冲区高速缓存的大小，用于删除内存中重新使用的可能性很小的块

 

16,Redo Log Buffer

·         Records all changes made to the database data blocks

·         Primary purpose is recovery

·         Changes recorded within are called redo entries

·         Redo entries contain information to reconstruct or redo changes

·         Sized defined by LOG_BUFFER

 

17,Large Pool

·         An optional area of memory in the SGA

·         Relieves the burden placed on the Shared Pool

·         Used for：

Session memory (UGA) for the Shared Server

I/O Server Processes

Backup and restore operation or RMAN

Parallel execution message buffers

PARALLEL_AUTOMATIC_TUNING 设置为 TRUE

·         Does not use an LRU list

·         Sized by  LARGE_POOL_SIZE

·         Can be dynamically resized

·         备份和恢复：

如果设置了 BACKUP_DISK_IO= n 和 BACKUP_TAPE_IO_SLAVE = TRUE 参数，则恢复管理器 (RMAN) 将使用大型共享池。如果大型共享池已经配置，但不够大，则在大型共享池中分配内存就会失败。RMAN 将错误消息写入警报日志文件，而且不使用 I/O 操作进行备份或恢复操作。

·         并行执行：

如果将 PARALLEL_AUTOMATIC_TUNING 设置为 TRUE，将使用大型共享池。否则，将把这些缓冲区分配至共享池。

 

18,Java Pool

·         An optional area of memory in the SGA

·         Services parsing requirements for Java commands

·         Requied if installing and using java

·         Sized by JAVA_POOL_SIZE parameter

19,PGA: Program Global Area

·         Memory reserved for each user process connecting to an Oracle database

·         Allocated when a process is created

·         Deallocated when the process is terminated

·         Used by only one process

 

20,Process Structure

Oracle takes advantage of various types of processes：

·         User Process: Started at the time a database user requests connection to the Oracle Server

·         Server Process: Connects to the Oracle instance and is started when user establishes a session

·         Background processes: Started when an Oracle instance is started

 

21,User Process

·         A program that requests interaction with the Oracle server

·         Must first establish a connection

·         Does not interact directly with Oracle Server

 

22, Server Process (服务器进程)

·         A program that directly interacts with the Oracle Server.

·         Fulfill(执行) calls generated and returns results

·         Can be dedicated server or shared server

·         用户建立连接后，服务器进程便会启动，以处理用户进程的请求。服务器进程可以是专用服务器进程或共享服务器进程。在专用服务器环境中，服务器进程只处理一个用户进程的请求。用户进程断开连接后，服务器进程就会终止。在共享服务器环境中，服务器进程将处理多个用户进程的请求。服务器进程可以通过 Oracle 程序接口 (OPI) 与 Oracle 服务器进行通信。

 

23,Background Process

·         It maintains and enforces relationships between physical and memory structures:

·         Mandatory background processes：DBWn(0-9,a-j) , PMON,CKPT,LGWR,SMON

Optional background processes： ARCn,LMDn, RECO,CJQ0,LMON,Snnn,Dnnn,Pnnn,

LCKn,QMNn

 

RECO：恢复程序

QMNn：高级排队

ARCn：归档程序

LCKn： RAC 锁管理器 – 例程锁

LMON：RAC DLM 监控程序 – 全局锁

LMDn：RAC DLM 监控程序 – 远程锁

CJQ0：协调程序作业队列后台进程

Dnnn：调度程序

Snnn：共享服务器

Pnnn：并行查询从属

 

24,Database Writer (DBWn)

It’s responsible for writing the dirty data from Database Buffer Cache back to Disk Data files. Writes when：

·         Checkpoint occurs

·         Dirty Buffers reach threshold

·         There are no free buffers

·         Timeout occurs

·         Tablespace OFFLINE

·         Tablespace READ ONLY

·         Table DROP OR TRUNCATE

·         Tablespace BEGIN BACKUP

·         RAC/OPS ping request is made

 

25,Log Write (LGWR)

It’s responsible for writing the entry data from Redo Log buffer to Online redo log files.

LGWR writes：

·         At commit

·         When one-third full

·         When there is 1 MB of redo

·         Every three seconds

·         Before DBWn writes

 

26,System Monitor  (SMON)

Responsibilities：

·         Instance recovery:

1,Rolls forward changes in online redo log files

2,Opens database for user access

3,Rolls back uncommited transactions(Data in Undo TBS?)

·         Coalesces free space

·         Deallocates temporary segments

 

27,Process Monitor (PMON)(过程监视器)

Cleans up after failed processes by：

·         Rolling back the transaction

·         Releasing locks

·         Releasing other resource

·         Restarting dead dispatchers

进程失败后，后台进程 PMON 通过下面的方法进行清理：

回退用户的当前事务处理

释放当前保留的所有表锁或行锁

释放用户当前保留的其它资源

重新启动已失效的调度程序

 

 

 

 

28,Checkpoint  (CKPT)

Responsible for：

·         Signaling DBWn at checkpoint

·         Updating datafile headers with checkpoint information

·         Updating control files with checkpoint information

·         由 CKPT 写入的检查点信息包括检查点位置、系统更改号、重做日志中恢复操作的起始位置以及有关日志的信息等等。

·         注：CKPT 并不将数据块写入磁盘，或将重做块写入联机重做日志。

·         每隔三秒，CKPT 进程就会向控制文件存储数据，以标识重做日志文件中恢复操作的起始位置，该操作称作检查点.

 

29,Archiver (ARCn)

·         Optional background process

·         Automatically archives online redo log files when ARCHIVELOG mode is set

·         Preserves the record of all changes made to the database

 

30, Oracle Database Logical Structure

Tablespace <------Segment  <----Extent <---Block

表空间：

·         Oracle 数据库内的数据存储在表空间内。

·         Oracle 数据库可以从逻辑上分组到称为表空间的更小的逻辑空间区。

·         一个表空间在某一时刻只能属于一个数据库。

·         每个表空间由一个或多个称为数据文件的操作系统文件组成。

·         表空间可能包含一个或多个段。

·         表空间可以在数据库运行时联机。

·         除了 SYSTEM 表空间或者有活动还原段的表空间，可将其它表空间置于脱机状态而不会影响数据库运行。

·         表空间可以在可读写和只读状态之间切换。

数据文件（不是逻辑结构）：

·         Oracle 数据库内的每个表空间由一个或者多个称为数据文件的文件组成。这些物理结构与在其上运行 Oracle 服务器的操作系统是一致的。

·         一个数据文件只能属于一个表空间。

·         通过分配指定数量的磁盘空间加上少量的开销，Oracle 服务器创建表空间数据文件。

·         数据文件创建后，数据库管理员可以更改其大小或者指定数据文件应随着表空间内对象的增长而动态增长。

段：

·         段是在表空间中为特定逻辑存储结构分配的空间。

·         表空间可以由一个或多个段组成。

·         段无法跨越表空间；但是段可以跨越属于同一表空间的多个数据文件。

·         每个段由一个或多个区组成。

区：

·         按区向段分配空间。

·         一个或多个区组成一个段。

·         当段创建后，它至少由一个区组成。

·         随着段的增长，需要向该段添加区。

·         DBA 可以手动向段添加区。

·         一个区就是一组连续的 Oracle 块。

·         区无法跨越数据文件，所以，它必须存在于一个数据文件内。

数据块：

Oracle 服务器以 Oracle 块或者数据块为单位来管理数据文件中的存储空间。

·         Oracle 数据库内的数据存储在数据块内，数据块为最精细的粒度等级。

·         Oracle 数据块是 Oracle 服务器能够分配、读或写的最小存储单元。

·         一个数据块对应一个或多个从现有数据文件中分配的操作系统块。

·         每个 Oracle 数据库的标准数据块大小是在创建数据库时由初始化参数 DB_BLOCK_SIZE 指定的。

·         数据块大小应当是操作系统块大小的整数倍以避免不必要的 I/O。

·         数据块大小最大值取决于操作系统。

 

31,Processing SQL statements(处理 SQL 语句 )

处理查询：

?          语法分析：

–        搜索同一语句

–        检查语法、对象名和权限

–        锁定语法分析过程中使用的对象

–        创建和存储执行计划

?          绑定：获取变量值

?          执行：处理语句

?          提取：将结果行返回用户进程

处理 DML 语句：

·         语法分析：与处理查询时的语法分析阶段相同。

·         绑定：与处理查询时的绑定阶段相同。

·         执行：

·         如果数据库缓冲区高速缓存中不存在某些数据块和还原块，服务器进程就从数据文件将它们读入数据库缓冲区高速缓存。

·         服务器进程锁定要进行修改的行。还原块用于存储数据的前像，以便在需要时回退 DML 语句。

·         数据块记录数据的新值。

·         服务器进程将数据的前像记录到回退块中，并更新数据块。这两种更改都    是在数据库缓冲区高速缓存中进行的。数据库缓冲区高速缓存中所有已更改的块都标记为灰数据缓冲区，即与磁盘中相应的块不同的缓冲区。

·         DELETE 或 INSERT 命令的处理使用类似的步骤。DELETE 命令的前像包含已
删除行中的列值，而 INSERT 命令的前像中包含行的位置信息。

处理 DDL 语句：

DDL（数据定义语言）语句的执行与 DML（数据操纵语言）语句和查询的执行不尽相同，因为成功执行 DDL 语句需要对数据字典具有写权限。对于这些语句，语法分析阶段实际上包括分析、数据字典查找和执行。事务处理管理 SQL 语句、会话管理 SQL 语句和系统管理 SQL 语句在语法分析和执行阶段处理。要重新执行这些语句，再次进入执行阶段即可。

 

32，Oracle Enterprise Manager — Architecture

 

Oracle Enterprise Manager 体系结构

Oracle Enterprise Manager 使用三层体系结构，其中包括：

第一层：控制台客户机和集成工具，为管理员提供图形界面。

第二层：Oracle Management Servers 和数据库资料档案库，为处理系统管理任务提供可伸缩的中层结构。

第三层：安装在每个节点上的智能代理，监视 Oracle Enterprise Manager 服务并执行 Management Server 上的任务。

 

33，The sequence of Oracle Database

SpfileSID.ora ---Control file---Data files

Nomount---mount---open

 

34,PFILE - initSID.ora

·         Text file

·         Modified with an operating system editor

·         Modifications made manually

·         Changes take effect on the next start up

·         Only opened during instance start up

·         Default locations is $ORACLE_HOME/dbs

 

35,PFILE format

·         以这样的格式指定值：keyword=value（关键字 = 值）。

·         服务器为每个参数都设置了缺省值。根据参数的不同，缺省值可能与操作系统相关。

·         可以按任意顺序指定参数，但也存在例外。

·         注释行以 # 符号开头。

·         参数中如果包括字符文字，可将参数用双引号括起。

·         可以使用关键字 IFILE 使参数中包括其它文件。

·         如果使用的操作系统区分大小写，那么文件名也区分大小写。

·         如果有多个值，应该用圆括号将它们括起来，用逗号隔开。

注：请为参数的列出顺序指定一个标准：按字母顺序列出或按功能进行分组。PFILE 根据例程的不同而变化，不一定与上例相同。

 

36,SPFILE(spfileSID.ora)

·         Binary file

·         Maintained by the Oracle Server

·         Always resides on the server side

·         Ability to make changes persistent across shutdown and startup

·         Can self-tune parameter values

·         Can have Recovery Manager support backing up to the initialization parameter file

 

37,Startup Database phase

Nomount :

·         仅在创建数据库或重新创建控制文件过程中，例程才会在 NOMOUNT 阶段启动。

·         启动例程包括下列任务：

·         按以下顺序从 $ORACLE_HOME/dbs 读取初始化文件：

·         首先读取 spfileSID.ora

·         如果找不到，则读取 spfile.ora

·         如果仍然找不到，则读取 initSID.ora

·         使用 STARTUP 指定 PFILE 参数以覆盖缺省行为。

·         分配 SGA

·         启动后台进程

·         打开 alertSID.log 文件和跟踪文件

Mount:

若要执行特定的维护操作，可启动例程并加载数据库，但不要打开数据库。

例如，在以下任务中必须加载数据库但不要打开数据库：

·         重命名数据文件

·         启用和禁用重做日志归档选项

·         执行完全数据库恢复

加载数据库包括以下任务：

·         使数据库与以前启动的例程关联

·         定位并打开参数文件中指定的控制文件

·         读取控制文件以获取数据文件和重做日志文件的名称和状态。但是，在此时
不进行数据文件和联机重做日志文件是否存在的检查。

Open:

正常的数据库操作指启动例程、加载数据库和打开数据库。通过正常的数据库操作，任何有效用户都可以连接到数据库并执行一般的数据访问操作。

打开数据库包括以下任务：

打开联机数据文件

打开联机重做日志文件

如果在尝试打开数据库时有任何数据文件或联机重做日志文件不存在，Oracle 服务器将返回错误消息。

在这个最后阶段中，Oracle 服务器验证所有数据文件和联机重做日志文件是否可以打开，并检查数据库的一致性。如果需要，系统监视 (SMON) 后台进程将启动例程恢复操作。

 

38,Shutdown

Abort  Immediate Transactional Normal

 

39,Diagnostic Files

·         Alert_SID.log file

Background trace files[sid_processname_PID.trc (db01_lgwr_23845.trc)]

·         User trace files

alert_SID.log 的存储位置由 BACKGROUND_DUMP_DEST 初始化参数定义。

 

 

40,User Trace Files

·         User trace files

                                Produced by the user process

                                Can be generated by a server process

                                Contain statistics for traced SQL statements

                                Contain user error messages

·         Created when a user encounters user sessions errors

·         Location is defined by USER_DUMP_DEST

·         Size defined by MAX_DUMP_FILE_SIZE

·         用户跟踪文件包含跟踪的 SQL 语句的统计信息，这对于SQL 优化非常有用。此外，用户跟踪文件还包含用户错误消息。

·         用户跟踪文件的命名约定：sid_ora_PID.trc(db01_ora_23845.trc)。

·         其存储位置由 USER_DUMP_DEST 初始化参数定义。

41, Enable/Disable User Tracing

Session level：

Using the ALTER SESSION command：

ALTER SESSION SET SQL_TRACE = TRUE

Executing DBMS procedure:

dbms_system.SET_SQL_TRACE_IN_SESSION

 

Instance level:

Setting the initialization parameter: SQL_TRACE = TRUE

注：在例程级别设置 SQL_TRACE=TRUE 后将生成大量跟踪数据。因此，要谨慎使用此选项。

SQL> SHOW PARAMETER SQL_TRACE

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sql_trace                            boolean     FALSE

 

42,Authentication Methods

OS: sqlplus / as sysdba

Password parameter file is used for remote log on database.

cd $ORACLE_HOME/dbs

orapwd file=orapwSID password=XXXXXX entries=5 force=y;

sqlplus sys/oracle@test as sysdba   (need to start listener)

 

43,Password File Authentication

·         Create the password file using the password utility.

·         orapwd file=orapwSID password=XXXXXX entries=5 force=y;

·         Set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in initialization parameter file

·         Add users to the password file

·         Assign appropriate privileges to each user.

·         GRANT SYSDBA TO HR;

其中entries的值表示，可以有多少个用户可以拥有sysdba or sysoper权限，数据不一定准确的。SQL> select * from v$pwfile_users; 通过它可以查看。

给用户授与sysdba权限的过程就是把密码从数据字典复制到了密码文件中的过程。

 

44，Install Oracle must pre-specify OS Environment

ORACLE_BASE  eg:/u01/app/oracle

ORACLE_HOME   eg:$ORACLE_BASE/product/release(10.2.0)

ORACLE_SID

PATH  定义操作系统查找可执行程序时要搜索的目录

LD_LIBRARY_PATH  定义所需的库文件的存储目录

 

45,Creating a DB Manually

·         Choose a unique instance and database name.

·         Choose a database character set

·         Set operating system variables

·         Create the initialization parameter file

·         Create Server Parameter file

·         Start the instance in NOMOUNT stage

·         Create and execute CREATE DATABASE command

·         Open database (create database do it automatically)

·         Run scripts to generate the data dictionary and accomplish post-creation steps

·         Create addional tablespaces as need

 

46,Choose a Database Administrator Authentication Method

You must be authenticated and granted appropriate system privileges in order to create a database. You can authenticate as an administrator with the required privileges in the following ways:

a,With a password file

b,With operating system authentication

 

In this step, you decide on an authentication method.If you decide to authenticate with a password file, create the password file as described in "Creating and Maintaining a Password File" on page 1-21.If you decide to authenticate with operating system user group. On the UNIX and Linux plateforms for example this is typically the dba user group. On the Windows platform the user installing the Oracle software is automatically placed in the required user group.

 

47,4:Create the Initialization Parameter File

shared_pool_size=80M

sga_max_size=280M

启动的时候shared_pool_size 需要设置的大一点一般要有80MB，可以吧SGA_TARGET设置到280MB，不然直接跑 create database PROD 会导致

Tue Aug 16 01:54:01 2005

Errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\prod_ora_1408.trc:

ORA-01501: CREATE DATABASE ??

ORA-01519: ????? ‘%ORACLE_HOME%\RDBMS\ADMIN\SQL.BSQ‘ ???? 1413 ???

ORA-00604: ?? SQL ?? 1 ????

ORA-04031: ???? 2208 ??????? ("shared pool","unknown object","KGLS heap","KGLS MEM BLOCK")

导致sql.bsq无法顺利建立完毕，也就是数据字典表没办法建立完毕，最终导致连control file 都mount不上

 

 

48,Create a Server Parameter File

SQL>CREATE SPFILE FROM PFILE;

Tip: The database must be restarted before the server parameter file takes effect.

 

49,Data Dictionary

·         Central to every Oracle database

·         Describe the database and its objects

·         Contains read-only tables and views

·         Stored in the SYSTEM tablespace

·         Owned by the user SYS

·         Maintained by the Oracle Server

·         Accessed with SELECT

只要执行数据定义语言 (DDL) 命令，Oracle 服务器就会更新数据字典。

The data dictionary contains two parts:

Base tables :

a,Stores description of the database

b,Created with CREATE DATABASE

Data dictionary views:

a,Used to simplify the base table information

b,Accessed through public synonyms

c,Created with the catalog.sql script

 

50,Create Data Dictionary

Location: UNIX/Linux：$ORACLE_HOME/rdbms/admin

 

51,Data Dictionary Contents

The data dictionary provides information about:

·         Logical and physical database structures

·         Definitions and space allocations of objects

·         Integrity constraints

·         Users

·         Roles

·         Privileges

·         Auditing

 

52,How the DD Is Used

Primary uses:

·         Oracle server uses it to find information about

                a,Users

                b,Schema objects

                c,Storage structures

·         Oracle server modifies it when a DDL statement is executed.

·         Users and DBAs use it as a read-only reference for information about the database.

 

53,DD(Data Dictionary) View Categories

Three sets of static views

Distinguished by their scope:

·         DBA:What is in all the schemas

·         ALL:What the user can access

·         USER:What is in the user‘s schema

 

54,Dynamic Performance Tables

·         Virtual tables

·         Record current database activity

·         Continually updated while the database is operational

·         Information is accessed from memory and control file, spfile

·         Used to monitor and tune the database

·         Owned by SYS user

·         Synonyms begin with V$

·         Listed in V$FIXED_TABLE

SQL> SELECT * FROM V$FIXED_TABLE WHERE NAME=‘V$INSTANCE‘;

 

55,Admin Script Naming

 

56,Control File

·         A small binary file

·         Defines current state of physical database

·         Maintains integrity of database

·         Required:

                a,At MOUNT state during database startup

                b,To operate the database

·         Linked to a single database

·         Size initially by CREATE DATABASE

·         Loss may require recovery

 

57,The Size of Control File

The main determinants of the size of a control file are the values set for the MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES parameters in the CREATE DATABASE statement that created the associated database. Increasing the values of these parameters increases the size of a control file of the associated database.

 

58,Control File Contents

A control file contains the following entries:

·         Database name and identifier

·         Time stamp of database creation

·         Tablespace names

·         Names and locations of data files and online redo log files

·         Current online redo log file sequence number

·         Checkpoint information

·         Begin and end of undo segments

·         Redo log archive information

·         Backup information

 

59, Tom says about control files

The control file is a fairly small file (it can grow up to 64MB or so in extreme cases) that contains a directory of the other files Oracle needs. The parameter file tells the instance where the control files are, and the control files tell the instance where the database and online redo log files are.

The control files also tell Oracle other things, such as information about checkpoints that

have taken place, the name of the database (which should match the DB_NAME parameter), the timestamp of the database as it was created, an archive redo log history (this can make a control file large in some cases), RMAN information, and so on.

Control files should be multiplexed either by hardware (RAID) or by Oracle when RAID or

mirroring is not available. More than one copy of them should exist, and they should be stored on separate disks, to avoid losing them in the event you have a disk failure. It is not fatal to lose your control files—it just makes recovery that much harder.

Control files are something a developer will probably never have to actually deal with.

To a DBA they are an important part of the database, but to a software developer they are not

extremely relevant.

 

 

60,Multiplexing the Control File

1, Place on different disks, can up to eight copies.

 

61, The behavior of mutiplexed control files is:

·         Write to all filenames listed by control_files parameter

·         Only read the first file listed by control_files parameter

·         If any of the control files become unavailable during operation, instance becomes Inoperable and should be aborted.

 

62, Using SPFILE to multiplex controlfile

1,alter SPFILE：

SQL>ALTER SYSTEM SET control_files=‘$ORCLE_HOME/oradata/u01/ctrl01.ctl‘,

‘$ORCLE_HOME/oradata/u02/ctrl02.ctl‘ SCOPE=SPFILE;

2,Shutdown database：

SQL>shutdown immediate

3,Create additional control files：

$cp $ORCLE_HOME/oradata/u01/ctrl01.ctl $ORCLE_HOME/oradata/u02/ctrl02.ctl

4,Start the database：

SQL>startup

 

63,Get Control File Information

Information about control file status and locations can be retrieved by querying the following:

·         V$CONTROLFILE: List the name and status of all control files associated with the instance

·         V$PARAMETER: Lists status and location of all parameters

·         V$CONTROLFILE_RECORD_SECTION: Provides information about the control file record sections

·         SHOW PARAMETER CONTROL_FILES: Lists the name, status, and location of the control files

SQL> SELECT type, record_size, records_total, records_used FROM v$controlfile_record_section WHERE TYPE=‘DATAFILE‘;

 

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED

---------------------------- ----------- ------------- ------------

DATAFILE                             428           100            8

此例表示数据文件的最大数为 100，这个数字由 CREATE DATABASE 命令中的 MAXDATAFILES 参数确定。

 

64，Features of redo log files：

·         Record all changes made to data

·         Apply recovery mechanism

·         Can be organized into groups

·         At least two groups required

SQL> show parameter log_buffer

All the changes are put into the log buffer. LGWR will write the entries from log buffer to online redo log.  The data will be written to online redo log when trigger commit operation.

So actually, no data will be lost.

 

65, The structure of redo log files

·         A set of identical copies of online redo log files is called an online redo log file group.

·         The LGWR background process concurrently writes the same information to all online redo log files in a group.

·         The Oracle server needs a minimun of two online redo log file groups for the normal operation of a database.

 

66,How Redo File Work

·         Online Redo log files are used in a cyclic fashion.

·         When a online redo log file is full, LGWR will move to the next log group.

                a, Called a log switch

                b, Checkpoint operation also occurs

                c, Information written to the control file

·         The act of switching from one log file group to the other is called a log switch.

·         A checkpoint is the writing of dirty (modified) blocks from the buffer cache to disk.

 

67,LS & LSN

LS:Log Switch

LSN: Log Sequence Number

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM CHECKPOINT;

FAST_START_MTTR_TARGET = 600;  600秒必须做一次CKPT

 

68，Adding Redo Files Groups

You can query the v$logfile and v$log to specify the number of redo log groups and the size of each member.

SQL>alter database add logfile group 4 ‘/u01/app/oracle/oradata/test/redo04.log’ size 50M;

 

69, Adding Redo File Members

alter database add logfile member

‘/u01/app/oracle/oradata/test/redo01a.log‘ to group 1,

‘/u01/app/oracle/oradata/test/redo02a.log‘ to group 2,

‘/u01/app/oracle/oradata/test/redo03a.log‘ to group 3,

‘/u01/app/oracle/oradata/test/redo04a.log‘ to group 4;

 

 

70,Drop Redo File Group

SQL> alter database drop logfile group 4;

限制：

一个例程至少需要两组联机重做日志文件。

无法删除活动组或者当前组。

删除联机重做日志文件组时并不删除操作系统文件。

 

71,Drop Redo Log File Member

SQL>al