11g Physical Standby配置

一,准备
 
DatabaseDB_UNIQUE_NAMEOracle Net Service Name
Primary PROD PROD
Physical standby PRODDG PRODDG
 
二,主库设置
 
1.启用force logging
alter database force logging;
2.确认主库处于归档模式
archive log list;
3.在primary库创建standby数据库控制文件
alter database create standby controlfile as '/dsg/oracle11/back/prordg.ctl';
4.创建primary库客户端初始化参数文件

1).创建主库中的pfile
create pfile from spfile;
2).备份到backup目录用于创建备库的pfile
cp $ORACLE_HOME/dbs/initPROD.ora $ORACLE_HOME/dbs/initPRODDG.ora
3).修改后主库pfile中内容如下:
*.db_domain=''
*.db_name='PROD'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
*.open_cursors=300
*.pga_aggregate_target=3809476608
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_tablespace='UNDOTBS1'


#################################################################
#Parameters for Primary Database.
#################################################################
*.DB_NAME='PROD'
*.DB_UNIQUE_NAME=PROD
*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PRODDG)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/dsg/oracle11/PROD/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD'
*.LOG_ARCHIVE_DEST_2='SERVICE=PRODDG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODDG'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

#################################################################
#Parameters which using for switch over from Primary to Standby.
#################################################################
*.FAL_SERVER=PRODDG
*.FAL_CLIENT=PROD
*.DB_FILE_NAME_CONVERT='/dsg/oracle11/PRODDG','/dsg/oracle11/PROD'
*.LOG_FILE_NAME_CONVERT='/dsg/oracle11/PRODDG','/dsg/oracle11/PROD'
*.STANDBY_FILE_MANAGEMENT=AUTO
 
4)通过pfile创建spfile
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;

File created.
 
4.配置tnsnames.ora文件和listener.ora 文件(由于是一个服务器上的两个数据库,所以standby数据库不需要再配置)
 
如果在不同的服务器上那么两个服务器上都要配置
 
 cat listener.ora 

LISTENER =
(ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.70)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (GLOBAL_DBNAME=db11)
         (SERVICE_NAME = DB11)
         (SID_NAME=db111)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0)
        (PRESPAWN_MAX=20)
        (PRESPAWN_LIST=
          (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
        )
       )
      (SID_DESC=
         (GLOBAL_DBNAME=PROD)
         (SERVICE_NAME = PROD)
         (SID_NAME=PROD)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0)
        (PRESPAWN_MAX=20)
        (PRESPAWN_LIST=
          (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
        )
       )
      (SID_DESC=
         (GLOBAL_DBNAME=PRODDG)
         (SERVICE_NAME = PRODDG)
         (SID_NAME=PRODDG)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0)
        (PRESPAWN_MAX=20)
        (PRESPAWN_LIST=
          (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
        )
       )
)
 
cd $ORACLE_HOME/network/admin
 
PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.70)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
  )

PRODDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.70)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDG)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
 
PROD@dbrac1  /u01/app/oracle/product/11.2.0/network/admin$ tnsping prod

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 24-APR-2014 22:00:52

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.70)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD)))
OK (130 msec)
PROD@dbrac1  /u01/app/oracle/product/11.2.0/network/admin$ tnsping proddg

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 24-APR-2014 22:00:57

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

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


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

 

三,Standby 数据库配置设置
 
 
1.创建密码文件,注意sys的密码和主库的密码一致(说明:这里不要这样创建standby的密码文件) 报错信息见最后
cd $ORACLE_HOME/dbs
orapwd file=orapwPRODDG force=y password=oracle entries=30
推荐直接拷贝primary数据的密码文件:
cd $ORACLE_HOME/dbs
cp  orapwPROD  orapwPROD
2.创建standby所需的目录(注意11g和10g有所区别)
cd $ORACLE_BASE/admin
mkdir -p PRODDG/adump
mkdir -p PRODDG/dpdump
创建Standby的数据文件及日志文件,控制文件所在目录
mkdir  /dsg/oracle11/PRODDG
3,拷贝主库生成的standby控制文件和主库的数据文件,日志文件
cp /dsg/oracle11/back/prordg.ctl  /dsg/oracle11/PRODDG/
cp  prordg.ctl control01.ctl 
cp control01.ctl  control02.ctl 
4,修改standby参数文件
 
1)修改参数如下:
 
cat initPRODDG.ora
PRODDG.__db_cache_size=297795584
PRODDG.__java_pool_size=4194304
PRODDG.__large_pool_size=4194304
PRODDG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PRODDG.__pga_aggregate_target=3812622336
PRODDG.__sga_target=536870912
PRODDG.__shared_io_pool_size=0
PRODDG.__shared_pool_size=218103808
PRODDG.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PRODDG/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/dsg/oracle11/PRODDG/control01.ctl','/dsg/oracle11/PRODDG/control02.ctl'
*.db_block_size=32768
*.db_domain=''
*.db_name='PROD'    ##注意和主库的一致
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODDGXDB)'
*.open_cursors=300
*.pga_aggregate_target=3809476608
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_tablespace='UNDOTBS1'

#################################################################
#Parameters for Standby Database.
#################################################################
*.DB_UNIQUE_NAME=PRODDG
*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PRODDG)'
*.DB_FILE_NAME_CONVERT='/dsg/oracle11/PROD','/dsg/oracle11/PRODDG'
*.LOG_FILE_NAME_CONVERT='/dsg/oracle11/PROD','/dsg/oracle11/PRODDG'
*.FAL_SERVER=PROD
*.FAL_CLIENT=PRODDG
*.STANDBY_FILE_MANAGEMENT=AUTO

