Oracle 11g DataGuard 搭建教程指南

# 修改主机名
hostname primary
sed -i 's/hostname=.*/hostname=primary/' /etc/sysconfig/network
# 重启网络服务
service network restart
# 配置hosts解析，添加主备库映射
cat >> /etc/hosts <<eof
# dg主备库解析
192.168.56.101  primary
192.168.56.102  standby
eof
# 验证网络连通性
ping -c 3 primary
# 关闭networkmanager服务
service networkmanager stop
chkconfig networkmanager off

# 临时关闭iptables防火墙
service iptables stop
# 永久关闭防火墙开机自启
chkconfig iptables off

# 临时关闭selinux
setenforce 0
# 永久关闭selinux
sed -i 's/selinux=enforcing/selinux=disabled/' /etc/selinux/config

# 验证selinux状态，预期输出disabled
getenforce

# 创建用户组
groupadd -g 1000 oinstall
groupadd -g 1031 dba
groupadd -g 1032 oper

# 创建oracle用户，加入用户组
useradd -u 1101 -g oinstall -g dba,oper oracle

# 设置oracle用户密码（统一为oracle）
echo "oracle" | passwd --stdin oracle

# 验证用户创建成功
id oracle

# 创建基础目录、软件目录、通用数据目录、归档目录
mkdir -p /u01/app/oracle/product/11.2.0.4/db_1
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/arch
mkdir -p /u01/app/oracle/admin/adump
mkdir -p /u01/app/orainventory
mkdir -p /soft

# 递归授权目录给oracle用户
chown -r oracle:oinstall /u01
chown -r oracle:oinstall /soft

# 统一权限为775（oracle官方标准）
chmod -r 775 /u01
chmod -r 775 /soft

# 编辑内核参数配置文件
cat >> /etc/sysctl.conf <<eof

# oracle 11g 核心内核参数（3g内存）
kernel.shmmax = 1610612736  # 物理内存的50%，1.5gb，适配amm自动内存管理
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
# dg稳定性增强参数
kernel.panic_on_oops = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_syncookies = 1
eof

# 注销掉原先的kernel配置
vi /etc/sysctl.conf

# 使内核参数立即生效
sysctl -p

# 编辑/etc/fstab配置文件
vi /etc/fstab

# 找到tmpfs对应的行，修改为以下内容（无则直接在末尾追加）
tmpfs                   /dev/shm                tmpfs   defaults,size=2000m        0 0

# 重新挂载生效，无需重启系统
mount -o remount /dev/shm

# 验证配置生效，预期输出大小为2g左右
df -h /dev/shm

# 修改nproc配置，避免覆盖limits.conf
vi /etc/security/limits.d/90-nproc.conf
# 将* soft nproc 1024改为
*          soft    nproc     16384

# 添加oracle用户官方要求的资源限制
cat >> /etc/security/limits.conf <<eof
# oracle用户资源限制（官方强制要求）
oracle  soft    nproc   2047
oracle  hard    nproc   16384
oracle  soft    nofile  1024
oracle  hard    nofile  65536
oracle  soft    stack   10240
oracle  hard    stack   32768
eof

# 配置login文件，启用资源限制
cat >> /etc/pam.d/login <<eof
session required pam_limits.so
eof

# 配置profile全局用户资源限制
cat >> /etc/profile <<eof
if [ \$user = "oracle" ]; then
    if [ \$shell = "/bin/ksh" ]; then
        ulimit -p 16384
        ulimit -n 65536
    else
        ulimit -u 16384 -n 65536
    fi
    umask 022
fi
eof

# 使配置生效
source /etc/profile

# 挂载系统光盘
mkdir /mnt/cdrom
mount /dev/cdrom /mnt/cdrom

# 配置本地yum源
cat > /etc/yum.repos.d/local.repo <<eof
[local]
name=local rhel 6.10 dvd
baseurl=file:///mnt/cdrom
enabled=1
gpgcheck=0
eof

yum clean all
yum repolist

# 批量安装依赖
yum install -y binutils compat-libcap1 compat-libstdc++-33 \
elfutils-libelf-devel gcc gcc-c++ glibc glibc-devel ksh libaio \
libaio-devel libgcc libstdc++ libstdc++-devel libxext libxtst \
libx11 libxau libxi make sysstat unixodbc unixodbc-devel libaio.i686 libaio-devel.i686

# 安装rlwrap工具（需要先传上去）
yum localinstall -y /soft/rlwrap-0.42-1.el6.x86_64.rpm

# 验证依赖包安装完整性，无输出则表示全部安装成功
rpm -q binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libxi libxtst make sysstat unixodbc unixodbc-devel | grep "not installed"

# 切换到oracle用户
su - oracle

# 编辑环境变量文件
cat >> ~/.bash_profile <<'eof'

# shell基础配置
ps1="[\u@\h:\w]\$ "
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"
export ps1

# oracle核心环境变量
export lang=en_us
export tmp=/tmp
export tmpdir=$tmp
export oracle_hostname=primary
export oracle_unqname=primdb
export oracle_base=/u01/app/oracle
export oracle_home=$oracle_base/product/11.2.0.4/db_1
export tns_admin=$oracle_home/network/admin
export oracle_sid=primdb
export oracle_term=xterm

# 字符集
export nls_date_format="yyyy-mm-dd hh24:mi:ss"
export nls_lang=american_america.zhs16gbk

# 路径
export path=$oracle_home/bin:.:$path:$home/bin
export ld_library_path=$oracle_home/lib:/lib:/usr/lib
umask 022
eof

# 使环境变量生效
source ~/.bash_profile

# 验证环境变量
echo $oracle_home
echo $path

su - oracle
cd /soft
# 解压两个分卷压缩包（自动合并生成 database 文件夹）
unzip -o p13390677_112040_linux-x86-64_1of7.zip
unzip -o p13390677_112040_linux-x86-64_2of7.zip

# 删除压缩包
rm p13390677_112040_linux-x86-64_1of7.zip
rm p13390677_112040_linux-x86-64_2of7.zip

# 重启
exit
reboot

su - oracle
cd /soft/database
export display=192.168.56.1:0.0
./runinstaller

# 清除所有 ifcfg 文件中的 hwaddr 行
sed -i '/^hwaddr=/d' /etc/sysconfig/network-scripts/ifcfg-eth[0-1]

# 清理网卡规则，避免克隆后网卡无法启动
rm -rf /etc/udev/rules.d/70-persistent-net.rules

# 关闭模板虚拟机
shutdown -h now

# virtualbox中移除模板机挂载的iso镜像，避免克隆后重复挂载

# 修改主机名
hostname standby
sed -i 's/hostname=.*/hostname=standby/' /etc/sysconfig/network

# 配置网卡ip，编辑host-only网卡配置文件（ifcfg-eth0）
sed -i 's/^ipaddr=.*/ipaddr=192.168.56.102/' /etc/sysconfig/network-scripts/ifcfg-eth0

# 重启网络服务
service network restart

# 检查hosts解析，和主库完全一致
cat /etc/hosts

# 验证主备网络互通
ping -c 3 primary
ping -c 3 standby

su - oracle
# 编辑.bash_profile，修改以下核心参数
sed -i 's/^export oracle_hostname=.*/export oracle_hostname=standby/' ~/.bash_profile
sed -i 's/^export oracle_unqname=.*/export oracle_unqname=standb/' ~/.bash_profile
sed -i 's/^export oracle_sid=.*/export oracle_sid=standb/' ~/.bash_profile

# 使配置生效
source ~/.bash_profile

# 验证
echo $oracle_sid

# 创建审计目录、数据目录、归档目录
mkdir -p /u01/app/oracle/admin/standb/adump
mkdir -p /u01/app/oracle/oradata/standb
mkdir -p /u01/app/oracle/arch

# 递归授权整个oracle目录
chown -r oracle:oinstall /u01/app/oracle
chmod -r 775 /u01/app/oracle

# 给 rc.local 加执行权限
chmod +x /etc/rc.d/rc.local

vi /etc/rc.d/rc.local
# 在文件最后一行添加：
# 备库 oracle 开机自启（dg专用）
su - oracle -c "/u01/app/oracle/product/11.2.0.4/db_1/bin/lsnrctl start"
su - oracle -c "/u01/app/oracle/product/11.2.0.4/db_1/bin/sqlplus / as sysdba <<eof
startup mount;
alter database recover managed standby database disconnect from session;
exit;
eof"

mkdir -p /u01/app/oracle/oradata/primdb
mkdir -p /u01/app/oracle/admin/primdb/adump
mkdir -p /u01/app/oracle/admin/primdb/dpdump
chown -r oracle:oinstall /u01/app/oracle/oradata/primdb
chown -r oracle:oinstall /u01/app/oracle/admin
chmod -r 775 /u01/app/oracle/oradata/primdb
chmod -r 775 /u01/app/oracle/admin

# 给 rc.local 加执行权限
chmod +x /etc/rc.d/rc.local

