[oracle]Oracle 11g DG搭建(备库使用ASM)

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了[oracle]Oracle 11g DG搭建(备库使用ASM)，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。


配置信息:

主库

主机名: r5

ip: 192.168.0.5

ORACLE_SID: r5


备库 (注: 备库使用了ASM)

主机名: r5standby

ip: 192.168.0.9

ORACLE_SID:r5standby


1. 确保两台机器ping主机名能互通(即配置好网络和/etc/hosts解析)

主库/etc/hosts (备库应保持一致)

127.0.0.1               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
192.168.0.5     R5.localdomain R5
192.168.0.9     r5standby.localdomain r5standby


2. 创建好主库、备库数据库,配置好监听,确保两台数据库之间可通过tnsname互相连接

主库tnsname.ora(备库应保持一致)

r5 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = R5.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = r5)
    )
  )
r5standby=
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = r5standby.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = r5standby)
      (UR=A)
    )
  )


3. 主备库开启强制日志模式和归档

两库开启强制日志:

alter database force logging;

主库开启归档:

shutdown immediate;
alter database mount;
alter system set log_archive_dest_1='LOCATION=/bak2/archivelog';
alter database archive log;
alter database open;

备库开启归档: 
shutdown immediate;
alter database mount;
alter diskgroup DATA add directory '+DATA/R5STANDBY/ARC1';
alter system set log_archive_dest_1='LOCATION=+DATA/R5STANDBY/ARC1';
alter database archive log;
alter database open;


4. 设置主库参数

alter system set log_archive_config='dg_config=(r5,r5standby)';

alter system set log_archive_dest_1='LOCATION=/bak2/archivelog VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=r5';
alter system set log_archive_dest_2='SERVICE=r5standby LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=r5standby';
alter system set log_archive_dest_state_1='ENABLE';
alter system set log_archive_dest_state_2='DEFER';
alter system set fal_client='R5';
alter system set fal_server='r5standby';
alter system set db_file_name_convert='/u01/app/oracle/oradata/r5/','+data/r5standby/datafile/','/data2/r5/','/data/r5/','+data/r5standby/datafile/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/r5/','+data/r5standby/onlinelog/' scope=spfile;
alter system set standby_file_management='AUTO';

主库根据spfile创建pfile

create pfile='/u01/r5_0107.ora' from spfile;

将pfile和密码文件scp到备库:

scp $ORACLE_HOME/dbs/orapwr5 192.168.0.9:~
scp /u01/r5_0107.ora 192.168.0.9:/u01/app/oracle/product/11g/dbs/


5. 关闭备库后,同时备份主库

主库:

rman target /
run
{
 allocate channel c0 device type disk;
 allocate channel c1 device type disk;
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/bak2/backupset/%F';
 backup database format '/bak2/backupset/ora11g_full_db_%d_%T_%u.bak';
 BACKUP ARCHIVELOG ALL FORMAT '/bak2/backupset/ora11g_arc_%s_%p_%t.bak';
}

这里一般需要较长时间

完成后,scp到备库对应目录(注意目录要一致):

scp -r /bak2/backupset/ 192.168.0.9:/bak2


备库:

备份原密码文件

cd $ORACLE_HOME/dbs

mvorapwr5standbyorapwr5standby.bk

mvorapwr5orapwr5standby


修改传送过来的参数文件r5_0107.ora

r5standby.__db_cache_size=12952010752

r5standby.__java_pool_size=67108864

r5standby.__large_pool_size=67108864

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

r5standby.__pga_aggregate_target=10200547328

r5standby.__sga_target=15166603264

r5standby.__shared_io_pool_size=0

r5standby.__shared_pool_size=1946157056

r5standby.__streams_pool_size=0


*._system_trig_enabled=FALSE

*.audit_file_dest='/u01/app/oracle/admin/r5standby/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA/r5standby/controlfile/current.260.932432629'#Restore Controlfile

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_file_name_convert='/u01/app/oracle/oradata/r5/','+data/r5standby/datafile/'

*.db_name='r5'

*.db_recovery_file_dest_size=10737418240

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

*.db_unique_name='r5standby'

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

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

*.fal_client='r5standby'

*.fal_server='r5'

*.log_archive_config='dg_config=(r5,r5standby)'

