配置dataguard (oracle10g)

 

1.主库
配置归档（2个）
本地归档路径：
SQL> alter system set log_archive_dest_1='location=/home/oracle/archive valid_for=(online_logfiles,all_roles) db_unique_name=orcl';

System altered.

远程归档路径：
SQL> alter system set log_archive_dest_2='service=aux1 valid_for=(online_logfiles,primary_role) db_unique_name=aux1';

System altered.

更改主库为force logging
SQL> alter database force logging;

Database altered.

更改归档配置：
SQL> alter system set log_archive_config='dg_config=(orcl,aux1)';

System altered.

2.从库
  1.作参数文件
[oracle@xie dbs]$ vi initaux1.ora

*.compatible='10.2.0.1.0'
*.audit_file_dest='/u01/app/oracle/admin/dataguard/adump'
*.background_dump_dest='/u01/app/oracle/admin/dataguard/bdump'
*.user_dump_dest='/u01/app/oracle/admin/dataguard/udump'
*.core_dump_dest='/u01/app/oracle/admin/dataguard/cdump'
*.control_files='/u01/app/oracle/oradata/dataguard/control01.ctl','/u01/app/oracle/oradata/dataguard/control02.ctl','/u01/app/oracle/oradata/dataguard/control03.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_cache_size=8388608
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/dataguard/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.java_pool_size=12582912
*.job_queue_processes=10
*.large_pool_size=4194304
*.log_archive_config='dg_config=(orcl,aux1)'
*.log_archive_dest_1='location=/home/oracle/dataguard/archive valid_for=(online_logfiles,all_roles) db_unique_name=aux1'
*.log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.open_cursors=300
*.pga_aggregate_target=198180864
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=0
*.shared_pool_size=243269632
*.streams_pool_size=0
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.log_archive_format='%t_%s_%r.log'
*.db_file_name_convert=('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/dataguard/')
*.db_file_name_convert=('/dev/raw/','/u01/app/oracle/oradata/dataguard/')
*.log_file_name_convert=('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/dataguard/')
  2.从主库拷密码文件

  3.启动到nomount状态

3.配置网络，确保可以互联 （controlfile for standby）

4.用rman备份主库

[oracle@xie admin]$ rman target /

RMAN> configure channel device type disk format '/home/oracle/bk8/%d_%I_%s_%p.bkp';

RMAN> backup as compressed backupset database include current controlfile for standby plus archivelog;

将备份片传到从库：
[root@xie ~]# scp /home/oracle/bk8/* 10.1.1.135:/home/oracle/bk7/

5.用备份恢复主库到从库上：

 在主库重新启动rman

[oracle@xie admin]$ rman target / auxiliary sys/oracle@aux1
[uniread] Loaded history (205 lines)

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 5 09:02:51 2011

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

connected to target database: ORCL (DBID=1284586472, not open)
connected to auxiliary database: ORCL (not mounted)

恢复： （报错，因为备份出来的路径，和scp到从库的路径不一样，改成跟备份时的路径一样，并把路径的权限分给oracle,否则也会报错）
RMAN> duplicate target database for standby;

Starting Duplicate Db at 05-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK

contents of Memory Script:
{
   restore clone standby controlfile;
   sql clone 'alter database mount standby database';
}
executing Memory Script

Starting restore at 05-AUG-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/bk8/ORCL_1284586472_47_1.bkp
ORA-19870: error reading backup piece /home/oracle/bk8/ORCL_1284586472_47_1.bkp
ORA-19505: failed to identify file "/home/oracle/bk8/ORCL_1284586472_47_1.bkp"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
failover to previous backup

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_08_05/o1_mf_ncsnf_TAG20110805T085317_73qszpfp_.bkp
ORA-19870: error reading backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_08_05/o1_mf_ncsnf_TAG20110805T085317_73qszpfp_.bkp
ORA-19505: failed to identify file "/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_08_05/o1_mf_ncsnf_TAG20110805T085317_73qszpfp_.bkp"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
failover to previous backup

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/05/2011 09:03:50
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore

 改成跟备份时的路径一样，重试：

RMAN> duplicate target database for standby;

Starting Duplicate Db at 05-AUG-11
using channel ORA_AUX_DISK_1

contents of Memory Script:
{
   restore clone standby controlfile;
   sql clone 'alter database mount standby database';
}
executing Memory Script

Starting restore at 05-AUG-11

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/bk8/ORCL_1284586472_47_1.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/bk8/ORCL_1284586472_47_1.bkp tag=TAG20110805T085939
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/dataguard/control01.ctl
output filename=/u01/app/oracle/oradata/dataguard/control02.ctl
output filename=/u01/app/oracle/oradata/dataguard/control03.ctl
Finished restore at 05-AUG-11

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/dataguard/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/dataguard/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/dataguard/undotbs01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/dataguard/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/dataguard/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/dataguard/raw6";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/dataguard/users02.dbf";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/dataguard/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

Starting restore at 05-AUG-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/dataguard/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/dataguard/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/dataguard/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/dataguard/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/dataguard/raw6
restoring datafile 00006 to /u01/app/oracle/oradata/dataguard/users02.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/bk8/ORCL_1284586472_46_1.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/bk8/ORCL_1284586472_46_1.bkp tag=TAG20110805T085939
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 05-AUG-11

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=16 stamp=758380402 filename=/u01/app/oracle/oradata/dataguard/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=17 stamp=758380402 filename=/u01/app/oracle/oradata/dataguard/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=18 stamp=758380402 filename=/u01/app/oracle/oradata/dataguard/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=19 stamp=758380402 filename=/u01/app/oracle/oradata/dataguard/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=20 stamp=758380402 filename=/u01/app/oracle/oradata/dataguard/raw6
datafile 6 switched to datafile copy
input datafile copy recid=21 stamp=758380402 filename=/u01/app/oracle/oradata/dataguard/users02.dbf
Finished Duplicate Db at 05-AUG-11

备份完成

6.查看那主库中第二存档终点是否存在：
SQL> select dest_name,status,error,target,process from v$archive_dest where substr(dest_name,-1) in (1,2);

DEST_NAME                      STATUS    ERROR                TARGET  PROCESS
------------------------------ --------- -------------------- ------- ----------
LOG_ARCHIVE_DEST_1             VALID                          PRIMARY ARCH
LOG_ARCHIVE_DEST_2             VALID                          STANDBY ARCH

查看dg_config配置：
SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(orcl,aux1)

查看归档路径：
select DESTINATION from v$archive_dest substr(dest_name,-1) in (1,2);

查看归档使用情况：
SQL> select SEQUENCE#,APPLIED from v$archived_log;

启动MRP0服务：自动日志应用。
SQL> alter database recover managed standby database disconnect from session ;

在主库创建一个scott.datagurad表。 然后切日志。
SQL> create table scott.dataguard(x int);

Table created.
SQL> alter system switch logfile;

System altered.

然后取消自动日志应用，查看有没有新建的表。
SQL> alter database recover managed standby database cancel;

Database altered.
SQL> alter database open;

Database altered.
SQL> select * from scott.dataguard;

no rows selected
同步成功！

查看从库文件管理 模式：（auto 主库增加一个数据文件，在从库中自动反应 ,manual  一般在参数文件中指定为auto）
SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL

查看从库警报日志，如果等当前组，说明同步成功。

主库切换日志,到从库查看standby_archive_dest=/home/oracle/dataguard/archive 有没有归档!

启动：先起从，后起主
停库：先停主，在停从

查看数据库的dataguard的信息：
SQL> select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

没建dataguard也是这个效果：