centos 7.0下配置oracle11g dataguard

一、环境准备

     主库：

      ip地址：192.168.122.203

      操作系统：centos7.0

      oracle根目录：/data/db/oracle

      SID:qyq

      数据文件路径/data/db/oracle/oradata/qyq

      归档文件路径：/data/db/oracle/archive'

     备库：

      ip地址：192.168.122.204

      操作系统：centos7.0

      oracle根目录：/data/app/oracle

      SID:qyq

      数据文件路径/data/app/oracle/oradata/qyq

      归档文件路径：/data/app/oracle/archive'

二、安装步骤

     1、安装主库（含安装软件和建库）

     2、安装备库（仅安装软件，不建库）

     注：安装必须安装oracle企业版本

     3、  配置dataguard

         以下在主库操作

         步骤一：备份初始化文件

          cp   /data/db/oracle/product/11.2.0/db_1/dbs/initqyq.ora   /backup/initqyqdg.ora （如无backup目录请创建）

          步骤二： 修改初始化文件（'/data/db/oracle/product/11.2.0/db_1/dbs/initqyq.ora'） --注意参数前面的空格

         

         *.db_unique_name=pri

        *.log_archive_config='DG_CONFIG=(pri,dg)'

        *.log_archive_dest_1='LOCATION=/data/db/oracle/oradata/qyq/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri'

        *.log_archive_dest_2='SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'

        *.log_archive_dest_state_1='ENABLE'

        *.log_archive_dest_state_2='ENABLE'

        *.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

         *.fal_server='dg1'

         *.fal_client='pri'

         *.db_file_name_convert='oradata/qyqdg','oradata/qyq'

         *.log_file_name_convert='oradata/qyqdg','oradata/qyq'

         *.standby_file_management=AUTO

          步骤三： 修改初始化文件（'/data/db/oracle/product/11.2.0/db_1/dbs/initqyq.ora'） --spfile 文件需要startup才能生效

          //创建文件,数据库不能处于启动状态

                 $sqlplus /nolog

            sql>conn /as sysdba

            sql>create spfile from pfile='initqyq.ora';

       

          步骤四：创建主库日志文件

           alter database add standby logfile thread 1 group 4 ('/data/db/oracle/oradata/qyq/redo04.log') size 50M;

alter database add standby logfile thread 1 group 5 ('/data/db/oracle/oradata/qyq/redo05.log') size 50M;

alter database add standby logfile thread 1 group 6 ('/data/db/oracle/oradata/qyq/redo06.log') size 50M;

alter database add standby logfile thread 1 group 7 ('/data/db/oracle/oradata/qyq/redo07.log') size 50M;

步骤 五：配置监听和tns

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = qyq)

      (GLOBAL_DBNAME = pri)

      (ORACLE_HOME = /data/db/oracle/product/11.2.0/db_1/)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = rgl203)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /data/db/oracle

//配置tns

pri =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.122.203 )(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = pri)

    )

  )

dg =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.122.204)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg)

    )

  )

步骤 六：配置监听和tns（备库操作）

//配置监听（netca配置，然后修改配置文件如下）

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = qyq)

      (GLOBAL_DBNAME = dg)

      (ORACLE_HOME = /data/app/oracle/product/11.2.0/dbhome_1/)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = rgl204)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /data/db/oracle

//配置tns（netca配置，然后修改配置文件如下）

pri =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.122.203 )(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = pri)

    )

  )

dg =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.122.204)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg)

    )

  )

步骤 七：创建目录（备库操作）

mkdir -p $ORACLE_BASE/oradata/qyq

mkdir -p $ORACLE_BASE/admin/qyq

mkdir -p $ORACLE_BASE/admin/qyq/adump

mkdir -p $ORACLE_BASE/admin/qyq/bdump

mkdir -p $ORACLE_BASE/admin/qyq/cdump

mkdir -p $ORACLE_BASE/admin/qyq/dpdump

mkdir -p $ORACLE_BASE/admin/qyq/pfile

mkdir -p $ORACLE_BASE/admin/qyq/udump

mkdir -p $ORACLE_BASE/diag/rdbms

mkdir -p $ORACLE_BASE/diag/tnslsnr

mkdir -p $ORACLE_BASE/flash_recovery_area/QYQ

mkdir -p $ORACLE_BASE/flash_recovery_area/qyq

