There are multiple examples: discussed here
a) Database level copy
b) TBS level copy
c) Data File level copy
d) COnvert command option
===========================================================
****** All Data Files in a DATABASE copy *****
a) First place the database in mount mode
SQL>startup mount ;
Then switch to RMAN ...
Use following rman option to move all datafiles under ASM to new diskgroup.
RMAN> backup as copy database format '+DATADG2' ;
RMAN> switch database to copy ;
*** Next SQL*plus session:
SQL>startup mount ;
SQL>recover database ;
SQL>alter database open ;
****** Tablespace level copy *****
Go to SQL*Plus Session:
.....................
create tablespace TEST_TBS1 datafile '/opt/oracle/work/TEST_TBS1.dbf' size 50M ;
create user test1 identified by test1 default tablespace test_tbs1 ;
STEP(1)
SQL> select file#, name from v$datafile ;
FILE# NAME
------ --------------------------------------------------
1 +DATADG1/dwhwfl20/datafile/system.256.587509305
2 +DATADG1/dwhwfl20/datafile/undotbs1.258.587509305
3 +DATADG1/dwhwfl20/datafile/sysaux.257.587509305
4 +DATADG1/dwhwfl20/datafile/users.259.587509305
5 +DATADG1/dwhwfl20/datafile/undotbs2.264.587509403
6 +DATADG1/dwhwfl20/datafile/bearmon.277.587648019
7 +DATADG1/dwhwfl20/datafile/tsproc.278.587649873
8 +DATADG1/dwhwfl20/datafile/tsperf.279.587650003
9 /opt/oracle/work/TEST_TBS1.dbf
9 rows selected.
col ts# for 9999
col file# for 99999
col TBS_NAME for a20
col FILE_NAME for a70
select a.ts#, a.name TBS_NAME, b.FILE#, b.name FILE_NAME from v$tablespace a, v$datafile b
where b.ts# = a.ts#
/
******************** Go to RMAN to copy the file ******** (without control file) *********
********** Below example is basically copy at TBS level .........................) *********
whibmdev51-h:/opt/oracle>>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Apr 16 21:57:06 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DWHWFL20 (DBID=2046196098)
RMAN> sql "alter tablespace TEST_TBS1 offline" ;
using target database control file instead of recovery catalog
sql statement: alter tablespace TEST_TBS1 offline
RMAN> backup as copy tablespace TEST_TBS1 format '+DATADG1' ;
Starting backup at 16-APR-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=117 instance=DWHWFL21 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00009 name=/opt/oracle/work/TEST_TBS1.dbf
output filename=+DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749 tag=TAG20060416T215908 recid=1 stamp=587944749
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-APR-06
RMAN> switch datafile 9 to copy ;
using target database control file instead of recovery catalog
datafile 9 switched to datafile copy "+DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749"
RMAN> report schema ;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 490 SYSTEM *** +DATADG1/dwhwfl20/datafile/system.256.587509305
2 35 UNDOTBS1 *** +DATADG1/dwhwfl20/datafile/undotbs1.258.587509305
3 380 SYSAUX *** +DATADG1/dwhwfl20/datafile/sysaux.257.587509305
4 5 USERS *** +DATADG1/dwhwfl20/datafile/users.259.587509305
5 25 UNDOTBS2 *** +DATADG1/dwhwfl20/datafile/undotbs2.264.587509403
6 500 BEARMON *** +DATADG1/dwhwfl20/datafile/bearmon.277.587648019
7 5120 TSPROC *** +DATADG1/dwhwfl20/datafile/tsproc.278.587649873
8 5120 TSPERF *** +DATADG1/dwhwfl20/datafile/tsperf.279.587650003
9 0 TEST_TBS1 *** +DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 22 TEMP 32767 +DATADG1/dwhwfl20/tempfile/temp.263.587509383
RMAN>
***** Now come out of
SQL> connect / as sysdba
Connected.
SQL> alter tablespace TEST_TBS1 online ;
Tablespace altered.
SQL> select * from test1.ny1 ;
************************ NEXT EXAMPLE (Sepcific Data File) *********************
2nd EXAMPLE ..SPECIFIC DATAFILE you want to move to another disk group .. *****
a) OFFLINE the tablespace
SQL> alter tablespace TEST_TBS1 offline ;
Tablespace altered.
b) In RMAN do the following .. !!
RMAN> COPY DATAFILE '+DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749' TO '+DATADG2';
Starting backup at 16-APR-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=127 instance=DWHWFL21 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00009 name=+DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749
output filename=+DATADG2/dwhwfl20/datafile/test_tbs1.256.587946275 tag=TAG20060416T222434 recid=3 stamp=587946275
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-APR-06
RMAN>
c) Switch to SQLPLUS and Rename the file to point to new location.
SQL> ALTER DATABASE RENAME FILE '+DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749' TO
2 '+DATADG2/dwhwfl20/datafile/test_tbs1.256.587946275' ;
Database altered.
SQL> RECOVER DATAFILE '+DATADG2/dwhwfl20/datafile/test_tbs1.256.587946275' ;
Media recovery complete.
SQL>
SQL> alter tablespace test_tbs1 online ;
Tablespace altered.
SQL> select * from test1.ny1 ;
SQL> select file#, name from v$datafile ;
FILE# NAME
---------- --------------------------------------------------
1 +DATADG1/dwhwfl20/datafile/system.256.587509305
2 +DATADG1/dwhwfl20/datafile/undotbs1.258.587509305
3 +DATADG1/dwhwfl20/datafile/sysaux.257.587509305
4 +DATADG1/dwhwfl20/datafile/users.259.587509305
5 +DATADG1/dwhwfl20/datafile/undotbs2.264.587509403
6 +DATADG1/dwhwfl20/datafile/bearmon.277.587648019
7 +DATADG1/dwhwfl20/datafile/tsproc.278.587649873
8 +DATADG1/dwhwfl20/datafile/tsperf.279.587650003
9 +DATADG2/dwhwfl20/datafile/test_tbs1.256.587946275
9 rows selected.
******************************* NOW to ASMCMD *************************
NOW switch to ASM instance
whibmdev51-h:/opt/oracle>>asmcmd
ASMCMD> cd +DATADG1/DWHWFL20/DATAFILE
ASMCMD> ls -ltr
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE APR 12 14:00:00 Y UNDOTBS1.258.587509305
DATAFILE UNPROT COARSE APR 12 14:00:00 Y UNDOTBS2.264.587509403
DATAFILE UNPROT COARSE APR 12 14:00:00 Y USERS.259.587509305
DATAFILE UNPROT COARSE APR 13 11:00:00 Y BEARMON.277.587648019
DATAFILE UNPROT COARSE APR 13 12:00:00 Y TSPERF.279.587650003
DATAFILE UNPROT COARSE APR 13 12:00:00 Y TSPROC.278.587649873
DATAFILE UNPROT COARSE APR 15 22:00:00 Y SYSTEM.256.587509305
DATAFILE UNPROT COARSE APR 16 20:00:00 Y SYSAUX.257.587509305
ASMCMD>
==========================================================================
Copying Tablespaces From ASM With CONVERT TABLESPACE: Example
==========================================================================
In this example, the datafiles of tablespace tbs_2 (stored in ASM) are
copied from an ASM storage location to non-ASM storage using the CONVERT TABLESPACE command:
RMAN> convert tablespace tbs_2 format '/tmp/tbs_2_%U.df';
Starting backup at 03-JUN-05
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=20 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=+DATAFILE/tbs_21.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-6_11gm2fq9.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=+DATAFILE/tbs_22.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-7_12gm2fqa.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00019 name=+DATAFILE/tbs_25.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-19_13gm2fqb.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00009 name=+DATAFILE/tbs_23.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-9_14gm2fqc.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00010 name=+DATAFILE/tbs_24.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-10_15gm2fqd.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 03-JUN-05
ANOTHER example:
Copying Datafiles From ASM Using CONVERT DATAFILE: Example
This example illustrates copying a single datafile out of ASM storage using CONVERT DATAFILE:
RMAN> convert datafile "+DATAFILE/tbs_21.f" format "/tmp/conv_df_%U";
Starting backup at 03-JUN-05
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DATAFILE/tbs_21.f
converted datafile=/tmp/conv_df_data_D-L2_I-2786301554_TS-TBS_2_FNO-6_18gm2hft
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 03-JUN-05
a) Database level copy
b) TBS level copy
c) Data File level copy
d) COnvert command option
===========================================================
****** All Data Files in a DATABASE copy *****
a) First place the database in mount mode
SQL>startup mount ;
Then switch to RMAN ...
Use following rman option to move all datafiles under ASM to new diskgroup.
RMAN> backup as copy database format '+DATADG2' ;
RMAN> switch database to copy ;
*** Next SQL*plus session:
SQL>startup mount ;
SQL>recover database ;
SQL>alter database open ;
****** Tablespace level copy *****
Go to SQL*Plus Session:
.....................
create tablespace TEST_TBS1 datafile '/opt/oracle/work/TEST_TBS1.dbf' size 50M ;
create user test1 identified by test1 default tablespace test_tbs1 ;
STEP(1)
SQL> select file#, name from v$datafile ;
FILE# NAME
------ --------------------------------------------------
1 +DATADG1/dwhwfl20/datafile/system.256.587509305
2 +DATADG1/dwhwfl20/datafile/undotbs1.258.587509305
3 +DATADG1/dwhwfl20/datafile/sysaux.257.587509305
4 +DATADG1/dwhwfl20/datafile/users.259.587509305
5 +DATADG1/dwhwfl20/datafile/undotbs2.264.587509403
6 +DATADG1/dwhwfl20/datafile/bearmon.277.587648019
7 +DATADG1/dwhwfl20/datafile/tsproc.278.587649873
8 +DATADG1/dwhwfl20/datafile/tsperf.279.587650003
9 /opt/oracle/work/TEST_TBS1.dbf
9 rows selected.
col ts# for 9999
col file# for 99999
col TBS_NAME for a20
col FILE_NAME for a70
select a.ts#, a.name TBS_NAME, b.FILE#, b.name FILE_NAME from v$tablespace a, v$datafile b
where b.ts# = a.ts#
/
******************** Go to RMAN to copy the file ******** (without control file) *********
********** Below example is basically copy at TBS level .........................) *********
whibmdev51-h:/opt/oracle>>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Apr 16 21:57:06 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DWHWFL20 (DBID=2046196098)
RMAN> sql "alter tablespace TEST_TBS1 offline" ;
using target database control file instead of recovery catalog
sql statement: alter tablespace TEST_TBS1 offline
RMAN> backup as copy tablespace TEST_TBS1 format '+DATADG1' ;
Starting backup at 16-APR-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=117 instance=DWHWFL21 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00009 name=/opt/oracle/work/TEST_TBS1.dbf
output filename=+DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749 tag=TAG20060416T215908 recid=1 stamp=587944749
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-APR-06
RMAN> switch datafile 9 to copy ;
using target database control file instead of recovery catalog
datafile 9 switched to datafile copy "+DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749"
RMAN> report schema ;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 490 SYSTEM *** +DATADG1/dwhwfl20/datafile/system.256.587509305
2 35 UNDOTBS1 *** +DATADG1/dwhwfl20/datafile/undotbs1.258.587509305
3 380 SYSAUX *** +DATADG1/dwhwfl20/datafile/sysaux.257.587509305
4 5 USERS *** +DATADG1/dwhwfl20/datafile/users.259.587509305
5 25 UNDOTBS2 *** +DATADG1/dwhwfl20/datafile/undotbs2.264.587509403
6 500 BEARMON *** +DATADG1/dwhwfl20/datafile/bearmon.277.587648019
7 5120 TSPROC *** +DATADG1/dwhwfl20/datafile/tsproc.278.587649873
8 5120 TSPERF *** +DATADG1/dwhwfl20/datafile/tsperf.279.587650003
9 0 TEST_TBS1 *** +DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 22 TEMP 32767 +DATADG1/dwhwfl20/tempfile/temp.263.587509383
RMAN>
***** Now come out of
SQL> connect / as sysdba
Connected.
SQL> alter tablespace TEST_TBS1 online ;
Tablespace altered.
SQL> select * from test1.ny1 ;
************************ NEXT EXAMPLE (Sepcific Data File) *********************
2nd EXAMPLE ..SPECIFIC DATAFILE you want to move to another disk group .. *****
a) OFFLINE the tablespace
SQL> alter tablespace TEST_TBS1 offline ;
Tablespace altered.
b) In RMAN do the following .. !!
RMAN> COPY DATAFILE '+DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749' TO '+DATADG2';
Starting backup at 16-APR-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=127 instance=DWHWFL21 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00009 name=+DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749
output filename=+DATADG2/dwhwfl20/datafile/test_tbs1.256.587946275 tag=TAG20060416T222434 recid=3 stamp=587946275
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-APR-06
RMAN>
c) Switch to SQLPLUS and Rename the file to point to new location.
SQL> ALTER DATABASE RENAME FILE '+DATADG1/dwhwfl20/datafile/test_tbs1.280.587944749' TO
2 '+DATADG2/dwhwfl20/datafile/test_tbs1.256.587946275' ;
Database altered.
SQL> RECOVER DATAFILE '+DATADG2/dwhwfl20/datafile/test_tbs1.256.587946275' ;
Media recovery complete.
SQL>
SQL> alter tablespace test_tbs1 online ;
Tablespace altered.
SQL> select * from test1.ny1 ;
SQL> select file#, name from v$datafile ;
FILE# NAME
---------- --------------------------------------------------
1 +DATADG1/dwhwfl20/datafile/system.256.587509305
2 +DATADG1/dwhwfl20/datafile/undotbs1.258.587509305
3 +DATADG1/dwhwfl20/datafile/sysaux.257.587509305
4 +DATADG1/dwhwfl20/datafile/users.259.587509305
5 +DATADG1/dwhwfl20/datafile/undotbs2.264.587509403
6 +DATADG1/dwhwfl20/datafile/bearmon.277.587648019
7 +DATADG1/dwhwfl20/datafile/tsproc.278.587649873
8 +DATADG1/dwhwfl20/datafile/tsperf.279.587650003
9 +DATADG2/dwhwfl20/datafile/test_tbs1.256.587946275
9 rows selected.
******************************* NOW to ASMCMD *************************
NOW switch to ASM instance
whibmdev51-h:/opt/oracle>>asmcmd
ASMCMD> cd +DATADG1/DWHWFL20/DATAFILE
ASMCMD> ls -ltr
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE APR 12 14:00:00 Y UNDOTBS1.258.587509305
DATAFILE UNPROT COARSE APR 12 14:00:00 Y UNDOTBS2.264.587509403
DATAFILE UNPROT COARSE APR 12 14:00:00 Y USERS.259.587509305
DATAFILE UNPROT COARSE APR 13 11:00:00 Y BEARMON.277.587648019
DATAFILE UNPROT COARSE APR 13 12:00:00 Y TSPERF.279.587650003
DATAFILE UNPROT COARSE APR 13 12:00:00 Y TSPROC.278.587649873
DATAFILE UNPROT COARSE APR 15 22:00:00 Y SYSTEM.256.587509305
DATAFILE UNPROT COARSE APR 16 20:00:00 Y SYSAUX.257.587509305
ASMCMD>
==========================================================================
Copying Tablespaces From ASM With CONVERT TABLESPACE: Example
==========================================================================
In this example, the datafiles of tablespace tbs_2 (stored in ASM) are
copied from an ASM storage location to non-ASM storage using the CONVERT TABLESPACE command:
RMAN> convert tablespace tbs_2 format '/tmp/tbs_2_%U.df';
Starting backup at 03-JUN-05
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=20 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=+DATAFILE/tbs_21.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-6_11gm2fq9.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=+DATAFILE/tbs_22.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-7_12gm2fqa.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00019 name=+DATAFILE/tbs_25.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-19_13gm2fqb.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00009 name=+DATAFILE/tbs_23.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-9_14gm2fqc.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00010 name=+DATAFILE/tbs_24.f
converted datafile=/tmp/tbs_2_data_D-L2_I-2786301554_TS-TBS_2_FNO-10_15gm2fqd.df
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 03-JUN-05
ANOTHER example:
Copying Datafiles From ASM Using CONVERT DATAFILE: Example
This example illustrates copying a single datafile out of ASM storage using CONVERT DATAFILE:
RMAN> convert datafile "+DATAFILE/tbs_21.f" format "/tmp/conv_df_%U";
Starting backup at 03-JUN-05
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DATAFILE/tbs_21.f
converted datafile=/tmp/conv_df_data_D-L2_I-2786301554_TS-TBS_2_FNO-6_18gm2hft
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 03-JUN-05
No comments:
Post a Comment