vi /etc/rc.d/rc.local
# 在文件最后一行添加：
# 主库 oracle 开机自启
su - oracle -c "/u01/app/oracle/product/11.2.0.4/db_1/bin/lsnrctl start"
su - oracle -c "/u01/app/oracle/product/11.2.0.4/db_1/bin/sqlplus / as sysdba <<eof
startup;
exit;
eof"

su - oracle
cd $oracle_home
export display=192.168.56.1:0.0
netca

# 启动监听
lsnrctl start

# 验证监听状态
lsnrctl status

su - oracle
cd $oracle_home
export display=192.168.56.1:0.0
dbca

sqlplus / as sysdba

-- 查询当前强制日志状态
select force_logging from v$database;

-- 开启强制日志模式
alter database force logging;

-- 再次验证，预期输出yes
select force_logging from v$database;
exit

sqlplus / as sysdba

-- 查询归档模式
archive log list;
select log_mode from v$database;
-- 预期输出：archive mode
exit

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

sqlplus / as sysdba

-- 1. 设置db_unique_name，主库唯一标识
alter system set db_unique_name='primdb' scope=spfile sid='*';

-- 2. 配置dg集群节点列表，必须包含主备库的db_unique_name
alter system set log_archive_config='dg_config=(primdb,standb)' scope=spfile sid='*';

-- 3. 配置本地归档路径
alter system set log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=primdb' scope=both sid='*';

-- 4. 配置备库远程归档路径
alter system set log_archive_dest_2='service=standby lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=standb' scope=both sid='*';

-- 5. 启用归档路径
alter system set log_archive_dest_state_1=enable scope=both sid='*';
alter system set log_archive_dest_state_2=enable scope=both sid='*';

-- 6. 配置备库数据文件路径转换（主库：备库路径在前，主库路径在后）
alter system set db_file_name_convert='/u01/app/oracle/oradata/standb','/u01/app/oracle/oradata/primdb' scope=spfile sid='*';

-- 7. 配置备库日志文件路径转换
alter system set log_file_name_convert='/u01/app/oracle/oradata/standb','/u01/app/oracle/oradata/primdb' scope=spfile sid='*';

-- 8. 配置备库文件自动管理
alter system set standby_file_management=auto scope=spfile sid='*';

-- 9. 配置fal客户端和服务端
alter system set fal_client='primary' scope=both sid='*';
alter system set fal_server='standby' scope=both sid='*';

-- 10. 配置远程登录密码文件
alter system set remote_login_passwordfile='exclusive' scope=spfile sid='*';

-- 重启数据库使所有spfile参数生效
shutdown immediate;
startup;

-- 验证参数配置是否生效
show parameter db_unique_name;
show parameter log_archive_config;
exit

# 编辑监听配置文件
vi $oracle_home/network/admin/listener.ora

listener =
  (description_list =
    (description =
      (address = (protocol = ipc)(key = extproc1521))
      (address = (protocol = tcp)(host = primary)(port = 1521))
    )
  )

# 静态监听配置
sid_list_listener =
  (sid_list =
    (sid_desc =
      (global_dbname = primdb)
      (sid_name = primdb)
      (oracle_home = /u01/app/oracle/product/11.2.0.4/db_1)
    )
  )

adr_base_listener = /u01/app/oracle

lsnrctl stop
lsnrctl start

# 验证监听状态，预期看到primdb服务ready/unknown
lsnrctl status

# 编辑tns配置文件
vi $oracle_home/network/admin/tnsnames.ora

# 主库本地别名
primary =
  (description =
    (address_list =
      (address = (protocol = tcp)(host = primary)(port = 1521))
    )
    (connect_data =
      (service_name = primdb)
    )
  )

# 备库远程别名
standby =
  (description =
    (address_list =
      (address = (protocol = tcp)(host = standby)(port = 1521))
    )
    (connect_data =
      (service_name = primdb)
    )
  )

# 主库执行：scp拷贝密码文件到备库
su - oracle
cd $oracle_home/dbs
scp orapwprimdb oracle@standby:$oracle_home/dbs/
# 备库执行：重命名密码文件为备库的oracle_sid对应名称
su - oracle
cd $oracle_home/dbs
mv orapwprimdb orapwstandb
# 验证密码文件权限，必须是640，属主oracle:oinstall
ls -l orapwstandb

# 1. 主库执行：生成pfile参数文件
sqlplus / as sysdba
sql> create pfile from spfile;
exit

# 2. 主库执行：拷贝pfile文件到备库
cd $oracle_home/dbs
scp initprimdb.ora oracle@standby:$oracle_home/dbs/

