Database Software

I did a refresh of data on production site, then after import is done, I found out the standby site password file out of sync. After I sync up the password files on both prod and standby, the standby archivelog apply was long out of sync.

Now recover is going on, however it was very very slow. I don't think it is right. Here are some data:

On Production:

SQL> SELECT
NAME AS STANDBY,
SEQUENCE#,
APPLIED,
COMPLETION_TIME
FROM
V$ARCHIVED_LOG
WHERE
DEST_ID =2
AND
NEXT_TIME > SYSDATE -1; 2 3 4 5 6 7 8 9 10 11

STANDBY SEQUENCE# APPLIED COMPLETIO
-------------------- ---------- ---------- ---------
hysdev 18693 NO 02-JAN-12
hysdev 18696 NO 02-JAN-12
hysdev 18697 NO 02-JAN-12
hysdev 18698 NO 02-JAN-12
hysdev 18699 NO 02-JAN-12
hysdev 18700 NO 02-JAN-12
hysdev 18701 NO 02-JAN-12
hysdev 18702 NO 02-JAN-12
hysdev 18703 NO 03-JAN-12
hysdev 18704 NO 03-JAN-12
hysdev 18705 NO 03-JAN-12

STANDBY SEQUENCE# APPLIED COMPLETIO
-------------------- ---------- ---------- ---------
hysdev 18706 NO 03-JAN-12
hysdev 18707 NO 03-JAN-12
hysdev 18708 NO 03-JAN-12
hysdev 18709 NO 03-JAN-12
hysdev 18710 NO 03-JAN-12

16 rows selected.

SQL> SELECT
MAX(SEQUENCE#)
FROM
V$ARCHIVED_LOG
WHERE
NEXT_TIME > SYSDATE -1;
2 3 4 5 6
MAX(SEQUENCE#)
--------------
18710

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)
---------- --------------
1 18710

SQL> select name, floor(space_limit / 1024 / 1024) "Size MB", ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
2 3 4
SQL> /

NAME
----------------------------------------------------------------------------------------------------
Size MB Used MB
---------- ----------
+HY_FRA
368640 367839

SQL> SQL> select ds.dest_id id
2 , ad.status
, ds.database_mode db_mode
3 4 , ad.archiver type
5 , ds.recovery_mode
6 , ds.protection_mode
7 , ds.standby_logfile_count "SRLs"
8 , ds.standby_logfile_active active
9 , ds.archived_seq#
10 from v$archive_dest_status ds
, v$archive_dest ad
where ds.dest_id = ad.dest_id
11 12 13 and ad.status != 'INACTIVE'
14 order by
15 ds.dest_id
16 /

ID STATUS DB_MODE TYPE RECOVERY_MODE PROTECTION_MODE
---------- --------- --------------- ---------- ----------------------- --------------------
SRLs ACTIVE ARCHIVED_SEQ#
---------- ---------- -------------
1 VALID OPEN ARCH IDLE MAXIMUM PERFORMANCE
0 0 18710

2 VALID OPEN_READ-ONLY LGWR MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE
8 2 16851

on Standby:
SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CLOSING 18703
ARCH CLOSING 18704
ARCH CLOSING 18705
ARCH CLOSING 18706
ARCH CLOSING 18707
ARCH CLOSING 18708
ARCH CLOSING 18709
ARCH CLOSING 18710

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS RECEIVING 16852
RFS RECEIVING 16869
RFS RECEIVING 16863

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
RFS RECEIVING 16860
RFS RECEIVING 16872
RFS RECEIVING 16862
RFS RECEIVING 16878
RFS RECEIVING 16853
RFS RECEIVING 16866
RFS RECEIVING 16868
RFS RECEIVING 16876
RFS RECEIVING 16856
RFS RECEIVING 16877
RFS RECEIVING 16858

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
RFS RECEIVING 16874
RFS RECEIVING 16871
RFS RECEIVING 16870
RFS RECEIVING 16879
RFS RECEIVING 16855
RFS RECEIVING 16859
RFS IDLE 18711
RFS RECEIVING 16873
RFS RECEIVING 16861
RFS RECEIVING 16875
RFS RECEIVING 16857

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
RFS RECEIVING 16867
RFS RECEIVING 16865
RFS RECEIVING 16864
RFS RECEIVING 16880
RFS RECEIVING 16854
MRP0 WAIT_FOR_LOG 16852

61 rows selected.

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

THREAD# MAX(SEQUENCE#)
---------- --------------
1 16851

SQL> select ds.dest_id id
2 , ad.status
3 , ds.database_mode db_mode
4 , ad.archiver type
5 , ds.recovery_mode
6 , ds.protection_mode
7 , ds.standby_logfile_count "SRLs"
8 , ds.standby_logfile_active active
9 , ds.archived_seq#
10 from v$archive_dest_status ds
11 , v$archive_dest ad
12 where ds.dest_id = ad.dest_id
13 and ad.status != 'INACTIVE'
14 order by
15 ds.dest_id
16 /


ID STATUS DB_MODE TYPE RECOVERY_MODE
---------- --------- --------------- ---------- -----------------------
PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
-------------------- ---------- ---------- -------------
1 VALID OPEN_READ-ONLY ARCH MANAGED REAL TIME APPLY
MAXIMUM PERFORMANCE 0 0 18710

2 VALID UNKNOWN LGWR IDLE
MAXIMUM PERFORMANCE 0 0 0

32 VALID UNKNOWN ARCH IDLE
MAXIMUM PERFORMANCE 0 0 16851


SQL> SQL> host
oracle@oracle01[home/oracle]hysdev$ ps -ef|grep mrp
oracle 20660 1 0 00:27 ? 00:00:00 ora_mrp0_hysdev


Last night the archivelog applied was sequence 15100, this morning, it was 16851, which is extremely slow.

dgmgrl results:
DGMGRL> show database hysdev

Database - hysdev

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 1 day(s) 4 hours 40 minutes 52 seconds
Apply Lag: 1 day(s) 4 hours 40 minutes 53 seconds
Real Time Query: ON
Instance(s):
hysdev

Database Status:
SUCCESS


I don't understand why it is so slow, and wonder if any archivelogs are skipped? or is the FRA area in prod is almost full?

Please help. Thanks in advance.