一、准备工作

操作系统:Red Hat Enterprise Linux Server release 5.8 (Tikanga)

数据库:ORACLE 11.2.0.3

配置信息:

说明

主机名

IP地址

db_name

sid

主库

redhat58a

192.168.2.106

prod

prod

备库

redhat58b

192.168.2.107

prod

prodadg

写个JOB一分钟插入主库一条记录

begin

  for i in 23601 .. 27200 loop

insert into SH.CHANNELS values

(

  i,

  ‘it’,

  ‘hangzhou’,

  11,

  ‘Channel total’,

  1

);

commit;

dbms_lock.sleep(60);

end loop;

end;

用于验证搭建ADG过程中是否会丢失数据。

二、搭建ADG

主库的操作:

1.确认主库开归档,force_logging

SQL> select name,open_mode,database_role,log_mode,force_logging from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     FOR

——— ——————– —————- ———— —

PROD      READ WRITE           PRIMARY          ARCHIVELOG   YES

SQL> show parameter name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_file_name_convert                 string

db_name                              string      prod

db_unique_name                       string      prod

global_names                         boolean     FALSE

instance_name                        string      prod

lock_name_space                      string

log_file_name_convert                string

processor_group_name                 string

service_names                        string      prod

2.配置监听

备库:

 [oracle@redhat58b admin]$ cat listener.ora

LISTENER =

  (ADDRESS_LIST=

        (ADDRESS=(PROTOCOL=tcp)(HOST=redhat58b.localdomain)(PORT=1521))

  )

SID_LIST_LISTENER=

   (SID_LIST=

        (SID_DESC=

          (GLOBAL_DBNAME=stdb)

          (SID_NAME=stdb)

          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db1)

        )

        (SID_DESC=

          (GLOBAL_DBNAME=prodadg)

          (SID_NAME=prodadg)

          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db1)

        )

   )

3.配置主库,备库的TNSNAMES.ORA和scp密码文件到备库并rename

[oracle@redhat58a admin]$ cat tnsnames.ora

prod=

(DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prod)

    )

)

stdb=

(DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = stdb)

    )

)

prodadg=

(DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prodadg)

    )

)

主库$scp orapwprod redhat58b:/u01/app/oracle/product/11.2.0/db1/dbs

oracle@redhat58b’s password:

orapwprod                                     100% 1536     1.5KB/s   00:00

备库$mv orapwprod orapwprodadg

4.修改主库参数

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prod,prodadg)’;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=”LOCATION= /u01/app/oracle/arch LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod” ;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=”SERVICE=prodadg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prodadg” ;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=’ENABLE’;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=’DEFER’;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=’AUTO’;

ALTER SYSTEM SET FAL_SERVER=prodadg;

ALTER SYSTEM SET FAL_CLIENT=prod;

5.全备主库,并传输备份集到备库的相同位置

run

{

 allocate channel c0 device type disk;

 allocate channel c1 device type disk;

 CONFIGURE CONTROLFILE AUTOBACKUP ON;

 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u01/app/rman/%F’;

 backup  database format ‘/u01/app/rman/ora11g_full_db_%d_%T_%u.bak’;

 BACKUP ARCHIVELOG ALL FORMAT ‘/u01/app/rman/ora11g_arc_%s_%p_%t.bak’;

 }

6.修改备库参数

 [oracle@redhat58b pfile]$ cat initprodadg.ora

prod.__db_cache_size=310378496

prod.__java_pool_size=4194304

prod.__large_pool_size=4194304

prod.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment

prod.__pga_aggregate_target=293601280

prod.__sga_target=549453824

prod.__shared_io_pool_size=0

prod.__shared_pool_size=222298112

prod.__streams_pool_size=0

*.compatible=’11.2.0.0.0′

*.control_files=’/u01/app/oracle/oradata/prodadg/control01.ctl’,’/u01/app/oracle/oradata/prodadg/control02.ctl’

*.db_block_size=8192

*.db_domain=”

*.db_name=’prod’

*.fal_client=’PRODADG’

*.fal_server=’PROD’

*.log_archive_config=’DG_CONFIG=(prod,prodadg)’

*.log_archive_dest_1=’LOCATION=/u01/app/oracle/arch LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prodadg’

*.log_archive_dest_2=’SERVICE=prod ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod’

*.log_archive_dest_state_1=’ENABLE’

*.log_archive_dest_state_2=’ENABLE’

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

*.memory_target=842006528

*.open_cursors=300

*.processes=150

*.recyclebin=’OFF’

*.remote_login_passwordfile=’EXCLUSIVE’

*.standby_file_management=’AUTO’

*.undo_tablespace=’UNDOTBS1′

7.启动主备库监听,用pfile启动备库到nomount状态

