Windows环境Oracle搭建Data Guard

软件安装

主库安装数据库,备库只安装软件

修改db_unique_name参数

1. 主库db_unique_name: primary
2. 备库db_unique_name: standby

-- 查看现在的db_unique_name
show parameter db_unique_name; -- orcl
-- 在线修改db_unique_name(分别修改主库和备库)
1. alter system set db_unique_name='primary' scope=spfile;
2. alter system set db_unique_name='standby' scope=spfile;
-- 重新启动数据库可见

主库配置

开启归档模式与force logging flashback

shudown immediate;
startup mount;
alter database archivelog; -- 开启归档模式
alter database force logging; -- 开启强制日志模式
alter database flashback on;  -- 开启闪回

为备用库增加日志组

set wrap off;
select * from v$logfile order by group#;
alter database add standby logfile group 4 ('D:\ORADATA\ORCL\REDO04.LOG') size 50m;
alter database add standby logfile group 5 ('D:\ORADATA\ORCL\REDO05.LOG') size 50m;
alter database add standby logfile group 6 ('D:\ORADATA\ORCL\REDO06.LOG') size 50m;
alter database add standby logfile group 7 ('D:\ORADATA\ORCL\REDO07.LOG') size 50m;

修改系统参数spfile文件(13个参数)

前面已经修改了db_unique_name

1. alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile;
-- 需要确认目录存在D:\ORADATA\flash\orcl\archivelog\
2. alter system set log_archive_dest_1='location=D:\ORADATA\flash\orcl\archivelog\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile;
3. alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;
4. alter system set log_archive_dest_state_1='enable' scope=spfile;
5. alter system set log_archive_dest_state_2='enable' scope=spfile;
6. alter system set fal_server='standby' scope=spfile;
7. alter system set fal_client='primary' scope=spfile;
8. alter system set archive_lag_target=1800 scope=spfile;
9. alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
10. alter system set standby_file_management=auto scope=spfile;
11. alter system set db_file_name_convert='standby','primary' scope=spfile;
12. alter system set log_file_name_convert='standby','primary' scope=spfile;

创建密码文件

如无需手工创建密码文件

orapwd file=‪D:\app\admin\product\11.2.0\dbhome_1\database\PWDorcl.ora password=123456 entries=10;

配置主库网络监听(备用库同理)

• listener.ora (D:\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora)

# 以下为主库完整的listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\app\admin\product\11.2.0\dbhome_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.122)(PORT = 1521))
  )

ADR_BASE_LISTENER = D:\app\admin

• tnsnames.ora (D:\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora)

# 增加primary 和 standby配置
PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.122)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  
STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.123)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

• 重启监听,可能出现无权限启动监听,需要使用管理员权限

参考文档:https://blog.youkuaiyun.com/breaker892902/article/details/17607479
也可以考虑在c:\windows\system32\cmd.exe文件直接右键以管理员身份执行,再cmd,进入普通用户的cmd
-- 重启监听
lsnrctl stop
lsnrctl start
-- 重启数据库,注意本次启动db_unique_name=standby时会出现报错
shutdown immediate
startup
-- 再测试监听
tnsping primary

手工删除数据库

--关闭监听
lsnrctl stop
-- 关闭数据库,开启受限模式,删除数据库
shutdown immediate;
startup mount;
alter system enable restricted session;
drop database;
-- 删除实例名
oradim -delete -sid orcl

• 可使用下列命令得到需要删除的文件

SET SERVEROUTPUT ON;
DECLARE
TYPE string_arr IS TABLE OF VARCHAR2(1024);
file_list string_arr;
BEGIN
   SELECT t.file_path BULK COLLECT
   INTO file_list
   FROM (SELECT NAME file_path
          FROM V$DATAFILE
         UNION
           SELECT MEMBER file_path
           FROM V$LOGFILE
         UNION
           SELECT NAME file_path
           FROM v$controlfile
         UNION
           SELECT VALUE file_path
            FROM v$parameter
            WHERE NAME LIKE '%dest'
         UNION
           SELECT VALUE file_path
           FROM v$parameter2
           WHERE NAME = 'utl_file_dir'
         UNION
           SELECT '$ORACLE_BASE/admin/$ORACLE_SID' file_path 
           FROM dual
         ) t;

         FOR i IN file_list.FIRST .. file_list.LAST LOOP
                    DBMS_OUTPUT.PUT_LINE('rm -f ' || file_list(i));
         END LOOP;

 END;
 /

复制文件到备库

方案1

• 复制d:\app\admin下的admin,cfgtoollogst,diag和d:\oradata\flash到备库相同路径
• 复制主库的listener.ora和tnsname.ora到备库相同路径(注意修改listener.ora中的ip)
• 在备机建立文件夹存放rmn文件
• 复制主库创建的pfile参数文件initorcl.ora到备库
• 修改相应参数,详见参考文档1,要修改的内容见#备注

