11g单实例adg部署

广大1年前技术文章410

一、环境规划

搭建adg需要备端完成rdbms软件安装这一步和监听配置,不需要安装数据库。

注意:

db_unique_name 主备库不能相同

db_name主备库需保持一致

主备库DB版本需保持一致

信息项

主数据库(Primary)

备数据库(Standby)

主机名

orcl1

orcl2

IP地址

172.16.104.58

172.16.104.59

sys|system口令

123456

123456

db_name

orcl

orcl

db_unique_name(主备需要不同)

orcl

orcl_dg

instance_name

orcl

orcl

service_name

orcl

orcl_dg

ORACLE_BASE

/u01/app/oracle

/u01/app/oracle

ORACLE_HOME

/u01/app/oracle/product/11.2.0/dbhome_1

/u01/app/oracle/product/11.2.0/dbhome_1

数据存储位置

文件系统/u01/app/oracle/oradata

文件系统/u01/app/oracle/oradata_std

归档路径

/u01/arch

/u01/arch_std

数据库版本

11.2.0.4

11.2.0.4

操作系统(可以版本不一样,官网有支持的系统版本)

centos7.9

centos7.9

二、搭建流程

1、配置生产主库为归档模式

SQL> alter system set log_archive_dest_1='location=/u01/arch' scope=spfile;

SQL>alter system set log_archive_format='%t_%s_%r.arch' scope=spfile; --更改归档文件格式,可以不设置这步。

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 /u01/arch

Oldest online log sequence 1

Next log sequence to archive 3

Current log sequence 3

注意:对rac环境来说,打开归档模式需要所有实例全停后操作。

2、设置生产主库为force logging

SQL> alter database force logging;

SQL> select force_logging from v$database;

FORCE_LOGGING

--------------------

YES

注意:在rac环境下,本操作只需在其中任意一节点执行即可

 

3、网络配置

1、配置监听

因为恢复的时候是rman远程登陆的,备端需要配置静态监听,不然无法连接。

 

[oracle@orcl2 admin]$ vi listener.ora 添加如下内容:

#动态监听

LISTENER =

 (DESCRIPTION_LIST =

 (DESCRIPTION =

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

 (ADDRESS = (PROTOCOL = TCP)(HOST = orcl2)(PORT = 1521))

 )

 )

 

#静态监听

SID_LIST_LISTENER =

 (SID_LIST =

 (SID_DESC =

 (GLOBAL_DBNAME = orcl_dg)

 (ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)

 (SID_NAME = orcl)

 )

 )

ADR_BASE_LISTENER = /u01/app/oracle

2、配置两端tnsnames.ora文件

cd $ORACLE_HOME/network/admin/

vi tnsnames.ora

orcl_pri=

 (DESCRIPTION =

 (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.104.58)(PORT = 1521))

 )

 (CONNECT_DATA =

 (SERVICE_NAME = orcl)

 )

 )

orcl_std=

 (DESCRIPTION =

 (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.104.59)(PORT = 1521))

 )

 (CONNECT_DATA =

 (SERVICE_NAME = orcl_dg)

 )

 )

 

--主备分别tnsping 测试和登陆测试

[oracle@orcl1 admin]$ tnsping orcl_pri

[oracle@orcl1 admin]$ tnsping orcl_std

[oracle@orcl2 admin]$ sqlplus sys/123456@orcl_std as sysdba

 

4、主库密码文件复制到从库:

[oracle@orcl1 dbs]$ scp $ORACLE_HOME/dbs/orapworcl oracle@172.16.104.59:$ORACLE_HOME/dbs

5、更改主库参数

alter system set log_archive_config='dg_config=(orcl,orcl_dg)';

alter system set log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl';

alter system set log_archive_dest_2='service=orcl_std lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=orcl_dg';

alter system set log_archive_dest_state_1=enable;

alter system set log_archive_dest_state_2=enable;

alter system set fal_server='orcl_std';

alter system set fal_client='orcl_pri';

alter system set service_names='orcl','orcl_dg' scope=both;

alter system set standby_file_management='auto';

--下面重启生效

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

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

6、更改备库参数

1、主库创建pfile,传输到备库

create pfile='/home/oracle/pfile0309.txt' from spfile;

scp /home/oracle/pfile0309.txt oracle@172.16.104.59:$ORACLE_HOME/dbs/initorcl.ora

2、修改备库参数

主要添加如下内容:

*.db_unique_name='orcl_dg'

*.log_archive_dest_1='location=/u01/arch_std valid_for=(all_logfiles,all_roles) db_unique_name=orcl_dg'

*.log_archive_dest_2='service=orcl_pri lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=orcl'

*.fal_server='orcl_pri'

*.fal_client='orcl_std'

*.log_file_name_convert='/u01/app/oracle/oradata','/u01/app/oracle/oradata_std'

*.db_file_name_convert='/u01/app/oracle/oradata','/u01/app/oracle/oradata_std'

*.standby_file_management='manual'

 

7、创建备库目录

主要是参数中出现的目录:有审计和归档和FRA以及数据文件目录,oracle用户执行

mkdir -pv /u01/app/oracle/admin/orcl/adump

mkdir -pv /u01/arch_std

mkdir -pv /u01/app/oracle/fast_recovery_area

