-
Oracle-ADG部署
Oracle-ADG部署
0.准备
01、环境准备
类型 IP 实例 操作系统 数据库软件 主库 192.168.5.101 test OracleLinux7 Oracle19.3.0 备库 192.168.5.102 testdg OracleLinux7 Oracle19.3.0 02、主库备库安装数据库软件
03、主库创建数据库
$ dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -responseFile NO_VALUE \ -gdbname ${ORACLE_SID} -sid ${ORACLE_SID} \ -characterSet ZHS16GBK \ -sysPassword bsoft \ -systemPassword bsoft \ -databaseType OLTP \ -totalMemory 6144 \ -storageType FS \ -datafileDestination '/data/oradata' \ -recoveryAreaDestination 'NONE' \ -redoLogFileSize 50 \ -emConfiguration NONE \ -ignorePreReqs- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
1.主库配置
创建归档日志路径
mkdir -p /data/archivelog- 1
1.1查看数据库是否是归档模式
SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled #未开启归档 Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 117 Current log sequence 119 SQL> alter system set log_archive_dest_1='location=/data/archivelog' scope=spfile; #设置归档路径 SQL> shutdown immediate #关闭数据库 SQL> startup mount; #启动到mount状态 SQL> alter database archivelog; #开启归档 SQL> alter database open; #open数据库 SQL> archive log list;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
1.2开启强制归档
SQL>select force_logging from v$database; FORCE_LOGGING --------------- NO SQL>alter database force logging; #开启force logging SQL>select force_logging from v$database; #验证 FORCE_LOGGING --------------- YES ```bash ### 1.3添加standby日志文件 在备库，当RFS进程接受到日志后，就将其写入Standby日志文件里，备库的Standby日志文件可以看做是主库在线日志文件的一个镜像，当主库做日志切换时，备库的Standby日志也做相应的切换，切换后的Standby日志由备库的ARCH进程归档。 ```bash SQL>select a.thread#,a.group#,a.bytes/1024/1024,b.member from v$log a,v$logfile b where a.group#=b.group#; THREAD# GROUP# A.BYTES/1024/1024 MEMBER 1 3 200 /data/oradata/TEST/redo03.log 1 2 200 /data/oradata/TEST/redo02.log 1 1 200 /data/oradata/TEST/redo01.log SQL>select group#,bytes/1024/1024 from v$standby_log; no rows selected- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
创建standby日志组，数量为redo日志的n+1,大小和redo一样
alter database add standby logfile thread 1 group 4 ('/data/oradata/TEST/redo04_stb01.log') size 50m; alter database add standby logfile thread 1 group 5 ('/data/oradata/TEST/redo05_stb01.log') size 50m; alter database add standby logfile thread 1 group 6 ('/data/oradata/TEST/redo06_stb01.log') size 50m; alter database add standby logfile thread 1 group 7 ('/data/oradata/TEST/redo07_stb01.log') size 50m; SQL> select group#,type,member from v$logfile order by 2; GROUP# TYPE MEMBER 1 ONLINE /data/oradata/TEST/redo01.log 2 ONLINE /data/oradata/TEST/redo02.log 3 ONLINE /data/oradata/TEST/redo03.log 6 STANDBY /data/oradata/TEST/redo06_stb01.log 5 STANDBY /data/oradata/TEST/redo05_stb01.log 7 STANDBY /data/oradata/TEST/redo07_stb01.log 4 STANDBY /data/oradata/TEST/redo04_stb01.log- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
1.4修改参数文件
alter system set log_archive_config='DG_CONFIG=(test,testdg)'; alter system set log_archive_dest_1='LOCATION=/data/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test'; alter system set log_archive_dest_2='SERVICE=testdg VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdg'; alter system set log_archive_dest_state_1='enable'; alter system set log_archive_dest_state_2='enable'; alter system set db_file_name_convert='/data/oradata/TESTDG/','/data/oradata/TEST/' scope=spfile; alter system set log_file_name_convert='/data/oradata/TESTDG/','/data/oradata/TEST/' scope=spfile; alter system set fal_server='testdg'; alter system set fal_client='test'; alter system set standby_file_management='AUTO'; alter system set DB_UNIQUE_NAME='test' scope=spfile;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
上面修改的参数有的需要重启数据库才能生效
shutdown immediate startup- 1
- 2
- 3
1.5配置TNS文件
cd $ORACLE_HOME/network/admin/- 1
netmgr,配置tns,监听
vi tnsnames.ora TEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) ) TESTDG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.102)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = testdg) ) )- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
重启监听
$ lsnrctl reload- 1
1.6拷贝参数文件
利用spfile创建pfile
create pfile='/tmp/1.ora' from spfile; scp /tmp/1.ora 192.168.5.102:/tmp/1.ora- 1
- 2
- 3
1.7拷贝密码文件
cd /u01/app/oracle/product/19.3.0/dbhome_1/dbs/ scp orapwtest oracle@192.168.5.102:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/- 1
- 2
- 3
2. 备库配置
2.1修改密码文件
cd $ORACLE_BASE/product/19.3.0/dbhome_1/dbs mv orapwtest orapwtestdg- 1
- 2
- 3
2.2修改参数文件
cd $ORACLE_BASE/product/19.3.0/dbhome_1/dbs cp /tmp/1.ora inittestdg.ora vi inittestdg.ora testdg.__data_transfer_cache_size=0 testdg.__db_cache_size=8321499136 testdg.__inmemory_ext_roarea=0 testdg.__inmemory_ext_rwarea=0 testdg.__java_pool_size=0 testdg.__large_pool_size=100663296 testdg.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment testdg.__pga_aggregate_target=3355443200 testdg.__sga_target=10066329600 testdg.__shared_io_pool_size=134217728 testdg.__shared_pool_size=1476395008 testdg.__streams_pool_size=0 testdg.__unified_pga_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/testdg/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/data/oradata/TESTDG/control01.ctl','/data/oradata/TESTDG/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='/data/oradata/TEST/','/data/oradata/TESTDG/' *.db_name='test' *.db_unique_name='testdg' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=testdgXDB)' *.fal_client='testdg' *.fal_server='test' *.log_archive_config='DG_CONFIG=(testdg,test)' *.log_archive_dest_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdg' *.log_archive_dest_2='SERVICE=test VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_file_name_convert='/data/oradata/TEST/','/data/oradata/TESTDG/' *.nls_language='SIMPLIFIED CHINESE' *.nls_territory='CHINA' *.open_cursors=300 *.pga_aggregate_target=3200m *.processes=5000 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=9599m *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1'- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
2.3创建参数文件里相对应的目录
mkdir -p /data/oradata/TESTDG/ mkdir -p /u01/app/oracle/admin/testdg/adump/ mkdir -p /data/archivelog- 1
- 2
- 3
- 4
- 5
2.4修改监听配置文件文件,配置为静态监听
cd $ORACLE_HOME/network/admin vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.102)(PORT = 1521)) ) ) SID_LIST_LISTENER= (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = testdg) (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1) (SID_NAME = testdg) ) ) ADR_BASE_LISTENER = /u01/app/oracle- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
2.5修改TNS配置文件
vi tnsnames.ora TEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) ) TESTDG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.102)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = testdg) ) )- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
重启监听
2.6启动数据库到nomount状态
SQL> startup nomount;- 1
3.恢复备库
3.1验证监听
主库：
sqlplus sys/Aa123456@test as sysdba sqlplus sys/Aa123456@testdg as sysdba- 1
- 2
- 3
备库：
sqlplus sys/Aa123456@test as sysdba sqlplus sys/Aa123456@testdg as sysdba- 1
- 2
- 3
3.2主库登入rman，复制数据库至备库
rman target sys/Aa123456@test auxiliary sys/Aa123456@testdg DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;- 1
- 2
- 3
执行日志如下：
[oracle@node1 dbs]$ rman target sys/Aa123456@test auxiliary sys/Aa123456@testdg Recovery Manager: Release 19.0.0.0.0 - Production on Sat Aug 13 22:15:02 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: TEST (DBID=2397297790) connected to auxiliary database: TEST (not mounted) RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK; Starting Duplicate Db at 2022-08-13 22:15:29 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=271 device type=DISK contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwtestdg' ; } executing Memory Script Starting backup at 2022-08-13 22:15:30 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=286 device type=DISK Finished backup at 2022-08-13 22:15:31 contents of Memory Script: { restore clone from service 'test' standby controlfile; } executing Memory Script Starting restore at 2022-08-13 22:15:31 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service test channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/data/oradata/TESTDG/control01.ctl output file name=/data/oradata/TESTDG/control02.ctl Finished restore at 2022-08-13 22:15:34 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/data/oradata/TESTDG/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/data/oradata/TESTDG/system01.dbf"; set newname for datafile 3 to "/data/oradata/TESTDG/sysaux01.dbf"; set newname for datafile 4 to "/data/oradata/TESTDG/undotbs01.dbf"; set newname for datafile 7 to "/data/oradata/TESTDG/users01.dbf"; restore from nonsparse from service 'test' clone database ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /data/oradata/TESTDG/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 2022-08-13 22:15:39 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service test channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /data/oradata/TESTDG/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service test channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/oradata/TESTDG/sysaux01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service test channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00004 to /data/oradata/TESTDG/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service test channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00007 to /data/oradata/TESTDG/users01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 2022-08-13 22:15:51 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=1 STAMP=1112652950 file name=/data/oradata/TESTDG/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=1112652950 file name=/data/oradata/TESTDG/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=1112652950 file name=/data/oradata/TESTDG/undotbs01.dbf datafile 7 switched to datafile copy input datafile copy RECID=4 STAMP=1112652950 file name=/data/oradata/TESTDG/users01.dbf Finished Duplicate Db at 2022-08-13 22:15:53- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
4.开启实时同步
备库：
SQL> alter database open; SQL> alter database recover managed standby database using current logfile disconnect from session; ```bash 关闭同步：即停止 mgr 进程 ```bash SQL> alter database recover managed standby database cancel;- 1
- 2
- 3
- 4
- 5
- 6
- 7
5.验证DG
- 通过查看archive_log_dest_2列是否有error报错，如果有报错，则需要先根据报错内容解决问题
SQL> set linesize 10000 SQL> set pagesize 10000 SQL>col dest_name format a30 SQL>col error format a20 SQL>select dest_name,error from v$archive_dest;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 查询主库最大归档序号，一致即归档同步成功。
主，备库上执行：
SQL>select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 22- 1
- 2
- 3
- 4
- 5
- 6
- 7
主库上执行日志切换
SQL>alter system archive log current; SQL>select max(sequence#) from v$archived_log;- 1
- 2
- 3
6.测试数据同步
-- 创建用户u1 create user u1 identified by Aa123456 default tablespace users temporary tablespace temp; grant "DBA" to u1 with admin option; -- 使用u1登录，创建测试表 CREATE TABLE t1(id INT,name VARCHAR(10)); INSERT INTO t1(id,name) VALUES(1,'a'); INSERT INTO t1(id,name) VALUES(2,'b'); COMMIT; SELECT * FROM t1;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
-
相关阅读:
存储芯片大厂集体“越冬” 头部厂商扎堆砍单、业绩下滑
Vue入门
HCIP-MGRE实验
FPGA零基础学习：数字电路中的数字表示
【Java】微服务——RabbitMQ消息队列（SpringAMQP实现五种消息模型）
.Net 在容器中操作宿主机
你好，Cartesi Rollups Alpha 0.7.0
【WALT】WALT入口 update_task_ravg() 代码详解
阿里巴巴面试题- - -多线程&并发篇（三十五）
聊聊超卖
- 原文地址：https://blog.csdn.net/huryer/article/details/126351603
