Oracle纯手工DG搭建

最新推荐文章于 2024-07-24 04:19:02 发布

迷彩斑斓

最新推荐文章于 2024-07-24 04:19:02 发布

阅读量1.4k

点赞数 26

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_45336263/article/details/138606902

版权

DG搭建

DG的三种同步模式：

模式	功能
最大保护模式(Maximum protection)	Redo日志必须在主库和备库写入后才能提交。
最大性能(Maximum Performance)	Redo日志发送后，主库就可以提交。
最大可用性(Maximum Availability)	结合两者优点

实验环境

配置	主库	备库
系统版本	Oracle-Linux-R6-U9	Oracle-Linux-R6-U9
磁盘	100G	100G
软件	Oracle Soft + database	Oracle Soft
数据库属性	开启FORCE LOGGING（强制日志）	-
归档	开启归档(见5.3开启归档日志)	-
主机名	Primary	Standby
监听服务名	Primary	Standby
sid_name	orcl	orcl
db_name	orcl	orcl
instance_name	orcl	orcl
db_unique_name	orcl	orclst
service_names	orcl	orclst

1.主库开启强制日志

--查询是否开启强制日志
SELECT force_logging FROM v$database;
--开启强制日志
ALTER DATABASE FORCE LOGGING;

2.主库添加standby redo log

作用：用于主备切换

要求：1.数量：redo日志组数+1；2.大小：等于redo log大小

--查询redo大小、组数
select GROUP#,BYTES/1024/1024 MB from v$log;
--查询redo日志组路径
select GROUP#,MEMBER from v$logfile;
--添加redo
alter database add standby logfile group 4 '/u01/oradata/orcl/redo04.log' size 50m;
alter database add standby logfile group 5 '/u01/oradata/orcl/redo05.log' size 50m;
alter database add standby logfile group 6 '/u01/oradata/orcl/redo06.log' size 50m;
alter database add standby logfile group 7 '/u01/oradata/orcl/redo07.log' size 50m;
--查看添加日志组信息
select group#,sequence#,status, bytes/1024/1024 from v$standby_log;

其他命令：
--删除redo，要自行删除物理文件
alter database drop logfile group 3;
--更改redo大小(先添加再删除即可)

3.主库监听配置

vim listener.ora 修改内容如下：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME=Primary)
      (SID_NAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    )
  )
-- Primary 为监听服务名称，可随意填写。SID_NAME = ORacle_sid 查明后填写

vim tnsnames.ora 修改内容如下：
Primary =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.218)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Primary)
    )
  )
Standby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.219)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Standby)
    )
  )
  -Primary：该监听服务别名，可以随意填写。 SERVICE_NAME = Primary：根据监听服务名称填写
  -Standby：该监听服务别名，可以随意填写。 SERVICE_NAME = Standby：根据监听服务名称填写

4.主库参数配置

#参数说明：orcl为主库db_unique_name，orclst为备库db_unique_name 
         Primary为主库监听服务名称，Standby为备库监听服务名称

alter system set log_archive_config='DG_CONFIG=(orcl,orclst)' scope=spfile;
alter system set log_archive_dest_1='location=/u01/oradata/archivelog VALI..D_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;
alter system set log_archive_dest_2='SERVICE=Standby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclst' scope=spfile;
alter system set log_archive_dest_state_1=enable  scope=spfile;
alter system set log_archive_dest_state_2=enable  scope=spfile;
alter system set remote_login_passwordfile=exclusive scope=spfile;
alter system set fal_client='Primary' scope=both; 
alter system set fal_server='Standby' scope=both;
alter system set standby_file_management=auto scope=spfile;
---- 如果主库和备库数据路径不相同，则需要设置替换目录，如何设置如下：
alter system set db_file_name_convert='/u01/oradata/orcl','/u01/oradata/orcl' scope=spfile;
alter system set log_file_name_convert='/u01/oradata/orcl','/u01/oradata/orcl' scope=spfile;
1.主备库名称
2.配置本地归档路径
3.配置远程归档路径
4.开启归档通道1
5.开启归档通道2
6.密码文件独占
7.设置本地库
8.设置远程库
9.设置备库文件自动管理
10.设置数据文件路径
11.设置日志文件路径

5.传输参数文件和密码文件到备库

在主库上生成初始化参数文件
SQL> create pfile from spfile;

从主库拷贝初始化参数文件和密码文件 通过scp传输到备库：
scp $ORACLE_HOME/dbs/orapworcl 192.168.100.219:/u01/app/oracle/product/11.2.0/db_1/dbs
scp $ORACLE_HOME/dbs/initorcl.ora 192.168.100.219:/u01/app/oracle/product/11.2.0/db_1/dbs
---如果主库sid和备库sid不相同，则需要把密码文件和参数文件名进行修改
如：mv orapworcl orapworclst && mv initorcl.ora initorclst.ora

6.备库修改参数文件

vim initorcl.ora 
修改以下内容：
---------------------------
*.audit_trail='os' 
*.db_unique_name='orclst'
*.fal_client='Standby'
*.fal_server='Primary'
*.log_archive_dest_1='location=/u01/oradata/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclst'
*.log_archive_dest_2='SERVICE=Primary LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
---------------------------
注意：initorclst.ora 涉及的目录 如果没有要手动创建
mkdir -p /u01/oradata/orcl/ 
mkdir -p /u01/app/oracle/admin/orcl/adump 
mkdir -p /u01/app/oracle/flash_recovery_area/orcl/ 
mkdir -p /u01/oradata/archivelog

7.使用初始化参数文件启动备库

--登陆到备库数据库
sqlplus / as sysdba

--创建spfile，可在$ORACLE_HOME/dbs下查看
create spfile from pfile;

