Configure and monitor Flashback Database

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Creating Restore Point.
[2] FLASHBACK DATABASE SCENARIO
2.1 FLASHBACK ONE OF THE PDB TABLE BEFORE TRUNCATE.
[3] Performing PDB DATABASE PITR.
[4] FLASHING BACK A CDB AFTER A PDB WAS RECOVERED WITH DBPITR.
[5] Perform various recovery senarios using Flashback Technology
5.1 Flashback Query
5.2 Flashback Version Query
5.3 Flashback transaction query
5.4 Flashback transaction backout
5.5 Flashback table
5.6 Flashback drop
5.7 Flashback archive

Oracle Flashback Database feature is used to revert the database back to the specific time when enabled.
It creates the flashback log files and store them in the flash recovery area by applying flashback log files.

We will configure oracle flashback database and bring the database back to the specific time without
restoring and recovering the database with RMAN.

You can use flashback database option both from RMAN and SQL *Plus.

[1] Creating restore point.

------------------------- 
Enable Flashback Database
-------------------------

-> To enable flashback database feature, set DB_FLASHBACK_RETENTION_TARGET parameter (in minutes)
and run the following command:

-> Below command enable flashback log to 24 hours.

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;


SQL> ALTER DATABASE Flashback ON;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> SELECT NAME,CDB,LOG_MODE,FLASHBACK_ON FROM V$DATABASE;

NAME CDB LOG_MODE FLASHBACK_ON
--------- --- ------------ ------------------
CDB1 YES ARCHIVELOG YES


-> Restore point is a name that is associated with the SCN number of the database or specific point in time 
and is used to flash a table or a database back.

SQL> CREATE RESTORE POINT rest_01;

-> To assure to flash the database back to the specific time or SCN value by keeping flashback log files
regardless the value of DB_FLASHBACK_RETENTION_TARGET parameter.

SQL> CREATE RESTORE POINT rest_guar_01 GUARANTEE Flashback DATABASE;

-> You can query the list of all restore points either using V$RESTORE_POINT view or running 
LIST RESTORE POINT ALL command from RMAN.

-> To drop restore point, use DROP RESTORE POINT restore_point_name command as follows:

SQL> DROP RESTORE POINT rest_01;


-> To disable flashback option for a specific tablespace use following command.
SQL> ALTER TABLESPACE T5 FLASHBACK OFF;

-> Run the following command to disable the flashback database option completely.
SQL> ALTER DATABASE FLASHBACK OFF;

-> To get a detail information about the flashback logs. query V$FLASHBACK_DATABASE_STAT; 
SQL> DESC V$FLASHBACK_DATABASE_STAT;
Name Null? Type
----------------------------------------- -------- ----------------------------
BEGIN_TIME DATE
END_TIME DATE
FLASHBACK_DATA NUMBER
DB_DATA NUMBER
REDO_DATA NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER
CON_ID NUMBER

[2] FLASHBACK DATABASE SCENARIO

1. Check the status of the flashback database and enable it.
SQL> SELECT flashback_on FROM V$DATABASE;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> SELECT flashback_on FROM V$DATABASE;

2. Change DB_FLASHBACK_RETENTION_TARGET parameter.

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;

3. Create a new table based of DBA_OBJECTS view. Update all rows.

SQL> create tablespace t7 datafile '/u01/app/oracle/oradata/cdb1/t7.dbf' size 5M AUTOEXTEND ON;
Tablespace created.

SQL> alter session set container=pdb1;
Session altered.

SQL> create tablespace t7 datafile '/u01/app/oracle/oradata/cdb1/pdb1/t7.dbf' size 5M autoextend on;
Tablespace created.

SQL> alter session set container=cdb$root;
Session altered.

SQL> create user c##t7 identified by t7 default tablespace t7 quota unlimited on t7 temporary tablespace temp;
User created.

SQL> grant connect,resource to c##t7 CONTAINER=ALL;
Grant succeeded.

SQL> connect c##t7/t7@//racnode2/cdb1
Connected.
SQL>
SQL> create table myt7 (id number);

Table created.

SQL> run
1 BEGIN
2 FOR I IN 1..5000
3 LOOP
4 INSERT INTO c##t7.myt7 values(I);
5 COMMIT;
6 END LOOP;
7* END ;

PL/SQL procedure successfully completed.

SQL> UPDATE c##t7.MYT7 set ID = ID +55;

5000 rows updated.

SQL> COMMIT;

Commit complete.

SQL> insert into c##t7.MYT7 select * from c##t7.MYT7;

5000 rows created.

SQL> commit;

Commit complete.

4. Check flash recovery area for flashback logs. Query v$FLASHBACK_DATABASE_STAT view.
SELECT * FROM V$FLASHBACK_DATABASE_STAT;

5. Get count of the rows the table.
SELECT COUNT(*) FROM c##t7.myt7;

Create restore point.
SQL> CREATE RESTORE POINT REVERT_TRUNCATE;

Get current SCN.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
4136247

Check restore point from V$RESTORE_POINT view.
SQL> SELECT * FROM V$RESTORE_POINT;

Delete all rows and commit the transaction.
SQL> DELETE FROM C##T7.MYT7;

10000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM C##T7.MYT7;

COUNT(*)
----------
0

6. Revert the database back to the restore point. Open the database and Check the count of the rows.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 666894440 bytes
Database Buffers 398458880 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO RESTORE POINT REVERT_TRUNCATE;
FLASHBACK DATABASE TO RESTORE POINT REVERT_TRUNCATE
*
ERROR at line 1:
ORA-38753: Cannot flashback data file 31; no flashback log data.
ORA-01110: data file 31:
'/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31'

SQL> ALTER TABLESPACE T5 FLASHBACK on;

Tablespace altered.

SQL> FLASHBACK DATABASE TO RESTORE POINT REVERT_TRUNCATE;
FLASHBACK DATABASE TO RESTORE POINT REVERT_TRUNCATE
*
ERROR at line 1:
ORA-38753: Cannot flashback data file 31; no flashback log data.
ORA-01110: data file 31:
'/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31'

SQL> ALTER DATABASE DATAFILE '/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31' offline drop;

Database altered.

SQL> FLASHBACK DATABASE TO RESTORE POINT REVERT_TRUNCATE;

