Oracle Dataguard（主库为 Oracle 11g 单节点）配置详解（2）：配置主数据库

Oracle Dataguard（主库为 Oracle 11g 单节点）配置详解（2）：配置主数据库

一、配置环境

操作系统为 CentOS7，Oracle 版本为 11g，主库为单机版 Oracle。详细情况如下表所示：

	主库	备库
DB 类型	单机	单机
操作系统	Centos7.3	Centos7.3
Hostname	ora	oradg
IP 地址	192.168.1.2	192.168.1.3
ORACLE_BASE	/usr/local/oracle	/usr/local/oracle
DB_NAME	hisdb	hisdbdg
ORACLE_SID	ora	oradg
DB_Unique_Name	hisdb	hisdbdg
Instance_Name	ora	oradg
service_names	hisdb	hisdbdg
TNS_Name	hisdb	hisdbdg
闪回区	开启	开启
归档	开启	开启

二、安装 Oracle 软件并创建数据库

1、在主库上安装数据库软件，创建数据库，创建监听（安装过程省略）

2、在备库上安装数据库软件，创建监听，但不创建实例

主库的数据库参数如下：

SQL> show parameter nameNAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name		     string
db_file_name_convert		     string
db_name 			     string	 hisdb
db_unique_name			     string	 hisdb
global_names			     boolean	 FALSE
instance_name			     string	 ora
lock_name_space 		     string
log_file_name_convert		     string
processor_group_name		     string
service_names			     string	 hisdb

三、配置主数据库

1、启动 FORCE LOGGING

命令如下：

SQL> select open_mode,log_mode,database_role from v$database;OPEN_MODE	     LOG_MODE	  DATABASE_ROLE
-------------------- ------------ ----------------
READ WRITE	     NOARCHIVELOG PRIMARYSQL> select force_logging from v$database;FOR
---
NOSQL> alter database force logging;Database altered.SQL> select force_logging from v$database;FOR
---
YES

2、启动归档模式

-- 查询归档模是否开启
SQL> archive log list;
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Current log sequence	       7-- 关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.-- 开启数据库至 mount 状态
SQL> startup mount;
ORACLE instance started.Total System Global Area 1068937216 bytes
Fixed Size		    2260088 bytes
Variable Size		  331350920 bytes
Database Buffers	  729808896 bytes
Redo Buffers		    5517312 bytes
Database mounted.-- 修改数据库为归档模式
SQL> alter database archivelog;Database altered.-- 开启数据库
SQL> alter database open;Database altered.-- 查看归档日志是否开启
SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence	       7SQL> show  parameter recoveryNAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /usr/local/oracle/fast_recovery_area
db_recovery_file_dest_size	     big integer 4182M
recovery_parallelism		     integer	 0

3、开启闪回

 -- 查看闪回未开启
SQL> select flashback_on from v$database;FLASHBACK_ON
------------------
NO-- 查看 db_recovery_file_dest
SQL> show parameter recoveryNAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /usr/local/oracle/fast_recovery_area
db_recovery_file_dest_size	     big integer 4182M
recovery_parallelism		     integer	 0-- 指定闪回区大小，指定闪回目录路径（顺序必须先设置闪回区大小，才能指定闪回目录，否则报错）
alter system set db_recovery_file_dest_size=4182m;
-- 先创建目录，再指定该闪回区域目录
alter system set db_recovery_file_dest='/usr/local/oracle/fast_recovery_area';-- 开启闪回
SQL> alter database flashback on;Database altered.-- 查看闪回未开启
SQL> select flashback_on from v$database;FLASHBACK_ON
------------------
YES

4、为主库添加 standby redo logfile

说明：

（1）为主库添加standby redo log后，备库自动同步，所以备库不用再创建standby redo log 。

（2）Dataguard 在最大保护和最高可用性模式下，Standby 数据库必须配置standby redo log。

（3）确保 Standby redo log的大小与主库 online redo log的大小一致。

（4）如果主库为单实例数据库： Standby redo log组数 = 主库日志总数 + 1。

（5）如果主库是RAC数据库：Standby redo log组数 = （每线程的日志数 + 1）* 最大线程数。

查看主数据库的日志信息：

