Oracle-通过(RESTORE/RECOVER FROM SERVICE)方式搭建DataGuard

前言:

Oracle在12c推出了新特性(RESTORE/RECOVER ...FROM SERVICE)通过网络服务方式恢复数据库，这个新特性也大大简化了DataGuard主备的搭建。

测试环境:

	主库	备库
数据库版本	19.3.0.0	19.3.0.0
架构	单实例	单实例
db_unique_name	testdb	testdg
ip	192.168.2.101	192.168.2.201

通过from service部署主备:

1    检查数据库开启归档以及force_logging

---数据库开启归档模式
archive log list 
---检查开启force_logging
select force_logging from v$database;
alter database force logging;

2    检查并开启SYS远程登录

---确认参数remote_login_passwordfile为EXCLUSIVE
show parameter remote_login_passwordfile

3    配置主备TNS

---配置主备TNS
#主库tns
testdb =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.201)(PORT = 1521))
  (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
   ) 
#备库tns
testdg=
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdg)
     )
  )

4    配置主库参数

---设置主库参数  
alter system set log_archive_dest_1='location=/u01/app/oracle/arch'scope=both  sid='*';
alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both  sid='*';
alter system set log_archive_config='dg_config=(testdb,testdg)' scope=both  sid='*';
alter system set log_archive_dest_2=
'service=testdg LGWR ASYNC NOAFFIRM delay=0 optional 
 compression=disable max_failure=0 max_connections=1 reopen=30 
 net_timeout=300 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) 
 db_unique_name=testdg' scope=both  sid='*';
alter system set log_archive_dest_state_2=defer scope=both  sid='*';
alter system set fal_client=testdb scope=both sid='*'; 
alter system set fal_server=testdg scope=both sid='*'; 
alter system set standby_file_management=AUTO scope=both sid='*';

5    主库添加standby log

---添加standby_log
#查询当前log的设置
select thread#,group#,bytes/1024/1024 SIZE_IN_GB, status from v$log;
#创建需要保持一致
alter database ADD standby logfile thread 1 group 21 ('/u01/app/oracle/oradata/TESTDB/onlinelog/redo01.log') size 200M ;
alter database ADD standby logfile thread 1 group 22 ('/u01/app/oracle/oradata/TESTDB/onlinelog/redo02.log') size 200M ; 
alter database ADD standby logfile thread 1 group 23 ('/u01/app/oracle/oradata/TESTDB/onlinelog/redo03.log') size 200M ; 
alter database ADD standby logfile thread 1 group 24 ('/u01/app/oracle/oradata/

6    拷贝密码文件到备库

scp -rp $ORACLE_HOME/dbs/orapwtestdb 192.168.2.101:$ORACLE_HOME/dbs/orapwtestdg

7   生成spfile文件拷贝到备库

create pfile='/tmp/initstandby.ora' from spfile;
scp -rp /tmp/initstandby.ora 192.168.2.101:$ORACLE_HOME/dbs/inittestdg.ora

8   备库编辑参数

---参数需要根据实际环境进行修改，以下参数只是测试环境参数，不能作为生产使用
*.audit_file_dest='/u01/app/oracle/admin/testdg/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/TESTDG/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='testdb'
*.db_unique_name='testdg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdgXDB)'
*.fal_client='TESTDG'
*.fal_server='TESTDB'
*.local_listener=''
*.log_archive_config='dg_config=(testdb,testdg)'
*.log_archive_dest_1='location=/u01/app/oracle/arch'
*.log_archive_dest_2='service=testdb LGWR ASYNC NOAFFIRM delay=0 optional
 compression=disable max_failure=0 max_connections=1 reopen=30
 net_timeout=300 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)
 db_unique_name=testdb'
*.log_archive_dest_state_2='DEFER'
*.memory_target=1470m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
---创建spfile
create spfile from pfile;

9   备库创建审计，数据目录

mkdir -p /u01/app/oracle/admin/testdg/adump
mkdir -p /u01/app/oracle/oradata/TESTDG/controlfile
mkdir -p /u01/app/oracle/oradata/TESTDG/datafile
mkdir -p /u01/app/oracle/oradata/TESTDG/tempfile
mkdir -p /u01/app/oracle/oradata/TESTDG/onlinelog
mkdir -p /u01/app/oracle/oradata/arch

10   启动备库实例到nomount

startup nomount

11   备库通过service远程恢复standby controlfile

rman target /
restore standby controlfile from service 'testdb';
​
RMAN> restore standby controlfile from service 'testdb';
​
Starting restore at 2022/11/01 15:46:02
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
​
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TESTDG/controlfile/control01.ctl
Finished restore at 2022/11/01 15:46:06
​
RMAN> 

12   启动到mount

RMAN> alter database mount;
​
released channel: ORA_DISK_1
Statement processed
RMAN>
 

