准备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
# 完全恢复, 不完全恢复, 各种文件损坏的恢复
阅读(527) | 评论(0) | 转发(0) |