linux环境下创建数据库

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/jinlinxie/article/details/52223874

前言:

业务系统oralce 数据库版本为10gR2,同时字符集为ZHS16GBK,而中间库对应的版本是11gR2 同时字符集为UTF8,由于 数据库字符集在创建数据库时指定,在创建后通常不能更改。所以中间库需要新增定义以字符集为ZHS16GBK的实例。

注释:新增SID:hsdb1

步骤:

1登录终端,并切换至Oracle 用户

Connecting to 10.7.101.24:22...

Connection established.

To escape to local shell, press 'Ctrl+Alt+]'.

Last login: Wed May 25 09:20:05 2016 from 172.16.112.92

[root@dwtest ~]# su - oracle

2查看当前oracle变量和sqlplus运行情况

[oracle@dwtest ~]$ env |grep ORA
ORACLE_SID=dwdb
ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/product/11.2.0/db_1
[oracle@dwtest ~]$

[oracle@dwtest ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 09:27:52 2016

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

SQL> exit
[oracle@dwtest ~]$

3设置新的实例

[oracle@dwtest ~]$  export ORACLE_SID=hsdb1

4新实例创建密码文件

[oracle@dwtest ~]$ orapwd file=$ORACLE_HOME/dbs/orapwhsdb1  password=oracle entries=10

5创建pfile文件

[oracle@dwtest ~]$ cd $ORACLE_HOME/dbs
[oracle@dwtest admin]$ vi inithsdb1.ora

inithsdb1.ora 中需要添加的内容如下

[oracle@dwtest dbs]$ cat inithsdb1.ora
db_name='hsdb1'
memory_target=4294967296
processes = 1000
audit_file_dest='/oracle/admin/hsdb1/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/oracle/hsdb1/flash_recovery_area'
db_recovery_file_dest_size=4385144832
diagnostic_dest='/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=hsdb1XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS2'
# You may want to ensure that control files are created on separate physical
# devices
control_files=(/oracle/hsdb1/ora_control1,/oracle/hsdb1/ora_control2)
compatible ='11.2.0.4.0'
undo_management=AUTO


6创建实例相关的目录

[oracle@dwtest /]$ mkdir -p $ORACLE_BASE/admin/hsdb1/adump
[oracle@dwtest /]$ mkdir -p $ORACLE_BASE/admin/hsdb1/bdump
[oracle@dwtest /]$ mkdir -p $ORACLE_BASE/admin/hsdb1/cdump
[oracle@dwtest /]$ mkdir -p $ORACLE_BASE/admin/hsdb1/udump
[oracle@dwtest /]$ mkdir -p $ORACLE_BASE/admin/hsdb1/pfile 

7创建一个数据库执行脚本

[oracle@dwtest /]$ mkdir /oracle/hsdb1
[oracle@dwtest oracle]$ mkdir /oracle/hsdb1/flash_recovery_area

[oracle@dwtest /]$ cd $ORACLE_HOME/dbs
[oracle@dwtest dbs]$ touch hsdb1.sql

hsdb1.sql中需要添加的内容如下

[oracle@dwtest oracle]$ vi spdbface.sql

spool dbcreate.log;
spool dbcreate.log;
create DATABASE "hsdb1"
MAXDATAFILES 500
MAXINSTANCES  8
MAXLOGFILES   32
CHARACTER SET "ZHS16GBK"
NATIONAL CHARACTER SET AL16UTF16
ARCHIVELOG
DATAFILE
'/oracle/hsdb1/system01.dbf' SIZE 300M
SYSAUX DATAFILE
'/oracle/hsdb1/sysaux01.dbf' SIZE 120M
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/oracle/hsdb1/tempts01.dbf' SIZE 100M EXTENT
MANAGEMENT LOCAL
UNDO TABLESPACE "UNDOTBS2"
DATAFILE '/oracle/hsdb1/undotbs01.dbf' SIZE 200M
LOGFILE
GROUP 1(
'/oracle/hsdb1/redo01a.rdo',
'/oracle/hsdb1/redo01b.rdo'
) SIZE 100M,
GROUP 2(
'/oracle/hsdb1/redo02a.rdo',
'/oracle/hsdb1/redo02b.rdo'
)SIZE 100M,
GROUP 3(
'/oracle/hsdb1/redo03a.rdo',
'/oracle/hsdb1/redo03b.rdo'
)SIZE 100M
;
spool off

8创建spfile并将并将数据库启动到nomount 状态

[oracle@dwtest oracle]$  sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 10:05:15 2016

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

SQL> connect /as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size            2260088 bytes
Variable Size         2650801032 bytes
Database Buffers     1610612736 bytes
Redo Buffers           12107776 bytes
SQL>

9执行数据库脚本

@$ORACLE_HOME/dbs/hsdb1.sql

Clipboard Image.png

10创建表空间

create tablespace hsdb1_db datafile'/oracle/hsdb1/hsdb1_db.dbf' size 500M autoextend on next 1000M maxsize unlimited extent management local segment space management auto;

11创建数据字典

SQL>@?/rdbms/admin/catalog.sql 
SQL>@?/rdbms/admin/catproc.sql
SQL> SELECT dbms_registry_sys.time_stamp('CATPROC') AS timestamp FROM DUAL;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATPROC    2016-05-25 10:21:09

1 row selected.

SQL>
SQL> SET SERVEROUTPUT OFF

SQL>@?/sqlplus/admin/pupbld.sql

12创建用户

SQL> create user hs identified by  FoticHs;

User created.

grant create session,create table,create view ,create sequence,unlimited tablespace to hs;

SQL> grant create session,create table,create view ,create sequence,unlimited tablespace to hs;

Grant succeeded.

13创建监听器

 SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = hsdb1)
      (ORACLE_HOME = /oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL DBNAME = orcl) 
      (SID_NAME = spdbface)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

14启动监听器

[oracle@dwtest dbs]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-MAY-2016 10:30:28

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

TNS-01106: Listener using listener name LISTENER has already been started

15验证登录PL/sql Developer查看字符集:

Clipboard Image.png


注:之前修正报错如下:

Clipboard Image.png


补充:

在 11g 中,引入 Automatic Memory Management(自动内存管理,AMM)。通过使用两个参数,MEMORY_MAX_TARGET 和 ,可以启用

 PGA 和 SGA 的自动调整。

AMM参数

自动内存管理是用两个初始化参数进行配置的:

MEMORY_TARGET:动态控制SGA和PGA时,Oracle总共可以使用的共享内存大小,这个参数是动态的,因此提供给Oracle的内存总量是可以动态增大,也可以动态减小的。它不能超过MEMORY_MAX_TARGET参数设置的大小。默认值是0。

MEMORY_MAX_TARGET:这个参数定义了MEMORY_TARGET最大可以达到而不用重启实例的值,如果没有设置MEMORY_MAX_TARGET值,默认等于MEMORY_TARGET的值。

使用动态内存管理时,SGA_TARGET和PGA_AGGREGATE_TARGET代表它们各自内存区域的最小设置,要让Oracle完全控制内存管理,这两个参数应该设置为0。


Clipboard Image.png
展开阅读全文
博主设置当前文章不允许评论。

没有更多推荐了,返回首页