mysql手工创建数据库_手工创建数据库及删除数据库示例

首先是应对ORACLE_SID,DB_NAME,存放数据文件、REDO日志、控制文件等的目录进行规划。 如我的实验中：ORACLE_SID=bys3，DB_NAME为bys3，数据文件、REDO日志、控制文件均存放在/u01/oradata/bys3/ 实验如下: 环境：OEL5.7,ORACLE 11.2.0.4,VBOX虚拟机--建库前OR

首先是应对ORACLE_SID,DB_NAME,存放数据文件、REDO日志、控制文件等的目录进行规划。

如我的实验中：ORACLE_SID=bys3，DB_NAME为bys3，数据文件、REDO日志、控制文件均存放在/u01/oradata/bys3/

实验如下:

环境：OEL5.7,ORACLE 11.2.0.4,VBOX虚拟机--建库前ORACLE软件要装好唉。。

1.设置ORACLE_SID

可以通过.bash_profile里查看--更改未退出会话时需要source .bash_profile使之生效，

或者直接在命令行中用：export ORACLE_SID=bys3

echo $ORACLE_SID来验证一下。

2.再次确定一下.bash_profile里的各个变量参数是否正确

cat .bash_profile 进行查看

[oracle@bys3 ~]$ cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

RACLE_BASE=/u01

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

ORACLE_SID=bys3

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:$HOME/bin

export PATH ORACLE_BASE ORACLE_HOME ORACLE_SID

export NLS_LANG=AMERICAN

export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'

alias sqlplus='rlwrap sqlplus'

alias rman='rlwrap rman'

ORACLE_HOSTNAME=bys3.bys.com

export ORACLE_HOSTNAME

3.创建密码文件--注意WINDOWS下和LINUX下的密码文件格式是不一样的

注意：WIN下文件夹名和LINUX下不一样。WIN下$ORACLE_HOME/database LINUX下是：$ORACLE_HOME\dbs\

如实例名为orcl,则WINDOWS下密码文件格式为：PWDorcl.ORA LINUX下为orapworcl

orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwbys3 password=oracle entries=3 force=y

4.创建相应目录--根据自己计划的数据文件、TRACE等文件的位置进行调整。

可以写成类似下面的脚本进行调用

[oracle@bys3 ~]$ cat createdir.sh

#!/bin/sh

OLD_UMASK=`umask`

umask 0027

mkdir -p /u01/admin/bys3/adump

mkdir -p /u01/admin/bys3/dpdump

mkdir -p /u01/admin/bys3/pfile

mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1/dbs

mkdir -p /u01/archivelog

mkdir -p /u01/cfgtoollogs/dbca/bys3

mkdir -p /u01/fast_recovery_area

mkdir -p /u01/fast_recovery_area/bys3

mkdir -p /u01/oradata/bys3

umask ${OLD_UMASK}

5.创建初始化参数文件--仅设置以下两条参数即可-db_name，controlfile

vi /home/oracle/bys.ora

db_name=bys3

control_files='/u01/oradata/bys3/control01.ctl','/u01/oradata/bys3/control02.ctl'

6.启动实例--与第1步对应，用到了ORACLE_SID

sqlplus / as sysdba

创建SPFILE文件，默认放在：WINDOWS下是：$ORACLE_HOME\database\ LINUX下是：$ORACLE_HOME\dbs\

注：从spfile 创建 pfile，需要SYSDBA权限，DBA权限发出此语句会报错。

create spfile from pfile='/home/oracle/bys.ora';

启动实例：--默认搜索顺序是：在缺省目录$ORACLE_HOME/database下搜索SPFILESID.ORA——SPFILE.ORA——PFILESID.ORA

startup nomount;

7.使用CREATE DATABASE命令创建数据库。建议使用ZHS16GBK 字符集

在SQLPLUS中使用如下命令：--也可以写入一个脚本文件，然后在SQLPLUS中调用此脚本。注意数据文件 、日志文件的创建目录位置及权限。

create database bys3

user sys identified by oraclesys

user system identified by oraclesys

logfile group 1('/u01/oradata/bys3/redo01.log') size 50m,

group 2('/u01/oradata/bys3/redo02.log') size 50m,

group 3('/u01/oradata/bys3/redo03.log') size 50m

maxlogfiles 20

maxlogmembers 5

maxloghistory 200

maxdatafiles 100

character set AL32UTF8

national character set AL16UTF16

extent management local

datafile '/u01/oradata/bys3/system01.dbf' size 500m reuse

sysaux datafile '/u01/oradata/bys3/sysaux01.dbf' size 325m reuse

default temporary tablespace temp

tempfile '/u01/oradata/bys3/temp01.dbf' size 20m reuse

