Oracle 11g ADG搭建手册(RMAN+目录异构)

1、#规划时注意的事项db_name必须保持一致，db_unique_name必须保持不一致主库安装软件、数据库、监听，备库只安装软件、监听，不安装数据库#操作系统层面的操作#安装32位依赖包rpm -ivh compat-libstdc+-33-3.2.3-69.el6.i686.rpmrpm -ivh glibc-devel-2.12-1.132.el6.i686.rpmrpm -ivh libaio-0.3.107-10.el6.i686.rpmrpm -ivh libstdc+-4.4.7-4.el6.i686.rpmrpm -ivh libstdc+-devel-4.4.7-4.el6.

2、i686.rpmrpm -ivh libtool-ltdl-2.2.6-15.5.el6.i686.rpmrpm -ivh libtool-ltdl-devel-2.2.6-15.5.el6.i686.rpmrpm -ivh ncurses-libs-5.7-3.20090208.el6.i686.rpmrpm -ivh ncurses-devel-5.7-3.20090208.el6.i686.rpmrpm -ivh readline-6.0-4.el6.i686.rpmrpm -ivh readline-devel-6.0-4.el6.i686.rpmrpm -ivh unixODBC-2

3、.2.14-12.el6_3.i686.rpmrpm -ivh unixODBC-devel-2.2.14-12.el6_3.i686.rpm#建立目录mkdir -p /data/pri/mkdir -p /data/pri/oradatamkdir -p /data/pri/archmkdir -p /data/pri/redomkdir -p /data/pri/oradata_bakmkdir -p /data/pri/product/11.2.0/db_1mkdir -p /data/pri/fast_recovery_areamkdir -p /data/pri/oraInvent

4、orymkdir -p /data/std/mkdir -p /data/std/oradatamkdir -p /data/std/archmkdir -p /data/std/redomkdir -p /data/std/oradata_bakmkdir -p /data/std/product/11.2.0/db_1mkdir -p /data/std/fast_recovery_areamkdir -p /data/std/oraInventory#创建Oracle账户groupadd oinstallgroupadd dbauseradd -g oinstall -G dba -m

5、oraclepasswd oracle#修改主库操作系统Oracle用户环境变量vi /.bash_profileLANG=enORACLE_BASE=/data/pri/oracleORACLE_SID=priORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1PATH=$ORACLE_HOME/bin:$PATH:$HOME/binLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libCLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jli

6、bexport PATH LANG DISPLAY ORACLE_BASE ORACLE_SID ORACLE_HOME LD_LIBRARY_PATH CLASSPATHvi /.bash_profileLANG=enORACLE_BASE=/data/std/oracleORACLE_SID=stdORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1PATH=$ORACLE_HOME/bin:$PATH:$HOME/binLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libCLASSPATH=$ORACLE_HOM

7、E/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibexport PATH LANG DISPLAY ORACLE_BASE ORACLE_SID ORACLE_HOME LD_LIBRARY_PATH CLASSPATH#修改系统参数vi /etc/pam.d/loginsession required /lib64/security/pam_limits.sovi /etc/security/limits.conforacle soft nproc 65536oracle hard nproc 65536oracle soft nofile 655

8、36oracle hard nofile 65536oracle soft stack 102405vi /etc/sysctl.conf# Controls the maximum shared segment size, in bytes# 定义单个共享内存段的最大值kernel.shmmax = 68719476736# Controls the maximum number of shared memory segments, in pages# 参数是控制共享内存页数,每页大小为4KBkernel.shmall = 4294967296# 共享内存段的最大数量kernel.shmmn

9、i = 4096kernel.sem = 250 32000 100 128net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576sysctl -pvi /etc/security/limits.d/90-nproc.conf* soft nproc 65536#修改目录权限chown -R oracle:oinstall /data/chm

10、od -R 755 /data/chown -R oracle:oinstall /opt/database/#在两台服务器数据库层面上都做的操作- 查看是否安装了DG功能select * from v$option where parameter = 'Oracle Data Guard'#开启归档和设置DG模式(pri主库上操作)shutdown immediate;startup mount;alter database archivelog; #开启归档模式alter system set db_recovery_file_dest='' #更改orac