Flashback complete.

-bash-4.1$ sql

SQL> alter database open RESETLOGS;

Database altered.

SQL> select count(*) from c##t7.myt7;

COUNT(*)
----------
10000

7. Get the current SCN and truncate the table.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
4137364

SQL> truncate table c##t7.myt7;

Table truncated.

8. Create the second table based on DBA_OBJECTS.
SQL> create table c##t7.myt7_2 tablespace t7 as select * from all_objects;

Table created.

9. Get the current SCN again.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
4137609

10.Flashback the database back to the SCN before truncate table command.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 666894440 bytes
Database Buffers 398458880 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 4137364;

Flashback complete.

Open the database in read only mode.
SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> SELECT COUNT(*) FROM C##T7.MYT7;

COUNT(*)
----------
10000

SQL> SELECT COUNT(*) FROM C##T7.MYT7_2;
SELECT COUNT(*) FROM C##T7.MYT7_2
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> QUIT

Export the data of the first table to the dump file.
-bash-4.1$ exp \'sys/oracle_4U as sysdba\' tables=c##t7.myt7 file=/home/oracle/myt7.dmp
Export: Release 12.1.0.2.0 - Production on Thu Aug 15 12:24:39 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to C##T7
. . exporting table MYT7 10000 rows exported
Export terminated successfully without warnings.

Bring the database forward to the last SCN.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 666894440 bytes
Database Buffers 398458880 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL>
SQL> FLASHBACK DATABASE TO SCN 4137609;

Flashback complete.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> SELECT COUNT(*) FROM C##T7.MYT7_2;

COUNT(*)
----------
89488

Open the database
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 666894440 bytes
Database Buffers 398458880 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL>
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

Import the data of the truncated table.
-bash-4.1$ imp \'sys/oracle_4U as sysdba\' tables=c##t7.myt7 file=/home/oracle/myt7.dmp data_only=y

Import: Release 12.1.0.2.0 - Production on Thu Aug 15 12:29:08 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export file created by EXPORT:V12.01.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
IMP-00029: cannot qualify table name by owner (c##t7.myt7), use FROMUSER parameter
IMP-00000: Import terminated unsuccessfully
-bash-4.1$
-bash-4.1$ imp \'sys/oracle_4U as sysdba\' tables=myt7 file=/home/oracle/myt7.dmp data_only=y fromuser=c##t7

Import: Release 12.1.0.2.0 - Production on Thu Aug 15 12:30:00 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export file created by EXPORT:V12.01.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing C##T7's objects into C##T7
. . importing table MYT7 10000 rows imported
Import terminated successfully without warnings.
-bash-4.1$

11. Check the row count of both tables.

-bash-4.1$ sql

SQL> SELECT COUNT(*) FROM C##T7.MYT7;

COUNT(*)
----------
10000

SQL> SELECT COUNT(*) FROM C##T7.MYT7_2;

COUNT(*)
----------
89488
#FLASHBACK ONE OF THE PDB TABLE BEFORE TRUNCATE.

SQL> alter session set container=pdb1;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL>
SQL> connect test/test@//racnode2/pdb1
Connected.

SQL> create table mytest5 (ID NUMBER);

Table created.

SQL> INSERT INTO MYTEST5 VALUES(555);

1 row created.

SQL> COMMIT
2 /

Commit complete.

SQL> SELECT * FROM MYTEST5;

ID
----------
555

SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL>
SQL> connect sys/oracle_4U@//racnode2/pdb1 as sysdba
Connected.
SQL>
SQL> select current_scn from V$database;

CURRENT_SCN
-----------
4242422

SQL> create restore point before_truncate_pdb1;
create restore point before_truncate_pdb1
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

SQL> connect / as sysdba
Connected.
SQL> select current_scn from V$database;

CURRENT_SCN
-----------
4242432

SQL> create restore point before_truncate_pdb1;

Restore point created.

SQL> connect sys/oracle_4U@//racnode2/pdb1 as sysdba
Connected.
SQL> truncate table test.MYTEST5;

Table truncated.

SQL> connect / as sysdba
Connected.
SQL>
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 666894440 bytes
Database Buffers 398458880 bytes
Redo Buffers 5455872 bytes
Database mounted.

SQL> FLASHBACK DATABASE TO RESTORE POINT before_truncate_pdb1;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

SQL> connect sys/oracle_4U@//racnode2/pdb1 as sysdba
Connected.

SQL> select * from test.MYTEST5;

ID
----------
555

SQL>

[3] Performing PDB DATABASE PITR.

RMAN> backup database plus archivelog;

-bash-4.1$ sqlplus sys/oracle_4U@//racnode2/pdb1 as sysdba

SQL> create tablespace dj_pdb1 datafile '/u01/app/oracle/oradata/cdb1/pdb1/dj_pdb1.dbf' size 10m;

Tablespace created.

SQL> create user dj identified by dj temporary tablespace temp default tablespace dj_pdb1;

User created.

SQL> grant create session, create table, unlimited tablespace to dj;

Grant succeeded.

SQL> create table dj.t1(c varchar2(100)) tablespace dj_pdb1;

Table created.

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
4477747

SQL> SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL>
SQL> begin
for i in 1.. 10000 loop
insert into dj.t1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
4477776

SQL> SQL>
SQL>
SQL>
SQL> select count(*) from dj.t1;

COUNT(*)
----------
10000

SQL> truncate table dj.t1;

Table truncated.

SQL> select count(*) from dj.t1;

COUNT(*)
----------
0

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
4477856

SQL> alter pluggable database pdb1 close;

-bash-4.1$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 16 12:23:44 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=989477122)

RMAN> run {
set until SCN = 4477776 ;
restore pluggable database pdb1;
recover pluggable database pdb1 auxiliary destination='/home/oracle/aux';
alter pluggable database pdb1 open resetlogs;
}
2> 3> 4> 5> 6>
executing command: SET until clause

Starting restore at Aug 16 2019 12:23:54
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=33 device type=DISK