mkdir -pv /u01/app/oracle/oradata_std/orcl/

mkdir -pv /u01/app/oracle/fast_recovery_area/orcl/

8、主库添加standby redo log

Standby redo log是为了在日志应用开启实时应用的时候能够接收生产端的redo log的相应的内容,一般情况下,每个实例节点的standby redo log比生产redo log至少多1组。

#检查日志组情况:

set linesize 300

col member for a50

select thread#,group#,MEMBERS,bytes/1024/1024 mb,status,blocksize from gv$log order by 1,2;

select thread#,group#,bytes/1024/1024 mb,status,blocksize from v$standby_log order by 1,2;

select * from gv$logfile order by 1,2;

 

#创建standby redo log

Alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redo_st04.log') size 100m;

Alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redo_st05.log') size 100m;

Alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redo_st06.log') size 100m;

Alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redo_st07.log') size 100m;

 

9、启动备库到nomount

[oracle@orcl2 ~]$ sqlplus / as sysdba

SQL> startup nomount

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initorcl.ora'

 

10、rman恢复

1、在主库进行全库备份,并传输到备库服务器上

[oracle@orcl1 ~]$ rman target /

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

backup database format '/u01/rmanbak/full_%d_%T_%s_%p';

sql 'alter system archive log current';

sql 'alter system archive log current';

sql 'alter system archive log current';

backup archivelog all format '/u01/rmanbak/arch_%d_%T_%s_%p';

backup current controlfile for standby format '/u01/rmanbak/control_std.ctl'; --rman方法配置备库使用的控制文件

release channel c1;

release channel c2;

release channel c3;

}

 

[oracle@orcl1 ~]$ scp -r /u01/rmanbak/ oracle@172.16.104.59:/u01/

2、备库恢复控制文件

rman target /

restore standby controlfile from '/home/oracle/control_std.ctl'; --根据拷贝到备端控制文件路径名称修改

alter database mount;

3、进行rman恢复

--备库启动到mount状态进行恢复:

[oracle@orcl2 ~]$ rman target /

--注册备份集到控制文件

catalog start with '/u01/rmanbak/';

--备份集信息检查校验

list backup;

crosscheck backup;  

delete expired backup;

crosscheck archivelog all;

delete expired archivelog all;

--进行还原

run

{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

allocate channel c3 device type disk;

SET NEWNAME FOR DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf' to '/u01/app/oracle/oradata_std/orcl/users01.dbf';

SET NEWNAME FOR DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf' to '/u01/app/oracle/oradata_std/orcl/undotbs01.dbf';

SET NEWNAME FOR DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf' to '/u01/app/oracle/oradata_std/orcl/sysaux01.dbf';

SET NEWNAME FOR DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf' to '/u01/app/oracle/oradata_std/orcl/system01.dbf';

restore database;

switch datafile all;

release channel c1;

release channel c2;

release channel c3;

}

--进行恢复直到报错

recover database;

11、开启日志应用

[oracle@orcl2 ~]$ sqlplus / as sysdba

--启动数据库到open状态

alter database open read only;

--开启实时日志应用

Alter database recover managed standby database using current logfile disconnect from session;

 

--开启自动主备库同步的参数

alter system set standby_file_management='auto';

 

12、检查数据库

--查看数据库状态和模式

set linesize 300;

select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,switchover_status from gv$database;

--查看是否延迟

select value from v$dataguard_stats where name='apply lag';

--主库执行切换日志,然后看主备库分别查看最大序列号,看看是否应用过去了

alter system switch logfile;

select thread#,applied,max(sequence#) from v$archived_log group by thread#,applied order by 1,2 ;

主库创建测试表,看备库是否同步过去了

相关文章

minio存储桶命名规则

存储桶命名规则创建S3存储桶后,无法更改存储桶名称,因此请明智地选择名称。重要在2018年3月1日,我们更新了美国东部(弗吉尼亚北部)地区S3存储桶的命名约定,以匹配我们在所有其他全球AWS区域中使用...

Nginx-Ingress和traefik区别

Nginx-Ingress和traefik区别

Nginx-Ingress和traefik区别1. Ingress Controllerk8s 是通过一个又一个的 controller 来负责监控、维护集群状态。Ingress Controller...

MySQL 使用开源审计插件

MySQL 使用开源审计插件

前言MySQL 只有企业版有审计插件,开源社区版没有审计插件。企业要通过等保需要开通审计,这里记录使用 MariaDB 开源审计插件,让 MySQL 社区版拥有审计功能。1. 审计插件下载审计插件是包...

mysql 事务隔离级别

mysql 事务隔离级别

一、事务隔离级别介绍多个连接开启各自事务操作数据库中数据时,数据库系统要负责隔离操作,以保证各个连接在获取数据时的准确性。事务隔离级别      MySQL隔离级别定义了事务与事务之间的隔离程度  二...

adg切换(RAC TO SINGLE)

adg切换步骤:1、关闭两节点监听和清理外部链接:su - gridsrvctl stop listenerps -ef|grep LOCAL=NO|awk '{print $2}'|xargs ki...

Prometheus与Zabbix的对比

一、Prometheus与Zabbix的对比对比项PrometheusZabbixPrometheus优势Zabbix优势管理二进制文件启动LNMP+编译轻量级Server,便于迁移和维护-配置配置文...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。