创建逻辑Standby数据库

最新推荐文章于 2023-02-07 11:40:54 发布

cpcmix6825

最新推荐文章于 2023-02-07 11:40:54 发布

阅读量145

点赞数

Creating a Logical Standby Database

Prerequisite Conditions for Creating a Logical Standby Database

Before you create a logical standby database, you must first ensure the primary database is properly configured：”Determine Support for Data Types and Storage Attributes for Tables” and “Ensure Table Rows in the Primary Database Can Be Uniquely Identified”;

1、 Determine Support for Data Types and Storage Attributes for Tables

When setting up a logical standby database, you must ensure the logical standby database can maintain the data types and tables in your primary database. It contains the following topics:

Data Type Considerations;

Storage Type Considerations;

PL/SQL Supplied Packages Considerations;

Unsupported Tables, Sequences, and Views;

Skipped SQL Statements on a Logical Standby Database;

DDL Statements Supported by a Logical Standby Database;

2、 Ensure Table Rows in the Primary Database Can Be Uniquely Identified

The physical organization in a logical standby database is different from that of the primary database, even though the logical standby database is created from a backup copy of the primary database. Thus, ROWIDs contained in the redo records generated by the primary database cannot be used to identify the corresponding row in the logical standby database.

Oracle uses primary-key or unique-constraint/index supplemental logging to logically identify a modified row in the logical standby database. When database-wide primary-key and unique-constraint/index supplemental logging is enabled, each UPDATE statement also writes the column values necessary in the redo log to uniquely identify the modified row in the logical standby database.

u If a table has a primary key defined, then the primary key is logged along with the modified columns as part of the UPDATE statement to identify the modified row.

u In the absence of a primary key, the shortest nonnull unique-constraint/index is logged along with the modified columns as part of the UPDATE statement to identify the modified row.

u In the absence of both a primary key and a nonnull unique constraint/index, all columns of bounded size are logged as part of the UPDATE statement to identify the modified row. In other words, all columns except those with the following types are logged: LONG, LOB, LONG RAW, object type, and collections.

Oracle recommends that you add a primary key or a nonnull unique index to tables in the primary database, whenever possible, to ensure that SQL Apply can efficiently apply redo data updates to the logical standby database.

Perform the following steps to ensure SQL Apply can uniquely identify rows of each table being replicated in the logical standby database:

Step 1 Find tables without unique logical identifier in the primary database.

Query the DBA_LOGSTDBY_NOT_UNIQUE view to display a list of tables that SQL Apply may not be able to uniquely identify. For example:

SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE

2> WHERE (OWNER, TABLE_NAME) NOT IN

3> (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)

4> AND BAD_COLLUMN = 'Y'

Step 2 Add a disabled primary-key RELY constraint.

If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. This avoids the overhead of maintaining a primary key on the primary database. When you specify the RELY constraint, the system will assume that rows are unique.

To create a disabled RELY constraint on a primary database table, use the ALTER TABLE TABLE statement with a RELY DISABLE clause. The following example creates a disabled RELY constraint on a table named mytab, for which rows can be uniquely identified using the id and name columns:

SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

Step-by-Step Instructions for Creating a Logical Standby Database

1、 Create a Physical Standby Database:

You create a logical standby database by first creating a physical standby database and then transitioning it to a logical standby database.

2、 Stop Redo Apply on the Physical Standby Database:

Before converting to a logical standby database, stop Redo Apply on the physical standby database. Stopping Redo Apply is necessary to avoid applying changes past the redo that contains the LogMiner dictionary.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3、 Prepare the Primary Database to Support a Logical Standby Database:

a) Prepare the Primary Database for Role Transitions:

Base on the physical standby database’s initialization parameters, If you plan to transition the primary database to the logical standby role, then you must also include a LOG_ARCHIVE_DEST_3 destination on the primary database, so that no parameters need to change after a role transition. This parameter only takes effect when the primary database is transitioned to the standby role.

Example:

LOG_ARCHIVE_DEST_3=

'LOCATION=/arch2/chicago/

VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=chicago'

LOG_ARCHIVE_DEST_STATE_3=ENABLE

