Oracle管理与维护.手工创建数据库以及脚本

 Oracle管理与维护.手工创建数据库以及脚本

 

 

转自 草木瓜

 

一、前言

 说实话，早已厌倦了使用dbca的创建数据库，大概比较有点作用的是可以生成创建脚本罢。
《Oracle管理与维护.安装创建启动关闭数据库的常见问题(一)》(三、创建数据库)一文中有所提及。本文主要就是详述手
工使用脚本创建Oracle 10g数据库。

二、安装Oracle 10g软件

 操作系统环境：
 
 [root@liwei root]# uname -a
 Linux liwei 2.4.20-8 #1 Thu Mar 13 17:54:28 EST 2003 i686 i686 i386 GNU/Linux
 
 [root@liwei root]#  cat /etc/redhat-release
 Red Hat Linux release 9 (Shrike)
 
 虚拟机：
 
 VMware WorkStation 6.0.3 build-80004
 
 安装略过，注意三点：
 
 a.修改Disk1/install/oraparam.ini 见
 《虚拟机VMware安装OracleRAC.RedHat Enterprise AS4安装Oracle 9206 Patch》一文<问题二>
 b.只安装软件不创建数据库
 c.须安装libaio-0.3.93-4.i386.rpm(redhat9 第二张盘)，否则提示如下错误信息
 
 [oracle@liwei liwei]$ sqlplus /nolog
 
 SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 26 05:47:54 2008
 
 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 SQL> conn / as sysdba
 oracleliwei: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
 ERROR:
 ORA-12547: TNS:lost contact

redhat 9 第二张盘

[root@liwei mnt]# umount cdrom
[root@liwei mnt]# mount /dev/hdc cdrom
mount: block device /dev/hdc is write-protected, mounting read-only
mount: No medium found
[root@liwei mnt]# find . -name '*aio*'
[root@liwei mnt]# mount /dev/hdc cdrom
mount: block device /dev/hdc is write-protected, mounting read-only
[root@liwei mnt]# find . -name '*aio*'
./cdrom/RedHat/RPMS/libaio-0.3.93-4.i386.rpm
./cdrom/RedHat/RPMS/libaio-devel-0.3.93-4.i386.rpm
[root@liwei mnt]# rpm -ivh libaio-0.3.93-4.i386.rpm
error: open of libaio-0.3.93-4.i386.rpm failed: No such file or directory
[root@liwei mnt]# rpm -ivh ./cdrom/RedHat/RPMS/libaio-0.3.93-4.i386.rpm
warning: ./cdrom/RedHat/RPMS/libaio-0.3.93-4.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e
Preparing...                ########################################### [100%]
   1:libaio                 ########################################### [100%]
[root@liwei mnt]#

三、创建密码验证文件

orapwd file=<ORACLE_HOME>/dbs/orapw<sid> password=sys entries=10
注意：密码文件命名与目录推荐使用Oracle默认目录，否则Oracle无法识别。笔者还没找到修
改缺省目录的方法。

[oracle@liwei liwei]$ orapwd file=/oracle/db/dbs/orapwliwei password=sys entries=10

[oracle@liwei liwei]$ orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>

  where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA and     force - whether to overwrite existing file (opt),
OPERs (opt),
  There are no spaces around the equal-to (=) character.

四、创建初始化参数文件

注意：笔者单独创建目录/oracle/data/liwei用于存放数据库相关的所有文件

创建initliwei.ora文件，内容如下：

*.compatible='10.2.0.1.0'
*.db_domain=''
*.db_name='liwei'
*.db_block_size=8192
*.db_file_multiblock_read_count=32
*.dispatchers='(PROTOCOL=TCP) (SERVICE=liwei)'
*.job_queue_processes=10
*.open_cursors=100
*.processes=150
*.sessions=200

*.remote_login_passwordfile='EXCLUSIVE'
*.star_transformation_enabled='TRUE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

