OCM系列-Server Configuration->Create the database

最新推荐文章于 2024-08-06 18:34:57 发布

weixin_33809981

最新推荐文章于 2024-08-06 18:34:57 发布

阅读量57

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/recyclebin/1241218

版权

0.1.1. 官方文档

Administrator's Guide -> Creating an Oracle Database

# Creating the Database

* Step 1: Decide on Your Instance Identifier (SID)

* Step 2: Establish the Database Administrator Authentication Method

* Step 3: Create the Initialization Parameter File

* Step 4: Connect to the Instance

* Step 5: Create a Server Parameter File (Recommended)

* Step 6: Start the Instance

* Step 7: Issue the CREATE DATABASE Statement

* Step 8: Create Additional Tablespaces

* Step 9: Run Scripts to Build Data Dictionary Views

* Step 10: Run Scripts to Install Additional Options (Optional)

* Step 11: Back Up the Database.

0.1.2. 操作步骤

0.1.2.1. 修改环境变量

export ORACLE_BASE=/oracle

export ORACLE_HOME=/oracle/product/10.2.0/db_1

export ORACLE_SID=prod

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

alias ob='cd $ORACLE_BASE'

alias oh='cd $ORACLE_HOME'

alias bdump='cd $ORACLE_BASE/admin/$ORACLE_SID/bdump'

alias alert='tail -f $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log'

alias sql='sqlplus / as sysdba'

alias tns='cd $ORACLE_HOME/netwokr/admin'

alias prod='export ORACLE_SID=prod'

编辑完成后，使用 . .bash_profile 命令，使环境变量生效。

[oracle@db ~]$ . .bash_profile

0.1.2.2. 准备建库目录

l 创建数据文件目录

[oracle@db ~]$ ob

[oracle@db oracle]$

[oracle@db oracle]$ mkdir oradta

[oracle@db oracle]$ cd oradta/

[oracle@db oradta]$ mkdir prod

[oracle@db oradta]$ cd prod/

[oracle@db prod]$ pwd

/oracle/oradta/prod

精简后的命令：

mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID

l 创建dump文件目录

[oracle@db ~]$ ob

[oracle@db oracle]$ ls

oradta oraInventory product

[oracle@db oracle]$ mkdir admin

[oracle@db oracle]$ cd admin

[oracle@db admin]$ mkdir prod

[oracle@db admin]$ cd prod

[oracle@db prod]$ mkdir -p {adump,bdump,cdump,udump}

[oracle@db prod]$ ls

adump bdump cdump udump

[oracle@db prod]$ cd bdump/

[oracle@db bdump]$ pwd

/oracle/admin/prod/bdump

精简后的命令：

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/{adump,bdump,cdump,udump}

0.1.2.3. 修改初始化文件

cat init.ora | grep -v ^# | grep -v ^$ > initprod.ora

备注：去除注释和空白行

db_name=DEFAULT

db_files = 200 # SMALL

db_file_multiblock_read_count = 8 # SMALL

db_block_buffers = 100 # SMALL

shared_pool_size = 3500000 # SMALL

log_checkpoint_interval = 10000

processes = 50 # SMALL

parallel_max_servers = 5 # SMALL

log_buffer = 32768 # SMALL

max_dump_file_size = 10240 # limit trace file size to 5 Meg each

global_names = TRUE

control_files = (ora_control1, ora_control2)

修改db_name，control_files参数，删去内存相关参数，使用内存自动管理方式。

[oracle@db dbs]$ more initprod.ora

db_name=prod

db_files = 200 # SMALL

db_file_multiblock_read_count = 8 # SMALL

processes = 150 # SMALL

parallel_max_servers = 5 # SMALL

log_buffer = 132768 # SMALL

global_names = TRUE

control_files = (/oracle/oradata/prod/ora_control1, /oracle/oradata/prod/ora_control2, /oracle/oradata/prod/ora_control3)

sga_max_size=300m

sga_target=300m

undo_management=auto

undo_tablespace=undotbs

0.1.2.4. 生成密码文件

[oracle@db ~]$ oh

[oracle@db db_1]$ cd dbs

[oracle@db dbs]$ ls

hc_prod.dat initdw.ora init.ora initprod.ora

[oracle@db dbs]$ orapwd -h

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@db dbs]$ orapwd file=orapwdprod password=oracle entries=5

0.1.2.5. 启动实例到nomount状态，创建spfile文件

[oracle@db dbs]$ sql

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 3 09:28:16 2013

Connected to an idle instance.

SQL> startup nomount pfile=initprod.ora

ORACLE instance started.

Total System Global Area 314572800 bytes

Fixed Size 1219184 bytes

Variable Size 96470416 bytes

Database Buffers 213909504 bytes

Redo Buffers 2973696 bytes

SQL>

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 314572800 bytes

Fixed Size 1219184 bytes

Variable Size 96470416 bytes

