DG实施文档及遇到问题处理方法

一：primary服务器

1：主库上开启Forced Logging

查看是否开始force loggin

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FORCE_LOG

---------

NO

SQL> alter database force logging;

Database altered.

2：在备库上创建密码文件，这里直接从主库复制到备库

[oracle@wilson ~]$ scp $ORACLE_HOME/dbs/orapwwilson 192.168.227.30:/u01/app/oracle/product/10.2.0/db_1/dbs

oracle@192.168.227.30's password:

orapwwilson 100% 1536 1.5KB/s 00:00

3：主库上配置Standby Redo Log

1.原则

1).standby redo log的文件大小与primary 数据库online redo log 文件大小相同

2).standby redo log日志文件组的个数依照下面的原则进行计算

Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数

例如在我的环境中，只有一个节点，这个节点有三组redo，所以

Standby redo log组数公式>=(3+1)*1 == 4

所以需要创建4组Standby redo log

3).每一日志组为了安全起见，可以包含多个成员文件

SQL> select member from v$logfile;

MEMBER

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

/oradata/orcldb/redo03.log

/oradata/orcldb/redo02.log

/oradata/orcldb/redo01.log

SQL> !du -sh /oradata/orcldb/redo01.log

51M /oradata/orcldb/redo01.log

SQL> alter database add standby logfile group 4

'/oradata/standby/standby04.log' size 50M;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;删除standby logfile group

alter database add standby logfile group 4 '/oradata/standby/standby04.log' size 51M;

alter database add standby logfile group 5 '/oradata/standby/standby05.log' size 51M;

alter database add standby logfile group 6 '/oradata/standby/standby06.log' size 51M;

alter database add standby logfile group 7 '/oradata/standby/standby07.log' size 51M;

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS

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

4 0 0 YES UNASSIGNED

5 0 0 YES UNASSIGNED

6 0 0 YES UNASSIGNED

4.修改主库的初始化参数

SQL> show parameter db_name;

NAME TYPE VALUE

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

db_name string orcldb

--alter system set db_unique_name=orcldb scope=spfile;

startup force

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcldb,standby)';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcldb' scope=spfile;

System altered.

SQL> alter system set log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=spfile;

System altered.

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

ALTER SYSTEM SET log_archive_dest_state_2='DEFER'SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE'SCOPE=BOTH;

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

System altered.

#SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/wilson','/u01/app/oracle/wilson','/u01/app/oracle/wilson/standby','/u01/app/oracle/wilson/standby' scope=spfile;

System altered.

alter system set LOG_FILE_NAME_CONVERT='' scope=spfile;

SQL> show parameter remote_login;

NAME TYPE VALUE

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

remote_login_passwordfile string EXCLUSIVE

SQL> alter system set log_archive_max_processes=30;

System altered.

SQL> alter system set fal_server=standby;

System altered.

SQL> alter system set fal_client=orcldb;

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> !mkdir -p /arch

SQL> shutdown immediate;

SQL> startup

5：

--配置主备库的监听和网络服务名：

主库：

listener.ora:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcldb)

(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcldb)

)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

ORCLDB =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcldb)

)

)

STANDBY =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = STANDBY)

)

)

备库：

listener.ora:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcldb)

(ORACLE_HOME = oracle/app/oracle/product/11.2.0/db_1)

(SID_NAME = standby)

)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

ORCLDB =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcldb)

)

)

STANDBY =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = STANDBY)

)

)

配置主库的tnsnames.ora文件，备库需要同样的操作

[oracle@wilson ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

orcldb =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcldb)

)

)

standby =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = STANDBY)

)

)

测试tns在彼此之间互通，关闭防火墙

service iptables status

service iptables stop

6：在主库上准备备库需要的pfile和数据库备份文件，控制文件等

[oracle@wilson ~]$ mkdir -p /arch/backup

SQL> create pfile='/arch/backup/initorcldb.ora' from spfile;

File created.

[oracle@wilson ~]$ rman target /

RMAN> backup tag 'dg_20170906' format '/arch/backup/dg_%U' incremental level 0 database plus archivelog;

RMAN> backup format '/arch/backup/controlfile_%U' current controlfile for standby;

二：standby服务器

1：设置oracle_sid,配置tnsnames.ora

[oracle@wilson ~]$ export ORACLE_SID=standby

[oracle@wilson ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

orcldb =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcldb)

)

)

standby =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = STANDBY)

)

)

2：在备库上复制主库备份出来的备份文件，并准备相关的目录

[oracle@wilson ~]$ mkdir -p /u01/backup/

