1:关闭数据库
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
2：查看参数文件
[oracle@ds ~]$
[oracle@ds ~]$ cd $ORACLE_HOME/dbs
[oracle@ds dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@ds dbs]$ ls
hc_ORA11GR2.dat init.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora
[oracle@ds dbs]$
3:生成pfile文件
[oracle@ds dbs]$ cat init.ora |grep -v ^#|grep -v ^$ > initPROD.ora
[oracle@ds dbs]$ ls
hc_ORA11GR2.dat init.ora initPROD.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora
[oracle@ds dbs]$
4:编辑pfile文件
[oracle@ds dbs]$ vi initPROD.ora
db_name='PROD'
memory_target=600M    #--只能大不能小
processes = 150
#--注意要校验此路径，在下一步创建目录时，要相同
audit_file_dest='/u01/app/oracle/admin/prod/adump'
#audit_trail ='db'
#--把审计的参数注释掉，去掉这个功能，DBCA建库，默认情况下是启用的。
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表空间名称，在创建数据库时名称要匹配上
undo_tablespace='UNDOTBS1'
#--修改控制文件的名字及位置
control_files = (/u01/app/oracle/oradata/PROD/ora_control1.ctl,/u01/app/oracle/oradata/PROD/ora_control2.ctl)
compatible ='11.2.0'
5:根据pfile创建目录adump目录和控制文件目录
[oracle@ds dbs]$
[oracle@ds dbs]$ mkdir -p $ORACLE_BASE/admin/PROD/adump
[oracle@ds dbs]$ mkdir -p $ORACLE_BASE/oradata/PROD/
[oracle@ds dbs]$
#--查看已存在的initPROD.ora文件
[oracle@ds ~]$ cd $ORACLE_HOME/dbs
[oracle@ds dbs]$
[oracle@ds dbs]$ ls
hc_ORA11GR2.dat initPROD.ora orapwORA11GR2
init.ora         lkORA11GR2    spfileORA11GR2.ora
[oracle@ds dbs]$
#--验证当前环境变量
[oracle@ds dbs]$ echo $ORACLE_SID
ORA11GR2
[oracle@ds dbs]$ export ORACLE_SID=PROD
[oracle@ds dbs]$ echo $ORACLE_SID
PROD
[oracle@ds dbs]$
6:通过pfile生成spfile文件
[oracle@ds dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 28 17:42:47 2016

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> !ls
hc_ORA11GR2.dat initPROD.ora orapwORA11GR2       spfilePROD.ora
init.ora         lkORA11GR2    spfileORA11GR2.ora
7:数据库启动到nomount（默认情况下，oracle会使用spfile启动数据库）
SQL> startup nomount
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             427820072 bytes
Database Buffers          188743680 bytes
Redo Buffers                7507968 bytes
SQL>
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

SQL>
8:#编写创建数据库脚本
Oracle11g官方文档：
Administrator’s Guide->2 Creating and Configuring an Oracle Database->Step 9: Issue the CREATE DATABASE Statement
1)修改数据库名称及相关口令
2)由于是测试环境，所以，可以将日志文件改为1个成员，把成员大小改为10M
3)修改users表空间大小及undo表空间大小
4)修改undo表空间的名称，要与pfile文件中的名称相同
[oracle@ds dbhome_1]$ vi zz.sql
CREATE DATABASE PROD
      USER SYS IDENTIFIED BY oracle
       USER SYSTEM IDENTIFIED BY oracle
       LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/redo01a.log') SIZE 10M BLOCKSIZE 512,
               GROUP 2 ('/u01/app/oracle/oradata/PROD/redo02a.log') SIZE 10M BLOCKSIZE 512,
              GROUP 3 ('/u01/app/oracle/oradata/PROD/redo03a.log') SIZE 10M 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;

[oracle@ds dbhome_1]$
[oracle@ds dbhome_1]$
[oracle@ds dbhome_1]$
9:创建数据库（数据库此时的状态应该是nomount，直接在sqlplus下执行创建数据库脚本即可）
SQL> @?/zz.sql或者@?/zz（默认找 .sql）,@执行脚本，？指$ORACLE_HOME.
SQL> CREATE DATABASE PROD
2        USER SYS IDENTIFIED BY oracle
3         USER SYSTEM IDENTIFIED BY oracle
4         LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/redo01a.log') SIZE 10M BLOCKSIZE 512,
5                 GROUP 2 ('/u01/app/oracle/oradata/PROD/redo02a.log') SIZE 10M BLOCKSIZE 512,
6                GROUP 3 ('/u01/app/oracle/oradata/PROD/redo03a.log') SIZE 10M BLOCKSIZE 512
7         MAXLOGFILES 5
8        MAXLOGMEMBERS 5
9         MAXLOGHISTORY 1
10        MAXDATAFILES 100
11        CHARACTER SET US7ASCII
12        NATIONAL CHARACTER SET AL16UTF16
13        EXTENT MANAGEMENT LOCAL
14        DATAFILE '/u01/app/oracle/oradata/PROD/system01.dbf' SIZE 325M REUSE
15        SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/sysaux01.dbf' SIZE 325M REUSE
16        DEFAULT TABLESPACE users
17           DATAFILE '/u01/app/oracle/oradata/PROD/users01.dbf'
18          SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
19        DEFAULT TEMPORARY TABLESPACE tempts1
20           TEMPFILE '/u01/app/oracle/oradata/PROD/temp01.dbf'
21           SIZE 20M REUSE
22        UNDO TABLESPACE UNDOTBS1
23          DATAFILE '/u01/app/oracle/oradata/PROD/undotbs01.dbf'
24          SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Database created.

SQL>
#--查看参数
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL>
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/PROD/o
                                                 ra_control1.ctl, /u01/app/orac
                                                 le/oradata/PROD/ora_control2.c
                                                 tl
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilePROD.ora
SQL>
10-创建字典表及工具包
--必执行脚本
SQL> conn / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> conn system/passwd    #--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.

SQL>
11:创建密码文件：（可建可不建）
[oracle@ds dbs]$ ls
hc_ORA11GR2.dat init.ora      lkORA11GR2 orapwORA11GR2       spfilePROD.ora
hc_PROD.dat      initPROD.ora lkPROD      spfileORA11GR2.ora
[oracle@ds dbs]$
#——查看显示没有建密码文件，创建密码文件（作用：允许远程用密码方式以sysdba身份登陆数据库，密码文件可有可无）
[oracle@ds dbs]$ orapwd file=orapwPROD password=oracle
[oracle@ds dbs]$ ls
hc_ORA11GR2.dat init.ora      lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora
hc_PROD.dat      initPROD.ora lkPROD      orapwPROD      spfilePROD.ora
[oracle@ds dbs]$
数据库手工建库完成！
#--验证查看
[oracle@ds dbs]$ cd /u01/app/oracle/oradata/PROD/
[oracle@ds PROD]$ ls
ora_control1.ctl redo01a.log redo03a.log   system01.dbf undotbs01.dbf
ora_control2.ctl redo02a.log sysaux01.dbf temp01.dbf    users01.dbf
[oracle@ds PROD]$ echo $ORACLE_SID
PROD
[oracle@ds PROD]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 28 19:13:48 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      PROD
db_unique_name                       string      PROD
global_names                         boolean     FALSE
instance_name                        string      PROD
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      PROD
SQL>
#--GG！！！！
   Good luck

ITPub博客

手工建库

最新文章