creating datafile file number=44 name=/u01/app/oracle/oradata/cdb1/pdb1/dj_pdb1.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9
channel ORA_DISK_1: restoring datafile 00010 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10
channel ORA_DISK_1: restoring datafile 00032 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32
channel ORA_DISK_1: restoring datafile 00034 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34
channel ORA_DISK_1: restoring datafile 00035 to /u01/app/oracle/oradata/cdb1/pdb1/tc.dbf
channel ORA_DISK_1: restoring datafile 00042 to /u01/app/oracle/oradata/cdb1/pdb1/t7.dbf
channel ORA_DISK_1: restoring datafile 00043 to /u01/app/oracle/oradata/cdb1/pdb1/t10.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godn944p_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00008 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8
channel ORA_DISK_2: restoring datafile 00012 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12
channel ORA_DISK_2: restoring datafile 00017 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17
channel ORA_DISK_2: restoring datafile 00025 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25
channel ORA_DISK_2: restoring datafile 00036 to /u01/app/oracle/oradata/cdb1/pdb1/tbs_pitr.dbf
channel ORA_DISK_2: restoring datafile 00039 to /u01/app/oracle/oradata/cdb1/pdb1/tbs_test_pitr.dbf
channel ORA_DISK_2: restoring datafile 00040 to /u01/app/oracle/oradata/cdb1/pdb1/t6.dbf
channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godnbvqm_.bkp
channel ORA_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godnbvqm_.bkp tag=TAG20190816T121450
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godn944p_.bkp tag=TAG20190816T121450
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at Aug 16 2019 12:24:31

Starting recover at Aug 16 2019 12:24:31
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='Dafa'

initialization parameters used for automatic instance:
db_name=CDB1
db_unique_name=Dafa_pitr_pdb1_CDB1
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1024M
processes=200
db_create_file_dest=/home/oracle/aux
log_archive_dest_1='location=/home/oracle/aux'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

starting up automatic instance CDB1

Oracle instance started

Total System Global Area 1073741824 bytes

Fixed Size 2932632 bytes
Variable Size 281018472 bytes
Database Buffers 784334848 bytes
Redo Buffers 5455872 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until scn 4477776;
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script

executing command: SET until clause

Starting restore at Aug 16 2019 12:24:48
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=173 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_16/o1_mf_s_1016453849_godnd1ox_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_16/o1_mf_s_1016453849_godnd1ox_.bkp tag=TAG20190816T121729
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/aux/CDB1/controlfile/o1_mf_godnssnj_.ctl
Finished restore at Aug 16 2019 12:24:50

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until scn 4477776;
# switch to valid datafilecopies
switch clone datafile 8 to datafilecopy
"/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8";
switch clone datafile 9 to datafilecopy
"/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9";
switch clone datafile 10 to datafilecopy
"/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10";
switch clone datafile 12 to datafilecopy
"/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12";
switch clone datafile 17 to datafilecopy
"/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17";
switch clone datafile 25 to datafilecopy
"/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25";
switch clone datafile 32 to datafilecopy
"/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32";
switch clone datafile 34 to datafilecopy
"/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34";
switch clone datafile 35 to datafilecopy
"/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf";
switch clone datafile 36 to datafilecopy
"/u01/app/oracle/oradata/cdb1/pdb1/tbs_pitr.dbf";
switch clone datafile 39 to datafilecopy
"/u01/app/oracle/oradata/cdb1/pdb1/tbs_test_pitr.dbf";
switch clone datafile 40 to datafilecopy
"/u01/app/oracle/oradata/cdb1/pdb1/t6.dbf";
switch clone datafile 42 to datafilecopy
"/u01/app/oracle/oradata/cdb1/pdb1/t7.dbf";
switch clone datafile 43 to datafilecopy
"/u01/app/oracle/oradata/cdb1/pdb1/t10.dbf";
switch clone datafile 44 to datafilecopy
"/u01/app/oracle/oradata/cdb1/pdb1/dj_pdb1.dbf";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 26 to new;
set newname for clone datafile 28 to new;
set newname for clone datafile 29 to new;
set newname for clone datafile 30 to new;
set newname for clone datafile 33 to new;
set newname for clone datafile 41 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3, 26, 28, 29, 30, 33, 41;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

datafile 8 switched to datafile copy
input datafile copy RECID=74 STAMP=1016454296 file name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8

datafile 9 switched to datafile copy
input datafile copy RECID=75 STAMP=1016454296 file name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9

datafile 10 switched to datafile copy
input datafile copy RECID=76 STAMP=1016454296 file name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10

datafile 12 switched to datafile copy
input datafile copy RECID=77 STAMP=1016454296 file name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12

datafile 17 switched to datafile copy
input datafile copy RECID=78 STAMP=1016454296 file name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17

datafile 25 switched to datafile copy
input datafile copy RECID=79 STAMP=1016454296 file name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25

datafile 32 switched to datafile copy
input datafile copy RECID=80 STAMP=1016454296 file name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32

datafile 34 switched to datafile copy
input datafile copy RECID=81 STAMP=1016454296 file name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34

datafile 35 switched to datafile copy
input datafile copy RECID=82 STAMP=1016454296 file name=/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf

datafile 36 switched to datafile copy
input datafile copy RECID=83 STAMP=1016454296 file name=/u01/app/oracle/oradata/cdb1/pdb1/tbs_pitr.dbf

datafile 39 switched to datafile copy
input datafile copy RECID=84 STAMP=1016454296 file name=/u01/app/oracle/oradata/cdb1/pdb1/tbs_test_pitr.dbf

datafile 40 switched to datafile copy
input datafile copy RECID=85 STAMP=1016454296 file name=/u01/app/oracle/oradata/cdb1/pdb1/t6.dbf

datafile 42 switched to datafile copy
input datafile copy RECID=86 STAMP=1016454296 file name=/u01/app/oracle/oradata/cdb1/pdb1/t7.dbf

datafile 43 switched to datafile copy
input datafile copy RECID=87 STAMP=1016454296 file name=/u01/app/oracle/oradata/cdb1/pdb1/t10.dbf