*.sga_target=134217728
*.pga_aggregate_target=10485760
*.db_cache_size=10485760
*.java_pool_size=1048576
*.large_pool_size=1048576
*.shared_pool_size=67108864
*.streams_pool_size=0

*.audit_file_dest='/oracle/data/liwei/audit_file_dest'
*.background_dump_dest='/oracle/data/liwei/background_dump_dest'
*.core_dump_dest='/oracle/data/liwei/core_dump_dest'
*.user_dump_dest='/oracle/data/liwei/user_dump_dest'
*.control_files='/oracle/data/liwei/control/CONTROL01.CTL','/oracle/data/liwei/control/CONTROL02.CTL','/oracle/data/liwei/control/CONTROL03.CTL'
*.db_recovery_file_dest='/oracle/data/liwei/db_recovery_file_dest'
*.db_recovery_file_dest_size=104857600

SQL> startup pfile=/oracle/data/liwei/initliwei.ora nomount
ORACLE instance started.

Total System Global Area  134217728 bytes
Fixed Size                  1218124 bytes
Variable Size              75499956 bytes
Database Buffers           54525952 bytes
Redo Buffers                2973696 bytes
SQL>

注意：

1.共享池不能太小
2.最后系列目录必须提前建好
3.具体参数含义可参考Oracle在线文档

以下列出一些参数设置错误后的提示：

 SQL> conn / as sysdba
 Connected to an idle instance.
 
 SQL> startup pfile=/oracle/data/liwei/initliwei.ora nomount
 ORA-09925: Unable to create audit trail file
 Linux Error: 2: No such file or directory
 Additional information: 9925
  原因：audit_file_dest参数对应的目录没有
 
 SQL> startup pfile=/oracle/data/liwei/initliwei.ora nomount
 ORA-07446: sdnfy: bad value '/oracle/data/liwei/user_dump_dest' for parameter user_dump_dest.
  原因：user_dump_dest参数对应的目录没有
 
 SQL> startup pfile=/oracle/data/liwei/initliwei.ora nomount
 ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
 ORA-01262: Stat failed on a file destination directory
 Linux Error: 2: No such file or directory
  原因：db_recovery_file_dest参数对应的目录没有
 
 SQL> startup pfile=/oracle/data/liwei/initliwei.ora nomount
 ORA-04031: unable to allocate 2048 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","library cache")
  原因：shared_pool_size设置太小
 
 SQL>  startup pfile=/oracle/data/liwei/initliwei.ora nomount
 ORA-00821: Specified value of sga_target 52M is too small, needs to be at least 56M
  原因：shared_pool_size与sga_target大小不匹配
 
 SQL> startup pfile=/oracle/data/liwei/initliwei.ora nomount
 ORA-00444: background process "MMAN" failed while starting
 ORA-07446: sdnfy: bad value '' for parameter .
  原因：shared_pool_size相关设置有误
 

五、创建数据库

create database liwei
maxinstances 4
maxloghistory 2
maxlogfiles 16
maxlogmembers  2
maxdatafiles 8
character set zhs16gbk
national character set al16utf16
datafile '/oracle/data/liwei/oradata/system.dbf' size 20m autoextend on next 1m maxsize unlimited
sysaux datafile '/oracle/data/liwei/oradata/sysaux.dbf' size 20m autoextend on next 1m maxsize unlimited
default temporary tablespace temp tempfile '/oracle/data/liwei/oradata/temp.dbf' size 20m extent management local uniform size 32k
undo tablespace undotbs1 datafile '/oracle/data/liwei/oradata/undotbs1.dbf' size 20m
logfile
group 1 ('/oracle/data/liwei/oradata/redo0101.dbf') size 10m reuse,
group 2 ('/oracle/data/liwei/oradata/redo0201.dbf') size 10m reuse;

具体过程如下：

[oracle@liwei liwei]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 1 09:13:10 2008

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile=/oracle/data/liwei/initliwei.ora nomount
ORACLE instance started.

