12c New Feature=> RESTORE AND RECOVER STANDBY DATABASE FROM NETWORK SERVICE

-

 It is always possible to loose archivelogs in our Prmiary-Standby Database configurations. When we loose even only one archivelog file, a gap would occur and the automatic recovery of our standby database stops.


We'd have several methods to resolve the gaps:

·                      Restore the archivelogs from backup and apply them manually
·                      Create incremental backup from primary and recover standby.


With the release of 12c, we have a new method to resolve the gaps; 

--->Restore & Recover from Network Service 


Steps:

1. Check the status:

SQL> SELECT distinct PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY order by process;
  

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH      CLOSING               1         82
ARCH      CLOSING               1         89
ARCH      CONNECTED             0          0
MRP0      WAIT_FOR_GAP          1         86
RFS       IDLE                  0          0
RFS       IDLE                  1         90

6 rows selected.

SQL> 


2. Stop the recovery on standby

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> 


3. Recover the database over the network;

RMAN> recover database from service TWELVE;

Starting recover at 25-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=249 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TWELVE
destination for restore of datafile 00001: /u01/app/oracle/oradata/TWELVE/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network bac
kup set from service TWELVE
destination for restore of datafile 00003: /u01/app/oracle/oradata/TWELVE/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TWELVE
destination for restore of datafile 00004: /u01/app/oracle/oradata/TWELVE/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TWELVE
destination for restore of datafile 00005: /u01/app/oracle/oradata/TWELVE/example01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TWELVE
destination for restore of datafile 00006: /u01/app/oracle/oradata/TWELVE/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 87 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_87_910395669.dbf
archived log for thread 1 with sequence 88 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_88_910395669.dbf
archived log for thread 1 with sequence 89 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_89_910395669.dbf
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-APR-16

RMAN> 


4. Start the recovery Check the status again;

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> SELECT distinct PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY order by process;

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH      CLOSING               1         82
ARCH      CLOSING               1         89
ARCH      CONNECTED             0          0
MRP0      WAIT_FOR_GAP          1         86
RFS       IDLE                  0          0
RFS       IDLE                  1         90

6 rows selected.

SQL> 

***As you see, standby is still waiting 86th archivelog because controlfile doesnt know the new SCN values of the datafiles yet. So, we need to restore the controlfile too.


5. Restore standby controlfile from network service

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shu immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  926941184 bytes
Fixed Size                  2930944 bytes
Variable Size             343934720 bytes
Database Buffers          574619648 bytes
Redo Buffers                5455872 bytes


SQL> 

[oracle@rent ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 25 21:23:20 2016

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

connected to target database: TWELVE (not mounted)

RMAN>  restore standby controlfile from service TWELVE;

Starting restore at 25-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service TWELVE
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TWELVE/control01.ctl
output file name=/u01/app/oracle/oradata/TWELVE/control02.ctl
Finished restore at 25-APR-16

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> alter database recover managed standby database using current logfile disconnect from session;

Statement processed



SQL> SELECT distinct PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY order by process;

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
MRP0      APPLYING_LOG          1         90
RFS       IDLE                  0          0
RFS       IDLE                  1         90

SQL>   

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more