配置Oracle11g的Dataguard测试，创建物理备库(Physical Standby Database)-CSDN博客

主、备库均为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#;

没有实时同步。

原因：原来是该参数没有把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#;

备库可以实时同步.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-682237/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-682237/