oracle 11g dataguard 配置管理

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:81

目录1. DataGuard配置条件... 22. 实施计划... 23. 判断DataGuard是否安装... 22. 网络配置:主库和备库... 24. 配置hosts文件:主库和备库... 35. 监听配置:主库和备库... 36. 修改主库备库到归档模式... 45. 创建主库口令文件... 57. 修改主库初始化参数pfile. 58. 创建备份库需要的控制文件... 79. 创建主库的spfile. 710. 备份生产数据库... 711. 修改备库初始化参数pfile. 812. 复制备库控制文件... 913. 在备库上创建口令文件... 914. 创建备库的spfile. 915. 启动物理备用数据库... 916. 配置Standby Redo Log. 917. Redo Apply的启动和停止... 101. DataGuard配置条件 DataGuard的备库有物理standby和逻辑standby两种,由于逻辑standby的局限性,生产环境选择物理standby作为DataGuard的备库。实现物理standby需要具备以下条件:1、 oralce数据库必须是企业版,企业版才支持DataGuard;2、 DataGuard中的数据库运行的平台必须相同;3、 DataGuard中的主库和备库必须运行于归档模式;4、 DataGuard中的主库和备库的db_name必须相同、db_unique_name必须不同;5、 使用具有相关sysdba权限的用户管理primary和standby数据库。2. 实施计划 对于在DataGuard实施的第一步的实施计划如下:
[td=130] 日期 [td=312]工作内容[td=85]备注
[td=130]2013-11-18[td=312][align=right]检查数据库是否满足配置要求[td=85]

[td=130]2013-11-19[td=312][align=right]配置DataGuard,并进行相关验证[td=85]

[td=130]2013-11-20[td=312][align=right]进行DataGuard管理,验证主备库切换[td=85]

3. 判断DataGuard是否安装 Sql>select* from v$option where parameter = 'Oracle Data Guard';返回结果为空,DG没有配置。2. 网络配置:主库和备库

