某控股公司OA系统ORACLE DG搭建
*此处安装ORACLE DATAGUARD是利用ORACLE RMAN DUPLICATE方式安装。
*可以搭建好ORACLE DG再来impdp生产数据，也可以先导入主库数据再来做DG
*注意看下面的配置文件，此处LISTENER TNSNAMES里的SID_NAME我都是用的db_name的ora，没有测试其它的
*TNSNAMES的起名orcl_pd orcl_sd我都用用和db_unique_name一样的
*版本CENTOS6.5 ORACLE11.2.0.4
1、两台服务器上分别安装数据库软件，只安装软件不安装实例，为了减少复杂度，所有目录都一样
安装请参考其他文档
2、在主库上安装数据库实例ORA
DBCA安装
修改日志文件大小
内存分配好
3、在主库上创建与旧库一样的用户名表空间
create tablespace ekp datafile '/u01/app/oracle/oradata/ora/ora01.dbf' size 10240M autoextend on next 512M extent management local; create temporary tablespace ora_temp tempfile'/u01/app/oracle/oradata/ora/ora_temp.dbf' size 10240m autoextend on next 512m extent management local; create user ora identified by abcABC123 default tablespace ekp temporary tablespace ora_temp; grant connect,resource,dba to ora;
4、停止应用，到旧库上导出用户数据
expdp ora/abcABC123 DIRECTORY=oa_databackup dumpfile=oadb1010.dmp logfile=oadbf111.log schemas=ora compression=all
5、把备份文件拷贝到主库上，创建directory，然后导入数据IMPDP
mkdir -p /u01/app/oracle/bakdump create or replace directory oa_databackup as '/u01/app/oracle/bakdump';
6、主库参数修改
1）开启归档
shutdown immediate; startup mount; alter database archivelog; archive log list; alter database open;
2)强制写日志
alter database force logging;
3)增加备库日志
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/ora/redo04.log') size 50M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/ora/redo05.log') size 50M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/ora/redo06.log') size 50M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/ora/redo07.log') size 50M;
4）监听
--主库
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ora) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = ora) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.3.28)(PORT = 1521)) ) ADR_BASE_LISTENER = /u01/app/oracle
--从库
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ora) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = ora) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.3.29)(PORT = 1521)) ) ADR_BASE_LISTENER = /u01/app/oracle
5）tnsname
--主库从库一样
orcl_pd = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.3.28)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ora) ) ) orcl_sd = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.3.29)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora) ) )
6）参数文件
主库参数
ora.__db_cache_size=61874372608 ora.__java_pool_size=939524096 ora.__large_pool_size=536870912 ora.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment ora.__pga_aggregate_target=39325794304 ora.__sga_target=68719476736 ora.__shared_io_pool_size=0 ora.__shared_pool_size=4831838208 ora.__streams_pool_size=134217728 *.audit_file_dest='/u01/app/oracle/admin/ora/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/ora/control01.ctl','/u01/app/oracle/fast_recovery_area/ora/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='ora' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oraXDB)' *.open_cursors=300 *.pga_aggregate_target=39267074048 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=1105 *.sga_target=68719476736 *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=orcl_pd LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_pd,orcl_sd)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_pd' LOG_ARCHIVE_DEST_2='SERVICE=orcl_sd ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_sd' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=orcl_sd STANDBY_FILE_MANAGEMENT=AUTO
从库参数
ora.__db_cache_size=61874372608 ora.__java_pool_size=939524096 ora.__large_pool_size=536870912 ora.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment ora.__pga_aggregate_target=39325794304 ora.__sga_target=68719476736 ora.__shared_io_pool_size=0 ora.__shared_pool_size=4831838208 ora.__streams_pool_size=134217728 *.audit_file_dest='/u01/app/oracle/admin/ora/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/ora/control01.ctl','/u01/app/oracle/fast_recovery_area/ora/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='ora' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oraXDB)' *.open_cursors=300 *.pga_aggregate_target=39267074048 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=1105 *.sga_target=68719476736 *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=orcl_sd LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_pd,orcl_sd)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_sd' LOG_ARCHIVE_DEST_2='SERVICE=orcl_pd ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_pd' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=orcl_pd STANDBY_FILE_MANAGEMENT=AUTO
7) impdp 旧数据
impdp ora/abcABC123 DIRECTORY=oa_databackup DUMPFILE=oadb1010.dmp SCHEMAS=ora
7、 备库操作
从主课拷贝密码文件initoracle_sid listener.ora tnsname.ora 到从库对应位置，注意修改相对应的内容，如initora.ora修改上面从库增加的内容，listener.ora修改IP。其它一样
并在从库新建相关目录：
cd /u01/app/oracle mkdir archivelog mkdir -p oradata/ora mkdir -p fast_recovery_area/ora mkdir -p /u01/app/oracle/admin/ora/adump mkdir -p /u01/app/oracle/bakdump mkdir -p /u01/app/oracle/admin/ora/adump
注意修改etc/hosts
8、备库duplicate
备库利用拷贝过来的参数文件启动到nomount
sqlplus / as sysdba startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initora.ora'
另开窗口rman duplicate复制
rman target sys/oracleadmin@orcl_pd auxiliary sys/oracleadmin@orcl_sd nocatalog duplicate target database for standby nofilenamecheck from active database;
9、打开备库数据库并开启主从复制
sqlplus / as sysdba
alter database open;
alter database recover managed standby database using current logfile disconnect from session;
10、检查状态
正常情况下主库OPEN_MODE 是WIRITE READ 从库是READ ONLY WITH APPLY
SELECT OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUS FROM V$DATABASE;
主备库查询下面语句，看看日志是否一致
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
不一致时，主库切换一下日志在查看
ALTER SYSTEM SWITCH LOGFILE;
11、角色切换SWITCHOVER
1)主库上 select switchover_status from v$database; 如果状态为SESSION ACTIVE则执行下面 alter database commit to switchover to physical standby with session shutdown; 如果状态为TO_STANDBY则执行下面 alter database commit to switchover to physical standby （如果切换失败后要重启一下库） 否则要切换一下日志看看alter system switch logfile; 2)备库上 select switchover_status from v$database switchover_status 为NOT ALLOWED alter database commit to switchover to primary; 3)原来主库上 shutdown immediate startup alter database recover managed standby database using current logfile disconnect from session; （上面是开启日志传输，这个是关闭alter database recover managed standby database cancel;） 4)原来备库上 shutdown immediate startup
12、failover灾难切换
假设物理主库宕机，无法启动，紧急启用备库
直接在备库上操作，将备库转换为主库角色
备库上执行下面四条命令即可
SQL > alter database recover managed standby database finish; SQL > alter database commit to switchover to primary; SQL > shutdown immediate; SQL > startup;
