meta données pour cette page
  •  

Ceci est une ancienne révision du document !

FLASHBACK

Prérequis
  • Il faut que le Flashback soit activé
  • la rétention par défaut est de 1440min soit 24h (db_flashback_retention_target)


Modifier la durée de rétentention Flashback (ici 1/2 journée)
ALTER system SET db_flashback_retention_target=720;


Lister les restore point
SET lines 600
col NAME format a40
SELECT NAME, TO_CHAR(SCN) SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE/1024/1024/1024 GB 
FROM V$RESTORE_POINT ;


Lister les fichiers flashback log
 SELECT LOG# AS "Log No", thread# AS "Thread No", sequence# AS "Seq No", name, bytes/1024/1024 AS "Size(MB)", first_change# AS "First Chg No", first_time FROM v$flashback_database_logfile ORDER BY first_time;


Lister l'activité flashback
SELECT * FROM V$FLASHBACK_DATABASE_STAT;
Faire un Flashback
FLASHBACK DATABASE TO RESTORE POINT 'before_upgrade';
ou
FLASHBACK DATABASE TO SCN 202381;


supprimer un restore point
DROP RESTORE POINT good_data;
  • restore a restore point
SQL> ALTER session SET container=PDB1;
 
SQL> SELECT rp.name, rp.guarantee_flashback_database, rp.pdb_restore_point, pdb.name pdb_name, rp.SCN, rp.TIME FROM v$restore_point rp, v$pdbs pdb WHERE pdb.con_id (+)= rp.con_id ORDER BY TIME;
 
NAME                                     GUA PDB PDB_NAME                                        SCN TIME
---------------------------------------- --- --- ---------------------------------------- ---------- --------------------------------
PDB1_20241010_1403_PIT                YES YES PDB1                                  8521276782 10-OCT-24 02.05.19.000000000 PM
PDB1_20241010_1425_PIT                YES YES PDB1                                  8523905796 10-OCT-24 02.27.08.000000000 PM
 
 
SQL> ALTER session SET container=cdb$root;
 
Session altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 T24REF                         READ WRITE NO
         5 PDB1                        READ WRITE NO
        12 PDB1_PREV                   READ WRITE YES
        13 T24PDB                         READ WRITE NO
 
SQL> ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE INSTANCES=ALL;
 
Pluggable database altered.
 
SQL> FLASHBACK PLUGGABLE DATABASE PDB1 TO RESTORE POINT PDB1_20241010_1403_PIT;
 
Flashback complete.
 
SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN RESETLOGS;
 
Pluggable database altered.
 
SQL> ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE INSTANCES=ALL;
 
Pluggable database altered.
 
SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN INSTANCES=ALL;
 
Pluggable database altered.
 
 
SELECT GUID, name, open_mode, CREATE_SCN, OPEN_TIME, CREATION_TIME, restricted FROM v$pdbs ORDER BY creation_time, CON_ID;
SQL> SQL>
GUID     NAME      OPEN_MODE        CREATE_SCN        OPEN_TIME                           CREATION_ 	RES
--------------------------------------------------------------------------------------------------------------------------
...   	PDB$SEED   READ ONLY     	1473503                 24-SEP-24 07.18.15.552 AM +02:00    08-JAN-24 	NO
... 	PDB1       READ WRITE     	8497094687           1-OCT-24 12.53.19.568 PM +02:00    10-OCT-24 	NO

</code>