datafile 44 switched to datafile copy
input datafile copy RECID=88 STAMP=1016454296 file name=/u01/app/oracle/oradata/cdb1/pdb1/dj_pdb1.dbf

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at Aug 16 2019 12:24:56
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/aux/CDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00026 to /home/oracle/aux/CDB1/datafile/o1_mf_mytbs01_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00028 to /home/oracle/aux/CDB1/datafile/o1_mf_t2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00033 to /home/oracle/aux/CDB1/datafile/o1_mf_user2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00041 to /home/oracle/aux/CDB1/datafile/o1_mf_t7_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godn731g_.bkp
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to /home/oracle/aux/CDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: restoring datafile 00004 to /home/oracle/aux/CDB1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_2: restoring datafile 00029 to /home/oracle/aux/CDB1/datafile/o1_mf_t3_%u_.dbf
channel ORA_AUX_DISK_2: restoring datafile 00030 to /home/oracle/aux/CDB1/datafile/o1_mf_t4_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godn730d_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godn731g_.bkp tag=TAG20190816T121450
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
channel ORA_AUX_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godn730d_.bkp tag=TAG20190816T121450
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:25
Finished restore at Aug 16 2019 12:26:21

datafile 1 switched to datafile copy
input datafile copy RECID=98 STAMP=1016454382 file name=/home/oracle/aux/CDB1/datafile/o1_mf_system_godnt15j_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=99 STAMP=1016454382 file name=/home/oracle/aux/CDB1/datafile/o1_mf_undotbs1_godnt16w_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=100 STAMP=1016454382 file name=/home/oracle/aux/CDB1/datafile/o1_mf_sysaux_godnt125_.dbf
datafile 26 switched to datafile copy
input datafile copy RECID=101 STAMP=1016454382 file name=/home/oracle/aux/CDB1/datafile/o1_mf_mytbs01_godnt14n_.dbf
datafile 28 switched to datafile copy
input datafile copy RECID=102 STAMP=1016454382 file name=/home/oracle/aux/CDB1/datafile/o1_mf_t2_godnt16p_.dbf
datafile 29 switched to datafile copy
input datafile copy RECID=103 STAMP=1016454382 file name=/home/oracle/aux/CDB1/datafile/o1_mf_t3_godnt177_.dbf
datafile 30 switched to datafile copy
input datafile copy RECID=104 STAMP=1016454382 file name=/home/oracle/aux/CDB1/datafile/o1_mf_t4_godnt18x_.dbf
datafile 33 switched to datafile copy
input datafile copy RECID=105 STAMP=1016454382 file name=/home/oracle/aux/CDB1/datafile/o1_mf_user2_godnt161_.dbf
datafile 41 switched to datafile copy
input datafile copy RECID=106 STAMP=1016454382 file name=/home/oracle/aux/CDB1/datafile/o1_mf_t7_godnt13n_.dbf

contents of Memory Script:
{
# set requested point in time
set until scn 4477776;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 3 online";
sql clone 'PDB1' "alter database datafile
8 online";
sql clone 'PDB1' "alter database datafile
9 online";
sql clone 'PDB1' "alter database datafile
10 online";
sql clone 'PDB1' "alter database datafile
12 online";
sql clone 'PDB1' "alter database datafile
17 online";
sql clone 'PDB1' "alter database datafile
25 online";
sql clone 'PDB1' "alter database datafile
32 online";
sql clone 'PDB1' "alter database datafile
34 online";
sql clone 'PDB1' "alter database datafile
35 online";
sql clone 'PDB1' "alter database datafile
36 online";
sql clone 'PDB1' "alter database datafile
39 online";
sql clone 'PDB1' "alter database datafile
40 online";
sql clone 'PDB1' "alter database datafile
42 online";
sql clone 'PDB1' "alter database datafile
43 online";
sql clone 'PDB1' "alter database datafile
44 online";
sql clone "alter database datafile 26 online";
sql clone "alter database datafile 28 online";
sql clone "alter database datafile 29 online";
sql clone "alter database datafile 30 online";
sql clone "alter database datafile 33 online";
sql clone "alter database datafile 41 online";
# recover pdb
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX", "MYTBS01", "T2", "T3", "T4", "USER2", "T7" pluggable database
'PDB1' delete archivelog;
sql clone 'alter database open read only';
plsql <<<begin
add_dropped_ts;
end; >>>;
plsql <<<begin
save_pdb_clean_scn;
end; >>>;
# shutdown clone before import
shutdown clone abort
plsql <<<begin
pdbpitr_inspect(pdbname => 'PDB1');
end; >>>;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 8 online

sql statement: alter database datafile 9 online

sql statement: alter database datafile 10 online

sql statement: alter database datafile 12 online

sql statement: alter database datafile 17 online

sql statement: alter database datafile 25 online

sql statement: alter database datafile 32 online

sql statement: alter database datafile 34 online

sql statement: alter database datafile 35 online

sql statement: alter database datafile 36 online

sql statement: alter database datafile 39 online

sql statement: alter database datafile 40 online

sql statement: alter database datafile 42 online

sql statement: alter database datafile 43 online

sql statement: alter database datafile 44 online

sql statement: alter database datafile 26 online

sql statement: alter database datafile 28 online

sql statement: alter database datafile 29 online

sql statement: alter database datafile 30 online

sql statement: alter database datafile 33 online

sql statement: alter database datafile 41 online

Starting recover at Aug 16 2019 12:26:23
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

starting media recovery

archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_17_godnczfl_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_18_godns7xq_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_17_godnczfl_.arc thread=1 sequence=17
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_18_godns7xq_.arc thread=1 sequence=18
media recovery complete, elapsed time: 00:00:02
Finished recover at Aug 16 2019 12:26:27

sql statement: alter database open read only

Oracle instance shut down

Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/aux/CDB1/datafile/o1_mf_sysaux_godnt125_.dbf deleted
auxiliary instance file /home/oracle/aux/CDB1/controlfile/o1_mf_godnssnj_.ctl deleted
Finished recover at Aug 16 2019 12:26:36

Statement processed

RMAN> quit

Recovery Manager complete.
-bash-4.1$
-bash-4.1$ sqlplus sys/oracle_4U@//racnode2/pdb1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 16 12:27:08 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count(*) from dj.t1;

COUNT(*)
----------
10000

[4] FLASHING BACK A CDB AFTER A PDB WAS RECOVERED WITH DBPITR.

If you’ve performed a DBPITR recovery on any of the PDBs in a CDB, you can’t perform a flashback database operation on that CDB.
The reason is that you can’t directly rewind the CDB to a point before that at which you performed the DBPITR for a PDB.

-> However, you can follow these steps to flash back a CDB to a point beyond that at which you performed the DBPITR operation on a PDB.

-bash-4.1$ sql

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> alter database flashback on;

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
4479402

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
4479416

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
4479418

-bash-4.1$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 16 12:34:30 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=989477122)

