Create database

1.创建数据库之前的准备工作

• a privileged (SYSDBA) account authenticated in one of the following ways : ( By the operating system | Using a password file )
• Sufficient memory to start the instance and sufficient disk spack for the planned database
• Planning Database file Locations( Control Files , Online Redo Log Files , data files )
  • Keep at least two active copies of a database control file on at least two different devices.
  • Multiplex the redo log files and put group members on different disks.
  • separate data files whose data : ( participate in disk resource contention across different physical disk resource , have different life spans , have different administrative characteristics.
• 设置环境变量( UNIX )
  • ORACLE_BASE : Specfies the directory at the top of the Oracle software. 例如 : /u01, 标准 /u01/app/oracle
  • ORACLE_HOME : Specfies the directory where the Oracle software is installed ./oracle_base/oracle/product/10.2.0/db_1
  • ORACLE_SID : Specifies the instance name and must be unique for Oracle instances running on the same machine leon(最多只能 8 位)
  • ORA_NLS33 : 字符集相关内容 , 一般路径为 : $oracle_home/nls/admin/data
  • PATH : 执行命令路径 默认为 : $oracle_home/bin
  • LD_LIBRARY_PATH : Specifies the directory for the opearting system and Oracle library files .$oracle_home/lib

2.创建数据库

• DBCA
• 手工创建
  • Decide on a unique instance and database name
  • Choose a database character set
  • Set the operating system variables
  • Edit / Create the initialization parameter file
  • Start the instance ( nomount )
  • Execute the CREATE DATABASE command
  • Run scripts to generate the data dictionary and accomplish post creation steps
• 数据库类型
  • OLTP ( Online Transaction Processing , 常用的,包括 读, 写, 删除数据等等操作)
  • Data Warehousing ( 主要是处理 queries ( typically read-only )
  • Mixed ( 混合类型, 这种是默认的, OLTP + Data Warehousing 混合 )
• Complete Database Creation
  • Create database
  • Save as a database template - This template will then be added to the list of available templates
  • Generate database creation scripts - This option enables you to save the database creation parameters as a script file , for later use.
• create databae  manually proecess
  • decide on a unique instance and database name
  • choose a database character set
  • set the operationg system variables
  • Edit / Create the initialization parameter file
  • start the instance( nomount )
  • execute the create database command
  • run scripts to generate the data dictionary and accomplish post creation steps

create database 语法

CREATE DATABASE [ database ]         --> database 是数据库名字

  [ CONTROLFILE REUSE ]   

  [  LOGFILE [ GROUP integer ] filespec  ]

  [ MAXLOGFILES integer ]

  [ MAX LOGMEMBERS integer ]

  [ MAXLOGHISTORY integer ]

  [ MAXDATAFILES integer ]

  [ MAXINSTANCES integer ]

  [ ARCHIVELOG | NOARCHIVELOG ]

  [ CHARACTER SET charset ]

  [ NATIONAL CHARACTER SET charset ]

  [DATAFILE filespec [autoextend_clause ]]

  [DEFAULT TEMPORARY TABLESPACE tablespace filespec ]

  [UNDO TABLESPACE tablespace DATAFILE filespec [autoextend_clause]]

  [SET TIME_ZONE[ time_zone_region]]

 手工创建数据库时，创建的脚本是关键，找个老鸟，把他的脚本改一改就可以了

v$logfile, v$controlfile, v$datafile

刚刚创建完的数据库, 没有数据字典

OMF 简化创建 database 过程，就是将几个参数在参数化文件中，主要是文件夹路径，一次性初始化

OMF 个人觉得意义不大。

个人总结

本章的难点是手动创建数据库

CREATE DATABASE db01

logfile

GROUP 1 ('/u01/oradata/db01/log_01/db_01.rdo') SIZE 15M,

GROUP 2 ('/u01/oradata/db01/log_01/db_02.rdo') SIZE 15M,

GROUP 3 ('/u01/oradata/db01/log_01/db_03.rdo') SIZE 15M,

datafile '/u01/oradata/db01/system_01_db01.dbf' SIZE 100M

undo tablespace UNDO

datafile '/u01/oradata/db01/undo_01_db01.dbf' SIZE 40M

default temporary tablespace TEMP

tempfile '/u01/oradata/db01/temp_01_db01.dbf' SIZE 20M

extent management local uniform size 128K

character set AL32UTF8

national character set AL16UTF16

set time_zone = 'America/New_York'

;