ITPub博客

首页 > 数据库 > Oracle > ORACLE19C RAC+DG

ORACLE19C RAC+DG

原创 Oracle 作者:hellohf123 时间:2021-09-30 12:52:24 0 删除 编辑

ORACLE19C RAC+DG

RAC搭建参考http://blog.itpub.net/70004783/viewspace-2791938/

dg搭建参考 http://blog.itpub.net/70004783/viewspace-2794470/


此处是上面搭建完成之后rac和dg端的配置

1、rac俩节点加dg节点hosts文件

[root@racdg rpm]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.8.98.102 racdg
#public ip eth0
10.8.98.103         rac1
10.8.98.104         rac2
#priv ip eth1
192.168.1.103     rac1-priv
192.168.1.104     rac2-priv
#vip ip
10.8.98.105         rac1-vip
10.8.98.106         rac2-vip
#scan ip
10.8.98.107         cluster-scan


2、RAC开启归档,主机开启force logging 模式

SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;
alter system set db_unique_name='prod' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(prod,prodstd)' scope=both sid='*';
alter system set log_archive_dest_1='LOCATION=+FRADG/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' scope=both sid='*';
alter system set log_archive_dest_2='SERVICE=tns_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prodstd' scope=both sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';
alter system set db_file_name_convert='+DATADG/','+DATADG/' scope=spfile sid='*';
alter system set log_file_name_convert='+DATADG/','+DATADG/' scope=spfile sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set fal_server='tns_standby' scope=both sid='*';
alter system set fal_CLIENT='tns_primary' scope=both sid='*';


3、重新启动数据库,  检查上述参数配置

set linesize 500 pages 0
col value for a90
col name for a50
select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert', 'log_file_name_convert', 'standby_file_management');


4、查询主库日志文件,并添加standby log

SQL> select thread#,group#,members,bytes/1024/1024 from v$log;
	 1	    1	       1	     200
	 1	    2	       1	     200
	 2	    3	       1	     200
	 2	    4	       1	     200
alter database add standby logfile thread 1 group 21 size 200m;
alter database add standby logfile thread 1 group 22 size 200m;
alter database add standby logfile thread 2 group 23 size 200m;
alter database add standby logfile thread 2 group 24 size 200m;


5、rac端更改lisenter.ora,tnsnames.ora(俩节点)lisenter需要重新reload一下

节点1

[grid@rac1:/home/grid]$cat /u01/app/19c/grid/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
      (SID_NAME = prod1))
  )
MGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR))))		# line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))		# line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))		# line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON		# line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON		# line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF		# line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON		# line added by Agent
VALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET		# line added by Agent
[grid@rac1:/home/grid]$
[grid@rac1:/home/grid]$lsnrctl reload
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-SEP-2021 11:57:47
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[grid@rac1:/home/grid]$


节点2

[grid@rac2:/home/grid]$cat /u01/app/19c/grid/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
      (SID_NAME = prod2))
  )
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))		# line added by Agent
MGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR))))		# line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))		# line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON		# line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON		# line added by Agent
VALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON		# line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF		# line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
[grid@rac2:/home/grid]$lsnrctl reload
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-SEP-2021 12:00:08
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[grid@rac2:/home/grid]$



节点1和2

[oracle@rac1:/u01/app/oracle/product/19c/db_1/network/admin]$cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
tns_primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.107)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
tns_standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
[oracle@rac1:/u01/app/oracle/product/19c/db_1/network/admin]$


测试rac端rman连接

[oracle@rac1:/home/oracle]$rman target sys/oracle@tns_primary
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 30 12:03:52 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database: 
ORA-01017: invalid username/password; logon denied

登陆数据库指定

SQL> alter user sys identified by oracle;
User altered.

再次尝试,成功。

[oracle@rac1:/home/oracle]$rman target sys/oracle@tns_primary
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 30 12:05:04 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PROD (DBID=496022129)
RMAN>


至此rac端已经配置ok



dg端配置

登陆dg端,创建pfile

SQL> create pfile='/u01/app/oracle/product/19c/db_1/dbs/initproddg.ora' from spfile;

配置pfile添加以下

#dg add
*.DB_UNIQUE_NAME=prodstd
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,prodstd)'
*.log_archive_format='ARC_%T_%S_%R.arc'
*.LOG_ARCHIVE_DEST_1='LOCATION=+FRADG/  valid_for=(all_logfiles,all_roles)  db_unique_name=prodstd'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_primary LGWR  SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=tns_primary
*.FAL_CLIENT=tns_standby
*.STANDBY_FILE_MANAGEMENT=AUTO
*.db_file_name_convert='+DATADG/','+DATADG/'
*.log_file_name_convert='+DATADG/','+DATADG/'

最终dg端的pfile为

