• About Me

    Maxwell Miranda
    Oracle DBA Consultant.
  • Category

  • Archives

  • My Blog Calendar

    February 2018
    M T W T F S S
    « Sep    
     1234
    567891011
    12131415161718
    19202122232425
    262728  
  • RSS Oracle Feeds

    • Can't access database after power outage
      I could access the database before the power outage. From what I've read, it is a problem with the environment variables, but they are correct. Not sure what else to add. C:\Documents and Settings\konoca>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 21 09:17:08 2018 Copyright (c) 1982, 2005, Oracle. All rights reserved. ERR […]
    • Problems with RESET DATABASE
      Recently I wanted to do RESET DATABASE. Oracle documentation suggests the following commands connect target / startup force nomount reset database to incarnation 2 run { set until time "date time" restore controlfile from autobackup alter database mount restore database recover database } alter database open resetlogs In this view my db has given e […]
  • Advertisements

Dataguard Scripts – Monitoring

Note: This query needs to be run on the Primary database.

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_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.’),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,’.’)-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
);
DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
——– ———— ———— ———– ———— ——-
CPSPRD PRDU009N1 11213 11213 30-JUN/08:48 0
================================================================================

SQL> select status,instance_name,database_role from v$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 from v$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

9 rows 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 from v$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

9 rows 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.

Advertisements