【ASM】ASM数据文件和OS文件(FILESYSTEM)转移方法总结

简介: 【ASM】ASM数据文件和OS文件(FILESYSTEM)转移方法总结 blog文档结构图:   1  说明 本blog介绍了各种asm数据文件和filesystem文件之间的转换方法,有的记录了过程,有的没有记录过程只记录了相关代码,大家若有兴趣可以自行测试。

【ASM】ASM数据文件和OS文件(FILESYSTEM)转移方法总结

blog文档结构图:

image

 

1  说明

blog介绍了各种asm数据文件和filesystem文件之间的转换方法,有的记录了过程,有的没有记录过程只记录了相关代码,大家若有兴趣可以自行测试。

 

本次测试的表空间、磁盘组和os文件关系如下,文档中不再说明:

表空间名

磁盘组

filesystem文件

testdg

+DATA/orclasm/datafile/

/home/oracle/

 

用的数据库环境:

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

SQL>

 

 

2  os–>asm

2.1  rman + set newname+ open状态

[oracle@rhel6_lhr ~]$ more b.sql

run{

sql 'alter tablespace testdg offline immediate';

set newname for datafile 14 to'+DATA';

restore tablespace testdg;

switch datafile 14;

recover tablespace testdg;

sql 'alter tablespace testdg online';

}

[oracle@rhel6_lhr ~]$

[oracle@rhel6_lhr ~]$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 13 12:14:43 2015

 

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

 

connected to target database: ORCLASM (DBID=3424884828)

RMAN> report schema;

 

Report of database schema for database with db_unique_name ORCLASM

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    890      SYSTEM               ***     +DATA/orclasm/datafile/system.256.850260145

2    940      SYSAUX               ***     +DATA/orclasm/datafile/sysaux.257.850260145

3    190      UNDOTBS1             ***     +DATA/orclasm/datafile/undotbs1.258.851526539

4    2676     USERS                ***     +DATA/orclasm/datafile/users.259.850260147

5    345      EXAMPLE              ***     +DATA/orclasm/datafile/example.265.850260295

6    5        UNDOTBS2             ***     +DATA/orclasm/datafile/undotbs2.267.851204361

7    50       TBS_RC               ***     +DATA/orclasm/datafile/tbs_rc.268.852116523

8    100      TS_LHR               ***     +DATA/orclasm/datafile/ts_lhr.269.852632495

9    1        ENCRYPTED_TS         ***     +DATA/orclasm/datafile/encrypted_ts.272.854650889

10   100      GOLDENGATE           ***     +DATA/orclasm/datafile/goldengate.273.862829891

11   50       APP1TBS              ***     +DATA/orclasm/datafile/app1tbs.274.866911939

12   50       APP2TBS              ***     +DATA/orclasm/datafile/app2tbs.275.866912075

13   50       IDXTBS               ***     +DATA/orclasm/datafile/idxtbs.276.866912133

14   2        TESTDG               ***     /home/oracle/test1.dbf

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    237      TEMP                 32767       +DATA/orclasm/tempfile/temp.264.850260283

2    10       TEMP1                10          +DATA/orclasm/tempfile/temp1.270.853779297

RMAN> @/home/oracle/b.sql

 

RMAN> run{

2> sql 'alter tablespace testdg offline immediate';

3> set newname for datafile 14 to'+DATA';

4> restore tablespace testdg;

5> switch datafile 14;

6> recover tablespace testdg;

7> sql 'alter tablespace testdg online';

8> }

using target database control file instead of recovery catalog

sql statement: alter tablespace testdg offline immediate

 

executing command: SET NEWNAME

 

Starting restore at 13-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=14 device type=DISK

 

creating datafile file number=14 name=+DATA

restore not done; all files read only, offline, or already restored

Finished restore at 13-JAN-15

 

datafile 14 switched to datafile copy

input datafile copy RECID=3 STAMP=868882494 file name=+DATA/orclasm/datafile/testdg.277.868882493

 

Starting recover at 13-JAN-15

using channel ORA_DISK_1

 

starting media recovery

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

 

Finished recover at 13-JAN-15

 

sql statement: alter tablespace testdg online

 

RMAN> **end-of-file**

 

RMAN> report schema;

 

Report of database schema for database with db_unique_name ORCLASM

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    890      SYSTEM               ***     +DATA/orclasm/datafile/system.256.850260145

