Oracle 10gR2 DataGuard搭建(非duplicate方式)

共计 13740 个字符，预计需要花费 35 分钟才能阅读完成。

Oracle 10gR2 DataGuard 搭建(非 duplicate 方式)

我的实验环境：
源生产库(主库)：
IP 地址：192.168.1.30
Oracle 10.2.0.5 单实例

新 DG 库（备库）：
IP 地址：192.168.1.31
Oracle 10.2.0.5 单实例

1. 源生产库开启归档
2.rman 备份源生产库
3. 修改源生产库参数
4. 配置 tnsnames.ora
5. 同步密码文件
6. 配置 pfile 文件
7. 创建备库控制文件
8. 还原备库
9. 开启日志应用
10.switchover 测试
11. 创建还原点，激活备库测试
12.failover 测试

1. 源生产库开启归档

部署 Dataguard 环境，要求主库必须开启归档模式；如果没有开启，需要先申请停机开启归档。

shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list;

注意：归档日志存放位置，并制定归档日志删除策略；

归档日志删除举例(删除 7 天前归档)：

vi delarch.sql
crosscheck archivelog all;
delete noprompt archivelog all completed before "sysdate-7";

vi delarch.sh
#!/bin/bash
#Oracle ENV (you may need to change it.)
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export ORACLE_SID=ora10
export PATH=$ORACLE_HOME/bin:$PATH
rman target / @/home/oracle/rman/delarch.sql log=/home/oracle/rman/delarch.log

如果之前主库开启了归档，且有合理的备份策略；则实际上很可能不需要此步骤单独清除归档。

2.rman 备份源生产库

将主库 rman 全备，视具体情况选择是否压缩备份集，我这里空间有限选择压缩备份集。

备份脚本：

[oracle@oradb30 rman]$ cat backup.sql 
run {allocate channel d1 type disk;
allocate channel d2 type disk;
backup as compressed backupset database format '/orabak/rman/data_%d_%T_%s.bak' plus archivelog format '/orabak/rman/log_%d_%T_%s.bak';
release channel d1;
release channel d2;
}

[oracle@oradb30 rman]$ cat backup.sh
#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export ORACLE_SID=ora10
export PATH=$ORACLE_HOME/bin:$PATH
rman target / @backup.sql log backup.log

执行备份：
[oracle@oradb30 rman]$ nohup sh backup.sh &

[oracle@oradb30 rman]$ tail -200f backup.log 

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Jan 4 21:47:23 2017

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORA10 (DBID=914008358)

RMAN> run {2> allocate channel d1 type disk;
3> allocate channel d2 type disk;
4> backup as compressed backupset database format '/orabak/rman/data_%d_%T_%s.bak' plus archivelog format '/orabak/rman/log_%d_%T_%s.bak';
5> release channel d1;
6> release channel d2;
7> }
8> 
using target database control file instead of recovery catalog
allocated channel: d1
channel d1: sid=143 devtype=DISK

allocated channel: d2
channel d2: sid=142 devtype=DISK


Starting backup at 04-JAN-17
current log archived
channel d1: starting compressed archive log backupset
channel d1: specifying archive log(s) in backup set
input archive log thread=1 sequence=46 recid=45 stamp=932415090
input archive log thread=1 sequence=47 recid=46 stamp=932420845
channel d1: starting piece 1 at 04-JAN-17
channel d2: starting compressed archive log backupset
channel d2: specifying archive log(s) in backup set
input archive log thread=1 sequence=43 recid=42 stamp=932414314
input archive log thread=1 sequence=44 recid=43 stamp=932414362
input archive log thread=1 sequence=45 recid=44 stamp=932415036
channel d2: starting piece 1 at 04-JAN-17
channel d1: finished piece 1 at 04-JAN-17
piece handle=/orabak/rman/log_ORA10_20170104_102.bak tag=TAG20170104T214725 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:02
channel d2: finished piece 1 at 04-JAN-17
piece handle=/orabak/rman/log_ORA10_20170104_103.bak tag=TAG20170104T214725 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:02
Finished backup at 04-JAN-17

