Oracle11gR2 RAC配置单实例DG On CentOS6.5

发表于 | 分类于 ， ， | | 阅读量
字数统计: 1,939 | 阅读时长 ≈ 10

前言

最近项目要求给生产环境中的RAC数据库配置DG,过程中遇到不少问题，以此记录，避免同一个问题犯二。

环境

主库

node instance_name db_name db_unique_name IP
rac1 wwlc1 wwlc wwlc 192.168.66.66
rac2 wwlc2 wwlc wwlc 192.168.66.88

备库

instance_name db_name db_unique_name IP
wwlc wwlc wwlcdg 192.168.66.237

安装数据库软件(备库)

安装过程略，可参见：Linux下安装Oracle11.2.0.1小结
PS：只安装软件不创建数据库

确保主库处于归档模式(主库)

1
2
3
4
5
6
7
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +ARCH/wwlc/archfile
Oldest online log sequence     1128
Next log sequence to archive   1131
Current log sequence           1131

如果主库处于非归档模式，需要修改：

1
2
3
4
5
6
su - oracle
SQL> alter system set log_archive_dest_1='location=+ARCH/wwlc/archfile valid_for=(all_logfiles,all_roles) db_unique_name=wwlc' scope=both sid='*';
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

确保主库强制Logging(主库)

1
2
3
4
SQL> select force_logging from v$database;
FORCE_LOGGING
------------------------------
YES

如果主库处于非归档模式，需要修改：

1
SQL> alter database force logging;

配置DG所需参数(主库)

1
2
3
4
5
6
7
8
SQL> alter system set log_archive_config='DG_CONFIG=(wwlc,wwlcdg)' scope=both sid='*';
SQL> alter system set log_archive_dest_1='location=+ARCH/wwlc/archfile valid_for=(all_logfiles,all_roles) db_unique_name=wwlc' scope=both sid='*';
SQL> alter system set log_archive_dest_2='service=wwlcdg LGWR ASYNC NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=wwlcdg' scope=both sid='*';
SQL> alter system set standby_file_management=auto scope=both sid='*';
SQL> alter system set db_file_name_convert='/home/s01/app/oracle/oradata/wwlcdg','+DATA/wwlc/datafile' scope=spfile sid='*';
SQL> alter system set log_file_name_convert='/home/s01/app/oracle/oradata/wwlcdg/onlinelog','+DATA/wwlc/onlinelog' scope=spfile sid='*';
SQL> alter system set fal_client='wwlc' scope=both sid='*';
SQL> alter system set fal_server='wwlcdg' scope=both sid='*';

生成pfile并同密码文件一起传输到备库相应目录(主库)

1
2
3
SQL> create pfile='/home/oracle/pfile.ora' from spfile;
[oracle@rac1 ~]$ scp /home/oracle/temp/pfile root@192.168.66.237:/home/s01/app/oracle/product/11.2.0/db_1/dbs/initwwlc.ora
[oracle@rac1 ~]$ scp $ORACLE_HOME/dbs/orapwwwlc12 root@192.168.66.237:/home/s01/app/oracle/product/11.2.0/db_1/dbs/orapwwwlc

创建目录(备库)

1
2
3
4
[oracle@backup ~]$ mkdir -pv  /home/s01/app/oracle/oradata/wwlcdg/onlinelog
[oracle@backup ~]$ mkdir -pv  /home/s01/app/oracle/fast_recovery_area/wwlcdg
[oracle@backup ~]$ mkdir -pv  /home/s01/app/oracle/admin/wwlc/adump
[oracle@backup ~]$ mkdir -pv  /home/s01/app/oracle/archive/wwlcdg

配置监听(备库)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[oracle@backup ~]$ vim $ORACLE_HOME/network/admin/listener.ora
添加如下配置：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = wwlc)
      (ORACLE_HOME = /home/s01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = wwlc)
    )
  )
  
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.66.237)(PORT = 1521))
    )
  )

配置tnsnames.ora(主库、备库)

rac所有节点 、DG备库添加同样的配置，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[oracle@backup ~]$ vim $ORACLE_HOME/network/admin/tnsnames.ora
添加如下配置：
wwlcdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.66.237)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = wwlc)
    )
  )
