分类目录归档：Oracle备份恢复

oracle 10g rman自动创建数据文件

发表于 2011 年 8 月 13 日 由 惜分飞

oracle官方建议，如果修改过数据库结构后，需要立即重新备份数据库，我想通过试验验证该知识点。
试验过程是使用rman备份数据库，然后添加表空间，添加数据文件，创建表在新表空间和新数据文件上，然后关闭数据库，删除新添加的数据文件，再使用rman备份来恢复数据库。操作过程如下：

1、当前数据库表空间已经数据文件情况
SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
SQL> select file#,name from v$datafile;
FILE# NAME
———- ————————————————–
1 /opt/oracle/oradata/test/system01.dbf
2 /opt/oracle/oradata/test/undotbs01.dbf
3 /opt/oracle/oradata/test/sysaux01.dbf
4 /opt/oracle/oradata/test/users01.dbf
5 /opt/oracle/oradata/test/user32g.dbf
2、rman备份数据库
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sat Aug 13 21:44:36 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2056006906)
RMAN> backup database format ‘/tmp/%U’ plus archivelog delete input;
Starting backup at 2011-08-13 21:46:46
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=1 stamp=757977034
input archive log thread=1 sequence=4 recid=2 stamp=758152833
input archive log thread=1 sequence=5 recid=3 stamp=758320953
input archive log thread=1 sequence=6 recid=4 stamp=758321218
input archive log thread=1 sequence=7 recid=5 stamp=758412073
input archive log thread=1 sequence=8 recid=6 stamp=758574035
input archive log thread=1 sequence=9 recid=7 stamp=758665608
input archive log thread=1 sequence=10 recid=8 stamp=758757646
input archive log thread=1 sequence=11 recid=9 stamp=758844058
input archive log thread=1 sequence=12 recid=10 stamp=758930497
input archive log thread=1 sequence=13 recid=11 stamp=759027608
input archive log thread=1 sequence=14 recid=12 stamp=759102408
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:46:50
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:47:35
piece handle=/opt/oracle/flash_recovery_area/TEST/backupset/2011_08_13/o1_mf_annnn_TAG20110813T214648_74f02bg3_.bkp tag=TAG20110813T214648 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/opt/oracle/oradata/test/archivelog/1_3_757860476.dbf recid=1 stamp=757977034
archive log filename=/opt/oracle/oradata/test/archivelog/1_4_757860476.dbf recid=2 stamp=758152833
archive log filename=/opt/oracle/oradata/test/archivelog/1_5_757860476.dbf recid=3 stamp=758320953
archive log filename=/opt/oracle/oradata/test/archivelog/1_6_757860476.dbf recid=4 stamp=758321218
archive log filename=/opt/oracle/oradata/test/archivelog/1_7_757860476.dbf recid=5 stamp=758412073
archive log filename=/opt/oracle/oradata/test/archivelog/1_8_757860476.dbf recid=6 stamp=758574035
archive log filename=/opt/oracle/oradata/test/archivelog/1_9_757860476.dbf recid=7 stamp=758665608
archive log filename=/opt/oracle/oradata/test/archivelog/1_10_757860476.dbf recid=8 stamp=758757646
archive log filename=/opt/oracle/oradata/test/archivelog/1_11_757860476.dbf recid=9 stamp=758844058
archive log filename=/opt/oracle/oradata/test/archivelog/1_12_757860476.dbf recid=10 stamp=758930497
archive log filename=/opt/oracle/oradata/test/archivelog/1_13_757860476.dbf recid=11 stamp=759027608
archive log filename=/opt/oracle/oradata/test/archivelog/1_14_757860476.dbf recid=12 stamp=759102408
Finished backup at 2011-08-13 21:47:36
Starting backup at 2011-08-13 21:47:37
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/test/sysaux01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/test/undotbs01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/test/user32g.dbf
input datafile fno=00004 name=/opt/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:47:37
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:48:12
piece handle=/tmp/06mjtuvp_1_1 tag=TAG20110813T214737 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:48:14
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:48:15
piece handle=/tmp/07mjtv0s_1_1 tag=TAG20110813T214737 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2011-08-13 21:48:15
Starting backup at 2011-08-13 21:48:15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=15 recid=13 stamp=759102495
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:48:16
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:48:17
piece handle=/opt/oracle/flash_recovery_area/TEST/backupset/2011_08_13/o1_mf_annnn_TAG20110813T214815_74f050vl_.bkp tag=TAG20110813T214815 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/opt/oracle/oradata/test/archivelog/1_15_757860476.dbf recid=13 stamp=759102495
Finished backup at 2011-08-13 21:48:17
3、添加表空间/数据文件
SQL> create tablespace xff datafile ‘/opt/oracle/oradata/test/xifenfei01.dbf’ size 10m autoextend on next 10m maxsize 5G;
Tablespace created.
SQL> alter tablespace users add datafile ‘/opt/oracle/oradata/test/user02.dbf’ size 10m autoextend on next 10m maxsize 5G;
Tablespace altered.
4、创建测试表
SQL> create table chf.t_1 tablespace xff
2 as
3 select * from all_objects;
Table created.
SQL> create table chf.t_2 tablespace users
2 as
3 select * from all_objects;
Table created.
SQL> select count(*) from chf.t_1;
COUNT(*)
———-
49855
SQL> select count(*) from chf.t_2;
COUNT(*)
———-
49856
SQL> select file#,name from v$datafile;
FILE# NAME
———- ————————————————–
1 /opt/oracle/oradata/test/system01.dbf
2 /opt/oracle/oradata/test/undotbs01.dbf
3 /opt/oracle/oradata/test/sysaux01.dbf
4 /opt/oracle/oradata/test/users01.dbf
5 /opt/oracle/oradata/test/user32g.dbf
6 /opt/oracle/oradata/test/xifenfei01.dbf
7 /opt/oracle/oradata/test/user02.dbf
7 rows selected.
5、关闭数据库
SQL> shutdown abort
ORACLE instance shut down.
6、删除相关数据文件
[oracle@ECP-UC-DB1 ~]$ cd /opt/oracle/oradata/test/
[oracle@ECP-UC-DB1 test]$ ll
total 1066968
drwxr-xr-x 2 oracle oinstall 4096 Aug 13 21:48 archivelog
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control01.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control02.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control03.ctl
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:55 redo01.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:46 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:48 redo03.log
-rw-r—– 1 oracle oinstall 335552512 Aug 13 21:48 sysaux01.dbf
-rw-r—– 1 oracle oinstall 513810432 Aug 13 21:55 system01.dbf
-rw-r—– 1 oracle oinstall 20979712 Aug 13 06:00 temp01.dbf
-rw-r—– 1 oracle oinstall 26222592 Aug 13 21:55 undotbs01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 21:55 user02.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 21:55 user32g.dbf
-rw-r—– 1 oracle oinstall 5251072 Aug 13 21:55 users01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 21:55 xifenfei01.dbf
[oracle@ECP-UC-DB1 test]$ rm xifenfei01.dbf
[oracle@ECP-UC-DB1 test]$ rm user*.dbf
[oracle@ECP-UC-DB1 test]$ ll
total 1031036
drwxr-xr-x 2 oracle oinstall 4096 Aug 13 21:48 archivelog
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control01.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control02.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control03.ctl
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:55 redo01.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:46 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:48 redo03.log
-rw-r—– 1 oracle oinstall 335552512 Aug 13 21:48 sysaux01.dbf
-rw-r—– 1 oracle oinstall 513810432 Aug 13 21:55 system01.dbf
-rw-r—– 1 oracle oinstall 20979712 Aug 13 06:00 temp01.dbf
-rw-r—– 1 oracle oinstall 26222592 Aug 13 21:55 undotbs01.dbf
6、开启数据库
SQL> startup
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2082784 bytes
Variable Size 125831200 bytes
Database Buffers 75497472 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/opt/oracle/oradata/test/users01.dbf’
7、alert.log文件报错
Sat Aug 13 21:58:22 2011
ALTER DATABASE OPEN
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/opt/oracle/oradata/test/users01.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘/opt/oracle/oradata/test/user32g.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 6 – see DBWR trace file
ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘/opt/oracle/oradata/test/user02.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN…
8、登录rman
[oracle@ECP-UC-DB1 test]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sat Aug 13 22:00:03 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2056006906, not open)
9、rman还原预览
RMAN> restore datafile 4,5,6,7 preview;
Starting restore at 2011-08-13 22:01:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
data file 6 will be created automatically during restore operation
data file 7 will be created automatically during restore operation
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
4 Full 602.67M DISK 00:00:30 2011-08-13 21:47:38
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20110813T214737
Piece Name: /tmp/06mjtuvp_1_1
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——————- —-
4 Full 1177858 2011-08-13 21:47:37 /opt/oracle/oradata/test/users01.dbf
5 Full 1177858 2011-08-13 21:47:37 /opt/oracle/oradata/test/user32g.dbf
using channel ORA_DISK_1
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— ——————-
6 2.00K DISK 00:00:01 2011-08-13 21:48:16
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20110813T214815
Piece Name: /opt/oracle/flash_recovery_area/TEST/backupset/2011_08_13/o1_mf_annnn_TAG20110813T214815_74f050vl_.bkp
List of Archived Logs in backup set 6
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——————- ———- ———
1 15 1177835 2011-08-13 21:46:46 1177874 2011-08-13 21:48:15
Media recovery start SCN is 1177858
Recovery must be done beyond SCN 1178068 to clear data files fuzziness
Finished restore at 2011-08-13 22:01:30
10、还原数据文件
RMAN> restore datafile 4,5,6,7;
Starting restore at 2011-08-13 22:02:04
using channel ORA_DISK_1
creating datafile fno=6 name=/opt/oracle/oradata/test/xifenfei01.dbf
creating datafile fno=7 name=/opt/oracle/oradata/test/user02.dbf
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /opt/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /opt/oracle/oradata/test/user32g.dbf
channel ORA_DISK_1: reading from backup piece /tmp/06mjtuvp_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/06mjtuvp_1_1 tag=TAG20110813T214737
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 2011-08-13 22:02:08
11、查看恢复后的数据文件情况
[oracle@ECP-UC-DB1 ~]$ cd /opt/oracle/oradata/test
[oracle@ECP-UC-DB1 test]$ ll
total 1066968
drwxr-xr-x 2 oracle oinstall 4096 Aug 13 21:48 archivelog
-rw-r—– 1 oracle oinstall 7061504 Aug 13 22:03 control01.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 22:03 control02.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 22:03 control03.ctl
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:55 redo01.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:46 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:48 redo03.log
-rw-r—– 1 oracle oinstall 335552512 Aug 13 21:58 sysaux01.dbf
-rw-r—– 1 oracle oinstall 513810432 Aug 13 21:58 system01.dbf
-rw-r—– 1 oracle oinstall 20979712 Aug 13 06:00 temp01.dbf
-rw-r—– 1 oracle oinstall 26222592 Aug 13 21:58 undotbs01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 22:02 user02.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 22:02 user32g.dbf
-rw-r—– 1 oracle oinstall 5251072 Aug 13 22:02 users01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 22:02 xifenfei01.dbf
12、恢复数据文件
RMAN> recover datafile 4,5,6,7;
Starting recover at 2011-08-13 22:04:21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 2011-08-13 22:04:25
13、open数据库
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
14、验证rman恢复结果
[oracle@ECP-UC-DB1 test]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sat Aug 13 22:05:25 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select file#,name from v$datafile;
FILE# NAME
———- ————————————————–
1 /opt/oracle/oradata/test/system01.dbf
2 /opt/oracle/oradata/test/undotbs01.dbf
3 /opt/oracle/oradata/test/sysaux01.dbf
4 /opt/oracle/oradata/test/users01.dbf
5 /opt/oracle/oradata/test/user32g.dbf
6 /opt/oracle/oradata/test/xifenfei01.dbf
7 /opt/oracle/oradata/test/user02.dbf
7 rows selected.
SQL> select count(*) from chf.t_1;
COUNT(*)
———-
49855
SQL> select count(*) from chf.t_2;
COUNT(*)
———-
49856
注：如果在rman的还原过程中，没有自动创建数据文件，需要用命令创建alter database datafile n或者alter database datafile ‘path’，然后进行恢复
发表在 rman备份/恢复 | 评论关闭

