ORACLE体系架构
分为两个部分: Database和Instance。
Instance:连接到oracle数据库。由内存(SGA)和后台进程(Background process structures)构成
oracle Database: 三种基本的文件类型 : 数据文件, 控制文件,重做日志文件 。(还包括 参数文件 归档日志文件)
内存区域包含两个内存块:SGA(System Global Area) 当instance启动的时候分配,是oracle实例的基础部分,
一个实例一个SGA
PGA (Program Global Area): 当服务进程(server process)启动的时候分配,注意和后台
进程的区别。服务进程主要负责和客户端进行通信。
一个后台进程一个PGA ,体积相对小
SGA包含下面几个内存结构:
Shared Pool :SHARED_POOL_SIZE
Database Buffer Cace :DB_CACHE_SIZE
Redo Log Buffer :LOG_BUFFER
其他结构(lock and latch management statistical data)
另外两个可选的区域:Large Pool Java Pool (LARGE_POOL_SIZE,JAVA_POOL_SIZE)
SGA 是动态的 , 最大SGA_MAX_SIZE 。增大或减少 都以Granule为单位,在内存中是连续的
select component,granule_size from v$sga_dynamic_components;
Shared Pool: 用来存储 最近执行的SQL语句, 最近用到的数据定义 包含两块性能相关的两个内存结构:
Library Cache ,Data Dictionary Cache
DataBase Buffer Cache:
大小由DB_BLOCK_SIZE决定,为了提高性能的。存储着使用的数据。由三个独立的子块:
DB_CACHE_SIZE DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE
ALTER SYSTEM SET DB_CACHE_SIZE=96M
可以用视图来查看: V$DB_CACHE_ADVICE
Redo Log Buffer:
Records all change 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
Size defined by LOG_BUFFER
Large Pool :rman
Java Pool : JAVA Commands
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
oracle 有下面几类进程:用户进程 服务进程 后台进程
Background Process:Mandatory background process: DBWn , PMON, CKPT, LGWR, SMON
Optional background process: ARCn,LMDn,QMNn,CJQ0, LMON, RECO,Dnnn etc..
Database Writer(DBWn) :
DBWn writes when: Checkpoint occurs
Dirty buffers reach threshold
There are no free buffers
Timeout occurs
RAC ping request is made
Tablespace OFFLINE
Tablespace READ ONLY
Table DROP or TRUNCATE
Tablespace BEGIN
BACKUP
Log Writer (LGWR):
LGWR writes: At commit
When one-third full
When there is 1MB of redo
Every three seconds
Before DBWn writes
System Monitor(SMON):
Instance recovery , Deallocates free space, Deallocates temporary segments
Process Monitor(PMON):
Cleans up after failed processes by :Rolling back the transaction, Releasing locks, Releasing other resources,Restarting dead dispatchers
Checkpoint(CKPT):
Signaling DBWn at checkpoints
Updating datafile headers with checkpoint information
Updating control files with checkpoint information
Archiver (ARCn):
Logical Structure
逻辑上来说 由 Tablespace, segment,Extent,Blocks构成
物理上来说 由 Datafile构成
Tablespace,segment能跨datafile,Extent,Blocks不能跨Datafile, 一个Extent由多个连续的Blocks构成
./runInstaller - responsefile myrespfile -silent 无交互安装
对应安装目录下 一般有实例安装文件 10201_database_linux32\database\response\*.rsp
在安装时候 默认创建的用户:sys,system 权限 sys>system
初始化参数文件:
Ecplicit: Having an entry in the file
Implicit: No entry within the file, but assuming the Oracle default values
Changes to entries in the file take effect based on the type of initialization parameter file used:
Static parameter file, PFILE
Persistent server parameter file , SPFILE
查看两种方式:
相关的视图:v$parameter 或者 show parameter sga;
PFILE: 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 location is $ORACLE_HOME/dbs
一般的命名规范:initSid.ora
Creating a PFILE
cp init.ora $ORACLE_HOME/dbs/initdba01.ora 要注意命名规范 initSid.ora
然后根据需求修改 initdba01.ora文件
SPFILE - spfileSID.ora
Binary file , Maintained by the Oracle srver , Always resides on the server side,Ability to make changes persistent across shut down and start up,can self-tune parameter values, can have recovery manager support backing up to the initialization parameter file
Creating an SPFILE
Created form a PFILE file
Create SPFILE='$ORACLE_HOME/dbs/spfileDBA01.ora' FROm PFILE='$ORACLE_HOME/dbs/initDBA01.ora';
can be executed before or after instance start up
当然也可以用 strings spfileSID.ora | more 方式来看
Modify Parameters in SPFILE
Alter system set parameter=value <comment='text'> <deferred> <scope=memory| spfile | both> <sid='sid|*'>
Alter system RESET xxxxx SCOPE=BOTH SID='*'
SPFILE VS PFILE
1. An SPFILE can be backed-up with RMAN(RMAN cannot backup PFILES)
2.Reduce human errors
3.Eliminate configuration problems ( no need to have a local PFILE if you want to start Oracle from a remot machine)
数据库在启动时候找初始化文件的顺序:
1.spfileSID.ora
2.Default SPFILE->spfile.ora
3.initSID.ora
4.Default PFILE
Specified PFILE can override precedence
STARTUP PFILE=$ORACLE_HOME/dbs/initDBA1.ora
PFILE can indicate to use SPFILE
SPFILE=/database/startup/spfileDBA1.ora
ORACLE 启动的过程:SHUTDOWN->NOMOUNT->MOUNT->OPEN
到NOMOUNT状态: When Oracle Database starts an instance , it reads the srver parameter file(SPFILE) or initialization parameter file to determine the values of initialization parameters. Then, it allocates anSGA, which is a shared area of memory used for database information , and creates backgroud process.At this point, no database is associated with these memory structures and process.
When the instance starts , the database wirtes all explicit parameter settrings to the alert log in valid parameter syntax.If necessary ,you can copy and paset this text into a new parameter file and restart the instance.
到Mounted状态:the instance mounts a database to associate the database with that instance.To mount the database, the instance finds the database control files and opens them. Control files are specifie in the CONTROL_FILES initailization parameter in the parameter file used to start the instance ,.ORACLE database then reads the control files to get the names of the database's datafiles and redo log files
At this poing , the database is still closed and is accessible only to the database administrator. the database administrator can keep the database closed while completing specific maintenance operations However the database is not yet available for normal operations.
到 OPEN状态:Open a mountd database makes it available for narmal database operations. Any valid user can connect to an open database and access its information . Usually , a database administrator opens the database to make it available for general use.
When you open the database, Oracle Database opens the online datafiles and redo log files. If a tablespace was offline when the database was previously shut dwn , the tablespace and its corresponding datafiles will still be offline when you reopen the database.
If any of the datafiles or redo log files are not present when you attempt to open the database , then Ooracle Database returns an error. You must perform recovery on a backup of any damaged or missing files before you can open the database.