#################################################################
#Parameters which using for switch over from Standby to Primary.
#################################################################
*.LOG_ARCHIVE_DEST_1='LOCATION=/dsg/oracle11/PRODDG/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDG'
*.LOG_ARCHIVE_DEST_2='SERVICE=PROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
2)生成spfile
PROD@dbrac1  /home/oracle$ export ORACLE_SID=PRODDG
PRODDG@dbrac1  /home/oracle$ sql

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 24 22:21:45 2014

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.
5.启动Standby到mount
SQL> startup mount
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2215064 bytes
Variable Size             226493288 bytes
Database Buffers          297795584 bytes
Redo Buffers                7958528 bytes
Database mounted.
6,启动redo应用
alter database recover managed standby database disconnect from session;
7,查看同步情况
 
连到主库
 
SQL> set line 120
SQL> select instance_name,host_name,version,status from v$instance;

INSTANCE_NAME    HOST_NAME                                                        VERSION           STATUS
---------------- ---------------------------------------------------------------- ----------------- ------------
PROD                            dbrac1                                                           11.2.0.1.0        OPEN
 
SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            96
连接到standby数据库
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            96
 
8,取消日志应用
 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 
四,验证
 
在主库创建一个用户,并且创建一张表
 
SQL> create user hxy identified by hxy;

User created.

SQL> grant dba to hxy;

Grant succeeded.

SQL> conn hxy/hxy
Connected.
SQL> create table t as select * from dba_users;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            98
 
去备库验证 一番
 
连接standby数据库
 
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            98
 
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> conn hxy/hxy
Connected.
 
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE

SQL> select count(*) from t;

  COUNT(*)
----------
        23
OK,没有问题!
 
五,主备切换
 
1,连接到primary数据库,切换操作
 
SQL> conn / as sysdba
Connected.
SQL> select switchover_status from v$database;

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

SQL>  alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>  startup nomount
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2215064 bytes
Variable Size             226493288 bytes
Database Buffers          297795584 bytes
Redo Buffers                7958528 bytes
SQL>  alter database mount standby database;

Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session parallel 2;

Database altered.
 
2,连接到standby数据库
 
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL>  alter database commit to switchover to primary with session shutdown ;

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  534462464 bytes
Fixed Size                  2215064 bytes
Variable Size             226493288 bytes
Database Buffers          297795584 bytes
Redo Buffers                7958528 bytes
Database mounted.
Database opened.

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY
 
Ok,切换成功
 
3,再切换回去
 
注意:在主库没有切换成备库时,standby数据切换状态为
 
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED
重复上面的步骤
 
1)连接当前的主库
 
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> select switchover_status from v$database;

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

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2215064 bytes
Variable Size             226493288 bytes
Database Buffers          297795584 bytes
Redo Buffers                7958528 bytes
SQL> alter database mount standby database;

Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL>  alter database recover managed standby database disconnect from session parallel 2;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE   --备库没有切换
 
2)连接当前的备库
 
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY    ---此时的状态变为TO PRIMARY

SQL> alter database commit to switchover to primary with session shutdown ;

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  534462464 bytes
Fixed Size                  2215064 bytes
Variable Size             226493288 bytes
Database Buffers          297795584 bytes
Redo Buffers                7958528 bytes
Database mounted.
Database opened.
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY
 
此时再查看备库的切换状态
 
连接备库
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED
六,问题总结
 
配置完成后,在主库的alert日志里报下面的错误:
 
Thu Apr 24 23:14:58 2014
Starting background process SMCO
Thu Apr 24 23:14:58 2014
SMCO started with pid=25, OS id=8759
Thu Apr 24 23:15:55 2014
Thread 1 cannot allocate new log, sequence 96
Private strand flush not complete
  Current log# 2 seq# 95 mem# 0: /dsg/oracle11/PROD/redo02.log
Thu Apr 24 23:15:59 2014
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thread 1 advanced to log sequence 96 (LGWR switch)
  Current log# 3 seq# 96 mem# 0: /dsg/oracle11/PROD/redo03.log
Thu Apr 24 23:15:59 2014
Archived Log entry 14 added for thread 1 sequence 95 ID 0xef8b15b dest 1:
 
 
备库报下面的错误
Thu Apr 24 23:14:58 2014
Starting background process SMCO
Thu Apr 24 23:14:58 2014
SMCO started with pid=25, OS id=8759
Thu Apr 24 23:15:55 2014
Thread 1 cannot allocate new log, sequence 96
Private strand flush not complete
  Current log# 2 seq# 95 mem# 0: /dsg/oracle11/PROD/redo02.log
Thu Apr 24 23:15:59 2014
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thread 1 advanced to log sequence 96 (LGWR switch)
  Current log# 3 seq# 96 mem# 0: /dsg/oracle11/PROD/redo03.log
Thu Apr 24 23:15:59 2014
Archived Log entry 14 added for thread 1 sequence 95 ID 0xef8b15b dest 1:
 
日志没有被应用,密码文件创建完全一样
orapwd file=orapwPRODDG force=y password=oracle entries=30
orapwd file=orapwPROD force=y password=oracle entries=30
依然不行,
 
最后创建好,主库的密码文件后,拷贝主库的密码文件到备库,问题解决!!!!很奇怪!!!!
 
2014-04-25 09:50:49 
posted @ 2014-04-25 09:52  hao_xiaoyu  阅读(1078)  评论(0编辑  收藏  举报