oracle 搭建实时同步data guard的最高可用-切换主备

搭建实时同步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  [email protected]:$ORACLE_HOME/network/admin/listner.ora
      scp tnsnames.ora.std [email protected]:$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 [email protected]:/home/oracle
[email protected]'s password: 
s.ora                                            100% 1156     1.1KB/s   00:00    
[oracle@node1 ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl[email protected]:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworclstd
[email protected]'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