Starting backup at 04-JAN-17
channel d1: starting compressed full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/ora10/ORA10/datafile/o1_mf_system_d5tw48bw_.dbf
input datafile fno=00002 name=/oradata/ora10/ORA10/datafile/o1_mf_undotbs1_d5tw48d8_.dbf
input datafile fno=00004 name=/oradata/ora10/ORA10/datafile/o1_mf_users_d5tw48dg_.dbf
channel d1: starting piece 1 at 04-JAN-17
channel d2: starting compressed full datafile backupset
channel d2: specifying datafile(s) in backupset
input datafile fno=00003 name=/oradata/ora10/ORA10/datafile/o1_mf_sysaux_d5tw48c3_.dbf
input datafile fno=00007 name=/oradata/ora10/ORA10/datafile/o1_mf_forhapoc_d5x5bm2b_.dbf
input datafile fno=00005 name=/oradata/ora10/ORA10/datafile/o1_mf_dbs_d_ji_d5x45hbj_.dbf
input datafile fno=00006 name=/oradata/ora10/ORA10/datafile/o1_mf_dbs_i_ji_d5x45jd9_.dbf
channel d2: starting piece 1 at 04-JAN-17
channel d2: finished piece 1 at 04-JAN-17
piece handle=/orabak/rman/data_ORA10_20170104_105.bak tag=TAG20170104T214728 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:35
channel d1: finished piece 1 at 04-JAN-17
piece handle=/orabak/rman/data_ORA10_20170104_104.bak tag=TAG20170104T214728 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:42
Finished backup at 04-JAN-17

Starting backup at 04-JAN-17
current log archived
channel d1: starting compressed archive log backupset
channel d1: specifying archive log(s) in backup set
input archive log thread=1 sequence=48 recid=47 stamp=932420890
channel d1: starting piece 1 at 04-JAN-17
channel d1: finished piece 1 at 04-JAN-17
piece handle=/orabak/rman/log_ORA10_20170104_106.bak tag=TAG20170104T214810 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:02
Finished backup at 04-JAN-17

Starting Control File and SPFILE Autobackup at 04-JAN-17
piece handle=/orabak/rman/20170104/controlfilec-914008358-20170104-06 comment=NONE
Finished Control File and SPFILE Autobackup at 04-JAN-17

released channel: d1

released channel: d2

Recovery Manager complete.


[1]+  Done                    nohup sh backup.sh
[oracle@oradb30 rman]$

备份完成后, 将备份集拷贝到备机。

3. 修改源生产库参数

查看主库文件 (数据文件、临时文件、重做日志文件) 存放目录：

select name from v$datafile union all
select name from v$tempfile union all
select member from v$logfile;

修改源生产库参数：

-- 设置 convert 参数（我这里源端和目标端都设置了 db_create_file_dest，没有设置这两个参数）
alter system set log_file_name_convert='','' scope=spfile;
alter system set db_file_name_convert='','' scope=spfile;
-- 设置数据库为 force logging
alter database force logging;
-- 设置 db_unique_name, log_archive_config(这里主库的 db_unique_name，出于对现有的生产环境最小影响考虑没有改)
alter system set db_unique_name='ora10' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(ora10,ora10dg)'; 
-- 归档日志目录
alter system set log_archive_dest_1='LOCATION=/orabak/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora10';
alter system set log_archive_dest_2='SERVICE=ora10dg ASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora10dg';
-- 归档日志文件命名规则
alter system set log_archive_format='arch_%r_%t_%s.arc' scope=spfile;
--DG 的 fal_server 和 fal_client
alter system set fal_server='ora10dg';
alter system set fal_client='ora10';
-- 设置 standby_file_management 为自动
alter system set standby_file_management=AUTO; 
-- 设置备库日志文件组，数量一般为目标库日志文件组 +1
alter database add standby logfile group 11 size 52428800; 
alter database add standby logfile group 12 size 52428800; 
alter database add standby logfile group 13 size 52428800; 
alter database add standby logfile group 14 size 52428800;

