技术标签: Data Guard Oracle 11g
角色 | 主库 | 备库 |
---|---|---|
IP | 192.168.1.59 | 192.168.1.51/52 |
数据库类型 | 单实例 | RAC |
实例 | orcl | orcl1,orcl2 |
db_name | orcl | orcl |
db_unique_name | orcl_st | orcl |
服务名 | orcl_st | orcl_pd |
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/app/oracle/product/12.1.0/db_1/dbs/arch
Oldest online log sequence 22
Current log sequence 23
sqlplus / as sysdba
SQL> alter system set log_archive_dest_1='location= /oracle/archive' scope=spfile sid='*';
将数据库启动到mount状态并修改数据库的归档模式并启动数据库
SQL>shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/archive
Oldest online log sequence 59
Next log sequence to archive 60
Current log sequence 60
用 net manager 工具,在备库创建一个监听。 也可以手动的修改 listener.ora 文件。
--对于 RAC 环境:
用grid用户操作
节点1
[grid@rac1 admin]$ vi /oracle/app/12.1.0/grid/network/admin/listener.ora
添加以下内容:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/app/oracle/product/12.1.0/db_1`)
(SID_NAME = orcl1)
)
)
节点2
[grid@rac2 admin]$ vi /oracle/app/12.1.0/grid/network/admin/listener.ora
添加以下内容:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/app/oracle/product/12.1.0/db_1`)
(SID_NAME = orcl2)
)
)
--这里写的 Oracle 用户的 ORACLE_HOME,否则连接时会报错:
ORA-01031: insufficient privileges
然后重启监听
[grid@rac1 admin]$ lsnrctl reload
[grid@rac2 admin]$ lsnrctl reload
单实例:
用oracle用户操作
修改listenerr.ora文件添加以下内容:
[oracle@adg admin]$ cp listener.ora listener.ora.ora
[oracle@adg admin]$ vi /oracle/app/product/12.1.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/app/oracle/product/12.1.0/db_1)
(SID_NAME = orcl)
)
)
然后重启监听
[oracle@adg admin]$ lsnrctl reload
[oracle@rac1 admin]$ cp tnsnames.ora tnsnames.ora.bak
节点1,节点2,单实例 的 tnsnames.ora 文件添加以下内容,对应rac使用netmgr创建服务或者直接把单实例的tnsnames.ora 复制过去对应修改即可。
[oracle@rac1 admin]$ vi /oracle/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
orcl_pd=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.52)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =orcl)
)
)
orcl_st=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.59)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
配置完成后,使用 tnsping 命令效验:
[oracle@rac1 ~]$ tnsping orcl_pd
[oracle@rac2 ~]$ tnsping orcl_st
[oracle@adg admin]$ mkdir -p /oracle/app/oracle/fast_recovery_area/orcl
[oracle@adg admin]$ mkdir -p /oracle/app/oracle/oradata/orcl
[oracle@adg admin]$ mkdir -p /oracle/app/oracle/admin/orcl/adump
[grid@rac1 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCRVOTE/
ASMCMD> cd data
ASMCMD> mkdir orcl
ASMCMD> cd orcl
ASMCMD> mkdir controlfile datafile onlinelog parameterfile password tempfile
主库
[oracle@rac1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl1 password=oracle
[oracle@rac2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl2 password=oracle
备库
[oracle@adg ~]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle
或者把节点的口令文件copy 到备库
[oracle@adg dbs]$ scp orapworcl 192.168.1.51:/oracle/app/oracle/product/12.1.0/db_1/dbs/orapworcl1
[oracle@adg dbs]$ scp orapworcl 192.168.1.52:/oracle/app/oracle/product/12.1.0/db_1/dbs/orapworcl2
alter system set log_archive_config='dg_config=(orcl,orcl_st)' scope=both sid='*';
alter system set log_archive_dest_1='location=/oracle/archive valid_for=(all_logfiles,all_roles) db_unique_name=orcl_st' scope=both sid='*';
alter system set log_archive_dest_2='service=orcl_pd valid_for=(online_logfiles,primary_role) db_unique_name=orcl' scope=both sid='*';
alter system set log_archive_dest_state_1=enable scope=both sid='*';
alter system set log_archive_dest_state_2=enable scope=both sid='*';
alter system set standby_file_management='auto' scope=both sid='*';
alter system set fal_server='orcl_pd' scope=both sid='*';
alter system set db_file_name_convert='+DATA/orcl/datafile','/oracle/app/oracle/oradata/orcl','+DATA/orcl/tempfile','/oracle/app/oracle/oradata/orcl' scope=both sid='*';
alter system set log_file_name_convert='+DATA/orcl/onlinelog','/oracle/app/oracle/oradata/orcl' scope=both sid='*';
alter system set log_archive_format='%t_%s_%r.arch' scope=both sid='*';
在主库创建pfile 文件并scp 到备库修改
主要指定一些pfile的路径,不要直接create pfile from spfile
create pfile='/tmp/initorcl1.ora' from spfile;
[oracle@rac1 tmp]$ scp /tmp/initorcl1.ora 192.168.1.51:/oracle/app/oracle/product/12.1.0/db_1/dbs
修改备库的参数文件添加以下内容:
orcl2.__data_transfer_cache_size=0
orcl1.__data_transfer_cache_size=0
orcl2.__db_cache_size=369098752
orcl1.__db_cache_size=419430400
orcl2.__java_pool_size=16777216
orcl1.__java_pool_size=16777216
orcl2.__large_pool_size=33554432
orcl1.__large_pool_size=33554432
orcl1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
orcl2.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
orcl2.__pga_aggregate_target=402653184
orcl1.__pga_aggregate_target=402653184
orcl2.__sga_target=771751936
orcl1.__sga_target=771751936
orcl2.__shared_io_pool_size=33554432
orcl1.__shared_io_pool_size=33554432
orcl2.__shared_pool_size=301989888
orcl1.__shared_pool_size=251658240
orcl2.__streams_pool_size=0
orcl1.__streams_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.1.0.2.0'
*.control_files='+DATA/ORCL/CONTROLFILE/current.261.985172099'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
orcl2.instance_number=2
orcl1.instance_number=1
*.memory_target=1120m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='exclusive'
orcl2.thread=2
orcl1.thread=1
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'
--添加以下内容,对应修改上面的参数
*.service_names='orcl_pd'
*.db_unique_name='orcl'
*.log_archive_config='dg_config=(orcl,orcl_st)'
*.log_archive_dest_1='location=+FRA valid_for=(all_logfiles,all_roles) db_unique_name=orcl'
*.log_archive_dest_2='service=orcl_st valid_for=(online_logfiles,primary_role) db_unique_name=orcl_st'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arch'
*.standby_file_management='auto'
*.fal_server='orcl_st'
*.log_file_name_convert='/oracle/app/oracle/oradata/orcl','+DATA/orcl/onlinelog'
*.db_file_name_convert='/oracle/app/oracle/oradata/orcl','+DATA/orcl/datafile','/u01/app/oracle/oradata/orcl/tempfile','+DATA/orcl/tempfile'
[oracle@adg orcl]$ lsnrctl start
[oracle@adg orcl]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 19 20:46:28 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/oracle/app/oracle/product/12.1.0/db_1/dbs/initorcl1.ora'
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 2932336 bytes
Variable Size 654311824 bytes
Database Buffers 385875968 bytes
Redo Buffers 5455872 bytes
--源端
sqlplus sys/oracle@orcl_st as sysdba
rman target sys/oracle@orcl_st
--目标端
sqlplus sys/oracle@orcl_pd as sysdba
rman target sys/oracle@orcl_pd
[oracle@adg orcl]$ rman target sys/oracle@orcl_st auxiliary sys/oracle@orcl_pd
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Sep 21 00:11:05 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1512952835)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby from active database dorecover;
create spfile='+DATA/' from pfile='/tmp/initorcl1.ora';
SQL> shutdown immediate
注册数据库及参数文件
[oracle@rac1 ~]$srvctl add database -d orcl -o /oracle/app/oracle/product/12.1.0/db_1/ -p +data/orcl/parameterfile/spfile.268.985172961
注册实例
[oracle@rac1 ~]$srvctl add instance -d orcl -i orcl1 -n rac1
[oracle@rac1 ~]$srvctl add instance -d orcl -i orcl2 -n rac2
[oracle@rac1 ~]$srvctl stop database -d orcl
[oracle@rac1 ~]$srvctl start database -d orcl
[oracle@rac1 ~]$ srvctl status database -d orcl
Instance orcl1 is running on node rac1
Instance orcl2 is running on node rac2
RAC 每个 Redo Thread 都需要创建对应的 Standby Redo Log。 创建原则和单实例一样,包括日志 文件大小相等,日志组数量要多1组。
SQL> set lines 120
SQL> col member for a50
SQL> select a.thread#,a.group#,a.bytes/1024/1024,b.member from v$log a,v$logfile b where a.group#=b.group#;
THREAD# GROUP# A.BYTES/1024/1024 MEMBER
---------- ---------- ----------------- --------------------------------------------------
1 2 50 +DATA/ORCL/ONLINELOG/group_2.263.985172107
1 1 50 +DATA/ORCL/ONLINELOG/group_1.262.985172105
2 3 50 +DATA/ORCL/ONLINELOG/group_3.266.985172955
2 4 50 +DATA/ORCL/ONLINELOG/group_1.267.985172957
alter database add standby logfile thread 1 group 5 ('+DATA') size 50m;
alter database add standby logfile thread 1 group 6 ('+DATA') size 50m;
alter database add standby logfile thread 1 group 7 ('+DATA') size 50m;
alter database add standby logfile thread 2 group 8 ('+DATA') size 50m;
alter database add standby logfile thread 2 group 9 ('+DATA') size 50m;
alter database add standby logfile thread 2 group 10 ('+DATA') size 50m;
select group#,type,member from v$logfile order by 2;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
2 ONLINE +DATA/ORCL/ONLINELOG/group_2.263.985172107
4 ONLINE +DATA/ORCL/ONLINELOG/group_1.267.985172957
3 ONLINE +DATA/ORCL/ONLINELOG/group_3.266.985172955
1 ONLINE +DATA/ORCL/ONLINELOG/group_1.262.985172105
9 STANDBY +DATA/ORCL/ONLINELOG/group_9.273.987282345
8 STANDBY +DATA/ORCL/ONLINELOG/group_8.272.987282341
5 STANDBY +DATA/ORCL/ONLINELOG/group_5.271.987282333
7 STANDBY +DATA/ORCL/ONLINELOG/group_7.270.987282315
10 STANDBY +DATA/ORCL/ONLINELOG/group_10.271.987282353
6 STANDBY +DATA/ORCL/ONLINELOG/group_6.269.987282309
SQL> set lines 120
SQL> col member for a80
SQL> select a.thread#,a.group#,a.bytes/1024/1024,b.member from v$log a,v$logfile b where a.group#=b.group#;
THREAD# GROUP# A.BYTES/1024/1024 MEMBER
---------- ---------- ----------------- --------------------------------------------------------------------------------
1 1 50 /oracle/app/oracle/oradata/orcl/group_1.262.985172105
1 2 50 /oracle/app/oracle/oradata/orcl/group_2.263.985172107
2 3 50 /oracle/app/oracle/oradata/orcl/group_3.266.985172955
2 4 50 /oracle/app/oracle/oradata/orcl/group_1.267.985172957
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 '/oracle/app/oracle/oradata/orcl/stbredo01.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 '/oracle/app/oracle/oradata/orcl/stbredo02.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 '/oracle/app/oracle/oradata/orcl/stbredo03.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 '/oracle/app/oracle/oradata/orcl/stbredo01.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 9 '/oracle/app/oracle/oradata/orcl/stbredo05.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 10 '/oracle/app/oracle/oradata/orcl/stbredo06.log' SIZE 50M;
SQL> select group#,type,member from v$logfile order by 2;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------------------------------------
2 ONLINE /oracle/app/oracle/oradata/orcl/group_2.263.985172107
4 ONLINE /oracle/app/oracle/oradata/orcl/group_1.267.985172957
3 ONLINE /oracle/app/oracle/oradata/orcl/group_3.266.985172955
1 ONLINE /oracle/app/oracle/oradata/orcl/group_1.262.985172105
9 STANDBY /oracle/app/oracle/oradata/orcl/stbredo05.log
8 STANDBY /oracle/app/oracle/oradata/orcl/stbredo01.log
7 STANDBY /oracle/app/oracle/oradata/orcl/stbredo03.log
6 STANDBY /oracle/app/oracle/oradata/orcl/stbredo02.log
10 STANDBY /oracle/app/oracle/oradata/orcl/stbredo06.log
5 STANDBY /oracle/app/oracle/oradata/orcl/stbredo01.log
SQL> select NAME,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL MOUNTED
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
文章浏览阅读502次。原标题:用Python帮小姐姐选口红,人人都是李佳琦 对于李佳琦,想必知道他的女生要远远多于男生,李佳琦最早由于直播向广大的网友们推荐口红,逐渐走红网络,被大家称作“口红一哥”。不可否认的是,李佳琦的直播能力确实很强,他能够抓住绝大多数人的心理,让大家喜欢看他的直播,看他直播推荐的口红适不适合自己,色号适合什么样子的妆容。为了提升效率,让自己的家人或者女友能够快速的挑选出合适自己妆容的口红色号,今..._获取口红品牌 及色号,色值api
文章浏览阅读3.6k次。简介awk命令的名称是取自三位创始人Alfred Aho 、Peter Weinberger 和 Brian Kernighan姓名的首字母,awk有自己的程序设计语言,设计简短的程序,读入文件,数据排序,处理数据,生成报表等功能。awk 通常用于文本处理和报表生成,最基本功能是在文件或者字符串中基于指定规则浏览和抽取信息,awk抽取信息后,才能进行其他文本操作。awk 通常以文件的一行为处理单位..._linux awk nr
文章浏览阅读1.3w次,点赞5次,收藏2次。在网上找了一个小时,一直没有头绪,因为上个星期还是好好的,最后看到一个大神的解答,只需要将防火墙关闭就好了.原本向测试功能的,却卡在了登录上.以此记录.另外好像还有种错误是电脑与手机连接的WiFi不同,也可以看看...._failed to connect to 192.168.88.218:80
文章浏览阅读1.9k次。利用MATLAB仿真多种多径衰落信道摘要:移动信道的多径传播引起的瑞利衰落,时延扩展以及伴随接收过程的多普勒频移使接受信号受到严重的衰落,阴影效应会是接受的的信号过弱而造成通信的中断:在信道中存在噪声和干扰,也会是接收信号失真而造成误码,所以通过仿真找到衰落的原因并采取一些信号处理技术来改善信号接收质量显得很重要,这里利用MATLAB对多径衰落信道的波形做一比较。一,多径衰落信道的特点关于多径衰落..._matlab多径衰落工具箱
文章浏览阅读1w次,点赞2次,收藏17次。Json简介:Json,全名 JavaScript Object Notation,是一种轻量级的数据交换格式。它基于 ECMAScript (w3c制定的js规范)的一个子集,采用完全独立于编程语言的文本格式来存储和表示数据。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。 易于人阅读和编写,同时也易于机器解析和生成,并有效地提升网络传输效率。(来自百度百科)python关于json文_import json灰色
文章浏览阅读1.1k次,点赞6次,收藏3次。一、工作原理MHA工作原理总结为以下几条:(1) 从宕机崩溃的 master 保存二进制日志事件(binlog events);(2) 识别含有最新更新的 slave ;(3) 应用差异的中继日志(relay log) 到其他 slave ;(4) 应用从 master 保存的二进制日志事件(binlog events);(5) 通过Manager控制器提升一个 slave 为新 m..._mysql mha超详细教程
文章浏览阅读194次。一 java环境安装:1 安装JDK 参考链接地址:https://blog.csdn.net/qq_42815754/article/details/82968464注:有网情况下直接 yum 一键安装:yum -y list java(1)首先执行以下命令查看可安装的jdk版本(2)选择自己需要的jdk版本进行安装,比如这里安装1.8,执行以下命令:yum install -y java-1.8.0-openjdk-devel.x86_64(3)安装完之后,查看安装的jdk 版本,输入以下指令_linux的java主从策略是什么
文章浏览阅读104次。定义int 类型,由while实现A,B的连续输入,输出A+B的值按Ctrl Z结束循环。#include<iostream>using namespace std;int main(){ int A,B; while(cin>>A>>B) { cout<<A+B<&_acm竞赛题 i 'm from mars
文章浏览阅读5.2k次。在需要给TextView的某句话添加点击事件的时候,我们一般会使用ClickableSpan来进行富文本编辑。与此同时我们还需要配合 textView.setMovementMethod(LinkMovementMethod.getInstance());方法才能使点击处理生效。但与此同时还会有一个问题:如果我们给父布局添加一个点击事件,需要在点击非链接的时候触发(例如RectclerV..._linkmovementmethod
文章浏览阅读1.1w次,点赞6次,收藏31次。JAVA实现压缩解压文件_java 解压zip
文章浏览阅读1.3w次,点赞7次,收藏21次。在Java 8 中使用Stream 例子对一个 Map 进行按照keys或者values排序.1. 快速入门 在java 8中按照此步骤对map进行排序.将 Map 转换为 Stream 对其进行排序 Collect and return a new LinkedHashMap (保持顺序)Map result = map.entrySet().stream() .sort..._java comparingbykey
文章浏览阅读497次。第一次参加GDKOI,考完感觉还可以,结果发现还是不行,有一些地方细节打错,有些失分严重,总结出以下几点:1.大模拟一定要注意,细节打挂就是没分,像T1就是一道大模拟题,马上切了,后面就没想着检查以下,导致有些地方挂掉了,用民间数据一测,才85分。2.十年OI一场空,不开longlonglong longlonglong见祖宗。今天的T2本来想用暴力水点分的,结果没想到longlong→intlong long\to intlonglong→int,40→040\to040→0。3.代码实现能力太差,_gdkoi