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

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;
<*--------------------------End-----------------------------------------*>

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:
Sql> ALTER DATABASE RECOVER to LOGICAL STANDBY Boston;

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 4.2.3.2, "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:
Sql> SHUTDOWN;
Sql> STARTUP MOUNT;

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:
sql> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

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

Sql>!hostname
Chicago.oracle.com

Sql> Select COUNT (*) from test;

COUNT (*)
----------
1048576

Sql>!hostname
Boston.oracle.com

Sql> Select COUNT (*) from test;

COUNT (*)
----------
1048576

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

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE statement.
Standby Redo log files is required to use real-time apply.