Oracle 11g standby主从配置

转载自：http://greatdeer007.blog.163.com/blog/static/16366517420105331447834/

一、主机描述

 dbprimary: 192.168.1.57 主机名称db1
 dbstandby: 192.168.1.58 主机名成db2
 SID: jifenpay

二、配置tns, 使用netca配置

dbprimary的listener.ora (/u01/app/oracle/product/11.1.0/db_1/network/admin目录下)

# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = jifenpay)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (GLOBAL_DBNAME = jifenpay)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
    )
  )

dbstandby的listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = jifenpay)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (GLOBAL_DBNAME = jifenpay)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
    )
  )

dbprimary和dbstandby上的tnsnames.ora是一样的

# Generated by Oracle configuration tools.

DBSTANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = jifenpay)
    )
  )

DBPRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = jifenpay)
    )
  )

 然后在两台机器上分别检查配置是否正确

 oracle>lsnrctl stop
 oracle>lsnrctl start
 oracle>lsnrctl status
 oracle>tnsping dbprimary
 oracle>tnsping dbstandby
 oracle>sqlplus sys/password@dbprimary as sysdba
 oracle>sqlplus sys/password@dbstandby as sysdba

三、在dbprimary上操作

1. oracle>mkdir /u01/app/oracle/oradata/archive
2. cd /u01/app/oracle/product/11.1.0/db_1/dbs
   oracle>orapwd file=orapwjifenpay password=jifenpay entries=4 产生密码文件，一定要拷贝到从库上，就算从库执行同样的命令也不行,生成后用户权限会失去，需要手动重新赋予权限
3. oracle>sqlplus sys/password as sysdba
 sql>create pfile from spfile; 在/u01/app/oracle/product/11.1.0/db_1/dbs下生成initjifenpay.ora
 在initjifenpay.ora文件后面增加内容：
*.DB_UNIQUE_NAME='dbprimary'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbprimary, dbstandby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dbprimary'
*.LOG_ARCHIVE_DEST_2='SERVICE=dbstandby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbstandby'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=5
*.FAL_SERVER='dbstandby'
*.FAL_CLIENT='dbprimary'
*.STANDBY_FILE_MANAGEMENT='AUTO'

4. sql>create spfile from pfile;
 sql>startup mount;
 sql>alter database archivelog;
 sql>alter database open;
 sql>archive log list; 查看是否处于log模式,如果不是执行如下命令
 sql>alter database force logging; 改变为logging模式
 sql>alter database create standby controlfile as '/u01/app/oracle/oradata/standby01.ctl'; 创建dbstandby上的控制文件
 sql>shutdown immediate;
 
5. 把/u01/app/oracle/oradata打包传到dbstandby服务器, 把 /u01/app/oracle/product/11.1.0/db_1/dbs/orapwjifenpay也传过去

四、在dbstandby服务器上操作

1. oracle>sqlplus sys/password as sysdba
   sql>shutdown immediate;
2. oracle>cd /u01/app/oracle
   oracle>rm -rf oradata
   把刚才传过来的包解压
   oracle>cd oradata
   oracle>cp standby01.ctl standby02.ctl
   oracle>cp standby01.ctl standby03.ctl
3. sql>create pfile from spfile
   修改initjifenpay.ora
*.control_files='/u01/app/oracle/oradata/standby01.ctl','/u01/app/oracle/oradata/standby02.ctl','/u01/app/oracle/orada
   增加
*.DB_UNIQUE_NAME='dbstandby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbprimary, dbstandby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dbstandby'
*.LOG_ARCHIVE_DEST_2='SERVICE=dbprimary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbprimary'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=5
*.FAL_SERVER='dbprimary'
*.FAL_CLIENT='dbstandby'
*.STANDBY_FILE_MANAGEMENT='AUTO'
    
4. sql>create spfile from pfile;
   sql>startup nomount;
   sql>alter database mount standby database; //可能提示权限问题，需要修改一些文件的权限
   sql>alter database recover managed standby database disconnect from session;  启动到recover mange模式

五、在dbprimary上操作

sql>startup;

六、检查是否工作正常

1. 查看oradata/archive文件是否传过去了
  2. 在dbprimary上插入一些数据
     (dbprimary)sql>select max(sequence#) from v$archived_log; 查看当前sequence
     (dbstandby)sql>select max(sequence#) from v$archived_log;
     (dbprimary)sql>alter system switch logfile;
     (dbprimary)sql>select max(sequence#) from v$archived_log; 查看当前sequence
     (dbstandby)sql>select max(sequence#) from v$archived_log;
    如果dbstandby的sequence跟着改变，说明已经好了
   3. select dest_name,status,error from v$archive_dest;  察看相应的归档路径的状态是否valid，否则根据error信息进行处理
   4. 可以查看alert日志文件
      /u01/app/oracle/diag/rdbms/dbprimary/jifenpay/alert/log.xml
      /u01/app/oracle/diag/rdbms/dbprimary/jifenpay/trace/alert_jifenpay.log

七、切换从库到read only

在dbstandby操作
   sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   sql>alter database open;
   好像从oracle 11开始standby处于read only模式下也能同步，这个我试过是好用的，只要在primary上执行alter system switch logfile

八、switchover

 1.主库上执行
    sql>alter database commit to switchover to physical standby with session shutdown;
    sql>shutdown immediate;
    sql>startup no mount;
    sql>recover managed standby database disconnect;
  2.从库上执行
    sql>alter database commit to switchover to primary;
    sql>shutdown immediate;
    sql>startup;

 

九、一些其他命令

create user dbra identified by dbra;
grant sysdba to dbra;
select * from V_$PWFILE_USERS; // 查看sysdba权限用户