管理CDB

参考文档

https://docs.oracle.com/database/122/ADMIN/administering-a-cdb-with-sql-plus.htm#ADMIN13848

-- 查看都监听有那些service，后面可能会用到

LSNRCTL> statusConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11gr2.test.com)(PORT=1521)))STATUS of the LISTENER------------------------Alias                     LISTENERVersion                   TNSLSNR for Linux: Version 12.2.0.1.0 - ProductionStart Date                13-NOV-2017 14:03:08Uptime                    0 days 0 hr. 0 min. 51 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.oraListener Log File         /u01/app/oracle12/diag/tnslsnr/11gr2/listener/alert/log.xmlListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=11gr2.test.com)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Services Summary...Service "5d989e0872562852e0536402a8c0ec3d" has 1 instance(s).  Instance "orcl12c", status READY, has 1 handler(s) for this service...Service "5dc6e0676bdd2e73e0536402a8c033b0" has 1 instance(s).  Instance "orcl12c", status READY, has 1 handler(s) for this service...Service "5dc86389cf0d372ae0536402a8c0a2e5" has 1 instance(s).  Instance "orcl12c", status READY, has 1 handler(s) for this service...Service "5dc9b52de0d03c0fe0536402a8c0dc22" has 1 instance(s).  Instance "orcl12c", status READY, has 1 handler(s) for this service...Service "app_con1" has 1 instance(s).  Instance "orcl12c", status READY, has 1 handler(s) for this service...Service "app_con1$seed" has 1 instance(s).  Instance "orcl12c", status READY, has 1 handler(s) for this service...Service "app_con_pdb" has 1 instance(s).  Instance "orcl12c", status READY, has 1 handler(s) for this service...Service "orcl12c" has 1 instance(s).  Instance "orcl12c", status READY, has 1 handler(s) for this service...Service "pdb_plugged" has 1 instance(s).  Instance "orcl12c", status READY, has 1 handler(s) for this service...The command completed successfully

-- 查询当前容器的ID  ，Name

SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;SELECT SYS_CONTEXT ('USERENV', 'CON_ID') FROM DUAL;SYS@orcl12c>SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;SYS_CONTEXT('USERENV','CON_NAME')--------------------------------------------------------------------------------CDB$ROOTSYS@orcl12c>SELECT SYS_CONTEXT ('USERENV', 'CON_ID') FROM DUAL;SYS_CONTEXT('USERENV','CON_ID')--------------------------------------------------------------------------------1

-- 通过ezconnect 连接到pdbs

@>conn sys/111111@192.168.2.100:1521/orcl12c as sysdbaConnected.@>conn sys/111111@192.168.2.100:1521/APP_CON1 as sysdbaConnected.SYS@192.168.2.100:1521/APP_CON1>SYS@orcl12c>conn sys/111111@192.168.2.100:1521/app_con1$seed as sysdbaConnected.SYS@192.168.2.100:1521/app_con1$seed>conn sys/111111@192.168.2.100:1521/app_con_pdb as sysdbaConnected.SYS@192.168.2.100:1521/app_con_pdb>conn sys/111111@192.168.2.100:1521/APP_CON1$SEED as sysdbaConnected.SYS@192.168.2.100:1521/APP_CON1$SEED>conn sys/111111@192.168.2.100:1521/APP_CON_PDB as sysdbaConnected.SYS@192.168.2.100:1521/APP_CON_PDB>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 6 APP_CON_PDB  MOUNTEDSYS@192.168.2.100:1521/APP_CON_PDB>

-- 切换容器，并指定对应的service（指定不对会出错）

-- 查询service name，及容器的名称

SELECT NAME,CON_NAME, CON_ID  FROM V$ACTIVE_SERVICES  WHERE UPPER(NAME) != CON_NAME  AND CON_ID !=1  ORDER BY CON_ID;SYS@orcl12c>col name for a30SYS@orcl12c>col open_mode for a30SYS@orcl12c>select name,open_mode from v$containers;NAME       OPEN_MODE------------------------------ ------------------------------CDB$ROOT       READ WRITEPDB$SEED       READ ONLYPDB_PLUGGED       MOUNTEDAPP_CON1       READ WRITEAPP_CON1$SEED       MOUNTEDAPP_CON_PDB       MOUNTED

