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

 

 

上一篇:Hadoop集群 手动主备切换 (Active - Standby)


下一篇:MySQL的keepalived高可用监控脚本