基于RAC-RAC搭建DG详细过程以及步骤说明

2020-08-12 00:00:00 创建 修改 数据库 节点 监听

rac-rac setup dg 具体步骤

1 创建备库日志

alter database add standby logfile thread 1 group 5 ('+ASMVG1' ) size 50m,group 6 ('+ASMVG1' ) size 50m;


alter database add standby logfile thread 2 group 7 ('+ASMVG1' ) size 50m,group 8 ('+ASMVG1' ) size 50m;


2 强制记录日志

aler database force logging;


3 备库添加静态监听(grid用户)

静态监听文件


SID_LIST_LISTENER_DUP =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcldg)

      (ORACLE_HOME = /oracle/db/product/11.2)

      (SID_NAME= orcldg1)

    )

  )


LISTENER_DUP =

  (DESCRIPTION=

   (address_list=

   (ADDRESS =(PROTOCOL=TCP)(HOST=192.168.56.107)(PORT=1521))

  )

)


看监听状态,lsnrctl status listener_dup 

实例orcldg1监听状态应该是unknown


4 主备Oracle用户tnsnames.ora文件增加如下内容


这里都使用scan-IP,防止一个实例失败,无法获取日志。这里用作主备之间的service传递传递日志log_archive_dest_2=‘service=orcl …’;


ORCL=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.105)(PORT = 1551))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )


orcldg=  

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.111)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED) 

      (SERVICE_NAME = orcldg) 

    )

  )


########dg-service-configuration############ rman duplicate 使用如下service,这里地址都是公网地址,不能是can-IP

ORCLp=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.105)(PORT = 1551))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )



dup=   

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.107)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcldg) 

       (UR=A) 

    )

  )



测试监听连通性

tnsping orcl  / orcldg / dup


5 备库两个节点修改Oracle用户和Grid用户参数环境变量.bash_profile


参数文件

oracle:

export ORACLE_BASE=/oracle/db

export ORACLE_HOME=$ORACLE_BASE/product/11.2

export ORACLE_SID=orcldg1

export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin:$PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib

export CLASSPATH

ORA_NLS33=$ORACLE_HOME/nls/admin/data

export ORA_NLS33


grid:


export ORACLE_BASE=/oracle/grid

export ORACLE_HOME=/oracle/asm

export ORACLE_SID=+ASM1

export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin:$PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib

export CLASSPATH

ORA_NLS33=$ORACLE_HOME/nls/admin/data

export ORA_NLS33


6 为备库修改参数文件,基于主库修改

###开始rman复制库时的两个别名,主库别名中地址应该用SCAN-IP,备库用新建的静态监听地址

修改对应目录,以对应sid(orcl—orcldg),基于主备ASM存储磁盘组名称形同,asmvg1-数据库,arch—归档


修改:

*.audit_file_dest='/oracle/db/admin/orcldg/adump'          

*.control_files='+ASMVG1/orcldg/control.ctl'

*.remote_listener='racdg-scan:1521'


修改:


orcldg2.thread=2

orcldg1.thread=1

orcldg2.undo_tablespace='UNDOTBS2'

orcldg1.undo_tablespace='UNDOTBS1'

orcldg1.instance_number=1

orcldg2.instance_number=2


新增

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)'

log_archive_dest_1='location=/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'

log_archive_dest_2='service=orcl lgwr  async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'

db_unique_name='orcldg'

db_file_name_convert='+ASMVG1/orcl','+ASMVG1/orcldg'

log_file_name_convert='+ASMVG1/orcl','+ASMVG1/orcldg'

fal_server='orcl'

fal_client='orcldg'  

standby_file_management=auto

后备库参数文件如下:

orcldg2.__db_cache_size=637534208

orcldg1.__db_cache_size=637534208

orcldg1.__java_pool_size=16777216

orcldg2.__java_pool_size=16777216

orcldg1.__large_pool_size=16777216

orcldg2.__large_pool_size=16777216

orcldg1.__oracle_base='/oracle/db'#ORACLE_BASE set from environment

orcldg2.__oracle_base='/oracle/db'#ORACLE_BASE set from environment

orcldg1.__pga_aggregate_target=419430400

orcldg2.__pga_aggregate_target=419430400

orcldg1.__sga_target=1258291200

orcldg2.__sga_target=1258291200

orcldg1.__shared_io_pool_size=0

orcldg2.__shared_io_pool_size=0

