Oracle11g 搭建单实例DataGuard (转载)

环境:主备库都为单实例并且数据库SID相同

OS:red hat 6.5

Oracle:11.2.0.4.3

主库操作

 
 

1. 开启归档模式

 

创建归档目录

[root@enn ~]# mkdir -p /u01/archivelog

[root@enn ~]# chown -R oracle:oinstall /u01/archivelog

[root@enn ~]# chmod 777 /u01/archivelog

数据库到mount状态开启归档模式

[root@enn ~]# su - oracle

[oracle@enn ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 20 23:46:46 2014

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size            2257840 bytes

Variable Size           541068368 bytes

Database Buffers    289406976 bytes

Redo Buffers          2371584 bytes

Database mounted.

设置主库归档目录

SQL> alter system set log_archive_dest=‘/u01/archivelog‘;

开启归档模式

SQL> alter database archivelog;

Database altered.

查看归档设置

SQL> archive log list;

Database log mode        Archive Mode

Automatic archival           Enabled

Archive destination          /u01/archivelog

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence         4

 

2. 启动force_logging模式

 

查看是否force_logging模式

SQL> select log_mode,force_logging from v$database;

LOG_MODE     FOR

------------ ---

ARCHIVELOG   NO

开启force_logging模式

SQL> alter database force logging;

Database altered.

 

3.创建备库日志文件路径

 

查看数据库的日志组个数与大小,因为我们创建 standby 日志组的个数是原日志

组个数+1 再与 thread 的积((2+1)*3),size 不能小于原日志文件的大小。

SQL> select group#,thread#,bytes/1024/1024 M ,STATUS from v$log;

    GROUP#    THREAD#         M STATUS

---------- ---------- ---------- ----------------

     1       1          50 CURRENT

     3       1          50 INACTIVE

     2       1          50 INACTIVE

 

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ENN/redo03.log

/u01/app/oracle/oradata/ENN/redo02.log

/u01/app/oracle/oradata/ENN/redo01.log

 

创建备库日志组路径

SQL> alter database add standby logfile thread 1 group 4 (‘/u01/app/oracle/oradata/ENN/redo04.log‘) size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5 (‘/u01/app/oracle/oradata/ENN/redo05.log‘) size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6 (‘/u01/app/oracle/oradata/ENN/redo06.log‘) size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7 (‘/u01/app/oracle/oradata/ENN/redo07.log‘) size 50M;

Database altered.

 

查看状态

 SQL> select group#,status,type,member from v$logfile;

   GROUP# STATUS  TYPE    MEMBER

---------- ------- ------- ----------------------------------------

     3      ONLINE  /u01/app/oracle/oradata/ENN/redo03.log

     2      ONLINE  /u01/app/oracle/oradata/ENN/redo02.log

     1      ONLINE  /u01/app/oracle/oradata/ENN/redo01.log

     4      STANDBY /u01/app/oracle/oradata/ENN/redo04.log

     5      STANDBY /u01/app/oracle/oradata/ENN/redo05.log

     6      STANDBY /u01/app/oracle/oradata/ENN/redo06.log

     7      STANDBY /u01/app/oracle/oradata/ENN/redo07.log

 

4.创建监听

 

执行netca创建监听器

[oracle@enn oracle]$ netca

 

 

修改tnsname文件

[oracle@enn oracle]$ cd $ORACLE_HOME/network/admin

[oracle@enn admin]$ vim tnsname.ora

ENN =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = enn)

    )

  )

ENN_DG =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = enn_dg)

    )

  )

 

tnsname.ora复制到备库中

[oracle@enn admin]$ scp tnsname.ora oracle@192.168.80.16:$ORACLE_HOME/network/admin/tnsname.ora

oracle@192.168.80.16‘s password:

tnsname.ora                                                                                                                100%  361     0.4KB/s   00:00  

 

注:可以使用图形配置tnsname

[oracle@enn admin]$ netmgr

 

附:如果备库tnsping不通, 关闭防火墙

[oracle@enn_dg ~]$ tnsping enn

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-JUL-2014 09:26:09

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.15)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ENN)))

TNS-12543: TNS:destination host unreachable

[root@enn ~]# chkconfig iptables off

[root@enn ~]# service iptables stop

 

5. 设置主库和备库归档路径

设置主库归档路径

