频道栏目
首页 > 网络 > 其他 > 正文

Oracle Active DataGuard Oracle to Oracle配置教程

2019-02-11 11:42:19           
收藏   我要投稿
1.数据库环境介绍

1)主库(源数据库):Oracle 11G R2IP:192.168.4.105HOSTNAME:oadbSID:oaORACLE_BASE:/u01/app/oracleORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1

2)备库(目标数据库):Oracle 11g R2IP:192.168.4.106HOSTNAME:oadbstandbySID:oaORACLE_BASE:/u01/app/oracleORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1

2.配置db_unique_name

1)配置主库db_unique_nameSQL> show parameter db_unique_name;NAME TYPE VALUE


db_unique_name string oa注:主库db_unique_name这里不作修改,保持生产环境不受影响2)配置备库db_unique_nameSQL> show parameter db_unique_name;NAME TYPE VALUE


db_unique_name string oa

SQL> alter system set db_unique_name=standbydb scope=spfile;System altered注:配置db_unique_name,需要重启数据库才能生效

3.主库配置1)配置网络服务名[oracle@oadb ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin[oracle@oadb admin]$ vi tnsnames.oraOA =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.105)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = oa)))

STANDBYDB =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.106)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = standbydb)))2)配置监听,添加静态注册[root@oadb admin]# pwd/u01/app/oracle/product/11.2.0/dbhome_1/network/admin[root@oadb admin]# vi listener.oralistener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraGenerated by Oracle configuration tools.SID_LIST_LISTENER =(SID_DESC =(GLOBAL_DBNAME = oa)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)(SID_NAME = oa))

LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))(ADDRESS = (PROTOCOL = TCP)(HOST = oadb)(PORT = 1521))))

ADR_BASE_LISTENER = /u01/app/oracle3)将主库修改为forcelogging模式SQL> select name,open_mode,log_mode,force_logging from gv$database;

NAME OPEN_MODE LOG_MODE FOR


OA READ WRITE ARCHIVELOG NO

SQL> alter database force logging;

Database altered.

SQL> select name,open_mode,log_mode,force_logging from gv$database;

NAME OPEN_MODE LOG_MODE FOR


OA READ WRITE ARCHIVELOG YES4)设置参数a)设置所有启用的数据库名SQL> show parameter log_archive_config;

NAME TYPE VALUE


log_archive_config stringSQL> alter system set log_archive_config='dg_config=(oa,standbydb)';

System altered.

SQL> show parameter log_archive_config;

NAME TYPE VALUE


log_archive_config string dg_config=(oa,standbydb)注:dg_config为主备库的db_unique_nameb)开启归档模式SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.

Total System Global Area 409194496 bytesFixed Size 2253744 bytesVariable Size 310381648 bytesDatabase Buffers 92274688 bytesRedo Buffers 4284416 bytesDatabase mounted.SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.c)设置归档日志的路径(本地和网络)SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE


log_archive_dest_1 stringlog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 stringlog_archive_dest_17 stringlog_archive_dest_18 stringlog_archive_dest_19 stringSQL> show parameter log_archive_dest_2;

NAME TYPE VALUE


log_archive_dest_2 stringlog_archive_dest_20 stringlog_archive_dest_21 stringlog_archive_dest_22 stringlog_archive_dest_23 stringlog_archive_dest_24 stringlog_archive_dest_25 stringlog_archive_dest_26 stringlog_archive_dest_27 stringlog_archive_dest_28 stringlog_archive_dest_29 stringSQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/OA/archivelog/ optional';

System altered.

SQL> alter system set log_archive_dest_2='service=standbydb lgwr sync affirm valid_for=(online_logfile,primary_role) db_unique_name=standbydb';

System altered.

SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE


log_archive_dest1 string LOCATION=/u01/app/oracle/fastrecovery_area/OA/archivelog/ optionallog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 stringlog_archive_dest_17 string

NAME TYPE VALUE


log_archive_dest_18 stringlog_archive_dest_19 stringSQL> show parameter log_archive_dest_2;

NAME TYPE VALUE


log_archive_dest_2 string service=standbydb lgwr sync affirm valid_for=(online_logfile,primary_role) db_unique_name=standbydblog_archive_dest_20 stringlog_archive_dest_21 stringlog_archive_dest_22 stringlog_archive_dest_23 stringlog_archive_dest_24 stringlog_archive_dest_25 stringlog_archive_dest_26 string