RMAN> alter pluggable database pdb1 datafile all offline;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 08/16/2019 12:34:49
ORA-65046: operation not allowed from outside a pluggable database

RMAN> quit
Recovery Manager complete.

-bash-4.1$
-bash-4.1$ rman target sys/oracle_4U@racnode2/pdb1
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 16 12:35:28 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=989477122)

RMAN> alter pluggable database pdb1 datafile all offline;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 08/16/2019 12:35:42
ORA-65025: Pluggable database is not closed on all instances.

RMAN> shutdown immediate
database closed

RMAN> alter pluggable database pdb1 datafile all offline;

Statement processed

RMAN> quit
Recovery Manager complete.
-bash-4.1$
-bash-4.1$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 16 12:36:10 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=989477122)

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 1073741824 bytes

Fixed Size 2932632 bytes
Variable Size 666894440 bytes
Database Buffers 398458880 bytes
Redo Buffers 5455872 bytes

RMAN> flashback database to scn 4479418;

Starting flashback at Aug 16 2019 12:37:06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=248 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=13 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished flashback at Aug 16 2019 12:37:10

RMAN> alter database open resetlogs;

Statement processed

RMAN> restore pluggable database pdb1;

Starting restore at Aug 16 2019 12:38:13
using channel ORA_DISK_1
using channel ORA_DISK_2

creating datafile file number=44 name=/u01/app/oracle/oradata/cdb1/pdb1/dj_pdb1.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9
channel ORA_DISK_1: restoring datafile 00010 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10
channel ORA_DISK_1: restoring datafile 00032 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32
channel ORA_DISK_1: restoring datafile 00034 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34
channel ORA_DISK_1: restoring datafile 00035 to /u01/app/oracle/oradata/cdb1/pdb1/tc.dbf
channel ORA_DISK_1: restoring datafile 00042 to /u01/app/oracle/oradata/cdb1/pdb1/t7.dbf
channel ORA_DISK_1: restoring datafile 00043 to /u01/app/oracle/oradata/cdb1/pdb1/t10.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godn944p_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00008 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8
channel ORA_DISK_2: restoring datafile 00012 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12
channel ORA_DISK_2: restoring datafile 00017 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17
channel ORA_DISK_2: restoring datafile 00025 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25
channel ORA_DISK_2: restoring datafile 00036 to /u01/app/oracle/oradata/cdb1/pdb1/tbs_pitr.dbf
channel ORA_DISK_2: restoring datafile 00039 to /u01/app/oracle/oradata/cdb1/pdb1/tbs_test_pitr.dbf
channel ORA_DISK_2: restoring datafile 00040 to /u01/app/oracle/oradata/cdb1/pdb1/t6.dbf
channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godnbvqm_.bkp
channel ORA_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godnbvqm_.bkp tag=TAG20190816T121450
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_16/o1_mf_nnndf_TAG20190816T121450_godn944p_.bkp tag=TAG20190816T121450
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at Aug 16 2019 12:38:50

RMAN> alter pluggable database pdb1 datafile all online;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 08/16/2019 12:39:06
ORA-65046: operation not allowed from outside a pluggable database

RMAN> quit
Recovery Manager complete.

-bash-4.1$ rman target sys/oracle_4U@racnode2/pdb1
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 16 12:39:10 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=989477122, not open)

RMAN> alter pluggable database pdb1 datafile all online;

using target database control file instead of recovery catalog
Statement processed

RMAN> quit
Recovery Manager complete.
-bash-4.1$
-bash-4.1$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 16 12:39:23 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=989477122)

RMAN> recover pluggable database pdb1 ;

Starting recover at Aug 16 2019 12:39:41
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=261 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=262 device type=DISK

starting media recovery

archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_17_godnczfl_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_18_godokc4b_.arc
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_19_godokbqw_.arc
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_20_godokc05_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_17_godnczfl_.arc thread=1 sequence=17
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_18_godokc4b_.arc thread=1 sequence=18
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_19_godokbqw_.arc thread=1 sequence=19
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_16/o1_mf_1_20_godokc05_.arc thread=1 sequence=20
media recovery complete, elapsed time: 00:00:02
Finished recover at Aug 16 2019 12:39:45

RMAN> alter pluggable database pdb1 open;

Statement processed

RMAN> quit
Recovery Manager complete.

-bash-4.1$ sqlplus sys/oracle_4U@racnode2/pdb1 as sysdba

SQL> select CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
4480668

SQL> SELECT COUNT(*) FROM DJ.T1;

COUNT(*)
----------
10000

-bash-4.1$ sql
SQL> startup force
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 666894440 bytes
Database Buffers 398458880 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO

SQL> select name ,con_id from v$tempfile;

NAME
--------------------------------------------------------------------------------
CON_ID
----------
/u01/app/oracle/oradata/cdb1/temp01.dbf
1

/u01/app/oracle/oradata/cdb1/pdbseed/temp03.dbf
2

SQL> alter session set container=pdb1;

Session altered.

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/cdb1/pdb1/temp05.dbf' size 5M autoextend on;

Tablespace altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 666894440 bytes
Database Buffers 398458880 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> select name ,con_id from v$tempfile;

NAME
--------------------------------------------------------------------------------
CON_ID
----------
/u01/app/oracle/oradata/cdb1/temp01.dbf
1

/u01/app/oracle/oradata/cdb1/pdb1/temp05.dbf
3

/u01/app/oracle/oradata/cdb1/pdbseed/temp03.dbf
2

[5] Perform various recovery senarios using Flashback Technology

Flashback Query

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> show parameter db_flashback_retention_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL>

SQL> connect system/oracle_4U@racnode2/pdb1
Connected.
SQL>
SQL> create table mytable as select * from dba_objects;

Table created.

SQL> select count(*) from mytable;

COUNT(*)
----------
91392

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
4784520

SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'),dbms_flashback.get_system_change_number() from dual;

