Oracle手工建库

 

准备Oracle的课程
计划以手工建库的方式引出Oracle的各个知识点

[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个字符，且只能包含数字和字母，在某些平台上，SID是大小写敏感的。
 ORACLE_SID=mysid
 export ORACLE_SID
 
 # sid, db_name, db_unique_name, global_name, service_names, instance_name

 

2. 确保必需的环境变量都已经设置
 ORACLE_SID/ORACLE_HOME是必需设置的。
 建议设置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. 选择数据库认证方式
 a) 密码文件认证
 b) 操作系统认证

 # orapwd, sqlnet.ora
 
4. 创建初始化参数文件
 参数文件分为pfile和spfile(server parameter file)两种
 最简单的参数文件只需
  DB_NAME=mydb # 数据库名最多支持8个字符
  
 # pfile, spfile, v$parameters, v$system_parameters, v$spparameters, create spfile from pfile, create pfile from memory(11g only)

 

5. 连接实例
 $ sqlplus /nolog
 SQL> conn / as sysdba
 
 $ sqlplus / as sysdba

6. 启动实例
 startup nomount [pfile='xxxxx']
 
 # nomount, mount, open
 
7. 执行create database的语句
 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. 创建数据库字典
 @?/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. 备份数据库
 Oracle建议，完成数据库的创建过，进行一次数据库的全备(OCM考试有要求)
 
 # 冷备，热备，RMAN
 # 完全恢复， 不完全恢复， 各种文件损坏的恢复