Category: FLASHBACK

How To Enable Flash Recovery Area In 18c Oracle Database

1)Check and confirm Flash Recovery Area is not enabled.

SQL> show parameter db_recovery_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

SQL> select * from V$RECOVERY_FILE_DEST;
no rows selected
SQL>

2) Enable Flash Recovery Area.

SQL> alter system set db_recovery_file_dest_size=10g scope=both;
System altered.

SQL>alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope=both;
System altered.

SQL>  show parameter db_recovery_file
NAME                         TYPE        VALUE
---------------------------- ----------- ----------------------
db_recovery_file_dest        string      /u01/app/oracle/fra
db_recovery_file_dest_size   big integer 10G

SQL>  select * from V$RECOVERY_FILE_DEST;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
---- ----------- ---------- ----------------- --------------- -------
/u01/app/oracle/fra 1.0737E+10       0       0         0       0

3) Configure  archive log destination to use flash recovery area.

SQL>alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
SQL> select * from v$flash_recovery_area_usage;

FILE_TY PENT_SPACE_USED  PCENT_SPACE_REBLE NUMBER_OF_FILES    CON_ID
------------------------ ------------------ ---------------  ---------
CONTROL FILE     0                   0               0          0
REDO LOG         0                   0               0          0
ARCHIVED LOG     .59                 0               1          0
BACKUP PIECE     0                   0               0          0
IMAGE COPY       0                   0               0          0
FLASHBACK LOG    0                   0               0          0
FOREIGN ARCHIVED LOG  0              0               0          0
AUXILIARY DATAFILE COPY  0           0               0          0
8 rows selected.

Advertisement

Flashback RAC Database to Guarantee Restore Point ( GRP )

It is a good practice to create a guarantee restore point ( GRP ), before upgrading or patching databases.

Here is an example of how to create a guarantee  restore point ( GRP ), and how to flashback the RAC database to this guarantee  restore point ( GRP ).

1)Check and make sure database is running at archivelog mode.

SQL> set pagesize 120
SQL> set linesize 180
SQL> select LOG_MODE,FORCE_LOGGING,CURRENT_SCN,FLASHBACK_ON 
      from v$database;

LOG_MODE   FORCE_LOGGING CURRENT_SCN FLASHBACK_ON
---------- ------------- ----------- ------------
ARCHIVELOG YES           6622166     NO

2) Create the guarantee restore point.

SQL> select * from v$restore_point;
no rows selected

SQL> create table before_patching ( id number, name varchar2(30));
Table created.

SQL> create restore point BEFORE_PATCHING guarantee flashback database;
Restore point created.

SQL> create table after_GRP ( id number, name varchar2(30));
Table created.

SQL> select * from v$restore_point;

SCN       DT GUA STORAGE_SIZE TIME      REST NAME           CON_ID
-------- --- --- ------------ --------- ---- -------------- -------
 6623316 1   YES 4294967296   29-NOV-16 YES BEFORE_PATCHING 0

3) Applying the patches unsuccessfully.

 The rollback is required to flashback the database to guarantee restore point ( GRP ).

4) Shutdown RAC database,  and then start up current instance only in mount mode.

$ srvctl stop database -d RACTEST
$ srvctl start instance -d RACTEST -i RACTEST1 -o mount;

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> flashback database to restore point BEFORE_PATCHING;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select LOG_MODE,FORCE_LOGGING,CURRENT_SCN,FLASHBACK_ON 
       from v$database;

LOG_MODE   FORCE_LOGGING CURRENT_SCN  FLASHBACK_ON
---------- ------------  ----------  -----------------
ARCHIVELOG YES           6625602     RESTORE POINT ONLY

SQL> select count(*) from before_patching;

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

SQL> select count(*) from after_GRP;  <---DB was flasged back to the correct point.
 select count(*) from after_GRP
 *
ERROR at line 1:
ORA-00942: table or view does not exist

5) Start up all the instances.

$ srvctl stop database -d RACTEST
$ srvctl start database -d RACTEST
$ srvctl status database -d RACTEST
Instance RACTEST1 is running on node racnode1
Instance RACTEST2 is running on node racnode2
Instance RACTEST3 is running on node racnode3
Instance RACTEST4 is running on node racnode4

6) Drop the guarantee  restore point ( GRP ).

SQL> drop restore point BEFORE_PATCHING;

Restore point dropped.

SQL> select * from v$restore_point;

no rows selected