使用RMAN和Broker快速搭建DataGuard环境
1. 不适合在生成环境中使用,如果生成环境中搭建dg的话推荐手动配置;
2. 实验环境:
1. 主库:
1. ip:192.168.10.11;
2. hostname:primary.snda.com;
2. 备库:
1. ip:192.168.10.12;
2. hostname:standby.snda.com;
3. 分别设置主库和备库的~/.bash_profile,$ORACLE_HOME/network/admin/listener.ora和$ORACLE_HOME/network/admin/tnsnames.ora文件;
4. 修改主库的系统参数,然后关闭数据库,启动到mount状态,并修改数据库为归档模式,并创建pfile;
5. 备份主库的数据文件和控制文件;
6. 拷贝主库的文件到备库;
7. 修改备库的pfile,创建spfile,并启动到nomount状态;
8. 使用rman恢复备库;
9. 主库备库分别添加Standby Redo Log Fiels;
10. 分别修改主库和备库的初始化参数:dg_broker_start;
11. 在任意一台机器上启动dgmgrl,并配置当前的broker环境;
12. 修改数据库为高可用模式;
13. SWITCHOVER和FAILOVER操作;
14. 切换数据库的状态为只读模式和在线接收日志状态;

— 主库和备库的~/.bash_profile文件内容;
ORACLE_SID=PROD
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
PATH=/usr/sbin:$PATH
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH CLASSPATH

alias sqlplus=’rlwrap sqlplus’
alias rman=’rlwrap rman’
alias dgmgrl=’rlwrap dgmgrl’

— 主库的$ORACLE_HOME/network/admin/listener.ora文件内容,修改完成后运行lsnrctl start/reload重新加载监听文件;
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=primary.snda.com)(PORT=1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
)
(SID_DESC=
(SID_NAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME=PROD_PRI_DGMGRL)
)
)

— 备库的$ORACLE_HOME/network/admin/listener.ora文件内容,修改完成后运行lsnrctl start/reload重新加载监听文件;
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=standby.snda.com)(PORT=1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
)
(SID_DESC=
(SID_NAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME=PROD_SBY_DGMGRL)
)
)

— 主库和备库的$ORACLE_HOME/network/admin/tnsnames.ora文件
PROD_PRI=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=primary.snda.com)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=PROD)
(SERVER=DEDICATED)
)
)

PROD_SBY=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=standby.snda.com)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=PROD)
(SERVER=DEDICATED)
)
)

— 修改主库的系统参数,然后关闭数据库,启动到mount状态,并修改数据库为归档模式,并创建pfile;
ALTER SYSTEM SET db_unique_name=PROD_PRI scope=spfile;
ALTER SYSTEM SET db_recovery_file_dest_size=4G;
ALTER SYSTEM SET db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’;
ALTER SYSTEM SET log_archive_dest_1=’LOCATION=/u01/app/oracle/flash_recovery_area’;
ALTER SYSTEM SET log_archive_dest_2=”;
ALTER SYSTEM SET local_listener=”;
ALTER SYSTEM SET dispatchers=”;
ALTER SYSTEM SET standby_file_management=AUTO;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ARCHIVELOG;
— ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
CREATE PFILE FROM SPFILE;

— 备份主库的数据文件和控制文件;
rman target /
BACKUP DATABASE FORMAT ‘/u01/app/oracle/flash_recovery_area/%U’;
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT ‘/u01/app/oracle/flash_recovery_area/%U’;

— 拷贝主库的文件到备库;
scp /u01/app/oracle/flash_recovery_area/* oracle@standby.snda.com:/u01/app/oracle/flash_recovery_area/
scp $ORACLE_HOME/dbs/initPROD.ora oracle@standby.snda.com:$ORACLE_HOME/dbs/
scp $ORACLE_HOME/dbs/orapwPROD oracle@standby.snda.com:$ORACLE_HOME/dbs/

— 修改备库的pfile,创建spfile,并启动到nomount状态;
修改备库的参数文件,只修改db_unique_name=’PROD_SBY’即可;
CREATE SPFILE FROM PFILE;
STARTUP NOMOUNT;

— 使用rman恢复备库;
rman target sys/oracle@prod_pri auxiliary /
DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

— 主库备库分别添加Standby Redo Log Fiels;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (‘/u01/app/oracle/oradata/PROD/redo04.log’) SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (‘/u01/app/oracle/oradata/PROD/redo05.log’) SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 (‘/u01/app/oracle/oradata/PROD/redo06.log’) SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 (‘/u01/app/oracle/oradata/PROD/redo07.log’) SIZE 100M;

— 分别修改主库备库的初始化参数;
ALTER SYSTEM SET dg_broker_start=TRUE;

— 在任意一台机器上启动dgmgrl,并配置当前的broker环境;
dgmgrl sys/oracle@prod_pri
CREATE CONFIGURATION DGCONFIG1 AS PRIMARY DATABASE IS PROD_PRI CONNECT IDENTIFIER IS PROD_PRI;
ADD DATABASE PROD_SBY AS CONNECT IDENTIFIER IS PROD_SBY MAINTAINED AS PHYSICAL;
ENABLE CONFIGURATION;
SHOW CONFIGURATION;

— 修改数据库为高可用的保护状态;
EDIT DATABASE PROD_PRI SET PROPERTY LogXptMode=’Sync’;
EDIT DATABASE PROD_SBY SET PROPERTY LogXptMode=’Sync’;
EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

— 切换数据库的主备角色;
SWITCHOVER TO PROD_SBY;
SWITCHOVER TO PRDO_PRI;
SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE, SWITCHOVER_STATUS FROM v$database;

— FAILOVER操作;
FAILOVER TO PROD_PRI;
FAILOVER TO PROD_SBY;

— 切换数据库的状态为只读模式和在线接收日志状态;
EDIT DATABASE PROD_SBY SET STATE=’READ-ONLY’;
EDIT DATABASE PROD_SBY SET STATE=’ONLINE’;

— 设置数据库的其它属性;
EDIT DATABASE db SET PROPERTY StandbyFileManagement=’AUTO’;
EDIT DATABASE db SET PROPERTY StandbyArchiveLocation=’/u01/app/oracle/flash_recovery_area’;

— 删除表空间和表空间所有的内容;
DROP TABLESPACE tbs INCLUDE CONTENTS CASCADE CONTRAINTS;