使用rman duplicate创建物理active standby dataguard

2024-02-09 19:08

本文主要是介绍使用rman duplicate创建物理active standby dataguard,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

11g的rman提供duplicate可以快速创建物理dataguard。简单方便,dataguard要求主库和备库的db_name一致,db_unique_name不同,下面是具体步骤

基本环境如下:

主库:
hostname:11g1
ip:192.168.56.35
db_name:squan
sid=squan
db_unique_name=squan
备库:
hostname:11g2
ip:192.168.56.106
db_name:squan
sid=suq

db_unique_name=suq

==============================

一、主库的基本步骤:

1.修改数据库为force logging

2.创建密码文件

3.创建standby logfile,standby logfile的组数比联机日志多一组

4.修改tnsname.ora,添加一个到standby数据库的服务命名

5.修改standby的相关参数

具体操作如下:

1.修改数据库为force logging
SQL> alter database force logging;Database altered.2.创建密码文件
[oracle@11g1 dbs]$ orapwd file=orapwsquan password=manager entries=10SQL> select * from v$log;GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------1	    1	      47   52428800	   512		1 YES ACTIVE		     1170031 24-MAY-14	    1172488 24-MAY-142	    1	      48   52428800	   512		1 NO  CURRENT		     1172488 24-MAY-14	 2.8147E+143.创建standby logfile
SQL> alter database add standby logfile group 3 ('/opt/oracle/oradata/squan/stand_redo03.log') size 50m;Database altered.SQL> alter database add standby logfile group 4 ('/opt/oracle/oradata/squan/stand_redo04.log') size 50m;Database altered.SQL> alter database add standby logfile group 5 ('/opt/oracle/oradata/squan/stand_redo05.log') size 50m;Database altered.4.修改tnsname.ora
[oracle@11g1 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /opt/oracle/product/OraHome/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.SQUAN =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 11g1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = squan)))11g2=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 11g2)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = suq)))11g1=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 11g1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = squan)))5.修改standby的相关参数  
SQL> alter system set log_archive_config='dg_config=(squan,suq)' scope=both;  --罗列dg中的所有的db_unique_nameSystem altered.SQL> alter system set log_archive_dest_1='location=/opt/arch valid_for=(all_logfiles,all_roles) db_unique_name=squan' scope=both;System altered.SQL> alter system set log_archive_dest_2='service=11g2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=suq' scope=both;System altered.SQL>  alter system set fal_server=squan scope=both;System altered.SQL> alter system set fal_client=suq scope=both;System altered.SQL> show parameter db_name                   --如果db_name没有设置,那么需要设置,并且重启数据库生效NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_name 			     string	 squan

二、备库的基本步骤

1.配置静态监听

2.配置密码文件

3.修改参数文件并启动实例到nomount

具体步骤如下:


