ITPub博客

首页 > 数据库 > Oracle > ORACLE11g R2 三节点 DG FOR OEL6.5安装

ORACLE11g R2 三节点 DG FOR OEL6.5安装

原创 Oracle 作者:jikcheng 时间:2015-12-11 14:10:30 0 删除 编辑


第一步:开启归档(主库RAC)

shutdown immediate;

startup mount;

alter system archive log;

第二步:开启强制logging(主库RAC)

alter database force logging;

第三步:记录信息(主库RAC)

查看日志和数据文件的位置,便于数据文件的目录转换。

select name from v$datafile;

select group#,member from v$logfile;

show parameter controlfile;

第四步:配置监听和tns (主库和备库)

1)    配置listener.ora 文件。或者使用netmgr配置。

2)    tnsname.ora文件修改。

orcl =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

orcldg=

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcldg)

    )

  )

 

orclst=

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dg3)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orclst)

    )

  )

 

sqlplus sys/123@orcldg as sysdba

sqlplus sys/123@orcl as sysdba

sqlplus sys/123@orclst as sysdba

3) 拷贝密码文件到目标机

第五步:创建参数文件

1)  使用命令行配置主库

alter system set fal_client='orcl';

alter system set fal_server='orcldg';

alter system set fal_server='orclst';

alter system set local_listener=' (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))';

alter system set log_archive_config='DG_CONFIG=(orcl,orcldg,orclst)';

alter system set log_archive_dest_1='LOCATION=/u01/app/orcl/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 log_archive_dest_3='SERVICE=orclst LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclst';

alter system set log_archive_dest_state_1='ENABLE';

alter system set log_archive_dest_state_2='ENABLE';

alter system set log_archive_dest_state_3='ENABLE';

alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;

alter system set db_unique_name='orcl' scope=spfile;

alter system set db_file_name_convert='/u01/app/orcl/oradata/orcl','/u01/app/orcl/oradata/orcl' scope=spfile;

alter system set log_file_name_convert='/u01/app/orcl/oradata/orcl','/u01/app/orcl/oradata/orcl' scope=spfile;

alter system set STANDBY_FILE_MANAGEMENT=AUTO;

 

2) 使用修改pfile的方式。

 

create pfile='/home/oracle/initorcl.ora' from spfile;

 

注意:

 db_file_name_convert,和log_file_name_convert 最后路径不要添加'/',要么全部加上/,要么全都不加

 

主库参数展示:

orcl.__java_pool_size=4194304

orcl.__large_pool_size=8388608

orcl.__oracle_base='/u01/app/orcl'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=155189248

orcl.__sga_target=197132288

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=83886080

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/orcl/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/orcl/fast_recovery_area/orcl/control02.ctl','/u01/app/orcl/oradata/orcl/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/orcl/fast_recovery_area'

*.db_recovery_file_dest_size=4353687552

*.diagnostic_dest='/u01/app/orcl'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

#add

*.fal_client='orcl'

*.fal_server='orcldg'

*.fal_server='orclst'

*.local_listener=' (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))'

*.log_archive_config='DG_CONFIG=(orcl,orcldg,orclst)'

*.log_archive_dest_1='LOCATION=/u01/app/orcl/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=orcldg'

*.log_archive_dest_3='SERVICE=orclst LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclst'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_dest_state_3='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'

*.db_unique_name='orcl'

*.db_file_name_convert='/u01/app/orcl/oradata/orcl/','/u01/app/orcl/oradata/orcl/'

*.log_file_name_convert='/u01/app/orcl/oradata/orcl/','/u01/app/orcl/oradata/orcl/'

*.STANDBY_FILE_MANAGEMENT=AUTO

#add

*.memory_target=349175808

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

 

 

备库orcldg:

orcl.__java_pool_size=4194304

orcl.__large_pool_size=8388608

orcl.__oracle_base='/u01/app/orcl'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=155189248

orcl.__sga_target=197132288

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=83886080

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/orcl/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/orcl/oradata/orcl/standby.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/orcl/fast_recovery_area'

*.db_recovery_file_dest_size=4353687552

*.diagnostic_dest='/u01/app/orcl'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

#add

*.fal_client='orcldg'

*.fal_server='orcl'

*.local_listener=' (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))'

*.log_archive_config='DG_CONFIG=(orcldg,orcl)'

*.log_archive_dest_1='LOCATION=/u01/app/orcl/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg'

*.log_archive_dest_2='SERVICE=orcl LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'

*.db_unique_name='orcldg'

*.db_file_name_convert='/u01/app/orcl/oradata/orcl/','/u01/app/orcl/oradata/orcl/'

*.log_file_name_convert='/u01/app/orcl/oradata/orcl/','/u01/app/orcl/oradata/orcl/'

*.STANDBY_FILE_MANAGEMENT=AUTO

#add

*.memory_target=349175808

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

 

备库orclst:

orcl.__java_pool_size=4194304

orcl.__large_pool_size=8388608

orcl.__oracle_base='/u01/app/orcl'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=155189248

orcl.__sga_target=197132288

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=83886080

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/orcl/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/orcl/oradata/orcl/standby.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/orcl/fast_recovery_area'

