[DataGuard] 创建物理备库与角色切换

创建一主库PRIM, 二物理备库PDG, LDG

1. 设置主库归档及强制日志模式
PRIM> select log_mode, force_logging from v$database;
LOG_MODE     FOR
------------ ---
ARCHIVELOG   YES

PRIM> alter database [no]archivelog;
PRIM> alter database [no] force logging;

2. 创建密码文件并复制到备库
[PRIM]$ orapwd file=$ORACLE_HOME/dbs/orapwprim password=oracle entries=10
[PRIM]$ cp orapwprim orapwpdg
[PRIM]$ cp orapwprim orapwldg

3. 采用最大保护或最高可用模式时, 从库要创建 standby redologs
# 为了将来主库也能顺利的做角色切换, 最好也创建
PRIM> alter database add standby logfile group 4 ('/u01/data/prim/redo04.log') size 50m;
PRIM> alter database add standby logfile group 5 ('/u01/data/prim/redo05.log') size 50m;
PRIM> alter database add standby logfile group 6 ('/u01/data/prim/redo06.log') size 50m;
PRIM> alter database add standby logfile group 7 ('/u01/data/prim/redo07.log') size 50m;

4. 设置主库初始化参数
PRIM> create pfile from spfile;
[PRIM]$ vi $ORACLE_HOME/dbs/initprim.ora
--
# primary settings
*.db_name='dg'
*.db_unique_name='prim'
*.log_archive_config='dg_config=(prim,pdg,ldg)'
*.log_archive_dest_1='location=/u01/arch/prim'
*.log_archive_dest_2='service=pdg_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=pdg'
*.log_archive_dest_3='service=ldg_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=ldg'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer
*.log_archive_dest_state_3=defer
*.remote_login_passwordfile='exclusive'

# standby settings
*.fal_server=pdg_tns_name,ldg_tns_name
*.fal_client=prim_tns_name
*.db_file_name_convert='/u01/data/pdg','/u01/data/prim','/u01/data/ldg','/u01/data/prim'
*.log_file_name_convert='/u01/data/pdg','/u01/data/prim','/u01/data/ldg','/u01/data/prim'
*.standby_file_management=auto
--

PRIM> shutdown immediate
PRIM> create spfile from pfile;
PRIM> startup

5. 创建备库库控制文件
PRIM> alter database create standby controlfile as '/tmp/control01.ctl' reuse;

6. 创建主库冷(热)备份文件
[PRIM]$ 复制备份数据文件, 初始化参数文件, 控制文件到备库

7.主, 备库创建监听与网络服务名
[PRIM/PDG/LDG]$ cat $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )

[PRIM/PDG/LDG]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
prim_tns_name =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prim_host_name)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prim.localhost)
    )
  )

pdg_tns_name =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pdg_host_name)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdg.localhost)
    )
  )

ldg_tns_name =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ldg_host_name)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ldg.localhost)
    )
  )

[PRIM/PDG/LDG]$ tnsping prim_tns_name
[PRIM/PDG/LDG]$ tnsping pdg_tns_name
[PRIM/PDG/LDG]$ tnsping ldg_tns_name

8. 创建备库所需要的文件夹
[PDG]$ mkdir -p $ORACLE_BASE/admin/pdg/{adump,bdump,cdump,udump}
[LDG]$ mkdir -p $ORACLE_BASE/admin/ldg/{adump,bdump,cdump,udump}
[PDG]$ mkdir -p /u01/arch/pdg
[LDG]$ mkdir -p /u01/arch/ldg

9. 设置备库初始化参数
[PDG]$ cat $ORACLE_HOME/dbs/initpdg.ora
--
# primary settings
*.db_name='dg' => 与主库相同
*.db_unique_name='pdg'
*.log_archive_config='dg_config=(prim,pdg,ldg)'
*.log_archive_dest_1='location=/u01/arch/pdg'
*.log_archive_dest_2='service=prim_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=prim'
*.log_archive_dest_3='service=ldg_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=ldg'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer
*.log_archive_dest_state_3=defer
*.remote_login_passwordfile='exclusive'
# standby settings
*.fal_server=prim_tns_name,ldg_tns_name
*.fal_client=pdg_tns_name
*.db_file_name_convert='/u01/data/prim','/u01/data/pdg','/u01/data/ldg','/u01/data/pdg'
*.log_file_name_convert='/u01/data/prim','/u01/data/pdg','/u01/data/ldg','/u01/data/pdg'
*.standby_file_management=auto
--

