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

最新推荐文章于 2021-09-29 05:44:05 发布

conghuozhu3793

最新推荐文章于 2021-09-29 05:44:05 发布

阅读量175

点赞数

文章标签：数据库网络操作系统

第一步：开启归档（主库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环境也就破坏了.