How to Copy ASM datafile From Primary Database to Standby Database on ASM using RMAN

Copy backup of datafile from one ASM system to Another ASM system.
==============================================================
This Article has been written for Primary and Standby database Configuration .
The Steps below are useful especially when you encounter NO LOGGING/Corruption errors in standby database and want to copy the current/good backup of datafile in Primary which is on ASM to Standby database which is on ASM
For example :-
ORA-01578: ORACLE data block corrupted (file # 15, block # 834)
ORA-01110: data file 15: '+DATA/orcl/datafile/users.278.658933000' '
ORA-26040: Data block was loaded using the NOLOGGING option

Solution
There are two options to achieve the same.
Before we proceed with the steps above.We are assuming all the datafiles are on Disk group +DATA.
Assuming in this Scenario file 15 on Standby has corruption or is reported NO LOGGING operation error.
Location of file 15 in Primary database is '+DATA/orcl/datafile/users.278.658933000' .
Both standby and primary are on two separate servers.
Option 1
On Primary database :-
Step 1:- Take an copy of the file to normal file system
Rman > Copy datafile '' to '' ;

RMAN>copy datafile '+DATA/orcl/datafile/users.278.658933000' to '/tmp/backup_file.dbf'
Ftp or copy the datafile backup_file.dbf to standby server at /tmp/backup_file.dbf
On Standby database :-
Step 2:- On Standby Catalog this copy using Rman
Rman> Catalog datafilecopy '/tmp/backup_file.dbf' ;
Message Similar to the lines below would be display

cataloged datafile copy
datafile copy filename=/tmp/backup_file.dbf recid=18 stamp=658950108
Step 3:- Switch the datafile to point copy on standby
Stop the recovery
SQL> Alter database recover managed standby database cancel ;

Rman > switch datafile to COPY;
For example :-
RMAN> Switch datafile 15 to COPY;
datafile 15 switched to datafile copy "/tmp/backup_file.dbf "
Step 4 :- Now we copy this to ASM disk group on standby
RMAN> Backup as copy datafile format '';
For example :-
Rman> Backup as copy datafile 15 format '+DATA' ;
Starting backup at 01-JUL-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=21 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00015 name=/tmp/backup_file.dbf
output filename=+DATA/orcl/datafile/users.278.658933000 tag=TAG20080701T174306 r ecid=20 stamp=658950191
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 01-JUL-08

Step 5 :- Switch to point Backup copy created in ASM disk group
RMAN>switch datafile to COPY;
For example :-
Rman>Switch datafile 15 to Copy ;
Start the recovery
SQL> Alter database recover managed standby database disconnect from session ;

Or

Option 2
On Primary database :-

Step 1:- Take an copy of the file to normal file system


RMAN>copy datafile '+DATA/orcl/datafile/users.278.658933000' to '/tmp/backup_file.dbf'
Ftp or copy the datafile backup_file.dbf to standby server at /tmp/backup_file.dbf
On Stanbdy :-

Step 2

SQL> Shutdown immediate;
SQL> startup mount ( Note we are not starting the recovery)
Please note if there is dataguard broker running on the standby then it might start the recovery automatically at step 2. You would need to stop the same.

Step 3

SQL> Select name from v$datafile where file#=
This will give you the location of the file.(Note it down)

Step 4:- Catalog datafilecopy on Standby using Rman
Rman > Catalog datafilecopy '/tmp/backup_file.dbf' ;
Step 5

Rman > Connect target

Rman> copy datafile '/tmp/backup_file.dbf' to '+DATA'

Starting backup at 01-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input is copy of datafile 00015: /tmp/backup_file.dbf
output filename=+DATA/orcl/datafile/users.278.658933175 tag=TAG20080701T125709 r ===> Name reported
ecid=8 stamp=658933188
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:17
Finished backup at 01-JUL-08


This will report the new location/name of the original file 15.
In this example its +DATA/orcl/datafile/users.278.658933175

Step 5

Go to sqlplus on standby database

SQL> Select name from v$datafile where file#=
For example
SQL> Select name from v$datafile where file#=15
'+DATA/orcl/datafile/users.278.658933000'


This will show the original name/location of the datafile. We would need to rename this to the new file
name show from above rman command

We would issue rename command to point to the new location of the file.

However we would need to set standby_file_management=manual temporarily for this operation.

SQL>Alter system set standby_file_management=manual scope=spfile ;

SQL> Alter database rename file '' to 'reported in step3 after copy datafile command>'
For example

SQL> Alter database rename file '+DATA/orcl/datafile/users.278.658933000' to
'+DATA/orcl/datafile/users.278.658933175' ;


SQL> Alter system set standby_file_management=auto scope=spfile ;

Restore Archive Logs to New Location using RMAN

Oracle up to 10g

When attempting to restore archive logs that are already located on disk using RMAN you’ll receive an error if the archivelogs are stored within ASM because until Oracle 11g shipped you could not use the OS copy or move command to retrieve them.

The solution is to use RMAN to copy them from ASM to an OS filesystem:

RMAN> copy archivelog ‘+psdisk1/ORCLSID/archivelog/2008_10_30/thread_1_seq_2.245.88866622′ to ‘/tmp/oraclearchive’;

You would have to repeat this command for each archive log to be copied or try the following solution:

RMAN> change archivelog from logseq=60 until logseq=70 uncatalog;

Once the archivelos have been successfully uncataloged you are now able to successfully restore the archive logs to a new location.

RMAN> run {
set archivelog destination to ‘/tmp’;
restore archivelog from logseq=60 until logseq=70;
}

Following the successful restore of the archivelogs you can then continue to recatalog the archivelogs back into the ASM diskgroup.

Example:

RMAN> catalog archivelog ‘+psdisk1/ORCLSID/archivelog/2008_10_30/thread_1_seq.60.245.888666222′;

How To Move Datafile from a File System to ASM
Up to 10g R2:

Moving a datafile from the file system can be achieved in two ways:

1. While the database is in shutdown or mount stage
2. While the database is running with the select tablespace offline

Moving an Oracle datafile is in mount state is performed in the following way:

1. Shutdown and mount the database

SQL> shutdown immediate;
SQL> startup mount;

Check disk space in the ASM diskgroup you want to copy the datafile to:

SQL> select file#, name, (bytes/1048576) File_size_MB from v$datafile;

export ORACLE_SID=+ASM

SQL> select name, state, total_mb, free_mb from v$asm_diskgroup;

$ rman target=/
RMAN> copy datafile 4 to ‘+psdisk2′;

Update the controlfile with the new location of the datafile:

$ rman target /
RMAN> switch datafile 4 to copy;

The file is now in the new location;

SQL> select name from v$datafile;

The database may now be opened.

While the Database is Running (with the selected tablespace offline)
In order to move a datafile on a running active database, the tablespace where the datafile resides must be placed offline first. Identify the tablespace which contains the datafile and offline that tablespace.

SQL> select tablespace_name, file_name from dba_data_files where file_id=4;
SQL> alter tablespace users offline;

After you have taken the tablespace offline you can execute the same steps above for copying the datafile to the ASM Diskgroup and then put the tablespace back online.

ASMt o local file system without rman.txt

1. Log onto the target database that is local to the ASM instance as the sys user.

2. create source directory within the target database.

SQL> create or replace directory SOURCE_DIR as '+DGROUP1/V10ASM/datafile/';

Directory created.

(In this example +DGROUP1/V10ASM/datafile/ is the source directory where the datafile is located and where you wish to copy the file from.)

3. create destination directory within database.

SQL> create or replace directory ORACLE_DEST as '/restore';

Directory created.

(In this example restore is the destination directory where the datafile is to be copied to.)

4. Execute the dbms_file_transfer package.

SQL>

BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
'SOURCE_DIR', source_file_name => 'system.272.617284341',
destination_directory_object => 'ORACLE_DEST',
destination_file_name => 'system.dbf');
END;