To dynamically set the LOG_ARCHIVE_DEST_3 parameter, use the SQL ALTER SYSTEM SET statement and include the SCOPE=BOTH clause so that the change takes effect immediately and persists after the database is shut down and started up again.

When the Chicago Database Is Running in the Primary Role: LOG_ARCHIVE_DEST_3 Is ignored; LOG_ARCHIVE_DEST_3 is valid only when chicago is running in the standby role.

When the Chicago Database Is Running in the Logical Standby Role: LOG_ARCHIVE_DEST_3 Archives redo data received from the primary database to the local archived redo log files in /arch2/chicago/.

b) Build a Dictionary in the Redo Data:

A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo. As part of building LogMiner Multiversioned Data Dictionary, supplemental logging is automatically set up to log primary key and unique-constraint/index columns. The supplemental logging information ensures each update contains enough information to logically identify each row that is modified by the statement.

To build the LogMiner dictionary, issue the following statement:

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.

The DBMS_LOGSTDBY.BUILD procedure uses Flashback Query to obtain a consistent snapshot of the data dictionary that is then logged in the redo stream. Oracle recommends setting the UNDO_RETENTION initialization parameter to 3600 on both the primary and logical standby databases.

4、 Transition to a Logical Standby Database:

a) Convert to a Logical Standby Database:

The redo logs contain the information necessary to convert your physical standby database to a logical standby database.

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name;

For db_name, specify a database name to identify the new logical standby database.

The statement waits, applying redo data until the LogMiner dictionary is found in the log files. This may take several minutes, depending on how long it takes redo generated to be transmitted to the standby database, and how much redo data need to be applied. If a dictionary build is not successfully performed on the primary database, this command will never complete. You can cancel the SQL statement by issuing the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL statement from another SQL session.

b) Create a New Password File:

Because the conversion process changes the database name (that was originally set with the DB_NAME initialization parameter) for the logical standby database, you must re-create the password file.

c) Adjust Initialization Parameters for the Logical Standby Database:

On the logical standby database, shutdown the instance and issue the STARTUP MOUNT statement to start and mount the database.

SQL> SHUTDOWN;

SQL> STARTUP MOUNT;

You need to modify the LOG_ARCHIVE_DEST_n parameters because, unlike physical standby databases, logical standby databases are open databases that generate redo data and have multiple log files (online redo log files, archived redo log files, and standby redo log files). It is good practice to specify separate local destinations for:

Archived redo log files that store redo data generated by the logical standby database.

Archived redo log files that store redo data received from the primary database.

Example:

LOG_ARCHIVE_DEST_1=

'LOCATION=/arch1/boston/

VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=boston'

LOG_ARCHIVE_DEST_2=

'SERVICE=chicago LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=chicago'

LOG_ARCHIVE_DEST_3=

'LOCATION=/arch2/boston/

VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=boston'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_STATE_3=ENABLE

The following table describes the archival processing defined by the initialization parameters shown in above example:

	When the Boston Database Is Running in the Primary Role	When the Boston Database Is Running in the Logical Standby Role
LOG_ARCHIVE_DEST_1	Directs archival of redo data generated by the primary database from the local online redo log files to the local archived redo log files in /arch1/boston/.	Directs archival of redo data generated by the logical standby database from the local online redo log files to the local archived redo log files in /arch1/boston/.
LOG_ARCHIVE_DEST_2	Directs transmission of redo data to the remote logical standby database chicago.	Is ignored; LOG_ARCHIVE_DEST_2 is valid only when boston is running in the primary role.
LOG_ARCHIVE_DEST_3	Is ignored; LOG_ARCHIVE_DEST_3 is valid only when boston is running in the standby role.	Directs archival of redo data received from the primary database to the local archived redo log files in /arch2/boston/.

5、 Open the Logical Standby Database:

To open the new logical standby database, you must open it with the RESETLOGS option by issuing the following statement:

SQL> ALTER DATABASE OPEN RESETLOGS;

Because this is the first time the database is being opened, the database’s global name is adjusted automatically to match the new DB_NAME initialization parameter.

Issue the following statement to begin applying redo data to the logical standby database. For example:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

6、 Verify the Logical Standby Database Is Performing Properly: