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

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查看字符集：

注：之前修正报错如下：

补充：

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