使用duplicate搭建oracle 11.2.0.4.0数据库active dataguard (ADG)

来源：互联网发布：如何找淘宝达人推广编辑：程序博客网时间：2024/06/10 08:48

1：配置情况（主库需要建立实例，从库只需要安装软件）

主库

Centos6.7

Oracle 11.2.0.4.0

IP:192.168.1.155 sid:orcl

备库

Centos6.7

Oracle 11.2.0.4.0软件

IP：192.168.1.156

2：关闭防火墙(两边都需要操作)

lsnrctl iptables stop

chkconfig iptables off

3:配置监听

主库

修改listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = dg1)

(ORACLE_HOME = /u01/oracle/11g)

(SID_NAME = orcl)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

修改tnsnames.ora

dg1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg1)

)

dg2 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg2)

)

for_db =

(DESCRIPTION =

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

)

备库

修改listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = dg2)

(ORACLE_HOME = /u01/oracle/11g)

(SID_NAME = orcl)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

修改tnsnames.ora

dg1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg1)

)

dg2 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg2)

)

for_db =

(DESCRIPTION =

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

Note:配置完，tnsping dg1，tnsping dg2出现下面信息，表示互通。

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.155)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg1)))
OK (20 msec)

4：配置传输目录和添加日志组（主库）

mkdir -p /u01/archivelog

mkdir -p /u01/standbylog

4.2 修改参数

SQL> alter database force logging;

Database altered.

SQL> alter system set db_unique_name='dg1' scope=spfile;

System altered.

SQL> alter system set log_archive_config='DG_CONFIG=(dg1,dg2)';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/archivelog valid_for=(all_logfiles,primary_role) db_unique_name=dg1' scope=spfile;

SQL> alter system set log_archive_dest_2='SERVICE=dg2 lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=dg2';

SQL> alter system set log_archive_dest_3='LOCATION=/u01/standbylog valid_for=(standby_logfile,standby_role) db_unique_name=dg1' scope=spfile;

System altered.

SQL> alter system set fal_client='dg1';

System altered.

SQL> alter system set fal_server='dg2';

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

4.3 添加从库日志组

SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/orcl/standby04.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 5 '/u01/oracle/oradata/orcl/standby05.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 6 '/u01/oracle/oradata/orcl/standby06.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 7 '/u01/oracle/oradata/orcl/standby07.log' size 50M;

4.4 重启数据库，创建pfile

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 417546240 bytes

Fixed Size 2228944 bytes

Variable Size 285216048 bytes

Database Buffers 121634816 bytes

Redo Buffers 8466432 bytes

Database mounted.

Database opened.

SQL> alter system set local_listener='for_db';

System altered.

SQL> create pfile='/u01/oracle/11g/dbs/initdg.ora' from spfile;

File created.

5：将密码文件和pfile文件传输到备库

5.1 修改pfile文件

orcl.__db_cache_size=327155712

orcl.__java_pool_size=4194304

orcl.__large_pool_size=8388608

orcl.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=318767104

orcl.__sga_target=469762048

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=121634816

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/oracle/oradata/orcl/control01.ctl','/u01/oracle/fast_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='/u01/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.db_unique_name='dg2'

*.diagnostic_dest='/u01/oracle'

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

*.fal_client='dg2'

*.fal_server='dg1'

*.log_archive_config='DG_CONFIG=(dg1,dg2)'

*.log_archive_dest_1='LOCATION=/u01/archivelog valid_for=(all_logfiles,primary_role) db_unique_name=dg2'

*.log_archive_dest_2='SERVICE=dg1 lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=dg1'

*.log_archive_dest_3='LOCATION=/u01/standbylog valid_for=(standby_logfile,standby_role) db_unique_name=dg2'

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

*.memory_target=786432000

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.local_listener='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2)))'

Note：红色为需要修改的部分，此处把*.local_listener='for_db'修改为了*.local_listener='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2)))'

5.2传输pfile和密码文件到备库服务器

cd $ORACLE_HOME/dbs

scp orapworcl oracle@192.168.1.156:/u01/oracle/11g/dbs/

scp initdg oracle@192.168.1.156:/u01/oracle/11g/dbs/

6:备库启动监听并将备库启动到nomount状态

[oracle@dg2 ~]$ lsnrctl start

Note：启动之前，需要在备库创建一个db_recovery_file_dest，要不在读取pfile会报错读取不到db_recovery_file_dest

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

创建控制文件目录

[oracle@dg2 ~]$ mkdir -p /u01/oracle/oradata/orcl

[oracle@dg2 ~]$ mkdir -p /u01/oracle/fast_recovery_area/orcl