undo tablespace undotbs1

datafile '/u01/oradata/bys3/undotbs01.dbf' size 200m reuse;

以上语句成功执行后是有个类似database create complete吧，数据库此时就被置为OPEN状态--可查询select status from v$instance验证。

此时执行下面一条语句，增加USERS表空间。

create tablespace users datafile '/u01/oradata/bys3/user01.dbf' size 50m;

alter database default tablespace users; ---将users表空间设置为系统默认的表空间，用户新建的表不指定默认即在此表空间。

8.以上创建完成后，执行以下三个脚本：

我这里用了脚本的绝对路径,也可以使用@?/rdbms/admin/catalog.sql的方式;，

@=run ?==$ORACLE_HOME mark (?) is a SQL*Plus variable indicating the Oracle home directory

catalog.Sql创建SQL视图，

catproc.sql创建系统的存储过程...

pupbld.sql脚本主要是创建SQLPLUS_PRODUCT_PROFILE表，并在表上建立相关的视图和同义词

@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catalog.sql

@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catproc.sql

下面一句需要使用SYSTEM用户登陆，不能用SYSDBA，会报错，见http://blog.csdn.net/q947817003/article/details/16117123

即上两个脚本执行完成后，使用conn system/oraclesys命令以SYSETM用户登陆，可以show user验证一下。

@/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/pupbld.sql

到此创建完成。默认是非归档模式。

9.删除数据库：因使用手工创建的数据库使用DBCA无法删除，此时需要使用命令来删除数据库。

使用以下三个命令即可：

SYS@bys1>startup mount exclusive

SYS@bys1>alter system enable restricted session;

SYS@bys1>drop database;

数据库相应的控制文件,日志文件，数据文件会自动删除,spfile也会删除(归档日志不会删除). 从以下官方文档的内容可以证实：Dropping a database involves

removing its datafiles, redo log files, control files, and initialization parameter files. The DROP DATABASE statement deletes all control files and all other database files listed in the control file.

The DROP DATABASE statement hasno effect on archived log files, nor does it have any effect on copies or backups of the database. It is best to use RMAN to delete such files. If the database is on raw disks,

the actual raw disk special files are not deleted.

10.手工建库时的ALERT日志

[oracle@bys3 ~]$ cat alert_bys3.log

Thu Nov 14 14:17:19 2013

Starting ORACLE instance (normal)

************************ Large Pages Information *******************

Per process system memlock (soft) limit = 3418 MB

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)

Large Pages unused system wide = 0 (0 KB)

Large Pages configured system wide = 0 (0 KB)

Large Page size = 2048 KB

RECOMMENDATION:

Total System Global Area size is 146 MB. For optimal performance,

prior to the next instance restart:

1. Increase the number of unused large pages by

at least 73 (page size 2048 KB, total size 146 MB) system wide to

get 100% of the System Global Area allocated with large pages

********************************************************************

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Initial number of CPU is 1

Shared memory segment for instance monitoring created

CELL communication is configured to use 0 interface(s):

CELL IP affinity details:

NUMA status: non-NUMA system

cellaffinity.ora status: N/A

CELL communication will use 1 IP group(s):

Grp 0:

Picked latch-free SCN scheme 2

Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Autotune of undo retention is turned on.

IMODE=BR

ILAT =22

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: bys3.bys.com

Release: 2.6.32-200.13.1.el5uek

Version: #1 SMP Wed Jul 27 20:21:26 EDT 2011

Machine: i686

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilebys3.ora

System parameters with non-default values: ----和前面设置的初始化参数文件中参数对应

control_files = "/u01/oradata/bys3/control01.ctl"

control_files = "/u01/oradata/bys3/control02.ctl"

db_name = "bys3"

Thu Nov 14 14:17:21 2013

PMON started with pid=2, OS id=4389

Thu Nov 14 14:17:21 2013

PSP0 started with pid=3, OS id=4393

Thu Nov 14 14:17:23 2013

VKTM started with pid=4, OS id=4397 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Thu Nov 14 14:17:23 2013

GEN0 started with pid=5, OS id=4403

Thu Nov 14 14:17:23 2013

DIAG started with pid=6, OS id=4407

Thu Nov 14 14:17:23 2013

DBRM started with pid=7, OS id=4411

Thu Nov 14 14:17:23 2013

DIA0 started with pid=8, OS id=4415

Thu Nov 14 14:17:23 2013

MMAN started with pid=9, OS id=4419

Thu Nov 14 14:17:24 2013

DBW0 started with pid=10, OS id=4423

Thu Nov 14 14:17:24 2013

LGWR started with pid=11, OS id=4427

Thu Nov 14 14:17:24 2013

CKPT started with pid=12, OS id=4431

