Preinstallation Tasks (for Linux/Unix only)
1. Configuring the Kernel and Creating the oracle User
2. Setting Environment Variables
Change Password in Database Configuration Assistant
用户密码设置
sys => sysdba 凌驾于多个db之上
system => dba 只属于一个db
Strcture overview
Physical Structure
Instance (db启动后的管理, 运转的机制)
memory （sga + pga）：
SGA: System Global Area, allocated at instance startup
PGA: Program Global Area, allocated when the server process is started (pga private属于一个用户session)
share pool: library cache (put in SQL)
data dict. cache (数据字典，包含了数据库中所有的信息).
data buffer cache 数据缓存
large pool 对share pool的补充
redo log buffer 对数据库的任何change
pga: user session info
backgroud process
SMON(system monitor)
DBW0(db writer, 原dbwr, wr:writer)可以有多个
PMON(process monitor)
LGWR(log writer)
CKPT (checkpoint)
Others…
Database (数据库的文件形式)
Files:
data files,
control files(binary file, defines db name , instant name, collate char…),
redo log(record redo log buffer),
other…
redo log file: 文件循环使用、可以通过写到archive log file导出成文件， 写到tape等存储介质
transction <-> DML(data management language) DDL(data definition language)
rollback commit 中间包含update delete alter
LOCK: when a transction is running, other transctions have to wait
rollback segment (undo) -> 存储在data buffer cache
old image: buffer 中，与transction有关
new image: buffer -> file, 记录所有新的改变
roll forward: database crash, first compare the SCN(seqence number in files), get data from redo log files, put into data cache, then write to data file
checkpoint: 触发点 在redo log file从1到2时会产生一个checkpoint，可以手工设置
NONArchiveLog mode: 循环使用redo log file，不写出文件，自动清空；否则当磁盘满是系统会等待
parameter file(pfile) (sp file:server parameter file, binary)
password file(binary), relate to security
alert file: important system event(boot, shutdown, error log)
trace file: backgroud process & server process or user process(a user session)
MTS share server
Logical Structure
Tablespaces
An Oracle database can be logically grouped into smaller logical areas of space known as tablespaces.
A tablespace can belong to only one database at a time.
Each tablespace consists of one or more operating system files, which are called data files.
A tablespace may consist of zero or more segments.
Tablespaces can be brought online while the database is running.
Except for the SYSTEM tablespace or a tablespace with an active undo segment, tablespaces can be taken offline, leaving the database running.
Tablespaces can be switched between read-write and read-only status.
Tablespace (can have multi data file,1 data file only belong to 1 tablespace): system(dict.) & non-system (table, index, sp) 存放考虑因素: IO & 碎片. temp (for sorting) temporate, undo(RBS), data(table), index, user, tools
Segment
A segment is the space allocated for a specific logical storage structure within a tablespace. For example, all of the storage allocated to a table is a segment.
A tablespace may consist of one or more segments.
A segment cannot span tablespaces; however, a segment can span multiple data files that belong to the same tablespace.
Each segment is made up of one or more extents.
Extents
Space is allocated to a segment by extents.
One or more extents make up a segment.
An extent is a set of contiguous Oracle blocks.
An extent cannot span a data file but must exist in one data file.
extent (连续的block)
Data Blocks
The Oracle server manages the storage space in the data files in units called Oracle blocks or data blocks.
oracle block (DW:32k, OLTP:8k) depend on data transfer
RAC: 多个instance 对应一个db(cluster)
Process Structure
User Process
Server Process
Background Process (related to physical and memory structures)
Oracle Startup:
NOMOUNT(instance started) -> MOUNT(open control files) -> OPEN (open all files)
Oracle Shutdown:
Abort
Immediate
Transctional
Normal
Tuning DEV
design: 3NF
application: SQL* , clusters物理存储重叠， index (B-tree, bitmap, function based一个表达式index)
memory: sga (3)
I/O (space management)
contention ( latch 资源锁), lock <=> (DML) <=> trans; level : table, record; size of trans
OS
Tuning PRO
contention
SQL Tuning:
Optimizer
RBO rule-based
CBO cost-based
小的表放在后面(产生笛卡尔积)
尽量加等于
大于、小于子句放在等于前面
where子句中有”=”,使用index较好；数据较多用index
delete (record in redo log file); truncate (not record, DDL operate) 磁盘整理好，但developer一般不使用
使用IN性能较差，尤其当使用OR时
exist 替代 distinct
Tools
Statspack 整个db
explain plan => 分析结果，plan_table
tkprof读trace工具
sql analyze
sql *plus: plan_table, role, set autotrace [on] [explain]
Security
User:
Collection of privileges
Schema:
Collection of objects
object集合, table属于schema,
User: 权限集合 Create user
一个user对应一个schema，但也不尽然
v$_ dynamic view
DBA_ static view
Role:
可赋于多个User的权限集合
Profile对应User
Direct权限：Create User,Table
Authentication mechanism: 1. OS SYS; 2.DB SYSTEM
CONNECT -> table, index
Resoure -> more privilege
DBA -> can only give to dba
Net Service
db_1networkadmintnsnames.ora
Client Side: Service Name
Production环境将port换掉，不用1521
tnsping命令， 测试连接
DB Backup
Logical: import/ export
Physical: copy file (cold, hot)
Mode:
Table mode: export时没有关系，import要注意导入的顺序，如需要将有PK的表先导入，FK的表随后导入
用途：数据库升级，处理碎片，Backup数据
Rocovery: 从Redo Log File中恢复某一点的DB备份，用Until time制定恢复时间
RESETLOGS命令，用于同步各file的SCN
RMAN: backup工具包
优点：可以做基于increametal/accumental的累积备份，不用每次复制整个db（export/import同样有这一功能）
Resource:
ASK TOM (oracle expect)
Book: Expect one on one