SYS@orcl12c>col name for a30SYS@orcl12c>col con_name for a30SYS@orcl12c>select name,con_name from v$active_services;NAME       CON_NAME------------------------------ ------------------------------app_con1$seed       APP_CON1$SEEDapp_con_pdb       APP_CON_PDBapp_con1       APP_CON1SYS@orcl12c>alter session set container=APP_CON1 service=pdb_plugged;ERROR:ORA-44787: Service cannot be switched into.SYS@orcl12c>alter session set container=APP_CON1 service=app_con1;Session altered.SYS@orcl12c>

-- 更改pdb的状态

SYS@orcl12c>alter pluggable database app_con_pdb open upgrade  2  ;Pluggable database altered.SYS@orcl12c>show pdbs;    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  READ ONLY  NO 4 APP_CON1  READ WRITE NO 5 APP_CON1$SEED  MOUNTED 6 APP_CON_PDB  MIGRATE    YESSYS@orcl12c>

-- 关闭多个pdb

SYS@orcl12c>alter pluggable database PDB_PLUGGED,APP_CON1,APP_CON_PDB close;Pluggable database altered.SYS@orcl12c>show pdbs;    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  MOUNTED 4 APP_CON1  MOUNTED 5 APP_CON1$SEED  MOUNTED 6 APP_CON_PDB  MOUNTED--- open，有一个pdb没有open ，原因是APP_CON1 这个pdb是root application pdb，需要read writeSYS@orcl12c>alter pluggable database PDB_PLUGGED,APP_CON1,APP_CON_PDB open;alter pluggable database PDB_PLUGGED,APP_CON1,APP_CON_PDB open*ERROR at line 1:ORA-65054: Cannot open a pluggable database in the desired mode.SYS@orcl12c>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  READ ONLY  YES 4 APP_CON1  READ ONLY  YES 5 APP_CON1$SEED  MOUNTED 6 APP_CON_PDB  MOUNTEDSYS@orcl12c>![oracle@11gr2 db_1]$ oerr ora 6505465054, 00000, "Cannot open a pluggable database in the desired mode."// *Cause:  An attempt was made to open a pluggable database (PDB) in a mode //          incompatible with the multitenant container database (CDB) or, //          if the PDB belonged to an application container, in a mode//          incompatible with the application root.// *Action: Open the CDB or the application root in a compatible mode //          first and retry the operation.//[oracle@11gr2 db_1]$ exit-- 修改root application container 状态为read write，再open pdbSYS@orcl12c>alter pluggable database APP_CON1 close  2  ;Pluggable database altered.SYS@orcl12c>alter pluggable database APP_CON1 open read write;Pluggable database altered.SYS@orcl12c>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  READ ONLY  YES 4 APP_CON1  READ WRITE NO 5 APP_CON1$SEED  MOUNTED 6 APP_CON_PDB  MOUNTEDSYS@orcl12c>alter pluggable database APP_CON_PDB open;Warning: PDB altered with errors.

-- 一次性关闭或打开多个pdb或所有的pdb

SYS@orcl12c>alter pluggable database all close;Pluggable database altered.SYS@orcl12c>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  MOUNTED 4 APP_CON1  MOUNTED 5 APP_CON1$SEED  MOUNTED 6 APP_CON_PDB  MOUNTEDSYS@orcl12c>alter pluggable database all open;Warning: PDB altered with errors.SYS@orcl12c>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  READ WRITE NO 4 APP_CON1  READ WRITE NO 5 APP_CON1$SEED  READ WRITE NO 6 APP_CON_PDB  READ WRITE YESSYS@orcl12c>