mkdir -p $ORACLE_BASE/archive

  步骤 八：拷贝文件到备库（主库操作）

 $scp $ORACLE_BASE/oradata/qyq/*.dbf 192.168.122.204:/data/app/oracle/oradata/qyq

 $scp $ORACLE_BASE/oradata/qyq/*.log 192.168.122.204:/data/app/oracle/oradata/qyq

 $scp /data/db/oracle/oradata/qyq/qyqdg01.ctl 192.168.122.204:/data/app/oracle/oradata/qyq

 $scp /data/db/oracle/product/11.2.0/db_1/dbs/initqyq.ora 192.168.122.204:/data/app/oracle/product/11.2.0/dbhome_1/dbs/

  步骤 九：文件处理（备库操作）

$ cd $ORACLE_BASE/oradata/qyq

$ mv qyqdg01.ctl  control01.ctl

$ cp control01.ctl /data/app/oracle/flash_recovery_area/qyq/

$cd /data/app/oracle/flash_recovery_area/qyq/

$ mv control01.ctl  control02.ctl

步骤 十： 修改备库参数文件（initqyq.ora）（备库操作）

*.audit_file_dest='/data/app/oracle/admin/qyq/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/data/app/oracle/oradata/qyq/control01.ctl','/data/app/oracle/flash_recovery_area/qyq/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='qyq'

*.db_recovery_file_dest='/data/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4070572032

*.diagnostic_dest='/data/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=qyqXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=1547698176

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.db_unique_name=dg

*.log_archive_config='DG_CONFIG=(pri,dg)'

*.log_archive_dest_1='LOCATION=/data/app/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg'

*.log_archive_dest_2='SERVICE=pri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

*.fal_server='pri'

*.fal_client='dg'

#*.db_file_name_convert='oradata/qyqdg','oradata/qyq'

#*.log_file_name_convert='oradata/qyqdg','oradata/qyq'

*.standby_file_management=AUTO

  步骤 十一：建立密码文件、复制密码文件（主库操作）

如果主库没有密码文件则建立密码文件，从而可以 OS验证的方式登陆（文件位置$ORACLE_HOME/dbs/ ）

          $ orapwd file=$ORACLE_HOME/dbs/orapwora11g password=oracle entries=5

复制主库的密码文件到备库的相应位置( 如果主备库不能同步很可能就是密码文件不一样)

先查看备份库是否有密码文件，有就先删除，然后再复制。

scp /data/db/oracle/product/11.2.0/db_1/dbs/orapwqyq 192.168.122.204:/data/app/oracle/product/11.2.0/dbhome_1/dbs/

步骤 十二：备库增加日志文件（备库操作）

aler database add standby logfile thread 1 group 4 ('/data/app/oracle/oradata/qyq/redo04.log') size 50M;

alter database add standby logfile thread 1 group 5 ('/data/app/oracle/oradata/qyq/redo05.log') size 50M;

alter database add standby logfile thread 1 group 6 ('/data/app/oracle/oradata/qyq/redo06.log') size 50M;

alter database add standby logfile thread 1 group 7 ('/data/app/oracle/oradata/qyq/redo07.log') size 50M;

 4、 启动/关闭数据库

       (1).启从、主库的监听Listener

         从库dg：

$lsnrctl start

         主库pri：

$lsnrctl start

      (2).启动备库数据库，执行如下：

$sqlplus /nolog

SQL>conn /as sysdba

SQL> startup nomount

SQL> alter database mount standby database;               #让备库处于standby

SQL>alter databse open read only;

SQL> alter database recover managed standby database using current logfile disconnect from session;                                            #开始实时同步

       (3).启动主库

$sqlplus /nolog

SQL>conn /as sysdba

SQL> startup

关闭的时候正好相反，先关闭主库，然后关闭从库。

(1).  关闭主库

$su – oracle

SQL>sqlplus /nolog

SQL>conn /as sysdba

SQL>shutdown immediate;

(2). 关闭从库

SQL>sqlplus /nolog

SQL>conn /as sysdba

SQL>alter database recover managed standby database cancel;     #停止同步

SQL>shutdown immediate

 5、 问题处理

        1、备库可以启动，但是无法open，报ora-01110

            select file#,status,bytes/1024/1024,name from v$datafile  --查看数据文件还是指向主库的目录。需要手工修改数据文件指向备库。

          

          修改步骤：

alter system set standby_file_management=MANUAL;

alter database rename file '/data/db/oracle/oradata/qyq/system01.dbf' to '/data/app/oracle/oradata/qyq/system01.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/sysaux01.dbf' to '/data/app/oracle/oradata/qyq/sysaux01.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/undotbs01.dbf' to '/data/app/oracle/oradata/qyq/undotbs01.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/users01.dbf' to '/data/app/oracle/oradata/qyq/users01.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/data_qyq_01.dbf' to '/data/app/oracle/oradata/qyq/data_qyq_01.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/data_qyq_02.dbf' to '/data/app/oracle/oradata/qyq/data_qyq_02.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/data_qyq_03.dbf' to '/data/app/oracle/oradata/qyq/data_qyq_03.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/data_qyq_04.dbf' to '/data/app/oracle/oradata/qyq/data_qyq_04.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/data_qyq_05.dbf' to '/data/app/oracle/oradata/qyq/data_qyq_05.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/data_qyq_06.dbf' to '/data/app/oracle/oradata/qyq/data_qyq_06.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/data_qyq_07.dbf' to '/data/app/oracle/oradata/qyq/data_qyq_07.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/data_qyq_08.dbf' to '/data/app/oracle/oradata/qyq/data_qyq_08.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/data_qyq_09.dbf' to '/data/app/oracle/oradata/qyq/data_qyq_09.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/temp01.dbf' to '/data/app/oracle/oradata/qyq/temp01.dbf';

alter database rename file '/data/db/oracle/oradata/qyq/redo01.log' to '/data/app/oracle/oradata/qyq/redo01.log';

alter database rename file '/data/db/oracle/oradata/qyq/redo02.log' to '/data/app/oracle/oradata/qyq/redo02.log';

alter database rename file '/data/db/oracle/oradata/qyq/redo03.log' to '/data/app/oracle/oradata/qyq/redo03.log';

shutdown immediate  --先关闭数据库

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; --开启同步

alter system set standby_file_management=auto; --恢复文件自动管理

6、 验证是否安装成功

主库操作：

create table test (id number);

desc test;

备库操作：

select * from test;

如果没有则执行：

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

主库操作：

insert into test values(123);

commit;

select * from test;

备库操作：

select * from test;