2    940      SYSAUX               ***     +DATA/orclasm/datafile/sysaux.257.850260145

3    190      UNDOTBS1             ***     +DATA/orclasm/datafile/undotbs1.258.851526539

4    2676     USERS                ***     +DATA/orclasm/datafile/users.259.850260147

5    345      EXAMPLE              ***     +DATA/orclasm/datafile/example.265.850260295

6    5        UNDOTBS2             ***     +DATA/orclasm/datafile/undotbs2.267.851204361

7    50       TBS_RC               ***     +DATA/orclasm/datafile/tbs_rc.268.852116523

8    100      TS_LHR               ***     +DATA/orclasm/datafile/ts_lhr.269.852632495

9    1        ENCRYPTED_TS         ***     +DATA/orclasm/datafile/encrypted_ts.272.854650889

10   100      GOLDENGATE           ***     +DATA/orclasm/datafile/goldengate.273.862829891

11   50       APP1TBS              ***     +DATA/orclasm/datafile/app1tbs.274.866911939

12   50       APP2TBS              ***     +DATA/orclasm/datafile/app2tbs.275.866912075

13   50       IDXTBS               ***     +DATA/orclasm/datafile/idxtbs.276.866912133

14   2        TESTDG               ***     +DATA/orclasm/datafile/testdg.277.868882493

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    237      TEMP                 32767       +DATA/orclasm/tempfile/temp.264.850260283

2    10       TEMP1                10          +DATA/orclasm/tempfile/temp1.270.853779297

 

RMAN>

 

 

2.2  rman+backup as copy+mount状态

run{

shutdown immediate;

startup mount;

backup as copy datafile 14 format '+DATA';

}

SWITCH TABLESPACE testdg TO COPY;

alter database open;

 

[oracle@rhel6_lhr ~]$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 13 15:00:19 2015

 

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

 

connected to target database: ORCLASM (DBID=3424884828)

 

RMAN> report schema;

 

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name ORCLASM

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    890      SYSTEM               ***     +DATA/orclasm/datafile/system.256.850260145

2    940      SYSAUX               ***     +DATA/orclasm/datafile/sysaux.257.850260145

3    190      UNDOTBS1             ***     +DATA/orclasm/datafile/undotbs1.258.851526539

4    2676     USERS                ***     +DATA/orclasm/datafile/users.259.850260147

5    345      EXAMPLE              ***     +DATA/orclasm/datafile/example.265.850260295

6    5        UNDOTBS2             ***     +DATA/orclasm/datafile/undotbs2.267.851204361

7    50       TBS_RC               ***     +DATA/orclasm/datafile/tbs_rc.268.852116523

8    100      TS_LHR               ***     +DATA/orclasm/datafile/ts_lhr.269.852632495

9    1        ENCRYPTED_TS         ***     +DATA/orclasm/datafile/encrypted_ts.272.854650889

10   100      GOLDENGATE           ***     +DATA/orclasm/datafile/goldengate.273.862829891

11   50       APP1TBS              ***     +DATA/orclasm/datafile/app1tbs.274.866911939

12   50       APP2TBS              ***     +DATA/orclasm/datafile/app2tbs.275.866912075

13   50       IDXTBS               ***     +DATA/orclasm/datafile/idxtbs.276.866912133

14   2        TESTDG               ***     /home/oracle/testdg.dbf

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    237      TEMP                 32767       +DATA/orclasm/tempfile/temp.264.850260283

2    10       TEMP1                10          +DATA/orclasm/tempfile/temp1.270.853779297

 

RMAN> @/home/oracle/h.sql

 

RMAN> run{

2> shutdown immediate;

3> startup mount;

4> backup as copy datafile 14 format '+DATA';

5> }

database closed

database dismounted

Oracle instance shut down

 

connected to target database (not started)

Oracle instance started

database mounted

 

Total System Global Area     375828480 bytes

 

Fixed Size                     2228464 bytes

Variable Size                268439312 bytes

Database Buffers             100663296 bytes

Redo Buffers                   4497408 bytes

 

Starting backup at 13-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=399 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00014 name=/home/oracle/testdg.dbf

output file name=+DATA/orclasm/datafile/testdg.282.868892465 tag=TAG20150113T150104 RECID=35 STAMP=868892465

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02

Finished backup at 13-JAN-15

 

Starting Control File and SPFILE Autobackup at 13-JAN-15

piece handle=/home/oracle/oracle_bk/orclasm/control_c-3424884828-20150113-09.bak comment=NONE

