Oracle11g的Dataguard測試，建立物理備庫(Physical Standby Database)

主、備庫均為Linux相同的系統版本,資料庫版本均為Oracle11gR2
主庫：10.1.1.1  備庫：10.2.2.2
1、確認主備資料庫系統
系統：
[root@BZXXDBS01 ~]# uname -a
Linux BZXXDBS01 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

備庫：
[root@BZXXDBS02 ~]# uname -a
Linux BZXXDBS02 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

2、在主庫設定：
SQL>ALTER DATABASE FORCE LOGGING;
檢視下面引數：
如：SQL> show parameter LOG_ARCHIVE_DEST_1
主庫
DB_NAME=bhoms
DB_UNIQUE_NAME=bhoms01  （如果是spfile檔案，alter system set db_unique_name='bhoms01' scope=spfile; 統一修改引數後，可以重啟資料庫）
LOG_ARCHIVE_CONFIG='DG_CONFIG=(bhoms01,bhoms02)'  （alter system set log_archive_config='dg_config=(bhoms01,bhoms02)';）
LOG_ARCHIVE_DEST_1='location=/u01/app/oracle/flash_recovery_area/BHOMS/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=bhoms01'
(alter system set log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/BHOMS/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=bhoms01';)
LOG_ARCHIVE_DEST_2='service=bhoms02 async valid_for=(online_logfiles,primary_role) db_unique_name=bhoms02'
(alter system set log_archive_dest_2='service=bhoms02 async valid_for=(online_logfiles,primary_role) db_unique_name=bhoms02';)
LOG_ARCHIVE_DEST_STATE_1=ENABLE  (alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;)
LOG_ARCHIVE_DEST_STATE_2=ENABLE  (alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;)
FAL_SERVER=bhoms02  (alter system set fal_server=bhoms02;)
FAL_CLIENT=bhoms01  (alter system set fal_client=bhoms01;)
DB_FILE_NAME_CONVERT='bhoms02','bhoms01'   (alter system set DB_FILE_NAME_CONVERT='bhoms02','bhoms01' scope=spfile;)
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/bhoms/','/u01/app/oracle/oradata/bhoms','/u01/app/oracle/flash_recovery_area/BHOMS02/onlinelog','/u01/app/oracle/flash_recovery_area/BHOMS01/onlinelog'
(alter system set log_file_name_convert='/u01/app/oracle/oradata/bhoms/','/u01/app/oracle/oradata/bhoms','/u01/app/oracle/flash_recovery_area/BHOMS02/onlinelog','/u01/app/oracle/flash_recovery_area/BHOMS01/onlinelog' scope=spfile;)
STANDBY_FILE_MANAGEMENT=AUTO  (alter system set STANDBY_FILE_MANAGEMENT=AUTO;)

關閉資料庫：
SQL> shutdown immediate;

啟動：
SQL> startup

檢視：
SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME
------------------------------
bhoms01
bhoms02

3、在主庫備份
[oraoms@BZXXDBS01 ~]$ rman target/
RMAN> backup database;

把備份的資料檔案ftp上傳到備庫/home/oraoms/backup上
ftp 10.2.2.2
輸入使用者、密碼
cd /home/oraoms/backup
bin
put 檔名
bye

在主庫上建立備庫的控制檔案
SQL> alter database create standby controlfile as '/home/oraoms/bhoms02.ctl';

Database altered.
在主庫上建立備份需要的pfile檔案
SQL> create pfile='/home/oraoms/initbhoms.ora' from spfile;

File created.

把上面的控制檔案、pfile檔案、密碼檔案上傳到備庫
（密碼檔案位於：$ORACLE_HOME/dbs/orapwbhoms）

4、在備庫上對檔案授權
[oraoms@BZXXDBS02 backup]$ pwd
/home/oraoms/backup
[oraoms@BZXXDBS02 backup]$ chmod 777 *

5、假裝置庫上已經安裝好資料庫名為bhoms的資料庫
關閉資料庫，備份資料檔案到其他的目錄
SQL>shutdown immediate

配置備庫tnsnames.ora($ORACLE_HOME/network/admin/tnsnames.ora)
bhoms01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bhoms01)
    )
  )

bhoms02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bhoms02)
    )
  )

測試：
[oraoms@BZXXDBS02 admin]$ tnsping bhoms01
[oraoms@BZXXDBS02 admin]$ tnsping bhoms02

同時配置主庫的tnsnames.ora($ORACLE_HOME/network/admin/tnsnames.ora)
bhoms01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bhoms01)
    )
  )

bhoms02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bhoms02)
    )
  )
測試：
[oraoms@BZXXDBS01 admin]$ tnsping bhoms01
[oraoms@BZXXDBS01 admin]$ tnsping bhoms02

