DBA Hub

📋Steps in this guide1/2

Restore missing datafile in standby database oracle 12c DBACLASS

Restore missing datafile in standby database oracle 12c . If standby datafile is missing and no backup is available, then with 12c new feature, we can restore it ..

oracle clusteringintermediate
by OracleDba
13 views
1

DEMO:

PRIMDB – Primary database STYDB – Standby database Check status of primary and standby database: Lets remove one file from standby database:[STYDB]: CHECK FROM RMAN:

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
--PRIMARY 

SQL> select DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE from v$database;

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
PRIMDB READ WRITE PRIMARY
-- STANDBY
SQL> select name,OPEN_MODE,DATABASE_ROLE from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
STYDB READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/archive/PRIMDB/PRIMDB/system01.dbf
/archive/PRIMDB/PRIMDB/sysaux01.dbf
/archive/PRIMDB/PRIMDB/undotbs01.dbf
/archive/PRIMDB/PRIMDB/users01.dbf

mv /archive/PRIMDB/PRIMDB/users01.dbf /archive/PRIMDB/PRIMDB/users01.dbf_bkp


-- Try to query any table 

SQL> select file_name from dba_data_files;
select file_name from dba_data_files
                      *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/archive/PRIMDB/PRIMDB/users01.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

RMAN> list failure;

using target database control file instead of recovery catalog
Database Role: PHYSICAL STANDBY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
322        CRITICAL OPEN      18-OCT-16     One or more non-system datafiles are missing
2

SOLUTION:

Now follow below steps to restore the datafile from primary: NOTE : All activities need to be done on standby database 1. CANCEL THE RECOVERY( STANDBY) 2 . START STANDBY IN MOUNT STAGE( IF IN READ-ONLY MODE): 3. RESTORE THE DATAFILE ( STANDBY) Now connect to RMAN in standby and use restore command. SYNTAX – RESTORE DATAFILE < FILE_ID> FROM SERVICE < PRIMARY DB SERVICE NAME > 4. START THE RECOVERY ( STANDBY) 5. CANCEL RECOVERY AND START DATABASE IN READ ONLY: 6 . START THE RECOVERY AGAIN: Now all datafiles are online : MORE ARTICLES ON STANDBY: > 1. Oracle dataguard Concepts: 2. How to create physical standby database 3 . Convert physical standby database to snapshot database 4.  Switchover in physical standby database 5. Applying PSU patch in physical standby database 6. Rolling forward standby database if archive missing in primary – 12c 1. Oracle dataguard Concepts: 2. How to create physical standby database 3 . Convert physical standby database to snapshot database 4.  Switchover in physical standby database 5. Applying PSU patch in physical standby database 6. Rolling forward standby database if archive missing in primary – 12c

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
SQL> recover managed standby database cancel;
Media recovery complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.3935E+10 bytes
Fixed Size                  6002112 bytes
Variable Size            4630514240 bytes
Database Buffers         9193914368 bytes
Redo Buffers              104153088 bytes
Database mounted.

rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Oct 18 18:04:15 2016

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: STYDB (DBID=599956155, not open)

RMAN> restore datafile 4 from service PRIMDB;

Starting restore at 18-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=705 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service PRIMDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /archive/PRIMDB/PRIMDB/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 18-OCT-16

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> shutdown immediate;
ORA-01109: database not open


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

Total System Global Area 1.3935E+10 bytes
Fixed Size                  6002112 bytes
Variable Size            4630514240 bytes
Database Buffers         9193914368 bytes
Redo Buffers              104153088 bytes
Database mounted.
Database opened.

SQL>  recover managed standby database using current logfile disconnect from session;
Media recovery complete.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/archive/PRIMDB/PRIMDB/system01.dbf
/archive/PRIMDB/PRIMDB/sysaux01.dbf
/archive/PRIMDB/PRIMDB/undotbs01.dbf
/archive/PRIMDB/PRIMDB/users01.dbf

Comments (0)

Please to add comments

No comments yet. Be the first to comment!