-- 查看日志存放位置
SQL> select member from v$logfile;MEMBER
--------------------------------------------------------------------------------
/usr/local/oradata/hisdb/redo03.log
/usr/local/oradata/hisdb/redo02.log
/usr/local/oradata/hisdb/redo01.log-- 查看日志数量
SQL> select count(group#),thread# from v$log group by thread#;COUNT(GROUP#)	 THREAD#
------------- ----------3	       1-- 查看日志文件大小
SQL> select group#,bytes/1024/1024 from v$log;GROUP# BYTES/1024/1024
---------- ---------------1		502		503		50

添加 standby logfile：

 -- 添加 4 个standby logfile，文件大小必须为 50M
/*
alter database add standby logfile '/usr/local/oradata/hisdb/standby01.log' size 50M;
alter database add standby logfile '/usr/local/oradata/hisdb/standby02.log' size 50M;
alter database add standby logfile '/usr/local/oradata/hisdb/standby03.log' size 50M;
alter database add standby logfile '/usr/local/oradata/hisdb/standby04.log' size 50M;
*/SQL> alter database add standby logfile '/usr/local/oradata/hisdb/standby01.log' size 50M;Database altered.SQL> alter database add standby logfile '/usr/local/oradata/hisdb/standby02.log' size 50M;Database altered.SQL> alter database add standby logfile '/usr/local/oradata/hisdb/standby03.log' size 50M;Database altered.SQL> alter database add standby logfile '/usr/local/oradata/hisdb/standby04.log' size 50M;Database altered.-- 查看 standby_log 信息
SQL> select group#,bytes/1024/1024 from v$standby_log;GROUP# BYTES/1024/1024
---------- ---------------4		505		506		507		50-- 查看所有的日志信息
SQL> select group#,status,type,member from v$logfile;GROUP# STATUS  TYPE            MEMBER
-----------------------------------------------------------------------------------------3	   ONLINE            /usr/local/oradata/hisdb/redo03.log2	   ONLINE           /usr/local/oradata/hisdb/redo02.log1	   ONLINE           /usr/local/oradata/hisdb/redo01.log4	   STANDBY          /usr/local/oradata/hisdb/standby01.log5	   STANDBY          /usr/local/oradata/hisdb/standby02.log6	   STANDBY          /usr/local/oradata/hisdb/standby03.log7	   STANDBY          /usr/local/oradata/hisdb/standby04.log7 rows selected.

5、为主数据库配置监听

（1）查看监听文件

[oracle@ora ~]$ cd $ORACLE_HOME/network/admin
[oracle@ora admin]$ ll
总用量 16
-rw-r--r-- 1 oracle oinstall 368 12月 30 21:28 listener.ora
drwxr-xr-x 2 oracle oinstall  64 12月 30 21:15 samples
-rw-r--r-- 1 oracle oinstall 381 12月 17 2012 shrept.lst
-rw-r--r-- 1 oracle oinstall 223 12月 30 21:28 sqlnet.ora
-rw-r----- 1 oracle oinstall 322 12月 30 21:46 tnsnames.ora

（2）配置监听

[oracle@ora admin]$ vi listener.ora# listener.ora Network Configuration File: /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))(ADDRESS = (PROTOCOL = TCP)(HOST = ora)(PORT = 1521))))SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = hisdb)(ORACLE_HOME = /usr/local/oracle/product/11.2.0/db_1)(SID_NAME = ora))(SID_DESC =(GLOBAL_DBNAME = hisdbdg)(ORACLE_HOME = /usr/local/oracle/product/11.2.0/db_1)(SID_NAME = oradg)))ADR_BASE_LISTENER = /usr/local/oracle[oracle@ora admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /usr/local/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.HISDB =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = ora)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = hisdb)))HISDBDG =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = oradg)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = hisdbdg)))

（3）重启监听，查看监听状态

[oracle@ora admin]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-DEC-2024 18:52:02Copyright (c) 1991, 2013, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                31-DEC-2024 18:51:55
Uptime                    0 days 0 hr. 0 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /usr/local/oracle/diag/tnslsnr/ora/listener/alert/log.xml
Listening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora)(PORT=1521)))
Services Summary...
Service "hisdb" has 1 instance(s).Instance "ora", status UNKNOWN, has 1 handler(s) for this service...
Service "hisdbdg" has 1 instance(s).Instance "oradg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

（4）使用tnsping测试主备连通性

tnsping hisdbtnsping hisdbdg

6、修改主数据库的配置文件

（1）生成参数文件

SQL> create pfile from spfile;File created.

（2）查看数据库信息

-- 主数据库
SQL> show parameter db_unique_name;NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 hisdb-- 备份数据库
SQL> show parameter db_unique_name;

（3）修改参数文件

