搭建实时同步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/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;--理解这二个地方的意思

System altered.

SQL> alter system set standby_file_management=auto;--理解这二个地方的意思,切换时会报错

System altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select protection_mode,database_role,protection_level from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PRIMARY          RESYNCHRONIZATION
实践体现:
1、oracle数据库每天凌晨4：00：00会对现网的adway的数据做一个exp导出整个用户的操作，在现网数据有问题时，可以恢复到前一天的凌晨4点的状态。
  需要整个数据库至昨天的状态步骤如下：
  SQL> drop user zboss_user;
  SQL> create user zboss_user identified by izptec;
  SQL> grant connect,resource to zboss_user;
  cd /home/oracle/backdm/
  imp zboss_user/izptec file=./zboss1_date_2010-12-20.dmp full=y
  需要恢复表table1、table2至昨天的状态
  cd /home/oracle/backdm/
  进入数据库把两张表清空，
  imp zboss_user/izptec file=./zboss1_date_2010-12-20.dmp tables=table1，table2
2、oracle在武汉111.175.242.22服务器上做了dataguard，对现网数据库进行实时同步，若现网数据库down机了，恢复只需要15分钟 
   主库需要做的操作
  Dataguard 角色切换（由PRIMARY DB到STANDBY DB）
  首先应把primary db切换成standby db（如果主库已经down掉了，就不用做这一步了）
  SQL>alter  database commit to switchover to physical standby with session shutdown;
  SQL>shutdown immediate;
  SQL>startup mount;
  SQL>alter database recover managed standby database disconnect from session;
  查看状态
  SQL>select name,database_role from v$database;
  NAME      DB_UNIQUE_NAME                 PROTECTION_MODE
  --------- ------------------------------ --------------------
  STANDBY   STANDBY                        UNPROTECTED
  看到主库已经成了备库.
  备库需要做的操作
  接着把standby db 切换成primary db
  SQL> alter database commit to switchover to primary;
  SQL> shutdown immediate;
  SQL> startup;
  查看状态
  SQL> select name,database_role from v$database;
  NAME      DATABASE_ROLE
  --------- ----------------
  STANDBY   PRIMARY
  从primary关键字可以看出备库切换成了主库.
  这样就恢复到主库down机前的状态了。
===========================================主库一轮切换======================
  datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=825206190 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=825206190 file name=/u01/app/oracle/oradata/orcl/users01.dbf

contents of Memory Script:
{
   set until scn  1050293;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 04-SEP-13
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/11.2.0.1/db_1/dbs/arch1_2_825206525.dbf
archived log file name=/u01/app/oracle/product/11.2.0.1/db_1/dbs/arch1_2_825206525.dbf thread=1 sequence=2
media recovery complete, elapsed time: 00:00:01
Finished recover at 04-SEP-13
Finished Duplicate Db at 04-SEP-13

RMAN> exit

Recovery Manager complete.
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 4 00:14:14 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  select group#,dbid,thread# from v$standby_log;

    GROUP# DBID                                        THREAD#
---------- ---------------------------------------- ----------
         4 UNASSIGNED                                        1
         5 UNASSIGNED                                        1
         6 UNASSIGNED                                        0
         7 UNASSIGNED                                        0

SQL> ho /home/oracle/standby_log_archive;
/bin/bash: /home/oracle/standby_log_archive: No such file or directory

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$ ls /home/oracle/standbylog_archive
1_19_825119295.dbf  1_21_825119295.dbf
1_20_825119295.dbf  o1_mf_2_92ct2t1v_.log
[oracle@node1 ~]$ ls /home/oracle/standbylog_archive -l
total 62844
-rw-r----- 1 oracle oinstall     1024 Sep  3 22:56 1_19_825119295.dbf
-rw-r----- 1 oracle oinstall  9760256 Sep  3 22:57 1_20_825119295.dbf
-rw-r----- 1 oracle oinstall  2073088 Sep  3 23:01 1_21_825119295.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep  3 23:25 o1_mf_2_92ct2t1v_.log
[oracle@node1 ~]$ ls -l  /home/oracle/standbylog_archive
total 62844
-rw-r----- 1 oracle oinstall     1024 Sep  3 22:56 1_19_825119295.dbf
-rw-r----- 1 oracle oinstall  9760256 Sep  3 22:57 1_20_825119295.dbf
-rw-r----- 1 oracle oinstall  2073088 Sep  3 23:01 1_21_825119295.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep  3 23:25 o1_mf_2_92ct2t1v_.log
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 4 00:22:42 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/standbylog_archive
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> alter system set log_archive_dest_3='location=/home/oracle/standbylog_archive valid_for=(standby_logfile,standby_role) db_unique_name=orcl';

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> ho tnsping to_157;

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-SEP-2013 00:33:46

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.157)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)

