oracle 11g dataguard 配置管理
目录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
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;