|
这篇梳理一下主库不停机状态下搭建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代码语言：javascript 复制 select log_mode,force_logging from v$database; alter database force logging; 2. 主库参数代码语言：javascript 复制 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配置代码语言：javascript 复制 --节点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 代码语言：javascript 复制 --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，注意大小一致 代码语言：javascript 复制 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. 创建参数文件代码语言：javascript 复制 su - oracle cd $ORACLE_HOME/dbs vi initxkdg.ora --添加 db_name=xkdb --启动 startup nmount 2. 创建文件夹代码语言：javascript 复制 --oracle mkdir -p /u01/app/oracle/admin/xkdg/adump 3. 静态监听备库为什么一定要配置静态监听？ nomount状态下必须使用静态监听才能连接到实例 代码语言：javascript 复制 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测试通不通 代码语言：javascript 复制 --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脚本代码语言：javascript 复制 --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完成，登录备库查看状态，欧博allbet执行open 代码语言：javascript 复制 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代码语言：javascript 复制 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有变化，但是原先命令也兼容 代码语言：javascript 复制 --实时同步 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服务,执行下方命令加入集群资源 代码语言：javascript 复制 --详细看-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 另外： 1、不加入集群资源，不能用dbca -silent删库 2、正常dbca静默建库能加入集群资源 本文参与 腾讯云自媒体分享计划，分享自微信公众号。 原始发表：2022-04-14，如有侵权请联系 cloudcommunity@tencent.com 删除 config data database db instance 本文分享自 数据库学习笔记 微信公众号，前往查看 如有侵权，请联系 cloudcommunity@tencent.com 删除。 本文参与 腾讯云自媒体分享计划 ，欢迎热爱写作的你一起参与！ (责任编辑：) |