普通库迁移至ASM存储

发表于 2011 年 8 月 1 日 由 惜分飞 
--查看当前库文件位置
SQL> select name from v$datafile
  2  union
  3  select member from v$logfile
  4  union 
  5  select name from v$controlfile
  6  union
  7  select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/tos/control01.ctl
/u01/oradata/tos/control02.ctl
/u01/oradata/tos/control03.ctl
/u01/oradata/tos/example01.dbf
/u01/oradata/tos/redo01.log
/u01/oradata/tos/redo02.log
/u01/oradata/tos/redo03.log
/u01/oradata/tos/sysaux01.dbf
/u01/oradata/tos/system01.dbf
/u01/oradata/tos/temp01.dbf
/u01/oradata/tos/undotbs01.dbf
/u01/oradata/tos/users01.dbf
/u01/oradata/tos/xff01.dbf
/u01/oradata/tos/xff02.dbf

14 rows selected.

--备份控制文件
SQL> alter database backup controlfile to '/u01/control.ctl';

Database altered.

--修改spfile中的控制文件至asm
SQL>  alter system set control_files='+DATA' scope=spfile;

System altered.

--修改db_recovery_file_dest至asm中
SQL> alter system set db_recovery_file_dest='+FLASHBACK';

System altered.

--修改归档日志至asm中
SQL> alter system set log_archive_dest_1='location=+DATA';

System altered.

--关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

--登录rman
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 08:02:37 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

--启动数据库至nomount状态
RMAN> startup nomount;

Oracle instance started

Total System Global Area     167772160 bytes

Fixed Size                     1260672 bytes
Variable Size                 67109760 bytes
Database Buffers              92274688 bytes
Redo Buffers                   7127040 bytes

--恢复控制文件
RMAN>  restore controlfile from '/u01/control.ctl';   

Starting restore at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA/tos/controlfile/current.268.754905785
Finished restore at 27-JUN-11

--打开数据库至mount状态
RMAN> alter database mount; 

database mounted
released channel: ORA_DISK_1

--copy数据文件至asm
RMAN> backup as copy database format '+DATA';

Starting backup at 27-JUN-11
Starting implicit crosscheck backup at 27-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 27-JUN-11

Starting implicit crosscheck copy at 27-JUN-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-JUN-11

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/oradata/tos/system01.dbf
output filename=+DATA/tos/datafile/system.270.754905833 tag=TAG20110627T080352 recid=2 stamp=754905928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/oradata/tos/sysaux01.dbf
output filename=+DATA/tos/datafile/sysaux.271.754905929 tag=TAG20110627T080352 recid=3 stamp=754905990
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/oradata/tos/example01.dbf
output filename=+DATA/tos/datafile/example.272.754905995 tag=TAG20110627T080352 recid=4 stamp=754906010
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/oradata/tos/undotbs01.dbf
output filename=+DATA/tos/datafile/undotbs1.273.754906021 tag=TAG20110627T080352 recid=5 stamp=754906025
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/tos/xff01.dbf
output filename=+DATA/tos/datafile/xff.274.754906027 tag=TAG20110627T080352 recid=6 stamp=754906029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u01/oradata/tos/xff02.dbf
output filename=+DATA/tos/datafile/xff.275.754906031 tag=TAG20110627T080352 recid=7 stamp=754906032
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/oradata/tos/users01.dbf
output filename=+DATA/tos/datafile/users.276.754906035 tag=TAG20110627T080352 recid=8 stamp=754906035
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 27-JUN-11

RMAN-06497: WARNING: control file is not current, control file autobackup skipped

--恢复数据库
RMAN>  recover database;

Starting recover at 27-JUN-11
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u01/oradata/tos/redo02.log
archive log filename=/u01/oradata/tos/redo02.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-11

--修改数据库中数据文件路径
RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/tos/datafile/system.270.754905833"
datafile 2 switched to datafile copy "+DATA/tos/datafile/undotbs1.273.754906021"
datafile 3 switched to datafile copy "+DATA/tos/datafile/sysaux.271.754905929"
datafile 4 switched to datafile copy "+DATA/tos/datafile/users.276.754906035"
datafile 5 switched to datafile copy "+DATA/tos/datafile/example.272.754905995"
datafile 6 switched to datafile copy "+DATA/tos/datafile/xff.274.754906027"
datafile 7 switched to datafile copy "+DATA/tos/datafile/xff.275.754906031"

--打开数据库
RMAN> alter database open resetlogs;

database opened

--添加日志文件
SQL> alter database add logfile group 4 '+DATA' size 10m;

Database altered.

SQL> alter database add logfile group 5 '+DATA' size 10m;

Database altered.

SQL> alter database add logfile group 6 '+DATA' size 10m;

Database altered.

--添加临时文件
SQL> alter tablespace temp add tempfile '+DATA' size 30m;

Tablespace altered.

--删除原临时文件
SQL> alter tablespace temp drop tempfile '/u01/oradata/tos/temp01.dbf';

Tablespace altered.

--切换日志
SQL> alter system switch logfile; --多次

System altered.

--数据文件修改写入磁盘
SQL> alter system checkpoint;

System altered.

--检查联机日志状态
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 INACTIVE
         4 INACTIVE
         5 INACTIVE
         6 CURRENT

6 rows selected.

--删除原联机日志
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

--查看修改后状态
SQL>  select name from v$datafile
  2      union
  3      select member from v$logfile
  4      union 
  5      select name from v$controlfile
  6      union
  7      select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/tos/controlfile/current.268.754905785
+DATA/tos/datafile/example.272.754905995
+DATA/tos/datafile/sysaux.271.754905929
+DATA/tos/datafile/system.270.754905833
+DATA/tos/datafile/undotbs1.273.754906021
+DATA/tos/datafile/users.276.754906035
+DATA/tos/datafile/xff.274.754906027
+DATA/tos/datafile/xff.275.754906031
+DATA/tos/onlinelog/group_4.277.754906309
+DATA/tos/onlinelog/group_5.278.754906319
+DATA/tos/onlinelog/group_6.279.754906321
+DATA/tos/tempfile/temp.280.754906369
发表在 Oracle ASM, rman备份/恢复 | 评论关闭

asm数据文件迁移(asm–>os)

发表于 2011 年 8 月 1 日 由 惜分飞 
--查看当前情况
SQL> select count(*) from hr.a;

  COUNT(*)
----------
      1580

SQL>  select name from v$DATAFILE;

NAME
-----------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DG2/tasm/datafile/xff.256.754902279

6 rows selected.

--创建目录
SQL>  create directory asmsrc as '+DG2/TASM/datafile';

Directory created.

SQL>  create directory osdesc as '/u01/oradata';

Directory created.

--表空间离线
SQL> alter tablespace xff offline;

Tablespace altered.

--数据文件迁移
SQL> begin
  2      dbms_file_transfer.copy_file('ASMSRC',
  3      'xff.256.754902279',
  4      'OSDESC',
  5      'xff_new_2.dbf');
  6  END;
  7  /

PL/SQL procedure successfully completed.

--修改数据库中datafile路径
SQL> alter database rename file
  2             '+DG2/tasm/datafile/xff.256.754902279'
  3             to '/u01/oradata/xff_new_2.dbf';

Database altered.

--表空间在线
SQL> alter tablespace xff online;

Tablespace altered.

--测试迁移结果
SQL> select name from v$DATAFILE;

NAME
-------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
/u01/oradata/xff_new_2.dbf

6 rows selected.

SQL> select count(*) from hr.a;

  COUNT(*)
----------
      1580

--删除asm中文件
ASMCMD> rm XFF.256.754902279
ORA-15032: not all alterations performed
ORA-15028: ASM file '+dg2/tasm/datafile/XFF.256.754902279' not dropped; 
currently being accessed (DBD ERROR: OCIStmtExecute)
--不能直接删除，关闭数据库后可以删除（应该是bug）
发表在 Oracle ASM, rman备份/恢复 | 评论关闭