30分钟创建一个Oracle11g DataGuard物理备库的步骤

Stella981
• 阅读 646

30分钟创建一个Oracle11g DataGuard物理备库的步骤:

主库主机名:prmy
备库主机名:sby1
主库ip:192.168.1.1
备库ip:192.168.1.2 
主库dbname:prmy
备库dbname:sby1
主库db_unique_name=prmy
备库db_unique_name=sby1
主库listener.ora:prmy
备库listener.ora:sby1
主库备库tnsnames.ora:prmy,sby1

概述:

1.准备主库

2.设置物理备库上的参数

3.配置oracle 网络服务

4.启动备份实例

5.执行RMAN 命令:DUPLICATE TARGET DATABASE FOR

STANDBY FROM ACTIVE DATABASE

6.开启redo的传输和应用

准备主库:

    在数据库层面启用FORCE LOGGING
    如果需要的话创建一个密码文件
    创建备份日志组(standby redo log)
    设置初始化参数

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
alter database force logging;
select log_mode,force_logging from v$database;

scp orapwprmy oracle@192.168.76.134:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwsby1

ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/prmy/srl01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/prmy/srl02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/prmy/srl03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/prmy/srl04.log') SIZE 50M;

select group#,type,member from v$logfile where type='STANDBY';
select group#,dbid,thread#,sequence#,status from v$standby_log;

(1)alter system set log_archive_config='dg_config=(prmy,sby1)'; 

show parameter log_ARCHIVE_CONFIG;

select * from v$dataguard_config;

(2)ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prmy';
show parameter log_archive_dest_1;

(3)ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=sby1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sby1';

show parameter LOG_ARCHIVE_DEST_2;

show parameter log_archive_dest_state_2

(4)alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

show parameter log_archive_format;

(5)alter system set log_archive_max_processes=10;

show parameter log_archive_max_processes;

show parameter remote_login_passwordfile;

(6)alter system set fal_server=sby1;

show parameter fal_server

(7)alter system set standby_file_management=auto;

show parameter standby_file_management

(8)alter system set db_file_name_convert='/u01/app/oracle/oradata/sby1/','/u01/app/oracle/oradata/prmy/' scope=spfile;

(9)alter system set log_file_name_convert='/u01/app/oracle/oradata/sby1/','/u01/app/oracle/oradata/prmy/'scope=spfile;

为物理备库创建一个参数文件

cat dg.ora
DB_NAME=prmy

DB_UNIQUE_NAME=sby1

DB_BLOCK_SIZE=8192

为物理备库创建目录

mkdir -p /u01/app/oracle/oradata/sby1

mkdir -p /u01/app/oracle/fast_recovery_area/sby1

mkdir -p /u01/app/oracle/fast_recovery_area/SBY1

mkdir -p /u01/app/oracle/admin/sby1/adump

启动物理备库

export ORACLE_SID=sby1

sqlplus /nolog
connect /as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/dg.ora';  

select instance_name,host_name from v$instance;
select name from v$database;

为主库及物理备库创建网络服务名
prmy:
cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = prmy)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = prmy)
    )
  )

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

ADR_BASE_LISTENER = /u01/app/oracle

 cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRMY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = prmy)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prmy)
    )
  )

SBY1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sby1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sby1)
    )
  )

sby1:

 cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sby1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = sby1)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = sby1)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

 cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRMY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = prmy)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prmy)
    )
  )

SBY1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sby1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sby1)
    )
  )

tnsping prmy
tnsping sby1

使用RMAN脚本来创建物理备库

 cat du.rmn 
run{
allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile

 parameter_value_convert 'prmy','sby1'

 set db_unique_name='sby1'

 set db_file_name_convert='/u01/app/oracle/oradata/prmy/','/u01/app/oracle/oradata/sby1/'

 set log_file_name_convert='/u01/app/oracle/oradata/prmy/','/u01/app/oracle/oradata/sby1/'

 set control_files='/u01/app/oracle/oradata/sby1/control01.ctl', '/u01/app/oracle/fast_recovery_area/sby1/control02.ctl'

 set log_archive_max_processes='10'

 set fal_server='prmy'

 set standby_file_management='AUTO'

 set log_archive_config='dg_config=(prmy,sby1)'

 set log_archive_dest_2='service=prmy ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=prmy';

}

rman target sys/sys@prmy auxiliary sys/sys@sby1

@du.rmn

开启实时应用
alter database open;

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

archvie log list;

alter system switch logfile;

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Wesley13 Wesley13
3年前
oracle 搭建dg步骤
   如果搭建dg先安装主库database并建库,同时建备库database软件,不需要建库。1   主库打开归档功能       启动到mount状态下,打开归档功能       alterdatabasearchivelog;SQLarchiveloglist;Databaselogmod
Wesley13 Wesley13
3年前
Oracle 12c DG备库Alert报错ORA
环境是12.2.0.1version,OracleDataGuard备库近段时间一直报错,但是备库主库同步一致,数据一致。20190306T23:42:22.18404808:00Errorsinfile/u01/app/oracle/diag/rdbms/ccdb/ccdb/trace/ccdb_m000_12
Wesley13 Wesley13
3年前
Oracle 11g DataGuard搭建(一)
(一)DataGuard概要DataGuard中文称为”数据卫士“,提供了数据库高可用性、数据保护和灾难恢复的功能。DataGuard通过建立primary数据库和standby数据库来确立参照关系,DataGuard将主库(primary)的redo日志传递给备库(standby),然后在备库中应用redo进行同步。备库又分为2种类型:物理
Wesley13 Wesley13
3年前
mysql主从环境,切换IP操作
环境:redhat7.5 mysql5.7老IP:192.168.1.1,192.168.1.2新IP:192.168.3.3,192.168.3.4\备库停机mysqlstopslave;systemctlstopmysqld\主库停机systemctlstopmysqld\修改主库
Wesley13 Wesley13
3年前
ORACLE DATAGUARD 日志传输状态监控
ORACLEDATAGUARD的主备库同步,主要是依靠日志传输到备库,备库应用日志或归档来实现。当主、备库间日志传输出现GAP,备库将不再与主库同步。因此需对日志传输状态进行监控,确保主、备库间日志没有GAP,或发现GAP后及时处理。除了在告警日志中查看日志同步情况外,还可以通过查看相关视图来对日志传输状态进行监控。1、主、备库查看当前日志状况
Wesley13 Wesley13
3年前
Oracle physical dataguard with broker部署
一、环境说明主库:10.110.96.88备库:10.110.96.87数据库实例:gisc二、主库操作1.开启forceloggingALTERDATABASEFORCELOGGING;2.开启日志归档a.查看日志归档是否开启ARCHIVELOGLI
Wesley13 Wesley13
3年前
5、pgpool
官网示例一、实现原理使用pgpoolII软件;我们常用来实现流复制的高可用性;备库只读的,不可写;就是当主库出现问题时;需要把备库自动激活为主库;来接管服务。这在其他高可用软件也有这功能,而pgpoolII在配置文件pgpool.conf中提供配置项failover\_command。让用户配置一个脚本,当发生故
Wesley13 Wesley13
3年前
MySQL学习(三)主备分库分表和恢复数据
1、MySQL主备切换readonly设置对超级(super)权限是无效的,而用于同步更新的线程,就拥有超级权限。建议在做主备数据库的时候,将备用数据库设置为只读。(反向用readonly来判断节点的角色)主备的同步是通过binlog日志同步,流程:1)、备库上通过changemaster命令,设置主库的