OracleDataGuard-使用Duplicate

使用 DUPLICATE 命令可以在 Oracle 数据库中搭建 Data Guard。Data Guard 是 Oracle 数据库提供的一种可靠性解决方案,用于实现数据库的高可用性、灾难恢复和数据保护。通过搭建 Data Guard,可以在主库和备库之间实现数据复制,并确保备库始终与主库保持同步,从而提供了对主库故障的快速故障转移和数据恢复能力。

IP主机名全局数据库名实例名
192.168.1.121primary.example.comorclorcl
192.168.1.122standby.example.comorclorcl

操作系统:Centos7 64Bit

oracle 版本:11.2.0.4

提示
不要忘记添加 hosts

将 Oracle 数据库实例启动到挂载状态

1
2
SQL> shutdown immediate;
SQL> startup mount;

更改数据库为归档模式

1
SQL> alter database archivelog;

查询归档日志文件路径

1
SQL> archive log list;

设置归档日志文件路径

1
SQL> host mkdir -p /data/oracle/archivelog
1
SQL> alter system set log_archive_dest_1='location=/data/oracle/archivelog/' scope=both;

打开数据库

1
SQL> alter database open;
1
SQL> alter database force logging;
1
SQL> alter system set standby_file_management=auto scope=both;
1
2
3
4
SQL> alter database add standby logfile group 11 '/data/oracle/oradata/orcl/standby11.log' size 50M;
SQL> alter database add standby logfile group 12 '/data/oracle/oradata/orcl/standby12.log' size 50M;
SQL> alter database add standby logfile group 13 '/data/oracle/oradata/orcl/standby13.log' size 50M;
SQL> alter database add standby logfile group 14 '/data/oracle/oradata/orcl/standby14.log' size 50M;
重要
以上配置主库备库都要配置
1
2
3
4
# 主库
SQL> alter system set db_unique_name=PRIMARY_DB scope=spfile;
# 备库
SQL> alter system set db_unique_name=STANDBY_DB scope=spfile;
1
SQL> alter system set FAL_SERVER='STANDBY' SCOPE=BOTH;

STANDBY 是在 tnsnames.ora 中为备库定义的连接标识符名称

1
2
SQL> alter system set log_archive_config = 'dg_config=(PRIMARY_DB,STANDBY_DB)';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY_DB';

log_archive_config 中PRIMARY_DBSTANDBY_DB是配置的 db_unique_name

1
SQL> alter system set FAL_SERVER='PRIMARY' SCOPE=BOTH;
1
2
SQL> alter system set log_archive_config = 'dg_config=(PRIMARY_DB,STANDBY_DB)';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=PRIMARY LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY_DB';

修改listener.ora

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary.example.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /data/oracle

修改tnsnames.ora

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

重载监听

1
lsnrctl reload

修改listener.ora

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby.example.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /data/oracle

tnsnames.ora和主库保持一致即可

重载监听

1
lsnrctl reload

关闭备库

1
SQL> shutdown immediate;

将密码文件复制到备库

1
scp $ORACLE_HOME/dbs/orapworcl oracle@standby.example.com:$ORACLE_HOME/dbs/
1
SQL> startup nomount;
1
rman target sys/oracle@primary auxiliary sys/oracle@standby nocatalog
  • target sys/oracle@primary:这指定了 RMAN 的目标数据库,也就是要备份的数据库。在这里,目标数据库是主数据库(primary),用户名是 sys,密码是 oracle,连接字符串是 @primary。
  • auxiliary sys/oracle@standby:这指定了 RMAN 的辅助数据库,也就是备份数据将要写入的数据库。在这里,辅助数据库是备用数据库(standby),用户名是 sys,密码是 oracle,连接字符串是 @standby。
  • nocatalog:这告诉 RMAN 不使用 RMAN 目录数据库。在这种情况下,RMAN 会直接与目标数据库和辅助数据库通信,而不是通过 RMAN 目录数据库来管理备份信息。

在执行 duplicate 的时候,如果源库和目标库目录相同,那么在 duplicate 时,需要加上 nofilenamecheck

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

看到 Finished 表示恢复完成

提示
duplicate 后数据会处于启动状态
1
2
3
4
5
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
sqL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect from session;

至此 DataGuard 搭建完成

相关内容