Total System Global Area  134217728 bytes
Fixed Size                  1218124 bytes
Variable Size              75499956 bytes
Database Buffers           54525952 bytes
Redo Buffers                2973696 bytes
SQL>
SQL> create database liwei
  2  maxinstances 4
  3  maxloghistory 2
  4  maxlogfiles 16
  5  maxlogmembers  2
  6  maxdatafiles 8
  7  character set zhs16gbk
  8  national character set al16utf16
  9  datafile '/oracle/data/liwei/oradata/system.dbf' size 20m autoextend on next 1m maxsize unlimited
 10  sysaux datafile '/oracle/data/liwei/oradata/sysaux.dbf' size 20m autoextend on next 1m maxsize unlimited
 11  default temporary tablespace temp tempfile '/oracle/data/liwei/oradata/temp.dbf' size 20m extent management local uniform size 32k
 12  undo tablespace undotbs1 datafile '/oracle/data/liwei/oradata/undotbs1.dbf' size 20m
 13  logfile
 14  group 1 ('/oracle/data/liwei/oradata/redo0101.log') size 10m reuse,
group 2 ('/oracle/data/liwei/oradata/redo0201.log') size 10m reuse; 15 

Database created.

SQL>

此时可以连接数据库(通过system/manager)，但不能通过sqlplus  "sys/change_on_install@unixdb
as sysdba"进行连接，创建后短时会有不能解析服务名的提示，但没有任何数据库的内部字典与
视图。

创建数据库后，如果须要删除重新创建，方法有二。
1、可删除对应目录的数据库文件(/oracle/data/liwei/oradata)重新create database即可。
2、Oracle 10g新提供了drop database的功能。

[oracle@liwei oradata]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 1 09:07:17 2008

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL>  startup restrict exclusive mount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/db/dbs/initliwei.ora'
SQL> startup pfile=/oracle/data/liwei/initliwei.ora restrict exclusive mount;
ORACLE instance started.

Total System Global Area  134217728 bytes
Fixed Size                  1218124 bytes
Variable Size              75499956 bytes
Database Buffers           54525952 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

六、创建数据库内部对象

脚本说明
catalog.sql 创建系统常用的数据字典视图和同义词

 SQL>@ ?/rdbms/admin/catalog.sql;
 
 数据库内部表和视图已创建，但唯一缺少表空间相关视图，可以创建表空间
 但PLSQL会提示SYS.DBMS_OUTPUT之类包体未定义。

脚本说明
catproc.sql 运行服务器端所需要的PL/SQL脚本

 SQL>@ ?/rdbms/admin/catproc.sql;
  创建服务端所需要的PL/SQL脚本，创建系统包与函数，创建表空间相关视图字典。

脚本catalog.sql和catproc.sql 是创建数据库后必须要运行的两个脚本。

其中这类脚本也调用了其他脚本：
standard.sql Load PL/SQL Package STANDARD first, so views can depend upon it
catcdc.sql change data capture views
cataudit.sql Auditing views
catexp.sql Import/export views
catldr.sql Loader views
catpart.sql Partitioning views
catadt.sql Object views
catsum.sql Summary views
catxtb.sql External Table views
catexp7.sql Create v7 style export/import views against the v8 RDBMS, so that EXP/IMP v7 can be used to read out data in a v8 RDBMS
catblock.sql create views of oracle locks
catproc.sql Run all sql scripts for the procedural option
catoctk.sql Contains scripts needed to use the PL/SQL Cryptographic Toolkit
catobtk.sql Contains scripts needed to use the PL/SQL Obfuscation Toolkit
caths.sql Create Heterogeneous Services data dictionary objects
owminst.plb this package containa all the functions and procedures that need to be fun under the SYS user privilege ---this package has definers rights. So ,all the procedures which modify system tables live in this package.
pubbld.sql Script to install the SQL*Plus PRODUCT_USER_PROFILE tables. These tables allow SQL*Plus to disable commands per user. The tables are used only by SQL*Plus and do not affect other client tools- that access the database. Refer to the SQL*Plus manual for table usage information.

