How to create a logical standby database based on a physical standby database

Source: Internet
Author: User
Tags database issues failover

The related steps about what to create a phisical standby database please REFER:HTTP://BLOG.CSDN.NET/WATERXCFG304/ARTICL e/details/35991771

/* How to create a logical standby database based on a physical standby database */

Here is the main library:
The following statements can be used to check whether a SQL app uniquely identifies a table column and to identify tables that are not supported: query which table does not support logical standby transport

Sql> Select owner, table_name from Dba_logstdby_not_unique where (owner, table_name) not in (SELECT distinct Owner, tab Le_name from dba_logstdby_unsupported)
and bad_column = ' Y ' ORDER by owner, TABLE_NAME;

OWNER table_name
------------------------------ ------------------------------
Tsmsys srs$

/*< supports logical standby transforming--------------------after an unsupported table Tsmsys operation---
Rely disable represents that the Oracle won't maintain this function

ALTER TABLE tsmsys.srs$ add primary key (cursor) rely disable;

Select username from dba_users where username not in (select owner from Dba_logstdby_skip) Order by username;

Select Supplemental_log_data_min, SUPPLEMENTAL_LOG_DATA_PK, supplemental_log_data_ui from V$database;

ALTER DATABASE Add supplemental log data (primary Key,unique index) columns;

<*----------------------The following statements are being performed by the master repository--------------------------*>
alter system set Parallel_max_servers = 40;
alter system set Control_file_record_keep_time = 30;
alter system set undo_retention = 3600;
alter system set log_archive_max_processes = 4;
Create Pfile from SPFile;

Primary database:logical Standby Role initialization Parameters
Alter system set log_archive_dest_3= ' Location=/u01/app/oracle/arch2/chicago valid_for= (standby_logfiles,standby_ ROLE) Db_unique_name=chicago ';
alter system set log_archive_dest_state_3=enable;

Standby database:logical Standby Role initialization Parameters
Alter system set log_archive_dest_3= ' Location=/u01/app/oracle/arch2/boston valid_for= (standby_logfiles,standby_ ROLE) Db_unique_name=boston ';
alter system set log_archive_dest_state_3=enable;

Start to implement logical standby:
1, Standby database:
ALTER DATABASE recover managed standby database cancel;

2, Primary database:to build the Logminer dictionary, issue the following statement:
Sql> EXECUTE Dbms_logstdby. BUILD;

3, physical database:convert to a 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 for convert to a logical standby database, issue the following SQL statement:

For db_name, specify a database name to identify the new logical standby database. If you is using a server parameter file (spfile) at the time of issue this statement and then the database would update the file with appropriate information about the new logical standby database. If you is not using a spfile and then the database issues a message reminding you to set the name of the db_name parameter After shutting down the database. The statement waits, applying redo data until the Logminer dictionary is found in the log files. This could take several minutes, depending on how long it takes redo generated in section, "Build a Dictionary in th E Redo data "to is transmitted to the standby database, and what much Redo Data need to be applied. If A dictionary build is not successfully performed on the primary database, this command would never complete. You can cancel the SQL statement by issuing the ALTER database RECOVER MANAGED STANDBY database cancel statement from Anot Her SQL SessioN.

4, Standby database:create a New Password File
Because The conversion process changes the database name (that is originally set with the Db_name initialization paramete R) for the logical standby database, you must re-create the password file. See Oracle Database Administrator ' s Guide for more information on creating a secure authentication scheme.
[Email protected] dbs]$ orapwd File=orapwboston password=oracle entries=5 force=y

5, Logical Standby database: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 Databa Se. Do not open the database; It should remain closed to user access until later in the creation process. For example:

Unlike physical standby databases, logical standby databases is open databases that generate redo data and has multiple Log files (online redo log files, archived redo log files, and standby redo log files)

6, Logical standby database:open the Logical standby database
The new database is logically the same as your primary database, but it's transactionally inconsistent with the primary D Atabase, and thus incompatible for recover operations. To open the new logical standby database, your must open it with the Resetlogs option by issuing the following statement:
sql> ALTER DATABASE OPEN resetlogs;

Because This was 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:

The following is testing data from primary database and logical standby database respectively.


Sql> Select COUNT (*) from test;



Sql> Select COUNT (*) from test;


7, Using real-time apply to apply Redo Data Immediately
If The real-time apply feature is enabled, log apply services can apply redo data as it was received, without waiting for t The He current standby redo log file is archived. This results in faster switchover and failover times because the standby redo log files has been applied already to the S Tandby database by the time the failover or switchover begins.

Use the ALTER DATABASE statement to enable the real-time apply feature, as follows:
I, for physical standby databases, issue the

ALTER database RECOVER MANAGED STANDBY database USING current LOGFILE statement.
II, for logical standby databases, issue the

Standby Redo log files is required to use real-time apply.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.