Multitenant - Enable Flashback on CDB and perform flashback on CDB Common User

Multitenant -  Enable Flashback on CDB and  perform flashback on CDB Common User

• Create common user on CDB 

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 20:40:04 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE USER C##USER1 IDENTIFIED BY oracle CONTAINER=ALL;

User created.

SQL> GRANT CREATE SESSION TO C##USER1 CONTAINER=ALL;

Grant succeeded.

SQL>
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area/SMRCDB
db_recovery_file_dest_size           big integer 10398M

• Enable flashback on CDB database

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO

SQL> SHUTDOWN IMMEDIATE
;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size                  8793832 bytes
Variable Size             704643352 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440 SCOPE=BOTH;

System altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.


SQL> ALTER DATABASE OPEN;

Database altered.

• Check current scn and perform flashback by drop common user 

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    3477642

SQL> col username format A20
SELECT USERNAME, COMMON, CON_ID FROM CDB_USERS WHERE USERNAME='C##USER1';SQL>

USERNAME             COM     CON_ID
-------------------- --- ----------
C##USER1             YES          1

SQL> DROP USER C##USER1 CASCADE;

User dropped.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size                  8793832 bytes
Variable Size             704643352 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL>FLASHBACK DATABASE TO SCN 3477642;

Flashback complete.

• Check the status of common user and then open database in resetlogs 

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;

Pluggable database altered.

SQL> SELECT USERNAME, COMMON, CON_ID FROM CDB_USERS WHERE USERNAME='C##USER1';

USERNAME             COM     CON_ID
-------------------- --- ----------
C##USER1             YES          1
C##USER1             YES          5
C##USER1             YES          6
C##USER1             YES          8

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
STARTUP MOUNORACLE instance shut down.

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size                  8793832 bytes
Variable Size             704643352 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PLM2                           MOUNTED
         6 PLM4                           MOUNTED
         8 PLM5                           MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.