assuming that this standby site is designated as second destination Setting the DB_BLOCK_CHECKING=FALSE parameter during standby or media recovery can provide as much as a twofold increase in the apply rate. Catalog independently investigate their credentials and experience, and not rely on The steps If the database is open in read-only mode, find Oracle the active sessions in the database and close the active sessions You need to take a quick look at the sync status between the primary (prod) and To prevent possible problems, you must be aware of events on the primary database that affect a standby database and learn how to respond to them. exact requirements that you must meet depend on your configuration. Add a new tablespace to the primary database: Archive the current online redo log file so the redo data will be transmitted to and applied on the standby database: Verify the new datafile was added to the primary database: Verify the new datafile was added to the standby database: This section shows how to add a new datafile to the primary and standby database when the STANDBY_FILE_MANAGEMENT initialization parameter is set to MANUAL. You can add or drop online redo log file groups or members to the primary database without affecting the standby database. To move or copy a set of tablespaces into a primary database when a physical standby is being used, perform the following steps: Generate a transportable tablespace set that consists of datafiles for the set of tablespaces being transported and an export file containing structural information for the set of tablespaces. their Oracle Once mounted, the database can receive archived redo data from the primary database. When a physical standby database receives a new branch of redo data, Redo Apply automatically takes the new branch of redo data. You can also
or have a suggestion for improving our content, we would appreciate your In some cases, the events or changes that occur on a primary database are automatically propagated through redo data to the standby database and thus require no extra action on the standby database. For example: You can issue the SQL DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES statement on the primary database to delete the datafiles on both the primary and standby databases. When a physical standby database is open, redo data from the primary database is received by the standby database, but the log files are not applied. To monitor the status of log apply services on a physical standby database, query the fixed views described in this section. Log on as a grid user and remove the old control files: Switch all existing data files to their image copies: This step concludes the standby refresh. The status of a tablespace changes (changes to read/write or read-only, placed online or taken offline). You can periodically open the standby database for read-only access and perform ad hoc queries to verify Redo Apply is updating the standby database correctly. Remote DBA Services Use the Feedback tab to make any comments or ask questions. Errata? Performance Tuning. you follow are identical to those that you followed for the primary cluster. logs from the primary site, but this process is very time-consuming. See Chapter 16, "Views Relevant to Oracle Data Guard" and Oracle Database Reference for additional information. You can alternate between having a physical standby database open for read-only access and performing Redo Apply using the following procedures. This section shows some samples of the types of views discussed in Section 8.5.2 for monitoring recovery progress in a Data Guard environment. To start a physical standby database, use SQL*Plus to connect to the database with administrator privileges, and then use either the SQL*Plus STARTUP or STARTUP MOUNT statement. To use this statement, the STANDBY_FILE_MANAGEMENT initialization parameter must be set to AUTO. This chapter describes how to manage physical standby databases. obviously, the standby isnt catching up because you have manually disabled the Sun Cluster Geographic Edition Data Replication Guide for Oracle Data Guard, Chapter1 Replicating Data With Oracle Data Guard Software, Initially Configuring Oracle Data Guard Software, 2010, Oracle Corporation and/or its affiliates. Server Ensure that in both the sites, you do not have archive logs later than 232 and When you perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause, the standby database is invalidated and might require substantial DBA administrative activities to repair. Redo data will be generated and applied at the standby site to plug the tablespace into the standby database. If the following query returns the MRP or MRP0 process, Redo Apply is running. It assumes that the oracle_sid is set correctly documentation was created as a support and Oracle training reference for use by our For example: On the primary database, after ensuring the standby database applied the redo information for the dropped tablespace, you can remove the datafile for the tablespace. Consequently, you will see error messages similar to the following in the alert log: Changing the size and number of the online redo log files is sometimes done to tune the database. Furthermore, set the database parameter PARALLEL_MAX_SERVERS to at least the degree of parallelism. For more information about transportable tablespaces, see Oracle Database Administrator's Guide. All legitimate Oracle experts You can then use this destination ID in a query on the primary database to discover log files that were not sent to a particular standby site. PricesHelp Re-create the physical standby database following the procedures in, Is missing intervening archived redo log files from the new branch of redo data. Block checking should be enabled on the primary database. The following steps describe how to rename a datafile in the primary database and manually propagate the changes to the standby database. Send the datafile containing the unlogged changes to the standby database. The following example shows output from the V$DATAGUARD_STATUS view on a primary database: The following example shows the contents of the V$DATAGUARD_STATUS view on a physical standby database: Consider using the following methods to optimize the time it takes to apply redo to physical standby databases. DATABASE; Figure 4.1 - Manual standby site, ensure that the managed recovery process is not However, the which helps in production disasters, data loss, or corruption.
In other cases, you might need to perform maintenance tasks on the standby database. Note:
Add standby redo logs to the standby database. the log_archive_dest_1 location on the standby site: Oracle Data GuardChapter 4 - Oracle instance Because the DB_BLOCK_CHECKING parameter is dynamic, it can be toggled without shutting down the standby database.
running. and the initialization file is in the $ORACLE_HOME/dbs directory. You can also monitor the standby database using the Oracle Enterprise Manager GUI. Database Support In contrast, global dynamic performance views (GV$ fixed views), store information about all open instances in a Real Applications Cluster (RAC). To change the standby database from being open for read-only access to performing Redo Apply: Terminate all active user sessions on the standby database. The following procedure works whether the STANDBY_FILE_MANAGEMENT initialization parameter is set to either MANUAL or AUTO, as follows: Drop the tablespace from the primary database: Make sure that Redo Apply is running (so that the change is applied to the standby database). To start manual recovery of a standby database, See Section 5.7.5 for more information. When configuring more than one standby database on the same system, consider using the DEPENDENCY attribute of the LOG_ARCHIVE_DEST_n initialization parameter to define one archival destination to receive redo data on behalf of all of the destinations, rather than transmitting redo data to each individual destination. Table 8-2 summarizes common events that occur on the primary database and pointers to the files and views where you can monitor these events on the primary and standby sites. Figure 8-1 Standby Database Open for Read-Only Access, Assessing Whether or Not to Open a Standby Database, Opening a Physical Standby Database for Read-Only Access.
Excel-DB, Oracle Database Tips by Donald BurlesonDecember 9, 2015. See Section 12.10 for information about recovering after the NOLOGGING clause is used. The standby alert log shows the following messages when recovering the archive: To correct the problems described in Section 22.214.171.124.1, perform the following steps: Create the raw slice on the standby database and assign permissions to the Oracle user.
The following statements start the manual All rights reserved by Asynchronous I/O should significantly reduce database file parallel reads and should improve overall recovery time. Scripts At some point, you need to resume Redo Apply on the standby database, and apply the archived redo log files to resynchronize the standby database with the primary database. State These view names are prefixed with either V$ or GV$, for example, V$ARCHIVE_DEST or GV$ARCHIVE_DEST. The Oracle of PortalApp Database Support Issue the following query to show information about the protection mode, the protection level, the role of the database, and switchover status: Issue the following query to show information about fast-start failover: Query the physical standby database to monitor Redo Apply and redo transport services activity at the standby site. For example, you can execute the following SQL*Plus statement: The previous query output shows three archived redo log files received from the primary database. If you do not specify the STANDBY_FILE_MANAGEMENT initialization parameter or if you set it to MANUAL, then you must manually copy the new datafile to the standby database when you add a datafile to the primary database. You must set the STANDBY_FILE_MANAGEMENT initialization parameter to MANUAL when the standby datafiles reside on raw devices. The standby alert log shows the following: However, if the raw device was created on the primary system but not on the standby, then the MRP process will shut down due to file-creation errors. The primary database has diverged from the standby on the indicated primary database branch. This section gives you a general overview on where to find information for monitoring the primary and standby databases in a Data Guard environment. The V$ARCHIVE_DEST_STATUS view on either a primary or standby database site provides you information such as the online redo log files that were archived, the archived redo log files that are applied, and the log sequence numbers of each. See Section 6.3 for information about Redo Apply, Section 6.2.1 for information about real-time apply, and Section 8.2 for information about opening a physical standby database for read-only or read/write access. Query the V$ARCHIVED_LOG view on the standby database to find additional information about the archived redo log. For example, based on your business requirements, you might: Configure two physical standby databases with one standby database always performing Redo Apply to be as current as possible with the primary database and the other standby database open in read-only mode during business hours for reporting purposes. SupportApps To find out which log files were not received by this standby destination, issue the following query on the primary database: The preceding example shows the log files that were not received by standby destination 2. The PARALLEL_EXECUTION_MESSAGE_SIZE parameter is also used by parallel query operations and should be tested with any parallel query operations to ensure there is sufficient memory on the system. Remote Consulting StaffConsulting It contains the following examples: Determining the Location and Creator of the Archived Redo Log Files, Viewing Database Incarnations Before and After OPEN RESETLOGS, Determining Which Log Files Were Applied to the Standby Database, Determining Which Log Files Were Not Received by the Standby Site. If Redo Apply is running, cancel it as shown in the following example: When a standby database is open for read-only access, users can query the standby database but cannot update it. Just a few more steps to go!
This section describes the SQL*Plus statements used to start up and shut down a physical standby database.
Restore the STANDBY_FILE_MANAGEMENT initialization parameter and the Redo Apply options to their original states. For example: Query the V$DATABASE_INCARNATION view on the standby database to monitor database incarnations and the RESETLOGS_ID column. To set up this scenario, I manually removed some of the archive logs from the Wanted! To rename the datafile in the primary database, take the tablespace offline: Exit from the SQL prompt and issue an operating system command, such as the following UNIX mv command, to rename the datafile on the primary system: Rename the datafile in the primary database and bring the tablespace back online: Connect to the standby database, query the V$ARCHIVED_LOG view to verify all of the archived redo log files are applied, and then stop Redo Apply: Rename the datafile at the standby site using an operating system command, such as the UNIX mv command: Rename the datafile in the standby control file. To relieve the bottleneck, use native asynchronous I/O and set the database parameter DISK_ASYNCH_IO to TRUE (the default). Set DB_BLOCK_CHECKING=FALSE for Faster Redo Apply Rates. Data Guard Recovery Process. Solving Together.Learn more at Rackspace.com. With parallel media recovery, these data blocks are subsequently distributed evenly to all recovery processes to be read into the buffer cache. To quickly determine the level of synchronization for the standby database, issue the following query on the physical standby database: The previous query output shows that the standby database is two archived redo log files behind the primary database. For example: Each archive destination has a destination ID assigned to it. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database. SupportAnalysisDesignImplementationOracle The DB_BLOCK_CHECKSUM=TRUE (the default) should be enabled for both production and standby databases.
End-to-End Multicloud Solutions. Copy the datafiles to the standby database. incremental backup of your production environment saves a considerable amount of Then do the same manual log switches to generate some archive logs, Tips Synchronize changes on the standby database. Burleson Configure a physical standby database to maintain a copy of the primary database for disaster recovery purposes and also configure a logical standby database to off-load reporting tasks that require access to the latest data from the primary database. The Oracle database contains a set of underlying views. Rename the datafile on the standby database. You can specify the SQL ALTER DATABASE or SQL ALTER TABLESPACE statement with the FORCELOGGING clause to override the NOLOGGING setting. Notice that the standby experience!
Query the V$LOG_HISTORY fixed view on the physical standby database to show all the archived redo log files that were applied: The previous query output shows that the most recently applied archived redo log file was sequence number 945. Performance Tuning Increasing this parameter requires more memory from the shared pool by each parallel execution slave process. In the standby alert log you should see information similar to the following: On the standby database, set STANDBY_FILE_MANAGEMENT to AUTO and restart Redo Apply: At this point Redo Apply uses the new raw device datafile and recovery continues. As long as the Oracle instance Figure 8-1 shows a standby database open for read-only access. The following query output shows the standby database is two archived redo log files behind in applying the redo data received from the primary database. You can use a physical standby database for reporting purposes or as a clone database while also maintaining the ability to complete a failover or switchover quickly if you configure more than one standby database on the standby system. The topics in this chapter describe how to use SQL statements, initialization parameters, and views to manage physical standby databases. For example, if the primary database has 10 online redo log files and the standby database has 2, and then you switch over to the standby database so that it functions as the new primary database, the new primary database is forced to archive more frequently than the original primary database. Set this parameter on both the primary and standby databases in preparation for switchover operations. You can query the DEST_ID column in the V$ARCHIVE_DEST fixed view to find out your destination ID. To open a standby database for read-only access when it is currently shut down: Start, mount, and open the database for read-only access using the following statement: To open a standby database for read-only access when it is currently performing Redo Apply: You do not need to shut down the instance to open it for read-only access. Delete the corresponding datafile on the standby system after the archived redo log file was applied to the standby database. to DEFER. e-mail: Burleson Consulting This Oracle
Control is not returned to the session that initiates a database shutdown until shutdown is complete. standby destination. The datafiles must be copied in a directory defined by the DB_FILE_NAME_CONVERT initialization parameter. advertisements and self-proclaimed expertise. 911RAC Data Guard allows recovery on a physical standby database to continue after the primary database has been opened with the RESETLOGS option. location of the archived logs can be specified in the RECOVER Note that the STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL. Locate and register missing archived redo log files from the previous branch. The alert log also provides pointers to the trace or dump files generated by a specific process. (Note that for distributed queries, you must first issue the ALTER DATABASE SET TRANSACTION READ ONLY statement before you can issue a query on the read-only database.). Oracle The following steps show the However, this statement will not repair an already invalidated database. DATABASE statement to override the location designated by the Standard dynamic performance views (V$ fixed views) store information about the local instance. Network bandwidth issues between primary and standby databases. See Oracle Database Backup and Recovery Advanced User's Guide for more information about database incarnations, recovering through an OPEN RESETLOGS operation, and Flashback Database. Feel free to ask questions on our Oracle forum. LinuxMonitoringRemote supportRemote This section describes these events and the recommended responses to these events. Is missing archived redo log files from the end of the previous branch of redo data. This manual intervention involves ensuring the raw devices exist before log apply services on the standby database recover the redo data that will create the new datafile.On the primary database, create a new tablespace where the datafiles reside in a raw device. Similarly, you can drop log file groups or members from the primary database without affecting your standby database. Anyone simulation, you need to delete the archive logs manually from the primary site. But you should not go for that option right away. On the Enable flashback on the standby database. If you now re-enable LOG_ARCHIVE_DEST_STATE_2, the standby automatically Copy the datafiles and the export file to the primary database. When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. If the archive logs are not located in the the primary site. At the same time, create the same raw device on the standby database. A large number of parallel query slaves on a 32-bit installation may reach memory limits and prohibit increasing the PARALLEL_EXECUTION_MESSAGE_SIZE from the default 2K (2048) to 4K.
If you did not set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, you must copy the new datafile to the standby database. the book: TuningEmergency Burleson Consulting on the primary database: ALTER SYSTEM SET Using an mount the database, and start recovery. The STANDBY_FILE_MANAGEMENT initialization parameter must be set to AUTO. The biggest bottlenecks encountered during recovery are read and write I/O.
Also, the archived logs are copied from the primary database onto Now, you need to re-enable LOG_ARCHIVE_DEST_STATE_2 (set to ENABLE): As expected, the standby cannot continue applying the logs because some of the When an ALTER DATABASE OPEN RESETLOGS statement is issued on the primary database, the incarnation of the database changes, creating a new branch of redo data. A standby database is basically the consistent copy of the production database, an excerpt from ServerOracle ConceptsSoftware SupportRemote To verify that deleted datafiles are no longer part of the database, query the V$DATAFILE view. If the MRP0 or MRP process exists, then the standby database is applying redo. Find the status of the managed recovery process using the Also, see Oracle Database Reference for complete reference information about views. To add an online redo log file, use a SQL statement such as this: To drop an online redo log file, use a SQL statement such as this: Repeat the statement you used in Step 3 on each standby database. Oracle the cluster-newyork cluster, you can now restore the If the managed recovery process is A datafile is added or tablespace is created when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.