oracle 11g手工建库

最新推荐文章于 2023-03-27 15:55:22 发布

安伦_Alan

最新推荐文章于 2023-03-27 15:55:22 发布

阅读量792

点赞数

分类专栏： oracle数据库

oracle数据库专栏收录该内容

76 篇文章 0 订阅

订阅专栏

手动创建数据库是DBA们经常遇到的情形，因为有些情况下无法提供GUI环境。实际上手动建库，只要设置好了相关的参数或值，也是非常方便的。本文基于Oracle 11g首先描述了手动建库的大致步骤并给出示例演示。最后提供了一个shell脚本直接执行来实现手动建库。在执行前该脚本可根据你的路径进行适当的修改。

一、手动建库大致步骤
    设置环境变量.bash_profile
    创建参数文件(位置:$ORACLE_HOME/dbs)
    创建目录结构
    执行建库脚本

二、手动创建数据库

[sql] view plain copy print ?

1、设置环境变量.bash_profile
--确保至少包含下列环境变量，对于已经安装好Oracle Database software的情形下，实际上可以跳过此步骤
$ vi .bash_profile
export ORACLE_SID=catadb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/db_1
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
2、创建参数文件(位置:$ORACLE_HOME/dbs)
[oracle@linux1 dbs]$ export ORACLE_SID=catadb
[oracle@linux1 ~]$ cd $ORACLE_HOME/dbs
[oracle@linux1 dbs]$ grep -v \# init.ora >init$ORACLE_SID.ora
[oracle@linux1 dbs]$ more initcatadb.ora
db_name='catadb'
memory_target=512M
processes = 150
audit_file_dest='/u03/uat/catadb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u03/uat/catadb/fr_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u03/uat/catadb'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ('/u03/uat/catadb/oradata/catadb01.ctl','/u03/uat/catadb/oradata/catadb02.ctl')
compatible ='11.2.0'
3、创建目录结构
[oracle@linux1 ~]$ mkdir -p /u03/uat/catadb
[oracle@linux1 dbs]$ cd /u03/uat/catadb
[oracle@linux1 catadb]$ mkdir adump diag fr_area oradata
4、执行建库脚本
[oracle@linux1 catadb]$ more create_catadb.sql
create database catadb
maxlogfiles 5
maxlogmembers 5
maxdatafiles 100
maxinstances 1
logfile
group 1 ('/u03/uat/catadb/oradata/redo1.log') size 10m,
group 2 ('/u03/uat/catadb/oradata/redo2.log') size 10m,
group 3 ('/u03/uat/catadb/oradata/redo3.log') size 10m
datafile
'/u03/uat/catadb/oradata/system.dbf' size 200m reuse
sysaux datafile '/u03/uat/catadb/oradata/sysaux.dbf' size 100m
default tablespace user
datafile '/u03/uat/catadb/oradata/userdata.dbf' size 100m
undo tablespace undotbs1
datafile '/u03/uat/catadb/oradata/undo1.dbf' size 100m
default temporary tablespace temp1
tempfile '/u03/uat/catadb/oradata/temp01.dbf'
size 100m reuse
character set al32utf8
;
idle> @/u03/uat/catadb/create_catadb.sql
Database created.
--添加数据字典
idle> edit /u03/uat/catadb/post_create_catadb.sql
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
conn system/manager
@$ORACLE_HOME/sqlplus/admin/pupbld.sql
idle> @/u03/uat/catadb/post_create_catadb.sql
system@CATADB> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
CATADB READ WRITE

dbca
   数据库创建助手
       确认主机名,DNS和glogin.sql文件中书写正确(里面不要写DDL语句) 基本都能创建成功

ps -ef |grep ora_

export ORACLE_SID=test
conn / as sysdba
show parameter name

export ORACLE_SID=orcl
conn / as sysdba
show parameter name

手工建库

一.准备数据库物理文件存放的相关路径
     1.trace file(实例管理目录): 默认位置 $ORACLE_BASE/admin/<sid>/

   SYS@test> !echo $ORACLE_BASE
   /home/oracle

   SYS@test>
   SYS@test> !echo $ORACLE_SID
   seker

   SYS@test>
   SYS@test> ! mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump

   SYS@test> ! mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump

   SYS@test> ! mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump

   SYS@test> ! mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump

   SYS@test>


    2.存放数据文件的目录:
   SYS@test> ! mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/

   SYS@test>


