Oracle11g dg搭建步骤

最新推荐文章于 2025-06-18 09:20:27 发布

wanedepidan

最新推荐文章于 2025-06-18 09:20:27 发布

阅读量3k

点赞数

CC 4.0 BY-SA版权

文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/wanedepidan/article/details/129204520

本文详细介绍了如何配置Oracle数据库的主库和备库，包括开启归档模式、设置归档日志参数、创建备用日志文件、建立备库参数文件以及实现主备库之间的实时同步。在验证步骤中，确保日志序列号一致以确认同步成功。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

主库上的操作

一、开启force logging

SQL> alter database force logging;

修改主库初始化参数文件（根据实际情况修改）

SQL> alter system setlog_archive_config='DG_CONFIG=(orcl,orcls)';

注：其中dg_config填写的是主备库的db_unique_name，orcls是备库

SQL>alter system set log_archive_dest_1='LOCATION=/data/oradata/ocrl/archivelogvalid_for=(all_logfiles,all_roles) db_unique_name=ocrl' scope=both;

SQL> alter system setlog_archive_dest_2='SERVICE=orcls ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcls' scope=both;

注：第一个ocrls是tnsname.ora的连接名，第二ocrls 是DB_UNIQUE_NAME

SQL> alter system setlog_archive_dest_state_1=ENABLE;

SQL> alter system setlog_archive_dest_state_2=ENABLE;

SQL> alter system set fal_server=orcls;

注：设置standby库从哪个数据库获取归档日志（只对standby库有效，在主库上设置是为了在故障切换后，主库可以成为备库使用）

SQL>alter system set db_create_file_dest='/data/oradata/ocrl/datafile' scope=both;

SQL> alter system set db_file_name_convert='/data/oradata/orcls/datafile','/data/oradata/orcl/datafile'scope=both;

SQL> alter system setlog_file_name_convert='/data/oradata/orcls/redo','/data/oradata/orcl/redo'scope=both;

SQL> alter system setstandby_file_management='AUTO' scope=both;

注：设置文件管理模式，此项设置为自动，不然在主库创建数据文件后，备库不会自动创建

SQL> alter system setlog_archive_max_processes=30;

注：设置归档日志进程的最大数量（视实际情况调整）

开启归档模式

SQL> archive log list

如果是Archive Mode则已经是归档模式

如果是No Archive Mode模式则运行以下命令

SQL> shutdown immediate //先确认是否可停库

SQL> startup mount

SQL> alter database archivelog

SQL> alter database open

SQL> archive log list

创建standby logfile

查看是否有

selectgroup#,thread#,sequence#,archived,status from v$standby_log;

查看当前redo日志大小

SQL> selectthread#,group#,bytes/1024/1024 from v$log;

THREAD# GROUP# BYTES/1024/1024

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

1 1 50

1 2 50

2 3 50

2 4 50

新建多个（最少多一个或一组）和当前日志大小相同的standby logfile

SQL> alter database add standby logfilethread 1 group 10 size 50M;

SQL> alter database add standby logfilethread 1 group 11 size 50M;

SQL> alter database add standby logfilethread 1 group 12 size 50M;

SQL> alter database add standby logfilethread 2 group 13 size 50M;

SQL> alter database add standby logfilethread 2 group 14 size 50M;

SQL> alter database add standby logfilethread 2 group 15 size 50M;

SQL> selectthread#,group#,bytes/1024/1024 from v$standby_log;

THREAD# GROUP# BYTES/1024/1024

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

1 10 50

1 11 50

1 12 50

2 13 50

2 14 50

2 15 50

创建备库参数文件

SQL> create pfile='/tmp/initorcls.ora'from spfile;

[oracle@rac1 tmp]$ scp initorcls.ora oracle@192.168.10.101:$ORACLE_HOME/dbs

//将参数文件传至备库dbs目录下

生成密码文件

oracle用户下

orapwdfile=orapwocrlpassword=oracle entries=10

scp orapworcl oracle@192.168.10.101:$ORACLE_HOME/dbs

注：密码为sys密码

备库上的操作

修改参数文件（根据实际情况修改）

*.audit_file_dest='/u01/app/oracle/admin/orcls/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/data/oradata/orcl/control01.ctl'

*.db_block_size=8192

*.db_create_file_dest='/data/oradata/orcls/datafile'

*.db_domain=''

*.db_file_name_convert='/data/oradata/orcl/datafile','/data/oradata/orcls/datafile'

*.db_name='orcl'

*.db_unique_name='orcls'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclsXDB)'

*.fal_server='orcl'

*.log_archive_config='DG_CONFIG=(orcl,orcls)'

*.log_archive_dest_1='LOCATION=/data/oradata/orcls/archivelog'valid_for=(all_logfiles,all_roles) db_unique_name=ocrls'

*.log_archive_dest_2='SERVICE=orcl ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=30

*.log_file_name_convert='/data/oradata/orcl/redo','/data/oradata/orcls/redo'

*.open_cursors=300

*.processes=2000

*.memory_target=71800m

*.remote_login_passwordfile='exclusive'

*.standby_file_management='AUTO'

orcls.undo_tablespace='UNDOTBS1'

注：绿色字为主库相关信息

启动到 nomout状态

SQL> startup nomountpfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcls.ora';

更改密码文件名

cd $ORACLE_HOME/dbs

mv orapworcl orapworcls

配置监听文件，保证primary和standby能够互连

备库:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcls)

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

(SID_NAME = orcls)

)

主库和备库是tnsname.ora 配置如下：

orcl =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

orcls =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcls)

)

主备分别验证：

[oracle@rac1 ~]$ sqlplus sys/oracle@orcl assysdba

[oracle@rac1 ~]$ sqlplus sys/oracle@orclsas sysdba

[oracle@rac2 ~]$ sqlplus sys/oracle@orcl assysdba

[oracle@rac2 ~]$ sqlplus sys/oracle@orclsas sysdba

注：必须全部成功连接

备份恢复数据

orcls:/home/oracle@oracle1>rman targetsys/oracle@orcl auxiliary sys/oracle@orcls

Recovery Manager: Release 11.2.0.4.0 -Production on Mon Sep 26 16:50:42 2022

connected to target database: ORCL(DBID=3774196505)

connected to auxiliary database: ORCL (notmounted)

RMAN> duplicate target database forstandby from active database;

开启实时同步

SQL> alter database recover managedstandby database using current logfile disconnect from session;

验证:

1）

SQL> select dest_name,error fromv$archive_dest;//通过查看archive_log_dest_2列是否有error报错，如果有报错，则需要先根据报错内容解决问题

2）

查询主库最大归档序号和备库最大归档序号

select max(sequence#) from v$archived_log;

然后在主库切换日志：alter system switch logfile;

再次查询备库最大归档序号，一致即归档同步成功。

主库：

SQL> select max(sequence#) fromv$archived_log;

MAX(SEQUENCE#)

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

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) fromv$archived_log;

MAX(SEQUENCE#)

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

SQL>

备库：

SQL> select max(sequence#) fromv$archived_log;

MAX(SEQUENCE#)

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

主库验证

SQL>select SEQUENCE#, FIRST_TIME,NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG；

备库验证

SQL>select SEQUENCE#, FIRST_TIME,NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG；