linux7.6部署oracle11g-DG

Tyler唐

已于 2024-06-30 17:31:55 修改

阅读量839

点赞数 23

文章标签：运维

于 2024-06-29 22:01:09 首次发布

本文链接：https://blog.csdn.net/weixin_44270742/article/details/140069065

版权

一、安装oracle备库，参考以下链接

http://t.csdnimg.cn/XYhdm

二、开始搭建DG

1、【主库】开启归档模式

#显示当前归档信息
archive log list;

#如果没有开启归档，则使用以下命令开启

shutdown immediate; #关闭数据库

startup mount;#启动列程

alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;#设置归档文件位置也称为归档目标

alter database archivelog;#改变日志模式

alter database open;#打开数据库

2、【主库】开启force_logging

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

3、【主库】添加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;

4、【主库】配置监听,此处可根据主库情况做调整，比如主库不是ORCL，可参考做修改，此处不要直接复制下面的，会有格式的问题(后面rman会报错)，直接复制源文件的，再修改

vi $ORACLE_HOME/network/admin/tnsnames.ora#配置如下

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 主ip)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

ORCLDG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 备库ip)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

5、【主库】修改参数

#配置备库归档

alter system set standby_file_management=auto scope=both;

alter system set log_archive_dest_2='SERVICE=orcldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' scope=both;

alter system set fal_server='orcldg' scope=both;

alter system set fal_client='orcl' scope=both;

alter system set log_archive_dest_state_1=enable scope=both;

alter system set log_archive_dest_state_2=enable scope=both;

#查询主库数据文件路劲的命令如下：

select name from v$datafile;
select file_name from dba_data_files;

#配置主备库数据文件名称转换关系,路劲需要根据上面查出来的做更改

alter system set db_file_name_convert ='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl' scope=spfile;

#配置主备库日志文件名称转换关系,路劲需要根据实际情况

alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl' scope=spfile;

6、【主库】生成参数文件

create pfile='/tmp/initorcl.ora' from spfile;

7、【备库】oracle用户下将主库的参数文件、控制文件、tnsnames.ora拷贝到备库

scp root@主库ip:$ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/

scp root@主库ip:/tmp/initorcl.ora $ORACLE_HOME/dbs/

scp root@主库ip:$ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/

#如果主库sid和备库sid不相同，则需要把密码文件和参数文件名进行修改

例：mv orapworcl orapworclst && mv initorcl.ora initorclst.ora

8、【备库】配置静态监听

vi $ORACLE_HOME/network/admin/listener.ora

LISTERNER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 备库IP)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(GLOBAL_DBNAME=orcl)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_4/)
(SID_NAME=orcl)
)
)

#修改完保存退出，然后重新启动监听

lsnrctl stop

lsnrctl start

9、【备库】修改参数文件

vi $ROACLE_HOME/dbs/initorcl.ora

主要是下面框起来的地方需要做修改

并将此文件中所涉及到的路劲都在备库上创建，使用oracle用户

10、【备库】使用初始化参数文件启动备库

启动备份

#登陆到备库数据库

sqlplus / as sysdba

#创建spfile，可在$ORACLE_HOME/dbs下查看

shutdown immediate;

create spfile from pfile;

#启动数据库到nomount状态

startup nomount;

11、【备库】数据同步，通过duplicate开始克隆数据库

rman target sys/password@ORCL auxiliary sys/password@ORCLDG nocatalog

duplicate target database for standby from active database nofilenamecheck;

12、【备库】打开备库并开启日志实时应用

Sql>alter database open;

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

13、复制完成后检查备库状态

#归档模式已打开

archive log list;

#数据库角色应为PHYSICAL STANDBY，打开模式为MOUNTED

select database_role, protection_mode, protection_level, open_mode from v$database;

14、同步情况检查

#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';

15、常用命令

#数据库状态查询
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;

16、DG切换

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;

17、查询GAP(归档丢失)

SELECT THREAD#,LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

#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;