【DG实验】搭建physical standby——rman
来源：互联网 发布：算法相关的书 编辑：程序博客网 时间：2024/06/05 08:15
使用RMAN 复制搭建物理Data Guard
生产库搭建物理DG一般使用rman而不是copy。Rman若不需要修改db_name, db_unique_name这两个参数，就可以完全不停业务进行搭建物理DG，而copy需要停业务，主库一致处于mount状态！
1.DG环境说明
数据库版本：11.2.0.1.0
主库：192.168.88.3 sharon
备库：192.168.88.2 sharon
使用静态IP，要确保IP是固定的，不要用DHCP来分配！
Database DB_UNIQUE_NAME Oracle Net Service Name
Primary sharon sharon_pd
Physical standby sharon sharon_st
2.主库设置为force logging 模式
SQL> alter database force logging;
SQL> select FORCE_LOGGING from v$database;
FORCE_LOGGING
--------------------------
YES
3. 主库修改为归档模式
SQL> archive log list;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter system set log_archive_dest_1='location=/u01/arch/' scope=spfile;
SQL> archive log list;
4. 在主备库分别创建Listener，并启动
用netca或netmgr配
启动监听
[oracle@sharon admin]$lsnrctl status|start|stop
此步若不清楚，可参考用copy file方式配置物理DG的部分。
http://blog.csdn.net/sharqueen/article/details/8853118
5. 在主备库添加Oracle Net Service（tnsnames.ora）
注意：
sharon_pd,sharon_st——去连接的，不代表实例名
GLOBAL_DBNAME = sharon这个名可以随便改
tsnping下，是否通
[oracle@sharon admin]$tnsping sharon_pd
[oracle@sharon admin]$tnsping sharon_st
6. 在主库创建pfile 文件并修改pfile 内容
SQL> create pfile from spfile;
在pfile添加如下内容：
#add for primary dg
*.db_unique_name='sharon_pd'
*.log_archive_config='dg_config=(sharon_pd,sharon_st)'
*.log_archive_dest_1='location=/u01/archlog valid_for=(all_logfiles,all_roles) db_unique_name=sharon_pd'
*.log_archive_dest_2='service=sharon_st reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sharon_st'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='sharon_st'
*.fal_client=’sharon_pd’ –10g 要写！11g不需要写！
注意：
（1）在oracle 11g中，废弃了fal_client 参数，也就是说不用配置。
（2）log_archive_dest_n 这个参数中的Oracle Net Service名首位是一样的，前面写哪个，最后的db_unique_name就写哪个，不要搞错了。
（3）如果主备库的路径不同，修改在主库的参数文件里添加如下2个参数：
*.log_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'
*.db_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'
--用修改过的pfile重新生成spfile：
SQL>startup pfile=’$ORACLE_HOME/dbs/initsharon.ora’;
SQL> create spfile from pfile;
7. 在备库创建相关的目录
参考pfile，不要漏了
[oracle@sharon sharon]$ mkdir –p /u01/app/oracle/oradata/sharon
[oracle@sharon sharon]$ mkdir –p /u01/app/oracle/fast_recovery_area/sharon
[oracle@sharon sharon]$ mkdir –p /u01/app/oracle/admin/sharon/adump
[oracle@sharon sharon]$ mkdir –p /u01/app/oracle/admin/sharon/dpdump
8. 将主库的参数文件copy到备库并修改
参数文件，我们在主库的pfile中已经修改，我们这里只需要该2个参数即可：
#add for standby dg
*.db_unique_name='sharon_st'
*.log_archive_config='dg_config=(sharon_pd,sharon_st)'
*.log_archive_dest_1='location=/u01/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=sharon_st'
*.log_archive_dest_2='service=sharon_pd reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sharon_pd'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='sharon_pd'
9. 检查主库的口令文件并copy到备库
[oracle@sharon dbs]$ scp orapwdave 192.168.88.2:/u01/app/oracle/product/11.2.0/db_1/dbs
10. 使用RMAN备份主库（一定要掌握！）
[oracle@sharon~]$ rman target /
RMAN>RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
BACKUP FORMAT '/u01/backup/db_%U_%T'skip inaccessible filesperset 5 DATABASE ;
sql 'alter system archive log current';
BACKUP FORMAT '/u01/backup/arch_%U_%T'skip inaccessible filesperset 5 ARCHIVELOG ALLDELETE INPUT;
backup current controlfile forstandby format='/u01/backup/control_%U';
release channel c2;
release channel c1;
}
通道可以加，通道多备份速度也可以加快
11. 将主库的备份文件全部copy到备库的相同位置
[oracle@sharon backup]$ scp * 192.168.88.2:/u01/backup
12. 在备库使用RMAN 恢复备库
备库
SQL> startup nomount
--在备库执行duplicate操作：
[oracle@sharon2 ~]$ rman target sys/oracle@dave_pdauxiliary sys/oracle@dave_st;
Duplicae数据库：
RMAN> duplicate target database for standby nofilenamecheck dorecover;
-- nofilenamecheck主备库路径相同就一定要加该参数！
13. 启动备库
--完成duplicate 之后，备库就是mount状态：
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
启动MRP，使之redo apply
SQL> alter database recover managed standbydatabase disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
14. 在主备库添加online redo log 和standby redolog
1. 主库要加standbyredo log
2. standby redolog 大小和 online redo 一样大
3. standby redolog group 比 online redo log group 多一组
SQL> col member fora50
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE /u01/app/oracle/flash_recovery_area/sharon/onlinelo
g/o1_mf_3_8q606gxz_.log
2 ONLINE /u01/app/oracle/flash_recovery_area/sharon/onlinelo
g/o1_mf_2_8q606c2j_.log
1 ONLINE /u01/app/oracle/flash_recovery_area/sharon/onlinelo
g/o1_mf_1_8q6066pl_.log
4 STANDBY /u01/app/oracle/flash_recovery_area/sharon/onlinelo
g/o1_mf_4_8q606m0h_.log
发现恢复后的备库数据文件在FRA里，需要手动改回来，将其删除再重建。
删除standbylogfile，再添加
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
–报改错是因为启动了MRP
停MRP再drop
alter database recover managed stanby database cancel;
SQL> alterdatabase drop logfile group 4;
alter databasedrop logfile group 4
*
ERRORat line 1:
ORA-00261:log 4 of thread 1 is being archived or modified
ORA-00312:online log 4 thread 1:'/home/oracle/app/flash_recovery_area/ISPACE_ST/onlinelog/o1_mf_4_8qhpmhdk_.log'
该报错说明log 4是current，先删除其他standby logfile
SQL> alter databasedrop logfile group 5;
SQL> alter databasedrop logfile group 6;
SQL> alter databasedrop logfile group 7;
添加5、6、7
SQL> ALTER DATABASEADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/sharon/redo05.log') size50M;
SQL> ALTER DATABASEADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/sharon/redo06.log') size50M;
SQL> ALTER DATABASEADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/sharon/redo07.log') size50M;
用实施应用模式打开MRP，主库切换日志当log 4不是current后再drop
SQL> alter database recover managedstandby database using current logfile disconnect from session；
主库：SQL> alter system switch logfile;
SQL> alter databasedrop logfile group 4;
SQL> ALTER DATABASEADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/sharon/redo04.log') size50M;
删除online redologfile，再添加
SQL> alter databasedrop logfile group 1;
alter databasedrop logfile group 1
*
ERROR at line1:
ORA-01275:Operation DROP LOGFILE is not allowed if standby file management is automatic.
SQL> alter system setstandby_file_management=MANUAL;
Systemaltered.
SQL> alter databasedrop logfile group 1;
alter databasedrop logfile group 1
*
ERROR at line1:
ORA-01623:log 1 is current log for instance sharon (thread 1) - cannot drop
ORA-00312:online log 1 thread 1: '/u01/app/oracle/flash_recovery_area/SHARON_ST/onlinelog/o1_mf_1_8qhswqjt_.log'
先删2、3
SQL> alter databasedrop logfile group 2;
Databasealtered.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line1:
ORA-01567:dropping log 3 would leave less than 2 log files for instance sharon (thread 1)
ORA-00312:online log 3 thread 1:'/u01/app/oracle/flash_recovery_area/SHARON_ST/onlinelog/o1_mf_3_8qhswy4h_.log'
先加group 2，再删3
SQL> alter databaseadd logfile group 2 ('/u01/app/oracle/oradata/sharon/redo02.log') size50M;
Databasealtered.
SQL> alter databasedrop logfile group 3;
Databasealtered.
SQL> alter databaseadd logfile group 3 ('/u01/app/oracle/oradata/sharon/redo03.log') size 50M;
Databasealtered.
启动MRP，主库切换日志
SQL> alter databaserecover managed standby database disconnect from session;
Databasealtered.
主库：
SQL> alter systemswitch logfile;
Systemaltered.
备库：
SQL> select * fromv$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#NEXT_TIME
-------------------- ---------- ---------- ---------- ---------- --- ----------------------------- --------- ------------ ---------
1 1 25 52428800 512 1 YES CLEARING 874946 25-APR-13 892869 25-APR-13
2 1 26 52428800 512 1 YES CLEARING 892869 25-APR-13 892984 25-APR-13
3 1 27 52428800 512 1 YES CLEARING_CURRENT 892984 25-APR-13 0
Group 1不是current，可以drop了
停MRP删group 1
SQL> alter databaserecover managed standby database cancel;
Databasealtered.
SQL> alter databasedrop logfile group 1;
alter databasedrop logfile group 1
*
ERROR at line1:
ORA-01624:log 1 needed for crash recovery of instance sharon (thread 1)
ORA-00312:online log 1 thread 1: '/u01/app/oracle/flash_recovery_area/SHARON_ST/onlinelog/o1_mf_1_8qhswqjt_.log'
SQL> alter databaseclear logfile group 1;
Databasealtered.
SQL> alter databasedrop logfile group 1;
Databasealtered.
SQL> alter databaseadd logfile group 1 ('/u01/app/oracle/oradata/sharon/redo01.log') size 50M;
Database altered.
SQL> select * fromv$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------------- --------------------------------------------------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/sharon/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/sharon/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/sharon/redo01.log NO
4 STANDBY /u01/app/oracle/oradata/sharon/redo04.log NO
5 STANDBY/u01/app/oracle/oradata/sharon/redo05.log NO
6 STANDBY/u01/app/oracle/oradata/sharon/redo06.log NO
7 STANDBY /u01/app/oracle/oradata/sharon/redo07.log NO
7 rowsselected.
成功！
15. 测试主库
SQL> col error for a10
SQL> col dest_name for a20
SQL> select DEST_NAME,STATUS,PROCESS,ERROR,TRANSMIT_MODE from v$archive_dest WHERE TARGET='STANDBY';
DEST_NAME STATUS PROCESS ERROR TRANSMIT_MOD
----------------------------- ---------- ---------- ------------
LOG_ARCHIVE_DEST_2 VALID LGWR ASYNCHRONOUS
SQL> SELECT PROCESS,STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------------------- ---------- ---------- ---------- ----------
ARCH CLOSING 1 43 1 6
ARCH CLOSING 1 23 1 2574
ARCH CLOSING 1 44 1 4
ARCH CLOSING 1 45 1 2
LNS WRITING 1 46 156 1
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
43 YES
44 YES
45 YES
主库：
SQL> selectmax(sequence#) from v$archived_log;
SQL> altersystem switch logfile;
SQL> altersystem switch logfile;
SQL> selectmax(sequence#) from v$archived_log;
SQL> SELECTPROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY; --速度要快，就可以看到LNS进程了！
SQL> select sequence#,applied from v$archived_log;
有问题时如何考虑？
1. 检查日志是否传过来
2. 为什么不apply
1） mrp 进程没有启动
ps-ef |grep mrp
2) alert log
- 【DG实验】搭建physical standby——rman
- 【DG实验】搭建physical standby——copy files
- DG physical standby 搭建
- 实验DG Snapshot Standby
- Oracle DG之--构建Physical Standby
- Data Guard——使用 RMAN 创建单实例物理(physical) standby 数据库
- DG(1)Performing a Failover to a Physical Standby Database
- Oracle DG之--构建Physical Standby（一主库对应多备库）
- Linux 平台 Physical Standby 搭建ADG
- 搭建ORACLE10G DATA GUARD--->Physical Standby
- 利用Rman搭建物理standby
- Step byStep Guide on Creating Physical Standby Using RMAN DUPLICATE
- data guard中利用rman构建physical standby
- rman 在线不停机，搭建dg
- 11G DG搭建--rman方式
- 利用rman搭建DG备用库
- DG(2)Flashing Back a Failed Primary Database into a Physical Standby Database
- Oracle Data Guard Linux 平台 Physical Standby 搭建实例
- 【COCOS2DX-LUA 脚本开发之十三】解决lua项目编译Android出现get data from file failed、Cocos2dxActivity cannot be 路径等问题 .
- 【xinfanqie】Windows 8开机后自动进入桌面的策略
- Flex4与java交互
- mac os x 命令+10个常用命令行工具
- vsftpd主配置文件基本说明
- 【DG实验】搭建physical standby——rman
- 分享一个遍历当前文件夹下所以子目录，并在子目录中执行其他操作的shell脚本
- etc
- paip.TechExcel DevSuite IIS 根目录结构
- JS 压缩工具 closure compiler
- java 连接MySql
- C语言程序设计自学全套视频教程 C++数据结构 编程
- 轻量级的HTTP开发库 Unirest
- SQL 2000分析器連接SQL 2005
