Linux7部署Oracle11g数据库-ADG

本文主要是介绍Linux7部署Oracle11g数据库-ADG，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

主库192.168.2.191 数据库实例名：orcl db_unique_name:primary

从库 192.168.2.192 数据库实例名：orcl db_unique_name:standby

1. 主备库判断DG是否已经安装,默认是安装好的

SQL> select * from v$option where parameter = 'Oracle Data Guard' ;

如果是true表示已经安装可以配置，否则需要安装相应组件。

2. 备份主备库的pfile文件

sqlplus / as sysdba 再输入 conn / as sysdba

create pfile='init_20210910.ora' from spfile;

3. 主备都要开启archive log

先关闭shutdown immediate

SQL> startup mount;

接着把数据库改为归档模式：alter database archivelog

查看结果：archive log list

4. 主备库开启强制日志模式（避免sql使用nologging）

SQL> alter database force logging;

Database altered.

检查是否开启成功：

SQL> select name,log_mode,force_logging from v$database;

NAME LOG_MODE FOR

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

ORCL ARCHIVELOG YES

如果需要在主库添加或者删除数据文件时，这些文件也会在备库添加或删除，使用如下：

sql>alter system set standby_file_management=AUTO ;

默认此参数是manual手工方式

查看结果：

sql>show parameter standby

NAME TYPE VALUE

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

standby_archive_dest string ?/dbs/arch

standby_file_management string AUTO

5. 主、备库创建standby redolog日志组

从库使用standby log files来保存从主库接收到的重做日志。查看主库当前线程与日志组的对应关系及日志组的大小：

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

THREAD# GROUP# BYTES/1024/1024

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

1 1 50

1 2 50

2 3 50

2 4 50

查看当前有哪些日志组及其成员：

SQL> select group#,member from v$logfile;

GROUP# MEMBER

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

3 /u01/app/oracle/oradata/zzbs/redo03.log

2 /u01/app/oracle/oradata/zzbs/redo02.log

1 /u01/app/oracle/oradata/zzbs/redo01.log

公式可以做参考：(每线程的日志组数+1)*最大线程数，假设现在节点是1个，则=(3+1)*1=4 ，这里我们从建立从11到14的standby logfile

/software/app/oracle/oradata/orcl/redo01.log

alter database add standby logfile group 11 '/software/app/oracle/oradata/orcl/standby11.log' size 50M;

alter database add standby logfile group 12 '/software/app/oracle/oradata/orcl/standby12.log' size 50M;

alter database add standby logfile group 13 '/software/app/oracle/oradata/orcl/standby13.log' size 50M;

alter database add standby logfile group 14 '/software/app/oracle/oradata/orcl/standby14.log' size 50M;

查看standby 日志组的信息：

SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER

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

1 ONLINE /u01/app/oracle/oradata/zzbs/redo01.log

2 ONLINE /u01/app/oracle/oradata/zzbs/redo02.log

3 ONLINE /u01/app/oracle/oradata/zzbs/redo03.log

11 STANDBY /u01/app/oracle/oradata/standbylog/standby11.log

12 STANDBY /u01/app/oracle/oradata/standbylog/standby12.log

13 STANDBY /u01/app/oracle/oradata/standbylog/standby13.log

14 STANDBY /u01/app/oracle/oradata/standbylog/standby14.log

SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;

GROUP# SEQUENCE# STATUS BYTES/1024/1024

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

11 0 UNASSIGNED 50

12 0 UNASSIGNED 50

13 0 UNASSIGNED 50

14 0 UNASSIGNED 50

6. 主备库修改db_name和db_unique_name

主从库db_name必须一致，db_unique_name不一致,主库为zzbspri，从库为zzbsstd

查看：sql>show parameter name主备库的db_name都一致不需要设置

主库db_unique_name设置：

sql>alter system set db_unique_name=primary scope=spfile;

备库db_unique_name设置：

sql>alter system set db_unique_name=standby scope=spfile;

7. 主备库设置log_archive_config，Data Guard 配置里的另外一个库的名字，同步方式

主备一样设置设置，该参数定义了DG配置中可用的DB_UNIQUE_NAME参数值列表

alter system set log_archive_config= 'DG_CONFIG=(primary,standby)';

主库归档路径，log_archive_dest_1 是写入本地路径，log_archive_dest_2是写入对端的路径，service对端的服务名称：

alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=spfile;

alter system set log_archive_dest_2=' SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=standby' scope=spfile;

备库归档路径：

alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=spfile;

alter system set log_archive_dest_2=' SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=primary' scope=spfile;

检查：

show parameter log_archive_dest

select * from v$archive_dest_status

8. 主备库配置FAL_SERVER

这个参数指定当日志传输出现问题时，备库到哪里去找缺少的归档日志。它用在备库接收到的重做日志间有缺口的时候。你是主库,就填写:fal_server=从库,从库上就反过来:fal_server=主库

主库修改：

alter system set fal_server='standby';

SQL> SHOW PARAMETER FAL_SERVER

NAME TYPE VALUE

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

fal_server string zzbsstd

备库修改:

alter system set fal_server='primary';

SQL> SHOW PARAMETER FAL_SERVER

NAME TYPE VALUE

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

fal_server string zzbspri

9. 主库密码文件复制到备库

主库执行：

scp $ORACLE_HOME/dbs/orapworcl oracle@192.168.2.192:/software/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl

10. 主备库配置静态监听

主库配置listener.ora

cd /software/app/oracle/product/11.2.0/dbhome_1/network/admin

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = primary)

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

(SID_NAME = orcl)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

备库配置listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = standby)

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

(SID_NAME = orcl)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

主备配置tnsnames.ora配置：

standby =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)

)

primary =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = primary)

)

注意：监听配好后最好重启一下监听，否则会在rman步骤报错。

11. 主备重启

shutdown immediate

startup

12. 使用duplicate搭建备库

备库需启动到nomount状态；启动后同步。

在主库同步数据到备库

[oracle@rac1 ~]$rman target sys/LW_SYS_2017@primary auxiliary 'sys/LW_SYS_2017'@standby

RMAN> duplicate target database for standby from active database nofilenamecheck;

13. 在OPEN状态下进行日志应用

备库开启数据库：

alter database open;

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

查看archive_dest_status是否正确：

select * from v$archive_dest_status;

14. 启动关闭操作

启动顺序：

1、启动备库： startup

2、启动备库实时日志应用：

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

3、启动主库：startup 　

关闭顺序：

1、关闭主库：shutdown immediate

2、暂停备库实时应用:alter database recover managed standby database cancel;

3、关闭备库:shutdown immediate

15. 查看状态

查看数据库的保护模式：

primary 端查看，我们可以看到数据库的保护模式为最大性能

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

DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE

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

PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE

#standby 端查看，也是一样的。

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

DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE

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

PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY

这篇关于Linux7部署Oracle11g数据库-ADG的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

Linux教程