13   备库通过from service恢复数据文件

---可以通过修改rman的默认配置，设置并行
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 8;
​
---恢复管道section size的大小参考以下公式算法
On the primary, query the largest datafile size to determine the section size to be used for the recover command.
SQL> select max(bytes)/1073741824 GB from v$datafile;
If the largest file is:
<15TB use section size of 64GB
>15TB and <30TB used section size of 128G
>30TB and <60TB used section size of 256G
>60TB use section size of 512G
​
---进行恢复
#注意使用sys/password方式登陆，不然会出现
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12001: could not open channel c1
RMAN-10008: could not create channel context
RMAN-10003: unable to connect to target database
ORA-01017: invalid username/password; logon denied
​
rman target sys/oracle
run
{
allocate channel c1 type disk connect '/@testdg';
allocate channel c2 type disk connect '/@testdg';
allocate channel c3 type disk connect '/@testdg';
allocate channel c4 type disk connect '/@testdg';
allocate channel c5 type disk connect '/@testdg';
allocate channel c6 type disk connect '/@testdg';
allocate channel c7 type disk connect '/@testdg';
allocate channel c8 type disk connect '/@testdg';
restore database from service 'testdb' section size 64G;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
}

14   备库恢复日志

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=13 device type=DISK
​
allocated channel: c2
channel c2: SID=258 device type=DISK
​
allocated channel: c3
channel c3: SID=20 device type=DISK
​
allocated channel: c4
channel c4: SID=259 device type=DISK
​
allocated channel: c5
channel c5: SID=21 device type=DISK
​
allocated channel: c6
channel c6: SID=260 device type=DISK
​
allocated channel: c7
channel c7: SID=22 device type=DISK
​
allocated channel: c8
channel c8: SID=261 device type=DISK
​
Starting restore at 2022/11/01 16:34:06
​
channel c1: starting datafile backup set restore
channel c1: using network backup set from service testdb
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_system_kp1phykz_.dbf
channel c1: restoring section 1 of 1
channel c2: starting datafile backup set restore
channel c2: using network backup set from service testdb
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00003 to /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_sysaux_kp1phyo1_.dbf
channel c2: restoring section 1 of 1
channel c3: starting datafile backup set restore
channel c3: using network backup set from service testdb
channel c3: specifying datafile(s) to restore from backup set
channel c3: restoring datafile 00004 to /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_undotbs1_kp1phyt2_.dbf
channel c3: restoring section 1 of 1
channel c4: starting datafile backup set restore
channel c4: using network backup set from service testdb
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00007 to /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_users_kp1phz14_.dbf
channel c4: restoring section 1 of 1
channel c4: restore complete, elapsed time: 00:00:02
channel c3: restore complete, elapsed time: 00:00:08
channel c1: restore complete, elapsed time: 00:00:16
channel c2: restore complete, elapsed time: 00:00:16
Finished restore at 2022/11/01 16:34:22
​
released channel: c1
​
released channel: c2
​
released channel: c3
​
released channel: c4
​
released channel: c5
​
released channel: c6
​
released channel: c7
​
released channel: c8

15   启动备库到open read only

alter database open read only;

16   备库添加standby log

---备库添加standby_log
#查询当前log的设置
select thread#,group#,bytes/1024/1024 SIZE_IN_GB, status from v$log;
#创建需要保持一致
alter database ADD standby logfile thread 1 group 21 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo01.log') size 200M ;
alter database ADD standby logfile thread 1 group 22 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo02.log') size 200M ; 
alter database ADD standby logfile thread 1 group 23 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo03.log') size 200M ; 
alter database ADD standby logfile thread 1 group 24 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo04.log') size 200M ;

17   备库启动mrp进程进行同步，完成DataGuard主备搭建

---启动mrp进程
alter database recover managed standby database using current logfile disconnect from session;
​
SQL> SQL> SQL> 
SOURCE_DBID SOURCE_DB_UNIQUE_NAME       NAME            VALUE             UNIT
----------- -------------------------------- -------------------------------- -------------------------------------------------- ------------------------------
TIME_COMPUTED           DATUM_TIME        CON_ID
------------------------------ ------------------------------ ----------
 2902942490 testdb           transport lag          +00 00:00:00           day(2) to second(0) interval
11/01/2022 16:53:03         11/01/2022 16:53:03           0
​
 2902942490 testdb           apply lag            +00 00:00:00           day(2) to second(0) interval
11/01/2022 16:53:03         11/01/2022 16:53:03           0
​
 2902942490 testdb           apply finish time                   day(2) to second(3) interval
11/01/2022 16:53:03                   0
​
    0             estimated startup time        9              second
11/01/2022 16:53:03                   0

总结:

        通过(RESTORE/RECOVER ...FROM SERVICE)方式进行备库DG的部署，可以提高备库数据初始化的速度以及简化部署的步骤。