Incremental RMAN backup approach in 2 node RAC and standalone standby
----------------------------------------------------------------------
Primary - 2 node RAC - deopvdvvnode5a(TEST1),deopvdvvnode5b(TEST2)
Secondary - standalone DB - deopvdvvnode4a(TEST1)
1) Find current_scn number from both primary and standby.
PRIMARY:TEST\sys> select current_scn from v$database;
CURRENT_SCN
-----------
65445955
TEST1\sys> select current_scn from v$database;
CURRENT_SCN
-----------
42188320
2) Find the exact time , since its lagging. Need to hit for both the scn on primary , since on standby it will fail.
PRIMARY:TEST\sys> select scn_to_timestamp(65445955) from dual;
SCN_TO_TIMESTAMP(65445955)
---------------------------------------------------------------------------
02-MAR-16 04.02.32.000000000 PM
select scn_to_timestamp(42188320) from dual;
3) Stop the managed standby apply process:
SQL> alter database recover managed standby database cancel;
Database altered.
4) Shutdown the standby database
5) On the primary, take an incremental backup from the SCN number where the standby has been stuck:
run {
allocate channel c1 type disk format '/opt/app/oracle/oraback/%U.rmb';
backup incremental from scn 42188320 database;
}
RMAN> run {
allocate channel c1 type disk format '/opt/app/oracle/oraback/%U.rmb';
backup incremental from scn 42188320 database;
}2> 3> 4>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=15 instance=TEST1 device type=DISK
Starting backup at 02-MAR-16
backup will be obsolete on date 09-MAR-16
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DG_TEST/test/datafile/sysaux.257.893130419
input datafile file number=00001 name=+DG_TEST/test/datafile/system.256.893130419
input datafile file number=00003 name=+DG_TEST/test/datafile/undotbs1.258.893130419
input datafile file number=00006 name=+DG_TEST/test/datafile/undotbs2.268.893130659
input datafile file number=00005 name=+DG_TEST/test/datafile/example.267.893130515
input datafile file number=00004 name=+DG_TEST/test/datafile/users.259.893130419
channel c1: starting piece 1 at 02-MAR-16
channel c1: finished piece 1 at 02-MAR-16
piece handle=/opt/app/oracle/oraback/0dqvg095_1_1.rmb tag=TAG20160302T164109 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:46
backup will be obsolete on date 09-MAR-16
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 02-MAR-16
channel c1: finished piece 1 at 02-MAR-16
piece handle=/opt/app/oracle/oraback/0eqvg0an_1_1.rmb tag=TAG20160302T164109 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-MAR-16
released channel: c1
6) On the primary, create a new standby controlfile:
PRIMARY:TEST\sys> alter database create standby controlfile as '/opt/app/oracle/oraback/DEL1_standby.ctl';
Database altered.
7) Copy all the files from primary to standby host:
8) Bring up the instance in nomount mode:
SQL> startup nomount
9) Check the location of the controlfile:
SQL> show parameter control_files
10) Restore the standby control file.
RMAN> restore standby controlfile from '/opt/app/oracle/oraback/DEL1_standby.ctl';
11) Mount the standby database:
RMAN> alter database mount;
12) Catalog the backups.
13) Recover these files.
RMAN> recover database;
Starting recover at 02-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=329 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/02/2016 17:01:29
RMAN-06094: datafile 1 must be restored
RMAN> exit
Error came as RMAN was using information from control file with Primary ASM directory strucuture. So we need to use switch command to upate RMAN repository.
14) Switch datafiles.
RMAN> switch datafile 1 to copy;
RMAN> switch datafile 2 to copy;
RMAN> switch datafile 3 to copy;
RMAN> switch datafile 4 to copy;
RMAN> switch datafile 5 to copy;
RMAN> switch datafile 6 to copy;
15) Start recovery again.
TRAINING_STANDBY:deopvdvvnode4a:[TEST1]:/opt/app/oracle/oraback> rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 2 17:12:43 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2191276923, not open)
RMAN> recover database;
Starting recover at 02-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=312 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DG_DATA/test_stby/datafile/system.257.899494475
destination for restore of datafile 00002: +DG_DATA/test_stby/datafile/sysaux.261.899494475
destination for restore of datafile 00003: +DG_DATA/test_stby/datafile/undotbs1.260.899494475
destination for restore of datafile 00004: +DG_DATA/test_stby/datafile/users.263.899494493
destination for restore of datafile 00005: +DG_DATA/test_stby/datafile/example.256.899494475
destination for restore of datafile 00006: +DG_DATA/test_stby/datafile/undotbs2.262.899494491
channel ORA_DISK_1: reading from backup piece /opt/app/oracle/oraback/0dqvg095_1_1.rmb
channel ORA_DISK_1: piece handle=/opt/app/oracle/oraback/0dqvg095_1_1.rmb tag=TAG20160302T164109
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
starting media recovery
archived log for thread 1 with sequence 7232 is already on disk as file +DG_DATA/test_stby/archivelog/2016_03_02/thread_1_seq_7232.882.905446609
archived log for thread 2 with sequence 7064 is already on disk as file +DG_DATA/test_stby/archivelog/2016_03_02/thread_2_seq_7064.884.905446609
archived log file name=+DG_DATA/test_stby/archivelog/2016_03_02/thread_1_seq_7232.882.905446609 thread=1 sequence=7232
archived log file name=+DG_DATA/test_stby/archivelog/2016_03_02/thread_2_seq_7064.884.905446609 thread=2 sequence=7064
unable to find archived log
archived log thread=1 sequence=7233
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/02/2016 17:13:06
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7233 and starting SCN of 65468812
RMAN> exit
Error is exepected as all the archives has been applied and its waiting for a particular sequence.
16) Start the managed recovery process.
SQL> alter database recover managed standby database disconnect from session;
17) Check the primary and standby with below queries to find if primary/standby are in sync.
Primary:
SQL >
select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PRIMARY:TEST\sys> 2 3 4
THREAD# Last Primary Seq Generated
---------- --------------------------
1 7242
2 7070
PhyStdby:
SQL >
select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
THREAD# Last Standby Seq Received
---------- -------------------------
1 7242
2 7070
PhyStdby:
SQL >
TEST1\sys> select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and applied='YES'
group by thread# order by 1; 2 3 4 5
THREAD# Last Standby Seq Applied
---------- ------------------------
1 7241
2 7070
Note - Though its a 2-node cluster, incremental approach do not change much. Once all steps are done, you might see a lot of latency for thread 2. But after some time, both the threads will be in sync.
----------------------------------------------------------------------
Primary - 2 node RAC - deopvdvvnode5a(TEST1),deopvdvvnode5b(TEST2)
Secondary - standalone DB - deopvdvvnode4a(TEST1)
1) Find current_scn number from both primary and standby.
PRIMARY:TEST\sys> select current_scn from v$database;
CURRENT_SCN
-----------
65445955
TEST1\sys> select current_scn from v$database;
CURRENT_SCN
-----------
42188320
2) Find the exact time , since its lagging. Need to hit for both the scn on primary , since on standby it will fail.
PRIMARY:TEST\sys> select scn_to_timestamp(65445955) from dual;
SCN_TO_TIMESTAMP(65445955)
---------------------------------------------------------------------------
02-MAR-16 04.02.32.000000000 PM
select scn_to_timestamp(42188320) from dual;
3) Stop the managed standby apply process:
SQL> alter database recover managed standby database cancel;
Database altered.
4) Shutdown the standby database
5) On the primary, take an incremental backup from the SCN number where the standby has been stuck:
run {
allocate channel c1 type disk format '/opt/app/oracle/oraback/%U.rmb';
backup incremental from scn 42188320 database;
}
RMAN> run {
allocate channel c1 type disk format '/opt/app/oracle/oraback/%U.rmb';
backup incremental from scn 42188320 database;
}2> 3> 4>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=15 instance=TEST1 device type=DISK
Starting backup at 02-MAR-16
backup will be obsolete on date 09-MAR-16
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DG_TEST/test/datafile/sysaux.257.893130419
input datafile file number=00001 name=+DG_TEST/test/datafile/system.256.893130419
input datafile file number=00003 name=+DG_TEST/test/datafile/undotbs1.258.893130419
input datafile file number=00006 name=+DG_TEST/test/datafile/undotbs2.268.893130659
input datafile file number=00005 name=+DG_TEST/test/datafile/example.267.893130515
input datafile file number=00004 name=+DG_TEST/test/datafile/users.259.893130419
channel c1: starting piece 1 at 02-MAR-16
channel c1: finished piece 1 at 02-MAR-16
piece handle=/opt/app/oracle/oraback/0dqvg095_1_1.rmb tag=TAG20160302T164109 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:46
backup will be obsolete on date 09-MAR-16
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 02-MAR-16
channel c1: finished piece 1 at 02-MAR-16
piece handle=/opt/app/oracle/oraback/0eqvg0an_1_1.rmb tag=TAG20160302T164109 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-MAR-16
released channel: c1
6) On the primary, create a new standby controlfile:
PRIMARY:TEST\sys> alter database create standby controlfile as '/opt/app/oracle/oraback/DEL1_standby.ctl';
Database altered.
7) Copy all the files from primary to standby host:
8) Bring up the instance in nomount mode:
SQL> startup nomount
9) Check the location of the controlfile:
SQL> show parameter control_files
10) Restore the standby control file.
RMAN> restore standby controlfile from '/opt/app/oracle/oraback/DEL1_standby.ctl';
11) Mount the standby database:
RMAN> alter database mount;
12) Catalog the backups.
13) Recover these files.
RMAN> recover database;
Starting recover at 02-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=329 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/02/2016 17:01:29
RMAN-06094: datafile 1 must be restored
RMAN> exit
Error came as RMAN was using information from control file with Primary ASM directory strucuture. So we need to use switch command to upate RMAN repository.
14) Switch datafiles.
RMAN> switch datafile 1 to copy;
RMAN> switch datafile 2 to copy;
RMAN> switch datafile 3 to copy;
RMAN> switch datafile 4 to copy;
RMAN> switch datafile 5 to copy;
RMAN> switch datafile 6 to copy;
15) Start recovery again.
TRAINING_STANDBY:deopvdvvnode4a:[TEST1]:/opt/app/oracle/oraback> rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 2 17:12:43 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2191276923, not open)
RMAN> recover database;
Starting recover at 02-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=312 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DG_DATA/test_stby/datafile/system.257.899494475
destination for restore of datafile 00002: +DG_DATA/test_stby/datafile/sysaux.261.899494475
destination for restore of datafile 00003: +DG_DATA/test_stby/datafile/undotbs1.260.899494475
destination for restore of datafile 00004: +DG_DATA/test_stby/datafile/users.263.899494493
destination for restore of datafile 00005: +DG_DATA/test_stby/datafile/example.256.899494475
destination for restore of datafile 00006: +DG_DATA/test_stby/datafile/undotbs2.262.899494491
channel ORA_DISK_1: reading from backup piece /opt/app/oracle/oraback/0dqvg095_1_1.rmb
channel ORA_DISK_1: piece handle=/opt/app/oracle/oraback/0dqvg095_1_1.rmb tag=TAG20160302T164109
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
starting media recovery
archived log for thread 1 with sequence 7232 is already on disk as file +DG_DATA/test_stby/archivelog/2016_03_02/thread_1_seq_7232.882.905446609
archived log for thread 2 with sequence 7064 is already on disk as file +DG_DATA/test_stby/archivelog/2016_03_02/thread_2_seq_7064.884.905446609
archived log file name=+DG_DATA/test_stby/archivelog/2016_03_02/thread_1_seq_7232.882.905446609 thread=1 sequence=7232
archived log file name=+DG_DATA/test_stby/archivelog/2016_03_02/thread_2_seq_7064.884.905446609 thread=2 sequence=7064
unable to find archived log
archived log thread=1 sequence=7233
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/02/2016 17:13:06
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7233 and starting SCN of 65468812
RMAN> exit
Error is exepected as all the archives has been applied and its waiting for a particular sequence.
16) Start the managed recovery process.
SQL> alter database recover managed standby database disconnect from session;
17) Check the primary and standby with below queries to find if primary/standby are in sync.
Primary:
SQL >
select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PRIMARY:TEST\sys> 2 3 4
THREAD# Last Primary Seq Generated
---------- --------------------------
1 7242
2 7070
PhyStdby:
SQL >
select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
THREAD# Last Standby Seq Received
---------- -------------------------
1 7242
2 7070
PhyStdby:
SQL >
TEST1\sys> select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and applied='YES'
group by thread# order by 1; 2 3 4 5
THREAD# Last Standby Seq Applied
---------- ------------------------
1 7241
2 7070
Note - Though its a 2-node cluster, incremental approach do not change much. Once all steps are done, you might see a lot of latency for thread 2. But after some time, both the threads will be in sync.
