以下是我总结的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>alter database drop logfile member ‘/u01/app/oracle/oradata/test/redo03a.log‘;
限制：
l 如果要删除的是组内的最后一个有效成员，那么您不能删除该成员。
l 如果该组是当前组，那么必须先强制执行日志文件切换，然后才能删除该成员。
l 如果数据库正运行在 ARCHIVELOG 模式下并且未将该成员所属日志文件组归档，那么您无法删除该成员。
l 删除联机重做日志文件成员时，如果没有使用 OMF 功能，则不会删除操作系统文件。
72,Relocate & Rename
Relocate or rename online redo log files in one of the two following ways:
1,ALTER DATABASE CLEAR LOGFILE group n;
a,Shut down the database
b,Copy the online redo log files to the new location.
c,Place the database in MOUNT mode.
e,Execute the command:
ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/PROD/redo01.log‘ to ‘/u01/app/oracle/oradata/PROD/redo011.log‘;
f,Open database for normal operation.
SQL>alter database open;
2,Add new members and drop old members
alter database add logfile group 4 ‘/u01/app/oracle/oradata/PROD/redo01.log‘ size 50M;
SQL> alter database drop logfile group 1;
73,Redo File Configuration
Put the online redo log file and archived log file on the different disks.
74,Group & Member Information
Information about group and its members can be obtained by querying the following views：
l V$LOG from controlfile
l V$LOGFILE identify redo log group status
l V$LOG_HISTORY obtain log history information
75,在Oracle10g中，log_archive_start参数已经被废弃，只要启动数据库的归档模式，Oracle就会启用自动归档，从而避免了10g以前由于用户疏忽所带来的一系列问题。
76,Tablespaces & Data Files
Oracle stores data logically in tablespaces and physically in data files.
Tablespaces:
l Can belong to only one database at a time
l Consist of one or more data files
l Are further divided into logical units of storage
Data files:
l Can belong to only one tablespace and one database
l Are a repository for schema object data
77,Storage Hierarchy Summary
l A database is mode up of one or more tablespaces.
l A tablespace is mode up of one or more data files. These files might be cooked files in a file system, raw partitions, ASM managed database files, or a file on a clustered file system. A tablespace contains segments.
l A segment (TABLE,INEX, and so on) is made up of one or more extents. A segment exists in a tablespace, but may have data in many data files within that tablespace.
l An extent is a logically contiguous set of blocks on disk. An extent is in a single tablespace and, furthermore,is alwys in a single file within that tablespace.
l A block is the smallest unit of allocation in the database. A block is the smallest unit of I/O used by database.
78,Types of Tablespaces
A, System tablespace
B, non-system tablespace
79,Space Mgmt in Tablespaces(表空间以区为单位分配空间)
Locally managed tablespace:(after 9i version default)
l Free extents are managed in the tablespace.
l Bitmap is used to record free extents.
l Each bit corresponds to a block or group of blocks.
l Bit value indicates free or used.
Dictionary-manage tablespace:
l Free extents are managed by the data dictionary.
l Appropriate tables are upated when extents are allocated or deallocated.
80,Locally Managed Tablespace
l Reduced contention on data dictionary tables
l No undo generated when space allocation or deallocation occurs
l No coalescing required
CREATE TABLESPACE userdata DATAFILE ‘/u01/oradata/userdata01.dbf‘ SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
The default value of extent is 1M.
81,What does Tom say
With a locally-managed tablespace, a bitmap stored in each data file is used to manage the extents. Now to get an extent, all the system needs to do is set a bit to 1 in the bitmap.To free space, the system sets a bit back to 0. Compared to using dictionary-managed tablespaces, this is incredibly fast. We no longer serialize for a long-running operation at the database level for a very fast operation. Locally-managed tablespaces have other nice attributes as well, such as the enforcement of a uniform extent size, but that is starting to get heavily into the role of the DBA.
Going forward, the only storage management method you should be using is a locally-managed tablespace. In fact, in Oracle9i and above, if you create a database using the DBCA, it will create SYSTEM as LMT and if SYSTEM is locally managed, all other tablespaces in that database will be locally managed as well, and the legacy dictionary-managed method will not work. It is not that dictionary-managed tablespaces are not supported in a database where SYSTEM is locally managed, it is that they simply cannot be created.
82,Migrating a DM SYSTEM TS
Migrate a dictionary managed SYSTEM tablespace to locally managed:
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘SYSTEM’);
83,Undo Tablespace
l Used to store undo segments
l Cannot contain any other objects
l Extents are locally managed
l Can only use the DATAFILE and EXTENT MANAGEMENT clauses
CREATE UNDO TABLESPACE undo1 DATAFILE ‘/u01/oradata/undo01.dbf‘ SIZE 40M;
84,Temporary Tablespaces
l Used for sort operations
l Can be shared by multiple users
l Cannot contain any permanent objects
l Locally managed extents recommended
CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/u01/oradata/temp01.dbf‘ SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.
If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage. However, you will receive a warning in ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.
本地管理的临时表空间具有临时数据文件 (Tempfile)，它与普通数据文件很相似，只有
以下几点不同：
? Tempfile 始终设为 NOLOGGING 模式。
? 无法将 Tempfile 设置为只读。
? 无法重命名 Tempfile。
? 无法通过 ALTER DATABASE 命令创建 Tempfile。
? Tempfile 对于只读数据库是必需的。
? 介质恢复不恢复 Tempfile。
若要优化临时表空间内的排序性能，可将 UNIFORM SIZE 设置为 SORT_AREA_SIZE 参数的整数倍。
85,Default Temporary TS
l Specifies a database-wide default temporary tablespace
l Eliminates using SYSTEM tablespace for storing temporary data
l Can be created by using：
a,CREATE DATABASE
b,ALTER DATABASE
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
86,Restrictions of Temp TS
Default temporary tablespace cannot be：
l Dropped until after a new default is made available
l Taken offline
l Altered to a permanent tablespace
87,Read-Only Tablespaces
Use the following command to place a tablespace in read-only mode:
ALTER TABLESPACE userdata READ ONLY;
l Causes a checkpoint
l Data available only for read operations
l Objects can be dropped from tablespace(because drop only update data dictionary)
88.Taking a Tablespace Offline
Not available for data access
1,Tablespaces that cannot be taken offline：
l SYSTEM tablespace
l Tablespaces with active undo segments
2,Default temporary tablespace
To take a tablespace offline：
ALTER TABLESPACE userdata OFFLINE;
To bring a tablespace online：
ALTER TABLESPACE userdata ONLINE;
ALTER TABLESPACE tablespace
{ONLINE |OFFLINE [NORMAL|TEMPORARY|IMMEDIATE|FOR RECOVER]}
其中：
NORMAL：将该表空间中所有数据文件内的所有块从 SGA 中清空。这是缺省设置。在使该表空间重新联机之前，您无须对其执行介质恢复。尽可能使用 NORMAL 子句。
TEMPORARY：对表空间内的所有联机数据文件执行检查点操作，即使某些文件无法写入。所有脱机文件可能都需要进行介质恢复。
IMMEDIATE：不保证表空间文件可用，而且不执行检查点操作。在使表空间重新联机前，您必须对其执行介质恢复操作。
FOR RECOVER：使表空间脱机以进行表空间时间点恢复。
89, Changing Storage Settings
A,Using ALTER TABLESPACE command to change storage settings(Dictionary Management TBS only):
l ALTER TABLESPACE userdata MINIMUM EXTENT 2M;
l ALTER TABLESPACE userdata DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);
B,Storage settings for locally managed tablespaces cannot be altered.
90,Resizing a Tablespace
A tablespace can be resized by:
a,Changing the size of a data file:
l Automatically using AUTOEXTENTD
l Manually using ALTER DATABASE
b,Adding a data file using ALTER TABLESPACE
91,Enable Automatic Extension
a,Can be resized automatically with the following command:
l CREATE DATABASE
l CREATE TABLESPACE
l ALTER TABLESPACE … ADD DATAFILE
b,Example：
l CREATE TABLESPACE user_data DATAFILE ‘/u01/oradata/userdata01.dbf‘ SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
c,Query the DBA_DATA_FILES View to determine whether AUTOEXTEND is enable.
为新数据文件指定 AUTOEXTEND（续）
使用 ALTER DATABASE 命令可修改数据文件并启用自动扩展：
ALTER DATABASE DATAFILE filespec [autoextend_clause]
autoextend_clause:== [ AUTOEXTEND { OFF|ON[NEXT integer[K|M]] [MAXSIZE UNLIMITED | integer[K|M]] } ]
其中：
AUTOEXTEND OFF：禁用数据文件的自动扩展
AUTOEXTEND ON：启用数据文件的自动扩展
NEXT：指定在需要更多区时分配给数据文件的磁盘空间
MAXSIZE：指定允许分配给该数据文件的最大磁盘空间
UNLIMITED：将分配给数据文件的磁盘空间设为不受限
SQL> alter database datafile ‘/u01/app/oracle/oradata/PROD/corrupt01.dbf‘ autoextend on next 10M maxsize 500M;
92, Manually Resizing Data File
l Manually increase or decrease a data file size using ALTER DATABASE
l Resizing a data file adds more space without adding more data files.
l Manual resizing of a data file reclaims unused space in database.
l Example：
ALTER DATABASE DATAFILE ‘/u03/oradata/userdata02.dbf‘ RESIZE 200M;
93,Adding Data Files to a TS
l Increases the space allocated to a tablespace by adding additional data files
l ADD DATAFILE clause is used to add a data file
l Example：
SQL> ALTER TABLESPACE corrupt_tbs ADD DATAFILE ‘/u01/app/oracle/oradata/PROD/corrupt02.dbf‘ SIZE 200M;
94,Method for Moving Data File
1,ALTER TABLESPACE(它仅适用于不含活动还原段或临时段的非 SYSTEM 表空间中的数据文件)
· Tablespace must be offline
· Target data files must exist
The step of renaming data file：
· Make the tablespace offline
Alter tablespace example offline;
· Use OS command copy or move data files
· execute ALTER TABLESPACE RENAME DATAFILE
alter tablespace EXAMPLE rename datafile ‘/u01/app/oracle/oradata/test/example01.dbf’ to ‘/u01/app/oracle/oradata/test/example02.dbf’;
· Make the tablespace online
SQL> recover datafile 5; 需要做RECOVER
Media recovery complete.
SQL> alter tablespace example online;
Tablespace altered.
· Use OS command delete data file if it’s needed
2, ALTER DATABASE
Database must be mounted
Target data file must exist
ALTER DATABASE 命令
ALTER DATABASE 命令可用来移动任意类型的数据文件：
ALTER DATABASE [database]
RENAME FILE ‘filename‘[, ‘filename‘]...
TO ‘filename‘[, ‘filename‘]...
因为 SYSTEM 表空间无法脱机，您必须使用该方法移动 SYSTEM 表空间内的数据文件。
使用如下进程重命名无法脱机的表空间内的文件：
1. 关闭数据库。
2. 使用操作系统命令移动文件。
3. 装载数据库。
4. 执行 ALTER DATABASE RENAME FILE 命令。
SQL> alter database rename file ‘/u01/app/oracle/oradata/test/system01.dbf‘ to ‘/u01/app/oracle/oradata/test/system02.dbf‘;
5. 打开数据库。
95，Dropping Tablespaces
You cannot drop a tablespace if it：
SYSTEM tablespace
Has active segments
You can with the following Optional:
INCLUDING CONTENTS drop the segments
INCLUDING CONTENTS AND DATAFILES deletes data files
CASCADE CONSTRAINTS drops all referential integrity constraints
SQL>drop tablespace example including contents and datafiles cascade constraints;
96，Get Tablespace Information
Tablespace information：DBA_TABLESPACES V$TABLESPACE
Data file information：DBA_DATA_FILES V$DATAFILE
Temp file information：DBA_TEMP_FILES V$TEMPFILE
97，Type of segments
Table segment, Index segment, Undo segment, Lob segment, Cluster segment, temp segment, partition segment.
LOB 段：
表中的一列或者多列可以用来存储大型对象 (LOB)，如文本文档、图像或者视频。如果列很大，Oracle 服务器将把这些值存储在独立的段（称为 “LOB 段”）中。表中只包含一个定位器或者指针，指向对应的 LOB 数据所在的位置。
98,Extent Alloc & Dealloc
An extent is a chunk of space used by segment within a tablespace
An extent is allocated when the segment is:
· Created
· Extented
· Altered
An extent is deallocated when segment is：
· Dropped
· Altered
· Truncated
99,Database Block
· Minimum unit of I/O
· Consists of one or more operating system blocks
· Set at tablespace creation
· DB_BLOCK_SIZE is the default block size
100,Multiple Block Size Support
A database can be created with a standard block size and up to four nonstandard block sizes.
Block sizes can have any power-of-two value between 2KB and 32KB.
101,Standard Block Size
A,Set at database creation using the DB_BLOCK_SIZE parameter;
· cannot be changed without re-creating the database
· Used for SYSTEM and TEMPORARY tablespaces
B,DB_CACHE_SIZE specifies the size of DEFAULT buffer cache for standard block size：
· Minimum = one granule（4 MB or 16 MB）
· default = 48 MB
102,Create Non-std Block Size TS
SQL> CREATE TABLESPACE tbs_1 DATAFILE ‘/u01/app/oracle/oradata/test/tbs_101.dbf‘ SIZE 10M BLOCKSIZE 4K;
CREATE TABLESPACE tbs_1 DATAFILE ‘/u01/app/oracle/oradata/test/tbs_101.dbf‘ SIZE 10M BLOCKSIZE 4K
*
ERROR at line 1:
ORA-29339: tablespace block size 4096 does not match configured block sizes
You need to pre-configure a non-std block size cache as following:
SQL> alter system set db_4k_cache_size=4k;
SQL> CREATE TABLESPACE tbs_1 DATAFILE ‘/u01/app/oracle/oradata/test/tbs_101.dbf‘ SIZE 10M BLOCKSIZE 4K;
SQL> select block_size ,tablespace_name from dba_tablespaces;
103,Multiple Block Size Rules
· All partitions of a partitioned object must reside in tablespaces of the same block size.
· All temporary tablespaces, including the permanent ones that are being used as default temporary tablespaces, must be of standard block size.
· Index-organized table overflow and out-of-line LOB segments can be stored in a tablespace with a block size different from the base table.
104,Database Block Contents
Block Space Util Parameters:
INITRANS
MAXTRANS
PCTFREE (default 10%)
PCTUSED( default 40%)
105,Data Block Management
Two methods are available for managing data blocks：
· Automatic segment-space management
· Manual management
106, The advantages of Automatic segment-space management
A,It is a method of managing free space inside database segments.
B,Tracking in-segment free and used space is done using bitmaps as opposed to using free lists.
This method provides:
· Ease of management
· Better space utilization
· Better performance for concurrent INSERT operations
C,Bitmap segments contain a bitmap that describes the status of each block in the segment D,with respect to its avaiable space.
E,The map is contained in a separate set of blocks referred to as bitmapped blocks(BMBs).
F,When inserting a new row, the server searches the map for a block with sufficient space.
G,As the amount of space available in a block changes, its new state is reflected in the bitmap.
107,Configuring ASSM(Automatic Segment Space Management)
· Automatic segment-space management can be enabled at the tablespace level only, for locally managed tablespaces.
· After a tablespace is created, the specifications apply to all segments created in the tablespace.
Example:
CREATE TABLESPACE data02
DATAFILE ‘/u01/app/oracle/oradata/test/data02.dbf‘ SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;
108,Getting Storage Information
· DBA_EXTENTS
· DBA_SEGMENTS
· DBA_TABLESPACES
· DBA_DATA_FILES
· DBA_FREE_SPACE
· 查询 DBA_SEGMENTS 视图以获得分配给某个段的区和块的数目。
SQL>SELECT segment_name,tablespace_name,extents,blocks FROM dba_segments WHERE owner = ‘HR‘;
· 使用 DBA_EXTENTS 视图以检查给定段的区。
SQL>SELECT extent_id,file_id,block_id,blocks FROM dba_extents WHERE owner=‘HR‘ AND segment_name=‘EMPLOYEES‘;
109,Managing Undo Data
There are two methods for managing undo data：
· Automatic Undo Management
· Manual Undo Management
The term undo was known as rollback in previous versions.
110,Undo Segment：Purpose
Transaction recovery
Read consistency
Transaction rollback
111,Types of Undo Segments(SYSTEM 还原段 ,非SYSTEM 还原段 ,延迟还原段 )
A,SYSTEM: Used for objects in the SYSTEM tablespace
B,Non-SYSTEM：Used for objects in other tablespaces:
· Auto mode: Requires an UNDO tablespace
· Manual mode：
· Private: Acquired by a single instance
· Public:Acquired by any instance
C,Deferred: Used when tablespaces are taken offline immediate, temporary, or for recovery
112,Auto Undo Mgmt: Concepts
· Undo data is managed using an UNDO tablespace.
· You allocate one UNDO tablespace per instance with enought space for the workload of the instance.
· The Oracle server automatically mainstains undo data within the UNDO tablespace.
113,Auto Undo Mgmt: Config
A,Configure two parameter in the initialization file：
· UNDO_MANAGEMENT
· UNDO_TABLESPACE
B,Create at least one UNDO tablespace
UNDO_MANAGEMENT: Specifies wheter the system should use AUTO or MANUAL mode
UNDO_TABLESPACE: Specifies a particular UNDO tablespace to used
They are both need to be specified in PFILE|SPFILE
SQL> ALTER SYSTEM SET undo_tablespace = UNDOTBS;
114,How to create UNDO Tablespace
A, Create the UNDO tablespace with the database by adding a clause in the CREATE DATABASE command:
CREATE DATABASE db01
. . .
UNDO TABLESPACE undo1
DATAFILE ‘/u01/oradata/undoldb01.dbf‘ SIZE 20M
AUTOEXTEND ON
B,create it later by using the CREATE UNDO TABLESPACE command:
SQL> CREATE UNDO TABLESPACE undo1 DATAFILE ‘/u01/app/oracle/oradata/test/undo1db01.dbf‘ SIZE 20M;
Notes:自动还原管理需要一个 UNDO 表空间。数据库中可能有多个 UNDO 表空间，但只能有一个 UNDO 表空间处于活动状态。
115,Switching UNO TS
· You can switch from using one UNDO tablespace to another.
· Only one UNDO tablespace can be in assigned to a database at a time.
· More than one UNDO tablespace may exist within an instance, but only one can be active.
· Use the ALTER SYSTEM command for dynamic switching between UNDO tablespaces.
SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDO1;
116,Dropping an UNDO TS
A,The DROP TABLESPACE command drops an UNDO tablespace.
· DROP TABLESPACE UNDOTBS2;
B,An UNDO tablespace can only be dropped if it is currently not in use by any instance.
C,To drop an active UNDO tablespace:
· Switch to a new UNDO tablespace.
· Drop the tablespace after all current transactions are complete.
您可以在表空间 UNDOTBS 内的所有事务处理都已完成后删除它。要确定是否存在任何一个活动的事务处理，请使用以下查询：
SELECT a.name,b.status FROM v$rollname a, v$rollstat b WHERE a.name IN ( SELECT segment_name FROM dba_segments WHERE tablespace_name = ‘UNDOTBS‘ ) AND a.usn = b.usn;
117,Sizing an UNDO TS
Determining a size for the UNDO tablespace requires three pieces of information:
· (UR) UNDO_RETENTION in seconds
· (UPS) Number of undo data blocks generated per second
· (DBS) Overhead varies based on extent and file size (db_block_size)
· UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)
SQL> SELECT (SUM(undoblks) / SUM((end_time - begin_time) * 86400)) as UPS FROM v$undostat;
SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR
FROM v$parameter WHERE name = ‘undo_retention‘),
(SELECT (SUM(undoblks)/SUM(((end_time -begin_time)*86400))) AS UPS
FROM v$undostat),(SELECT value AS DBS FROM v$parameter WHERE name = ‘db_block_size‘);
为了获得最佳结果，应该在一天中数据库负载最繁重的时候进行计算。
118,Get Undo Segment Info
Information about undo segments can be obtained by querying the following views:
DBA_ROLLBACK_SEGS
Dynamic Performance Views
V$ROLLNAME
V$ROLLSTAT (rollback segment detail)
V$UNDOSTAT (undo data detail made by transaction)
V$SESSION
V$TRANSACTION
SQL>SELECT segment_name,owner,tablespace_name,status FROM dba_rollback_segs;
OWNER 列指定还原段的类型：
SYS：指专用还原段
PUBLIC：指公用还原段
119,ROWID Format
Restricted ROWID: only for Oracle 7 or before version
Because a segment can only reside in one tablespace, by using the data objec number, the Oracle server can determine the tablespace that contains a row.
The relative file number within the tablespace is used to locate the file, the block number is used to locate block containing the row, and the row number is used to locate the row directory for the row.
The row directory entry can be used to locate the beginning of the row.
Thus, ROWID can be used to locate any row within a database
180,Structure of a Row
Row Header--Column length--Column value
Row data is stored in database blocks as variable-length records. Columns for a row are generally stored in the order in which they are defined and any trailing NULL columns are not stored.
Note: A single byte for column length is required for non trailing NULL columns.
Row header: Used to store the number of columns in the row, the chaining information, and the row lock status.
Row data: For each column, the Oracle server stores the column length and value (One byte is needed to store the column length if the column will require more than 250 bytes of storage in which case three bytes will be used for column length. The column value is stored immediately following the column length bytes.Adjacent rows do not need any space between them. Each row in the block has a slot in the row directory. The directory slot points to the beginning of the row.
181,Creating a Table
ASSM: Automatic Segment Space Management (PCTFREE, INITRANS)
MSSM: Manual Segment Space Management (FREELISTS, PCTFREE, PCTUSED, INITRANS)
182, Oracle Temporary Tables
CREATE GLOBAL TEMPORARY TABLE hr.emp_temp ON COMMIT DELETE ROWS AS SELECT * FROM hr.employees;
此时只会创建一个临时表结构，不会有数据信息。
insert into emp_temp select * from employees;
此时数据顺利的插入进emp_temp临时表，是可以select * from emp_temp查看到记录的
commit;
事务提交后此时再select * from emp_temp就看不到记录了
CREATE GLOBAL TEMPORARY TABLE hr.emp_temp ON COMMIT PRESERVE ROWS AS SELECT * FROM hr.employees;
同理：此时只会创建一个临时表结构，不会有数据信息。
insert into emp_temp select * from employees;
此时数据顺利的插入进emp_temp临时表，是可以select * from emp_temp查看到记录的
commit;
事务提交后还是可以看到select * from emp_temp里面有记录了。如果我退出SQL*PLUS重新登录，再select * from emp_temp就没有记录了，这就是会话期间都保存数据信息
· Tables retain data only for the duration of a transaction or session.
· DML locks are not acquired on the data.
· You can create indexes, views, and triggers on temporary tables
183,Row Migration & Chaining
Row Migration: If PCTFREE is set to a low value, there may be insufficinet space in a block to accommodate a row that grows as a result of an update. When this happens, the Oracle server will move the entire row to a new block and leave a pointer from the original block to the new location. This process is referred to as row mirgration. When a row is migrated, I/O performance associated with this row decreases because the Oracle Server must scan two data blocks to retrieve the data.
Row Chaining: Row chaining occurs when a row is too large to fit into any block. This might occur when the row contains columns that are very long. In this case, the Oracle server divides the row into smaller chunks called row pieces. Each row piece is stored in a block along with the necessary pointers to retrieve and assemble the entire row. Row chaining can be minimized by choosing a higher block size or by splitting the table into multiple tables with fewer columns, if possible.
184,Manually Allocating Extents
ALTER TABLE hr.employees ALLOCATE EXTENT(SIZE 500K DATAFILE ‘/u01/app/oracle/oradata/PROD/users01.dbf‘);
185,Nonpartitioned Table Reorg
ALTER TABLE hr.employees MOVE TABLESPACE data1;
When a nonpartitioned table is reorganized, its structure is kept, but not its contents. (ROWID 会变) .
It is used to move a table to a different tablespace or reorganize extents.
A nonpartitioned table can be moved without having to run the Export or Import utility. In addition, it allows the storage parameters to be changed. This is useful when:
Moving a table from one tablespace to another
Reorganizing the table to eliminate row migration
186,Truncating Table
Truncating a table deletes all rows in a table and releases used space.
Corresponding indexes are truncated
187,Dropping a Column
SQL>ALTER TABLE hr.employees DROP COLUMN comments CASCADE CONSTRAINTS CHECKPOINT 1000;
Removes the column length and data from each row, freeing space in the data block.
Dropping a column in a large table tables a considerable amount of time.
删除列时使用检查点：
删除列可能需要很长时间，并且且需要大量的还原空间。从大型表中删除列时，可以指定检查点来尽量减少还原空间的使用。在幻灯片上的示例中，每 1,000 行出现一个检查点。在操作运行完成前，该表一直被标记为 INVALID。如果操作过程中例程失败，则该表在启动后仍将处于 INVALID 状态，因此该操作必须完成。
使用下面的语句可恢复中断的删除操作：
SQL> ALTER TABLE hr.employees DROP COLUMNS CONTINUE;
188,Renaming a Column
alter table hr.TT1 rename column old_name to new_name
189, Using the unused option
ALTER TABLE hr.employees SET UNUSED COLUMN comments CASCADE CONSTRAINTS;
ALTER TABLE hr.employees DROP UNUSED COLUMNS CHECKPOINT 1000;
ALTER TABLE hr.employees DROP COLUMNS CONTINUE CHECKPOINT 1000;
190,Getting Table Information
DBA_TABLES
DBA_OBJECTS
191, B- Tree Index
192,Creating Indexes: Guideline
l Balance query and DML needs
l Place in separate tablespace.
l Use uniform extent sizes: Multiples of five blocks or MINIMUM EXTENT size for tablespace.
l Consider NOLOGGING for large inexes.
l INITRANS should generally be higher on indexes than on the corresponding tables.
193,Create Bitmap Indexes
CREATE BITMAP INDEX orders_region_id_idx
ON orders(region_id)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;
CREATE_BITMAP_AREA_SIZE 参数：
初始化参数 CREATE_BITMAP_AREA_SIZE 决定了内存中用于存储位图段的空间量。缺省值为 8 MB。使用较大的值，可提高索引创建的速度。如果基数很小，可将该值设置为一个较小值。例如，如果基数仅为 2，则该值可以为千字节数量级而非兆字节数量级。一般来讲，基数越大，则获取最佳性能所需的内存越多。
194,Storage Params for Indexes
ALTER INDEX EMP_EMP_ID_PK STORAGE(NEXT 400K MAXEXTENTS 100);
有可能修改不成功。
195,Alloc & Dealloc Index Space
ALTER INDEX orders_region_id_idx ALLOCATE EXTENT (SIZE 200K DATAFILE ‘/DISK6/indx01.dbf‘);
ALTER INDEX orders_id_idx DEALLOCATE UNUSED;
196,Rebuilding Indexes
l Move an index to a different tablespace
l Improve space utilization by removing deleted entries
ALTER INDEX orders_region_id_idx REBUILD TABLESPACE indx02;
ALTER INDEX ...REBUILD
命令不能用于将位图索引更改为 B 树索引，反之亦然。
只能为 B
树索引指定 REVERSE 或 NOREVERSE
关键字。
197,Rebuilding Indexes Online
Indexes can be rebuilt with minimal table locking.
SQL>ALTER INDEX orders_id_idx REBUILD ONLINE;
Some restrictions still apply.
联机重建索引
建立或重建索引是一项费时的任务，尤其当表非常大时更是如此。在 Oracle8i 之前，建立或重建索引都需要锁定表，并要防止并发的 DML 操作。 Oracle9i 允许在基表上进行并发操作的同时建立或重建索引，但不建议在此过程中执行大量的 DML 操作。
注：仍存在 DML 锁，这意味着在联机索引建立期间不能执行其它 DDL 操作。
限制：
l 不能在临时表中重建索引
l 不能重建整个分区索引。必须分别重建每个分区或子分区。
l 也不能回收未用空间。
l 不能整个更改索引的 PCTFREE 参数值。
198,Coalescing Indexes
SQL> ALTER INDEX hr.employees_idx COALESCE;
199,Checking Index Validity
ANALYZE INDEX EMP_EMP_ID_PK VALIDATE STRUCTURE;
运行此命令后，查询 INDEX_STATS 以获取索引的有关信息，如下例所示：
SQL> SELECT blocks, pct_used, distinct_keys lf_rows, del_lf_rows FROM index_stats;
BLOCKS PCT_USED LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- -----------
8 19 107 0
如果索引中已删除行的比例很高，请重新组织该索引。例如：当 DEL_LF_ROWS 占 LF_ROWS 的比率超过 30% 时。
200,Dropping Indexes
l Drop and re-create an index before bulk loads.
l Drop indexes that are infrequently needed, and build indexes when necessary.
l Drop and re-create invalid indexes.
SQL>DROP INDEX hr.deptartments_name_idx;
201,Identifying Unused Indexes
To start monitoring the usage of an index:
ALTER INDEX hr. EMP_EMP_ID_PK MONITORING USAGE;
To stop monitoring the usage of an index:
ALTER INDEX hr. EMP_EMP_ID_PK NOMONITORING USAGE;
从 Oracle9i 开始，可以在 V$OBJECT_USAGE 中收集和显示有关索引使用的统计信息。如果收集的信息表明索引从未使用过，则删除该索引。此外，删除未用索引还可减少 Oracle 服务器用于 DML 操作的开销，从而改善了性能。每次指定 MONITORING USAGE 子句时，将对指定的索引重置 V$OBJECT_USAGE。以前的信息被清除或重置，并记录新的开始时间。
V$OBJECT_USAGE 列
INDEX_NAME：索引名
TABLE_NAME：对应的表
MONITORING：指示监视是 ON 还是 OFF
USED：指示 YES 或 NO，即在监视时间内是否使用了索引
START_MONITORING：索引监视的开始时间
END_MONITORING：索引监视的结束时间
202,Getting Index Information
DBA_INDEXES
DBA_IND_COLUMNS
V$OBJECT_USAGE
203,The type of constraints
l NOT NULL
l UNIQUE(可以有NULL值)
l PRIMARY KEY(不可以有NULL值)
l CHECK
l FOREIGN KEY
204, The integrate of Data
Application
Trigger
Constraints
205,The status of constraints
· DISABLE NOVALIDATE
· DISABLE VALIDATE
· ENABLE NOVALIDATE
· ENABLE VALIDATE
ALTER SESSION SET CONSTRAINT[S] ={IMMEDIATE|DEFERRED|DEFAULT}
206, Foreign key
涉及父表的 DDL：
在删除父表之前，必须先删除外键。可以使用以下一条语句同时执行这两个操作：
DROP TABLE table CASCADE CONSTRAINTS
在未删除或禁用外键之前无法截断父表。
在删除包含父表的表空间之前，必须先删除外键。可使用下列命令完成该操作：
DROP TABLESPACE tablespace INCLUDING CONTENTS CASCADE CONSTRAINTS
207, Using Constraints
A,ENABLE NOVALIDATE
没有表锁定
主键和唯一键必须使用非唯一索引
ALTER TABLE hr.departments ENABLE NOVALIDATE CONSTRAINT dept_pk;
B, ENABLE VALIDATE
锁定表
可以使用唯一或非
唯一的索引
需要有效的表数据
ALTER TABLE hr.employee ENABLE VALIDATE CONSTRAINT emp_dept_fk;
208,创建表时定义约束
SQL> create tablespace INDX datafile ‘/u01/app/oracle/oradata/test/indx01.dbf‘ size 100M;
SQL>CREATE TABLE hr.employee(id NUMBER(7) CONSTRAINT employee_id_pk PRIMARY KEY DEFERRABLE
USING INDEX STORAGE(INITIAL 100K NEXT 100K) TABLESPACE indx,
last_name VARCHAR2(25) CONSTRAINT employee_last_name_nn NOT NULL,
dept_id NUMBER(7)) TABLESPACE users;
209, Using Exceptions table
· 通过运行 utlexcpt.sql 脚本来创建 EXCEPTIONS 表。
SQL> @?/rdbms/admin/utlexcpt
SQL>Desc EXCEPTIONS
· 执行带有 EXCEPTIONS 选项的 ALTER TABLE 语句。
ALTER TABLE hr.employee ENABLE VALIDATE CONSTRAINT employee_dept_id_fk EXCEPTIONS INTO HR.exceptions;
· 使用 EXCEPTIONS 上的子查询定位包含无效数据的行。
SELECT * FROM hr.employee WHERE ROWID in (SELECT row_id FROM exceptions) FOR UPDATE;
· 纠正错误。
· 重新执行 ALTER TABLE 以启用约束。
210, Get the constraints information
DBA_CONSTRAINTS
DBA_CONS_COLUMNS
211,Enabling Password Mgmt
Te enable password management, run the utlpwdmg.sql script as user SYS
SQL>@?/rdbms/admin/utlpwdmg.sql
这个时候系统的密码就安装严格的格式校验，如果是个人测试系统，很麻烦，索引需要取消校验如下：
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function;(启用校验)
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;(取消校验)
212,Database Schema
· A schema is a named collection of objects.
· A user is created, and a corresponding schema is created.
· A user can be associated with only one schema.
· Username and schema are often used interchangeably.
213, Database authentication
SQL> create user watson identified by watson default tablespace users temporary tablespace temp quota 15M on USERS quota 10M on EXAMPLE password expire;
214,Getting User Information
DBA_USERS
DBA_TS_QUOTAS
215,Managing Privileges
There are two types of Oracle user privileges:
· System Priviledges: Enables users to perform particular actions in the database
· Object Priviledges: Enables users to access and manipulate a specific object
216,Who Can Grant or Revoke?
Only two types of users can grant system privileges to other users or revoke those privileges form them:
Users who were granted a specific system privilege with the ADMIN OPTION
Users with the system privilege GRANT ANY PRIVILEGE
For this reason, only grant these privileges to trusted users.
217,The PUBLIC
PUBLIC is a user group defined in the database; it is not a database user or a role. Every user in the database belongs to this group. Therefore, if you grant privileges to PUBLIC, they are avaiable to all users of the database.
218,Granting System Privileges
Use the GRANT command to grant system privileges.
The grantee can further grant the system privilege with the ADMIN option.
GRANT CREATE SESSION TO emi;
GRANT CREATE SESSION TO emi WITH ADMIN OPTION;
219,Revokoing System Privileges
· Use the REVOKE command to remove a system privilege from a user.
· Users with ADMIN OPTION for system privilege can revoke system privileges.
· Only privileges granted with a GRANT command can be revoked.
SQL>REVOKE CREATE TABLE FROM emi;
不能级联撤销系统权限
220，Granting Object Privileges
· Use the GRANT command to grant object privileges.
· Grant must be in grantor‘s schema or grantor must have GRANT OPTION.
GRANT EXECUTE ON dbms_output TO jeff;
GRANT UPDATE ON emi.customers TO jeff WITH GRANT OPTION;
221，Revoking Object Privileges
Use the REVOKE command to revoke object privileges
User revoking the privilege must be the original grantor of the object privilege being revoked.
REVOKE SELECT ON emi.orders FROM jeff;
可以级联revoke object priviledges
222，Getting Privileges Inform
· DBA_SYS_PRIVS
· SESSION_PRIVS
· DBA_TAB_PRIVS
· DBA_COL_PRIVS
223，Roles
· Roles can be grant to and revoked from users with the same commands that are used to grant and revoke system privileges.
· Roles can be granted to any user or role. However, a role cannot be granted to itself and cannot be granted circularly.
· A role can consist of both system and object privileges.
· A role can be enabled or disabled for each user who is granted the role.
· A role can require a password to be enabled.
· Each role name must be unique among existing usernames and role names.
· Roles are not owned by anyone; and they are not in any schema.
· Roles have their descriptions stored in the data dictionary.
224，Predefined Roles
· CONNECT Privilege to connect to the database;
· RESOURCE Privilege to create a cluster, a table, and a sequence, and to create programmatic objects such as procedures, functions, packages, indextypes, types, triggers, and operators.
· DBA ALL system privileges with the ADMIN option, so the system privileges can be granted to other users of the database or to roles.
· SELECT_CATALOG_ROLE Ability to query the dictionary views and tables.
· EXECUTE_CATALOG_ROLE Privilege to execute the dictionary packages (SYS-owned packages).
· DELETE_CATALOG_ROLE Ability to drop or re-create the dictionary packages.
· Also, when you run the catproc.sql script as part of the database creation, the script executes catexp.sql, with creates two more roles:
· EXP_FULL_DATABASE Ability to make full and incremental exports of the database using the Export utility.
· IMP_FULL_DATABASE Ability to perform full database imports using the Import utility, This is a very powerful role.
225，Getting Role Informatin
Information about roles can be obtained by querying the following views:
· DBA_ROLES： All roles that exist in the database.
· DBA_ROLES_PRIVS：Roles granted to users and roles
· ROLE_ROLE_PRIVS： Roles that are granted to roles
· DBA_SYS_PRIVS： System privileges granted to users and roles
· ROLE_SYS_PRIVS： System privileges granted to roles
· ROLE_TAB_PRIVS： Object privileges granted to roles
· SESSION_ROLES： Roles that the user currently has enabled
226，Data Loading Methods
Direct Load:
将数据从外部文件加载到 Oracle 数据库表中。SQL*Loader 包含一个功能强大的数据分析引擎，该引擎对数据文件中数据的格式几乎没有限制
SQL*Loader(直接加载):
直接加载插入可用于在同一数据库中从一个表向另一个表复制数据。此方法绕过数据库缓冲区高速缓存直接将数据写入数据文件，从而加快了插入操作的速度
227,Direct Load
直接加载插入（串行或并行）只能支持 INSERT
语句的 INSERT ... SELECT 语法而
无法支持其 INSERT ... Values
语法。INSERT ... SELECT 的并行性是由并行
提示或并行表定义决定的。
可使用 APPEND 提示调用直接加载插入，如以下的命令所示：
INSERT /*+APPEND */ INTO [ schema. ] table [ [NO]LOGGING ] sub-query;
228,Serial Direct Load
INSERT /*+ APPEND */ INTO my_objects NOLOGGING SELECT * FROM dba_objects;
COMMIT;(如果不提交，则没有办法做DML,或者Query my_objects对象)
数据时直接插入到HWM之后的。不走
SQL> select FORCE_LOGGING from v$database;
Note: If the database or tablespace is in FORCE LOGGING mode, then direct path INSERT always logs, regardless of the logging setting.
229，SQL*Loader control file
The loader control file tells SQL*Loader:
Where to find the load data
The data format
Configuration details:
· Memory management
· Record rejection
· Interrupted load handing details
How to manipulate the data
230，编码方案
Oracle 提供不同类别的编码方案：
单字节
宽度可变
宽度固定
Unicode
231:字符集
CHARACTER SET (数据库字符集)
NATIONAL CHARACTER SET (国家字符集):Oracle只有两种：AL16UTF16 | UTF8
NATIONAL LANGUAGE SUPPORT(NLS)国家语言支持
232,编码方案(推荐用Unicode)
Oracle 支持不同类别的字符编码方案：
· 单字节字符集(Including: 7 位 and 8 位)
单字节方案示例：
7 位字符集：美国 7 位 ASCII 码 (US7ASCII)
8 位字符集：
? 西欧 ISO 8859-1 码 (WE8ISO8859P1)
? 西欧 8 位 EBCDIC 代码页 500 码 (WE8EBCDIC500)
? 西欧 8 位 DEC 码 (WE8DEC)
· 宽度可变的多字节字符集
宽度可变的多字节方案示例：
? 日文扩展 UNIX 代码 (JEUC)
? 中文 GB2312-80 (CGB2312-80)
? AL32UTF8 (UTF-8)
· 宽度固定的多字节字符集
除了每个字符采用字节数固定的格式外，宽度固定的多字节字符集同宽度 可变的多字节字符集提供的支持类似。
这提供了每个字符具有统一字节长度表示法的好处。
宽度固定的多字节字符集示例：
AL16UTF16、16 位 Unicode（宽度固定的双字节 Unicode）
· Unicode (AL32UFT8, AL16UTF16, UTF8)
Unicode 是一种全球字符编码标准，可以表示计算机中使用的所有字符，包 括技术符号和出版用的字符。Unicode 标准 3.0 版包含 49,149 个字符，容量 超过一百多万个字符。
Oracle 提供AL32UTF8、UTF8 和UTFE 作为数据库字符集，同时提供 AL16UTF16 和UTF8 作为国家字符集。
233,数据库字符集和国家字符集
数据库字符集:存储类型为 CHAR、 VARCHAR2、 CLOB、 LONG 的数据列
国家字符集 :存储类型为 NCHAR、NVARCHAR2、NCLOB 的数据列
234,National Language Support(NLS)国家语言支持
235，指定会话的语言相关行为
ALTER SESSION SET NLS_DATE_FORMAT=‘DD.MM.YYYY’;
DBMS_SESSION.SET_NLS(‘NLS_DATE_FORMAT’,’’’DD.MM.YYYY’’’) ;
236，NLS 排序
SQL> ALTER SESSION SET NLS_SORT = BINARY;
237，在 SQL 函数中使用 NLS 参数
SELECT TO_CHAR(hire_date,‘DD.Mon.YYYY‘, ‘NLS_DATE_LANGUAGE=CHINESE‘) FROM employees;
238，使用 NLS 导入和加载数据
· 在导入过程中，数据将从导出文件字符集转换成数据库字符集。
SQL*Loader:
· 常规路径：将数据转换为 NLS_LANG 指定的会话字符集。
· 直接路径：数据直接转换为数据库字符集。
239，获取字符集信息
SQL>SELECT parameter, value FROM nls_database_parameters WHERE parameter LIKE ‘%CHARACTERSET%‘;
240，获取 NLS 设置信息
以下视图仅显示出已在 init<SID>.ora 文件中显式设置的参数的值。
SQL> SELECT * FROM nls_instance_parameters;
以下视图显示会话参数。
SQL> SELECT * FROM nls_session_parameters;
列出 NLS 参数的所有有效值。
SQL>SELECT * FROM v$nls_valid_values;
显示 NLS 参数的当前值。
SQL> SELECT * FROM v$nls_parameters;