Ashiwini
19 May, 2024
0 Comments
8 Mins Read

Refresh Standby Database using RMAN Incremental SCN Backup

Action Plan

1) Verify Gap

On Primary

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      READ WRITE           PRIMARY

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
    425

[oracle@node1 DELL]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> show configuration;
Configuration - DELL
Protection Mode: MaxPerformance
Databases:
DELL    - Primary database
Error: ORA-16724: cannot resolve gap for one or more standby databases
DELL_DG - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL>

On Standby

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      MOUNTED              PHYSICAL STANDBY

SQL>
SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
     367

2) Stop Redo Transfer (on Primary)

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE

SQL>

SQL> alter system set log_archive_dest_state_2='DEFER' scope=both;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER <----

SQL>

3) Find current SCN from standby

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      MOUNTED              PHYSICAL STANDBY

SQL>
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
   1430085

4) Take RMAN Incremental from SCN (on primary)

 [oracle@node1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 24 04:17:33 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DELL (DBID=3965088591)
RMAN>
RMAN> run
2> {
3> allocate channel t1 type disk;
4> allocate channel t2 type disk;
5> allocate channel t3 type disk;
6> backup incremental from SCN 1430085 database tag='STANDBY_TODAY_REFRESH' format '/u02/oracle/backup/DELL_BACKUP/database_%d_%u_%s';
7> release channel t1;
8> release channel t2;
9> release channel t3;
10> }

using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=52 device type=DISK

allocated channel: t2
channel t2: SID=46 device type=DISK

allocated channel: t3
channel t3: SID=42 device type=DISK

Starting backup at 24-JAN-19
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oracle/oradata/DELL/system01.dbf
input datafile file number=00004 name=/u02/oracle/oradata/DELL/users01.dbf
channel t1: starting piece 1 at 24-JAN-19
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u02/oracle/oradata/DELL/sysaux01.dbf
input datafile file number=00003 name=/u02/oracle/oradata/DELL/undotbs01.dbf
channel t2: starting piece 1 at 24-JAN-19
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
including current control file in backup set
channel t3: starting piece 1 at 24-JAN-19
channel t3: finished piece 1 at 24-JAN-19
piece handle=/u02/oracle/backup/DELL_BACKUP/database_DELL_0vto3o79_31 tag=STANDBY_TODAY_REFRESH comment=NONE
channel t3: backup set complete, elapsed time: 00:00:01
channel t1: finished piece 1 at 24-JAN-19
piece handle=/u02/oracle/backup/DELL_BACKUP/database_DELL_0tto3o79_29 tag=STANDBY_TODAY_REFRESH comment=NONE
channel t1: backup set complete, elapsed time: 00:00:08
channel t2: finished piece 1 at 24-JAN-19
piece handle=/u02/oracle/backup/DELL_BACKUP/database_DELL_0uto3o79_30 tag=STANDBY_TODAY_REFRESH comment=NONE
channel t2: backup set complete, elapsed time: 00:00:08
Finished backup at 24-JAN-19

released channel: t1

released channel: t2

released channel: t3

RMAN>

5) Create standby control file

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      READ WRITE           PRIMARY <-------

SQL>

SQL> alter database create standby controlfile as '/u02/oracle/backup/DELL_BACKUP/std_control.ctl';

Database alter

6) Transfer backup to standby

[oracle@node1 DELL_BACKUP]$ scp * oracle@node2:/u02/oracle/backup/DELL_DG
oracle@node2's password:
database_DELL_0tto3o79_29                        100%  232KB 232.0KB/s   00:00
database_DELL_0uto3o79_30                        100%  736KB 736.0KB/s   00:00
database_DELL_0vto3o79_31                        100%   10MB  10.0MB/s   00:00
std_control.ctl                                  100%   10MB   9.9MB/s   00:00
[oracle@node1 DELL_BACKUP]$

7) List control file loaction on standby

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u02/oracle/oradata/DELL_DG/co
                                                 ntrol01.ctl, /u02/oracle/orada
                                                 ta/fast_recovery_area/DELL_DG/
                                                 control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>

8) Shutdown standby

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      MOUNTED              PHYSICAL STANDBY

SQL>
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>

9) Replace control file from backup


oracle@node2 DELL_DG]$ cp std_control.ctl /u02/oracle/oradata/DELL_DG/control01.ctl
[oracle@nide2 DELL_DG]$ cp std_control.ctl /u02/oracle/oradata/fast_recovery_area/DELL_DG/control02.ctl

10) Mount standby database


[oracle@node2 DELL_DG]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 24 04:28:58 2019

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
Database mounted

13) Recover standby

[oracle@node2 DELL_DG]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 24 04:29:48 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DELL (DBID=3965088591, not open)

RMAN> catalog backuppiece '/u02/oracle/backup/DELL_DG/database_DELL_0tto3o79_29';