SQL> create table scott.yun as select * from dba_tables;

Table created.

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

SQL> select sequence#,applied,archived from v$archived_log;

 SEQUENCE# APPLIED   ARC
---------- --------- ---
         7 YES       YES
         8 YES       YES
         9 YES       YES
        10 YES       YES
        10 YES       YES
        11 YES       YES
        11 YES       YES
        12 YES       YES
        12 YES       YES
        13 YES       YES
        13 YES       YES

 SEQUENCE# APPLIED   ARC
---------- --------- ---
        14 YES       YES
        14 YES       YES
        15 YES       YES
        15 YES       YES
        16 YES       YES
        16 YES       YES
        17 YES       YES
        17 YES       YES
        18 YES       YES
        18 YES       YES
        19 YES       YES

 SEQUENCE# APPLIED   ARC
---------- --------- ---
        19 YES       YES
        20 NO        YES
        20 YES       YES
        21 NO        YES
        21 YES       YES
         1 NO        YES
         2 NO        YES
         3 NO        YES
         3 YES       YES
         4 NO        YES
         4 YES       YES

 SEQUENCE# APPLIED   ARC
---------- --------- ---
         5 NO        YES
         5 YES       YES
         6 NO        YES
         6 YES       YES
         7 NO        YES
         7 NO        YES

39 rows selected.

SQL> ho ls -l /home/oracle/standbylog_archive;
total 62844
-rw-r----- 1 oracle oinstall     1024 Sep  3 22:56 1_19_825119295.dbf
-rw-r----- 1 oracle oinstall  9760256 Sep  3 22:57 1_20_825119295.dbf
-rw-r----- 1 oracle oinstall  2073088 Sep  3 23:01 1_21_825119295.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep  3 23:25 o1_mf_2_92ct2t1v_.log

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> alter database commit to switchover to physical standby; 

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount;
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.
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
ORCL      PHYSICAL STANDBY

SQL> alter system set standby_file_management=auto;

System altered.

SQL>  alter system set log_archive_dest_state_2=enable;

System altered.
--一般备库下的状态为NOT ALLOWED是不能切换的,因为一山不容二虎,第一先把主库降下来后,才能把主库提上去,提之前,新备库得进恢复模式,新主库得DEST_STATE_2再次重置enable
SQL> select SWITCHOVER_STATUS from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

SQL> ho tnsping to_157

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-SEP-2013 00:46:36

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.157)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

SQL> select SWITCHOVER_STATUS from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED
--在恢复模式下不能开库,得取消恢复状态
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress

SQL>  alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select count(*) from scott.cwm;

  COUNT(*)
----------
      2730

SQL>  ho tnsping to_157

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-SEP-2013 00:51:28

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.157)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)

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;

System altered.

SQL>  alter system set standby_file_management=auto;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.
--由于第二轮切换时的新主库还未处于OPEN状态下面
SQL> create table scott.chenjian as select * from dba_objects;
create table scott.chenjian as select * from dba_objects
*
ERROR at line 1:
ORA-01109: database not open

SQL> alter database open;

Database altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> create table scott.chenjian as select * from dba_objects;

