Oracle DBA面试笔试之高可用DG灾备系列
👉 本文共约5667个字，系统预计阅读时间或需22分钟。
本页目录 隐藏
在Oracle中，什么是DG？DG有哪些优缺点？
在Oracle中，请简单描述DG的架构。
在Oracle中，有哪几种类型的DG？
在Oracle中，搭建物理DG需要注意的地方有哪些？
在Oracle中，将一个物理DG转换为逻辑DG有哪些步骤呢？
⊙ 在Oracle中，将一个物理DG转换为逻辑DG有哪些步骤呢？
在Oracle中，物理DG和快照DG相互转换的步骤有哪些？
⊙ 在Oracle中，物理DG和快照DG相互转换的步骤有哪些？
在Oracle的DG中，RFS、LNSn、MRP、LSP进程的作用分别是什么？
⊙ 在Oracle的DG中，RFS、LNSn、MRP、LSP进程的作用分别是什么？
在Oracle的DG中，有哪些重要的参数，它们分别代表什么含义？
⊙ 在Oracle的DG中，有哪些重要的参数，它们分别代表什么含义？
在Oracle中，DG有哪3种保护模式？
在Oracle的DG中，Switchover和Failover的区别有哪些？
⊙ 在Oracle的DG中，Switchover和Failover的区别有哪些？
在Oracle中，如果主库丢失归档文件，那么物理DG如何恢复？
⊙ 在Oracle中，如果主库丢失归档文件，那么物理DG如何恢复？
在Oracle中，备库数据文件异常，物理DG如何恢复？
在Oracle中，DG中GAP有几种解决方案？
在Oracle中，DG中常常用到的比较有用的性能视图有哪些？
⊙ 在Oracle中，DG中常常用到的比较有用的性能视图有哪些？
在Oracle中，物理DG维护中常用到的SQL语句有哪些？
⊙ 在Oracle中，物理DG维护中常用到的SQL语句有哪些？
在Oracle中，逻辑DG维护中常用到的SQL语句有哪些？
⊙ 在Oracle中，逻辑DG维护中常用到的SQL语句有哪些？
在Oracle中，RAC、DG和OGG的区别有哪些？
其它DG基础知识
https://www.xmmup.com/oracle-11g-dataguardjichuzhishi.html
1 2 3 4 5 6 7 8 9 10 11 12 | 一：请描述Oracle Data Guard优点有哪些; 二：请描述DG备库类型和特点; 三：请描述如何将物理备用数据库转换为快照备用数据库; 四：请描述如何将快照备用数据库转换为物理备用数据库; 五：请描述DG环境需要配置哪些参数; 六：请描述如果DG备库服务器要回收，如何关闭DG同步关系; 七：请描述在DG执行SWITCHOVER前需要注意什么; 八：请描述在DG主、备库都是二节点RAC的场景下，执行SWITCHOVER的过程; 九：请描述Oracle DG FAILOVER过程; 十：请描述Oracle DG GAP问题处理方法; 十一：请描述Oracle DG有哪些进程; 十二：请描述Oracle DG常用命令。 |
一：请描述Oracle Data Guard优点有哪些
1 2 3 | Summary of Oracle Data Guard Benefits Oracle Data Guard提供了一个高效、全面的灾难恢复和高可用性解决方案。 Oracle Data Guard provides an efficient and comprehensive disaster recovery and high availability solution. |
Oracle Data Guard提供以下好处：
Oracle Data Guard offers these benefits:
1.高可用性
1 2 3 | High availability Oracle Data Guard易于管理的switchover和failover功能允许在主数据库和备用数据库之间进行角色转换，从而最大限度地减少主数据库在计划内和计划外停机时的停机时间。 Oracle Data Guard’s easy-to-manage switchover and failover capabilities allow role reversals between primary and standby databases, minimizing the downtime of the primary database for planned and unplanned outages. |
2.完整的数据保护
1 2 3 4 5 6 7 8 9 10 11 12 13 | Complete data protection Oracle Data Guard可以确保零数据丢失，即使在遇到意外情况时也是如此。 Oracle Data Guard can ensure zero data loss, even in the face of unforeseendisasters. 备用数据库可防止所有类型的计划外停机，包括数据损坏和管理错误。 A standby database provides a safeguard against unplanned outages of all types, including data corruption and administrative error. 因为从主数据库接收的重做数据在备用数据库中进行验证，所以主数据库中可能发生的物理损坏不会传播到备用数据库。 Because the redo data received from a primary database is validated at a standby database, physical corruptions that can occur at a primary database are not propagated to the standby database. 在备用数据库上执行的附加验证还可以防止逻辑块内损坏和丢失写入损坏传播到备用数据库。 Additional validation performed at a standby database also prevents logical intra-block corruptions and lost-write corruptions from propagating to the standby. 类似地，管理错误（如存储管理员意外删除文件）不会传播到备用数据库。 Similarly, administrative errors such as accidental file deletions by a storage administrator are not propagated to a standby database. 物理备用数据库也可用于防止用户错误，方法是延迟重做应用，或使用闪回数据库倒回备用数据库并提取数据的良好副本。 A physical standby database can also be used to protect against user errors either by delaying the redo apply or by using Flashback Database to rewind the standby and extract a good copy of the data. |
3.有效利用系统资源
1 2 3 | Efficient use of system resources 使用从主数据库接收的重做数据更新的备用数据库表可用于其他任务，如备份、报告、汇总和查询，从而减少执行这些任务所需的主数据库工作负载，节省宝贵的CPU和I/O周期。 The standby database tables that are updated with redo data received from the primary database can be used for other tasks such as backups, reporting,summations, and queries, thereby reducing the primary database workloadnecessary to perform these tasks, saving valuable CPU and I/O cycles. |
4.数据保护的灵活性，以平衡可用性和性能要求
1 2 3 | Flexibility in data protection to balance availability against performance requirements Oracle Data Guard提供了最大保护、最大可用性和最大性能模式，帮助企业平衡数据可用性和系统性能需求。 Oracle Data Guard offers maximum protection, maximum availability, and maximum performance modes to help enterprises balance data availability against system performance requirements. |
5.自动gap检测和解决
1 2 3 4 5 6 7 8 9 | Automatic gap detection and resolution 如果主数据库和一个或多个备用数据库之间的连接丢失（例如，由于网络问题）， If connectivity is lost between the primary and one or more standby databases (for example, due to network problems), 这样，在主数据库上生成的重做数据就不能发送到那些备用数据库。 then redo data being generated on the primary database cannot be sent to those standby databases. 重新建立连接后，Oracle Data Guard会自动检测到丢失的存档重做日志文件（称为gap），然后将丢失的存档重做日志文件自动传输到备用数据库。 After a connection is reestablished, the missing archived redo log files (referred to as a gap) are automatically detected by Oracle Data Guard, which then automatically transmits the missing archived redo log files to the standby databases. 备用数据库与主数据库同步，无需DBA手动干预。 The standby databases are synchronized with the primary database, without manual intervention by the DBA. |
6.集中化、简单化管理
1 2 3 4 5 | Centralized and simple management Oracle Data Guard broker提供了图形用户界面和命令行界面，用于在DG配置中跨多个数据库自动化管理和操作任务。 代理还监视单个Oracle Data Guard配置中的所有系统。 The Oracle Data Guard broker provides a graphical user interface and a command-line interface to automate management and operational tasks across multiple databases in an Oracle Data Guard configuration. The broker also monitors all of the systems within a single Oracle Data Guard configuration. |
7.与Oracle数据库集成
1 2 3 | Integration with Oracle Database Oracle Data Guard是Oracle Database Enterprise Edition的一项功能，不需要单独安装。 Oracle Data Guard is a feature of Oracle Database Enterprise Edition and does not require separate installation. |
8.自动角色转换
1 2 3 | Automatic role transitions 启用快速启动故障切换后，如果主站点发生灾难，Oracle Data Guard broker将自动故障切换到同步备用站点，而无需DBA干预。此外，应用程序会自动通知角色转换。 When fast-start failover is enabled, the Oracle Data Guard broker automatically fails over to a synchronized standby site in the event of a disaster at the primary site, requiring no intervention by the DBA. In addition, applications are automatically notified of the role transition. |
二：请描述DG备库类型和特点
1 2 3 4 5 | Standby Database Types 备用数据库是Oracle生产数据库的事务一致性副本，它最初是从主数据库的备份副本创建的。 A standby database is a transactionally consistent copy of an Oracle production database that is initially created from a backup copy of the primary database. 一旦创建并配置了备用数据库，Oracle Data Guard就会通过将主数据库redo数据传输到备用系统来自动维护备用数据库，并将redo数据应用到备用数据库。 Once the standby database is created and configured, Oracle Data Guard automatically maintains the standby database by transmitting primary database redo data to the standby system, where the redo data is applied to the standby database. |
备用数据库可以是以下类型之一：物理备用数据库、逻辑备用数据库或快照备用数据库。
1 2 3 4 5 | A standby database can be one of these types: a physical standby database, a logical standby database, or a snapshot standby database. 如果需要，物理或逻辑备用数据库都可以充当主数据库的角色并接管生产处理。 If needed, either a physical or a logical standby database can assume the role of the primary database and take over production processing. Oracle Data Guard配置可以包括这些类型的备用数据库的任意组合。 An Oracle Data Guard configuration can include any combination of these types of standby databases. |
2.1 Physical Standby Databases
1 2 3 4 5 6 7 8 9 10 | 物理备用数据库是主数据库的精确逐块副本。 A physical standby database is an exact, block-for-block copy of a primary database. 物理备份通过一个称为Redo Apply的过程作为精确副本进行维护，在这个过程中，使用数据库恢复机制将从主数据库接收的Redo数据连续应用到物理备份数据库。 A physical standby is maintained as an exact copy through a process called Redo Apply, in which redo data received from a primary database is continuously applied to a physical standby database using the database recovery mechanisms. 可以打开物理备用数据库进行只读访问，并用于从主数据库卸载查询。 A physical standby database can be opened for read-only access and used to offload queries from a primary database. 如果购买了Oracle Active Data Guard选项的许可证，那么Redo Apply可以在物理备用数据库打开时处于活动状态，从而允许查询返回与从主数据库返回的结果相同的结果。 If a license for the Oracle Active Data Guard option has been purchased, Redo Apply can be active while the physical standby database is open, thus allowing queries to return results that are identical to what would be returned from the primary database. 此功能称为实时查询功能。 This capability is known as the real-time query feature. |
Physical Standby Database优点
1 2 | Benefits of a Physical Standby Database A physical standby database provides the following benefits: |
灾难恢复和高可用性
1 2 3 4 5 | Disaster recovery and high availability 物理备用数据库是一种健壮、高效的灾难恢复和高可用性解决方案。 A physical standby database is a robust and efficient disaster recovery and high availability solution. 易于管理的切换和故障切换功能允许在主数据库和物理备用数据库之间轻松进行角色转换，从而最大限度地减少主数据库因计划内和计划外停机而导致的停机时间。 Easy-to-manage switchover and failover capabilities allow easy role reversals between primary and physical standby databases, minimizing the downtime of the primary database for planned and unplanned outages. |
数据保护
1 2 3 4 5 6 7 8 9 10 11 | Data protection 物理备用数据库可以防止数据丢失，即使是在遇到不可预见的灾难时。 A physical standby database can prevent data loss, even in the face of unforeseen disasters. 物理备用数据库支持所有数据类型，以及主数据库可以支持的所有DDL和DML操作。 A physical standby database supports all datatypes, and all DDL and DML operations that the primary database can support. 它还提供了防止数据损坏和用户错误的保护措施。 It also provides a safeguard against data corruptions and user errors. 主数据库上的存储级物理损坏不会传播到备用数据库。 Storage level physical corruptions on the primary database are not propagated to a standby database. 类似地，可以很容易地解决逻辑损坏或用户错误，否则会导致数据丢失。 Similarly, logical corruptions or user errors that would otherwise cause data loss can be easily resolved. |
减少主数据库工作负载
1 2 3 4 5 | Reduction in primary database workload Oracle Recovery Manager（RMAN）可以使用物理备用数据库从主数据库卸载备份，从而节省宝贵的CPU和I/O周期。 Oracle Recovery Manager (RMAN) can use a physical standby database to offload backups from a primary database, saving valuable CPU and I/O cycles. 当Redo Apply处于活动状态时，还可以查询物理备用数据库，这样可以将查询从主数据库卸载到物理备用数据库，从而进一步减少主数据库的工作负载。 A physical standby database can also be queried while Redo Apply is active, which allows queries to be offloaded from the primary to a physical standby, further reducing the primary workload. |
性能
1 2 3 | Performance 物理备用数据库使用的Redo Apply技术是保持备用数据库更新为主数据库所做更改的最有效机制，因为它使用绕过所有SQL级代码层的低级恢复机制应用更改。 The Redo Apply technology used by a physical standby database is the most efficient mechanism for keeping a standby database updated with changes being made at a primary database because it applies changes using low-level recovery mechanisms which bypass all SQL level code layers. |
2.2 Logical Standby Databases
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 逻辑备用数据库最初是作为主数据库的相同副本创建的，但以后可以更改为具有不同的结构。 A logical standby database is initially created as an identical copy of the primary database, but it later can be altered to have a different structure. 通过执行SQL语句更新逻辑备用数据库。 The logical standby database is updated by executing SQL statements. 逻辑备用数据库的灵活性使您可以升级Oracle数据库软件（修补程序集和新的Oracle数据库版本），并以滚动方式执行其他数据库维护，几乎没有停机。 The flexibility of a logical standby database lets you upgrade Oracle Database software (patch sets and new Oracle Database releases) and perform other database maintenance in rolling fashion with almost no downtime. 从Oracle Database 11g开始，临时逻辑数据库滚动升级过程也可以用于现有的物理备用数据库。 From Oracle Database 11g onward, the transient logical database rolling upgrade process can also be used with existing physical standby databases. Oracle Data Guard通过将日志文件中的数据转换为SQL语句，然后在逻辑备用数据库上执行SQL语句，自动将归档的重做日志文件或备用重做日志文件中的信息应用到逻辑备用数据库。 Oracle Data Guard automatically applies information from the archived redo log file or standby redo log file to the logical standby database by transforming the data in the log files into SQL statements and then executing the SQL statements on the logical standby database. 因为逻辑备用数据库是使用SQL语句更新的，所以它必须保持打开状态。 Because the logical standby database is updated using SQL statements, it must remain open. 尽管逻辑备用数据库是以读/写模式打开的，但其重新生成的SQL的目标表仅可用于只读操作。 Although the logical standby database is opened in read/write mode, its target tables for the regenerated SQL are available only for read-only operations. 在更新这些表时，它们可以同时用于其他任务，如报告、汇总和查询。 While those tables are being updated, they can be used simultaneously for other tasks such as reporting,summations, and queries. 逻辑备用数据库对数据类型、表类型以及DDL和DML操作类型有一些限制。 A logical standby database has some restrictions on data types, types of tables, and types of DDL and DML operations. 有关逻辑备用数据库上的数据类型和DDL支持的信息，请参见逻辑备用数据库上的数据类型和DDL支持。 See Data Type and DDL Support on a Logical Standby Database for information on data type and DDL support on logical standby databases. |
逻辑备用数据库的优点
1 2 3 4 5 | Benefits of a Logical Standby Database 逻辑备用数据库是高可用性（HA）的理想选择，同时还能提供数据恢复（DR）好处。 A logical standby database is ideal for high availability (HA) while still offering data recovery (DR) benefits. 与物理备用数据库相比，逻辑备用数据库提供了显著的附加HA好处： Compared to a physical standby database, a logical standby database provides significant additional HA benefits: |
最大限度地减少软件升级的停机时间
1 2 3 4 5 6 7 8 9 10 11 | • Minimizing downtime on software upgrades 逻辑备用数据库是以滚动方式升级Oracle Data Guard配置的理想选择。 A logical standby database is ideal for upgrading an Oracle Data Guard configuration in a rolling fashion. 逻辑待机可用于大大减少与应用补丁集和新软件版本相关的停机时间。 Logical standby can be used to greatly reduce downtime associated with applying patchsets and new software releases. 逻辑备用可以升级到新版本，然后切换为活动主版本。 A logical standby can be upgraded to the new release and then switched over to become the active primary. 这允许在旧的主设备转换为逻辑备用设备并应用补丁集时实现完全可用性。 This allows full availability while the old primary is converted to a logical standby and the patchset is applied. 逻辑备用程序为DBMS\u ROLLING PL/SQL包提供了底层平台，它提供的功能允许您在滚动升级和其他存储重组的上下文中使您的Oracle Data Guard配置高度可用。 Logical standbys provide the underlying platform for the DBMS_ROLLING PL/SQL package, which provides functionality that allows you to make your Oracle Data Guard configuration highly available in the context of rolling upgrades and other storage reorganization. |
最大限度地减少软件升级的停机时间
1 2 3 4 5 6 7 8 9 10 11 | • Support for reporting and decision support requirements 逻辑备用的一个关键好处是可以创建重要的辅助结构来优化报告工作量； A key benefit of logical standby is that significant auxiliary structures can be created to optimize the reporting workload; 可能对主服务器的事务响应时间产生禁止性影响的结构。 structures that could have a prohibitive impact on the primary's transactional response time. 逻辑备用可以将其数据物理地重新组织为具有不同分区的不同存储类型，具有许多不同的索引，创建和维护按需刷新物化视图，并且可以用于驱动数据多维数据集和其他OLAP数据视图的创建。 A logical standby can have its data physically reorganized into a different storage type with different partitioning, have many different indexes, have on-demand refresh materialized views created and maintained, and can be used to drive the creation of data cubes and other OLAP data views. 但是，逻辑备用数据库不允许对数据进行任何转换（例如仅复制列的子集或允许在用户表上添加列）。 However, a logical standby database does not allow for any transformation of your data (such as replicating only a subset of columns or allowing additional columns on user tables). 对于这些类型的报告活动，Oracle GoldenGate是Oracle的首选解决方案。 For those types of reporting activities, Oracle GoldenGate is Oracle's preferred solution. |
2.3 Snapshot Standby Databases
1 2 3 4 5 6 7 8 9 10 11 12 | 快照备用数据库是一种可更新的备用数据库，为主数据库提供完整的数据保护。 A snapshot standby database is a type of updatable standby database that provides full data protection for a primary database. 快照备用数据库从其主数据库接收并存档（但不应用）重做数据。 A snapshot standby database receives and archives, but does not apply, redo data from its primary database. 在放弃对快照备用数据库的所有本地更新后，当快照备用数据库转换回物理备用数据库时，将应用从主数据库接收的重做数据。 Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database. 快照备用数据库随着时间的推移会与其主数据库分离，因为主数据库中的重做数据在接收时不会应用。 A snapshot standby database diverges from its primary database over time because redo data from the primary database is not applied as it is received. 对快照备用数据库的本地更新会导致其他分歧。 Local updates to the snapshot standby database cause additional divergence. 但是，主数据库中的数据是完全受保护的，因为可以随时将快照备用数据库转换回物理备用数据库，然后应用从主数据库接收的重做数据。 The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary is then applied. |
快照备用数据库的好处
1 2 3 4 5 | Benefits of a Snapshot Standby Database 快照备用数据库是完全可更新的备用数据库，它提供了与物理备用数据库类似的灾难恢复和数据保护好处。 A snapshot standby database is a fully updatable standby database that provides disaster recovery and data protection benefits that are similar to those of a physical standby database. 快照备用数据库最好用在这样的场景中：主数据库有一个临时的、可更新的快照，这样可以增加从主数据库故障中恢复的时间。 Snapshot standby databases are best used in scenarios where the benefit of having a temporary, updatable snapshot of the primary database justifies the increased time to recover from primary database failures. |
使用快照备用数据库的好处包括：
1 2 3 4 5 6 7 8 9 10 11 | The benefits of using a snapshot standby database include the following: •它为开发和测试的提供生产数据库的精确副本，同时始终保持数据保护。 • It provides an exact replica of a production database for development and testing purposes, while maintaining data protection at all times. 您可以使用Oracle Real Application Testing选项捕获主数据库工作负载，然后在snapshot备用服务器上重放它以进行测试。 You can use the Oracle Real Application Testing option to capture primary database workload and then replay it for test purposes on the snapshot standby. •通过转换为物理备用并重新同步，可以轻松刷新以包含当前生产数据。 • It can be easily refreshed to contain current production data by converting to a physical standby and resynchronizing. 创建快照备用、测试、与生产重新同步，然后再次创建快照备用和测试的能力是一个可以根据需要重复的周期。 The ability to create a snapshot standby, test, resynchronize with production, and then again create a snapshot standby and test, is a cycle that can be repeated as often as desired. 在需要对数据进行读/写访问的情况下，可以使用相同的过程轻松创建和定期更新备用快照，以便进行报告。 The same process can be used to easily create and regularly update a snapshot standby for reporting purposes where read/write access to data is required. |
三：请描述如何将物理备用数据库转换为快照备用数据库;
1 2 3 4 5 6 7 8 9 | Converting a Physical Standby Database into a Snapshot Standby Database These steps describe how to convert a physical standby database into a snapshot standby database. 1. Stop Redo Apply, if it is active. 2. Ensure that the database is mounted, but not open. 3. Ensure that a fast recovery area has been configured. It is not necessary for flashback database to be enabled. 4. Issue the following SQL statement to perform the conversion: SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; 5. Open the snapshot standby in read/write mode by issuing the following SQL statement: SQL> ALTER DATABASE OPEN READ WRITE; |
四：请描述如何将快照备用数据库转换为物理备用数据库;
1 2 3 4 5 6 7 | Converting a Snapshot Standby Database into a Physical Standby Database These steps describe how to convert a snapshot standby database into a physical standby database. 1. On an Oracle Real Applications Cluster (Oracle RAC) database, shut down all but one instance. 2. Ensure that the database is mounted, but not open. 3. Issue the following SQL statement to perform the conversion: SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Redo data received while the database was a snapshot standby database is automatically applied |
五：请描述DG环境需要配置哪些参数;
1 2 | 设置主数据库初始化参数 Set Primary Database Initialization Parameters |
1.DB_NAME
1 2 3 4 5 6 7 | 示例： DB_NAME=chicago 说明： 在主数据库上，指定创建数据库时使用的名称。 On a primary database, specify the name used when the database was created. 在物理备用数据库上，使用主数据库的DB_NAME名称。 On a physical standby database, use the DB_NAME of the primary database. |
2.DB_UNIQUE_NAME
1 2 3 4 5 6 7 | 示例： DB_UNIQUE_NAME=chicago 说明： 为每个数据库指定唯一的名称。 Specify a unique name for each database. 此名称将保留在数据库中，并且不会更改，即使主数据库和备用数据库反转角色也是如此。 This name stays with the database and does not change, even if the primary and standby databases reverse roles. |
3.LOG_ARCHIVE_CONFIG
1 2 3 4 5 6 7 | 示例： LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)' 说明： 必须在Oracle Data Guard配置中的每个数据库上显式设置此参数的DG_CONFIG属性，以启用完整的Oracle Data Guard功能。 The DG_CONFIG attribute of this parameter must be explicitly set on each database in an Oracle Data Guard configuration to enable full Oracle Data Guard functionality. 将DG_CONFIG设置为包含配置中每个数据库的DB_UNIQUE_NAME的文本字符串，该列表中的每个名称用逗号分隔。 Set DG_CONFIG to a text string that contains the DB_UNIQUE_NAME of each database in the configuration, with each name in this list separated by a comma. |
4.CONTROL_FILES
1 2 3 4 5 6 7 | 示例： CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl' 说明： 指定主数据库上控制文件的路径名。 Specify the path name for the control files on the primary database. 建议提供控制文件的第二个副本，以便在将好的控制文件复制到坏的控制文件位置后，可以轻松地重新启动实例。 It is recommended that a second copy of the control file is available so an instance can be easily restarted after copying the good control file to the location of the bad control file. |
5.LOG_ARCHIVE_DEST_1
1 2 3 4 5 | 示例： LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago' |
6.LOG_ARCHIVE_DEST_2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 示例： LOG_ARCHIVE_DEST_2= 'SERVICE=boston ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' 说明： LOG_ARCHIVE_DEST_n 指定重做数据在主系统和备用系统上的存档位置。 Specify where the redo data is to be archived on the primary and standby systems. • LOG_ARCHIVE_DEST_1 将主数据库生成的重做数据从本地联机重做日志文件存档到/arch1/chicago/中的本地存档重做日志文件。 archives redo data generated by the primary database from the local online redo log files to the local archived redo log files in arch1/chicago/. • LOG_ARCHIVE_DEST_2 仅对主角色有效。 is valid only for the primary role. 此目的地将重做数据传输到远程物理备用目的地。 This destination transmits redo data to the remote physical standby destination boston. Note: 如果已配置快速恢复区域（使用DB_RECOVERY_FILE_DEST参数），但您尚未使用local属性显式配置本地存档目标， If a fast recovery area was configured (with the DB_RECOVERY_FILE_DEST initialization parameter) and you have not explicitly configured a local archiving destination with the LOCATION attribute, Oracle Data Guard会自动使用LOG_ARCHIVE_DEST_1初始化参数（如果尚未设置）作为本地存档的默认目标。 Oracle Data Guard automatically uses the LOG_ARCHIVE_DEST_1 initialization parameter (if it has not already been set) as the default destination for local archiving. 另外，有关完整的日志存档目标信息，请参阅日志存档目标参数属性。 Also,see LOG_ARCHIVE_DEST_n Parameter Attributes for complete LOG_ARCHIVE_DEST_n information. |
7.REMOTE_LOGIN_PASSWORDFILE
1 2 3 4 5 | 示例： REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 说明： 如果远程登录密码文件用于验证管理用户或重做传输会话，则必须将此参数设置为EXCLUSIVE或SHARED。 This parameter must be set to EXCLUSIVE or SHARED if a remote login password file is used to authenticate administrative users or redo transport sessions. |
8.LOG_ARCHIVE_FORMAT
1 2 3 4 5 | 示例： LOG_ARCHIVE_FORMAT=%t_%s_%r.arc 说明： 使用线程（%t）、序列号（%s）和resetlogs ID（%r）指定已存档重做日志文件的格式。 Specify the format for the archived redo log files using a thread (%t), sequence number (%s), and resetlogs ID (%r). |
下面显示了主数据库上的其他备用角色初始化参数。
1 2 3 | The following shows the additional standby role initialization parameters on the primary database. 这些参数在主数据库转换为备用角色时生效。 These parameters take effect when the primary database is transitioned to the standby role. |
1.FAL_SERVER
1 2 3 4 5 6 7 | 示例： FAL_SERVER=boston 说明： 指定FAL服务器的Oracle Net服务名称（通常这是以主角色运行的数据库）。 Specify the Oracle Net service name of the FAL server (typically this is the database running in the primary role). 当Chicago数据库以备用角色运行时，如果Boston无法自动发送丢失的日志文件，它将使用Boston数据库作为FAL服务器，从中获取（请求）丢失的存档重做日志文件。 When the Chicago database is running in the standby role, it uses the Boston database as the FAL server from which to fetch (request) missing archived redo log files if Boston is unable to automatically send the missing log files. |
2.DB_FILE_NAME_CONVERT
1 2 3 4 5 6 7 8 9 10 11 | 示例： DB_FILE_NAME_CONVERT='/boston/','/chicago/' 说明： 指定备用数据库数据文件的路径名和文件名位置，后跟主位置。 Specify the path name and filename location of the standby database data files followed by the primary location. 此参数将主数据库数据文件的路径名转换为备用数据文件路径名。 This parameter converts the path names of the primary database data files to the standby data file path names. 此参数仅用于转换物理备用数据库的路径名。 This parameter is used only to convert path names for physical standby databases. 此参数可以指定多对路径。 Multiple pairs of paths may be specified by this parameter. |
3.LOG_FILE_NAME_CONVERT
1 2 3 4 5 6 7 8 9 | 示例： LOG_FILE_NAME_CONVERT='/boston/','/chicago/' 说明： 指定备用数据库联机重做日志文件的位置，后跟主位置。 Specify the location of the standby database online redo log files followed by the primary location. 此参数将主数据库日志文件的路径名转换为备用数据库上的路径名。 This parameter converts the path names of the primary database log files to the path names on the standby database. 此参数可以指定多对路径。 Multiple pairs of paths may be specified by this parameter. |
4.STANDBY_FILE_MANAGEMENT
1 2 3 4 5 | 示例： STANDBY_FILE_MANAGEMENT=AUTO 说明： 设置为AUTO，这样当数据文件添加到主数据库或从主数据库中删除时，将自动对备用数据库进行相应的更改。 Set to AUTO so when data files are added to or dropped from the primary database, correspon |
六：请描述如果DG备库服务器要回收，如何关闭DG同步关系;
1.主库：检查当前参数值log_archive_dest_state_2、log_archive_dest_2、log_archive_config。
1 2 3 4 5 6 7 8 9 10 11 | su - oracle sqlplus as sysdba col name for a30 col value for a100 set line 200 select name,value from v$parameter where name in('log_archive_dest_state_2', 'log_archive_dest_2', 'log_archive_config'); NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------------- log_archive_dest_2 service=HCJCDB valid_for=(online_logfiles,primary_roles) db_unique_name=CJCDB compression=enable log_archive_dest_state_2 ENABLE log_archive_config dg_config=(XXCJCDB,CJCDB) |
2.主库：修改DG参数，停止DG同步关系
1 2 3 | alter system set log_archive_dest_state_2=defer; alter system set log_archive_dest_2=''; alter system set log_archive_config=''; |
3.检查修改后参数值
1 | select name,value from v$parameter where name in('log_archive_dest_state_2', 'log_archive_dest_2', 'log_archive_config'); |
4.停止备库
1 2 3 4 5 6 | export ORACLE_SID=CJCDB1 sqlplus as sysdba 检查会话确认无业务会话 select username,status,count(*) from gv$session group by username,status; 关闭数据库实例 shutdown immediate |
七：请描述在DG执行SWITCHOVER前需要注意什么;
1 2 3 4 5 6 7 8 9 10 11 12 | 1.数据库版本、补丁版本。 2.DG相关参数配置是否正确。 检查DB_UNIQUE_NAME、LOG_ARCHIVE_CONFIG、LOG_ARCHIVE_DEST_1、LOG_ARCHIVE_DEST_2、DB_FILE_NAME_CONVERT、LOG_FILE_NAME_CONVERT等参数。 3.检查最近半年监听连接信息，避免漏掉某个连接数据库的系统。 4.申请停机窗口。 5.操作系统相关配置 因为DG切换后，不仅仅是数据库的切换，同时也是操作系统，服务器，网络的切换，至少需要检查如下内容： (1)数据库服务器上操作系统用户是否一致。 (2)数据库服务器上操作系统crontab等计划任务是否一致。 (3)tnsnames是否一致。 (4)如有directory，检查是否一致。 (5)如有NAS挂载，检查是否一致。 |
八：请描述在DG主、备库都是二节点RAC的场景下，执行SWITCHOVER的过程;
1 查询主库状态
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | ssh 192.168.1.100 su - oracle export ORACLE_SID=cjcdb1 sqlplus / as sysdba SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM v$DATABASE; /* NOT ALLOWED 当前的数据库不是带有备用数据库的主数据库 PREPARING DICTIONARY 该逻辑备用数据库正在向一个主数据库和其他备用数据库发送它的重做数据，以便为切换做准备 PREPARING SWITCHOVER 接受用于切换的重做数据时，逻辑备用配置会使用它 RECOVERY NEEDED 备用数据库还没有接收到切换请求 SESSIONS ACTIVE 在主数据库中存在活动的SQL会话；在继续执行之前必须断开这些会话 SWITCHOVER PENDING 适用于那些已收到主数据库切换请求但是还没有处理该请求的备用数据库 SWITCHOVER LATENT 切换没有完成并返回到主数据库 TO LOGICAL STANDBY 主数据库已经收到了来自逻辑备用数据库的完整的字典 TO PRIMARY 该备用数据库可以转换为主数据库 TO STANDBY 该主数据库可以转换为备用数据库 */ |
2 停用备库2节点
1 2 3 4 5 | ssh 10.10.10.200 su - oracle sqlplus / as sysdba show parameter instance_name shutdown immediate |
3 停用主库2节点
1 2 3 4 | ssh 192.168.1.200 export ORACLE_SID=trnt2 sqlplus / as sysdba shutdown immediate |
4 主库验证GAP(不能大于3)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | ssh 192.168.1.100 export ORACLE_SID=cjcdb1 sqlplus as sysdba SELECT LOG_ARCHIVED - LOG_APPLIED + 1 LOGGAP FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID = 1 AND ARCHIVED = 'YES' AND RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)), (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 AND APPLIED = 'YES' AND RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)); |
5 主切备
1 2 3 4 5 6 7 8 9 10 11 | ssh 192.168.1.100 export ORACLE_SID=cjcdb1 sqlplus as sysdba SELECT trim(DATABASE_ROLE) DBROLE FROM v$DATABASE; alter system switch logfile; alter system flush SHARED_POOL; ---启动trace，方便问题排查 ALTER SYSTEM SET log_archive_trace=8191 sid='*'; ---执行命令后，原主库自动关闭实例 alter database commit to switchover to physical standby with session shutdown; startup mount |
6 备切主
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | ssh 10.10.10.100 su - oracle sqlplus as sysdba show parameter instance_name SELECT trim(DATABASE_ROLE) DBROLE FROM v$DATABASE; ALTER SYSTEM SET log_archive_trace=8191 sid='*'; ---执行命令后，原备库自动启动到mount alter database commit to switchover to primary with session shutdown; shutdown immediate; startup; set lin 200 pages 100 set lin 200 pages 100 col FLASHBACK_ON for a10 col current_scn for 99999999999999 col open_mode for a10 col SWITCHOVER_STATUS for a20 col PROTECTION_MODE for a20 select current_scn,protection_mode,database_role,force_logging,FLASHBACK_ON,open_mode,switchover_status from v$database; ---关闭trace ALTER SYSTEM SET log_archive_trace=0 sid='*'; |
7 新备库启动mrp
1 2 3 4 5 6 | ssh 192.168.1.100 export ORACLE_SID=cjcdb1 sqlplus as sysdba alter database open; ALTER SYSTEM SET log_archive_trace=0 sid='*'; recover managed standby database using current logfile disconnect from session; |
8 新备库启动节点2
1 2 3 | ssh 192.168.1.200 export ORACLE_SID=cjcdb1 startup |
9 新主库启动节点2
1 2 3 4 5 | ssh 10.116.7.88 export ORACLE_SID=trnt2 sqlplus / as sysdba show parameter instance_name startup |
10 新备库检查
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | ssh 192.168.1.100 export ORACLE_SID=cjcdb1 sqlplus as sysdba SELECT status from v$instance; set lin 200 pages 200 select process,status,thread#,sequence#,block#,blocks from v$managed_standby; set lin 200 pages 100 col FLASHBACK_ON for a10 col current_scn for 99999999999999 col open_mode for a10 col SWITCHOVER_STATUS for a20 col PROTECTION_MODE for a20 select current_scn, protection_mode, database_role, force_logging, FLASHBACK_ON, open_mode, switchover_status from v$database; |
11 新主库检查
1 2 3 4 | ssh 10.10.10.100 export ORACLE_SID=cjcdb1 sqlplus as sysdba SELECT status from v$instance; |
九：请描述Oracle DG FAILOVER过程;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | set lin 200 pages 100 set lin 200 pages 100 col FLASHBACK_ON for a10 col current_scn for 99999999999999 col open_mode for a10 col SWITCHOVER_STATUS for a20 col PROTECTION_MODE for a20 select current_scn,protection_mode,database_role,force_logging,FLASHBACK_ON,open_mode,switchover_status from v$database; alter database recover managed standby database cancel; alter database recover managed standby database finish; select current_scn,protection_mode,database_role,force_logging,FLASHBACK_ON,open_mode,switchover_status from v$database; alter database commit to switchover to primary with session shutdown; select current_scn,protection_mode,database_role,force_logging,FLASHBACK_ON,open_mode,switchover_status from v$database; ###mount alter database open; |
十：请描述Oracle DG GAP问题处理方法;
1 2 3 | 检查备库缺失的归档 SELECT * FROM V$ARCHIVE_GAP; SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 43968 AND 50948; |
场景1：检查主库是否还有这些归档
1 2 3 4 5 6 7 8 9 10 11 | 如果有日志文件未被传输或未被注册到备库，那么可以使用如下命令手工注册到备库。 下面的SQL语句可以生成物理和逻辑DG注册的SQL语句，日志号从1980到2000： 如果还有通常情况下会自动解决，也可以手动传输到备库，执行注册操作。 SELECT 'alter database register or replace logical logfile ''/arch/log_1_' || A || ' _666200636.arc'';' LOGICAL_DG, 'alter database register or replace logfile ''/arch/log_1_' || A || ' _666200636.arc'';' PHYSICAL_DG FROM (SELECT LEVEL A FROM DUAL CONNECT BY LEVEL <= 2000) WHERE A >= 1980; alter database register logfile 'XXXXX'; |
场景2：检查主库和备库没有这些归档
1 2 3 4 5 6 7 8 9 | 但是备份文件中有这些归档，可以通过备份恢复归档文件。 http://blog.itpub.net/29785807/viewspace-2729496/ 查看备库节点1本地归档日志，显示已经没有thread 1 sequence 99924-99925了。 rman target / list archivelog all; 查看控制文件记录的备份中是存在thread 1 sequence 99924-99925归档 list backup of archivelog all; 手动将缺失的归档restore restore archivelog sequence between 99924 and 99925 thread 1; |
场景3：主备库、备份中都没有这些归档
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | http://blog.itpub.net/29785807/viewspace-2147373/ (1)如果数据量很小，可以考虑重建备库； (2)如果数据量很大，可以使用Rman基于SCN的增量备份来修复GAP问题； 确定增量恢复的起始SCN号 select file#,checkpoint_change# from v$datafile order by checkpoint_change#; 主库:使用Rman基于SCN的增量备份，并将备份文件传输到备库 run { allocate channel c3 device type disk; backup as compressed backupset incremental from scn 1879904814 database format '/home/oracle/rman1116/%U'; release channel c3; } 备库：恢复控制文件 RMAN> restore standby controlfile from '/home/oracle/rman1116/12sjnabm_1_1'; SQL> alter database mount standby database; 恢复备库 RMAN> catalog start with '/home/oracle/rman1116'; RMAN> recover database; 验证备库，并启动mrp SQL> alter database open; |
十一：请描述Oracle DG有哪些进程;
1.LNSn（LGWR Network Server process）进程
1 | DG可以使用ARCn、LGWR来传送日志，但它们都是把日志发送给本地的LNSn（如果有多个目标备库，那么会启动相应数量的LNSn进程，同时发送数据）进程，然后备库的RFS进程接收数据，接收到的数据可以存储在备库的备用Redo日志文件中或备库的归档日志中，然后再应用到备库中。 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
2.RFS（Remote File Server）进程
1 2 | RFS（Remote File Server）进程主要用来接受从主库传送过来的日志信息。 对于物理备库而言，RFS进程可以直接将日志写进Standby Redo logs，也可以直接将日志信息写到归档日志中。 |
3.NSA，NSS
1 2 3 4 | 需要注意的是，若在Oracle 10g中采用LGWR传输日志的时候，则进程表现为LNSn。 但在Oracle 11g中，若采用LGWR ASYNC（异步方式）来传输日志的时候，则进程表现为nsa。 若采用LGWR SYNC（同步方式）来传输日志的时候，则进程表现为nss。 且通过视图GV$MANAGED_STANDBY查询的结果不尽相同。 |
4.MRP（Managed Recovery Process）进程
1 2 3 | 该进程只针对物理备库，作用为应用从主库传递过来的Redo日志到物理备库，称为Redo Apply。 如果使用SQL语句“ALTER DATABASE RECOVER MANAGED STANDBY DATABASE”启用该进程，那么前台进程将会做恢复。 如果加上DISCONNECT语句，那么恢复过程将在后台进程，发出该语句的进程可以继续做其它的事情。 |
5.LSP（logical standby process）进程
1 2 | 只有逻辑备库才会有该进程。 LSP进程控应用Redo日志到逻辑备库。 |
十二：请描述Oracle DG常用命令。
1.检查备库是否是real time apply模式
1 2 3 4 | set lines 200 col dest_name for a30 select DEST_ID,DEST_NAME,RECOVERY_MODE from v$archive_dest_status where RECOVERY_MODE <>'IDLE'; #RECOVERY_MODE=MANAGED REAL TIME APPLY 为 real time apply，否则不是 |
2.启动和关闭mrp
1 2 3 4 5 6 | 实时应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; 非实时应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; |
3.查看DG进程状态
1 2 | set lin 200 pages 200 select process,status,thread#,sequence#,block#,blocks from v$managed_standby; |
4.主库传输状态检查
1 2 3 4 5 6 7 | col dest_name for a30 col error for a50 set lin 200 pages 100 select dest_id,error,status,log_sequence,applied_scn,MAX_CONNECTIONS,NET_TIMEOUT,COMPRESSION from v$archive_dest where dest_id<5; 如有必要重新激活传输 ###alter system set log_archive_dest_state_2=defer; ###alter system set log_archive_dest_state_2=enable; |
5.查看数据库信息
1 2 3 4 5 6 7 | set lin 200 pages 100 col FLASHBACK_ON for a10 col current_scn for 99999999999999 col open_mode for a10 col SWITCHOVER_STATUS for a20 col PROTECTION_MODE for a20 select current_scn,protection_mode,database_role,force_logging,FLASHBACK_ON,open_mode,switchover_status from v$database; |
参考
886道Oracle和MySQL面试笔试题库：https://www.xmmup.com/mianshibishi.html
