Oracle+dataguard.docx

Oracle dataguard实施转载请注明出处 主库00实例 whever软件安装路径:c:/oracle_setup数据库路径c:oradata备库00实例 whever软件安装路径:c:/oracle_setup数据库路径c:oradata两个数据库都安装相同的软件和实列，路径也相同主库新建立好用户，表空间，导入数据。CREATE SMALLFILE TABLESPACE ever DATAFILE C:ORADATAYKTever SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;CREATE USER UEVER PROFILE DEFAULT IDENTIFIED BY everpass DEFAULT TABLESPACE ever ACCOUNT UNLOCKGRANT CONNECT TO UEVER GRANT DBA TO UEVER 从库建立好同名数据库即可，用户，表空间都不需要建立。完善TNS,在两个数据库下面都加入如下语句：=primary = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 00)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = whever) ) )standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 00)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = whever) ) )两边这一部分是完全一样的主库 tnsping standby从库 tnsping primary然后用tnsping 命令，看两边是否可以ping 通。第一步：开启主库的归档模式：C:Documents and SettingsAdministratorsqlplusEnter user-name: /as sysdbaSQL archive log list;Database log mode No Archive ModeAutomatic archival DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 4Current log sequence 6SQL SHUTDOWN immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL STARTUP MOUNT;ORACLE instance started.Total System Global Area 171966464 bytesFixed Size 787988 bytesVariable Size 145488364 bytesDatabase Buffers 25165824 bytesRedo Buffers 524288 bytesDatabase mounted.SQL ALTER DATABASE ARCHIVELOG;Database altered.SQL alter database force logging;SQL ALTER DATABASE OPEN;Database altered.SQL archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 4Next log sequence to archive 6Current log sequence 6SQL alter system set LOG_ARCHIVE_DEST_1=LOCATION=c:Arch;System altered.SQL archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination f:ArchOldest online log sequence 4Next log sequence to archive 6Current log sequence 6SQL shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.上面的是开启归档模式，下面生成控制文件startup mount;ORACLE instance started.Total System Global Area 171966464 bytesFixed Size 787988 bytesVariable Size 145488364 bytesDatabase Buffers 25165824 bytesRedo Buffers 524288 bytesDatabase mounted.SQL ALTER DATABASE CREATE STANDBY CONTROLFILE AS C:control01.ctl;Database altered.Shutdown immediate;生成的控制文件复制成三分，control01.ctl control02.ctl control03.ctl关闭主库，从库shutdown immediate;拷贝主库的文件包括f:oradata 的所有文件，database/下的yktpwd.ora文件，spfilesykt.ora文件到备份库的相同目录，上面的控制文件覆盖掉备份库向同文件在从库修改spfilesykt.ora方法生成pfile文件CREATE PFILE=C:pf.ora FROM SPFILE;然后修改pf.ora在最后面加上STANDBY_ARCHIVE_DEST=location=c:oracleStarch /这个是接受文件的FAL_SERVER=PRIMARYFAL_CLIENT=STANDBYstandby_file_management=AUTO /为，主库建立表空间呀，数据文件，自动同步然后用pfile启动Shutdown immediatetartup pfile=c:pf.ora nomount;CREATE SPFILE FROM PFILE=C:pf.ora;shutdown immediate; startup mount;切换脚本SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;Database altered.SQL alter database recover managed standby database cancel;Database altered.SQL alter database open;Database altered.SQL=主库SQL shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL start mount;SP2-0310: unable to open file mount.sqlSQL startup mount;ORACLE instance started.Total System Global Area 171966464 bytesFixed Size 787988 bytesVariable Size 145488364 bytesDatabase Buffers 25165824 bytesRedo Buffers 524288 bytesDatabase mounted.SQL ALTER DATABASE OPEN;Database altered.切换到开启。然后alter system set log_archive_dest_2=SERVICE=standby MANDATORY REOPEN=60;发送归档到standby反复运行：alter system switch logfile;生成主库的备份库的监视 在备份库上安装Tail4win命令行模式下打开dbdump下的*.log文件。什么是oracle Data guard?1. Data guard 为 Oracle 数据库提供灾难恢复解决方案2. Data gurad 是oracle数据库企业版(EE)的特性3.自动创建和维护生产数据库(或主数据库primary database)的一个或多个事务一致的副本(备用数据库 standby database,事务一致的意思是数据一致)4.如果主数据库(因灾难、维护)不可用，那么可用激活一个备用数据库并使之承担主数据库的角色5.要求主服务器和备用服务器上的Oracle数据库和操作系统的版本相同primary database 到 standby database是通过redo log来进行保持一致的。物理备用数据库，它是直接拷贝redo log,根据它的redo log来生成 datafile .还可以把它写到磁带上，用来备份和恢复逻辑备份数据库，它是把redo log拷贝回来以后，把它转换成SQL语句，然后执行SQL语句，去改变备库里的数据，从而达到备库里的数据和主库里的数据一致.(逻辑上于生成库一致，数据一致，但是存储结构和方式上可能与生成库不一致)物理备库和逻辑备库的目的不一致：物理备库可以用来做备份,写到磁带里去。逻辑备库因为逻辑一致，一方面可以不断接受生产库的数据，一方面可以对外开放，可以提供查询操作保护模式数据丢失保护重做传输最高保护零数据丢失双重故障保护LGWR SYNC-将重做同步传输至两个站点最高可用性零数据丢失单重故障保护LGWR SYNC-将重做同步传输最高性能最少的数据丢失LGWR SYNC-将重做异步传输这三种模式的切换，可以通过一条命令来完成:ALTER DATABASE SET STANDBY TO MAXIMIZE PROTECTION;一些Data Guard 的效果1. Data Guard 同步传输对于LAN, 甚至对于MAN(300英里/10毫秒 RTT)都是可行的 1.零数据丢失2.对主系统的吞吐量影响最小(在测试中小于3%)2.异步传输对于所有类型的网络都可行 1.美国大西洋到太平洋的部署(2600英里/80毫秒 RTT)对性能几乎没有影响(在测试中小于1%),并且在出现灾难的情况下，潜在的平均事务丢失为1秒3.异步模式下，在WAN上使用SSH压缩可使网络的流量减少35%-60%4.转换可以在1分钟内完成，故障切换在短短的15秒内即可完成Data Guard技术特点:1.网络效率 （只传输重做数据）2.更适于WAN(广域网)-无需协议转换器，基于标准的TCP/IP3.更好的数据保护-保持写顺序的一致性，避免逻辑损坏和物理损坏4. 功能 -全面的DR解决方案5.灵活性-底层存储器对供应商没有限制6.ROI-从DR投资中提取价值，与数据库本地集成=具体步骤如下：一、主库操作1、修改主库属性：alter system force logging;#查看状态select FORCE_LOGGING from v$database;2、修改数据库为归档模式：archive log list;shutdown immediate;startup mount;alter database archivelog;(alter database noarchivelog;关掉归档模式)archive log list;3、添加standby logfile(也可以不加)为主数据库添加备用联机日志文件，这里要保证备日志文件与主库联机日志文件相同大小。 添加备用日志文件是规则： 备用日志最少应该比redo log 多一个。推荐的备重做日志数依赖于主数据库上的线程数。 (每线程日志文件最大数目 + 1 ) * 线程数 alter database add standby logfile group 4 (/oracle2/app/oracle/oradata/std_redo04a.log,/oracle2/app/oracle/oradata/std_redo04b.log) size 50m, group 5 (/oracle2/app/oracle/oradata/std_redo05a.log,/oracle2/app/oracle/oradata/std_redo05b.log) size 50m, group 6 (/oracle2/app/oracle/oradata/std_redo06a.log,/oracle2/app/oracle/oradata/std_redo06b.log) size 50m, group 7 (/oracle2/app/oracle/oradata/std_redo07a.log,/oracle2/app/oracle/oradata/std_redo08b.dbf) size 50m;否则备库在应用时报如下信息：RFS1: No standby redo logfiles createdRFS1: Archived Log: /oracle2/arch/1_30_633287861.dbf在主库添加完standby logfile后，当主库切换后备库后会自动使用备库的redo logfile，具体应用信息如下：RFS1: Successfully opened standby log 4: /oracle2/app/oracle/oradata/10g/redo04.logRFS1: Successfully opened standby log 4: /oracle2/app/oracle/oradata/10g/redo04.log4、修改主库参数文件：10g._db_cache_size=120795955210g._java_pool_size=1677721610g._large_pool_size=1677721610g._shared_pool_size=35232153610g._streams_pool_size=0*.audit_file_dest=/oracle2/app/oracle/admin/10g/adump*.background_dump_dest=/oracle2/app/oracle/admin/10g/bdump*.compatible=.0*.control_files=/oracle2/app/oracle/oradata/10g/control01.ctl,/oracle2/app/oracle/oradata/10g/control02.ctl,/oracle2/app/oracle/oradata/10g/control03.ctl*.core_dump_dest=/oracle2/app/oracle/admin/10g/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=10g*.DB_UNIQUE_NAME=10gpri #必须 定义每个数据库的唯一标识*.log_archive_config=DG_CONFIG=(10gpri,10gstandby) #必须*.log_archive_dest_1=location=/oracle2/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gpri #必须本地的归档路径*.LOG_ARCHIVE_DEST_2=SERVICE=10gstandby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby #必须(远程服务器端的归档日志)*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.FAL_SERVER=10gpri # 定义FAL服务器的Oracle Net服务的名称*.FAL_CLIENT=10gstandby # 定义备数据库的Oracle Net服务名 （这两个参数在主库可有可无，但备库必须有。ORACLE 老外工程师说这个必须有_） *.db_recovery_file_dest=/oracle2/app/oracle/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.dispatchers=(PROTOCOL=TCP) (SERVICE=10gXDB)*.job_queue_processes=10*.open_cursors=300*.pga_aggregate_target=1707081728*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=1610612736*.undo_management=AUTO*.undo_tablespace=UNDOTBS1*.user_dump_dest=/oracle2/app/oracle/admin/10g/udump*.STANDBY_FILE_MANAGEMENT=AUTO #设置为AUTO,使得当数据文件添加到主数据库或者从主数据库删除的时候,对应的修改能够在备用数据库中自动执行.5、用pfile启动，再重新创建spfile.shutdown immediate;startup pfile=./pfile.pra;create spfile from pfile=./pfile.ora;shutdown immediate;startup;6、在主库创建密码文件、以及控制文件。orapwd file=orapw10gstandby.ora password=change_on_install entries=10alter database create standby database controlfile /tmp/standby.ctl;7、TNS信息如下：主库10g=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 04)(PORT = 1921) ) (CONNECT_DATA = (SID = 10g) )10gpri=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 24)(PORT = 1921) ) (CONNECT_DATA = (SID = 10g) )10gstandby =(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 04)(PORT = 1921) ) (CONNECT_DATA = (SID = 10g) ) 8、对主库进行全库备份run allocate channel t1 type disk;allocate channel t2 type disk;backup database format /tmp/full_%s;release channel t1;release channel t2;=二、备库操作如下：1、依照主库的数据文件位置，在备库上创建相应的目录结构（最好与主库一致）；mkdir -p /oracle/app/oracle/oradata/.2、通过FTP把在主库创建的密码文件、standby controlfile、full backup database文件到备库主机上。3、备份的参数文件内容：10g._db_cache_size=120795955210g._java_pool_size=1677721610g._large_pool_size=1677721610g._shared_pool_size=35232153610g._streams_pool_size=0*.audit_file_dest=/oracle2/app/oracle/admin/10g/adump*.background_dump_dest=/oracle2/app/oracle/admin/10g/bdump*.compatible=.0*.control_files=/oracle2/app/oracle/oradata/10g/control01.ctl,/oracle2/app/oracle/oradata/10g/control02.ctl,/oracle2/app/oracle/oradata/10g/control03.ctl*.core_dump_dest=/oracle2/app/oracle/admin/10g/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=10g*.DB_UNIQUE_NAME=10gstandby #*.log_archive_config=DG_CONFIG=(10gpri,10gstandby) #*.log_archive_dest_1=location=/oracle2/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gstandby #*.LOG_ARCHIVE_DEST_2=SERVICE=10gpri arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gpri#*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.FAL_SERVER=10gstandby #*.FAL_CLIENT=10gpri #*.db_recovery_file_dest=/oracle2/app/oracle/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.dispatchers=(PROTOCOL=TCP) (SERVICE=10gXDB)*.job_queue_processes=10*.open_cursors=300*.pga_aggregate_target=1707081728*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=1610612736*.undo_management=AUTO*.undo_tablespace=UNDOTBS1*.user_dump_dest=/oracle2/app/oracle/admin/10g/udump*.STANDBY_FILE_MANAGEMENT=AUTO #4、分别对备库进行全库恢复，并启动到standby database mount状态下。用PFILE文件起动到nomount状态下，恢复控制文件;起动到mount状态下，然后再恢复全库。5、TNS信息如下：10g=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 04)(PORT = 1921) ) (CONNECT_DATA = (SID = 10g) )10gpri=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 24)(PORT = 1921) ) (CONNECT_DATA = (SID = 10g) )10gstandby =(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 04)(PORT = 1921) ) (CONNECT_DATA = (SID = 10g) ) 用TNSPING 对方是否通。6、修改备库处于应用归档状态alter database recover managed standby database disconnect from session;如果主库从不过来归档，可以通过在主库侧手工修改参数如下：ALTER SYSTEM SET log_archive_dest_state_2=DEFER SCOPE=MEMORY;ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=MEMORY;7、测试通过在主库执行alter system switch logfile;切换日志可以观察到备库会自动应用通过主库传过来的日志。三、切换测试1、在主库端select switchover_stats from v$database;如果是to standby表可以正常切换.直接执行 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;否则执行: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;shutdown immediate;startup nomount;alter database mount standby database;2、在备库在备库SELECT SWITCHOVER_STATUS FROM V$DATABASE; 如果是to_primary表可以正常切换.执行:ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;否则执行: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;shutdown immediate;startup;然后观察主备库日志,如果正常的话会看到备库会自动应用日志.如何在11gR2 中设置dataguard broker.1. 检查并设置相关参数.SQL show parameter broker NAME TYPE VALUE- - -dg_broker_config_file1 string /opt/app/oracle/products/11.2. 0/dbs/dr1dbmain.datdg_broker_config_file2 string /opt/app/oracle/products/11.2. 0/dbs/dr2dbmain.datdg_broker_start boolean TRUE-如果dg_broker_start为False,就将其改为True.alter system set dg_broker_start = true scope=both;2. 调整listener配置,添加listener相关监听项. 1). dbstby 上的配置变更为.-变化前的配置.SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_NAME=) (SID_NAME=dbstby) (ORACLE_HOME=/opt/app/oracle/products/11.2.0) ) )-变化后的配置.SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_NAME=) (SID_NAME=dbstby) (ORACLE_HOME=/opt/app/oracle/products/11.2.0) ) (SID_DESC= (GLOBAL_NAME=dbmain_DGMGRL.) (SID_NAME=dbstby) (ORACLE_HOME=/opt/app/oracle/products/11.2.0) ) )-修改完毕之后,运行lsnrctl reload 配置. 2).dbmain 的listener 配置也做类似的处理.-变化前的配置.SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_NAME=) (SID_NAME=dbmain) (ORACLE_HOME=/opt/app/oracle/products/11.2.0) ) )-变化后的配置SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_NAME=) (SID_NAME=dbmain) (ORACLE_HOME=/opt/app/oracle/products/11.2.0) ) (SID_DESC= (GLOBAL_NAME=dbmain_DGMGRL.) (SID_NAME=dbmain) (ORACLE_HOME=/opt/app/oracle/products/11.2.0) ) )-配置完毕之后, 运行lsnrctl reload加载新配置.3. 运行dgmgrl命令创建broker 相关配置.-创建基于主库的broker 配置.DGMGRL create configuration dgconfig1 as primary database is dbmain connect identifier is dbmain;Configuration dgconfig1 created with primary database dbmain-添加standby db的broker配置.DGMGRL add database dbstby as connect identifier is dbstby maintained as physical;Database dbstby added-显示添加好之后的配置.DGMGRL show configuration;Configuration - dgconfig1 Protection Mode: MaxAvailability Databases: dbmain - Primary database dbstby - Physical standby databaseFast-Start Failover: DISABLEDConfiguration Status:DISABLED-激活配置并重新显示配置.DGMGRL enable configuration;Enabled.DGMGRL show configuration;Configuration - dgconfig1 Protection Mode: MaxAvailability Databases: dbmain - Primary database dbstby - Physical standby databaseFast-Start Failover: DISABLEDConfiguration Status:SUCCESSDGMGRL -显示primary database的信息.DGMGRL show database dbmain;Database - dbmain Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): dbmainDatabase Status:SUCCESS-显示standby database的信息.DGMGRL show database dbstby;Database - dbstby Role: PHYSICAL STANDBY Intended State: APPLY-ON -是否正在apply. Transport Lag: 0 seconds -log transport delay Apply Lag: 0 seconds -log apply delay Real Time Query: ON -是否打开read time apply Instance(s): dbstbyDatabase Status:SUCCESSNo related posts.Data Guard 之表误操作恢复有这样种情况，由于误操作了一个业务表(drop/truncate/delete column,etc.)幸被及时发现.此时，我们可以在短时间内恢复这个表的常规操作有这么几种：1) Flashback/Logmnr(如果是DML类，可以使用闪回查询或者logmnr挖掘;如果是drop，可以使用flashback table，但若是字段的更改或者truncate等操作，上述就无法起到作用了)2) Dataguard端恢复(当然，需要standby端设置delay，利用延迟应用日志时间差，来迅速将standby recover到故障时间点之前，然后利用逻辑exp&imp来实现表级恢复)Dataguard体系下，方法2不失为一种高效简洁的办法.下面，看如下一个试验：1. 在standby端，设置日志的延迟应用时间：SQL alter database recover managed standby database DELAY 60 disconnect from session;数据库已更改。2.primay端，模拟表误删除