准备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 # 完全恢复, 不完全恢复, 各种文件损坏的恢复 |