以Create Database 方式在已有Oracle实例的主机新增实例

已有实例环境：

Oracle 11.2.0.4 + Linux redhat-7

echo $ORACLE_BASE

/u01/app/

echo $ORACLE_HOME

/u01/app/product/11.2.0.4/dbhome_1

echo $ORACLE_SID

nmsdb

整体流程：创建实例目录，创建参数文件，创建密码文件，创建建库脚本并建库，创建数据字典，配置监听及TNS。

新增实例名为 evansdb

一、创建新实例 evansdb目录

cd $ORACLE_BASE/admin
mkdir evansdb
cd evansdb
mkdir adump  bdump  cdump  pfile  udump bpdump
mk -p $ORACLE_BASE/flash_recovery_area/evansdb

cd $ORACLE_BASE 
cd oradata 
mkdir evansdb

二、创建初始化文件：(文件名字须为init实例名.ora)

cd $ORACLE_HOME/dbs

vi initevansdb.ora 内容如下：(添加时最好删除注释)

#下面的参数描述了实例相关池的分配情况
evansdb.__db_cache_size=1,932735283 #1.8GM 理论上是60%sga
evansdb.__java_pool_size=16777216 #如果没用到数据库端java的系统，30MB足够。
evansdb.__large_pool_size=16777216
evansdb.__shared_pool_size=369098752 #350M
evansdb.__streams_pool_size=0
evansdb.__shared_io_pool_size=0
#SGA+PGA
#*.memory_target=1073741824  #非0，则表示启用AMM
evansdb.__pga_aggregate_target=536870912 
evansdb.__sga_target=3581225472   
evansdb.__oracle_base='/u01/app'
#审计日志及目录
*.audit_file_dest='/u01/app/admin/evansdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oradata/evansdb/control01.ctl','/u01/app/flash_recovery_area/evansdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='evansdb'
*.db_recovery_file_dest='/u01/app/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=evansdbXDB)'
*.local_listener='' #最好为空
*.open_cursors=300
*.processes=2000
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
 

三、生成密码文件

[oracle@mysql-evans ~]$  $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwevansdb password=Evansliang123 force=y  
# 文件生成在$ORACLE_HOME/dbs目录下

四、创建建库脚本并建库

CREATE DATABASE evansdb 
USER SYS IDENTIFIED BY sys 
USER SYSTEM IDENTIFIED BY system
LOGFILE GROUP 1 ('/u01/app/oradata/evansdb/redo01.log') SIZE 20M,
GROUP 2 ('/u01/app/oradata/evansdb/redo02.log') SIZE 20M,
GROUP 3 ('/u01/app/oradata/evansdb/redo03.log') SIZE 20M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1

CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16

DATAFILE '/u01/app/oradata/evansdb/system01.dbf' SIZE 5G REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oradata/evansdb/sysaux01.dbf' SIZE  5G REUSE
DEFAULT TABLESPACE USERS datafile '/u01/app/oradata/evansdb/users1.dbf' size 5G
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oradata/evansdb/temp01.dbf'
SIZE 3G REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oradata/evansdb/undotbs1.dbf'
SIZE 3G REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

select instance_name,status from v$instance;
 # open表示成功

五、创建数据字典及相关视图

conn sys as sysdba  # 登录
@?/rdbms/admin/catalog.sql;
-- 此过程可能需要10分钟左右
@?/rdbms/admin/catproc.sql;
-- 此过程可能需要15分钟左右
@?/rdbms/admin/catblock.sql;
@?/rdbms/admin/catoctk.sql;
@?/rdbms/admin/owminst.plb;

#以下使用system用户执行

conn system/ manager
@?/sqlplus/admin/pupbld.sql;
@?/sqlplus/admin/help/hlpbld.sql helpus.sql

六、配置 listener.ora 和 tnsnames.ora

evansdd-LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = mysql-evans)(PORT = 1521))
      (GLOBAL_DBNAME = evansdb         
           (ORACLE_HOME = /u01/app/product/11.2.0.4/dbhome_1)
    )
  )

evansdd=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = evansdb)
    )
  )

七、创建spfile,重启实例

create spfile from pfile;

默认在$ORACLE_HOME/dbs

八、检查sga、pga等各项参数