startup nomount pfile=’/u01/app/oracle/admin/prodadg/pfile/initprodadg.ora’

8.进行备库的恢复

[oracle@redhat58a ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Fri Jul 25 23:16:44 2014

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

connected to target database: PROD (DBID=264496995)

RMAN> connect auxiliary sys/oracle@prodadg

connected to auxiliary database: PROD (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

Starting Duplicate Db at 25-JUL-14

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:

{

   restore clone standby controlfile;

}

executing Memory Script

Starting restore at 25-JUL-14

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/rman/c-264496995-20140725-00

channel ORA_AUX_DISK_1: piece handle=/u01/app/rman/c-264496995-20140725-00 tag=TAG20140725T225118

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/prodadg/control01.ctl

output file name=/u01/app/oracle/oradata/prodadg/control02.ctl

Finished restore at 25-JUL-14

contents of Memory Script:

{

   sql clone ‘alter database mount standby database’;

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to

 “/u01/app/oracle/oradata/prod/temp01.dbf”;

   switch clone tempfile all;

   set newname for datafile  1 to

 “/u01/app/oracle/oradata/prod/system01.dbf”;

   set newname for datafile  2 to

 “/u01/app/oracle/oradata/prod/sysaux01.dbf”;

   set newname for datafile  3 to

 “/u01/app/oracle/oradata/prod/undotbs01.dbf”;

   set newname for datafile  4 to

 “/u01/app/oracle/oradata/prod/users01.dbf”;

   set newname for datafile  5 to

 “/u01/app/oracle/oradata/prod/example01.dbf”;

   set newname for datafile  6 to

 “/u01/app/oracle/oradata/prod/ggs01.dbf”;

   set newname for datafile  7 to

 “/u01/app/oracle/oradata/prod/ggs02.dbf”;

   restore

   clone database

   ;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/prod/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 25-JUL-14

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/prod/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/prod/undotbs01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/prod/users01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/prod/ggs01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/rman/ora11g_full_db_PROD_20140725_02peahr0.bak

channel ORA_AUX_DISK_1: piece handle=/u01/app/rman/ora11g_full_db_PROD_20140725_02peahr0.bak tag=TAG20140725T224655

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:37

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/prod/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/prod/example01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/prod/ggs02.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/rman/ora11g_full_db_PROD_20140725_01peahr0.bak

channel ORA_AUX_DISK_1: piece handle=/u01/app/rman/ora11g_full_db_PROD_20140725_01peahr0.bak tag=TAG20140725T224655

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:21

Finished restore at 25-JUL-14

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=2 STAMP=853888748 file name=/u01/app/oracle/oradata/prod/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=3 STAMP=853888748 file name=/u01/app/oracle/oradata/prod/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=853888748 file name=/u01/app/oracle/oradata/prod/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=853888748 file name=/u01/app/oracle/oradata/prod/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=853888748 file name=/u01/app/oracle/oradata/prod/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=7 STAMP=853888748 file name=/u01/app/oracle/oradata/prod/ggs01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=8 STAMP=853888748 file name=/u01/app/oracle/oradata/prod/ggs02.dbf

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/prod/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/prod/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/prod/redo03.log’

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

Finished Duplicate Db at 25-JUL-14

9.将主库的LOG_ARCHIVE_DEST_STATE_2设置为ENABLE

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=’ENABLE’;

10.添加standby redo log

SQL> select member from v$logfile;

SQL>SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

#主库增加 STANDBY LOGFILE

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/u01/app/oracle/oradata/prod/sredo04.log’ size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/u01/app/oracle/oradata/prod/sredo05.log’ size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘/u01/app/oracle/oradata/prod/sredo06.log’ size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ‘/u01/app/oracle/oradata/prod/sredo07.log’ size 50M;

11.备库增加STANDBY LOGFILE

ALTER DATABASE ADD STANDBY LOGFILE  GROUP 4 ‘/u01/app/oracle/oradata/prod/sredo04.log’ size 50M;

ALTER DATABASE ADD STANDBY LOGFILE  GROUP 5 ‘/u01/app/oracle/oradata/prod/sredo05.log’ size 50M;

ALTER DATABASE ADD STANDBY LOGFILE  GROUP 6 ‘/u01/app/oracle/oradata/prod/sredo06.log’ size 50M;

ALTER DATABASE ADD STANDBY LOGFILE  GROUP 7 ‘/u01/app/oracle/oradata/prod/sredo07.log’ size 50M;

12.开启active dataguard

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

13主库随便切几个日志验证,发现日志能同步

SQL> select count(1) from SH.CHANNELS ;

  COUNT(1)

———-

     20319

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> select count(1) from SH.CHANNELS ;

  COUNT(1)

———-

     20320

物理DATAGUARD已经创建成功