oracle 单机多实例启动登录-天翼云

一、演示环境介绍

oracle版本为oracle11g 企业版静默安装系统版本为centos7.8 x86_64最小化安装

涉及到2台服务器: 10.0.0.9机器主机名oracle 安装oracle服务，然后创建实例orcl和orcl001 10.0.0.8 机器主机名dbstandby08 安装oracle软件,不创建oracle实例 10.0.0.9和10.0.0.8机器 iptables都关闭 selinux都关闭

二、创建实例orcl和orcl001并启动挂载

2.1创建实例orcl并启动挂载

$ORACLE_HOME/bin/dbca -silent  -force -responseFile /u01/oracle/etc/dbca.rsp

sid为orcl:


[oracle@oracle etc]$ export ORACLE_SID=orcl
[oracle@oracle etc]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 20 15:00:53 2024

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

Connected to an idle instance.

SQL> SELECT instance_name FROM v$instance;
SELECT instance_name FROM v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


SQL> startup mount;
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size                  2213736 bytes
Variable Size             939526296 bytes
Database Buffers          637534208 bytes
Redo Buffers                7434240 bytes
Database mounted.
SQL> SELECT instance_name FROM v$instance;

INSTANCE_NAME
----------------
orcl

SQL> !ps -ef|grep orcl|grep -v grep
oracle    74430      1  0 15:01 ?        00:00:00 ora_pmon_orcl
oracle    74432      1  0 15:01 ?        00:00:00 ora_vktm_orcl
oracle    74437      1  0 15:01 ?        00:00:00 ora_gen0_orcl
oracle    74439      1  0 15:01 ?        00:00:00 ora_diag_orcl
oracle    74441      1  0 15:01 ?        00:00:00 ora_dbrm_orcl
oracle    74443      1  0 15:01 ?        00:00:00 ora_psp0_orcl
oracle    74445      1  0 15:01 ?        00:00:00 ora_dia0_orcl
oracle    74447      1  4 15:01 ?        00:00:05 ora_mman_orcl
oracle    74449      1  0 15:01 ?        00:00:00 ora_dbw0_orcl
oracle    74451      1  0 15:01 ?        00:00:00 ora_lgwr_orcl
oracle    74453      1  1 15:01 ?        00:00:01 ora_ckpt_orcl
oracle    74455      1  0 15:01 ?        00:00:00 ora_smon_orcl
oracle    74457      1  0 15:01 ?        00:00:00 ora_reco_orcl
oracle    74459      1  0 15:01 ?        00:00:00 ora_mmon_orcl
oracle    74461      1  0 15:01 ?        00:00:00 ora_mmnl_orcl
oracle    74463      1  0 15:01 ?        00:00:00 ora_d000_orcl
oracle    74465      1  0 15:01 ?        00:00:00 ora_s000_orcl
oracle    74513  74374  0 15:01 ?        00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

2.2 创建实例orcl001并启动挂载

$ORACLE_HOME/bin/dbca -silent  -force -responseFile /u01/oracle/etc/orcl001.rsp

sid为orcl001:

[oracle@oracle etc]$ sqlplus sys/oracle123 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 20 15:04:26 2024

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

Connected to an idle instance.

SQL> SELECT instance_name FROM v$instance;
SELECT instance_name FROM v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size                  2212776 bytes
Variable Size             369101912 bytes
Database Buffers          805306368 bytes
Redo Buffers                9232384 bytes
Database mounted.
SQL> 
SQL> SELECT instance_name FROM v$instance;

INSTANCE_NAME
----------------
orcl001

SQL> !ps -ef|grep 001|grep -v grep
oracle    74697      1  0 15:04 ?        00:00:00 ora_pmon_orcl001
oracle    74699      1  0 15:04 ?        00:00:00 ora_vktm_orcl001
oracle    74703      1  0 15:04 ?        00:00:00 ora_gen0_orcl001
oracle    74705      1  0 15:04 ?        00:00:00 ora_diag_orcl001
oracle    74707      1  0 15:04 ?        00:00:00 ora_dbrm_orcl001
oracle    74709      1  0 15:04 ?        00:00:00 ora_psp0_orcl001
oracle    74711      1  0 15:04 ?        00:00:00 ora_dia0_orcl001
oracle    74713      1  0 15:04 ?        00:00:00 ora_mman_orcl001
oracle    74715      1  0 15:04 ?        00:00:00 ora_dbw0_orcl001
oracle    74717      1  0 15:04 ?        00:00:00 ora_lgwr_orcl001
oracle    74719      1  0 15:04 ?        00:00:00 ora_ckpt_orcl001
oracle    74721      1  0 15:04 ?        00:00:00 ora_smon_orcl001
oracle    74723      1  0 15:04 ?        00:00:00 ora_reco_orcl001
oracle    74725      1  0 15:04 ?        00:00:00 ora_mmon_orcl001
oracle    74727      1  0 15:04 ?        00:00:00 ora_mmnl_orcl001
oracle    74729      1  0 15:04 ?        00:00:00 ora_d000_orcl001
oracle    74731      1  0 15:04 ?        00:00:00 ora_s000_orcl001
oracle    74741  74673  0 15:04 ?        00:00:00 oracleorcl001 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

