参考oracle官方文档
部署环境：
主库：oracle11gr2
OS：solaris10
备库：oracle11gr2
OS：solaris10
主备库在不同的主机，且目录结构相同。通过单独的ip段（110段）进行dataguard同步以保证网络速度。
部署步骤：
生产库：（无需停库，在线修改dataguard相应参数）
1. Ensure that the database is in archivelog mode .
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
2. Enable force logging
select log_mode,force_logging from v$database;
SQL> ALTER DATABASE FORCE LOGGING;
3. Create standby redologs
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/orahome/oracle/oradata/bims/sredo7.log' size 2G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/orahome/oracle/oradata/bims/sredo8.log' size 2G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 '/orahome/oracle/oradata/bims/sredo9.log' size 2G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 '/orahome/oracle/oradata/bims/sredo10.log' size 2G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '/orahome/oracle/oradata/bims/sredo11.log' size 2G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '/orahome/oracle/oradata/bims/sredo12.log' size 2G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '/orahome/oracle/oradata/bims/sredo13.log' size 2G;
4. Modify the primary initialization parameter for dataguard on primary,
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=
(bims,bimsstb)';
//注意dg_config参数为主备库db_unique_name名
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bims';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=
bimsstb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bimsstb';
//service参数为tnsname中的服务名
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set FAL_SERVER=bimsstb;
//fal_service参数为tnsname中的服务名
alter system set FAL_CLIENT=bimspri;
//fal_cleient参数为tnsname中的服务名
alter system set DB_FILE_NAME_CONVERT='/orahome/oracle/oradata/bims','/orahome/oracle/oradata/bims' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/orahome/oracle/oradata/bims','/orahome/oracle/oradata/bims' scope=spfile;
alter system set standby_file_management=auto;
5. Ensure that the sql*net connectivity is working fine.
Insert a static entry for Boston in the listener.ora file of the standby system.
主库listener
bash-3.00$ more /orahome/oracle/product/11.2.0/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 业务ip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 业务ip)(PORT = 15555))
(ADDRESS = (PROTOCOL = TCP)(HOST = 业务ip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.59)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /orahome/oracle/product/11.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = bims)
(ORACLE_HOME = /orahome/oracle/product/11.2.0)
(SID_NAME = bims)
)
)
TNSNAMES.ORA for the Primary and Standby should have BOTH entries
bimspri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.59)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = bimspri)
)
)
bimsstb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = bimsstb)
(UR=A)
)
)
Check with the SQL*Net configuration using the following commands on the Primary AND Standby
% tnsping bimspri
% tnsping bimsstb
备库
1、将主库生成的pfile参数文件拷贝到备库相应位置
添加修改以下参数
db_unique_name=bimsstb
log_archive_config='dg_config=
(bims,bimsstb)'
log_archive_dest_1='location=/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=bimsstb'
log_archive_dest_2='service=bimspri lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=bims'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=bimspri
fal_client=bimsstb
db_file_name_convert='/orahome/oracle/oradata/bims','/orahome/oracle/oradata/bims'
log_file_name_convert='/orahome/oracle/oradata/bims','/orahome/oracle/oradata/bims'
standby_file_management=auto
2、启动备库到nomount 状态
Sqlplus /nolog
Conn /as sysdba
Startup nomount (使用pfile)
3、修改备库监听，启动
备库listener
bash-3.00$ more /orahome/oracle/product/11.2.0/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.7)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = 业务ip)(PORT = 15555))
# (ADDRESS = (PROTOCOL = TCP)(HOST = 业务ip)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = 业务ip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.7)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /orahome/oracle/product/11.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = bims)
(ORACLE_HOME = /orahome/oracle/product/11.2.0)
(SID_NAME = bims)
)
)
Lsnrctl start
修改备库tnsname.ora
bimspri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.59)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = bimspri)
)
)
bimsstb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = bimsstb)
(UR=A)
)
)
4、Verify if the connection 'AS SYSDBA' is working
% sqlplus /nolog
SQL> connect sys/passwd@bimspri AS SYSDBA
SQL> connect sys/passwd@bimsstb AS SYSDBA
5、On the primary system invoke the RMAN executable and connect to the primary and the auxiliary database ( i.e., the standby)
开始恢复standby database
方法一、
脚本1（在主库active状态，从磁盘复制库）：
rman target sys/passwd@bimspri auxiliary sys/passwd@bimsstb <
run {
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
ALLOCATE CHANNEL ch03 TYPE DISK;
allocate auxiliary channel stby type DISK;
duplicate target database for standby from active database
nofilenamecheck dorecover;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
}
exit;
EOF" >> /archivelog/dataguard/1.log
方法二、
脚本2（利用主库在带库中的备份恢复备库）：
rman target sys/passwd@bimspri auxiliary sys/passwd@bimsstb <
run {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
allocate auxiliary channel stby type 'SBT_TAPE';
duplicate target database for standby
nofilenamecheck dorecover;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
}
exit;
EOF" >> /archivelog/dataguard/1.log
注意当主备库目录相同时需使用
nofilenamecheck参数，否则会报错
复制结束后的Standby 只启动到mount standby 的状态。 并没有启动MRP的应用归档程序。 所以这个时候查询主备库，归档是不同步的。需要手动的启动MRP进程。
SQL> alter database recover managed standby database disconnect from session;
6、Now connect to standby using SQL*Plus and start the MRP (Managed Recovery Process). Compare the primary last sequence and MRP (Managed Recovery Process) applying sequence.
Example :
SQL> alter database recover managed standby database disconnect from session;
7、If you are licensed to use Active Dataguard (ADG) than open the Standby Database in READ ONLY and start the recovery.
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect;
SQL> alter database recover managed standby database using current logfile disconnect from session;
查看主库状态：
SQL> select status ,gap_status from v$archive_dest_status where dest_id in (1,2);
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID NO GAP
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE PRIMARY MAXIMUM PERFORMANCE READ WRITE
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
查看备库状态：
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY
SQL> select status,gap_status from v$archive_dest_status where dest_id in (1,2);
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID NO GAP
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
检查主备库日志序列是否同步：
SQL> select max(Sequence#) from v$log;
MAX(SEQUENCE#)
--------------
13774
archive log list
关键点：
1.db_unique_name主备设置最好不同，这样保证可以切换。
2.alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(bims,bimsstb)';这个对应的是db_unique_name，而不是tnsnames的服务名。
3.tnsname.ora的服务名是用在fal_server、fal_client上和log_archive_dest_2的SERVICE上
