1、项目编号：项目名称：文档编号：版本号 ：V1.0Oracle 备份策略-用友 NC 案例dataguard 异地容灾、rman 备份和逻辑备份建立日期: 2015-03-23文档控制更改记录审阅人姓名 职位 审阅签字 备注日期 作者 版本更改参考号备注余超目录.1一、NC 数据库主库创建 .11. 创建表空间和用户 .12、每个表空间增加一个表空间文件 .23. 数据库参数修改(NC 要求) .24. 添加存储过程 ANALYZE_TB； .25. 添加计划执行 ANALYZE_TB：（每周天 2：00 执行） .5二、 创建 dataguard .61. Primary 端准备 .62. S
2、tandby 端准备 .103数据库迁移 .144最大性能切换到最大可用 .155检查动作 .166. 手动切换 switchover 和 failover.188. 启动 dataguard .209. 关闭 dataguard .20三、 设置相同的服务名 .22四、 数据库的 RMAM 备份和还原 .241、RMAN 配置 .242、还原和备份脚本 .24五、 数据库的逻辑备份和还原 .271 备份 .272 还原 .29六、 启用 oracle 快闪 .29第 1 页一、NC 数据库主库创建1. 创建表空间和用户创建表空间：CREATE TABLESPACE NNC_DATA01 DA
3、TAFILE D:appAdministratororadataorcl1nnc_data01.dbf SIZE 4000M AUTOEXTEND ON NEXT 400M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ; CREATE TABLESPACE NNC_DATA02 DATAFILE D:appAdministratororadataorcl1nnc_data02.dbf SIZE 2000M AUTOEXTEND ON NEXT 200M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ; CREATE
4、TABLESPACE NNC_DATA03 DATAFILE D:appAdministratororadataorcl1nnc_data03.dbf SIZE 4000M AUTOEXTEND ON NEXT 400M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K ;CREATE TABLESPACE NNC_INDEX01 DATAFILE D:appAdministratororadataorcl1nnc_index01.dbf SIZE 3000M AUTOEXTEND ON NEXT 200M EXTENT MANAGEMENT LOCAL UN
5、IFORM SIZE 128K ; CREATE TABLESPACE NNC_INDEX02 DATAFILE D:appAdministratororadataorcl1nnc_index02.dbf SIZE 2000M AUTOEXTEND ON NEXT 200M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ; CREATE TABLESPACE NNC_INDEX03 DATAFILE D:appAdministratororadataorcl1nnc_index03.dbf SIZE 3000M AUTOEXTEND ON NEXT 400
6、M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;创建用户：CREATE USER NTXXNC57 IDENTIFIED BY NTXXNC57 DEFAULT TABLESPACE NNC_DATA01 TEMPORARY TABLESPACE temp;赋予用户角色：GRANT connect,dba to NTXXNC57;第 2 页2、每个表空间增加一个表空间文件ALTER TABLESPACE NNC_DATA01 ADD DATAFILE D:appAdministratororadataorcl1nnc_data01_2.dbf SIZE
7、 4000M AUTOEXTEND ON NEXT 400M; ALTER TABLESPACE NNC_DATA02 ADD DATAFILE D:appAdministratororadataorcl1nnc_data02_2.dbf SIZE 2000M AUTOEXTEND ON NEXT 200M; ALTER TABLESPACE NNC_DATA03 ADD DATAFILE D:appAdministratororadataorcl1nnc_data03_2.dbf SIZE 4000M AUTOEXTEND ON NEXT 400M;ALTER TABLESPACE NNC_
8、INDEX01 ADD DATAFILE D:appAdministratororadataorcl1nnc_index01_2.dbf SIZE 3000M AUTOEXTEND ON NEXT 200M; ALTER TABLESPACE NNC_INDEX02 ADD DATAFILE D:appAdministratororadataorcl1nnc_index02_2.dbf SIZE 2000M AUTOEXTEND ON NEXT 200M; ALTER TABLESPACE NNC_INDEX03 ADD DATAFILE D:appAdministratororadataor
9、cl1nnc_index03_2.dbf SIZE 3000M AUTOEXTEND ON NEXT 400M;3. 数据库参数修改(NC 要求)ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;（用户密码永不过期）alter system set open_cursors=4000 scope= spfile;alter system set optimizer_dynamic_sampling=4 scope= spfile;alter system set processes=300 scope= spfile;alter
10、system set sessions=300 scope= spfile;alter system set optimizer_index_cost_adj=40 scope= spfile;alter system set deferred_segment_creation=false scope= spfile;alter system set “_optimizer_cartesian_enabled“ =false scope= spfile;alter system set “_b_tree_bitmap_plans“=false scope= spfile;alter syste
11、m set “_optimizer_skip_scan_enabled“=false scope= spfile;第 3 页4. 添加存储过程 ANALYZE_TB；使用 NC 用户（NTXXNC57）登录数据库，添加存储过程 ANALYZE_TB 如下：CREATE OR REPLACE PROCEDURE ANALYZE_TB ASOWNER_NAME VARCHAR2(100);V_LOG INTEGER;V_SQL1 VARCHAR2(800);V_TABLENAME VARCHAR2(50);CURSOR CUR_LOG ISSELECT COUNT(*) FROM USER_TAB
12、LES WHERE TABLE_NAME = ANALYZE_LOG;-1BEGIN-DBMS_OUTPUT.ENABLE (buffer_size=100000);-1.1BEGINOPEN CUR_LOG;FETCH CUR_LOGINTO V_LOG;IF V_LOG = 0 THENEXECUTE IMMEDIATE CREATE TABLE ANALYZE_LOG (USER_NAME VARCHAR(20),OP_TIME CHAR(19) DEFAULT to_char(sysdate,yyyy-mm-dd hh24:mi:ss),ERROR_TEXT VARCHAR(200),
13、TABLE_NAME VARCHAR(40);END IF;END;SELECT USER INTO OWNER_NAME FROM DUAL;V_SQL1 := INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES ( |第 4 页OWNER_NAME | ,ANALYZE BEGIN,ALL);EXECUTE IMMEDIATE V_SQL1;sys.dbms_stats.gather_schema_stats(ownname = UPPER(OWNER_NAME),estimate_percent = 100,m
14、ethod_opt = FOR ALL INDEXED COLUMNS,cascade = TRUE);V_SQL1 := INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES ( |OWNER_NAME | ,ANALYZE END,ALL);EXECUTE IMMEDIATE V_SQL1;commit;-1.2 delete tmptb statitics and lock statisticsBEGINfor x in (select a.table_name, a.last_analyzed, b.statt
15、ype_lockedfrom user_tables a, user_tab_statistics bwhere a.temporary = Yand a.table_name = b.table_nameand (b.STATTYPE_LOCKED is null ora.last_analyzed is not null) LOOPIF x.last_analyzed IS NOT NULL THEN-delete statsdbms_stats.delete_table_stats(ownname = user,tabname = x.table_name,force = TRUE);E
16、ND IF;IF x.stattype_locked IS NULL THEN-lock statsdbms_stats.lock_table_stats(ownname = user,tabname = x.table_name);END IF;END LOOP;第 5 页end;EXCEPTIONWHEN OTHERS THENIF CUR_LOG%ISOPEN THENCLOSE CUR_LOG;END IF;commit;end;/5. 添加计划执行 ANALYZE_TB：（每周天 2：00 执行）VARIABLE JOBNO NUMBER;VARIABLE INSTNO NUMBER
17、;BEGINSELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;DBMS_JOB.SUBMIT(:JOBNO,ANALYZE_TB;,TRUNC(SYSDATE)+2+2/24,TRUNC(SYSDATE)+7+2/24,TRUE,:INSTNO);COMMIT;END;/第 6 页二、 创建 dataguard主库（Primary）：IP：10.40.1.121SID:orcl1db_unique_name：orcl1datafile: D:appAdministratororadataorcl1archivelog: D:appAdmin
18、istratorfast_recovery_areaorcl1ARCHIVELOG备库（standby）：IP: 10.40.1.122SID:orcl2db_unique_name：orcl2datafile: D:appAdministratororadataorcl1archivelog: D:appAdministratorfast_recovery_areaorcl1ARCHIVELOG1. Primary 端准备1.1. Primary开启归档SQL archive log list;数据库日志模式 存档模式自动存档 启用存档终点 USE_DB_RECOVERY_FILE_DEST
19、最早的联机日志序列 7下一个存档日志序列 9当前日志序列 9SQL show parameter DB_RECOVERY_FILE_DEST;NAME TYPE VALUE- - -db_recovery_file_dest string D:appAdministratorfast_recovery_areadb_recovery_file_dest_size big integer 4122M1.2.开启force logging#查询是否开启force loggingSQL select force_logging from v$database;FORCE_LOGGING-NO#没有则
20、开启第 7 页SQLalter database force logging;1.3.拷贝密钥文件到standby服务器D:appAdministratorproduct11.2.0dbhome_1databasePWDorcl1.ora拷贝后并改名D:appAdministratorproduct11.2.0dbhome_1databasePWDorcl2.ora1.4.配置standby redolog(先将原日志文件大小改为300M)SQLalter database add standby logfile group 4 (D:appAdministratororadataorcl1s
21、tandbyredo01.log) size 300M;SQLalter database add standby logfile group 5 (D:appAdministratororadataorcl1standbyredo02.log) size 300M;SQLalter database add standby logfile group 6 (D:appAdministratororadataorcl1standbyredo03.log) size 300M;SQLalter database add standby logfile group 7 (D:appAdminist
22、ratororadataorcl1standbyredo04.log) size 300M;1.5.配置primary初始化参数#由于spfile无法直接修改，所以先通过spfile创建pfile，再通过修改后pfile创建为spfile。SQLcreate pfile=D:appAdministratoradminorcl1pfilepfile_orcl1.ora from spfile;用记事本打开pfile_orcl1.ora以下内容是修改过的*.db_unique_name=orcl1*.log_archive_config=dg_config=(orcl1,oral2)*.log_a
23、rchive_dest_2=service=orcl2 lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=orcl2*.log_archive_dest_state_2=enable*.fal_server=orcl1*.fal_client=orcl2*.db_file_name_convert= D:appAdministratoradminorcl1, D:appAdministratoradminorcl2#主备库数据文件如果存放路径不一样，这个参数就是自动转换映射的。*.log_file_name_convert= D:appAdministratororadataorcl1, D:appAdministratororadataorcl2*.standby_file_management=auto#这个参数好啊，主库做了新增删除表空间，数据文件等动作时就会自动传输到库备。#现在来看下参数文件内容：用记事本打开pfile_orcl1.oraorcl1._db_cache_size=24561844224orcl1._java_pool_size=134217728orcl1._large_pool_size=134217728
