Oracle11g 搭建单实例DataGuard.docx
《Oracle11g 搭建单实例DataGuard.docx》由会员分享，可在线阅读，更多相关《Oracle11g 搭建单实例DataGuard.docx（16页珍藏版）》请在冰豆网上搜索。
Oracle11g搭建单实例DataGuard
环境：
主备库都为单实例并且数据库SID相同
OS：
redhat6.5
Oracle:
11.2.0.4.3
主库操作
1. 开启归档模式
创建归档目录
[root@enn~]#mkdir-p/u01/archivelog
[root@enn~]#chown-Roracle:
oinstall/u01/archivelog
[root@enn~]#chmod777/u01/archivelog
数据库到mount状态开启归档模式
[root@enn~]#su-oracle
[oracle@enn~]$sqlplus/assysdba
SQL*Plus:
Release11.2.0.4.0ProductiononSunJul2023:
46:
462014
Copyright（c）1982,2013,Oracle. Allrightsreserved.
Connectedtoanidleinstance.
SQL>startupmount
ORACLEinstancestarted.
TotalSystemGlobalArea 835104768bytes
FixedSize 2257840bytes
VariableSize 541068368bytes
DatabaseBuffers 289406976bytes
RedoBuffers 2371584bytes
Databasemounted.
设置主库归档目录
SQL>altersystemsetlog_archive_dest='/u01/archivelog';
altersystemsetlog_archive_dest_1='location=/data/oracle/log1/archive_log'; 最后的目录名称需要为archive_log
开启归档模式
SQL>alterdatabasearchivelog;
Databasealtered.
查看归档设置
SQL>archiveloglist;
Databaselogmode ArchiveMode
Automaticarchival Enabled
Archivedestination /u01/archivelog
Oldestonlinelogsequence 2
Nextlogsequencetoarchive 4
Currentlogsequence 4
2. 启动force_logging模式
查看是否force_logging模式
SQL>selectlog_mode,force_loggingfromv$database;
LOG_MODE FOR
---------------
ARCHIVELOG NO
开启force_logging模式
SQL>alterdatabaseforcelogging;
Databasealtered.
3.创建备库日志文件路径
查看数据库的日志组个数与大小，因为我们创建 standby 日志组的个数是原日志
组个数+1 再与 thread 的积（（2+1）*3），size 不能小于原日志文件的大小。
SQL>selectgroup#,thread#,bytes/1024/1024M,STATUSfromv$log;
GROUP# THREAD# MSTATUS
----------------------------------------------
1 1 50CURRENT
3 1 50INACTIVE
2 1 50INACTIVE
SQL>selectmemberfromv$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ENN/redo03.log
/u01/app/oracle/oradata/ENN/redo02.log
/u01/app/oracle/oradata/ENN/redo01.log
创建备库日志组路径
SQL>alterdatabaseaddstandbylogfilethread1group4（'/u01/app/oracle/oradata/ENN/redo04.log'）size50M;
Databasealtered.
SQL>alterdatabaseaddstandbylogfilethread1group5（'/u01/app/oracle/oradata/ENN/redo05.log'）size50M;
Databasealtered.
SQL>alterdatabaseaddstandbylogfilethread1group6（'/u01/app/oracle/oradata/ENN/redo06.log'）size50M;
Databasealtered.
SQL>alterdatabaseaddstandbylogfilethread1group7（'/u01/app/oracle/oradata/ENN/redo07.log'）size50M;
Databasealtered.
查看状态
SQL>selectgroup#,status,type,memberfromv$logfile;
GROUP#STATUS TYPE MEMBER
----------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/ENN/redo03.log
2 ONLINE /u01/app/oracle/oradata/ENN/redo02.log
1 ONLINE /u01/app/oracle/oradata/ENN/redo01.log
4 STANDBY/u01/app/oracle/oradata/ENN/redo04.log
5 STANDBY/u01/app/oracle/oradata/ENN/redo05.log
6 STANDBY/u01/app/oracle/oradata/ENN/redo06.log
7 STANDBY/u01/app/oracle/oradata/ENN/redo07.log
4.创建监听
执行netca创建监听器
[oracle@ennoracle]$netca
修改tnsname文件
[oracle@ennoracle]$cd$ORACLE_HOME/network/admin
[oracle@ennadmin]$vimtnsname.ora
ENN=
（DESCRIPTION=
（ADDRESS_LIST=
（ADDRESS=（PROTOCOL=TCP）（HOST=192.168.80.15）（PORT=1521））
）
（CONNECT_DATA=
（SERVICE_NAME=enn）
）
）
ENN_DG=
（DESCRIPTION=
（ADDRESS_LIST=
（ADDRESS=（PROTOCOL=TCP）（HOST=192.168.80.16）（PORT=1521））
）
（CONNECT_DATA=
（SERVICE_NAME=enn_dg）
）
）
tnsname.ora复制到备库中
[oracle@ennadmin]$scptnsname.oraoracle@192.168.80.16:
$ORACLE_HOME/network/admin/tnsname.ora
oracle@192.168.80.16'spassword:
tnsname.ora 100% 361 0.4KB/s 00:
00
注：
可以使用图形配置tnsname
[oracle@ennadmin]$netmgr
附：
如果备库tnsping不通, 关闭防火墙
[oracle@enn_dg~]$tnspingenn
TNSPingUtilityforLinux:
Version11.2.0.4.0-Productionon21-JUL-201409:
26:
09
Copyright（c）1997,2013,Oracle. Allrightsreserved.
Usedparameterfiles:
UsedTNSNAMESadaptertoresolvethealias
Attemptingtocontact（DESCRIPTION=（ADDRESS_LIST=（ADDRESS=（PROTOCOL=TCP）（HOST=192.168.80.15）（PORT=1521）））（CONNECT_DATA=（SERVICE_NAME=ENN）））
TNS-12543:
TNS:
destinationhostunreachable
[root@enn~]#chkconfigiptablesoff
[root@enn~]#serviceiptablesstop
5. 设置主库和备库归档路径
设置主库归档路径
SQL>altersystemsetlog_archive_dest='';
Systemaltered.
SQL>altersystemsetlog_archive_dest_1='LOCATION=/u01/archivelogVALID_FOR=（ALL_LOGFILES,ALL_ROLES）DB_UNIQUE_NAME=ENN';
Systemaltered.
设置备库归档路径
SQL>altersystemsetlog_archive_dest_2='SERVICE=enn_dg asyncVALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE）DB_UNIQUE_NAME=ENN';
Systemaltered.
配置归档最大进程数
SQL>showparameterlog_archive_max
NAME TYPEVALUE
-----------------------------------------------------------------------------
log_archive_max_processes integer 4
SQL>altersystemsetlog_archive_max_processes=30;
Systemaltered.
6. 配置参数文件
生成参数文件
SQL>createpfile='/u01/app/oracle/initENN.ora'fromspfile;
Filecreated.
修改参数文件
[oracle@ennoracle]$viminitENN.ora
DB_UNIQUE_NAME=ENN
#LOG_ARCHIVE_CONFIG='DG_CONFIG=（ENN,ENN_DG）'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=ENN_DG
FAL_CLIENT=ENN
STANDBY_FILE_MANAGEMENT=AUTO
7. 配置密码文件
备份standby库参数文件和密码文件
[oracle@ennpfile]$cd$ORACLE_HOME/dbs
[oracle@enndbs]$cporapwENNorapwENN.back
[oracle@enndbs]$cpspfileENN.oraspfileENN.ora.bak
将参数文件和密码文件传到备库
[oracle@ennoracle]$scpinitENN.oraoracle@192.168.80.16:
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora
[oracle@ennoracle]$scp/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENN oracle@192.168.80.16:
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENN
备库操作
关闭防火墙
[root@enn_dg~]#chkconfigiptablesoff
[root@enn_dg~]#serviceiptablesstop
图形方式创建监听器，同主库操作相同
[oracle@enn~]$netca
注：
可以使用图形方式配置tnsname.ora
[oracle@enn~]$netmgr
启动备库到nomount
SQL> startupnomountpfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora';
Duplicate复制主库到备库
用rman连接主库和备库
[oracle@enn~]$rmantargetsys/oracle@ENNauxiliarysys/oracle@ENN_DG
RecoveryManager:
Release11.2.0.4.0-ProductiononMonJul2111:
02:
392014
Copyright（c）1982,2011,Oracleand/oritsaffiliates. Allrightsreserved.
connectedtotargetdatabase:
ENN（DBID=4141660501）
connectedtoauxiliarydatabase:
ENN（notmounted）
执行同步复制备库操作
RMAN>duplicatetargetdatabaseforstandbyfromactivedatabasespfilesetdb_unique_name'ENN';
出现错误：
sqlstatement:
alterdatabasemountstandbydatabase
RMAN-05538:
WARNING:
implicitlyusingDB_FILE_NAME_CONVERT
RMAN-00571:
===========================================================
RMAN-00569:
===============ERRORMESSAGESTACKFOLLOWS===============
RMAN-00571:
===========================================================
RMAN-03002:
failureofDuplicateDbcommandat07/21/201411:
15:
58
RMAN-05501:
abortingduplicationoftargetdatabase
RMAN-05001:
auxiliaryfilename/u01/app/oracle/oradata/ENN/users01.dbfconflictswithafileusedbythetargetdatabase
RMAN-05001:
auxiliaryfilename/u01/app/oracle/oradata/ENN/undotbs01.dbfconflictswithafileusedbythetargetdatabase
RMAN-05001:
auxiliaryfilename/u01/app/oracle/oradata/ENN/sysaux01.dbfconflictswithafileusedbythetargetdatabase
RMAN-05001:
auxiliaryfilename/u01/app/oracle/oradata/ENN/system01.dbfconflictswithafileusedbythetargetdatabase
附：
a、在异机克隆时，如果auxiliaryDB使用了与targetDB相同的磁盘配置，目录结构以及文件名时，必须指定NOFILENAMECHECK。
NOFILENAMECHECK可以阻止检查targetDB的数据文件及联机日志文件是否处于正常使用的状态。
而auxiliaryDB与targetDB的磁盘配置，目录结构以及文件名任一不同时，应避免使用NOFILENAMECHECK。
b、对于没有连接到targetDB或catalog的情形，应使用BACKUPLOCATION''指定备份文件所在的位置。
继续执行duplicate（注：
Duplicate时如果主库与备库文件目录结构与文件名都相同时需要使用nofilenamecheck参数）
RMAN>duplicatetargetdatabaseforstandbyfromactivedatabasespfilesetdb_unique_name'ENN'
nofilenamecheck;
出现问题：
StartingDuplicateDbat21-JUL-14
RMAN-00571:
===========================================================
RMAN-00569:
===============ERRORMESSAGESTACKFOLLOWS===============
RMAN-00571:
===========================================================
RMAN-03002:
failureofDuplicateDbcommandat07/21/201411:
35:
59
RMAN-05501:
abortingduplicationoftargetdatabase
RMAN-05537:
DUPLICATEwithoutTARGETconnectionwhenauxiliaryinstanceisstartedwithspfilecannotuseSPFILEclause
Mos☹（备库必需用主库传过来的pfile启动到nomount状态，才能执行duplicate）
CAUSE:
ADUPLICATEwasattemptedwhentheauxiliarydatabasewasstartedwithaserverparameterfileandtheSPFILEsub-clausewasspecifiedinDuplicatesyntax.
RMANcannotrestoretheserverparameterfileiftheauxiliarydatabaseisalreadystartedwithaserverparameterfile.
SOLUTION:
Starttheauxiliarydatabasewithaclientparameterfile（pfile）or DonotspecifySPFILEsub-clauseandretry.
启动备库到nomount是使用pfile
SQL>startupnomountpfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora';
重新同步复制
RMAN>duplicatetargetdatabaseforstandbyfromactivedatabasespfilesetdb_unique_name'ENN'nofilenamecheck;
报错但正常完成复制☹
ORACLEerrorfromauxiliarydatabase:
ORA-19527:
physicalstandbyredologmustberenamed
ORA-00312:
onlinelog1thread1:
'/u01/app/oracle/oradata/ENN/redo01.log'
RMAN-05535:
WARNING:
Allredologfileswerenotdefinedproperly.
ORACLEerrorfromauxiliarydatabase:
ORA-19527:
physicalstandbyredologmustberenamed
ORA-00312:
onlinelog2thread1:
'/u01/app/oracle/
升级会员 