第一章集中备份系统
1、实验环境
hostname | 系统版本号 | 数据库版本 | Ip地址 |
历史数据库(backup) | Linux (RedHat 4.6) | Oracle Release 10.2.0.1.0 | 192.168.8.63 |
生产库1(prod1) | Linux (RedHat 4.6) | Oracle Release 10.2.0.1.0 | 192.168.8.62 |
生产库2(prod2) | Linux (RedHat 4.6) | Oracle Release 10.2.0.1.0 | 192.168.8.61 |
2、前期准备
2.1、配置hosts文件
2.1.1、配置prod1的hosts:
127.0.0.1 localhost.localdomain localhost
192.168.8.61 prod1
192.168.8.62 prod2
192.168.8.63 backup
2.1.1、配置prod2的hosts:
127.0.0.1 localhost.localdomain localhost
192.168.8.62 prod2
192.168.8.61 prod1
192.168.8.63 backup
2.1.1、配置backup的hosts:
127.0.0.1 localhost.localdomain localhost
192.168.8.63 backup
192.168.8.61 prod1
192.168.8.62 prod2
2.2、设置数据库为归档模式(prod1,prod2.DSS)
2.2.1、查看当前归档模式
SQL> archivelog list
Database logmode No Archive Mode
Automaticarchival Disabled
Archivedestination USE_DB_RECOVERY_FILE_DEST
Oldest onlinelog sequence 1
Current log sequence 2
2.2.2、切换为归档模式
SQL> alterdatabase archivelog;
Databasealtered.
2.2.3、打开数据库
SQL> alterdatabase open;
Databasealtered.
2.3、配置监听与tns
2.3.1、配置生产库监听与tns
在listener.ora文件中添加(prod1):
[oracle@prod1~]$ vi /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = prod1)
(ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1)
(SERVICE_NAME=prod1)
)
)
在tnsnames.ora文件中添加(prod1):
[oracle@prod1~]$ vi /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
DSS=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =backup)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = DSS)
(INSTANCE_NAME = DSS)
)
)
在listener.ora文件中添加(prod2):
[oracle@prod2~]$ vi/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = prod2)
(ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1)
(SERVICE_NAME=prod2)
)
)
在tnsnames.ora文件中添加(prod2):
[oracle@prod2~]$ vi /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = backup)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = DSS)
(INSTANCE_NAME = DSS)
)
)
2.3.2、配置历史库监听与tns(此处必须用图形界面netca建立,否则rman连接时会出错)
在listener.ora文件中添加(backup):
[oracle@backup~]$ vi /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = DSS)
(ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME= DSS)
)
)
在tnsnames.ora文件中添加(backup):
[oracle@backup~]$ vi /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
prod1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =prod1)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = prod1)
(INSTANCE_NAME = prod1)
)
)
prod2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =prod2)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = prod2)
(INSTANCE_NAME = prod2)
)
)
2.4、在backup数据库上配置catalog
2.4.1、在恢复目录所在的数据库中创建表空间
SQL> createtablespace rcata_tbs datafile '/u01/app/oracle/oradata/DSS/rman_tbs01.dbf' size50m;
Tablespacecreated.
2.4.2、创建raman用户
SQL> createuser rcata_owner identified by oracle default tablespace rcata_tbs temporarytablespace temp;
User created.
SQL> alteruser rcata_owner quota unlimited on rcata_tbs;
User altered.
2.4.3、赋予rman用户权限
SQL> grantrecovery_catalog_owner to rcata_owner;
Grant succeeded.
SQL> grantconnect,resource to rcata_owner;
Grant succeeded.
2.4.4、创建恢复目录
[oracle@backup~]$ rman catalog rcata_owner/oracle@DSS
RecoveryManager: Release 10.2.0.1.0 - Production on Tue Nov 8 07:59:39 2011
Copyright (c)1982, 2005, Oracle. All rights reserved.
connected torecovery catalog database
RMAN> createcatalog tablespace rcata_tbs;
recovery catalogcreated
2.4.5、注册prod1 到catalog
[oracle@prod1~]$ rman target sys/oracle@PROD1 catalog rcata_owner/oracle@DSS
RecoveryManager: Release 10.2.0.1.0 - Production on Mon Dec 12 14:59:38 2011
Copyright (c)1982, 2005, Oracle. All rights reserved.
connected totarget database: PROD1 (DBID=1990716788)
connected torecovery catalog database
RMAN>register database;
databaseregistered in recovery catalog
starting fullresync of recovery catalog
full resync complete
2.4.6、注册prod2 到catalog
[oracle@prod2~]$ rman target sys/oracle@PROD2 catalog rcata_owner/oracle@DSS
RecoveryManager: Release 10.2.0.1.0 - Production on Mon Dec 12 15:01:48 2011
Copyright (c)1982, 2005, Oracle. All rights reserved.
connected totarget database: PROD2 (DBID=1421210417)
connected torecovery catalog database
RMAN>register database;
databaseregistered in recovery catalog
starting fullresync of recovery catalog
full resynccomplete
2.4.7、注册backup到catalog
[oracle@backup~]$ rman target /
RecoveryManager: Release 10.2.0.1.0 - Production on Mon Dec 12 15:52:54 2011
Copyright (c)1982, 2005, Oracle. All rights reserved.
connected totarget database: DSS (DBID=79136848)
RMAN>CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURERETENTION POLICY TO REDUNDANCY 2;
RMAN> connectcatalog rcata_owner/oracle@DSS
connected torecovery catalog database
RMAN>register database;
databaseregistered in recovery catalog
starting fullresync of recovery catalog
full resynccomplete
3、用NFS实现集中备份
3.1、在历史数据库中创建共享文件
在history所在的linux创建共享目录/backup,更改目录属性为oracle:oinstall 权限为755
[root@backup ~]#mkdir /backup
[root@backup ~]#chown oracle:oinstall /u01/backup
[root@backup ~]#chmod 755 /u01/backup
3.2、挂载设置
在/etc/exports 中添加如下内容
/u01/backup *(rw,no_root_squash,sync)
3.3、开启nfs,protmap 服务
[root@backup ~]#service portmap start
[root@backup ~]#service nfs start
在prod1 上同样开启上述服务
3.4、挂载目录
[root@prod1 ~]#mount -t nfs -o bg,hard,intr,rsize=32768,wsize=32768,vers=2,proto=tcp backup:/u01/backup/u01/backup
将该目录同样挂在到prod2上
4、编写备份脚本
在prod1,和prod2上执行相同步骤(以prod1为例)
4.1、0级备份脚本
[oracle@prod1bin]$ vi level_0.sql
#!/bin/bash
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
ORACLE_SID=prod1
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASEORACLE_HOME ORACLE_SID PATH
rman target /catalog rcata_owner/oracle@DSS <<EOF
run{
allocate channelz1 type disk;
allocate channelz2 type disk;
BACKUPINCREMENTAL level 0 database
format '/u01/backup/level0_%c_%s_%p.bak';
BACKUParchivelog all
format '/u01/backup/level0.arch_%c_%s_%p.bak'
delete input;
}
crosscheckbackup;
report obsolete;
delete nopromptobsolete;
exit;
EOF
4.2、2级备份脚本
[oracle@prod1bin]$ vi level_2.sql
#!/bin/bash
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
ORACLE_SID=prod1
PATH=$PATH:$ORACLE_HOME/bin
exportORACLE_BASE ORACLE_HOME ORACLE_SID PATH
rman target /catalog rcata_owner/oracle@DSS <<EOF
run{
allocate channelz1 type disk;
allocate channelz2 type disk;
BACKUPINCREMENTAL level 2 database
format '/u01/backup/level2_%c_%s_%p.bak';
BACKUParchivelog all
format '/u01/backup/level2.arch_%c_%s_%p.bak'
delete input;
}
crosscheckbackup;
report obsolete;
delete nopromptobsolete;
exit;
EOF
5、创建计划任务
5.1、生产库自动备份计划任务 crontab–e
*/20 * * * */u01/app/oracle/product/10.2.0/db_1/bin/level_2.sql >>/home/oracle/log/level_2.log 2>&1
* */2 * * */u01/app/oracle/product/10.2.0/db_1/bin/level_0.sql >>/home/oracle/log/level_0.log 2>&1
历史数据库自动备份计划任务
6、恢复测试
6.1、远程恢复
在备份服务器上恢复prod1(prod2操作一样)
6.1.1、在恢复服务器上声明生产库的ORACLE_SID
在历史库上执行:
[oracle@backup~]$ export ORACLE_SID=prod1
[oracle@backup~]$ env |grep ORACLE
ORACLE_SID=prod1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
6.1.2、使用RMAN连接恢复服务器与catalog:
[oracle@backup~]$ rman target / catalog rcata_owner/oracle@DSS
RecoveryManager: Release 10.2.0.1.0 - Production on Tue Dec 13 20:16:11 2011
Copyright (c)1982, 2005, Oracle. All rights reserved.
connected totarget database (not started)
connected torecovery catalog database
6.1.3、设置DBID为生产库的DBID
RMAN> setDBID=1990716788
executingcommand: SET DBID
database name is"PROD1" and DBID is 1990716788
6.1.4、启动数据库到nomount状态
RMAN> startupnomount;
6.1.5、从备份片还原spfile,controlfile文件
RMAN> restorespfile from autobackup;
Starting restoreat 22-DEC-11
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: sid=36 devtype=DISK
channelORA_DISK_1: looking for autobackup on day: 20111222
channelORA_DISK_1: autobackup found: /backup/control_c-1990716788-20111222-01
channelORA_DISK_1: SPFILE restore from autobackup complete
Finished restoreat 22-DEC-11
RMAN> restorecontrolfile from autobackup;
Starting restoreat 22-DEC-11
using channelORA_DISK_1
channelORA_DISK_1: looking for autobackup on day: 20111222
channelORA_DISK_1: autobackup found: /backup/control_c-1990716788-20111222-01
channelORA_DISK_1: control file restore from autobackup complete
outputfilename=/u01/app/oracle/product/10.2.0/db_1/dbs/cntrlprod1.dbf
Finished restoreat 22-DEC-11
6.1.6、创建pfile文件
[oracle@backupdbs]$ export ORACLE_SID=prod1
SQL> createpfile from spfile;
File created.
6.1.7、根据pfile在历史库上创建相应的目录结构
[oracle@backup~]$ mkdir -p /u01/app/oracle/admin/prod1/adump
[oracle@backup~]$ mkdir -p /u01/app/oracle/admin/prod1/bdump
[oracle@backup~]$ mkdir -p /u01/app/oracle/admin/prod1/cdump
[oracle@backup~]$ mkdir -p /u01/app/oracle/admin/prod1/udump
[oracle@backup~]$ mkdir -p /u01/app/oracle/oradata/prod1
6.1.8、转储数据文件
启动数据库到mount状态,转储所有的数据文件
RMAN> alterdatabase mount;
database mounted
RMAN> restoredatabase ;
Starting restoreat 22-DEC-11
Startingimplicit crosscheck backup at 22-DEC-11
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: sid=157 devtype=DISK
Crosschecked 35objects
Finishedimplicit crosscheck backup at 22-DEC-11
Startingimplicit crosscheck copy at 22-DEC-11
using channelORA_DISK_1
Finishedimplicit crosscheck copy at 22-DEC-11
searching forall files in the recovery area
catalogingfiles...
no filescataloged
using channelORA_DISK_1
channelORA_DISK_1: starting datafile backupset restore
channelORA_DISK_1: specifying datafile(s) to restore from backup set
restoringdatafile 00002 to /u01/app/oracle/oradata/prod1/undotbs01.dbf
restoringdatafile 00003 to /u01/app/oracle/oradata/prod1/sysaux01.dbf
channel ORA_DISK_1:reading from backup piece /backup/level0_1_465_1.bak
channelORA_DISK_1: restored backup piece 1
piecehandle=/backup/level0_1_465_1.bak tag=TAG20111222T151619
channelORA_DISK_1: restore complete, elapsed time: 00:00:47
channelORA_DISK_1: starting datafile backupset restore
channelORA_DISK_1: specifying datafile(s) to restore from backup set
restoringdatafile 00001 to /u01/app/oracle/oradata/prod1/system01.dbf
restoringdatafile 00004 to /u01/app/oracle/oradata/prod1/users01.dbf
channel ORA_DISK_1:reading from backup piece /backup/level0_1_464_1.bak
channelORA_DISK_1: restored backup piece 1
piecehandle=/backup/level0_1_464_1.bak tag=TAG20111222T151619
channelORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restoreat 22-DEC-11
6.1.9、恢复数据库
RMAN> recoverdatabase;
Starting recoverat 22-DEC-11
using channelORA_DISK_1
starting mediarecovery
channelORA_DISK_1: starting archive log restore to default destination
channelORA_DISK_1: restoring archive log
archive logthread=1 sequence=100
channelORA_DISK_1: reading from backup piece /backup/level2.arch_1_466_1.bak
channelORA_DISK_1: restored backup piece 1
piecehandle=/backup/level2.arch_1_466_1.bak tag=TAG20111222T151641
channelORA_DISK_1: restore complete, elapsed time: 00:00:02
archive logfilename=/u01/app/oracle/flash_recovery_area/PROD1/archivelog/2011_12_22/o1_mf_1_100_7h5q99fd_.arcthread=1 sequence=100
channel default:deleting archive log(s)
archive logfilename=/u01/app/oracle/flash_recovery_area/PROD1/archivelog/2011_12_22/o1_mf_1_100_7h5q99fd_.arcrecid=99 stamp=770571177
channelORA_DISK_1: starting archive log restore to default destination
channelORA_DISK_1: restoring archive log
archive logthread=1 sequence=101
channelORA_DISK_1: reading from backup piece /backup/level0.arch_1_468_1.bak
channelORA_DISK_1: restored backup piece 1
piecehandle=/backup/level0.arch_1_468_1.bak tag=TAG20111222T152133
channelORA_DISK_1: restore complete, elapsed time: 00:00:01
archive logfilename=/u01/app/oracle/flash_recovery_area/PROD1/archivelog/2011_12_22/o1_mf_1_101_7h5q9bo3_.arcthread=1 sequence=101
channel default:deleting archive log(s)
archive logfilename=/u01/app/oracle/flash_recovery_area/PROD1/archivelog/2011_12_22/o1_mf_1_101_7h5q9bo3_.arcrecid=100 stamp=770571178
unable to findarchive log
archive logthread=1 sequence=102
RMAN-03002:failure of recover command at 12/22/2011 15:33:03
RMAN-06054:media recovery requesting unknown log: thread 1 seq 102 lowscn 787847
不完全恢复成功
6.1.10、打开数据库
RMAN> alterdatabase open resetlogs;
database opened
new incarnationof database registered in recovery catalog
starting fullresync of recovery catalog
full resynccomplete
6.2、生产库本地恢复
6.2.1、建立恢复目录
[oracle@prod1~]$ mkdir /u01/app/oracle/oradata/recover
[oracle@prod1bin]$ mkdir -p /u01/app/oracle/admin/recover/adump
[oracle@prod1bin]$ mkdir -p /u01/app/oracle/admin/recover/bdump
[oracle@prod1bin]$ mkdir -p /u01/app/oracle/admin/recover/cdump
[oracle@prod1bin]$ mkdir -p /u01/app/oracle/admin/recover/udump
6.2.2、转储数据文件到恢复目录
[oracle@prod1~]$ export ORACLE_SID=prod1
[oracle@prod1~]$ rman target /
RMAN> run {
2> setnewname for datafile 1 to'/u01/app/oracle/oradata/recover/system01.dbf';
3> setnewname for datafile 2 to '/u01/app/oracle/oradata/recover/undotbs01.dbf';
4> set newnamefor datafile 3 to '/u01/app/oracle/oradata/recover/sysaux01.dbf';
5> setnewname for datafile 4 to '/u01/app/oracle/oradata/recover/users01.dbf';
6> setnewname for datafile 5 to '/u01/app/oracle/oradata/recover/tbs_stream01.dbf';
7> restoredatabase force;
8> }
Finished restoreat 27-DEC-11
6.2.3、创建pfile文件
[oracle@prod1backup]$ cd $ORACLE_HOME/dbs
[oracle@prod1dbs]$ cp initprod1.ora initrecover.ora
[oracle@prod1dbs]$ vi initrecover.ora
执行 :%s/prod1/recover/g
6.2.4、重建控制文件
[oracle@prod1dbs]$ export ORACLE_SID=recover
[oracle@prod1~]$ sqlplus / as sysdba
SQL> startupnomount;
SQL> CREATECONTROLFILE SET DATABASE "recover" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1'/u01/app/oracle/oradata/recover/redo01.log' SIZE 50M,
GROUP 2'/u01/app/oracle/oradata/recover/redo02.log' SIZE 50M,
GROUP 3'/u01/app/oracle/oradata/recover/redo03.log' SIZE 50M
-- STANDBYLOGFILE
DATAFILE
'/u01/app/oracle/oradata/recover/system01.dbf',
'/u01/app/oracle/oradata/recover/undotbs01.dbf',
'/u01/app/oracle/oradata/recover/sysaux01.dbf',
'/u01/app/oracle/oradata/recover/users01.dbf',
'/u01/app/oracle/oradata/recover/tbs_stream01.dbf';
CHARACTER SETWE8ISO8859P1;
6.2.6、设置为归档模式
SQL> alterdatabase archivelog;
Databasealtered.
6.2.6、恢复数据库
RMAN> recoverdatabase;
RMAN> alterdatabase open resetlogs;
SQL> showparameter name;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_file_name_convert string
db_name string recover
db_unique_name string recover
global_names boolean FALSE
instance_name string recover
lock_name_space string
log_file_name_convert string
service_names string recover
第二章 历史同步查询系统
1、项目目的
利用Oracle的stream技术实现生产库到历史库的业务数据单项同步
2、项目环境
hostname | 系统版本号 | 数据库版本 | Ip地址 |
历史数据库(history) | Linux (RedHat 4.6) | Oracle Release 10.2.0.1.0 | 192.168.8.63 |
生产库1(prod) | Linux (RedHat 4.6) | Oracle Release 10.2.0.1.0 | 192.168.8.61 |
3、生产库配置
3.1、初始化参数配置
通过以下命令修改生产库参数文件
SQL> altersystem set aq_tm_processes=2 scope=both;
SQL> altersystem set global_names=true scope=both;
SQL> altersystem set job_queue_processes=10 scope=both;
SQL> altersystem set parallel_max_servers=20 scope=both;
SQL> altersystem set undo_retention=3600 scope=both;
SQL> altersystem set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;
SQL> altersystem set streams_pool_size=25M scope=spfile;
SQL> altersystem set utl_file_dir='*' scope=spfile;
SQL> altersystem set open_links=4 scope=spfile;
3.2、将数据库变为归档模式
将生产库启动到mount状态,并改为归档模式
SQL> startupmount force
SQL> alterdatabase archivelog;
SQL> altersystem set log_archive_dest_1='location=/home/oracle/arch' scope=spfile;
System altered.
SQL> altersystem set log_archive_start=TRUE scope=spfile;
System altered.
SQL> altersystem set log_archive_format='arch%t_%s_%r.arc' scope=spfile;
System altered.
SQL>alterdatabase open;
3.3、强制使用日志模式
SQL> alterdatabase force logging;
3.4、创建stream用户使用的表空间
SQL> createtablespace tbs_stream datafile '/u01/app/oracle/oradata/prod1/tbs_stream01.dbf'size 100m autoextend on maxsize unlimited segment space management auto;
Tablespacecreated.
3.5、将logminer的数据字典从system表空间转移到新建的表空间
使用如下命令,将logminer的数据字典从system表空间转移到新建的表空间,防止撑爆system表空间
SQL> executedbms_logmnr_d.set_tablespace('tbs_stream');
PL/SQL proceduresuccessfully completed.
3.6、创建stream用户
SQL> createuser strmadmin identified by oracle default tablespace tbs_stream temporarytablespace temp;
User created.
授权stream用户
SQL> grantconnect,resource,dba,aq_administrator_role to strmadmin;
Grant succeeded.
SQL> begin
2 dbms_streams_auth.grant_admin_privilege(
3 grantee =>'strmadmin',
4 grant_privileges=>true);
5 end;
6 /
PL/SQL proceduresuccessfully completed.
3.7、开启追加日志
可以基于Database级别或Table级别,启用追加日志(Supplemental Log)。在建立根据Schema粒度进行复制的Oracle Stream环境中,如果确认Schema下所有Table都有合理的主键(Primary Key),则不再需要启用追加日志。
SQL> alter database add supplemental log data;
Databasealtered.
3.8、配置监听
添加以下内容
DSS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.11.63)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DSS)
)
)
3.9、创建DBlink
根据Oracle 10gR2Stream官方文档,针对主数据库建立的数据库链的名字必须和从数据库的global_name相同。
在prod1执行如下语句更改global_name
SQL> alterdatabase rename global_name to prod1;
Databasealtered.
以stmadmin身份登录prod1
SQL> connstrmadmin/oracle
Connected.
SQL> create database linkDSS connect to strmadmin identified by oracle using 'DSS';
Database link created.
4、历史数据库配置
4.1、初始化参数配置
通过以下命令修改历史库参数文件
SQL> altersystem set aq_tm_processes=2 scope=both;
SQL> altersystem set global_names=true scope=both;
SQL> altersystem set job_queue_processes=10 scope=both;
SQL> altersystem set parallel_max_servers=20 scope=both;
SQL> altersystem set undo_retention=3600 scope=both;
SQL> altersystem set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;
SQL> altersystem set streams_pool_size=25M scope=spfile;
SQL> altersystem set utl_file_dir='*' scope=spfile;
SQL> altersystem set open_links=4 scope=spfile;
4.2、将数据库变为归档模式
将生产库启动到mount状态,并改为归档模式
SQL> startupmount force
SQL> alterdatabase archivelog;
SQL> altersystem set log_archive_dest_1='location=/home/oracle/arch' scope=spfile;
System altered.
SQL> altersystem set log_archive_start=TRUE scope=spfile;
System altered.
SQL> altersystem set log_archive_format='arch%t_%s_%r.arc' scope=spfile;
System altered.
SQL>alterdatabase open;
4.3、强制使用日志模式
SQL> alterdatabase force logging;
4.4、创建stream用户使用的表空间
SQL> createtablespace tbs_stream datafile '/u01/app/oracle/oradata/prod1/tbs_stream01.dbf'size 100m autoextend on maxsize unlimited segment space management auto;
Tablespacecreated.
4.5、将logminer的数据字典从system表空间转移到新建的表空间
使用如下命令,将logminer的数据字典从system表空间转移到新建的表空间,防止撑爆system表空间
SQL> executedbms_logmnr_d.set_tablespace('tbs_stream');
PL/SQL proceduresuccessfully completed.
4.6、创建stream用户
SQL> createuser strmadmin identified by oracle default tablespace tbs_stream temporarytablespace temp;
User created.
授权stream用户
SQL> grantconnect,resource,dba,aq_administrator_role to strmadmin;
Grant succeeded.
SQL> begin
2 dbms_streams_auth.grant_admin_privilege(
3 grantee =>'strmadmin',
4 grant_privileges=>true);
5 end;
6 /
PL/SQL proceduresuccessfully completed.
4.7、配置监听
添加以下内容
prod1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.11.61)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod1)
)
)
4.8、创建DBlink
以stmadmin身份登录DSS
SQL> connstrmadmin/oracle
Connected.
建立DBlink
SQL> createdatabase link prod1 connect to strmadmin identified by oracle using 'prod1';
Database linkcreated.
5、数据库同步stream配置
5.1、登录成产库,创建master流队列
SQL> begin
2 dbms_streams_adm.set_up_queue(
3 queue_table =>'prod1_queue_table',
4 queue_name =>'prod1_queue');
5 end;
6 /
PL/SQL proceduresuccessfully completed.
5.2、登录历史库,创建backup流队列
以strmadmin身份登录DSS,执行如下指令
SQL> begin
2 dbms_streams_adm.set_up_queue(
3 queue_table => 'DSS_queue_table',
4 queue_name => 'DSS_queue');
5 end;
6 /
PL/SQL proceduresuccessfully completed.
5.3、生产库创建捕捉过程
以stream用户,登录数据库,执行以下代码块,创建捕获进程,同步wenxin用户的表
SQL> begin
2 dbms_streams_adm.add_schema_rules(
3 schema_name=>'scott',
4 streams_type=>'capture',
5 streams_name=>'capture_prod1',
6 queue_name=>'strmadmin.prod1_queue',
7 include_dml=>true,
8 include_ddl=>true,
9 include_tagged_lcr=>false,
10 source_database=>null,
11 inclusion_rule=>true);
12 end;
13 /
PL/SQL proceduresuccessfully completed.
5.4、生产库创建传播过程
以strmadmin身份登录prod1库,执行如下语句
SQL> connstrmadmin/oracle
Connected.
SQL> begin
2 dbms_streams_adm.add_schema_propagation_rules(
3 schema_name=>'scott',
4 streams_name=>'prod1_to_DSS',
5 source_queue_name=>'strmadmin.prod1_queue',
6 destination_queue_name=>'strmadmin.DSS_queue@DSS',
7 include_dml=>true,
8 include_ddl=>true,
9 include_tagged_lcr=>false,
10 source_database=>'prod1',
11 inclusion_rule=>true);
12 end;
13 /
PL/SQL proceduresuccessfully completed.
5.5、修改propagation休眠时间
修改propagation休眠时间为0,表示实时传播LCR
SQL> begin
2 DBMS_aqadm.alter_propagation_schedule(
3 queue_name => 'prod1_queue',
4 destination=>'DSS',
5 latency=>0);
6 end;
7 /
PL/SQL proceduresuccessfully completed.
5.6、设置目标数据库上的wenxin schema的Instantiation SCN
Stream用户登录在源数据库上设置目标数据库上scott schema的InstantiationSCN
SQL>DECLARE
iscnNUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@dss(
source_schema_name => 'scott',
source_database_name=> 'prod1',
instantiation_scn => iscn+10,
recursive => true);
END;
/
以strmadmin身份登录DSS库,执行如下语句
SQL> connstrmadmin/oracle
Connected.
SQL> begin
2 dbms_streams_adm.add_schema_rules(
3 schema_name=>'scott',
4 streams_type=>'apply',
5 streams_name=>'apply_DSS',
6 queue_name=>'strmadmin.DSS_queue',
7 include_dml=>true,
8 include_ddl=>true,
9 include_tagged_lcr=>false,
10 source_database=>'prod1',
11 inclusion_rule=>true);
12 end;
13 /
PL/SQL proceduresuccessfully completed.
5.7、启动捕捉进程
以strmadmin身份,登录prod1库,启动capture进程
SQL> begin
2 dbms_capture_adm.start_capture(
3 capture_name=>'capture_prod1');
4 end;
5 /
PL/SQL proceduresuccessfully completed.
以strmadmin身份,登录DSS库,启动apply进程
SQL> begin
2 dbms_apply_adm.start_apply(
3 apply_name=>'apply_DSS');
4 end;
5 /
PL/SQL proceduresuccessfully completed.
6、历史同步测试场景
此次建立了针对scott用户的stream复制环境,如果没有特别的声明,一下测试均以wenxin用户身份执行
6.1、创建表测试
prod1:
SQL> createtable test (id number primary key, name varchar(50));
Table created.
DSS:
SQL> desctest;
Name Null? Type
------------------------------------------------- ----------------------------
ID NOTNULL NUMBER
NAME VARCHAR2(50)
成功同步
6.2、往表中添加数据测试:
prod1:
SQL> insertinto test values(1,'scott);
1 row created.
SQL> commit;
Commit complete.
SQL> insertinto test values(2,'liu');
1 row created.
DSS:
SQL> select *from test;
ID NAME
------------------------------------------------------------
1 scott
同步成功
6.3、更改表结构测试
prod1:
SQL> altertable test add (age number(5));
Table altered.
DSS:
SQL> desctest;
Name Null? Type
------------------------------------------------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(50)
AGE NUMBER(5)
同步成功