|
Oracle 19c Dataguard 物理备库搭建指南
部署目标 搭建两节点 Oracle 19c Dataguard 环境 主库不关闭 使用物理备库 不使用Broker 部署环境介绍 操作系统版本：OEL 7.6 数据库版本：Oracle Database 19c 两台主机名为：sdedu，欧博官网 sdrep 主库名称：SS19P 备库名称：SS19S 操作步骤 (1). 查看主库归档和附加日志配置，如下的输出显示，开启了归档模式但没有开启附加日志； [oracle@sdedu ~]$ sqlplus as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 20:04:29 2019 Version 19.2.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.2.0.0.0 SQL> select log_mode,force_logging from v$database; LOG_MODE FORCE_LOGGING ---------------------- --------------------------------------- ARCHIVELOG NO (2). 查看主库归档目的地，从下面结果中可以看到，欧博归档目的地使用了快速恢复区； SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 3 Current log sequence 3 (3). 开启主库附加日志，并验证开启的结果； SQL> alter database force logging; Database altered. SQL> select log_mode,force_logging from v$database; LOG_MODE FORCE_LOGGING ---------------------- --------------------------------------- ARCHIVELOG YES 从Oracle Database 18c开始，引入了以下两个新的nologging子句，它们可以执行非日志记录操作，欧博娱乐同时可以使Active Data Guard备用数据库接收到所有数据，从而防止FORCE 方式生成大量重做日志导致性能下降。 STANDBY NOLOGGING FOR DATA AVAILABILITY模式使批量加载操作通过其自身与备用数据库的连接将加载的数据发送到每个备用数据库。提交会延迟，直到所有Active Data Guard备用数据库通过 recover 方式将数据应用完成。 SQL> alter database set standby nologging for data availability; Database altered. SQL> select log_mode,force_logging from v$database; LOG_MODE FORCE_LOGGING --------------------------- --------------------------------------------------------------------- NOARCHIVELOG STANDBY NOLOGGING FOR DATA AVAILABILITY STANDBY NOLOGGING FOR LOAD PERFORMANCE模式与先前的模式类似，不同之处在于，欧博allbet如果网络无法跟上数据加载到主数据库的速度，则加载过程可以停止将数据发送到备用数据库。在此模式下，备用数据库可能缺少数据，但每个Active Data Guard备用数据库都会在recover过程中自动从主数据库中提取数据。 SQL> alter database set standby nologging for load performance; Database altered. SQL> select log_mode,force_logging from v$database; LOG_MODE FORCE_LOGGING -------------------------- -------------------------------------------------------------------------- NOARCHIVELOG STANDBY NOLOGGING FOR LOAD PERFORMANCE (4). 在主库中添加附加日志； SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/SS19P/stredo04.log') size 100m; Database altered. SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/SS19P/stredo05.log') size 100m; Database altered. SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/SS19P/stredo06.log') size 100m; Database altered. SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/SS19P/stredo07.log') size 100m; Database altered. (5). 修改主库参数； SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(SS19P,SS19S)'; System altered. SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SS19P'; System altered. SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=SS19S ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SS19S'; System altered. SQL> alter system set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' scope=spfile; System altered. SQL> alter system set FAL_SERVER=SS19S; System altered. SQL> alter system set DB_FILE_NAME_CONVERT='SS19S','SS19P' scope=spfile; System altered. SQL> alter system set LOG_FILE_NAME_CONVERT='SS19S','SS19P' scope=spfile; System altered. SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO; System altered. SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.2.0.0.0 (6). 编辑主库 listener.ora 网络配置文件，添加连接主库点静态注册； [oracle@sdedu ~]$ cd u01/app/oracle/product/19.2.0/dbhome_1/network/admin/ [oracle@sdedu admin]$ vi listener.ora # listener.ora Network Configuration File: u01/app/oracle/product/19.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = SS19P.sandata.com.cn) (SID_NAME = SS19P) (ORACLE_HOME = u01/app/oracle/product/19.2.0/dbhome_1) ) ) (7). 编辑主库 tnsnames.ora 网络配置文件，添加连接备库的本地服务名； [oracle@sdedu admin]$ vi tnsnames.ora # tnsnames.ora Network Configuration File: u01/app/oracle/product/19.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_SS19P = (ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521)) SS19P = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SS19P.sandata.com.cn) ) ) SS19S = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SS19S.sandata.com.cn) ) ) (8). 将主库中的网络文件和口令文件传输到备库； [oracle@sdedu admin]$ scp listener.ora tnsnames.ora sdrep:`pwd` oracle@sdrep's password: listener.ora 100% 558 451.3KB/s 00:00 tnsnames.ora 100% 652 580.3KB/s 00:00 [oracle@sdedu admin]$ cd ../../dbs [oracle@sdedu dbs]$ scp orapwSS19P sdrep:`pwd`/orapwSS19S oracle@sdrep's password: orapwSS19P (9). 备库中创建对应路径： [oracle@sdrep ~]$ cd u01/app/oracle/ [oracle@sdrep oracle]$ mkdir -p admin/SS19S/adump [oracle@sdrep oracle]$ mkdir -p oradata/SS19S [oracle@sdrep oracle]$ mkdir -p fast_recovery_area/SS19S (10). 创建备库的参数文件： [oracle@sdrep oracle]$ cd u01/app/oracle/product/19.2.0/dbhome_1/dbs/ [oracle@sdrep dbs]$ vi initSS19S.ora DB_NAME=SS19S (11). 修改备库的 listener.ora 配置文件： [oracle@sdrep dbs]$ vi ../network/admin/listener.ora # listener.ora Network Configuration File: u01/app/oracle/product/19.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = SS19S.example.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = SS19S.sandata.com.cn) (ORACLE_HOME = u01/app/oracle/product/12.1.0/dbhome_1) (SID_NAME = SS19S) ) ) (12). 修改备库的 tnsnames.ora 配置文件： [oracle@sdedu admin]$ vi tnsnames.ora # tnsnames.ora Network Configuration File: u01/app/oracle/product/19.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_SS19P = (ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn)(PORT = 1521)) SS19P = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SS19P.sandata.com.cn) ) ) SS19S = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SS19S.sandata.com.cn) ) ) (13). 启动辅助实例； [oracle@sdrep dbs]$ export ORACLE_SID=SS19S [oracle@sdrep dbs]$ sqlplus as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 16:41:03 2019 Version 19.2.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 1543500824 bytes Fixed Size 9135128 bytes Variable Size 1006632960 bytes Database Buffers 520093696 bytes Redo Buffers 7639040 bytes Database mounted. SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.2.0.0.0 (14). 使用复制数据库技术搭建备库，由于备库的文件路径包含SS19S，因此需要写明audit_file_dest,control_files参数（使用单引号也可以）； [oracle@sdrep dbs]$ rman target sys/oracle@SS19P auxiliary sys/oracle@SS19S Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 6 20:51:58 2019 Version 19.2.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: SS19P (DBID=592912636) connected to auxiliary database: SS19P (not mounted) RMAN> DUPLICATE TARGET DATABASE 2> FOR STANDBY 3> FROM ACTIVE DATABASE 4> DORECOVER 5> SPFILE 6> SET DB_UNIQUE_NAME="SS19S" COMMENT "Is a dbstyle duplicate" 7> SET AUDIT_FILE_DEST="/u01/app/oracle/admin/SS19S/adump" 8> SET CONTROL_FILES='/u01/app/oracle/oradata/SS19S/control01.ctl','/u01/app/oracle/fast_recovery_area/SS19S/control02.ctl' 9> SET LOG_ARCHIVE_CONFIG="DG_CONFIG=(SS19S,SS19P)" 10> SET LOG_ARCHIVE_DEST_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SS19S" 11> SET LOG_ARCHIVE_DEST_2="SERVICE=SS19P ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SS19P" 12> SET DB_FILE_NAME_CONVERT="SS19P","SS19S" 13> SET LOG_FILE_NAME_CONVERT="SS19P","SS19S" 14> SET FAL_SERVER="SS19P" COMMENT "Is primary" 15> SET STANDBY_FILE_MANAGEMENT="AUTO" 16> NOFILENAMECHECK; Starting Duplicate Db at 06-MAR-19 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=21 device type=DISK current log archived 省略部分……… released channel: ORA_DISK_1 released channel: ORA_AUX_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=53 device type=DISK deleted archived log archived log file name=/u01/app/oracle/fast_recovery_area/SS19S/archivelog/2019_03_06/o1_mf_1_10_g7zjp7gs_.arc RECID=1 STAMP=1002228807 deleted archived log archived log file name=/u01/app/oracle/fast_recovery_area/SS19S/archivelog/2019_03_06/o1_mf_1_11_g7zjp8po_.arc RECID=2 STAMP=1002228808 Deleted 2 objects Finished Duplicate Db at 06-MAR-19 RMAN> quit Recovery Manager complete. (15). 进入备库验证角色信息； [oracle@sdrep dbs]$ sqlplus as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 20:54:39 2019 Version 19.2.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.2.0.0.0 SQL> select database_role from v$database; DATABASE_ROLE ------------------------------ PHYSICAL STANDBY SQL> select instance_name from v$instance; INSTANCE_NAME ---------------------------- SS19S (16). 在备库启动recover 过程，应用主库传过来的日志（默认已经是real-time apply模式，因此省略using current logfile）； SQL> recover managed standby database disconnect from session; Media recovery complete. (17). 查询v$dataguard_process 视图，验证来自主库传输过来的日志过程和备库应用日志的情况（v$dataguard_process视图在12.2版本出现，取代了v$managed_standby)； SQL> select role,thread#,sequence#,action from v$dataguard_process; ROLE THREAD# SEQUENCE# ACTION ------------------------------- ----------------- -------------------- ------------------------ recovery apply slave 0 0 IDLE archive local 0 0 IDLE redo transport timer 0 0 IDLE gap manager 0 0 IDLE recovery logmerger 1 12 WAIT_FOR_LOG recovery apply slave 0 0 IDLE managed recovery 0 0 IDLE archive redo 0 0 IDLE archive redo 0 0 IDLE archive redo 0 0 IDLE redo transport monitor 0 0 IDLE log writer 0 0 IDLE 12 rows selected. (18). 查询 v$archived_log 视图，验证来自主库传输过来的日志变化情况，下面输出可以看到主库传输过来的日志在增加； SQL> select sequence#,applied from v$archived_log; SEQUENCE# APPLIED ------------------- --------- 10 YES 11 YES SQL> select sequence#,applied from v$archived_log; SEQUENCE# APPLIED ------------------- --------- 10 YES 11 YES 12 YES SQL> select sequence#,applied from v$archived_log; SEQUENCE# APPLIED ------------------- --------- 10 YES 11 YES 12 YES 13 YES
后续还有19c的系列文章 MySQL 5.7题库解析(1z0-888)-No: 3 MySQL 5.7题库解析(1z0-888)-No: 5 MySQL 5.7题库解析(1z0-888)-No: 12 MySQL 5.7题库解析(1z0-888)-No: 76
|
