18c XE Flashback Pluggable Database Session
SQL> sho parameters reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> host df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 4.8G 0 4.8G 0% /dev
tmpfs 4.8G 0 4.8G 0% /dev/shm
tmpfs 4.8G 26M 4.8G 1% /run
tmpfs 4.8G 0 4.8G 0% /sys/fs/cgroup
/dev/mapper/ol-root 45G 24G 21G 54% /
/dev/xvda1 1014M 399M 616M 40% /boot
tmpfs 974M 12K 974M 1% /run/user/42
tmpfs 974M 0 974M 0% /run/user/0
tmpfs 974M 0 974M 0% /run/user/54321
SQL> sho parameters pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/18c/dbhome
XE/dbs/spfileXE.ora
SQL> alter system set db_recovery_file_dest_size=20G scope=BOTH;
System altered.
SQL> alter system set db_recovery_file_dest='/opt/oracle/oradata/XE/FRA' scope=BOTH;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1610612704 bytes
Fixed Size 8896480 bytes
Variable Size 520093696 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> set lines 170
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 1.95 0 2 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
YES
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 1.95 0 2 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
YES
SQL> alter session set container=xepdb1;
Session altered.
SQL> create restore point PDB1_GRP1 guarantee flashback database;
Restore point created.
SQL> create table tw (id number);
Table created.
SQL> insert into tw values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> shutdown immediate
Pluggable Database closed.
SQL> flashback pluggable database xepdb1 to restore point PDB1_GRP1;
Flashback complete.
SQL> show con_name
CON_NAME
------------------------------
XEPDB1
SQL> startup nomount
-- we are in the xepdb1 container
SQL> select name from v$pdbs;
NAME
--------------------------------------------------------
XEPDB1
SQL> alter pluggable database open resetlogs;
Pluggable database altered.
SQL> select * from dual;
D
-
X
SQL> desc tw
ERROR:
ORA-04043: object tw does not exist
SQL> select name from v$pdbs;
NAME
----------------------------------------------------------------
XEPDB1
SQL> ALTER SESSION SET container = cdb$root;
Session altered.
SQL> select name from v$pdbs;
NAME
---------------------------------------------------------------
PDB$SEED
XEPDB1
PDB2