Configure data guard environment to reduce overheads of fast incremental backups on the primary database

9.2. 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. A detailed explanation has been provided in the Chapter 3 regarding the block change tracking feature.

To reduce the overhead of backup on the primary database, a standby database might be used to backup the database.

Switch to the standby database and enable block change tracking as follows:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/home/oracle/block_chg_tracking.trc’;
Database altered.

SQL> COL FILENAME FORMAT a50
SQL> SELECT * FROM v$block_change_tracking;
STATUS FILENAME BYTES
———- ————————————————– ———-
ENABLED /home/oracle/block_chg_tracking.trc 11599872

SQL>

You might be asked to recover the standby database using incremental backup of primary database during exam. To practice this case, disable shipment of archived log files to standby database, perform some log switched and delete archived log files from the primary database.

Then take an incremental backup from the primary database and apply it to the standby database as shown in the following steps.

Reference:
High Availability -> Data Guard Concepts and Administration-> 11 Using RMAN to Back Up and Restore Files -> 11.10 Using RMAN Incremental Backups to Roll Forward a Physical Standby Database

First of all, move to the primary database, disable the archivelog shipment, create archive log gap by switching some log files and deleting archived log files from primary database as follows:

SQL> SHOW PARAMETER log_archive_dest_2
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_2 string service=STBDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBDB

Check the list of archived log files on the primary database:
SQL> ALTER SESSION SET nls_date_format=’HH24:MI:SS’;
Session altered.

SQL> SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TI NEXT_TIM
———- ——– ——–
65 13:00:55 13:05:00
66 13:05:00 13:10:45
66 13:05:00 13:10:45
67 13:10:45 13:10:46
68 13:10:46 13:12:03
SQL>

Now run the same query on the standby database:
SQL> ALTER SESSION SET nls_date_format=’HH24:MI:SS’;
Session altered.

SQL> SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TI NEXT_TIM
———- ——– ——–
63 12:57:32 12:59:27
64 12:59:27 13:00:55
65 13:00:55 13:05:00
66 13:05:00 13:10:45

As you see, 2 archived log files are missing. Now we will delete all archived log files from the primary database, enable the archive log shipping and check alert log file of standby database:

[oracle@ocmnode1 rman_backup]$ cd /home/oracle/arch/
[oracle@ocmnode1 arch]$ rm -rf *.dbf
SQL> ALTER SYSTEM SET log_archive_dest_2=’service=STBDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBDB’;
System altered.
SQL>

[oracle@ocmnode2 trace]$ tail -f alert_STBDB.log
Fetching gap sequence in thread 1, gap sequence 67-68
Tue Jun 21 13:17:56 2016
FAL[client]: Failed to request gap sequence
GAP – thread 1 sequence 67-68
DBID 654484567 branch 914436829
FAL[client]: All defined FAL servers have been attempted.

As you see, we have missing archived log files that are not available at primary database.
We either need to recreate a standby database, or apply an incremental backup of the primary database to the standby database.

To get an incremental backup, we need to get SCN of the standby database. Get the SCN value from both databases:

Primary database
SQL> SELECT TO_CHAR(current_scn) FROM v$database;
TO_CHAR(CURRENT_SCN)
———————–
924379
SQL>

Standby database
SQL> SELECT TO_CHAR(current_scn) FROM v$database;
TO_CHAR(CURRENT_SCN)
—————————————-
923999
SQL>

Switch to primary database now and get an incremental backup starting from the SCN value of the standby database:
RMAN> BACKUP INCREMENTAL FROM SCN 923999 DATABASE FORMAT ‘/home/oracle/rman_backup/incr_backup_gap_%U’;

Take backup of standby control file and move it with along the backup files to the standby host:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/home/oracle/rman_backup/stb_control.ctl’;
Database altered.

SQL>

[oracle@ocmnode1 rman_backup]$ scp /home/oracle/rman_backup/stb_control.ctl ocmnode2:/home/oracle/rman_backup/
[oracle@ocmnode1 rman_backup]$ scp /home/oracle/rman_backup/incr_backup_gap_1* ocmnode2:/home/oracle/rman_backup/
[oracle@ocmnode1 rman_backup]$

Switch to standby, restore controlfile, mount the database and recover it:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT

[oracle@ocmnode2 admin]$ rman target /
RMAN> RESTORE CONTROLFILE FROM ‘/home/oracle/rman_backup/stb_control.ctl’;

[oracle@ocmnode2 admin]$ sqlplus / as sysdba
SQL> STARTUP FORCE NOMOUNT

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL>

RMAN> RECOVER DATABASE NOREDO;

After the recovery completed successfully, start the apply process:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL>

Switch to primary database and switch a few log files:
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL>

Now check alert.log file at standby database to see that new archived log files are shipped and applied successfully:

[oracle@ocmnode2 trace]$ tail -f alert_STBDB.log
Check alert.log file at standby
Tue Jun 21 13:25:35 2016
Archived Log entry 2 added for thread 1 sequence 71 rlc 914436829 ID 0x27028757 dest 2:
RFS[2]: Opened log for thread 1 sequence 72 dbid 654484567 branch 914436829
Tue Jun 21 13:25:37 2016
Media Recovery Log /home/oracle/arch1_71_914436829.dbf
Media Recovery Waiting for thread 1 sequence 72 (in transit)
Archived Log entry 3 added for thread 1 sequence 72 rlc 914436829 ID 0x27028757 dest 2:
RFS[2]: Opened log for thread 1 sequence 73 dbid 654484567 branch 914436829
Tue Jun 21 13:25:47 2016
Media Recovery Log /home/oracle/arch1_72_914436829.dbf
Media Recovery Waiting for thread 1 sequence 73 (in transit)

▼Practice:
► 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.

Practice Time
►15 min.
If you want to get more information on creating, configuring and managing Data Guard get my book from the following link:
https://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/ 

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>