方案2

手工创所需目录

mkdir -p D:\app\admin\admin\orcl\adump
mkdir -p D:\app\admin\admin\orcl\dbdump
mkdir -p D:\app\admin\admin\orcl\pfile
mkdir -p D:\app\admin\flash_recovery_area\orcl
mkdir -p D:\ORADATA\orcl
mkdir -p D:\ORADATA\flash\orcl\ARCHIVELOG

配置文件清单

orcl.__db_cache_size=6744440832
orcl.__java_pool_size=33554432
orcl.__large_pool_size=33554432
orcl.__oracle_base='D:\app\admin'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=5502926848
orcl.__sga_target=8220835840
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=1308622848
orcl.__streams_pool_size=0
*.archive_lag_target=1800
*.audit_file_dest='D:\app\admin\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\ORADATA\orcl\control01.ctl','D:\app\admin\flash_recovery_area\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
#db_file_name_convert 修改为'primary','standby'
*.db_file_name_convert='primary','standby'
*.db_name='orcl'
*.db_recovery_file_dest='D:\app\admin\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
#db_unique_name 修改为 'standby'
*.db_unique_name='standby'
*.diagnostic_dest='D:\app\admin'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
#fal_client 修改为 'standby'
*.fal_client='standby'
#fal_server 修改为 'primary'
*.fal_server='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
#db_unique_name 修改为 standby
*.log_archive_dest_1='location=D:\ORADATA\flash\orcl\archivelog\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
#db_unique_name 修改为 primary
*.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
#log_file_name_convert 修改为 'primary','standby'
*.log_file_name_convert='primary','standby'
*.memory_target=13697548288
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

备机新建实例并启动为nomount

-- 如果有即删除该sid
oradim -delete -sid orcl
-- 创建新实例
oradim -new -sid orcl
-- 启动监听
lsnrctl start
-- 启动数据库到nomount模式(使用主机的pfile)
sqlplus /nolog
> conn sys/123456 as sysdba;
> startup nomount pfile='D:\app\admin\product\11.2.0\dbhome_1\database\INITorcl.ORA'

主库使用RMAN备份

rman target /

backup full database format='d:\oradata\tmp\FOR_STANDBY_%u%p%s.RMN' include current controlfile for standby;
-- 可不执行
sql'alter system archive log current';

• 将本次rman备份文件复制到备库相同路径
• 复制数据库

connect auxiliary sys/123456@standby
-- ps: connect有可能失败,要能连到(未加载的数据库)
-- 可能需要修改主库的listener.ora文件,去掉以下几行:
# (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
# (PROGRAM = extproc)
# (ENVS = "EXTPROC_DLLS=ONLY:D:\app\admin\product\11.2.0\dbhome_1\bin\oraclr11.dll")
-- 再执行下面这句复制数据库的命令
-- 方案1 采用
duplicate target database for standby nofilenamecheck;

-- 方案2 采用
duplicate target database for standby from active database nofilenamecheck;

备库启动standby

sqlplus / as sysdba
---------mode 1 ===========
> startup nomount;
> alter database mount standby database;
---------mode 2 ===========
> startup mount;
> alter database open read only;
---------step 2 ===========
> alter database recover managed standby database disconnect from session;
---- 另一种方式
> alter database recover managed standby database using current logfile disconnect from session;

至此data guard 搭建完成

验证是否搭建成功

1. 查看日志祖是否相同,若主备库sequence#一致,表示已经同步

select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc, sequence# desc;

2. 在主库切换活动日志,后查看日志同步情况

alter system switch logfile;

select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc, sequence# desc;

3. 检验主备切换是否正常

select switchover_status from v$database;
-- 结果为 TO STANDBY 表示可以切换

4. 主库切到physical standby

alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
-- startup nomount;
-- alter database mount standby database;
startup mount;
alter database open read only;
alter database recover managed standby database disconnect from session;

5. 备库切到primary

alter database commit to switchover to primary;
shutdown immediate;
startup;
alter system switch logfile;

6. 在新主库和新备库查看日志组在切换后sequence#是否相同

select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc, sequence# desc;

alter system switch logfile;

fail over 步骤

1. 模拟主库宕机 shutdown abort
2. 备库取消recover managed,切换为主库

--- 在备库执行,待测试
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

--- 以下步骤经过测试
shutdown immediate;
startup mount;
alter database open;

备用命令

-- 必须是一个字符串,用双引号括起来
delete archivelog all completed before 'sysdate-7';
delete archivelog all completed before "to_date('20200820163000','yyyymmddhh24miss')";

参考文档

1. Windows环境下配置Oracle11g Data Guard
2. Oracle 修改db_unique_name
3. TNS-01190: 用户无权执行所请求的监听程序命令
4. ORACLE手工删除数据库
5. 11g 新特性：Active Data Guard
6. 详细配置手册 内含fail over步骤