1.配置静态监听
[oracle@11g2 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /opt/oracle/product/OraHome/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = /opt/oracle/product/OraHome11204)(PROGRAM = extproc))(SID_DESC =(SID_NAME = suq)(ORACLE_HOME = /opt/oracle/product/OraHome11204)(GLOBAL_DBNAME=suq)))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 11g2)(PORT = 1521))))ADR_BASE_LISTENER = /opt/oracle[oracle@11g2 ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-5月 -2014 21:32:21Copyright (c) 1991, 2013, Oracle.  All rights reserved.正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11g2)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名                      LISTENER
版本                      TNSLSNR for Linux: Version 11.2.0.4.0 - Production
启动日期                  24-5月 -2014 18:58:46
正常运行时间              0 天 2 小时 33 分 35 秒
跟踪级别                  off
安全性                    ON: Local OS Authentication
SNMP                      OFF
监听程序参数文件          /opt/oracle/product/OraHome11204/network/admin/listener.ora
监听程序日志文件          /opt/oracle/diag/tnslsnr/11g2/listener/alert/log.xml
监听端点概要...(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=11g2)(PORT=1521)))
服务摘要..
服务 "PLSExtProc" 包含 1 个实例。实例 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "suq" 包含 2 个实例。实例 "suq", 状态 UNKNOWN, 包含此服务的 1 个处理程序...实例 "suq", 状态 BLOCKED, 包含此服务的 1 个处理程序...
命令执行成功
2.配置密码文件,直接从主库拷贝然后改名
[oracle@11g2 dbs]$ scp 11g1:`pwd`/orapwsquan .
The authenticity of host '11g1 (192.168.56.35)' can't be established.
RSA key fingerprint is 73:56:4c:3a:01:3f:50:c8:d8:3a:5d:d5:21:00:6a:fe.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '11g1' (RSA) to the list of known hosts.
oracle@11g1's password: 
orapwsquan                                                                                                     100% 2560     2.5KB/s   00:00    
[oracle@11g2 dbs]$ mv orapwsquan orapwsuq<pre name="code" class="sql">3.修改参数文件并启动实例到nomount
[oracle@11g2 dbs]$ cat initsuq.ora 
*.__db_cache_size=314572800
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__oracle_base='/opt/oracle' #ORACLE_BASE set from environment
*.__pga_aggregate_target=490733568
*.__sga_target=583008256
*.__shared_io_pool_size=0
*.__shared_pool_size=239075328
*.__streams_pool_size=8388608
*.audit_file_dest='/opt/oracle/admin/suq/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/oracle/oradata/suq/control01.ctl','/opt/oracle/flash_recovery_area/squan/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='squan'         #db_name需要和主库一致
*.db_unique_name='suq'    #db_unique_name必须不同
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.diagnostic_dest='/opt/oracle'
*.fal_client='SUQ'
*.fal_server='SQUAN'
*.log_archive_config='dg_config=(squan,suq)'
*.log_archive_dest_1='location=/opt/arch valid_for=(all_logfiles,all_roles) db_unique_name=suq'
*.log_archive_dest_2='service=11g1 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=squan'
*.memory_target=1073741824
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/opt/oracle/oradata/squan','/opt/oracle/oradata/suq'  #设置数据文件名字转换
*.log_file_name_convert='/opt/oracle/oradata/squan','/opt/oracle/oradata/suq' #设置日志文件名字转换
*.standby_file_management='AUTO'        #文件自动管理


 
[oracle@11g2 dbs]$ export ORACLE_SID=suq 
[oracle@11g2 dbs]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on 星期六 5月 24 21:30:45 2014Copyright (c) 1982, 2013, Oracle.  All rights reserved.已连接到空闲例程。SQL>  startup nomount pfile='initsuq.ora'
ORACLE 例程已经启动。Total System Global Area 1068937216 bytes
Fixed Size		    2260088 bytes
Variable Size		  746587016 bytes
Database Buffers	  314572800 bytes
Redo Buffers		    5517312 bytes
SQL> exit

三、使用duplicate创建standby database

在源端rman连接源库和备库,发起duplicate:

