搜索到
11
篇与
的结果
-
Oracle 19c 单实例DG搭建 Oracle 19c 单实例DG搭建1.数据库配置1.1.关闭数据库sqlplus / as sysdba shutdown immediate1.2.启动到mount模式startup mount;1.3.修改archivelog#oracle用户下创建新的闪回路径（主库和备库同时创建） mkdir -p /u01/app/oracle/arch_risdb #sqlplus下修改archive log 日志路径 alter system set log_archive_dest_1='location=/u01/app/oracle/arch_risdb'; alter database archivelog;1.4.开启闪回模式#创建闪回目录 mkdir -p /u01/app/oracle/flashback_area#修改闪回路径 show parameter db_recovery_file_dest; alter system set db_recovery_file_dest='/u01/app/oracle/flashback_area' scope=spfile; alter system set db_recovery_file_dest_size=16G; alter database flashback on;1.5.为备库创建日志文件set wrap off; select * from v$logfile order by group#; #查询日志组 GROUP# STATUS TYPE MEMBER ---------- -------------- -------------------- --------------- 1 ONLINE /u01/app/oracle/oradata/RISDB/redo01.log 2 ONLINE /u01/app/oracle/oradata/RISDB/redo02.log 3 ONLINE /u01/app/oracle/oradata/RISDB/redo03.log # 当前在线日志共有3组，为备库创建的日志建议比在线日志多一组，即4组 alter database add standby logfile group 4 ('/u01/app/oracle/oradata/SPECTRA/sredo04.log') size 200m; alter database add standby logfile group 5 ('/u01/app/oracle/oradata/SPECTRA/sredo05.log') size 200m; alter database add standby logfile group 6 ('/u01/app/oracle/oradata/SPECTRA/sredo06.log') size 200m; alter database add standby logfile group 7 ('/u01/app/oracle/oradata/SPECTRA/sredo07.log') size 200m; select * from v$logfile order by group#; #查询日志组 GROUP# STATUS TYPE MEMBER ---------- -------------- -------------------- ---------------------------------------------------- 1 ONLINE /u01/app/oracle/oradata/RISDB/redo01.log 2 ONLINE /u01/app/oracle/oradata/RISDB/redo02.log 3 ONLINE /u01/app/oracle/oradata/RISDB/redo03.log 4 STANDBY /u01/app/oracle/oradata/RISDB/redo04.log 5 STANDBY /u01/app/oracle/oradata/RISDB/redo05.log 6 STANDBY /u01/app/oracle/oradata/RISDB/redo06.log 7 STANDBY /u01/app/oracle/oradata/RISDB/redo07.log1.6.主库修改系统参数#设置db_unique_name alter system set db_unique_name='master' scope=spfile; #设置日志归档配置，用于限制日志传输的目标，只能在这两个指定的数据库进行，该配置中的顺序可以调换 alter system set log_archive_config='DG_CONFIG=(master,slave)' scope=spfile; #设置日志归档路径1 alter system set log_archive_dest_1='location=/u01/app/oracle/arch_risdb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=master' scope=spfile; #设置日志归档路径2 alter system set log_archive_dest_2='SERVICE=slave LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=slave' scope=spfile; #启用归档路径1 alter system set log_archive_dest_state_1='enable' scope=spfile; #启用归档路径2 alter system set log_archive_dest_state_2='enable' scope=spfile; #设置当前库获取归档日志的服务器listerner名称（与8成对出现，用于switchover角色切换） alter system set fal_server='slave' scope=spfile; #设置当前库获取归档日志的客户端名称listerner名称（与7成对出现，用于switchover角色切换） alter system set fal_client='master' scope=spfile; #设置归档日志强制切换时间（s）1800表示30分钟 alter system set archive_lag_target=1800 scope=spfile; #设置归档日志文件名命名方式 alter system set log_archive_format='%t_%s_%r.arc'scope=spfile; #设置备库文件管理的方式为auto,结合后续的convert参数，当主库增删文件时会自动在备库也生成相应文件。 alter system set standby_file_management=auto scope=spfile; #设置数据文件名称的映射关系（注意顺序不能变） alter system set db_file_name_convert='slave','master' scope=spfile; #设置日志文件名称的映射关系（注意顺序不能变） alter system set log_file_name_convert='slave','master' scope=spfile;1.7.主库重启alter database open; shutdown immediate; startup;1.8.主库创建pfile文件create pfile from spfile; #Pfile文件位置：/u01/app/oracle/product/19c/db_1/dbs/initrisdb.ora1.9.主库配置网络监听#修改/u01/app/oracle/product/19c/db_1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pacspri)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = pacspri) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (SID_NAME = risdb) ) ) ## 修改 /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = risdb) ) ) standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = risdb) ) ) RISDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = risdb) ) )1.10.主库测试监听#重启LISTENER服务 lsnrctl stop lsnrctl start tnsping primary tnsping standby tnsping risdb #在主库上tnsping测试都能ping通1.11.主库复制配置及参数文件到备库#关闭主数据库（备库） SQL> shutdown immediate #将主库pfile,orapwrisdb,listener.ora和tnsnames.ora文件拷贝到备库相应目录下。1.12.修改监听文件# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pacsstd)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = risdb) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (SID_NAME = risdb) ) )1.13.备库启动监听并测试tnsping#重启LISTENER服务 lsnrctl stop lsnrctl start tnsping primary tnsping standby tnsping risdb #在备库上tnsping测试都能ping通1.14.修改参数文件risdb.__data_transfer_cache_size=0 risdb.__db_cache_size=241055039488 risdb.__inmemory_ext_roarea=0 risdb.__inmemory_ext_rwarea=0 risdb.__java_pool_size=0 risdb.__large_pool_size=2684354560 risdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment risdb.__pga_aggregate_target=17179869184 risdb.__sga_target=274877906944 risdb.__shared_io_pool_size=536870912 risdb.__shared_pool_size=30064771072 risdb.__streams_pool_size=0 risdb.__unified_pga_pool_size=0 *.archive_lag_target=1800 *.audit_file_dest='/u01/app/oracle/admin/risdb/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/u01/app/oracle/oradata/RISDB/control01.ctl','/u01/app/oracle/oradata/RISDB/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='primary','standby' #'standby','primary'交换位置 *.db_name='risdb' *.db_recovery_file_dest='/u01/app/oracle/flashback_area' *.db_recovery_file_dest_size=17179869184 *.db_unique_name='standby' #primary修改为standby *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=risdbXDB)' *.fal_client='standby' #primary修改为standby *.fal_server='primary' #standby修改为primary *.local_listener='LISTENER_RISDB' *.log_archive_config='DG_CONFIG=(primary,standby)' *.log_archive_dest_1='location=/u01/app/oracle/arch_risdb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby' #primary修改为standby *.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=primary' #standby两处，修改为primary *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.arc' *.log_file_name_convert='primary','standby' #standby primary修改位置 *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=16g *.processes=3000 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=256g *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' #改完且检查路径是否都存在1.15.修改完成后通过pfile启动并创建spfilesqlplus / as sysdba SQL> startup pfile="/u01/app/oracle/product/19c/db_1/dbs/initrisdb.ora"; ORACLE instance started. Total System Global Area 2.7488E+11 bytes Fixed Size 30150760 bytes Variable Size 3.2749E+10 bytes Database Buffers 2.4159E+11 bytes Redo Buffers 506716160 bytes Database mounted. Database opened. SQL> create spfile from pfile; File created. SQL>2.恢复数据库2.1.连接备库进行数据恢复rman target sys/oracle19c@primary auxiliary sys/oracle19c@standby duplicate target database for standby from active database nofilenamecheck; duplicate target database for standby from active database dorecover nofilenamecheck;2.2.备库启动数据同步#完成后关闭数据库，启动到mount模式 startup mount; alter database recover managed standby database disconnect from session; #如果要停止同步 SQL> alter database recover managed standby database cancel; #注：如果想让备库比主库延迟几分钟可以启动为如下方式 SQL> alter database recover managed standby database delay 5 disconnect from session;2.3.恢复后数据测试#验证数据是否同步（在主库和备库上都执行，对比日志文件如果备库和主库一样表示同步完成） select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc; select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a where a.COMPLETION_TIME>sysdate -1/24 order by sequence#; #主库上切换日志文件再观察有没有正确同步到备库 alter system switch logfile;