oracle集中备份&历史查询
来源：互联网 发布：算法类的书籍推荐 编辑：程序博客网 时间：2024/04/29 13:24
第一章集中备份系统
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
DSS=
(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.
建立DBlink
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)
同步成功
- oracle集中备份&历史查询
- oracle集中备份
- 查询据库备份历史
- ORACLE集中备份方案之NFS篇
- 查询oracle历史sql操作
- oracle从备份集中恢复归档日志方法
- oracle从备份集中恢复归档日志方法
- ORACLE查询某个sqlid 执行历史时间
- ORACLE备份、恢复、常用查询
- Oracle 查询结果集中的一行或多行
- Amanda集中备份实例
- 及时清理Oracle 备份目录中的历史文件
- 长江证券集中交易系统本地查询及异地容灾备份系统建设
- Windows系统下Oracle自动备份数据库，删除历史备份数据
- oracle数据库rman备份进度查询
- Oracle 查询数据库碎片和和历史会话
- DB2查看历史备份
- innobackupex存储备份历史
- poj 1033 Defragment 简单搜索
- POJ 1690 - (Your)((Term)((Project)))
- 14：Labeling Balls
- 黑马程序员_强大的SQL语句—查询
- 交通灯系统
- oracle集中备份&历史查询
- Android通过XML来定义Menu
- Django点滴（四）---ORM对象存取
- 微软实习笔试的几道程序题
- PHP的date()函数所支持的格式代码
- ORANGE'S之二-保护模式(2)
- jquery表单验证插件 jquery.form.js
- 3dMax Plugin
- ZOJ 2417 Lowest Bit
