oracle 11G dataguard配置
1、規劃
源端
IP: 192.168.2.2 hostname: dgdb sid:dgdb dbname:dgdb db_unique_name:dgdb_pd
目標端
IP:192.168.2.4 hostname:sgdb sid:dgdb dbname:dgdb db_unique_name:sgdb_st
2、生產端配置
a、設定歸檔模式
archive log list;
alter system set log_archive_dest='/u01/app/oracle/arch' scope=both;
shutdown immediate
startup mount
alter database archivelog;
archive log list;
b、設定強制模式
select force_logging from v$database;
alter database force logging;
c、配置監聽，需要在主資料庫和STANDBY資料庫建立各自的監聽，主資料庫監聽配置如下：
[oracle@dgdb admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgdb)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dgdb)
(ORACLE_HOME = /u01/app/oracle/11.2.0/db_1)
(SID_NAME = dgdb)
)
)
d、配置TNS,需要在主資料庫和STANDBY資料庫建立,主資料庫TNS配置如下：
liu_dgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
liu_sgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
[oracle@dgdb admin]$ more tnsnames.ora
liu_dgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
liu_sgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
[oracle@dgdb admin]$
d、配置主庫引數檔案
create pfile from spfile;
[oracle@dgdb dbs]$ more initdgdb.ora
dgdb.__db_cache_size=104857600
dgdb.__java_pool_size=4194304
dgdb.__large_pool_size=4194304
dgdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dgdb.__pga_aggregate_target=159383552
dgdb.__sga_target=251658240
dgdb.__shared_io_pool_size=0
dgdb.__shared_pool_size=130023424
dgdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dgdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dgdb/dgdb/control01.ctl','/oradata/dgdb/dgdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dgdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgdbXDB)'
*.log_archive_dest='/u01/app/oracle/arch' ---去掉該引數
*.memory_target=408944640
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@dgdb dbs]$
需要修改的引數：
*.db_name='dgdb'
*.db_unique_name='dgdb_pd'
*.log_archive_config='dg_config=(dgdb_pd,sgdb_st)'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=dgdb_pd'
*.log_archive_dest_2='service=liu_sgdb reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sgdb_st'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='liu_sgdb'
*.fal_client='liu_dgdb'
修改後的配置如下：
dgdb.__db_cache_size=104857600
dgdb.__java_pool_size=4194304
dgdb.__large_pool_size=4194304
dgdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dgdb.__pga_aggregate_target=159383552
dgdb.__sga_target=251658240
dgdb.__shared_io_pool_size=0
dgdb.__shared_pool_size=130023424
dgdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dgdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dgdb/dgdb/control01.ctl','/oradata/dgdb/dgdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dgdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgdbXDB)'
*.memory_target=408944640
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='dgdb_pd'
*.log_archive_config='dg_config=(dgdb_pd,sgdb_st)'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=dgdb_pd'
*.log_archive_dest_2='service=liu_sgdb reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sgdb_st'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='liu_sgdb'
*.fal_client='liu_dgdb'
create spfile from pfile;
e、配置主庫STANDBY REDO 日誌
select thread#,bytes/1024/1024,members from v$log;
select member from v$logfile;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo01.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo02.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo03.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo04.log' size 50m;
f、建立密碼檔案
orapwd file=$ORACLE_HOME/dbs/orapwdgdb password=oracle entries=5
3、備庫的配置
a、備資料庫端配置監聽和TNS
---監聽---
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sgdb)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dgdb)
(ORACLE_HOME = /u01/app/oracle/11.2.0/db_1)
(SID_NAME = dgdb)
)
)
---TNS----
liu_dgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
liu_sgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
b、備庫建立引數檔案
dgdb.__db_cache_size=104857600
dgdb.__java_pool_size=4194304
dgdb.__large_pool_size=4194304
dgdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dgdb.__pga_aggregate_target=159383552
dgdb.__sga_target=251658240
dgdb.__shared_io_pool_size=0
dgdb.__shared_pool_size=130023424
dgdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dgdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dgdb/dgdb/control01.ctl','/oradata/dgdb/dgdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dgdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sgdbXDB)'
*.memory_target=408944640
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='sgdb_st'
*.log_archive_config='dg_config=(dgdb_pd,sgdb_st)'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=sgdb_st'
*.log_archive_dest_2='service=liu_dgdb reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dgdb_pd'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='liu_dgdb'
LOG_FILE_NAME_CONVERT='/oradata/dgdb/dgdb','/oradata/dgdb/dgdb'
c、備庫建立相應的目錄
adump bdump cdump udump
歸檔位置等等
d、備庫啟動到NOMOUNT狀態下
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2213856 bytes
Variable Size 297797664 bytes
Database Buffers 104857600 bytes
Redo Buffers 4325376 bytes
SQL>
e、建立密碼檔案
ftp主庫密碼檔案到備庫相應的位置
4、主庫備份
run
{
allocate channel c1 device type disk format '/u01/backup/datafile/full_%u_%s_%p';
backup database plus archivelog;
}
run
{
allocate channel c1 device type disk format '/u01/backup/archive/control%u_%s_%p';
backup current controlfile for standby;
}
oracle@dgdb datafile]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 18 16:15:53 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DGDB (DBID=2176221724)
RMAN> run
2> {
3> allocate channel c1 device type disk format '/u01/backup/datafile/full_%u_%s_%p';
4> backup database plus archivelog;
5> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=39 device type=DISK
Starting backup at 2014:03:18 16:16:02
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=1 STAMP=842541448
input archived log thread=1 sequence=8 RECID=2 STAMP=842541451
input archived log thread=1 sequence=9 RECID=3 STAMP=842544864
input archived log thread=1 sequence=10 RECID=4 STAMP=842544865
input archived log thread=1 sequence=11 RECID=5 STAMP=842544963
channel c1: starting piece 1 at 2014:03:18 16:16:04
channel c1: finished piece 1 at 2014:03:18 16:16:05
piece handle=/u01/backup/datafile/full_01p3gdq4_1_1 tag=TAG20140318T161603 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:16:05
Starting backup at 2014:03:18 16:16:06
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/dgdb/dgdb/system01.dbf
input datafile file number=00002 name=/oradata/dgdb/dgdb/sysaux01.dbf
input datafile file number=00003 name=/oradata/dgdb/dgdb/undotbs01.dbf
input datafile file number=00004 name=/oradata/dgdb/dgdb/users01.dbf
channel c1: starting piece 1 at 2014:03:18 16:16:06
channel c1: finished piece 1 at 2014:03:18 16:17:31
piece handle=/u01/backup/datafile/full_02p3gdq6_2_1 tag=TAG20140318T161606 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:25
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 2014:03:18 16:17:35
channel c1: finished piece 1 at 2014:03:18 16:17:36
piece handle=/u01/backup/datafile/full_03p3gdss_3_1 tag=TAG20140318T161606 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:17:36
Starting backup at 2014:03:18 16:17:36
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=6 STAMP=842545056
channel c1: starting piece 1 at 2014:03:18 16:17:37
channel c1: finished piece 1 at 2014:03:18 16:17:38
piece handle=/u01/backup/datafile/full_04p3gdt1_4_1 tag=TAG20140318T161736 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:17:38
released channel: c1
RMAN>
RMAN> run
2> {
3> allocate channel c1 device type disk format '/u01/backup/archive/control%u_%s_%p';
4> backup current controlfile for standby;
5> }
allocated channel: c1
channel c1: SID=39 device type=DISK
Starting backup at 2014:03:18 16:21:24
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 2014:03:18 16:21:26
channel c1: finished piece 1 at 2014:03:18 16:21:27
piece handle=/u01/backup/archive/control05p3ge45_5_1 tag=TAG20140318T162124 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:21:27
released channel: c1
5、傳輸到備庫相應目錄
6、備庫恢復
a、恢復控制檔案
restore standby controlfile from '/u01/backup/archive/control05p3ge45_5_1';
RMAN> restore standby controlfile from '/u01/backup/archive/control05p3ge45_5_1';
Starting restore at 2014:03:18 14:00:26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oradata/dgdb/dgdb/control01.ctl
output file name=/oradata/dgdb/dgdb/control02.ctl
Finished restore at 2014:03:18 14:00:31
RMAN>
b、備份啟動到mount狀態下，恢復資料檔案
alter database mount standby database;
c、恢復資料檔案
RMAN> restore database;
Starting restore at 2014:03:18 14:24:23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata/dgdb/dgdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata/dgdb/dgdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/dgdb/dgdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/dgdb/dgdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/datafile/full_02p3gdq6_2_1
channel ORA_DISK_1: piece handle=/u01/backup/datafile/full_02p3gdq6_2_1 tag=TAG20140318T161606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 2014:03:18 14:25:41
RMAN>
RMAN>
RMAN> recover database;
Starting recover at 2014:03:18 14:54:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece /u01/backup/datafile/full_04p3gdt1_4_1
channel ORA_DISK_1: piece handle=/u01/backup/datafile/full_04p3gdt1_4_1 tag=TAG20140318T161736
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/arch/1_12_830232990.dbf thread=1 sequence=12
unable to find archived log
archived log thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/18/2014 14:54:38
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 13 and starting SCN of 1027111
RMAN>
7、備份建立STANDY REDO日誌
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo01.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo02.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo03.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo04.log' size 50m;
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
8、開啟同步應用
alter database recover managed standby database disconnect from session; -- 啟動日誌應用
alter database recover managed standby database using current logfile disconnect from session;
源端
IP: 192.168.2.2 hostname: dgdb sid:dgdb dbname:dgdb db_unique_name:dgdb_pd
目標端
IP:192.168.2.4 hostname:sgdb sid:dgdb dbname:dgdb db_unique_name:sgdb_st
2、生產端配置
a、設定歸檔模式
archive log list;
alter system set log_archive_dest='/u01/app/oracle/arch' scope=both;
shutdown immediate
startup mount
alter database archivelog;
archive log list;
b、設定強制模式
select force_logging from v$database;
alter database force logging;
c、配置監聽，需要在主資料庫和STANDBY資料庫建立各自的監聽，主資料庫監聽配置如下：
[oracle@dgdb admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgdb)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dgdb)
(ORACLE_HOME = /u01/app/oracle/11.2.0/db_1)
(SID_NAME = dgdb)
)
)
d、配置TNS,需要在主資料庫和STANDBY資料庫建立,主資料庫TNS配置如下：
liu_dgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
liu_sgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
[oracle@dgdb admin]$ more tnsnames.ora
liu_dgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
liu_sgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
[oracle@dgdb admin]$
d、配置主庫引數檔案
create pfile from spfile;
[oracle@dgdb dbs]$ more initdgdb.ora
dgdb.__db_cache_size=104857600
dgdb.__java_pool_size=4194304
dgdb.__large_pool_size=4194304
dgdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dgdb.__pga_aggregate_target=159383552
dgdb.__sga_target=251658240
dgdb.__shared_io_pool_size=0
dgdb.__shared_pool_size=130023424
dgdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dgdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dgdb/dgdb/control01.ctl','/oradata/dgdb/dgdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dgdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgdbXDB)'
*.log_archive_dest='/u01/app/oracle/arch' ---去掉該引數
*.memory_target=408944640
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@dgdb dbs]$
需要修改的引數：
*.db_name='dgdb'
*.db_unique_name='dgdb_pd'
*.log_archive_config='dg_config=(dgdb_pd,sgdb_st)'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=dgdb_pd'
*.log_archive_dest_2='service=liu_sgdb reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sgdb_st'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='liu_sgdb'
*.fal_client='liu_dgdb'
修改後的配置如下：
dgdb.__db_cache_size=104857600
dgdb.__java_pool_size=4194304
dgdb.__large_pool_size=4194304
dgdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dgdb.__pga_aggregate_target=159383552
dgdb.__sga_target=251658240
dgdb.__shared_io_pool_size=0
dgdb.__shared_pool_size=130023424
dgdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dgdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dgdb/dgdb/control01.ctl','/oradata/dgdb/dgdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dgdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgdbXDB)'
*.memory_target=408944640
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='dgdb_pd'
*.log_archive_config='dg_config=(dgdb_pd,sgdb_st)'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=dgdb_pd'
*.log_archive_dest_2='service=liu_sgdb reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sgdb_st'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='liu_sgdb'
*.fal_client='liu_dgdb'
create spfile from pfile;
e、配置主庫STANDBY REDO 日誌
select thread#,bytes/1024/1024,members from v$log;
select member from v$logfile;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo01.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo02.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo03.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo04.log' size 50m;
f、建立密碼檔案
orapwd file=$ORACLE_HOME/dbs/orapwdgdb password=oracle entries=5
3、備庫的配置
a、備資料庫端配置監聽和TNS
---監聽---
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sgdb)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dgdb)
(ORACLE_HOME = /u01/app/oracle/11.2.0/db_1)
(SID_NAME = dgdb)
)
)
---TNS----
liu_dgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
liu_sgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgdb)
)
)
b、備庫建立引數檔案
dgdb.__db_cache_size=104857600
dgdb.__java_pool_size=4194304
dgdb.__large_pool_size=4194304
dgdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dgdb.__pga_aggregate_target=159383552
dgdb.__sga_target=251658240
dgdb.__shared_io_pool_size=0
dgdb.__shared_pool_size=130023424
dgdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dgdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dgdb/dgdb/control01.ctl','/oradata/dgdb/dgdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dgdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sgdbXDB)'
*.memory_target=408944640
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='sgdb_st'
*.log_archive_config='dg_config=(dgdb_pd,sgdb_st)'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=sgdb_st'
*.log_archive_dest_2='service=liu_dgdb reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dgdb_pd'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='liu_dgdb'
LOG_FILE_NAME_CONVERT='/oradata/dgdb/dgdb','/oradata/dgdb/dgdb'
c、備庫建立相應的目錄
adump bdump cdump udump
歸檔位置等等
d、備庫啟動到NOMOUNT狀態下
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2213856 bytes
Variable Size 297797664 bytes
Database Buffers 104857600 bytes
Redo Buffers 4325376 bytes
SQL>
e、建立密碼檔案
ftp主庫密碼檔案到備庫相應的位置
4、主庫備份
run
{
allocate channel c1 device type disk format '/u01/backup/datafile/full_%u_%s_%p';
backup database plus archivelog;
}
run
{
allocate channel c1 device type disk format '/u01/backup/archive/control%u_%s_%p';
backup current controlfile for standby;
}
oracle@dgdb datafile]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 18 16:15:53 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DGDB (DBID=2176221724)
RMAN> run
2> {
3> allocate channel c1 device type disk format '/u01/backup/datafile/full_%u_%s_%p';
4> backup database plus archivelog;
5> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=39 device type=DISK
Starting backup at 2014:03:18 16:16:02
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=1 STAMP=842541448
input archived log thread=1 sequence=8 RECID=2 STAMP=842541451
input archived log thread=1 sequence=9 RECID=3 STAMP=842544864
input archived log thread=1 sequence=10 RECID=4 STAMP=842544865
input archived log thread=1 sequence=11 RECID=5 STAMP=842544963
channel c1: starting piece 1 at 2014:03:18 16:16:04
channel c1: finished piece 1 at 2014:03:18 16:16:05
piece handle=/u01/backup/datafile/full_01p3gdq4_1_1 tag=TAG20140318T161603 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:16:05
Starting backup at 2014:03:18 16:16:06
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/dgdb/dgdb/system01.dbf
input datafile file number=00002 name=/oradata/dgdb/dgdb/sysaux01.dbf
input datafile file number=00003 name=/oradata/dgdb/dgdb/undotbs01.dbf
input datafile file number=00004 name=/oradata/dgdb/dgdb/users01.dbf
channel c1: starting piece 1 at 2014:03:18 16:16:06
channel c1: finished piece 1 at 2014:03:18 16:17:31
piece handle=/u01/backup/datafile/full_02p3gdq6_2_1 tag=TAG20140318T161606 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:25
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 2014:03:18 16:17:35
channel c1: finished piece 1 at 2014:03:18 16:17:36
piece handle=/u01/backup/datafile/full_03p3gdss_3_1 tag=TAG20140318T161606 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:17:36
Starting backup at 2014:03:18 16:17:36
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=6 STAMP=842545056
channel c1: starting piece 1 at 2014:03:18 16:17:37
channel c1: finished piece 1 at 2014:03:18 16:17:38
piece handle=/u01/backup/datafile/full_04p3gdt1_4_1 tag=TAG20140318T161736 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:17:38
released channel: c1
RMAN>
RMAN> run
2> {
3> allocate channel c1 device type disk format '/u01/backup/archive/control%u_%s_%p';
4> backup current controlfile for standby;
5> }
allocated channel: c1
channel c1: SID=39 device type=DISK
Starting backup at 2014:03:18 16:21:24
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 2014:03:18 16:21:26
channel c1: finished piece 1 at 2014:03:18 16:21:27
piece handle=/u01/backup/archive/control05p3ge45_5_1 tag=TAG20140318T162124 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:21:27
released channel: c1
5、傳輸到備庫相應目錄
6、備庫恢復
a、恢復控制檔案
restore standby controlfile from '/u01/backup/archive/control05p3ge45_5_1';
RMAN> restore standby controlfile from '/u01/backup/archive/control05p3ge45_5_1';
Starting restore at 2014:03:18 14:00:26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oradata/dgdb/dgdb/control01.ctl
output file name=/oradata/dgdb/dgdb/control02.ctl
Finished restore at 2014:03:18 14:00:31
RMAN>
b、備份啟動到mount狀態下，恢復資料檔案
alter database mount standby database;
c、恢復資料檔案
RMAN> restore database;
Starting restore at 2014:03:18 14:24:23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata/dgdb/dgdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata/dgdb/dgdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/dgdb/dgdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/dgdb/dgdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/datafile/full_02p3gdq6_2_1
channel ORA_DISK_1: piece handle=/u01/backup/datafile/full_02p3gdq6_2_1 tag=TAG20140318T161606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 2014:03:18 14:25:41
RMAN>
RMAN>
RMAN> recover database;
Starting recover at 2014:03:18 14:54:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece /u01/backup/datafile/full_04p3gdt1_4_1
channel ORA_DISK_1: piece handle=/u01/backup/datafile/full_04p3gdt1_4_1 tag=TAG20140318T161736
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/arch/1_12_830232990.dbf thread=1 sequence=12
unable to find archived log
archived log thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/18/2014 14:54:38
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 13 and starting SCN of 1027111
RMAN>
7、備份建立STANDY REDO日誌
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo01.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo02.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo03.log' size 50m;
alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo04.log' size 50m;
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
8、開啟同步應用
alter database recover managed standby database disconnect from session; -- 啟動日誌應用
alter database recover managed standby database using current logfile disconnect from session;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27571661/viewspace-1126403/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [zt] Oracle 11g DataGuard 配置Oracle
- ORACLE 11g dataguard配置練習Oracle
- Oracle 11g dataguard 配置簡約步驟Oracle
- Oracle 11g RAC 配置單例項 DataGuardOracle單例
- 通過 rman duplicate 配置Oracle 11g Active DataguardOracle
- oracle 11g dataguardOracle
- Linux Oracle 11g Dataguard配置詳細步驟LinuxOracle
- Oracle之11g DataGuardOracle
- oracle 11g dataguard 建立Oracle
- Oracle 11g Active DataguardOracle
- oracle dataguard broker 配置Oracle
- 配置Oracle physical DataGuardOracle
- Oracle 11g RAC 配置單例項 DataGuard（通過 DUPLICATE 方式）Oracle單例
- [Oracle] oracle 11g dataguard (one instance)Oracle
- Oracle 單機配置DataGuardOracle
- oracle 11g dataguard 完全手冊Oracle
- 【DataGuard】Oracle 11g physical standby switchoverOracle
- 【DataGuard】Oracle 11g DataGuard 新特性之 Snapshot Standby DatabaseOracleDatabase
- 【DataGuard】手工冷備搭建 Oracle 11g DataGuard 物理備庫Oracle
- oracle-11g-配置dataguardOracle
- Debian下配置Oracle DataGuardOracle
- Oracle DataGuard 11g 雙機實驗Oracle
- Oracle 11g Active Dataguard Switchover實驗Oracle
- DataGuard on Oracle 11g初步介紹(1)Oracle
- DataGuard on Oracle 11g初步介紹(2)Oracle
- DataGuard on Oracle 11g初步介紹(3)Oracle
- 【DataGuard】Oracle 11g DataGuard 角色轉換(一)物理備庫SwitchoverOracle
- oracle dataguard 配置錯誤彙總Oracle
- VMware中配置ORACLE DATAGUARD步驟Oracle
- 探索ORACLE之11g DataGuard_01概念Oracle
- Oracle 11g dataguard check real time applyOracleAPP
- ORACLE10G DATAGUARD配置筆記Oracle筆記
- ORACLE 11g dataguard系列，手工切換測試Oracle
- Oracle 11G DataGuard重啟詳細過程Oracle
- ORACLE 11G DATAGUARD 日誌中斷處理方案Oracle
- Oracle 11g Data Guard (physical standby - active dataguard) [final]Oracle
- 【DataGuard】Oracle 11g DataGuard 新特性之 Active Standby：Real-Time Apply+QueryOracleAPP
- ORACLE10g DataGuard 配置Physical Standby DatabaseOracleDatabase