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   */

以下是主庫:
下列語句可以用來檢查SQL應用能否唯一識別表列，並找出不被支援的表：查詢哪個表不支援logical standby傳輸

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$

 

/*<---對不支援的表tsmsys操作後即可支援logical standby transforming--------------------
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;

<*----------------------以下語句主備庫都要執行--------------------------*> 
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 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.

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 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 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 received, 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 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 are required to use real-time apply.