How to manually create datafile on standby server

DataGaurd issue with datafile creation if standby_file_management is set MANUAL

I HAVE SEEN ERROR IN ALERT LOG OF DR-SERVER
ERROR IS

Errors with log /ora_archive_log/arch/1_20216_750626102.dbf
MRP0: Background Media Recovery terminated with error 1274
Wed Sep 12 07:54:45 EEST 2012
Errors in file /u01/app/oracle/admin/prod/bdump/prod1_mrp0_26910.trc:
ORA-01274: cannot add datafile '/oraindex2/prod/APP_INDEX_SPACE2-02.dbf' - file could not be created
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Wed Sep 12 07:54:47 EEST 2012
Errors in file /u01/app/oracle/admin/prod/bdump/prod1_mrp0_26910.trc:
ORA-01274: cannot add datafile '/oraindex2/prod/APP_INDEX_SPACE2-02.dbf' - file could not be created
Wed Sep 12 07:54:47 EEST 2012
MRP0: Background Media Recovery process shutdown (prod1)
Wed Sep 12 07:57:14 EEST 2012

or another error can be like
Errors in file /u01/app/oracle/admin/prod/bdump/prod1_mrp0_27731.trc:
ORA-01111: name for data file 137 is unknown - rename to correct file
ORA-01110: data file 137: '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00137'
ORA-01157: cannot identify/lock data file 137 - see DBWR trace file
ORA-01111: name for data file 137 is unknown - rename to correct file
ORA-01110: data file 137: '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00137'
Tue Oct 16 09:05:23 EEST 2012
MRP0: Background Media Recovery process shutdown (prod1)
Tue O

alter database create datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00137' as '/oradata2/prod/APP_DATA_SPACE53.dbf';

alter database create datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00135' as '/oradata2/prod/APP_INDEX_SPACE2-02.dbf'

I CHECKED THE STANDBY FILE MANAGEMENT

SQL> sho parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
SQL>

We need manually to add datafile to standby server as below

SQL> alter database create datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00135' as '/oradata2/prod/APP_INDEX_SPACE2-02.dbf';
Database altered.

SQL>

WE CAN SEE FILE IS CREATED WITH THE SAME SIZE AS OF PRIMARY SERVER

[oracle@dr1-orac1 script]$ ls -rlt /oradata2/prod/APP_INDEX_SPACE2-02.dbf
-rw-r—– 1 oracle oinstall 10737426432 Sep 12 07:58 /oradata2/prod/APP_INDEX_SPACE2-02.dbf
[oracle@dr1-orac1 script]$

SQL>alter database recover managed standby database disconnect from session;

alert log of DR-SERVER is

alter database recover managed standby database disconnect from session
Wed Sep 12 08:00:35 EEST 2012
Attempt to start background Managed Standby Recovery process (prod1)
MRP0 started with pid=21, OS id=32122
Wed Sep 12 08:00:35 EEST 2012
MRP0: Background Managed Standby Recovery process started (prod1)
Wed Sep 12 08:00:40 EEST 2012
Managed Standby Recovery not using Real Time Apply
Wed Sep 12 08:00:42 EEST 2012
 parallel recovery started with 7 processes
Wed Sep 12 08:00:43 EEST 2012
Waiting for all non-current ORLs to be archived...
Wed Sep 12 08:00:43 EEST 2012
Errors in file /u01/app/oracle/admin/prod/bdump/prod1_mrp0_32122.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '/oradata/prod/node3/redo4node3.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Sep 12 08:00:43 EEST 2012
Errors in file /u01/app/oracle/admin/prod/bdump/prod1_mrp0_32122.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '/oradata/prod/node3/redo4node3.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 4 /oradata/prod/node3/redo4node3.log
Clearing online log 4 of thread 2 sequence number 11438
Wed Sep 12 08:00:43 EEST 2012
Errors in file /u01/app/oracle/admin/prod/bdump/prod1_mrp0_32122.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '/oradata/prod/node3/redo4node3.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Sep 12 08:00:43 EEST 2012
Errors in file /u01/app/oracle/admin/prod/bdump/prod1_mrp0_32122.trc:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 4 thread 2: '/oradata/prod/node3/redo4node3.log'
Clearing online redo logfile 4 complete
Media Recovery Log /ora_archive_log/arch/1_20216_750626102.dbf
Media Recovery Log /ora_archive_log/arch/2_11395_750626102.dbf
Wed Sep 12 08:00:43 EEST 2012
Completed: alter database recover managed standby database disconnect from session
Wed Sep 12 08:00:44 EEST 2012
Media Recovery Log /ora_archive_log/arch/4_10227_750626102.dbf
Wed Sep 12 08:01:12 EEST 2012
Media Recovery Log /ora_archive_log/arch/2_11396_750626102.dbf
Wed Sep 12 08:01:36 EEST 2012
Media Recovery Log /ora_archive_log/arch/1_20217_750626102.dbf
Wed Sep 12 08:01:55 EEST 2012
Media Recovery Log /ora_archive_log/arch/4_10228_750626102.dbf
Wed Sep 12 08:02:33 EEST 2012
Media Recovery Log /ora_archive_log/arch/1_20218_750626102.dbf
Wed Sep 12 08:03:05 EEST 2012
Media Recovery Log /ora_archive_log/arch/1_20219_750626102.dbf
Media Recovery Log /ora_archive_log/arch/2_11397_750626102.dbf
Wed Sep 12 08:03:36 EEST 2012
Media Recovery Log /ora_archive_log/arch/1_20220_750626102.dbf
Media Recovery Log /ora_archive_log/arch/4_10229_750626102.dbf
Wed Sep 12 08:04:18 EEST 2012
Media Recovery Log /ora_archive_log/arch/1_20221_750626102.dbf
Wed Sep 12 08:04:42 EEST 2012
Media Recovery Log /ora_archive_log/arch/1_20222_750626102.dbf
Media Recovery Log /ora_archive_log/arch/2_11398_750626102.dbf
Wed Sep 12 08:05:02 EEST 2012
Media Recovery Log /ora_archive_log/arch/1_20223_750626102.dbf