SQL> alter system set log_archive_dest=‘‘;

System altered.

SQL> alter system set log_archive_dest_1=‘LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ENN‘;

System altered.

设置备库归档路径

SQL> alter system set log_archive_dest_2=‘SERVICE=enn_dg  async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ENN‘;    

System altered.

配置归档最大进程数

SQL> show parameter log_archive_max

NAME                   TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_max_processes      integer  4

SQL> alter system set log_archive_max_processes=30;

System altered.

 

6.  配置参数文件

 

生成参数文件

SQL> create pfile =‘/u01/app/oracle/initENN.ora‘ from spfile;

File created.

修改参数文件

[oracle@enn oracle]$ vim initENN.ora

DB_UNIQUE_NAME=ENN

#LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(ENN,ENN_DG)‘

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=ENN_DG

FAL_CLIENT=ENN

STANDBY_FILE_MANAGEMENT=AUTO

 

7. 配置密码文件

 

备份standby库参数文件和密码文件

[oracle@enn pfile]$ cd $ORACLE_HOME/dbs

[oracle@enn dbs]$ cp orapwENN orapwENN.back

[oracle@enn dbs]$ cp spfileENN.ora spfileENN.ora.bak

将参数文件和密码文件传到备库

[oracle@enn oracle]$ scp initENN.ora oracle@192.168.80.16:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora

[oracle@enn oracle]$ scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENN  oracle@192.168.80.16:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENN

 

备库操作

 
 

关闭防火墙

[root@enn_dg ~]# chkconfig iptables off

[root@enn_dg ~]# service iptables stop

图形方式创建监听器,同主库操作相同

[oracle@enn ~]$ netca

注:可以使用图形方式配置tnsname.ora

[oracle@enn ~]$ netmgr

启动备库到nomount

SQL> startup nomount pfile=‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora‘;

 

Duplicate复制主库到备库

 
 

用rman连接主库和备库

[oracle@enn ~]$ rman target sys/oracle@ENN auxiliary sys/oracle@ENN_DG

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 21 11:02:39 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ENN (DBID=4141660501)

connected to auxiliary database: ENN (not mounted)

执行同步复制备库操作

RMAN> duplicate target database for standby from active database spfile set db_unique_name ‘ENN‘;

出现错误:

sql statement: alter database mount standby database

RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 07/21/2014 11:15:58

RMAN-05501: aborting duplication of target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/users01.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/undotbs01.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/sysaux01.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/system01.dbf conflicts with a file used by the target database

 

附:a、在异机克隆时,如果auxiliary DB使用了与target DB相同的磁盘配置,目录结构以及文件名时,必须指定NOFILENAMECHECK。NOFILENAMECHECK可以阻止检查target DB的数据文件及联机日志文件是否处于正常使用的状态。而auxiliary DB与target DB的磁盘配置,目录结构以及文件名任一不同时,应避免使用NOFILENAMECHECK。

b、对于没有连接到target DB或catalog的情形,应使用BACKUP LOCATION ‘<bak_dir>‘指定备份文件所在的位置。

继续执行duplicate(注:Duplicate时如果主库与备库文件目录结构与文件名都相同时需要使用nofilenamecheck参数)

RMAN> duplicate target database for standby from active database spfile set db_unique_name ‘ENN‘

nofilenamecheck;

出现问题:

Starting Duplicate Db at 21-JUL-14

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 07/21/2014 11:35:59

RMAN-05501: aborting duplication of target database

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

MosL(备库必需用主库传过来的pfile启动到nomount状态,才能执行duplicate)

CAUSE:
A DUPLICATE was attempted when the auxiliary database was started with a server parameter file and the SPFILE sub-clause was specified in Duplicate syntax.
RMAN cannot restore the server parameter file if the auxiliary database is already started with a server parameter file.

SOLUTION:
Start the auxiliary database with a client parameter file(pfile) or Do not specify SPFILE sub-clause and retry.

 

启动备库到nomount是使用pfile

SQL> startup nomount pfile=‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora‘;

重新同步复制

RMAN> duplicate target database for standby from active database spfile set db_unique_name ‘ENN‘ nofilenamecheck;

报错但正常完成复制L

ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 1 thread 1: ‘/u01/app/oracle/oradata/ENN/redo01.log‘

