section 0
入口：Administrator's Guide->2 Creating an Oracle Database->Creating the Database
1.设置SID
export ORACLE_SID=devdb
echo $ORACLE_SID
2.创建目录
echo $ORACLE_BASE
mkdir -p $ORACLE_BASE/oradata/devdb
mkdir -p $ORACLE_BASE/admin/devdb/{a,b,c,u}dump
mkdir -p $ORACLE_BASE/archivelog/devdb
3.创建密码文件
cd $ORACLE_HOME/dbs
orapwd file=orapwdevdb password=oracle entries=30
ls
4.创建参数文件
考试时，先看$ORACLE_HOME/dbs 有没有参数文件模板，如果有则按如下方式生成你需要的参数文件。如果没有，则可
到文档中查找参数文件范例。位置如下：
入口：Administrator's Guide->2 Creating an Oracle Database->Understanding Initialization Parameters
cat init.ora |grep -v ^$ |grep -v ^# >>initdevdb.ora
ls
用vi编辑器编辑initdevdb.ora参数文件：
删掉
db_block_buffers = 100 # SMALL
shared_pool_size = 3500000 # SMALL
修改
db_name = devdb
processes = 150 //Section 1的1.2.2题
control_files =
(/u01/oracle/oradata/devdb/control01.ctl,/u01/oracle/oradata/devdb/control02.ctl,/u01/oracle/oradata/devdb/control03.ctl)
添加
undo_management = AUTO
sessions = 300
sga_target = 400M
sga_max_size = 400M
log_archive_dest_1 = "LOCATION=/u01/archivelog/PROD"
log_archive_dest_state_1 = enable
注意：根据机器的配置，设置sga_target、sga_max_size。设置太小，影响做题速度。
5.生成spfile
sqlplus / as sysdba
startup nomount pfile='/u01/oracle/product/10.2.0/db_1/dbs/initdevdb.ora'
create spfile from pfile;
shutdown abort;
6.手动建库
startup nomount;
将Step 7: Issue the CREATE DATABASE Statement的语句复制，去掉DEFAULT TABLESPACE tbs_1，修改后内容如下。主
要修改的是文件目录
CREATE DATABASE devdb
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY oracle
   LOGFILE GROUP 1 ('/u01/oracle/oradata/devdb/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/oracle/oradata/devdb/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/oracle/oradata/devdb/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   MAXINSTANCES 1
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/u01/oracle/oradata/devdb/system01.dbf' SIZE 325M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/u01/oracle/oradata/devdb/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/oracle/oradata/devdb/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/oracle/oradata/devdb/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

7.启用归档
shutdown immediate;
startup mount;
alter database archivelog;
alter database force logging;
alter database open;
archive log list;

Section1：数据库和网络设置

问题1 数据库设置、Undo 管理
1.1 运行基本脚本，配置数据库
入口：Administrator's Guide->2 Creating an Oracle Database->Creating the Database->Step 9: Run Scripts to
Build Data Dictionary Views
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
注意：如果时间够用，可以先做个冷备份
SQL> shutdown immediate;
[oracle@server01 devdb]$ cd /u01/oracle/oradata/devdb
[oracle@server01 devdb]$ cd ..
[oracle@server01 devdb]$ tar -czvf ../devdb.gz *
冷备之后，别忘记开启数据库
sqlplus / as sysdba
startup
1.2 设置UNDO 自动管理
1.2.1 调整undo 使快照保留90 分钟
解答1.2.1：调整undo表空间
show parameters undo
alter system set undo_retention=5400 scope=both;
select tablespace_name,retention from dba_tablespaces;
alter tablespace undotbs RETENTION GUARANTEE;

------------------------------------
创建临时表空间组
select file_name from dba_temp_files;
create temporary tablespace temp1 tempfile '/u01/oracle/oradata/devdb/temp01.dbf' size 10m tablespace group temp_gp;
create temporary tablespace temp2 tempfile '/u01/oracle/oradata/devdb/temp02.dbf' size 10m tablespace group temp_gp;
alter database default temporary tablespace temp_gp;
ALTER TABLESPACE temp1 TABLESPACE GROUP temp_gp;
ALTER TABLESPACE temp1 TABLESPACE GROUP '';

创建表空间example
select file_name from dba_data_files;
create tablespace example datafile '/u01/oracle/oradata/devdb/example01.dbf' size 400m autoextend on next 1m
extent management local uniform size 1m segment space management manual;
创建索引表空间indx
create tablespace INDX datafile '/u01/oracle/oradata/devdb/indx01.dbf' size 40m;
创建TOOLS表空间
create tablespace TOOLS datafile '/u01/oracle/oradata/devdb/tools01.dbf' size 10m;
创建USERS表空间
select * from database_properties;
create tablespace users datafile '/u01/oracle/oradata/devdb/users01.dbf' size 48m
autoextend on next 4m
extent management local uniform size 4m;
alter database default tablespace users;
创建表空间OLTP
create tablespace oltp datafile '/u01/oracle/oradata/devdb/oltp01.dbf' size 48m
autoextend on next 2m
extent management local uniform size 2m
segment space management auto;

创建大数据文件表空间
create bigfile tablespace dss datafile '/u01/oracle/oradata/devdb/dss01.dbf' size 48m
autoextend on next 2m
maxsize 4T;
日志文件管理
冗余redo日志
select group#,status member from v$logfile;
select gourp#,bytes/1024/1024 mb,status from v$log;
向现有日志组添加成员
alter database add logfile member '/u01/oracle/oradata/devdb/redo102.log' to group 1;
alter database add logfile member '/u01/oracle/oradata/devdb/redo202.log' to group 2;
alter database add logfile member '/u01/oracle/oradata/devdb/redo303.log' to group 3;
alter database add logfile group 4 ('/u01/oracle/oradata/devdb/redo4.log','/u01/oracle/oradata/devdb/redo402.log') size 100m;
方案二删除现有redo，重新添加
select group#,status,member,bytes/1024/1024 mb from v$log;
删除状态为inactive 的日志组
切换日志
alter system switch logfile;
删除日志组
alter database drop logfile group 2;
alter database drop logfile group 3;
删除之后，磁盘文件并未删除，需要在操作系统上删除对应的redo文件。
数据库至少需要保留两组redo日志，因此需要在删除日志组的同时，添加符合题目要求的日志组
添加日志组
alter database logfile group 2('/u01/oracle/oradata/devdb/redo21.log','/u01/oracle/oradata/devdb/redo22.log') size 100m;
alter database logfile group 3('/u01/oracle/oradata/devdb/redo31.log','/u01/oracle/oradata/devdb/redo32.log') size 100m;
冗余控制文件
1、创建静态参数文件
SQL> create pfile='/u01/oradata/PROD/initPROD.ora' from spfile;
File created.
2、关闭数据库
3、复制控制文件到多个磁盘
4、修改静态参数文件的control_files部分，指定多个控制文件的位置。
5、用静态参数文件启动数据库
SQL> startup nomount pfile='/u01/oradata/PROD/initPROD.ora';
SQL> show parameters control;
6、创建动态数据库参数，并用动态数据库参数文件启动数据库
SQL> create spfile from pfile='/u01/oradata/PROD/initPROD.ora';
File created.
SQL> shutdown immediate;
SQL> startup
SQL> show parameters pfile;

收集数据库统计信息
exec dbms_stats.gather_database_stats(degree=>5);
设置UTL_FILE_DIR参数
alter system set UTL_FILE_DIR='/home/oracle','/home/oracle/temp','/home/oracle/scripts' scope=spfile;