wwlc =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.66.66)(PORT = 1521))
	(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.66.88)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = wwlc)
    )
  )

修改启动参数(备库)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[oracle@backup ~]$ vim /home/s01/app/oracle/product/11.2.0/db_1/dbs/initwwlc.ora
添加如下配置：
*.audit_file_dest='/home/s01/app/oracle/admin/wwlc/adump'
*.audit_trail='db'
*.cluster_database=FALSE
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=16
*.control_files='/home/s01/app/oracle/oradata/wwlcdg/control01.ctl','/home/s01/app/oracle/fast_recovery_area/wwlcdg/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA/wwlc/datafile','/home/s01/app/oracle/oradata/wwlcdg'
*.db_name='wwlc'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=wwlcXDB)'
*.fal_client='wwlcdg'
*.fal_server='wwlc'
*.log_archive_config='DG_CONFIG=(wwlc,wwlcdg)'
*.log_archive_dest_1='location=/home/s01/app/oracle/archfile/wwlcdg valid_for=(all_logfiles,all_roles) db_unique_name=wwlcdg'
*.log_archive_dest_2='service=wwlcpm LGWR ASYNC NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=wwlc'
*.log_file_name_convert='+DATA/wwlc/onlinelog','/home/s01/app/oracle/oradata/wwlcdg/onlinelog'
*.memory_max_target=8589934592
*.memory_target=7589934592
*.open_cursors=300
*.pga_aggregate_target=0
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=0
*.standby_file_management='AUTO'
*.db_unique_name='wwlcdg'
*.service_names='wwlc'
*.undo_tablespace='UNDOTBS1'

启动数据库至nomount状态(备库)

1
2
SQL> create spfile from pfile;
SQL> startup nomount

复制活动数据库(备库)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[oracle@backup ~]$ rman target sys@wwlc auxiliary sys@wwlcdg
RMAN> run {
allocate channel c11 type disk;
allocate channel c12 type disk;
allocate channel c13 type disk;
allocate channel c14 type disk;
allocate auxiliary channel c21 type disk;
allocate auxiliary channel c22 type disk;
allocate auxiliary channel c23 type disk;
allocate auxiliary channel c24 type disk;
duplicate target database for standby from active database nofilenamecheck dorecover
spfile
parameter_value_convert 'wwlc','wwlcdg'
set db_unique_name='wwlcdg'
set log_archive_config='DG_CONFIG=(wwlc,wwlcdg)'
set log_archive_dest_1='location=/home/s01/app/oracle/archfile/wwlcdg valid_for=(all_logfiles,all_roles) db_unique_name=wwlcdg'
set log_archive_dest_2='service=wwlcpm LGWR ASYNC NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=wwlc'
set db_file_name_convert='+DATA/wwlc/datafile','/home/s01/app/oracle/oradata/wwlcdg'
set log_file_name_convert='+DATA/wwlc/onlinelog','/home/s01/app/oracle/oradata/wwlcdg/onlinelog'
set control_files='/home/s01/app/oracle/oradata/wwlcdg/control01.ctl'
set fal_client='wwlcdg'
set fal_server='wwlc'
set standby_file_management='AUTO'
set cluster_database='false'
set diagnostic_dest='/home/s01/app/oracle'
set audit_file_dest='/home/s01/app/oracle'
set memory_max_target='8589934592'
set memory_target='7589934592'
set REMOTE_LISTENER=''
;
}

添加standby log(主库、备库)

原则

RAC每个 thread都需要创建且standby redo log 比 redo log 多一组,大小相同

主库

1
2
3
4
5
6
7
8
9
10
11
我的RAC是每个thread 4组日志，每组一个日志文件，所以需要为每个thread创建5组 standby log。
SQL> ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 9 ('+DATA') size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 10 ('+DATA') size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 11 ('+DATA') size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 12 ('+DATA') size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 13 ('+DATA') size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  thread 2 GROUP 14 ('+DATA') size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  thread 2 GROUP 15 ('+DATA') size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  thread 2 GROUP 16 ('+DATA') size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  thread 2 GROUP 17 ('+DATA') size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  thread 2 GROUP 18 ('+DATA') size 501M;

