Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will be discussing what the Flashback Activation Procedure is in Oracle database.
1. MWRPR replicas must stop. Otherwise, REPLICAT PROCESS will receive an error when the database is closed.
1 2 3 4 5 | [root@mwrpr1 ~]# su - goldengate $ /goldengate/ggs/ggsci > stop * |
2. In order to use the FLASHBACK feature in the database, the database must be ARCHIVE LOG MODE.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | SQL> select log_mode from v$database; LOG_MODE ------------ NOARCHIVELOG [oracle@mwrpr1 ~]$ srvctl stop database -d mwrpr [NODE1] [oracle@mwrpr1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 20 12:59:15 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 5344731136 bytes Fixed Size 2262656 bytes Variable Size 4848617856 bytes Database Buffers 486539264 bytes Redo Buffers 7311360 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG [NODE2] SQL> startup; ORACLE instance started. Total System Global Area 6898044928 bytes Fixed Size 2266384 bytes Variable Size 1291848432 bytes Database Buffers 5586812928 bytes Redo Buffers 17117184 bytes Database mounted. Database opened. |
3. Now that the database is in archive mode, we can start MWRPR REPLICAT.
1 2 3 4 5 | [root@mwrpr1 ~]# su - goldengate $ /goldengate/ggs/ggsci > start * |
4. Before enabling the FLASHBACK feature, we need to check and set the following parameters in the database.
[NODE1]a. How long will FLASHBACK logs be stored?
1 2 3 4 5 | SQL> show parameter db_flashback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 |
The above value is 1440 minutes (24 hours). We can adjust this value as we wish.
1 2 3 4 5 6 7 8 9 10 | SQL> alter system set db_flashback_retention_target=60 scope=both; System altered. SQL> show parameter db_flashback_retention_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 60 |
b. The size of the archive area where FLASHBACK logs will be stored should be checked.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> show parameter db_recovery_file_dest_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 130G SQL> select name, total_mb, free_mb from v$asm_diskgroup; NAME TOTAL_MB FREE_MB ------------------------------ ---------- ---------- DATA 255996 55996 FRA 153597 124478 |
c. The location of the archive area where FLASHBACK logs will be stored should be checked.
1 2 3 4 5 | SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA |
d. UNDO TABLESPACE has to be GUARANTEE.
1 2 3 4 5 6 | SQL> select tablespace_name, retention from dba_tablespaces where tablespace_name like 'UNDO%'; TABLESPACE_NAME RETENTION ------------------------------ ----------- UNDOTBS1 GUARANTEE UNDOTBS2 GUARANTEE |
If UNDO TBS is not GUARANTEE, it can be set as follows.
1 2 3 | SQL> alter tablespace UNDOTBS1 retention guarantee; Tablespace altered. |
e. UNDO TABLESPACE must be AUTOEXTENSIBLE.
1 2 3 4 5 6 | SQL> select TABLESPACE_NAME, AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'UNDO%'; TABLESPACE_NAME AUT ------------------------------ --- UNDOTBS1 YES UNDOTBS2 YES |
f. UNDO parameters should be checked. Necessary changes should be made.
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> alter system set undo_retention = 1800 scope=both; System altered. |
g. UNDO TABLESPACE dimensions should be checked. If there are UNDO TBS smaller than the estimated size, their size should be increased.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> select tablespace_name, BYTES/1024/1024 MB from dba_data_files where tablespace_name like 'UNDO%'; TABLESPACE_NAME MB ------------------------------ ---------- UNDOTBS1 1205 UNDOTBS2 1175 SQL> alter tablespace UNDOTBS1 add datafile '+DATA' size 20G autoextend on next 1G maxsize unlimited; Tablespace altered. SQL> alter tablespace UNDOTBS2 add datafile '+DATA' size 20G autoextend on next 1G maxsize unlimited; Tablespace altered. SQL> select name, total_mb, free_mb from v$asm_diskgroup; NAME TOTAL_MB FREE_MB ------------------------------ ---------- ---------- DATA 255996 15032 FRA 153597 124478 |
h. In order to use the FLASHBACK QUERY feature, the database must be in SUPPLEMENTAL LOGGING mode. (for Flashback Query)
1 2 3 4 5 | SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_FK from v$database; SUPPLEME SUP SUP -------- --- --- YES YES YES |
If it is closed, it can be activated as follows.
1 2 3 4 5 6 7 8 9 10 11 | SQL> alter database add supplemental log data; Database altered. SQL> alter database add supplemental log data (primary key) columns; Database altered. SQL> alter database add supplemental log data (foreign key) columns ; Database altered. |
i. In order to use the FLASHBACK TABLE feature, the ROW MOVEMENT value of the table to be flashed must be ENABLE. This parameter can also be updated in case of a problem. (For Flashback Table)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SQL> select table_name, row_movement from dba_tables where table_name like 'ACCOUNT%'; TABLE_NAME ROW_MOVE ------------------------------ -------- ACCOUNT DISABLED SQL> alter table WEBTELMWCORE.ACCOUNT enable row movement; Table altered. SQL> select table_name, row_movement from dba_tables where table_name like 'ACCOUNT%'; TABLE_NAME ROW_MOVE ------------------------------ -------- ACCOUNT ENABLED |
j. The RECYCLEBIN folder must be active so that we can retrieve the tables we sent to the trash bin with the DROP TABLE command. (Flashback Drop)
1 2 3 4 5 | SQL> show parameter recyclebin NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on |
5. Now that all preparations are completed, Database FLASHBACK can be activated.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES |
