Tuesday, May 5, 2020

Dataguard Stuff


How To Enable Active Dataguard In Physical Standby Database

Active dataguard means, the standby database is open with read only mode, even when redo logs are getting applied in real time.
Below are the benefit of using active dataguard.
1.    Reporting queries can be offloaded to standby database.
2.    Physical block corruptions are repaired automatically either at primary or physical standby database.
3.    RMAN backups can be initiated from standby , instead  of primary which will reduce cpu load from primary.
NOTE – To use active dataguard, you need additional license from oracle
Let’s say we have a physical standby database which is in mount state. Implement below steps to enable active dataguard database.

1. Cancel the media recovery on physical standby.
1
2
3
4


SQL> alter database recover managed standby database cancel;
2. Open the database[PHYSICAL STANDBY]
1
2
3
4
5
6


SQL> alter database open;

Database altered.
3. Start media recovery with real-time log apply[PHYSICAL STANDBY]
1
2
3
4
5
6


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

Database altered.
4. Check the database status:[PHYSICAL STANDBY]
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


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRODDB   READ ONLY WITH APPLY



SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       RECEIVING           510
RFS       IDLE                  0
RFS       IDLE               4178
MRP0      APPLYING_LOG        510 --->>>>  MRP PROCESS

Now active dataguard has been enabled.





A.1 Standby Database Does Not Receive Redo Data from the Primary Database
If the standby site is not receiving redo data, query the V$ARCHIVE_DEST view and check for error messages.
For example, enter the following query:
SQL> SELECT DEST_ID "ID", -
 STATUS "DB_status", -
 DESTINATION "Archive_dest", -
 ERROR "Error" -
 FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;

ID DB_status Archive_dest                   Error  
-- --------- ------------------------------ ------------------------------------
 1  VALID    /vobs/oracle/work/arc_dest/arc                         
 2  ERROR    standby1                       ORA-16012: Archivelog standby database identifier mismatch 
 3  INACTIVE                           
 4  INACTIVE                   
 5  INACTIVE                                          
5 rows selected.
If the output of the query does not help you, then check the following list of possible issues. If any of the following conditions exist, then redo transport services fail to transmit redo data to the standby database:
  • The service name for the standby instance is not configured correctly in the tnsnames.ora file for the primary database.
  • The Oracle Net service name specified by the LOG_ARCHIVE_DEST_n parameter for the primary database is incorrect.
  • The LOG_ARCHIVE_DEST_STATE_n parameter for the standby database is not set to the value ENABLE.
  • The listener.ora file has not been configured correctly for the standby database.
  • The listener is not started at the standby site.
  • The standby instance is not started.
  • You have added a standby archiving destination to the primary SPFILE or text initialization parameter file, but have not yet enabled the change.
  • You used an invalid backup as the basis for the standby database (for example, you used a backup from the wrong database, or did not create the standby control file using the correct method).


HOW TO CHECK WHETHER PHYSICAL STANDBY IS IN SYNC WITH THE PRIMARY OR NOT AND RESOLVED GAP ?

June 25, 2014
Step by Step Process to Resolve gap on the Standby database.
Summary
1. Check the name and status of database.
2. Check for GAP on standby
3. Check redo received on standby
4. Check redo applied on standby
5. Identify missing archive log files
6. Copy archive log files
7. Register archive log files with standby
8. Restart the managed recovery operations
******************************************************************************************************************
Step 1 : Check the status of database on both server.
On Primary Server.
SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE  DATABASE_ROLE
——— ———- —————-
MYDB   READ WRITE PRIMARY
On Standby Server.
SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE  DATABASE_ROLE
——— ———- —————-
MYDB   MOUNTED    PHYSICAL STANDBY
Step 2 : Check for GAP on Standby
PRIMARY@MYDB>select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
————–
76921
STANDBY@MYDB>select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
————–
76921
STANDBY@MYDB>SELECT THREAD# “Thread”,SEQUENCE# “Last Sequence Generated”
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
Thread Last Sequence Generated
———- ———————–
1                   76921

