oracle11g dg 配置(图文版)
有点懒,大家多见谅图片的位置就不一一添加了,工作比较忙,只给大家复制粘贴了下我的文档。第三章 主库配置3.1 创建目录并修改名称//在主库创建所需的文件夹 mkdir /u01/app/oracle/arch //修改名称 sqlplus / assysdba
show parametername alter system set db_unique_name=orclscope=spfile; !!!!如果默认是上图内容,则可不必执行下面2段sql语句!!!! alter system set service_names=orclscope=spfile; alter system set instance_name=orclscope=spfile; 3.2 配置日志归档模式shutdownimmediate;
startup mount
//设置归档日志目录 alter system set log_archive_dest_1='location=/u01/app/oracle/arch'; //验证日志目录 archive log list //开启归档模式 Alter databasearchivelog; //开启强制记录日志模式 alter databaseforce logging; 验证 selectFORCE_LOGGING from v$database; 3.3 添加 standby redolog日志文件alter database add standby logfile group11'/u01/app/oracle/oradata/orcl/std11.log'size 60M; alter database add standby logfile group12'/u01/app/oracle/oradata/orcl/std12.log'size 60M; alter database add standby logfile group13'/u01/app/oracle/oradata/orcl/std13.log'size 60M; alter database add standby logfile group14'/u01/app/oracle/oradata/orcl/std14.log'size 60M; //验证,加上系统默认的三条共7条 Select member,type from v$logfile; 3.4 开启闪回数据库//开启闪回数据库(作用:当发生故障转移时,这个功能非常有用,它能让你将老的主库闪回到故障前,然后将其转换为备库。如果没有启用闪回功能,你就必须重建备库,意味着要再复制一次数据文件。除了这个好处,闪回还能在某些情况下让你避免从备份恢复数据) //查看闪回数据库状态 show parameter db_recovery_file_dest //更改路径和大小 可以修改位置:sql>alter system set db_recovery_file_dest='新路径'; //查看是否启用,默认是不开启的 select flashback_on from v$database; alter database flashback on; //验证 select flashback_on from v$database; 3.5 DG配置// Data Guard 配置里的另外一个库的名字 alter system setlog_archive_config='DG_CONFIG=(orcl,orclstd)'; //配置归档日志位置(很重要) alter system setlog_archive_dest_1='LOCATION=/u01/app/oracle/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'; //配置重做日志到备份库(很重要) alter system set log_archive_dest_2='SERVICE=orclstdASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd'; //设置日志格式 alter system setlog_archive_format='arch_%r_%t_%s.arc' scope=spfile; //设置FAL_SERVER(作用:这个参数指定当日志传输出现问题时,备库到哪里去找缺少的归档日志) alter system set fal_server=orclstd; alter system set fal_client=orcl; //配置数据文件和日志文件目录 show parameter convert alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'scope=spfile; alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'scope=spfile; shutdown immediate startup mount //验证 show parameter convert 3.6 设置自动管理//查看主库是否自动管理文件(作用:主库添加或删除数据文件时,备库也跟着添加删除数据文件) show parameter standby alter system set standby_file_management=auto; //验证 show parameter standby 3.7 修改fal参数alter system set fal_client=orcl; alter system set fal_server=orclstd; //验证 show parameter fal; 3.8 配置密码文件//由于默认的密码不知道,所以可以删除或备份一份密码文件 cd /u01/app/oracle/product/11.2.0/db_1/dbs/ mv orapworcl orapworcl.bak 创建密码文件 orapwd file=orapworcl password=jinhetech force=y 3.9 配置监听cd/u01/app/oracle/product/11.2.0/db_1/network/admin/ 在监听文件里添加: vim listener.ora =========================================================== SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = orcl) ) )
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION= (ADDRESS =(PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS =(PROTOCOL = TCP)(HOST = compute02)(PORT = 1521)) ) )
ADR_BASE_LISTENER = /u01/app/oracle =========================================================== 重启监听,重启数据库. 验证: lsnrctl status //配置远程监听,添加一下内容 vim tnsnames.ora =========================================================== ORCL = (DESCRIPTION = (ADDRESS =(PROTOCOL = TCP)(HOST = compute02)(PORT = 1521)) (CONNECT_DATA = (SERVER =DEDICATED) (SERVICE_NAME = orcl) ) ) ORCLSTD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = controller)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclstd) ) ) =========================================================== 验证 tnsping compute02 3.10 设置参数文件//生成修改主库的pfile用于备库 create pfile='/tmp/init.ora' from spfile; //生成自己的pfile配置文件 create pfile from spfile; //分别修改主备库配置 //修改下面两个文件配置,手动添加红色字段 vim /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora vim /tmp/init.ora =========================================================== orcl.__db_cache_size=7784628224 orcl.__java_pool_size=268435456 orcl.__large_pool_size=268435456 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE setfrom environment orcl.__pga_aggregate_target=11005853696 orcl.__sga_target=16106127360 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=7516192768 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/' *.db_name='orcl' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=4070572032 *.db_unique_name='ORCL' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.fal_client='ORCL' *.fal_server='ORCLSTD' *.log_archive_config='DG_CONFIG=(orcl,orclstd)' *.log_archive_dest_1='LOCATION=/u01/app/oracle/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' *.log_archive_dest_2='SERVICE=orclstd ASYNC LGWRVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd' *.log_archive_format='arch_%r_%t_%s.arc' *.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/' *.memory_target=26902265856 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable =========================================================== 3.11 创建控制文件alter database create standby controlfile as '/tmp/controlstb.ctl'; 3.12 传输文件!!!!!!按顺序执行!!!!! //传输密码文件到备库 cd /u01/app/oracle/product/11.2.0/db_1/dbs/ scp orapworcl oracle@172.16.0.1:/u01/app/oracle/product/11.2.0/db_1/dbs/ //拷贝监听文件到备库 cd /u01/app/oracle/product/11.2.0/db_1/network/admin/ scp listener.ora tnsnames.ora oracle@172.16.0.1:/u01/app/oracle/product/11.2.0/db_1/network/admin/ //拷贝参数文件到备库 scp /tmp/init.ora oracle@172.16.0.1:/u01/app/oracle/product/11.2.0/db_1/dbs/ //拷贝数据文件到备库 scp /u01/app/oracle/oradata/orcl/* oracle@172.16.0.1:/u01/app/oracle/oradata/orcl/ //拷贝控制文件到备库 scp /tmp/controlstb.ctloracle@172.16.0.1:/u01/app/oracle/oradata/orcl/control01.ctl scp /tmp/controlstb.ctl oracle@172.16.0.1:/u01/app/oracle/flash_recovery_area/orcl/control02.ctl 第四章 备库配置4.1 创建所需的文件夹//在备库创建所需的文件夹 mkdir -p/u01/app/oracle/oradata/orcl/ mkdir -p/u01/app/oracle/arch/ mkdir -p /u01/app/oracle/flash_recovery_area/orcl mkdir -p /u01/app/oracle/admin/orcl/adump mkdir -p /u01/app/oracle/admin/orcl/dpdump chown -R oracle:oinstall/u01/app chmod 6751/u01/app 4.2 修改参数文件cd/u01/app/oracle/product/11.2.0/db_1/dbs/ vim init.ora //修改init.ora为如下内容: =========================================================== *.db_unique_name='orclstd' fal_server='orcl' fal_client='orclstd' log_archive_dest_1='location=/u01/app/oracle/archVALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclstd' log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role)db_unique_name=orcl' ======================================================== 4.3 配置监听cd /u01/app/oracle/product/11.2.0/db_1/network/admin/ vim listener.ora ======================================================== SID_LIST_LISTENER= (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orclstd) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = orclstd) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = controller)(PORT = 1521)) ) ) =========================================================== //启动监听 lsnrctl start //验证 tnsping orcl tnsping orclstd 第五章 日常维护5.1 启动方式(先启动备库在启动主库)//备库操作 shut immediate startup nomountpfile='/u01/app/oracle/product/11.2.0/db_1/dbs/init.ora'; alter databasemount standby database; alter databaSEOpen read only; select open_modefrom v$database; alter databaserecover managed standby database using current logfile disconnect from session; select open_modefrom v$database; archive log list; //生成自己的pfile配置文件 create pfile from spfile; //主库操作 shut immediate startup //主库最终监听状态 //备库最终监听状态 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |