OracleDataGuard部署

---

配置 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 $ORACLE_BASE mkdir -p admin/`$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临时文件，数据库在打开的时候会自动创建。