Oracle19C DataGuard搭建
一、配置前提
主库 10.135.245.106 sid:orcl service:orcl
备库 10.135.245.107 sid:orcl service:orcldg
二、配置过程
1.判断DG是否已经安装
select * from v$option where parameter = ‘Oracle Data Guard‘;
如果是true表示已经安装可以配置,否则需要安装相应组件。
2.设置主、从库为强制记录日志。(主、从库)
(1)开启强制归档
1)SQL> conn / as sysdba (以DBA身份连接数据库)
2)SQL> shutdown immediate; (立即关闭数据库)
3)SQL> startup mount (启动实例并加载数据库,但不打开)
4)SQL> alter database archivelog; (更改数据库为归档模式)
5)alter database force logging; (设置强制归档)
(2)sql>select name,force_logging from v$database;检查状态(YES为强制)
(3)如果需要在主库添加或者删除数据文件时,这些文件也会在备份添加或删除,使用如下:
sql> alter system set STANDBY_FILE_MANAGEMENT=auto;(设置自动同步)
sql>show parameter standby (默认此参数是manual手工方式)
3.创建standby log files(备用日志文件)(主、从库都建立)
sql> alter database add standby logfile group 11 ‘D:\app\Administrator\oradata\ORCL\DG\standby11.log‘ size 200M;
sql> alter database add standby logfile group 12 ‘D:\app\Administrator\oradata\ORCL\DG\standby12.log‘ size 200M;
sql> alter database add standby logfile group 13 ‘D:\app\Administrator\oradata\ORCL\DG\standby13.log‘ size 200M;
sql> alter database add standby logfile group 14 ‘D:\app\Administrator\oradata\ORCL\DG\standby14.log‘ size 200M;
4.密码文件和控制文件的创建传输
(1)windows server系统密码文件在D:\app\Administrator\product\19.3.0\dbhome_1\database),如果没有
sql>orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle
(2)检查REMOTE_LOGIN_PASSWORDFILE值是否为 EXCLUSIVE
sql>show parameter REMOTE_LOGIN_PASSWORDFILE
如果值不是EXCLUSIVE,则:
alter system set remote_login_passwordfile=exclusive scope=spfile;
(3)密码文件需要scp到从库
windows环境
拷贝D:\app\Administrator\product\19.3.0\dbhome_1\database\PWDorcl.ora 到备库对应目录
5.db_name和db_unique_name
默认db_name和db_unique_name和实例名是一致的,这里是orcl需要注意在DG中主库和从库的db_unique_name是不能一致的,需要区分开的。这里我们设置主库的db_unique_name为orcl,从库为orcldg.
sql>show parameter db_unique_name
sql>alter system set db_unique_name=orcl scope=spfile; (主库)
sql>alter system set db_unique_name=orcldg scope=spfile; (从库)
6.闪回数据库:
(1)快速恢复区(Flash/Fast Recovery Area),默认是配置的,但是需要确认这个区域的磁盘够大,至少300G以上(默认3G)
sql>show parameter db_recovery_file_dest
主、备都执行
更改大小:
sql>alter system set db_recovery_file_dest_size=200G;
主、备都执行
可以修改位置:
sql>alter system set db_recovery_file_dest=‘D:\app\Administrator\recovery‘;
(2)查看是否启用,默认是不开启的(主库开启,备库在后面第17步开启)
sql>select flashback_on from v$database;
开启:
sql>alter database flashback on;
如果你碰到 ORA-01153 报错,那一定是在备库进行此操作。
如果不启用闪回日志,当出现故障转移时,你将需要完全重新开始创建一个备库。
7.SQL*NET设置
(1)配置主库的监听
虽然可以通过netca来进行配置,但是除了这个默认的外,我们还需要一个静态注册SID_LIST_LISTENER,如果没有此参数dataguard启动顺序不正确,主库会报PING[ARC1]:Heartbeat failed to connect to standby ‘***‘.Error is 12514导致归档无法完成。
配置如下:
listener.ora 加入下面的内容
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.135.245.106)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL) #增加_DGMGRL,后面的DG Broker使用
(ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcldg_DGMGRL)
(ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orclpdb_DGMGRL)
(ORACLE_HOME =D:\WINDOWS.X64_193000_db_home)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = xukepdb_DGMGRL)
(ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
(SID_NAME = orcl)
)
)
(2)配置tnsnames
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.135.245.106)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.135.245.106)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.135.245.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
(UR=A)
)
)
(3)传输到备库并修改listener.ora和tnsnames.ora
修改listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.135.245.107)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL) #增加_DGMGRL,后面的DG Broker使用
(ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcldg_DGMGRL)
(ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orclpdb_DGMGRL)
(ORACLE_HOME =D:\WINDOWS.X64_193000_db_home)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = xukepdb_DGMGRL)
(ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
(SID_NAME = orcl)
)
)
tnsnames.ora不变
8.重做日志传输配置
(1)配置归档日志位置:
查询已经设置的归档路径sql:archive log list或者show parameter log_archive_dest_1
(主库操作)
sql> alter system set log_archive_dest_1=‘LOCATION=D:\app\Administrator\archive valid_for=(all_logfiles,all_roles) db_unique_name=orcl‘ scope=spfile;
SQL> alter system set log_archive_dest_state_1=‘enable‘ scope=spfile;
(备库操作)
sql> alter system set log_archive_dest_1=‘LOCATION=D:\app\Administrator\archive valid_for=(all_logfiles,all_roles) db_unique_name=orcldg‘ scope=spfile;
SQL> alter system set log_archive_dest_state_1=‘enable‘ scope=spfile;
(注:官方文档里说使用 valid_for=(online_logfiles, all_roles),这将导致备库无法归档备用日志文件,因为它们不是在线日志。但如果使用 all_logfiles 选项,主备库将都能归档在线以及备用日志。)
(2)配置重做日志到备份库:
(主库操作)
sql>alter system set log_archive_dest_2=‘SERVICE=orcldg lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=orcldg‘;
SQL> alter system set log_archive_dest_state_2=‘enable‘ scope=spfile;
(备库操作)
SQL> alter system set log_archive_dest_2=‘SERVICE=orcl LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl‘;
SQL> alter system set log_archive_dest_state_2=‘enable‘ scope=spfile;
(3)设置db_file_name_convert和log_file_name_conver参数:
(主库、备库都操作)
SQL> alter system set log_file_name_convert=‘D:\app\Administrator\archive‘,‘D:\app\Administrator\archive‘ scope=spfile;
SQL> alter system set db_file_name_convert=‘D:\app\Administrator\oradata\ORCL\DG‘,‘D:\app\Administrator\oradata\ORCL\DG‘ scope=spfile;
(4)要注意STANDBY_ARCHIVE_DEST 参数不需要,已经被官方弃用。设置此参数后启动数据库,只会报 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance 错。
9.配置FAL_SERVER
这个参数指定当日志传输出现问题时,备库到哪里去找缺少的归档日志。它用在备库接收的到的重做日志间有缺口的时候。这种情况会发生在日志传输出现中断时,比如你需要对备库进行维护操作。在备库维护期间,没有日志传输过来,这时缺口就出现了。设置了这个参数,备库就会主动去寻找那些缺少的日志,并要求主库进行传输。
主库使用 :fal_server=从库
从库上就反过来:fal_server=主库
sql>alter system set FAL_SERVER=‘orcldg‘; (主库)
alter system set fal_client=orcl scope=spfile; (主库)
sql>alter system set FAL_SERVER=‘orcl‘; (从库)
alter system set fal_client=orcldg scope=spfile; (从库)
10.Data Guard 配置里的另外一个库的名字(主备)
主库操作
sql> alter system set log_archive_config = ‘dg_config=(orcl,orcldg)‘;
备库操作
sql> alter system set log_archive_config = ‘dg_config=(orcldg,orcl)‘;
11.主库生成pfile (initorcl.ora),并传输到备库进行修改
主库操作
sql>create pfile from spfile;
initorcl.ora
拷贝至备库
备库操作
修改备库的initorcl.ora 修改参数如下:
*.db_name=‘orcl‘
*.db_unique_name=‘orcldg‘
*.fal_server=‘orcl‘
*.log_archive_config=‘dg_config=(orcldg,orcl)‘
*.log_archive_dest_1=‘LOCATION=/home/oracle/app/oracle/archive valid_for=(all_logfiles,primary_role) db_unique_name=orcldg‘
*.log_archive_dest_2=‘SERVICE=orcl lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=orcl‘
12.启动备库到nomount状态: (备库操作)
SQL> shutdown immediate
SQL> create spfile from pfile=‘D:\WINDOWS.X64_193000_db_home\database\initorcl.ora‘;
SQL> startup nomount
13.在主库上执行RMAN 并连接主数据库和备数据库
rman target / auxiliary sys/password@orcldg
RMAN>duplicate target database for standby from active database nofilenamecheck dorecover;
若出现RMAN-05541则
alter system switch logfile;
14.启用物理备用数据库: (duplicate 完成之后,备库是mount的)
备库操作
SQL>select status,instance_name from v$instance;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
15.启用主库
SQL> startup
SQL> alter pluggable database all open;
16. 主备库角色验证、查看主备库同步状态
(1)分别在主备库上,执行下面的SQL进行验证:
主库:SQL> select database_role,switchover_status from v$database;
primary to standby
备库:SQL> select database_role,switchover_status from v$database;
physical standby not allowed
(2)查看归档日志同步状况
SQL> alter system switch logfile; //主库
sql>select sequence# from v$archived_log; 查看日志序号
sql>select sequence#,applied from v$archived_log;备库验证
17.备库开启flashback:
SQL> select flashback_on from v$database;
SQL> alter database recover managed standby database cancel;
SQL> alter database flashback on;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
18.启动备库到只读模式
SQL> recover managed standby database cancel;
SQL> alter database open read only;
SQL> alter pluggable database all open;
SQL> recover managed standby database disconnect from session;
三、配置DG Broker服务
1、主备库启用broker
主备库上执行下面的SQL启用broker:
SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;
2、创建broker配置文件
主库执行
dgmgrl sys@orcl as sysdba
DGMGRL>create configuration dg_cfg as primary database is orcl connect identifier is orcl;
DGMGRL>add database orcldg as connect identifier is orcldg maintained as physical;
注:如果添加standby数据库的时候,报如下错误:
ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
在备库上,将LOG_ARCHIVE_DEST_2参数的值清空重新添加即可。
SQL> alter system set LOG_ARCHIVE_DEST_2=‘ ‘ scope=both; (备库操作)
3、启动DGMGRL配置
DGMGRL> show configuration; 查看配置文件信息
DGMGRL> enable configuration; 开启配置信息
DGMGRL> show database orcl 查看orcl配置
DGMGRL> show database orcldg 查看orcldg配置
4、使用dgmgrl执行主备切换
DGMGRL> show configuration;
DGMGRL> switchover to orcldg; 主备库切换
DGMGRL> show configuration;
详细的DG Broker再另专题叙述。