oracle 19c dataguard 搭建
| IP | sid | DB_UNIQUE_NAME | |
| 主库 | 192.168.31.1 | orcl | orcl |
| 备库 | 192.168.31.2 | orcl | orcldg |
1.主库参数设置
--打开数据强制日志 alter database force logging; --确认数据库开启归档，如未开启归档，请开启归档 archive log list; alter system set log_archive_config='dg_config=(orcl,orcldg)' scope=both; --设置归档日志的本地目录及远程目录,async表示采用异步 alter system set log_archive_dest_1='location=/data/orcl_arch lgwr valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=both; alter system set log_archive_dest_2='service=orcldg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcldg' scope=both; alter system set standby_file_management=auto scope=both; alter system set fal_server='orcldg' scope=spfile; alter system set fal_client='orcl' scope=spfile;2.主库日志文件添加
standby日志组比redo日志多一组即可
alter database add standby logfile group 5 ('/data/ORCL/standbyredo05.log') size 200M; alter database add standby logfile group 6 ('/data/ORCL/standbyredo06.log') size 200M; alter database add standby logfile group 7 ('/data/ORCL/standbyredo07.log') size 200M; alter database add standby logfile group 8 ('/data/ORCL/standbyredo08.log') size 200M; alter database add standby logfile group 9 ('/data/ORCL/standbyredo09.log') size 200M;3.主库添加静态监听及tnsnames.ora添加
SID_LIST_LISTENER = (SID_LIST = (SID_DESC= (GLOBAL_DBNAME=mislake) (ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1) (SID_NAME=orcl) ) )orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) orcldg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldg) ) )4.备库添加静态监听及tnsnames.ora添加
SID_LIST_LISTENER = (SID_LIST = (SID_DESC= (GLOBAL_DBNAME=orcldg) (ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1) (SID_NAME=orcldg) ) )orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) orcldg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldg) ) )5主库创建pfile文件，并将pfile文件及密码文件拷贝至备库
scp orapworcl oracle@192.168.31.2:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapworcldg
create pfile='/home/oracle/1.ora' from spfilescp /home/oracle/1.oracle@192.168.31.2:/home/oracle
6.修改备库pfile文件
对于目录装换
--以下两种配置选择一种即可
*.log_file_name_convert='/data/ORCL','/data/ORCLDG'
*.db_file_name_convert='/data/ORCL','/data/ORCLEDG'
*.db_create_file_dest='/data'
*.db_create_online_log_dest_1='/data'
--------------------
| 场景 | 主库创建方式 | 备库配置 | 是否需要 convert |
|---|---|---|---|
| 场景 1：主备都用 OMF | OMF（不指定路径） | 设了 db_create_file_dest + AUTO | 不需要，OMF 自动接管 |
| 场景 2：主库显式路径，备库用 OMF | 指定完整路径 | 设了 db_create_file_dest + AUTO | 必须配，redo 里是具体路径，OMF 不会自动拦截 |
| 场景 3：主库 OMF，备库没设 OMF | OMF | 没设 db_create_file_dest | 必须配，备库不知道往哪放 |
| 场景 4：都不用 OMF | 显式路径 | 路径不同 | 必须配 |
--添加 *.db_unique_name='orcldg' *.db_create_file_dest='/data' *.db_create_online_log_dest_1='/data' *.standby_file_management='auto' --修改 *.log_archive_dest_1='location=/data/orcl_arch lgwr valid_for=(all_logfiles,all_roles) db_unique_name=orcldg' scope=both; *.log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl' scope=both; *.fal_client='orcldg' *.fal_server='orcl'7.备库创建spfile
create spfile from pfile='/home/orcl/1.ora'8.启动备库至nomount
SQL> startup nomount9.用rman duplicate主库
rman target sys/oracle@orcl auxiliary sys/oracle@orcldg run { allocate channel c1 type disk rate 200M; allocate channel c2 type disk rate 200M; allocate channel c3 type disk rate 200M; allocate auxiliary channel st1 type disk rate 200M; allocate auxiliary channel st2 type disk rate 200M; allocate auxiliary channel st3 type disk rate 200M; duplicate target database for standby from active database nofilenamecheck dorecover; release channel c1; release channel c2; release channel c3; release channel st1; release channel st2; release channel st3; }10.open数据库，并开启同步
SQL> select open_mode from v$database; OPEN_MODE ------------------------------------------------------------ MOUNTED SQL> alter database open; Database altered. SQL> select log_mode,open_mode ,database_role from v$database; LOG_MODE OPEN_MODE DATABASE_ROLE --------------- --------------- -------------------- ARCHIVELOG READ ONLY PHYSICAL STANDBY SQL>alter database recover managed standby database using current logfile disconnect from session;11.验证是否正常同步，可以在主库新建一个表看看备库是否有