PURPOSE
——-
The purpose of this document is;
a) How to verify whether there is a gap in the log transfer from the primary
to the standby database.
b) How to solve this problem.
The problem is that the primary and standby database are not able to operate
in managed recovery mode while the setup followed the Oracle reference manuals.
The cause is a gap in archives produced by primary database, the standby
database is waiting for these archive logs. It may be necessary to synchronize
the primary and standby database.
SCOPE & APPLICATION
——————-
Detection of the log gap primary and standby database.
a) Use a sql statement.
b) Check the alert files.
c) Check for fal server process.
d) Checking archive destinations.
Synchronize the primary and standby database.
a) Make the missing archives available for the standby database.
b) Recover the standby database automatic.
Check the log gap primary and standby database is solved.
a) Use a sql statement.
b) Check the alert files.
After correcting the log gap problem place the standby database in
sustained managed recovery mode as in Note:120855.1.
Detection of the log gap primary and standby database.
a) Using a sql statement.
———————–
To find out which logs have not been received by this standby destination,
issue the following query at the primary database:
SQL> SELECT local.thread#, local.sequence# from
(select thread#, sequence# from v$archived_log where dest_id=1) local
where
local.sequence# not in
(select sequence# from v$archived_log where dest_id=2 and
thread# = local.thread#);
THREAD# SEQUENCE#
———- ———-
1 521
1 522
1 523
1 524
1 525
Another possible statement to check this is (at the standby database):
SELECT thread#, low_sequence#, high_sequence#
from V$archive_gap;
Note: the sql statements give additional information (confirmation)
of information of alert files. If these sql statements return rows
it doesn’t necessarily mean ther’s an actual gap! Always check the
alert files as in 1b.
b) Check the alert files.
————————-
Alert file primary database:
Thu Jun 21 13:23:13 2001
ARC0: Beginning to archive log 1 thread 1 sequence 525
ARC0: Warning; LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC0: Transmitting activation ID 680595809 (28911161)
ARC0: Error 12541 connecting to standby host ‘ssym_nlsu22.world’
ARC0: Error 12541 Creating archive log file to ‘ssym_nlsu22.world’
ARC0: Completed archiving log 1 thread 1 sequence 525
Thu Jun 21 13:40:21 2001
Thread 1 advanced to log sequence 527
Current log# 1 seq# 527 mem# 0: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG1Oa
Current log# 1 seq# 527 mem# 1: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG2Oa
Thu Jun 21 13:40:21 2001
ARC0: Beginning to archive log 2 thread 1 sequence 526
ARC0: Warning; LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC0: Transmitting activation ID 680595809 (28911161)
ARC0: Completed archiving log 2 thread 1 sequence 526
Alert file standby:
$ tail -50 alert_ssy*|more
Thu Jun 21 13:34:53 2001
Media Recovery Start: Managed Standby Recovery
Successfully started datafile 1 media recovery
Datafile #1: ‘/ots0/app/oracle/product/9.0.1/dbs/sroo/backup/system09.dbf’
Media Recovery Log
Media Recovery Waiting for thread 1 seq# 522
Thu Jun 21 13:40:23 2001
Fetching gap sequence for thread 1, gap sequence 522-525
Trying FAL server:
Error fetching gap sequence, no FAL server specified
Thu Jun 21 13:40:38 2001
Failed to request gap sequence. Thread #: 1, gap sequence: 522-525
All FAL server has been attempted.
c) Fal server process.
——————-
A Fal server process is an Oracle server process running on the primary database
servicing fal request from a fal client. An example of a request of a client are
queueing requests to send archived redologfiles from a primary database to one or
more standby databases.
d) Checking archive destinations.
——————————
Checking the archive destination of the primary database:
/home/server/sroo/test
$ ls
arch_1_521.arc arch_1_523.arc arch_1_525.arc
arch_1_522.arc arch_1_524.arc arch_1_526.arc testsroo/
Checking the archive destination of the standby database:
/home/server/sroo/test/testsroo
$ ls
arch_1_526.arc
2 Synchronize the primary and standby database.
———————————————
a) Make the missing archives available for the standby database.
————————————————————-
=>Copy and register (if necessary) the missing archives from the
gap to a location that can be reached by the standby database that
is the standby_archive_dest.
$ pwd
/home/server/sroo/test
$ cp *521.arc testsroo
$ cp *522.arc testsroo
$ cp *523.arc testsroo
$ cp *524.arc testsroo
$ cp *525.arc testsroo
SQL> alter database register logfile ‘/home/server/sroo/test/testsroo/arch_1_521.arc’;
<repeat for all copied ArchiveLogs>
b) Recover the standby database automatic.
—————————————
Cancel the managed recovery (check Note:120855.1):
after cancel the standby recovery session shows:
SQL> recover managed standby database;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
‘/ots0/app/oracle/product/9.0.0/dbs/sroo/backup/system09.dbf’
ORA-16037: user requested cancel of managed recovery operation
Then recover the standby database:
SQL> recover automatic standby database;
ORA-00279: change 61729 generated at 06/21/2001 13:40:21 needed for thread 1
ORA-00289: suggestion : /home/server/sroo/test/testsroo/arch_1_527.arc
ORA-00280: change 61729 for thread 1 is in sequence #527
ORA-00278: log file ‘/home/server/sroo/test/testsroo/arch_1_527.arc’ no longer
needed for this recovery
ORA-00308: cannot open archived log
‘/home/server/sroo/test/testsroo/arch_1_527.arc’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
=> cancel
Media recovery cancelled.
Set the standby database back to work:
SQL> recover managed standby database;
3 Check the log gap primary and standby database is solved.
a) Using an sql statement.
———————–
To find out which logs have not been received by this standby destination, issue
the following query at the primary database:
SQL> SELECT local.thread#, local.sequence# from
(select thread#, sequence# from v$archived_log where dest_id=1) local
where
local.sequence# not in
(select sequence# from v$archived_log where dest_id=2 and
thread# = local.thread#);
=> no rows selected => OK!
Note: Even if this sql statement returns rows synchronisation
may be succesfull – always check alert files (next item).
b) Checking the alert files.
————————-
Check alert files if archives are now picked up by standby database:
Checking alert file primary:
$ tail -50 alert_sy*
Thu Jun 21 13:23:13 2001
ARC0: Beginning to archive log 1 thread 1 sequence 525
ARC0: Warning; LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC0: Transmitting activation ID 680595809 (28911161)
ARC0: Error 12541 connecting to standby host ‘ssym_nlsu22.world’
ARC0: Error 12541 Creating archive log file to ‘ssym_nlsu22.world’
ARC0: Completed archiving log 1 thread 1 sequence 525
Thu Jun 21 13:40:21 2001
Thread 1 advanced to log sequence 527
Current log# 1 seq# 527 mem# 0: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG1Oa
Current log# 1 seq# 527 mem# 1: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG2Oa
Thu Jun 21 13:40:21 2001
ARC0: Beginning to archive log 2 thread 1 sequence 526
ARC0: Warning; LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC0: Transmitting activation ID 680595809 (28911161)
ARC0: Completed archiving log 2 thread 1 sequence 526
Thu Jun 21 13:56:37 2001
LGWR: Transmitting activation ID 680595809 (28911161)
LGWR: Beginning to archive log 2 thread 1 sequence 528
Thread 1 advanced to log sequence 528
Current log# 2 seq# 528 mem# 0: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG3Oa
Current log# 2 seq# 528 mem# 1: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG4Oa
Thu Jun 21 13:56:38 2001
ARC0: Beginning to archive log 1 thread 1 sequence 527
ARC0: Warning; LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC0: Completed archiving log 1 thread 1 sequence 527
Checking alert file standby:
$ tail -50 alert_ssy*
Thu Jun 21 13:54:45 2001
ALTER DATABASE RECOVER automatic standby database
Thu Jun 21 13:54:45 2001
Media Recovery Start
Successfully started datafile 1 media recovery
Datafile #1: ‘/ots0/app/oracle/product/9.0.1/dbs/sroo/backup/system09.dbf’
Media Recovery Log
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_522.arc
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_523.arc
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_524.arc
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_525.arc
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_526.arc
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_527.arc
ORA-279 signalled during: ALTER DATABASE RECOVER automatic standby database…
Thu Jun 21 13:55:01 2001
ALTER DATABASE RECOVER CANCEL
Media Recovery Cancelled
Completed: ALTER DATABASE RECOVER CANCEL
Thu Jun 21 13:56:00 2001
ALTER DATABASE RECOVER managed standby database
Thu Jun 21 13:56:00 2001
Media Recovery Start: Managed Standby Recovery
Successfully started datafile 1 media recovery
Datafile #1: ‘/ots0/app/oracle/product/9.0.1/dbs/sroo/backup/system09.dbf’
Media Recovery Log
Media Recovery Waiting for thread 1 seq# 527
Thu Jun 21 13:56:45 2001
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_527.arc
Media Recovery Waiting for thread 1 seq# 528
=> OK!
Comentários