Table created.

 

SQL> show parameter date

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fixed_date                           string
nls_date_format                      string
nls_date_language                    string

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL>  select sysdate from dual;

SYSDATE
-------------------
2013-09-04 01:05:55

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$ exit
logout
[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

 

[root@node1 ~]# su - oracle
[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
===========================备库=192.168.56.157==========================================================

    GROUP# DBID                                        THREAD#
---------- ---------------------------------------- ----------
         4 1352900027                                        1
         5 1352900027                                        1
         6 1352900027                                        1
         7 UNASSIGNED                                        0

SQL> ho ls /home/oracle/standbylog_archive
1_14_825119295.dbf  1_15_825119295.dbf  1_16_825119295.dbf  1_17_825119295.dbf  1_18_825119295.dbf

SQL> ho ls /home/oracle/ -l
total 2297868
drwxr-xr-x 8 oracle oinstall       4096 Aug 21  2009 database
-rw-r--r-- 1 root   root     1239269270 Dec  1  2012 linux.x64_11gR2_database_1of2.zip
-rw-r--r-- 1 root   root     1111416131 Dec  1  2012 linux.x64_11gR2_database_2of2.zip
drwxrwxr-x 2 oracle oinstall       4096 Sep  3 23:01 log_archive
drwxr-xr-x 3 oracle oinstall       4096 Sep  1 10:10 oradiag_oracle
drwxr-xr-x 2 oracle oinstall       4096 Sep  3 22:09 orcl_stdlog
-rw-r--r-- 1 oracle oinstall        845 Sep  3 21:20 s.ora
drwxr-xr-x 2 oracle oinstall       4096 Sep  3 22:32 standbylog_archive

SQL> ho ls -l  /home/oracle/standbylog_archive
total 2660
-rw-r----- 1 oracle oinstall  858624 Sep  3 22:14 1_14_825119295.dbf
-rw-r----- 1 oracle oinstall 1043968 Sep  3 22:18 1_15_825119295.dbf
-rw-r----- 1 oracle oinstall  786432 Sep  3 22:32 1_16_825119295.dbf
-rw-r----- 1 oracle oinstall    1536 Sep  3 22:32 1_17_825119295.dbf
-rw-r----- 1 oracle oinstall   13312 Sep  3 22:32 1_18_825119295.dbf

SQL> alter system set standby_file_management=auto
  2  ;

System altered.

SQL> 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';

System altered.

SQL> alter system set log_archive_dest_2='service=to_147  lgwr affirm sync valid_for=(online_logfile,primary_role) db_unique_name=orcl';

System altered.

SQL> ho mkdir -pv /home/oracle/standbylog_archive;

SQL> alter system set log_archive_dest_3='location=/home/oracle/standbylog_archive valid_for=(standby_logfile,standby_role) db_unique_name=orcl02';

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter database recover managed standby database using current logfile disconnect  from session
  2  ;

Database altered.

 

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  931143680 bytes
Fixed Size                  2218872 bytes
Variable Size             545260680 bytes
Database Buffers          377487360 bytes
Redo Buffers                6176768 bytes
Database mounted.
raDatabase opened.
SQL> show parameter archive
SP2-0734: unknown command beginning "rashow par..." - rest of line ignored.
SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string      location=/home/oracle/log_arch
                                                 ive valid_for=(all_logfiles,al
                                                 l_roles) db_unique_name=orcl
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      service=to_157 valid_for=(onli
                                                 ne_logfile,primary_role) db_un
                                                 ique_name=orcl02
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_11            string      enable
log_archive_dest_state_12            string      enable
log_archive_dest_state_13            string      enable
log_archive_dest_state_14            string      enable
log_archive_dest_state_15            string      enable
log_archive_dest_state_16            string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_17            string      enable
log_archive_dest_state_18            string      enable
log_archive_dest_state_19            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_30            string      enable
log_archive_dest_state_31            string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9             string      enable
SQL> select group#,dbid,thread#,sequence# from  v$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#
---------- ---------------------------------------- ---------- ----------
         4 1352900027                                        1          4
         5 1352900027                                        1          3
         6 1352900027                                        1          5
         7 UNASSIGNED                                        0          0

SQL> 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';

System altered.

SQL> alter system set log_archive_dest_2='service=to_147  lgwr affirm sync valid_for=(online_logfile,primary_role) db_unique_name=orcl';

System altered.

SQL> alter system set log_archive_dest_3='location=/home/oracle/standbylog_archive valid_for=(standby_logfile,standby_role) db_unique_name=orcl02';

System altered.

 

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL>  ho tnsping to_147

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-SEP-2013 00:19:02

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.147)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (60 msec)

SQL> select count(*) from scott.yun;

  COUNT(*)
----------
      2728

SQL>
select protection_mode from v$database;SQL>

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

SQL> select sequence#,applied,archived from v$archived_log;

 SEQUENCE# APPLIED   ARC
---------- --------- ---
         2 YES       YES
         3 YES       YES
         4 YES       YES
         5 YES       YES
         6 YES       YES
         7 NO        YES
         7 IN-MEMORY YES

7 rows selected.

SQL> ho ls -l /home/oracle/standbylog_archive;
total 2696
-rw-r----- 1 oracle oinstall  858624 Sep  3 22:14 1_14_825119295.dbf
-rw-r----- 1 oracle oinstall 1043968 Sep  3 22:18 1_15_825119295.dbf
-rw-r----- 1 oracle oinstall  786432 Sep  3 22:32 1_16_825119295.dbf
-rw-r----- 1 oracle oinstall    1536 Sep  3 22:32 1_17_825119295.dbf
-rw-r----- 1 oracle oinstall   13312 Sep  3 22:32 1_18_825119295.dbf
-rw-r----- 1 oracle oinstall   34816 Sep  4 00:16 1_7_825206525.dbf

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

SQL> /                      

SWITCHOVER_STATUS
--------------------
TO PRIMARY
--备库切成新主库后,记得一定要先一致性关闭数据,直接启动数据库之前,新备库一定到恢复模式下,然后才能在新主库上面执行
--alter system set log_archive_dest_state_2=enable;

--切到主库,如果有SESSION,就在后面加上 with session shutdown;
SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  931143680 bytes
Fixed Size                  2218872 bytes
Variable Size             545260680 bytes
Database Buffers          377487360 bytes
Redo Buffers                6176768 bytes
Database mounted.
Database opened.
SQL> select name ,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
ORCL      PRIMARY

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.
--新主库注意是在157上面的旧备库
SQL> select SWITCHOVER_STATUS from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> ho tnsping to_147

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-SEP-2013 00:31:30

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.147)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)

SQL>  alter system set log_archive_dest_state_2=enable;

System altered.

SQL> create table scott.cl as select * from dba_objects;

Table created.

SQL>  alter system set log_archive_dest_state_2=enable;

System altered.
--新主库创建一张表,验证是否无缝同步到备库里面去
SQL> create table scott.cwm as select * from dba_tables;

Table created.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY
--在正常切换之前一定要记得查询监听,这是在计划下切换数据库
SQL> ho tnsping to_157

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-SEP-2013 00:36:22

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.157)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)

SQL>  ho tnsping to_147

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-SEP-2013 00:36:38

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.147)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY
--原备库成新主加,现在准备切换为物理库
SQL> alter database commit to switchover to physical standby;             

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  931143680 bytes
Fixed Size                  2218872 bytes
Variable Size             545260680 bytes
Database Buffers          377487360 bytes
Redo Buffers                6176768 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

 

 

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> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL>  alter database recover managed standby database cancel; --退出恢复模式才能开库,因为以前的都只能在MOUNT状态下,现在可以READ ONLY

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select count(*) from scott.chenjian;

  COUNT(*)
----------
     71904

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL>  select sysdate from dual;

SYSDATE
-------------------
2013-09-04 00:51:17