Chinaunix首页 | 论坛 | 博客

东来

首页　| 　博文目录　| 　关于我

lzp_0004

  • 博客访问： 667323
  • 博文数量： 128
  • 博客积分： 265
  • 博客等级： 二等列兵
  • 技术积分： 1464
  • 用 户 组： 普通用户
  • 注册时间： 2011-09-27 20:44
个人简介

just do it

文章分类

全部博文（128）

文章存档

2023年（1）

2020年（1）

2019年（1）

2018年（3）

2017年（6）

2016年（17）

2015年（16）

2014年（39）

2013年（34）

2012年（10）

我的朋友
最近访客
推荐博文
相关博文
oracle11gR2 dataguard部署测试（单实例）

分类： SOLARIS

2015-09-24 10:18:33


点击(此处)折叠或打开

  1. 搭建环境：
  2. OS：Solaris10 x86-64
  3. DB：Oracle 11.2.0
  4. 主库所在主机：sol01
  5. 备库所在主机：sol02
  7. 2 主库部署步骤
  8. 2.1 主库操作
  9. 创建归档目录，开启归档模式。
  10. su – oracle
  11. mkdir /orahome/oracle/archivelog
  12. sqlplus /as sysdba
  13. shutdown immediate
  14. startup mount
  16. alter database archivelog;
  18. 查看是否force_logging模式
  19. SQL> select log_mode,force_logging from v$database;
  20. 开启force_logging模式
  21. alter database force logging;
  23. create pfile from spfile;
  25. 创建备库控制文件
  26. alter database create standby controlfile as '/orahome/oracle/bak/controlfile01.ctl';
  28. 创建备库日志组路径
  29. alter database add standby logfile group 4 '/orahome/oracle/app/oradata/test/standbyredo04.log' size 50m;
  30. alter database add standby logfile group 5 '/orahome/oracle/app/oradata/test/standbyredo05.log' size 50m;
  31. alter database add standby logfile group 6 '/orahome/oracle/app/oradata/test/standbyredo06.log' size 50m;
  32. alter database add standby logfile group 7 '/orahome/oracle/app/oradata/test/standbyredo07.log' size 50m;
  34. cd $ORACLE_HOME/dbs
  35. 备份参数文件
  36. cp inittest.ora  inittest.ora.bk
  37. 2.2 在inittest.ora参数文件中加入：
  38. 修改参数文件：
  39. db_unique_name=test
  40. log_archive_config='dg_config=(test,test_dg)'
  41. log_archive_dest_1='location=/orahome/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=test'
  42. log_archive_dest_2='service=test_dg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=test_dg'
  43. log_archive_dest_state_1=enable
  44. log_archive_dest_state_2=enable
  45. fal_server=test_dg
  46. fal_client=test
  47. db_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
  48. log_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
  49. standby_file_management=auto
  51. 2.3 使更改参数生效：
  52. sqlplus / as sysdba
  53. startup nomount pfile= '$ORACLE_HOME/dbs/inittest.ora';
  54. create spfile from pfile;
  55. shutdown immediate
  56. startup mount
  59. 2.4 把相应的文件SCP到standby主机：
  60. 控制文件：
  61. scp /orahome/oracle/bak/controlfile01.ctl 192.168.0.22: $ORACLE_BASE/oradata/$ORACLE_SID/control01.ctl
  62. scp /orahome/oracle/bak/controlfile01.ctl 192.168.0.22: $ORACLE_BASE/oradata/$ORACLE_SID/control02.ctl
  63. oracle口令文件：
  64. scp orapwgisdb1 192.168.111.129:/home/oracle/u01/11.2.3/dbhome_1/dbs/orapwgisdb2
  65. oracle数据文件：
  66. scp $ORACLE_BASE/oradata/$ORACLE_SID/*.dbf 192.168.0.22:$ORACLE_BASE/oradata/$ORACLE_SID/
  67. oracle redo日志文件（不包括standyredo.log）
  68. scp $ORACLE_BASE/oradata/$ORACLE_SID/*.log 192.168.0.22:$ORACLE_BASE/oradata/$ORACLE_SID/
  69. 参数文件
  70. scp $ORACLE_HOME/dbs/inittest.ora 192.168.0.22:$ORACLE_HOME/dbs/
  71. 2.5 更改备库使用的pfile:inittest.ora
  72. db_unique_name=test_dg
  73. log_archive_config='dg_config=(test,test_dg)'
  74. log_archive_dest_1='location=/orahome/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=test_dg'
  75. log_archive_dest_2='service=test lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=test'
  76. log_archive_dest_state_1=enable
  77. log_archive_dest_state_2=enable
  78. fal_server=test
  79. fal_client=test_dg
  80. db_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
  81. log_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
  82. standby_file_management=auto
  83. 2.6 配置监听：
  84.  bash-3.00$ more tnsnames.ora
  85. test =
  87.   (DESCRIPTION =
  89.     (ADDRESS_LIST =
  91.       (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.21 )(PORT = 1521))
  93.     )
  95.     (CONNECT_DATA =
  97.       (SERVICE_NAME = test)
  99.     )
  101.   )
  103. test_dg =
  105.   (DESCRIPTION =
  107.     (ADDRESS_LIST =
  109.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.22)(PORT = 1521))
  111.     )
  113.     (CONNECT_DATA =
  115.       (SERVICE_NAME = test_dg)
  117.     )
  119.   )
  120. 将tnsnames.ora文件拷贝到备库相同位置。
  121. 2.7 打开主库：
  123. Alter database open;
  124. 日志如下：
  127. 3 从库上操作：
  128. 3.1 创建需要的目录
  129. mkdir -p $ORACLE_BASE/oradata/test
  130. mkdir -p $ORACLE_BASE/admin/test/{a,dp}dump
  131. sqlplus / as sysdba
  132. startup nomount pfile='$ORACLE_HOME/dbs/inittest.ora';
  133. create spfile from pfile;
  134. startup nomount
  137. 3.2 配置网络服务
  138. 启动监听(lsnrctl start),再tnsnames.ora中加入如下内容
  139. bash-3.00$ more tnsnames.ora
  140. test =
  142.   (DESCRIPTION =
  144.     (ADDRESS_LIST =
  146.       (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.21 )(PORT = 1521))
  148.     )
  150.     (CONNECT_DATA =
  152.       (SERVICE_NAME = test)
  154.     )
  156.   )
  158. test_dg =
  160.   (DESCRIPTION =
  162.     (ADDRESS_LIST =
  164.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.22)(PORT = 1521))
  166.     )
  168.     (CONNECT_DATA =
  170.       (SERVICE_NAME = test_dg)
  172.     )
  174.   )
  176. 查看tnsping是否互通
  178. 将备库启动到mount状态
  179. alter database mount;
  181. 添加备库日志组
  182. alter database add standby logfile group 4 '/orahome/oracle/app/oradata/test/standbyredo04.log' size 50m;
  183. alter database add standby logfile group 5 '/orahome/oracle/app/oradata/test/standbyredo05.log' size 50m;
  184. alter database add standby logfile group 6 '/orahome/oracle/app/oradata/test/standbyredo06.log' size 50m;
  185. alter database add standby logfile group 7 '/orahome/oracle/app/oradata/test/standbyredo07.log' size 50m;
  186. 3.3 开启日志应用
  187. alter database recover managed standby database disconnect from session;
  188. 相关LOG:
  190.  alter database recover managed standby database disconnect from session
  191. Attempt to start background Managed Standby Recovery process (test)
  192. Thu Sep 24 09:01:44 2015
  193. MRP0 started with pid=26, OS id=3901
  194. MRP0: Background Managed Standby Recovery process started (test)
  195. Serial Media Recovery started
  196. Managed Standby Recovery not using Real Time Apply
  197. Waiting for all non-current ORLs to be archived...
  198. All non-current ORLs have been archived.
  199. Media Recovery Log /orahome/oracle/archivelog/1_79_891106389.dbf
  200. Completed: alter database recover managed standby database disconnect from session
  201. Media Recovery Waiting for thread 1 sequence 80 (in transit)
  202. 4 测试：
  203. 4.1 主库上：
  204. Conn scott/tiger
  205. SQL> create table a as select * from emp;
  206. Table created.
  207. SQL> conn / as sysdba
  208. Connected.
  209. SQL> alter system switch logfile;
  210. System altered.
  211. SQL>
  212. 4.2 从库上操作：
  213. SQL> alter database recover managed standby database cancel;
  214. Database altered.
  215. SQL> alter database open read only;
  216. Database altered.
  217. SQL> conn scott/tiger
  218. Connected.
  219. SQL> select * from tab;
  221. TNAME                          TABTYPE  CLUSTERID
  222. ------------------------------ ------- ----------
  223. A                              TABLE
  224. BONUS                          TABLE
  225. DEPT                           TABLE
  226. EMP                            TABLE
  227. SALGRADE                       TABLE
  229. 成功完成了同步
  231. 5 转换成ADG（active dataguard）
  232. /////////////////备库 Database mounted.///////////////
  233. SQL> alter database recover managed standby database disconnect from session;
  234. Database altered.
  235. SQL> alter database recover managed standby database cancel;
  236. Database altered.
  237. SQL> alter database open;          
  238. Database altered.
  239. SQL> alter database recover managed standby database using current logfile disconnect ;
  240. Database altered.
  241. SQL> SELECT OPEN_MODE FROM V$DATABASE;
  243. OPEN_MODE
  244. --------------------
  245. READ ONLY WITH APPLY
  247. 6 Swithover 测试：
  248. 6.1 检查主从库的信息：
  249. 主库上查询：
  250. SQL> select status ,gap_status from v$archive_dest_status where dest_id in (1,2);
  251.  
  252. STATUS    GAP_STATUS
  253. --------- ------------------------
  254. VALID
  255. VALID     NO GAP
  256. SQL> select switchover_status from v$database;
  257.  
  258. SWITCHOVER_STATUS
  259. --------------------
  260. TO STANDBY
  261. 此处的信息一定要显示是“to standby”
  262. 从库信息：
  263. SQL> select status,gap_status from v$archive_dest_status where dest_id in (1,2);
  264.  
  265. STATUS    GAP_STATUS
  266. --------- ------------------------
  267. VALID
  268. VALID     NO GAP
  270. 6.2 开始switchover:
  271. -- 主库的执行：
  272. SQL> alter database commit to switchover to physical standby with session shutdown ;
  273.  
  274. Database altered.
  275. SQL> shutdown abort;
  276. ORACLE instance shut down.
  277. SQL> startup mount
  278. ORACLE instance started.
  279.  
  280. Total System Global Area  413372416 bytes
  281. Fixed Size                  2228904 bytes
  282. Variable Size             339742040 bytes
  283. Database Buffers           62914560 bytes
  284. Redo Buffers                8486912 bytes
  285. Database mounted.
  286. SQL> select switchover_status from v$database;
  287.  
  288. SWITCHOVER_STATUS
  289. --------------------
  290. TO PRIMARY
  291.  
  292. SQL> alter database open;
  293.  
  294. Database altered.
  295.  
  296. SQL> select switchover_status from v$database;
  297.  
  298. SWITCHOVER_STATUS
  299. --------------------
  300. TO PRIMARY
  302. --- 从库上操行：
  303. SQL> select switchover_status from v$database;
  304.  
  305. SWITCHOVER_STATUS
  306. --------------------
  307. TO PRIMARY
  308.  
  309. SQL> alter database commit to switchover to primary with session shutdown ;
  310.  
  311. Database altered.
  312. SQL> select open_mode from v$database;
  313.  
  314. OPEN_MODE
  315. --------------------
  316. MOUNTED
  317.  
  318. SQL> alter database open;
  319.  
  320. Database altered.
  323. ---- 在现在的从库上（之前的主库上执行）：
  324. SQL> select open_mode from v$database;
  325.  
  326. OPEN_MODE
  327. --------------------
  328. READ ONLY
  329.  
  330. SQL> alter database recover managed standby database using current logfile disconnect ;
  331.  
  332. Database altered.
  333. SQL> select open_mode from v$database;
  334.  
  335. OPEN_MODE
  336. --------------------
  337. READ ONLY WITH APPLY
  339. 6.3 切换后 再次测试：
  340. --- 主上执行：
  341.  SQL> conn scott/tiger
  342. Connected.
  343. SQL> create table b as select * from tab;
  345. Table created.
  347. SQL> conn /as sysdba
  348. Connected.
  349. SQL> alter system switch logfile;
  351. System altered.
  353. SQL>  conn scott/tiger
  354. Connected.
  355. SQL> select count(*) from a;
  357.   COUNT(*)
  358. ----------
  359.         14
  361. SQL> insert into a select * from a;
  363. 14 rows created.
  365. SQL> r
  366.   1* insert into a select * from a
  368. 28 rows created.
  370. SQL> r
  371.   1* insert into a select * from a
  373. 56 rows created.
  375. SQL> r
  376.   1* insert into a select * from a
  378. 112 rows created.
  380. SQL> select count(*) from a;
  382.   COUNT(*)
  383. ----------
  384.        224
  385. SQL> conn /as sysdba
  386. Connected.
  387. SQL> alter system switch logfile;
  389. System altered.
  391. 查看主库角色
  392. SQL> select database_role from v$database;
  394. DATABASE_ROLE
  395. ----------------
  396. PRIMARY
  397. 再从库上查询：
  398.  SQL> conn scott/tiger
  399. Connected.
  400. SQL> select * from tab;
  402. TNAME                          TABTYPE  CLUSTERID
  403. ------------------------------ ------- ----------
  404. A                              TABLE
  405. B                              TABLE
  406. BONUS                          TABLE
  407. DEPT                           TABLE
  408. EMP                            TABLE
  409. SALGRADE                       TABLE
  411. 6 rows selected.
  412. SQL> r
  413.   1* select count(*) from a
  415.   COUNT(*)
  416. ----------
  417.        224
  418. 查看备库角色
  419. SQL> select database_role from v$database;
  421. DATABASE_ROLE
  422. ----------------
  423. PHYSICAL STANDBY

阅读(1648) | 评论(0) | 转发(0) |
0

上一篇：Linux下双网卡绑定bond0

下一篇：oracle备份脚本

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6