Thu Nov 14 14:17:24 2013

SMON started with pid=13, OS id=4435

Thu Nov 14 14:17:24 2013

RECO started with pid=14, OS id=4439

Thu Nov 14 14:17:24 2013

MMON started with pid=15, OS id=4443

ORACLE_BASE not set in environment. It is recommended

that ORACLE_BASE be set in the environment

Thu Nov 14 14:17:25 2013

MMNL started with pid=16, OS id=4447

Thu Nov 14 14:23:19 2013

create database bys3

user sys identified by *user system identified by * logfile group 1('/u01/oradata/bys3/redo01.log') size 50m,

group 2('/u01/oradata/bys3/redo02.log') size 50m,

group 3('/u01/oradata/bys3/redo03.log') size 50m

maxlogfiles 20

maxlogmembers 5

maxloghistory 200

maxdatafiles 100

character set AL32UTF8

national character set AL16UTF16

extent management local

datafile '/u01/oradata/bys3/system01.dbf' size 500m reuse

sysaux datafile '/u01/oradata/bys3/sysaux01.dbf' size 325m reuse

default temporary tablespace temp

tempfile '/u01/oradata/bys3/temp01.dbf' size 20m reuse

undo tablespace undotbs1

datafile '/u01/oradata/bys3/undotbs01.dbf' size 200m reuse

Database mounted in Exclusive Mode

Lost write protection disabled

Thu Nov 14 14:23:38 2013

Successful mount of redo thread 1, with mount id 3358374039

Thu Nov 14 14:23:38 2013

Database SCN compatibility initialized to 1

Assigning activation ID 3358374039 (0xc82cb897)

Thread 1 opened at log sequence 1

Current log# 1 seq# 1 mem# 0: /u01/oradata/bys3/redo01.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Thu Nov 14 14:23:38 2013

SMON: enabling cache recovery

processing ?/rdbms/admin/dcore.bsq

create tablespace SYSTEM datafile '/u01/oradata/bys3/system01.dbf' size 500m reuse

EXTENT MANAGEMENT LOCAL online

Thu Nov 14 14:24:23 2013

Completed: create tablespace SYSTEM datafile '/u01/oradata/bys3/system01.dbf' size 500m reuse

EXTENT MANAGEMENT LOCAL online

create rollback segment SYSTEM tablespace SYSTEM

storage (initial 50K next 50K)

UNDO_SEG_CRT: Could not find usn tail

Completed: create rollback segment SYSTEM tablespace SYSTEM

storage (initial 50K next 50K)

Undo initialization finished serial:0 start:12514064 end:12514074 diff:10 (0 seconds)

processing ?/rdbms/admin/dsqlddl.bsq

processing ?/rdbms/admin/dmanage.bsq

CREATE TABLESPACE sysaux DATAFILE '/u01/oradata/bys3/sysaux01.dbf' size 325m reuse

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE

Thu Nov 14 14:24:54 2013

Completed: CREATE TABLESPACE sysaux DATAFILE '/u01/oradata/bys3/sysaux01.dbf' size 325m reuse

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE

processing ?/rdbms/admin/dplsql.bsq

processing ?/rdbms/admin/dtxnspc.bsq

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/oradata/bys3/undotbs01.dbf' size 200m reuse

Thu Nov 14 14:25:12 2013

[4450] Successfully onlined Undo Tablespace 2.

Completed: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/oradata/bys3/undotbs01.dbf' size 200m reuse

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/bys3/temp01.dbf' size 20m reuse

Completed: CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/bys3/temp01.dbf' size 20m reuse

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

ALTER DATABASE DEFAULT TABLESPACE SYSTEM

Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM

processing ?/rdbms/admin/dfmap.bsq

processing ?/rdbms/admin/denv.bsq

processing ?/rdbms/admin/drac.bsq

processing ?/rdbms/admin/dsec.bsq

processing ?/rdbms/admin/doptim.bsq

processing ?/rdbms/admin/dobj.bsq

processing ?/rdbms/admin/djava.bsq

processing ?/rdbms/admin/dpart.bsq

processing ?/rdbms/admin/drep.bsq

processing ?/rdbms/admin/daw.bsq

processing ?/rdbms/admin/dsummgt.bsq

processing ?/rdbms/admin/dtools.bsq

processing ?/rdbms/admin/dexttab.bsq

processing ?/rdbms/admin/ddm.bsq

processing ?/rdbms/admin/dlmnr.bsq

processing ?/rdbms/admin/ddst.bsq

Thu Nov 14 14:25:21 2013

SMON: enabling tx recovery

Starting background process SMCO

Thu Nov 14 14:25:22 2013

SMCO started with pid=18, OS id=4476

