Data Guard安装
<p>[TOC]</p>
<h1>第四次安装DG</h1>
<hr />
<h2>情况说明</h2>
<ul>
<li>主库使用ASM磁盘组，为学习RAC准备</li>
<li>尽量最少的人工参与配置，自动脚本完成</li>
<li>两台机器装Oracle Linux7.9</li>
<li>主库创建实例：db_name=asmdb，db_unique_name=asmdbdg，ASM存储。（IP 31.204）</li>
<li>备库只安装oracle软件。（IP 31.204）</li>
</ul>
<h2>需要配置的参数（主库）</h2>
<ul>
<li>
<p>TNSNAME配置：主备库都需要互通</p>
<pre><code>[oracle@asmdemo admin]$ vim /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
asmdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.31.204)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = asmdb)
)
)
asmdbdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.31.205)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = asmdbdg)
)
)
##检查
tnsping asmdb
tnsping asmdbdg</code></pre>
</li>
<li>
<p>监听：备库一定需要，asmdbdg有一个是TNSNAME，有一个是实例名</p>
<pre><code>[oracle@ASMDEMODG admin]$ vim /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = asmdbdg)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = asmdbdg)
)
)</code></pre>
</li>
<li>
<p>主库打开归档</p>
<pre><code>SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10</code></pre>
</li>
<li>
<p>主库关键文件位置：需要在备库创建相同的子路径</p>
<pre><code>--数据文件
select name from v$datafile;
+DATA/ASMDB/DATAFILE/system.257.1085957143
+DATA/ASMDB/DATAFILE/sysaux.258.1085957177
+DATA/ASMDB/DATAFILE/undotbs1.259.1085957203
+DATA/ASMDB/DATAFILE/odata.264.1085963389
+DATA/ASMDB/DATAFILE/users.260.1085957203
--查看重做日志文件
select member from v$logfile;
+REDO/ASMDB/ONLINELOG/group_1.259.1085963047
+REDO/ASMDB/ONLINELOG/group_2.260.1085963053
+REDO/ASMDB/ONLINELOG/group_3.261.1085963061
--控制文件
select name from v$controlfile;
+DATA/ASMDB/CONTROLFILE/current.261.1085957267
+ARCH/ASMDB/CONTROLFILE/current.256.1085957267
--临时文件
SELECT NAME FROM v$tempfile;
+DATA/ASMDB/TEMPFILE/temp.265.1085957279</code></pre>
</li>
<li>这应该是SRL文件：需要注意怎么做备库才可以只有一组
<pre><code>ALTER DATABASE ADD STANDBY LOGFILE group 11 ('+REDO') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE group 12 ('+REDO') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE group 13 ('+REDO') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE group 14 ('+REDO') SIZE 200M;</code></pre></li>
</ul>
<h2>需要配置的参数（备库）</h2>
<ul>
<li>
<p>备库上创建对应的目录文件</p>
<pre><code>mkdir -p /u01/app/oracle/admin/ASMDBDG/adump
mkdir -p /u01/d01/asmdb/datafile
mkdir -p /u01/d01/asmdb/tempfile</code></pre>
</li>
<li>
<p>复制文件到备库</p>
<pre><code>scp oracle@172.18.31.204:$ORACLE_HOME/dbs/orapwasmdb $ORACLE_HOME/dbs/orapwasmdbdg</code></pre>
</li>
<li>初始化参数文件
<pre><code>[oracle@ASMDEMODG dbs]$ vim /u01/app/oracle/product/19.3.0/dbhome_1/dbs/initasmdbdg.ora
DB_NAME=ASMDB</code></pre></li>
</ul>
<h2>使用RMAN复制数据库(备库)</h2>
<ul>
<li>
<p>使用RMAN复制数据库，在主库复制或者在备库复制都是可以的<br />
备库是(not mounted)才是正确状态，(not start)是监听有问题，重启才变成这种状态 </p>
<pre><code>[oracle@ASMDEMODG ~]$ rman TARGET sys/oracle@ASMDB AUXILIARY sys/oracle@ASMDBDG
connected to target database: ASMDB (DBID=830156051)
connected to auxiliary database: ASMDB (not mounted)</code></pre>
</li>
<li>RMAN脚本
<pre><code>run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate channel prmy5 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'asmdb','asmdbdg'
set 'db_name'='asmdb'
set 'db_unique_name'='asmdbdg'
set control_files='/u01/ctlfile/control01.ctl'
set db_create_file_dest='/u01/d01'
set db_create_online_log_dest_1='/u01/redo'
set db_create_online_log_dest_2='/u01/app/oracle/fast_recovery_area'
set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
set db_recovery_file_dest_size='10G'
set audit_file_dest='/u01/app/oracle/admin/ASMDBDG/adump'
set db_file_name_convert='+DATA','/u01/d01'
set log_file_name_convert='+DATA','/u01/redo'
set log_archive_max_processes='10'
set fal_client='asmdbdg'
set fal_server='asmdb'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(asmdb,asmdbdg)'
set log_archive_dest_2='service=asmdb LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=asmdb'
nofilenamecheck;
sql channel prmy1 "alter system set log_archive_config=''dg_config=(asmdb,asmdbdg)''";
sql channel prmy1 "alter system set log_archive_dest_2=''service=asmdbdg LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=asmdbdg''";
sql channel prmy1 "alter system set log_archive_max_processes=10";
sql channel prmy1 "alter system set fal_client=asmdb";
sql channel prmy1 "alter system set fal_server=asmdbdg";
sql channel prmy1 "alter system set standby_file_management=AUTO";
sql channel prmy1 "alter system archive log current";
allocate auxiliary channel stby type disk;
sql channel stby "alter database recover managed standby database using current logfile disconnect";
}</code></pre></li>
</ul>
<h2>脚本错误的一些情况</h2>
<h3>执行成功提示</h3>
<ul>
<li>正常执行的输出
<pre><code>released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: prmy5
released channel: stby1
released channel: stby</code></pre></li>
</ul>
<h3>DBNAME错误</h3>
<ul>
<li>在脚本中添加set 'db_name'='asmdb' 设置
<pre><code>ORA-01103: database name 'ASMDB' in control file is not 'ASMDBDG'
set 'db_name'='asmdb'</code></pre></li>
</ul>
<h3>目录创建问题</h3>
<ul>
<li>
<p>如下报错没有创建对应的目录</p>
<pre><code>contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/d01/asmdb/tempfile/temp.265.1085957279";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/d01/asmdb/datafile/system.257.1085957143";
set newname for datafile 3 to
"/u01/d01/asmdb/datafile/sysaux.258.1085957177";
set newname for datafile 4 to
"/u01/d01/asmdb/datafile/undotbs1.259.1085957203";
set newname for datafile 5 to
"/u01/d01/asmdb/datafile/odata.264.1085963389";
set newname for datafile 7 to
"/u01/d01/asmdb/datafile/users.260.1085957203";
backup as copy reuse
datafile 1 auxiliary format
"/u01/d01/asmdb/datafile/system.257.1085957143" datafile
3 auxiliary format
"/u01/d01/asmdb/datafile/sysaux.258.1085957177" datafile
4 auxiliary format
"/u01/d01/asmdb/datafile/undotbs1.259.1085957203" datafile
5 auxiliary format
"/u01/d01/asmdb/datafile/odata.264.1085963389" datafile
7 auxiliary format
"/u01/d01/asmdb/datafile/users.260.1085957203" ;
sql 'alter system archive log current';
}</code></pre>
</li>
<li>添加目录后成功复制
<pre><code>Starting backup at 19-OCT-21
channel prmy1: starting datafile copy
input datafile file number=00001 name=+DATA/ASMDB/DATAFILE/system.257.1085957143
channel prmy2: starting datafile copy
input datafile file number=00003 name=+DATA/ASMDB/DATAFILE/sysaux.258.1085957177
channel prmy3: starting datafile copy
input datafile file number=00004 name=+DATA/ASMDB/DATAFILE/undotbs1.259.1085957203
channel prmy4: starting datafile copy
input datafile file number=00005 name=+DATA/ASMDB/DATAFILE/odata.264.1085963389
channel prmy5: starting datafile copy
input datafile file number=00007 name=+DATA/ASMDB/DATAFILE/users.260.1085957203
output file name=/u01/d01/asmdb/datafile/users.260.1085957203 tag=TAG20211020T152035
channel prmy5: datafile copy complete, elapsed time: 00:00:08</code></pre></li>
</ul>
<h2>恢复库成功后启动备库</h2>
<ul>
<li>
<p>打开备库</p>
<pre><code>--直接打开会报错
SQL> alter database open;
ORA-10456: cannot open standby database; media recovery session may be in progress
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.</code></pre>
</li>
<li>alert日志
<pre><code>[oracle@ASMDEMODG trace]$ tail -f alert_asmdbdg.log
Recovery of Online Redo Log: Thread 1 Group 11 Seq 16 Reading mem 0
Mem# 0: /u01/redo/ASMDBDG/onlinelog/o1_mf_11_jpwx0nlq_.log
Mem# 1: /u01/app/oracle/fast_recovery_area/ASMDBDG/onlinelog/o1_mf_11_jpwx0oqy_.log</code></pre></li>
</ul>