11、l系统，禁止往闪回恢复区放归档日志alter system set log_archive_format='%t_%s_%r.arc' scope=spfile; #修改归档日志格式alter system set log_archive_dest_1='location=/data/pri/arch' scope=both; #设置归档路径位置select log_mode from v$database;archive log list; #确认归档结果#设置DG模式，默认是最高性能，maximize后可跟PROTECTION | AVAILABILITY

12、| PERFORMANCE，分别对应最大保护，最高可用性及最高性能。#新建DG的时候，只能用最高性能，用最大保护和最高可用会报错，无法启动数据库。#alter database set standby database to maximize availability; #select protection_mode,protection_level from v$database; #查看DG模式# select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;#开启强制归档alter database force

13、 logging;#设置、查看、打开闪回恢复区alter system set db_recovery_file_dest='/data/pri/fast_recovery_area' #设置主库alter system set db_recovery_file_dest='/data/std/fast_recovery_area' #设置从库alter system set db_recovery_file_dest_size=500M;alter database flashback on;select flashback_on from v$databas

14、e;#开启global_namesalter system set global_names=true;show parameter global_names;select * from global_name;#在pri主库上执行的操作#pri主库修改参数控制文件#在pri主库上手动执行添加参数的命令alter system set log_archive_config='dg_config=(pri,std)' scope=both;alter system set log_archive_dest_1='location=/data/pri/arch valid_

15、for=(all_logfiles,all_roles) db_unique_name=pri' scope=both;alter system set log_archive_dest_2='service=std reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=std' scope=both;alter system set standby_file_management='auto' scope=both;alter system set f

16、al_server='std' scope=both;alter system set db_file_name_convert='/data/std/oradata/std/','/data/pri/oradata/pri/' scope=spfile;alter system set log_file_name_convert='/data/std/arch/','/data/pri/arch/' scope=spfile;show parameter convert;create pfile from spf

17、ile;cd $ORACLE_HOME/dbsvi initpri.ora#删除*.log_archive_dest_1='location=/data/pri/arch'这个参数#增加如下参数，*.db_unique_name='pri' *.log_archive_config='dg_config=(pri,std)'*.log_archive_dest_1='location=/data/pri/arch valid_for=(all_logfiles,all_roles) db_unique_name=pri'*.log

18、_archive_dest_2='service=std reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=std'*.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.standby_file_management='auto'*.fal_server='std'*.fal_client='std' #Oracle11g以作废此参数，控制参

19、数文件中不能有#，这个只是在文档中注释用*.db_file_name_convert='/data/std/oradata/std/','/data/pri/oradata/pri/' #将前面的路径替换为后面的路径，前面为备库的路径，后面为主库的路径*.log_file_name_convert='/data/std/arch/','/data/pri/arch/' #将前面的路径替换为后面的路径，前面为备库的路径，后面为主库的路径create spfile='/data/pri/product/11.2.0/db_1/

20、dbs/spfiledgpri.ora' from pfile;/*LGWR还分为LGWR ASYNC(异步)和LGWR SYNC(同步)两种。最大保护最大可用最大性能进程LGWRLGWRLGWR或ARCH网络传输模式SYNCSYNCLGWR时设置ASYNC磁盘写操作AFFIRMAFFIRMNOAFFIRM备用日志YES物理备用需要LGWR和物理备用时需要备用库类型物理Standby物理或逻辑物理或逻辑alter system set log_archive_dest_1='location=/home/oracle/log_archive lgwr affirm sync v

21、alid_for=(all_logfiles,all_roles) db_unique_name=orcl02'*/#在pri主库上创建STANDBY REDO 日志，logfile group是日志组的意思，就是redo log，当有写入操作时，日志先写到日志组里面，当日志组的文件写满后，在往archlog里面刷新文件，在系统繁忙的时候，如果日志组经常写满，会影响写入性能alter database add standby logfile group 14 ('/data/pri/redo/redo14') size 50m;alter database add st

22、andby logfile group 15 ('/data/pri/redo/redo15') size 50m;alter database add standby logfile group 16 ('/data/pri/redo/redo16') size 50m;#删除日志组的命令alter database drop standby logfile group 4;alter database drop standby logfile group 5;alter database drop standby logfile group 6;#查看添加的

