数据库设计方案[初步]

1、中国银行项目名称：中国银行数据库建设方案数据库建设前期说明：全行所有经营数据集中于北京、上海、广州三个数据中心。上海和广州数据中心成为生产运行中心。北京为灾难备份中心。我们将于这三个地方建设三个数据库，三个数据库通过镜像技术，数据同步变化。在此我们选择广州的数据中心来说明数据库建设方案。其余两个数据中心的建设方案完全相同。采用三个数据中心完全相同的原因：1. 可以互相分担任务，提高业务操作的效率。2. 一旦发生灾害，无事故的数据中心可以立即替代故障的数据中心继续执行任务，将银行的损失降到最低。概念结构设计数据库包含如下几类表：1.分支（分支号，分支名，分支城市，资金）2.客户（客户Id，客户姓

2、名，性别，身份证号，地址）3.贷款（贷款号，数量）4.支付（支付号，支付日期，支付数量）5.员工（员工号，员工姓名，开始工作时间，密码）6.存款账户（账户号，密码，余额，利率）7.贷款分支（贷款号，分支号）8.申请（客户Id,贷款号）9.支询（客户Id,员工号）10.存取款（客户Id,账户号，查询日期，取款日期，存款日期）员工表（Employee）字段含义字段名称数据类型大小员工号Employee_IDchar20密码Employee_Passwordchar10员工姓名Employee_namevarchar20开始工作时间Employee_StartTimeDate3一条员工信息为0.05

3、KB。客户表(Customer)字段含义字段名称数据类型大小客户IDCustomer_IDchar20密码Customer_Pwdchar10姓名Customer_Namevarchar30性别Customer_Sexchar2身份证号Customer_IdentityChar18地址Customer_Addressvarchar40分支表（Branch）字段含义字段名称数据类型大小分支号Branch_IDchar10分支名Branch_Namevarchar20分支城市Branch_CityVarchar20资金Branch_Assetvarchar20存取款表（Account）字段含义字段

4、名称数据类型大小客户IDCustomer_IDchar20账户号Dps_numchar20存款时间SavingTimeDateTime8取款时间GetTimeDateTime8查询时间InquireTimeDateTime8贷款表（Loan）字段含义字段名称数据类型大小贷款号Loan_IDchar20数量Loan_AmountVarchar20支付表（Payment）字段含义字段名称数据类型大小支付号Payment_IDchar20支付日期Payment_TimeDateTime8支付数量Payment_AmountVarchar20存款账户表（Saving_Account）字段含义字段名称数

5、据类型大小账户号Saving_Account_IDchar20余额Saving_Account_Balancevarchar20支付数量Saving_Account_Amountvarchar20贷款分支表（Loan_Branch）字段含义字段名称数据类型大小贷款号Loan_IDchar20支付号Payment_IDchar20申请表（Borrower）字段含义字段名称数据类型大小客户IDCustomer_IDchar20贷款号Loan_IDchar20支询表（Ask）字段含义字段名称数据类型大小客户IDCustomer_IDchar20员工号Employee_IDchar20数据库结构1.

6、数据库部署模式(采用什么样的模式，什么样的业务类型等等，都应该写明原因)RAC+DATAGUARD模式RAC+DATAGURAD具备以下特点：（1）、需要冗余的服务器设备。该模式需要有冗余的服务器硬件。硬件成本较高。（2）、安装配置比较复杂。该模式既需要配置RAC又需要配置DATAGUARD，配置过程比较复杂，配置周期长。数据库服务器采用RAC+DATAGUARD模式，可以满足对可用性和容灾都有特定需求的应用。选择该模式的原因：RAC的好处是可以由多个性能较差的机器构建出一个整体性能很好的集群，并且实现了负载均衡，那么当一个节点出现故障时，其上的服务会自动转到另外的节点去执行，用户甚至感觉不到

