DATA GUARD搭建.doc

DATA GUARD搭建利用RMAN 创建physical standbyCovey Niu摘要：搭建data guard环境，利用RMAN DUPLICATE命令创建physical standby。创建备库之前需要有备库的参数文件；同时连接主数据库和备用数据库，启动备库到nomount 状态。备库必须工作在recover managed 模式下才能应用主库的redo数据，具体创建过程如下:整体系统结构包含三部分：1.oracle 数据库服务器： 作为主节点，承担用户连接和数据库服务。2.oracle 备用数据库：承担针对主节点数据跟踪、实市时备份责任，在系统灾难情况下直接转移到这台服务器操作，实现几分钟内的灾难恢复。3.专用网络：连接数据库服务器和备用数据库服务器之间的专用网络，避免公用网络出现故障时主库与备库之间无法通信。在开始之前，请先关闭防火强及SELINUX本项目涉及到的配置环境：主、备库： 物理内存1G，本地存储操作系统Red Hat Enterprise Linux AS release 5Kernel 2.6.18-164.el5 EDT 2009 i686 i686 i386 GNU/Linux- 2 -数据库软件版本ORACLE 10g release .0-主数据库机器名：内网IP：00外网IP：00-备用数据库机器名：内网IP：00外网IP：00-双方主机名和IP绑定到hosts文件#公共IPADDR00 00 #私有IPADDR00 00 -用户/口令操作系统根用户：rootOracle 数据库属主：oracle 主组：oinstall 用户属组：dba数据库：sys/oracleSystem/oracleScott/tiger-修改内核参数# vim /etc/security/limits.conforacle soft nofile 65536oracle hard nofile 65536oracle soft nproc 16384oracle hard nproc 16384# vim /etc/sysctl.conf kernel.shmmax = 2147483648kernel.shmmni = 4096kernel.shmall = 2097152kernel.sem = 250 32000 100 128fs.file-max = 65536net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default = 262144net.core.rmem_max = 262144net.core.wmem_default = 262144net.core.wmem_max = 262144运行下面命令使参数生效# sysctl p-ORACLE环境变量：（主）export ORACLE_BASE=/oracle/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1export PATH=$ORACLE_HOME/bin:$PATHexport ORACLE_OWNER=oracleexport ORACLE_SID=covey（主库）export THREADS_FLAG=nativeexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATHexport PATH=$ORACLE_HOME/bin:$CRS_HOME/bin:$PATHexport LANG=en_US.UTF-8（备）export ORACLE_BASE=/oracle/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1export PATH=$ORACLE_HOME/bin:$PATHexport ORACLE_OWNER=oracleexport ORACLE_SID=covey（备库）export THREADS_FLAG=nativeexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATHexport PATH=$ORACLE_HOME/bin:$CRS_HOME/bin:$PATHexport LANG=en_US.UTF-8第一步：primary database的创建。1. 使用OUI 安装数据库软件2. 使用DBCA 创建数据库（注意查看数据库文件是否为设计的位置）,全局数据库名：covey 实例名：covey3. 以 oracle 身份登录系统，连接主库，启动到 mount 状态，修改数据库强制记录日志。SQLalter database force logging; 如果创建数据库时没有选择归档，将数据库改为归档模式SQLalter database archivelog;SQL alter system set log_archive_dest_1=location=/archive_log;开启闪回数据库（不是必须的）SQL alter database flashback on;4根据spfile文件创建pfile，关闭数据库。SQL create pfile from spfile;SQLshutdown immediate5修改pfile文件$ vim initcovey.oracovey._db_cache_size=188743680covey._java_pool_size=4194304covey._large_pool_size=4194304covey._shared_pool_size=83886080covey._streams_pool_size=0*.audit_file_dest=/oracle/app/oracle/admin/covey/adump*.background_dump_dest=/oracle/app/oracle/admin/covey/bdump*.compatible=.0*.control_files=/oracle/app/oracle/oradata/covey/control01.ctl,/oracle/app/oracle/oradata/covey/control02.ctl,/oracle/app/oracle/oradata/covey/control03.ctl*.core_dump_dest=/oracle/app/oracle/admin/covey/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=covey*.db_recovery_file_dest=/oracle/app/oracle/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.dispatchers=(PROTOCOL=TCP) (SERVICE=coveyXDB)*.job_queue_processes=10*.log_archive_dest_1=location=/archive_log*.open_cursors=300*.pga_aggregate_target=94371840*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=285212672*.undo_management=AUTO*.undo_tablespace=UNDOTBS1*.user_dump_dest=/oracle/app/oracle/admin/covey/udumpdb_unique_name=bjlog_archive_config=dg_config=(bj,sz)fal_server=szfal_client=bjlog_archive_dest_1=location=/archive_loglog_archive_dest_2=service=sz lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=szlog_archive_dest_state_1=ENABLElog_archive_dest_state_2=ENABLEdb_file_name_convert=/oracle/app/oracle/oradata/covey,/oracle/app/oracle/oradata/covey #先远程后本地log_file_name_convert=/oracle/app/oracle/oradata/covey,/oracle/app/oracle/oradata/covey #先远程后本地standby_file_management=AUTO标注为黄色的参数最为重要，特别是标有双色的参数，无论哪个出现错误都可能导致redo log无法应用到备库。5采用pfile文件启动数据库SQL startup nomount pfile=initcovey.ora;SQL create spfile from pfile;SQL alter database mount;6为主数据库创建监听网络$ netmgr 、配置TNS选中Service naming点击红圈中的加号填入bj后点击NextTCP/IP(INTERNET PROTOCOL),默认Next填入主机名（也可以填入IP）后点击Next填入bj后Next，之后点击完成即可。以上述步骤添加db_unique_name为sz的监听。、配置监听选中Listeners后点击加号点击OK点击Address在Listening Locations下拉列表中选中database service改动上面的Global database Name和SID为covey，完成配置后点击file，点击save network config后，关闭窗口后启动监听。$ lsnrctl start第二步：使用RMAN备份数据库1.创建备份集的存放位置rootdev # mkdir /backrootdev # chown -R oracle.oinstall /back2.备份当前的控制文件oracledev dbs$ rman target /RMAN backup format /back/bk_%U current controlfile for standby;Starting backup at 04-JUN-12using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=138 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding standby control file in backupsetchannel ORA_DISK_1: starting piece 1 at 04-JUN-12channel ORA_DISK_1: finished piece 1 at 04-JUN-12piece handle=/back/bk_01ncnoo7_1_1 tag=TAG20120604T231103 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:18Finished backup at 04-JUN-123备份数据文件和归档文件RMAN backup format /back/db_%U database plus archivelog;Starting backup at 04-JUN-12current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupset.piece handle=/back/db_05ncnpc7_1_1 tag=TAG20120604T232140 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 04-JUN-12第三步：physical standby database创建1.安装数据库软件，本实验选择安装软件的路径与主库相同。2.参考主库创建备库上相应的文件目录（注意目录名及位置不能写错，否则创建过程会失败）。rootdev # mkdir /backrootdev # chown -R oracle.oinstall /back/rootdev # mkdir /archive_logrootdev # chown oracle.oinstall /archive_log/$ mkdir -p /oracle/app/oracle/admin/covey/a,b,c,udump$ mkdir /oracle/app/oracle/flash_recovery_area$ mkdir -p /oracle/app/oracle/oradata/covey3.拷贝备份集及相关的文件到相应位置（备份过程中FORMAT 后面的路径），如果没有拷贝到相应的位置会报错（也可以通过RMAN的交叉检测更改RMAN的备份路径catalog backuppiece 新路径）。1）、拷贝RMAN备份$ scp -r /back/* 00:/back/oracle00s password:bk_01ncnoo7_1_1 100% 6944KB 6.8MB/s 00:01 db_02ncnosv_1_1 100% 45MB 1.4MB/s 00:33 db_03ncnov3_1_1 100% 502MB 1.3MB/s 06:24 db_04ncnpbu_1_1 100% 6944KB 3.4MB/s 00:02 db_05ncnpc7_1_1 100% 3072 3.0KB/s 00:00 2）、拷贝ORACLE的密码文件$ scp /oracle/app/oracle/product/10.2.0/db_1/dbs/orapwcovey 00:/oracle/app/oracle/product/10.2.0/db_1/dbs/orapwcovey3）、拷贝pfile文件scp /oracle/app/oracle/product/10.2.0/db_1/dbs/initcovey.ora 00:/oracle/app/oracle/product/10.2.0/db_1/dbs/initcovey.ora4.修改参数文件initsdb.oracovey._db_cache_size=188743680covey._java_pool_size=4194304covey._large_pool_size=4194304covey._shared_pool_size=83886080covey._streams_pool_size=0*.audit_file_dest=/oracle/app/oracle/admin/covey/adump*.background_dump_dest=/oracle/app/oracle/admin/covey/bdump*.compatible=.0*.control_files=/oracle/app/oracle/oradata/covey/control01.ctl,/oracle/app/oracle/oradata/covey/control02.ctl,/oracle/app/oracle/oradata/covey/control03.ctl*.core_dump_dest=/oracle/app/oracle/admin/covey/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=covey*.db_recovery_file_dest=/oracle/app/oracle/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.dispatchers=(PROTOCOL=TCP) (SERVICE=coveyXDB)*.job_queue_processes=10*.log_archive_dest_1=location=/archive_log*.open_cursors=300*.pga_aggregate_target=94371840*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=285212672*.undo_management=AUTO*.undo_tablespace=UNDOTBS1*.user_dump_dest=/oracle/app/oracle/admin/covey/udumpdb_unique_name=szlog_archive_config=dg_config=(bj,sz)fal_server=bjfal_client=szlog_archive_dest_1=location=/archive_loglog_archive_dest_2=service=bj lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=bjlog_archive_dest_state_1=ENABLElog_archive_dest_state_2=ENABLEdb_file_name_convert=/oracle/app/oracle/oradata/covey,/oracle/app/oracle/oradata/coveylog_file_name_convert=/oracle/app/oracle/oradata/covey,/oracle/app/oracle/oradata/coveystandby_file_management=AUTO具体修点为黄色部分,注意红色部分要和主库保持一致。5. 为备库创建监听及服务名称（方法同主库）$ lsnrctl start6使用initsdb.ora 启动数据库到nomount 状态。SQL startup nomountSQL create spfile from pfile;7启动rman 使用 rman duplicate 创建备用数据库$ rman target sys/oraclebj auxiliary /Recovery Manager: Release .0 - Production on Tue Jun 5 11:03:09 2012Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: COVEY (DBID=3303871395)connected to auxiliary database: COVEY (not mounted)RMAN duplicate target database for standby nofilenamecheck;这里要注意一下：如果主备目录不同，不需要加nofilenamecheck，如果相同必须指定。Starting Duplicate Db at 05-JUN-12using channel ORA_AUX_DISK_1contents of Memory Script: restore clone standby controlfile; sql clone alter database mount standby database;executing Memory ScriptStarting restore at 05-JUN-12channel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece /back/bk_01ncnoo7_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/back/bk_01ncnoo7_1_1 tag=TAG20120604T231103channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25output filename=/oracle/app/oracle/oradata/covey/control01.ctloutput filename=/oracle/app/oracle/oradata/covey/control02.ctl.restoring datafile 00004 to /oracle/app/oracle/oradata/covey/users01.dbfchannel ORA_AUX_DISK_1: reading from backup piece /back/db_03ncnov3_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/back/db_03ncnov3_1_1 tag=TAG20120604T231443channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:14Finished restore at 05-JUN-12contents of Memory Script: switch clone datafile all;executing Memory Scriptdatafile 1 switched to datafile copyinput datafile copy recid=5 stamp=785157992 filename=/oracle/app/oracle/oradata/covey/system01.dbfdatafile 2 switched to datafile copyinput datafile copy recid=6 stamp=785157992 filename=/oracle/app/oracle/oradata/covey/undotbs01.dbfdatafile 3 switched to datafile cop