[td=79] 主库 [td=489]操作系统版本:centos5.4 数据库版本: Database 11g Enterprise Edition Release 11.2.0.1.0 Ip地址:172.24.141.102 Db_name:standbyd Db_unique_name:standbyd instance_name:primary service_names:standbyd
[td=79]备库[td=489][align=right]操作系统版本:centos5.4 数据库版本: Database 11g Enterprise Edition Release 11.2.0.1.0 Ip地址:172.24.141.103 Db_name:standbyd Db_unique_name:standbydb instance_name:standby service_names:standbydb
4. 配置hosts文件:主库和备库 vi /etc/hosts# Do not remove the following line, orvarious programs# that require network functionality willfail.127.0.0.1 hlht-db1 localhost.localdomain localhost::1 localhost6.localdomain6 localhost610.52.151.102 hlht-db110.52.151.103 hlht-db25. 监听配置:主库和备库 主库:配置主库监听和tns:[oracle@ hlht-db1~]$ cd $ORACLE_HOME/network/admin[oracle@ hlht-db1admin]$ cat listener.ora
#listener.ora Network Configuration File:/opt/oracle/112/network/admin/listener.ora#Generated by Oracle configuration tools.#addSID_LIST_LISTENER= (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /opt/oracle/112) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = primary) (ORACLE_HOME = /opt/oracle/112) (SID_NAME = primary) ) )#addLISTENER= (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST =10.52.151.102)(PORT = 11731)) #(ADDRESS = (PROTOCOL = TCP)(HOST =172.24.141.102)(PORT = 11731)) ) )
[oracle@hlht-db1admin]$ cat tnsnames.ora # tnsnames.oraNetwork Configuration File: /opt/oracle/112/network/admin/tnsnames.ora#Generated by Oracle configuration tools.
STANDBYD =(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.52.151.102)(PORT = 11731)) #(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.141.102)(PORT = 11731)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary) ) )
STANDBYDB =(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.52.151.103)(PORT = 11731)) #(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.141.103)(PORT = 11731)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby) ) )备库:配置备库监听和tns:[oracle@ hlht-db2~]$ cd $ORACLE_HOME/network/admin[oracle@hlht-db2 admin]$ cat listener.ora # listener.ora Network Configuration File:/opt/oracle/112/network/admin/listener.ora# Generated by Oracle configuration tools.
SID_LIST_LISTENER =(SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /opt/oracle/112) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = standby) (ORACLE_HOME = /opt/oracle/112) (SID_NAME = standby) ) )
LISTENER =(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.52.151.103)(PORT = 11731)) #(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.141.103)(PORT = 11731)))[oracle@hlht-db2admin]$ cat tnsnames.ora #tnsnames.ora Network Configuration File: /opt/oracle/112/network/admin/tnsnames.ora#Generated by Oracle configuration tools.
STANDBYDB =(DESCRIPTION = #(ADDRESS = (PROTOCOL = TCP)(HOST = hlht-db2)(PORT = 1521)) #(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.141.103)(PORT = 11731)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.52.151.103)(PORT = 11731)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby) ) )
STANDBYD =(DESCRIPTION = #(ADDRESS = (PROTOCOL = TCP)(HOST = hlht-db2)(PORT = 1521)) #(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.141.102)(PORT = 11731)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.52.151.102)(PORT = 11731)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary) ) )6. 修改主库备库到归档模式 检查归档情况:Archive log list;
修改归档路径、归档文件夹大小、归档文件格式:sqlplus / as sysdbaSQL> alter system setlog_archive_dest_1='location=/data/archlog'; altersystem set db_recovery_file_dest_size = 10G; altersystem set log_archive_format='archivelog_%t_%s_%r' scope=spfile;修改归档SQL>shutdown immediate; startupmount; alterdatabase archivelog;启动数据库SQL> alter database open;
设置强制写日志:SQL> select FORCE_LOGGING from v$database;NOSQL>alter database force logging;SQL>select FORCE_LOGGING from v$database;YES 5. 创建主库口令文件 orapwd file=$ORACLE_HOME/dbs/orapwprimary password=XXXentries=10注:这里的file命名规则在unix/linux下orapw,在windows下为pwd.ora。主备库的sys用户密码相同7. 修改主库初始化参数pfile 创建主库pfilesql > create pfile from spfile;使用 createpfile from spfile创建的都放在默认的位置和默认的命名:Pfile:init.ora 与spfile相同路径。修改pfile[font="]DB_UNIQUE_NAME=standby#[font="] DB_UNIQUE_NAME主备数据库不能相同[font="]LOG_ARCHIVE_CONFIG='DG_CONFIG=(standby,standbydb)'#DataGuard专用配置,主备数据库相同,为两个库的db_unique_name[font="]LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= standby’#主数据库归档地址LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= standbydb’#备用数据库归档地址,由该配置指定向备用数据库写归档日志# SERVICE=standby LGWRASYNC AFFIRM:这3个参数影响dg的保护模式,一般这3个参数都配置[font="]LOG_ARCHIVE_DEST_STATE_1=ENABLE[font="]LOG_ARCHIVE_DEST_STATE_2=ENABLE[font="]LOG_ARCHIVE_FORMAT=%t_%s_%r.arc#归档日志文件名格式LOG_ARCHIVE_MAX_PROCESSES=30#默认是4,可以不修改#以下是主库作为备用数据库的配置:用于主备切换[font="]FAL_SERVER=standby# FAL_SERVER是另一个数据库的[font="]service_name[font="]FAL_CLIENT=orcl #当前数据库的[font="]service_name[font="]DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby','/u01/app/oracle/oradata/orcl'[font="]# 配置主备库的数据文件位置,远程的目录写在前面,[font="]LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby','/u01/app/oracle/oradata/orcl'[font="]# 配置主备库的在线日志和[font="]standby日志文件位置,远程的目录写在前面[font="]STANDBY_FILE_MANAGEMENT=AUTO [font="]REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE修改成功后进行启动验证:1、Startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora’;2、如果可以启动成功,就创建spfile:Createspfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora’;3、如果不成功,查看pfile是否修改错误。备库的pfile可以是主库复制过来的,也可以由备库是spfile生成后进行修改,但是备库的控制文件一定是主库创建的。Pfile 拷贝到备库上:scp –rp/u01/app/product/11.2.0/db_1/dbs/initorcl.ora node2:/u01/app/product/11.2.0/db_1/dbs/8. 创建备份库需要的控制文件 创建控制文件ShutdownimmediateSTARTUP MOUNT; ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orcl.ctl'; ALTER DATABASE OPEN;这个就是standbycontrolfile,备库的控制文件一定要使用这个控制文件。9. 创建主库的spfile 修改主库pfile成功后进行启动验证:1、Startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora’;2、如果可以启动成功,先进行spfile的备份,然后创建spfile:Createspfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora’;3、如果不成功,查看pfile是否修改错误。4、穿件spfile成功后进行spfile启动数据库进行验证。Shutdownimmediatestartuppfile='//u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora'sql> create spfile from pfile='//u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora';(先把原来的干掉)shutdownimmediate;startupstartupfrom pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora'10.备份生产数据库 至此主库已经配置完毕,需要复制主库的数据文件、standby 控制文件、归档日志、参数文件pfile到备库。1、 停止数据库2、 直接复制数据文件、归档日志文件、standby 控制文件、参数文件pfile到备库相应目录
scp -rp /u01/oradata/ORCL node2:/u01/oradata/scp –rp /u01/app/admin/orcl node2:/u01/app/admin(记得在备库创建admin)

11.修改备库初始化参数pfile 修改备库参数文件pfile,这个备库pfile参数文件可以是备库由spfile生成。也可以是主库复制过来的,但是要修改正确。Pfile指定的控制文件路径:修改成主库复制过来的standby控制文件,该控制文件复制三份,在pfile文件指定路径。[font="]control_files='E:\Oracle\oradata\DGMAIN\control01.ctl','E:\Oracle\oradata\DGMAIN\control02.ctl','E:\Oracle\oradata\DGMAIN\control03.ctl'#控制文件[font="]DB_UNIQUE_NAME=standby#[font="] DB_UNIQUE_NAME主备数据库不能相同[font="]LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,standby)'#DataGuard专用配置,主备数据库相同[font="]LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby’#主数据库归档地址[font="]LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC[font=??] [font="]VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)[font=??] [font="]DB_UNIQUE_NAME=orcl’#备用数据库归档地址,由该配置指定向备用数据库写归档日志[font="]LOG_ARCHIVE_DEST_STATE_1=ENABLE[font="]LOG_ARCHIVE_DEST_STATE_2=ENABLE[font="]LOG_ARCHIVE_FORMAT=%t_%s_%r.arc#归档日志文件名格式LOG_ARCHIVE_MAX_PROCESSES=30#默认是4,可以不修改#以下是主库作为备用数据库的配置:用于主备切换[font="]FAL_SERVER=orcl# FAL_SERVER是另一个数据库的[font="]service_name[font="]FAL_CLIENT=standby #当前数据库的[font="]service_name[font="]DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/standby'[font="]LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/standby'[font="]#如果主库与备库安装目录不一样需要配置[font="]CONVERT,远程的数据库目录写在前面[font="]STANDBY_FILE_MANAGEMENT=AUTO [font="]REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE修改成功后进行启动验证:1、Startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora’;2、如果可以启动成功,就创建spfile:Createspfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora’;3、如果不成功,查看pfile是否修改错误。12.复制备库控制文件 备库的控制文件是主库的standby控制文件复制过来的,不需要修改,直接复制三份。scp -rp /tmp/dg.ctl node2:/u01/oradata/ORCL/controlfile/scp -rp /tmp/dg.ctl node2:/u01/flash_recovery_area/orcl/controlfile/13.在备库上创建口令文件 orapwd file=$ORACLE_HOME/dbs/orapwdb1 password=XXXX entries=1014.创建备库的spfile 修改成功后进行启动验证:1、Startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora’;2、如果可以启动成功,就创建spfile:Createspfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora’;3、如果不成功,查看pfile是否修改错误。4、创建成功后进行spfile方式启动数据库进行验证。Shutdownimmediatestartuppfile='//u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora'
CREATE SPFILE FROM PFILE;15.启动物理备用数据库 STARTUP MOUNT;16.配置Standby Redo Log 主库和备库都配置standby redo log,在主库查看日志组的数量和每个日志文件的大小SQL>SELECT GROUP#, BYTES FROM V$LOG;在备库库查看日志组的数量和每个日志文件的大小,standby日志文件组文件大小和redo log大小一样,需要比redo log多一组或者几组。SQL>SELECT GROUP#, BYTES/1024/1024 FROM V$STANDBY_LOG;创建日志组和redolog文件SQL> ALTER DATABASE ADDSTANDBY LOGFILE group 4([color=#0001ff]'/oracle/dbs/slog1.rdo[color=#0001ff]') SIZE 50M;[color=#0001ff] SQL> ALTER DATABASE ADDSTANDBY LOGFILE group 5 ([color=#0001ff]'/oracle/dbs/slog2.rdo[color=#0001ff]') SIZE 50M;SQL>ALTER DATABASE ADDSTANDBY LOGFILE group 6 ([color=#0001ff]'/oracle/dbs/slog3.rdo[color=#0001ff]') SIZE 50M;SQL> ALTER DATABASE ADDSTANDBY LOGFILE group 7([color=#0001ff]'/oracle/dbs/slog4.rdo[color=#0001ff]') SIZE 50M;ALTER DATABASE ADD STANDBYLOGFILE group 7([color=#0001ff]'/u01/app/oracle/oradata/standby/sredo07.log[color=#0001ff]') SIZE 50M;ALTER DATABASE ADD STANDBYLOGFILE group 7([color=#0001ff]'/u01/app/oracle/oradata/orcl/sredo07.log[color=#0001ff]') SIZE 50M;[size=11pt]ALTERDATABASE ADD STANDBY LOGFILE group 7('/u01/app/oracle/product/10.2.0/db_1/oradata/standby/sredo07.log')size 50M; 17.Redo Apply的启动和停止 启动日志恢复:ALTERDATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROMSESSION; Alterdatabase recover managed standby database disconnect from session;关闭日志恢复:[font="]alter database recover managed standbydatabase cancel;对于物理备库(physicalstandby)来说,处于日志恢复模式的时候数据库是不能打开的。如果要打开只能先关闭日志恢复模式,然后以readonly (只读方式)打开。(oracle11G版本可以在read only下应用归档日志)SQL>alterdatabase recover managed standby database cancel;停止日志恢复模式SQL>alterdatabase open read only;打开数据库。物理备库在只读模式下可以增加临时文件。
SQL> alter database temp add tempfile '/opt/oradata/orcl/temp02.dbf' size 100m reuse;这个时候备库还是可以接收主库传输过来的归档日志文件,只是不能应用日志而已。当用read only 方式打开备库后只有重新启动才能使备库重新回到备用模式。进行主备库验证:在备库SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;在主库强制日志切换到当前的online redo log file.ALTER SYSTEM ARCHIVE LOG CURRENT;在备库查看新的被归档的redodataSELECTSEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;在备库查看接收到的被应用的redoSQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;查看数据库的角色selectdatabase_role,protection_mode,protection_level from v$database;l 主备库切换1.查看主库的状态SQL> SELECTSWITCHOVER_STATUS FROM V$DATABASE;2.将主库切换至备用模式SQL> ALTER DATABASECOMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH -> SESSION SHUTDOWN;3.关闭、装载主数据库SQL> SHUTDOWN ABORT;SQL> STARTUP MOUNT;4.查看备库准备向主库模式切换SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS-----------------TO_PRIMARY1 rowselected5.切换备库至主库模式SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;6.打开新的主数据库SQL> ALTER DATABASE OPEN;7. 在新的备库服务器上启动 REDO apply。SQL> ALTER DATABASE RECOVER MANAGED STANDBYDATABASE USING CURRENT LOGFILE -> DISCONNECT FROMSESSION;l 灾难恢复(failover)Step 1 Flush anyunsent redo from the primary database to the target standbydatabaseSQL> ALTER SYSTEM FLUSHREDO TO target_db_name;Step 2 Verify thatthe standby database has the most recently archived redo logfile for each primarydatabase redo thread.SQL> SELECT UNIQUETHREAD# AS THREAD, MAX(SEQUENCE#) -> OVER (PARTITION BYthread#) AS LAST from V$ARCHIVED_LOG;SQL> ALTER DATABASEREGISTER PHYSICAL LOGFILE 'filespec1';Step 3 Identify andresolve any archived redo log gaps.SQL> SELECT THREAD#,LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;SQL> ALTER DATABASEREGISTER PHYSICAL LOGFILE 'filespec1';Step 4 Repeat Step 3until all gaps are resolved.Step 5 Stop RedoApply.Issue the followingSQL statement on the target standby database:SQL> ALTER DATABASERECOVER MANAGED STANDBY DATABASE CANCEL;Step 6 Finishapplying all received redo data.Issue the followingSQL statement on the target standby database:SQL> ALTER DATABASERECOVER MANAGED STANDBY DATABASE FINISH;Step 7 Verify thatthe target standby database is ready to become a primarydatabase.Step 8 Switch thephysical standby database to the primary role.Issue the followingSQL statement on the target standby database:SQL> ALTER DATABASECOMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;Step 9 Open the newprimary database.SQL> ALTER DATABASE OPEN;Step 10 Back up thenew primary database.Oracle recommendsthat a full backup be taken of the new primary database.Step 11 Restart RedoApply if it has stopped at any of the other physical standbydatabases in yourData Guard configuration.For example:SQL> ALTER DATABASERECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE -> DISCONNECT FROMSESSION;附:1. 启动到管理模式SQL>shutdown immediate;SQL>startup nomount;SQL>alter database mount standby database;SQL>alter database recover managed standby database disconnect fromsession;[color=#2a2a2a][font="]2.[color=#2a2a2a]启动到只读方式SQL>shutdown immediate;SQL>startup nomount;SQL>alter database mount standby database;SQL>alter database open read only;[color=#2a2a2a][font="]3[color=#2a2a2a]如果在管理恢复模式下到只读模式SQL> recover managed standby database cancel;SQL> alter database open read only;

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】