oracle10g+Dataguard配置.doc

Linux上实现oracle双机实时备份DataGuard更改机器名Hostname 机器名(自己启,两台机器不能一样,建议用dataguard1和dataguard2)vi /etc/sysconfig/network 文件里面的hostname然后vi /etc/hosts 添加本地hosts安装数据库在RHEL5下安装oracle 10g1、添加组和用户# /usr/sbin/groupadd oinstall# /usr/sbin/groupadd dba# /usr/sbin/useradd -g oinstall -G dba oracle# passwd oracle2、添加以下内容到/etc/sysctl.confkernel.shmall = 2097152kernel.shmmax = 2147483648kernel.shmmni = 4096kernel.sem = 250 32000 100 128fs.file-max = 65536net.ipv4.ip_local_port_range = 1024 65000net.core.rmem_default = 1048576net.core.rmem_max = 1048576net.core.wmem_default = 262144net.core.wmem_max = 2621443、添加以下内容到/etc/security/limits.conforacle soft nproc 2047oracle hard nproc 16384oracle soft nofile 1024oracle hard nofile 65536 4、添加以下内容到/etc/pam.d/loginsession required /lib/security/pam_limits.sosession required pam_limits.so 5、添加以下内容到/etc/profileif $USER = oracle ; thenif $SHELL = /bin/ksh ; thenulimit -p 16384ulimit -n 65536elseulimit -u 16384 -n 65536fifi 6、创建安装目录# mkdir -p /u01/app/oracle# chown -R oracle:oinstall /u01/app/oracle# chmod -R 775 /u01/app/oracle7、修改oracle环境变量,首先进入oracle用户模式,vi /.bash_profile ,在里面加入以下内容export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1export ORACLE_SID=DB99export PATH=$ORACLE_HOME/bin:$PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/libexport LC_CTYPE=en_US.UTF-8export NLS_LANG=AMERICAN_AMERICA.ZHS16GBKexport LANG=AMERICAN_AMERICA.ZHS16GBKexport LC_ALL=C8、安装数据库# gunzip 10201_database_linux_x86_64.cpio.gz (64位) # cpio -idmv ALTER DATABASE FORCE LOGGING;SQLselect FORCE_LOGGING from v$database;主库检查密码文件这个默认安装完之后都有，文件在/u01/app/oracle/product/10.2.0/db_1/dbs/orapwDB99文件名格式为orapw$sid主库添加standby redo logSQLalter database add standby logfile group 4 (/u01/app/oracle/oradata/DB99/redo04.log) size 50m;SQLalter database add standby logfile group 5 (/u01/app/oracle/oradata/DB99/redo05.log) size 50m;SQLalter database add standby logfile group 6 (/u01/app/oracle/oradata/DB99/redo06.log) size 50m;SQLalter database add standby logfile group 7 (/u01/app/oracle/oradata/DB99/redo07.log) size 50m;查看日志情况sqlselect * from v$logfile;执行后应该能看到你新加的这四个日志相关信息修改主数据库初始化参数先执行SQLcreate pfile from spfile;这样就会有/u01/app/oracle/product/10.2.0/db_1/dbs/initDB99.ora文件直接编辑这个文件加入以下参数*.db_unique_name=dbtest1*.fal_server=dbtest1*.fal_client=dbtest2*.standby_file_management=auto*.db_file_name_convert=/u01/app/oracle/oradata/DB99/,/u01/app/oracle/oradata/DB99/*.log_file_name_convert=/u01/app/oracle/oradata/DB99/,/u01/app/oracle/oradata/DB99/*.log_archive_config=dg_config=(dbtest1,dbtest2) *.log_archive_dest_2=service=dbtest2 LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=dbtest2*.log_archive_dest_state_2=ENABLE这样初始化参数改好了使用命令Sqlshutdown immediateSqlcreate spfile from pfile;修改/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora下的监听文件listener.ora文件如下SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = DB99) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2) (ADDRESS = (PROTOCOL = TCP)(HOST = 1)(PORT = 1521) ) )修改/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora 文件如下DB99 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 1)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB99) ) )#LISTENER_DB99 =# (ADDRESS = (PROTOCOL = TCP)(HOST = localhoenorthdb1)(PORT = 1521)DBTEST2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 4)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = DB99) ) )DBTEST1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = DB99) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )这样参数文件就改完了主库启用归档日志SQL STARTUP MOUNT;SQL ALTER DATABASE ARCHIVELOG;SQL ALTER DATABASE OPEN;SQL Alter database set standby database to maximize availability;注意执行过alter database archivelog;后以后就不用再执行这个命令了,以后直接用startup启动数据库就可以了用rman备份主库数据文件、归档日志、控制文件在oracle用户环境下,执行rman target /然后执行rmanbackup database plus archivelog;rmanbackup current controlfile for standby;这样就会在/u01/app/oracle/flash_recovery_area/DB99下生成备份,包括数据文件,控制文件和归档日志下面对备库进行操作由于备库没有创建实例,所以缺少几个文件目录用oracle用户添加对应主库的目录结构添加/u01/app/oracle/admin/DB99下的adump,bdump,cdump,udump/u01/app/oracle/flash_recovery_area/u01/app/oracle/oradata/DB99从主库dbtest1复制文件到备用库dbtest2上需要复制/u01/app/oracle/flash_recovery_area/下的所有东西还有/u01/app/oracle/product/10.2.0/db_1/dbs/下的所有东西主库上的/u01/app/oracle/product/10.2.0/db_1/network/admin 目录下的listener.ora和tnsnames.ora备库修改如/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora下SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = DB99) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2) (ADDRESS = (PROTOCOL = TCP)(HOST = 4)(PORT = 1521) ) )注:和主库相同只有ip不同tnsnames.ora 如下DB99 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 4)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB99) ) )#LISTENER_DB99 =# (ADDRESS = (PROTOCOL = TCP)(HOST = localhoenorthdb1)(PORT = 1521)DBTEST2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 4)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = DB99) ) )DBTEST1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = DB99) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )注:和主库一样,只有ip不同恢复备用库在恢复备份库的时候先把主库和备库的监听程序重启一下方法,在oracle用户模式下lsnrctl stop,然后lsnrctl start然后再回到备库启动数据库到nomount阶段sqlplus / as sysdbasqlstartup nomount再回到oracle用户模式从rman恢复rman target sys/userjishubudbtest1 auxiliary /rmanduplicate target database for standby nofilenamecheck;开始进行恢复数据库修改备库的参数文件直接编辑这个文件/u01/app/oracle/product/10.2.0/db_1/dbs/initDB99.ora修改以下参数*.db_unique_name=dbtest2然后执行sqlshutdown immediate;sqlcreate spfile from pfile;启动备用库sqlplus / as sysdbasqlshutdown immediate;sqlstartup nomount;sqlalter database mount standby database;sqlALTER DATABASE ADD STANDBY LOGFILE;sqlALTER DATABASE ADD STANDBY LOGFILE;sqlALTER DATABASE ADD STANDBY LOGFILE;sqlALTER DATABASE ADD STANDBY LOGFILE;sqlalter database recover managed standby database using current logfile disconnect from session;这样Dataguard的最大可用模式实时备份就完成了下面进行一下测试在两边都执行SqlArchive log list看看返回的结果我的结果是主库SQL archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 17Next log sequence to archive 19Current log sequence 19备库SQL archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 18Next log sequence to archive 0Current log sequence 19如果没出现数字，在oracle用户模式下lsnrctl stop,然后lsnrctl start看我所标示的地方是否数字一致,说明已经同步了然后看主库执行Sql selectprotection_level,protection_modefromv$database;结果为SQL select protection_level,protection_mode from v$database;PROTECTION_LEVEL PROTECTION_MODE- -MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY如果是这样的结果.两个显示都是高可用模式,那么说明应该配置正确了再做测试,再主库上插入用户并添加表然后在备库上执行Sql alter database recover managed standby database cancel;Sqlalter database open read only;连接备库看看有没有你新加的用户名和表,如果都同步过来了,说明配置正确,一切正常了ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;select *from v$archive_gap;select SEQUENCE# from V$ARCHIVED_LOG;补充知识,关于大于2TB的硬盘如何分区,fdisk已经不能用了,方法如下假设要用parted对Linux下的/dev/sdb（大小为3000G）进行分区操作：1、进入系统，在#提示符下执行：parted /dev/sdb2、建立磁盘label： mklabel GPT3、按p键，查看磁盘可用空间，显示单位为M。3000G应该显示大致为: /dev/sdb 的磁盘几何结构：0000 兆字节 4、创建分区：在(parted)后输入： mkpart并按照提示操作：分区类型？ primary/主分区/extended/扩展分区? p （如果要创建扩展分区 此处输入e）。文件系统类型？ ext2? (这里直接回车,千万不要改成ext3，分区创建完毕后通过mkfs.ext3 /dev/sdb*建立文件系统)起始点？0.000 (加入要建立一个10G的分区)结束点？10240.0005、按p键查看刚创建的分区是否已经成功。6、如果成功，键入quit退出parted。7、使用mkfs.ext3 /dev/hdb* 来创建文件系统8、挂载并使用该分区：mkdir /testdirmount /dev/sdb* /testdircd /testdir9、写入数据，测试写入是否正常。附:em控制端按钮显示乱码解决方法 Linux安装oracle后em按钮出现乱码现象 - oracle在linux环境中安装oracle10，启用EM时，会出现所有的按钮显示乱码的现象，原来是因为在安装oracle10时，JDK/JRE自动选择了字符集，而在$ORACLE_HOME/jdk/jre/lib和$ORACLE_HOME/jre/1.4.2/lib目录下，都有多种字体：oraclelinzhaorong lib$ ll-rwxr-xr-x 1 oracle oinstall 6966 11-18 22:00 perties-rwxr-xr-x 1 oracle oinstall 5870 2005-04-20 perties_bak-rwxr-xr-x 1 oracle oinstall 5681 2005-04-20 perties.Redhat6.1-rwxr-xr-x 1 oracle oinstall 3828 2005-04-20 perties.Redhat8.0-rwxr-xr-x 1 oracle oinstall 3752 2005-04-20 perties.SuSE8.0-rwxr-xr-x 1 oracle oinstall 6854 2005-04-20 perties.zh_CN.Redhat-rwxr-xr-x 1 oracle oinstall 7410 2005-04-20 perties.zh_CN.Redhat2.1-rwxr-xr-x 1 oracle oinstall 6845 2005-04-20 perties.zh_CN.Sun-rwxr-xr-x 1 oracle oinstall 7137 2005-04-20 perties.zh_CN_UTF8.Sun-rwxr-xr-x 1 oracle oinstall 6922 2005-04-20 perties.zh_HK.Sun-rwxr-xr-x 1 oracle oinstall 7132 2005-04-20 perties.zh_HK_UTF8.Sun-rwxr-xr-x 1 oracle oinstall 5810 2005-04-20 perties.zh_HK_UTF8.Sun2003-rwxr-xr-x 1 oracle oinstall 6521 2005-04-20 perties.zh.Turbo-rwxr-xr-x 1 oracle oinstall 7236 2005-04-20 perties.zh_TW.Redhat-rwxr-xr-x 1 oracle oinstall 7232 2005-04-20 perties.zh_TW.Redhat2.1-rwxr-xr-x 1 oracle oinstall 6892 2005-04-20 perties.zh_TW.Sun-rwxr-xr-x 1 oracle oinstall 7142 2005-04-20 perties.zh_TW_UTF8.Sun-rwxr-xr-x 1 oracle oinstall 5810 2005-04-20 perties.zh_TW_UTF8.Sun2003而每一个目录下都有一个缺省的字符集perties，我们只要用合适的中文字符集来替换缺省字符集即可，我选择的是perties.zh_CN.Redhat。首先，我们应该先将em关闭：emctl stop dbconsole然后进行替换，注意，$ORACLE_HOME/jdk/jre/lib和$ORACLE_HOME/jre/1.4.2/lib目录下都要进行如下替换：oracleredhat lib$ cp perties.zh_CN.Redhat perties替换之后，我们还要查一下，linux中有没有安装相应的中文字符集，怎么看？oracleredhat lib$ tail perties fontset.dialoginput.plain= -b&h-luxi mono-medium-r-normal-*-%d-*-*-m-*-iso8859-1, -isas-song ti-medium-r-normal-*-%d-*-*-c-*-gb2312.1980-0 fontset.default= -b&h-luxi sans-medium-r-normal-*-%d-*-*-p-*-iso8859-1, -isas-song ti-medium-r-normal-*-%d-*-*-c-*-gb2312.1980-0 filename.-misc-zysong18030-medium-r-normal-*-%d-*-*-c-*-iso10646-1=/usr/share/fonts/zh_CN/TrueType/zysong.ttf -查看字体文件是否存在，如果不存在，需要更改路径，或者安装相应字体文件。根据最后一行的路径，我们去看看是否有该文件是否存在，如果没有，那说明没有安装相应的中文字符集，我们可以到linux的系统安装盘中去找到相应的rpm包，进行安装。下载地址为/index.php3/stat/4/idpl/5594715/com/ttfonts-zh_CN-2.14-6.noarch.rpm.htmlwget ftp:/mirror.switch.ch/pool/3/mirror/centos/4.7/os/x86_64/CentOS/RPMS/ttfonts-zh_CN-2.14-6.noarch.rpm安装 rpm ivh ttfonts-zh_CN-2.14-6.noarch.rpm最后还要清理一下Cache，路径一般是ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/em/em/cabo/images/cache/zhs注意：只需要清理该路径下的gif文件即可：rm *.gif最后再将em开启，此时，我们发现，乱码不见了。emctl start dbconsoleSQL create DIRECTORY enorth_bak_dir as /u01/oracle/bak/data/;SQL grant READ,WRITE on DIRECTORY enorth_bak_dir to pub;rootworkdb bak# more expdp_pub.shexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/10.2.0/db_1export ORACLE_SID=db21export PATH=$PATH:$ORACLE_HOME/binexport PATH=$PATH:$HOME/bin:$ORACLE_HOME/binexpdp system/adminjishubu parfile=/u01/oracle/bak/expdp_pub.txtrootworkdb bak# more expdp_pub.txtschemas=zhengz2directory=enorth_bak_dirdumpfile=db21.dmplogfile=expdp_db21.logcompression=nonecontent=allestimate=blocksrootworkdb bak# more impdp_temp.shexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/10.2.0/db_1export ORACLE_SID=db21export PATH=$PATH:$ORACLE_HOME/binexport PATH=$PATH:$HOME/bin:$ORACLE_HOME/binimpdp system/adminjishubu parfile=/u01/oracle/bak/impdp_temp.txtrootworkdb bak# more impdp_temp.txt REMAP_SCHEMA=zhengz2:pub_tempdirectory=enorth_bak_dirdumpfile=db21_20091019.dmplogfile=expdp_db21_temp.logcontent=all/创建目录create or replace directory expdir as d:;/给test赋予目录的读写权限grant read,write on directory expdir to test;/导出数据C:expdp test/testacf dumpfile=test.dmp directory=expdir151、查看原主库切换状态SqlSELECT SWITCHOVER_STATUS FROM V$DATABASE;如果这里显示的信息是：to standby;则，可以正常切换。如果显示的是session active那么有两种可能，一种是日志断档，可以查看以下视图select SEQUENCE# from V$ARCHIVED_LOG;对比两个数据库中是否一致，如果没有，则说明主库的日志没有传递到备库。此时需要手工将日志文件复制到备用库上，然后在备用库上执行ALTER DATABASE REGISTER LOGFILE /u01/oracle/flash_recovery_area/DG2/onlinelog/.dbf;系统会自动同步。如果这个时候之前的查询还是session active的状态，可以使用以下语句直接切换，在下一节中讲解。152、切换主库角色ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY (WITH SESSION SHUTDOWN);括号中的语句在之前提到的session active状态是才用，意思就是强制关闭所有session；153、重起原来的主库数据库,并做redo应用SQL SHUTDOWN IMMEDIATE;SQL STARTUP MOUNT;SQLALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;154、查看原来的备用库状态SqlSELECT SWITCHOVER_STATUS FROM V$DATABASE;正常情况下，应该是TO_PRIMARY155、将原来的备用库升级为主库SqlALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;156、将新的主库（原来的备用库）打开SqlALTER DATABASE OPEN;如果在alert中提示是read-only的话，则需要先关闭，再打开。物理standby的failover 注意几点： l failover之后，原primary数据库默认不再是data guard配置的一部分。 l 多数情况下，其它逻辑/物理standby数据库不直接参与failover的过程，因此这些数据库不需要做任何操作。 l 某些情况下，新的primary数据库配置之后，需要重新创建其它所有的standby数据库。 另外，如果待转换角色的standby处于maximum protection或maximum availability模式的话，归档日志应该是连续存在的，这种情况下你可以直接从第 3 步执行，否则建议你按照操作步骤从第1步开始执行。 一般情况下failover都是表示primary数据库瘫痪，最起码也是起不来了，因此这种类型的切换基本上不需要primary数据库做什么操作。所以下列步骤中如果有提到primary和standby执行的，只是建议你如果primary还可以用，那就执行一下，即使它能用你却不执行，也没关系，不影响standby数据库的切换:) 1、 检查归档文件是否连续 查询待转换standby数据库的V$ARCHIVE_GA