NAME TYPE VALUE


log_archive_dest_27 stringlog_archive_dest_28 stringlog_archive_dest_29 string注:service值为备用数据库的TNSNAMES描述符d)设置备库文件自动管理,可以在主库新建数据文件的时候自动新建备库SQL> show parameter standby_file_management;

NAME TYPE VALUE


standby_file_management string MANUALSQL> alter system set standby_file_management='AUTO';

System altered.

SQL> show parameter standby_file_management;

NAME TYPE VALUE


standby_file_management string AUTOe)设置FAL_SERVER为备库Oracle Net service name,SQL> show parameter fal_server;

NAME TYPE VALUE


fal_server stringSQL> alter system set fal_server='standbydb';

System altered.

SQL> show parameter fal_server;

NAME TYPE VALUE


fal_server string standbydbf)创建standby redologSQL> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/oa/standby11.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 12 ('/u01/app/oracle/oradata/oa/standby12.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 13 ('/u01/app/oracle/oradata/oa/standby13.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 14 ('/u01/app/oracle/oradata/oa/standby14.log') size 50m;

Database altered.

注1:由于备库后续会使用主库RMAN还原,这里无需在备库存创建,等备库使用RMAN还原后,再创建standby redolog注2:建立standby如要注意以下几点:a)standby log files的大小和redo log files一样。 查询redo log files文件大小(默认50M,3个):select group#,bytes/1024/1024 as M from v$log;b)一般而言, standbyredo 日志文件组数要比 primary 数据库的 online redo 日志文件组数至少多一个。 有一个推荐的公式可以做参考:(每线程的日志组数+1)最大线程数 假设现在节点是1个,则=(3+1)1=4如果是双节点 则=(3+1)*2=8c)不建议组号group#紧挨着redo,因为后续redo有可能调整

c)不建议组号group#紧挨着redo,因为后续redo有可能调整4.备库配置1)配置网络服务名[oracle@oadbstandby ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin[oracle@oadbstandby admin]$ vi tnsnames.oraOA =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.105)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = oa)))

STANDBYDB =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.106)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = standbydb)))2)配置监听,添加静态注册[root@oadbstandby admin]# pwd/u01/app/oracle/product/11.2.0/dbhome_1/network/admin[root@oadbstandby admin]# vi listener.oralistener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraGenerated by Oracle configuration tools.SID_LIST_LISTENER =(SID_DESC =(GLOBAL_DBNAME = oa)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)(SID_NAME = oa))

LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))(ADDRESS = (PROTOCOL = TCP)(HOST = oadbstandby)(PORT = 1521))))

ADR_BASE_LISTENER = /u01/app/oracle3)将备库修改为forcelogging模式SQL> select name,open_mode,log_mode,force_logging from gv$database;

NAME OPEN_MODE LOG_MODE FOR


OA READ WRITE ARCHIVELOG NO

SQL> alter database force logging;

Database altered.

SQL> select name,open_mode,log_mode,force_logging from gv$database;

NAME OPEN_MODE LOG_MODE FOR


OA READ WRITE ARCHIVELOG YES4)设置参数a)开启归档模式SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.

Total System Global Area 409194496 bytesFixed Size 2253744 bytesVariable Size 310381648 bytesDatabase Buffers 92274688 bytesRedo Buffers 4284416 bytesDatabase mounted.SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.b)设置所有的启用的数据库名SQL> show parameter log_archive_config;

NAME TYPE VALUE


log_archive_config stringSQL> alter system set log_archive_config='dg_config=(oa,standbydb)';

System altered.

SQL> show parameter log_archive_config;

NAME TYPE VALUE


log_archive_config string dg_config=(oa,standbydb)注:dg_config为主备库的db_unique_namec)设置归档日志的路径(本地和网络)SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE


log_archive_dest_1 stringlog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 stringlog_archive_dest_17 stringlog_archive_dest_18 stringlog_archive_dest_19 stringSQL> show parameter log_archive_dest_2;

NAME TYPE VALUE