# 3. 备库执行：重命名pfile文件为备库oracle_sid对应名称
su - oracle
cd $oracle_home/dbs
mv initprimdb.ora initstandb.ora

# 4. 备库执行：编辑pfile文件，修改核心参数
cat > initstandb.ora <<'eof'
memory_target=1216m
memory_max_target=1216m
compatible=11.2.0.4.0
*.audit_file_dest='/u01/app/oracle/admin/standb/adump'
*.db_name='primdb'
*.db_unique_name='standb'
*.db_file_name_convert='/u01/app/oracle/oradata/primdb','/u01/app/oracle/oradata/standb'
*.log_file_name_convert='/u01/app/oracle/oradata/primdb','/u01/app/oracle/oradata/standb'
*.control_files='/u01/app/oracle/oradata/standb/control01.ctl','/u01/app/oracle/oradata/standb/control02.ctl'
*.log_archive_config='dg_config=(primdb,standb)'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=standb'
*.log_archive_dest_2='service=primary lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=primdb'
*.fal_client='standb'
*.fal_server='primary'
*.standby_file_management='auto'
*.remote_login_passwordfile='exclusive'
*.processes=150
eof

# 5. 备库执行：通过pfile创建spfile，启动数据库到nomount状态
sqlplus / as sysdba
-- 通过pfile创建spfile
create spfile from pfile;
-- 启动数据库到nomount状态
startup nomount;
-- 验证数据库状态，预期输出started
select status from v$instance;
exit

# 编辑监听配置文件
vi $oracle_home/network/admin/listener.ora

listener =
  (description_list =
    (description =
      (address = (protocol = ipc)(key = extproc1521))
      (address = (protocol = tcp)(host = standby)(port = 1521))
    )
  )

# 静态监听配置
sid_list_listener =
  (sid_list =
    (sid_desc =
      (global_dbname = primdb)
      (sid_name = standb)
      (oracle_home = /u01/app/oracle/product/11.2.0.4/db_1)
    )
  )

adr_base_listener = /u01/app/oracle

lsnrctl stop
lsnrctl start

# 验证监听状态
lsnrctl status

# 主库执行：拷贝tnsnames.ora到备库
scp $oracle_home/network/admin/tnsnames.ora oracle@standby:$oracle_home/network/admin/
# 备库验证文件内容
cat $oracle_home/network/admin/tnsnames.ora

# 1. tnsping网络连通性测试
# 主库执行，预期均输出ok
tnsping primary
tnsping standby

# 备库执行，预期均输出ok
tnsping primary
tnsping standby

# 2. sysdba远程登录认证测试
# 主库执行，远程登录备库
sqlplus sys/oracle@standby as sysdba
sql> select status from v$instance;
sql> exit

# 备库执行，远程登录主库
sqlplus sys/oracle@primary as sysdba
sql> select status from v$instance;
sql> exit

# 备库执行，连接主库为target，备库为auxiliary
rman target sys/oracle@primary auxiliary sys/oracle@standby nocatalog

# 核心命令，创建物理备库
rman> duplicate target database for standby from active database nofilenamecheck;

-- 备库执行，验证数据库角色和状态
sqlplus / as sysdba
select open_mode,database_role from v$database;
exit

open_mode            database_role
-------------------- ----------------
mounted              physical standby

-- 主库执行，查询在线redo日志组数量和大小
sqlplus / as sysdba
select group#,bytes/1024/1024 as size_m,status from v$log;

-- 示例：3组在线redo日志，每组50m，创建4组srl
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/primdb/stredo04.log') size 50m;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/primdb/stredo05.log') size 50m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/primdb/stredo06.log') size 50m;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/primdb/stredo07.log') size 50m;

-- 验证srl创建成功
select group#,bytes/1024/1024 as size_m,status from v$standby_log;
exit

-- 备库执行，创建srl
sqlplus / as sysdba
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/standb/stredo04.log') size 50m;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/standb/stredo05.log') size 50m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/standb/stredo06.log') size 50m;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/standb/stredo07.log') size 50m;

-- 验证srl创建成功
select group#,bytes/1024/1024 as size_m,status from v$standby_log;
exit

-- 1. 备库执行，打开数据库到只读状态
sqlplus / as sysdba
alter database open;

-- 验证打开模式，预期输出read only
select open_mode from v$database;

-- 2. 备库执行，开启实时应用模式
alter database recover managed standby database using current logfile disconnect from session;
exit