[oracle@11g1 admin]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 24 21:50:04 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: SQUAN (DBID=3808450041)RMAN> connect auxiliary sys/manager@11g2connected to auxiliary database: SQUAN (not mounted)RMAN> duplicate target database for standby from active database;Starting Duplicate Db at 24-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISKcontents of Memory Script:
{backup as copy reusetargetfile  '/opt/oracle/product/OraHome11204/dbs/orapwsquan' auxiliary format '/opt/oracle/product/OraHome11204/dbs/orapwsuq'   ;
}
executing Memory ScriptStarting backup at 24-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
Finished backup at 24-MAY-14contents of Memory Script:
{backup as copy current controlfile for standby auxiliary format  '/opt/oracle/oradata/suq/control01.ctl';restore clone controlfile to  '/opt/oracle/flash_recovery_area/squan/control02.ctl' from '/opt/oracle/oradata/suq/control01.ctl';
}
executing Memory ScriptStarting backup at 24-MAY-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/oracle/product/OraHome11204/dbs/snapcf_squan.f tag=TAG20140524T215011 RECID=7 STAMP=848440211
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 24-MAY-14Starting restore at 24-MAY-14
using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 24-MAY-14contents of Memory Script:
{sql clone 'alter database mount standby database';
}
executing Memory Scriptsql statement: alter database mount standby databasecontents of Memory Script:
{set newname for tempfile  1 to "/opt/oracle/oradata/suq/temp01.dbf";switch clone tempfile all;set newname for datafile  1 to "/opt/oracle/oradata/suq/system01.dbf";set newname for datafile  2 to "/opt/oracle/oradata/suq/sysaux01.dbf";set newname for datafile  3 to "/opt/oracle/oradata/suq/undotbs01.dbf";set newname for datafile  4 to "/opt/oracle/oradata/suq/users01.dbf";set newname for datafile  5 to "/opt/oracle/oradata/suq/test02.dbf";set newname for datafile  6 to "/opt/oracle/oradata/suq/test01.dbf";backup as copy reusedatafile  1 auxiliary format "/opt/oracle/oradata/suq/system01.dbf"   datafile 2 auxiliary format "/opt/oracle/oradata/suq/sysaux01.dbf"   datafile 3 auxiliary format "/opt/oracle/oradata/suq/undotbs01.dbf"   datafile 4 auxiliary format "/opt/oracle/oradata/suq/users01.dbf"   datafile 5 auxiliary format "/opt/oracle/oradata/suq/test02.dbf"   datafile 6 auxiliary format "/opt/oracle/oradata/suq/test01.dbf"   ;sql 'alter system archive log current';
}
executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /opt/oracle/oradata/suq/temp01.dbf in control fileexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 24-MAY-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/opt/oracle/oradata/squan/system01.dbf
output file name=/opt/oracle/oradata/suq/system01.dbf tag=TAG20140524T215020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/opt/oracle/oradata/squan/sysaux01.dbf
output file name=/opt/oracle/oradata/suq/sysaux01.dbf tag=TAG20140524T215020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/opt/oracle/oradata/squan/undotbs01.dbf
output file name=/opt/oracle/oradata/suq/undotbs01.dbf tag=TAG20140524T215020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/opt/oracle/oradata/squan/users01.dbf
output file name=/opt/oracle/oradata/suq/users01.dbf tag=TAG20140524T215020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/opt/oracle/oradata/squan/test02.dbf
output file name=/opt/oracle/oradata/suq/test02.dbf tag=TAG20140524T215020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/opt/oracle/oradata/squan/test01.dbf
output file name=/opt/oracle/oradata/suq/test01.dbf tag=TAG20140524T215020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 24-MAY-14sql statement: alter system archive log currentcontents of Memory Script:
{switch clone datafile all;
}
executing Memory Scriptdatafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=848440398 file name=/opt/oracle/oradata/suq/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=848440398 file name=/opt/oracle/oradata/suq/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=848440398 file name=/opt/oracle/oradata/suq/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=848440398 file name=/opt/oracle/oradata/suq/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=848440398 file name=/opt/oracle/oradata/suq/test02.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=848440398 file name=/opt/oracle/oradata/suq/test01.dbf
Finished Duplicate Db at 24-MAY-14

四、开启standby日志应用

duplicate完成之后备库数据库启动到mount阶段,这时候就可以开启应用日志,最后再重启打开到read only并应用日志:

SQL> select status from v$Instance;STATUS
------------
MOUNTEDSQL> select database_role from v$database;DATABASE_ROLE
----------------
PHYSICAL STANDBYSQL> alter database recover managed standby database disconnect from session;数据库已更改。SQL> alter database open read only;
alter database open read only
*
第 1 行出现错误:
ORA-10456: cannot open standby database; media recovery session may be in
progressSQL> alter database recover managed standby database cancel;数据库已更改。SQL> alter database open read only;数据库已更改。SQL> alter database recover managed standby database disconnect from session;数据库已更改。


五、standby数据库基本管理

查看主库日志传输状态:
SQL> SELECT dest_id,dest_name,status,target,destination,ERROR,db_unique_name FROM v$archive_dest WHERE ROWNUM<3;DEST_ID DEST_NAME	     STATUS    TARGET  DESTINATION     ERROR	  DB_UN
------- -------------------- --------- ------- --------------- ---------- -----1 LOG_ARCHIVE_DEST_1   VALID     PRIMARY /opt/arch		  squan2 LOG_ARCHIVE_DEST_2   VALID     STANDBY 11g2			  suq查看角色和打开模式:
SQL> select DATABASE_ROLE,PROTECTION_MODE,open_mode from v$database; DATABASE_ROLE	 PROTECTION_MODE      OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE  READ ONLY WITH APPLY开启应用:
SQL> alter database recover managed standby database disconnect from session;开启实时应用:
SQL>recover managed standby database using current logfile disconnect from session;关闭应用:
SQL> alter database recover managed standby database cancel;查看是否有gap:
SQL>SELECT * FROM v$archive_gap;查看归档日志是否被应用:
SQL>SELECT NAME,thread#,sequence#,first_time,next_time,applied FROM v$archived_log ORDER BY 1,2;fail over:
SQL> alter database recover managed standby database finish ;
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;standby进程状态:
SQL>SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';







这篇关于使用rman duplicate创建物理active standby dataguard的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/695058

相关文章

中文分词jieba库的使用与实景应用(一)

知识星球:https://articles.zsxq.com/id_fxvgc803qmr2.html 目录 一.定义: 精确模式(默认模式): 全模式: 搜索引擎模式: paddle 模式(基于深度学习的分词模式): 二 自定义词典 三.文本解析   调整词出现的频率 四. 关键词提取 A. 基于TF-IDF算法的关键词提取 B. 基于TextRank算法的关键词提取

使用SecondaryNameNode恢复NameNode的数据

1)需求: NameNode进程挂了并且存储的数据也丢失了,如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。 2)故障模拟 (1)kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 (2)删除NameNode存储的数据(/opt/module/hadoop-3.1.4/data/tmp/dfs/na

Hadoop数据压缩使用介绍

一、压缩原则 (1)运算密集型的Job,少用压缩 (2)IO密集型的Job,多用压缩 二、压缩算法比较 三、压缩位置选择 四、压缩参数配置 1)为了支持多种压缩/解压缩算法,Hadoop引入了编码/解码器 2)要在Hadoop中启用压缩,可以配置如下参数

Makefile简明使用教程

文章目录 规则makefile文件的基本语法:加在命令前的特殊符号:.PHONY伪目标: Makefilev1 直观写法v2 加上中间过程v3 伪目标v4 变量 make 选项-f-n-C Make 是一种流行的构建工具,常用于将源代码转换成可执行文件或者其他形式的输出文件(如库文件、文档等)。Make 可以自动化地执行编译、链接等一系列操作。 规则 makefile文件

使用opencv优化图片(画面变清晰)

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化,使其看起来更清晰,同时保持尺寸不变,通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多,主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器:相机传

【Python编程】Linux创建虚拟环境并配置与notebook相连接

1.创建 使用 venv 创建虚拟环境。例如,在当前目录下创建一个名为 myenv 的虚拟环境: python3 -m venv myenv 2.激活 激活虚拟环境使其成为当前终端会话的活动环境。运行: source myenv/bin/activate 3.与notebook连接 在虚拟环境中,使用 pip 安装 Jupyter 和 ipykernel: pip instal

pdfmake生成pdf的使用

实际项目中有时会有根据填写的表单数据或者其他格式的数据,将数据自动填充到pdf文件中根据固定模板生成pdf文件的需求 文章目录 利用pdfmake生成pdf文件1.下载安装pdfmake第三方包2.封装生成pdf文件的共用配置3.生成pdf文件的文件模板内容4.调用方法生成pdf 利用pdfmake生成pdf文件 1.下载安装pdfmake第三方包 npm i pdfma

零基础学习Redis(10) -- zset类型命令使用

zset是有序集合,内部除了存储元素外,还会存储一个score,存储在zset中的元素会按照score的大小升序排列,不同元素的score可以重复,score相同的元素会按照元素的字典序排列。 1. zset常用命令 1.1 zadd  zadd key [NX | XX] [GT | LT]   [CH] [INCR] score member [score member ...]

在cscode中通过maven创建java项目

在cscode中创建java项目 可以通过博客完成maven的导入 建立maven项目 使用快捷键 Ctrl + Shift + P 建立一个 Maven 项目 1 Ctrl + Shift + P 打开输入框2 输入 "> java create"3 选择 maven4 选择 No Archetype5 输入 域名6 输入项目名称7 建立一个文件目录存放项目,文件名一般为项目名8 确定

git使用的说明总结

Git使用说明 下载安装(下载地址) macOS: Git - Downloading macOS Windows: Git - Downloading Windows Linux/Unix: Git (git-scm.com) 创建新仓库 本地创建新仓库:创建新文件夹,进入文件夹目录,执行指令 git init ,用以创建新的git 克隆仓库 执行指令用以创建一个本地仓库的