[LDG]$ cat $ORACLE_HOME/dbs/initldg.ora
--
# primary settings
*.db_name='dg' => 与主库相同
*.db_unique_name='ldg'
*.log_archive_config='dg_config=(prim,pdg,ldg)'
*.log_archive_dest_1='location=/u01/arch/pdg'
*.log_archive_dest_2='service=prim_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=prim'
*.log_archive_dest_3='service=pdg_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=pdg'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer
*.log_archive_dest_state_3=defer
*.remote_login_passwordfile='exclusive'
# standby settings
*.fal_server=prim_tns_name,pdg_tns_name
*.fal_client=ldg_tns_name
*.db_file_name_convert='/u01/data/prim','/u01/data/ldg','/u01/data/pdg','/u01/data/ldg'
*.log_file_name_convert='/u01/data/prim','/u01/data/ldg','/u01/data/pdg','/u01/data/ldg'
*.standby_file_management=auto
--

[PDG/LDG]$ export ORACLE_SID=pdg => ldg
[PDG/LDG]$ sqlplus / as sysdba
PDG/LDG> create spfile from pfile;

10. 挂载从库, 启动从库日志应用, 启动主库发送日志
PDG/LDG> startup mount
PDG/LDG> alter database recover managed standby database disconnect from session;
PRIM> alter system set log_archive_dest_state_2=enable;
PRIM> alter system set log_archive_dest_state_3=enable;

11. 验证创建结果

12. SwitchOver
12-1. 主库->备库
PRIM> alter database commit to switchover to physical standby [with session shutdown];
PRIM> shutdown immediate
PRIM> startup mount
PRIM> select database_role,switchover_status,open_mode from v$database;
PRIM> alter database recover managed standby database disconnect from session;

12-2. 备库->主库
PDG> alter database commit to switchover to primary [with session shutdown];
ERROR at line 1:
ORA-16139: media recovery required
PDG> alter database recover managed standby database disconnect from session;
Database altered.
PDG> shutdown immediate
PDG> startup
PDG> select database_role,switchover_status,open_mode from v$database;

13. FailOver
# 基本都是在待转换的物理备库上执行, 因为此时主库可能无法访问

13-1. 查看保护模式, 切换到最大性能模式
PDG> select protection_level from v$database;
PDG> alter database set standby database to maximize performance;

13-2. 检查归档文件是否连续
PDG> select * from v$archive_gap;

# 可能的话从主库把缺少的归档文件复制到备库并注册
PDG> alter database register physical logfile 'logfilepath';

13-3. 检查归档是否完整
# 可能的话将多出的序号归档拷贝到备库
PRIM> select distinct resetlogs_id, thread#, max(sequence#) over(partition by resetlogs_id, thread#) a from v$archived_log;
PDG> select distinct resetlogs_id, thread#, max(sequence#) over(partition by resetlogs_id, thread#) a from v$archived_log;

13-4. 切换备库为主库
PDG> alter database recover managed standby database finish force;
PDG> alter database commit to switchover to primary [with session shutdown];

13-5. 启动新主库
# 当前为mount的话直接打开即可, 否则需要先关闭再启动
PDG> shutdown immediate
PDG> startup

13-6. 利用备份恢复或重建旧主库


14. 调整物理备库redo应用频率
14-1. 设置recover并行度
PDG> recover standby database parallel N; -- cpus x 2
PDG> show parameter parallel_max_servers -- 重启后会恢复为此初始值
NAME                  TYPE     VALUE
--------------------- -------- -------
parallel_max_servers  integer  40

14-2. 物理备库设置
PDG> show parameter db_block_checking
NAME                TYPE    VALUE
------------------- ------- -------
db_block_checking   string  FALSE

14-3. 提高次值, 如4096能提高20%性能, 注意会占用更多内存
PDG> show parameter parallel_execution_message_size
NAME                             TYPE    VALUE
-------------------------------- ------- -------
parallel_execution_message_size  integer 2148

14-4. 优化磁盘I/O -- 控制到数据文件磁盘IO是否异步
PDG> show parameter disk_asynch_io
NAME            TYPE     VALUE
--------------- -------- -------
disk_asynch_io  boolean  TRUE