以下是我總結的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;
· SetREMOTE_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 thedba 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
Thecontrol 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, OracleTemporary 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 toPUBLIC, 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 theADMIN 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 haveGRANT OPTION.
GRANT EXECUTE ON dbms_output TO jeff;
GRANT UPDATE ON emi.customers TO jeff WITHGRANT 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;
