ADG单实例搭建系列之(Active Database Duplicate Using Image Copies)

网友投稿 752 2022-05-30

参考自:Data Guard Physical Standby Setup in Oracle Database 11g Release 2

MOS文档:Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE…FROM ACTIVE DATABASE (Doc ID 1075908.1)

官方文档:Duplicating Databases

一、Active Database Duplicate步骤(Using Image Copies)

1.配置主库DG参数,备库根据主库的PFILE,设置参数值,生成备库SPFILE. 2.配置hosts文件,配置TNS,配置静态监听,添加standby log文件. 3.拷贝主库的密码文件至备库,备库创建PFILE中不存在的目录. 4.把备库启动到nomount状态. 5.RMAN同时连接主库与备库,执行duplicate命令.

注:由于Active Database Duplicate无需提前备份,而是通过网络在线copy数据库文件,对主库的CPU等负载要求较高,因此最好在空闲时间进行操作,对于TB级别的数据库,使用Active Duplicate进行DG搭建效率较高,节省空间,但是对网络要求较高;源库必须使用SPFILE。

二、环境准备

Notes:

1、db_unique_name主备库不能相同。

2、db_name主备库需保持一致。

3、主备库DB版本需保持一致。

三、搭建过程

1、Oracle软件安装

ADG单实例搭建系列之(Active Database Duplicate Using Image Copies)

主库一键安装:

./AllOracleSilent.sh -i 192.168.56.120 -d 11g -n orcl -o orcl -b /u01/app -s AL32UTF8

备库一键安装:(备库仅安装ORACLE软件,不建库)

./AllOracleSilent.sh -i 192.168.56.121 -d 11g -w Y -n orcl_stby -o orcl -b /u01/app -s AL32UTF8

一键安装脚本可参考:ORACLE一键安装单机11G/12C/18C/19C并建库脚本

2、环境配置

a.配置hosts文件

主库:

cat <> /etc/hosts ##FOR DG BEGIN 192.168.56.121 orcl_stby ##FOR DG END EOF

备库:

cat <> /etc/hosts ##FOR DG BEGIN 192.168.56.120 orcl ##FOR DG END EOF

b.配置静态监听和TNS

主库+备库:

Notes:注意这里的GLOBAL_DBNAME和service_name保持一致,即备库需要改为 orcl_stby。

##listener.ora su - oracle -c "cat <> /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora ##FOR DG BEGIN SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db) (SID_NAME = orcl) ) ) ##FOR DG END EOF" ##重启监听 su - oracle -c "lsnrctl stop" su - oracle -c "lsnrctl start" ##tnsnames.ora su - oracle -c "cat <> /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora ##FOR DG BEGIN ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCL_STBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl_stby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl_stby) ) ) ##FOR DG BEGIN EOF"

c.主库配置参数

select log_mode,force_logging from gv$database; LOG_MODE FOR ------------ --- NOARCHIVELOG NO --开启归档模式 shutdown immediate startup mount alter database archivelog; alter database open; --开启强制日志模式 alter database force logging; --查看数据文件路径是否一致,OMF参数建议关闭 select name from v$datafile; show parameter db_create_file_dest alter system reset db_create_file_dest; --NOTES:如果数据文件路径不一致,duplicate将失败。 --设置DG参数 ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STBY)'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER; ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; ALTER SYSTEM SET FAL_SERVER=ORCL_STBY; ALTER SYSTEM SET FAL_CLIENT=ORCL; ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl' SCOPE=SPFILE; ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl' SCOPE=SPFILE; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

d.生成备库pfile文件并修改,复制参数文件和密码文件至备库

create pfile='/tmp/initorcl_stby.ora' from spfile; --修改的部分: *.db_unique_name=orcl_stby *.fal_client='ORCL_STBY' *.fal_server='ORCL' *.log_archive_config='DG_CONFIG=(ORCL_STBY,ORCL)' *.log_archive_dest_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_STBY' *.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL' --复制参数文件至备库(备库执行) scp oracle@orcl:/tmp/initorcl_stby.ora /tmp --复制密码文件至备库(备库执行),要在oracle用户下复制 su - oracle scp oracle@orcl:/u01/app/oracle/product/11.2.0/db/dbs/orapworcl /u01/app/oracle/product/11.2.0/db/dbs

e.主库添加stanby log文件

set line222 col member for a60 select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from gv$logfile t1,gv$log t2 where t1.group#=t2.group#; THREAD# GROUP# MEMBER T2.BYTES/1024/1024 ---------- ---------- ------------------------------------------------------------ ------------------ 1 3 /oradata/orcl/redo03.log 120 1 2 /oradata/orcl/redo02.log 120 1 1 /oradata/orcl/redo01.log 120 --需要注意: --1.stanby log日志大小与redo log日志保持一致 --2.stanby log数量: standby logfile=(1+logfile组数)*thread=(1+3)*1=4组,需要加4组standby logfile. --3.thread要与redo log保持一致,如果是rac,需要增加多个thread对应的standby log ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 4 ('/oradata/orcl/standby_redo04.log') SIZE 120M, group 5 ('/oradata/orcl/standby_redo05.log') SIZE 120M, group 6 ('/oradata/orcl/standby_redo06.log') SIZE 120M, group 7 ('/oradata/orcl/standby_redo07.log') SIZE 120M;

f.备库创建db目录,开启到nomount状态

su - oracle -c "mkdir -p /oradata/orcl" su - oracle -c "mkdir -p /u01/app/oracle/fast_recovery_area/orcl" su - oracle -c "mkdir -p /u01/app/oracle/admin/orcl/adump" sqlplus / as sysdba create spfile from pfile='/tmp/initorcl_stby.ora'; startup nomount

3、 RMAN DUPLICATE

rman target sys/oracle@orcl AUXILIARY sys/oracle@orcl_stby run { allocate channel prmy1 type disk; allocate channel prmy2 type disk; allocate auxiliary channel aux1 type disk; allocate auxiliary channel aux2 type disk; DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK; }

4、备库开启日志应用

alter database open read only; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY SQL> SELECT protection_mode FROM v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE

5、主库开启LOG_ARCHIVE_DEST_STATE_2

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

6、测试同步情况

set line222 col member for a60 --查看是否存在RFS和MRP进程 select process,group#,thread#,sequence# from gv$managed_standby; --查看standby日志status是否存在active select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#; --主库建表空间,建用户,建表,增删改测试 create tablespace TEST datafile '/oradata/orcl/test01.dbf' size 100M autoextend off; create user test identified by test; grant dba to test; conn test/test create table test(id number); insert into test values (1); insert into test values (2); commit; --备库查看是否同步 SQL> select tablespace_name from dba_tablespaces where tablespace_name='TEST'; TABLESPACE_NAME ------------------------------ TEST SQL> select username,account_status,created from dba_users where username='TEST'; USERNAME ACCOUNT_STATUS CREATED ------------------------------ -------------------------------- ------------------ TEST OPEN 17-APR-21 SQL> select * from test.test; ID ---------- 1 2

Image

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:Python操作SQLite/MySQL/LMDB/LevelDB
下一篇:scala快速入门系列【Actor并发编程】
相关文章