6、在備庫上修改initbhoms.ora引數
*.db_unique_name='bhoms02'
*.db_file_name_convert='bhoms01','bhoms02'
*.fal_client='bhoms02'
*.fal_server='bhoms01'
*.log_file_name_convert='/u01/app/oracle/oradata/bhoms/','/u01/app/oracle/oradata/bhoms','/u01/app/oracle/flash_recovery_area/BHOMS01/onlinelog','/u01/app/oracle/flash_recovery_area/BHOMS02/onlinelog'
*.log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/BHOMS/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=bhoms02'
*.log_archive_dest_2='service=bhoms01 async valid_for=(online_logfiles,primary_role) db_unique_name=bhoms01'

在備庫複製初始化檔案pfile、密碼檔案到$ORACLE_HOME/dbs目錄下
[oraoms@BZXXDBS02 dbs]$ cp /home/oraoms/backup/initbhoms.ora $ORACLE_HOME/dbs/
[oraoms@BZXXDBS02 dbs]$ cp /home/oraoms/backup/orapwbhoms $ORACLE_HOME/dbs/

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initbhoms.ora';

File created.

啟動備庫到nomount狀態：
SQL> startup nomount
ORACLE instance started.

Total System Global Area 6747725824 bytes
Fixed Size                  2213976 bytes
Variable Size            5033166760 bytes
Database Buffers         1677721600 bytes
Redo Buffers               34623488 bytes

恢復備庫控制檔案
[oraoms@BZXXDBS02 dbs]$ rman target/
RMAN> restore controlfile from '/home/oraoms/backup/bhoms02.ctl';

Starting restore at 21-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=601 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/bhoms/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/bhoms/control02.ctl
Finished restore at 21-DEC-10

更改資料庫到mount狀態
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

可以檢視dataguard配置：
SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME
------------------------------
bhoms02
bhoms01

恢復資料檔案時，需要在備庫上建立和主庫一致的放置備份資料的目錄
(如：/u01/app/oracle/flash_recovery_area/BHOMS01/backupset/2010_12_21/)
[oraoms@BZXXDBS02 flash_recovery_area]$ pwd
/u01/app/oracle/flash_recovery_area
[oraoms@BZXXDBS02 flash_recovery_area]$ mkdir BHOMS01
[oraoms@BZXXDBS02 flash_recovery_area]$ cd BHOMS01
[oraoms@BZXXDBS02 BHOMS01]$ mkdir backupset
[oraoms@BZXXDBS02 BHOMS01]$ cd backupset/
[oraoms@BZXXDBS02 backupset]$ mkdir 2010_12_21

把備份的資料移動到目錄中
[oraoms@BZXXDBS02 2010_12_21]$ mv /home/oraoms/backup/o1_mf_ncsnf_TAG20101221T091706_6k001pvl_.bkp /u01/app/oracle/flash_recovery_area/BHOMS01/backupset/2010_12_21
[oraoms@BZXXDBS02 2010_12_21]$ mv /home/oraoms/backup/o1_mf_nnndf_TAG20101221T091706_6k000lpz_.bkp /u01/app/oracle/flash_recovery_area/BHOMS01/backupset/2010_12_21

RMAN> restore database;

Starting restore at 21-DEC-10
Starting implicit crosscheck backup at 21-DEC-10
。。。。。
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 21-DEC-10

關閉資料庫：
SQL>shutdown immediate
SQL>startup nomount
SQL>
SQL> alter database mount standby database;
只讀開啟模式：
SQL> alter database open read only;
讓主備庫同步：
SQL> alter database recover managed standby database disconnect from session;

Database altered.

驗證：
在備庫檢視歸檔日誌
 SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
       721 21-DEC-10 21-DEC-10
       722 21-DEC-10 21-DEC-10
       723 21-DEC-10 21-DEC-10
       724 21-DEC-10 21-DEC-10
       725 21-DEC-10 21-DEC-10

在主庫強制歸檔日誌
SQL>ALTER SYSTEM SWITCH LOGFILE;

再次查詢備庫：
 SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
       721 21-DEC-10 21-DEC-10
       722 21-DEC-10 21-DEC-10
       723 21-DEC-10 21-DEC-10
       724 21-DEC-10 21-DEC-10
       725 21-DEC-10 21-DEC-10

沒有實時同步。

原因：原來是該引數沒有把standby修改成bhoms02
檢視主庫或者備庫
select * from V$ARCHIVE_DEST_STATUS 
發現：LOG_ARCHIVE_DEST_2 的 status：BAD PARAM   其中destination值： standby

如主庫：
SQL> show parameter LOG_ARCHIVE_DEST_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=standby lgwr async noa
                                                 ffirm
SQL>alter system set log_archive_dest_2='service=bhoms02 async valid_for=(online_logfiles,primary_role) db_unique_name=bhoms02';

再查詢正常：
select * from V$ARCHIVE_DEST_STATUS

在備庫修改：
SQL>alter system set log_archive_dest_2='service=bhoms01 async valid_for=(online_logfiles,primary_role) db_unique_name=bhoms01';
再查詢正常：
select * from V$ARCHIVE_DEST_STATUS

再次查詢備庫：
 SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 備庫可以實時同步.