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
