Oracle 10g 單一實例資料庫Data Guard 之 Logical Standby 配置詳解（根據官方文檔總結）

-----------Step-by-Step Instructions for Creating a Logical Standby Database-----------

1.Create a Physical Standby Database （EXSITING NOW）

2.Stop Redo Apply on the Physical Standby Database
  
  Executing the following statement in the sqlplus on the physical standby database：  
  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3.Prepare the Primary Database to Support a Logical Standby Database
  
  3.1 Prepare the Primary Database for Role Transitions
  
  Adding the following parameters in the standby role initiallization   settings for the primary database.
  
  LOG_ARCHIVE_DEST_3='LOCATION=/u02/app/oracle/oradata/beijing/arch2/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=beijing'
  LOG_ARCHIVE_DEST_STATE_3=ENABLE
  
  Note: When the Chicago Database Is Running in the Primary Role,the LOG_ARCHIVE_DEST_3 is ignored, and becoming  valid only     when cuug is running in the standby role.When the Chicago Database Is Running in the Logical Standby Role,Archives redo data    
received from the primary database to the local archived redo log files in /u02/app/oracle/oradata/cuug/arch/ .
  
  3.2 Build a Dictionary in the Redo Data
  To build dictionary in the Redo Data, you must enable the Logminer at first.
  Issuing the following statement when the primary  database is in the open state:
  
  04:59:50 SQL> alter database add supplemental log data;  
  If you do not run the command above to enable the logminer, the ALTER DATABASE RECOVER TO LOGICAL STANDBY cuuo will never end up even if you run the ;
  EXECUTE DBMS_LOGSTDBY.BUILD; below successfully.

  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. 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.

  4. Transition to a Logical Standby Database
  4.1 Convert to a Logical Standby Database
  show parameter db_name -----Physical standby database
  ALTER DATABASE RECOVER TO LOGICAL STANDBY cuug;
  LOG_ARCHIVE_DEST_1='LOCATION=/u02/app/oracle/oradata/cuuo/arch VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cuuo'
  LOG_ARCHIVE_DEST_2='SERVICE=cuug LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cuug'
  LOG_ARCHIVE_DEST_3='LOCATION=/u02/app/oracle/oradata/wuhan/arch2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=wuhan'
  LOG_ARCHIVE_DEST_STATE_1=ENABLE
  LOG_ARCHIVE_DEST_STATE_2=ENABLE
  LOG_ARCHIVE_DEST_STATE_3=ENABLE
  
  5.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;

  ------------Applying the redo data to the logical standby database-------------
  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;

  6.Testing the configuration for the Logical Standby Database.
  6.1 Switch to the Primary database,and then issue the following:
23:38:02 sys@CUUG> select * from scott.test;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 DEVELOPMENT    LEADER
        50 TEST           LEADER
        60 ADIVISOR       LEADER

7 rows selected.

Elapsed: 00:00:00.00
23:45:40 sys@CUUG> insert into scott.test values(70,'MARKETING','LEADER');

1 row created.

Elapsed: 00:00:00.01
23:46:40 sys@CUUG> commit;

Commit complete.

Elapsed: 00:00:00.05

  6.2 You have inserted a row in the scott's table test, and now you must want to know whether the update of this table will be reflected in the same table on the standby database.
  To do this, Firstly you can check the standby client 's alert log via tail command ,and then run the following command for several times:

23:46:43 sys@CUUG> alter system switch logfile;

System altered.

Elapsed: 00:00:03.26

  If the alert log of the logical standby database prints the information as following,It means that the standby database is applying the redo data now,you can wait a moment, and then execute a SELECT * FROM SCOTT.TEST in the sqlplus and check whether there
is a new row,and Congratuations to you if so.

Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: '/u02/app/oracle/oradata/wuhan/redo04.log'
Tue Jul  3 23:58:18 2012
LOGMINER: End mining logfile: /u02/app/oracle/oradata/wuhan/redo05.log
Tue Jul  3 23:58:18 2012
LOGMINER: Begin mining logfile: /u02/app/oracle/oradata/wuhan/redo04.log
Tue Jul  3 23:58:31 2012
RFS[1]: Archived Log: '/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_94_787678850.log'
Tue Jul  3 23:58:31 2012

RFS LogMiner: Registered logfile [/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_94_787678850.log] to LogMiner session id [1]

轉載請註明出處及原文連結：

http://blog.csdn.net/xiangsir/article/details/8572899