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

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

Requirement is to copy one datafile (file#20)from primary to standby.

We encountered 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
There are three options to achieve the same.

Option 1

On Primary database :-

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

RMAN > Copy datafile ''  to '' ;

for example

RMAN>copy datafile '+DATA/orcl/datafile/users.20.658960000'  to '/u01/stage/tmp/backup_file_20.dbf'

transfer the file backup_file_20.dbf to standby

use Ftp or copy (scp) to transfer the datafile  backup_file_20.dbf  to standby server at
/u01/stage/tmp/backup_file_20.dbf

On Standby database :-

Step 2:- On Standby Catalog this copy using Rman

RMAN> Catalog datafilecopy '/u01/stage/tmp/backup_file_20.dbf' ;

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 20 to COPY;
datafile 20 switched to datafile copy "/u01/stage/tmp/backup_file_20.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 20 format '+DATA' ;
.....................................
.....................................
output filename=+DATA/orcl/datafile/users.20.658960000 tag=TAG20080701T174316 r ecid=20 stamp=658953191

Step 5 :- Switch to point Backup copy created in ASM disk group

RMAN>switch datafile  to COPY;

For example :-

Rman>Switch datafile 20 to Copy ;


Option 2

On Primary database :-

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


RMAN>copy datafile '+DATA/orcl/datafile/users.20.658960000'  to '/u01/stage/tmp/backup_file_20.dbf'

transfer the file backup_file_20.dbf to standby

Ftp or copy (scop) the datafile backup_file.dbf  to standby server at

/u01/stage/tmp/backup_file_20.dbf

On Standby database :-

Step 2

SQL> Shutdown immediate;

SQL> startup mount

Ensure recovery is stopped. Ensure no MRP process is listed in below query. If recovery is in progress stop it

SQL> select PROCESS,STATUS from v$managed_standby
  2  ;
PROCESS                     STATUS
--------------------------- --------------------
ARCH                        CLOSING
ARCH                        CLOSING
ARCH                        CONNECTED
ARCH                        CLOSING
RFS                         IDLE
RFS                         IDLE
RFS                         IDLE
RFS                         IDLE
RFS                         IDLE
RFS                         IDLE
10 rows selected.

 to stop recovery issue below command on standby

SQL> recover managed standby database cancel;

Step 3

notedown the file location

SQL> Select name from v$datafile where file#=20

+DATA/orcl/datafile/users.20.658933000

Step 4

Catalog datafilecopy on Standby using Rman

RMAN > Catalog datafilecopy '/u01/stage/tmp/backup_file_20.dbf' ;

Step 5
Rman > Connect target

RMAN> copy datafilecopy '/u01/stage/tmp/backup_file_20.dbf'  to '+DATA'
.....................................
.....................................
output filename=+DATA/orcl/datafile/users.20.658960000 tag=TAG20080701T174316 r ecid=20 stamp=658953191 <== note down this name
This will report the new location/name of the original file  20.
In this example  its +DATA/orcl/datafile/users.20.658960000

Step 6

Go to sqlplus on standby database

SQL> Select name from v$datafile where file#=20

For example

SQL> Select name from v$datafile where file#=20
'+DATA/orcl/datafile/users.20.658933000'

This will show the original name/location of the datafile. We would need to rename this to the new file
name showen from above rman command to update the information in controlfile about this new datafile
We would issue rename command to point to the new location of the file.

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

SQL> Alter database rename  file '' to 'reported in step5 after copy datafile command;'

For example

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

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

After the activity is over, remove the transient copy i.e.

RMAN> delete datafilecopy '/u01/stage/tmp/backup_file_20.dbf' ;

Option 3

On Primary database :-

Step1

backup affected datafile (in my case its file#20)

RMAN> BACKUP DATAFILE 20 FORMAT '/u01/stage/tmp/backup_file_20_%U';

Transfer the backup piece to standby

On Standby database :-

Step2

RMAN> catalog start with '/u01/stage/tmp/' noprompt;

Step3

SQL> recover managed standby database cancel;

Step4

RMAN> restore datafile 20;

Step5

SQL> recover managed standby disconnect from session;

The options assume that you have enough archivelogs availiable to recover the datafile to the current point in time
If you have space crunch and you are running 12cR2 and all the above 3 steps does not fit for you then you can check my post

ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

ORCL1> show parameter spfile

NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
spfile                               string          +DATA01/ORCL/spfile

ORCL1> alter system set db_unique_name="ORCLP" scope=spfile sid='*';
alter system set db_unique_name="ORCLP" scope=spfile sid='*'
*
ERROR at line 1:

ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

Bug 20876249 : COULD NOT MODIFY DB_UNIQUE_NAME RESOURCE EXISTS

Workaround

1. Manually edit the pfile and added the entry

*.db_unique_name='ORCLP'

2. startup the db using the pfile

SQL> startup pfile='/local/dbms/oracle/product/12.1.0.2/db_1/dbs' ;

3.created the spfile from the pfile

SQL> create spfile='+DATA01/ORCL/spfile' from
pfile='/local/dbms/oracle/product/12.1.0.2/db_1/dbs';

File created.

4.set the spfile parameter
SQL> alter system set spfile='+DATA01/ORCL/spfile'  ;
System altered.

What is Oracle Flashback Technologies

Understanding Oracle Flashback Technologies

What is oracle Flashback Technologies?

The Flashback features offer the capability to query historical data, perform change analysis, and perform self-service repair to recover from logical corruptions while the database is online. With Oracle Flashback Technology, you can indeed undo the past.

How to Enable Flashback?

Flashback was introduced in 10g and up to 11gR1 you need to clean mount the database to enable the

flashback. It means you need downtime to enable the flashback

before 11gR2

login as sysdba

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT EXCLUSIVE;

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;  << for how long the flashback logs are retained

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=50G SCOPE=BOTH;   << maximum limit on size flashback log can use in DB_RECOVERY_FILE_DEST location

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=/ora_data01/orcl/recovery/;  << location where the flashback logs are written

SQL> ALTER DATABASE FALSHBACK ON;

SQL> ALTER DATABASE OPEN;

11gR2 and after

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;  << for how long the flashback logs are retained

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=50G SCOPE=BOTH;   << maximum limit on size flashback log can use in DB_RECOVERY_FILE_DEST location

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=/ora_data01/orcl/recovery/;  << location where the flashback logs are written

SQL> ALTER DATABASE FALSHBACK ON;

SQL> ALTER DATABASE OPEN;

Notice the difference. starting from 11gR2 you dont need to shutdown your database to enable the flashback

How to Check if the FALSHBACK is enabled?

SQL> select flashback_on from v$database;

Which background process is responsible for flashback logs writing?

RVWR (Recovery Writer, a.k.a Flashback Writer) was introduced in Oracle 10g to write flashback data from the Flashback Buffer in the SGA to the flashback database logs on disk.

What is Flashback Buffer?

flashback buffer is a designated area in SGA (just like log buffer) to hold the flashback logs. RVWR process flush the log from flashback buffer to disk

When are the flashback logs deleted?

• If the flash recovery area has enough space, then a flashback log is deleted whenever necessary to satisfy the flashback retention target.
• If a flashback log is old enough that it is no longer needed to satisfy the flashback retention target, then a flashback log is overwritten.
• If the database must create a new flashback log and the fast recovery area is full or there is no disk space, then the oldest flashback log is overwritten instead.
• If the fast recovery area is full, then an archived redo log that is reclaimable according to the FRA rules may be automatically deleted by the fast recovery area to make space for other files. In this case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.
• No file in the fast recovery area is eligible for deletion if it is required to satisfy a guaranteed restore point. Thus, retention of flashback logs and other files required to satisfy the guaranteed restore point, in addition to files required to satisfy the backup retention policy, can cause the fast recovery area to fill completely.
• When flashback mode is turned off all flashback logs are deleted ONLY if there’s no guaranteed restore points. If there’s at least one guaranteed restore point, no flashback logs are deleted.
• When the oldest guaranteed restore point is deleted and flashback mode is off, all flashback logs older than the second oldest guaranteed restore point are deleted. If flashback mode is on for the database OR the guaranteed restore point is not the oldest no flashback logs are deleted.

What feature Flashback technology offers?

Enabling flashback offers following featuers

Flashback Database: restore the entire database to a specific point-in-time, using Oracle-optimized flashback logs, rather than via backups and forward recovery.

Flashback Table: easily recover tables to a specific point-in-time, useful when a logical corruption is limited to one or a set of tables instead of the entire database.

Flashback Drop: recover an accidentally dropped table. It restores the dropped table, and all of its indexes, constraints, and triggers, from the Recycle Bin (a logical container of all dropped objects).

Flashback Transaction: undo the effects of a single transaction, and optionally, all of its dependent transactions. via a single PL/SQL operation or by using an Enterprise Manager wizard.

Flashback Transaction Query:  see all the changes made by a specific transaction, useful when an erroneous transaction changed data in multiple rows or tables.

Flashback Query: query any data at some point-in-time in the past. This powerful feature can be used to view and logically reconstruct corrupted data that may have been deleted or changed inadvertently.

Flashback Versions Query: retrieve different versions of a row across a specified time interval instead of a single point-in-time.

Total Recall: efficiently manage and query long-term historical data. Total Recall automatically tracks every single change made to the data stored inside the database and maintains a secure, efficient and easily accessible archive of historical data.

FRA and Flashback Queries

To find Location, quota, in use/reclaimable space, number of files

SQL>SELECT * FROM v$recovery_file_dest;

For each file type, percent of FRA space it uses and is reclaimable and number of files of that type

SQL>SELECT * FROM v$recovery_area_usage;

Estimated space used by Flashbacklogs

SQL>SELECT estimated_flashback_size FROM v$flashback_database_log;

ORA-54013 ON FLASHBACK TABLE WITH VIRTUAL COLUMNS

ORA-54013: INSERT operation disallowed on virtual columns

Solution:

Oracle does not support direct flashback on a table with virtual column. the reason for the same is that the values for virtual column are derived rather than being stored on disk and there is no undo for them.

 Virtual column is not officially supported by flashback table feature. ( Including 12c )

Refer Metalink note Doc ID 1943791.1 for more details