*.log_archive_dest_1='LOCATION=+DATA/R5STANDBY/ARC1 VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=r5standby'

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

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_file_name_convert='/u01/app/oracle/oradata/r5/','+data/r5standby/onlinelog/'

*.memory_target=25321013248

*.open_cursors=300*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_archive_dest='+DATA/R5STANDBY/ARC1'

*.standby_file_management='AUTO'


注意对比红色字的参数,

其中隐式参数_system_trig_enabled=FALSE之所以设为FALSE,是因为我这边主库存在SYS的登录触发器,备库需要禁用,否则打开备库时会报错:

ORA-00604: error occurred at recursive sql level 1


备库启动到nomount状态:

startup pfile=?/dbs/r5_0107.ora nomount;

然后

create spfile from pfile;

退出sqlplus,确保主库的所有备份文件已传输到备库对应目录


6. 进入主库,进行RMAN恢复

主库上:

rman target /
connect auxiliary sys/mellson@r5standby
duplicate target database for standby nofilenamecheck;

完成后在主库里设置下参数LOG_ARCHIVE_DEST_STATE_2:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE';


7. 添加备库日志

在主库上执行:

ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 '/u01/app/oracle/oradata/r5/sredo06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 '/u01/app/oracle/oradata/r5/sredo08.log' size 50M;

此处应注意日志大小应与主库其原有的日志大小保持一致,否则后面备库日志可能出现下面的信息:

RFS[3]: No standby redo logfiles available for thread 1
RFS[3]: Opened log for thread 1 sequence 1059 dbid -1950166189 branch 865010252

...


备库上增加同样多的日志:

alter database add STANDBY logfile group 9('+DATA/r5standby/onlinelog/sredo06.log') size 50M;
alter database add STANDBY logfile group 10 ('+DATA/r5standby/onlinelog/sredo08.log') size 50M;


8. 备库打开,并启用实时应用日志同步服务

alter database open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


最后检查:

主库:

sql> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL     OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE  PRIMARY          MAXIMUM PERFORMANCE  READ WRITE

备库: 
sql> select protection_mode,open_mode from v$database; 

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE  PHYSICAL STANDBY MAXIMUM PERFORMANCE  READ ONLY WITH APPLY
sql> select group#,thread#,sequence#,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# STATUS
---------- ---------- ---------- ----------
	 9	    1	    1062 ACTIVE
	10	    1	       0 UNASSIGNED



在主库的某表里增加条数据,备库里能查询到.

或者主库执行alter system switch logfile;后,观察主备库的日志,是否有异常信息


附我当前备库的日志信息:

Sat Jan 07 20:24:34 2017
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 8 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +DATA/r5standby/arc1/1_1059_865010252.dbf
Media Recovery Waiting for thread 1 sequence 1060 (in transit)
Recovery of Online Redo Log: Thread 1 Group 9 Seq 1060 Reading mem 0
Mem# 0: +DATA/r5standby/onlinelog/sredo09.log
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Sun Jan 08 06:23:43 2017
RFS[6]: Selected log 10 for thread 1 sequence 1061 dbid -1950166189 branch 865010252
Sun Jan 08 06:23:44 2017
Media Recovery Waiting for thread 1 sequence 1061 (in transit)
Recovery of Online Redo Log: Thread 1 Group 10 Seq 1061 Reading mem 0
Mem# 0: +DATA/r5standby/onlinelog/sredo10.log
Sun Jan 08 06:23:49 2017
Archived Log entry 9 added for thread 1 sequence 1060 ID 0x8d0424d8 dest 3:
Mon Jan 09 02:23:12 2017
RFS[6]: Selected log 9 for thread 1 sequence 1062 dbid -1950166189 branch 865010252
Mon Jan 09 02:23:14 2017
Media Recovery Waiting for thread 1 sequence 1062 (in transit)
Recovery of Online Redo Log: Thread 1 Group 9 Seq 1062 Reading mem 0
Mem# 0: +DATA/r5standby/onlinelog/sredo09.log
Mon Jan 09 02:23:18 2017
Archived Log entry 10 added for thread 1 sequence 1061 ID 0x8d0424d8 dest 3:

...

原文链接：https://www.f2er.com/oracle/210932.html

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问