pdb启停测试

 现有一套19c rac测试环境，测试一下pdb启动关闭。

1.删除lmd0.trc日志

rm删除的，发现新的trc无法产生了【注意生产环境清理trc文件,使用echo /dev/null >xxx.trc    当然有些trc 很多天都没有写,可以使用find 加时间删除即可】

2.由于上述原因需要重启pdb

ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;
ALTER PLUGGABLE DATABASE OPEN FORCE; 读写
ALTER PLUGGABLE DATABASE OPEN UPGRADE;可迁移模式
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;
如果是RAC环境,2个节点都有pdb,那么关闭只能关闭一个节点的pdb

使用instances=ALL即可关闭所有节点的PDB

alter pluggable database PP1 close instances=ALL;
alter pluggable database PP1 open instances=ALL;

3.测试immediate属性

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PP1                            READ WRITE NO

SQL> alter session set container=pp1;
SQL> create user tt identified by tt;
SQL> grant connect,resource to tt;
$ sqlplus tt/tt@192.168.60.44:1521/pp1

SQL> create table a(id int);

Table created.

SQL> insert into a values(1);
insert into a values(1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'   12c之后, resource 这个角色不在会隐士授予unlimited tablespace系统权限

SQL> grant unlimited tablespace to tt;

SQL> alter user tt default tablespace users;
alter user tt default tablespace users
*
ERROR at line 1:
ORA-00959: tablespace 'USERS' does not exist

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDO_2

。。。发现连DB默认有的USERS表空间都没了。。。

新建一个pdb的表空间吧。。。

SQL> create tablespace cc datafile '+DATA' size 1m autoextend on;

SQL> alter user tt default tablespace cc;

SQL> r
1* insert into a values(1)

1 row created.   --会话未重置，但是权限同步了？？？？ 这块11g没测试过，但是一般都是会话重登，才能识别新的权限。

实例二,存在未提交的dml操作

2020-04-17T18:36:42.702848+08:00
PP1(3):alter pluggable database pp1 close instances=all
2020-04-17T18:36:43.356154+08:00
PP1(3):JIT: pid 16048 requesting stop
2020-04-17T18:36:43.361064+08:00
Increasing priority of 1 RS
Domain Action Reconfiguration started (domid 3, new da inc 11, cluster inc 16)
Instance 1 is detaching from domain 3 (lazy abort? 0)
Global Resource Directory partially frozen for domain action
* domain detach - domain 3 valid ? 1
Non-local Process blocks cleaned out
Set master node info
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Domain Action Reconfiguration complete (total time 0.4 secs)
Decreasing priority of 1 RS

实例一，无session

2020-04-17T18:36:42.963425+08:00
PP1(3):JIT: pid 22713 requesting stop
PP1(3):Buffer Cache flush started: 3
PP1(3):Buffer Cache flush finished: 3
PP1(3):queued detach DA request 0x6f737358 for pdb 3
2020-04-17T18:36:43.336701+08:00
Increasing priority of 1 RS
Domain Action Reconfiguration started (domid 3, new da inc 11, cluster inc 16)
Instance 1 is detaching from domain 3 (lazy abort? 0)
Global Resource Directory partially frozen for domain action
* domain detach - domain 3 valid ? 1
Non-local Process blocks cleaned out
Set master node info
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
freeing the fusion rht of pdb 3
freeing the pdb enqueue rht
Domain Action Reconfiguration complete (total time 0.4 secs)
Decreasing priority of 1 RS
2020-04-17T18:36:43.766997+08:00
Pluggable database PP1 closed

实例pdb无法关闭！
之前的会话事物还可以继续操作

SQL> insert into a values(2);

1 row created.

新的session能否连接？？？

[oracle@d2:/home/oracle]$ sqlplus tt/tt@192.168.60.44:1521/pp1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 17 18:41:54 2020
Version 19.5.1.0.0

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

ERROR:
ORA-03135: connection lost contact
Process ID: 0
Session ID: 0 Serial number: 0

关闭pdb操作取消！

SQL> alter pluggable database pp1 close instances=all;

^Calter pluggable database pp1 close instances=all
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

使用immediate操作

SQL> alter pluggable database pp1 close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database pp1 open instances=all;

$ sqlplus tt/tt@192.168.60.44:1521/pp1

SQL> select * from a;

no rows selected