管理CDB

最新推荐文章于 2024-04-11 18:21:16 发布

文档搬运工

最新推荐文章于 2024-04-11 18:21:16 发布

阅读量1.4k

点赞数

分类专栏： CDB/PDB

本文链接：https://blog.csdn.net/xxzhaobb/article/details/78524524

版权

CDB/PDB 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

参考文档

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

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

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11gr2.test.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                13-NOV-2017 14:03:08
Uptime                    0 days 0 hr. 0 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle12/diag/tnslsnr/11gr2/listener/alert/log.xml
Listening 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$ROOT

SYS@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 sysdba
Connected.

@>conn sys/111111@192.168.2.100:1521/APP_CON1 as sysdba
Connected.
SYS@192.168.2.100:1521/APP_CON1>
SYS@orcl12c>conn sys/111111@192.168.2.100:1521/app_con1$seed as sysdba
Connected.
SYS@192.168.2.100:1521/app_con1$seed>conn sys/111111@192.168.2.100:1521/app_con_pdb as sysdba
Connected.
SYS@192.168.2.100:1521/app_con_pdb>conn sys/111111@192.168.2.100:1521/APP_CON1$SEED as sysdba
Connected.
SYS@192.168.2.100:1521/APP_CON1$SEED>conn sys/111111@192.168.2.100:1521/APP_CON_PDB as sysdba
Connected.
SYS@192.168.2.100:1521/APP_CON_PDB>show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 6 APP_CON_PDB			  MOUNTED
SYS@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 a30
SYS@orcl12c>col open_mode for a30
SYS@orcl12c>select name,open_mode from v$containers;

NAME			       OPEN_MODE
------------------------------ ------------------------------
CDB$ROOT		       READ WRITE
PDB$SEED		       READ ONLY
PDB_PLUGGED		       MOUNTED
APP_CON1		       READ WRITE
APP_CON1$SEED		       MOUNTED
APP_CON_PDB		       MOUNTED

SYS@orcl12c>col name for a30
SYS@orcl12c>col con_name for a30
SYS@orcl12c>select name,con_name from v$active_services;

NAME			       CON_NAME
------------------------------ ------------------------------
app_con1$seed		       APP_CON1$SEED
app_con_pdb		       APP_CON_PDB
app_con1		       APP_CON1



SYS@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    YES
SYS@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 write
SYS@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			  MOUNTED

SYS@orcl12c>!
[oracle@11gr2 db_1]$ oerr ora 65054
65054, 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 pdb

SYS@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			  MOUNTED
SYS@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			  MOUNTED
SYS@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 YES
SYS@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			  MOUNTED
SYS@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			  MOUNTED
SYS@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			  MOUNTED
SYS@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			  MOUNTED
SYS@orcl12c>startup force
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size		    8794696 bytes
Variable Size		  486542776 bytes
Database Buffers	   20971520 bytes
Redo Buffers		    7979008 bytes
Database 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			  MOUNTED
SYS@orcl12c>

SYS@orcl12c>ALTER PLUGGABLE DATABASE APP_CON1 DISCARD STATE;

Pluggable database altered.

SYS@orcl12c>startup force
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size		    8794696 bytes
Variable Size		  473959864 bytes
Database Buffers	   33554432 bytes
Redo Buffers		    7979008 bytes
Database 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			  MOUNTED
SYS@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_cur

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
open_cursors			     integer	 301
SYS@orcl12c>alter system set open_cursors=300 container=current;

System altered.

SYS@orcl12c>show parameter open_cur

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
open_cursors			     integer	 300
SYS@orcl12c>

-- 重启后，修改的参数还原了？ 可能原因是修改了PDB，PDB 又继承自CDB的原因？  这里待确认 ？

-- 设置缺省的容器

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='CONTAINERS_DEFAULT_TARGET';

-- 今天先到这里，end。

文档搬运工

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录