TO_CHAR(SYSDATE,'DD DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
------------------- -----------------------------------------
16-08-2019 14:50:40 4784525

SQL> delete from mytable;

91392 rows deleted.

SQL> select * from mytable;

no rows selected

SQL> select count(*) from mytable as of timestamp to_timestamp('16-08-2019 14:50:40','DD-MM-YYYY HH24:MI:SS');

COUNT(*)
----------
91392

SQL> select count(*) from mytable as of scn 4784520;

COUNT(*)
----------
91392

SQL> select count(*) from mytable as of scn 4784525;

COUNT(*)
----------
91392

SQL> select count(*) from mytable;

COUNT(*)
----------
0

SQL>

Flashback Version Query

#VERSIONS BETWEEN { SCN | TIMESTAMP } start AND end

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> ALTER DATABASE ADD SUPPLIMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLIMENTAL LOG DATA
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> connect system/oracle_4U@racnode2/pdb1
Connected.

SQL> CREATE TABLE TBL_FB_VQ
2 (ID NUMBER,NAME VARCHAR2(25));

Table created.

SQL> INSERT INTO TBL_FB_VQ
2 VALUES(1,'Oracle Certified Master');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
4790671

SQL> UPDATE TBL_FB_VQ SET NAME='This is first update';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> UPDATE TBL_FB_VQ SET NAME='This is second update';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> DELETE FROM TBL_FB_VQ;

1 row deleted.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
4790699

SQL> DESC TBL_FB_VQ
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
ID NUMBER
NAME VARCHAR2(25)

SET LINESIZE 150
COL VERSIONS_STARTSCN FORMAT 999999999999999
COL VERSIONS_ENDSCN FORMAT 999999999999999
COL VERSIONS_STARTTIME FORMAT A24
COL VERSIONS_ENDTIME FORMAT A24
COL VERSIONS_OPERATION FORMAT A1
COL VERSIONS_XID FORMAT A20
SELECT TO_CHAR(VERSIONS_STARTTIME,'hh24:mi:ss') starttime,
TO_CHAR(VERSIONS_ENDTIME,'hh24:mi:ss') endtime,
VERSIONS_XID,
VERSIONS_OPERATION,
VERSIONS_STARTSCN START_SCN,
VERSIONS_ENDSCN END_SCN,
ID,
NAME
FROM TBL_FB_VQ
VERSIONS BETWEEN scn 4790671 AND 4790699;

STARTTIM ENDTIME VERSIONS_XID V START_SCN END_SCN ID NAME
-------- -------- -------------------- - ---------- ---------- ---------- ---------------
17:07:15 01002100B1060000 U 4790691 1 This is second
update

17:07:00 17:07:15 03000C0019070000 U 4790684 4790691 1 This is first u
pdate

17:07:00 4790684 1 Oracle Certifie
d Master

SQL> select * from TBL_FB_VQ;

ID NAME
---------- -------------------------
1 This is second update

SQL> INSERT INTO TBL_FB_VQ VALUES(2,'First Insert');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> UPDATE TBL_FB_VQ SET NAME='First Update' where ID=2;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> UPDATE TBL_FB_VQ SET NAME='Second Update' where ID=2;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> select current_scn,current_timestamp from v$database;

CURRENT_SCN CURRENT_TIMESTAMP
----------- ---------------------------------------------------------------------------
4793066 16-AUG-19 05.50.56.548732 PM +05:30

SQL> SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
last_name, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP(' 2 3
SQL>
SQL>
SQL> SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
last_name, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP(' 2 3
SQL>
SQL> SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
id,name
FROM tbl_fb_vq
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('16-AUG-19 05.49.01', 'DD-MON-YY HH24:MI:SS')
AND TO_TIMESTAMP('16-AUG-19 05.50.56', 'DD-MON-YY HH24:MI:SS'); 2 3 4 5 6 7 8
FROM tbl_fb_vq
*
ERROR at line 5:
ORA-01466: unable to read data - table definition has changed

SQL> SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
id,name
FROM tbl_fb_vq
VERSIONS BETWEEN scn
4793000
AND 4793066; 2 3 4 5 6 7 8

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V ID NAME
----------------- ------------------------ ---------------- ------------------------ -------------------- - ---------- -------------------------
4793063 16-AUG-19 05.50.47 PM 03001B001C070000 U 2 Second Update
4793052 16-AUG-19 05.50.26 PM 4793063 16-AUG-19 05.50.47 PM 06001A00D2080000 U 2 First Update
4793024 16-AUG-19 05.49.51 PM 4793052 16-AUG-19 05.50.26 PM 05001E004F070000 I 2 First Insert
1 This is second update

SQL> SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
id,name
FROM tbl_fb_vq
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('16-AUG-19 17:49:01', 'DD-MON-YY HH24:MI:SS')
AND TO_TIMESTAMP('16-AUG-19 17:50:56', 'DD-MON-YY HH24:MI:SS'); 2 3 4 5 6 7 8

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V ID NAME
----------------- ------------------------ ---------------- ------------------------ -------------------- - ---------- -------------------------
4793063 16-AUG-19 05.50.47 PM 03001B001C070000 U 2 Second Update
4793052 16-AUG-19 05.50.26 PM 4793063 16-AUG-19 05.50.47 PM 06001A00D2080000 U 2 First Update
4793024 16-AUG-19 05.49.51 PM 4793052 16-AUG-19 05.50.26 PM 05001E004F070000 I 2 First Insert
1 This is second update

Flashback transaction query

SQL> set pagesize 1000
col logon_user format a5
col operation format a10
col table_name format a25
col undo_sql format a50
select xid,start_scn,logon_user,operation,table_name,undo_sql
from flashback_transaction_query
where table_name='TBL_FB_VQ';SQL> SQL> SQL> SQL> SQL> 2 3
select xid,start_scn,logon_user,operation,table_name,undo_sql
*
ERROR at line 1:
ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles

From oracle document:

Multitenant Container Database Restrictions for Oracle Flashback Technology
These Oracle Flashback Technology features are unavailable for a multitenant container database (CDB):

For Oracle Database 12c Release 1 (12.1.0.1), Flashback Data Archive (FDA) is not supported in a CDB.
For Oracle Database 12c Release 1 (12.1.0.2), this restriction is removed.

Flashback Transaction Query is not supported in a CDB.
Flashback Transaction Backout is not supported in a CDB.

sql
SQL> select name,cdb from v$database;

NAME CDB
--------------------------- ---------
ORCL NO

SQL>shutdown
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;

SQL>alter database flashback on;
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SQL>SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

SQL>alter system set undo_retention=3600;

SQL> connect scott/tiger@racnode2/orcl
Connected.
SQL>
SQL>
SQL> create table fbvq (id number,name varchar2(25),primary key(id));

Table created.

SQL> insert into fbvq values(&1,'&2');
Enter value for 1: 1
Enter value for 2: OCM12c
old 1: insert into fbvq values(&1,'&2')
new 1: insert into fbvq values(1,'OCM12c')

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
3884373

SQL> insert into fbvq values(&1,'&2');
Enter value for 1: 2
Enter value for 2: OCP12C
old 1: insert into fbvq values(&1,'&2')
new 1: insert into fbvq values(2,'OCP12C')

1 row created.

SQL> commit;

Commit complete.

SQL> update fbvq set name='This is first update' where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> update fbvq set name='This is second update' where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
3884417

SQL> select * from fbvq;

ID NAME
---------- ---------------------------------------------------------------------------
1 OCM12c
2 This is second update

SQL>
set pagesize 1000
set linesize 150
col logon_user format a5
col operation format a10
col table_name format a25
col undo_sql format a50
select xid,start_scn,logon_user,operation,table_name,undo_sql
from flashback_transaction_query
where table_name='FBVQ';

XID START_SCN LOGON OPERATION TABLE_NAME UNDO_SQL
---------------- ---------- ----- ---------- ------------------------- --------------------------------------------------
0100130002060000 3884396 SCOTT UPDATE FBVQ update "SCOTT"."FBVQ" set "NAME" = 'OCP12C' where
ROWID = 'AAAWktAAGAAAADtAAB';

0600000026080000 3884366 SCOTT INSERT FBVQ delete from "SCOTT"."FBVQ" where ROWID = 'AAAWktAA
GAAAADtAAA';

07000B0082050000 3884403 SCOTT UPDATE FBVQ update "SCOTT"."FBVQ" set "NAME" = 'This is first
update' where ROWID = 'AAAWktAAGAAAADtAAB';

090012006D060000 3884384 SCOTT INSERT FBVQ delete from "SCOTT"."FBVQ" where ROWID = 'AAAWktAA
GAAAADtAAB';

Flashback transaction backout

#Flashback Transaction Backout is not supported in a CDB.

SQL> show user
USER is "SCOTT"
SQL> connect / as sysdba
Connected.

SQL>
SQL> DECLARE
v_txid xid_array;
BEGIN
v_txid := sys.xid_array('07000B0082050000');
dbms_flashback.transaction_backout(1,v_txid,dbms_flashback.cascade);
END;
/ 2 3 4 5 6 7
DECLARE
*
ERROR at line 1:
ORA-55507: Encountered mining error during Flashback Transaction Backout. function:krvxglsr
ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5

-bash-4.1$ oerr ora 55507
55507, 0000, "Encountered mining error during Flashback Transaction Backout. function:%s"
// *Cause: Mining error.
// *Action: None.

SQL> select name from v$archived_log;
no rows selected

SQL> show user
USER is "SYS"
SQL> alter system switch logfile;

System altered.

SQL> select name from v$archived_log;

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_08_17/o1_mf_1_14_goh9rmq7_.arc

SQL> select xid,start_scn,logon_user,operation,table_name,undo_sql
2 from flashback_transaction_query
3 where table_name='FBVQ'
/

XID START_SCN LOGON OPERATION TABLE_NAME UNDO_SQL
---------------- ---------- ----- ---------- ------------------------- --------------------------------------------------
0100130002060000 3884396 SCOTT UPDATE FBVQ update "SCOTT"."FBVQ" set "NAME" = 'OCP12C' where
ROWID = 'AAAWktAAGAAAADtAAB';

0600000026080000 3884366 SCOTT INSERT FBVQ delete from "SCOTT"."FBVQ" where ROWID = 'AAAWktAA
GAAAADtAAA';

07000B0082050000 3884403 SCOTT UPDATE FBVQ update "SCOTT"."FBVQ" set "NAME" = 'This is first
update' where ROWID = 'AAAWktAAGAAAADtAAB';

090012006D060000 3884384 SCOTT INSERT FBVQ delete from "SCOTT"."FBVQ" where ROWID = 'AAAWktAA
GAAAADtAAB';

SQL> select * from scott.fbvq;

ID NAME
---------- ---------------------------------------------------------------------------
1 OCM12c
2 This is second update

SQL> DECLARE
v_txid xid_array;
BEGIN
v_txid := sys.xid_array('07000B0082050000');
dbms_flashback.transaction_backout(1,v_txid,dbms_flashback.cascade);
END;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> select * from scott.fbvq;

ID NAME
---------- ---------------------------------------------------------------------------
1 OCM12c
2 This is first update

Flashback table

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
SQL>
SQL> connect system/oracle_4U@racnode2/pdb1
Connected.
SQL>
SQL> create table tbl_fb as select * from dba_objects;

Table created.

SQL> select count(*) from tbl_fb;

COUNT(*)
----------
91394

SQL> alter table tbl_fb enable row movement;

Table altered.

SQL> select count(*) from tbl_fb;

COUNT(*)
----------
91394

SQL> select current_scn,current_scn from v$database;

CURRENT_SCN CURRENT_SCN
----------- -----------
4794233 4794233

SQL> select current_scn,current_timestamp from v$database;

CURRENT_SCN
-----------
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
4794246
16-AUG-19 06.19.15.552434 PM +05:30

SQL> delete tbl_fb;

91394 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tbl_fb;

COUNT(*)
----------
0

SQL> flashback table tbl_fb to scn 4794246;

Flashback complete.

SQL> select count(*) from tbl_fb;

COUNT(*)
----------
91394

Flashback drop

#Not working for sys and system

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> connect t10/t10@racnode2/pdb1
Connected.
SQL>
SQL> create table tblfbdrop (ID NUMBER,NAME VARCHAR2(25));

Table created.

SQL> INSERT INTO tblfbdrop VALUES(1,'First Record');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO tblfbdrop VALUES(2,'Second Record');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> show recyclebin
SQL> drop table tblfbdrop;

Table dropped.

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TBLFBDROP BIN$kDyETB7PLCngU2Y4qMAkfw==$0 TABLE 2019-08-16:18:26:20
SQL>
SQL> select * from "BIN$kDyETB7PLCngU2Y4qMAkfw==$0";

ID NAME
---------- -------------------------
1 First Record
2 Second Record

SQL> flashback table tblfbdrop to before drop;

Flashback complete.

SQL> select * from tblfbdrop;

ID NAME
---------- -------------------------
1 First Record
2 Second Record

SQL> show recyclebin
SQL>
SQL> CREATE TABLE tblfbdrop2 (ID NUMBER);

Table created.

SQL> INSERT INTO tblfbdrop2 (10);
INSERT INTO tblfbdrop2 (10)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> INSERT INTO tblfbdrop2 values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tblfbdrop2;

ID
----------
10

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

SQL> show recyclebin;
SQL> drop table tblfbdrop2;

Table dropped.

SQL> show recycblebin
SP2-0735: unknown SHOW option beginning "recycblebi..."
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TBLFBDROP2 BIN$kDyETB7QLCngU2Y4qMAkfw==$0 TABLE 2019-08-16:18:30:26
SQL>
SQL> select * from "BIN$kDyETB7QLCngU2Y4qMAkfw==$0";

ID
----------
10

SQL> flashback table tblfbdrop2 to before drop rename to TBLFB2;

Flashback complete.

SQL> show recyclebin
SQL> select * from TBLFB2;

ID
----------
10
Flashback archive

-bash-4.1$ sqlplus sys/oracle_4U@racnode2/pdb1 as sysdba
SQL>
SQL> create tablespace tbs_fba datafile '/u01/app/oracle/oradata/cdb1/pdb1/tbs_fba.dbf' size 10M autoextend on;

Tablespace created.

SQL> create user user_fba identified by fba default tablespace tbs_fba quota unlimited on tbs_fba temporary tablespace temp;

User created.

SQL> grant connect,resource,flashback archive administer to user_fba;

Grant succeeded.

SQL> create flashback archive f1_archive tablespace tbs_fba retention 1 year;

Flashback archive created.

SQL> connect user_fba/fba@racnode2/pdb1
Connected.
SQL>
SQL> create table tbl_f1_archive(id number,name varchar2(10));

Table created.

SQL> insert into tbl_f1_archive values(1,'Hey FBA');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tbl_f1_archive;

ID NAME
---------- ----------
1 Hey FBA

SQL> alter table tbl_f1_archive flashback archive f1_archive;

Table altered.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
16-AUG-19 06.40.51.207982 PM +05:30

SQL> delete from tbl_f1_archive;

1 row deleted.

SQL> commit;
Commit complete.

SQL> SQL>
SQL> select * from tbl_f1_archive;

no rows selected

SQL> select * from tbl_f1_archive as of timestamp to_timestamp('16-AUG-19 18.40.51','DD-MON-YY HH24:MI:SS');

ID NAME
---------- ----------
1 Hey FBA

SQL> CONNECT / AS SYSDBA
Connected.
SQL>
SQL> SHOW USER
USER is "SYS"
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
SQL> connect user_fba/fba@racnode2/pdb1
Connected.
SQL>
SQL> alter flashback archive f1_archive modify retention 2 year;

Flashback archive altered.

SQL> alter flashback archive f1_archive modify tablespace tbs_fba quota 1G;

Flashback archive altered.

SQL> alter flashback archive f1_archive purge all;

Flashback archive altered.

SQL> select * from tbl_f1_archive as of timestamp to_timestamp('16-AUG-19 18.40.51','DD-MON-YY HH24:MI:SS');

no rows selected

SQL> alter flashback archive f1_archive purge before scn 4794233;

Flashback archive altered.

SQL> alter flashback archive f1_archive purge before timestamp to_timestamp('16-AUG-19 18.40.51','DD-MON-YY HH24:MI:SS');

Flashback archive altered.

SQL> drop flashback archive f1_archive;

Flashback archive dropped.

SQL> desc dba_flashback_archive
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER_NAME VARCHAR2(30)
FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255)
FLASHBACK_ARCHIVE# NOT NULL NUMBER
RETENTION_IN_DAYS NOT NULL NUMBER
CREATE_TIME TIMESTAMP(9)
LAST_PURGE_TIME TIMESTAMP(9)
STATUS VARCHAR2(7)

SQL> select * from dba_flashback_archive;

no rows selected

SQL> desc dba_flashback_archive_ts
Name Null? Type
----------------------------------------- -------- ----------------------------
FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255)
FLASHBACK_ARCHIVE# NOT NULL NUMBER
TABLESPACE_NAME NOT NULL VARCHAR2(30)
QUOTA_IN_MB VARCHAR2(40)

SQL> select * from dba_flashback_archive_ts;

no rows selected

SQL> desc dba_flashback_archive_tables
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(128)
OWNER_NAME NOT NULL VARCHAR2(128)
FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255)
ARCHIVE_TABLE_NAME VARCHAR2(53)
STATUS VARCHAR2(13)

SQL> select * from dba_flashback_archive_tables;

no rows selected

SQL> quit

=====================
Path to Documentation
=====================

Reference
Database Backup and Recovery User's Guide
=> 7 Using Flashback Database and Restore Points
=> 18 Performing Flashback and Database Point-in-Time Recovery
=> 30 Performing User-Managed Database Flashback and Recovery

Database Backup and Recovery User's Guide
18 Performing Flashback and Database Point-in-Time Recovery
=> Performing Database Point-in-Time Recovery
-> Performing Point-in-Time Recovery of PDBs

Application Development Concepts Database Development Guide                                    => 16 Using Oracle Flashback Technology                                                                        => 16.3 Using Oracle Flashback Query (SELECT AS OF)                                                => 16.4 Using Oracle Flashback Version Query                                                                => 16.5 Using Oracle Flashback Transaction Query        

Thank you for visiting this blog…

Advertisements