oracle install manually(手动建库)

于 2017-09-22 12:29:16 发布
阅读量549 收藏
点赞数 2
分类专栏： ORACLE BASE
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/stephen11111/article/details/78061640
版权

环境变量

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

数据库软件已经安装：测试手动建库：

大致的步骤如下：
1 新建参数密码文件
2 create db
3 执行创建视图，同义词，存储过程的脚本。

[oracle@hdp dbs]$ cat initorcl.ora 
db_name=orcl
db_unique_name=orcl
compatible=11.2.0
control_files=/u01/app/oracle/oradata/control001.ctl,/u01/app/oracle/oradata/control002.ctl
db_create_file_dest=/u01/app/oracle/oradata/
SQL> show parameter sga

NAME                     TYPE                  VALUE
------------------------------------ --------------------------------- ------------------------------
lock_sga                 boolean                   FALSE
pre_page_sga                 boolean                   FALSE
sga_max_size                 big integer               252M
sga_target               big integer               0
[oracle@hdp ~]$ cat create_db.sql 
create database orcl  
 logfile  
 group 1 ('/u01/app/oracle/oradata/redo1.log') size 50m,  
 group 2 ('/u01/app/oracle/oradata/redo2.log') size 50m,  
 group 3 ('/u01/app/oracle/oradata/redo3.log') size 50m  
 MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL  
 datafile  
 '/u01/app/oracle/oradata/system.dbf' size 200m reuse  
 sysaux datafile '/u01/app/oracle/oradata/sysaux.dbf' size 100m  
 default tablespace users  
 datafile '/u01/app/oracle/oradata/userdata.dbf' size 100m  
 undo tablespace undotbs1  
 datafile '/u01/app/oracle/oradata/undo1.dbf' size 100m  
 default temporary tablespace temp1  
 tempfile '/u01/app/oracle/oradata/temp01.dbf'  
 size 100m reuse;
 SQL>@create_db.sql
Database created.
SQL>@catalog.sql

跑完这两个脚本，数据库已经在open状态了！

 SQL> select status from v$instance
  2  /

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

不过有报错

ORA-1653: unable to extend table SYS.SOURCE$ by 128 in                 tablespace SYSTEM 
ORA-1653: unable to extend table SYS.SOURCE$ by 128 in                 tablespace SYSTEM

因为system表空间的空间不足，导致很多存储过程都没有创建，系统无法正常使用

很明显以上脚本（来源网络）有缺陷，得修改

新建密码文件（注意密码文件的名字一定要正确哦！）

[oracle@hdp admin]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=Oracle123 entries=100

system ,sysaux 的空间都不能太小，根据经验

create database orcl  
 logfile  
 group 1 ('/u01/app/oracle/oradata/redo1.log') size 50m,  
 group 2 ('/u01/app/oracle/oradata/redo2.log') size 50m,  
 group 3 ('/u01/app/oracle/oradata/redo3.log') size 50m  
 MAXLOGFILES 10
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL  
 datafile  
 '/u01/app/oracle/oradata/system.dbf' size 750m  autoextend on next 200m maxsize unlimited   
 sysaux datafile '/u01/app/oracle/oradata/sysaux.dbf' size 500m   autoextend on next 200m maxsize unlimited  
 default tablespace users  
 datafile '/u01/app/oracle/oradata/userdata.dbf' size 200m   
 undo tablespace undotbs1  
 datafile '/u01/app/oracle/oradata/undo1.dbf' size 200m  
 default temporary tablespace temp1  
 tempfile '/u01/app/oracle/oradata/temp01.dbf'  
 size 200m reuse;

再来一次

 SQL> startup nomount;
ORACLE instance started.

Total System Global Area  263090176 bytes
Fixed Size          2252256 bytes
Variable Size         205521440 bytes
Database Buffers       50331648 bytes
Redo Buffers            4984832 bytes


SQL> @create_db.sql

Database created.

SQL> select status from v$instance
  2  /

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

看看alert.log的输出

ORACLE_BASE from environment = /u01/app/oracle
Sat Sep 02 22:36:05 2017
create database orcl
 logfile
 group 1 ('/u01/app/oracle/oradata/redo1.log') size 50m,
 group 2 ('/u01/app/oracle/oradata/redo2.log') size 50m,
 group 3 ('/u01/app/oracle/oradata/redo3.log') size 50m
 MAXLOGFILES 10
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
 datafile
 '/u01/app/oracle/oradata/system.dbf' size 750m  autoextend on next 200m maxsize unlimited
 sysaux datafile '/u01/app/oracle/oradata/sysaux.dbf' size 500m   autoextend on next 200m maxsize unlimited
 default tablespace users
 datafile '/u01/app/oracle/oradata/userdata.dbf' size 200m
 undo tablespace undotbs1
 datafile '/u01/app/oracle/oradata/undo1.dbf' size 200m
 default temporary tablespace temp1
 tempfile '/u01/app/oracle/oradata/temp01.dbf'
 size 200m reuse
Database mounted in Exclusive Mode
Lost write protection disabled
Successful mount of redo thread 1, with mount id 1481420501
Database SCN compatibility initialized to 1
Assigning activation ID 1481420501 (0x584caed5)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/redo1.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
processing ?/rdbms/admin/dcore.bsq
create tablespace SYSTEM datafile 
 '/u01/app/oracle/oradata/system.dbf' size 750m  autoextend on next 200m maxsize unlimited

  EXTENT MANAGEMENT LOCAL online
Completed: create tablespace SYSTEM datafile 
 '/u01/app/oracle/oradata/system.dbf' size 750m  autoextend on next 200m maxsize unlimited

  EXTENT MANAGEMENT LOCAL online
