Linux RHEL5.5 创建 oracle 10g Dataguard
来源：互联网 发布：打击垫软件下载 编辑：程序博客网 时间：2024/06/05 18:14
Linux RHEL5.5 创建 oracle 10g Dataguard 2 dataguard建立2.1 dataguard规划Host_IP DB_NAME DB_UNIQUE_NAME Net Service Name主库192.168.2.135syw syw syw备库192.168.2.136syw syw01 syw01主库归档路径/sywdg/arch1备库归档路径/sywdg/arch1保护模式：默认最大性能模式2.2主库准备工作2.2.1 检查数据库是否支持Data Guard是否归档模式，Enable force loggingSQL> select * from v$option where parameter = 'Managed Standby';PARAMETER VALUE------------------ -------------------------------Managed Standby TRUESQL> archive log list （先检查是否归档模式，不是则修改）数据库日志模式 存档模式自动存档 启用存档终点 /sywdg/arch1/最早的联机日志序列 164下一个存档日志序列 166当前日志序列 166 SQL> alter database force logging;数据库已更改。2.2.2 如果主库没有密码文件则建立密码文件，从而可以OS验证的方式登陆$orapwd file=$ORACLE_HOME/dbs/orapwsyw password=oracle entries=52.2.3、配置standby redolog（最佳性能模式可以忽略，如果将来变成备库且要转为其它两种模式则要建立）查看当前redolog大小SQL> select group#,members,bytes/1024/1024,status from v$log; GROUP# MEMBERS BYTES/1024/1024 STATUS---------- ---------- --------------- ---------------- 1 1 50 CURRENT 2 1 50 INACTIVE 3 1 50 INACTIVE现有三个日志组，每个组内有一个成员，每个成员的大小为50MB2.2.4在主库增加standby redologSQL> alter database add standby logfile 2 group 4 ('/u01/app/oracle/oradata/stdby_redo04.log') size 50m, 3 group 5 ('/u01/app/oracle/oradata/stdby_redo05.log') size 50m, 4 group 6 ('/u01/app/oracle/oradata/stdby_redo06.log') size 50m, 5 group 7 ('/u01/app/oracle/oradata/stdby_redo07.log') size 50m;standby redolog的组数参考公式：(online redolog组数 + 1) * 数据库线程数；单机线程数为1，RAC一般为2。standby redolog的组成员数和大小也尽量和online redolog一样。2.3 设置主库初始化参数启动db接受或发送redo data，包括所有库的db_unique_nameSQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(SYW,SYW01)' scope=spfile;设置主库归档目的地SQL>alter system set LOG_ARCHIVE_DEST_1='LOCATION=/sywdg/arch1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SYW' scope=spfile; 当该库充当主库角色时，设置物理备库redo data的传输目的地SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=SYW01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SYW01' scope=spfile; 修改最大ARCn进程数SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=5 scope=spfile; 允许redo传输服务传输数据到目的地，默认是enableSQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile; 允许redo传输服务传输数据到目的地，默认是enableSQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile; 主库备库库sys密码要一致，默认是exclusiveSQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile; --以下是主库切换为备库，充当备库角色时的一些参数设置，如果不打算做数据库切换就不用设置了配置网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件SQL> alter system set FAL_SERVER=syw01 scope=spfile; 配置网络服务名，fal_server拷贝丢失的归档文件到这里SQL> alter system set FAL_CLIENT=syw scope=spfile; 前为切换后的主库路径，后为切换后的备库路径，如果主备库目录结构完全一样，则无需设定SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/syw01',' /u01/app/oracle/oradata/syw' scope=spfile; 同上，这两个名字转换参数是主备库的路径映射关系，可能会是路径全名，看情况而定SQL> alter system set LOG_FILE_NAME_CONVERT=' /u01/app/oracle/oradata/syw01',' /u01/app/oracle/oradata/syw' scope=spfile; 设置auto后当主库的datafiles增删时备库也同样自动操作，且会把日志传送到备库standby_archive_dest参数指定的目录下，确保该目录存在，如果你的存储采用文件系统没有问题，但是如果采用了裸设备，你就必须将该参数设置为manualSQL> alter system set STANDBY_FILE_MANAGEMENT=auto scope=spfile;一般和LOG_ARCHIVE_DEST_1的位置一样，如果备库采用ARCH传输方式，那么主库会把归档日志传到该目录下SQL> alter system set STANDBY_ARCHIVE_DEST='LOCATION=/sywdg/arch1' scope=spfile; 有了以上参数设置，则无论该库充当主库角色还是备库角色都无需再修改了配置完成之后重新启动主数据库SQL> shutdown immediate;数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startup ORACLE 例程已经启动。Total System Global Area 285212672 bytesFixed Size 2020224 bytesVariable Size 96472192 bytesDatabase Buffers 184549376 bytesRedo Buffers 2170880 bytes数据库装载完毕。数据库已经打开。2.4 配置主库备库监听listener.ora及tnsnames.ora，sqlnet.ora[oracle@DB_primary admin]$ more listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.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 = (GLOBAL_DBNAME = SYW) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = SYW) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DB_primary)(PORT = 1521)) ) )[oracle@DB_primary admin]$ more tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.SYW = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.135)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = syw) ) )SYW01 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.136)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = syw01) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )[oracle@DB_primary admin]$ more sqlnet.ora # sqlnet.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora# Generated by Oracle configuration tools.NAMES.DIRECTORY_PATH= (TNSNAMES)备库监听信息[oracle@DB_standby admin]$ more listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DB_standby)(PORT = 1521)) ) )[oracle@DB_standby admin]$ more tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.SYW01 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.136)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = syw01) ) )SYW = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.135)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = syw) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )[oracle@DB_standby admin]$ more sqlnet.ora # sqlnet.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora# Generated by Oracle configuration tools.NAMES.DIRECTORY_PATH= (TNSNAMES)监听配置完成之后正常启动监听，在主库备库分别执行tnsping命令[oracle@DB_primary ~]$ tnsping syw[oracle@DB_primary ~]$ tnsping syw01[oracle@DB_standby ~]$ tnsping syw[oracle@DB_standby ~]$ tnsping syw01确保主备都可以正常通信2.5、RMAN备份主库数据 关闭应用服务器，停止监听，开始rman备份：[oracle@DB_primary dgbak]$ lsnrctl stopLSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-8月 -2011 10:55:03Copyright (c) 1991, 2005, Oracle. All rights reserved.正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))命令执行成功RMAN> backup full database format '/sywdg/dgbak/backup_%T_%s_%p.bak';启动 backup 于 22-8月 -11使用目标数据库控制文件替代恢复目录分配的通道: ORA_DISK_1通道 ORA_DISK_1: sid=137 devtype=DISK通道 ORA_DISK_1: 启动全部数据文件备份集通道 ORA_DISK_1: 正在指定备份集中的数据文件输入数据文件 fno=00007 name=/u01/app/oracle/oradata/syw/tbs_syw.ora输入数据文件 fno=00001 name=/u01/app/oracle/oradata/syw/system01.dbf输入数据文件 fno=00004 name=/u01/app/oracle/oradata/syw/users01.dbf输入数据文件 fno=00006 name=/u01/app/oracle/oradata/syw/tbs_cms.ora输入数据文件 fno=00003 name=/u01/app/oracle/oradata/syw/sysaux01.dbf输入数据文件 fno=00005 name=/u01/app/oracle/oradata/syw/TBS_IDX_SYW.ora输入数据文件 fno=00002 name=/u01/app/oracle/oradata/syw/undotbs01.dbf通道 ORA_DISK_1: 正在启动段 1 于 22-8月 -11通道 ORA_DISK_1: 已完成段 1 于 22-8月 -11段句柄=/sywdg/dgbak/backup_20110822_27_1.bak 标记=TAG20110822T121100 注释=NONE通道 ORA_DISK_1: 备份集已完成, 经过时间:00:02:16备份集中包括当前控制文件在备份集中包含当前的 SPFILE通道 ORA_DISK_1: 正在启动段 1 于 22-8月 -11通道 ORA_DISK_1: 已完成段 1 于 22-8月 -11通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:03完成 backup 于 22-8月 -11RMAN> sql "alter system archive log current";sql 语句: alter system archive log currentRMAN> backup archivelog all format='/sywdg/dgbak/arch_%T_%s_%p.bak';启动 backup 于 22-8月 -11当前日志已存档完成 backup 于 22-8月 -112.6、在主库上建立备库控制文件[oracle@DB_primary ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on 星期一 8月 22 12:21:27 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> alter database create standby controlfile as '/sywdg/dgbak/stdby_control01.ctl';[oracle@DB_primary ~]$ cd /sywdg/dgbak/[oracle@DB_primary dgbak]$ ls -al -rw-r----- 1 oracle oinstall 147193344 08-22 12:19 arch_20110822_29_1.bak-rw-r----- 1 oracle oinstall 1855897600 08-22 12:13 backup_20110822_27_1.bak-rw-r----- 1 oracle oinstall 7143424 08-22 12:13 backup_20110822_28_1.bak-rw-r----- 1 oracle oinstall 7061504 08-22 12:21 stdby_control01.ctl复制备库控制文件[oracle@DB_primary dgbak]$ cp stdby_control01.ctl stdby_control02.ctl [oracle@DB_primary dgbak]$ cp stdby_control01.ctl stdby_control03.ctl [oracle@DB_primary dgbak]$ ls -lh -rw-r----- 1 oracle oinstall 141M 08-22 12:19 arch_20110822_29_1.bak-rw-r----- 1 oracle oinstall 1.8G 08-22 12:13 backup_20110822_27_1.bak-rw-r----- 1 oracle oinstall 6.9M 08-22 12:13 backup_20110822_28_1.bak-rw-r----- 1 oracle oinstall 6.8M 08-22 12:21 stdby_control01.ctl-rw-r----- 1 oracle oinstall 6.8M 08-22 12:22 stdby_control02.ctl-rw-r----- 1 oracle oinstall 6.8M 08-22 12:22 stdby_control03.ctl2.7、创建备库init参数文件 在主库上执行SQL> create pfile='/sywdg/dgbak/initsyw.ora' from spfile;编辑pfile 注意主备库不同角色的属性配置，注意文件路径等，注意db_name要和主库一致，主要是以下参数syw.__db_cache_size=167772160syw01.__db_cache_size=180355072syw.__java_pool_size=4194304syw01.__java_pool_size=4194304syw.__large_pool_size=4194304syw01.__large_pool_size=4194304syw.__shared_pool_size=104857600syw01.__shared_pool_size=92274688syw.__streams_pool_size=0syw01.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/syw01/adump'*.audit_trail='os'*.background_dump_dest='/u01/app/oracle/admin/syw01/bdump'*.compatible='10.2.0.1.0'*.control_files='/u01/app/oracle/oradata/syw01/stdby_control01.ctl','/u01/app/oracle/oradata/syw01/stdby_control02.ctl','/u01/app/oracle/oradata/syw01/stdby_control03.ctl'*.core_dump_dest='/u01/app/oracle/admin/syw01/cdump'*.db_block_size=8192*.db_domain=''*.db_file_multiblock_read_count=16*.db_file_name_convert=/u01/app/oracle/oradata/syw',' /u01/app/oracle/oradata/syw01'*.db_name='syw'*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'*.db_recovery_file_dest_size=2147483648*.db_unique_name='syw01'*.dispatchers='(PROTOCOL=TCP) (SERVICE=sywXDB)'*.fal_client='SYW01'*.fal_server='SYW'*.job_queue_processes=10*.log_archive_config='DG_CONFIG=(SYW01,SYW)'*.log_archive_dest_1='LOCATION=/sywdg/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SYW01'*.log_archive_dest_2='SERVICE=syw LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SYW'*.log_archive_dest_state_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.log_archive_max_processes=5*.log_file_name_convert='/u01/app/oracle/oradata/syw','/u01/app/oracle/oradata/syw01'*.nls_language='SIMPLIFIED CHINESE'*.nls_territory='CHINA'*.open_cursors=300*.optimizer_secure_view_merging=FALSE*.pga_aggregate_target=94371840*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.sga_target=283115520*.standby_archive_dest='LOCATION=/sywdg/arch1'*.standby_file_management='AUTO'*.undo_management='AUTO'*.undo_tablespace='UNDOTBS1'*.user_dump_dest='/u01/app/oracle/admin/syw01/udump'*.user_dump_dest='/u01/app/oracle/admin/syw/udump'在主库上用oracle身份执行，修改备库pfile$ cp initsyw.ora initsyw01.ora2.8 复制文件到备库拷贝上面生成的文件backup_%T.bak、stdby_control01/02/03.ctl、initsyw01.ora到备库所在主机，注意rman备份的文件在主备库主机上目录要一致。[oracle@DB_primary dgbak]$ scp *.bak oracle@192.168.2.136:/sywdg/dgbak/oracle@192.168.2.136's password: arch_20110822_29_1.bak 100% 140MB 17.6MB/s 00:08 backup_20110822_27_1.bak 100% 1770MB 17.9MB/s 01:39 backup_20110822_28_1.bak[oracle@DB_primary dgbak]$ scp *.ctl oracle@192.168.2.136:/u01/app/oracle/oradata/syw/oracle@192.168.2.136's password: stdby_control01.ctl 100% 6896KB 6.7MB/s 00:00 stdby_control02.ctl 100% 6896KB 6.7MB/s 00:01 stdby_control03.ctl 100% 6896KB 6.7MB/s 00:00 [oracle@DB_primary dgbak]$ scp initsyw01.ora oracle@192.168.2.136:/u01/app/oracle/product/10.2.0/db_1/dbs/oracle@192.168.2.136's password: initsyw01.ora 100% 1663 1.6KB/s 00:00 2.9建立备库相关文件和目录(备库主机已经装了oracle软件,且目录结构和主库一致)2.9.1设置环境变量并建立备库一些必需目录$ export ORACLE_BASE=/u01/app/oracle$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1$ export ORACLE_SID=SYW01$ mkdir -p $ORACLE_BASE/admin/syw01/adump$ mkdir -p $ORACLE_BASE/admin/syw01/bdump$ mkdir -p $ORACLE_BASE/admin/syw01/cdump$ mkdir -p $ORACLE_BASE/admin/syw01/udump--以下目录要看哪些地方可能会存放数据库文件，注意不能少建$ mkdir -p /u01/app/oracle/oradata/syw01$ mkdir -p /sywdg/arch1/2.9.2、在备库主机上生成密码文件，且sys密码和主库得一致$ orapwd file=$ORACLE_HOME/dbs/orapwsyw01 password=oracle entries=52.9.3在备库上建立spfile[oracle@DB_standby ~]$ sqlplus / as sysdbaSQL> create spfile from pfile;2.10 恢复创建standby database启动物理备库SQL> startup nomount;ORACLE 例程已经启动。Total System Global Area 285212672 bytesFixed Size 2020224 bytesVariable Size 92277888 bytesDatabase Buffers 188743680 bytesRedo Buffers 2170880 bytesSQL> alter database mount standby database;数据库已更改。$ rman target / (要求主备库rman备份文件的存放路径和文件名一致)RMAN> restore database;RMAN> restore archivelog all;配置备库的standby redolog（最佳性能模式可以忽略，如果要转为其它两种模式则要建立）SQL> alter database add standby logfile 2 group 4 ('/u01/app/oracle/oradata/stdby_redo04.log') size 50m, 3 group 5 ('/u01/app/oracle/oradata/stdby_redo05.log') size 50m, 4 group 6 ('/u01/app/oracle/oradata/stdby_redo06.log') size 50m, 5 group 7 ('/u01/app/oracle/oradata/stdby_redo07.log') size 50m;standby redolog的组数参考公式：(online redolog组数 + 1) * 数据库线程数；单机线程数为1，RAC一般为2。standby redolog的组成员数和大小也尽量和online redolog一样。SQL> select status from v$instance;STATUS------------MOUNTED2.8、在备库上,启动redo applySQL> alter database recover managed standby database disconnect from session;
