首页 » ORACLE 9i-23ai » noarchive model,迁tablespace从ASM到Local files fs

noarchive model,迁tablespace从ASM到Local files fs

  • 2012/07/11
  • ORACLE 9i-23ai
  • 548 views
  • noarchive model,迁tablespace从ASM到Local files fs已关闭评论

利用rman 可以方便的把datafile,tablespace,database在本地文件系统和ASM之间转换

小例子
single instance 转换一个TABLESPACE 从 ASM 到 本地文件

[oracle@rac1 dbfiles]$ ora

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 9 20:50:17 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     244
Current log sequence           246

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 total_mb from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME                  TOTAL_MB
---------- -------------------------------------------------- ------------------------------ ----------
         4 +DATA/anbob/datafile/users.259.781398343           USERS                                  15
         3 +DATA/anbob/datafile/undotbs1.258.781398343        UNDOTBS1                              160
         2 +DATA/anbob/datafile/sysaux.257.781398339          SYSAUX                                750
         1 +DATA/anbob/datafile/system.256.781398335          SYSTEM                                700
         5 +DATA/anbob/datafile/mydata.270.781669087          MYDATA                                100


[oracle@rac1 dbfiles]$ rman target /           

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 9 20:54:17 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ANBOB (DBID=1158650278)

RMAN> backup as copy tablespace mydata format '/u01/app/oracle/dbfiles/mydata01.dbf';

Starting backup at 09-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/09/2012 20:55:06
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

RMAN> sql "alter tablespace mydata offline";

sql statement: alter tablespace mydata offline

RMAN> backup as copy tablespace mydata format '/u01/app/oracle/dbfiles/mydata01.dbf';

Starting backup at 09-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/anbob/datafile/mydata.270.781669087
output file name=/u01/app/oracle/dbfiles/mydata01.dbf tag=TAG20120709T205558 RECID=1 STAMP=788216170
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 09-JUL-12

RMAN> switch tablespace mydata to copy;

datafile 5 switched to datafile copy "/u01/app/oracle/dbfiles/mydata01.dbf"

RMAN> recover tablespace mydata;

Starting recover at 09-JUL-12
using channel ORA_DISK_1

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

Finished recover at 09-JUL-12

RMAN> sql "alter tablespace mydata online";

sql statement: alter tablespace mydata online

RMAN> exit

SQL> col file_name for a50
SQL> set linesize 150
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 total_mb from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME                  TOTAL_MB
---------- -------------------------------------------------- ------------------------------ ----------
         4 +DATA/anbob/datafile/users.259.781398343           USERS                                  15
         3 +DATA/anbob/datafile/undotbs1.258.781398343        UNDOTBS1                              160
         2 +DATA/anbob/datafile/sysaux.257.781398339          SYSAUX                                750
         1 +DATA/anbob/datafile/system.256.781398335          SYSTEM                                700
         5 /u01/app/oracle/dbfiles/mydata01.dbf               MYDATA                                100

[oracle@rac1 dbfiles]$ asmcmd
Connected to an idle instance.
ASMCMD> ls
ASMCMD-08102: no connection to ASM; command requires ASM to run
[oracle@rac1 ~]$ su - grid
Password: 
[grid@rac1 ~]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd DATA/anbob/datafile
ASMCMD> ls
MYDATA.270.781669087
SYSAUX.257.781398339
SYSTEM.256.781398335
UNDOTBS1.258.781398343
USERS.259.781398343
ASMCMD> rm MYDATA.270.781669087
ASMCMD> exit

test!
sys@ANBOB>create table t (id int) tablespace mydata;

Table created.
打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：