linux环境下创建数据库

前言：

业务系统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 ORAORACLE_SID=dwdbORACLE_BASE=/oracleORACLE_HOME=/oracle/product/11.2.0/db_1[oracle@dwtest ~]$[oracle@dwtest ~]$ sqlplus /nologSQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 09:27:52 2016Copyright (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.orainithsdb1.ora 中需要添加的内容如下[oracle@dwtest dbs]$ cat inithsdb1.oradb_name='hsdb1'memory_target=4294967296processes = 1000audit_file_dest='/oracle/admin/hsdb1/adump'audit_trail ='db'db_block_size=8192db_domain=''db_recovery_file_dest='/oracle/hsdb1/flash_recovery_area'db_recovery_file_dest_size=4385144832diagnostic_dest='/oracle'dispatchers='(PROTOCOL=TCP) (SERVICE=hsdb1XDB)'open_cursors=300remote_login_passwordfile='EXCLUSIVE'undo_tablespace='UNDOTBS2'# You may want to ensure that control files are created on separate physical# devicescontrol_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.sqlhsdb1.sql中需要添加的内容如下[oracle@dwtest oracle]$ vi spdbface.sqlspool dbcreate.log;spool dbcreate.log;create DATABASE "hsdb1"MAXDATAFILES 500MAXINSTANCES  8MAXLOGFILES   32CHARACTER SET "ZHS16GBK"NATIONAL CHARACTER SET AL16UTF16ARCHIVELOGDATAFILE'/oracle/hsdb1/system01.dbf' SIZE 300MSYSAUX DATAFILE'/oracle/hsdb1/sysaux01.dbf' SIZE 120MEXTENT MANAGEMENT LOCALDEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/oracle/hsdb1/tempts01.dbf' SIZE 100M EXTENTMANAGEMENT LOCALUNDO TABLESPACE "UNDOTBS2"DATAFILE '/oracle/hsdb1/undotbs01.dbf' SIZE 200MLOGFILEGROUP 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 /nologSQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 10:05:15 2016Copyright (c) 1982, 2013, Oracle.  All rights reserved.SQL> connect /as sysdbaConnected to an idle instance.SQL> create spfile from pfile;File created.SQL> startup nomountORACLE instance started.Total System Global Area 4275781632 bytesFixed Size            2260088 bytesVariable Size         2650801032 bytesDatabase Buffers     1610612736 bytesRedo Buffers           12107776 bytesSQL>

9执行数据库脚本

@$ORACLE_HOME/dbs/hsdb1.sql

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.sqlSQL> SELECT dbms_registry_sys.time_stamp('CATPROC') AS timestamp FROM DUAL;TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP CATPROC    2016-05-25 10:21:091 row selected.SQL>SQL> SET SERVEROUTPUT OFFSQL>@?/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 startLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-MAY-2016 10:30:28Copyright (c) 1991, 2013, Oracle.  All rights reserved.TNS-01106: Listener using listener name LISTENER has already been started

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

注：之前修正报错如下：

补充：

在 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。