Step 3 & 4: Check redo received and applied on standby. 
STANDBY@MYDB> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”,
APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1                  76922                 20931      55991
Step 5: Identify the missing archive log file. 
STANDBY@MYDB>SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
—-If found gap
Step 6: Copy missing archive log file
After identifying a gap (as shown above), the DBA will need to query the primary database to locate the archived redo logs on the primary database. The following query assumes the local archive destination on the primary database is LOG_ARCHIVE_DEST_1:
PRIMARY@MYDB>  SELECT name
FROM v$archived_log
WHERE thread# = 1
AND dest_id = 1
AND sequence# BETWEEN 20931 and 76922;
Output:
/oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc
56027 rows selected.
Step 7: Register archive logfile with standby.
Copy the above redo log files to the physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE … SQL statement on the physical standby database.
For example:
STANDBY@MYDB> ALTER DATABASE REGISTER LOGFILE ‘/oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc’;
Step 8: Restart the managed recovery operations.
— After the redo logs have been registered on the physical standby database, the DBA can restart the managed recovery operations.
For example, to put the physical standby database into automatic recovery managed mode:
STANDBY@MYDB> alter database recover managed standby database disconnect from session;

RELATED

UNNAMED File Error in standby after adding new file to primary database – ORA-01111, ORA-01110, ORA-01157