RMAN-05535: WARNING: All redo log files were not defined properly.

ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 2 thread 1: ‘/u01/app/oracle/oradata/ENN/redo02.log‘

RMAN-05535: WARNING: All redo log files were not defined properly.

ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 3 thread 1: ‘/u01/app/oracle/oradata/ENN/redo03.log‘

RMAN-05535: WARNING: All redo log files were not defined properly.

ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 4 thread 1: ‘/u01/app/oracle/oradata/ENN/redo04.log‘

RMAN-05535: WARNING: All redo log files were not defined properly.

ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 5 thread 1: ‘/u01/app/oracle/oradata/ENN/redo05.log‘

RMAN-05535: WARNING: All redo log files were not defined properly.

ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 6 thread 1: ‘/u01/app/oracle/oradata/ENN/redo06.log‘

RMAN-05535: WARNING: All redo log files were not defined properly.

ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 7 thread 1: ‘/u01/app/oracle/oradata/ENN/redo07.log‘

RMAN-05535: WARNING: All redo log files were not defined properly.

Finished Duplicate Db at 21-JUL-14

 

备库日志应用

 
 

1. 备库开启日志应用

 

开启备库日志应用

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

Database altered.

 

2. 验证备库日志应用

 

验证备库接收日志是否应用

SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;

 SEQUENCE# FIRST_TIME        NEXT_TIME     APPLIED

---------- ------------------- ------------------- ---------

     8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES

     9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES

主库切换日志

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

备库查看日志

select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;

SEQUENCE# FIRST_TIME         NEXT_TIME     APPLIED

---------- ------------------- ------------------- ---------

     8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES

     9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES

    10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES

    11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES

    12 2014-07-21 12:23:23 2014-07-21 12:23:27 YES

 

备库数据库开启read only

SQL> alter database open read only;

alter database open read only

*

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 system switch logfile;

System altered.

SQL> /

System altered.

备库已接收但未应用

SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;

 SEQUENCE# FIRST_TIME        NEXT_TIME     APPLIED

---------- ------------------- ------------------- ---------

     8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES

     9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES

    10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES

    11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES

    12 2014-07-21 12:23:23 2014-07-21 12:23:27 YES

    13 2014-07-21 12:23:27 2014-07-21 12:42:17 NO

    14 2014-07-21 12:42:17 2014-07-21 12:42:19 NO

备库open到read only模式

SQL> alter database open read only;

Database altered.

备库开启日志应用

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

Database altered.

备库已经应用主库日志

SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;

 SEQUENCE# FIRST_TIME        NEXT_TIME     APPLIED

---------- ------------------- ------------------- ---------

     8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES

     9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES

    10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES

    11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES

    12 2014-07-21 12:23:23 2014-07-21 12:23:27 YES

    13 2014-07-21 12:23:27 2014-07-21 12:42:17 YES

    14 2014-07-21 12:42:17 2014-07-21 12:42:19 YES

 

3. 验证数据库操作

 

在主库创建用户

SQL> create user test identified by test;

User created.

在备库查看

SQL> select username from dba_users where username=‘TEST‘;

USERNAME

------------------------------

TEST

 

主备库切换操作验证

 
 

1. 主库执行切换

 

主库执行切换命令

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

Database altered.

重启主库角色变为备库

SQL> startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size            2257840 bytes

Variable Size           679480400 bytes

Database Buffers    150994944 bytes

Redo Buffers          2371584 bytes

Database mounted.

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY

 

2. 备库切换成主库

 

查看备库状态

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

将备库切换成主库

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

Database altered.

查看备库角色

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PRIMARY

查看备库状态

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

Open备库(注:现在已经是主库)

SQL> alter database open;

Database altered.

 

将原主库切换成备库

SQL> alter database open;

Database altered.

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

Database altered.


------------end--------------
        
            DBA_建瑾
            2014.8.5

Oracle11g 搭建单实例DataGuard (转载),布布扣,bubuko.com

Oracle11g 搭建单实例DataGuard (转载)

原文:http://www.cnblogs.com/future2012lg/p/3897389.html

上一篇:Oracle Grid 11.2.0.4 安装是出现“[INS-41112] Specified network interface doesnt maintain connectivity across cluster”错误


下一篇:Qt通过QNetworkAccessManager实现Http网络通信