---- 关闭所有的pdb ，除过某一个或者某几个pdb （奇怪，这里出错了，补充，应该要这样查询？

-- SYS@orcl12c>select pdb_name,status from dba_pdbs; ）

SYS@orcl12c>alter pluggable database all except APP_CON_PDB close;Pluggable database altered.SYS@orcl12c>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  MOUNTED 4 APP_CON1  MOUNTED 5 APP_CON1$SEED  MOUNTED 6 APP_CON_PDB  MOUNTEDSYS@orcl12c>alter pluggable database all except APP_CON_PDB open;Pluggable database altered.SYS@orcl12c>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  READ WRITE NO 4 APP_CON1  READ WRITE NO 5 APP_CON1$SEED  READ WRITE NO 6 APP_CON_PDB  MOUNTED

--使用startup 命令启动pdbs ，

SYS@orcl12c>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  MOUNTED 4 APP_CON1  MOUNTED 5 APP_CON1$SEED  MOUNTED 6 APP_CON_PDB  MOUNTEDSYS@orcl12c>SYS@orcl12c>startup pluggable database APP_CON1 open;Pluggable Database opened.SYS@orcl12c>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  MOUNTED 4 APP_CON1  READ WRITE NO 5 APP_CON1$SEED  MOUNTED 6 APP_CON_PDB  MOUNTEDSYS@orcl12c>

--保留pdb的状态，使之不受cdb的启动或关闭的影响。比如保留APP_CON1的状态，启动后状态不受影响。

ALTER PLUGGABLE DATABASE APP_CON1 SAVE STATE;ALTER PLUGGABLE DATABASE APP_CON1 DISCARD STATE;SYS@orcl12c>ALTER PLUGGABLE DATABASE APP_CON1 SAVE STATE;Pluggable database altered.SYS@orcl12c>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  MOUNTED 4 APP_CON1  READ WRITE NO 5 APP_CON1$SEED  MOUNTED 6 APP_CON_PDB  MOUNTEDSYS@orcl12c>startup forceORACLE instance started.Total System Global Area  524288000 bytesFixed Size    8794696 bytesVariable Size  486542776 bytesDatabase Buffers   20971520 bytesRedo Buffers    7979008 bytesDatabase mounted.Database opened.oSYS@orcl12c>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  MOUNTED 4 APP_CON1  READ WRITE NO 5 APP_CON1$SEED  MOUNTED 6 APP_CON_PDB  MOUNTEDSYS@orcl12c>SYS@orcl12c>ALTER PLUGGABLE DATABASE APP_CON1 DISCARD STATE;Pluggable database altered.SYS@orcl12c>startup forceORACLE instance started.Total System Global Area  524288000 bytesFixed Size    8794696 bytesVariable Size  473959864 bytesDatabase Buffers   33554432 bytesRedo Buffers    7979008 bytesDatabase mounted.Database opened.SYS@orcl12c>show pdbs    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB_PLUGGED  MOUNTED 4 APP_CON1  MOUNTED 5 APP_CON1$SEED  MOUNTED 6 APP_CON_PDB  MOUNTEDSYS@orcl12c>

-- 也可以使用all 或者 except 参数

ALTER PLUGGABLE DATABASE ALL SAVE STATE;ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb SAVE STATE;

--在CDB中使用alter system 命令

SYS@orcl12c>alter pluggable database app_con1 open;Pluggable database altered.SYS@orcl12c>show parameter open_curNAME     TYPE VALUE------------------------------------ ----------- ------------------------------open_cursors     integer 301SYS@orcl12c>alter system set open_cursors=300 container=current;System altered.SYS@orcl12c>show parameter open_curNAME     TYPE VALUE------------------------------------ ----------- ------------------------------open_cursors     integer 300SYS@orcl12c>-- 重启后，修改的参数还原了？ 可能原因是修改了PDB，PDB 又继承自CDB的原因？  这里待确认 ？

-- 设置缺省的容器

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='CONTAINERS_DEFAULT_TARGET';

-- 今天先到这里，end。