step by step enable archivelog and flashback in Oracle 19c RAC Database

 

Step:-1 Before Changing the archivelog mode  and Flashback Database check the status. 

SQL> select log_mode,flashback_on from gv$database;

LOG_MODE     FLASHBACK_ON

------------ ------------------

NOARCHIVELOG NO

NOARCHIVELOG NO

Step:-2 Stop the rac database service.

[oracle@bamisdbrac02 ~]$ srvctl status database -d misdbrac

Instance misdbrac1 is running on node bamisdbrac01

Instance misdbrac2 is running on node bamisdbrac02

[oracle@bamisdbrac02 ~]$ 

[oracle@bamisdbrac02 ~]$ srvctl stop database -d misdbrac

[oracle@bamisdbrac02 ~]$

Step:-3 Start the rac database in mount state.

[oracle@bamisdbrac02 ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 14 19:02:36 2021

Version 19.12.0.0.0

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

Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 6.4425E+10 bytes

Fixed Size    37476752 bytes

Variable Size 1.0872E+10 bytes

Database Buffers 5.3419E+10 bytes

Redo Buffers    96739328 bytes

Database mounted.

SQL> 

[oracle@bamisdbrac01 ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 14 19:02:04 2021

Version 19.12.0.0.0

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

Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 6.4425E+10 bytes

Fixed Size    37476752 bytes

Variable Size 1.0335E+10 bytes

Database Buffers 5.3956E+10 bytes

Redo Buffers    96739328 bytes

Database mounted.

SQL> 

Step:-4 Enable archive log mode and Flashback database

[oracle@bamisdbrac02 ~]$ srvctl status database -d misdbrac

Instance misdbrac1 is running on node bamisdbrac01

Instance misdbrac2 is running on node bamisdbrac02

[oracle@bamisdbrac02 ~]$ 

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

------------------

NO

SQL> archive log list

Database log mode        No Archive Mode

Automatic archival        Disabled

Archive destination        /u01/app/oracle/product/19.3/db_1/dbs/arch

Oldest online log sequence     1772

Current log sequence        1773

SQL> select log_mode,flashback_on from gv$database;

LOG_MODE     FLASHBACK_ON

------------ ------------------

NOARCHIVELOG NO

NOARCHIVELOG NO

SQL> 

alter system set log_archive_dest_1='LOCATION=+FRA/' scope=both sid='*';

alter database archivelog;

alter system set db_recovery_file_dest_size=500G scope=both sid='*';

alter system set db_recovery_file_dest='+FRA' scope=both sid='*';

alter database flashback on;

Step:-5 Stop the RAC database service

[oracle@bamisdbrac02 ~]$ srvctl stop database -d misdbrac

Step:-6 Restart the RAC database

[oracle@bamisdbrac02 ~]$ srvctl start database -d misdbrac

[oracle@bamisdbrac02 ~]$ srvctl status database -d misdbrac

Instance misdbrac1 is running on node bamisdbrac01

Instance misdbrac2 is running on node bamisdbrac02

[oracle@bamisdbrac02 ~]$ 

Step:-7 Check Archivelog and flashback Status 

SQL> select log_mode,flashback_on from gv$database;

LOG_MODE     FLASHBACK_ON

------------ ------------------

ARCHIVELOG   YES

ARCHIVELOG   YES

SQL> 

[oracle@bamisdbrac02 ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 14 19:11:38 2021

Version 19.12.0.0.0

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

Enter user-name: /as sysdba

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.12.0.0.0

SQL> select log_mode,flashback_on from gv$database;

LOG_MODE     FLASHBACK_ON

------------ ------------------

ARCHIVELOG   YES

ARCHIVELOG   YES

SQL> 

Step by step enable Archive log and Flashback in Oracle 12c R2 RAC Database

---Check Archive log is enabled or not--------

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /d01/app/oracle/product/12.2.0.1/db_1/dbs/arch
Oldest online log sequence     16
Current log sequence           17
SQL>

------Check Database Configuration------------

-bash-4.4$ srvctl config database -d cdbpr
Database unique name: cdbpr
Database name: cdbpr
Oracle home: /d01/app/oracle/product/12.2.0.1/db_1
Oracle user: oracle
Spfile: +CDATA/CDBPR/PARAMETERFILE/spfile.269.1037317075
Password file: +CDATA/CDBPR/PASSWORD/pwdcdbpr.260.1037316549
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: CDATA,ADATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: cdbpr1,cdbpr2
Configured nodes: badc-cdbpr01,badc-cdbpr02
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
-bash-4.4$

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>

----Change Flashback retention period--------

SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440     -----------> (Minutes)
SQL>

SQL> alter system set db_flashback_retention_target=2880 scope=both sid='*';

System altered.

SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     2880      -------------> (Minutes ---48 Hours)
SQL>

----Set db_recovery_file_dest_size and db_recovery_file_dest system parameter for total size limit for flashback recovery.

SQL> alter system set db_recovery_file_dest_size=900G scope=both sid='*';

System altered.

SQL> alter system set db_recovery_file_dest='+FRA' scope=both sid='*';

System altered.

SQL>
SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 900G
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>

-----Shutdown Database for both instance---

-bash-4.4$ srvctl stop database -d cdbpr
-bash-4.4$


-bash-4.4$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 24 14:40:08 2020

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5.3687E+11 bytes
Fixed Size                 30051944 bytes
Variable Size            5.0251E+11 bytes
Database Buffers         3.3286E+10 bytes
Redo Buffers             1043685376 bytes
Database mounted.
SQL>

-------------Enable Archive log first then enable Flashback--------

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.


SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL>

SQL> select flashback_on  from v$database;

FLASHBACK_ON
------------------
YES

SQL>

SQL> alter database open;

Database altered.

SQL>

-------Shutdown Instance------

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

-------Start Database with both instance simultaneously-------

-bash-4.4$ srvctl start database -d cdbpr
-bash-4.4$

----Check Archive Log and Flashback enable from both instance -------


SQL> select INST_ID,FLASHBACK_ON,NAME from GV$DATABASE;

   INST_ID FLASHBACK_ON       NAME
---------- ------------------ ---------
         1 YES                CDBPR
         2 YES                CDBPR

SQL>