DBA Hub

📋Steps in this guide1/3

How To Recover Standby Database When Archive Logs Are Missing In Primary

Generally in Dataguard setup, the archives from primary shipped to standby and applied there. Suppose some of the archives hasn’t been shipped to secondary

oracle configurationintermediate
by OracleDba
14 views
1

Overview

Generally in Dataguard setup, the archives from primary shipped to standby and applied there. Suppose some of the archives hasn’t been shipped to secondary .But due to intermittence error or human error, the archives has been deleted from primary. So without those archives, standby can’t be in sync with primary.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
1. Find current_scn from primary.

SQL > select current_scn from v$database;
CURRENT_SCN
-----------
2791422

2. Find current_scn from standby.

SQL > select current_scn from v$database;
CURRENT_SCN
-----------

2791087 -

SQL > alter database recover managed standby database cancel;

Database altered.

SQL > shutdown immediate;
Database closed.
Database dismounted.

RMAN> run {
allocate channel c1 type disk format '/home/oracle/app/rman_bkup%U.rmb';
backup incremental from scn 2791087 database;
}
2> 3> 4>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=90 device type=DISK
Starting backup at 20-SEP-13
backup will be obsolete on date 27-SEP-13
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u02/PRIM/oradata/user01.dbf
input datafile file number=00001 name=/u02/PRIM/oradata/system.dbf
input datafile file number=00002 name=/u02/PRIM/oradata/sysaux.dbf
input datafile file number=00003 name=/u02/PRIM/oradata/undotbs1.dbf
channel c1: starting piece 1 at 20-SEP-13
channel c1: finished piece 1 at 20-SEP-13
piece handle=/home/oracle/app/rman_bkup0aokac2j_1_1.rmb tag=TAG20130920T080539
comment=NONE
channel c1: backup set complete, elapsed time: 00:00:45
backup will be obsolete on date 27-SEP-13
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 20-SEP-13
channel c1: finished piece 1 at 20-SEP-13
piece handle=/home/oracle/app/rman_bkup0bokac40_1_1.rmb tag=TAG20130920T080539
comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-SEP-13
released channel: c1

SQL > alter database create standby controlfile as
'/home/oracle/app/control02.ctl';

Database altered.
2

Section 2

Copy the RMAN backup file and new standby controlfile to standby database. In standby the files were copied to /home/oracle/app/.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL > startup nomount;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2233336 bytes
Variable Size 813698056 bytes
Database Buffers 247463936 bytes
Redo Buffers 5541888 bytes

SQL > show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u03/PRIM/oradata/control02.ctl

cp /home/oracle/app/control02.ctl  
 /u03/PRIM/oradata/control02.ctl

SQL > alter database mount standby database;

Database altered.
3

Section 3

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
NODE2]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 20 09:14:08 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTER (DBID=3502897675, not open)
RMAN> catalog start with '/home/oracle/app';
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/app
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/app/control02.ctl
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/app/control02.ctl

RMAN> recover database;
Starting recover at 20-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=76 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u03/PRIM/oradata/system.dbf
destination for restore of datafile 00002: /u03/PRIM/oradata/sysaux.dbf9
destination for restore of datafile 00003: /u03/PRIM/oradata/undotbs1.dbf
destination for restore of datafile 00004: /u03/PRIM/oradata/user01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/rman_bkup0aokac2j_1_1.rmb
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/20/2013 09:16:41
ORA-19870: error while restoring backup piece /home/oracle/app/rman_bkup0aokac2j_1_1.rmb
ORA-19573: cannot obtain exclusive enqueue for datafile 4

Please note : If above errors are coming, then cancel the recovery again 

SQL > alter database recover managed standby database cancel;

Database altered.


Again follow the same process i.e recovering the database.


RMAN> recover database;

Starting recover at 20-SEP-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u03/PRIM/oradata/system.dbf
destination for restore of datafile 00002: /u03/PRIM/oradata/sysaux.dbf
destination for restore of datafile 00003: /u03/PRIM/oradata/undotbs1.dbf
destination for restore of datafile 00004: /u03/PRIM/oradata/user01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/rman_bkup0aokac2j_1_1.rmb
channel ORA_DISK_1: piece handle=/home/oracle/app/rman_bkup0aokac2j_1_1.rmb tag=TAG20130920T080539
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
archived log for thread 1 with sequence 250 is already on disk as file
/u03/PRIM/oradata/REDO_STDBY/1_250_824551947.arc
archived log file name=/u03/PRIM/oradata/REDO_STDBY/1_250_824551947.arc thread=1 sequence=250
unable to find archived log
archived log thread=1 sequence=251
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================10
RMAN-03002: failure of recover command at 09/20/2013 09:28:04
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 251 and starting SCN of
2796410
This error are expected errors.This happens because we have come to the last of the archived logs. The expected archived log with sequence# 8008 has not been generated yet.

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

Database altered.

1. (Primary) Find current_scn from primary.
SQL > select current_scn from v$database;

CURRENT_SCN
-----------
2791422
2. (Standby) Find current_scn from standby.
SQL > select current_scn from v$database;

CURRENT_SCN
-----------
279142211

SQL > select sequence#,process,status from v$managed_standby;
SEQUENCE# PROCESS STATUS
---------- --------- ------------
258 ARCH CLOSING
0 ARCH CONNECTED
259 RFS IDLE
259 MRP0 WAIT_FOR_LOG

RFS IDLE

SQL> alter system switch logfile;

System altered.

SQL > archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/PRIM/oradata/REDO_STDBY/
Oldest online log sequence 257
Next log sequence to archive 259
Current log sequence 259

SQL > archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u03/PRIM/oradata/REDO_STDBY/
Oldest online log sequence 258
Next log sequence to archive 0
Current log sequence 259

Comments (0)

Please to add comments

No comments yet. Be the first to comment!