ASM backup using RMAN .By using backup as copy command : -

TEST USING TRANSPORTABLE TABLESPACES, STEP BY STEP
1. Create tablespace accounts
2. Create table accounts on tablespace accounts, insert some records on it
3. Create a Directory for Data Pump and grant privileges on it to Public
4. Check that the tablespace can be part of a transportable tablespace set
5. Flush online logs to disk
6. Set tablespace accounts read only
7. Export tablespace accounts using data pump
8. Set tablespace accounts offline
9. Backup the tablespace using Rman backup as copy set keep until sysdate + 2 years
10. Drop tablespace accounts including datafiles
11. Confirm that you cannot access the table on the dropped tablespace
Restore tablespace steps
12. Restore the datafile backup to ASM
13. Import tablespace accounts back into the database using data pump
14. Set tablespace accounts online and check the control table

1. Create tablespace accounts
SQL> create tablespace accounts;
Tablespace created.
SQL> select file_name from dba_data_files where tablespace_name='ACCOUNTS';
FILE_NAME
-----------------------------------------------

2. Create table accounts on tablespace accounts, insert some records on it
SQL> conn avargas/oracle
Connected.
SQL> create table accounts tablespace accounts as select * from dba_users;
Table created.
SQL> insert into accounts select * from accounts;
15 rows created.
SQL> /
30 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from accounts;
COUNT(*)
----------
60

3. Create a Directory for Data Pump and grant privileges on it to Public
SQL> create or replace directory XTTS as '/oradisk/app01/oracle/scripts/av';
Directory created.  
SQL> grant read, write on directory XTTS to public;
Grant succeeded.

4. Check that the tablespace can be part of a transportable tablespace set
SQL> EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('accounts',true);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected

5. Flush online logs to disk
SQL> alter system archive log current;
System altered.

6. Set tablespace accounts read only
SQL> alter tablespace accounts read only;
Tablespace altered.

7. Export tablespace accounts using data pump
{oracle} /oradisk/app01/oracle/scripts/av [pollux.com] > expdp system/oracle
dumpfile=xtts.dmp directory=XTTS transport_tablespaces=accounts
Export: Release 10.2.0.3.0 - Production on Thursday, 03 January, 2008 21:34:20
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

8. Set tablespace accounts offline
SQL> alter tablespace accounts offline;
Tablespace altered.

9. Backup the tablespace using Rman backup as copy
Note that in order to be able to setup the keep time for a period longer than the setup of the Flash Recovery Area
we must make this backup outside the Flash Recovery Area
RMAN> backup as copy datafile '+DB1DATADG/redx/datafile/accounts.287.642998077
2> tag accounts_DF_CP
3> format '/oradisk/app01/oracle/scripts/av/%U'
4> keep until time='sysdate + 730' nologs;
Starting backup at 03/01/2008 21:44:33
using channel ORA_DISK_1
backup will be obsolete on

10. Drop tablespace accounts
SQL> drop tablespace accounts including contents and datafiles;
Tablespace dropped.

11. Confirm that you cannot access the table on the dropped tablespace
SQL> select count(*) from accounts;
select count(*) from accounts
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select tablespace_name from dba_tablespaces where tablespace_name='ACCOUNTS';

12. Restore the datafile backup to ASM
On this step we need to use the Rman copy command instead of restore. Restore do require that the tablespace
exist, and we did drop to it as part of the TTS procedure.
Take note of the output filename given by Rman; you will need to use this name on the next step
RMAN> copy datafilecopy '/oradisk/app01/oracle/scripts/av/data_D-REDX_I-
2305343033_TS-ACCOUNTS_FNO-6_0pj58r62' to '+DB1DATADG';
Starting backup at 03/01/2008 22:15:35
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input is copy of datafile 00006: /oradisk/app01/oracle/scripts/av/data_DREDX_
I-2305343033_TS-ACCOUNTS_FNO-6_0pj58r62
output filename=+DB1DATADG/redx/datafile/accounts.287.643068939
tag=ACCOUNTS_DF_CP recid=21 stamp=643068957
channel ORA_DISK_1: datafile copy complete,

13. Import tablespace accounts back into the database using data pump
[pollux.com] > impdp system/oracle dumpfile=xtts.dmp directory=XTTS
transport_datafiles='+DB1DATADG/redx/datafile/accounts.287.643068939'
Import: Release 10.2.0.3.0 - Production on Thursday, 03 January, 2008 22:18:14
Copyright (c) 2003, 2005, Oracle. All rights reserved.

14. Set tablespace accounts online and check the control table
On the previous check our current log sequence was 27, now we got to sequence 29. This check is made to be
sure that despite the database advanced its scn we will not require to do recover of the offline tablespace when
seting it online again.
SQL> alter tablespace accounts online;
Tablespace altered.
SQL> select count(*) from avargas.accounts

Apps Dba Free Lance

Sukhwinder Singh