Oracle11g手工建库代替dbca操作

操作系统及参数配置此处省略。。。

安装Oracle11g数据库软件此处省略。。。之后就可以用以下操作步骤来完成手工建库

0、配置sqlplus提示前置识别信息

配置sqlplus中显示连接的哪个数据库的提示前置识别信息 防止连错数据，操作失误

注：配置后效果：sqlplus 中显示连接的哪个数据库：“SYS@PROD01> ”

cd $ORACLE_HOME/sqlplus/admin

vi glogin.sql

增加如下内容

define _editor='vi'

set sqlprompt "_user'@'_connect_identifier> "

1、根据init.ora生成待调整的pfile参数文件

[oracle@db01 ~]$ cd $ORACLE_HOME/dbs

[oracle@db01 dbs]$ ls

hc_db01.dat hc_db01dg.dat init.ora lkDB01 orapwdb01 snapcf_db01.f spfiledb01.ora

[oracle@db01 dbs]$ cat init.ora |grep -v ^# |grep -v ^$ >initPROD01.ora

[oracle@db01 dbs]$ vi initPROD01.ora

[oracle@db01 dbs]$ export ORACLE_SID=PROD01

2、待调整的pfile参数文件内容

db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

3、调整后的参数文件内容

db_name='PROD01'
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/admin/PROD01/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=PROD01XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (/u01/app/oracle/ordata/PROD01/control1.ctl, /u01/app/oracle/ordata/PROD01/control2.ctl)
compatible ='11.2.0'

4、.bash_profile文件

vi .bash_profile

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.0.2/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=PROD01

使环境变量生效

$ source .bash_profile

验证环境变量

$ echo $ORACLE_HOME

$ echo $ORACLE_SID

5、创建initPROD01.ora所需要的文件目录

进入ORACLE_BASE目录 创建initPROD01.ora所需要的文件目录

cd $ORACLE_BASE

mkdir -p admin/PROD01/adump

mkdir -p ordata/PROD01

6、根据pfile参数文件生成spfile参数文件

sqlplus / as sysdba

SQL> create spfile from pfile;

SQL> startup nomount;

Oracle数据库四种参数文件，依次优先

1. spfilePROD01.ora
2. spfile.ora
3. initPROD01.ora
4. init.ora

7、生成密码文件

cd $ORACLE_HOME/dbs

orapwd file=orapwPROD01 password=oracle

8、手工建库的create database PROD01

官方文件参考位置

Books--> Administrator's Guide --> 2 Createing and Configuring an Oracle Database

--> Createing a Database with the CREATE DATABASE Statment

--> Step9: Issue the CREATE DATABASE Statement

需要调整的内容列表

01、修改数据名为 PROD01

02、修改SYS和SYSTEM 用户的密码为 oracle

03、修改数据文件和日志文件的路径名

CREATE DATABASE PROD01
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY oracle
   LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512,
                    GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512,
                    GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512
   MAXLOGHISTORY 1
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 1024
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/PROD01/system01.dbf'
     SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD01/sysaux01.dbf'
     SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/PROD01/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/PROD01/temp01.dbf'
      SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
   UNDO TABLESPACE undotbs1
      DATAFILE '/u01/app/oracle/oradata/PROD01/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
   USER_DATA TABLESPACE usertbs
      ATAFILE '/u01/app/oracle/oradata/PROD01/usertbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

9、执行必选脚本

数据字典动态性能视图创建过程 cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba

SYS@PROD01> @crtdb.sql

Database created.

SYS@PROD01> @?/rdbms/admin/catalog.sql

SYS@PROD01> @?/rdbms/admin/catproc.sql

SYS@PROD01> conn system/oracle

SYS@PROD01> @?/sqlplus/admin/pupbld.sql

SYS@PROD01> selec * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SYS@PROD01> select open_mode,name,dbid from v$database;
OPEN_MODE                   NAME                      DBID
-----------------------  --------------------------  ----------
READ WRITE                  PROD01                   1916288830