oracle 单节点 搭建data guard

1、准备两台服务器 ip 分别是
172.x.x.51 pri
172.x.x.52 std
同时关闭iptables selinux=disabled
两库的SID一样
主库安装数据库软件并且创建数据库，备库只安装数据库即可
2、确定主库为归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archivelog
Oldest online log sequence 44
Next log sequence to archive 46
Current log sequence 46
SQL>
3、确定库为force logging模式
SQL> alter database force logging;
Database altered.

3、主库操作 创建备库日志文件路径
查看数据库的日志组个数与大小，因为我们创建 standby 日志组的个数是原日志
组个数+1 再与 thread 的积((2+1)*3)，size 不能小于原日志文件的大小。
SQL> select group#,thread#,bytes/1024/1024 M ,STATUS from v$log;
GROUP# THREAD# M STATUS

---

 1       1          50 CURRENT
 3       1          50 INACTIVE
 2       1          50 INACTIVE

ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 4 (‘/home/oracle/app/oradata/pri/redo01_dg1.log’) size 50M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5 (‘/home/oracle/app/oradata/pri/redo02_dg1.log’) size 50M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 6 (‘/home/oracle/app/oradata/pri/redo03_dg1.log’) size 50M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 7 (‘/home/oracle/app/oradata/pri/redo04_dg1.log’) size 50M;

4、主库操作 创建监听
修改 listner.ora文件

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pri)
(SID_NAME = pri)
(ORACLE_HOME = /home/oracle/app/product/11.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pri)(PORT = 1530))
)
)

ADR_BASE_LISTENER = /home/oracle/app

5、主库操作：修改tnsnames.ora 文件

pri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = pri)(PORT = 1530))
)
(CONNECT_DATA =
(SERVICE_NAME = pri)
)
)

pri_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1530))
)
(CONNECT_DATA =
(SERVICE_NAME = pri)
)
)
ps:SERVICE_NAME 是数据库中的那个service_name)
tnsname.ora 也可以用netmgr 命令来创建
6、主库操作 设置主备库的归档目录
设置主库归档路径
SQL> alter system set log_archive_dest=”;
System altered.
SQL> alter system set log_archive_dest_1=’LOCATION=/data/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dtct’;
System altered.
设置备库归档路径
SQL> alter system set log_archive_dest_2=’SERVICE=pri_dg async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dtct’;
System altered.

ps SERVICE=pri_dg 其中pri_dg为tnsname.ora 中的pri_dg

7、主库操作 配置归档最大进程数（可做可不做）
SQL> show parameter log_archive_max
NAME TYPE VALUE

---

log_archive_max_processes integer 4
SQL> alter system set log_archive_max_processes=25;
System altered.

8、主库操作 修改参数文件

SQL>create pfile from pfile;
修改$ORACLE_HOME/dbs下的initpri.ora文件
DB_UNIQUE_NAME=pri
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=pri_dg
FAL_CLIENT=pri
STANDBY_FILE_MANAGEMENT=AUTO

---

具体如下
pri.__db_cache_size=75497472
pri.__java_pool_size=4194304
pri.__large_pool_size=4194304
pri.__oracle_base=’/home/oracle/app’#ORACLE_BASE set from environment
pri.__pga_aggregate_target=197132288
pri.__sga_target=213909504
pri.__shared_io_pool_size=0
pri.__shared_pool_size=121634816
pri.__streams_pool_size=0
*.audit_file_dest=’/home/oracle/app/admin/pri/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0’
*.control_files=’/home/oracle/app/oradata/pri/control01.ctl’,’/home/oracle/app/flash_recovery_area/pri/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_file_name_convert=’/home/oracle/app/oradata/pri/’,’/home/oracle/app/oradata/pri/’
*.db_name=’pri’
*.db_recovery_file_dest=’/home/oracle/app/flash_recovery_area’
*.db_recovery_file_dest_size=4070572032
*.DB_UNIQUE_NAME=’pri’
*.diagnostic_dest=’/home/oracle/app’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=priXDB)’
*.fal_client=’pri’
*.fal_server=’pri_dg’
*.log_archive_config=’DG_CONFIG=(pri,pri_dg)’
*.log_archive_dest_1=’LOCATION=/home/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri’
*.log_archive_dest_2=’SERVICE=pri_dg async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_max_processes=30
*.log_file_name_convert=’/home/oracle/app/oradata/pri/’,’/home/oracle/app/oradata/pri/’
*.memory_target=411041792
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
*.undo_tablespace=’UNDOTBS1’
9、主库操作 创建密码文件
orapwd file=$ORACLE_HOME/dbs/orapwdtct password=oracle entries=10；

