oracle 10g R2 dataguard

   10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
前期規劃
           Global Database Name   SID      DB_UNIQUE_NAME
primary    oracleDB               primDB   primDB
standby    oracleDB               stanDB   stanDB
hostname 
192.168.151.143 primDB
hostname
192.168.151.2 stanDB
安裝oracle在此不詳述
日誌歸檔
開啟primary和standby的資料庫歸檔強制歸檔屬性並檢視
SYS>alter database force logging;
Database altered.
SYS>SELECT DBID, NAME, LOG_MODE, FORCE_LOGGING FROM V$DATABASE;

      DBID NAME      LOG_MODE      FOR
---------- --------- ------------ ---
3434715927 ORACLEDB  ARCHIVELOG   YES
準備初始化引數檔案
primDB的初始化引數檔案
create pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initprimDB.ora' from spfile;
修改initprimDB.ora，紅色部分為新增的內容
shutdown immediate;
vim initprimDB.ora
DB_UNIQUE_NAME=primDB
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primDB,stanDB)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/flash_recovery_area/ORACLEDB/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=primDB'
LOG_ARCHIVE_DEST_2='SERVICE=stanDB
LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=stanDB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAL_SERVER=primDB
FAL_CLIENT=stanDB
STANDBY_FILE_MANAGEMENT=AUTO
注意：1 /u01/app/oracle/flash_recovery_area/ORACLEDB/archivelog需要手動建立
      2 SERVIC=stanDB中的stanDB是在本機上配置的資料庫服務名用於訪問standby資料庫
用pfile建立spfile
sqlplus / as sysdba
SQL>create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initprimDB.ora';
SQL>startup 啟動資料庫驗證初始化引數檔案是否建立正確
standby 初始化引數檔案
SQL>create pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initstanDB.ora' from spfile;
修改initstanDB.ora，新增紅色部分內容
vim initstanDB.ora
DB_UNIQUE_NAME=stanDB
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primDB,stanDB)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/flash_recovery_area/ORACLEDB/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=stanDB'
LOG_ARCHIVE_DEST_2='SERVICE=primDB
LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=primDB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAL_SERVER=stanDB
FAL_CLIENT=primDB
STANDBY_FILE_MANAGEMENT=AUTO
注意：1 /u01/app/oracle/flash_recovery_area/ORACLEDB/archivelog需要手動建立
      2 SERVIC=primDB中的stanDB是在本機上配置的資料庫服務名用於訪問primary資料庫
用pfile建立spfile
sqlplus / as sysba
SQL>create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initstanDB.ora';
SQL>startup 啟動驗證引數檔案是否正確
建立密碼檔案，控制檔案
建立密碼檔案，primary,standby密碼要相同
orapwd file='/u01/app/oracle/product/10.2.0/db_1/dbs/orapwstanDB' password=sys entries=30
在primary資料庫上建立standby資料庫要用到的控制檔案
SQL>alter database create standby controlfile as '/u01/app/oracle/back/control01.ctl';
SQL>host cp /u01/app/oracle/back/control01.ctl /u01/app/oracle/back/control02.ctl
SQL>host cp /u01/app/oracle/back/control01.ctl /u01/app/oracle/back/control03.ctl
刪除standby資料庫中所有的控制檔案，資料檔案，線上日誌檔案
關閉primary,standby資料庫，複製/u01/app/oracle/back/中的控制檔案副本，primary中所有的資料檔案，線上日誌檔案到standby資料庫對應的位置。注意，所屬許可權。
建立standby redo log
啟動primary,standby資料庫到mount狀態，建立standby redo log
alter database add standby logfile '/u01/app/oracle/oradata/oracleDB/standby_redo01.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/oracleDB/standby_redo02.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/oracleDB/standby_redo03.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/oracleDB/standby_redo04.log' size 50M;
數量至少要比線上日誌多一個，大小和線上日誌一樣大
網路配置
primary
vim /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsname.ora
primDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.151.143)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primDB)
    )
  )

stanDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.151.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stanDB)
    )
  )
standby
vim /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsname.ora
primDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.151.143)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primDB)
    )
  )

stanDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.151.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stanDB)
    )
  )
standby資料庫啟動redo應用
alter database recover managed standby database disconnect from session;
啟動primary到open狀態，測試同步效果
在primary建表
SYS>create table scott.e as select * from scott.emp;
standby切換到read only狀態
停止日誌應用服務,在該模式下可以切換為read only狀態
alter database recover managed standby database cancel;
alter database open read only;
檢視是否有e表生成
SYS>select table_name from all_tables where owner='SCOTT';

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
E
有e表生成，說明同步成功。