Oracle11gR2 Data Guard搭建（一）

前期规划

操作系统：Linux5.6 x86_64

主库 primary：192.168.56.16

ORACLE_SID=priamry

备库standby：192.168.56.17

ORACLE_SID=standby

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

设置primary库

查看主数据库的归档模式和force_logging

SYS@primary>select log_mode,force_loggong from v$database;

改成归档模式和强制logging

SYS@primary >alter database force logging;

建立归档日志路径

[oracle@primaryprimary]$ pwd

/u01/app/oracle/oradata/primary

[oracle@primaryprimary]$ mkdir archivelog;

更改归档日志目录

SYS@primary>altersystem set log_archive_dest_1='location=/u01/app/oracle/oradata/primary/archivelog';

 

创建pfile

SYS@primary>create pfile from spfile;

创建standby控制文件

SYS@primary >alter database create standbycontrolfile as '/u01/app/oracle/standby.ctl';

把pfile文件，口令文件和备控制文件传输到standby

[oracle@primarydbs]$ scp orapwprimary 192.168.56.17:/u01/app/oracle/product/11.2.0/db_1/dbs/

[oracle@primarydbs]$ scp initprimary.ora192.168.56.17:/u01/app/oracle/product/11.2.0/db_1/dbs/

[oracle@primary dbs]$ scp/u01/app/oracle/standby.ctl 192.168.56.17:/u01/app/oracle/

设置standby数据库

将口令文件和pfile文件修改成standby的

[oracle@standbydbs]$ mv initprimary.ora initstandby.ora

[oracle@standby dbs]$ mv orapwprimaryorapwstandby

编辑初始化参数文件

[oracle@standbydbs]$ vi initprimary.ora

将最上边几行去掉，标注的地方primary修改为standby，其中db_name不改

在最后添加一下参数

*.db_unique_name='standby'

*.db_file_name_convert='/u01/app/oracle/oradata/primary','/u01/app/oracle/oradata/standby'

*.log_file_name_convert='/u01/app/oracle/oradata/primary','/u01/app/oracle/oradata/standby'

*.log_archive_config='dg_config=(primary,standby)'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/standby/archivelogvalid_for=(all_logfiles,all_roles) db_unique_name=standby'

*.log_archive_dest_2='service=primarylgwr async valid_for=(online_logfiles,primary_role) db_unique_name=primary'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.log_archive_max_processes=10

*.fal_server=primary

*.fal_client=standby

*.standby_file_management=auto

 

创建目录

[oracle@standby~]$ mkdir -p /u01/app/oracle/admin/standby/adump

[oracle@standbyoracle]$ mkdir -p oradata/standby/archivelog

[oracle@standbyoracle]$ mkdir -p flash_recovery_area/standby

配置standby库的listener和静态服务名

这里也可以直接修改对应的listener.ora和tnsname.ora，我们用图形化工具配置

[oracle@standby oracle]$ netmgr

tnsping primary

创建spfile，并启动nomount状态

检查环境

[oracle@standby oracle]$ env | grep ORA

SYS@standby>create spfile from pfile='?/dbs/initstandby.ora';

standby库的配置暂时结束，重新返回primary库的配置

配置primary库

配置连接到standby的网络服务名

[oracle@primary admin]$ netca

和standby库配置一样的，此处就略过

tnsping standby

配置primary库参数
alter system set log_archive_config='dg_config=(primary,standby)';

alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/primary/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=primary';

alter system set log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby';

alter system set log_archive_dest_state_1=enable;

alter system set log_archive_dest_state_2=enable;

alter system set log_archive_max_processes=10;

alter system set fal_server=standby;

alter system set fal_client=primary;

alter system set standby_file_management=auto;

alter system set db_unique_name='primary' scope=spfile;

alter system set db_file_name_convert='/u01/app/oracle/oradata/primary','/u01/app/oracle/oradata/standby' scope=spfile;

alter system set log_file_name_convert='/u01/app/oracle/oradata/primary','/u01/app/oracle/oradata/standby' scope=spfile;

 

备份primary库