output :
=======
create directory SOURCE_DIR as '+ORAFILES/hyttst9/datafile/';SQL>
Directory created.

SQL> create directory ORACLE_DEST as '/orabackup';
Directory created.

SQL> BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
'SOURCE_DIR', source_file_name => 'tools.dbf',
destination_directory_object => 'ORACLE_DEST',
destination_file_name => 'tool_temp.dbf');
END; 2 3 4 5 6
7
8 /

PL/SQL procedure successfully completed.


-rw-r----- 1 oracle oinstall 100671488 Jul 25 05:18 tool_temp.dbf

Clearing Flash area logs in DR environment

We need to follow following procedure to clean flash area logs ,It is applicable only when we donot have flash back restore points :
==================================================================Step 1 : Cancel the mrp process :
=======================================
alter database recover managed standby database cancel;

Step 2 : shutdown the database using shutdown immediate
========================================================
shutdown immediate;

Step3 : SQL > Start no mount

Step 4 :SQL > alter database mount standby database

Step 5 :SQL > alter database flashback off; /#this command will clear flash back logfiles #/

Step 6 :SQL > alter database flashback on ;/#Re enabling flash recover /

Step 7 : If possible restart db again not mandatory.










Example :
==========
We don't have flash back restore points here :
===============================================

INSTANCE_NAME STARTUp Time STATUS
---------------- ---------------- ------------
hytprod91 2010.10.27:03:52 MOUNTED

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

no rows selected


FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .08 0 1
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 99.91 76.65 1236

6 rows selected.

SQL> alter database recover managed standby database cancel;


Database altered.

SQL> SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2182600 bytes
Variable Size 379367992 bytes
Database Buffers 1224736768 bytes
Redo Buffers 4325376 bytes
SQL> alter database mount standby database;