-- This script should be run on every database that SQL*Plus connects to, even if the tables are not used to restrict commands.
-- USAGE -- Connect as sysdba before running this script

七、自动建库说明

 dbca可以生成建库脚本，这个功能十分有用，不过需要提示的是，创建数据库会出现各类问题，解决
思路如下：
 
 1.$ORACLE_HOME/cfgtoollogs 查看dbca日志
 2.dbca生成建库脚本，并对脚本进行手工修改，便于测试跟踪
 3.手工执行建库脚本进行跟踪
 
 如：ORA-01092: ORACLE instance terminated. Disconnection forced 其实根源问题在于create
 database 语句的参数 EXTENT MANAGEMENT LOCAL 有误，去掉此内容即可。以下是错误重
 现示例。

 [oracle@liwei scripts]$ sh test.sh
 You should Add this entry in the /etc/oratab: test:/oracle/db:Y
 
 SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 1 14:19:57 2008
 
 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 specify a password for sys as parameter 1
 Enter value for 1: test
 specify a password for system as parameter 2
 Enter value for 2: test
 
 Connected to an idle instance.
 SQL> spool /oracle/admin/test/scripts/CreateDB.log
 SQL> startup nomount pfile="/oracle/admin/test/scripts/init.ora";
 ORACLE instance started.
 
 Total System Global Area  167772160 bytes
 Fixed Size                  1218316 bytes
 Variable Size              62916852 bytes
 Database Buffers          100663296 bytes
 Redo Buffers                2973696 bytes
 SQL> CREATE DATABASE "test"
   2  MAXINSTANCES 8
   3  MAXLOGHISTORY 1
   4  MAXLOGFILES 16
   5  MAXLOGMEMBERS 3
   6  MAXDATAFILES 100
   7  DATAFILE '/oracle/oradata/test/system01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT     10240K MAXSIZE UNLIMITED
   8  EXTENT MANAGEMENT LOCAL
   9  SYSAUX DATAFILE '/oracle/oradata/test/sysaux01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
  10  SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oracle/oradata/test/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
  11  SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/oracle/oradata/test/undotbs01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
  12  CHARACTER SET WE8ISO8859P1
  13  NATIONAL CHARACTER SET AL16UTF16
  14  LOGFILE GROUP 1 ('/oracle/oradata/test/redo01.log') SIZE 25600K,
  15  GROUP 2 ('/oracle/oradata/test/redo02.log') SIZE 25600K
  16  USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
 CREATE DATABASE "test"
 *
 ERROR at line 1:
 ORA-01092: ORACLE instance terminated. Disconnection forced
 
 可见手工建库才是最为重要而又实用的方法，即所谓的“王道”。

八、创建测试使用用户与表空间等对象
 
 示例语句：

 create tablespace usertest datafile '/oracle/data/liwei/oradata/usertest.dbf' size 100m uniform size 64k;
 create user test identified by test default tablespace usertest;
 grant create session to test;
 grant create table to test;
 alter user test quota unlimited  on usertest;
 create table test.student_info
 (
 student_id number(10),
 student_name varchar2(20),
 student_note varchar2(10)
 );
 select * from test.student_info;
 insert into test.student_info values (0,'TEST','note');
 commit;

九、下文提示

 下文着重讨论Oracle的身份登录与验证问题，如下类问题：

 [oracle@liwei oracle]$ sqlplus / as sysdba
 
 SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 1 14:51:59 2008
 
 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 ERROR:
 ORA-01031: insufficient privileges

 [oracle@liwei oracle]$ sqlplus sys/test@unixdb as sysdba
 
 SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 1 14:52:52 2008
 
 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 ERROR:
 ORA-01017: invalid username/password; logon denied
 
 
 C:/Documents and Settings/Administrator>sqlplus  "system/manager@unixdb as sysdba"
 
 SQL*Plus: Release 9.2.0.1.0 - Production on Mon May 5 12:42:59 2008
 
 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 ERROR:
 ORA-01031: insufficient privileges