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.
------------------------- 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…
ORACLE-LEARN 