SQL>

三、配置oracle服务监听器和客户端网络别名文件

3.1 10.0.0.9机器配置监听器文件

[oracle@oracle etc]$ cat  /u01/oracle/tools/oracle11g/product/11.2.0/dbhome_1/network/admin/listener.ora
#LISTENER =
#  (DESCRIPTION =
#    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.9)(PORT = 1521))
#  )


SID_LIST_LISTENER =
  (SID_LIST =
  
    (SID_DESC =
      (GLOBAL_DBNAME = orcl.oracle)
      (ORACLE_HOME = /u01/oracle/tools/oracle11g/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl001.oracle)  
      (ORACLE_HOME = /u01/oracle/tools/oracle11g/product/11.2.0/dbhome_1)  
      (SID_NAME = orcl001)  
    )
  )

ADR_BASE_LISTENER = /u01/oracle/tools/oracle11g

3.2 10.0.0.9机器配置网络别名文件

提示:10.0.0.9机器本地需要采用网络别名进行连接实例时才需要配置,不需要这种登录方式的话就不用配置

[oracle@oracle etc]$ cat  /u01/oracle/tools/oracle11g/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
#LISTENER_ORCL =
#  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.9)(PORT = 1521))
net_orcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.9)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.oracle)
    )
  )

net_orcl001 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.9)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl001.oracle)
    )
  )

3.2.1 不配置tnsnames.ora文件

已经创建oracle实例orcl,orcl001的10.0.0.9机器不配置tnsnames.ora文件采用下面的登录方式是登录不上对应的实例的

sqlplus wangwu/pass123321@net_orcl 
sqlplus zhangsan/pass123321@net_orcl001

oracle 单机多实例启动登录

3.2.2 配置tnsnames.ora文件

采用下面的方式是可以登录到各自对应的实例的

sqlplus wangwu/pass123321@net_orcl 
sqlplus zhangsan/pass123321@net_orcl001

oracle 单机多实例启动登录

四、远程登录10.0.0.9上的oracle实例orcl和orcl001

首先10.0.0.8机器上要安装sqlplus客户端,同时远程登录的话 os系统命令行是不需要export ORACLE_SID=SID的

4.1远程登录oracle实例orcl001创建创建测试数据

远程登录oracle实例orcl001创建表空间,测试表,测试用户zhangsan,授权测试用户可以写入数据

-- sqlplus sys/oracle123@10.0.0.9:1521/orcl001.oracle
   sqlplus 账户/密码@IP:1521/service_name服务名

[oracle@dbstandby08 ~]$  sqlplus sys/oracle123@10.0.0.9:1521/orcl001.oracle as sysdba

alter database open;
SELECT instance_name FROM v$instance;
 SHOW PARAMETER SERVICE_NAME;
create tablespace zhangsan_db01 datafile '/u01/oracle/tools/oracle11g/oradata/zhangsan_db01' size 10m autoextend on;
create user zhangsan identified by pass123321 default tablespace zhangsan_db01 quota 3m on users;
ALTER USER zhangsan QUOTA UNLIMITED ON zhangsan_DB01;
GRANT CREATE SESSION to zhangsan;
GRANT CREATE ANY TABLE to zhangsan;

采用创建的zhangsan用户登录实例orcl001创建测试表:

[oracle@dbstandby08 admin]$ sqlplus zhangsan/pass123321@10.0.0.9:1521/orcl001.oracle 
SQL> show user;
USER is "ZHANGSAN"

CREATE TABLE zhangsan01 (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    hire_date DATE,
    salary NUMBER
);
INSERT INTO zhangsan01(employee_id, first_name, last_name, hire_date, salary)VALUES (1, 'zhangsan01', 'Doe', '01-Jan-2021', 001);
commit;

create table zhangsan02(id number,name varchar2(255));
insert into zhangsan02 values(1,'zhangsan02');
commit;

管理员账户远程登录验证数据是否写入:

