【DG】在Linux平台上搭建单实例的dataguard--rman还原方式
什么是dataguard?
DataGuard 是Oracle自带的数据库同步技术,基本原理为将日志文件(Redo)从源数据库传输到目标数据库,然后再目标数据库上应用(apply)这些日志文件,从而使目标数据库和源数据库保持同步。
提供三种模式可选:
(1) 最大性能模式:不影响源数据库的性能,在这种模式下,一旦数据库写到源数据库的联机日志文件中,事务即可提交,不必等到了日志写到目标数据库。
(2) 最大保护模式:日志数据必须同时写到源数据库的联机日志文件和一个目标库的备用日志文件,事务才能提交。可确保数据零丢失,当目标数据库数据不可写时,关闭源数据库。
(3) 最大可用模式:正常处于最大保护模式,当目标数据库不能写日志时,则源数据库不关闭,改为最大性能模式,当目标数据库可写后,转化为最大保护模式。
根据目标数据库上Apply方式不同:
(1) Physical Standby(Redo Apply):通过介质恢复的方法与Oracle保持同步,优点是DDL操作也可以同步包括数据文件的创建等。数据库不可用。或者在间歇过程中以只读方式打开,进行数据查询,然后恢复到日志应用模式下。
(2) Logical Standby(SQL Apply):数据库处于打开模式,通过LogMiner挖掘传输过来的日志,构造成Sql语句,然后再目标库执行,从而与源数据库保持同步。
DataGuard的缺点:
(1) 传输整个日志,因此需要很大的带宽,可能浪费三倍以上的带宽。
(2) Physical Standby 使数据库不可用,即使可以查询但是会使Oracle的同步停止。
(3) Logical Standby 不能支持DDL操作。
(4) 不支持一对多复制,不支持双向复制。
(5) 只能复制整个数据库,不能选择某个schema或表空间单独复制。
(6) 不支持异构的环境,必须是相同的操作系统以及相同的数据库版本。
主要用于高可用灾难恢复环境
1、打开主库DB系统强制归档
Shutdown immediate;
startup mount;
alter database forcelogging;
alter database open;
selectforce_logging,guard_status from V$database;
2、在备库上安装与主库一样版本Oracle Software
opatch lsinventory
3、在备库上使用NETCA配置好监听
4、在主备库上配置TNSNAMES.ORA
PRDBCSP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =prdbcsp)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prdbcsp)
)
)
STBBCSP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =stbbcsp)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stbbcsp)
)
)
5、修改主备DB上parameter参数
主库创建pfile后拷贝到备库,然后分别修改:
主库增加:
*.db_unique_name='prdbcsp'
*.log_archive_config='dg_config=(prdbcsp,stbbcsp)'
*.log_archive_dest_1='location=/dbfile/archive/prdbcspvalid_for=(all_logfiles,all_roles) db_unique_name=prdbcsp'
*.log_archive_dest_2='service=stbbcspoptional lgwr async valid_for=(online_logfiles,primary_role)db_unique_name=stbbcsp compression=enable'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/dbfile/oradata/stbbcsp','/dbfile/oradata/prdbcsp'
*.db_file_name_convert='/dbfile/oradata/stbbcsp','/dbfile/oradata/ prdbcsp'
standby_file_management='AUTO'
*.fal_client='prdbcsp'
*.fal_server='stbbcsp'
备库增加:
*.db_unique_name='stbbcsp'
*.log_archive_config='dg_config=(stbbcsp,prdbcsp)'
*.log_archive_dest_1='location=/dbfile/archive/stbbcspvalid_for=(all_logfiles,all_roles) db_unique_name=stbbcsp'
*.log_archive_dest_2='service=prdbcspoptional lgwr async valid_for=(online_logfiles,primary_role)db_unique_name=prdbcsp compression=enable'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/dbfile/oradata/prdbcsp','/dbfile/oradata/stbbcsp'
*.db_file_name_convert='/dbfile/oradata/prdbcsp','/dbfile/oradata/ stbbcsp'
standby_file_management='AUTO'
*.fal_client='stbbcsp'
*.fal_server='prdbcsp'
6、Copy database orapw口令文件
拷贝后重命名
7、Create the standby controlfile
主库执行:
Shutdown immediate
startup mount;
alter databasecreate standby controlfile as '/home/oracle/stdby_controlfile.ctl';
拷贝到备库
8、停止主库,完成数据文件迁移
有两种方式:
(1)利用RMAN导出,然后恢复
(2)直接拷贝文件
rman target /
run
allocate channel c3type disk;
copy datafile'/dev/vg_iora01/redo_256m_02.dbf' to '/dev/vg_iora03/rdata_512m_001';
copy datafile'/dev/vg_iora01/redo_256m_03.dbf' to '/dev/vg_iora03/rdata_512m_002';
}
最好用asm拷贝
利用RMAN导出,然后恢复
Startup nomountpfile=’/home/oracle/pfile.ora
Alter databasemount;
Select name fromv$datafile;
Select name fromv$tempfile;
然后恢复:
RMAN> restore controlfile to '/dbfile1/database/prdfrdb1/control1.ctl'from '/dbfile2/backupprdfrdb1/prdfrdb1_1_6100.ctb';
RMAN> alter database mount;
RMAN> RUN {
set command id to'123';
allocate channel d1type disk ;
allocate channel d2type disk ;
allocate channel d3type disk ;
allocate channel d4type disk ;
SET NEWNAME FORDATAFILE '/dbfile/oradata/prdbcsp/system01.dbf' to'/dbfile/oradata/stbbcsp/system01.dbf';
SET NEWNAME FORDATAFILE '/dbfile/oradata/prdbcsp/sysaux01.dbf' to'/dbfile/oradata/stbbcsp/sysaux01.dbf';
SET NEWNAME FORDATAFILE '/dbfile/oradata/prdbcsp/undotbs01.dbf' to'/dbfile/oradata/stbbcsp/undotbs01.dbf';
SET NEWNAME FORDATAFILE '/dbfile/oradata/prdbcsp/users01.dbf' to'/dbfile/oradata/stbbcsp/users01.dbf';
SET NEWNAME FORDATAFILE '/dbfile/oradata/prdbcsp/temp01.dbf' to'/dbfile/oradata/stbbcsp/temp01.dbf';
restore database;
SWITCH DATAFILE ALL;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}exit
直接拷贝文件:
拷贝dbfile下数据文件到备库,更改文件夹名称
拷贝之前的控制文件替换dbfile内的控制文件
Startup nomountpfile=’/home/oracle/pfile.ora
Alter databasemount;
Select name fromv$datafile;
Select name fromv$tempfile;
重命名:
alter datbase renamefile '/dbfile/oradata/prdbcsp/system01.dbf' to'/dbfile/oradata/stbbcsp/system01.dbf';
alter datbase renamefile '/dbfile/oradata/prdbcsp/sysaux01.dbf' to'/dbfile/oradata/stbbcsp/sysaux01.dbf';
alter datbase renamefile '/dbfile/oradata/prdbcsp/undotbs01.dbf' to'/dbfile/oradata/stbbcsp/undotbs01.dbf';
alter datbase renamefile '/dbfile/oradata/prdbcsp/users01.dbf' to '/dbfile/oradata/stbbcsp/users01.dbf';
alter datbase renamefile '/dbfile/oradata/prdbcsp/temp01.dbf' to'/dbfile/oradata/stbbcsp/temp01.dbf';
9、检查并修改数据文件名称和路径
select name frmV$datafile;
select name fromV$tempfile;
单机环境:
select member fromV$logfile;
RAC环境:
select a.group#,A.MEMBERfrom V$logfile a, V$log b where A.GROUP#=B.GROUP# and B.THREAD#=1;
selecta.group#,A.MEMBER from V$logfile a, V$log b where A.GROUP#=B.GROUP# and B.THREAD#=2;
修改logfile文件(不一定需要)
alter database renamefile '/dbfile/database/stbecif/stbecif-redoa1.rdo' to'/dbfile/database/stbecif/thread1-redoa1.rdo';
10、配置完成Standby redo logfile
主库:
alter database addstandby logfile group 6 ('/dbfile/oradata/prdbcsp/stbredoa.rdo') size 1024m;
alter database addstandby logfile group 7 ('/dbfile/oradata/prdbcsp/stbredob.rdo') size 1024m;
alter database addstandby logfile group 8 ('/dbfile/oradata/prdbcsp/stbredoc.rdo') size 1024m;
alter database addstandby logfile group 9 ('/dbfile/oradata/prdbcsp/stbredod.rdo') size 1024m;
e addstandby logfile group 10 ('/dbfile/oradata/prdbcsp/stbredoe.rdo') size 1024m;
alter database addstandby logfile group 11 ('/dbfile/oradata/prdbcsp/stbredof.rdo') size 1024m;
备库:
alter database addstandby logfile group 6 ('/dbfile/oradata/stbbcsp/stbredoa.rdo') size 1024m;
alter database addstandby logfile group 7 ('/dbfile/oradata/stbbcsp/stbredob.rdo') size 1024m;
alter database addstandby logfile group 8 ('/dbfile/oradata/stbbcsp/stbredoc.rdo') size 1024m;
alter database addstandby logfile group 9 ('/dbfile/oradata/stbbcsp/stbredod.rdo') size 1024m;
alter database addstandby logfile group 10 ('/dbfile/oradata/stbbcsp/stbredoe.rdo') size 1024m;
alter database addstandby logfile group 11 ('/dbfile/oradata/stbbcsp/stbredof.rdo') size 1024m;
11. 开启同步,查看状态
alter databaserecover managed standby database using current logfile disconnect from session;
alter databaserecover managed standby database cancel;
col open_mode fora25
col database_rolefor a30
col db_unique_namefor a20
selectopen_mode,database_role,db_unique_name from v$database;
col name format a30
col value format a30
col time_computedformat a30
set linesize 200
selectname,value,time_computed from V$dataguard_stats;