orcldg2.__shared_pool_size=570425344

orcldg1.__shared_pool_size=570425344

orcldg1.__streams_pool_size=0

orcldg2.__streams_pool_size=0

*.audit_file_dest='/oracle/db/admin/orcldg /adump'

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.4.0'

*.control_files='+ASMVG1/orcldg/controlfile.ctl'

*.db_block_size=8192

*.db_create_file_dest='+ASMVG1'

*.db_domain=''

*.db_name='orcl'

*.diagnostic_dest='/oracle/db'

orcldg1.instance_number=1

orcldg2.instance_number=2

*.log_archive_dest_1='location=+ARCH'

*.open_cursors=300

*.pga_aggregate_target=414187520

*.processes=2500

*.remote_listener='racdg-scan:1521'

*.remote_login_passwordfile='exclusive'

*.sessions=2755

*.sga_target=1242562560

orcldg2.thread=2

orcldg1.thread=1

orcldg2.undo_tablespace='UNDOTBS2'

orcldg1.undo_tablespace='UNDOTBS1'


LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)'

log_archive_dest_1='location='+ARCH' valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'

log_archive_dest_2='service=orcl  async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'

db_unique_name='orcldg'

db_file_name_convert='+ASMVG1/orcl','+ASMVG1/orcldg'

log_file_name_convert='+ASMVG1/orcl','+ASMVG1/orcldg'

fal_server='orcl'

fal_client='orcldg'

standby_file_management=auto


如果主库中有非OMF创建的文件,需要指定映射路径 ‘+DATA/orcl/datafile ‘,’+DATA/orcldg/datafile’ 备库要提前创建好目录

7 在备库创建所需目录,

oracle:

mkdir -p /oracle/db/admin/orcldg/adump

grid:asmcmd

mkdir +ASMVG1/orcldg/


mkdir +ARCH/orcldg/


根据实际需要创建


9  启动备库实例


内存要一致,否则需要修改内存参数如SGA


startup pfile='/home/oracle/pfileorcldg1.ora' nomount;


然后创建spfile

create spfile from pfile='/home/oracle/pfileorcldg1.ora' 

再使用spfile启动备库

startup nomount;


10  RMAN步骤duplicate   这里主库集群节点1 orcl中地址修改为本地地址如公网地址rac1,(后面可以再改回来)

在主库

[oracle@rac1 admin]$ rman  target sys/oracle@orclp auxiliary sys/oracle@dup


Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 14 12:58:31 2020


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: ORCL (DBID=1533381003)

connected to auxiliary database: ORCL (not mounted)



RMAN> duplicate target database for standby from active database nofilenamecheck;



11 要停止并删除静态监听,否则后面节点一的网络资源处于intermediate状态。

此时静态监听的状态为

[oracle@racdg1 ~]$ lsnrctl status listener_dup


LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-APR-2020 13:17:43


Copyright (c) 1991, 2013, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.107)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                14-APR-2020 12:20:45

Uptime                    0 days 0 hr. 56 min. 57 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/asm/network/admin/listener.ora

Listener Log File         /oracle/grid/diag/tnslsnr/racdg1/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.107)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.109)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

  Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "orcldg" has 1 instance(s).

  Instance "orcldg1", status READY, has 1 handler(s) for this service...

The command completed successfully


停止该监听,并删除配置    <<<报错

TNS-01190: The user is not authorized to execute the requested listener command

关闭数据库后,杀进程,删除配置再


12 查询此时的数据库状态



13 创建spfileASM磁盘组,并修改两个节点的$ORACLE_HOME/dbs/initorcldg1.ora ,$ORACLE_HOME/dbs/initorcldg2.ora为到ASM磁盘组中参数文件的连接

从处于mount状态的数据库创建pfile.此时这个pfile会知道创建好的controlfile位置。

create pfile=‘/home/oracle/pfilenew.ora’ from spfile;


从当前运行的数据库中的spfile创建一个pfile,再将该pfile创建为ASM 上的spfile

SQL> create spfile='+ASMVG1/orcldg/spfileorcldg.ora' from pfile='/home/oracle/pfilenew.ora';


File created.

vi $ORACLE_HOME/dbs/spfileorcldg1.ora 增加如下内容,两个节点都做

spfile='+ASMVG1/orcldg/spfileorcldg.ora'


14 关闭数据库实例(节点1)并注册数据库和实例资源到CRS,修改集群中数据属性为physical_standby

