Oracle手工建库
<DIV> </DIV><DIV>准备Oracle的课程<BR>计划以手工建库的方式引出Oracle的各个知识点</DIV>
<DIV><BR>$ cat /etc/redhat-release <BR>CentOS release 5.6 (Final)</DIV>
<DIV>SQL> select * from v$version where rownum<2;</DIV>
<DIV>BANNER<BR>--------------------------------------------------------------------------------<BR>Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production</DIV>
<DIV> </DIV>
<DIV>1. 定义SID(system identifier)<BR> SID最多支持12个字符，且只能包含数字和字母，在某些平台上，SID是大小写敏感的。<BR> ORACLE_SID=mysid<BR> export ORACLE_SID<BR> <BR> # sid, db_name, db_unique_name, global_name, service_names, instance_name</DIV>
<DIV> </DIV>
<DIV>2. 确保必需的环境变量都已经设置<BR> ORACLE_SID/ORACLE_HOME是必需设置的。<BR> 建议设置PATH包含ORACLE_HOME/bin目录<BR> ORACLE_HOME=/u01/app/oracle/product/11gR2<BR> ORACLE_SID=mysid<BR> export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 PATH=/db/oracle/app/oracle/product/11gR2/bin:$PATH<BR> export ORACLE_HOME ORACLE_SID PATH<BR> <BR> # NLS_LANG, character set, v$nls_parameters, data migration<BR> <BR>3. 选择数据库认证方式<BR> a) 密码文件认证<BR> b) 操作系统认证</DIV>
<DIV> # orapwd, sqlnet.ora<BR> <BR>4. 创建初始化参数文件<BR> 参数文件分为pfile和spfile(server parameter file)两种<BR> 最简单的参数文件只需<BR> DB_NAME=mydb # 数据库名最多支持8个字符<BR> <BR> # pfile, spfile, v$parameters, v$system_parameters, v$spparameters, create spfile from pfile, create pfile from memory(11g only)</DIV>
<DIV> </DIV>
<DIV>5. 连接实例<BR> $ sqlplus /nolog<BR> SQL> conn / as sysdba<BR> <BR> $ sqlplus / as sysdba</DIV>
<DIV>6. 启动实例<BR> startup nomount <BR> <BR> # nomount, mount, open<BR> <BR>7. 执行create database的语句<BR> CREATE DATABASE mydb<BR> USER SYS IDENTIFIED BY sys_password<BR> USER SYSTEM IDENTIFIED BY system_password<BR> LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512,<BR> GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512,<BR> GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512<BR> MAXLOGFILES 5<BR> MAXLOGMEMBERS 5<BR> MAXLOGHISTORY 1<BR> MAXDATAFILES 100<BR> CHARACTER SET US7ASCII<BR> NATIONAL CHARACTER SET AL16UTF16<BR> EXTENT MANAGEMENT LOCAL<BR> DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE<BR> SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE<BR> DEFAULT TABLESPACE users<BR> DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'<BR> SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED<BR> DEFAULT TEMPORARY TABLESPACE tempts1<BR> TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'<BR> SIZE 20M REUSE<BR> UNDO TABLESPACE undotbs<BR> DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'<BR> SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;<BR> <BR> 定义： DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'</DIV>
<DIV> </DIV>
<DIV> # sys/system user, redo logfile, system/sysaux tablespace, default tablespace, temporary tablespace/tempfile, undo tablespace<BR> # file system, raw, ASM<BR> <BR>8. 创建数据库字典<BR> @?/rdbms/admin/catalog.sql # <BR> @?/rdbms/admin/catproc.sql # Runs all scripts requried for or used with PL/SQL<BR> @?/sqlplus/admin/pupbld.sql # PUBBLD stands for "Product User Profile BuiLD". <BR> <BR> # at-sign(@) is shorthand for the command that runs a SQL*Plus script<BR> # question mark(?) is a SQL*Plus variable indicating the Oracle home directory<BR> <BR> # dict, v$fixed_tables<BR> <BR>9. 备份数据库<BR> Oracle建议，完成数据库的创建过，进行一次数据库的全备(OCM考试有要求)<BR> <BR> # 冷备，热备，RMAN<BR> # 完全恢复， 不完全恢复， 各种文件损坏的恢复</DIV>
页:
[1]