Oracle11g 搭建单实例DataGuard （转载）

2022-09-13 22:13:14

环境：主备库都为单实例并且数据库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

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

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

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‘