[oracle@dbstandby08 admin]$ sqlplus sys/oracle123@10.0.0.9:1521/orcl001.oracle as sysdba
SQL> SHOW PARAMETER SERVICE_NAME;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      orcl001.oracle
SQL> 

SQL> set line 300 pages 300
SQL> select * from zhangsan.zhangsan01;

EMPLOYEE_ID FIRST_NAME                                         LAST_NAME                                          HIRE_DATE              SALARY
----------- -------------------------------------------------- -------------------------------------------------- ------------------ ----------
          1 zhangsan01                                         Doe                                                01-JAN-21                   1

SQL> select * from zhangsan.zhangsan02;

        ID NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 zhangsan02

4.2远程登录oracle实例orcl创建创建测试数据

---创建表空间,测试表,测试用户wangwu,授权测试用户可以写入数据

[oracle@dbstandby08 ~]$ sqlplus sys/oracle@10.0.0.9:1521/orcl.oracle as sysdba

alter database open;
SELECT instance_name FROM v$instance;
SHOW PARAMETER SERVICE_NAME;
create tablespace wangwu_db01 datafile '/u01/oracle/tools/oracle11g/oradata/wangwu_db01' size 10m autoextend on;
create user wangwu identified by pass123321 default tablespace wangwu_db01 quota 3m on users;
ALTER USER wangwu QUOTA UNLIMITED ON WANGWU_DB01;
GRANT CREATE SESSION to wangwu;
GRANT CREATE ANY TABLE to wangwu;

登录用户wangwu,创建测试表和数据:

[oracle@dbstandby08 oradata]$ sqlplus wangwu/pass123321@10.0.0.9:1521/orcl.oracle 

CREATE TABLE wangwu01 (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    hire_date DATE,
    salary NUMBER
);
INSERT INTO wangwu01(employee_id, first_name, last_name, hire_date, salary)VALUES (1, 'wangwu01', 'Doe', '01-Jan-2021', 001);
commit;

create table wangwu02(id number,name varchar2(255));
insert into wangwu02 values(1,'wangwu02');
commit;

4.3、dbstandby08机器用网络别名远程登录10.0.0.9上oracle实例

dbstandby08机器采用网络名远程登录10.0.0.9上oracle实例时，dbstandby08机器不需要启动实例和不需要启动lsnrctl start 监听,只在dbstandby08机器配置客户端网络文件tnsnames.ora即可。

配置文件内容如下:


[oracle@dbstandby08 ~]$ cat /u01/oracle/tools/oracle11g/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

net_orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.9)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.oracle)
    )
  )

net_orcl001 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.9)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl001.oracle)
    )
  )

tnsping进行验证测试别名：

tnsping net_orcl  
tnsping net_orcl001

oracle 单机多实例启动登录

dbstandby08机器采用管理员账户sys,网络名net_orcl001远程登录10.0.0.9上oracle实例orcl001查看


[oracle@dbstandby08 ~]$ sqlplus sys/oracle123@net_orcl001 as sysdba 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 20 16:26:17 2024

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


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

SQL> 
SQL> SHOW PARAMETER SERVICE_NAME;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      orcl001.oracle
SQL> select * from zhangsan.zhangsan01;

EMPLOYEE_ID FIRST_NAME
----------- --------------------------------------------------
LAST_NAME                                          HIRE_DATE              SALARY
-------------------------------------------------- ------------------ ----------
          1 zhangsan01
Doe                                                01-JAN-21                   1


SQL> select * from zhangsan.zhangsan02;

        ID
----------
NAME
--------------------------------------------------------------------------------
         1
zhangsan02

dbstandby08机器采用zhangsan账户,网络名net_orcl001远程登录10.0.0.9上oracle实例orcl001查看: [oracle@dbstandby08 ~]$ sqlplus zhangsan/pass123321@net_orcl001

dbstandby08机器采用用户sys和wangwu,网络名net_orcl 远程登录10.0.0.9上oracle实例orcl查看：

[oracle@dbstandby08 ~]$ sqlplus wangwu/pass123321@net_orcl [oracle@dbstandby08 ~]$ sqlplus sys/oracle@net_orcl as sysdba oracle 单机多实例启动登录

五、10.0.0.9机器本地登录oracle实例和通过网络别名登录实例

5.1、10.0.0.9 本机登录orcl和orcl001实例查看

需要提前命令行定义下ORACLE_SID变量值，才能连接到对应的oracle实例 10.0.0.9机器oracle实例orcl本地登录：

[oracle@oracle etc]$ export ORACLE_SID=orcl;sqlplus system/oracle as sysdba
SQL> SELECT instance_name FROM v$instance;

INSTANCE_NAME
----------------
orcl

