Hello everybody.
We use 11.2.0.3 on both primary and standby
Here what i have on a physical standby:
SQL> select sequence#, first_time, applied from v$archived_log where applied <> 'YES' order by sequence# desc;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
103363 13-APR-17 IN-MEMORY
103346 12-APR-17 IN-MEMORY
103328 11-APR-17 IN-MEMORY
103311 10-APR-17 IN-MEMORY
103292 09-APR-17 IN-MEMORY
103290 08-APR-17 IN-MEMORY
103288 07-APR-17 IN-MEMORY
103267 06-APR-17 IN-MEMORY
103250 05-APR-17 IN-MEMORY
103229 04-APR-17 IN-MEMORY
103206 03-APR-17 IN-MEMORY
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
103192 02-APR-17 IN-MEMORY
103190 01-APR-17 IN-MEMORY
103188 31-MAR-17 IN-MEMORY
103169 30-MAR-17 IN-MEMORY
103149 29-MAR-17 IN-MEMORY
103131 28-MAR-17 IN-MEMORY
As you can see it is very strange situation
This information also from standby:
log_archive_dest_1 location="/ora/oradata/work/archive"
[oracle@odbs ~]$ ls -lt /ora/oradata/work/archive
total 12763460
-rw-r----- 1 oracle dba 725986816 Apr 13 20:00 1_103363_787685890.dbf
-rw-r----- 1 oracle dba 737408000 Apr 13 19:49 1_103362_787685890.dbf
-rw-r----- 1 oracle dba 726018560 Apr 13 19:47 1_103361_787685890.dbf
-rw-r----- 1 oracle dba 731826688 Apr 13 19:45 1_103360_787685890.dbf
-rw-r----- 1 oracle dba 724728832 Apr 13 19:37 1_103359_787685890.dbf
-rw-r----- 1 oracle dba 727676416 Apr 13 19:11 1_103358_787685890.dbf
-rw-r----- 1 oracle dba 721420288 Apr 13 16:21 1_103357_787685890.dbf
-rw-r----- 1 oracle dba 721518592 Apr 13 14:31 1_103356_787685890.dbf
-rw-r----- 1 oracle dba 723893248 Apr 13 12:38 1_103355_787685890.dbf
-rw-r----- 1 oracle dba 725026816 Apr 13 11:36 1_103354_787685890.dbf
-rw-r----- 1 oracle dba 727796224 Apr 13 11:13 1_103353_787685890.dbf
-rw-r----- 1 oracle dba 721692672 Apr 13 09:25 1_103352_787685890.dbf
-rw-r----- 1 oracle dba 724815360 Apr 13 09:10 1_103351_787685890.dbf
-rw-r----- 1 oracle dba 727294464 Apr 13 09:07 1_103350_787685890.dbf
-rw-r----- 1 oracle dba 727645184 Apr 13 09:02 1_103349_787685890.dbf
-rw-r----- 1 oracle dba 729434624 Apr 13 08:59 1_103348_787685890.dbf
-rw-r----- 1 oracle dba 724100096 Apr 13 08:56 1_103347_787685890.dbf
-rw-r----- 1 oracle dba 721397760 Apr 13 07:00 1_103346_787685890.dbf
SQL> !ls -l /ora/oradata/work/
total 403503648
drwxr-xr-x. 2 oracle dba 233472 Apr 13 22:13 archive
-rw-r----- 1 oracle dba 34359418880 Apr 13 22:13 b2_data01.dbf
-rw-r----- 1 oracle dba 34356600832 Apr 13 22:13 b2_data02.dbf
-rw-r----- 1 oracle dba 34358697984 Apr 13 22:13 b2_data03.dbf
-rw-r----- 1 oracle dba 34357649408 Apr 13 22:13 b2_data04.dbf
-rw-r----- 1 oracle dba 34359730176 Apr 13 22:13 b2_data05.dbf
-rw-r----- 1 oracle dba 31247679488 Apr 13 22:13 b2_data10.dbf
-rw-r----- 1 oracle dba 21390958592 Apr 13 22:13 b2_data11.dbf
-rw-r----- 1 oracle dba 104865792 Apr 13 22:13 b2_data12.dbf
-rw-r----- 1 oracle dba 34359730176 Apr 13 22:13 b2_data6.dbf
-rw-r----- 1 oracle dba 34359730176 Apr 13 22:13 b2_data7.dbf
-rw-r----- 1 oracle dba 34359730176 Apr 13 22:13 b2_data8.dbf
-rw-r----- 1 oracle dba 34359730176 Apr 13 22:13 b2_data9.dbf
-rw-r----- 1 oracle dba 44023808 Apr 13 22:13 control01.ora
-rw-r----- 1 oracle dba 44023808 Apr 13 22:13 control02.ora
-rw-r----- 1 oracle dba 44023808 Apr 13 22:13 control03.ora
-rw-r----- 1 oracle dba 367009792 Apr 13 22:13 FUND_XE.DBF
-rw-r----- 1 oracle dba 104865792 Apr 13 22:13 payment.dbf
-rw-r----- 1 oracle dba 104865792 Apr 13 22:13 payment_ind.dbf
-rw-r----- 1 oracle dba 1258299392 Apr 13 22:13 paynet.dbf
-rw-r----- 1 oracle dba 209723392 Apr 13 22:13 paynet_ind.dbf
-rw-r----- 1 oracle dba 1073742336 Dec 7 2015 redo01.log
-rw-r----- 1 oracle dba 1073742336 Dec 7 2015 redo02.log
-rw-r----- 1 oracle dba 1073742336 Dec 7 2015 redo03.log
-rw-r----- 1 oracle dba 1073742336 Dec 7 2015 redo04.log
-rw-r----- 1 oracle dba 1073742336 Dec 7 2015 redo05.log
-rw-r----- 1 oracle dba 1073742336 Apr 13 22:13 stby_redo01.log
-rw-r----- 1 oracle dba 1073742336 Apr 13 22:13 stby_redo02.log
-rw-r----- 1 oracle dba 1073742336 Mar 12 15:08 stby_redo03.log
-rw-r----- 1 oracle dba 1073742336 Aug 29 2016 stby_redo04.log
-rw-r----- 1 oracle dba 1073742336 Dec 7 2015 stby_redo05.log
Here what i have on the primary server
[oracle@odbp ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> connect sys/pass
Connected.
DGMGRL> show configuration
Configuration - work_DataGuardConfig
Protection Mode: MaxPerformance
Databases:
odbp_work - Primary database
ODBS_WORK - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
-----
SUCCESS!!!!!!
----
here are some steps i took on the standby
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect;
Database altered.
----
here is the alertlog on standby
alter database recover managed standby database cancel
Thu Apr 13 22:22:43 2017
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /ora/diag/rdbms/odbs_work/work/trace/work_pr00_24796.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 3221010445
Thu Apr 13 22:22:44 2017
MRP0: Background Media Recovery process shutdown (work)
Managed Standby Recovery Canceled (work)
Completed: alter database recover managed standby database cancel
Thu Apr 13 22:22:56 2017
alter database recover managed standby database disconnect
Attempt to start background Managed Standby Recovery process (work)
Thu Apr 13 22:22:56 2017
MRP0 started with pid=32, OS id=24916
MRP0: Background Managed Standby Recovery process started (work)
started logmerger process
Thu Apr 13 22:23:02 2017
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 32 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: alter database recover managed standby database disconnect
Media Recovery Waiting for thread 1 sequence 103368 (in transit)
-----------
on primary
oracle@odbp ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 13 22:24:30 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
on standby
Thu Apr 13 22:24:36 2017
RFS[1]: Selected log 7 for thread 1 sequence 103369 dbid -933262782 branch 787685890
Thu Apr 13 22:24:36 2017
Archived Log entry 20720 added for thread 1 sequence 103368 ID 0xc8613b39 dest 1:
Thu Apr 13 22:24:36 2017
ARC4: Standby redo logfile selected for thread 1 sequence 103368 for destination LOG_ARCHIVE_DEST_2
Thu Apr 13 22:24:39 2017
Media Recovery Log /ora/oradata/work/archive/1_103368_787685890.dbf
Media Recovery Waiting for thread 1 sequence 103369 (in transit)
RFS[1]: Selected log 6 for thread 1 sequence 103370 dbid -933262782 branch 787685890
Thu Apr 13 22:24:43 2017
Archived Log entry 20722 added for thread 1 sequence 103369 ID 0xc8613b39 dest 1:
ARC3: Standby redo logfile selected for thread 1 sequence 103369 for destination LOG_ARCHIVE_DEST_2
Media Recovery Log /ora/oradata/work/archive/1_103369_787685890.dbf
Media Recovery Waiting for thread 1 sequence 103370 (in transit)
Thu Apr 13 22:24:48 2017
RFS[1]: Selected log 7 for thread 1 sequence 103371 dbid -933262782 branch 787685890
Thu Apr 13 22:24:48 2017
Archived Log entry 20724 added for thread 1 sequence 103370 ID 0xc8613b39 dest 1:
ARC0: Standby redo logfile selected for thread 1 sequence 103370 for destination LOG_ARCHIVE_DEST_2
Thu Apr 13 22:24:51 2017
Media Recovery Log /ora/oradata/work/archive/1_103370_787685890.dbf
Media Recovery Waiting for thread 1 sequence 103371 (in transit)
SQL> select sequence#, first_time, applied from v$archived_log where applied <> 'YES' order by sequence# desc;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
103346 12-APR-17 IN-MEMORY
103328 11-APR-17 IN-MEMORY
103311 10-APR-17 IN-MEMORY
103292 09-APR-17 IN-MEMORY
103290 08-APR-17 IN-MEMORY
103288 07-APR-17 IN-MEMORY
103267 06-APR-17 IN-MEMORY
103250 05-APR-17 IN-MEMORY
103229 04-APR-17 IN-MEMORY
103206 03-APR-17 IN-MEMORY
103192 02-APR-17 IN-MEMORY
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
103190 01-APR-17 IN-MEMORY
103188 31-MAR-17 IN-MEMORY
103169 30-MAR-17 IN-MEMORY
103149 29-MAR-17 IN-MEMORY
103131 28-MAR-17 IN-MEMORY
16 rows selected.
[oracle@odbs ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 13 22:41:23 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: WORK (DBID=3361704514, not open)
RMAN> report unrecoverable;
using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
on primary
RMAN> show archivelog deletion policy;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ODBP_WORK are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
Could someone please look into this