[oracle@wilson ~]$ scp -rp 10.10.168.2:/u01/backup/* /u01/backup/

[oracle@wilson ~]$ cp /u01/backup/initwilson.ora $ORACLE_HOME/dbs

注意修改 *.db_unique_name='standby' 千万注意修改正确，和那个ORACLE_SID可以保持一致，还有tnsnames.ora里面的serivice_name保持一致

[oracle@wilson ~]$ grep 'fal_' $ORACLE_HOME/dbs/initstandby.ora

grep 'fal_' $ORACLE_HOME/dbs/initstandby.ora

*.fal_client='STANDBY'

*.fal_server='PRIMARY'

[oracle@wilson ~]$ grep 'log_archive_dest_' $ORACLE_HOME/dbs/initwilson.ora

grep 'log_archive_dest_' $ORACLE_HOME/dbs/initstandby.ora

*.log_archive_dest_1='LOCATION=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=standby'

*.log_archive_dest_2='service=orcldb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcldb'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE

[oracle@wilson ~]$ mkdir -p /u01/arch/wilson

[oracle@wilson ~]$ mkdir -p /u01/app/oracle/admin/wilson/{adump,bdump,cdump,dpdump,udump,pfile}

[oracle@wilson ~]$ mkdir -p /u01/app/oracle/oradata/orastrac

[oracle@wilson ~]$ mkdir -p /u01/app/oracle/fast_recovery_area

alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/wilson','/u01/app/oracle/wilson','/u01/app/oracle/wilson/standby','/u01/app/oracle/wilson/standby' scope=spfile;

/u01/app/oracle/wilson/redo01.log

/u01/app/oracle/wilson/redo02.log

/u01/app/oracle/wilson/redo03.log

/u01/app/oracle/wilson/standby/standby04.log

/u01/app/oracle/wilson/standby/standby05.log

/u01/app/oracle/wilson/standby/standby06.log

/u01/app/oracle/wilson/standby/standby07.log

3：将备库启动到mount状态

[oracle@wilson ~]$ sqlplus /nolog

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

4：使用rman对备库进行恢复

[oracle@wilson ~]$ rman target sys/oracle#123@ORCLDB auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 15 16:08:34 2011

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

connected to target database: orcldb (DBID=4071883054)

connected to auxiliary database: orcldb (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

5: 将备库置于应用redolog模式

[oracle@wilson ~]$ sqlplus /nolog

SQL> conn /as sysdba

Connected.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/arch/wilson

Oldest online log sequence 47

Next log sequence to archive 49

Current log sequence 49

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

Database altered.

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

SEQUENCE# FIRST_TIME NEXT_TIME APP

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

47 2011-09-09:10:56:31 2011-09-09:10:57:51 YES

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/flash_recovery_area/STANDBY/onlinelog/o1_mf_3_773dso2v_.log

/u01/app/oracle/flash_recovery_area/STANDBY/onlinelog/o1_mf_2_773dsmpk_.log

/u01/app/oracle/flash_recovery_area/STANDBY/onlinelog/o1_mf_1_773dsl9v_.log

可以在主库上进行日志切换，加快备库应用日志的速度！

[oracle@wilson ~]$ sqlplus /nolog

SQL> conn /as sysdba

Connected.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/arch/wilson

Oldest online log sequence 47

Next log sequence to archive 49

Current log sequence 49

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/arch/wilson

Oldest online log sequence 48

Next log sequence to archive 50

Current log sequence 50

备库上再次查询：

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

SEQUENCE# FIRST_TIME NEXT_TIME APP

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

47 2011-09-09:10:56:31 2011-09-09:10:57:51 YES

49 2011-09-09:10:57:54 2011-09-09:11:21:25 YES

48 2011-09-09:10:57:51 2011-09-09:10:57:54 YES

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE

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

wilson PHYSICAL STANDBY

三：测试

1.在主库上创建表空间，建表

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE

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

wilson PRIMARY

create tablespace dg_test1 datafile

'/u01/app/oracle/wilson/dg_test1.dbf'

size 10M autoextend off;

Tablespace created.

CREATE USER dgtest1 IDENTIFIED BY dgtest1 DEFAULT TABLESPACE dg_test1;

grant CONNECT,RESOURCE to dgtest1;

SQL> create table dg03 as select * from scott.emp;

Table created

SQL> alter system switch logfile;

System altered.

2.备库应用日志后以只读方式打开查看数据

SQL> alter database recover managed standby database cancel;

Database altered

SQL> alter database open read only;

Database altered.

SQL> select type from dg03 where rownum <= 3;

TYPE

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

PACKAGE

PACKAGE

PACKAGE

SQL> select count(*) from dg03;

COUNT(*)

----------

292428

3：从新将备库置于应用日志模式

[oracle@wilson ~]$ sqlplus /nolog

SQL> conn /as sysdba

Connected.

SQL> shutdown immediate;

SQL> startup nomount；

SQL> alter database mount standby database;

Database altered.

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

Database altered.

FAL[client, MRP0]: Error 12154 connecting to WILSON for fetching gap sequence

因为

SQL> select dest_name,error,status from v$archive_dest;

DEST_NAME ERROR STATUS

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

LOG_ARCHIVE_DEST_1 VALID

LOG_ARCHIVE_DEST_2 ORA-16057: server DISABLED

not in Data Guard

configuration

col DEST_NAME for a30

col ERROR for a60

col STATUS for a20

set line 200

SQL> select dest_name,error,status from v$archive_dest;

DEST_NAME ERROR STATUS

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

LOG_ARCHIVE_DEST_1 VALID

LOG_ARCHIVE_DEST_2 VALID--表示ok