dbca创建数据库不同模板之间的区别

   这篇文章讨论通过dbca创建数据库，默认模板之间的区别，下面是11.2.0.3 dbca建库选择模板的截图：



    从截图可以看出，系统自带3个模板，分别是：“一般用途和事务处理”，“定制数据库”，“数据仓库”，之前的Oracle Database版本好像是4个模板，一般用途和事务处理是不同的两个模板。

下面我们看看Oracle是怎么解释系统自带模板的：

Templates are divided into the following types:

• Seed templates

• Nonseed templates

The characteristics of each are shown in Table 2-1.

Table 2-1 DBCA Template Types

TypeFile ExtensionIncludes Data FilesDatabase Structure

Seed	.dbc	Yes	This type of template contains both the structure and the physical data files of an existing database, referred to as the seed database. Your new database starts as a copy of the seed database, and requires only the following changes: Name of the database Destination of the data files Number of control files Number of online redo log groups Initialization parameters Other changes can be made after database creation using custom scripts that can be invoked by DBCA, command-line SQL statements, or Oracle Enterprise Manager Database Control. The data files for the seed database are stored in compressed RMAN backup format in a file with a .dfb extension. The seed database control file is stored in a file with .ctl extension. (This file is needed only when storing data files in Oracle Automatic Storage Management (Oracle ASM) disk groups or as Oracle Managed Files.) The .dbc file contains the location of the seed database data files and contains the source database name used to mount the control file.
Nonseed	.dbt	No	This type of template is used to create a new database. It contains the characteristics of the database to be created. Nonseed templates are more flexible than their seed counterparts because all data files and online redo log files are created to your specification, and names, sizes, and other attributes can be changed as required.

    Oracle将模板类型分为seed和Nonseed模板，上图中的“包含数据文件”为“是”的就是seed模板类型，它的特点是包含数据文件，简单的说就是从一个RMAN备份中恢复数据库，由于是恢复数据库不能设定数据文件大小，不能设定选件等，最大特点是创建速度特别快，“一般用途和事务处理”和“数据仓库”就属于这种模板类型；“定制数据库”属于Nonseed模板，该模板不包含数据文件，需要使用create database命令创建数据库，创建周期较长，但能根据需要选择组件，控制数据文件大小等，更加的灵活，但对于大部分系统业务来说，“一般用途和事务处理”模板类型是最常用的选择。

   刚刚我们已经提到，“一般用途和事务处理”和“数据仓库”都是从同一个模板（备份）类型的文件中恢复而来，Oracle的模板存放位置为$ORACLE_HOME\assistants\dbca\templates，下面是模板位置下的文件截图：

    example.dmp和example01.dfb是“示例表空间”的内容，可以猜想是通过可传输表空间的形式附加到创建的数据库中。
   Data_Warehouse.dbc是“数据仓库”模板文件，General_purpose.dbc是“一般用途和事务处理”模板文件，它们都属于Seed模板类型，对应同一个模板文件Seed_Database.dfb，Seed_Database.ctl是seed数据库的控制文件（从这点再次印证控制文件对于RMAN恢复的重要性）。
   New_Database.dbt是Nonseed模板文件，属于不包含数据文件的模板，*.dbc和*.dbt都是以XML文件的形式进行存储。

    既然“一般用途和事务处理”和“数据仓库”都属于seed模板类型，那么它们之间又有什么区别呢？下面首先来看看Oracle对它们的解释：

DBCA Templates Provided by Oracle

Oracle provides the templates shown in Table 2-2.

Table 2-2 Oracle-Provided DBCA Templates and Their Corresponding Workloads

TemplateWorkload

Data warehouse	Users perform numerous, complex queries that process large volumes of data. Response time, accuracy, and availability are key issues. These queries (SELECT statements) range from a fetch of a few records to queries that sort thousands of records from many different tables.
General Purpose or Transaction processing	Many concurrent users perform numerous transactions that require rapid access to data. Availability, speed, concurrency, and recoverability are key issues. Transactions consist of reading (SELECT statements), writing (INSERT and UPDATE statements), and deleting (DELETE statements) data in database tables.
Custom database	This template allows you maximum flexibility in defining a database because you can change any of the settings for the database being created.

   Oracle只在用途上对它们进行了解释，除此之外，我们来对比一下两个模板dbc文件之间的区别：
用文件比较文件可以很容易的发现，Data_Warehouse.dbc比General_Purpose.dbc文件多了下面这行：
<initParam name="star_transformation_enabled" value="TRUE"/>

这是一个初始化参数，下面是对它的解释：

STAR_TRANSFORMATION_ENABLED

Property Description

Parameter type	String
Syntax	STAR_TRANSFORMATION_ENABLED = { FALSE | TRUE | TEMP_DISABLE }
Default value	FALSE
Modifiable	ALTER SESSION, ALTER SYSTEM
Basic	Yes

STAR_TRANSFORMATION_ENABLED determines whether a cost-based query transformation will be applied to star queries.

Values:

• FALSE

  The transformation will not be applied.

• TRUE

  The optimizer will consider performing a cost-based query transformation on the star query.

• TEMP_DISABLE

  The optimizer will consider performing a cost-based query transformation on the star query but will not use temporary tables in the star transformation.

这是启用数据仓库中的星形模式，相关的知识可以搜索一下，其他区别还未发现，等待我们去发现。

--end--