*.db_recovery_file_dest_size=4353687552

*.diagnostic_dest='/u01/app/orcl'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

#add

*.fal_client='orclst'

*.fal_server='orcl'

*.local_listener=' (ADDRESS = (PROTOCOL = TCP)(HOST = dg3)(PORT = 1521))'

*.log_archive_config='DG_CONFIG=(orclst,orcl)'

*.log_archive_dest_1='LOCATION=/u01/app/orcl/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclst'

*.log_archive_dest_2='SERVICE=orcl LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'

*.db_unique_name='orclst'

*.db_file_name_convert='/u01/app/orcl/oradata/orcl/','/u01/app/orcl/oradata/orcl/'

*.log_file_name_convert='/u01/app/orcl/oradata/orcl/','/u01/app/orcl/oradata/orcl/'

*.STANDBY_FILE_MANAGEMENT=AUTO

#add

*.memory_target=349175808

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

 

3)备库的参数文件修改后。传输到目标机,主库的参数文件修改后能否启动。

4)检查参数:

Show parameter log;

Show parameter control;

Show parameter

 

第六步:使用备份做DG

1)备份主库:

2)关闭数据库:

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) 出现的错误:

加上plus archivelog 备份归档日志 报这个错误

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 * oracledg:/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在线方式创建备库

8.1 连接主库和备库:

rman target sys/123@oracle auxiliary sys/123@orcldg

8.2 运行复制数据库命令

run {

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby dorecover nofilenamecheck

from active database;

release channel prmy4 ;

release channel stby;

}

8.3 这步出的错误:

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

 

 

8.4控制文件指定地方要正确,要提前建好目录.

mkdir –p /u01/app/orcl/oradata/orcl/

 

8.5日志转换目录要正确。

+DATA/oracle/onlinelog, /u01/app/orcl/oradata/orcl, +DATA2/oracle/onlinelog, /u01/app/orcl/oradata/orcl

8.6如果备份不在原来的位置,则需要注册备份:

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日志

1)主库的standby日志:

alter database add standby logfile group 4('/u01/app/orcl/oradata/orcl/stdbyredo4.log') SIZE 50m;

alter database add standby logfile group 5('/u01/app/orcl/oradata/orcl/stdbyredo5.log') SIZE 50m;

alter database add standby logfile group 6('/u01/app/orcl/oradata/orcl/stdbyredo6.log') SIZE 50m;

alter database add standby logfile group 7('/u01/app/orcl/oradata/orcl/stdbyredo7.log') SIZE 50m;

2)备库的standby日志:

alter database add standby logfile group 4('/u01/app/orcl/oradata/orcl/stdbyredo4.log') SIZE 50m;

alter database add standby logfile group 5('/u01/app/orcl/oradata/orcl/stdbyredo5.log') SIZE 50m;

alter database add standby logfile group 6('/u01/app/orcl/oradata/orcl/stdbyredo6.log') SIZE 50m;

alter database add standby logfile group 7('/u01/app/orcl/oradata/orcl/stdbyredo7.log') SIZE 50m;

 

第十步:主库备库切换

Data Guard的主备库切换分为2种,switchover和failover.遇到突发事件,基本都是采用failover切换. 在主备库切换完成后应该修改数据库服务器的IP地址,即备库的地址.这样才能保证系统的正常运作.

 

switchover

一般SWITCHOVER切换都是计划中的切换,特点是在切换后,不会丢失任何的数据,而且这个过程是可逆的,整个DATA GUARD环境不会被破坏,原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。

主库上的操作

10.1查看switchover状态

select switchover_status from v$database;

10.2切换成从库

alter database commit to switchover to physical standby with session shutdown;

10.3确保实例在mount状态下

select status from v$instance;

shutdown immediate

startup mount

10.4切换成从库

alter database commit to switchover to physical standby;

10.5应用日志

alter database recover managed standby database disconnect from session;

备库上的操作:

10.6查看switchover状态

select switchover_status from v$database;

10.7关闭所有的客户端连接后切换为主库

alter database commit to switchover to primary;

 

10.8打开数据库

alter database open;

Failovers:

FAILOVER切换一般是PRIMARY数据库发生故障后的切换,这种情况是STANDBY数据库发挥其作用的情况。这种切换发生后,可能会造成数据的丢失。而且这个过程不是可逆的,DATA GUARD环境会被破坏。由于PRIMARY数据库已经无法启动,所以FAILOVER切换所需的条件并不多,只要检查STANDBY是否运行在最大保护模式下,如果是的话,需要将其置为最大性能模式,否则切换到PRIMARY角色也无法启动。

FailOver后,主库会做一次resetlogs,整个DG环境也就破坏了.

 

10.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 '路径';

 

 

重复查看直到没有应用的日志。

 

10.10然后停止应用归档:

alter database recover managed standby database cancel;

10.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;

10.12检查数据库是否已经切换成功:

select database_role from v$database;

附: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 ('d:\oradata/wsjcyl/redo03.log') size 500m reuse;

alter database add logfile group 2 ('d:\oradata/wsjcyl/redo02.log') size 500m reuse;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16615244/viewspace-1870406/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-03-01

  • 博文量
    7
  • 访问量
    13482