Oracle10g logical standby 建立

1.  Check whether production database is in auto archived mode   (select  *   from  v$database ; )
2.  Force logging on the production database    (alter database force logging ; )
3.  Check the unsupported objects on the production database    
SQL>  select  *   from  dba_logstdby_unsupported  ;
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'  ;  "
4.  Check whether  the supplemental log enable on the production database ;
SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database; 
if the rusult is ""NO   NO"" ,  we need to enable supplemental log on primary db .
SQL> alter database add supplemental log data(primary key,unique index)  columns; "
5.  Create a new tablespace ""LOGMNRTS"" on the production database for Logminer  , then execute the below command :
SQL >  EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts');  "
6.  Check the parameter  "archive_lag_target " on the production server  :    SQL>  show parameter archive_lag_target 
7.  Modify the value of undo_retention on the production server :   SQL> alter system set undo_retention=3600 scope=both;
8.  Create physical standby
9.  Configure "tnsnames.ora" file on the production and standby database 
10.  Configure some parameters for Physical and Logical Standby database 
11.  Enable standby database to recover mananged standby mode  .
SQL> startup nomount 
SQL> alter database mount standby database ; 
SQL> alter database recover managed standby database disconnect from session ; "
12. On primary db ,  run:   
SQL> alter system set log_archive_dest_2='SERVICE=standby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mxstandby'; 
SQL> EXECUTE DBMS_LOGSTDBY.BUILD; "
13.  Switch physical standby to logical standby : 
SQL> alter database recover managed standby database cancel ; 
          (we must cancel recover mode at first if the standby is in recover mananged mode)   
SQL> alter database recover to logical standby mxweb01;  "
14.   restart logical standby database , on the standby database .
SQL> shutdown immediate; 
SQL> startup mount; 
SQL> alter database open resetlogs; 
SQL> shutdown immediate; 
SQL> startup; 
 
Then enable SQL apply on the logical  stadnby .  
SQL> alter database start logical standby apply;   "
15.   Preparer  and  appliers tuning
The default value of the preparer and  appliers is 1,5 , mostly default value of the preparer is enough， but appliers need tuning）。
Query number of the preparer  and  appliers
        SQL> select name,value from v$logstdby_stats where name like 'number%';   
Tune number of preparer and appliers :
        SQL> alter database stop logical standby apply;    
        SQL> execute dbms_logstdby.apply_set('PREPARE_SERVERS', 2);
        SQL> execute dbms_logstdby.apply_set('APPLY_SERVERS', 10);
        SQL> alter database start logical standby apply;    
Check the status of all processes for SQL Apply 
        SQL> select sid,type,status from v$logstdby_process; "
16. LCR Cache tuning  (default size of LCR Cache is 1/4 of Shared pool size )
Check the page out status 
        SQL> select name,value from v$logstdby_stats where name like '%page%';
If  Paged Out > 0 , we need to increate max_sga and LCR Cache size . 
       SQL> alter database stop logical standby apply;
       SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SGA', 1000);   
       SQL> alter database start logical standby apply; "
17. Monitor data sync on the logical standby database and trouble shooting  .
18.  On primary DB .   SQL>alter system  set  log_archive_dest_state_2=defer ; 
19. On priamry DB .    SQL>alter system  set  log_archive_dest_2=''  ;   
20.  Disable force logging on the production database,            SQL>  alter database no force logging ;