How to enable Flashback Database In Oracle 19c RAC Database

 Flashback Database In Oracle 19c RAC Database

1st Step: Check the status. 

    SQL> select log_mode,flashback_on from gv$database;

            LOG_MODE FLASHBACK_ON
            ———— ——————
            NOARCHIVELOG NO
            NOARCHIVELOG NO

2nd Step: Stop the database service.

        [oratansid@shsdbdr01 ~]$ srvctl stop database -d tansid

3rd Step: Start the database in mount state.

        [oratansid@shsdbdr01 ~]$ srvctl status database -d tansid
                    Instance tansid1 is running on node shsdbdr01
                    Instance atnsid2 is running on node shsdbdr02

4th Step: Enable archive log and Flashback database

[oratansid@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production 

Version 19.14.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.14.0.0.0

Before Enable flashback first we need to enable archivelog dest and archivelog 


SQL> alter system set log_archive_dest_1=’LOCATION=+ARCH/’ scope=both sid=’*’;

System altered.

SQL> alter database archivelog;

Database altered.

SQL> alter system set db_recovery_file_dest_size=155G scope=both sid=’*’;

System altered.

SQL> alter system set db_recovery_file_dest=’+ARCH’ scope=both sid=’*’;

System altered.

SQL> alter database flashback on;

Database altered.

5th Step: Stop the database service.

        [oratansid@shsdbdr01 ~]$ srvctl stop database -d tansid

6th Step: Start the database

        [oratansid@shsdbdr01 ~]$ srvctl status database -d tansid
                    Instance tansid1 is running on node shsdbdr01
                    Instance atnsid2 is running on node shsdbdr02

7th Step:- Check flashback Status 

            SQL> select log_mode,flashback_on from gv$database;

            LOG_MODE FLASHBACK_ON
            ———— ——————
            NOARCHIVELOG YES
            NOARCHIVELOG YES 


Some important commands for the Flashback

FLASHBACK TABLE flashback_table_test TO SCN SCN_****
FLASHBACK TABLE flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2023-02-23 12:00:00', 'YYYY-MM-DD HH:MI:SS');

Flashback Drop


FLASHBACK TABLE flashback_drop_tbl TO BEFORE DROP RENAME TO flashback_drop_tbl_old;

PURGE TABLE tablename;                   -- Specific table.
PURGE INDEX indexname;                   -- Specific index.
PURGE TABLESPACE ts_name;                -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username;  -- All tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN;                        -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN;                    -- The whole recycle bin.

This feature can be disabled/enabled at the session or system level.


ALTER SESSION SET recyclebin = OFF;
ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = ON;

Flashback Database

-- Flashback 5 minutes.
CONN / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;

Some options flashback database command include.

FLASHBACK DATABASE TO TIMESTAMP my_table;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_table;
FLASHBACK DATABASE TO SCN table_scn;
FLASHBACK DATABASE TO BEFORE SCN table_scn;

CREATE RESTORE POINT before_changes;
CREATE RESTORE POINT before_changes GUARANTEE FLASHBACK DATABASE;

Existing restore points can be displayed querying the V$RESTORE_POINT view


FLASHBACK DATABASE TO RESTORE POINT before_changes;

Assume that you inserted corrupted rows in many tables at 5:00 p.m. on February 14. 
You connect SQL*Plus to the database and query the earliest SCN in the flashback window:

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
  2  FROM   V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK
-------------------- ----------------
              411010 2013/02/14 16:49


Open a new terminal, start the RMAN client, and connect to 
the target database and recovery catalog.  Enter RMAN commands as follows 
(sample output for the FLASHBACK DATABASE is included):

RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT
RMAN> FLASHBACK DATABASE TO SCN 411010;
 
Starting flashback at 15-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=104 device type=DISK
 
 
starting media recovery
media recovery complete, elapsed time: 00:00:07
 
Finished flashback at 15-FEB-13
 
RMAN> ALTER DATABASE OPEN RESETLOGS;

 FLASHBACK DATABASE to a Restore Point

Assume that we are preparing to load a massive number of updates to the database. 
You create a guaranteed restore point before the performing the updates:

SQL> CREATE RESTORE POINT before_update GUARANTEE FLASHBACK DATABASE;

The bulk update fails, leaving the database with extensive corrupted data. We start an RMAN session, 
connect to the target database and recovery catalog, 
and list the guaranteed restore points:

RMAN> LIST RESTORE POINT ALL;

SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
412742                     GUARANTEED 15-FEB-13 BEFORE_UPDATE

We mount the database, flash back the database to the restore point (sample output included), 
and then open the database with the RESETLOGS option:

RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE';
 
Starting flashback at 15-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=104 device type=DISK
 
 
starting media recovery
 
archived log for thread 1 with sequence 34 is already on disk as file /disk2/oracle/oradata/prod/arch/archive1_34_614598462.dbf
media recovery complete, elapsed time: 00:00:01
Finished flashback at 15-FEB-13
 
RMAN> ALTER DATABASE OPEN RESETLOGS;

FLASHBACK DATABASE for a PDB to a Guaranteed PDB Restore Point

Assume that we need to upgrade an application that performs DML operations on the tables in the PDB hr_pdb. Before you perform the application upgrade, we create a guaranteed PDB restore point in hr_pdb when connected to the PDB (the PDB is mounted):

SQL> CREATE RESTORE POINT hr_pdb_grp_before_upgrade GUARENTEE FLASHBACK DATABASE;

The application upgrade fails leaving the PDB with corrupted data. You want to rewind the 
PDB to its state before the upgrade failure. You start SQL*Plus, connect to the CDB as a common user with the SYSDBA or SYSBACKUP privilege, 
and then run the following command to view all the restore points:

SQL> SELECT name, guarantee_flashback_database, pdb_restore_point, con_id 
     FROM v$restore_point;

NAME                          GUARANTEE_FLASHBACK_DATABASE PDB_RESTORE_POINT  CON_ID
----------------------------- ---------------------------  -----------------  ------
CDB_GRP_BEFORE_PATCH                 YES                    NO              0
HR_PDB_GRP_BEFORE_UPGRADE            YES                    YES             1

The output indicates that the restore point HR_PDB_GRP_BEFORE_UPGRADE is a guaranteed PDB restore point. We can reverse the effects of data corruption by rewinding hr_pdb to this guaranteed PDB restore point. To perform a flashback operation for hr_pdb, this PDB must be closed. All other PDBs in the CDB can  remain open and operational.

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> FLASHBACK PLUGGABLE DATABASE hr_pdb TO RESTORE POINT hr_pdb_grp_before_upgrade 
      AUXILIARY DESTINATION '/temp/aux_dest';
RMAN> ALTER PLUGGABLE DATABASE hr_pdb OPEN RESETLOGS;

We place the CDB in mount mode, flash back the PDB to the guaranteed PDB restore point, and then 
open the PDB with resetlogs. In this example, the CDB uses shared undo and, therefore, an auxiliary instance is used to store temporary files during the flashback operation

Comments

Popular posts from this blog

How to fix Oracle SQL Developer connection issue "Got minus one from a read call"

Few Important steps of Oracle Database Clone

How to autopopulate user_guid in fnd_user table ( EBS Blank Page)