oracle物理dg单实例配置:
1.查看数据库版本是否一致
2.配置主备库的hosts文件和tnsnames.ora,确保监听正常。
3.主库参数配置:
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri_unique_name,std_unique_nm)' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/data/arch/pri_sid_nm VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri_unique_nm' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=std_tnsname ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std_unique_nm' 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 LOG_ARCHIVE_MAX_PROCESSES=4 scope=spfile;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;
ALTER SYSTEM SET FAL_CLIENT ='pri_tnsname' SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER ='std_tnsname' SCOPE=SPFILE;
-------------若主备库数据日志文件路径不一致需要设置,主库设置是为主备切换用-------------
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/data/oradata/MCPDATA_ST','/data/oradata/MCPDATA' scope=spfile;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/data/oradata/MCPDATA_ST','/data/oradata/MCPDATA' scope=spfile; 重做日志(redo log)目录,先对方,后自己
---------------------------------------------------------------
4.开启强制归档:
alter database force logging;
5.主库若没开归档需要开启:
shutdown immediate
startup mount
alter database archivelog;
alter database open;
6.主库备份并恢复到备库
创建口令文件:
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=5
创建spfile:
create pfile from spfile;
mount状态下,使用rman备份数据库和归档日志
rman target /
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup database format '/data/rman_backup/Full_%U.bak';
backup archivelog all format '/data/rman_backup/Arc_%U.bak';
release channel c1;
release channel c2;
release channel c3;}
备份备库控制文件
backup device type disk format '/data/rman_backup/Standby_%U.ctl' current controlfile for standby;
将主库的备份集、口令文件、pfile传至备库相应目录下
备库配置:
7.配置备库参数文件:
各中文件路径确定修改正确,包括数据文件路径,归档日志路径
db_unique_name修改成备库的唯一名
LOG_ARCHIVE_DEST_1修改成备库本机的log目录,LOG_ARCHIVE_DEST_2修改成主库参数 ----------主备切换用
FAL_SERVER修改成主库连接串,FAL_CLIENT修改成备库连接串 --------主备切换会用到
----------------若主备库数据日志文件路径不一致备库需要需要设置下面两个参数----------------------
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/data/oradata/MCPDATA_ST','/data/oradata/MCPDATA' scope=spfile;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/data/oradata/MCPDATA_ST','/data/oradata/MCPDATA' scope=spfile; -----重做日志(redo log)目录,先对方,后自己
-----------------------------------------------------------------------------------------------
查看Oracle的.bash_profile文件里ORACLE_SID配置是否正确
8.创建spfile文件,并将备库置于nomount状态
create spfile from pfile;
startup nomount;
show parameter spfile;
9.使用rman恢复控制文件和数据文件等:
rman target /
restore standby controlfile from '/data/rman_backup/Standby_08ri8i4r_1_1.ctl';
alter database mount;
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
restore database;
release channelc1;
release channelc2;
release channelc3; }
===========================duplicate方式迁移数据(主库不用停机)===========================
创建口令文件----把主库的删掉,修改sys密码后重新生成一份,把这份传到备库:
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys的密码 entries=5
将主库的pfile文件传到备库修改后生成spfile,将备库起到nomount
rman target sys/wgot11g@BJ-YZ-207R-166-9 auxiliary sys/wgot11g@BJ-YZ-207R-166-8
duplicate target database for standby from active database dorecover nofilenamecheck;
======================================================================================
10.主库和备库状态查看:
column db_unique_name format a10;
set line 220;
SELECT db_unique_name,name,switchover_status,database_role,open_mode FROM v$database;
11.主库和备库创建Standby Redo Log组数(主库创建是为主备切换用)
公式如下:
如果主库是单实例库:Standby Redo Log组数=主库日志组总数 + 1
如果主库是RAC环境:Standby Redo Log组数=(所有节点中日志组数最大值 + 1) * RAC节点数
alter database add standby logfile thread 1 group 4 '/data/oradata/fle/stdredo04.rdo' size 200m;
alter database add standby logfile thread 1 group 5 '/data/oradata/fle/stdredo05.rdo' size 200m;
alter database add standby logfile thread 1 group 6 '/data/oradata/fle/stdredo06.rdo' size 200m;
alter database add standby logfile thread 1 group 7 '/data/oradata/fle/stdredo07.rdo' size 200m;
12.检查:
主库查看路径状态:
col dest_name for a20
col status for a8
col type for a8
col database_mode for a20
col recovery_mode for a25
col error for a20
col destination for a40
col synchronization_status for a30
set line 240
select dest_name, status, type, database_mode, recovery_mode, error, destination, synchronization_status
from v$archive_dest_status;
主库切换日志:
alter system switch logfile;
备库查看日志的应用状态:
column name format a50;
set line 200;
SELECT thread#,sequence#,name,applied FROM v$archived_log order by sequence#;
查看日志传输进程状态
col RECOVERY_MODE for a8
col protection_mode for a20
col db_mode for a18
col status for a10
set line 200
SELECT ds.dest_id id ,
ad.status ,
ds.database_mode db_mode ,
ad.archiver TYPE ,
ds.recovery_mode ,
ds.protection_mode ,
ds.standby_logfile_count "SRLs" ,
ds.standby_logfile_active active ,
ds.archived_seq#
FROM v$archive_dest_status ds ,
v$archive_dest ad
WHERE ds.dest_id = ad.dest_id
AND ad.status != 'INACTIVE'
ORDER BY ds.dest_id;
主备切换:
1.将主库切换成物理standby:
alter database commit to switchover to physical standby with session shutdown;
startup mount;
2.备库执行切换成primary,完成后数据库是mount,需要open:
recover managed standby database cancel;
recover automatic standby database ;
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
alter database commit to switchover to primary with session shutdown;
select status from v$instance;
startup;
3.打开新的备库(原主库)的归档应用:
alter database recover managed standby database using current logfile disconnect from session;
检查日志应用状态:
4.验证切换后,归档应用是否正常:
新备库执行:
column name format a50;
set line 200;
SELECT thread#,sequence#,name,applied FROM v$archived_log order by sequence#;
5.检查切换后的主备库的角色:
新的主备库分别执行:
select switchover_status,database_role from v$database;
注意切换完成以后必须自行添加临时文件,当我们在Primary端添加一个临时表空间,Standby端虽然不能创建出临时文件,但是临时表空间的信息是联动的带入的
oracle dataguard主备库参数文件配置详解
主库参数
fle.__db_cache_size=3070230528
fle.__java_pool_size=67108864
fle.__large_pool_size=83886080
fle.__oracle_base='/apps/oracle'#ORACLE_BASE set from environment
fle.__pga_aggregate_target=1073741824
fle.__sga_target=4294967296
fle.__shared_io_pool_size=0
fle.__shared_pool_size=973078528
fle.__streams_pool_size=0
*.audit_file_dest='/apps/oracle/admin/fle/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/oradata/fle/control01.ctl','/data/oradata/fast_recovery_area/fle/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='fle'
*.db_recovery_file_dest='/data/oradata/fast_recovery_area'
*.db_recovery_file_dest_size=4294967296
*.diagnostic_dest='/apps/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=fleXDB)'
*.event='10949 TRACE NAME CONTEXT FOREVER'
*.fal_client='BJ-YZ-207R-66-9'
*.fal_server='BJ-YZ-207R-66-8'
*.log_archive_config='DG_CONFIG=(fle,flp)'
*.log_archive_dest_1='LOCATION=/data/arch/fle VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=fle'
*.log_archive_dest_2='SERVICE=BJ-YZ-207R-66-8 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=flp'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=4294967296
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
备库参数
fle.__db_cache_size=2399141888
fle.__java_pool_size=67108864
fle.__large_pool_size=822083584
fle.__oracle_base='/apps/oracle'#ORACLE_BASE set from environment
fle.__pga_aggregate_target=1073741824
fle.__sga_target=4294967296
fle.__shared_io_pool_size=0
fle.__shared_pool_size=905969664
fle.__streams_pool_size=0
*.audit_file_dest='/apps/oracle/admin/fle/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/oradata/fle/control01.ctl','/data/oradata/fast_recovery_area/fle/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='fle'
*.db_recovery_file_dest='/data/oradata/fast_recovery_area'
*.db_recovery_file_dest_size=4294967296
*.db_unique_name='flp'
*.diagnostic_dest='/apps/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=fleXDB)'
*.event='10949 TRACE NAME CONTEXT FOREVER'
*.fal_client='BJ-YZ-207R-66-8'
*.fal_server='BJ-YZ-207R-66-9'
*.log_archive_config='DG_CONFIG=(flp,fle)'
*.log_archive_dest_1='LOCATION=/data/arch/fle VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=flp'
*.log_archive_dest_2='SERVICE=BJ-YZ-207R-66-9 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=fle'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=4294967296
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
主库参数详解:
保持同一个Data Guard中所有的DB_NAME相同
DB_NAME=ora11g
为一个数据库指定一个唯一的名称,该参数一经指定就不会发生改动除非DBA主动改动
DB_UNIQUE_NAME=ora11g_primary
初始化参数LOG_ARCHIVE_CONFIG用于控制发送归档日志到远程位置、接收远程归档日志,并指定Data Guard配置的惟一数据库名,默认值为SEND,RECEIVE,NODG_CONFIG。
当设置该参数为SEND时,会激活发送归档日志到远程位置;当设置该能数为NOSEND时,会禁止发送归档日志到远程位置;当设置该参数为RECEIVE时,会激活接收远程归档日志;当设置该参数
为NORECEIVE时,会禁止接收远程归档日志;当设置该参数为DG_CONFIG时,可以最多指定9个惟一数据库名;当设置该参数为NODG_CONFIG时,会禁止指定惟一数据库名。
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ora11g_primary ,ora11g_standby)'
指定本地归档的路径
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11g_primary'
指定远端备库的归档路径
LOG_ARCHIVE_DEST_2='SERVICE=ora11g_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11g_standby'
LOG_ARCHIVE_DEST_n(从1到10)定义redo文件路径。该参数必须通过location或service指定归档文件路径。location表示本地路径,service通常是net service name,即接收redo数据的
standby数据库。
注意:每一个LOG_ARCHIVE_DEST_n都有一个对应的LOG_ARCHIVE_DEST_STATE_n参数,该参数拥有以下4个属性值:
ENABLE:默认值,表示允许传输服务
DEFER: 指定对应的log_archive_dest_n参数有效,但暂不使用
ALTERNATE:禁止传输,但是如果其他相关的目的地的连接通通失败,则它将变成enable
RESET:功能与DEFER类似,不过如果传输目的地之前有过错误,它会清除所有错误信息
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
远程登录设置独享模式(z注意保证主备库密码相同)
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
%s log sequence number
%S log sequence number, zero filled %t thread number, RAC的节点中设有THREAD参数, 就是这个值了
%T thread number, zero filled
------其中这前4项是可以用在 9I 中的
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
Oracle 10g开始,log_archive_format初始化参数必须包含%s, %t 和 %r,以确保生成的归档重做日志名称唯一。
(原因:10g能做穿越resetlog的恢复,所以要加%r.而9i的不能做穿越resetlog的恢复,所以没有%r的参数)
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
当Primary Database的某些日志没有发送到Standby Database 这时候发生归档裂缝(Archive Gap)
缺失的日志就是裂缝(GAP)。Data Guard 能够自动检测,解决归档裂缝,不需要DBA的介入。这需要配置
FAL_CLIENT,FAL_SERVER这两个参数(FAL:Fetch Archive Log)
从FAL这个名字可以看出,这个过程是 Standby Database 主动发起的取日志的过程,Standby Database 就是
FAL_CLIENT,它是从FAL_SERVER中取这些GAP。10g中,这个FAL_SERVER可以是Primary Database,也可以是其他的
Standby Database
FAL_CLIENT和FAL_SERVER两个参数都是Oracle Net NameFAL_CLIENT通过网络向FAL_SERVER发送请求,
FAL_SERVER通过网络向FAL_CLIENT发送缺失的日志
但是这两个连接不一定是一个连接。因此 FAL_CLIENT向FAL_SERVER发送请求时,会携带FAL_CLIENT的参数
值,用来告诉FAL_SERVER应该向哪里发送缺少的日志。这个参数也是一个Oracle Net Name 这个Name在FAL_SERVER
上定义的,用来指向FAL_CLIENT
FAL_SERVER=ora11g_standby
FAL_CLIENT=ora11g_primary
如果Primary数据库数据文件发生修改(如新建)则在standby数据库作相应修改
设为AUTO表示自动管理。设为MANUAL表示需要手工管理(注:重命名修改
是不会传到standby数据库)
STANDBY_FILE_MANAGEMENT=AUTO