Oracle 19c DG主库不停机搭建（主库RAC、备库standalone）

甚至熊熊

发布于 2022-04-27 11:05:19

4.3K00

代码可运行

文章被收录于专栏：数据库学习笔记数据库学习笔记

运行总次数：0

代码可运行

这篇梳理一下主库不停机状态下搭建DG备库的流程。

一、环境规划

	主库(RAC)	备库(standalone)	说明
db_name	xkdb	xkdb	必须一致
db_unique_name	xkdb	xkdg	必须不一致
instance_name	xkdb	xkdg	一致不一致都行
IP	192.168.10.101/102	192.168.10.8
tns_name	tnsxkdb	tnsxkdg
数据盘	+DATA	+DG_DATA
归档盘	+CRS	+DG_REDO

standby不用建库

二、主库设置

1. 主库是否开启归档及force logging

select log_mode,force_logging from v$database;
alter database force logging;

2. 主库参数

alter system set log_archive_config='DG_CONFIG=(xkdb,xkdg)' scope=both sid='*';

alter system set log_archive_dest_1='LOCATION=+data VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=xkdb' scope=both sid='*';

alter system set log_archive_dest_2='SERVICE=tnsxkdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=xkdg' scope=both sid='*';

alter system set standby_file_management=auto scope=both sid='*';

alter system set fal_client='tnsxkdb' scope=both sid='*';
alter system set fal_server='tnsxkdg' scope=both sid='*';

/* 注意：以下两个参数是需要重启后生效的，为了实现主库不停机，我们在duplicate的时候设置
1、db_file_name_convert
2、log_file_name_convert
另外有些教程会让修改主库的db_unique_name也是需要重启，但是该参数安装完成后就有值，所以没必要
*/

3. TNS配置

--节点1:
tnsxkdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xkdb)
    )
  )

tnsxkdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.8)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xkdb)
    )
  )

--节点2:
tnsxkdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xkdb)
    )
  )

tnsxkdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.8)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xkdb)
    )
  )

4. 拷贝密码文件

将主库密码文件传输到备库查询主库pw文件位置

方法1：srvctl config database -d xkdb
方法2：asmcmd 进去 pwget --dbuniquename xkdb

--grid
asmcmd pwcopy +DATA/XKDB/PASSWORD/pwdxkdb.267.1099262109 /tmp/mypwfile

--root
scp /tmp/mypwfile 192.168.10.8:/tmp

--备库
cd /tmp
mv mypwfile orapwxkdg
chown oracle:oinstall orapwxkdg
cp orapwxkdg $ORACLE_HOME

5. 添加附加日志

redo log数量+1，注意大小一致

alter database add standby logfile thread 1 group 11 ('+DATA') size 200M;
alter database add standby logfile thread 1 group 12 ('+DATA') size 200M;
alter database add standby logfile thread 1 group 13 ('+DATA') size 200M;

三、备库设置

1. 创建参数文件

su - oracle
cd $ORACLE_HOME/dbs
vi initxkdg.ora

--添加
db_name=xkdb 

--启动
startup nmount

2. 创建文件夹

--oracle
mkdir -p /u01/app/oracle/admin/xkdg/adump

3. 静态监听

备库为什么一定要配置静态监听？ nomount状态下必须使用静态监听才能连接到实例

su - grid
--监听参数文件添加名为listener1的静态监听
LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xkdg)(PORT = 1522))
      )
    )
  )

SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = xkdb)
      (SID_NAME = xkdg)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
    )
  )

--启动
lsnrctl start listener1

4. TNS配置

主备库tns配置完可以分别tnsping测试通不通

--vi $ORACLE_HOME/network/admin/tnsnames.ora
TNSXKDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.8)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xkdb)
    )
  )

TNSXKDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.101)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xkdb)
    )
  )

四、duplicate创建备库

1. duplicate脚本

--standby
rman target sys/"Oracle123"@tnsxkdb auxiliary sys/"Oracle123"@tnsxkdg << EOF
run {
	allocate channel c1 type disk;
	allocate channel c2 type disk;
	allocate channel c3 type disk;
	allocate channel c4 type disk;
	allocate channel c5 type disk;
	allocate channel c6 type disk;
	allocate channel c7 type disk;
	allocate channel c8 type disk;
	allocate auxiliary channel s1 type disk;
	allocate auxiliary channel s2 type disk;
	allocate auxiliary channel s3 type disk;
	allocate auxiliary channel s4 type disk;
	allocate auxiliary channel s5 type disk;
	allocate auxiliary channel s6 type disk;
	allocate auxiliary channel s7 type disk;
	allocate auxiliary channel s8 type disk;
	duplicate target database
		for standby
		from active database nofilenamecheck
		dorecover
		spfile
		parameter_value_convert 'xkdb','xkdg','+DATA','+DG_DATA','+CRS','+DG_REDO'
		set db_name='XKDB'
		set db_unique_name='xkdg'
		set db_create_file_dest='+DG_DATA'
		set db_create_online_log_dest_1='+DG_REDO'
		set cluster_database='FALSE'
		set fal_server='tnsxkdb'
		set remote_listener=''
		set local_listener=''
		set standby_file_management='AUTO'
		set log_archive_dest_1='LOCATION=+DG_DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=xkdg'
		set log_archive_dest_2='SERVICE=tnsxkdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=xkdb'
		set control_files='+DG_DATA'
		set db_file_name_convert='+DATA','+DG_DATA'
		set log_file_name_convert='+CRS','+DG_REDO'
		set instance_name='xkdg'
		;
	}
EOF

2.备库状态查看

duplicate完成，登录备库查看状态，执行open

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 TEST01                         MOUNTED
SQL> alter database open;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST01                         MOUNTED

3. 备库添加standby redo log

alter database add standby logfile thread 1 group 11 ('+DG_DATA') size 200M;
alter database add standby logfile thread 1 group 12 ('+DG_DATA') size 200M;
alter database add standby logfile thread 1 group 13 ('+DG_DATA') size 200M;
													   
alter database add standby logfile thread 2 group 14 ('+DG_DATA') size 200M;
alter database add standby logfile thread 2 group 15 ('+DG_DATA') size 200M;
alter database add standby logfile thread 2 group 16 ('+DG_DATA') size 200M;

五、开启同步

19c的开启同步命令与11g有变化，但是原先命令也兼容

--实时同步
alter database recover managed standby database disconnect;

--日志切换才同步
alter database recover managed standby database using archived logfile disconnect;

--取消同步
alter database recover managed standby database cancel;

--查看状态
select name,open_mode,database_role,protection_mode,protection_level from v$database;

六、需要注意的

建完备库，发现能够正常登录使用，但是在集群资源里没有db服务,执行下方命令加入集群资源

--详细看-h
srvctl add database -db xkdg -o /u01/app/oracle/product/19.0.0/dbhome_1 -spfile /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilexkdg.ora -role PHYSICAL_STANDBY -pwfile /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwxkdg -instance xkdg