Starting implicit crosscheck backup at 24-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 24-JAN-19

Starting implicit crosscheck copy at 24-JAN-19
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 24-JAN-19

searching for all files in the recovery area
cataloging files...
no files cataloged

cataloged backup piece
backup piece handle=/u02/oracle/backup/DELL_DG/database_DELL_0tto3o79_29 RECID=16 STAMP=998368237

RMAN> catalog backuppiece '/u02/oracle/backup/DELL_DG/database_DELL_0uto3o79_30';

cataloged backup piece
backup piece handle=/u02/oracle/backup/DELL_DG/database_DELL_0uto3o79_30 RECID=17 STAMP=998368262

RMAN> catalog backuppiece '/u02/oracle/backup/DELL_DG/database_DELL_0vto3o79_31';

cataloged backup piece
backup piece handle=/u02/oracle/backup/DELL_DG/database_DELL_0vto3o79_31 RECID=18 STAMP=998368289

RMAN> list backup tag STANDBY_TODAY_REFRESH;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16      Incr    224.00K    DISK        00:00:00     24-JAN-19
        BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/DELL_DG/database_DELL_0tto3o79_29
  List of Datafiles in backup set 16
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Incr 1431069    24-JAN-19 /u02/oracle/oradata/DELL_DG/system01.dbf
  4       Incr 1431069    24-JAN-19 /u02/oracle/oradata/DELL_DG/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17      Incr    728.00K    DISK        00:00:00     24-JAN-19
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/DELL_DG/database_DELL_0uto3o79_30
  List of Datafiles in backup set 17
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Incr 1431070    24-JAN-19 /u02/oracle/oradata/DELL_DG/sysaux01.dbf
  3       Incr 1431070    24-JAN-19 /u02/oracle/oradata/DELL_DG/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Incr    9.95M      DISK        00:00:00     24-JAN-19
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/DELL_DG/database_DELL_0vto3o79_31
  Control File Included: Ckp SCN: 1431069      Ckp time: 24-JAN-19

RMAN> exit


Recovery Manager complete.
[oracle@node2 DELL_DG]$

12. Cancel MRP

[oracle@node2 DELL_DG]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 24 04:32:49 2019

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL>  recover managed standby database cancel;
Media recovery complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

[oracle@node2 DELL_DG]$

14) Enable MRP

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      READ WRITE           PRIMARY <-------

SQL>

SQL> alter database create standby controlfile as '/u02/oracle/backup/DELL_BACKUP/std_control.ctl';

Database alter

15) Enable loa_archive_dest_stat_2 (on primary)

SQL> alter system set log_archive_dest_state_2='ENABLE' scope=both;

System altered.

SQL>

[oracle@node1 DELL_BACKUP]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> EDIT DATABASE "DELL" SET STATE='TRANSPORT-ON';
Succeeded.
DGMGRL>

16) Switch logfile (on primary)

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/archive/DELL
Oldest online log sequence     429
Next log sequence to archive   431
Current log sequence           431
SQL>

17) Verify sync

On Primary

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
           430 <------

SQL>

DGMGRL> show configuration;

Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
    DELL_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database "DELL_DG"

Database - DELL_DG

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    DELL_DG

Database Status:
SUCCESS

DGMGRL>

On standby

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/archive/DELL_DG
Oldest online log sequence     429
Next log sequence to archive   0
Current log sequence           431
SQL> 

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
       430

SQL>

Refresh Standby Database using RMAN Incremental SCN Backup

1) Verify Gap

On Primary

On Standby

3) Find current SCN from standby

4) Take RMAN Incremental from SCN (on primary)

5) Create standby control file

6) Transfer backup to standby

7) List control file loaction on standby

8) Shutdown standby

9) Replace control file from backup

10) Mount standby database

13) Recover standby

14) Enable MRP

15) Enable loa_archive_dest_stat_2 (on primary)

16) Switch logfile (on primary)

17) Verify sync

On Primary

Join Our Free Webinar Today!

Explore Insights, Trends, and Expert Advice

About Us

Explore

Useful Links

Contact Info

Refresh Standby Database using RMAN Incremental SCN Backup

Refresh Standby Database using RMAN Incremental SCN Backup

1) Verify Gap

On Primary

On Standby

3) Find current SCN from standby

4) Take RMAN Incremental from SCN (on primary)

5) Create standby control file

6) Transfer backup to standby

7) List control file loaction on standby

8) Shutdown standby

9) Replace control file from backup

10) Mount standby database

13) Recover standby

14) Enable MRP

15) Enable loa_archive_dest_stat_2 (on primary)

16) Switch logfile (on primary)

17) Verify syncOn Primary

Join Our Free Webinar Today!

Explore Insights, Trends, and Expert Advice

About Us

Explore

Useful Links

Contact Info

17) Verify sync

On Primary