4. 配置 tnsnames.ora

Dataguard 环境，log_archive_config 的配置，就是用到 tnsnames.ora 配置文件中的别名。

-- 配置 tnsnames.ora
在主库所有节点上的 tnsnames.ora 添加相应的连接串，并传给备库。--- 主库 tnsnames.ora 添加
vi $ORACLE_HOME/network/admin/tnsnames.ora

ORA10 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora10)
    )
  )

ORA10DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.31)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora10dg)
    )
  )

将 tnsnames.ora 上传到备库，如果之前 tnsnames 有其他内容，可以选择添加内容，只要最终保证主备库可以互相访问即可。

5. 同步密码文件

-- 密码文件从主库拷贝到备库
[oracle@oradb30 dbs]$ scp orapwora10 192.168.1.31:/u01/app/oracle/product/10.2.0/db_1/dbs/
oracle@192.168.1.31's password: 
orapwora10                                                                                                                                                  100% 1536     1.5KB/s   00:00    
[oracle@oradb30 dbs]$

6. 配置 pfile 文件

Dataguard 环境下，需要将主库的参数文件传到备库，进行修改，尤其注意路径在备库都存在且有相应权限。

-- 创建 pfile 文件

从主库的 spfile 中导出 pfile 文件，上传到备库，并做适当的修改调整。-- 在主库上创建 pfile 文件，并上传到备库
create pfile='/tmp/init.ora' from spfile;

SQL> create pfile='/tmp/init.ora' from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oradb30 dbs]$ scp /tmp/init.ora 192.168.1.31:/tmp/
reverse mapping checking getaddrinfo for bogon failed - POSSIBLE BREAK-IN ATTEMPT!
oracle@192.168.1.31's password: 
init.ora                                                                                                                                                    100% 1430     1.4KB/s   00:00    
[oracle@oradb30 dbs]$

– 备库最终 pfile 参数文件
[oracle@oradb31 admin]$ vi /tmp/init.ora

*.audit_file_dest='/u01/app/oracle/admin/ora10/adump'
*.background_dump_dest='/u01/app/oracle/admin/ora10/bdump'
*.compatible='10.2.0.5.0'
*.core_dump_dest='/u01/app/oracle/admin/ora10/cdump'
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ora10'
*.db_recovery_file_dest='/orabak/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='ora10dg'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora10XDB)'
*.fal_client='ora10dg'
*.fal_server='ora10'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(ora10dg,ora10)'
*.log_archive_dest_1='LOCATION=/orabak/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora10dg'
*.log_archive_dest_2='SERVICE=ora10 ASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora10'
*.log_archive_format='arch_%r_%t_%s.arc'
*.open_cursors=300
*.pga_aggregate_target=96468992
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=290455552
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/ora10/udump'

启动备库到 nomount 状态：

