当前位置 博文首页 > LuciferLiu_DBA:Oracle 19C RAC连接PDB的几种方式

    LuciferLiu_DBA:Oracle 19C RAC连接PDB的几种方式

    作者:[db:作者] 时间:2021-06-16 18:15

    一、SESSION切换

    SYS@orcl1> show pdbs
    
        CON_ID CON_NAME			  OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
    	 2 PDB$SEED			  READ ONLY  NO
    	 3 PDB01			  READ WRITE NO
    SYS@orcl1> alter session set container=pdb01;
    
    Session altered.
    
    SYS@orcl1> show con_name
    
    CON_NAME
    ------------------------------
    PDB01
    SYS@orcl1> show user
    USER is "SYS"
    

    二、TNS本地监听连接(TNS配置单节点IP)

    [oracle@p19c01 ~]$ cd $ORACLE_HOME/network/admin
    [oracle@p19c01 admin]$ ls
    samples  shrept.lst  tnsnames.ora
    [oracle@p19c01 admin]$ vim tnsnames.ora 
    [oracle@p19c01 admin]$ cat tnsnames.ora 
    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/db/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.
    
    ORCL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = p19c-scan)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
        )
      )
    
    PDB01 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = p19c01)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = pdb01)
        )
      )
    
    [oracle@p19c01 admin]$ sqlplus admin/oracle@pdb01
    
    SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 20 06:06:05 2021
    Version 19.3.0.0.0
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0
    
    ADMIN@pdb01> show user
    USER is "ADMIN"
    ADMIN@pdb01> show con_name
    
    CON_NAME
    ------------------------------
    PDB01
    

    三、RAC添加SERVICE通过SCAN访问PDB

    Usage: srvctl add service -db <db_unique_name> -service "<service_name_list>" -update {-preferred "<new_pref_inst>" | -available "<new_avail_inst>"} [-force] [-verbose]
        -db <db_unique_name>           Unique name for the database
        -service "<serv,...>"          Comma separated service names
        -update                        Add a new instance to service configuration
        -preferred <new_pref_inst>     Name of new preferred instance
        -available <new_avail_inst>    Name of new available instance
        -force                         Force the add operation even though a listener is not configured for a network
        -verbose                       Verbose output
        -help                          Print usage
    --集群添加service
    [oracle@p19c01 ~]$ srvctl add service -d orcl -s spdb01 -pdb pdb01 -preferred orcl1 -available orcl2
    [oracle@p19c01 ~]$ srvctl config service -d orcl -s spdb01
    Service name: spdb01
    Server pool: 
    Cardinality: 1
    Service role: PRIMARY
    Management policy: AUTOMATIC
    DTP transaction: false
    AQ HA notifications: false
    Global: false
    Commit Outcome: false
    Failover type: 
    Failover method: 
    Failover retries: 
    Failover delay: 
    Failover restore: NONE
    Connection Load Balancing Goal: LONG
    Runtime Load Balancing Goal: NONE
    TAF policy specification: NONE
    Edition: 
    Pluggable database name: pdb01
    Hub service: 
    Maximum lag time: ANY
    SQL Translation Profile: 
    Retention: 86400 seconds
    Replay Initiation Time: 300 seconds
    Drain timeout: 
    Stop option: 
    Session State Consistency: DYNAMIC
    GSM Flags: 0
    Service is enabled
    Preferred instances: orcl1
    Available instances: orcl2
    CSS critical: no
    
    --TNS添加SPDB01
    [oracle@p19c01 ~]$ cd $ORACLE_HOME/network/admin
    [oracle@p19c01 admin]$ cat tnsnames.ora 
    SPDB01 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = p19c-scan)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = spdb01)
        )
      )
    --启动service
    [oracle@p19c01 admin]$ srvctl start service -d orcl -s spdb01
    [oracle@p19c01 admin]$ lsnrctl stat
    Service "spdb01" has 1 instance(s).
      Instance "orcl1", status READY, has 1 handler(s) for this service...
    The command completed successfully
    --连接pdb01
    [oracle@p19c01 admin]$ tnsping spdb01
    
    TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 20-MAR-2021 06:41:43
    
    Copyright (c) 1997, 2019, Oracle.  All rights reserved.
    
    Used parameter files:
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = p19c-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = spdb01)))
    OK (0 msec)
    [oracle@p19c01 admin]$ sqlplus admin/oracle@spdb01
    
    SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 20 06:41:53 2021
    Version 19.3.0.0.0
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Last Successful login time: Sat Mar 20 2021 06:06:05 +08:00
    
    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0
    
    ADMIN@spdb01> show user
    USER is "ADMIN"
    ADMIN@spdb01> show con_name
    
    CON_NAME
    ------------------------------
    PDB01
    

    ?