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也是这个效果: