Renaming datafile on standby database

DATAGUARD TROUBLE SHOOTING –

It is possible that a datafile is added on primary in some other area other than paths covered by the db_file_name_convert parameter which ensures the file is added on the standby database as well.

This documents the real life scenario if a datafile is added to an wrong area on the primary database which is not in the db_file_name_convert path in the standby init.ora and how to recover the standby database in this case.

STANDBY SITE

SQL> show parameter convert

NAME TYPE VALUE
———————————— ———– ——————————
db_file_name_convert string /opt/oracle/, /opt/oracle/
log_file_name_convert string /opt/oracle/, /opt/oracle/

SQL> show parameter standby

NAME TYPE VALUE
———————————— ———– ——————————
standby_file_management string AUTO

PRIMARY SITE:

If a file is created in any other area other than /opt/oracle ( which is in the db_file_name_convert path)

EX: SQL> create tablespace arjun datafile ‘/u01/ORACLE/dgtest9i_bkp/arjun.dbf’ size 10m;

Tablespace created.

SQL> alter system switch logfile;

Since the db_file_name_convert on the standby site only handles files created in /opt/oracle area, the datafile cannot be added to the standby site….

Alert log in standby site…

Media Recovery Log /opt/oracle/dgtest9i/arch/arch235.log
File #7 added to control file as ‘UNNAMED00007’. Originally created as:
‘/u01/ORACLE/dgtest9i_bkp/arjun.dbf’
Recovery was unable to create the file as:
‘/u01/ORACLE/dgtest9i_bkp/arjun.dbf’
MRP0: Background Media Recovery terminated with error 1274
ORACLE AUTOMATICALLY STOPS MEDIA RECOVERY –
Sun Mar 5 18:49:06 2006
Errors in file /opt/oracle/admin/dgtest9i/bdump/dgtest9i_mrp0_30121.trc:
ORA-01274: cannot add datafile ‘/u01/ORACLE/dgtest9i_bkp/arjun.dbf’ – file could not be created
ORA-01119: error in creating database file ‘/u01/ORACLE/dgtest9i_bkp/arjun.dbf’
ORA-27040: skgfrcre: create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
MRP0: Background Media Recovery process shutdown…………..Automatically shuts down the recovery process in this case.

Check whether logs have been applied….

SQL> SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;

LAST_LOG_GENERATED
——————
237

select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;

LAST_LOG_APPLIED
—————-
234

Now check the v$datafile view in standby database…..

SQL> select name from v$datafile;

NAME
——————————————————————————–
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/product9204/dbs/UNNAMED00007 – This is the new file.

To rename the file as required…

In standby database….

alter system set standby_file_management=manual;

SQL> alter database create datafile ‘/opt/oracle/product9204/dbs/UNNAMED00007’ as ‘/opt/oracle/oradata/dgtest9i/arjun1.dbf’ ;
Database altered.

SQL> select name from v$datafile;

NAME
——————————————————————————–
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/arjun1.dbf

alter system set standby_file_management=AUTO;

alter database recover managed standby database disconnect;

Now check the alertlog again….and notice recovery has started on the standby site.

ALTER SYSTEM SET standby_file_management=’MANUAL’ SCOPE=BOTH;
Sun Mar 5 18:56:23 2006
alter database create datafile ‘/opt/oracle/product9204/dbs/UNNAMED00007’
as ‘/opt/oracle/oradata/dgtest9i/arjun1.dbf’
Sun Mar 5 18:56:23 2006
Starting control autobackup
Control autobackup failed with following error :
ORA-00236: snapshot operation disallowed: mounted controlfile is a backup
Completed: alter database create datafile ‘/opt/oracle/produc
Sun Mar 5 18:57:01 2006
ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=BOTH;
Sun Mar 5 18:57:08 2006
Alter database recover managed standby database disconnect
Attempt to start background Managed Standby Recovery process
MRP0 started with pid=11
MRP0: Background Managed Standby Recovery process started
Starting datafile 1 recovery in thread 1 sequence 235
Datafile 1: ‘/opt/oracle/oradata/dgtest9i/system01.dbf’
Starting datafile 2 recovery in thread 1 sequence 235
Datafile 2: ‘/opt/oracle/oradata/dgtest9i/undotbs01.dbf’
Starting datafile 3 recovery in thread 1 sequence 235
Datafile 3: ‘/opt/oracle/oradata/dgtest9i/users01.dbf’
Starting datafile 4 recovery in thread 1 sequence 235
Datafile 4: ‘/opt/oracle/oradata/dgtest9i/arjun1.dbf’
Media Recovery Log /opt/oracle/dgtest9i/arch/arch235.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch236.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch237.log
Media Recovery Waiting for thread 1 seq# 238 (in transit)

Now check logs applied…..

SQL> SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;

LAST_LOG_GENERATED
——————
237
select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;

LAST_LOG_APPLIED
—————-
237

Therefore it is a good idea to check the alert_log on a regular basis to make sure managed recovery has not crashed, just log shipping taking place is not enough as logs will continue to get shipped irrespective of whether recovery is in progress or not.