Primary Database Changes That Require Manual Intervention at a Physical Standby

Primary Database Changes That Require Manual Intervention at a Physical Standby

Most structural changes made to a primary database are automatically propagated through redo data to a physical standby database. Table  lists primary database structural and configuration changes which require manual intervention at a physical standby database.

Table 9-1 Primary Database Changes That Require Manual Intervention at a Physical Standby

  Primary Database Change Action Required on Physical Standby Database
Add a datafile or create a tablespace No action is required if the STANDBY_FILE_MANAGEMENT database initialization parameter is set to AUTO. If this parameter is set to MANUAL, the new datafile must be copied to the physical standby database.
Drop or delete a tablespace or datafile Delete datafile from primary and physical standby database after the redo data containing the DROP or DELETE command is applied to the physical standby.
Use transportable tablespaces Move tablespace between the primary and the physical standby database.
Rename a datafile Rename the datafile on the physical standby database.
Add or drop a redo log file group Evaluate the configuration of the redo log and standby redo log on the physical standby database and adjust as necessary.
Perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause Copy the datafile containing the unlogged changes to the physical standby database.
Grant or revoke administrative privileges or change the password of a user who has administrative privileges If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to SHARED or EXCLUSIVE, replace the password file on the physical standby database with a fresh copy of the password file from the primary database.
Reset the TDE master encryption key Replace the database encryption wallet on the physical standby database with a fresh copy of the database encryption wallet from the primary database.
Change initialization parameters Evaluate whether a corresponding change must be made to the initialization parameters on the physical standby database.

Dataguard Monitoring Scripts

SET PAGESIZE 124

COL DB_NAME FORMAT A8

COL HOSTNAME FORMAT A12

COL LOG_ARCHIVED FORMAT 999999

COL LOG_APPLIED FORMAT 999999

COL LOG_GAP FORMAT 9999

COL APPLIED_TIME FORMAT A12

SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,LOG_ARCHIVED-LOG_APPLIED LOG_GAPFROM(SELECT NAME DB_NAMEFROM V$DATABASE),(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.’),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,’.’)-1))))) HOSTNAMEFROM V$INSTANCE),(SELECT MAX(SEQUENCE#) LOG_ARCHIVEDFROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’),(SELECT MAX(SEQUENCE#) LOG_APPLIEDFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’),(SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIMEFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);

 

 

 

================================================================================

 

SQL> select status,instance_name,database_role fromv$instance,v$database; 

STATUS       INSTANCE_NAME    DATABASE_ROLE

——————————————–

OPEN         prim             PRIMARY

 

SQL> select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

————–

40

Check the status of the physical standby database and the latest sequence applied on the physcial standby database.

 

 

 

 

 

 

 

 

 

SQL> select status,instance_name,database_role fromv$database,v$instance; 

STATUS   INSTANCE_NAME DATABASE_ROLE

——————————————

MOUNTED  stnd          PHYSICAL STANDBY

 

SQL> select max(sequence#) from v$archived_log where applied=’YES’;

 

MAX(SEQUENCE#)

————–

40

 

 

Check if the Managed Recovery Process (MRP) is active on the physcial standby database.

 

 

 

 

 

 

 

 

 

 

 

 

 

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

PROCESS   STATUS        SEQUENCE#

——————————-

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

RFS       IDLE          41

RFS       IDLE          0

RFS       IDLE          0

RFS       IDLE          0

MRP0      WAIT_FOR_LOG  41

 

9rows selected.

Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.

Step 4:

Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.

 

 

 

 

 

 

 

 

 

 

 

SQL> alter database recover managed standby database cancel; 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> select status,instance_name,database_role,open_mode fromv$database,v$instance;

 

STATUS INSTANCE_NAME  DATABASE_ROLE    OPEN_MODE

—————————————————

OPEN   stnd           PHYSICAL STANDBY READ ONLY

Step 6:

Now start the MRP on the physical standby database.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL> alter database recover managed standby database disconnectfrom session; 

Database altered.

 

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

 

PROCESS   STATUS        SEQUENCE#

——————————-

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

RFS       IDLE          41

RFS       IDLE          0

RFS       IDLE          0

RFS       IDLE          0

MRP0      WAIT_FOR_LOG  41

 

9rows selected.

Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.

 

 

 

Managing dataguard – monitoring scripts

  LOGS             TIME—————- ——————

Last applied   :  16-JUL-09:09:24:16

Last received :  16-JUL-09:09:28:36

 

 

 

 

 

    select ‘Last applied  : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Timefrom v$archived_log

where sequence# = (select max(sequence#) from v$archived_log where applied=’YES’)

union

select ‘Last received : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Time

from v$archived_log

where sequence# = (select max(sequence#) from v$archived_log);

 

 

 

 

 

 

 

 

 

 

NAME                   VALUE                  UNIT———————- ———————- ———————————–

apply finish time      +00 00:02:07.2         day(2) to second(1) interval

apply lag              +00 00:01:59           day(2) to second(0) interval

estimated startup time 16                     second

standby has been open  N

transport lag          +00 00:00:00           day(2) to second(0) interval

 

Time Computed: 16-JUL-2009 09:33:16

 

 

 

 

 

 

 

 

 

 

selectNAME Name,

VALUE Value,

UNIT Unit

from v$dataguard_stats

union

select null,null,’ ‘ from dual

union

select null,null,’Time Computed: ‘||MIN(TIME_COMPUTED)

from v$dataguard_stats;

  Redo onsite

——————–

16-JUL-2009 09:42:44

 

 

select to_char(max(last_time),’DD-MON-YYYY HH24:MI:SS’) “Redo onsite”from v$standby_log

 

Recovering a Standby database from a missing archivelog

Recovering a Standby database from a missing archivelog
A Physical Standby database relies on continuous application of archivelogs from a Primary Database to be in synch with it. In Oracle Database versions prior to 10g in the event of an archivelog gone missing or corrupt you had to rebuild the standby database from scratch.

In 10g you can use an incremental backup and recover the standby using the same to compensate for the missing archivelogs as shown below

In the case below archivelogs with sequence numbers 137 and 138 which are required on the standby are deleted to simulate this problem.

Step 1: On the standby database check the current scn.

STDBY>>select current_scn from v$database;
CURRENT_SCN
———–
4793543

Step 2: On the primary database create the needed incremental backup from the above SCN

$>rman target /
Recovery Manager: Release 10.2.0.2.0 – Production

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: USYD (DBID=768471617)

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 4793543 DATABASE
FORMAT ‘C:\temp\bkup_%U’;

Starting backup at 25/AUG/09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\SYSTEM01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\UNDOTBS01.DBF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\SYSAUX01.DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 25/AUG/09
channel ORA_DISK_1: finished piece 1 at 25/AUG/09
piece handle=C:\TEMP\BKUP_02I0NSNE_1_1 tag=TAG20061025T134102 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 25/AUG/09
channel ORA_DISK_1: finished piece 1 at 25/AUG/09
piece handle=C:\TEMP\BKUP_03I0NSOI_1_1 tag=TAG20061025T134102 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25/AUG/09

Step 3: Cancel managed recovery at the standby database

STDBY>>recover managed standby database cancel;
Media recovery complete.

Move the backup files to a new folder called INCR_BACKUP so that they are the only files in that folder.

Step 4: Catalog the Incremental Backup Files at the Standby Database

$>rman target /

Recovery Manager: Release 10.2.0.2.0 – Production

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: USYD (DBID=768471617, not open)

RMAN> CATALOG START WITH ‘C:\temp\INCR_BACKUP’;
searching for all files that match the pattern C:\temp\INCR_BACKUP

List of Files Unknown to the Database
=====================================
File Name: C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_1_1
File Name: C:\TEMP\INCR_BACKUP\BKUP_03I0NSOI_1_1

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_1_1
File Name: C:\TEMP\INCR_BACKUP\BKUP_03I0NSOI_1_1

Step 5: Apply the Incremental Backup to the Standby Database

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 25/AUG/09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\SYSTEM01.DBF
destination for restore of datafile 00002: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\UNDOTBS01.DBF
destination for restore of datafile 00003: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\SYSAUX01.DBF
destination for restore of datafile 00004: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_
1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_1_1 tag=TAG20061025T134102
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished recover at 25/AUG/09

Step 6: Put the standby database back to managed recovery mode.

STDBY>> recover managed standby database nodelay disconnect;
Media recovery complete.

From the alert.log you will notice that the standby database is still looking for the old log files

*************************************************
FAL[client]: Failed to request gap sequence
GAP – thread 1 sequence 137-137
DBID 768471617 branch 600609988
**************************************************

This is because the controlfile has not been updated.
Hence the standby controlfile has to be recreated

On the primary

PRIM>>alter database create standby controlfile as ‘C:\temp\standby01.ctl’;
System altered.

Copy the standby control file to the standby site and restart the standby database in managed recovery mode…

Managed Standby Recovery not using Real Time Apply
parallel recovery started with 2 processes
Media Recovery Waiting for thread 1 sequence 139

As you can see from the above output the standby is now looking for the next archivelog.