[oracle@primary~]$ mkdir -p /u01/backupset

登录rman开始备份

[oracle@primary~]$ rman target /

run

{

allocate channel d1 type disk format ‘/u01/backupset/%U’;

backup database;

release channel d1;

}

把备份集目录传输到standby库中

 [oracle@primary u01]$ scp -r /u01/backupset/ 192.168.56.17:/u01

开始克隆standby库，在primary库上运行

[oracle@primary ~]$ rman target / auxiliarysys/sys@standby

Run

{

allocate auxiliary channel d1 type disk;

duplicate target database for standby nofilenamecheck;

release channel d1;

}

还原了standby库

继续配置standby库

查询standby库的状态

SYS@standby >select status from v$instance;

创建备库的standby日志

alter database add standby logfile ('/u01/app/oracle/oradata/standby/standby01.log') size 50m;

alter database add standby logfile ('/u01/app/oracle/oradata/standby/standby02.log') size 50m;

alter database add standby logfile ('/u01/app/oracle/oradata/standby/standby03.log') size 50m;

alter database add standby logfile('/u01/app/oracle/oradata/standby/standby04.log') size 50m;

在primary库切换几个日志

SYS@primary >alter system switch logfile;

在看下standby库，查看一下是否有归档日志

日志传输ok！

备库执行日志应用同步语句，开始运行物理DG

alter database recover managed standby database using current logfile disconnect fromsession;

查询日志应用情况

select sequence#, applied from v$archived_log where applied='YES' order by sequence#;

测试一下同步

在primary上执行，使用scott用户

在standby库上执行

取消数据同步，open打开standby库

数据同步成功，物理DG搭建成功！

在看物理DG的open模式

再次启动数据同步语句，查看数据库的状态

alter database recover managed standby database using current logfile disconnect fromsession;

select open_mode from v$database;

可以看到这下是redo only with apply 状态，这个状态就是11g的新特性，备库在打开的时候也同时可以接受数据同步。

使用switchover模式切换物理备库

在primary库上执行

select switchover_status from v$database;

alter database commit to switchover to physical standby;

时间可能有些长

重启一下数据库，mount数据库，完成主库切换到了备库

在standby库上查看日志同步情况

select sequence#,applied from v$archived_logwhere applied='YES' order by sequence#;

完成备库切换到主库

查看standby的状态

在primary库上创建备库日志

alter database add standby logfile ('/u01/app/oracle/oradata/primary/standby01.log') size 50m;

alter database add standby logfile ('/u01/app/oracle/oradata/primary/standby02.log') size 50m;

alter database add standby logfile ('/u01/app/oracle/oradata/primary/standby03.log') size 50m;

alter database add standby logfile ('/u01/app/oracle/oradata/primary/standby04.log') size 50m;

执行数据同步语句，查看日志同步情况

alter database recover managed standby database using current logfile disconnect fromsession;

select sequence#,applied from v$archived_logwhere applied='YES' order by sequence#;

测试一下数据同步

在standby库上使用scott用户，还是刚才的t表

在primary库上，取消数据同步，查看t表

select open_mode from v$database;

alter database recover managed standby database using current logfile disconnect fromsession;

select open_mode from v$database;

再次测试一下切换

在standby库上执行

select switchover_status from v$database;

alter database commit to switchover to physicalstandby;

重启一下数据库，mount数据库，完成standby库还原回主库

在primary库上执行

select sequence#,applied from v$archived_logwhere applied='YES' order by sequence#;

select switchover_status from v$database;

alter database commit to switchover to primary;

select status from v$instance;

shutdown immediate

startup

在standby库上执行

alter database recover managed standby database using current logfile disconnect from session;

 

在primary库上执行

在standby库上执行

alter database recover managed standby database cancel;

alter database open;

conn scott/tiger

select * from t;

重新开启数据同步

alter database recover managed standby database using current logfile disconnect from session;

select open_mode from v$database;

至此，我们又恢复到了原来的状态，primary库是主库，standby是备库。物理备库搭建完毕！

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

转载于:http://blog.itpub.net/24626757/viewspace-2121288/