11g单实例adg部署

广大2年前技术文章737

一、环境规划

搭建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 ;

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

相关文章

ReadConcern与WriteConcern

一、ReadConcern1、ReadConcern vs ReadPreferenceReadPreference 主要控制从副本集哪个节点来读取数据,该参数可以实现读写分离、就近读取的功能prim...

python-序列化和反序列化

1、为什么要序列化内存中的字典、列表、集合以及各种对象,如何保存到一个文件中?如果是自己定义的类的实例,如何保存到一个文件中?如何从文件中读取数据,并让它们在内存中再次恢复成自己对应的类的实例?要设计...

bind服务-1

bind服务-1

DNS:域名系统(英文:Domain Name System)是一个域名系统,是万维网上作为域名和IP地址相互映射的一个分布式数据库,能够使用户更方便的访问互联网,而不用去记住能够被机器直接读取的IP...

大数据即席查询-Kylin

大数据即席查询-Kylin

一、Kylin 定义 Apache Kylin 是一个开源的分布式分析引擎,提供 Hadoop/Spark 之上的 SQL 查询接口 及多维分析(OLAP)能力以支持超大规模数据,最初由 eBay I...

使用clickhouse-copier迁移数据

说明clickhouse-copier是clickhouse官方提供的一个数据迁移工具。支持将clickhouse表从一个集群迁移到另外一个集群。使用clickhouse-copier有一些限制条件:...

SpringBootWeb 篇-深入了解 SpringBoot + Vue 的前后端分离项目部署上线与 Nginx 配置文件结构(2)

SpringBootWeb 篇-深入了解 SpringBoot + Vue 的前后端分离项目部署上线与 Nginx 配置文件结构(2)

        3.0 在云服务器进行环境配置        将项目放到云服务器上运行,那么离不开项目所依赖的环境,比如...

发表评论    

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