CentOS 下Oracle11g DataGuard部署配置-泽元知识库

Oracle DataGuard可以实现数据库的热备，11g后也可以用来作为实时的读写分离方案。下文以CentOS5.5+Oracle11g为例说明部署步骤。

主库
ip:10.1.43.230
host:primary
sid:orcl

备库
ip:10.1.43.231
host:standby
sid:orcl
安装时备库先不用装数据库，只安装程序。数据文件由主库复制。

1.primary库操作
1）打开强制日志模式
alter database force logging;

2）检查是否是归档模式
archive log list

如果为如下日志，则未归档
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           2

shutdown immediate
startup mount
alter database archivelog;

3）复制密码文件
scp /opt/oracle/product/11.2.0/db_1/dbs/orapworcl 10.1.43.231:/opt/oracle/product/11.2.0/db_1/dbs/orapworcl

4）设置归档文件地址
建立归档目录 mkdir /opt/oracle/oradata/orcl/archive
alter system set log_archive_dest_1='/opt/oracle/oradata/orcl/archive';

5）增加standby日志文件
startup mount
alter database add standby logfile group 4('/opt/oracle/oradata/orcl/standby_redo01.log') size 50M;
alter database add standby logfile group 5('/opt/oracle/oradata/orcl/standby_redo02.log') size 50M;
alter database add standby logfile group 6('/opt/oracle/oradata/orcl/standby_redo03.log') size 50M;
alter database add standby logfile group 7('/opt/oracle/oradata/orcl/standby_redo04.log') size 50M;

检查结果
select group#,type,member from v$logfile;

6）创建primary数据库初始化参数

通过修改创建的pfile，然后重新生成spfile,
create pfile from spfile;

备份pfile，路径/opt/oracle/product/11.2.0/db_1/dbs/initorcl.ora
主库
*.db_name='orcl'
*.db_unique_name='primary'
*.log_archive_config='dg_config=(primary,standby)'
*.log_archive_dest_1='LOCATION=/opt/oracle/oradata/orcl/archive valid_for=(all_logfiles,all_roles) db_unique_name=primary'
*.log_archive_dest_state_1=enable
*.log_archive_dest_2='SERVICE=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby'
*.log_archive_dest_state_2=enable
*.fal_server=standby
*.fal_client=primary
*.standby_file_management=auto
*.remote_login_passwordfile='EXCLUSIVE'
*.log_archive_format=%t_%s_%r.arc
DB_FILE_NAME_CONVERT='oradata/orcl','oradata/orcl'
LOG_FILE_NAME_CONVERT='oradata/orcl','oradata/orcl'

用pfile启动数据库，使配置生效
shutdown immediate
startup pfile='/opt/oracle/product/11.2.0/db_1/dbs/initorcl.ora'
create spfile from pfile='/opt/oracle/product/11.2.0/db_1/dbs/initorcl.ora';
shutdown immediate
startup

将initorcl.ora复制到备库
scp /opt/oracle/product/11.2.0/db_1/dbs/initorcl.ora 10.1.43.231:/opt/oracle/product/11.2.0/db_1/dbs/

备库
*.db_name='orcl'
*.db_unique_name='standby'
*.log_archive_config='dg_config=(primary,standby)'
*.log_archive_dest_1='LOCATION=/opt/oracle/oradata/orcl/archive valid_for=(all_logfiles,all_roles) db_unique_name=standby'
*.log_archive_dest_state_1=enable
*.log_archive_dest_2='SERVICE=primary lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=primary'
*.log_archive_dest_state_2=enable
*.fal_server=primary
*.fal_client=standby
*.standby_file_management=auto
*.remote_login_passwordfile='EXCLUSIVE'
*.log_archive_format=%t_%s_%r.arc
DB_FILE_NAME_CONVERT='oradata/orcl','oradata/orcl'
LOG_FILE_NAME_CONVERT='oradata/orcl','oradata/orcl'

7)在主库中创建备库的控制文件，注意备库的控制文件要在主库的MOUNT状态下创建。
shutdown immediate
startup mount
alter database create standby controlfile as '/home/oracle/standby.ctl';

复制控制文件到备库
scp /home/oracle/standby.ctl 10.1.43.231:/home/oracle/standby.ctl

复制数据文件到备库
alter database open;
alter database begin backup;
!scp /opt/oracle/oradata/orcl/*.dbf 10.1.43.231:/opt/oracle/oradata/orcl/
alter database end backup;

8)配置listener和tnsname
/opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/11.2.0/db_1/)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /opt/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
     )
)
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY)(PORT = 1521))
    )
)

ADR_BASE_LISTENER = /opt/oracle

/opt/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
PRIMARY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)

STANDBY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)

复制文件到备库
scp /opt/oracle/product/11.2.0/db_1/network/admin/*.ora 10.1.43.231:/opt/oracle/product/11.2.0/db_1/network/admin/
注意修改对应的listener的HOST

至此主库的操作完成。

2.standby备库操作
备库一般只需安装程序而不需要安装数据库，因此相关的文件需要从主库拷贝到备库。在上述过程中scp操作已经拷贝了部分数据。

1)建立相关admin、flash_recovery_area目录
mkdir admin
mdkir orcl/adump
mdkir orcl/dpdump
mkdir orcl/pfile

mkdir flash_recovery_area
mkdir orcl

2）复制控制文件
mv standby.ctl /opt/oracle/oradata/orcl/control01.ctl
cp control01.ctl control02.ctl
cp control01.ctl control03.ctl

注意需要将control02.ctl复制到flash_recovery_area/orcl下

3）切换pfile
shutdown immediate
startup pfile='/opt/oracle/product/11.2.0/db_1/dbs/initorcl.ora'
create spfile from pfile='/opt/oracle/product/11.2.0/db_1/dbs/initorcl.ora';
shutdown immediate
startup

4）测试
测试listener是否配置成功，可通过 tnsping测试
测试权限是否正确
sqlplus sys/oracle@standby as sysdba

3.测试
1）正常启动主库
sqlplus / as sysdba;
startup

2）启动备库
sqlplus /nolog
conn / as sysdba;
startup mount
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

3）在主库上做一次日志切换
ALTER SYSTEM SWITCH LOGFILE;

在主备上查看对应的归档日志情况
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

也可以在主库上查看错误信息
Select status,dest_name,error from v$archive_dest where rownum<5;

4）在主库上建表插入数据并在备库查询
create table test(id integer,name char(10));
insert into test values(1,'test1');
commit;

5）在备库上查询是否同步
select * from test;

注意：如果是oracle10g，不能在同步的时候同时执行查询
需要在备库执行
alter database recover managed standby database disconnect from session;
接收同步后查询需要
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
select * from test;

然后输入
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
继续接收主库的同步数据