ORACLE11g R2 DG FOR OEL7.1 安装实施
来源:互联网 发布:php while break 编辑:程序博客网 时间:2024/05/18 03:56
开启归档
shutdown immediate;
startup mount;
alter database archivelog;
二、开启强制logging
alter database force logging;
三、记录信息
查看日志和数据文件的位置,便于数据文件的目录转换。
select name from v$datafile;
select group#,member from v$logfile;
show parameter control;
四、修改hosts文件(rac ,dg上的操作)
Vi /etc/hosts
[oracle@dg1 ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.133.120 dg1
192.168.133.121 dg2
五、配置监听和tns
1) 配置listener.ora 文件。或者使用netmgr配置。
2) tnsname.ora文件修改。
ORCLDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcldg)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
3)拷贝密码文件到目标机
scporapworcl dg2:/u01/app/oracle/product/11.2.0/db_1/dbs/
4)双机访问。是否成功
sqlplussys/123@orcl as sysdba
sqlplussys/123@orcldg as sysdba
六、创建参数文件(RAC、备库)
1)使用命令行配置参数:(主库)
alter system set fal_client=orcl ;
alter system set fal_server=orcldg;
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)';
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
alter system set log_archive_dest_2='SERVICE=orcldg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg';
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;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
alter system set DB_UNIQUE_NAME='orcl' scope=spfile;
注意:
db_file_name_convert,和log_file_name_convert最后路径不要添加'/',要么全部加上/,要么全都不加
主库参数展示:
orcl.__db_cache_size=121634816
orcl.__java_pool_size=4194304
orcl.__large_pool_size=12582912
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=188743680
orcl.__sga_target=276824064
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=121634816
orcl.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_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/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='ORCL'
*.fal_server='ORCLDG'
*.log_archive_config='DG_CONFIG=(orcl,orcldg)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='SERVICE=orcldg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'
*.memory_target=463470592
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
2)备库参数展示
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=463470592
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_name='orcl'
*.db_unique_name='orcl'
*.fal_client='ORCLDG'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(orcl,orcldg)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='SERVICE=orcl LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'
*.standby_file_management='AUTO'
3)备库的参数文件修改后。传输到目标机,主库的参数文件修改后能否启动。
4)检查参数:
show parameter log;
show parameter control;
show parameter
七、使用备份做DG
1)备份主库:
2)关闭数据库:
登录到sqlplus 上关闭。
3)启动一个实例,关闭实例。
4)运行备份脚本。
run {
crosscheck archivelog all;
delete expired archivelog all;
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup incremental level 0
format '/home/oracle/backup/db_full_%U.bkp'
tag '2015-02-027-FULL'
database
include current controlfile for standby
plus archivelog;
release channel c1;
release channel c2;
release channel c3;
}
5)备份控制文件
backup current controlfile format '/home/oracle/backup/control20150227.bak';
6)备份参数文件
backup spfile format '/home/oracle/backup/spfile20150227.bak';
7)产生备库使用的控制文件
alter database create standby controlfile as '/home/oracle/backup/standby.ctl'
8) 出现的错误:
加上plusarchivelog 备份归档日志 报这个错误
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 05/05/2014 01:00:04
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /oracle/PRD/oraarch/PRDarch1_16336_840558852.dbf
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
a)原因 :归档日志会用 rm 命令定期清除
b)原理(从网上查的资料) :
在controlfile中记录着每一个archivelog的相关信息,当我们在OS下把这些物理文件delete掉或异常变动后,在controlfile中仍然记录着这些archivelog的信息,当我们手工清除archive目录下的文件后,这些记录并没有被我们从controlfile中清除掉,也就是oracle并不知道这些文件已经不存在了!这时候我们要做手工的清除。crosscheck archivelog all;的作用就是检查控制文件和实际物理文件的差别。
delete expired archivelog all;就是同步控制文件的信息和实际物理文件的信息。 如果单独执行crosscheck而没有执行delete那么备份还是失败的,原因是那些控制文件的信息和实际的信息还是不同。crosscheck backupset crosscheck backupset 是检查备份集和实际的文件 1 备份集有两种状态A(Available,RMAN认为该项存在于备份介质上)X(Expired,备份存在于控制文件或恢复目录中,但
是并没有物理存在于备份介质上)
c) crosscheck 的目的是检查RMAN 的目录以及物理文件,如果物理文件不存在于介质上,将标记为Expired。如果物理文件
存在,将维持Available。如果原先标记为Expired的备份集再次存在于备份介质上(如恢复了损坏的磁盘驱动器后),
crosscheck将把状态重新从Expired标记回Available。
d)解决方法:在脚本中加入
crosscheck archivelog all;
delete expired archivelog all;
八、恢复备库
1)传输备份文件和控制文件到目标机。
注意:备份文件要传输到跟源机目录一样的位置。否则会识别不到。、
Scp * orcldg:/home/oracle/backup/
2)使用rman连接主库和备库
rman target sys/123@oracle auxiliary sys/123@orcldg
4)还原数据文件。
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby dorecover nofilenamecheck ;
release channel c1;
release channel c2;
release channel c3;
release channel stby1;
}
5)查看配置的正确与否
远程归档目录查看
col dest_name for a20
col status for a20
col error for a20
select dest_name,status,error from v$archive_dest
说明:
出现log_archive_dest_1 valid
log_archive_dest_2 valid
这样的提示说明已经连通。
6)如果没有问题则应用日志:
alter database recover managed standby database disconnect from session;
7)查看当前的数据库角色
select database_role,protection_level,open_mode from v$database;
select switchover_status from v$database;
8)查看当前数据库是否有gap.
select thread#, low_sequence#, high_sequence# from v$archive_gap;
9)查看当前日志序列号
select group#,members,bytes/1024/1024,status from v$log;
select group# ,thread#, sequence#,archived from v$log;
10)切换日志文件
alter system switch logfile;
alter system checkpoint;
11)查看当前日志应用情况
select sequence#,applied from v$archived_log;
九、使用duplicate在线方式创建备库
9.1连接主库和备库:
(注意要加nocatalog
否则会报
PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
这个错误)
rman target sys/oracle@orcl auxiliary sys/oracle@orcldg nocatalog;
9.2运行复制数据库命令
run {
allocate channel prmy1 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby dorecover nofilenamecheck
from active database;
release channel prmy1;
release channel stby;
}
9.3 两边的目录都要有。否则会报
RMAN-03009: failure of backup command on prmy1 channel at 09/01/201516:15:18
ORA-17628: Oracle error 19505 returned by remote Oracle server
这个错误。
mkdir -p /u01/app/oracle/oradata/orcl/
mkdir -p /u01/app/oracle/fast_recovery_area/orcl/
9.4 这步出的错误:
1)共享空间不足:
RMAN-03002: failure of Duplicate Db command at 04/10/2014 11:51:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-00845: MEMORY_TARGET not supported on this system
原因:duplicate过程报错,执行失败。
从分析看,应该是Oracle运行和内部共享内存分配有关。Linux中的/dev/shm对应tmpfs,也就是临时共享内存结构。当前大小为:
[root@SimpleLinux ~]# df -lh
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 48G 13G 33G 28% /
tmpfs 448M 160M 288M 36% /dev/shm
当前系统中包括两个实例对象,消耗的共享内存要超过一个实力的情况。所以需要调节增大这部分大小。
[root@SimpleLinux ~]# vi /etc/fstab
tmpfs /dev/shm tmpfs defaults,size=2G 0 0
在存储空间允许的情况下,调节tmpfs方法就是在fstab中增加size参数,直接指定大小。
[root@SimpleLinux ~]# mount /dev/shm
[root@SimpleLinux ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 48G 13G 33G 28% /
tmpfs 2.0G 0 2.0G 0% /dev/shm
9.5 控制文件指定地方要正确,要提前建好目录.
mkdir –p /u01/app/orcl/oradata/orcl/
9.6 日志转换目录要正确。
+DATA/oracle/onlinelog, /u01/app/orcl/oradata/orcl, +DATA2/oracle/onlinelog, /u01/app/orcl/oradata/orcl
9.7 如果备份不在原来的位置,则需要注册备份:
catalog backuppiece '/backup/full_level0_0sni49k9_1_1_20120808';
catalog backuppiece '/backup/full_level0_0rni49jq_1_1_20120808';
catalog backuppiece '/backup/ARC_0oni2f44_1_1_20120807';
catalog backuppiece '/backup/ARC_0tni49kc_1_1_20120808';
十、增加standby日志
注意: 添加standby日志的数目=主库日志数目+1。
如果是RAC环境则是日志数目=实例日志*2 +1。
日志大小要一样。
1)主库的standby日志:
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/stdbyredo05.log') SIZE 50m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/stdbyredo06.log') SIZE 50m;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/stdbyredo07.log') SIZE 50m;
alter database add standby logfile group 8 ('/u01/app/oracle/oradata/orcl/stdbyredo08.log') SIZE 50m;
2)备库的standby日志:
alter database add standby logfile group 5('/u01/app/oracle/oradata/orcl/stdbyredo5.log') SIZE 50m;
alter database add standby logfile group 6('/u01/app/oracle/oradata/orcl/stdbyredo6.log') SIZE 50m;
alter database add standby logfile group 7('/u01/app/oracle/oradata/orcl/stdbyredo7.log') SIZE 50m;
alter database add standby logfile group 8('/u01/app/oracle/oradata/orcl/stdbyredo8.log') SIZE 50m;
十一、主库备库切换
DataGuard的主备库切换分为2种,switchover和failover.遇到突发事件,基本都是采用failover切换. 在主备库切换完成后应该修改数据库服务器的IP地址,即备库的地址.这样才能保证系统的正常运作.
switchover
一般SWITCHOVER切换都是计划中的切换,特点是在切换后,不会丢失任何的数据,而且这个过程是可逆的,整个DATA GUARD环境不会被破坏,原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。
主库上的操作
11.1查看switchover状态
select switchover_status from v$database;
11.2切换成从库
alter database commit to switchover to physical standby with session shutdown;
11.3确保实例在mount状态下
select status from v$instance;
shutdown immediate
startup mount
11.4切换成从库
alter database commit to switchover to physical standby;
11.5应用日志
alter database recover managed standby database disconnect from session;
备库上的操作:
11.6查看switchover状态
select switchover_status from v$database;
11.7关闭所有的客户端连接后切换为主库
alter database commit to switchover to primary;
11.8打开数据库
alter database open;
Failovers:
FAILOVER切换一般是PRIMARY数据库发生故障后的切换,这种情况是STANDBY数据库发挥其作用的情况。这种切换发生后,可能会造成数据的丢失。而且这个过程不是可逆的,DATA GUARD环境会被破坏。由于PRIMARY数据库已经无法启动,所以FAILOVER切换所需的条件并不多,只要检查STANDBY是否运行在最大保护模式下,如果是的话,需要将其置为最大性能模式,否则切换到PRIMARY角色也无法启动。
FailOver后,主库会做一次resetlogs,整个DG环境也就破坏了.
11.9查看是否有日志GAP,没有应用的日志
sql> select unique thread#, max(sequence#) over(partition by thread#) last from v$archived_log;
sql> select thread#, low_sequence#, high_sequence# from v$archive_gap;
如果有,则拷贝过来并且注册
sql> alter database register physical logfile '路径';
重复查看直到没有应用的日志。
11.10然后停止应用归档:
alter database recover managed standby database cancel;
11.11下面将STANDBY数据库切换为PRIMARY数据库:
alter database recover managed standby database finish;
select database_role from v$database;
alter database commit to switchover to primary;
alter database open;
11.12检查数据库是否已经切换成功:
select database_role from v$database;
十二、后续操作
2.修改备库的ip地址为以前主库的ip地址
3.修改备库监听的ip
4.启动数据库
附A: 配置DG所要的一些注意事项和命令。
1)设置好环境变量
2)数据库名字要一样
env ~/.bash_profile
3)数据库的名字主库和备库要一样
4)主库和备库的唯一名字可以不一样
5)查看监听状态使其只有一个服务注册上。
6)然后使用修改后的参数文件启动到nomount 状态。放置好备库的控制文件/u01/app/orcl/oradata/orcl/standby.ctl,后
启动数据库到mount状态。
alter database mount standby database;
7)主库备库都要实时观察报错日志。
tail -f alert_oracle.log
7)停止standby的自动恢复状态
alter database recover managed standby database finish;
8)取消应用的日志
alter database recover managed standby database cancel;
9)开启闪回
alter database flashback on;
10)应用日志
alter database recover managed standby database disconnect from session;
11)添加online 日志组。
alter database add logfile group 4
12)增大联机日志文件。
先删除:
alter database drop logfile group 1;
alter database drop logfile group2('/u01/app/orcl/oradata/orcl/red/o02.log');
alter database drop logfile group3('/u01/app/orcl/oradata/orcl/red/o03.log');
alter database drop logfile group4('/u01/app/orcl/oradata/orcl/red/o04.log');
后添加:
alter database add logfile group 1 ('/u01/app/orcl/oradata/orcl/red/o03.log') size 500m reuse;
alter database add logfile group 3 ('/u01/app/orcl/oradata/orcl/redo03.log') size 500m reuse;
alter database add logfile group 2 ('/u01/app/orcl/oradata/orcl/redo02.log') size 500m reuse;
13)配置好数据库后,应用日志
alterdatabase recover managed standby database disconnect from session;
删除tnsname 文件中的服务别名,则不会影响DG的数据同步。如果备库重启重新同步则会报错。
附C: 修改主机的归档时间
1.设置归档时间:
alter system set archive_lag_target=300;
设置切换联机日志文件5分钟切换一次。
2、改变DG的联机日志文件大小。
2.1.在主库和备库设置standby_file_management 为manual
1)在主库的设置
show parameter standby_file
alter system set standby_file_management='manual';
show parameter standby_file
2)在备库上的停止恢复应用并设置参数:
alter database recover managed standby database cancel;
show parameter standby_file
alter system set standby_file_management='manual';
show parameter standby_file
col MEMBER for a40
select a.group#,a.bytes/1024/1024 mb,b.member from v$log a,v$logfile b where a.group#=b.group#
2.2查看当前的日志文件
select group#,member from v$logfile;
2.3 删除备库的联机日志和主库的联机日志加一个删一个
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database add logfile group 1;
alter database add logfile group 2;
alter database add logfile group 3;
3.如果没有问题则应用日志
alter database recover managed standby database disconnect from session;
- ORACLE11g R2 DG FOR OEL7.1 安装实施
- ORACLE11g R2 DG FOR OEL7.1
- Oracle11g+CentOS6+DG安装记录
- oracle11g dataguard安装实施
- oracle11g r2 rac安装
- RedHat5安装Oracle11G R2
- Oracle11g R2 安装
- linux Oracle11g R2的安装
- windows7下oracle11g R2安装
- linux下安装oracle11g R2
- CentOS 6.5 安装Oracle11g R2
- CentOS6.5安装Oracle11G R2
- Centos6.6 安装oracle11g r2
- CentOS 6.5 安装Oracle11g R2
- linux下安装oracle11g R2
- centos7.2安装oracle11g r2
- Oracle11g RAC for Linux 实施手册
- ORACLE11g R2 for Linux 下载地址
- OpenDayLight+Mininet测试环境搭建
- 横.竖屏旋转方法
- leetcode -- Minimum Height Trees -- 关于graph的,重点
- eclipse jetty插件的安装
- Android启动页面的正确打开方式
- ORACLE11g R2 DG FOR OEL7.1 安装实施
- ios 第二课 类的初始化
- ios中的手势
- 最好的Python机器学习库
- C语言预处理及特殊函数介绍(extern/exit/atexit/volatile/static/calloc/realloc等)PART ONE
- [笔记] 大型网站技术架构——核心原理与案例分析 [三]
- FireFox 之 event is not defined
- 10G(82599EB) 网卡测试优化(总)
- 欧冠16强抽签概率计算器