同一台机器上配置DataGuard
1. 实验环境:
1. ip:192.168.10.11;
2. hostname:primary.snda.com;
2. 设置~/.bash_profile,$ORACLE_HOME/network/admin/listener.ora和$ORACLE_HOME/network/admin/tnsnames.ora文件;
3. 创建必要的目录;
4. 修改主库的系统参数,然后关闭数据库,启动到mount状态,并修改数据库为归档模式;
5. 在主库使用spfile创建pfile:CREATE PFILE FROM SPFILE;
6. 备份主库的数据文件和控制文件;
7. 拷贝主库的文件到备库;
8. 修改备库的参数文件;
9. 设置备库的ORACLE_SID,并启动备库到nomount状态;
10. 设置备库的ORACLE_SID, 使用rman恢复备库;
11. 主库备库分别添加Standby Redo Log Fiels;
12. 重启备库,并使用spfile启动到mount状态,并设置应用日志文件;
13. 修改数据库问高可用模式;
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文件内容;
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=PRODDG)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
)
)

— $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=primary.snda.com)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=PRODDG)
(SERVER=DEDICATED)
)
)

— 创建必要的目录;
mkdir -p /u01/app/oracle/oradata/PRODDG/
mkdir -p /u01/app/oracle/admin/PRODDG/adump
mkdir -p /u01/app/oracle/admin/PRODDG/bdump
mkdir -p /u01/app/oracle/admin/PRODDG/cdump
mkdir -p /u01/app/oracle/admin/PRODDG/udump
mkdir -p /u01/app/oracle/archivelog/prod
mkdir -p /u01/app/oracle/archivelog/proddg

— 修改主库的系统参数,然后关闭数据库,启动到mount状态,并修改数据库为归档模式;
ALTER SYSTEM SET DB_UNIQUE_NAME=PROD_PRI SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PROD_PRI,PROD_SBY)’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/archivelog/prod VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD_PRI’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=PROD_SBY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_SBY’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ SCOPE=SPFILE;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=’EXCLUSIVE’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT=’%t_%s_%r.arc’ SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=’PROD_PRI’ SCOPE=SPFILE;
ALTER SYSTEM SET FAL_CLIENT=’PROD_SBY’ SCOPE=SPFILE;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/PRODDG’,’/u01/app/oracle/oradata/PROD’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/PRODDG’,’/u01/app/oracle/oradata/PROD’ SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=’AUTO’ SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
SHUTDOWN MOUNT;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ARCHIVELOG;
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’;

— 拷贝主库的文件到备库;
cd $ORACLE_HOME/dbs
cp initPROD.ora initPRODDG.ora
orapwd file=orapwPRODDG password=oracle force=y

— 修改备库的参数文件
*.db_name=’PROD’
*.db_block_size=8192
*.db_unique_name=’PROD_SBY’
*.fal_client=’PROD_PRI’
*.fal_server=’PROD_SBY’
*.log_archive_config=’DG_CONFIG=(PROD_PRI,PROD_SBY)’
*.log_archive_dest_1=’LOCATION=/u01/app/oracle/archivelog/proddg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD_SBY’
*.log_archive_dest_2=’SERVICE=PROD_PRI LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_PRI’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’%t_%s_%r.arc’
*.sga_target=300M
*.pga_aggregate_target=150M
*.processes=150
*.compatible=’10.2.0′
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS’
*.db_file_name_convert=’/u01/app/oracle/oradata/PROD’,’/u01/app/oracle/oradata/PRODDG’
*.log_file_name_convert=’u01/app/oracle/oradata/PROD’,’/u01/app/oracle/oradata/PRODDG’
*.control_files=’/u01/app/oracle/oradata/PRODDG/control01.ctl’,’/u01/app/oracle/oradata/PRODDG/control02.ctl’

— 设置备库的ORACLE_SID,并启动备库到nomount状态;
ORACLE_SID=PRODDG
sqlplus / as sysdba
STARTUP NOMOUNT

— 设置备库的ORACLE_SID,使用rman恢复备库;
ORACLE_SID=PRODDG
rman target sys/oracle@prod_pri auxiliary /
DUPLICATE TARGET DATABASE FOR STANDBY;

— 分别在主库和备库添加Standby Redo Log files;
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 DATABASE ADD STANDBY LOGFILE GROUP 4 (‘/u01/app/oracle/oradata/PRODDG/redo04.log’) SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (‘/u01/app/oracle/oradata/PRODDG/redo05.log’) SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 (‘/u01/app/oracle/oradata/PRODDG/redo06.log’) SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 (‘/u01/app/oracle/oradata/PRODDG/redo07.log’) SIZE 100M;

— 重启备库,并使用spfile启动到mount状态,并设置应用日志文件;
shutdown immediate
CREATE SPFILE FROM PFILE;
startup mount
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

— 修改数据库问高可用模式,在主库运行;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
ALTER SYSTEM SWITCH LOGFILE;

— 切换主库备库的角色;
1.在主库运行;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
shutdown immediate;
startup mount;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
2.在备库运行;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;
ALTER SYSTEM SWITCH LOGFILE;

SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE, SWITCHOVER_STATUS FROM v$database;