在oracle9i上创建数据库实例

最新推荐文章于 2021-04-05 10:39:43 发布

sugarcane0926

最新推荐文章于 2021-04-05 10:39:43 发布

阅读量468

点赞数

分类专栏： Oracle 文章标签： Oracle SQL 脚本 Cache Security

Oracle 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

平时我们创建数据库实例的时候大多数是用图形化界面来一步步创建数据库。但是我们通过远程登录，这种方法就行不通了，因此我们必须手动创建数据库实例。一个比较好的方法就是在一个可以界面化创建数据库的地方，自己创建一个实例，然后保存创建脚本，在把脚本放到数据库中来执行。

下面，我将这次手动执行数据库实例创建的步骤

写一个shell了，运行这个shell 就可以创建一个数据库实例了

其中创建的数据库实例名为ORCL，原来数据库目录为/oracle/

创建数据库 orapwORCL文件的命令如下,password可根据实际情况改变

/oracle/product/bin/orapwd file=/oracle/product/dbs/orapwORCL password=change_on_install

ORCl.sh

#!/bin/sh

mkdir /oracle/product/admin/ORCL/bdump

mkdir /oracle/product/admin/ORCL/cdump

mkdir /oracle/product/admin/ORCL/create

mkdir /oracle/product/admin/ORCL/pfile

mkdir /oracle/product/admin/ORCL/udump

mkdir /oracle/product/dbs

mkdir /oracle/product/oradata/ORCL

setenv ORACLE_SID orcl

echo Add this entry in the oratab: ORCL:/oracle/product:Y

/oracle/product/bin/orapwd file=/oracle/product/dbs/orapwORCL password=change_on_install

/oracle/product/bin/sqlplus /nolog @/oracle/product/admin/ORCL/scripts/CreateDB.sql

/oracle/product/bin/sqlplus /nolog @/oracle/product/admin/ORCL/scripts/CreateDBFiles.sql

/oracle/product/bin/sqlplus /nolog @/oracle/product/admin/ORCL/scripts/CreateDBCatalog.sql

/oracle/product/bin/sqlplus /nolog @/oracle/product/admin/ORCL/scripts/postDBCreation.sql

分别写了4 个sql来执行

CreateDB.sql

connect SYS/change_on_install as SYSDBA

set echo on

spool /oracle/product/assistants/dbca/logs/CreateDB.log

startup nomount pfile="/oracle/product/admin/ORCL/scripts/initorcl.ora";

CREATE DATABASE ORCL

MAXINSTANCES 1

MAXLOGHISTORY 1

MAXLOGFILES 5

MAXLOGMEMBERS 3

MAXDATAFILES 100

DATAFILE '/oracle/product/oradata/ORCL/system01.dbf' SIZE 250M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oracle/product/oradata/ORCL/temp01.dbf' SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

UNDO TABLESPACE "UNDOTBS1" DATAFILE '/oracle/product/oradata/ORCL/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

CHARACTER SET UTF8

NATIONAL CHARACTER SET UTF8

LOGFILE GROUP 1 ('/oracle/product/oradata/ORCL/redo01.log') SIZE 102400K,

GROUP 2 ('/oracle/product/oradata/ORCL/redo02.log') SIZE 102400K,

GROUP 3 ('/oracle/product/oradata/ORCL/redo03.log') SIZE 102400K;

spool off

exit;

CreateDBfile.sql

connect SYS/change_on_install as SYSDBA

set echo on

spool /oracle/product/assistants/dbca/logs/CreateDBFiles.log

CREATE TABLESPACE "INDX" LOGGING DATAFILE '/oracle/product/oradata/ORCL/indx01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE "TOOLS" LOGGING DATAFILE '/oracle/product/oradata/ORCL/tools01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 320K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE "USERS" LOGGING DATAFILE '/oracle/product/oradata/ORCL/users01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

spool off

exit;

CreateDBCatalog.sql

connect SYS/change_on_install as SYSDBA

set echo on

spool /oracle/product/assistants/dbca/logs/CreateDBCatalog.log

@/oracle/product/rdbms/admin/catalog.sql;

@/oracle/product/rdbms/admin/catexp7.sql;

@/oracle/product/rdbms/admin/catblock.sql;

@/oracle/product/rdbms/admin/catproc.sql;

@/oracle/product/rdbms/admin/catoctk.sql;

@/oracle/product/rdbms/admin/owminst.plb;

connect SYSTEM/manager

@/oracle/product/sqlplus/admin/pupbld.sql;

connect SYSTEM/manager

set echo on

spool /oracle/product/assistants/dbca/logs/sqlPlusHelp.log

@/oracle/product/sqlplus/admin/help/hlpbld.sql helpus.sql;

spool off

exit;

postDBCreation.sql

connect SYS/change_on_install as SYSDBA

set echo on

spool /oracle/product/assistants/dbca/logs/postDBCreation.log

@/oracle/product/rdbms/admin/utlrp.sql;

shutdown ;

connect SYS/change_on_install as SYSDBA

set echo on

spool /oracle/product/assistants/dbca/logs/postDBCreation.log

create spfile='/oracle/product/dbs/spfileORCL.ora' FROM pfile='/oracle/product/admin/ORCL/scripts/initorcl.ora';

startup ;

附 initorcl.ora 初始化参数文件

##############################################################################

###########################################

# Cache and I/O

###########################################

db_block_size=8192

db_cache_size=52428800

db_file_multiblock_read_count=16

###########################################

# Cursors and Library Cache

###########################################

open_cursors=300

###########################################

# Database Identification

###########################################

db_domain=""

db_name=ORCL

###########################################

# Diagnostics and Statistics

###########################################

background_dump_dest=/oracle/product/admin/ORCL/bdump

core_dump_dest=/oracle/product/admin/ORCL/cdump

timed_statistics=TRUE

user_dump_dest=/oracle/product/admin/ORCL/udump

###########################################

# File Configuration

###########################################

control_files=("/oracle/product/oradata/ORCL/control01.ctl", "/oracle/product/oradata/ORCL/control02.ctl", "/oracle/product/oradata/ORCL/control03.ctl")

###########################################

# Instance Identification

###########################################

instance_name=ORCL

###########################################

# Miscellaneous

###########################################

compatible=9.2.0.0.0

###########################################

# Optimizer

###########################################

hash_join_enabled=TRUE