创建审计文件目录

[oracle@dg2 ~]$ mkdir -p /u01/oracle/admin/orcl/adump

[oracle@dg2 dbs]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 13 18:10:11 2017

SQL> conn / as sysdba

Connected to an idle instance.

SQL> create spfile from pfile='/u01/oracle/11g/dbs/initdg.ora';

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 784998400 bytes

Fixed Size 2257352 bytes

Variable Size 515903032 bytes

Database Buffers 264241152 bytes

Redo Buffers 2596864 bytes

7：在备库连接主库做duplicate操作

7.1 连接主库

[oracle@dg2 ~]$ rman target sys/manager@dg1 auxiliary sys/manager@dg2

Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 13 22:57:40 2017

connected to target database: ORCL (DBID=1471672977)

connected to auxiliary database: ORCL (not mounted)

Note：manager为sys设置的密码。如果拷贝的主库的密码文件，则一样，如果是用orapwd创建的，根据自己的设置而定。

7.2 duplicate恢复

RMAN> duplicate target database for standby nofilenamecheck from active database;

Starting Duplicate Db at 13-MAY-17

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/oracle/11g/dbs/orapworcl' auxiliary format

'/u01/oracle/11g/dbs/orapworcl' ;

}

executing Memory Script

Starting backup at 13-MAY-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=40 device type=DISK

Finished backup at 13-MAY-17

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/u01/oracle/oradata/orcl/control01.ctl';

restore clone controlfile to '/u01/oracle/fast_recovery_area/orcl/control02.ctl' from

'/u01/oracle/oradata/orcl/control01.ctl';

}

executing Memory Script

Starting backup at 13-MAY-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

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

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

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

RMAN-03002: failure of Duplicate Db command at 05/13/2017 22:58:14

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/13/2017 22:58:14

ORA-17628: Oracle error 19505 returned by remote Oracle server

Note：报错无法copying控制文件：根据恢复的机制，应该是没有对应的目录或者没有权限

查看源库的控制文件目录

SQL> /

STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS

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

/u01/oracle/oradata/orcl/control01.ctl NO 16384 594

/u01/oracle/fast_recovery_area/orcl/control02.ctl NO 16384 594

Note:检查备库服务器确实没有对应的目录

7.3 在备库上建立控制文件目录

[oracle@dg2 dbs]$ mkdir -p /u01/oracle/oradata/orcl

[oracle@dg2 orcl]$ mkdir -p /u01/oracle/fast_recovery_area/orcl

7.4 重新执行duplicate

RMAN> duplicate target database for standby nofilenamecheck from active database;

报错

DBGSQL: TARGET> begin :fhdbi := dbms_rcvcat.getDbid; end;

DBGSQL: sqlcode = 6550

DBGSQL: B :fhdbi = 32765

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

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

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

RMAN-03002: failure of Duplicate Db command at 05/13/2017 23:16:10

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

ORA-06550: line 1, column 17:

PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Note：这是因为没有退出rman，无法识别

解决方案

7.5 退出rman，重新执行duplicate

RMAN> exit

Recovery Manager complete.

[oracle@dg2 admin]$ rman target sys/manager@dg1 auxiliary sys/manager@dg2

Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 13 23:17:40 2017

connected to target database: ORCL (DBID=1471672977)

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby nofilenamecheck from active database;

成功恢复完成

8：切换到active的状态

[oracle@dg2 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 13 23:20:41 2017

SQL> conn / as sysdba

Connected.

SQL> select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

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

MOUNTED PHYSICAL STANDBY dg2

SQL> alter database open;

Database altered.

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

Database altered.

SQL> select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

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

READ ONLY WITH APPLY PHYSICAL STANDBY dg2

SQL> select status from v$standby_log;

STATUS

----------

ACTIVE

UNASSIGNED

SQL> select member from v$logfile;

MEMBER

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

/u01/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_3_dkg8znfn_.log

/u01/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_2_dkg8zmpd_.log

/u01/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_1_dkg8zm2h_.log

/u01/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_4_dkg8zogb_.log

/u01/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_5_dkg8zpk5_.log

/u01/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_6_dkg8zq6m_.log

/u01/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_7_dkg8zqy0_.log

7 rows selected.

SQL> select db_unique_name,protection_mode,protection_level from v$database;

DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL

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

dg2 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

9：测试数据同步

主库执行

SQL> create user test identified by test;

User created.

SQL> grant dba to test;

Grant succeeded.

查看备库

SQL> conn test/test

Connected.

数据库同步，ADG搭建完成。

0 0