搭建实时同步data guard的最高可用-切换主备
首先保证主库在归档模式下:错过N次了
准备二台机器(hostname gw hosts ech0)host-only
[root@node1 ~]# ifconfig
eth0 Link encap:Ethernet HWaddr 08:00:27:9C:CC:51
inet addr:192.168.56.147 Bcast:192.168.56.255 Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fe9c:cc51/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:750606 errors:0 dropped:0 overruns:0 frame:0
TX packets:132742 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:179921301 (171.5 MiB) TX bytes:2845074889 (2.6 GiB)
eth0:1 Link encap:Ethernet HWaddr 08:00:27:9C:CC:51
inet addr:192.168.1.147 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
主库
ip 192.168.1.147 node1.dg.com 安装好ORACLE,正常运行的数据库
ipadd: 192.168.1.147 gw:192.168.1.1
oracle sid: orcl
database name :orcl
service name :orcl
备库
ip 192.168.1.157 node1.dg.com 只安装软件就可以了
ipadd: 192.168.1.157
gw:192.168.1.1
oracle sid: orcl02
database name :orcl –>
service name :orcl –>
步骤
1 –在二台机器上都建立oracleNET 即生成listner.ora及tnsnames.ora
–主库已有listner.ora and tnsnames.ora,需要增加tnsnames.ora到备库network service names
netmgr
–只需要在备库上建立(监听为静态监听)和tnsnames
–以上建立完毕，需要主备都启监听(注意VBOX可以调界面)
1.1 ssh 192.168.1.147—> on primary
netmgr
/*1.1 cp -v $ORACLE_HOME/network/admin/listner.ora $ORACLE_HOME/network/admin/listner.ora.std
cp -v $ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.ora.std
vi listner.ora.std
vi tnsnames.ora.std
scp listner.ora.std oracle@192.168.1.57:$ORACLE_HOME/network/admin/listner.ora
scp tnsnames.ora.std oracle@192.168.1.57:$ORACLE_HOME/network/admin/tnsnames.ora
[oracle@node1 ~]$ cat /u01/app/oracle/product/11.2.0.1/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.1/db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.147)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
*/
lsnrctl start –>on primary,stdby二边都要做
2 –准备备库的parameter file
–on primary
SQL> show parameter name
NAME TYPE VALUE
———————————— ———–
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
service_names string orcl
SQL>
SQL> select name from v$controlfile;
NAME
————————————————————————————————————————
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
SQL> select name from v$datafile;
NAME
————————————————————————————————————————
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs1.dbf
7 rows selected.
SQL>
–add parameter in spfile on primary
SQL> alter system set db_unique_name=’orcl’ scope=spfile;
SQL> alter system set service_names=’orcl’ scope=spfile;
SQL> alter system set log_archive_config=‘dg_config=(orcl,orclstd)‘ ;
SQL> alter system set log_archive_dest_1=’location=/u01/app/oracle/archivelog/orcl valid_for=(all_logfiles,all_roles) db_unique_name=orcl’ ;
SQL> alter session set log_archive_dest_state_2=defer;
SQL> alter system set log_archive_dest_2=‘service=to_157 valid_for=(online_logfile,primary_role) db_unique_name=orclstd’;
dg_config 注册成员实例名:oracle_sid
location 本地相关信息
service=to_157 输送到备库监听名称 to_157字符串
—
— alter system set log_file_name_convert=’remote_archive_path’,’local_archive_path’
— /home/oracle/archive,/opt/oracle/oradata
— alter system set db_file_name_convert=’remote_oardata_path’,’local_oradata_path’
— /home/oracle/oradata,/opt/oracle/oradata
SQL> alter system set dispatchers=”;–在备库中一定删除掉
SQL> create pfile=’/home/oracle/p.ora’ from spfile;
[oracle@node1 ~]$ cp -v p.ora s.ora
`p.ora’ -> `s.ora’
[oracle@node1 ~]$ vim s.ora
[oracle@node1 ~]$ diff p.ora s.ora
1,9d0
< orcl.__db_cache_size=37748736
< orcl.__java_pool_size=4194304
< orcl.__large_pool_size=4194304
< orcl.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
< orcl.__pga_aggregate_target=117440512
< orcl.__sga_target=218103808
< orcl.__shared_io_pool_size=0
< orcl.__shared_pool_size=130023424
< orcl.__streams_pool_size=4194304
22c13
< *.db_unique_name=’orcl’
—
> *.db_unique_name=’orclstd’
24d14
< *.dispatchers='(protocol=TCP)’
27,28c17,18
< *.log_archive_dest_1=’location=/u01/app/oracle/archivelog/orcl valid_for=(all_logfiles,all_roles) db_unique_name=orcl’
< *.log_archive_dest_2=’service=to_157 valid_for=(online_logfile,primary_role) db_unique_name=orcl02′
*.log_arvhice_dest_state_2=defer;
—
> *.log_archive_dest_1=’location=/u01/app/oracle/archivelog/orcl valid_for=(all_logfiles,all_roles) db_unique_name=orclstd’
> *.log_archive_dest_2=’service=to_147 valid_for=(online_logfile,primary_role) db_unique_name=orcl’
[oracle@node1 ~]$ scp s.ora oracle@192.168.1.57:/home/oracle
oracle@192.168.1.157’s password:
s.ora 100% 1156 1.1KB/s 00:00
[oracle@node1 ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcloracle@192.168.1.157:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworclstd
oracle@192.168.1.157’s password:
orapworcl 100% 1536 1.5KB/s 00:00
[oracle@node1 ~]$
— on stdby
[oracle@node2 ~]$ export ORACLE_SID=orcl02
[oracle@node2 ~]$ cat s.oar
–下面照着参数文件进行修改一下
mkdir -pv /u01/app/oracle/admin/orcl/adump
mkdir -pv /u01/app/oracle/oradata/orcl
mkdir -pv /u01/app/oracle/flash_recovery_area/orcl
mkdir -pv /u01/app/oracle/archivelog/orcl
SQL> startup nomount pfile=’/home/oracle/s.ora’;
SQL>create spfile from pfile=’/home/oracle/s.ora’;
–处于NOMOUNT状态,备库
[oracle@node2 ~]$ rman target sys/oracle@to_147 auxiliary sys/oracle@to_257
Recovery Manager: Release 11.2.0.1.0 – Production on Thu Mar 8 12:00:44 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1334497743)
connected to auxiliary database: ORCL (not mounted)
`
duplicate target database for standby from active database nofilenamecheck;
–duplicate target database for standby from active database nofilenamecheck dorecover;
–on standby
SQL>alter database open read only–这一步是相当重要的
SQL> alter database open;
SQL> alter database recover managed standby database disconnect from session;
SQL>
SQL> alter database recover managed standby database cancel;
SQL> ho lsnrctl stop
SQL> shutdown immeidate;
测试是否归档时,数据能够同步到对面去:
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
关机顺序
主库:
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
备库:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
注意Data Guard 启动顺序：
启动顺序：先standby ,后primary;
闭顺序：先primary 后standby;
手工注册没有同步的日志,手工COPE过去,这样才能认得到
alter database register logfile ‘/home/oracle/archive/1_13_67867676.dbf’;
alter database register logfile ‘/home/oracle/archive/1_14_67867676.dbf’;
alter database register logfile ‘/home/oracle/archive/1_15_67867676.dbf’;
select open_mod,protection_mode,database_role from v$database;
select dest_id,error from v$archive_dest where DEST_ID in (1,2);
select sequence#,archvie
–最大可用的学习笔记
SQL> select database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
二边都要做主备库都要创建为以后切换作准备
SQL> ho mkdir -vp /home/oracle/orcl_stdlog
mkdir: created directory `/home/oracle/orcl_stdlog’
SQL> show parameter standby
NAME TYPE VALUE
———————————— ———– ——————————
standby_archive_dest string dbs/arch
standby_file_management string MANUAL
SQL> alter database add standby logfile ‘//home/oracle/orcl_stdlog/stdbylog01.dbf’ size .
SQL> alter database recover managed standby database cancel;
Database altered.
select * from v$standby_log;
SQL> alter database add standby logfile ‘/home/oracle/orcl_stdlog/stdbylog01.dbf’ size 50m;
Database altered.
–下面修改了SQL后加”/”执行一遍一定要大于在线三组的日志,在这里面建立了四组日志
SQL> c/01/02;
1* alter database add standby logfile ‘/home/oracle/orcl_stdlog/stdbylog02.dbf’ size 50m
SQL> c/02/03
1* alter database add standby logfile ‘/home/oracle/orcl_stdlog/stdbylog03.dbf’ size 50m
SQL> c/03/04
1* alter database add standby logfile ‘/home/oracle/orcl_stdlog/stdbylog04.dbf’ size 50m
SQL> alter system set standby_file_management=auto;
System altered.
SQL>
SQL> alter system set standby_file_management=auto;–注意二边都要设置成自动,增加stdbylog之前
手动:
—————–
主库:
alter system set log_archive_dest_1=’location=/home/oracle/log_archive lgwr affirm sync valid_for=(all_logfiles,all_roles) db_unique_name=orcl’;
alter system set log_archive_dest_2=’service=to_157 lgwr affirm sync valid_for=(online_logfile,primary_role) db_unique_name=orcl02′;
alter system set log_archive_dest_3=’location=/home/oracle/standbylog_archive valid_for=(standby_logfile,standby_role) db_unique_name=orcl’;
ho mkdir -pv /home/oracle/standbylog_archive;
ho mkdir -pv /home/oracle/standbylog_archive;
standby–备库,为未来做切换做好准备
主库假设就是切换成备库
ho mkdir -pv /home/oracle/standbylog_archive;
alter system set log_archive_dest_1=’location=/home/oracle/log_archive lgwr affirm sync valid_for=(all_logfiles,all_roles) db_unique_name=orcl02′;
alter system set log_archive_dest_2=’service=to_147 lgwr affirm sync valid_for=(online_logfle,primary_role) db_unique_name=orcl’;
alter system set log_archive_dest_3=’location=/home/oracle/standbylog_archive valid_for=(standby_logfile,standby_role) db_unique_name=orcl02′;
ho mkdir -pv /home/oracle/standbylog_archive;
查询是否已经应用:
select sequence#,applied,archived from v$archived_log;
show parameter log_archive_dest
备库:
–准备这一次在创建好,实时应用日志
alter database recover managed standby database using current logfile disconnect from session
主库:
select protection_mode from v$database;
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> select SWITCHOVER_STATUS from v$database;
SWITCHOVER_STATUS
——————–
TO STANDBY
alter database set standby database to maximize availability;
查询自己创建了多少组备库的日志组
SQL> select * from v$standby_log;
select group#,dbid,thread#,sequence# from v$standby_log;
SQL> select status from v$instance;
STATUS
————
OPEN
SQL> select open_mode from v$database;
OPEN_MODE
——————–
select protection_mode,database_role,protection_level from v$database;
主库主动关闭
SQL> select SWITCHOVER_STATUS from v$database;
SWITCHOVER_STATUS
——————–
TO STANDBY
SQL> alter system switch logfile;
System altered.
SQL> /
Database altered.
select open_mode from v$database;
select dest_id,status,destination,error from v$archive_dest where dest_id<=5;
SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1
AND SEQUENCE# BETWEEN 50 AND 202;
———-切换主备 切换之前前再一TNSPING 网络 切换之后记得改ENABLE 新主库alter system set log_archive_dest_state_2=enable;
主库
在最大可用的物理standby要拥有自己的联机日志:接收主库传送过来的redo条目
alter database add standby logfile ” size 50m;
alter database add standby logfile ” size 50m;
alter database add standby logfile ” size 50m;
alter database add standby logfile ” size 50m;
SQL> alter system set standby_file_management=auto;
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
——————–
TO STANDBY
SQL> alter database commit to switchover to physical standby;
Database altered.
SQL> select status from v$instance;
STATUS
————
STARTED
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 931143680 bytes
Fixed Size 2218872 bytes
Variable Size 578815112 bytes
Database Buffers 343932928 bytes
Redo Buffers 6176768 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> alter system set standby_file_management=auto;
System altered.
SQL> select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL
——————– —————- ——————–
MAXIMUM AVAILABILITY PHYSICAL STANDBY RESYNCHRONIZATION
SQL>
备库SQL> select PROTECTION_MODE from v$database;
PROTECTION_MODE
——————–
MAXIMUM AVAILABILITY
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
——————–
NOT ALLOWED
SQL> alter database commit to switchover to primary;
Database altered.
SQL> alter system set log_archive_dest_state_2=enable;–理解这二个地方的意思
来源：葛凹先生
声明：本站部分文章及图片转载于互联网，内容版权归原作者所有，如本站任何资料有侵权请您尽早请联系jinwei@zod.com.cn进行处理,非常感谢！