Saturday, February 22, 2014

True way to stop start primary and standby databases.

There are have a lot of ways to stop/start primary and standby databases. I will show you one of them.

We needed to migrate physically servers to new plaza. So, I had to stop all with clean.

Parameters:

Primary Stanby
db_name FCDBPROD FCDBPROD
instance_name FCDBPROD FCDBSTND
open_mode READ WRITE MOUNTED
database_role PRIMARY PHYSICAL STANDBY
ip 10.10.10.10 10.10.10.20


Data guard broker was configured.

DGMGRL> show configuration

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database
    FCDBSTND - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


1. Stop applying. # I do it on Standby side

DGMGRL> edit database "FCDBSTND" set state="APPLY-OFF";

2. Shutdown Primary database

[oracle@fcdbdb ~]$ export ORACLE_SID=FCDBPROD
[oracle@fcdbdb ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
DGMGRL> 

Stop listener

lsnrctl stop

Shutdown OS


3. Shutdown Standby database

[oracle@fcdbdb_stby ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

DGMGRL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
DGMGRL>

Stop listener

lsnrctl stop

Shutdown OS



After unmount servers, migrate to new place and mount follow below:

Standby side
1. After start OS and listener, startup database

[oracle@fcdbdb_stby ~]$ export ORACLE_SID=FCDBSTND
[oracle@fcdbdb_stby ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

DGMGRL> startup mount
ORACLE instance started.
Database mounted.
DGMGRL>

2. Start primary database

[oracle@fcdbdb ~]$ export ORACLE_SID=FCDBPROD
[oracle@fcdbdb ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

DGMGRL> startup
ORACLE instance started.
Database mounted.
Database opened.
DGMGRL>


3. Enable apply process

DGMGRL> edit database "FCDBSTND" set state="APPLY-ON";
Error: ORA-12543: TNS:destination host unreachable
Error: ORA-16625: cannot reach database "FCDBPROD"

Failed.
DGMGRL>

There is error. Let check tnsping. There is connection problem. In my situation, after OS restart firewall was enabled. Stop it

/etc/init.d/iptables status #check status

/etc/init.d/iptables stop

If you want to turn off firewall on boot do:

chkconfig iptables off


Try to enable again

DGMGRL> edit database "FCDBSTND" set state="APPLY-ON";
Succeeded.
DGMGRL> show configuration

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database
    FCDBSTND - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>  show database "FCDBSTND";

Database - FCDBSTND

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    FCDBSTND

Database Status:
SUCCESS

DGMGRL> show database "FCDBPROD";

Database - FCDBPROD

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    FCDBPROD

Database Status:
SUCCESS


Now check archive logs.

On primary:

[oracle@fcdbdb ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Sat Feb 22 15:04:15 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

MAX(SEQUENCE#)
--------------
  722

SQL>


On Standby side

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

MAX(SEQUENCE#)
--------------
  722



now on primary switch logfile

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

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

MAX(SEQUENCE#)
--------------
  724

SQL>



check on standby

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

MAX(SEQUENCE#)
--------------
  722

SQL> /

MAX(SEQUENCE#)
--------------
  724

SQL>



At the end check alertlogs on both side

Check alertlog

stnaby alert

Thread 1 advanced to log sequence 725 (LGWR switch)
  Current log# 2 seq# 725 mem# 0: /u01/app/oracle/oradata/FCDBPROD/redo02.log
Sat Feb 22 15:05:42 2014
Archived Log entry 963 added for thread 1 sequence 724 ID 0xfb8f2b6 dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 725 for destination LOG_ARCHIVE_DEST_2


primary alert

Sat Feb 22 15:05:45 2014
Archived Log entry 461 added for thread 1 sequence 724 ID 0xfb8f2b6 dest 1:
Media Recovery Waiting for thread 1 sequence 725 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 725 Reading mem 0
  Mem# 0: /u01/app/oracle/fast_recovery_area/FCDBSTND/onlinelog/o1_mf_5_9f753t2h_.log


That`s all.

To configure data guard broker and to post this post I got some best practices video and strongly recommend to every one to watch my friend Mahir`s video series about data guard broker.

1 comment:

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...