FLASHBACK

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

ALTER system SET db_flashback_retention_target=720;

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 ;

 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;

SELECT * FROM V$FLASHBACK_DATABASE_STAT;

CREATE restore point RP_20241028_1400 guarantee flashback database ;

FLASHBACK DATABASE TO RESTORE POINT 'before_upgrade';
ou
FLASHBACK DATABASE TO SCN 202381;

DROP RESTORE POINT good_data;

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.
 

• On vérifie le statut (standalone)

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

• On vérifie le statut (RAC)

SELECT DBID,NAME,OPEN_MODE,INST_ID FROM gv$pdbs;
      DBID NAME                                     OPEN_MODE     INST_ID
---------- ---------------------------------------- ---------- ----------
 138649373 PDB$SEED                                 READ ONLY           1
3919713600 PDB1                                    READ WRITE          1
 138649373 PDB$SEED                                 READ ONLY           2
3919713600 PDB1                                    READ WRITE          2