这篇梳理一下主库不停机状态下搭建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
--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完成，登录备库查看状态，执行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静默建库能加入集群资源