TO AVOID IN FUTURE FROM SAME PROBLEM SET THE PARAMETER

SQL> alter system set standby_file_management='AUTO' scope=both sid='*';
System altered.

after some time see DR-SERVER alert log

Wed Sep 12 10:35:08 EEST 2012
Errors in file /u01/app/oracle/admin/prod/bdump/prod1_arc1_20558.trc:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 400 thread 4: ‘/ora_archive_log/prod/standby400.log’
Wed Sep 12 10:35:09 EEST 2012
RFS[11]: Successfully opened standby log 100:
‘/oraindex/prod/standby/standby1101.log’
Wed Sep 12 10:36:40 EEST 2012
Media Recovery Log /ora_archive_log/arch/1_20286_750626102.dbf
Media Recovery Log /ora_archive_log/arch/4_10344_750626102.dbf
Wed Sep 12 10:36:59 EEST 2012
Media Recovery Log /ora_archive_log/arch/4_10345_750626102.dbf
Media Recovery Waiting for thread 2 sequence 11447

Check the dataguard syncronization run the query on primary server to know the max sequence#

SQL> select thread#, max(sequence#) "Last Primary Seq Generated" from
v$archived_log val, v$database vdb where val.resetlogs_change# =
vdb.resetlogs_change# group by thread# order by 1;
THREAD# Last Primary Seq Generated
---------- --------------------------
 1 20287
 2 11446
 4 10345

CHECKED THE ARCHIVE LOGS STATUS ON DR-SERVER

SQL> select THREAD#,SEQUENCE#,status,ARCHIVED,APPLIED from v$archived_log
where (THREAD#=1 and SEQUENCE# > 20278 )or (THREAD#=2 and SEQUENCE# > 11436)
or (THREAD#=4 and SEQUENCE# > 10335 ) order by THREAD#,SEQUENCE#;

 THREAD# SEQUENCE# S ARC APP
---------- ---------- - --- ---
 1 20279 A YES YES
 1 20280 A YES YES
 1 20281 A YES YES
 1 20282 A YES YES
 1 20283 A YES YES
 1 20284 A YES YES
 1 20285 A YES YES
 1 20286 A YES NO
 1 20287 A YES NO
 2 11437 A YES YES
 2 11438 A YES YES
THREAD# SEQUENCE# S ARC APP
---------- ---------- - --- ---
 2 11439 A YES YES
 2 11440 A YES YES
 2 11441 A YES YES
 2 11442 A YES YES
 2 11443 A YES YES
 2 11444 A YES YES
 2 11445 A YES YES
 2 11446 A YES YES
 4 10336 A YES YES
 4 10337 A YES YES
 4 10338 A YES YES
THREAD# SEQUENCE# S ARC APP
---------- ---------- - --- ---
 4 10339 A YES YES
 4 10340 A YES YES
 4 10341 A YES YES
 4 10342 A YES YES
 4 10343 A YES YES
 4 10344 A YES YES
 4 10345 A YES NO
29 rows selected.
SQL>

ALL ARCHIVE LOGS ARE APPLIED ON DR-SERVER.

IT MEANS DR-SERVER IS FULLY SYNCHRONIZED WITH PRIMARY SERVER.

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Oracle DataGaurd and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s