Database Buffers 213909504 bytes

Redo Buffers 2973696 bytes

SQL>

SQL> show parameter audit;

修改audit_file_dest

SQL> show parameter dump;

修改background_dump_dest

修改core_dump_dest

修改user_dump_dest

编辑spfile修改参数：

[oracle@db ~]$ vi param.txt

alter system set audit_file_dest='/oracle/admin/prod/adump' scope=spfile;

alter system set background_dump_dest='/oracle/admin/prod/bdump' scope=spfile;

alter system set core_dump_dest='/oracle/admin/prod/cdump' scope=spfile;

alter system set user_dump_dest='/oracle/admin/prod/bdump' scope=spfile;

注意：当admin目录下面有adump、bdump、cdump、udump目录时，不需要设置这4个参数，会自动调整。

SQL> show parameter undo;

修改undo_management

修改undo_tablespace

alter system set undo_management=auto scope=spfile;

alter system set undo_tablespace=undotbs scope=spfile;

注意：建议，直接修改到pfile文件中。

在目录和undo设置好的前提下，通过spfile重启后的数据库，不需要再修改参数即可开始执行

CREATE DATABASE 语句。

0.1.2.6. 创建数据库

从官方文档拷贝出CREATE DATABASE语句：

CREATE DATABASE mynewdb

USER SYS IDENTIFIED BY pz6r58

USER SYSTEM IDENTIFIED BY y1tz5p

LOGFILE GROUP 1 ('/u01/oracle/oradata/mynewdb/redo01.log') SIZE 100M,

GROUP 2 ('/u01/oracle/oradata/mynewdb/redo02.log') SIZE 100M,

GROUP 3 ('/u01/oracle/oradata/mynewdb/redo03.log') SIZE 100M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

CHARACTER SET US7ASCII

NATIONAL CHARACTER SET AL16UTF16

DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE

DEFAULT TABLESPACE tbs_1

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf'

SIZE 20M REUSE

UNDO TABLESPACE undotbs

DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

修改信息：

CREATE DATABASE prod

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/u01/oracle/oradata/mynewdb/redo01.log') SIZE 100M,

GROUP 2 ('/u01/oracle/oradata/mynewdb/redo02.log') SIZE 100M,

GROUP 3 ('/u01/oracle/oradata/mynewdb/redo03.log') SIZE 100M,

GROUP 4 ('/u01/oracle/oradata/mynewdb/redo04.log') SIZE 100M,

GROUP 5 ('/u01/oracle/oradata/mynewdb/redo05.log') SIZE 100M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

CHARACTER SET US7ASCII

NATIONAL CHARACTER SET AL16UTF16

DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE

DEFAULT TABLESPACE tbs_1 删除该内容

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf'

SIZE 20M REUSE

UNDO TABLESPACE undotbs

DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

红色内容是需要修改的信息。

Vi快速替换技巧，通过+号进行快速匹配：

%s+/u01/oracle/oradata/mynewdb+/oracle/oradata/prod

0.1.2.7. 修改现有表空间为自动扩展，创建附加表空间

SQL> select file_id from dba_data_files;

FILE_ID

----------

SQL> alter database datafile 1 autoextend on;

Database altered.

SQL> c/1/2/

1* alter database datafile 2 autoextend on

SQL> /

Database altered.

SQL> c/2/3/

1* alter database datafile 3 autoextend on

SQL> /

Database altered.

SQL> alter database tempfile 1 autoextend on;

Database altered.

alter database datafile 1 autoextend on;

alter database datafile 2 autoextend on;

alter database datafile 3 autoextend on;

alter database tempfile 1 autoextend on;

-- create a user tablespace to be assigned as the default tablespace for users

CREATETABLESPACEUSERSLOGGING

DATAFILE'/oracle/oradata/prod/user01.dbf'SIZE25M

REUSEAUTOEXTENDON

EXTENTMANAGEMENTLOCALUNIFORMSIZE1M

SEGMENTSPACEMANAGEMENTAUTO;

-- create a tablespace for indexes, separate from user tablespace

CREATETABLESPACE indx LOGGING

DATAFILE'/u01/oracle/oradata/mynewdb/indx01.dbf'

SIZE25M REUSEAUTOEXTENDONNEXT1280K MAXSIZEUNLIMITED

EXTENTMANAGEMENTLOCAL;

DROPTABLESPACEUSERSINCLUDINGCONTENTSANDDATAFILES;

0.1.2.8. 执行创建对象脚本

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?rdbms/admin/dbmspool.sql

转载于:https://blog.51cto.com/recyclebin/1241218

weixin_33809981

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
OCM系列-Server Configuration->Create the database

0.1.1. 官方文档Administrator's Guide -> Creating an Oracle Database# Creating the Database * Step 1: Decide on Your Instance Identifier (SID) * Step 2: Establish the Database Administr...
复制链接

扫一扫