10、把参数文件与密码文件拷到备库对应目录
通过scp命令即可
11、备库操作 创建listener.ora
netca 命令
然后修改 listner.ora文件
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pri)
(SID_NAME = pri)
(ORACLE_HOME = /home/oracle/app/product/11.2.0/dbhome_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1530))
)
)
ADR_BASE_LISTENER = /home/oracle/app
12、备库操作 配置tnsname.ora

pri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = pri)(PORT = 1530))
)
(CONNECT_DATA =
(SERVICE_NAME = pri)
)
)

pri_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1530))
)
(CONNECT_DATA =
(SERVICE_NAME = pri)
)
)

13、备库操作 修改参数文件
主要修改
fal_client
fal_server
log_archive_dest_2

pri.__db_cache_size=75497472
pri.__java_pool_size=4194304
pri.__large_pool_size=4194304
pri.__oracle_base=’/home/oracle/app’#ORACLE_BASE set from environment
pri.__pga_aggregate_target=197132288
pri.__sga_target=213909504
pri.__shared_io_pool_size=0
pri.__shared_pool_size=121634816
pri.__streams_pool_size=0
*.audit_file_dest=’/home/oracle/app/admin/pri/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0’
*.control_files=’/home/oracle/app/oradata/pri/control01.ctl’,’/home/oracle/app/flash_recovery_area/pri/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_file_name_convert=’/home/oracle/app/oradata/pri/’,’/home/oracle/app/oradata/pri/’
*.db_name=’pri’
*.db_recovery_file_dest=’/home/oracle/app/flash_recovery_area’
*.db_recovery_file_dest_size=4070572032
*.DB_UNIQUE_NAME=’pri’
*.diagnostic_dest=’/home/oracle/app’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=priXDB)’
*.fal_client=’pri_dg’
*.fal_server=’pri’
*.log_archive_dest_1=’LOCATION=/home/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri’
*.log_archive_dest_2=’SERVICE=pri async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_max_processes=30
*.log_file_name_convert=’/home/oracle/app/oradata/pri/’,’/home/oracle/app/oradata/pri/’
*.memory_target=411041792
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
*.undo_tablespace=’UNDOTBS1’

14、备库操作 pfile文件启动数据库到nomount状态
startup nomount pfiel =”
15、主库操作 上面create pfile from spfile 的时候数据还是用原来的spfile 启动的，现在在主库要用修改之后的pfile启动
startup nomount pfiel =”
然后create spfile from pfile （下次启动的时候直接用starup 即可利用spfile 启动）
16、主库服务器上操作 拷贝数据库
rman target sys/oracle@pri auxiliary sys/oracle@pri_dg
[oracle@localhost admin]$ rman target sys/oracle@pri auxiliary sys/oracle@pri_dg

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 14 17:02:04 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: DTCT (DBID=1482628294)
connected to auxiliary database: DTCT (not mounted)
这里的pri pri_dg是tnsnames上面的内容
最后一行显示 备库的状态为nomount

duplicate target database for standby from active database spfile set db_unique_name ‘dtct’ nofilenamecheck;

17、备库开启日志应用

1）备库开启备库日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

2）验证备库日志应用

验证备库接收日志是否应用
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED

---

 8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES
 9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES

3）主库切换日志
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
4）备库查看日志
select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED

---

 8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES
 9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES
10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES
11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES
12 2014-07-21 12:23:23 2014-07-21 12:23:27 YES

5）备库数据库开启read only
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
Progress
6）关闭备库管理
SQL> alter database recover managed standby database cancel;
Database altered.
7）主库切换日志
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
8）备库已接收但未应用
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED

---

 8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES
 9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES
10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES
11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES
12 2014-07-21 12:23:23 2014-07-21 12:23:27 YES
13 2014-07-21 12:23:27 2014-07-21 12:42:17 NO
14 2014-07-21 12:42:17 2014-07-21 12:42:19 NO

9）备库open到read only模式
SQL> alter database open read only;
Database altered.
备库开启日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
10）备库已经应用主库日志
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED

---

 8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES
 9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES
10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES
11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES
12 2014-07-21 12:23:23 2014-07-21 12:23:27 YES
13 2014-07-21 12:23:27 2014-07-21 12:42:17 YES
14 2014-07-21 12:42:17 2014-07-21 12:42:19 YES

18、验证：
主库
create table test1 (id number);
alter system switch logfile;

备库
SQL> desc test1;
Name Null? Type

---

ID NUMBER