Flashback of PDB ORA-39867 resetlogs 打开前一个GRP点后不能再前滚了解决办法

CHANGES
1. Created Restore Point P1 and then created another Restore Point P2.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
SQL> alter session set container=pdb1;

Session altered.

SQL> create restore point P1 guarantee flashback database;

Restore point created.

SQL>
SQL> create restore point p2 guarantee flashback database;

Restore point created.

SQL>

2. Flashback PDB to P1 with Resetlogs.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

SQL> FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT P1;

Flashback complete.

SQL> alter pluggable database pdb1 open resetlogs;

Pluggable database altered.

3. Flashback PDB to P2

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL>
SQL> FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT P2;

FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT P2
*
ERROR at line 1:
ORA-39867: Clean PDB restore point 'P2' is on an orphan incarnation of the
pluggable database.

CAUSE
+ This is expected Behaviour.

+ Flashback of a PDB to direct ancestor incarnations is only allowed. Flashback PDB to a sibling incarnation will report error.

Note :- For more details on incarnations - Refer https://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr006.htm

SOLUTION
1. Restore and Recover the entire database to Point in Time (P2). You may also Restore and Recover database to another server and then migrate/Clone the PDB to existing database.

2. Instead of open PDB in read/write (with resetlogs) open pdb in read only mode.

Note -

Flashback PDB from P2 to P1 to P0 should work fine.

Restore and Recovery of PDB1 to P2/SCN/Timestamp will also fail due to incarnation.

SQL> alter pluggable database pdb close;
SQL> flashback pluggable database pdb to restore point PDB_012118;
SQL> alter pluggable database open read only;

SQL> alter pluggable database pdb close;
SQL> flashback pluggable database pdb to restore point PDB_012123;
SQL> alter pluggable database open read only;

SQL> alter pluggable database pdb close;
SQL> flashback pluggable database pdb to restore point PDB_012123;
SQL> flashback pluggable database pdb to restore point PDB_012118;
SQL> alter pluggable database pdb open resetlogs;

SQL> alter pluggable database pdb close;
SQL> flashback pluggable database pdb to restore point PDB_012123;
flashback pluggable database pdb to restore point PDB_012123
*
ERROR at line 1:
ORA-39867: Clean PDB restore point 'PDB_012123' is on an orphan incarnation of
the pluggable database.

-----再次闪回，依旧不能前滚

SQL> flashback pluggable database pdb to restore point PDB_012118;

Flashback complete.

SQL> flashback pluggable database pdb to restore point PDB_012123;
flashback pluggable database pdb to restore point PDB_012123
*
ERROR at line 1:
ORA-39867: Clean PDB restore point 'PDB_012123' is on an orphan incarnation of
the pluggable database.

SQL>

--------------------------------CDB级试试- 可以成功----------------

SQL> alter database open;
SQL> create table a0122 as select *from dual;
SQL> alter session set container=pdb;
SQL> create table a0122 as select *from dual;

SQL> create restore point CDB_012220 GUARANTEE FLASHBACK DATABASE;
SQL> startup mount force;

SQL> flashback database to restore point CDB_012118;

Flashback complete.

SQL> alter database open resetlogs;-------CDB resetlogs
SQL> select *from a0122;
ORA-00942: table or view does not exist

SQL> alter session set container=pdb;

SQL> select *from a0122;
ORA-00942: table or view does not exist

SQL> select *from dual;

D
-
X

SQL> startup force mount;

SQL> flashback database to restore point CDB_012220;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select *from a0122;

D
-
X

SQL> alter session set container=pdb;

Session altered.

SQL> select *from a0122;

D
-
X

-----------------------------新建PDB 后的测试

SQL> create pluggable database pdb1 from pdb;
create pluggable database pdb1 from pdb
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/sysaux.288.1148481713
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted

SQL> show parameters create

db_create_file_dest string +DATA

SQL> alter system set db_create_file_dest=+DATA1;
alter system set db_create_file_dest=+DATA1
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> alter system set db_create_file_dest='+DATA1';
alter system set db_create_file_dest='+DATA1'
*
ERROR at line 1:
ORA-01031: insufficient privileges--为何退出就可以, CRS 中DB分派的DG没加入？失败后自动加入

SQL> exit
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> alter system set db_create_file_dest='+DATA1';

System altered.

SQL> create pluggable database pdb1 from pdb;
Pluggable database created.
SQL> create restore point CDB_012221 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> startup mount force;
SQL> flashback database to restore point CDB_012118;

Flashback complete.

---------------------ASM文件还在的
ASMCMD> ls
SYSAUX.652.1158917077
SYSTEM.653.1158917077
UNDOTBS1.649.1158917077
UNDO_3.650.1158917077
UNDO_4.651.1158917077
USERS.282.1158917077
ASMCMD> pwd
+DATA1/CDB/0F8A96DF673723EAE0636401A8C0884D/DATAFILE
ASMCMD>
-------------------ASM

SQL> alter database open resetlogs;

Database altered.

SQL> select *from a0122;
select *from a0122
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE YES
-----------------1PDB 没有了 ASM文件还在的
SQL> startup force mount;

SQL> flashback database to restore point CDB_012220;
SQL> alter database open resetlogs;
select *from a0122;
Database altered.

SQL>

D
-
X

SQL> create table a012201 as select *from dual;--------------这个表会没有

Table created.

SQL> startup mount force;

SQL> flashback database to restore point CDB_012221;---------------加完PDB后的，但又是创建a012201 之前的
flashback database to restore point CDB_012221
*
ERROR at line 1:
ORA-38795: WARNING: FLASHBACK DATABASE succeeded but OPEN RESETLOGS would fail
ORA-01135: file 23 accessed for DML/query is offline
ORA-01111: name for data file 23 is unknown - rename to correct file
ORA-01110: data file 23: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00023'

SQL> alter database open readonly;
alter database open readonly
*
ERROR at line 1:
ORA-02288: invalid OPEN mode

SQL> alter database open read only;

Database altered.

SQL> select *from a0122;

D
-
X

SQL> select *from a012201;
select *from a012201
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
5 PDB1 MOUNTED
SQL>