1.通过rman copy的方法把所有数据文件备份出来

原来的数据文件：

 

   FILE_ID FILE_NAME

---------- --------------------------------------------------

         4 +DATADG/racdb/datafile/users.259.816436847

         3 +DATADG/racdb/datafile/sysaux.257.816436847

         2 +DATADG/racdb/datafile/undotbs1.258.816436847

         1 +DATADG/racdb/datafile/system.256.816436847

         5 +DATADG/racdb/datafile/undotbs2.264.816436997

         6 +DATADG/racdb/datafile/whjtbs.272.816463349

         7 +DATADG/racdb/datafile/whj02.290.817125489

 

使用RMAN copy的方法把所有数据文件备份出来

 

copy datafile 1 to '/oracle/backup/SYSTEM.dbf';

copy datafile 2 to '/oracle/backup/UNDOTBS1.dbf';

copy datafile 3 to '/oracle/backup/SYSAUX.dbf';

copy datafile 4 to '/oracle/backup/USERS.dbf';

copy datafile 5 to '/oracle/backup/UNDOTBS2.dbf';

copy datafile 6 to '/oracle/backup/WHJTBS.dbf';

copy datafile 7 to '/oracle/backup/WHJ02.dbf';

 

2. 备份归档日志和控制文件

run{

sql 'alter system archive log current';

backup format  '/oracle/backup/archive_%U' archivelog all delete input;

backup format  '/oracle/backup/control_%U' current controlfile;

}

 

备份结果如下：

[oracle@rac1 backup]$ ll

total 1394052

-rw-r----- 1 oracle oinstall    337408 Jul 14 20:43 archive_19oeoev3_1_1

-rw-r----- 1 oracle oinstall  15335424 Jul 14 20:43 control_1aoeof03_1_1

-rw-r----- 1 oracle oinstall 314580992 Jul 14 20:36 SYSAUX.dbf

-rw-r----- 1 oracle oinstall 513810432 Jul 14 20:35 SYSTEM.dbf

-rw-r----- 1 oracle oinstall 340795392 Jul 14 20:36 UNDOTBS1.dbf

-rw-r----- 1 oracle oinstall  26222592 Jul 14 20:36 UNDOTBS2.dbf

-rw-r----- 1 oracle oinstall   5251072 Jul 14 20:36 USERS.dbf

-rw-r----- 1 oracle oinstall 104865792 Jul 14 20:37 WHJ02.dbf

-rw-r----- 1 oracle oinstall 104865792 Jul 14 20:37 WHJTBS.dbf

 

 

3. 把备份文件和参数文件拷贝到目标主机

参数文件如下，把所有rac相关的路径都修改成单节点路径：

*.audit_file_dest='/oracle/admin/racdb/adump'

*.background_dump_dest='/oracle/admin/racdb/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/oracle/oradata/racdb/controlfile01.ctl','/oracle/oradata/racdb/controlfile02.ctl'

*.core_dump_dest='/oracle/admin/racdb/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='racdb'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'

racdb1.instance_number=1

racdb2.instance_number=2

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=146800640

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=444596224

racdb1.thread=1

racdb2.thread=2

*.undo_management='AUTO'

racdb1.undo_tablespace='UNDOTBS1'

racdb2.undo_tablespace='UNDOTBS2'

*.user_dump_dest='/oracle/admin/racdb/udump'

 

设置ORACLE_SID：

export ORACLE_SID=racdb

启动到NOMOUNT下：

sqlplus / as sysdba

startup nomount pfile='$ORACLE_HOME/dbs/racdb.ora'

create spfile from pfilee='$ORACLE_HOME/dbs/racdb.ora'

 

4. 恢复控制文件,把数据启动到MOUNT下

RMAN> restore controlfile from '/oracle/backup/control_1aoeof03_1_1';

 

RMAN> alter database mount;

 

5. 把rman copy的数据文件mv到/oracle/oradata/racdb/下