Database altered.

SQL> select flashback_on from v$database;
select * from V$FLASH_RECOVERY_AREA_USAGE;
FLASHBACK_ON
------------------
YES

SQL>

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .08 0 1
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 99.91 76.61 1236

6 rows selected.

SQL> alter database flashback off;

Database altered.

SQL> SQL> SQL> SQL> SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .08 0 1
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

6 rows selected.

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database cancel;


Database altered.

SQL> SQL> SQL> SQL> alter database flashback on;

Database altered.

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .08 0 1
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG .05 0 1

6 rows selected.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> shut immediate;
ORA-01109: database not open


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

Total System Global Area 1610612736 bytes
Fixed Size 2182600 bytes
Variable Size 379367992 bytes
Database Buffers 1224736768 bytes
Redo Buffers 4325376 bytes
SQL> alter database mount standby database;

Database altered.

SQL> select flashback_on from v$database;
select * from V$FLASH_RECOVERY_AREA_USAGE;

FLASHBACK_ON
------------------
YES

SQL>
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .08 0 1
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG .05 0 1

6 rows selected.

Renaming datafile using RMAN

Rename datafile using online for non system datafile :
========================================================
sql 'alter tablespace users offline';
run {
allocate channel c1 type disk;
set newname for datafile 6 to F:\ORADATA\ORCL\USERS02_latest.DBF;
restore datafile 6;
switch datafile 6;
release channel c1;
}

Export and Import activity using mknode option

This is the procedure for taking export/import where the dump files is in zipped format.
Where the space is issue.


To create node :
=================
mknod /tmp/ p

use gunzip or uncompress as below
================================
gunzip dump.gz /tmp/ &

Above commnad will not create any file and would not take space as well.

Now you can run export and import operations.
================================================

imp username/password file=/tmp/ log=imp.log



create nod :
=================
mknod /tmp/ p
mkfifo /tmp/case1228016node p

use gunzip or uncompress as below:
=================================
gunzip dump.gz /tmp/ &
gunzip < exp.20091203.1900.exp.gz > /tmp/case1228016nod &

Above command will not create any file and would not take space as well.


================================================
Now you can run export and import operations.
================================================

imp username/password file=/tmp/ log=imp.log


file = /tmp/case1228016nod
log = imp_copy.log
buffer = 10240000
userid = system/xxxx@dbanme
commit = N
fromuser =abc
touser = abc_COPY


nohup imp parfile=copy_import.par &
username/password file=/tmp/ log=imp.log

Db clone Using RMAN

Database clone using RMAN Duplicate command :
======================================
Cretae auxiliary database service using : oradim
-------------------------------------------------------------------
oradim -new -sid aux1 -intpwd sys

Create password file for new auxiliary database :
---------------------------------------------------------------------
orapwd file=orapwdaux1 password=sys force=y

Cretae required folders for udump,bdump,cdump

Configure Listener service and tnsnames.ora for new database :
=============================================
Listener.ora file :
==============
# listener.ora Network Configuration File: F:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

CATALOG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = home-fe38bc8de0)(PORT = 1522))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = home-fe38bc8de0)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

AUX1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = home-fe38bc8de0)(PORT = 1523))
)
)


Tnsnames.ora :
===========
# tnsnames.ora Network Configuration File: F:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

CATALOG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = home-fe38bc8de0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = catalog)
)
)

AUX1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = home-fe38bc8de0)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = aux1)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = home-fe38bc8de0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = orcl)
(PRESENTATION = RO)
)
)


Cretae pfile for new auxialry database.update the correct path for auxiliary database.

Use :
====
db_file_name_convert='F:\oracle\product\10.2.0\oradata\orcl\','F:\oracle\product\10.2.0\oradata\aux1\'
log_file_name_convert='F:\oracle\product\10.2.0\oradata\orcl\','F:\oracle\product\10.2.0\oradata\aux1\'

and start the auxiliary database innomount state using new pfile.

Take the full backup of source database ; and copy them to auxiliary database device if source and clone databases are differenet in this case sorce and clone databases are in same device .
==========================
rman catalog rman/rman@catalog target sys/sys@orcl
configure chaneel 1 device type disk format 'F:\oracle\product\10.2.0\oradata\rmanbackup/%u';
backup database plus archivelog skip inaccessible;
backup as compressed backupset database;


Duplicate auxiliary database :
===============================
set ORACLE_SID=aux1
rman catalog rman/rman@catalog target sys/sys@orcl auxiliary sys/sys
configure auxiliary channel 1 device type disk format 'F:\oracle\product\10.2.0\oradata\aux1\';
connected to target database: ORCL (DBID=1218670140)
connected to recovery catalog database
connected to auxiliary database: AUX1 (not mounted)

duplicate target database to "AUX1";