Finished Control File and SPFILE Autobackup at 13-JAN-15

 

RMAN> SWITCH TABLESPACE testdg TO COPY;

datafile 14 switched to datafile copy "+DATA/orclasm/datafile/testdg.282.868892465"

 

RMAN> alter database open;

database opened

 

RMAN> **end-of-file**

 

RMAN> report schema;

 

Report of database schema for database with db_unique_name ORCLASM

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    890      SYSTEM               ***     +DATA/orclasm/datafile/system.256.850260145

2    940      SYSAUX               ***     +DATA/orclasm/datafile/sysaux.257.850260145

3    190      UNDOTBS1             ***     +DATA/orclasm/datafile/undotbs1.258.851526539

4    2676     USERS                ***     +DATA/orclasm/datafile/users.259.850260147

5    345      EXAMPLE              ***     +DATA/orclasm/datafile/example.265.850260295

6    5        UNDOTBS2             ***     +DATA/orclasm/datafile/undotbs2.267.851204361

7    50       TBS_RC               ***     +DATA/orclasm/datafile/tbs_rc.268.852116523

8    100      TS_LHR               ***     +DATA/orclasm/datafile/ts_lhr.269.852632495

9    1        ENCRYPTED_TS         ***     +DATA/orclasm/datafile/encrypted_ts.272.854650889

10   100      GOLDENGATE           ***     +DATA/orclasm/datafile/goldengate.273.862829891

11   50       APP1TBS              ***     +DATA/orclasm/datafile/app1tbs.274.866911939

12   50       APP2TBS              ***     +DATA/orclasm/datafile/app2tbs.275.866912075

13   50       IDXTBS               ***     +DATA/orclasm/datafile/idxtbs.276.866912133

14   2        TESTDG               ***     +DATA/orclasm/datafile/testdg.282.868892465

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    237      TEMP                 32767       +DATA/orclasm/tempfile/temp.264.850260283

2    10       TEMP1                10          +DATA/orclasm/tempfile/temp1.270.853779297

 

RMAN>

 

 

 

2.3  dbms_file_transfer

create directory asmsrc as'+DATA/orclasm/datafile/';

create directory osdesc as '/home/oracle/';

 

alter tablespace testdg offline;

 

