一、数据库全备
RUN {
ALLOCATE CHANNEL ch00 DEVICE TYPE disk;
ALLOCATE CHANNEL ch01 DEVICE TYPE disk;
backup as compressed backupset database filesperset 5 format '/apps/oracle_backup_20141209/bk_%d_%T%s_%p' ;
backup current controlfile format '/apps/oracle_backup_20141209/ctl_%d_%T_%s';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}
数据库备份采用压缩备份，压缩比大概为8:1,250多G的数据库备份出来30G。
二、数据库恢复
2.1.准备工作
拷贝备份文件到目标服务器：scp oracle_backup_20141209/ 10.230.35.177:/apps/(注：生产环境备份目录和测试环境备份目录最好一致，这样省许多不必要的麻烦)
2.2.创建相关目录
mkdir -p /app/oracle/admin/orcl/adump
mkdir -p /app/oracle/admin/orcl/bdump
mkdir -p /app/oracle/admin/orcl/cdump
mkdir -p /app/oracle/admin/orcl/udump
mkdir -p /app/oracle/admin/orcl/pfile
mkdir -p /app/oracle/controlfile
mkdir -p /app/oracle/arch
mkdir -p /app/oracle/oradata/orcl
mkdir -p /app/oracle/tempfile
2.3.准备参数文件
使用oracle用户
vi initorcl.ora
*.audit_file_dest='/app/oracle/admin/orcl/adump'
*.background_dump_dest='/app/oracle/admin/orcl/bdump'
*.cluster_database=false
*.compatible='10.2.0.5.0'
*.control_files='/app/oracle/controlfile/orcl_control1','/app/oracle/controlfile/orcl_control2','/app/oracle/controlfile/orcl_control3'
*.core_dump_dest='/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.job_queue_processes=10
*.log_archive_dest_1='location=/app/oracle/arch'
*.open_cursors=300
*.pga_aggregate_target=263997286
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.sga_max_size=1244245094
*.sga_target=1244245094
*.undo_management='AUTO'
*.undo_retention=0
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/app/oracle/admin/orcl/udump'
2.4.启动实例
$ export ORACLE_SID=orcl
sqlplus / as sysdba
startup nomount pfile='/app/oracle/initorcl.ora'
2.5.恢复控制文件
rman target /
set dbid=1340406187
restore controlfile from '/apps/oracle_backup_20141209/ctl_ORCL_20141209_5109';
2.6.恢复数据库
alter database mount;
run {
ALLOCATE CHANNEL ch00 DEVICE TYPE disk;
ALLOCATE CHANNEL ch01 DEVICE TYPE disk;
ALLOCATE CHANNEL ch02 DEVICE TYPE disk;
ALLOCATE CHANNEL ch03 DEVICE TYPE disk;
set newname for datafile '+ORCLDATA/orcl/datafile/system.256.812625405' to '/app/oracle/oradata/orcl/system.256.812625405'
set newname for datafile '+ORCLDATA/orcl/datafile/undotbs1.258.812625405' to '/app/oracle/oradata/orcl/undotbs1.258.812625405'
set newname for datafile '+ORCLDATA/orcl/datafile/sysaux.257.812625405' to '/app/oracle/oradata/orcl/sysaux.257.812625405'
set newname for datafile '+ORCLDATA/orcl/datafile/users.259.812625405' to '/app/oracle/oradata/orcl/users.259.812625405'
set newname for datafile '+ORCLDATA/orcl/datafile/undotbs2.264.812625489' to '/app/oracle/oradata/orcl/undotbs2.264.812625489'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_data_01.dbf' to '/app/oracle/oradata/orcl/haecm_data_01.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_index_01.dbf' to '/app/oracle/oradata/orcl/haecm_index_01.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/dms_data_01.dbf' to '/app/oracle/oradata/orcl/dms_data_01.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/dms_index_01.dbf' to '/app/oracle/oradata/orcl/dms_index_01.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_data_02.dbf' to '/app/oracle/oradata/orcl/haecm_data_02.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_index_02.dbf' to '/app/oracle/oradata/orcl/haecm_index_02.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_data_03.dbf' to '/app/oracle/oradata/orcl/haecm_data_03.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_index_03.dbf' to '/app/oracle/oradata/orcl/haecm_index_03.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/system_01.dbf' to '/app/oracle/oradata/orcl/system_01.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/dms_data_02.dbf' to '/app/oracle/oradata/orcl/dms_data_02.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_data_04.dbf' to '/app/oracle/oradata/orcl/haecm_data_04.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/sysaux_02.dbf' to '/app/oracle/oradata/orcl/sysaux_02.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/dms_index_02.dbf' to '/app/oracle/oradata/orcl/dms_index_02.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_data_05.dbf' to '/app/oracle/oradata/orcl/haecm_data_05.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_index_04.dbf' to '/app/oracle/oradata/orcl/haecm_index_04.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_data_06.dbf' to '/app/oracle/oradata/orcl/haecm_data_06.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_index_05.dbf' to '/app/oracle/oradata/orcl/haecm_index_05.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/dms_data_03.dbf' to '/app/oracle/oradata/orcl/dms_data_03.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_data_07.dbf' to '/app/oracle/oradata/orcl/haecm_data_07.dbf'
set newname for datafile '+ORCLDATA/orcl/datafile/haecm_index_06.dbf' to '/app/oracle/oradata/orcl/haecm_index_06.dbf'
restore database;
switch datafile all;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
}
recover database;
会报RMAN-06025: no backup of log thread 1 seq 23672 lowscn 1173065498 found to restore
恢复多个归档日志
run {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' connect 'sys/xxxx@orcl1';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' connect 'sys/xxxx@orcl2';
SET ARCHIVELOG DESTINATION TO '/rac_arch1';
RESTORE ARCHIVELOG SEQUENCE between 5550 and 5564 thread 2;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}
恢复单个归档日志23672
run {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' connect 'sys/xxxx@orcl1';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' connect 'sys/xxxx@orcl2';
SET ARCHIVELOG DESTINATION TO '/rac_arch1';
RESTORE ARCHIVELOG SEQUENCE 21519 thread 1;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}
补充：删除归档尽量不用操作系统命令rm（若用rm需要在rman中执行crosscheck archivelog all;）要用delete noprompt archivelog 归档的路径和名字
delete noprompt archivelog '/rac_arch2/2_21519_812625454.dbf';
delete noprompt archivelog '/rac_arch2/2_21525_812625454.dbf';
把归档放在pfile文件指定的归档路径中，然后rman下注册归档：catalog start with '/oracle/arch';
执行recover database;
归档都有后再执行recover database;
RENAME REDO
alter database rename file '+ORCLDATA/orcl/onlinelog/group_11.log' to '/app/oracle/oradata/orcl/redo1.log';
alter database rename file '+ORCLDATA/orcl/onlinelog/group_12.log' to '/app/oracle/oradata/orcl/redo2.log';
alter database rename file '+ORCLDATA/orcl/onlinelog/group_21.log' to '/app/oracle/oradata/orcl/redo3.log';
alter database rename file '+ORCLDATA/orcl/onlinelog/group_31.log' to '/app/oracle/oradata/orcl/redo4.log';
alter database rename file '+ORCLDATA/orcl/onlinelog/group_41.log' to '/app/oracle/oradata/orcl/redo5.log';
alter database rename file '+ORCLDATA/orcl/onlinelog/group_51.log' to '/app/oracle/oradata/orcl/redo6.log';
alter database rename file '+ORCLDATA/orcl/onlinelog/group_52.log' to '/app/oracle/oradata/orcl/redo7.log';
alter database rename file '+ORCLDATA/orcl/onlinelog/group_61.log' to '/app/oracle/oradata/orcl/redo8.log';
alter database rename file '+ORCLDATA/orcl/onlinelog/group_22.log' to '/app/oracle/oradata/orcl/redo9.log';
alter database rename file '+ORCLDATA/orcl/onlinelog/group_32.log' to '/app/oracle/oradata/orcl/redo10.log';
alter database rename file '+ORCLDATA/orcl/onlinelog/group_42.log' to '/app/oracle/oradata/orcl/redo11.log';
alter database rename file '+ORCLDATA/orcl/onlinelog/group_62.log' to '/app/oracle/oradata/orcl/redo12.log';
2.7.打开数据库
SQL> alter database open resetlogs;
若不执行alter database rename file '+ORCLDATA/orcl/onlinelog/group_42.log' to '/app/oracle/oradata/orcl/redo11.log'; 这些语句
会报错如下：
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00349: failure obtaining block size for
'+ORCLDATA/orcl/onlinelog/group_42.log'
2.8.数据库整理
SQL> set linesize 200 pagesize 200
SQL> select GROUP#,THREAD#,STATUS ,ARCHIVED from v$log;
SQL> alter database disable thread 2 ;
SQL> alter system archive log current;
SQL> select GROUP#,THREAD#,STATUS ,ARCHIVED from v$log;
SQL> alter database drop logfile group 3; --根据实际情况调整
SQL> select GROUP#,THREAD#,STATUS ,ARCHIVED from v$log;
alter database rename file '+ORCLDATA/orcl/tempfile/temp.263.812625459' to '/app/oracle/tempfile/temp1.dbf';
删除thread 2 的日志组如：alter database drop logfile group 2;
创建监听 、tnsnames.ora
