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博客 ” ，链接：https://blog.itpub.net/27571661/viewspace-1126403/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
物理DG监控
请登录后发表评论
登录
全部评论