二.设置环境变量
   vim .bash_profile
       export ORACLE_BASE=/home/oracle
       export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
       export ORACLE_SID=seker
       export PATH=$PATH:$ORACLE_HOME/bin
       export LANG=zh_CN.UTF-8
       export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
       alias sqlplus='rlwrap sqlplus'
       alias sql='rlwrap sqlplus'
       alias rman='rlwrap rman'
       alias lsnrctl='rlwrap lsnrctl'
   :wq

三.创建口令文件
       orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=1 force=y

四.准备参数文件
vim $ORACLE_HOME/dbs/init$ORACLE_SID.ora
shared_pool_size=200M
compatible='10.2.0.1.0'
db_name='seker'
control_files='/u01/oracle/oradata/seker/control01.ctl'
audit_file_dest='/u01/oracle/admin/seker/adump'
background_dump_dest='/u01/oracle/admin/seker/bdump'
core_dump_dest='/u01/oracle/admin/seker/cdump'
user_dump_dest='/u01/oracle/admin/seker/udump'
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
:wq

五.启动数据库到nomount
[oracle@seker ~]$ export ORACLE_SID=seker
[oracle@seker ~]$ echo $ORACLE_SID
seker
[oracle@seker ~]$ sql /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 21 07:32:30 2011

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

idle> conn / as sysdba
Connected to an idle instance.
idle> startup nomount
ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size            1219016 bytes
Variable Size          234882616 bytes
Database Buffers       50331648 bytes
Redo Buffers            2973696 bytes
idle> show parameter name

NAME                     TYPE                   VALUE
------------------------------------ --------------------------------- ------------------------------
db_file_name_convert             string
db_name                  string                   seker
db_unique_name                 string                   seker
global_names                 boolean                   FALSE
instance_name                 string                   seker
lock_name_space              string
log_file_name_convert             string
service_names                 string                   seker
idle>

六. 创建数据库

CREATE database seker
maxinstances 8
maxloghistory 1
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
DATAFILE '/u01/oracle/oradata/seker/system01.dbf' size 300m autoextend on next 10m extent management local
sysaux datafile '/u01/oracle/oradata/seker/sysaux01.dbf' size 120m autoextend on next 10m
default temporary tablespace temp tempfile '/u01/oracle/oradata/seker/temp01.dbf' size 20m
undo tablespace undotbs1 datafile '/u01/oracle/oradata/seker/undotbs01.dbf' size 50m
CHARACTER set AL32UTF8
NATIONAL character set AL16UTF16
SET TIME_ZONE='+08:00'
LOGFILE
group 1 '/u01/oracle/oradata/seker/redo01.log' size 50M,
group 2 '/u01/oracle/oradata/seker/redo02.log' size 50M;

ERROR:
ORA-06553: PLS-213: package STANDARD not accessible

Database created.

idle>

创建完成但有错误退出SQLPLUS 再重新登录

七.创建数据字典
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc

八.创建users表空间建立scott用户
SQL> create tablespace users datafile '/u01/oracle/oradata/seker/user01.dbf' size 10m autoextend on;

Tablespace created.

Elapsed: 00:00:00.15
SQL> @?/rdbms/admin/utlsampl

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dba dbs]$ sql /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 7 06:06:50 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> alter user scott account unlock identified by seker;

User altered.

Elapsed: 00:00:00.61

九.连接到scott用户有错误提示根据提示执行PUPBLD脚本
SQL> conn scott/seker
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL>
SQL> conn system/manager
Connected.
SQL>
SQL> show user
USER is "SYSTEM"
创建安全审核概要表:
SQL> @?/sqlplus/admin/pupbld
SQL> conn scott/seker
Connected.

十.重新启动测试数据库是否正常
SQL> conn / as sysdba
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;

File created.

Elapsed: 00:00:00.04
SQL> startup
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size            1266488 bytes
Variable Size          130026696 bytes
Database Buffers       50331648 bytes
Redo Buffers            2924544 bytes
Database mounted.
Database opened.
SQL>