sqlplus / as sysdba
select open_mode,database_role,protection_mode,protection_level from v$database;
exit

open_mode            database_role    protection_mode      protection_level
-------------------- ---------------- -------------------- --------------------
read only with apply physical standby maximum performance  maximum performance

sqlplus / as sysdba
select process,status,thread#,sequence#,block# from v$managed_standby;
exit

process   status          thread#  sequence#     block#
--------- ------------ ---------- ---------- ----------
mrp0      wait_for_log          1          0          0
rfs       idle                  1         10        120

sqlplus / as sysdba
select name,value from v$dataguard_stats where name in ('apply lag','transport lag');
exit

name                  value
--------------------- --------------------
transport lag         +00 00:00:00
apply lag             +00 00:00:00

-- 主库执行，创建测试表并插入数据
sqlplus / as sysdba
create table test_dg(id number,name varchar2(50));
insert into test_dg values(1,'dg同步测试成功');
insert into test_dg values(2,'oracle 11g adg实时同步');
commit;
-- 手动切换日志，触发同步
alter system archive log current;
exit
-- 备库执行，查询测试表
sqlplus / as sysdba
select * from test_dg;
exit

-- 1. 切换为最大可用模式（主库执行）
shutdown immediate;
startup mount;
alter database set standby database to maximize availability;
alter database open;
-- 2. 切换为最大保护模式（主库执行）
shutdown immediate;
startup mount;
alter database set standby database to maximize protection;
alter database open;
-- 3. 切回默认最大性能模式（主库执行）
shutdown immediate;
startup mount;
alter database set standby database to maximize performance;
alter database open;
-- 验证当前保护模式
select protection_mode,protection_level from v$database;

-- 步骤1：主库执行，检查切换状态
select switchover_status from v$database;

-- 步骤2：主库执行，切换为备库角色
alter database commit to switchover to physical standby with session shutdown;

-- 步骤3：原主库执行，重启到mount状态
shutdown immediate;
startup mount;

-- 步骤4：原备库执行，检查切换状态
select switchover_status from v$database;

-- 步骤5：原备库执行，切换为主库角色
alter database commit to switchover to primary with session shutdown;

-- 步骤6：新主库执行，打开数据库
alter database open;

-- 步骤7：新备库执行，开启实时同步
alter database recover managed standby database using current logfile disconnect from session;

-- 步骤8：验证角色状态
select database_role from v$database;

-- 步骤1：备库执行，检查日志gap，尽可能补齐主库未传输的归档日志
select thread#,low_sequence#,high_sequence# from v$archive_gap;
-- 若有gap，手动拷贝主库归档到备库，注册后应用
-- alter database register physical logfile '/u01/app/oracle/arch/1_10_1234567890.arc';
-- 步骤2：备库执行，结束redo应用
alter database recover managed standby database cancel;
-- 步骤3：备库执行，切换为主库角色
alter database recover managed standby database finish;
alter database commit to switchover to primary;
-- 步骤4：新主库执行，打开数据库
alter database open;
-- 步骤5：验证角色状态
select database_role from v$database;

-- 步骤1：主备库均执行，开启dg broker启动参数
alter system set dg_broker_start=true scope=both;
-- 步骤2：主备库监听均添加dgmgrl静态服务（listener.ora）
sid_list_listener =
  (sid_list =
    (sid_desc =
      (global_dbname = primdb)
      (sid_name = primdb)
      (oracle_home = /u01/app/oracle/product/11.2.0.4/db_1)
    )
    -- 新增dgmgrl静态服务
    (sid_desc =
      (global_dbname = primdb_dgmgrl)
      (sid_name = primdb)
      (oracle_home = /u01/app/oracle/product/11.2.0.4/db_1)
    )
  )
-- 主备库重启监听使配置生效
lsnrctl stop
lsnrctl start
-- 步骤3：主库执行，启动dgmgrl工具，连接主库
dgmgrl
dgmgrl> connect sys/oracle@primary
-- 步骤4：创建dg broker配置
dgmgrl> create configuration 'dg_broker' as primary database is 'primdb' connect identifier is primary;
-- 步骤5：添加备库到配置
dgmgrl> add database 'standb' as connect identifier is standby maintained as physical;
-- 步骤6：启用配置
dgmgrl> enable configuration;
-- 步骤7：查看配置状态
dgmgrl> show configuration;

# 查看主备库详情
show database verbose primdb;
show database verbose standb;
# 计划内switchover切换
switchover to standb;
# 故障failover切换
failover to standb;
# 禁用/启用配置
disable configuration;
enable configuration;