一台Linux系统服务器创建多个Oracle实例

1.查看当前实例SID及状态

[oracle@db ~]$ echo $ORACLE_SID
ora11g

SQL> select status,instance_name from v$instance;

STATUS        INSTANCE_NAME
-------------  ------------------------------------------------
OPEN            ora11g

[oracle@db ~]$ lsnrctl stat

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-5月 -2020 13:47:16

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-12月-2019 16:21:01
Uptime                    156 days 21 hr. 26 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ora11g" has 1 instance(s).
  Instance "ora11g", status READY, has 1 handler(s) for this service...
Service "ora11gXDB" has 1 instance(s).
  Instance "ora11g", status READY, has 1 handler(s) for this service...
The command completed successfully

2.指定数据库实例的变量与值

要建立的数据库实例的SID是orcl，声明SID：

[oracle@db ~]$ export ORACLE_SID=orcl
[oracle@db ~]$ echo $ORACLE_SID
orcl

3.用dbca工具创建实例

使用Xstart软件，远程连接到服务器的桌面。xstart上的SID还是以前的，重新export一下后，用dbca工具创建实例

step3 创建实例的名字要与刚刚声明的相同

step7创建一个新路径存放datafile

！注意，这里如果写{ORALCE_BASE}/ORADATA 那么会在$ORACLE_BASE目录下创建一个ORADATA目录，而不是oradata目录！严格区分大小写。并且是自动创建的。

傻傻的提前手动创建：

[oracle@db ~]$ cd $ORACLE_BASE
[oracle@db oracle]$ pwd
/u01/app/oracle
[oracle@db oracle]$ ls
admin/    cfgtoollogs/  checkpoints/  diag/  oradata/  product/
[oracle@db oracle]$ mkdir oracledata
[oracle@db oracle]$ ls
admin/    cfgtoollogs/  checkpoints/  diag/  oracledata/    oradata/  product/

实际建好实例以后：

[oracle@db ORACLEDATA]$ ll
total 4
drwxr-x--- 2 oracle oinstall 4096 May 25 13:58 orcl/
[oracle@db ORACLEDATA]$ pwd
/u01/app/oracle/ORACLEDATA

4.通过netca工具创建监听

$ netca

！注意，创建监听的时候，监听名字不能再是LISTENER了，会提示报错重名。

5.查看监听状态

[oracle@db oradata]$ lsnrctl stat

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-5月 -2020 14:30:22

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-12月-2019 16:21:01
Uptime                    156 days 22 hr. 9 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ora11g" has 1 instance(s).
  Instance "ora11g", status READY, has 1 handler(s) for this service...
Service "ora11gXDB" has 1 instance(s).
  Instance "ora11g", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

6.手工启动两个实例

[oracle@db oradata]$ echo $ORACLE_SID
orcl
[oracle@db oradata]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期一 5月 25 14:30:52 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME             STATUS
---------------------  ------------------------------------
orcl                      OPEN

SQL> show parameter name

NAME                     TYPE                   VALUE
------------------------------------ --------------------------------- ------------------------------
cell_offloadgroup_name             string
db_file_name_convert        	   string
db_name               			   string        orcl
db_unique_name             		   string        orcl
global_names             		   boolean       FALSE
instance_name              		   string        orcl
lock_name_space         		   string
log_file_name_convert        	   string
processor_group_name          	   string
service_names					   string        orcl

已经是open状态了，如果没有open，需要手动启动

• 连接orcl

SQL> export ORACLE_SID=orcl
SQL> sqlplus / as sysdba
SQL> startup
SQL> quit

• 连接ora11g

[oracle@db ~]$ export ORACLE_SID=ora11g
[oracle@db ~]$ echo $ORACLE_SID
ora11g
[oracle@db ~]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期一 5月 25 14:45:37 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME            STATUS
------------------------ ------------------------------------
ora11g                   OPEN

或者

$ sqlplus sys/123456@ora11g as sysdba;
SQL> select name from v$database;
SQL> exit

自动启动，可以在/etc/rc.local中设置，增加如下的内容

• 启动默认的数据库实例

su - oracle < lsnrctl start
sqlplus / as sysdba
startup
quit
EOF

• 启动第二个数据库实例

su - oracle < export ORACLE_SID=orcl
sqlplus / as sysdba
startup
quit
EOF