log_archive_dest_2 stringlog_archive_dest_20 stringlog_archive_dest_21 stringlog_archive_dest_22 stringlog_archive_dest_23 stringlog_archive_dest_24 stringlog_archive_dest_25 stringlog_archive_dest_26 stringlog_archive_dest_27 stringlog_archive_dest_28 stringlog_archive_dest_29 stringSQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/OA/archivelog/ optional';

System altered.

SQL> alter system set log_archive_dest_2='service=oa lgwr sync affirm valid_for=(online_logfile,primary_role) db_unique_name=oa';

System altered.

SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE


log_archive_dest1 string LOCATION=/u01/app/oracle/fastrecovery_area/OA/archivelog/ optionallog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 stringlog_archive_dest_17 string

NAME TYPE VALUE


log_archive_dest_18 stringlog_archive_dest_19 stringSQL> show parameter log_archive_dest_2;

NAME TYPE VALUE


log_archive_dest_2 string service=oa lgwr sync affirm valid_for=(online_logfile,primary_role) db_unique_name=oalog_archive_dest_20 stringlog_archive_dest_21 stringlog_archive_dest_22 stringlog_archive_dest_23 stringlog_archive_dest_24 stringlog_archive_dest_25 stringlog_archive_dest_26 stringlog_archive_dest_27 string

NAME TYPE VALUE


log_archive_dest_28 stringlog_archive_dest_29 string 注: 目的,当备库转换为主库的时候把重做日志写到新备库service值为备用数据库的TNSNAMES描述符d)设置备库文件自动管理,可以在主库新建数据文件的时候自动新建备库SQL> show parameter standby_file_management;

NAME TYPE VALUE


standby_file_management string MANUALSQL> alter system set standby_file_management='AUTO';

System altered.

SQL> show parameter standby_file_management;

NAME TYPE VALUE


standby_file_management string AUTOe)设置FAL_SERVER为备库Oracle Net service name,SQL> show parameter fal_server;

NAME TYPE VALUE


fal_server stringSQL> alter system set fal_server='oa';

System altered.

SQL> show parameter fal_server;

NAME TYPE VALUE


fal_server string oa

5.拷贝主库密码文件到备库注:密码文件一般都在$ORACLE_HOME/dbs/下,名称为orapw实例名;注意每个实例的实例名不一样,所以复制到对应的实例下时一定需要注意命名问题

6.使用RMAN备份主库备份脚本:rman target / log=/backup/rmanbackup/2018-05-11/rman_oa2018-05-11.log <

7.备库还原(使用主库RMAN备份文件还原)1)授权从主库拷贝到备库的RMAN备份文件[root@oadbstandby rmanbackup]# chown -R oracle:oinstall 2018-05-112)登录到备库,启动备库到nomount状态[root@oadbstandby /]# su - oracle[oracle@oadbstandby ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 11 10:56:14 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: OA (DBID=3393907898)

RMAN> startup nomount force;

Oracle instance started

Total System Global Area 409194496 bytes

Fixed Size 2253744 bytesVariable Size 314575952 bytesDatabase Buffers 88080384 bytesRedo Buffers 4284416 bytes

RMAN>3)将备库dbid设置和主库dbid相同RMAN> set dbid 3393850737;

executing command: SET DBID4)还原控制文件RMAN> restore standby controlfile from '/backup/rmanbackup/2018-05-11/ctl_0at2k9ev_1_1_3393850737_20180511';

Starting restore at 11-MAY-18using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:03output file name=/u01/app/oracle/oradata/oa/control01.ctloutput file name=/u01/app/oracle/fast_recovery_area/oa/control02.ctlFinished restore at 11-MAY-185)启动数据库到mount状态RMAN> alter database mount;

database mountedreleased channel: ORA_DISK_16)注册从源数据库拷贝过来的备份集到rman中RMAN> catalog start with '/backup/rmanbackup/2018-05-11/';

Starting implicit crosscheck backup at 11-MAY-18allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=27 device type=DISKCrosschecked 7 objectsFinished implicit crosscheck backup at 11-MAY-18

Starting implicit crosscheck copy at 11-MAY-18using channel ORA_DISK_1Finished implicit crosscheck copy at 11-MAY-18

searching for all files in the recovery areacataloging files...cataloging done

