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

Post a Comment

Popular posts from this blog

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

Image
 During connection from SQL Developer to EBS database got below error message: Status : Failure - Test failed: IO Error: Got minus one from a read call Following solution can be done : 1. Go to directory $ORACLE_HOME / network / admin 2. Modify sqlnet.ora file with following parameter : tcp.validnode_checking = no 3. If you don 't want to disable this, you can put the machine names as follows: tcp.invited_nodes=(machine1, machine2) 3. Bounce the listener. Due to security enhancements it is not good to put tcp.validnode_checking = no or comment out will through error when we start listener like below: Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shsebs7idb1.appsolworld.com)(PORT=1522))) TNS-00584: Valid node checking configuration error TNS-12560: TNS:protocol adapter error Better plan is to add localhost or IP address of client (Local Computer) in the sqlnet.ora file That will solve the problem NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME) SQLNET.EXPI
Read more

How to troubleshoot Long Running Concurrent Request in EBS 12.2

 The following points need to answer before jumping into troubleshooting: Are there any recent code changes done in the concurrent program?           Check long_running_sid and verify the recent code change of last_ddl_timestamp after a             discussion with the developer. Was this running long in the last few run as well, or was this time only?            ==> Check the program history. How much time does it take to complete?           Once again program history can give us some idea. Are these jobs fetching higher data compared to the last run           ===> Confirm on data change . Query Plan change will indicate this issue. Does this job runs at any specific time or it can be run anytime           ===> The nature of the job can help to understand the job's nature. Does this job fetch data using DB Link          ====> Check whether the DB link is involved or not. The below-given approach would help to conclude the gray areas. Check the load of the server using t
Read more

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

  Issue:  EBS Local Login url is working fine. When accessing sso url, users are getting error as below appended to the url. And login page will be blank. One of the reason could be : OAM Authentication is fine but during authorization process, OAM is unable to map guid from oid to user_guid in fnd_user. This can be due to user_guid being null in fnd_user table. As per the process, when user logins for the first time, user_guid need to be populated automatically (from oid) Query guid from oid and update fnd_user table with that. ldapsearch -h oidhost.appsolworld.com -p 3060 -D "cn=orcladmin" -w orcladmpwd -b "cn=Users,dc=dc,dc=online,dc=org" -s sub "(uid=$1)" orclsamaccountname krbprincipalname mail orcluserprincipalname orclguid Pass userid to this command which gives guid along with other details. Update fnd_user table with above guid : update fnd_user  set user_guid=' ' where user_name like ' '; Solution:  As per the process,  when user
Read more