- 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>