=======================List of Cataloged FilesFile Name: /u01/app/oracle/fast_recovery_area/STANDBYDB/archivelog/2018_05_11/o1_mf_1_8fh9zs7mh.arcFile Name: /u01/app/oracle/fast_recovery_area/STANDBYDB/archivelog/2018_05_11/o1_mf_1_6fh9znlgg.arcFile Name: /u01/app/oracle/fast_recovery_area/STANDBYDB/archivelog/2018_05_11/o1_mf_1_7fh9zpxw5.arc

searching for all files that match the pattern /backup/rmanbackup/2018-05-11/

List of Files Unknown to the Database

File Name: /backup/rmanbackup/2018-05-11/spfile_0bt2k9f1_1_1_3393850737_20180511File Name: /backup/rmanbackup/2018-05-11/rman_oa_2018-05-11.logFile Name: /backup/rmanbackup/2018-05-11/ctl_0at2k9ev_1_1_3393850737_20180511

Do you really want to catalog the above files (enter YES or NO)? yescataloging files...cataloging done

=======================List of Cataloged FilesFile Name: /backup/rmanbackup/2018-05-11/spfile_0bt2k9f1_1_1_3393850737_20180511File Name: /backup/rmanbackup/2018-05-11/ctl_0at2k9ev_1_1_3393850737_20180511

=======================================List of Files Which Where Not Cataloged

File Name: /backup/rmanbackup/2018-05-11/rman_oa_2018-05-11.logRMAN-07517: Reason: The file header is corrupted7)还原数据文件RMAN> run { set newname for datafile 1 to '/u01/app/oracle/oradata/oa/system01.dbf';set newname for datafile 2 to '/u01/app/oracle/oradata/oa/sysaux01.dbf';set newname for datafile 3 to '/u01/app/oracle/oradata/oa/undotbs01.dbf';set newname for datafile 4 to '/u01/app/oracle/oradata/oa/users01.dbf';restore database;switch datafile all;}6> 7> 8>

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 11-MAY-18using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/oa/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oa/undotbs01.dbfchannel ORA_DISK_1: reading from backup piece /backup/rmanbackup/2018-05-11/db_04t2k9d9_1_1_3393850737_20180511channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/db_04t2k9d9_1_1_3393850737_20180511 tag=STANDBY_DBchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/oa/system01.dbfchannel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oa/users01.dbfchannel ORA_DISK_1: reading from backup piece /backup/rmanbackup/2018-05-11/db_03t2k9d9_1_1_3393850737_20180511channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/db_03t2k9d9_1_1_3393850737_20180511 tag=STANDBY_DBchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:16Finished restore at 11-MAY-18

datafile 1 switched to datafile copyinput datafile copy RECID=1 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/system01.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=2 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=3 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=4 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/users01.dbf8)恢复数据库RMAN> recover database;

Starting recover at 11-MAY-18using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=8channel ORA_DISK_1: reading from backup piece /backup/rmanbackup/2018-05-11/arch_08t2k9es_1_1_3393850737_20180511channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/arch_08t2k9es_1_1_3393850737_20180511 tag=STANDBY_ARCHchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/u01/app/oracle/fast_recovery_area/OA/archivelog/1_8_975777331.dbf thread=1 sequence=8channel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=9channel ORA_DISK_1: reading from backup piece /backup/rmanbackup/2018-05-11/arch_09t2k9eu_1_1_3393850737_20180511channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/arch_09t2k9eu_1_1_3393850737_20180511 tag=STANDBY_ARCHchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/u01/app/oracle/fast_recovery_area/OA/archivelog/1_9_975777331.dbf thread=1 sequence=9unable to find archived logarchived log thread=1 sequence=10RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/11/2018 11:03:46RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1001269

8.创建standby redologSQL> alter database drop standby logfile group 11;

Database altered.

SQL> alter database drop standby logfile group 12;

Database altered.

SQL> alter database drop standby logfile group 13;

Database altered.

SQL> alter database drop standby logfile group 14;

Database altered.

SQL> alter database add standby logfile group 11 ('/data/oradata/standby11.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 12 ('/data/oradata/standby12.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 13 ('/data/oradata/standby13.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 14 ('/data/oradata/standby14.log') size 50m;

9.以只读方式打开standbydbSQL> alter database open read only;

Database altered.

10.standbydb启动redo applySQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

上一篇:FTP协议的命令command与返回码resultcode详解
下一篇:CentOS7 管理用户账号教程
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站