Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
oracle standby server not in sync with the primary server
I opened standby on flashback mode, then turned off the flash back,
checking if the primary and the secondary are in syc using the following query showed:
I still have the archive logs on the standby server, and the standby still receiving the new archive logs from the primary, but doesn't seem to apply them within it's database.
I would appreciate any help on how to get those archive logs applied again without screwing anything or loosing the current standby.
Thanks in advance.
checking if the primary and the secondary are in syc using the following query showed:
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (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#;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 137288 37 137251
I still have the archive logs on the standby server, and the standby still receiving the new archive logs from the primary, but doesn't seem to apply them within it's database.
I would appreciate any help on how to get those archive logs applied again without screwing anything or loosing the current standby.
Thanks in advance.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Physical or logical standby?
Was managed recovery started?
Of course the biggest question would be is log 38 there?
Realistically, you are looking at over 130,000 archives. It would probably be faster to recreate the standby than to apply all those logs. I can't imagine you have the storage for that many archives.
Was managed recovery started?
Of course the biggest question would be is log 38 there?
Realistically, you are looking at over 130,000 archives. It would probably be faster to recreate the standby than to apply all those logs. I can't imagine you have the storage for that many archives.
we have a physical standby
I see this error under alert.log ( many times because I used flashback_on and flashback_of manythimes
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 35-137288
........................
......................
ORA-16037: user requested cancel of managed recovery operation
if the logs exist: no, archive logs from 38-to 137110 !!!!!!!!!!!
I see this error under alert.log ( many times because I used flashback_on and flashback_of manythimes
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 35-137288
........................
......................
ORA-16037: user requested cancel of managed recovery operation
if the logs exist: no, archive logs from 38-to 137110 !!!!!!!!!!!
You would have to restore all the archives that are missing and then apply them. If you even have all those archives around somewhere.
If they don't exist on a backup somewhere, your only choice is to rebuild the standby.
Like I said originally, even if you have all the archives, rebuilding would be faster anyway. I cannot imagine that a full restore would be slower than applying that many logs.
If they don't exist on a backup somewhere, your only choice is to rebuild the standby.
Like I said originally, even if you have all the archives, rebuilding would be faster anyway. I cannot imagine that a full restore would be slower than applying that many logs.
doing more search I found that
on the standby: I have archive logs for the day I used flash back like this:
from sq#137001(Jul 29 00:08) to sq#137108 (jul 29 19:54) exist on both primary and secondary
then at Jul 29 19:59 (arround the time I opened the standby) archivelog started from sq#1 =>sq#37
(this doesn't exist on the primary)
then Jul 29 20:53(around the time I closed the standby) sq#137111 => sq#137123 ( which des exist on the primay) just missing sq#137109 and sq#13710
from that day, I do receive all the archive logs, but doesn't get applied
what can I do, knowing on the flash back did truncate a table that I truncated after on primary)
see result of the following query executed on the standby
result starts from sq#137111 to sq#137304(my last sq on both primary and standby) with APPLIED =NO for all of the returned rows
hope this helps to give me an idea on how to fix the issue!!
on the standby: I have archive logs for the day I used flash back like this:
from sq#137001(Jul 29 00:08) to sq#137108 (jul 29 19:54) exist on both primary and secondary
then at Jul 29 19:59 (arround the time I opened the standby) archivelog started from sq#1 =>sq#37
(this doesn't exist on the primary)
then Jul 29 20:53(around the time I closed the standby) sq#137111 => sq#137123 ( which des exist on the primay) just missing sq#137109 and sq#13710
from that day, I do receive all the archive logs, but doesn't get applied
what can I do, knowing on the flash back did truncate a table that I truncated after on primary)
see result of the following query executed on the standby
select sequence#,status,first_time, applied from v$archived_log;
SEQUENCE# S FIRST_TIM APP
---------- - --------- ---
137111 A 29-JUL-15 NO
137112 A 29-JUL-15 NO
137114 A 29-JUL-15 NO
137115 A 29-JUL-15 NO
137117 A 29-JUL-15 NO
........................
.........................
137303 A 31-JUL-15 NO
137304 A 31-JUL-15 NO
result starts from sq#137111 to sq#137304(my last sq on both primary and standby) with APPLIED =NO for all of the returned rows
hope this helps to give me an idea on how to fix the issue!!
I'm quite confused as to what you have done as you aren't listing out the steps. In the documentation, the only thing that I see that pertains to using flashback with a standby appears to convert the standby to the new primary.
Is it that you stopped managed recovery and need to restart it? That should be what you would normally do with the standby when bringing it up. The steps to do that are outlined many times in the documentation.
Is it that you stopped managed recovery and need to restart it? That should be what you would normally do with the standby when bringing it up. The steps to do that are outlined many times in the documentation.
I am not sure, I used a script that the previous dba was using to turn on the flashback to copy some data than turn off the flash back.
how to check if my manager recovery is started or stopped.
if stopped, is it safe to just turn it on?
how to check if my manager recovery is started or stopped.
if stopped, is it safe to just turn it on?
It should be safe to restart it. If it isn't, the database will certainly tell you.
As it seems you have things scripted, I would suspect that there is a script to restart the database that would include restarting managed recovery.
As it seems you have things scripted, I would suspect that there is a script to restart the database that would include restarting managed recovery.
can you please walk me through on how to :
* check if the manager recovery is tarted or not
* if not how to start it and make it apply the archive logs starting with a specific one ( since I am copying the missing archives from the primary to the standby)
* how to check if the data are in sync beside checking if the archive logs are applied or not?
* check if the manager recovery is tarted or not
* if not how to start it and make it apply the archive logs starting with a specific one ( since I am copying the missing archives from the primary to the standby)
* how to check if the data are in sync beside checking if the archive logs are applied or not?
The documentation is your friend.
How to start:
http://docs.oracle.com/cd/E11882_01/server.112/e41134/log_apply.htm#SBYDB00515
You do not need to tell it where to start. It always starts after the last log applied.
How to monitor:
http://docs.oracle.com/cd/E11882_01/server.112/e41134/manage_ps.htm#SBYDB00555
How to start:
http://docs.oracle.com/cd/E11882_01/server.112/e41134/log_apply.htm#SBYDB00515
You do not need to tell it where to start. It always starts after the last log applied.
How to monitor:
http://docs.oracle.com/cd/E11882_01/server.112/e41134/manage_ps.htm#SBYDB00555
when you start the managed recovery, does it increment applying the logs by sequence number? or by creation date?
also, is the standby needs to be on archivelog mode ?
also, is the standby needs to be on archivelog mode ?
SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
RFS IDLE
RFS IDLE
select process from v$managed_standby where process like 'MRP%';
no rows selected
SELECT PROCESS,STATUS,thread#,sequence#,block#,blocks
FROM V$MANAGED_STANDBY order by thread#,process;23:36:20 2
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_GAP 1 35 0 0
RFS IDLE 1 137864 79731 606
Premium Content
You need an Expert Office subscription to comment.Start Free Trial