oracle:

srvctl add database -d orcldg –o /oracle/db/product/11.2

root:

srvctl add instance -d orcldg -i orcldg1 -n racdg1

srvctl add instance -d orcldg -i orcldg2 -n racdg2

srvctl modify database -d orcldg -r physical_standby -p '+ASMVG1/orcldg/spfileorcldg.ora'

(如果是主备switchover 切换,新主库需要改为primary角色srvctl modify database -d db_unique_name  -r primary



查看集群的数据库配置信息

[root@racdg1 ~]# srvctl config database -d  orcldg

Database unique name: orcldg

Database name: 

Oracle home: /oracle/db/product/11.2

Oracle user: oracle

Spfile: +ASMVG1/orcldg/spfileorcldg.ora

Domain: 

Start options: open

Stop options: immediate

Database role: PHYSICAL_STANDBY

Management policy: AUTOMATIC

Server pools: orcldg

Database instances: orcldg1,orcldg2

Disk Groups: 

Mount point paths: 

Services: 

Type: RAC

Database is administrator managed



15 重启数据库


srvctl start database -d orcldg -o mount


crsctl stat res -t <<<<<数据库资源状态如下所示

ora.orcldg.db

      1        ONLINE  INTERMEDIATE racdg1                   Mounted (Closed)    

      2        ONLINE  INTERMEDIATE racdg2                   Mounted (Closed) 


16 修改主库参数

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)’;

alter system set  log_archive_dest_1='location=+ARCH valid_for=(all_logfiles,all_roles) db_unique_name=orcl';

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

db_unique_name='orcl'  <<<该参数主库默认为db_name

alter system set db_file_name_convert='+ASMVG1/orcldg' , '+ASMVG1/orcl' scope=spfile

alter system set log_file_name_convert='+ASMVG1/orcldg' , '+ASMVG1/orcl' scope=spfile

alter system set fal_server='orcldg'

alter system set fal_client='orcl'  


用日志

未应用日志时备库日志


ASMCMD> ls

thread_1_seq_44.258.1037714057

thread_1_seq_45.256.1037714035

thread_1_seq_46.257.1037714039

应用如下指令的日志

ASMCMD> ls

thread_1_seq_44.258.1037714057

thread_1_seq_45.256.1037714035

thread_1_seq_46.257.1037714039

thread_2_seq_30.259.1037714345

thread_2_seq_31.260.1037714355

thread_2_seq_32.261.1037714365


SQL> alter database recover managed standby database disconnect from session;   <<<此时会自动向主库要缺少的日志


Database altered.


SQL> alter database recover managed standby database cancel;



在节点1打开备库:

SQL> alter database open;


Database altered.

在一个节点启动mrp进程

启动日志应用

SQL> alter database recover managed standby database using current logfile disconnect;


Database altered.

[oracle@racdg1 ~]$ ps -ef | grep mrp

oracle   13614     1  0 14:01 ?        00:00:00 ora_mrp0_orcldg1

oracle   13703 13264  0 14:02 pts/0    00:00:00 grep mrp


在节点2打开备库

SQL> alter database open;


Database altered.


查询此时集群中数据库状态

crsctl stat res -t 

ora.orcldg.db

      1        ONLINE  ONLINE       racdg1                   Open                

      2        ONLINE  ONLINE       racdg2                   Open    


SQL> select open_mode,database_role,protection_mode from v$database;


OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE

-------------------- ---------------- --------------------

READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE



测试与验证





问题:主库节点2数据库告警日志报错

PING[ARC2]: Heartbeat failed to connect to standby 'orcldg'. Error is 16191.

Tue Apr 14 14:39:24 2020

Error 1017 received logging on to the standby

------------------------------------------------------------

Check that the primary and standby are using a password file

and remote_login_passwordfile is set to SHARED or EXCLUSIVE, 

and that the SYS password is same in the password files.

      returning error ORA-16191


orcl, orcldg使用scan地址。(在使用RMAN复制使用的service要指定公网地址,需要具体到一个节点,rman duplicate会报错。


mrp只能在一个节点启动,否则报错,mrp在一个节点启动后,在两个节点的修改都可以在备库应用。


SQL> alter database recover managed standby database using current logfile disconnect;

alter database recover managed standby database using current logfile disconnect

*

ERROR at line 1:

ORA-01153: an incompatible media recovery is active







相关文章