check the open modes of all the PDBs on a RAC instance when you connect to the CDB root

[oracle@zhongwc1 ~]$ cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 6.4 (Santiago)
[oracle@zhongwc1 ~]$ sqlplus  / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 14 20:12:25 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
ZHONGWC1                       READ WRITE
ZHONGWC2                       READ WRITE

SQL> 

The default database service with the same name as the PDB is created automatically during PDB creation. You should only use this default service to connect to the PDB for administrative tasks. For applications to access the PDB, you need to create user-defined services for the PDB. In Oracle 12c, the database service has an optional PDB property which allows you to create a database service that is associated with a PDB.

[oracle@zhongwc1 ~]$ srvctl add service -db zwc -service crm -pdb zhongwc1 -preferred zwc1
[oracle@zhongwc1 ~]$ srvctl add service -db zwc -service erp -pdb zhongwc1 -preferred zwc2
SQL> select name,pdb from dba_services;

NAME                 PDB
-------------------- --------------------
SYS$BACKGROUND       CDB$ROOT
SYS$USERS            CDB$ROOT
zwcXDB               CDB$ROOT
zwc                  CDB$ROOT

SQL> conn sys/oracle10gOCP@zhongwc-cluster-scan:1521/zhongwc1 as sysdba
Connected.
SQL> select name,pdb from dba_services;

NAME                 PDB
-------------------- --------------------
zhongwc1             ZHONGWC1

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ZHONGWC1                       READ WRITE NO

Start service

[oracle@zhongwc1 ~]$ srvctl config service -db zwc
Service name: crm
Service is enabled
Server pool: zwc_crm
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type: 
Failover method: 
TAF failover retries: 
TAF failover delay: 
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition: 
Pluggable database name: zhongwc1
Maximum lag time: ANY
SQL Translation Profile: 
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency: 
Preferred instances: zwc1
Available instances: 
Service name: erp
Service is enabled
Server pool: zwc_erp
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type: 
Failover method: 
TAF failover retries: 
TAF failover delay: 
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition: 
Pluggable database name: zhongwc1
Maximum lag time: ANY
SQL Translation Profile: 
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency: 
Preferred instances: zwc2
Available instances: 
[oracle@zhongwc1 ~]$ srvctl status service -db zwc -service erp
Service erp is not running.
[oracle@zhongwc1 ~]$ srvctl status service -db zwc -service crm
Service crm is not running.
[oracle@zhongwc1 ~]$ srvctl start service -db zwc -service erp
[oracle@zhongwc1 ~]$ srvctl start service -db zwc -service crm
[oracle@zhongwc1 ~]$ 
[oracle@zhongwc1 ~]$ 
[oracle@zhongwc1 ~]$ srvctl status service -db zwc -service crm
Service crm is running on instance(s) zwc1
[oracle@zhongwc1 ~]$ srvctl status service -db zwc -service erp
Service erp is running on instance(s) zwc2

Check the status of the zhongwc1 PDB

[oracle@zhongwc1 ~]$ sqlplus  / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 14 20:31:14 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> col pdb for a20
SQL> col name for a20
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select name,pdb from dba_services;

NAME                 PDB
-------------------- --------------------
SYS$BACKGROUND       CDB$ROOT
SYS$USERS            CDB$ROOT
zwcXDB               CDB$ROOT
zwc                  CDB$ROOT

SQL> 
SQL> 
SQL> 
SQL> conn sys/oracle10gOCP@zhongwc-cluster-scan:1521/zhongwc1 as sysdba
Connected.
SQL> select name,pdb from dba_services;

NAME                 PDB
-------------------- --------------------
zhongwc1             ZHONGWC1
erp                  ZHONGWC1
crm                  ZHONGWC1

SQL> 
[root@zhongwc2 ~]# crsctl stat res ora.zwc.erp.svc
NAME=ora.zwc.erp.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=ONLINE on zhongwc2

[root@zhongwc2 ~]# 
[root@zhongwc2 ~]# 
[root@zhongwc2 ~]# 
[root@zhongwc2 ~]# crsctl stat res ora.zwc.crm.svc
NAME=ora.zwc.crm.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=ONLINE on zhongwc1
[oracle@zhongwc1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 14-OCT-2013 20:33:47

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                14-OCT-2013 19:38:33
Uptime                    0 days 0 hr. 55 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/zhongwc1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.31)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.131)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 2 handler(s) for this service...
Service "crm" has 1 instance(s).
  Instance "zwc1", status READY, has 1 handler(s) for this service...
