一、Oracle 11g Dataguard（物理备库安装）
个人建议：
为使得在主备切换后，对应用影响最少，建议客户端配置使用DNS解析主库IP，或者配置多个IP地址（主库+备库地址），且创建服务名（使用触发器当角色为主库时开启服务）
环境描述
主库DG1:
192.168.10.21 dg1.zytk.com dg1
数据库ORACLE_SID=zytk35
监听服务名：primary
db_unique_name: primary
备库DG2（物理）:
192.168.10.22 dg2.zytk.com dg2
数据库ORACLE_SID=zytk35
监听服务名：standby
db_unique_name: standby
数据库版本：11.2.0.4.0
cat ~oracle/.bash_profile(主库备库同样的环境变量配置)
umask 022
#export ORACLE_OWNER=oracle
#export ORACLE_TERM=xterm
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=zytk35
export LANG=en_US
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
1、监听配置,要求主库和物理备库都配置静态监听
主库DG1：
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary) ----设置成当前库
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = zytk35) ----ORACLE_SID
)
)
备库：
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = zytk35)
)
)
2、配置服务名
主库和备机可以相同的配置
cat $ORACLE_HOME/network/admin/tnsnames.ora
PRIMARY = ---设置成db_unique_name (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.zytk.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = primary) ---此对应本机监听中的GLOBAL_DBNAME ) )STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg2.zytk.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = standby) ) )
启动监听后tnsping测试监听服务是否可用
3、主库前期准备开启归档及强制日志
sqlplus / as sysdba
startup mount
alter database force logging; ---强制日志模式
alter database archivelog; ----开启归档
alter database open;
4、创建standby redolog文件
ALTER DATABASE ADD STANDBY LOGFILE group 4 ('/OracleData/zytk35/standby04.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE group 5 ('/OracleData/zytk35/standby05.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE group 6 ('/OracleData/zytk35/standby06.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE group 7 ('/OracleData/zytk35/standby07.log') SIZE 50M;
standby redolog的组数参考公式：(online redolog组数 + 1) * 数据库线程数；单机线程数为1，RAC一般为2。
standby redolog的组成员数和大小也尽量和online redolog一样
5、修改主库的参数
sqlplus / as sysdba
alter system set DB_UNIQUE_NAME=primary scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)' scope=spfile;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/OracleArch/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;
alter system set FAL_SERVER=standby scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
shutdown immediate;
startup
create pfile from spfile;
alter system set archive_lag_target=1800; --强制日志归档，如果数据库30分未归档，则强制归档。
----可以设置网络延迟时间，以及是否启用压缩特性
alter system set log_archive_dest_2 = 'service=DG_ORCLSTD LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ORCLSTD compression=enable net_timeout=20 ';
cat $ORACLE_HOME/dbs/initzytk35.ora
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/OracleData/zytk35/control01.ctl','/u01/app/oracle/fast_recovery_area/zytk35/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='zytk35'
*.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=zytk35XDB)'
*.job_queue_processes=1000
*.memory_target=783286272
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=primary
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)' #列出所有主备库的db_unique_nam
LOG_ARCHIVE_DEST_1='LOCATION=/OracleArch/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_FORMAT=%s_%t_%r.arc
FAL_SERVER=standby #当备库切换到主库的DB_UNIQUE_NAME
#DB_FILE_NAME_CONVERT='standby','primary' #如数据文件路径不一致，则需加该参数，##本地库在后#LOG_FILE_NAME_CONVERT='/OracleData/standby/','/OracleData/primary/' #如 STANDBY_FILE_MANAGEMENT=AUTO #自动
红色标注：作为主库是需要的参数
绿色标注：当该主库切换为备库时需要的参数
LOG_ARCHIVE_DEST_2：几种写法
LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
SERVICE=standby ：standby表示备库服务器的服务名，一般和DB_UNIQUE_NAME一致
ASYNC:
VALID_FOR:
6、主库生成密钥文件（DBCA默认会创建，可省略）
[oracle@dg1 dbs]$ orapwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwzytk35 password=310012 entries=5
7、拷贝所需文件到备库(参数文件、密码文件)
[oracle@dg1 dbs]$ cd $ORACLE_HOME/dbs
scp orapwzytk35 dg2: $ORACLE_HOME/dbs
scp initzytk35.ora dg2: $ORACLE_HOME/dbs
8、修改备库参数文件
cat $ORACLE_HOME/dbs/ initzytk35.ora
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/OracleData/zytk35/control01.ctl','/u01/app/oracle/fast_recovery_area/zytk35/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='zytk35'
*.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=zytk35XDB)'
*.job_queue_processes=1000
*.memory_target=783286272
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=standby ##
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1='LOCATION=/OracleArch/standbylog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2='SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_FORMAT=%s_%t_%r.arc
FAL_SERVER=primary
#DB_FILE_NAME_CONVERT='standby','primary'
#LOG_FILE_NAME_CONVERT='/OracleData/standby/','/OracleData/primary/'
STANDBY_FILE_MANAGEMENT=AUTO
9、创建备库（此有多种方法实现）
9.1、使用ACTIVE DUPLUCATE从主库复制备库数据库
在备库执行
[oracle@dg2 dbs]$ sqlplus / as sysdba
create spfile from pfile;
startup nomount;
[oracle@dg2 ~]$ rman target sys/310012@primary auxiliary sys/310012@standby
duplicate target database for standby nofilenamecheck from active database;
如果主备库文件存放位置不一样，则需要使用db_file_name_convert和log_file_name_convert参数，且nofilenamecheck参数弃掉
此步：也可以通过主库的全备来进行复制
9.2、基于主库全备创建备库数据库
9.2.1 全备主库
主库执行：
RMAN> backup database format '/OracleBackup/full_%d_%I_%T_%u.dbf' plus archivelog format '/OracleBackup/arch_%d_%T_%u.bak';
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database create standby controlfile as '/OracleBackup/standby_20150429.ctl';
9.2.2 拷贝全备文件到备库
把主库生成全备文件、归档日志文件以及备份的控制文件复制到备库（最好都同一路径，否则需要catalog 更新控制文件备份信息）
cd /OracleBackup/
scp * dg1:/OracleBackup/
9.2.3 基于全备的复制
[oracle@dg1 ~]$ rman target sys/310012@primary auxiliary sys/310012@standby
RMAN> duplicate target database for standby nofilenamecheck;
9.3 拷贝数据文件创建备库
9.3.1 创建备库控制文件
alter database create standby controlfile as '/home/oracel/control01.ctl';
9.3.2主库一致性关闭后，拷贝数据文件、日志文件、standby控制文件
10、开启备库同步
非active dataguard模式，启用备用数据库
SQL > STARTUP NOMOUNMT;
SQL >alter database mount standby database;
SQL >alter database recover managed standby database disconnect from session;
或者
备库置于active dataguard模式下
alter database open ; ----默认是以read only模式打开数据库
SQL> alter database recover managed standby database using current logfile disconnect from session;
using current logfile disconnect from session和disconnect from session区别：前者会实时同步日志，即使该日志没有归档，也会应用到备库；而后者必须要归档后才会应用到备库
SQL> alter database recover managed standby database delay 5 disconnect from session;
----延迟应用日志，单位为分钟
11、检查DATAGUARD是否工作正常
11.1查看备库接受的日志及是否应用
SQL> select NAME,DEST_ID,THREAD#,SEQUENCE#,APPLIED from v$archived_log order by sequence#;
11.2查看数据库的角色及保护模式
主库
SQL> select open_mode,database_role,protection_mode,protection_level from v$database;
OPEN_MODE DATABASE_R PROTECTION_MODE PROTECTION_LEVEL
--------------- ---------- -------------------- ----------------------------------------
READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
备库
SQL> select open_mode,database_role,protection_mode,protection_level from v$database;
OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
-------------------- ------------------ ------------------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
11.3主库和备库日志序列检查
alter system archive log all; ----检查归档日志是否传输
archive log list;
11.4 关闭备用数据库
SQL> alter database recover managed standby database cancel;
备库仍旧会接受日志，但不会应用到实例
二、主备库角色转换
1、switchover角色转换
主服务器
SQL >alter database commit to switchover to physical standby; ---运行后数据库会先关闭
SQL >startup nomount;
SQL >alter database mount standby database; ----standby database 新版本可以省略
SQL >alter database recover managed standby database disconnect from session
或者切换到active dataguard
alter database recover managed standby database using current logfile disconnect from session;
物理备服务器
SQL >alter database commit to switchover to primary ;
SQL >shutdown immediate;
SQL >startup
2、failover角色转换
如果备库运行在maximum protection模式下，需修改成MAXIMIZE PERFORMANCE。
- 如果主库能启动到mount状态，则可把日志刷到备库,最大程度保证数据的不丢失。
start mount;
alter system flush redo to 'standbydg'; ---11G
注意：10G版本需要手工拷贝日志文件和归档文件到原备库。
然后手工加载到备库数据字典
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1'; ---备库
- 再检查备库的日志间隙和最近的归档日志文件，如未发现，则意味着此次数据未丢失。如果发现间隙，则重复3步骤。
SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap; ---间隙
no rows selected
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
- alter database recover managed standby database cancel; --原备库
- alter database recover managed standby database finish; --原备库 结束不了则加force
- 如果间隙问题解决不了，但仍需切换到主库角色,则先执行下面语句，然后执行切换到主库
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
- ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
三、常用语句
SELECT NAME, SEQUENCE#, ARCHIVED, APPLIED FROM V$ARCHIVED_LOG;
select * from v$archive_gap;
select dest_id,DEST_NAME,STATUS,TYPE,DATABASE_MODE,recovery_mode from v$archive_dest_status;
select * from v$dataguard_status;
select * from v$archive_lag;
ALTER DATABASE REGISTER LOGFILE '/physical_standby1/thread1_dest/arcr_1_7.arc';
四、备份归档策略
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY
alter system set “_log_deletion_policy”=’ALL’ scope=spfile;
REPORT OBSOLETE;
delete OBSOLETE;
四、ORACLE DATAGUARD BROKER
条件（包括OEM和DGMGRL）：
1、主备库数据库必须使用相同的版本,且版本必须大于10G
2、必须使用SPFILE
3、DG_BROKER_START初始化参数必须设置为TRUE
4、在RAC环境中必须设置DG_BROKER_CONFIG_FILEn
5、TNS_ADMIN环境变量添加
6、静态监听添加，RAC环境则ASM实例和RDBMS实例都需要添加
DG_BROKER_CONFIG_FILE1必须在BROKER未启动状态修改（DG_BROKER_START=FALSE）
SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;
---必须DG_BROKER_START=FALSE，才能改DG_BROKER_CONFIG_FILE1
----file1文件名官方建议drc<db_unqiue_name>.dat
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='/OracleData/drcprimary01.dat';
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='/OracleData/drcstandby01.dat';
LISTENER = (DESCRIPTION =
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)
(PORT=port_num))))
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)
(GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
(ORACLE_HOME=oracle_home)
(ENVS="TNS_ADMIN=oracle_home/network/admin")))
96
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
