首页 > 代码库 > 手工建库
手工建库
①关闭现有数据库：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
查看参数文件：
[oracle@wang ~]$ cd $ORACLE_HOME/dbs
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora
[oracle@wang dbs]$
生成pfile文件：
[oracle@wang dbs]$ cat init.ora |grep -v ^# |grep -v ^$ > initPROD.ora
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora initPROD.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora [oracle@wang dbs]$
修改initPROD.ora文件：
[oracle@wang dbs]$ vi initPROD.ora
db_name=‘PROD‘
memory_target=800M
processes = 150
audit_file_dest=‘/u01/app/oracle/admin/PROD/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‘
control_files = (/u01/app/oracle/oradata/PROD/control1.ctl, /u01/app/oracle/oradata/PROD/control2.ctl)
compatible =‘11.2.0‘
~
"initPROD.ora" 16L, 531C written
[oracle@wang dbs]$
根据pfile创建目录adump目录和控制文件目录：
[oracle@wang dbs]$ mkdir -p /u01/app/oracle/admin/PROD/adump
[oracle@wang dbs]$ mkdir -p /u01/app/oracle/oradata/PROD/
[oracle@wang dbs]$ cd $ORACLE_BASE
[oracle@wang oracle]$ ls
admin cfgtoollogs checkpoints diag oradata product
[oracle@wang oracle]$ cd admin/
[oracle@wang admin]$ ls
ORA11GR2 prod
[oracle@wang admin]$ cd ..
[oracle@wang oracle]$ cd oradata/
[oracle@wang oradata]$ ls
ORA11GR2 PROD
[oracle@wang oradata]$
通过pfile创建spfile：
--查看已存在的pfile文件initPROD.ora
oracle@wang dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora initPROD.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora
[oracle@wang dbs]$
--验证当前环境变量
[oracle@wang oradata]$ echo $ORACLE_SID
ORA11GR2
更改当前环境变量ORACLE_SID：
[oracle@wang oradata]$ export ORACLE_SID=PROD
[oracle@wang oradata]$ echo $ORACLE_SID
PROD
--通过pfile创建spfile
[oracle@wang oradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 19 10:22:17 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
验证生成spfile：
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora initPROD.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora spfilePROD.ora
数据库启动到nomount（默认情况下，oracle会使用spfile启动数据库）：
SYS>startup nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ -----------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilePROD.ora
SQL> select status from v$instance;
STATUS
------------
STARTED
编写创建数据库脚本：
[oracle@wang dbs]$ cd $ORACLE_HOME
[oracle@wang dbhome_1]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@wang dbhome_1]$
[oracle@wang dbhome_1]$
[oracle@wang dbhome_1]$ vi create.sql
create database PROD
user sys identified by oracle
user system identified by oracle
logfile group 1 (‘/u01/app/oracle/oradata/PROD/redo01.log‘) size 30m blocksize 512,
group 2 (‘/u01/app/oracle/oradata/PROD/redo02.log‘) size 30m blocksize 512,
group 3 (‘/u01/app/oracle/oradata/PROD/redo03.log‘) size 30m blocksize 512
maxlogfiles 5
maxlogmembers 5
maxloghistory 1
maxdatafiles 100
character set us7ascii
national character set al16utf16
extent management local
datafile ‘/u01/app/oracle/oradata/PROD/system01.dbf‘ size 325m reuse
sysaux datafile ‘/u01/app/oracle/oradata/PROD/sysaux01.dbf‘ size 325m reuse
default tablespace users
datafile ‘/u01/app/oracle/oradata/PROD/users01.dbf‘
size 50m reuse autoextend on maxsize unlimited
default temporary tablespace tempts1
tempfile ‘/u01/app/oracle/oradata/PROD/temp01.dbf‘
size 20m reuse
undo tablespace undotbs1
datafile ‘/u01/app/oracle/oradata/PROD/undotbs01.dbf‘
size 50m reuse autoextend on maxsize unlimited;
"create.sql" [New] 25L, 1139C written
[oracle@wang dbhome_1]$
SQL> @?/create
Database created.
--查看相关参数：
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ -----------
control_files string /u01/app/oracle/oradata/PROD/c
ontrol01.ctl, /u01/app/oracle/
oradata/PROD/control02.ctl
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ -----------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ -----------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilePROD.ora
SQL>
创建字典表及工具包
--必执行脚本
SQL> conn / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> conn system/oracle
SQL> @?/sqlplus/admin/pupbld.sql
--可选脚本
SQL> conn / as sysdba
SQL> @?/rdbms/admin/catblock.sql
SQL> @?/rdbms/admin/catoctk.sql
SQL> @?/rdbms/admin/owminst.plb
验证数据库可用性：
SQL> select * 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
5 rows selected.
创建密码文件：（可建可不建）
[oracle@wang dbhome_1]$ cd dbs/
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat initPROD.ora spfileORA11GR2.ora
hc_PROD.dat lkORA11GR2 spfilePROD.ora
init.ora lkPROD
initORA11GR2.ora orapwORA11GR2
[oracle@wang dbs]$ orapwd file=orapwPROD password=oracle
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat initPROD.ora orapwPROD
hc_PROD.dat lkORA11GR2 spfileORA11GR2.ora
init.ora lkPROD spfilePROD.ora
initORA11GR2.ora orapwORA11GR2
[oracle@wang dbs]$
手工建库