误操作建到文件系统的数据文件迁移到ASM一例

数据库为非归档模式。

SOLARIS 10  ORA10G ASM

 

大体：

RMAN> backup as copy datafile 5 format 'E:trans_tbsbakTEST.DBF';

 

RMAN> SQL 'ALTER DATABASE DATAFILE 5 OFFLINE';

RMAN> SWITCH DATAFILE 5 TO COPY;

RMAN> RECOVER DATAFILE 5;

RMAN> SQL 'ALTER DATABASE DATAFILE 5 ONLINE';

 

以下为操作记录：

1、shutdown

2、startup mount

3、RMAN> backup as copy datafile 5 format 'E:trans_tbsbakTEST.DBF';

　　RMAN> SQL 'ALTER DATABASE DATAFILE 5 OFFLINE';##非归档模式未open
　　RMAN> SWITCH DATAFILE 5 TO COPY;
　　RMAN> RECOVER DATAFILE 5;
　　RMAN> SQL 'ALTER DATABASE DATAFILE 5 ONLINE';##非归档模式未open

4、alter database open

 

 

### BEGIN LOG - DATE: 100607, TIME: 124828 ###

 

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 3.6709E+10 bytes
Fixed Size                  2206496 bytes
Variable Size            1.0784E+10 bytes
Database Buffers         2.5887E+10 bytes
Redo Buffers               34603008 bytes
SQL> alter database mount;

Database altered.

SQL>
SQL> !
$ rman

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jun 7 12:50:18 2010

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

RMAN> connect target /

connected to target database: NOAP (DBID=1270840870, not open)

RMAN> backup as copy datafile 5 format '+NOAPDG/noap/datafile/noap_data.dbf';

Starting backup at 07-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=785 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/export/home/oracle/product/10.2/dbs/noap_data.dbf
output filename=+NOAPDG/noap/datafile/noap_data.dbf tag=TAG20100607T125100 recid=1 stamp=721054267
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 07-JUN-10

RMAN> sql 'alter database datafile 5 offline';

sql statement: alter database datafile 5 offline
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 06/07/2010 12:51:56
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 5 offline
ORA-01145: offline immediate disallowed unless media recovery enabled

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy "+NOAPDG/noap/datafile/noap_data.dbf"

RMAN> recover datafile 5;

Starting recover at 07-JUN-10
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 07-JUN-10

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN> sql 'shutdown immediate';

sql statement: shutdown immediate
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 06/07/2010 12:54:26
RMAN-11003: failure during parse/execution of SQL statement: shutdown immediate
ORA-00900: invalid SQL statement

RMAN> exit

Recovery Manager complete.
$
$ exit

SQL> show sga

Total System Global Area 3.6709E+10 bytes
Fixed Size                  2206496 bytes
Variable Size            1.0784E+10 bytes
Database Buffers         2.5887E+10 bytes
Redo Buffers               34603008 bytes
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 3.6709E+10 bytes
Fixed Size                  2206496 bytes
Variable Size            1.0784E+10 bytes
Database Buffers         2.5887E+10 bytes
Redo Buffers               34603008 bytes
Database mounted.
SQL> !
$ rman

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jun 7 12:56:21 2010

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

RMAN> connect target /

connected to target database: NOAP (DBID=1270840870, not open)

RMAN> backup as copy datafile 6 format '+NOAPDG/noap/datafile/noap_indx.dbf';

Starting backup at 07-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=787 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/export/home/oracle/product/10.2/dbs/noap_INDX.dbf
output filename=+NOAPDG/noap/datafile/noap_indx.dbf tag=TAG20100607T125733 recid=3 stamp=721054657
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 07-JUN-10

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "+NOAPDG/noap/datafile/noap_indx.dbf"

RMAN> recover datafile 6;

Starting recover at 07-JUN-10
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 07-JUN-10

RMAN> exit

Recovery Manager complete.
$ exit

SQL> alter database open;

Database altered.

SQL>

SQL> SQL> show  parameter data

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
use_indirect_data_buffers            boolean     FALSE
SQL> ### END LOG - DATE: 100607, TIME: 130213 ###