[oracle@racdg:/u01/app/oracle/product/19c/db_1/dbs]$cat initproddg.ora 
proddg.__data_transfer_cache_size=0
proddg.__db_cache_size=1778384896
proddg.__inmemory_ext_roarea=0
proddg.__inmemory_ext_rwarea=0
proddg.__java_pool_size=0
proddg.__large_pool_size=16777216
proddg.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
proddg.__pga_aggregate_target=822083584
proddg.__sga_target=2466250752
proddg.__shared_io_pool_size=134217728
proddg.__shared_pool_size=520093696
proddg.__streams_pool_size=0
proddg.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='+DATADG/PROD/CONTROLFILE/current.260.1084618301'
*.db_block_size=8192
*.db_create_file_dest='+DATADG'
*.db_name='prod'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=proddgXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_PRODDG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=782m
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2346m
*.undo_tablespace='UNDOTBS1'
#dg add
*.DB_UNIQUE_NAME=prodstd
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,prodstd)'
*.log_archive_format='ARC_%T_%S_%R.arc'
*.LOG_ARCHIVE_DEST_1='LOCATION=+FRADG/  valid_for=(all_logfiles,all_roles)  db_unique_name=prodstd'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_primary LGWR  SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=tns_primary
*.FAL_CLIENT=tns_standby
*.STANDBY_FILE_MANAGEMENT=AUTO
*.db_file_name_convert='+DATADG/','+DATADG/'
*.log_file_name_convert='+DATADG/','+DATADG/'
[oracle@racdg:/u01/app/oracle/product/19c/db_1/dbs]$


修改 lisenter.ora,tnsnames.ora    lisenter需要重新reload一下

[grid@racdg:/home/grid]$cat /u01/app/19c/grid/network/admin/listener.ora
#Backup file is  /u01/app/grid/crsdata/racdg/output/listener.ora.bak.racdg.grid line added by Agent
# listener.ora Network Configuration File: /u01/app/19c/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
      (SID_NAME = proddg))
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = racdg)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON		# line added by Agent
[grid@racdg:/home/grid]$
[grid@racdg:/home/grid]$lsnrctl reload
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-SEP-2021 12:11:07
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racdg)(PORT=1521)))
The command completed successfully


tnsnames.ora

[oracle@racdg:/u01/app/oracle/product/19c/db_1/network/admin]$cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racdg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
tns_primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.107)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
tns_standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
LISTENER_PRODDG =
  (ADDRESS = (PROTOCOL = TCP)(HOST = racdg)(PORT = 1521))
[oracle@racdg:/u01/app/oracle/product/19c/db_1/network/admin]$


从pfile启动数据库到nomount状态

SQL> startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/initproddg.ora';
ORACLE instance started.
Total System Global Area 2466247664 bytes
Fixed Size		    9139184 bytes
Variable Size		  536870912 bytes
Database Buffers	 1912602624 bytes
Redo Buffers		    7634944 bytes
SQL>

pfile生成spfile

SQL>  create spfile='/u01/app/oracle/product/19c/db_1/dbs/spfileproddg.ora' from pfile;
File created.
SQL>


grid用户登陆asmcmd删除所有的文件夹

ASMCMD> rm -rf prod/
ASMCMD>


dg通过spfile启动到nomount状态

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2466247664 bytes
Fixed Size		    9139184 bytes
Variable Size		  536870912 bytes
Database Buffers	 1912602624 bytes
Redo Buffers		    7634944 bytes
SQL> show parameter spfile;
NAME				     TYPE
------------------------------------ ----------------------
VALUE
------------------------------
spfile				     string
/u01/app/oracle/product/19c/db
_1/dbs/spfileproddg.ora
SQL>

检测rman联通性

[oracle@racdg:/home/oracle]$rman target sys/oracle@tns_standby
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 30 12:38:43 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PROD (not mounted)
RMAN>

ok


6 . 实施数据库克隆通过rman duplicate    (在备机操作) 恢复时关闭rac的第二个节点,恢复完开启即可

[oracle@racdg:/home/oracle]$rman target sys/oracle@tns_primary auxiliary sys/oracle@tns_standby
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 30 12:40:39 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PROD (DBID=496022129)
connected to auxiliary database: PROD (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
恢复完成之后,打开数据库,并且开启实时应用同步
SQL> alter database open;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.



查看主机备机状态,确认dataguard搭建完成。

rac查看状态

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE;
OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ WRITE	     PRIMARY	      FAILED DESTINATION   MAXIMUM PERFORMANCE
SQL>

有时需要等一会才会

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE;
OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ WRITE	     PRIMARY	      TO STANDBY	   MAXIMUM PERFORMANCE
SQL>


dg端查看状态

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE;
OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED	   MAXIMUM PERFORMANCE
SQL>


恢复完成后可以删除过度的pfile,与新的spfile是有区别的


7、验证

主机创建一个tablespace,一个账户,一个表,插入一条数据,到备机查看


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

下一篇: oracle冷备恢复
请登录后发表评论 登录
全部评论

注册时间:2021-08-05

  • 博文量
    49
  • 访问量
    14805