Service "zhongwc1" has 1 instance(s).
  Instance "zwc1", status READY, has 1 handler(s) for this service...
Service "zhongwc2" has 1 instance(s).
  Instance "zwc1", status READY, has 1 handler(s) for this service...
Service "zwc" has 1 instance(s).
  Instance "zwc1", status READY, has 1 handler(s) for this service...
Service "zwcXDB" has 1 instance(s).
  Instance "zwc1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@zhongwc1 ~]$ 
[oracle@zhongwc1 ~]$ 
[oracle@zhongwc1 ~]$ lsnrctl service

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 14-OCT-2013 20:34:12

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: zhongwc1, pid: 3251>
         (ADDRESS=(PROTOCOL=tcp)(HOST=zhongwc1)(PORT=23127))
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "crm" has 1 instance(s).
  Instance "zwc1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "zhongwc1" has 1 instance(s).
  Instance "zwc1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "zhongwc2" has 1 instance(s).
  Instance "zwc1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "zwc" has 1 instance(s).
  Instance "zwc1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "zwcXDB" has 1 instance(s).
  Instance "zwc1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: zhongwc1, pid: 3840>
         (ADDRESS=(PROTOCOL=tcp)(HOST=zhongwc1)(PORT=57078))
The command completed successfully
[oracle@zhongwc1 ~]$ 
[root@zhongwc1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADG.dg
               ONLINE  ONLINE       zhongwc1                 STABLE
               ONLINE  ONLINE       zhongwc2                 STABLE
ora.FRADG.dg
               ONLINE  ONLINE       zhongwc1                 STABLE
               ONLINE  ONLINE       zhongwc2                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       zhongwc1                 STABLE
               ONLINE  ONLINE       zhongwc2                 STABLE
ora.SYSDG.dg
               ONLINE  ONLINE       zhongwc1                 STABLE
               ONLINE  ONLINE       zhongwc2                 STABLE
ora.asm
               ONLINE  ONLINE       zhongwc1                 Started,STABLE
               ONLINE  ONLINE       zhongwc2                 Started,STABLE
ora.net1.network
               ONLINE  ONLINE       zhongwc1                 STABLE
               ONLINE  ONLINE       zhongwc2                 STABLE
ora.ons
               ONLINE  ONLINE       zhongwc1                 STABLE
               ONLINE  ONLINE       zhongwc2                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       zhongwc1                 STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       zhongwc1                 169.254.198.87 172.1
                                                             68.1.31,STABLE
ora.cvu
      1        ONLINE  ONLINE       zhongwc1                 STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       zhongwc1                 Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       zhongwc1                 STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       zhongwc1                 STABLE
ora.zhongwc1.vip
      1        ONLINE  ONLINE       zhongwc1                 STABLE
ora.zhongwc2.vip
      1        ONLINE  ONLINE       zhongwc2                 STABLE
ora.zwc.crm.svc
      1        ONLINE  ONLINE       zhongwc1                 STABLE
ora.zwc.db
      1        ONLINE  ONLINE       zhongwc1                 Open,STABLE
      2        ONLINE  ONLINE       zhongwc2                 Open,STABLE
ora.zwc.erp.svc
      1        ONLINE  ONLINE       zhongwc2                 STABLE
--------------------------------------------------------------------------------

Creating Net Service Names for PDBs

[oracle@zhongwc1 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ZWC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zhongwc-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zwc)
    )
  )



crm_zhongwc1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zhongwc-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = crm)
    )
  )


erp_zhongwc1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zhongwc-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = erp)
    )
  )
[oracle@zhongwc1 ~]$ sqlplus sys@crm_zhongwc1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 14 20:39:29 2013

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

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ZHONGWC1                       READ WRITE NO
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@zhongwc1 ~]$ sqlplus sys@erp_zhongwc1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 14 20:39:49 2013

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

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ZHONGWC1                       READ WRITE NO
SQL> conn sys@erp_zhongwc1 as sysdba
Enter password: 
Connected.
SQL> select name,pdb from dba_services;

NAME                 PDB
-------------------- --------------------
zhongwc1             ZHONGWC1
erp                  ZHONGWC1
crm                  ZHONGWC1

SQL> conn sys@crm_zhongwc1 as sysdba
Enter password: 
Connected.
SQL> select name,pdb from dba_services;

NAME                 PDB
-------------------- --------------------
zhongwc1             ZHONGWC1
erp                  ZHONGWC1
crm                  ZHONGWC1

Logo

更多推荐