9-2 Data Guard - Configure the data guard environment to reduce overheads of fast incremental backups on the primary database

To increase speed of incremental backup of standby database, block change tracking should be enabled.

/* on node2 */
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/home/oracle/block_chg_tracking.trc';

SELECT * FROM v$block_change_tracking;

 

 

 

 

Q1. Stop the archive log apply process and remove archived log files from the standby database that were not applied. Create a table on the primary database, take an incremental backup and recover it on standby database. Query the table on the standby database. (15min)

 

1. check log gap between primary database and standby database.

/* on node1 */
SELECT     *
FROM       (SELECT sequence#, first_time, next_time 
            FROM   v$archived_log 
            ORDER BY sequence# desc)
WHERE      rownum <= 5;


/* on node2 */
SELECT     *
FROM       (SELECT sequence#, first_time, next_time 
            FROM   v$archived_log 
            ORDER BY sequence# desc)
WHERE      rownum <= 5;

 

 

 

2. remove archived log files from the standby database that were not applied, create table and check current_scn

/* on node1 */
SELECT     *
FROM       (SELECT sequence#, first_time, next_time, name
            FROM   v$archived_log 
            ORDER BY sequence# desc)
WHERE      rownum <= 5;

create table ryutable (id number);
insert into ryutable values (4);

SELECT current_scn FROM v$database;

cd /u01/app/oracle/fra/ORADB/archivelog/
rm -rf *


/* on node2 */
SELECT current_scn FROM v$database;

 

 

3. get an incremental backup starting from the SCN value of the standby database

/* on node1 */
BACKUP INCREMENTAL FROM SCN 2891658 DATABASE FORMAT '/home/oracle/incr_backup_gap_%U';

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/stb_control.ctl';


/* bash */
scp /home/oracle/incr* srv2:/home/oracle/
scp /home/oracle/stb* srv2:/home/oracle/

 

 

 

4. on node2, restore controlfile, mount the database and recover it.

/* on node2 */
SHUT;
STARTUP NOMOUNT;

rman target /
RESTORE CONTROLFILE FROM '/home/oracle/stb_control.ctl';

sqlplus / as sysdba
STARTUP FORCE NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;

RECOVER DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

/* on node1 */
ALTER SYSTEM SWITCH LOGFILE;

sqlplus / as sysdba
SELECT * FROM ryutable;

 

 

 

 

 

References

Oracld Docs 19c, Data Guard concepts and administration

Oracld Docs 19c, Database backup and recovery users guide

ORA-10456: cannot open standby database; media recovery session may be in progress