Oracle手工建庫(kù)

huayd_cu

 

準(zhǔn)備Oracle的課程
計(jì)劃以手工建庫(kù)的方式引出Oracle的各個(gè)知識(shí)點(diǎn)

[oracle@localhost ~]$ cat /etc/redhat-release
CentOS release 5.6 (Final)

SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

 

1. 定義SID(system identifier)
 SID最多支持12個(gè)字符，且只能包含數(shù)字和字母，在某些平臺(tái)上，SID是大小寫(xiě)敏感的。
 ORACLE_SID=mysid
 export ORACLE_SID
 
 # sid, db_name, db_unique_name, global_name, service_names, instance_name

 

2. 確保必需的環(huán)境變量都已經(jīng)設(shè)置
 ORACLE_SID/ORACLE_HOME是必需設(shè)置的。
 建議設(shè)置PATH包含ORACLE_HOME/bin目錄
 ORACLE_HOME=/u01/app/oracle/product/11gR2
 ORACLE_SID=mysid
 export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 PATH=/db/oracle/app/oracle/product/11gR2/bin:$PATH
 export ORACLE_HOME ORACLE_SID PATH
 
 # NLS_LANG, character set, v$nls_parameters, data migration
 
3. 選擇數(shù)據(jù)庫(kù)認(rèn)證方式
 a) 密碼文件認(rèn)證
 b) 操作系統(tǒng)認(rèn)證

 # orapwd, sqlnet.ora
 
4. 創(chuàng)建初始化參數(shù)文件
 參數(shù)文件分為pfile和spfile(server parameter file)兩種
 最簡(jiǎn)單的參數(shù)文件只需
  DB_NAME=mydb # 數(shù)據(jù)庫(kù)名最多支持8個(gè)字符
  
 # pfile, spfile, v$parameters, v$system_parameters, v$spparameters, create spfile from pfile, create pfile from memory(11g only)

 

5. 連接實(shí)例
 $ sqlplus /nolog
 SQL> conn / as sysdba
 
 $ sqlplus / as sysdba

6. 啟動(dòng)實(shí)例
 startup nomount [pfile='xxxxx']
 
 # nomount, mount, open
 
7. 執(zhí)行create database的語(yǔ)句
 CREATE DATABASE mydb
    USER SYS IDENTIFIED BY sys_password
    USER SYSTEM IDENTIFIED BY system_password
    LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512,
      GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512,
      GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXLOGHISTORY 1
    MAXDATAFILES 100
    CHARACTER SET US7ASCII
    NATIONAL CHARACTER SET AL16UTF16
    EXTENT MANAGEMENT LOCAL
    DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
    SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
    DEFAULT TABLESPACE users
    DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
    SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE tempts1
    TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
    SIZE 20M REUSE
    UNDO TABLESPACE undotbs
    DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
   
 定義： DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'

 

 # sys/system user, redo logfile, system/sysaux tablespace, default tablespace, temporary tablespace/tempfile, undo tablespace
 # file system, raw, ASM
  
8. 創(chuàng)建數(shù)據(jù)庫(kù)字典
 @?/rdbms/admin/catalog.sql  #
 @?/rdbms/admin/catproc.sql  # Runs all scripts requried for or used with PL/SQL
 @?/sqlplus/admin/pupbld.sql  # PUBBLD stands for "Product User Profile BuiLD". 
 
 # at-sign(@) is shorthand for the command that runs a SQL*Plus script
 # question mark(?) is a SQL*Plus variable indicating the Oracle home directory
 
 # dict, v$fixed_tables
 
9. 備份數(shù)據(jù)庫(kù)
 Oracle建議，完成數(shù)據(jù)庫(kù)的創(chuàng)建過(guò)，進(jìn)行一次數(shù)據(jù)庫(kù)的全備(OCM考試有要求)
 
 # 冷備，熱備，RMAN
 # 完全恢復(fù)， 不完全恢復(fù)， 各種文件損壞的恢復(fù)