23、standby logfile group信息，group#要全局唯一，不能与v$log对应重复select group#,dbid,thread#,sequence#,bytes/1024/1024,archived,status from v$standby_log;select member from v$logfile;select thread#,bytes/1024/1024,members from v$log;#在pri主库上创建密码文件orapwd file=$ORACLE_HOME/dbs/orapwdgpri password=123456 entries=5#修改pri

24、主库的listener.ora文件，增加如下内容SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = pri) (ORACLE_HOME = /data/pri/oracle/product/11.2.0/db_1) (SID_NAME = pri) ) )- 修改pri主库的tnsnames.ora文件，增加如下内容STD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = VM2)(PORT = 1521) ) (CONNECT_DATA

25、= (SERVICE_NAME = std) ) )#修改std主库的listener.ora文件，增加如下内容SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = std) (ORACLE_HOME = /data/std/oracle/product/11.2.0/db_1) (SID_NAME = std) ) )#修改std主库的tnsnames.ora文件，增加如下内容PRI = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = VM1

26、)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = pri) ) )- std备库修改参数控制文件create pfile from spfile;cd $ORACLE_HOME/dbsvi initstd.ora#删除*.log_archive_dest_1='location=/data/pri/arch'这个参数#增加如下参数，*.db_unique_name='std' *.log_archive_config='dg_config=(std,pri)'*.log_archive_dest_1

27、='location=/data/std/arch valid_for=(all_logfiles,all_roles) db_unique_name=std'*.log_archive_dest_2='service=pri reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=pri'*.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.standby_file_manag

28、ement='auto'*.fal_server='pri'*.fal_client='pri' #Oracle11g以作废此参数，控制参数文件中不能有#，这个只是在文档中注释用*.db_file_name_convert='/data/pri/oradata/pri/','/data/std/oradata/std/' #将前面的路径替换为后面的路径，前面为主库的路径，后面为备库的路径*.log_file_name_convert='/data/pri/arch/','/data/std/

29、arch/' #将前面的路径替换为后面的路径，前面为主库的路径，后面为备库的路径#关闭std备库，并启动到nomount状态shutdown immediate;create spfile from pfile='/data/std/product/11.2.0/db_1/dbs/initstd.ora'startup nomount#startup nomount pfile='/data/std/product/11.2.0/db_1/dbs/initstd.ora'#std备库创建密码文件orapwd file=$ORACLE_HOME/dbs/o

30、rapwdgstd password=123456 entries=5#将pri主库的密码文件传输到std备库相应的位置scp orapwpri oraclevm2:/data/std/oracle/product/11.2.0/db_1/dbs #一定要传主库正在用的密码文件不是用命令生成的那个文件#到备库上将主库传过来的密码文件改名cd $ORACLE_HOME/dbsmv orapwpri orapwstd#strings orapwstd命令用来查看密码文件内容，一定要确保两边密码文件内容一模一样#在std库上修改创建spfile文件和相关目录mv initpri.ora initst

31、d.oravi initstd.orapri._db_cache_size=239075328pri._java_pool_size=4194304pri._large_pool_size=8388608pri._oracle_base='/data/std/oracle'#ORACLE_BASE set from environmentpri._pga_aggregate_target=251658240pri._sga_target=377487360pri._shared_io_pool_size=0pri._shared_pool_size=113246208pri._

32、streams_pool_size=0*.audit_file_dest='/data/std/oracle/admin/std/adump'*.audit_trail='db'*.compatible='.0'*.control_files='/data/std/oradata/std/control01.ctl','/data/std/oracle/fast_recovery_area/std/control02.ctl'*.db_block_size=8192*.db_domain='

33、'*.db_file_name_convert='/data/pri/oradata/pri/','/data/std/oradata/std/'*.db_unique_name='std'*.db_name='pri'*.db_recovery_file_dest='/data/std/fast_recovery_area'*.db_recovery_file_dest_size=524288000*.diagnostic_dest='/data/std/oracle'*.dispatch