Standby is out of Sync or GAP is not getting resolved on standby
It can happen due to several reasons like
1. Archived log has been deleted
2. Moved log file has corrupted
3. Any network issues
If it’s because of either 1 or 2 incremental backup from SCN is the best option to recover it. Suppose if we have proper archive logs exist on the primary database then FAL_SERVER, FAL_CLIENT will take care of the gap issue.
SQL> select name,database_role,switchover_status,protection_mode,force_logging from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE FOR
——— —————- ——————– ——————– —
PRD6A PHYSICAL STANDBY NOT ALLOWED MAXIMUM PERFORMANCE YES
SQL>
Make sure all the logs upto the gap is applied using v$archived_log and applied=YES. APPLIED=NO means MRP process is not applying logs on the standby database even though all the logs are available in standby database except the gaps.
SQL> SELECT SEQUENCE#,applied, FIRST_TIME, NEXT_TIME,REGISTRAR,status,END_OF_REDO_TYPE,DELETED FROM V$ARCHIVED_LOG;
SEQUENCE# APPLIED FIRST_TIM NEXT_TIME REGISTR S END_OF_RED DEL
———- ——— ——— ——— ——- – ———- —
34 YES 14-JUL-12 14-JUL-12 RFS A NO
35 YES 14-JUL-12 14-JUL-12 RFS A NO
36 YES 14-JUL-12 14-JUL-12 RFS A NO
37 YES 14-JUL-12 14-JUL-12 RFS A NO
38 YES 14-JUL-12 14-JUL-12 RFS A NO
39 YES 14-JUL-12 15-JUL-12 RFS A NO
40 YES 15-JUL-12 15-JUL-12 RFS A NO
41 YES 15-JUL-12 15-JUL-12 RFS A NO
42 YES 15-JUL-12 15-JUL-12 RFS A NO
43 YES 15-JUL-12 15-JUL-12 RFS A NO
44 YES 15-JUL-12 16-JUL-12 RFS A NO
SEQUENCE# APPLIED FIRST_TIM NEXT_TIME REGISTR S END_OF_RED DEL
———- ——— ——— ——— ——- – ———- —
45 YES 16-JUL-12 16-JUL-12 RFS A NO
71 NO 19-JUL-12 19-JUL-12 RFS A NO
73 NO 19-JUL-12 19-JUL-12 RFS A NO
75 NO 19-JUL-12 19-JUL-12 RFS A NO
SQL>
After 45 you will be able to see 71, archive log gap is 46-70. In this situation we can take the incremental backup from SCN
Take an Incremental backup of primary database using standby current SCN.
Get the current SCN from standby database
SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;
TO_CHAR(CURRENT_SCN)
—————————————-
7728610994157
Stop the MRP process (stop redo Apply)
SQL> alter database recover managed standby database cancel;
Database altered.
Make sure no MRP is working, from V$MANAGED_STANDBY
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY where PROCESS=’MRP0′;
no rows selected
Connect primary database and take the RMAN Incremental backup using below script.
$ rman target /
Recovery Manager: Release 11.2.0.2.0 – Production on Sun Jul 29 05:15:21 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRD6A (DBID=2752208625)
RMAN> BACKUP INCREMENTAL FROM SCN 7728610994157 DATABASE FORMAT ‘/db/flashback/rmanback/ForStandby_%U’ tag ‘STANDBY’;
RMAN>
Starting backup at 29-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=373 device type=DISK
backup will be obsolete on date 05-AUG-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/db/prd6a/dbf1/datafile/doc_001.dbf
.
.
.
channel ORA_DISK_1: starting piece 1 at 29-JUL-12
channel ORA_DISK_1: finished piece 1 at 29-JUL-12
piece handle=/db/flashback/rmanback/ForStandby_11nh9epm_1_1 tag=STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUL-12
channel ORA_DISK_1: finished piece 1 at 29-JUL-12
piece handle=/db/flashback/rmanback/ForStandby_12nh9f29_1_1 tag=STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JUL-12
RMAN>
Copy this backup piece from primary server to standby server and catalog the pieces with standby controlfile.
RMAN> catalog backuppiece ‘/db/backup/ForStandby_11nh9epm_1_1’;
after this start the recovery on the sane session.
RMAN> recover database noredo;
allocated channel: disk1
channel disk1: SID=250 device type=DISK
allocated channel: disk2
channel disk2: SID=368 device type=DISK
Starting recover at 29-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
….
..
..
.
channel ORA_DISK_1: reading from backup piece /db/backup/ForStandby_11nh9epm_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/db/backup/ForStandby_11nh9epm_1_1 tag=STANDBY
channel ORA_DISK_1: restore complete, elapsed time: 05:03:08
Finished recover at 29-JUL-12
Better to delete the backup from the standby using the TAG given while taking backup.
RMAN>delete backup tag ‘STANDBY’;
Start the managed recovery on standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
Now check the database MRP is applying proper logs, It must solve the issue
SQL> SELECT SEQUENCE#,applied, FIRST_TIME, NEXT_TIME,REGISTRAR,status,END_OF_REDO_TYPE,DELETED FROM V$ARCHIVED_LOG;
46 YES 16-JUL-12 16-JUL-12 RFS A NO
47 YES 16-JUL-12 16-JUL-12 RFS A NO
SEQUENCE# APPLIED FIRST_TIM NEXT_TIME REGISTR S END_OF_RED DEL
———- ——— ——— ——— ——- – ———- —
48 YES 16-JUL-12 16-JUL-12 RFS A NO
49 YES 16-JUL-12 16-JUL-12 RFS A NO
select process,client_process,status,thread#,sequence#,block#,blocks,DELAY_MINS from v$managed_standby;
In case if it still persist then we need to re-create the controlfile of standby database from primary database.
Why we are recreating the controlfile?
Why because state of the database might not be changed while applying incremental backup. So that the database_scn
will be intact and scn for datafile were updated so that the database will look for old file copy.
Steps to re-create the controlfile [Refer note 734862.1 for details]
take the backup of control file from primary database
$:>rman target /
RMAN> backup current controlfile for standby;
Starting backup at 29-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=370 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUL-12
channel ORA_DISK_1: finished piece 1 at 29-JUL-12
piece handle=/db/flashback/PRD6A/backupset/2012_07_29/o1_mf_ncnnf_TAG20120729T062512_818spbmh_.bkp tag=TAG20120729T062512 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JUL-12
Restore and start database using new controlfile
Copy this controlfile /db/flashback/PRD6A/backupset/2012_07_29/o1_mf_ncnnf_TAG20120729T062512_818spbmh_.bkp from primary to standby and
restore the control file on standby.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> Shut immediate;
SQL> startup nomount;
Restore the controlfile using RMAN
rman target /
restore standby comtrolfile from ‘/db/backup/2012_07_29/o1_mf_ncnnf_TAG20120729T062512_818spbmh_.bkp’;
SQL> shut immediate;
SQL> startup mount;
Start the MRP.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
Note: If we have different file location on primary and standby, then we need change the datafile
location in standby database.
use alter database command to change the file name in controlfile.
SQL> alter database rename file ‘<from  file-name >’ to ‘<to file-name >’;
Once everything is fine, Make sure standby logfile exist both physically and on the controlfile too, then start
managed recovery using standby logfile.
In case still you have the problem with again few log file and those are existing on the standby server
then manually register it using the below command
SQL> alter database register logfile ”;
Check the MRP status now, we will be able to see it has changed from ARCHIVE_LOG_GAP to APPLYING_LOGS.



Posted by at No comments:
Subscribe to: Posts (Atom)