--------Primary Site------------------
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
2 (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE#
FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#))ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; 3 4 5
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 6223 6223 0
SQL>
----------------Standby-------------------
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
2 (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE#
FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#))ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; 3 4 5
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 4986 4986 0
SQL>
------Find the SCN on the PRIMARY:
SQL> column current_scn format 9999999999999999999;
SQL> select current_scn from v$database;
CURRENT_SCN
--------------------
14321572697658
SQL>
-----Find the SCN on the STANDBY:
SQL> column current_scn format 9999999999999999999;
SQL> select current_scn from v$database;
CURRENT_SCN
--------------------
14198440472267
SQL>
------Clearly you can see there is difference
Stop and shutdown the managed standby apply process:
SQL> alter database recover managed standby database cancel;
Database altered.
---Shutdown the standby database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
--On the primary, take an incremental backup from the SCN number where the standby current value 14198440472267:
RMAN> run { allocate channel c1 type disk format '/u05/stdby_backup/%U.bkp';
backup incremental from scn 14198440472267 database;
}
Create a new standby control file on primary database :
RMAN> alter database create standby controlfile as '/u05/stdby_backup/for_standby.ctl';
Statement processed
RMAN>
----Copy this RMAN incremental backup and newly created standby control file from primary to standby side----
---Primary Database Server---
[oracle@bachdb u05]$ du -sh stdby_backup/
25G stdby_backup/
[oracle@bachdb u05]$ scp -r stdby_backup/ oracle@10.11.1.30:/u05/
oracle@10.11.1.30's password:
4cuubuqm_1_1.bkp 100% 23GB 11.2MB/s 34:26
4duubv06_1_1.bkp 100% 1466MB 11.2MB/s 02:10
4fuubv0d_1_1.bkp 100% 20MB 11.1MB/s 00:01
for_standby.ctl 100% 20MB 11.1MB/s 00:01
[oracle@bachdb u05]$
'
-----Check the backup file in standby Database server---
oracle@DBBACH u05]$ cd stdby_backup/
[oracle@DBBACH stdby_backup]$ ls -lrt
total 25250064
-rw-r-----. 1 oracle oinstall 24276566016 Apr 23 11:46 4cuubuqm_1_1.bkp
-rw-r-----. 1 oracle oinstall 1536868352 Apr 23 11:49 4duubv06_1_1.bkp
-rw-r-----. 1 oracle oinstall 21348352 Apr 23 11:49 4fuubv0d_1_1.bkp
-rw-r-----. 1 oracle oinstall 21282816 Apr 23 11:49 for_standby.ctl
[oracle@DBBACH stdby_backup]$
----Startup Nomount Standby Database---
[oracle@DBBACH stdby_backup]$ sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 23 11:51:54 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2.6844E+10 bytes
Fixed Size 26502656 bytes
Variable Size 5570038272 bytes
Database Buffers 2.1206E+10 bytes
Redo Buffers 40603648 bytes
----Replace existing standby control file with newly created standby control file from primary DB
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u05/app/oracle/oradata/bachdb
/control01.ctl, /u05/FRA/bachd
b/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
copy the backup control file taken from primary database to standby location
[oracle@DBBACH stdby_backup]$ scp for_standby.ctl /u05/app/oracle/oradata/bachdb/
[oracle@DBBACH stdby_backup]$ cd /u05/app/oracle/oradata/bachdb
[oracle@DBBACH bachdb]$ ls -lrt
total 4664772
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 13:13 redo01.log
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 13:13 redo02.log
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 13:13 redo03.log
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 22:44 redo_std02.rdo
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 22:45 redo_std01.rdo
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 22:46 redo_std03.rdo
-rw-r-----. 1 oracle oinstall 34611200 Feb 8 22:54 temp01.dbf
drwxr-x---. 2 oracle oinstall 4096 Feb 8 22:54 pdbseed
drwxr-x---. 2 oracle oinstall 4096 Feb 8 22:54 micrpdb
-rw-r-----. 1 oracle oinstall 209715712 Feb 9 10:18 redo_std04.rdo
-rw-r-----. 1 oracle oinstall 5251072 Apr 23 10:59 users01.dbf
-rw-r-----. 1 oracle oinstall 136323072 Apr 23 10:59 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 912269312 Apr 23 10:59 system01.dbf
-rw-r-----. 1 oracle oinstall 2212503552 Apr 23 10:59 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 20004864 Apr 23 11:02 control01.ctl
-rw-r-----. 1 oracle oinstall 21282816 Apr 23 11:55 for_standby.ctl
[oracle@DBBACH bachdb]$
[oracle@DBBACH FRA]$ cd /u05/stdby_backup/
[oracle@DBBACH stdby_backup]$ scp for_standby.ctl /u05/FRA/bachdb/
[oracle@DBBACH stdby_backup]$ cd /u05/FRA/bachdb/
[oracle@DBBACH bachdb]$ ls -lrt
total 40320
drwxr-x---. 4 oracle oinstall 41 Feb 8 22:44 BACHSTDB
-rw-r-----. 1 oracle oinstall 20004864 Apr 23 11:02 control02.ctl
-rw-r-----. 1 oracle oinstall 21282816 Apr 23 11:56 for_standby.ctl
[oracle@DBBACH bachdb]$
----Rename the existing control files of standby database.---
[oracle@DBBACH bachdb]$ ls -lrt
total 40320
drwxr-x---. 4 oracle oinstall 41 Feb 8 22:44 BACHSTDB
-rw-r-----. 1 oracle oinstall 20004864 Apr 23 11:02 control02.ctl
-rw-r-----. 1 oracle oinstall 21282816 Apr 23 11:56 for_standby.ctl
[oracle@DBBACH bachdb]$ mv control02.ctl control02.ctl_old
[oracle@DBBACH bachdb]$ mv for_standby.ctl control02.ctl
[oracle@DBBACH bachdb]$ ls -lrt
total 40320
drwxr-x---. 4 oracle oinstall 41 Feb 8 22:44 BACHSTDB
-rw-r-----. 1 oracle oinstall 20004864 Apr 23 11:02 control02.ctl_old
-rw-r-----. 1 oracle oinstall 21282816 Apr 23 11:56 control02.ctl
[oracle@DBBACH bachdb]$
[oracle@DBBACH bachdb]$ cd /u05/app/oracle/oradata/bachdb
[oracle@DBBACH bachdb]$ ls -lrt
total 4664772
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 13:13 redo01.log
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 13:13 redo02.log
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 13:13 redo03.log
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 22:44 redo_std02.rdo
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 22:45 redo_std01.rdo
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 22:46 redo_std03.rdo
-rw-r-----. 1 oracle oinstall 34611200 Feb 8 22:54 temp01.dbf
drwxr-x---. 2 oracle oinstall 4096 Feb 8 22:54 pdbseed
drwxr-x---. 2 oracle oinstall 4096 Feb 8 22:54 micrpdb
-rw-r-----. 1 oracle oinstall 209715712 Feb 9 10:18 redo_std04.rdo
-rw-r-----. 1 oracle oinstall 5251072 Apr 23 10:59 users01.dbf
-rw-r-----. 1 oracle oinstall 136323072 Apr 23 10:59 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 912269312 Apr 23 10:59 system01.dbf
-rw-r-----. 1 oracle oinstall 2212503552 Apr 23 10:59 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 20004864 Apr 23 11:02 control01.ctl
-rw-r-----. 1 oracle oinstall 21282816 Apr 23 11:55 for_standby.ctl
[oracle@DBBACH bachdb]$ mv control01.ctl control01.ctl_old
[oracle@DBBACH bachdb]$ mv for_standby.ctl control01.ctl
[oracle@DBBACH bachdb]$ ls -lrt
total 4664772
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 13:13 redo01.log
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 13:13 redo02.log
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 13:13 redo03.log
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 22:44 redo_std02.rdo
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 22:45 redo_std01.rdo
-rw-r-----. 1 oracle oinstall 209715712 Feb 8 22:46 redo_std03.rdo
-rw-r-----. 1 oracle oinstall 34611200 Feb 8 22:54 temp01.dbf
drwxr-x---. 2 oracle oinstall 4096 Feb 8 22:54 pdbseed
drwxr-x---. 2 oracle oinstall 4096 Feb 8 22:54 micrpdb
-rw-r-----. 1 oracle oinstall 209715712 Feb 9 10:18 redo_std04.rdo
-rw-r-----. 1 oracle oinstall 5251072 Apr 23 10:59 users01.dbf
-rw-r-----. 1 oracle oinstall 136323072 Apr 23 10:59 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 912269312 Apr 23 10:59 system01.dbf
-rw-r-----. 1 oracle oinstall 2212503552 Apr 23 10:59 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 20004864 Apr 23 11:02 control01.ctl_old
-rw-r-----. 1 oracle oinstall 21282816 Apr 23 11:55 control01.ctl
[oracle@DBBACH bachdb]$
----Bring Standby Database into Mount mode----
SQL> alter database mount standby database;
Database altered.
SQL>
-----Catalog RMAN incremental backup on standby database server
---on Standby Database Server---
[oracle@DBBACH stdby_backup]$ pwd
/u05/stdby_backup
[oracle@DBBACH stdby_backup]$ ls -lrt
total 25250064
-rw-r-----. 1 oracle oinstall 24276566016 Apr 23 11:46 4cuubuqm_1_1.bkp
-rw-r-----. 1 oracle oinstall 1536868352 Apr 23 11:49 4duubv06_1_1.bkp
-rw-r-----. 1 oracle oinstall 21348352 Apr 23 11:49 4fuubv0d_1_1.bkp
-rw-r-----. 1 oracle oinstall 21282816 Apr 23 11:49 for_standby.ctl
[oracle@DBBACH stdby_backup]$ rman target/
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Apr 23 12:03:19 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: BACHDB (DBID=1588468615, not open)
RMAN> catalog start with '/u05/stdby_backup';
--PERFORM RECOVER---
RMAN> recover database noredo;
Starting recover at 23-APR-20
using channel ORA_DISK_1
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 00013: /u05/app/oracle/oradata/bachdb/micrpdb/system01.dbf
destination for restore of datafile 00014: /u05/app/oracle/oradata/bachdb/micrpdb/sysaux01.dbf
destination for restore of datafile 00015: /u05/app/oracle/oradata/bachdb/micrpdb/undotbs01.dbf
destination for restore of datafile 00016: /u05/app/oracle/oradata/bachdb/micrpdb/users01.dbf
.
.
.
.
channel ORA_DISK_1: reading from backup piece /u05/stdby_backup/4cuubuqm_1_1.bkp
channel ORA_DISK_1: piece handle=/u05/stdby_backup/4cuubuqm_1_1.bkp tag=TAG20200423T111742
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:45
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: /u05/app/oracle/oradata/bachdb/system01.dbf
destination for restore of datafile 00003: /u05/app/oracle/oradata/bachdb/sysaux01.dbf
destination for restore of datafile 00004: /u05/app/oracle/oradata/bachdb/undotbs01.dbf
destination for restore of datafile 00007: /u05/app/oracle/oradata/bachdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u05/stdby_backup/4duubv06_1_1.bkp
channel ORA_DISK_1: piece handle=/u05/stdby_backup/4duubv06_1_1.bkp tag=TAG20200423T111742
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished recover at 23-APR-20
RMAN>
---Clear all standby logfiles on standby database :
---on Standby Database Server---
SQL> set pagesize 100
col MEMBER for a50
SQL> select Group#,MEMBER,TYPE,status from v$logfile;
GROUP# MEMBER TYPE STATUS
---------- -------------------------------------------------- ------- -------
3 /u05/app/oracle/oradata/bachdb/redo03.log ONLINE
2 /u05/app/oracle/oradata/bachdb/redo02.log ONLINE
1 /u05/app/oracle/oradata/bachdb/redo01.log ONLINE
4 /u05/app/oracle/oradata/bachdb/redo_std01.rdo STANDBY
5 /u05/app/oracle/oradata/bachdb/redo_std02.rdo STANDBY
6 /u05/app/oracle/oradata/bachdb/redo_std03.rdo STANDBY
7 /u05/app/oracle/oradata/bachdb/redo_std04.rdo STANDBY
7 rows selected.
SQL>
SQL> select GROUP#,MEMBERS,BYTES,STATUS from v$log;
GROUP# MEMBERS BYTES STATUS
---------- ---------- ---------- ----------------
1 1 209715200 INACTIVE
3 1 209715200 INACTIVE
2 1 209715200 CURRENT
SQL>
SQL> select GROUP#,BYTES,status from v$standby_log;
GROUP# BYTES STATUS
---------- ---------- ----------
4 209715200 UNASSIGNED
5 209715200 UNASSIGNED
6 209715200 UNASSIGNED
7 209715200 UNASSIGNED
SQL>
-----Start the Media recovery process on standby database :
SQL> alter database recover managed standby database disconnect from session;
-----On Standby Database Server if RFS process is not working
-----On Standby Database Server----
SQL> select process, status , sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 6224
7 rows selected.
-----on Primary Database Server---
SQL> alter system set log_archive_dest_state_2=DEFER;
System altered.
SQL>
-----On Standby Database Server----
SQL> alter database recover managed standby database cancel;
Database altered.
SQL>
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
-----on Primary Database Server---
[oracle@bachdb ~]$ ps -ef | grep -i arc
oracle 1320 849 0 18:28 pts/4 00:00:00 grep --color=auto -i arc
oracle 21082 1 0 Feb08 ? 00:00:53 ora_arc0_bachdb
oracle 21157 1 0 Feb08 ? 00:00:49 ora_arc1_bachdb
oracle 21159 1 0 Feb08 ? 00:00:48 ora_arc2_bachdb
oracle 21161 1 0 Feb08 ? 00:00:48 ora_arc3_bachdb
oracle 21163 1 0 Feb08 ? 00:00:49 ora_arc4_bachdb
oracle 21165 1 0 Feb08 ? 00:00:47 ora_arc5_bachdb
oracle 21167 1 0 Feb08 ? 00:00:50 ora_arc6_bachdb
oracle 21171 1 0 Feb08 ? 00:00:47 ora_arc7_bachdb
oracle 21173 1 0 Feb08 ? 00:00:48 ora_arc8_bachdb
oracle 21175 1 0 Feb08 ? 00:00:50 ora_arc9_bachdb
[oracle@bachdb ~]$
[oracle@bachdb ~]$ kill -9 21082 21157 21159 21161 21163 21165 21167 21171 21173 21175
[oracle@bachdb ~]$ ps -ef | grep -i arc
oracle 1771 849 0 18:29 pts/4 00:00:00 grep --color=auto -i arc
[oracle@bachdb ~]$
-----On Standby Database Server----
SQL> startup mount
ORACLE instance started.
Total System Global Area 2.6844E+10 bytes
Fixed Size 26502656 bytes
Variable Size 5570038272 bytes
Database Buffers 2.1206E+10 bytes
Redo Buffers 40603648 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
-----on Primary Database Server---
SQL> alter system set log_archive_dest_state_2=ENABLE;
System altered.
SQL>
Now Check Primary and Standby Database server alert log that archive log is successfully applied or not
-----On Standby Database Server----
SQL> select process, status , sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 APPLYING_LOG 6238
RFS IDLE 0
RFS IDLE 6238
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
18 rows selected.
SQL>