create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
UNDO_SEG_CRT: Could not find usn tail
Completed: create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
Undo initialization finished serial:0 start:1226184 end:1226184 diff:0 (0 seconds)
processing ?/rdbms/admin/dsqlddl.bsq
processing ?/rdbms/admin/dmanage.bsq
CREATE TABLESPACE sysaux DATAFILE  '/u01/app/oracle/oradata/sysaux.dbf' size 500m   autoextend on next 200m maxsize unlimited

  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
Completed: CREATE TABLESPACE sysaux DATAFILE  '/u01/app/oracle/oradata/sysaux.dbf' size 500m   autoextend on next 200m maxsize unlimited

  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
processing ?/rdbms/admin/dplsql.bsq
processing ?/rdbms/admin/dtxnspc.bsq
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE  '/u01/app/oracle/oradata/undo1.dbf' size 200m

[2885] Successfully onlined Undo Tablespace 2.
Completed: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE  '/u01/app/oracle/oradata/undo1.dbf' size 200m

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE  '/u01/app/oracle/oradata/temp01.dbf'
 size 200m reuse
Completed: CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE  '/u01/app/oracle/oradata/temp01.dbf'
 size 200m reuse
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1
Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1
CREATE  TABLESPACE USERS DATAFILE  '/u01/app/oracle/oradata/userdata.dbf' size 200m
  SEGMENT SPACE MANAGEMENT AUTO
Completed: CREATE  TABLESPACE USERS DATAFILE  '/u01/app/oracle/oradata/userdata.dbf' size 200m
  SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DEFAULT TABLESPACE USERS
Completed: ALTER DATABASE DEFAULT TABLESPACE USERS
processing ?/rdbms/admin/dfmap.bsq
processing ?/rdbms/admin/denv.bsq
processing ?/rdbms/admin/drac.bsq
processing ?/rdbms/admin/dsec.bsq
processing ?/rdbms/admin/doptim.bsq
processing ?/rdbms/admin/dobj.bsq
processing ?/rdbms/admin/djava.bsq
processing ?/rdbms/admin/dpart.bsq
processing ?/rdbms/admin/drep.bsq
processing ?/rdbms/admin/daw.bsq
processing ?/rdbms/admin/dsummgt.bsq
processing ?/rdbms/admin/dtools.bsq
processing ?/rdbms/admin/dexttab.bsq
processing ?/rdbms/admin/ddm.bsq
processing ?/rdbms/admin/dlmnr.bsq
processing ?/rdbms/admin/ddst.bsq
Sat Sep 02 22:36:15 2017
SMON: enabling tx recovery
Starting background process SMCO
Sat Sep 02 22:36:15 2017
SMCO started with pid=18, OS id=2892 
Sat Sep 02 22:36:16 2017
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Sep 02 22:36:16 2017
QMNC started with pid=19, OS id=2894 
Completed: create database orcl
 logfile
 group 1 ('/u01/app/oracle/oradata/redo1.log') size 50m,
 group 2 ('/u01/app/oracle/oradata/redo2.log') size 50m,
 group 3 ('/u01/app/oracle/oradata/redo3.log') size 50m
 MAXLOGFILES 10
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
 datafile
 '/u01/app/oracle/oradata/system.dbf' size 750m  autoextend on next 200m maxsize unlimited
 sysaux datafile '/u01/app/oracle/oradata/sysaux.dbf' size 500m   autoextend on next 200m maxsize unlimited
 default tablespace users
 datafile '/u01/app/oracle/oradata/userdata.dbf' size 200m
 undo tablespace undotbs1
 datafile '/u01/app/oracle/oradata/undo1.dbf' size 200m
 default temporary tablespace temp1
 tempfile '/u01/app/oracle/oradata/temp01.dbf'
 size 200m reuse

 cd $ORACLE_HOME/rdbms/admin/
 SQL>@catalog.sql
-- 创建数据库的视图和同义词（动态性能视图等）

 SQL>@catproc.sql
 -- plsql相关联的

确保都能执行成功

PL/SQL procedure successfully completed.


TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP CATALOG    2017-09-02 22:39:50



PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT dbms_registry_sys.time_stamp('CATPROC') AS timestamp FROM DUAL;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATPROC    2017-09-02 22:47:22

1 row selected.

执行以下语句时报错：

SQL> conn hdp/123123
ERROR:
ORA-00942: table or view does not exist


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.

处理办法：
这块的报错涉及sql*plus的设置

[oracle@hdp app]$ find /u01/app/ -name pupbld.sql
/u01/app/oracle/product/11.2.0/db_1/sqlplus/admin/pupbld.sql
[oracle@hdp app]$ sqlplus system/manager
SQL> @/u01/app/oracle/product/11.2.0/db_1/sqlplus/admin/pupbld.sql

再做一些处理操作保证数据库的正常运行即可：

测试一下建表，建用户

SQL> @/tmp/1.sql

PL/SQL procedure successfully completed.

SQL> desc t 
 Name                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ID                                        NUMBER

SQL> select count(*) from t 
  2  /

  COUNT(*)
----------
      1000

还是创建 spfile 比较利于系统的正常运行 

SQL> conn sys/..  as sysdba
Connected.
SQL> create spfile from pfile
  2  /

File created.

SQL> startup force
ORACLE instance started.

Total System Global Area  263090176 bytes
Fixed Size          2252256 bytes
Variable Size         205521440 bytes
Database Buffers       50331648 bytes
Redo Buffers            4984832 bytes
Database mounted.
Database opened.


SQL> show parameter spfile

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
spfile                   string  /u01/app/oracle/product/11.2.0
                         /db_1/dbs/spfileorcl.ora
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值