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

The related steps about how to create a phisical standby database please refer: http://blog.csdn.net/waterxcfg304/article/details/35991771

 

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

The following is the master database:
The following statement can be used to check whether the SQL application can uniquely identify a table and find the unsupported table: query which table does not support logical standby transmission.

SQL> select owner, table_name from dba_logstdby_not_unique where (owner, table_name) not in (select distinct owner, table_name from dba_logstdby_unsupported)
And bad_column = 'y' order by owner, table_name;

OWNER TABLE_NAME
------------------------------------------------------------
Tsmsys srs $

 

/* <--- Logical standby transforming ------------------ can be supported after tsmsys operations on unsupported tables --------------------
Rely disable represents that the oracle will not 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 must be executed in both the master and slave databases ---------------------- *>
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 to 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 are using a server parameter file (spfile) at the time you issue this statement, then the database will update the file with appropriate information about the new logical standby database. if you are not using an spfile, 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 may take several minutes, depending on how long it takes redo generated in Section 4.2.3.2, "Build a Dictionary in the Redo Data" to be transmitted to the standby database, and how much redo data need to be applied. if a dictionary build is not successfully completed MED 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.

4, Standby database: 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. see Oracle Database Administrator's Guide for more information on creating a secure authentication scheme.
[Oracle @ boston 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 database. do not open the database; it shocould 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 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)

6. Logical standby database: 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 recover 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 IMMEDIATE;

 

The following are 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 is already ed, without waiting for the current standby redo log file to be archived. this results in faster switchover and failover times because the standby redo log files have been applied already to the standby 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

Alter database recover managed standby database using current logfile statement.
Ii, For logical standby databases, issue

Alter database start logical standby apply immediate statement.
Standby redo log files are required to use real-time apply.