[oracle@rac1 backup]$ mv *.dbf /oracle/oradata/racdb/

 

6. 恢复所有的归档日志文件

查看相关的归档日志序号：

list backup of archivelog;

list backup of archivelog from sequence 8 until sequence 52;

run{

set archivelog destination to '/oracle/backup/';

restore archivelog sequence between 8 and 52 thread 1;

restore archivelog sequence between 8 and 52 thread 2;

}

7. 更新数据文件的名称

--注意文件名大小写

run{

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

catalog datafilecopy '/oracle/oradata/racdb/SYSTEM.dbf';

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

catalog datafilecopy  '/oracle/oradata/racdb/UNDOTBS1.dbf';

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

catalog datafilecopy  '/oracle/oradata/racdb/SYSAUX.dbf';

set newname for datafile 4 to '/oracle/oradata/racdb/USERS.dbf';

catalog datafilecopy '/oracle/oradata/racdb/USERS.dbf';

set newname for datafile 5 to '/oracle/oradata/racdb/UNDOTBS2.dbf';

catalog datafilecopy '/oracle/oradata/racdb/UNDOTBS2.dbf';

set newname for datafile 6 to '/oracle/oradata/racdb/WHJTBS.dbf';

catalog datafilecopy '/oracle/oradata/racdb/WHJTBS.dbf';

set newname for datafile 7 to '/oracle/oradata/racdb/WHJ02.dbf';

catalog datafilecopy  '/oracle/oradata/racdb/WHJ02.dbf';

switch datafile all;

}

8. 恢复数据库

由于是copy的数据文件所有就不用restore 数据库了，只需要恢复日志即可。

 

recover database;

 

9. Open database resetlogs

alter database open resetlogs;

报以下错误，这是oracle的一个bug，重新创建控制文件即可：

[kgeade_is_0],[],[],[]……

 

 

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 192

    MAXLOGMEMBERS 3

    MAXDATAFILES 1024

    MAXINSTANCES 32

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 ('/oracle/oradata/racdb/redo01.log') SIZE 50M,

  GROUP 2 ('/oracle/oradata/racdb/redo02.log') SIZE 50M,

  GROUP 3 ('/oracle/oradata/racdb/redo03.log') SIZE 50M

DATAFILE

  '/oracle/oradata/racdb/SYSTEM.dbf',

  '/oracle/oradata/racdb/UNDOTBS1.dbf',

  '/oracle/oradata/racdb/SYSAUX.dbf',

  '/oracle/oradata/racdb/USERS.dbf',

  '/oracle/oradata/racdb/WHJTBS.dbf',

  '/oracle/oradata/racdb/WHJ02.dbf'

CHARACTER SET ZHS16GBK

;

RECOVER DATABASE USING BACKUP CONTROLFILE;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/racdb/temp01.dbf' 100m;

 

10. 重建控制文件后，数据库还是无法open resetlogs ，需要添加一个thread 2日志组

Alter database add logfile thread 2

  GROUP 4 '/oracle/oradata/racdb/redo2_1.log'  SIZE 50M,

  GROUP 5 '/oracle/oradata/racdb/redo2_2.log'  SIZE 50M,

  GROUP 6 '/oracle/oradata/racdb/redo2_3.log'  SIZE 50M

;

 

11. 重新open resetlogs

alter database open resetlogs;

成功。

 

 

12. 为数据库添加临时表空间

 ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/racdb/temp01.dbf' size 100M;

 

13. 禁用thread 2，并删除logfile group

alter database disable thread 2;

alter database clear unarchived logfile group 4;

alter system swithc logfile;

 

alter database drop logfile group 5;

alter database drop logfile group 6;

alter database drop logfile group 4;

 

14. 清理UNDO

select thread#,status,enabled from v$thread;

select group# thread#, archived, status from v$log;

select name from v$tablespace where name like 'UNDOTBS%';

drop tablespace UNDOTBS2 including contents and datafiles;