exec dbms_file_transfer.copy_file('osdesc','testdg.dbf','ASMSRC','testdg.dbf);

 

alter database rename file'/home/oracle/testdg.dbf'  to '+DATA/orclasm/datafile/testdg.dbf';

alter tablespace testdg online ;

 

 

2.4  RMAN convert

 

rman下:

convert datafile '/home/oracle/testdg.dbf' format '+DATA';

sql 下:

alter tablespace testdg offline ;

alter tablespace testdg rename datafile '/home/oracle/testdg.dbf' to '+DATA/orclasm/datafile/testdg.284.868895277';

recover datafile 14;

alter tablespace testdg online;

 

 

2.5  cp命令

11g的asm实例可以直接使用cp命令来实现,从asm实例的cp帮助信息上看,已经可以直接从本地数据库直接cp到远端数据库了!

alter tablespace testdg offline;

[root@rhel6_lhr ~]# cp /home/oracle/testdg.dbf /home/grid/testdg.dbf

[root@rhel6_lhr ~]# chown grid:oinstall /home/grid/testdg.dbf

[root@rhel6_lhr ~]# su - grid

ASMCMD> cp /home/grid/testdg.dbf +DATA/orclasm/datafile/testdg.dbf

copying /home/grid/testdg.dbf -> +DATA/orclasm/datafile/testdg.dbf

ASMCMD> 

alter database rename file'/home/oracle/testdg.dbf'  to '+DATA/orclasm/datafile/testdg.dbf';

alter tablespace testdg online ;

 

 

 

3  asm>os

 

3.1  dbms_file_transfer实现

SQL下执行:

create directory asmsrc as'+DATA/orclasm/datafile/';

create directory osdesc as '/home/oracle/';

 

alter tablespace testdg offline;

 

exec dbms_file_transfer.copy_file('ASMSRC','testdg.282.868891371','osdesc','testdg.dbf'); 

 

alter database rename file'+DATA/orclasm/datafile/testdg.282.868891371'  to '/home/oracle/testdg.dbf';

alter tablespace testdg online ;

 

SQL> select name,status from v$datafile;

 

NAME      STATUS

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

+DATA/orclasm/datafile/system.256.850260145      SYSTEM

+DATA/orclasm/datafile/sysaux.257.850260145      ONLINE

+DATA/orclasm/datafile/undotbs1.258.851526539      ONLINE

+DATA/orclasm/datafile/users.259.850260147      ONLINE

+DATA/orclasm/datafile/example.265.850260295      ONLINE

+DATA/orclasm/datafile/undotbs2.267.851204361      ONLINE

+DATA/orclasm/datafile/tbs_rc.268.852116523      ONLINE

+DATA/orclasm/datafile/ts_lhr.269.852632495      ONLINE

+DATA/orclasm/datafile/encrypted_ts.272.854650889      ONLINE

+DATA/orclasm/datafile/goldengate.273.862829891      ONLINE

+DATA/orclasm/datafile/app1tbs.274.866911939      ONLINE

+DATA/orclasm/datafile/app2tbs.275.866912075      ONLINE

+DATA/orclasm/datafile/idxtbs.276.866912133      ONLINE

+DATA/orclasm/datafile/testdg.282.868891371      ONLINE

SQL> edit f.sql

SQL> host more f.sql

create directory asmsrc as'+DATA/orclasm/datafile/';

create directory osdesc as '/home/oracle/';

 

alter tablespace testdg offline;

 

exec dbms_file_transfer.copy_file('ASMSRC','testdg.282.868891371','osdesc','testdg.dbf'); 

 

alter database rename file'+DATA/orclasm/datafile/testdg.282.868891371'  to '/home/oracle/testdg.dbf';

alter tablespace testdg online ;

 

SQL>

SQL> @f.sql

 

Directory created.

 

 

Directory created.

 

 

Tablespace altered.

 

 

PL/SQL procedure successfully completed.

 

 

Database altered.

 

 

Tablespace altered.

SQL> set pagesize 9999 line 9999

SQL> col name format a100

SQL> select name ,status from v$datafile;

 

NAME      STATUS

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

+DATA/orclasm/datafile/system.256.850260145      SYSTEM

+DATA/orclasm/datafile/sysaux.257.850260145      ONLINE

+DATA/orclasm/datafile/undotbs1.258.851526539      ONLINE

+DATA/orclasm/datafile/users.259.850260147      ONLINE

+DATA/orclasm/datafile/example.265.850260295      ONLINE

+DATA/orclasm/datafile/undotbs2.267.851204361      ONLINE

+DATA/orclasm/datafile/tbs_rc.268.852116523      ONLINE

+DATA/orclasm/datafile/ts_lhr.269.852632495      ONLINE

+DATA/orclasm/datafile/encrypted_ts.272.854650889      ONLINE

+DATA/orclasm/datafile/goldengate.273.862829891      ONLINE

+DATA/orclasm/datafile/app1tbs.274.866911939      ONLINE

+DATA/orclasm/datafile/app2tbs.275.866912075      ONLINE

+DATA/orclasm/datafile/idxtbs.276.866912133      ONLINE

/home/oracle/testdg.dbf      ONLINE

 

14 rows selected.

 

SQL>

 

 

 

3.2  rman + backup as copy

 

run{

shutdown immediate;

startup mount;

backup as copy  datafile 14 format '/home/oracle/testdg.dbf';

}

 

switch tablespace testdg to copy;

alter database open;

 

 

[oracle@rhel6_lhr ~]$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 13 14:20:32 2015

 

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

 

connected to target database: ORCLASM (DBID=3424884828)

 

RMAN> report schema;

 

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name ORCLASM

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    890      SYSTEM               ***     +DATA/orclasm/datafile/system.256.850260145

2    940      SYSAUX               ***     +DATA/orclasm/datafile/sysaux.257.850260145

3    190      UNDOTBS1             ***     +DATA/orclasm/datafile/undotbs1.258.851526539

4    2676     USERS                ***     +DATA/orclasm/datafile/users.259.850260147

5    345      EXAMPLE              ***     +DATA/orclasm/datafile/example.265.850260295

6    5        UNDOTBS2             ***     +DATA/orclasm/datafile/undotbs2.267.851204361

7    50       TBS_RC               ***     +DATA/orclasm/datafile/tbs_rc.268.852116523

8    100      TS_LHR               ***     +DATA/orclasm/datafile/ts_lhr.269.852632495

9    1        ENCRYPTED_TS         ***     +DATA/orclasm/datafile/encrypted_ts.272.854650889

10   100      GOLDENGATE           ***     +DATA/orclasm/datafile/goldengate.273.862829891

11   50       APP1TBS              ***     +DATA/orclasm/datafile/app1tbs.274.866911939

12   50       APP2TBS              ***     +DATA/orclasm/datafile/app2tbs.275.866912075

13   50       IDXTBS               ***     +DATA/orclasm/datafile/idxtbs.276.866912133

14   2        TESTDG               ***     +DATA/orclasm/datafile/testdg.281.868889825

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    237      TEMP                 32767       +DATA/orclasm/tempfile/temp.264.850260283

2    10       TEMP1                10          +DATA/orclasm/tempfile/temp1.270.853779297

 

RMAN> @/home/oracle/e.sql

 

RMAN> run{

2> shutdown immediate;

3> startup mount;

4> backup as copy  datafile 14 format '/home/oracle/testdg.dbf';

5> }

database closed

database dismounted

Oracle instance shut down

 

connected to target database (not started)

Oracle instance started

database mounted

 

Total System Global Area     375828480 bytes

 

Fixed Size                     2228464 bytes

Variable Size                268439312 bytes

Database Buffers             100663296 bytes

Redo Buffers                   4497408 bytes

 

Starting backup at 13-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=13 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00014 name=+DATA/orclasm/datafile/testdg.281.868889825

output file name=/home/oracle/testdg.dbf tag=TAG20150113T142110 RECID=30 STAMP=868890071

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 13-JAN-15

 

Starting Control File and SPFILE Autobackup at 13-JAN-15

piece handle=/home/oracle/oracle_bk/orclasm/control_c-3424884828-20150113-06.bak comment=NONE

Finished Control File and SPFILE Autobackup at 13-JAN-15

 

RMAN> **end-of-file**

 

RMAN> switch tablespace testdg to copy;

 

datafile 14 switched to datafile copy "/home/oracle/testdg.dbf"

 

RMAN> alter database open;

 

database opened

 

RMAN> report schema;

 

Report of database schema for database with db_unique_name ORCLASM

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    890      SYSTEM               ***     +DATA/orclasm/datafile/system.256.850260145

2    940      SYSAUX               ***     +DATA/orclasm/datafile/sysaux.257.850260145

3    190      UNDOTBS1             ***     +DATA/orclasm/datafile/undotbs1.258.851526539

4    2676     USERS                ***     +DATA/orclasm/datafile/users.259.850260147

5    345      EXAMPLE              ***     +DATA/orclasm/datafile/example.265.850260295

6    5        UNDOTBS2             ***     +DATA/orclasm/datafile/undotbs2.267.851204361

7    50       TBS_RC               ***     +DATA/orclasm/datafile/tbs_rc.268.852116523

8    100      TS_LHR               ***     +DATA/orclasm/datafile/ts_lhr.269.852632495

9    1        ENCRYPTED_TS         ***     +DATA/orclasm/datafile/encrypted_ts.272.854650889

10   100      GOLDENGATE           ***     +DATA/orclasm/datafile/goldengate.273.862829891

11   50       APP1TBS              ***     +DATA/orclasm/datafile/app1tbs.274.866911939

12   50       APP2TBS              ***     +DATA/orclasm/datafile/app2tbs.275.866912075

13   50       IDXTBS               ***     +DATA/orclasm/datafile/idxtbs.276.866912133

14   2        TESTDG               ***     /home/oracle/testdg.dbf

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    237      TEMP                 32767       +DATA/orclasm/tempfile/temp.264.850260283

2    10       TEMP1                10          +DATA/orclasm/tempfile/temp1.270.853779297

 

RMAN>

 

3.3  rman convert +open状态

使用rman的convert命令来实现,同样适用于10g

 

rman下:

convert datafile '+DATA/orclasm/datafile/testdg.277.868887219' format '/home/oracle/testdg.dbf';

 

sql下:

alter tablespace testdg offline ;

alter tablespace testdg rename datafile '+DATA/orclasm/datafile/testdg.277.868887219' to '/home/oracle/testdg1.dbf';

recover datafile 14;

alter tablespace testdg online;

 

 

[oracle@rhel6_lhr ~]$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 13 13:35:46 2015

 

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

 

connected to target database: ORCLASM (DBID=3424884828)

 

RMAN> report schema;

 

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name ORCLASM

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    890      SYSTEM               ***     +DATA/orclasm/datafile/system.256.850260145

2    940      SYSAUX               ***     +DATA/orclasm/datafile/sysaux.257.850260145

3    190      UNDOTBS1             ***     +DATA/orclasm/datafile/undotbs1.258.851526539

4    2676     USERS                ***     +DATA/orclasm/datafile/users.259.850260147

5    345      EXAMPLE              ***     +DATA/orclasm/datafile/example.265.850260295

6    5        UNDOTBS2             ***     +DATA/orclasm/datafile/undotbs2.267.851204361

7    50       TBS_RC               ***     +DATA/orclasm/datafile/tbs_rc.268.852116523

8    100      TS_LHR               ***     +DATA/orclasm/datafile/ts_lhr.269.852632495

9    1        ENCRYPTED_TS         ***     +DATA/orclasm/datafile/encrypted_ts.272.854650889

10   100      GOLDENGATE           ***     +DATA/orclasm/datafile/goldengate.273.862829891

11   50       APP1TBS              ***     +DATA/orclasm/datafile/app1tbs.274.866911939

12   50       APP2TBS              ***     +DATA/orclasm/datafile/app2tbs.275.866912075

13   50       IDXTBS               ***     +DATA/orclasm/datafile/idxtbs.276.866912133

14   2        TESTDG               ***     +DATA/orclasm/datafile/testdg.277.868887219

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    237      TEMP                 32767       +DATA/orclasm/tempfile/temp.264.850260283

2    10       TEMP1                10          +DATA/orclasm/tempfile/temp1.270.853779297

 

 

RMAN> convert datafile '+DATA/orclasm/datafile/testdg.277.868887219' format '/home/oracle/testdg.dbf';

 

Starting conversion at target at 13-JAN-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input file name=+DATA/orclasm/datafile/testdg.277.868887219

converted datafile=/home/oracle/testdg.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Finished conversion at target at 13-JAN-15

 

Starting Control File and SPFILE Autobackup at 13-JAN-15

piece handle=/home/oracle/oracle_bk/orclasm/control_c-3424884828-20150113-04.bak comment=NONE

Finished Control File and SPFILE Autobackup at 13-JAN-15

 

RMAN>

 

 

[oracle@rhel6_lhr ~]$ vi d.sql

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 13 13:38:36 2015

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SQL> host more /home/oracle/d.sql

alter tablespace testdg offline ;

alter tablespace testdg rename datafile '+DATA/orclasm/datafile/testdg.277.868887219' to '/home/oracle/testdg1.dbf';

recover datafile 14;

alter tablespace testdg online;

 

SQL> @/home/oracle/d.sql

 

Tablespace altered.

 

 

Tablespace altered.

 

Media recovery complete.

 

Tablespace altered.

 

SQL> set pagesize 9999

SQL> select name from v$datafile;

 

NAME

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

+DATA/orclasm/datafile/system.256.850260145

+DATA/orclasm/datafile/sysaux.257.850260145

+DATA/orclasm/datafile/undotbs1.258.851526539

+DATA/orclasm/datafile/users.259.850260147

+DATA/orclasm/datafile/example.265.850260295

+DATA/orclasm/datafile/undotbs2.267.851204361

+DATA/orclasm/datafile/tbs_rc.268.852116523

+DATA/orclasm/datafile/ts_lhr.269.852632495

+DATA/orclasm/datafile/encrypted_ts.272.854650889

+DATA/orclasm/datafile/goldengate.273.862829891

+DATA/orclasm/datafile/app1tbs.274.866911939

+DATA/orclasm/datafile/app2tbs.275.866912075

+DATA/orclasm/datafile/idxtbs.276.866912133

/home/oracle/testdg1.dbf

 

14 rows selected.

 

SQL>

 

 

3.4  rman + set newname + mount 状态

run{

shutdown immediate;

startup mount;

set newname for datafile 14 to '/home/oracle/testdg.dbf';

restore datafile 14;

switch datafile 14;

recover datafile 14;

alter database open;

}

 

[oracle@rhel6_lhr ~]$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 13 13:58:39 2015

 

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

 

connected to target database: ORCLASM (DBID=3424884828)

 

RMAN> report schema;

 

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name ORCLASM

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    890      SYSTEM               ***     +DATA/orclasm/datafile/system.256.850260145

2    940      SYSAUX               ***     +DATA/orclasm/datafile/sysaux.257.850260145

3    190      UNDOTBS1             ***     +DATA/orclasm/datafile/undotbs1.258.851526539

4    2676     USERS                ***     +DATA/orclasm/datafile/users.259.850260147

5    345      EXAMPLE              ***     +DATA/orclasm/datafile/example.265.850260295

6    5        UNDOTBS2             ***     +DATA/orclasm/datafile/undotbs2.267.851204361

7    50       TBS_RC               ***     +DATA/orclasm/datafile/tbs_rc.268.852116523

8    100      TS_LHR               ***     +DATA/orclasm/datafile/ts_lhr.269.852632495

9    1        ENCRYPTED_TS         ***     +DATA/orclasm/datafile/encrypted_ts.272.854650889

10   100      GOLDENGATE           ***     +DATA/orclasm/datafile/goldengate.273.862829891

11   50       APP1TBS              ***     +DATA/orclasm/datafile/app1tbs.274.866911939

12   50       APP2TBS              ***     +DATA/orclasm/datafile/app2tbs.275.866912075

13   50       IDXTBS               ***     +DATA/orclasm/datafile/idxtbs.276.866912133

14   2        TESTDG               ***     +DATA/orclasm/datafile/testdg.279.868888623

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    237      TEMP                 32767       +DATA/orclasm/tempfile/temp.264.850260283

2    10       TEMP1                10          +DATA/orclasm/tempfile/temp1.270.853779297

 

RMAN> @/home/oracle/c.sql

 

RMAN> run{

2> shutdown immediate;

3> startup mount;

4> set newname for datafile 14 to '/home/oracle/testdg.dbf';

5> restore datafile 14;

6> switch datafile 14;

7> recover datafile 14;

8> alter database open;

9> }

database closed

database dismounted

Oracle instance shut down

 

connected to target database (not started)

Oracle instance started

database mounted

 

Total System Global Area     375828480 bytes

 

Fixed Size                     2228464 bytes

Variable Size                268439312 bytes

Database Buffers             100663296 bytes

Redo Buffers                   4497408 bytes

 

executing command: SET NEWNAME

 

Starting restore at 13-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=13 device type=DISK

 

datafile 14 is already restored to file /home/oracle/testdg.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 13-JAN-15

 

datafile 14 switched to datafile copy

input datafile copy RECID=20 STAMP=868888765 file name=/home/oracle/testdg.dbf

 

Starting recover at 13-JAN-15

using channel ORA_DISK_1

 

starting media recovery

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

 

Finished recover at 13-JAN-15

 

database opened

 

RMAN> **end-of-file**

 

RMAN> report schema;

 

Report of database schema for database with db_unique_name ORCLASM

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    890      SYSTEM               ***     +DATA/orclasm/datafile/system.256.850260145

2    940      SYSAUX               ***     +DATA/orclasm/datafile/sysaux.257.850260145

3    190      UNDOTBS1             ***     +DATA/orclasm/datafile/undotbs1.258.851526539

4    2676     USERS                ***     +DATA/orclasm/datafile/users.259.850260147

5    345      EXAMPLE              ***     +DATA/orclasm/datafile/example.265.850260295

6    5        UNDOTBS2             ***     +DATA/orclasm/datafile/undotbs2.267.851204361

7    50       TBS_RC               ***     +DATA/orclasm/datafile/tbs_rc.268.852116523

8    100      TS_LHR               ***     +DATA/orclasm/datafile/ts_lhr.269.852632495

9    1        ENCRYPTED_TS         ***     +DATA/orclasm/datafile/encrypted_ts.272.854650889

10   100      GOLDENGATE           ***     +DATA/orclasm/datafile/goldengate.273.862829891

11   50       APP1TBS              ***     +DATA/orclasm/datafile/app1tbs.274.866911939

12   50       APP2TBS              ***     +DATA/orclasm/datafile/app2tbs.275.866912075

13   50       IDXTBS               ***     +DATA/orclasm/datafile/idxtbs.276.866912133

14   2        TESTDG               ***     /home/oracle/testdg.dbf

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    237      TEMP                 32767       +DATA/orclasm/tempfile/temp.264.850260283

2    10       TEMP1                10          +DATA/orclasm/tempfile/temp1.270.853779297

 

RMAN>

 

3.5  cp命令

 

alter tablespace testdg offline;

[root@rhel6_lhr ~]# su - grid

ASMCMD> cp +DATA/orclasm/datafile/testdg.dbf  /home/grid/testdg.dbf

copying +DATA/orclasm/datafile/testdg.dbf -> /home/grid/testdg.dbf

ASMCMD> 

 

[root@rhel6_lhr ~]# cp /home/grid/testdg.dbf /home/oracle/testdg.dbf

[root@rhel6_lhr ~]# chown oracle:oinstall /home/oracle/testdg.dbf

[root@rhel6_lhr ~]#

 

 

alter database rename file'+DATA/orclasm/datafile/testdg.dbf'  to '/home/oracle/testdg.dbf';

alter tablespace testdg online ;

 

4  总结

以上提供的各种办法各有优缺点,有的需要重启数据库,有的不需要,有的全在rman中执行,有的需要在sql下执行,大家需仔细领悟,如有牛人可以列出表格总结一下各种方法的优缺点我将不胜感激。





About Me

...............................................................................................................................

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群:230161599     微信群:私聊

● 联系我请加QQ好友(646634621),注明添加缘由

● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号:xiaomaimiaolhr,扫描右边的二维码加入小麦苗的QQ群,学习最实用的数据库技术。


DBA笔试面试讲解
欢迎与我联系

目录
相关文章
|
24天前
|
Shell Python
Python中os模块的常用方法和示例
在Python中,`os`模块提供了与操作系统交互的函数,用于文件和目录管理、路径操作、环境变量等。常用方法包括路径操作(如`os.path.join()`、`os.path.abspath()`)、文件和目录管理(如`os.mkdir()`、`os.remove()`)、环境变量和进程管理(如`os.getenv()`、`os.system()`)以及其他常用功能(如`os.getcwd()`、`os.urandom()`)。
24 0
|
1月前
|
存储 Java iOS开发
MacOS环境-手写操作系统-43-dir命令的实现 和 文件写入
MacOS环境-手写操作系统-43-dir命令的实现 和 文件写入
29 0
|
5月前
|
缓存 Linux Windows
初识Linux操作系统(根目录下的重要文件)(命令提示符的含义)
Linux系统基于"一切皆文件"的理念,重要文件分布在如/root(root用户目录)、/home(普通用户目录)、/etc(应用配置)、/dev(设备文件)、/boot(内核及启动文件)、/proc(动态系统信息)、/lib64(库文件)、/opt(软件存放)、/tmp(临时文件)。"[root@localhost ~]#"代表管理员在root目录,"$"代表普通用户。创建新用户用`useradd`命令。调节终端字体大小:Ctrl+Shift++增大,Ctrl+减号缩小。绝对路径从根目录开始,相对路径从当前目录开始。
|
5月前
|
Unix 关系型数据库 API
Python OS 文件/目录方法
Python OS 文件/目录方法
|
5月前
|
Java 开发工具 Android开发
详细解读Android开发DNK开发将.c文件打包成os
详细解读Android开发DNK开发将.c文件打包成os
30 0
|
5月前
|
Java API Android开发
ASM 框架:字节码操作的常见用法(生成类,修改类,方法插桩,方法注入)
ASM 框架:字节码操作的常见用法(生成类,修改类,方法插桩,方法注入)
91 0
|
5月前
|
消息中间件 Java Kafka
实时计算 Flink版操作报错合集之RocksDB在尝试打开更多文件时达到了操作系统允许的最大打开文件数限制,该怎么办
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
115 0
|
6月前
|
Oracle 关系型数据库
oracle asm 磁盘显示offline
oracle asm 磁盘显示offline
309 2
|
13天前
|
存储 Oracle 关系型数据库
数据库数据恢复—Oracle ASM磁盘组故障数据恢复案例
Oracle数据库数据恢复环境&故障: Oracle ASM磁盘组由4块磁盘组成。Oracle ASM磁盘组掉线 ,ASM实例不能mount。 Oracle数据库故障分析&恢复方案: 数据库数据恢复工程师对组成ASM磁盘组的磁盘进行分析。对ASM元数据进行分析发现ASM存储元数据损坏,导致磁盘组无法挂载。
|
6月前
|
存储 Oracle 关系型数据库
【数据库数据恢复】Oracle数据库ASM磁盘组掉线的数据恢复案例
oracle数据库ASM磁盘组掉线,ASM实例不能挂载。数据库管理员尝试修复数据库,但是没有成功。
【数据库数据恢复】Oracle数据库ASM磁盘组掉线的数据恢复案例