备库

1
2
3
4
5
6
7
8
9
10
因为duplicate的时候，两个thread各4个redo log被同步到备库，因此需要创建9组 standby log。
SQL> ALTER DATABASE ADD STANDBY LOGFILE  GROUP 9 '/home/s01/app/oracle/oradata/wwlcdg/onlinelog/sredo01.log' size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  GROUP 10 '/home/s01/app/oracle/oradata/wwlcdg/onlinelog/sredo02.log' size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  GROUP 11 '/home/s01/app/oracle/oradata/wwlcdg/onlinelog/sredo03.log' size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  GROUP 12 '/home/s01/app/oracle/oradata/wwlcdg/onlinelog/sredo04.log' size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  GROUP 13 '/home/s01/app/oracle/oradata/wwlcdg/onlinelog/sredo05.log' size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  GROUP 14 '/home/s01/app/oracle/oradata/wwlcdg/onlinelog/sredo06.log' size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  GROUP 15 '/home/s01/app/oracle/oradata/wwlcdg/onlinelog/sredo07.log' size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  GROUP 16 '/home/s01/app/oracle/oradata/wwlcdg/onlinelog/sredo08.log' size 501M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE  GROUP 17 '/home/s01/app/oracle/oradata/wwlcdg/onlinelog/sredo09.log' size 501M;

启动物理备库并开启管理恢复进程(MRP)(备库)

1
2
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;

验证

可通过在主库暂停、开启备用归档目录和切换日志查看数据同步情况。

1
2
3
4
5
6
SQL> alter system set log_archive_dest_state_2=defer;
SQL> alter system set log_archive_dest_state_2=enable;
SQL> alter system switch logfile;
SQL> select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
SQL> select sequence#,applied from v$archived_log order by sequence#;
SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;

踩坑

  • RMAN-06217: not connected to auxiliary database with a net service name
    duplicate target database for standby from active database需要使用net service name连接进行账号密码验证，如果使用操作系统验证会出现此错误。
  • RMAN-04006: error from auxiliary database、ORA-12528: TNS:listener: all appropriate instances are blocking new connections
    备用数据库处于nomount状态，无法动态注册到监听，通过net service name连接auxiliary时无法找到服务，可通过在监听器中添加静态注册解决。
  • ORA-10458: standby database requires recovery
    duplicate后打开物理备库可能会报此错误，此时只要打开备库的 日志恢复，让备库apply重做日志即可。
  • ORA-17628, ORA-19505
    db_file_name_convert没有设置，或者设置错误。
  • RMAN-05517: temporary file * conflicts with file used by target dat
    备库pfile或者duplicate时添加 nofilenamecheck选项，配置正确的db_file_name_convert、log_file_name_convert参数。
  • MAN-04017: startup error description: ORA-00439: feature not enabled: Real Application Clusters
    由于主库是 rac ，备库是单实例，所以备库pfile或者duplicate时，需要设置 cluster_database =’false’。
  • RMAN-05535: WARNING: All redo log files were not defined properly.
    log_file_name_convert参数必须配置，且为redo日志而不是archive log。
  • RMAN-05503: at least one auxiliary channel must be allocated to execute this command
    手动分配复制通道时(allocate channel) 必须要加上allocate auxiliary channel。
  • RMAN-06024: no backup or copy of the control file found to restore
    如果数据库没有做过全备，则duplicate命令必须带关键词” from active database”。
  • RMAN-06034: at least 1 channel must be allocated to execute this command
    如果duplicate命令中使用关键词” from active database”,则必须为主库分配通道。
  • ORA-17628: Oracle error 19505 returned by remote Oracle server
    相关路径必须存在，比如控制文件路径不存在。
  • ORA-16047: DGID mismatch between destination setting and standby
    log_archive_config参数未配置
  • ORA-16057: server not in Data Guard configuration
    log_archive_config参数错误，千万注意：dg_config 中必须是db_unique_name,而且主备库都必须配置正确。

参考

CentOS 6.5 部署 oracle 11G RAC+DG