--启动数据库到nomount状态
startup nomount;

8.备库监听配置

vim listener.ora 修改内容如下
SID_LIST_LISTENER = 
  (SID_LIST = 
    (SID_DESC = 
      (GLOBAL_DBNAME=Standby)
      (SID_NAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)       
    ) 
  )
 -- Primary 为监听服务名称，可随意填写。SID_NAME = ORacle_sid 查明后填写

vim tnsnames.ora 修改内容如下
Primary =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.218)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Primary)
    )
  )
Standby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.219)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Standby)
    )
  )
  -Primary：该监听服务别名，可以随意填写。 SERVICE_NAME = Primary：根据监听服务名称填写
  -Standby：该监听服务别名，可以随意填写。 SERVICE_NAME = Standby：根据监听服务名称填写
-------------------------
测试监听 Primary和Standby监听都要启动
tnsping Primary
tnsping Standby

9.备库通过Rman远程登录，开始进行复制

方法一：通过duplicate开始克隆数据库

rman target sys/oracle@Primary auxiliary sys/oracle@Standby nocatalog

--------两边目录结构不同 则不加nofilenamecheck
duplicate target database for standby from active database nofilenamecheck;

方法二：通过Rman备份数据库，再进行恢复

1.主库备份数据文件、归档日志文件、控制文件
backup database format '/u01/backup/data_%d_%T_%s_%p' tag 'data';
sql 'alter system archive log current';
backup archivelog all format='/u01/backup/arch_%d_%T_%s_%p' tag 'arc';
backup current controlfile for standby format='/u01/backup/ctl_%d_%T_%s_%p' tag 'cur';
2.远程传输到备库
scp /u01/backup/ root@192.168.100.219:/u01/backup/
3.备库开始恢复
rman target /
alter database nomount
restore standby controlfile from '/u01/backup/ctl_ORCL_20240509_23_1';
alter database mount;
catalog start with "/u01/backup";
restore database;
recover database;
[如报错RMAN-06054。执行下面语句：recover database until scn 1012850;]
alter database open resetlogs;

10.开启备库，设置主备同步

alter database open; 
alter database recover managed standby database using current logfile disconnect from session;

11.同步情况检查

---oracle进程检查
sql > select process from v$managed_standby;
主库显示：ARCH、ARCH、ARCH、ARCH  
备库显示：ARCH、MRPO和RFS表示正常

---归档序列号是否相同
sql> select max(sequence#) from v$archived_log where applied = 'YES'

12.日常管理命令

--数据库状态查询
select switchover_status,database_role from v$database;
--查看传输延迟
select * from v$dataguard_stats;
--关闭同步
alter database recover managed standby database cancel;
--开启同步
alter database recover managed standby database using current logfile disconnect from session;
--查询GAP(归档丢失)
SELECT THREAD#,LOW_SEQUENCE#, HIGH_SEQUENCE# FROM VSARCHIVE_GAP;

13.GAP恢复

备库GAP(归档丢失)文件丢失：
    1.从主库复制归档文件到备库
    2.备库手动注册恢复
    ALTER DATABASE REGISTER LOGFILE '路径/文件.dbf';

DG增量恢复：
故障环境：备库同步断开，备库没同步归档日志，同时主库有了增量数据，归档日志删除。
1.备库停止同步
alter database recover managed standby database cancel;
2.查询备库最小SCN号
select f.checkpoint_change#,d.enabled from v$datafile_header f,v$datafile d where f.file# = d.file#;
3.编辑备份脚本
-----------------------------------------------------------
#!/bin/ksh
$ORACLE_HOME/bin/rman target/ log=/tmp/backup_dg.log <<EOF
run
{
allocate channel t1 type disk;
allocate channel t2 type disk;
BACKUP as compressed backupset INCREMENTAL FROM SCN SCN号 DATABASE FORMAT '/oradata/dg_%U' tag 'Forstandby';
release channel t1;
release channel t2;
}
EOF
-----------------------------------------------------------
4.在主库执行脚本进行备份
 sh dg.sh
5.备份主库的控制文件
ALTER DATABASE CREATE standby controlfile As '/tmp/standby2.ctl';
6.把生成的备份的数据文件和控制文件复制到备库
7.关闭备库并启动到mount状态
 shutdown immediate;
 startup mount;
8.Rman备份备库控制文件
  rman target /
  backup current controlfile format '/tmp/standby1.ctl'
9.Rman恢复数据
  --备库重新启动到nomount状态
  alter database nomount
  --备库恢复主库控制文件
  restore controlfile from '/tmp/standby2.ctl'
  --注册备份集
  CATALOG START WITH '拷贝过来的数据路径'
  --增量数据恢复
  recover database noredo;
11.启动备库开启同步
   alter database open;
   alter database recover managed standby database using current logfile disconnect from session;

14.GD切换

1.查询主备库是否满足切换条件
SQL> select switchover_status,database_role,open_mode from v$database;
主库显示:TO STANDBY/PRIMARY，如果显示SESSION ACTIVE表示还有活动的会话，需要关闭会话再检查
备库显示:NOT ALLOWED/PHYSICAL STANDBY

2.主库切换备库
SQL>alter database commit to switchover to physical standby;
SQL>shutdown immediate;
SQL>startup mount;

3.备库切换主库
SQL>alter database commit to switchover to physical Primary;
SQL>alter database open;

4.新备库开启同步
SQL>alter database recover managed standby database using current logfile disconnect from session;

[主库故障，切换备库]
1.取消DG同步
alter database recover managed standby database cancel;
2.备库failover跟换
alter database recover managed standby database finish;
3.查看角色
select name,log_mode,open_mode,database_role,switchover_status,db_unique_name from v$database;
4.备库切换
alter database commit to switchover to primary with session shutdown;