SQL> SHOW PARAMETER SERVICE_NAME;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      orcl.oracle
SQL> select * from wangwu.wangwu01;

EMPLOYEE_ID FIRST_NAME
----------- --------------------------------------------------
LAST_NAME                                          HIRE_DATE              SALARY
-------------------------------------------------- ------------------ ----------
          1 wangwu01
Doe                                                01-JAN-21                   1

SQL> select * from wangwu.wangwu02;

        ID
----------
NAME
--------------------------------------------------------------------------------
         1
wangwu02

10.0.0.9机器oracle实例orcl001本地登录：

[oracle@oracle etc]$ export ORACLE_SID=orcl001 ;sqlplus system/oracle123 as sysdba
SQL> SELECT instance_name FROM v$instance;

INSTANCE_NAME
----------------
orcl001

SQL> SELECT * from zhangsan.zhangsan01;

EMPLOYEE_ID FIRST_NAME
----------- --------------------------------------------------
LAST_NAME                                          HIRE_DATE              SALARY
-------------------------------------------------- ------------------ ----------
          1 zhangsan01
Doe                                                01-JAN-21                   1

SQL> SELECT * from zhangsan.zhangsan02;

        ID
----------
NAME
--------------------------------------------------------------------------------
         1
zhangsan02

5.2、10.0.0.9机器配置客户端网络别名文件登录

配置文件内容参考3.2，此处不再描述具体登录命令如下:

[oracle@oracle admin]$  sqlplus wangwu/pass123321@net_orcl 
[oracle@oracle admin]$  sqlplus zhangsan/pass123321@net_orcl001

活动

智算服务

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

oracle 单机多实例启动登录

oracle 单机多实例启动登录

一、演示环境介绍

二、创建实例orcl和orcl001并启动挂载

2.1创建实例orcl并启动挂载

2.2 创建实例orcl001并启动挂载

三、配置oracle服务监听器和客户端网络别名文件

3.1 10.0.0.9机器配置监听器文件

3.2 10.0.0.9机器 配置网络别名文件

3.2.1 不配置tnsnames.ora文件

3.2.2 配置tnsnames.ora文件

四、远程登录10.0.0.9上的oracle实例orcl和orcl001

4.1远程登录oracle实例orcl001创建创建测试数据

4.2远程登录oracle实例orcl创建创建测试数据

4.3、dbstandby08机器用网络别名远程登录10.0.0.9上oracle实例

五、10.0.0.9机器本地登录oracle实例和通过网络别名登录实例

5.1、10.0.0.9 本机登录orcl和orcl001实例查看

5.2、10.0.0.9机器配置客户端网络别名文件登录

相关文章

ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl151/system01.dbf'

token以及axios响应拦截器请求拦截器

30天拿下Rust之泛型

一步一步在linux上部署Oracle 11g R2 RAC 【1】

30天拿下Rust之结构体

python json反序列化为对象

Java静态变量在静态方法内部无法改变值

oracle使用dblink impdp数据时报错ORA-39169

搭建ogg双向复制时，oracle 19.16向oracle 10.2.0.4 ogg复制时报警OGG-01389、OGG-02246处理记录

配置ogg同步oracle 11g数据到oracle 19c

作者介绍

最新文章

ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl151/system01.dbf'

30天拿下Rust之结构体

oracle使用dblink impdp数据时报错ORA-39169

搭建ogg双向复制时，oracle 19.16向oracle 10.2.0.4 ogg复制时报警OGG-01389、OGG-02246处理记录

更改备库自动生成数据文件路径

oracle表字段设置为unused的相关知识

热门文章

oracle 数据库 中 如何将不带汉字的日期格式转换成带“年月日时分秒”格式的日期

oracle字符串处理函数（部分）

关于jpa的Specification自定义函数,实现oracle的decode；以及如何在静态方法中调用注入的service

Oracle 10g在solaris 10下的自动运行脚本

oracle 、sql server 、mysql 复制表数据

ORA-00942: 表或视图不存在

热门标签

相关产品

弹性云主机

天翼云电脑（公众版）

对象存储

云硬盘

随机文章

oracle学习23-区分大小写和字符集不同

oracle学习2-创建数据库

关于jpa的Specification自定义函数,实现oracle的decode；以及如何在静态方法中调用注入的service

【python基础（八）】类的定义、属性、函数种类；创建对象，使用对象

Oracle系列十六：数据库备份

oracle VM manager 3.1 试验备忘录

3.2 10.0.0.9机器配置网络别名文件

oracle 数据库中如何将不带汉字的日期格式转换成带“年月日时分秒”格式的日期