热备份脚本 rem script:hotbak.sql rem creater:chenjiping rem date:4.4.2002 rem desc:backup database datafile in archive--connect database connect internal/virpure;--archive alter system checkpoint; alter system archive log all; alter system switch logfile; --start alter tablespace afis_remote begin backup; !xcopy e:\orant\database\remote_data1.ora e:\databak/H/R; !xcopy e:\orant\database\remote_data2.ora e:\databak/H/R; alter tablespace afis_remote end backup;alter tablespace afis_rmtafis begin backup; !xcopy e:\orant\database\rmtafis_data1.ora e:\databak/H/R; !xcopy e:\orant\database\rmtafis_data2.ora e:\databak/H/R; alter tablespace afis_rmtafis end backup;alter tablespace afis_lquser begin backup; !xcopy e:\orant\database\lquser_data.ora e:\databak/H/R; alter tablespace afis_lquser end backup;alter tablespace afis_transf begin backup; !xcopy e:\orant\database\transf1_data.ora e:\databak/H/R; !xcopy e:\orant\database\transf2_data.ora e:\databak/H/R; alter tablespace afis_transf end backup;alter tablespace system begin backup; !xcopy e:\orant\database\sys1afis.ora e:\databak/H/R; alter tablespace system end backup;alter tablespace rbs begin backup; !xcopy e:\orant\database\rbs1afis.ora e:\databak/H/R; alter tablespace rbs end backup;alter tablespace usr begin backup; !xcopy e:\orant\database\usr1afis.ora e:\databak/H/R; alter tablespace usr end backup; alter tablespace temporary begin backup; !xcopy e:\orant\database\tmp1afis.ora e:\databak/H/R; alter tablespace temporary end backup; --end--bak control file --bin alter database backup controlfile to 'e:\databak\controlbinbak.000'; --ascii alter database backup controlfile to trace;alter system switch logfile; alter system switch logfile; 以下是调用以上脚本的bat,照样可以加到任务计划中,一般热备份每周备份一次就够了,主要看情况不同而定。 rem script:hotbak.bat rem creater:chenjiping rem date:4.4.2002 rem desc:backup database datafile in archiveecho del old temp backup file del f:\datatmp\*.*/q del e:\datatmp\*.*/q echo bak backup file to temp move e:\databak\*.* e:\datatmp\ move f:\Archive\*.* f:\datatmp\ echo start new bak to e:\databak svrmgrl @e:\sql\hotbak.sql
给你个复制的简略文档,可以看看,复制确实麻烦,我先给说几个缺点: 1。改表结构需要停止复制 2。大批update需要停止复制,两边一起作同样的sql 3。truncate不被传播 4。...但是,复制通过数据库链传播到另一台机器上,可用性,安全性提高的不少。 --修改CPZB数据库全局名称 connect system/xxxxxxx@cpzb; select * from global_name; alter database rename global_name to cpzb.TEST.COM.CN; --在REPL数据库上创建数据库连接。 connect system/xxxxxxx@repl; CREATE PUBLIC DATABASE LINK "CPZB.TEST.COM.CN" USING 'CPZB' ; --修改REPL数据库全局名称 connect system/xxxxxxx@repl; select * from global_name; alter database rename global_name to repl.TEST.COM.CN; --在CPZB数据库上创建数据库连接。 connect system/xxxxxxx@cpzb; CREATE PUBLIC DATABASE LINK "REPL.TEST.COM.CN" USING 'REPL' ;3、建立管理数据库复制的用户repadmin,并赋权。 --CPZB connect system/xxxxxxx@cpzb; create user repadmin identified by repadmin default tablespace users temporary tablespace temp; execute sys.dbms_defer_sys.register_propagator('repadmin'); grant execute any procedure to repadmin; execute sys.dbms_repcat_admin.grant_admin_any_repgroup('repadmin'); grant comment any table to repadmin; grant lock any table to repadmin; --REPL connect system/xxxxxxx@repl; create user repadmin identified by repadmin default tablespace users temporary tablespace temp; execute dbms_defer_sys.register_propagator('repadmin'); grant execute any procedure to repadmin; execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin'); grant comment any table to repadmin; grant lock any table to repadmin;
4、在数据库复制的用户repadmin下创建私有的数据库链接。 --CPZB connect repadmin/repadmin@cpzb; create database link repl.TEST.COM.CN connect to repadmin identified by repadmin; select * from [email protected]; 返回结果为repl.TEST.COM.CN就对了。 --REPL connect repadmin/repadmin@repl; create database link cpzb.TEST.COM.CN connect to repadmin identified by repadmin; select * from [email protected]; 返回结果为cpzb.TEST.COM.CN就对了。 5.创建或选择实现数据库复制的用户和对象,给用户赋权,数据库对象必须有主关键字。 --CPZB connect internal/oracle@cpzb; grant connect, resource to scott; grant execute on sys.dbms_defer to scott; --创建测试表test connect scott/tiger@cpzb; create table test as select * from dept; CREATE UNIQUE INDEX "SCOTT"."DEPT_X" ON "SCOTT"."TEST"("DEPTNO"); ALTER TABLE "SCOTT"."TEST" ADD(CONSTRAINT "DEPT_KEY" PRIMARY KEY("DEPTNO"));--REPL connect internal/oracle@repl; grant connect, resource to scott; grant execute on sys.dbms_defer to scott; --创建测试表test connect scott/tiger@repl; create table test as select * from dept; CREATE UNIQUE INDEX "SCOTT"."DEPT_X" ON "SCOTT"."TEST"("DEPTNO"); ALTER TABLE "SCOTT"."TEST" ADD(CONSTRAINT "DEPT_KEY" PRIMARY KEY("DEPTNO"));--为了测试扩充了DEPTNO字段的长度2->16
exp这个我正在使用,我想问oracle自己的备份恢复如何做??
如何做??
具体指点一下??
一般是空闲时做。
优点:简单。
缺点:实时性差,只能到前一天的。
2.STANDBY,这个我没做过,只知道原理。
数据库运行在归档模式下,数据库崩溃后可以采用日志恢复。
双机热备份采用的好像是这个技术,定时把一台机器的归档日志,复制到另一台机器上,定时恢复。这个是oracle工程师推荐的,应该是个不错的选择。3.复制技术。复制本来是为了实现分布式应用的技术,也可以用来复制,业务数据库和备份数据库都设置为多主复制的主战点,把要复制的对象的添加进去就可以了复制的缺点就是控制实现复杂。
rem script:hotbak.sql
rem creater:chenjiping
rem date:4.4.2002
rem desc:backup database datafile in archive--connect database
connect internal/virpure;--archive
alter system checkpoint;
alter system archive log all;
alter system switch logfile;
--start
alter tablespace afis_remote begin backup;
!xcopy e:\orant\database\remote_data1.ora e:\databak/H/R;
!xcopy e:\orant\database\remote_data2.ora e:\databak/H/R;
alter tablespace afis_remote end backup;alter tablespace afis_rmtafis begin backup;
!xcopy e:\orant\database\rmtafis_data1.ora e:\databak/H/R;
!xcopy e:\orant\database\rmtafis_data2.ora e:\databak/H/R;
alter tablespace afis_rmtafis end backup;alter tablespace afis_lquser begin backup;
!xcopy e:\orant\database\lquser_data.ora e:\databak/H/R;
alter tablespace afis_lquser end backup;alter tablespace afis_transf begin backup;
!xcopy e:\orant\database\transf1_data.ora e:\databak/H/R;
!xcopy e:\orant\database\transf2_data.ora e:\databak/H/R;
alter tablespace afis_transf end backup;alter tablespace system begin backup;
!xcopy e:\orant\database\sys1afis.ora e:\databak/H/R;
alter tablespace system end backup;alter tablespace rbs begin backup;
!xcopy e:\orant\database\rbs1afis.ora e:\databak/H/R;
alter tablespace rbs end backup;alter tablespace usr begin backup;
!xcopy e:\orant\database\usr1afis.ora e:\databak/H/R;
alter tablespace usr end backup;
alter tablespace temporary begin backup;
!xcopy e:\orant\database\tmp1afis.ora e:\databak/H/R;
alter tablespace temporary end backup;
--end--bak control file
--bin
alter database backup controlfile to 'e:\databak\controlbinbak.000';
--ascii
alter database backup controlfile to trace;alter system switch logfile;
alter system switch logfile;
以下是调用以上脚本的bat,照样可以加到任务计划中,一般热备份每周备份一次就够了,主要看情况不同而定。
rem script:hotbak.bat
rem creater:chenjiping
rem date:4.4.2002
rem desc:backup database datafile in archiveecho del old temp backup file
del f:\datatmp\*.*/q
del e:\datatmp\*.*/q
echo bak backup file to temp
move e:\databak\*.* e:\datatmp\
move f:\Archive\*.* f:\datatmp\
echo start new bak to e:\databak
svrmgrl @e:\sql\hotbak.sql
1。改表结构需要停止复制
2。大批update需要停止复制,两边一起作同样的sql
3。truncate不被传播
4。...但是,复制通过数据库链传播到另一台机器上,可用性,安全性提高的不少。
--修改CPZB数据库全局名称
connect system/xxxxxxx@cpzb;
select * from global_name;
alter database rename global_name to cpzb.TEST.COM.CN;
--在REPL数据库上创建数据库连接。
connect system/xxxxxxx@repl;
CREATE PUBLIC DATABASE LINK "CPZB.TEST.COM.CN" USING 'CPZB' ;
--修改REPL数据库全局名称
connect system/xxxxxxx@repl;
select * from global_name;
alter database rename global_name to repl.TEST.COM.CN;
--在CPZB数据库上创建数据库连接。
connect system/xxxxxxx@cpzb;
CREATE PUBLIC DATABASE LINK "REPL.TEST.COM.CN" USING 'REPL' ;3、建立管理数据库复制的用户repadmin,并赋权。
--CPZB
connect system/xxxxxxx@cpzb;
create user repadmin identified by repadmin default tablespace users temporary tablespace temp;
execute sys.dbms_defer_sys.register_propagator('repadmin');
grant execute any procedure to repadmin;
execute sys.dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
grant comment any table to repadmin;
grant lock any table to repadmin; --REPL
connect system/xxxxxxx@repl;
create user repadmin identified by repadmin default tablespace users temporary tablespace temp;
execute dbms_defer_sys.register_propagator('repadmin');
grant execute any procedure to repadmin;
execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
grant comment any table to repadmin;
grant lock any table to repadmin;
--CPZB
connect repadmin/repadmin@cpzb;
create database link repl.TEST.COM.CN connect to repadmin identified by repadmin;
select * from [email protected];
返回结果为repl.TEST.COM.CN就对了。 --REPL
connect repadmin/repadmin@repl;
create database link cpzb.TEST.COM.CN connect to repadmin identified by repadmin;
select * from [email protected];
返回结果为cpzb.TEST.COM.CN就对了。
5.创建或选择实现数据库复制的用户和对象,给用户赋权,数据库对象必须有主关键字。 --CPZB
connect internal/oracle@cpzb;
grant connect, resource to scott;
grant execute on sys.dbms_defer to scott; --创建测试表test
connect scott/tiger@cpzb;
create table test as select * from dept;
CREATE UNIQUE INDEX "SCOTT"."DEPT_X" ON "SCOTT"."TEST"("DEPTNO");
ALTER TABLE "SCOTT"."TEST" ADD(CONSTRAINT "DEPT_KEY" PRIMARY KEY("DEPTNO"));--REPL
connect internal/oracle@repl;
grant connect, resource to scott;
grant execute on sys.dbms_defer to scott; --创建测试表test
connect scott/tiger@repl;
create table test as select * from dept;
CREATE UNIQUE INDEX "SCOTT"."DEPT_X" ON "SCOTT"."TEST"("DEPTNO");
ALTER TABLE "SCOTT"."TEST" ADD(CONSTRAINT "DEPT_KEY" PRIMARY KEY("DEPTNO"));--为了测试扩充了DEPTNO字段的长度2->16
创建要复制的组scott_mg,加入数据库对象,产生对象的复制支持。自己的经验可能有不对的,你自己可以去metalink.oracle.com下载相关文档