微信小程序本地存储与登录页面处理实例详细讲解
838
2022-09-15
Windows 7 环境下配置oracle 11gR2 Data Guard 详细过程
1 服务器环境:
两台PC 机,操作系统为Windows7 旗舰版
数据库版本:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
2 配置前准备:
主库主机名Service1,db_name:orcl,
db_unique_name:primary
备库主机名service2,db_name:orcl,
db_unique_name:standby(下面修改参数之一,不需要特别修改)
在安装oracle 时,主库安装数据库,备库只安装软件
安装路径在C 盘,具体路径为默认C:\app\Administrator\。
实例名为orcl
数据文件放在C:\app\Administrator\oradata\orcl 文件夹下,归档日志文件放在C:\app\Administrator\flash_recovery_area\PRIMARY\ARCHIVELOG\2017_08_24 文件夹下,联机日志文件默认
3 正式配置过程:
3.1 主库配置
在主库启动数据库到mount 模式,开启归档模式与force logging flashback。具体语句如下:
Shutdown immediate
Startup mount
Alter database archivelog;
Alter database force logging;
Alter database flashback on;
3.2 查看当前日志文件组---------都在mount格式下,第一次是在open下
Set wrap off;
Select * from v$logfile order by group#;
这里四组可以简单的理解为在线日志组数+1
alter database add standby logfile group 4 ('C:\app\Administrator\oradata\orcl\REDO04.LOG') size 20m;
alter database add standby logfile group 5 ('C:\app\Administrator\oradata\orcl\REDO05.LOG') size 20m;
alter database add standby logfile group 6 ('C:\app\Administrator\oradata\orcl\REDO06.LOG') size 20m;
alter database add standby logfile group 7 ('C:\app\Administrator\oradata\orcl\REDO07.LOG') size 20m;
再次查询v$logfile
已经建好备用日志了。
3.4 修改系统参数spfile 文件(13 个参数) 都在mount格式下
1 Alter system set db_unique_name='primary' scope=spfile;
2 Alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile;
3 Alter system set log_archive_dest_1='location=C:\app\Administrator\flash_recovery_area\PRIMARY\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile;
要建立目录,否则因为目录不存在而报错
4 Alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;
5 Alter system set log_archive_dest_state_1 = 'enable' scope=spfile;
6 Alter system set log_archive_dest_state_2 = 'enable' scope=spfile;
7 Alter system set fal_server='standby' scope=spfile;
8 Alter system set fal_client='primary' scope=spfile;
9 Alter system set archive_lag_target=1800 scope=spfile;
10 Alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
11 Alter system set standby_file_management=auto scope=spfile;
12 Alter system set db_file_name_convert='standby','primary' scope=spfile;
13 Alter system set log_file_name_convert='standby','primary' scope=spfile;
3.5 启动数据库
Alter database open;
重启数据库将修改的参数加载;
Shutdown immediate
Startup
3.6 创建pfile 参数文件;
create pfile='C:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ORA' from spfile;
名字一定要是INITorcl.ORA
注意:反推
(create spfile from pfile='C:\app\Administrator\product\11.2.0\dbhome_1\database\PFILEORCL.ORA';)
create spfile='C:\app\Administrator\product\11.2.0\dbhome_1\database\PFILEORCL.ORA ' from pfile;
3.7 创建密码文件
密码文件存放在:
C:\app\Administrator\product\11.2.0\dbhome_1\database\
文件名为:PWDorcl.ora
如果在目录下未能找见,则需要动手创建,创建命令为:
Orapwd file=D:\app\Administrator\product\11.2.0\dbhome_1\database\
PWDorcl.ora password=123 entries=5 -----以后可以试试,sys的密码
注:在cmd 命令下输入orapwd。Orcl 为实例名,123 为主库sys
的密码
3.8 配置主库网络监听(备用库同理)-------------------------到此
文件位置在
C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\
修改listener.ora 文件,内容如下:查看附件
修改tnsnames.ora 文件,内容如下:查看附件
3.9 测试监听
重启监听,用网络名连接测试监听是否正常
C:\>lsnrctl stop
C:\>lsnrctl start
重启数据库:
shutdown immediate ---> startup
测试监听
C:\>tnsping primary
C:\>sqlplus sys/WXH@primary as sysdba
如果不能连接,请检查防火墙。
3.10 关闭主数据库 shutdown immediate
3.11 在备库建立文件夹
A. 将C:\app\Administrator 目录下的①admin、②cfgtoollogs、
③diag 目录及④C:\app\Administrator\flash_recovery_area目录及⑤密码文件(C:\app\Administrator\product\11.2.0\dbhome_1\database)拷贝到备用库相同的路径下。
B. 将主库的listener.ora 和tnsnames.ora 拷贝到备库相同路径下(请看附件)。
C. 在主备机E 盘建立C:\app\Administrator\oradata\temp 文件夹
3.12 备机新建实例
在备机上注册oracle 实例到服务中,命令如下(cmd):
Oradim -new -sid orcl(实例名)
3.13 在备库启动监听 CMD下输入 lsnrctl start
注意查看(执行前启动服务),可能有报错,可以循环执行
3.14 备库参数文件
将刚才主库创建的pfile 参数文件initorcl.ora 文件拷贝至备库(C:\app\Administrator\product\11.2.0\dbhome_1\database)
修改为如下:
orcl.__db_cache_size=536870912
orcl.__java_pool_size=8388608
orcl.__large_pool_size=8388608
orcl.__oracle_base='d:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=520093696
orcl.__sga_target=771751936
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=209715200
orcl.__streams_pool_size=0
*.archive_lag_target=1800
*.audit_file_dest='d:\app\Administrator\admin\orcl\adump
'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='E:\oracleDB\orcl\control01.ctl','E:\ora
cleDB\flash\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='primary','standby'
*.db_name='orcl'
*.db_recovery_file_dest='E:\oracleDB\flash'
*.db_recovery_file_dest_size=10737418240
*.db_unique_name='standby'
*.diagnostic_dest='d:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='standby'
*.fal_server='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=E:\oracleDB\flash\orcl\AR
CHIVELOG\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primaryLGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=primary'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='primary','standby'
*.memory_target=1287651328
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
以上红色标注为需要修改成的
用参数文件建立实例(需要首先启动监听、服务,否则会报ORA-12560)
Sqlplus
Sys
WXH AS SYSDBA
startup nomount pfile='C:\app\Administrator\product\11.2.0\dbhome_1\database\INITORCL.ORA'
3.15 使用RMAN 复制主库
在主库startup
若显示,已连接到空闲实例。则
Sqlplus
Sys
WXH AS SYSDBA
startup
3.15.1 用rman 建立备份
Rman target \
WXH AS SYSDBA
RMAN>Backup full database format='C:\app\Administrator\oradata\temp\FOR_STANDBY _%u%p%s.RMN' include current controlfile for standby;
将当前archivelog 归档
RMAN>sql'alter system archive log current';
3.15.2 将rman 的备份集拷贝到standby 库相同的路径 ---千万别忘这一步
3.15.3 复制数据库 ---全是在主库执行哦
建议在此创建控制文件目录(不用复制控制文件:备库):(1)C:\app\Administrator\oradata\orcl ;(2)C:\app\Administrator\flash_recovery_area\orcl
RMAN>connect auxiliary sys/WXH@standby
RMAN>duplicate target database for standby nofilenamecheck;
3.15.4 备库启动standby
第一种方案:alter database mount standby database(出现ORA-01100:数据库已装载报错属于正常现象
);→Alter database recover managed standby database disconnect from session;
第二种方案(推荐):Shutdown immediate—Startup-- Alter database recover managed standby database disconnect from session; (执行本命令,备库会自动应用归档日志)
第三种方案: alter database mount standby database;→Alter database recover managed standby database disconnect from session;--- alter database recover managed standby database cancel;→alter database open;→Alter database recover managed standby database disconnect from session;
4 DataGuard 验证
下面来验证是否搭建成功
主、备库执行
Select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc;
select sequence#,appliedfrom v$archived_logorder by sequence#asc;
两边sequence#一致,说明已经同步
创建测试表
create table CESHI
(
FY_ID NUMBER(9)not null,
STU_ID NUMBER(9)not null,
MX_ID NUMBER(9)not null,
YIJIAO NUMBER(7,2)not null,
QIANFEI NUMBER(7,2)not null
)
下面在主库切换活动日志,然后查询日志同步情况
Alter system switch logfile;
(手动切换日志组
日志组A:当前正在写
日志组B:可写
手动切换日志组后,Oracle开始往日志组B里写日志,并进行一次checkpoint,把日志组A里没有经过checkpoint的那部分日志对应的dirty buffer从buffer cache里写到数据文件里。
checkpoint完成之后
日志组A:可写
日志组B:当前正在写)
稍后再查询主库
若一致,说明配置成功。
5 关于switchover(正常主备切换)
首先查询主库切换状态
Select switchover_status from v$database;
TO STANDBY 表示可以切换,如果是其它的需要根据情况处理后再切
换。
在主库将primary 切换到physical standby
主库执行以下命令
Alter database commit to switchover to physical standby with session shutdown;
Shutdown immediate
Startup
Alter database recover managed standby database disconnect from session;
(执行本命令,备库会自动应用归档日志)
在备库,switchover 到primary:
ALTER DATABASE COMMIT TO SWITCHOVER TO primary WITH SESSION SHUTDOWN;
Shutdown immediate
Startup
Alter system switch logfile;
在新主库和新备库执行如下命令:
Select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc;
select sequence#,appliedfrom v$archived_logorder by sequence#asc;
在新主库执行:alter system switch logfile;
新主库执行/新备库执行
select sequence#,appliedfrom v$archived_logorder by sequence#asc;
select sequence#,appliedfrom v$archived_logorder by sequence#asc;
两边sequence#一致,说明已经同步,dataguard 切换成功
参考:http://docin.com/p-453570668.html
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。
发表评论
暂时没有评论,来抢沙发吧~