SQL> startup nomount pfile='/tmp/init.ora';
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory
SQL> exit
Disconnected
[oracle@oradb31 admin]$ mkdir -p /orabak/flash_recovery_area
[oracle@oradb31 admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 4 23:24:25 2017

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL>  startup nomount pfile='/tmp/init.ora';
ORACLE instance started.

Total System Global Area  293601280 bytes
Fixed Size                  2095768 bytes
Variable Size             146802024 bytes
Database Buffers          138412032 bytes
Redo Buffers                6291456 bytes
SQL> exit

7. 创建备库控制文件

Dataguard 环境下，需要在主库创建备库的控制文件并传输到备库。

– 创建控制文件
主库执行，创建备库控制文件

alter database create standby controlfile as '/tmp/control01.ctlbak';

[oracle@oradb30 dbs]$ scp /tmp/control01.ctlbak 192.168.1.31:/tmp/
reverse mapping checking getaddrinfo for bogon failed - POSSIBLE BREAK-IN ATTEMPT!
oracle@192.168.1.31's password: 
control01.ctlbak                                                                                                                                            100% 6928KB   6.8MB/s   00:00

8. 还原备库

8.1 在备库上启动数据库到 nomount 状态

# 创建 SPFILE
SQL>create SPFILE from pfile='/tmp/init.ora';

# 启动到 nomount 状态
startup nomount

8.2 启动数据库到 mount 状态

restore controlfile from ‘/tmp/control01.ctlbak’;
alter database mount;

可能需要手工注册备份集；
crosscheck backupset;
catalog start with ‘/orabak/rman/’;

8.3 还原备份

vi /home/oracle/scripts/restore.sh

rman target / <<EOF! > ora10_restore.log
run {allocate channel d1 type disk;
allocate channel d2 type disk;
restore database;
release channel d1;
release channel d2;
}
exit;
EOF!

nohup sh restore.sh &

vi /home/oracle/scripts/recover.sh

rman target / <<EOF! > ora10_recover.log
run {allocate channel d1 type disk;
allocate channel d2 type disk;
recover database;
release channel d1;
release channel d2;
}
exit;
EOF!

nohup sh recover.sh &

9. 开启日志应用

备库开启日志应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

10. switchover 测试

switchover, 主备角色互换。
注意，主机的 crontab 定时任务确认也正确迁移。

-- 源生产库：
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
alter database commit to switchover to physical standby with session shutdown;

-- 备库（switchover 为新生产库）：
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE OPEN;

-- 返回源生产库执行（switchover 为新备库）：
shutdown immediate
startup mount
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

如果发现备库没有实时恢复，重置链路的状态再次切换日志试试：

SQL> alter system set log_archive_dest_state_2=defer;
SQL> alter system set log_archive_dest_state_2=enable;
SQL> alter system switch logfile;

11. 创建还原点，激活备库测试

11.1 创建 restore point

1) 在备库上设置 flashback 区域及大小

SQL> alter system set db_recovery_file_dest='/orabak/flash_recovery_area';
SQL> alter system set db_recovery_file_dest_size=1000g;
SQL> select current_scn||'' from v$database;

2) Standby 库取消 redo 日志应用并创建一个数据库闪回点

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
-- 这里注意下，如果你的备库环境是 RAC，需要关闭其他节点，然后才可以创建restore point：SQL> drop restore point before_application_test;
SQL> CREATE RESTORE POINT before_application_test GUARANTEE FLASHBACK DATABASE;
Restore point created.

11.2 激活备库

1) 停止主库的日志传到备库

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=defer;

2) 激活备库

SQL> alter system set job_queue_processes = 0 scope=both sid='*';
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> shutdown immediate
SQL> startup

3) 检查 dblinks
select * from dba_db_links;
如果有 dblink 需要删除或其他方式禁用；

4）通知新的应用 IP，开始测试应用工作

注意：为了预防测试过程中有 job 或者 dblink 修改了其他数据库信息，一般要修改 job_queue_processes 参数，删除 dblink。
– 参数
alter system set job_queue_processes = 0 scope=both sid=’‘;
–dblinks
select from dba_db_links;

11.3 闪回恢复备库

1) 恢复备库

SQL> shutdown immediate
SQL> startup mount;
SQL> FLASHBACK DATABASE TO RESTORE POINT before_application_test;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database recover managed standby database using current logfile disconnect;
恢复参数 job_queue_processes 设置值：alter system set job_queue_processes = 10 scope=both sid='*';
恢复参数 db_recovery_file_dest_size 设置值：alter system set db_recovery_file_dest_size=2g;

2) 恢复日志传输
恢复主库的日志传到备库

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable;

12. failover 测试

failover，源生产库不再可用，备库强制激活为主库；

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
#操作不可逆，确定实际情况需要 failover
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH force;
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE; 
#尝试常规切换为主库
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
如果这一步的常规切换失败，提示需要介质恢复，那么：1）恢复备库 recover standby database until cancel;
 2）激活备库 alter database activate standby database;
#最后重新启动数据库
shutdown immediate;
startup