搭建实时同步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/orac