This section describes the tasks you perform to create a logical standby database.
Steps for Creating a Logical standby database :
- Create a Physical standby database
- Stop Redo Apply on the Physical standby database
- Prepare the Primary Database to Support a Logical standby database
- Transition to a Logical standby database
- Open the Logical standby database
- Verify the Logical standby database Is Performing Properly
Oracle Data Guard - 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.
Oracle Data Guard - Stop Redo Apply on the Physical standby database
You can run Redo Apply on the new physical standby database for any length of time before converting it to a logical standby database. However, 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 .
To stop Redo Apply, issue the following statement on the physical standby database. If the database is a RAC database comprised of multiple instances, then you must first reduce the number of instances to one before issuing this statement:
SQL> ALTER DATABASE RECOVER MANAGED standby database CANCEL;
Oracle Data Guard - Prepare the Primary Database to Support a Logical standby database
This section contains the following topics:
Prepare the Primary Database for Role Transitions
- Prepare the Primary Database for Role Transitions
Build a Dictionary in the Redo Data
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, as shown in Example , 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 Primary Database: Logical Standby Role Initialization Parameters
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.
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. Additionally, supplemental logging is set up to log primary key and unique-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.
Oracle Data Guard - Transition to a Logical standby database
This section describes how to prepare the physical standby database to transition to a logical standby database. It contains the following topics:
Convert to a Logical standby database
- Convert to a Logical standby database
Create a New Password File
Adjust Initialization Parameters for the Logical standby database
The redo logs contain the information necessary to convert your physical standby database to a logical standby database. To continue applying redo data to the physical standby database until it is ready to convert to a logical standby database, issue the following SQL statement:
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY new-db_name;
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.
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.
Adjust Initialization Parameters for the Logical Standby Database
On the logical standby database, issue the STARTUP MOUNT statement to start and mount the database. Do not open the database; it should remain closed to user access until later in the creation process. For example:
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. In Example, this is configured as the LOG_ARCHIVE_DEST_1=LOCATION=/arch1/boston destination.
Archived redo log files that store redo data received from the primary database. In Example this is configured as the LOG_ARCHIVE_DEST_3=LOCATION=/arch2/boston destination.
Example shows the initialization parameter changes that were modified for the logical standby database. The parameters shown are valid for the Boston logical standby database when it is running in either the primary or standby database role.
Example Modifying Initialization Parameters for a Logical Standby Database
'SERVICE=chicago LGWR ASYNC
Oracle Data Guard - Open the Logical Standby Database
The new database is logically the same as your primary database, but it is transactionally inconsistent with the primary database, and thus incompatible for recovery operations.
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;
Oracle Data Guard - Verify the Logical Standby Database Is Performing Properly
- Monitoring Log File Archival Information
- Monitoring Log Apply Services on Logical Standby Databases
- Managing a Logical Standby Database
Oracle Data Guard - Post-Creation Steps
At this point, the logical standby database is running and can provide the maximum performance level of data protection. The following list describes additional preparations you can take on the logical standby database:
- Upgrade the data protection mode
- Enable Flashback Database
Oracle Data Guard
Standby Database Types