7、什么。 2. 设计的数据库业务类型：OLTP（联机事务处理）使用OLTP的原因：a. 支持大量并发用户定期添加和修改数据。b. 在联机事务处理中，事务是被立即执行的c. 将分布于不同地理位置的业务处理计算机设备或网络与业务管理中心网络连接，以便于在任何一个网络节点上都可以进行统一、实时的业务处理活动或客户服务。3. 数据库规模（自己可以先到网上搜索数据，自己选择的那个银行相关的数据，如客户存款多少，客户数目多少，想办法查找，如果没有的话，可以自己编但是要合理，而且要有两年以上的数据，因为要算出增长率的大小，有了增长率才能说明数据库每年的容量要增大多少，这里我按照稳定增长率计算。还有就是，当存储

8、数据一年下来，就已经占用了大量的空间，这时我们可以说，在一定的时间就把数据转储到历史库中。这样就可以空出磁盘的容量，使数据库继续正常运行。再说一下，数据量的算法。首先我们都已经将表建好了，那么我们以员工表为例来说明。员工表包含字段 员工号、密码、员工姓名、开始工作时间，那么员工号的字符类型是char，20个字节，密码10个字节，员工姓名20个字节，开始工作时间3个字节。加起来就是53个字节，即是53Byte,转换成KB，即53/1024=0.05KB。大约的而已那这样一条就有0.05KB,那全大陆的中国银行总共员工3万多人，我们就可以用3万*0.05KB，可以估计出，员工表所要占据的内存的大小

9、。其他表格的计算方法都是一样的。）查询中国银行相关信息后，制作出上述表格。由上表可知，中国银行在2010年末拥有客户数目为1.9亿，2011年末达到2.1亿，增长率为10%。而存储各类账户总数达4.8亿户。分析可知，大约每位客户拥有2-3个账户。根据客户表信息计算，一条客户信息大约为0.12KB。所以2011年客户信息总量为24GB。按10.5%的增长量计算，2012年客户信息总量为26.52GB。客户信息增量为2.52GB。2011年中国银行的营业网点（含自助银行）有23876家，以2%的增长率增长。根据分支表计算，每个网点信息大约为0.068KB。所以2011年营业网点信息总量为1.6MB

10、。预计2012年营业网点信息总量为1.632MB。营业网点信息增量为0.032MB。.2012年数据库的数据量大约为650.3GB。数据库的总数据增长量为105GB/年数据库业务数据量可能为600GB-1TB，大规模数据库。4.磁盘配置（RAID 0+1）RAID 0+1的特点使其特别适用于既有大量数据需要存取，同时又对数据安全性要求严格的领域，如银行、金融、商业超市、仓储库房、各种档案管理等。 Raid 0+1需要的驱动器数量（至少）：4（要求驱动器的数量为偶数）我们选择购买的磁盘，一个为320GB。由估计的数据量我们可以推算出，一年至少需要1000GB的容量。而数据库中的数据每年转储一次（

11、到历史库）。为购买磁盘编号将数据文件、重做日志文件、控制文件、数据库备份等分别存放在这些磁盘中。并且相互镜像。（1）业务数据量的估算序号表名每条数据大小（KB）增长量（/年）增长率(%)2011年数据量2012年数据量（预计）1.客户表（Customer）0122.52g 10.524gb26.52gb2.分支表（Branch）0.0680.032mb21.6mb1.632mb3.总数据量（2）表空间的类型应用表空间：业务应用数据保存在此类表空间中表空间名说明数据文件表空间中的表Branch该空间存放营业网点相关的数据文件，包含分支表信息。存储在1号磁盘上ORACLE11gORADATABOC

12、branch.dbf分支表、贷款分支表Saving_Account该空间存放存款账户相关的数据文件，包含存款账户表信息。存储在2号磁盘上ORACLE11gORADATABOCsaving_account_n.dbf存款账户表Saving_Account_index该空间存放存款账户相关的索引。存储在3号磁盘.表空间的管理方式：本地管理方式（区的分配和管理信息都存储在表空间的数据文件中，而与数据字典无关。）原因：1. 由于在区分配与回收过程中不需要对数据字典进行访问，因此提高了表空间存储管理操作的速度和并发性2. 能够避免表空间存储管理操作中的递归现象，提高了存储空间管理操作性能。.表空间的管理

13、策略使用多个表空间可以使用户操作更具灵活性，但是应注意：1. 将数据字典与用户数据分离，避免由于数据字典对象和用户对象保存在一个数据文件中而产生I/O冲突。2. 将回滚数据与用户数据分离，避免由于硬盘损坏而导致永久性的数据丢失。.参数文件对于单机或RAC方式的系统，可使用共享的spfile参数文件设置利用pfile创建spfile创建初始化参数文件将初始化参数文件命名为initBOC.ora 存放于xx号磁盘。路径为:.oracle11gadminBOCpfile目录中。其内容为# Copyright (c) 1991, 2001, 2002 by Oracle Corporation# #

14、NLS#nls_language="SIMPLIFIED CHINESE"nls_territory="CHINA" # SGA Memory#sga_target=6T # Shared Server#dispatchers="(PROTOCOL=TCP) (SERVICE=BOCXDB)" # Miscellaneous#compatible=11.2.0.0.0diagnostic_dest=E:Oracle11g # Security and Auditing#audit_file_dest=E:Oracle11gadmins

15、tuadumpaudit_trail=dbremote_login_passwordfile=EXCLUSIVE # Sort, Hash Joins, Bitmap Indexes#pga_aggregate_target=3T# Database Identification#db_domain=""db_name=BOC # File Configuration#db_create_file_dest=E:Oracle11goradatadb_create_online_log_dest_1=g:/oradata # Cursors and Library Cache

16、#open_cursors=300 # System Managed Undo and Rollback Segments#undo_tablespace=bocundoundo_management=AUTO # Processes and Sessions#processes=150 # Cache and I/O#db_block_size=8192 control_files=("g:oracle11goradatacontrol01.ctl", "d:oracle11goradatacontrol02.ctl", "f:oracle1

17、1goradatacontrol03.ctl")SGA自动管理在Oracle11g中，通过设置初始化参数SGA_TARGET，可以实现对SGA中的数据高速缓冲区、共享池、大型池、Java池和流池的自动管理，即这几个组件的内存调整不需要DBA来干预，系统自动进行调整。减轻DBA的工作负担，提高数据库的运行效率。*.日志缓冲区、非标准块的数据高速缓冲区、保留池、回收池等其他区域的调整还需要DBA使用ALTER SYSTEM语句手动进行调整。设置SGA自动管理：计算参数SGA_TARGET的大小SQL>SELECT ( (SELECT SUM(VALUE) FROM V$SGA) (

18、SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY) ) /1024/1024 | MB “SGA_TARGET” FROM DUAL通过计算，我们可以得知，SGA_TARGET为6T。设置参数SGA_TARGETSGA_TARGET=6T;将SGA中与自动管理相关的组件大小设置为0SHARED_POOL_SIZE=0;LARGE_POOL_SIZE=0;Java_POOL_SIZE=0;STREAMS_POOL_SIZE=0;进行完上述设置之后，SGA采用了自动管理。如果要取消自动管理，只需将参数SGA_TARGET设置为0即可。设置重做日志

19、缓冲区重做日志缓冲区用于缓存用户对数据库进行修改操作时生成的重做记录。重做日志缓冲区的大小由参数文件中的LOG_BUFFER参数指定。在这里，设置重做日志缓冲区的值为800G。可以在数据库运行期间进行调整，即SQL>ALTER SYSTEM SET LOG_BUFFER=800G;设置PGAPGA_AGGREGATE_TARGET：1.5T原因：对于OLTP系统，由于系统的每个会话所占用的内存较少同时会话数量较多，则系统的磁盘缓存和共享缓存需求较大，可以设定较小的PGA_AGGREGATE_TARGET，即可以设定PGA=SGA/4。设置数据库服务器工作模式采用共享服务器模式共享服务器模

20、式设置:#Shared Server原因:采用共享服务器模式，一个服务器进程可以被多个用户进程共享。符合高并发操作的要求。设置共享服务器dispatchers="(PROTOCOL=TCP) (SERVICE=BOCXDB)"设置兼容性compatible=11.2.0.0.0设置安全与审计remote_login_passwordfile=EXCLUSIVE设置全局数据库名db_domain=""db_name=BOC*BOC即中国银行英文Bank of China简称设置自动进行回滚并且设定回滚表空间为bocundoundo_tablespace=b

21、ocundoundo_management=AUTO最大进程数processes=300000设置标准数据块大小db_block_size=8192创建服务器初始化参数文件(参考课本)设置完上述初始化参数后，就可以利用初始化参数文件创建服务器初始化参数文件了。基本步骤：（1） 创建一个文本初始化参数文件，文件中包含所有参数设置，并将该文件放在数据库服务器上（2） 连接到Oracle数据库；创建临时表空间组所谓的临时表空间组是指，将一个或多个临时表空间构成一个表空间组。原因：当将临时表空间组作为数据库或用户的默认临时表空间时，用户就可以同时使用该表空间组中的所有临时表空间，避免了由于单个临时表空

22、间的空间补足而导致数据库运行故障。同时，使用临时表空间组，可以保证在一个简单并行操作中多个并行服务的执行。创建撤销表空间撤销表空间专门用于回滚段的自动管理。注意事项：1. 如果没有创建撤销表空间，那么将使用SYSTEM表空间来管理回滚段。2. 如果数据库中包含多个撤销表空间，那么一个实例只能使用一个处于活动状态的撤销表空间，可以通过参数UNDO_TABLESPACE来指定；如果数据库中只包含一个撤销表空间，那么数据库实例启动后会自动使用该撤销表空间。创建应用表空间区的管理方式为UNIFORM，大小为128K。如果选择AUTO的管理方式，数据库会按照8-128-1024-8192个数据块的方式急

23、剧增大，会造成系统过多的碎片和较低的存储空间利用效率。段的管理方式为AUTO。表空间的参数设置原则（1） 关于Pctfree和Pctuserd设置对于主要操作为insert的数据对象，可以考虑设定较小pctfree和较大的pctused，如pctfree=5 Pctused=60。对于更新较为频繁的系统，可以设定较大的pctfree和较小的pctused来避免行的迁移，如pctfree=20 Pctused=40。对于银行系统，由于数据的保留时间较长，同时数据的删除较少可以考虑设定较小的pctfree和较大的pctused，如：Pctfree=10 Pctused=50（2） 关于Initra

24、ns和Maxtrans设置由于目前的数据块由逐步变大的趋势，故此同一个数据块中发生并发事务的几率在上升。对于db_Block_Size=8192的OLTP系统，可以设定initrans4，Maxtrans10Branch表空间为例创建应用表空间(数据文件存放于1号磁盘)由于Branch表的数据占空间非常小，所以只需要创建一个数据文件，该数据文件50m。SQL>CREATE TABLESPACE branch DATAFILE .oracle11goradatabocbranch01.dbf size 50mExtent management local uniform size 128k

25、;Saving_Account表空间为例创建应用表空间(数据文件存放于2号磁盘)按上述表格计算，基础数据占11.4g，增长量0.912g/年，10年即为9.12g。按稳定增长计算可知，总共占21g。需要创建21个数据文件，每个数据文件1g。SQL>CREATE TABLESPACE saving_account DATAFILE .oracle11goradataboccustomer_01.dbfsize 1g .oracle11goradataboccustomer_02.dbfsize 1g .oracle11goradataboccustomer_21.dbfsize 1gExt

26、ent management local uniform size 128k;子系统：概要文件对于所有的用户，应为其创建一个名为pwd_profile的概要文件，如果用户连续3次登录失败，则锁定该账户。CREATE PROFILE pwd_profile LIMIT FAILED_LOGIN_ATTEMPTS 3;1. 开户子系统（Openning-Account）开户子系统的用户为Customer_cityname,按中国所有地级市+县级市+直辖市+自治区计算，共有666个用户，而每个用户对应一个模式。每个模式存放客户（Customer）表。此处以广州市为例子创建用户，其他城市的用户创建过程

27、完全相同。(注：初始密码全部与用户名相同，密码设置为过期状态，即首次连接数据库时需要修改口令。概要文件为pwd_profile。)Create USER Customer_Guangzhou Identified by Customer_GuangzhouDefault TABLESPACE Customer QUOTA 500m ON CustomerProfile pwd_profile PASSWORD EXPIRE;2. 查询子系统(Ask)查询子系统用户为Ask_cityname,拥有666个用户（理由同上）。每个用户对应一个模式。每个模式存放客户(Customer)表、存取款（Ac

