oracle 恢复用户表空间,异机恢复某用户表空间到单机

最新推荐文章于 2024-06-03 07:30:00 发布

接受现实发条熊

最新推荐文章于 2024-06-03 07:30:00 发布

阅读量275

点赞数

文章标签： oracle 恢复用户表空间

1 背景描述：

将RAC 某个表空间异机恢复至单实例，以检验数据的可用性。

1.1 RACDB数据库基本配置信息

主机节点1: rac1 操作系统：LIUNX 6.5

节点2: rac2 操作系统：LIUNX 6.5

数据库版本 Oracle11.2.0.4 RAC

OS用户名/组 uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)

数据库名 yirupay

实例名 rac1

rac2

数据库字符集 AL32UTF8

数据文件存储方式 ASM

1.2 恢复目标机的情况：

主机 rac1 操作系统：LIUNX 6.5

数据库版本 Oracle11.2.0.4 单机

OS用户名/组 uid=501(oracle) gid=500(oinstall) groups=502(dba),503(oper)

数据库名 rac

实例名 rac1

数据库字符集 AL32UTF8

数据文件存储方式 FS

2 恢复的相关信息：

2.1 确认备份的脚本是否正确备份

3 恢复的详细步骤

开始恢复：

3.1 在目标机上安装数据库软件，以及创建相应的目录

3.2 通过RMAN恢复spfile

rman target /

RMAN> startup force nomount;

RMAN> restore spfile from '/home/oracle/bk/c-96561339-20160111-00';

3.3 在目标机上修改initRACDB.ora

登录到数据库，生成pfile文件，并编辑，删除与集群即节点2相关参数，修改控制文件及其他目录路径，并根据需求创建相应目录(如闪回区、oracle基础目录)

3.4、启动到 mount

RMAN>sql 'alter database mount';

sql statement: alter database mount

released channel: ORA_DISK_1

3.5 注册备份集

catalog backuppiece '/home/oracle/bk/arch_7nqqvstc_1_1_YIRUPAY_20160110';

catalog backuppiece '/home/oracle/bk/arch_7oqqvstd_1_1_YIRUPAY_20160110';

catalog backuppiece '/home/oracle/bk/arch_7pqqvste_1_1_YIRUPAY_20160110';

catalog backuppiece '/home/oracle/bk/arch_83qr2j1c_1_1_YIRUPAY_20160111';

catalog backuppiece '/home/oracle/bk/arch_84qr2j1c_1_1_YIRUPAY_20160111';

catalog backuppiece '/home/oracle/bk/c-96561339-20160111-00';

catalog backuppiece '/home/oracle/bk/full_0_7hqqvssk_1_1_YIRUPAY_20160110';

catalog backuppiece '/home/oracle/bk/full_0_7iqqvssk_1_1_YIRUPAY_20160110';

catalog backuppiece '/home/oracle/bk/full_0_7jqqvssm_1_1_YIRUPAY_20160110';

catalog backuppiece '/home/oracle/bk/full_0_7kqqvssp_1_1_YIRUPAY_20160110';

catalog backuppiece '/home/oracle/bk/full_0_7lqqvst0_1_1_YIRUPAY_20160110';

catalog backuppiece '/home/oracle/bk/full_1_7tqr2j0t_1_1_YIRUPAY_20160111';

catalog backuppiece '/home/oracle/bk/full_1_7uqr2j0t_1_1_YIRUPAY_20160111';

catalog backuppiece '/home/oracle/bk/full_1_7vqr2j0u_1_1_YIRUPAY_20160111';

catalog backuppiece '/home/oracle/bk/full_1_80qr2j0u_1_1_YIRUPAY_20160111';

catalog backuppiece '/home/oracle/bk/full_1_81qr2j0v_1_1_YIRUPAY_20160111';

RMAN>list backup;

3.4 在目标机上用RMAN恢复

[根据备份信息，恢复数据文件及数据库

RMAN> run{

set newname for datafile 1 to '/oracle/oradata/system01.dbf';

set newname for datafile 2 to '/oracle/oradata/sysaux01.dbf';

set newname for datafile 3 to '/oracle/oradata/undotbs01.dbf';

set newname for datafile 9 to '/oracle/oradata/fcs_data01.dbf';

restore database skip tablespace ACC_DATA,ACC_IDX,BAT_DATA,BAT_IDX,CHECKACC_DATA,CHECKACC_DATA_IDX,CHECK_ACCOUNT,NOTIFY_USER_DATA,NOTIFY_USER_IDX,PER_DATA,PER_IDX,PER_LOG_DATA,PER_LOG_IDX,USERS,YRF_PAY_DATA,YRF_PAY_IDX,UNDOTBS2,FCS_IDX;

switch datafile all;}

3.5 恢复归档到指定的目录：

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 654

Next log sequence to archive 658

Current log sequence 658

SQL> alter system set log_archive_dest='/oracle/arch';

System altered.

SQL>

恢复节点1的归档日志

run{

set archivelog destination to '/oracle/arch';

restore archivelog sequence between 1753 and 1756 thread 1;

}

恢复节点2的归档日志

run{

set archivelog destination to '/oracle/arch';

restore archivelog sequence between 2045 and 2048 thread 2;}

3.6 将redolog改名并清理：

SQL>sqlplus / as sysdba

alter database rename file '+ASMDATA/yirupay/onlinelog/group_1.257.893008511' to '/oracle/oradata/log/redo1.log';

alter database rename file '+ASMDATA/yirupay/onlinelog/group_2.258.893008511' to '/oracle/oradata/log/redo2.log';

alter database rename file '+ASMDATA/yirupay/onlinelog/group_3.259.893008511' to '/oracle/oradata/log/redo3.log';

alter database rename file '+ASMDATA/yirupay/onlinelog/group_4.260.893009701' to '/oracle/oradata/log/redo4.log';

alter database rename file '+ASMDATA/yirupay/onlinelog/group_5.261.893009703' to '/oracle/oradata/log/redo5.log';

alter database rename file '+ASMDATA/yirupay/onlinelog/group_6.262.893009703' to '/oracle/oradata/log/redo6.log';

alter database rename file '+ASMDATA/yirupay/onlinelog/group_7.264.893776713' to '/oracle/oradata/log/redo7.log';

alter database rename file '+ASMDATA/yirupay/onlinelog/group_8.265.893776715' to '/oracle/oradata/log/redo8.log';

alter database clear logfile group 1;

alter database clear logfile group 2;

alter database clear logfile group 3;

alter database clear logfile group 4;

alter database clear logfile group 5;

alter database clear logfile group 6;

alter database clear logfile group 7;

alter database clear logfile group 8;

3.、修改redo路径和名字