RE: Standby database switching between read-only and recovery modes question

From: Ruel, Chris <Chris.Ruel_at_lfg.com>
Date: Wed, 15 Apr 2015 18:11:55 +0000
Message-ID: <1AFD62082EEAF0448EF1815139687F132BC09E17_at_NC2PWEX504.us.ad.lfg.com>

From a past life as evidenced by the date in my script(wow, has it been that long?!?) Might need to be hacked a little bit…used it on 10g. I have since learned better ways to authenticate than hard-coding passwords so change or use at your own risk. Also, FWIW, I swapped my actually primary database name with <primary>…insert your own.

Additionally, this opens standby read-write with a restore point. I know this is not what you asked, but, I think it is more the mechanics of managing the change you are interested in. In any case, I was running two standby’s on the same machine…one stayed in recovery mode, one open for developers, every day, to do whatever they wanted. Then, at night, I would flash it back, let it catch up with overnight batch, and, open it again in the morning. This way, they had a fresh copy every day. You could also take this as it is and just run some permission commands to essentially make it read only…but, it might be over engineered for your purposes. However, having a database you can report on (and maybe test changes, that automatically refreshes every day is pretty nice). To just open it read only, shouldn’t be that hard to change.

All scheduled through cron on the standby machine.

1. Script to take it out of recovery mode, create a restore point, and, open the database:

I think I had this scheduled to run at like 6:00 AM.

#!/usr/bin/ksh
# Name: dg_activate_standby.ksh
# Date: 27 November 2009
# Chris Ruel (chris.ruel_at_lfg.com)
# Desc: Shell script to activate Standby database
#
# Usage: takes 1 argument, ORACLE_SID. Example: dg_activate_standby.ksh <standby_db_name>
#
# Updates:
#

# this is just an environment script I wrote
. /opt/oracle/admin/scripts/oracle_env.ksh $1

export DATESTAMP=`date '+%Y%m%d'`

# Disable Database in broker configuration

dgmgrl << EOF
CONNECT sys/********
disable database '$ORACLE_SID';
EXIT
EOF
# Create restore point on standby

sqlplus -s /nolog << EOF
CONNECT sys/******** as sysdba
recover managed standby database cancel; create restore point fb_${ORACLE_SID}_${DATESTAMP} guarantee flashback database; EXIT
EOF
# Archive current log on primary

sqlplus -s /nolog << EOF
CONNECT sys/********_at_<primary> as sysdba alter system archive log current;
alter system set log_archive_dest_state_2 = defer; EXIT
EOF
# Active standby database

sqlplus -s /nolog << EOF
CONNECT sys/******** as sysdba
alter database activate standby database; startup mount force;
alter database open;
EXIT
EOF 2. Script to flash it back to restore point and re-enable recovery

I think I had this scheduled to run at like 8:00 PM.

#!/usr/bin/ksh
# Name: dg_flashback_standby.ksh
# Date: 27 November 2009
# Chris Ruel (chris.ruel_at_lfg.com)
# Desc: Shell script to kick off the Standby Flashback procedure
#
# Usage: takes 1 argument, ORACLE_SID. Example: dg_flashback_standby.ksh <standby_db_name>
#
# Updates:
#

# this is just an environment script I wrote
. /opt/oracle/admin/scripts/oracle_env.ksh $1

export DATESTAMP=`date '+%Y%m%d'`

# Flashback standby to restore point

sqlplus -s /nolog << EOF
CONNECT sys/******** as sysdba
startup mount force;
flashback database to restore point fb_${ORACLE_SID}_${DATESTAMP}; alter database convert to physical standby; startup mount force;
EXIT
EOF
# Enable Database in broker configuration

dgmgrl << EOF
CONNECT sys/********_at_<primary>
enable database '$ORACLE_SID';
EXIT
EOF
# Drop restore point

sqlplus -s /nolog << EOF
CONNECT sys/******** as sysdba
drop restore point fb_${ORACLE_SID}_${DATESTAMP}; EXIT
EOF

---

Chris Ruel * Oracle Database Administrator * Lincoln Financial Group cruel_at_lfg.com<mailto:cruel_at_lfg.com> * Desk:317.759.2172 * Cell 317.523.8482

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Taylor Sent: Wednesday, April 15, 2015 1:25 PM To: oracle-l_at_freelists.org
Subject: Standby database switching between read-only and recovery modes question

For those of you who switch your standby db between read-only and recovery mode (non active data guard), how do you manage it?

I've in the process of configuring a reporting database as a standby db, and I *think* I'm going to have to have a script that does 2 things (or 2 scripts).

1 - end recovery mode/open the db read-only during business hours 2 - close the db and put it back in recovery mode

I'm wondering about the "best" way to accomplish item #2. If there are users connected to the DB, I have to kill those sessions *if* I want to do an "alter database close". Should I just scratch that idea and do a shutdown abort on the standby and reopen it?

I'm basically curious how other people are managing the switch between read-only and recovery modes and if you're killing sessions or just shutting down the db to put it back in recovery mode?

Thanks,
Chris
Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.**

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 15 2015 - 20:11:55 CEST