28、count）表、支询(Ask)表此处以广州市为例子创建用户，其他城市的用户创建过程完全相同。(注：初始密码全部与用户名相同，密码设置为过期状态，即首次连接数据库时需要修改口令。概要文件为pwd_profile。)Create USER Ask_Guangzhou Identified by Ask_GuangzhouDefault TABLESPACE Ask QUOTA 500m ON AskProfile pwd_profile PASSWORD EXPIRE;模式所谓模式是指一系列逻辑数据结构或对象的集合。模式与用户相对应，一个模式只能被一个数据库用户所拥有，并且模式的名称与这个用户的名

29、称相同。在通常情况下，用户所创建的数据库对象都保存在与自己同名的模式中。*.在同一模式中，数据库对象的名称必须唯一，而在不同模式中的数据库对象可以同名。权限管理首先以SYSDBA的身份登录数据库并且对PUBLIC用户组授予CREATE SESSION的权限。(密码为oracle,数据库名称为BOC)原因：PUBLIC是创建数据库时自动创建的一个特殊的用户组，数据库中所有用户都属于该用户组。如果将某个权限授予PUBLIC用户组，则数据库中所有用户都具有该权限。C:>sqlplus /nologSQL>connect sys/oracleBOC as sysdbaSQL>GRAN

30、T CREATE SEESION TO PUBLIC;由于各系统中的用户数量非常多，如果为每个用户授予权限，耗费的时间非常大。故先将权限授予角色，再将角色授予用户。这样就可以减少工作量。由于上面已经写了数据库管理员的登录方式，下面不再重述。1 开户子系统先创建角色，角色名为customer_role,并且授予相应的权限，然后再将角色授予相应的用户。（可以按每个省份来授予，例如这一次操作可以授予广东省的所有城市，减少错误率）SQL>Create ROLE customer_role;SQL>GRANT select,update,delete,insert on customer t

31、o customer_role;SQL>GRANT customer_role to Customer_Guangzhou,Customer_Huizhou,Customer_Zhongshan,;2 查询子系统先创建角色，角色名为ask_role,并且授予相应的权限，然后再将角色授予相应的用户。（可以按每个省份来授予，例如这一次操作可以授予广东省的所有城市，减少错误率）SQL>Create ROLE ask_role;SQL>GRANT select on Ask to ask_role;SQL>GRANT select on Account to ask_role;

32、SQL>GRANT select on Customer to ask_role;SQL>GRANT ask_role to Ask_Guangzhou,Ask_Huizhou,Ask_Zhongshan,;文件设计如果使用文件作为数据库设备，则根据存储空间的需求，建立独立的文件系统，挂接到该目录下。4. 控制文件每个数据库实例应至少有两个控制文件，且每个文件存储在独立的物理磁盘上。如果有一个磁盘失效而导致控制文件不可用，与其相关的数据库实例必须关闭。一旦失效的磁盘得到修复，可以把保存在另一磁盘上的控制文件复制到该盘上。将控制文件分别存放在26，27，28号磁盘上。因此，为了使整个

33、系统的高可靠地运行，建议系统设置2-3个控制文件设置每个控制文件大小为256mb保存路径: .oracle11goradatacontrol01.ctl;(26号磁盘) .oracle11goradatacontrol02.ctl;（27号磁盘） .oracle11goradatacontrol03.ctl;（28号磁盘） 5. 重做日志文件分布：设置两个个重做日志文件组，group1在29号磁盘上放置一个重做日志文件，在30号磁盘上放置一个重做日志文件。Group2在29号磁盘上放置一个重做日志文件，在31号磁盘放置一个重做日志文件Create database bocLogfileGroup 1（oracle11goradatabocredo01.log） size 200m,Group 2( oracle11goradatabocredo03.log) size 200m;Alter database add logfile member oracle11goradatabocredo02.log to group 1, oracle11goradatabocredo04.log to group 2;服务器工作方式采用共享服务器模式一个服务器进