Data Guard搭建
<p>[TOC]</p>
<h2>Data Guard 单实例到单实例安装</h2>
<hr />
<h3>实验环境</h3>
<h5>主库端</h5>
<ul>
<li>基于linux系统安装19.3.0.0版本Oracle数据库</li>
<li>有一个单实例数据库db_name=db_unique_name=dgorcl</li>
</ul>
<h4>备库端</h4>
<ul>
<li>基于linux系统安装19.3.0.0版本Oracle数据库</li>
<li>未起数据库实例</li>
<li>db_unique_name=dgsorcl</li>
</ul>
<h4>注意事项</h4>
<ul>
<li>大小写不敏感，除密码外</li>
<li>路径结尾处是否有/是敏感的，同时末尾不要留有空格</li>
<li>注意每个参数路径存在且权限为oracle:oinstall</li>
</ul>
<h4>主库端操作步骤</h4>
<ul>
<li>
<p>检查主库是否开归档，启动归档方式</p>
<pre><code>## 在主库上启用归档
SELECT log_mode FROM v$database;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG；
ALTER DATABASE OPEN;</code></pre>
</li>
<li>检查主库的DB_NAME,DB_UNIQUE_NAME.</li>
<li>主库和备库前者一致，后者不能一致；</li>
<li>
<p>为了保证db_unique_name写入pfile内，alter system一下；</p>
<pre><code>SQL> show parameter db_name
SQL> show parameter db_unique_name
alter system set db_unique_name=dgorcl scope=spfile;</code></pre>
</li>
<li>
<p>log_archive_config 配置有效的db_unique_name 参数列表，表明实例间的互通</p>
<pre><code>ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dgorcl,dgsorcl)';</code></pre>
</li>
<li>
<p>查看重要文件位置</p>
<pre><code>--数据文件
select name from v$datafile;
/u01/app/oracle/oradata/DGORCL/datafile/o1_mf_system_jpommblf_.dbf
/u01/app/oracle/oradata/DGORCL/datafile/o1_mf_sysaux_jpompbq8_.dbf
/u01/app/oracle/oradata/DGORCL/datafile/o1_mf_undotbs1_jpompsdk_.dbf
/u01/app/oracle/oradata/DGORCL/datafile/o1_mf_users_jpomptgr_.dbf
--查看重做日志文件
select member from v$logfile;
/u01/app/oracle/oradata/DGORCL/onlinelog/o1_mf_1_jpomrd0o_.log
/u01/app/oracle/oradata/DGORCL/onlinelog/o1_mf_2_jpomrd15_.log
/u01/app/oracle/oradata/DGORCL/onlinelog/o1_mf_3_jpomrg5f_.log
--控制文件
select name from v$controlfile;
/u01/app/oracle/oradata/DGORCL/controlfile/o1_mf_jpomr9ok_.ctl
/u01/app/oracle/fast_recovery_area/DGORCL/controlfile/o1_mf_jpomr9pt_.ctl</code></pre>
</li>
<li>
<p>设置归档和传输模式：2号是给备库使用的，闪回区域有关。P72页尾 </p>
<pre><code>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=dgsorcl NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgsorcl';
-- net_timeout=30 失败等待的时间；reopen=300 主库重连从库时间
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=(备库的tnsname) (非必要参数，要求进程接收返回) (同步或者异步) VALID_FOR=(定义什么情况下使用该定义) DB_UNIQUE_NAME=(连接的依据，与log_archive_config参数要对应)';
-- 现在这个参数都是默认启动了，不需要了
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
## 备库上也要设置，才可以主备切换
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=dgorcl NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgorcl';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;</code></pre>
</li>
<li>
<p>使用ASM需要在备库中定义它：</p>
<pre><code>db_create_file_dest=+DATA </code></pre>
</li>
<li>
<p>设置归档日志和最大进程</p>
<pre><code>ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
--log_archive_max_processes 设置最大进程，处理重做日志归档，重做间隔，备库的传输的进程
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=10;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;</code></pre>
</li>
<li>
<p>当脑裂时的处理，发生故障转移时主备都需要的（备库捕捉数据）</p>
<pre><code>--数据跟不上时向其他库取数据，可以指向其他备用或者主库，或者同时指定多个。（tnsname）
ALTER SYSTEM SET FAL_SERVER=dgsorcl;
--当前数据库的tnsname
alter system set fal_client=dgorcl;
-- 数据文件复制路径：第一个参数填其他库的路径，第二个参数填本库的路径
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DGSORCL/datafile/','/u01/app/oracle/oradata/DGORCL/datafile/' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DGSORCL/onlinelog/','/u01/app/oracle/oradata/DGORCL/onlinelog/' SCOPE=SPFILE;
--主库上的数据文件增、减自动同步到备库，有时要改为手动
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;</code></pre>
</li>
<li>
<p>主库上创建备库的redo logs，数量加1</p>
<pre><code>ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DGORCL/onlinelog/standby_redo04.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DGORCL/onlinelog/standby_redo05.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DGORCL/onlinelog/standby_redo06.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DGORCL/onlinelog/standby_redo07.log') SIZE 200M;</code></pre>
</li>
<li>可能是备库的standby redo有问题，添加后就可以日志同步了。</li>
</ul>
<pre><code>MRP0 (PID:22260): Media Recovery Waiting for T-1.S-130 (in transit)
2021-10-19T15:12:19.665600+08:00
ARC5 (PID:4143): Archived Log entry 121 added for T-1.S-129 ID 0x6b5ce209 LAD:1
2021-10-19T15:12:19.668786+08:00
Recovery of Online Redo Log: Thread 1 Group 11 Seq 130 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/DGORCL/onlinelog/standby_redo011.log
2021-10-19T15:17:51.942252+08:00
Control autobackup written to DISK device
handle '/u01/app/oracle/fast_recovery_area/DGSORCL/autobackup/2021_10_19/o1_mf_s_1086275853_jpww8zst_.bkp'
2021-10-19T15:26:29.058502+08:00
.... (PID:4097): Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST [krsd.c:18157]
2021-10-19T15:26:29.064560+08:00
ALTER SYSTEM SET log_archive_dest_2='SERVICE=dgorcl NET_TIMEOUT=20 REOPEN=15 ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=dgorcl' SCOPE=BOTH;
2021-10-19T15:27:16.419629+08:00
MRP0 (PID:22260): Media Recovery Waiting for T-1.S-131
2021-10-19T15:27:16.428505+08:00
rfs (PID:16453): Selected LNO:12 for T-1.S-131 dbid 1801259017 branch 1086124171
2021-10-19T15:27:16.429360+08:00
ARC0 (PID:4129): Archived Log entry 122 added for T-1.S-130 ID 0x6b5ce209 LAD:1
2021-10-19T15:27:17.492348+08:00
Recovery of Online Redo Log: Thread 1 Group 12 Seq 131 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/DGORCL/onlinelog/standby_redo012.log</code></pre>
<ul>
<li>
<p>重启使生效</p>
<pre><code>shutdown immediate
startup</code></pre>
</li>
<li>
<p>主备都需要配置TNS</p>
<pre><code>[oracle@master admin]$ vim /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
dgorcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.31.202)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgorcl)
)
)
dgsorcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.31.203)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgsorcl)
)
)</code></pre>
</li>
<li>
<p>测试以上配置是否生效</p>
<pre><code>tnsping dgsorcl
tnsping dgorcl</code></pre>
</li>
<li>创建备库的pfile文件、控制文件，有些参数要修改。后面直接把这里的文件复制到备库。
<pre><code>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/tmp/dgsorcl.ctl';
CREATE PFILE='/u01/tmp/initdgsorcl.ora' FROM SPFILE;
*.db_unique_name='dgsorcl'
*.fal_server='dgorcl'
*.log_archive_dest_2='SERVICE=dgorcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgorcl'
*.local_listener='dgorcl' //Dynamic registration parameters 动态注册参数</code></pre></li>
</ul>
<h4>备库端操作</h4>
<ul>
<li>
<p>创建必要的目录</p>
<pre><code>$ mkdir -p /u01/app/oracle/oradata/DGORCL/datafile //datafile
$ mkdir -p /u01/app/oracle/oradata/DGORCL/onlinelog //onlineredo
$ mkdir -p /u01/app/oracle/admin/dgorcl/adump //adump目录
$ mkdir -p /u01/app/oracle/fast_recovery_area/dgorcl
$ mkdir -p /u01/app/oracle/oradata/DGORCL/CONTROLFILE
$ mkdir -p /u01/app/oracle/fast_recovery_area/DGORCL/controlfile</code></pre>
</li>
<li>
<p>恢复同步控制文件、初始化参数文件、密码文件</p>
<pre><code>$ # Standby controlfile to all locations.
$ scp oracle@172.18.31.202:/u01/tmp/dgsorcl.ctl /u01/app/oracle/oradata/DGORCL/CONTROLFILE/control01.ctl
$ cp /u01/app/oracle/oradata/DGORCL/CONTROLFILE/control01.ctl /u01/app/oracle/fast_recovery_area/dgorcl/control02.ctl
$ # Parameter file.
$ scp oracle@172.18.31.202:/u01/tmp/initdgsorcl.ora $ORACLE_HOME/dbs/initdgsorcl.ora
$ # Remote login password file，密码文件
$ scp oracle@172.18.31.202:$ORACLE_HOME/dbs/orapwdgorcl $ORACLE_HOME/dbs/orapwdgsorcl</code></pre>
</li>
<li>配置监听文件，静态监听，动态是否可以呢？备库需要静态监听，主库不需要，有监听RMAN才可以复制数据</li>
</ul>
<pre><code>[oracle@standby controlfile]$ vim /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dgsorcl)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = dgsorcl)
)
)</code></pre>
<ul>
<li>
<p>起用监听</p>
<pre><code>$ lsnrctl start
$ lsnrctl reload
$ lsnrctl status</code></pre>
</li>
<li>设置环境变量、启动恢复
<pre><code>export ORACLE_SID=dgsorcl
$ sqlplus / as sysdba
##启动失败就检查这个文件
SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/product/19.3.0/dbhome_1/dbs/initdgsorcl.ora';
SQL> exit
## 如果登陆失败就检查密码文件
$ rman TARGET sys/oracle@dgorcl AUXILIARY sys/oracle@dgsorcl
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='DGSORCL' COMMENT 'Is standby'
SET LOG_ARCHIVE_DEST_2='SERVICE=dgorcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgorcl'
SET FAL_SERVER='DGORCL' COMMENT 'Is primary'
NOFILENAMECHECK;</code></pre></li>
</ul>
<h4>开始应用standby redo log</h4>
<ul>
<li>
<p>启动应用日志，最后指定返回控制权</p>
<pre><code>SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;</code></pre>
</li>
<li>
<p>安装的时候需要重启才可以同步</p>
</li>
<li>
<p>如果要建立应用日志延迟的话</p>
<pre><code>SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;--取消日志应用
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;
-- 取消延迟
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;</code></pre>
</li>
<li>
<p>如果要实时应用日志的话</p>
<pre><code>SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;</code></pre>
</li>
<li>启动实时应用日志与取消日志应用
<pre><code>SQL>alter database recover managed standby database using current logfile disconnect from session;
SQL>alter database recover managed standby database cancel;</code></pre></li>
</ul>
<h4>查询信息</h4>
<ul>
<li>查看归档日志，只能在主库执行切换
<pre><code>SQL> SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;
SQL> archive log list
SQL> ALTER SYSTEM SWITCH LOGFILE;</code></pre></li>
</ul>
<h5>保护模式</h5>
<ul>
<li>
<p>对于主库会有以下三种不同的保护模式：<br />
1.最大可用<br />
事务在主库只有当online redo log已经传到备库才会提交，如果备库不可用，转为最大性能模式<br />
2.最大性能<br />
主库生成的redo信息会尽快写到online redo 日志里，到备库的传输是异步传输<br />
3.最大保护<br />
log已经传到备库才会提交，才能进行下一事务操作；如果备库不可用，主库会关机 </p>
</li>
<li>
<p>查看保护模式</p>
<pre><code>SELECT protection_mode FROM v$database;
PROTECTION_MODE
MAXIMUM PERFORMANCE</code></pre>
</li>
<li>
<p>转换保护模式</p>
<pre><code>-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;</code></pre>
</li>
<li>问题:切换最高可用性后要切换这个状态才可以同步一下下日志
<pre><code>SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;</code></pre></li>
</ul>
<h5>主备切换</h5>
<ul>
<li>
<p>数据库切换</p>
<pre><code>--在主库上
-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
-- Shutdown primary database
SHUTDOWN IMMEDIATE;
-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
--在备库上
-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
-- Shutdown standby database
SHUTDOWN IMMEDIATE;
-- Open old standby database as primary
STARTUP;</code></pre>
</li>
<li>当主库不可用的时候，故障转移
<pre><code>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;</code></pre></li>
</ul>