oracle手动建库

最近参加oracle认证培训，老师讲了手动建库的方法，平时的操作不太系统，今天就顺便系统总结一下：
1.创建必要的目录
2.初始化参数文件
3.执行建库语句
4.执行必要脚本

1.首先创建必要的目录

[oracle@DB ~]$ cd $ORACLE_BASE/oradata
[oracle@DB oradata]$ mkdir BOB
[oracle@DB BOB]$ mkdir disk1 disk2 disk3 disk4 disk5 bdump cdump udump
[oracle@DB BOB]$ ll
total 32
drwxr-xr-x 2 oracle oinstall 4096 Jul  7 09:53 bdump
drwxr-xr-x 2 oracle oinstall 4096 Jul  7 09:44 cdump
drwxr-xr-x 2 oracle oinstall 4096 Jul  7 15:34 disk1
drwxr-xr-x 2 oracle oinstall 4096 Jul  7 15:34 disk2
drwxr-xr-x 2 oracle oinstall 4096 Jul  7 15:56 disk3
drwxr-xr-x 2 oracle oinstall 4096 Jul  7 15:35 disk4
drwxr-xr-x 2 oracle oinstall 4096 Jul  7 15:35 disk5
drwxr-xr-x 2 oracle oinstall 4096 Jul  7 09:44 udump

2.创建初始化参数文件
oracle参数文件有pfile和spfile两种，spfile是二进制文件，pfile是文本文件，

查看数据库是用pfile启动还是spfile启动的方式为

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora

如果VALUE处是空的，就说明是按照pfile来启动的，发现首次安装oracle 11g软件并建库后是生成了spfile，没有生成pfile，可以手动spfile生成pfile.

SQL> create pfile from spfile;

当生成了pfile后，若想切换用pfile来启动数据库，有两种方式：

手动指定初始化参数文件
SQL> startup pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora --这是第一种方法
修改spfile的名字，然后再启动数据库。

扯远了，下面开始创建初始化参数文件
oracle的初始化参数文件一般存放在$ORACLE_HOME/dbs下面：

[oracle@DB BOB]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@DB dbs]$ vi initBOB.ora ---文件内容如下
sga_target=400m
db_name=BOB
control_files='/u01/app/oracle/oradata/BOB/disk1/control01.ctl',
'/u01/app/oracle/oradata/BOB/disk2/control01.ctl','/u01/app/oracle/oradata/BOB/disk3/control01.ctl',
undo_management=auto
undo_tablespace=undotbs
user_dump_dest=/u01/app/oracle/oradata/BOB/udump
background_dump_dest=/u01/app/oracle/oradata/BOB/bdump
core_dump_dest=/u01/app/oracle/oradata/BOB/cdump

3.执行建库语句
现在就可以把BOB这个库启动到nomount阶段

[oracle@DB dbs]$ export ORACLE_SID=BOB
[oracle@DB dbs]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 7 23:25:46 2013


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.


Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             134219728 bytes
Database Buffers          272629760 bytes
Redo Buffers                8482816 bytes
SQL> select instance_name,status from v$instance;


INSTANCE_NAME    STATUS
---------------- ------------
BOB              STARTED

启动到nomount阶段之后，接下来就可以执行建库语句了
oracle 官方给出的建库语句如下：

CREATE DATABASE mynewdb
   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

根据自己的环境修改如下：

SQL> CREATE DATABASE BOB
       LOGFILE GROUP 1 ('/u01/app/oracle/oradata/BOB/disk1/redo01.log',
                        '/u01/app/oracle/oradata/BOB/disk2/redo01.log') SIZE 100M,
               GROUP 2 ('/u01/app/oracle/oradata/BOB/disk1/redo02.log',
                        '/u01/app/oracle/oradata/BOB/disk2/redo02.log') SIZE 100M,
               GROUP 3 ('/u01/app/oracle/oradata/BOB/disk1/redo03.log',
                        '/u01/app/oracle/oradata/BOB/disk2/redo03.log') SIZE 100M
       CHARACTER SET zhs16gbk
       NATIONAL CHARACTER SET AL16UTF16
      DATAFILE '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
      EXTENT MANAGEMENT LOCAL
      SYSAUX DATAFILE '/u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf' SIZE 325M REUSE
      DEFAULT TEMPORARY TABLESPACE tempts
         TEMPFILE '/u01/app/oracle/oradata/BOB/disk5/temp01.dbf'
         SIZE 20M REUSE
      UNDO TABLESPACE undotbs
         DATAFILE '/u01/app/oracle/oradata/BOB/disk4/undotbs01.dbf'
         SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Database created.

上述语句的注意事项：

Tips:

• Ensure that all directories used in the CREATE DATABASE statement exist. The CREATE DATABASE statement does not create directories.

• If you are not using Oracle Managed Files, then every tablespace clause must include a DATAFILE or TEMPFILE clause.

• If database creation fails, then you can look at the alert log to determine the reason for the failure and to determine corrective actions. See "Viewing the Alert Log". If you receive an error message that contains a process number, then examine the trace file for that process. Look for the trace file that contains the process number in the trace file name. See "Finding Trace Files" for more information.

• To resubmit the CREATE DATABASE statement after a failure, you must first shut down the instance and delete any files created by the previous CREATE DATABASE statement.

在此过程中出现什么错误，都可以监控log日志来查看，查看日志文件所在目录用如下命令：

SQL> show parameter dump;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/bob/BOB/trace
core_dump_dest                       string      /u01/app/oracle/oradata/BOB/cdump
max_dump_file_size                   string      unlimited
shadow_core_dump                     string      partial
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/bob/BOB/trace

可以看到日志文件存放在/u01/app/oracle/diag/rdbms/bob/BOB/trace 目录下
进入找到alert_BOB.log文件即可跟踪日志信息。

4.执行脚本来创建数据字典视图及同义词以及PL/SQL包等

SQL> @?/rdbms/admin/catalog  ---创建数据字典表的视图，动态性能视图，以及一些视图的公共同义词
SQL> @?/rdbms/admin/catproc  ---创建DBMS过程
SQL> conn system/manager
Connected.
SQL> @?/sqlplus/admin/pupbld ---让sqpplus 可以使用其他用户登录

oracle官方的说明如下：

Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:
跑如下脚本来创建数据字典视图，同义词以及PL/SQL包，从而来支持SQL*Plus的正常工作：

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
EXIT

The at-sign (@) is shorthand for the command that runs a SQL*Plus script. The question mark (?) is a SQL*Plus variable indicating the Oracle home directory. The following table contains descriptions of the scripts:
@符号表示的是运行SQL*Plus脚本的简写命令，?是一个SQL*Plus变量表示ORACLE HOME目录.下面是具体的脚本描述信息

如果没有报错，就可以查看一下目前数据库的状态等信息了：

SQL> conn /as sysdba
Connected.
SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
BOB              OPEN

1 row selected.

SQL> select tablespace_name,contents,status from dba_tablespaces;

TABLESPACE_NAME                CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                         PERMANENT ONLINE
UNDOTBS                        UNDO      ONLINE
SYSAUX                         PERMANENT ONLINE
TEMPTS                         TEMPORARY ONLINE

4 rows selected.

到此，手动建库成功执行。