notes/tiddlywiki/Dataguard - sync using incremental backup.md

Dataguard configuration

DGMGRL> show configuration

Configuration - asty

  Protection Mode: MaxPerformance
  Members:
  astyprd - Primary database
    astydrp - Physical standby database 


DGMGRL> show database 'astydrp'

Database - astydrp

  Role:                PHYSICAL STANDBY
  Intended State:      APPLY-ON
  Transport Lag:       0 seconds (computed 1 second ago)
  Apply Lag:           0 seconds (computed 1 second ago)
  Average Apply Rate:  803.00 KByte/s
  Real Time Query:     OFF
  Instance(s):
    ASTYDRP

Simulate a gap

Stop the standby database.

On primary, switch 3-4 times the archivelog on primary and delete all archived logs:

SQL> alter system archive log current;
RMAN> delete noprompt force archivelog all;

To complicate the situation, add 2 new datafile and create a new tablespace on primary.

SQL> alter tablespace SYSTEM add datafile size 10M autoextend OFF;
SQL> alter tablespace SYSAUX add datafile size 10M autoextend OFF;
SQL> create tablespace NAL_HUTTA datafile size 10M autoextend ON next 10M;

Repeat switch/delete archivelog operation on primary:

SQL> alter system archive log current;
RMAN> delete noprompt force archivelog all;

Start the standby database in MOUNT mode, let it trying to resolve the gap and check the status of the syncronisation.
On primary:

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set lines 200

select THREAD#, max(SEQUENCE#), max(FIRST_TIME),max(NEXT_TIME),max(COMPLETION_TIME) 
from gv$archived_log group by THREAD#;

On standby:

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set lines 200

select THREAD#, max(SEQUENCE#), max(FIRST_TIME),max(NEXT_TIME),max(COMPLETION_TIME) 
from gv$archived_log
where APPLIED='YES' group by THREAD#;

Syncronize the standby

Cancel MRP on standby:

DGMGRL> edit database 'astydrp' set STATE='LOG-APPLY-OFF';

Try to recover the standby and note down the required SCN:

SQL> recover standby database;

Normaly it should be the same as:

SQL> select 1+CURRENT_SCN from v$database;

On primary, identify all datafiles created after this SCN; in my example SCN=5681090

SQL> select FILE#,NAME from v$datafile where CREATION_CHANGE# >= 5681090;

Backup datafiles and generate a new standby controlfile:

run{
  set nocfau;
  allocate channel ch01 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/temp/%d_%U_%s_%t.bck';
  allocate channel ch02 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/temp/%d_%U_%s_%t.bck';
  allocate channel ch03 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/temp/%d_%U_%s_%t.bck';
  allocate channel ch04 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/temp/%d_%U_%s_%t.bck';
  backup as compressed backupset datafile 17,18,19;
  release channel ch01;
  release channel ch02;
  release channel ch03;
  release channel ch04;
  allocate channel ch01 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/temp/%d_%U_%s_%t.ctl';
  backup current controlfile;
  release channel ch01;
}

Restart the standby in mode NOMOUNT and restore the standby controfile:

RMAN> restore standby controlfile from '/mnt/yavin4/tmp/_oracle_/orabackup/temp/ASTY_0l1678fs_21_1_1_21_1113825788.ctl';

Alternatively, you can restore the standby controfile from active database:

RMAN> restore standby controlfile from service ASTYPRD_DGMGRL;

Mount the standby database:

RMAN> alter database mount;

Restore and new datafiles:

RMAN> restore datafile 17,18,19;

Catalog recovery area and old standby datafiles:

RMAN> catalog start with '/data/ASTYDRP' noprompt;
RMAN> catalog start with '/fra/ASTYDRP' noprompt;

At this moment, because of fresh restored controlfile, Oracle see the datafiles as datafile copy:

RMAN> list datafilecopy all;

Switch database to copy:

RMAN> switch database to copy;

To recover standby using from SCN backupset, we can proceed from active database or using physical backupset:

rman auxiliary /
run {
  allocate channel pri1 device type DISK;
  allocate channel pri2 device type DISK;
  allocate channel pri3 device type DISK;
  allocate channel pri4 device type DISK;
  recover database from service ASTYPRD_DGMGRL using compressed backupset section size 8G;
}

Clear standby redolog:

SQL> select 'ALTER DATABASE CLEAR LOGFILE GROUP '||GROUP#||';' from v$standby_log;

Enable MRP:

DGMGRL> edit database 'astydrp' set STATE='ONLINE';