flashback pluggable database在pdb上的测试

最新推荐文章于 2024-02-28 16:19:10 发布

wgz7747147820

最新推荐文章于 2024-02-28 16:19:10 发布

阅读量311

点赞数

分类专栏： orale 文章标签： oracle

本文链接：https://blog.csdn.net/wgz7747147820/article/details/110296254

版权

orale 专栏收录该内容

204 篇文章 0 订阅

订阅专栏

一个用户如果想要open或者close pdb，则需要sysdba sysoper sysbackup或者sysdg权限里的一个

在flashback pluggable database之前必须要将pdb关掉在所有的instance上

在cdb上，是用flashback database
在pdb上，是用flashback pluggable database

07:10:41 SQL> select username,sysdba,sysoper,sysasm,sysbackup,sysdg,syskm from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM
------ ----- ----- ----- ----- ----- -----
SYS	     TRUE  TRUE  FALSE FALSE FALSE FALSE
PDBADMIN FALSE FALSE FALSE TRUE  FALSE FALSE

Elapsed: 00:00:00.07
07:10:52 SQL> create table t_2311 as select * from dba_objects;

Table created.

Elapsed: 00:00:02.07
07:11:25 SQL> select count(*) from t_2311;

  COUNT(*)
----------
     66826

Elapsed: 00:00:00.05
07:11:38 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2020-11-28 07:11:56

Elapsed: 00:00:00.01
07:11:56 SQL> drop table t_2311;

Table dropped.

Elapsed: 00:00:06.02

07:12:50 SQL> connect pdbadmin/comp1@comp1pdb21047 as sysbackup
Connected.

07:13:03 SQL> alter pluggable database close immediate instances=all;

Pluggable database altered.

Elapsed: 00:00:05.70

07:16:27 SQL> flashback database to timestamp to_date('2020-11-28 07:11:56','yyyy-mm-dd hh24:mi:ss');
flashback database to timestamp to_date('2020-11-28 07:11:56','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


Elapsed: 00:00:00.04
07:17:49 SQL> flashback pluggable database to timestamp to_date('2020-11-28 07:11:56','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

Elapsed: 00:00:04.05
07:18:15 SQL> alter pluggable database open resetlogs;

Pluggable database altered.

Elapsed: 00:00:10.78

07:18:42 SQL> alter pluggable database open read write  instances=all;

Pluggable database altered.

Elapsed: 00:00:04.16

07:19:51 SQL> select count(*) from t_2311;
select count(*) from t_2311
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.01
07:20:11 SQL> show user;
USER is "SYSBACKUP"
不能查询到t_2311这个表的原因是因为当前的schema不对

不以as sysbackup连接之后，就可以查询得到了
07:20:48 SQL> connect pdbadmin/comp1@comp1pdb21047
Connected.
07:21:13 SQL> select count(*) from t_2311;

  COUNT(*)
----------
     66826

Elapsed: 00:00:00.05