34、ers='(PROTOCOL=TCP) (SERVICE=priXDB)'*.fal_server='pri'*.global_names=TRUE*.log_archive_config='dg_config=(std,pri)'*.log_archive_dest_1='location=/data/std/arch valid_for=(all_logfiles,all_roles) db_unique_name=std'*.log_archive_dest_2='service=pri reopen=120 lgw

35、r async valid_for=(online_logfiles,primary_role) db_unique_name=pri'*.log_archive_format='%t_%s_%r.log'*.log_file_name_convert='/data/pri/arch/','/data/std/arch/'*.memory_target=629145600*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.sta

36、ndby_file_management='auto'*.undo_tablespace='UNDOTBS1'mkdir -p /data/std/oracle/admin/std/adumpmkdir -p /data/std/oradata/std/mkdir -p /data/std/oracle/fast_recovery_area/std/sqlplus / as sysdbacreate spfile from pfile='/data/std/oracle/product/11.2.0/db_1/dbs/initstd.ora'st

37、artup nomount;#pri主库备份，并传输到备库相应位置rman target /runallocate channel c1 device type disk format '/data/pri/rmanbak/full_%u_%s_%p'backup database plus archivelog;runallocate channel c1 device type disk format '/data/pri/rmanbak/control%u_%s_%p'backup current controlfile for standby;cd /d

38、ata/pri/rmanbak/scp * oracle:/data/std/rmanbak#在备库上执行恢复ln -s /data/std/ /data/pri #在操作系统上创建软连接，使得/data/pri/rmanbak等同于/data/std/rmanbakrman target /RMAN> restore standby controlfile from '/data/std/rmanbak/control0aqt0qqm_10_1'SQL> alter database mount standby database; #在s

39、qlplus中执行这个命令#RMAN> CROSSCHECK BACKUP; #这两个是排错的使用用的命令，如果出现RMAN-06023: no backup or copy of datafile 4 found to restore用的，删除无效备份文件#RMAN> delete expired backupset;RMAN> restore database;RMAN> list backup of database; #查看备份信息select open_mode,database_role,db_unique_name from v$database; #查看

40、备库状态#在备库添加日志组，大小与主库保持一致，在备库执行如下命令alter database recover managed standby database cancel; #退出日志应用模式alter system set standby_file_management=manual; #设置standby_file_management为manualalter database add standby logfile group 24 ('/data/std/redo/redo24') size 50m;alter database add standby logfil

41、e group 25 ('/data/std/redo/redo25') size 50m;alter database add standby logfile group 26 ('/data/std/redo/redo26') size 50m;alter system set standby_file_management=auto; #将standby_file_management修改为auto# 删除日志组#alter database drop standby logfile group 4;#alter database drop standby

42、 logfile group 5;#alter database drop standby logfile group 6;#将pri库的在线redo log传送到std备库上cd /data/pri/oradata/pri/scp redo01.log redo02.log redo03.log oraclevm2:/data/std/oradata/std/#在std备库上执行重命名redo log文件操作select * from v$logfile;alter database recover managed standby database cancel; #退出日志应用模式alte

43、r system set standby_file_management=manual; #设置standby_file_management为manualalter database rename file '/data/pri/oradata/pri/redo03.log' to '/data/std/oradata/std/redo03.log'alter database rename file '/data/pri/oradata/pri/redo02.log' to '/data/std/oradata/std/redo02.

44、log'alter database rename file '/data/pri/oradata/pri/redo01.log' to '/data/std/oradata/std/redo01.log'alter system set standby_file_management=auto; #将standby_file_management修改为auto#执行如下命令时，实时观察如下的日志文件alter database recover managed standby database using current logfile disconne

45、ct from session; #启用日志应用/data/std/diag/rdbms/std/std/trace/alert_std.log#将备库开启日志同步，正式开启DGalter database recover managed standby database disconnect from session;#将备库置于Active DataGuard模式，体验实时查询select open_mode,database_role,db_unique_name from v$database; #查看备库当前状态 mountalter database recover managed standby database cancel; #取消备库的自动恢复alter database open; #OPEN备库为只读模式（Dataguard只能启动到readonly模式）select open_mode from v$database;alter database recover managed standby database using current logfile disconnect; #打开实时应用状态模式select open_mode,database_role,db_unique_na