# 在参数文件中添加以下内容：
*.db_unique_name='hisdb'                           # 主数据库的唯一名，默认和数据库名一致
*.log_archive_config='dg_config=(hisdb,hisdbdg)'   # 主库和备库在tnsname.ora中的连接名*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=hisdb'                             # 主数据库的唯一名
*.log_archive_dest_2='service=hisdbdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=hisdbdg'                           # 备用数据库的唯一名
*.db_file_name_convert='/usr/local/oradata/hisdb/','/usr/local/oradata/hisdb/'
# db_file_name_convert参数的设置说明：当主备库目录结构不一致的时候，主数据库和备用数据库的数据文件
# 转换目录映射
# 格式： 
# 主库配置格式： *.db_file_name_convert= 备用数据库数据文件目录，主数据库数据文件目录
# 备库配置格式： *.db_file_name_convert= 主数据库数据文件目录，备用数据库数据文件目录
*.log_file_name_convert='/usr/local/oradata/hisdb/','/usr/local/oradata/hisdb/'
# log_file_name_convert：指明主数据库和备用数据库的log文件转换目录映射。
# 主库配置格式： *. log_file_name_convert=备用数据库log目录，主数据库log目录
# 备库配置格式： *. log_file_name_convert=主数据库log目录，备用数据库log目录
# 如果主备库的数据文件路径以及联机日志路径不一致，则需要设置db_file_name_convert和
# log_file_name_convert的参数，当然如果多对一同样要设置。
# 但是如果主备库数据文件以及日志文件路径一直则不需要设置该参数
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
# fal_client用于发送日志，fal_server用于接受日志。
# 也即无论是主库或备库，fal_server=对方，fal_client=自己
# 主库设置如下
*.fal_server='hisdbdg'
*.fal_client='hisdb'
# 备库设置如下
# *.fal_server='hisdb'
# *.fal_client='hisdbdg'

修改以后的参数文件内容如下：

[oracle@ora admin]$ cd $ORACLE_HOME/dbs
[oracle@ora dbs]$ ll
总用量 24
-rw-rw---- 1 oracle oinstall 1544 1月   1 17:31 hc_ora.dat
-rw-r--r-- 1 oracle oinstall 2851 5月  15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 1384 1月   1 17:41 initora.ora
-rw-r----- 1 oracle oinstall   24 12月 30 21:46 lkHISDB
-rw-r----- 1 oracle oinstall 1536 12月 30 21:46 orapwora
-rw-r----- 1 oracle oinstall 3584 1月   1 17:32 spfileora.ora[oracle@ora admin]$ vi initora.oraora.__db_cache_size=792723456
ora.__java_pool_size=4194304
ora.__large_pool_size=8388608
ora.__oracle_base='/usr/local/oracle'#ORACLE_BASE set from environment
ora.__pga_aggregate_target=499122176
ora.__sga_target=1073741824
ora.__shared_io_pool_size=0
ora.__shared_pool_size=255852544
ora.__streams_pool_size=0
*.audit_file_dest='/usr/local/oracle/admin/hisdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/usr/local/oradata/hisdb/control01.ctl','/usr/local/oracle/fast_recovery_area/hisdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='hisdb'
*.db_recovery_file_dest_size=4385144832
*.db_recovery_file_dest='/usr/local/oracle/fast_recovery_area'
*.diagnostic_dest='/usr/local/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraXDB)'
*.open_cursors=300
*.pga_aggregate_target=499122176
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1073741824
*.undo_tablespace='UNDOTBS1'# 新增如下内容
*.db_unique_name='hisdb'
*.log_archive_config='dg_config=(hisdb,hisdbdg)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=hisdb'
*.log_archive_dest_2='service=hisdbdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=hisdbdg'
*.db_file_name_convert='/usr/local/oradata/hisdbdg/','/usr/local/oradata/hisdb/'
*.log_file_name_convert='/usr/local/oradata/hisdbdg/','/usr/local/oradata/hisdb/'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='hisdbdg'
*.fal_client='hisdb'

停库，然后使用修改后的参数文件启动数据库：

-- 停库
SQL> shutdow immediate
Database closed.
Database dismounted.
ORACLE instance shut down.-- 生成spfile参数文件
SQL> create spfile from pfile;File created.
-- 启动数据库
SQL> startup
ORACLE instance started.Total System Global Area 1068937216 bytes
Fixed Size		    2260088 bytes
Variable Size		  331350920 bytes
Database Buffers	  729808896 bytes
Redo Buffers		    5517312 bytes
Database mounted.
Database opened.

7、把主数据库的口令文件复制到备库

[oracle@ora admin]$ cd $ORACLE_HOME/dbs
[oracle@ora dbs]$ ls
hc_ora.dat  init.ora  lkHISDB  orapwora  spfileora.ora[oracle@ora dbs]$ scp orapwora oracle@oradg:$ORACLE_HOME/dbs
oracle@oradg's password: 
orapwora                                                                                                                                  100% 1536     1.5KB/s   00:00    
[oracle@ora dbs]$