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 |
