配置 DG 详细步骤:
–====Primary端:
1、
主库全备:
rman > backup database format ‘/tmp/nsp_%U.full’;
2、
备份主库参数文件 nsp.init,编辑
SQL> create pfile=’/tmp/nsp.init’ from spfile;
3、
创建备库控制文件
SQL>alter database create standby controlfile as ‘/tmp/nsp_sty.ctl’;
4、
备库目录初始化:
cd
ORACLEBASEmkdir−padmin/‘
O
R
A
C
L
E
B
A
S
E
m
k
d
i
r
−
p
a
d
m
i
n
/
‘
ORACLE_SID
cd $ORACLE_BASE
mkdir diag diag/rdbms diag/tnslsnr
cd admin/$ORACLE_SID
mkdir adump pfile bdump cdump udump
5、主库配置TNS
cat11g =(description =(address = (protocol = tcp)(host = 10.200.8.35)(port = 1528))(connect_data = (sid = cat11g)))
nsp=(description =(address = (protocol = tcp)(host = 10.198.217.153)(port = 1528))(connect_data = (sid = nsp)))
lnsp=(description =(address = (protocol = tcp)(host = 25.8.1.33)(port = 1528))(connect_data = (sid = nsp)))
—-主库配置:
alter system set log_archive_config = ‘DG_CONFIG=(nsp,lnsp)’ scope = both;
配置log_archive_dest_n:
alter system set log_archive_dest_2 = ‘SERVICE=lnsp lgwr async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=lnsp’ scope = both;
配置fal:
alter system set fal_server = lnsp scope = both;
alter system set fal_client = nsp scope = both;
修改standby_file_management = auto
alter system set standby_file_management = auto scope = both;
检查:
select process,status,thread#,sequence#,block#,blocks from v$managed_standby
where process!=’ARCH’;
select name,SEQUENCE#,ARCHIVED,APPLIED,STATUS,to_char(COMPLETION_TIME,’yyyy-mm-dd hh24:mi:ss’) data from v$archived_log order by data;
–====Standby端nomount:
sqlplus ‘/as sysdba’
SQL> startup nomount pfile=’$ORACLE_HOME/dbs/nsp.init’
cp /tmp/nsp_sty.ctl /crbank/nsp/data/nsp/control01.ctl
SQL> alter databae mount;
2、备库增加standby redologfile:
alter database add standby logfile group 9 ‘/crbank/nsp/data/nsp/redolog09.log’ size 1024m;
alter database add standby logfile group 10 ‘/crbank/nsp/data/nsp/redolog10.log’ size 1024m;
alter database add standby logfile group 11 ‘/crbank/nsp/data/nsp/redolog11.log’ size 1024m;
alter database add standby logfile group 12 ‘/crbank/nsp/data/nsp/redolog12.log’ size 1024m;
alter database add standby logfile group 13 ‘/crbank/nsp/data/nsp/redolog13.log’ size 1024m;
alter database add standby logfile group 14 ‘/crbank/nsp/data/nsp/redolog14.log’ size 1024m;
alter database add standby logfile group 15 ‘/crbank/nsp/data/nsp/redolog15.log’ size 1024m;
alter database add standby logfile group 16 ‘/crbank/nsp/data/nsp/redolog16.log’ size 1024m;
alter database add standby logfile group 17 ‘/crbank/nsp/data/nsp/redolog17.log’ size 1024m;
alter database add standby logfile group 18 ‘/crbank/nsp/data/nsp/redolog18.log’ size 1024m;
select group#,status,bytes/1024/1024 from v$standby_log;
select ‘set newname for datafile ‘||file#||’ to ”’|| ‘/crbank/nsp/data/nsp/’||substr(name,instr(name,’/’,1,5)+1)||”” from v$datafile
;
select ‘set newname for datafile ‘||file#||’ to ”’|| ‘/crbank/nsp/data/nsp/’||substr(name,instr(name,’/’,1,5)+1)||”” from v$tempfile ;
3、备库恢复:
rman target /
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
restore database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
4、备库配置监听:
lnsp =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 25.8.1.33)(PORT = 1528))
)
)
)
备库配置TNS:
cat11g =(description =(address = (protocol = tcp)(host = 10.200.8.35)(port = 1528))(connect_data = (sid = cat11g)))
nsp=(description =(address = (protocol = tcp)(host = 10.198.217.153)(port = 1528))(connect_data = (sid = nsp)))
lnsp=(description =(address = (protocol = tcp)(host = 25.8.1.33)(port = 1528))(connect_data = (sid = nsp)))
备库DG配置
alter system set log_archive_dest_2 = ‘SERVICE=nsp lgwr async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=nsp’ scope = both;
alter system set fal_server = nsp scope = both;
alter system set fal_client = lnsp scope = both;
alter system set log_archive_config = ‘DG_CONFIG=(nsp,lnsp)’ scope = both;
alter system set db_unique_name=lnsp scope=both;
alter system set service_names = nsp scope= spfile;
alter system set standby_file_management = auto scope = both;
alter system set db_recovery_file_dest_size = 32G scope=spfile;
alter system set db_recovery_file_dest= ‘/crbank/nsp/fra’ scope=spfile;
alter system set standby_archive_dest =” scope=spfile;
alter system set log_archive_dest_1 = ” scope=spfile;
alter system set open_links=0 scope=spfile;
alter system set open_links_per_instance=0 scope=spfile;
alter system set parallel_execution_message_size=32768 scope=spfile;
alter system set disk_asynch_io=TRUE scope=spfile;
alter system set db_writer_processes=4 scope=spfile;
alter database recover managed standby database cancel;
shutdown immediate;
startup mount;
alter database open;
alter database recover managed standby database using current logfile disconnect;
错误问题:
1、
alter database recover managed standby database using current logfile disconnect
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs
使用’using current logfile’ 需要创建stanby logfile
解决:
alter database add standby logfile group 10 ‘/crbank/nsp/data/nsp/redolog10.log’ size 1024m;
3、备库在打开的时候alert.log出现了大量的错误,检测到控制文件记录的redolog文件不存在问题
ORA-00313: open failed for members of log group 8 of thread 1
ORA-00312: online log 8 thread 1: ‘/crbank/nsp/data/nsp/redo08.log’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
select l.group#,l.SEQUENCE#,lg.member,l.status,lg.type from v$logfile
lg,v$log
l where lg.group#=l.group#;
alter database drop logfile group 2;
alter database clear unarchived logfile ‘/crbank/nsp/data/nsp/redo02.log’;
v$log有记录,但是物理文件不存在,删除或者清除都出问题。
解决:设置logfile_name_convert参数,重新打开备库就可以自动创建。
4、备库在打开的时候在告警日志报临时文件损坏,需要重新创建,但是创建失败
ORA-01186: file 1025 failed verification tests
ORA-01122: database file 1025 failed verification check
ORA-01110: data file 1025: ‘/crbank/nsp/data/nsp/temp01.dbf’
ORA-01205: not a data file - type number in header is 3
解决:物理删除掉temp临时文件,数据库在打开的时候会自动创建。