Feb 4, 2016 - Resolving missing archive log gap at Standby Database. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL.
![Resolving missing archive log gap at standby database in sql Resolving missing archive log gap at standby database in sql](/uploads/1/2/5/3/125351064/892119312.jpg)
Due to reasons outside the scope of this question, we had to flashback our Logical Standby Database (at the VM level) to the previous day. So, now i'm trying to resolve gaps in missing archivelogs between the primary and the Logical Standby Databases. Standby: SQL select.
from gv$logstdbyprocess where statuscode!= 16116; READER ORA-16240: Waiting for log file (thread# 1, sequence# 54888) Sequence 54888 no longer exists in the Logical Standby (due to the flashback), and since we take a nightly archive log backup, this sequence no longer exists as an archived log on the Primary. However, I verified that this sequence is in the archived log backup on the Primary. If you can see the backup file in the directory, try cataloging it: RMAN catalog starts with '/path/to/archive/directory'; It will list the backup and ask you if you want to catalog it. Following which you should be able to continue with your gap recovery: alter database recover managed standby database cancel; recover standby database; #after all existing logs are applied put standby database to recover managed mode recover managed standby database If you don't have the archived logs anymore, you can do an incremental backup with: 'backup incremental until scn'.
B Although Oracle Corporation recommends that you use the managed recovery mode for your physical standby databases, you can also use manual recovery mode. You might choose manual recovery mode for any of the following reasons:. Manual recovery mode allows you to have more than 10 standby databases, which is the limit with managed recovery operations. If you do not want to connect to the primary and standby databases over the Oracle Net network, then open the database in manual recovery mode. If you are performing managed recovery operations and, for some reason, managed recovery operations no longer work, you can change to manual recovery mode. This appendix explains how to work in manual recovery mode.
It includes the following topics:. B.1 summarizes the basic tasks for setting up a standby database in preparation for manual recovery. This procedure assumes that you plan to connect to the standby database through Oracle Net. If you do not want to use Oracle Net to connect to the standby database, skip steps 4 and 5.
Table B-1 Task List: Preparing for Manual Recovery Step Task Procedure 1 Either make a new backup of the primary database datafiles or access an old backup. 2 Connect to the primary database and create the standby control file.
3 Prepare and copy the backup datafiles and standby control file from the primary site to the standby site. And 4 If you want to create an Oracle Net connection to the standby database, create a service name. And in 5 If you want to create an Oracle Net connection to the standby database, configure the listener on the standby site so that it can receive requests for connections to the standby instance. 6 Create the standby initialization parameter file on the standby site and set the initialization parameters for the standby database. Optionally, set DBFILENAMECONVERT and LOGFILENAMECONVERT to automatically rename primary files in the standby control file. 7 Start the standby instance and mount the standby database.
8 While connected to the standby database, manually change the names of the primary datafiles and redo logs in the standby control file for all files not automatically renamed using DBFILENAMECONVERT and LOGFILENAMECONVERT in step 6. If step 6 renamed all files, skip this step. B.2 After you have started and mounted the standby database, you can place it in manual recovery mode. To keep the standby database current, you must manually apply archived redo logs from the primary database to the standby database. Shows a database in manual recovery mode. Figure B-1 Standby Database in Manual Recovery Mode This section contains the following topics:.
B.2.1 Initiating Manual Recovery Mode Archived redo logs arrive at the standby site in one of the following ways:. The primary database automatically archives the logs (only if you implement a Data Guard environment). You manually transfer logs using an operating system utility or some other means. The standby database assumes that the archived log group is in the location specified by either of the following parameters in the standby initialization parameter file:. First valid disk location specified by LOGARCHIVEDEST n (where n is an integer from 1 to 10).
Location specified by LOGARCHIVEDEST n If the archived logs are not in the location specified in the initialization parameter file, you can specify an alternative location using the FROM option of the RECOVER statement. To place the standby database in manual recovery mode.
Use SQL.Plus to connect to the standby instance and then start the Oracle instance at the standby database. For example, enter: STARTUP NOMOUNT pfile=initSTANDBY.ora. Mount the standby database: ALTER DATABASE MOUNT STANDBY DATABASE;. If log transport services are not archiving logs automatically to the standby site, then manually copy the logs to the desired location on the standby site using an appropriate operating system utility for transferring binary data. For example, enter:% cp /oracle/arcdest/.arc /standby/arcdest.
Issue a RECOVER statement to place the standby database in manual recovery mode. Note: Specify the FROM 'location' option only if the archived log group is not in the location specified by the LOGARCHIVEDEST n parameter (where n is an integer from 1 to 10) or the LOGARCHIVEDEST n parameter in the standby initialization parameter file.
For example, execute one of the following statements: RECOVER STANDBY DATABASE # uses location for logs specified in # initialization parameter file RECOVER FROM '/logs' STANDBY DATABASE # specifies nondefault location As the Oracle database server generates archived redo logs, you must continually copy and apply them to the standby database to keep it current. B.2.2 When Is Manual Recovery Required?
![Resolving Resolving](/uploads/1/2/5/3/125351064/509278882.gif)
Manual recovery mode is required in a non-Data Guard environment. A non-Data Guard environment is one in which you manually:. Transfer the archived redo logs from the primary site to the standby site. Apply the archived redo logs to the standby database Even if you implement a Data Guard environment, you might occasionally choose to perform manual recovery on the standby database. For example, you might choose to manually resolve an existing archive gap by using manual recovery mode.
B.3 An archive gap is a range of archived redo logs created whenever you are unable to apply the next archived redo log generated by the primary database to the standby database. This section contains the following topics:. Note: Typically, archive gaps are resolved automatically without the need for manual intervention. See for more information about how log apply services automatically recover from gaps in the redo logs.
B.3.1 What Causes Archive Gaps? An archive gap can occur whenever the primary database archives a log, but the log is not archived to the standby site. Because the standby database requires the sequential application of redo logs, media recovery stops at the first missing log encountered. Archive gaps can occur in the following situations:. B.3.1.1 Creation of the Standby Database One example of an archive gap occurs when you create the standby database from an old backup. For example, if the standby database is made from a backup that contains changes through log 100, and the primary database currently contains changes through log 150, then the standby database requires that you apply logs 101 to 150.
Another typical example of an archive gap occurs when you generate the standby database from a hot backup of an open database. For example, assume the scenario illustrated in. Figure B-2 Manual Recovery of Archived Logs in an Archive Gap The following steps occur:. You take a hot backup of database primary.
At time t, while you are busy configuring the network files, primary archives log sequences 4 and 5. At time t + 1, you start the standby instance. primary archives log sequences 6, 7, and 8 to both the primary site and the standby site. Archived log sequences 4 and 5 are now part of an archive gap, and these logs must be applied to the standby database.
B.3.1.2 Shutdown of the Standby Database When the Primary Database Is Open You might be required to shut down the standby database to resolve maintenance issues. For example, you must shut down the standby database when you change a control file parameter, such as MAXDATAFILE, in the primary database. To avoid creating archive gaps, follow these rules:. Start the standby databases and listeners before starting the primary database. Shut down the primary database before shutting down the standby database. If you violate either of these two rules, then the standby database is down while the primary database is open and archiving.
Consequently, the Oracle database server can create an archive gap. Note: If the standby site is specified as MANDATORY in one of the LOGARCHIVEDEST n parameters of the primary initialization parameter file, dynamically change it to OPTIONAL before shutting down the standby database. Otherwise, the primary database eventually stalls because it cannot archive its online redo logs. B.3.1.3 Network Failure Preventing the Archiving of Logs to the Standby Site If you maintain a Data Guard environment, and the network goes down, the primary database might continue to archive to disk but be unable to archive to the standby site. In this situation, archived logs accumulate as usual on the primary site, but the standby instance is unaware of them.
To prevent this problem, you can specify that the standby destination have mandatory status. If the archiving destination is mandatory, then the primary database will not archive any logs until it is able to archive to the standby site. For example, you can set the following in the primary initialization parameter file to make standby1 a mandatory archiving destination: LOGARCHIVEDEST2 = 'SERVICE=standby1 MANDATORY' One consequence of this configuration is that unless the network problem is fixed, the primary database eventually stalls because it cannot switch into an unarchived online redo log.
This problem is exacerbated if you maintain only two online redo logs in your primary database. See Also:. for a detailed account of the significance of the OPTIONAL and MANDATORY attributes for standby archival. for a related scenario B.3.2 Determining If an Archive Gap Exists To determine if there is an archive gap, query the V$ARCHIVEDLOG and V$LOG views.
If an archive gap exists, the output of the query specifies the thread number and log sequence number of all logs in the archive gap. If there is no archive gap for a given thread, the query returns no rows.
To identify the logs in the archive gap Query the V$ARCHIVEDLOG and V$LOG views on the standby database. See Also: for additional information B.4 Sometimes all of the primary datafiles and redo logs cannot be renamed in the standby control file by conversion parameters.
For example, assume that your database has the following datafiles, which you want to rename as shown in the following table: Primary Filename Standby Filename /oracle/dbs/df1.dbf /standby/df1.dbf /oracle/dbs/df2.dbf /standby/df2.dbf /data/df3.dbf /standby/df3.dbf You can set DBFILENAMECONVERT as follows to convert the filenames for the first two datafiles: DBFILENAMECONVERT = '/oracle/dbs', '/standby' Nevertheless, this parameter will not capture the renaming of /data/df3.dbf.