Thu Nov 14 14:25:24 2013

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Thu Nov 14 14:25:25 2013

QMNC started with pid=19, OS id=4480

Completed: create database bys3

user sys identified by *user system identified by * logfile group 1('/u01/oradata/bys3/redo01.log') size 50m,

group 2('/u01/oradata/bys3/redo02.log') size 50m,

group 3('/u01/oradata/bys3/redo03.log') size 50m

maxlogfiles 20

maxlogmembers 5

maxloghistory 200

maxdatafiles 100

character set AL32UTF8

national character set AL16UTF16

extent management local

datafile '/u01/oradata/bys3/system01.dbf' size 500m reuse

sysaux datafile '/u01/oradata/bys3/sysaux01.dbf' size 325m reuse

default temporary tablespace temp

tempfile '/u01/oradata/bys3/temp01.dbf' size 20m reuse

undo tablespace undotbs1

datafile '/u01/oradata/bys3/undotbs01.dbf' size 200m reuse

Thu Nov 14 14:25:57 2013

create tablespace users datafile '/u01/oradata/bys3/sysaux01.dbf' size 50m

ORA-1537 signalled during: create tablespace users datafile '/u01/oradata/bys3/sysaux01.dbf' size 50m...

Thu Nov 14 14:26:25 2013

create tablespace users datafile '/u01/oradata/bys3/user01.dbf' size 50m

Completed: create tablespace users datafile '/u01/oradata/bys3/user01.dbf' size 50m

Thu Nov 14 14:30:31 2013

Thread 1 advanced to log sequence 2 (LGWR switch)

Current log# 2 seq# 2 mem# 0: /u01/oradata/bys3/redo02.log

Thu Nov 14 14:31:16 2013

Thread 1 advanced to log sequence 3 (LGWR switch)

Current log# 3 seq# 3 mem# 0: /u01/oradata/bys3/redo03.log

Thu Nov 14 14:33:22 2013

Thread 1 advanced to log sequence 4 (LGWR switch)

Current log# 1 seq# 4 mem# 0: /u01/oradata/bys3/redo01.log

Thu Nov 14 14:33:52 2013

Thread 1 advanced to log sequence 5 (LGWR switch)

Current log# 2 seq# 5 mem# 0: /u01/oradata/bys3/redo02.log

Thu Nov 14 14:34:11 2013

Create Relation IPS_PACKAGE_UNPACK_HISTORY

Thu Nov 14 14:34:28 2013

Thread 1 advanced to log sequence 6 (LGWR switch)

Current log# 3 seq# 6 mem# 0: /u01/oradata/bys3/redo03.log

Thu Nov 14 14:34:43 2013

Thread 1 advanced to log sequence 7 (LGWR switch)

Current log# 1 seq# 7 mem# 0: /u01/oradata/bys3/redo01.log

Thu Nov 14 14:35:04 2013

Thread 1 advanced to log sequence 8 (LGWR switch)

Current log# 2 seq# 8 mem# 0: /u01/oradata/bys3/redo02.log

Thu Nov 14 14:36:16 2013

Thread 1 advanced to log sequence 9 (LGWR switch)

Current log# 3 seq# 9 mem# 0: /u01/oradata/bys3/redo03.log

Thu Nov 14 14:37:43 2013

Thread 1 advanced to log sequence 10 (LGWR switch)

Current log# 1 seq# 10 mem# 0: /u01/oradata/bys3/redo01.log

Thu Nov 14 14:38:55 2013

Thread 1 advanced to log sequence 11 (LGWR switch)

Current log# 2 seq# 11 mem# 0: /u01/oradata/bys3/redo02.log

Thu Nov 14 14:40:10 2013

Thread 1 advanced to log sequence 12 (LGWR switch)

Current log# 3 seq# 12 mem# 0: /u01/oradata/bys3/redo03.log

Thu Nov 14 14:40:34 2013

Thread 1 advanced to log sequence 13 (LGWR switch)

Current log# 1 seq# 13 mem# 0: /u01/oradata/bys3/redo01.log

Thu Nov 14 14:40:46 2013

Starting background process CJQ0

Thu Nov 14 14:40:46 2013

CJQ0 started with pid=32, OS id=4598

Thu Nov 14 14:41:31 2013

Thread 1 advanced to log sequence 14 (LGWR switch)

Current log# 2 seq# 14 mem# 0: /u01/oradata/bys3/redo02.log

Thu Nov 14 14:41:40 2013

SERVER COMPONENT id=CATPROC: timestamp=2013-11-14 14:41:40

Thu Nov 14 15:06:30 2013

Spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilebys3.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format.

本文原创发布php中文网，转载请注明出处，感谢您的尊重！