Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 03, 2002 - 9:17 am UTC

Last updated: February 26, 2017 - 2:02 am UTC

Version: 9.0.0

Viewed 10K+ times! This question is

You Asked

Hello, Tom

Can you explain guaranteed protection mode of the Data Guard?
What happens with primary database instance in this mode if standby
database is unavailiable? Documentation says in this case all changes to primary is prohibited. If it is so, it is not HA tool for me.
In this case I have more that double risk that my database becomes unavailiabe: in case of hardware failure on primary AND standby PLUS network between them.


and Tom said...

There are 4 modes to data guard:

a) guaranteed
b) instant
c) rapid
d) delayed (this is the old standby from 8i and before mode)

Data Guard is many things to many people. Data Guard can be "make sure my data persists EVEN if the building in which it was inserted is destroyed". In that case, guaranteed is what you want. Here you prize the data more then anything else in the world. Who might use this? Well -- a bank would. If you transfer $100,000 from account 1 to account 2 and get a confirmation back -- you expect that to be persistant. Well, if it weren't done in more then one location -- SIMULTANEOUSLY -- you would be less assured of that being the case. Banking institutions would use this level of assurance to have the data reside in two geographically disperse locations at all times. They would use this as a replacement for syncronous replication and a two phase commit. Here data guard is providing two things:

a) extremely high levels of data assurance. If it was committed, we got it.
b) a failover site that can be failed over to AS SOON AS A standby site for it is constructed (makes no sense to fail over if your requirement is to have two copies AT ALL TIMES)

So, data guard in this mode provides for exactly what they need.


I believe most situations will permit RAPID mode -- but you must remember that running in rapid mode when the standby is unavailable -- is sort of like running with a failed mirror disk in mirrored pairs. You can still write to the disk but if the disk fails -- you no longer have a mirror do you. It is up to you whether you want to continue or you want to stop.


In short -- data guard has many levels, not just one, and you must pick the appropriate level based on YOUR needs, YOUR situation. If one size fit all -- well, that would be boring ;)



Rating

  (247 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Redo X SQL stream

Andre, June 03, 2002 - 5:27 pm UTC

1) The redo stream is made up of blocks using Data Guard whereas it was done in whole logs in 8i. Is it correct ? What size are these blocks ?

2) In 9i R2, there's this new SQL sream. How does it work compared to the previous release ?

Tom Kyte
June 03, 2002 - 5:45 pm UTC

1) no, not really. using Data Guard, we can have lgwr stream the logs over to the standby instance, but we still wait until a log is filled to apply it. It is just a different transport. Instead of ARCH doing it, LGWR does it. This allows us to make it so the latest greatest logs are on the standby site (waiting to be filled, waiting to be applied)

subtle difference, but a difference.

In 9iR2, we can mine the logs in more "real time"

2) it can happen in more "real time" basically. See
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96571/toc.htm <code>
....

difference between guaranteed and instant mode

A reader, October 17, 2002 - 7:33 am UTC

hi

I have difficulties understanding the difference between these two modes:

In guaranteed data divergence is not allowed whereas in instant it is but data loss is not allowed. But consider this situation

If I am in guaranteed mode then if I lose network connection my primary database transactions wll not commit

If I am in instant mode then if network connection is lost then my primary database transactions will not commit too (since no data loss means no changes can be made in primary if data is not in standby, without network data will never get to standby)

So what´s the difference in this case? I see zero difference :-0

Tom Kyte
October 17, 2002 - 7:43 am UTC

guaranteed mode says

"when we cannot get the redo to the failover site when we commit -- STOP, FAIL, HALT"

instant says

"when we cannot get the redo to the failover site when we commit -- don't stop, don't fail -- rather fall back into using archives, notify the DBA, make OEM flash red, get the problem fixed ASAP"


guaranteed mode = 2 phase commit like protection. either both or neither are updated.

instant = when all things are working normally, standby is upto the second. when something breaks in the connectivity between the two -- SOUND THE ALARMS, but don't stop production

Data Guard

David, September 01, 2003 - 11:19 am UTC

Tom, I am setting up a 9.2 data guard environment. We have gone through the procedures and some testing.

However we don't have enough experience with in production.

We have:

log_archive_dest_1='service=prod2 lgwr'

that is "maximum performance".

My worst fear is that the standby db may affect the primary somehow (making it hang, for one) if something is wrong. Is it possible ? Or should I just take it easy ?

Tom Kyte
September 01, 2003 - 12:35 pm UTC

why is that a "fear"

for some people -- that is the desired result actually. having production running without a failure is their fear.

if you are configured for maximum performance, then the database will fall back to archive log shipping in the event the standby is not available. You may have to perform some manual syncing after the standby becomes available if you move the archives on us.

A reader, November 06, 2003 - 1:34 pm UTC

Tom,

1. if using data guard command line utility dgmgrl.
Server1 is my production site and Server2 is my standby site
From where should i execute dgmgrl from production or fom standy.

2.Once physical standby database is set up it should be in mount mode or managed recovery mode for setting up data guard configuration.

Thanks.

Logical standby

reader, February 25, 2004 - 6:58 pm UTC

After reading the doc, logical standby is not the solution for "zero data loss". Is this a correct understanding? Only Physical standby provides "zero data loss" protection.

Also, I read that logical standby can be created on a different OS from the primary? How is this possible? Thanks.

Tom Kyte
February 25, 2004 - 7:43 pm UTC

a true logical standby is homogenous (same OS) and can run in zero data loss mode.

zero data loss mode requires the redo be written LOCALLY and REMOTELY, it cares not if the recipient is a physical or logical.



Data Guard

pinkfloyd, June 15, 2004 - 9:13 am UTC

Hi Tom
I'm facing a problem with Oracle Data Guard. I have configured the primary and the standby database. The standby database is running in managed recovery mode. when I try to connect to the standby database through netassistant I get ORA-01034 Oracle Not Available error. I see this same error in the v$archived_status view on the primary database.
Where am I going wrong?????


Tom Kyte
June 15, 2004 - 3:47 pm UTC

why would you try to connect to the database via netassistant?

Data Guard

pinkfloyd, June 16, 2004 - 2:00 am UTC

How will my Primary database connect to the standby database. The v$archived_status is showing "oracle not available" error. Primary database is not gettin the secondary database. should the listeners be listening in different port. I'm gettin confused in configuring the tnsnames.ora file

Tom Kyte
June 16, 2004 - 12:31 pm UTC

did you follow the step by step instructions that are documented in the data guard guide?

Data Guard

PinkFloyd, June 17, 2004 - 2:54 am UTC

Hello Tom!
This is what I have done
The Primary database(testdb) is on the linux ver 7.1 box
I took the cold backup and restored it in another linux ver7.1 box. the database name here is the same as primary (testdb)
Created a standby control file in the primary db
using <ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u04/stbycf.f'> command and
restored the standby control file in the standby database.

Did the followin changes to the primary database(testdb)init.ora file

CONTROL_FILES=primary.ctl
COMPATIBLE=9.2.0.1.0
LOG_ARCHIVE_START=true
LOG_ARCHIVE_DEST_1='LOCATION=/var/archive MANDATORY REOPEN=30'
LOG_ARCHIVE_DEST_2='SERVICE=stby LGWR SYNC AFFIRM'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
LOG_ARCHIVE_FORMAT=arc%t_%s.arc
REMOTE_ARCHIVE_ENABLE=true

Did the following changes to the standby init.ora file
SERVICE_NAMES = stby
CONTROL_FILES=/u01/control/stbycf.f
COMPATIBLE=9.2.0.1.0
LOG_ARCHIVE_START=true
LOCK_NAME_SPACE=stby
FAL_SERVER=primary
FAL_CLIENT=stby
STANDBY_ARCHIVE_DEST=/var/archive
LOG_ARCHIVE_DEST_1='LOCATION=/var/archive'
LOG_ARCHIVE_TRACE=127
LOG_ARCHIVE_FORMAT=arc%t_%s.arc
STANDBY_FILE_MANAGEMENT=auto
REMOTE_ARCHIVE_ENABLE=true

created services on both primary and standby database with names <primary> and <stby>

configured listeners on both primary and standby databases to listen on port 1521

started the standby database in nomount state.
mounted the standby database with this command
<alter database mount standby database>
put the stanby database in recovery mode using
<alter database recover managed standby database >

on the primary site, i opened the database,
issued the log switch.
checked the v$archive_dest
status is showing error
and error showing as Oracle Not available.


Tom Kyte
June 17, 2004 - 9:56 am UTC

question was -- did you follow the setup directions as documented.... (chapter 3) it has the blow by blow, step by steps.... you have not listed them all here.

Data Guard

PinkFloyd, June 19, 2004 - 2:56 am UTC

Hello Tom
I finally did it. Both the primary and the standby database's are configured. I guess i made some mistake configuring the listener and the tnsnames.
Its working now. Thanks for the support


Data Guard Protection Modes

Sami, July 11, 2004 - 7:56 pm UTC

Dear Tom,

In metalink "Doc ID: Note:186150.1", I don't see any step to select "Data Guard Protection Modes".

Could you please explain, how to choose between "Maximum protection" and "Maximum availability" while configuring Data Guard?


Tom Kyte
July 12, 2004 - 11:08 am UTC

have you checked out the docs? they have step by steps in them.

Reason to drop & recreate temp tablespace while creating logical stby db

Sami, July 12, 2004 - 12:08 pm UTC

Dear Tom,
I checked the doc at
http://otn.oracle.com/deploy/availability/htdocs/DataGuardRedoShipping.htm?_template=/ocom/technology/content/print
Now I am pretty clear.


My next question:-
=================
Is there any particular reason why do we have to drop and recreate temporary tablespace while creating logical stby db?

The document says as below..

4.2.16 Create a New Temporary File for the Logical Standby Database
The temporary files, which were included as a part of the closed backup
operation on the primary database, are not viable on the logical standby
database. (It is not necessary to copy temporary files from the primary
database to the logical standby database.)

To identify and drop obsolete temporary files, perform the following steps
on the logical standby database.

Step 1 Identify the current temporary files.
On the logical standby database, issue the following query to identify the
current temporary files for the standby database:

SQL> SELECT * FROM V$TEMPFILE;
no rows selected


If this query returns "no rows selected" as shown in the example, skip step
2 and go to step 3.

Step 2 Drop each current temporary file from the standby database.
Drop each current temporary file from the standby database, as follows:

SQL> ALTER DATABASE TEMPFILE 'tempfilename' DROP;
 

Tom Kyte
July 12, 2004 - 8:26 pm UTC

because tempfiles are not datafiles, you don't back them up, you don't restore them -- you just recreate them.

Creating physical standby

A reader, July 20, 2004 - 10:46 pm UTC

In

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/create_ps.htm#61209 <code>

"Copying the datafiles to a temporary location will reduce the amount of time that the primary database must remain shut down"

This requires my primary database server to have storage equal to at least twice the size of my database. For a database of >500G, this is a little impractical.

What if I can afford the primary database to be down for the time it takes to create the physical standby? How can I get by without needing to make a local copy of my primary database?

Thanks

Tom Kyte
July 21, 2004 - 7:29 am UTC

First, make backups of your primary database datafiles. You create the standby datafiles from these backups. You can use any backup of the primary database so long as you have archived redo logs to completely recover the database. The backup can be old or new, consistent or inconsistent. Hot backups (or open backups) allow you to keep the database open while performing the backup. Nevertheless, you may prefer to make a new closed, consistent backup using the Recovery Manager utility (RMAN) to prevent the application of a large number of archived redo logs.



the primary database never needs to go down at all.

How to do this in DataGuard?

A reader, July 21, 2004 - 6:58 am UTC

Currently I have a homegrown brew of Unix shell scripts, cron jobs, etc to manually 'rcp' my primary archive logs to the standby site, apply them. Now that we are on 9iR2, I want to start using DataGuard's physical standby database feature.

1. I want to retain a week's worth of archive logs on both the primary site and standby site. The physical disk (18GB) is not enough to hold 1 week's worth of logs. So I have a cron job that periodically moves them to 7 different disks with mountpoints named using 'date +%w' so that they automatically wrap around.

How can I do this using DataGuard? Ideally I could just specify 'date +%w' in the LOG_ARCHIVE_DEST_n parameter so that each day of the week the logs are locally stored and sent to a different directory and the standby's log apply srevices also pick it up from date +%w

2. After 7 days I want to delete the oldest archive logs. I do this using the traditional Unix 'find ... -mtime +7 | xargs rm -f'.

How can Oracle/DataGuard help me with this automatic purging of archived redologs?

Thanks

Tom Kyte
July 21, 2004 - 8:34 am UTC

it is not really a dataguard issue -- but a database issue.


You can use an alter system command to change the archive destination -- you can use dbms_job to automate it (eg: every sunday, job runs, does the alter system)


for the purging, you are still "scripting" -- or you might consider using rman and scripting via that.

Creating physical standby

A reader, July 21, 2004 - 9:34 am UTC

"the primary database never needs to go down at all"

Ok so what you are suggesting is slightly different than the DataGuard Admin guide where they have you shut down your primary database.

Just so I understand you, here is what you are suggesting?

1. ALTER DATABASE FORCE LOGGING;
2. Setup LOG_ARCHIVE_DEST_n appropriately
3. Put each tablespace in hot backup mode and copy the files to the standby server
4. ALTER DATABASE CREATE STANDBY CONTROLFILE ...
5. Copy the standby controlfile to the standy server
6. Verify the pfile on the standby server has all the necessary parameters (e.g, standby_archive_dest, db_file_name_convert, etc)
7. Verify Net services config on both sides, lsnrctl stop/start, etc
8. On the standby site, startup nomount, alter database mount standby database
9. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


Am I missing anything? Thanks

Tom Kyte
July 21, 2004 - 10:41 am UTC

Creating physical standby

A reader, July 21, 2004 - 1:51 pm UTC

Well, I am not using RMAN in our environment yet, so using it for just this purpose seems like overkill.

Regardless, I am not sure how this will perform.

DUPLICATE TARGET DATABASE FOR STANDBY;

The documentation doesnt refer to any channel allocation for this command. So, I assume it will sequentially take 1 datafile at a time and send it over the Net8 link?

Instead, if I copy the datafiles manually by writing rcp/ftp scripts and running multiple ftp's in the background, I am easily able to saturate my CPU and network (T3) bandwidth. Can RMAN saturate a T3 line?

If I wanted to do this manually, can you please review my steps above and see if I am missing anything?

Thanks

Tom Kyte
July 21, 2004 - 6:37 pm UTC

if you check out chapter 2 of the 9ir1 documentation (on otn.oracle.com), it has the steps listed out in there using an "old" backup.

rman could eat whatever you got.

A reader, July 21, 2004 - 10:08 pm UTC

"if you check out chapter 2 of the 9ir1 documentation (on otn.oracle.com), it has the steps listed out in there using an "old" backup"

Um, chapter 2 of what book exactly? I looked at the RMAN user guide and it is not much different than the 9iR2 one.

"rman could eat whatever you got"

Well, if rman internally just spawns off multiple threads and hammers the Net8 link, it would perform the same, I guess, I just didnt see from the docs where it said how exactly it implements the 'DUPLICATE DATABASE FOR STANDBY' command.

Looks like benchmark time! I will try to do a benchmark if I can

Tom Kyte
July 22, 2004 - 7:12 am UTC

chapter 2 of the dataguard docs, sorry.



Data Guard Broker

A reader, July 21, 2004 - 11:00 pm UTC

OK I got it, you meant the Data Guard Admin guide.

I compared the Data Guard Broker guide between 9iR1 and 9iR2 and strangely enough, 9iR1's Data Guard Broker supports both RMAN and OS file copies to create the standby database

</code> http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a88807/gui.htm#1048550

But 9iR2's Broker seems to have dropped support for RMAN for creating the standby database. Any idea why?

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96629/gui.htm#1005707 <code>

I guess I can just create the standby database manually (or using RMAN) and then inform the Data Guard processes about it.

In general, do you think the DG Broker GUI is needed or can the CLI be used to do everything? I dont have the Enterprise Manager infrastructure installed in our environment yet and the DG Broker GUI seems to require it.

Thanks

Tom Kyte
July 22, 2004 - 7:16 am UTC

rman is still fully supported, the documentation is right there for rman and "how to".

the dgmgrl cli can do it all, the gui is generally just a layer on top of that.

logical standby

Sam, July 22, 2004 - 9:54 am UTC

My understanding is that I can have logical standby created on a different OS platform from that of primary database? Is this correct? Thanks.

Tom Kyte
July 23, 2004 - 7:36 am UTC

that is not correct -- they must be homogenous operating systems.

init.ora

A reader, July 23, 2004 - 11:35 am UTC

In a Data Guard configuration, how does FAST_START_MTTR_TARGET interact with ARCHIVE_LAG_TARGET? The latter forces a log switch so as to ship the logs to the standby database.

So, if I have FSMT=10 minutes but ALT=5 minutes, does it mean that FSMT is effectively 5 minutes? In other words, does Oracle tune checkpoints to meet a MTTR of 5 mins or 10mins?

Thanks

Tom Kyte
July 23, 2004 - 4:19 pm UTC

they do not really.

fast_start_mttr_target triggers incremental checkpoints, archive lag says "make sure standby has archives at least every N units of time".

fsmt = 10 could fire incrementals every 1 minute (or whatever) depending on the load.

but alt = 5 will do a log switch every 5 minutes regardless.

so it is a "whatever happens first" -- every 5 minutes you'll have a background checkpoint -- but fsmt could be initiating incremental checkpoint more often (could be.)

and if you are using lgwr to transmit redo with DB, alt would not need to be used as the redo is transmitted upon commit.

spfile

A reader, July 23, 2004 - 11:53 am UTC

See

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/create_ps.htm#61079 <code>

Why do I need to use a spfile on my physical standby database?

I am not planning to use DG broker or anything, just plain Data Guard.

Thanks

Tom Kyte
July 23, 2004 - 4:28 pm UTC

you don't "need to", it would make life better. if you don't have an spfile, it'll default to looking for the pfile in the normal location.

using backup controlfile to setup standby db

Sean, July 23, 2004 - 10:08 pm UTC

Hi Tom,

I am following the instruction in chapter 3 of Data guard concept manual to create physical standby database (Oracle 920).  My primary database is called "summer" and my standby database is called "spring". 

I created standby database control file from primary database by following section 3.2.3 and move it to new location:
>ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'E:\oracle\oradata\spring\ctlspring.CTL';


When I startup standby database, I got the following error:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE MOUNT STANDBY DATABASE
*
ERROR at line 1:
ORA-01103: database name 'SUMMER' in controlfile is not 'SPRING'

Well, it did not really surprise me because I simply moved the backup controlfile  to different location and didn't edit the controlfile to change the database name and datafile location.  But the document didn't ask us to modify the control file.  I am aware of the way to create control file is a little different from regular way to backup controlfile.  But still, it seems that there is no way the backup controlfile will register the standby database name.

I followed the instruction step by step and set up both primary and standby in the same box(I know that you don't like the way to setup standby db, even for testing).

What step did I miss?

Thanks so much for your help.





 

Tom Kyte
July 24, 2004 - 11:16 am UTC

did the standby database have the right INIT.ORA file at that point? was the init file pointing to h:.....

use "nid" command to change the target/logical stanby db name

Sami, July 24, 2004 - 4:15 pm UTC


using backup controlfile to setup standby db

Sean, July 24, 2004 - 5:18 pm UTC

Hi Tom,

Here is the step by step that I followed the instruction of chapter 3 of Data Guard Concept (920).
----------------------------------------------------
Primary database:  Summer
Standby database:  Spring
(1) Set Primary db in archive mode, shutdown primary db and copy datafile to different location.
   In my case, copy file from E:\oracle\oradata\summer 
             to   E:\oracle\oradata\spring

(2) Startup primary db and create standby db controfile in primary db.
    >ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'E:\oracle\oradata\spring\ctlspring.CTL';

(3) Create pfile from spfile in Primary db
    >CREATE PFILE='E:\oracle\admin\spring\pfile\initspring.ora' FROM SPFILE;

(4) Edit pfile in standby db, change "Summer" to "Spring".

(5) Create window service
WINNT>oradim -NEW -SID spring -STARTMODE manual

(6) Configure listener and tnsname for standby db.

(7) Create spfile from pfile for standby db and mount standby db.
Winn>set oracle_sid=spring
E:\>sqlplus
Enter user-name: / as sysdba
Connected to an idle instance.
SQL>CREATE SPFILE FROM PFILE='E:\oracle\admin\spring\pfile\initspring.ora';
SQL> STARTUP NOMOUNT 
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;   
-- got error
ALTER DATABASE MOUNT STANDBY DATABASE
*
ERROR at line 1:
ORA-01103: database name 'SUMMER' in controlfile is not 'SPRING'
-------------------------------------------------------------------

From the error message, it is clearly show that SQL read standby init file (db_name is 'spring'), it also
read controlfile in standby db fold (Here is the parameter in Spring init file:
        control_files='E:\oracle\oradata\spring\CTLSPRING.CTL')

But db name in standby controlfile is still 'Summer'.

Since I didn't change any thing for standby db controlfile after step(2), it seems obivious that db name is still primary db name('Summer').

My question is really that according to Oracle doc(chapter 3 of Data Guard) why we can use backup controlfile from primary db for standby db without changing anything in it such as db name and datafile location.

Thanks so much for your help.





     

Sean, you may need to set lock_name_space parameter in the standby init.ora file?

reader, July 24, 2004 - 5:58 pm UTC


lock_name_space in standby controfile

Sean, July 24, 2004 - 7:45 pm UTC

Thanks so much,

You are right.  I have to set lock_name_space=standby_instance_name

Also, I found that I should keep the same db_name as primary db name (I chanced the everything by using replacing).  Now I know that primary and standby should use same db name.

I got the different error, but sovled by adding one line code:
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE MOUNT STANDBY DATABASE
*
ERROR at line 1:
ORA-01990: error opening password file 'E:\oracle\ora92\DATABASE\PWDspring.ORA'

-- the error is gone after this command:
E:\>ORAPWD FILE=E:\oracle\ora92\database\PWDspring.ora PASSWORD=password ENTRIES=30

SQL>  ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.


 

v$managed_standby

A reader, July 25, 2004 - 2:48 pm UTC

I managed to setup a physical standby DataGuard environment and everything is working fine, but I was just testing the FAL stuff by simulating various scenarious. I shut down the standby database, switched a few logs on the primary, saw that the FAL client/server worked nicely to ship the missing logs and catch up the standby.

But in the middle of all this, I issued the query on the physical standby

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 293035 0 0
RFS RECEIVING 0 0 0 0
RFS ATTACHED 1 293028 3 3

1. What is thread# 0? I have a non-RAC environment with only one redo thread #1.
2. Why are there 2 ARCH processes?
3. If MRP0 is waiting for sequence# 293035, what is one of the RFS processes doing 'ATTACHED' with 293028?

But all in all, I am very impressed with the depth and breadth of DataGuard. Much much better than 8i's standby database! Lots of v$ views to monitor stuff, the FAL stuff i simply fantastic.

Thanks

Tom Kyte
July 25, 2004 - 4:35 pm UTC

thread zeroes aren't doing anything. (you'd have thread# 1, 2, 3, ... in rac).

thread zero in rac would mean "find an available, enabled public thread and use it" -- hence it wouldn't really be zero anymore.


arch will spin off as many (log_archive_max_processes) copies of itself as need be to handle the archive load.


that looks like it could be a stale entry -- anything in either alert log to help us out there?


v$managed_standby

A reader, July 25, 2004 - 6:24 pm UTC

"that looks like it could be a stale entry -- anything in either alert log to help us out there?"

Not really. If i cancel managed recovery and restart it, that spurious entry goes away.

As far as I have been able to narrow it down, it happens when I simulate some kind of failure or bring the standby listener down or something.

Basically, my primary is set to

log_archive_dest_2='service=standby'

Anytime there is a (simulated) failure, I get the appropriate messages in the primary's alert log and when I fix the error and restart the managed recovery, I get these spurious RFS processes.

Not a big deal as long as everything is working, but I was just curious. I will see if I can narrow it down further.

Thanks

log_archive_dest_1

Robert, August 05, 2004 - 12:59 pm UTC

I am always confused about specifying the option LGWR for log_archive_dest_n parameter.

(1) If I chose LGWR option, do I have to have standby redologs on the recieving instance? Can LGWR archive after the primary redolog file switches just like ARCH to standby instance?

(2) can LGWR archive to a local destination on primary database everytime a log swicthes? Could you clarify the difference between ARCH and LGWR doing this? Thanks.

Tom Kyte
August 05, 2004 - 1:26 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96653/log_arch_dest_param.htm#77394 <code>

you have standby redo logs in both cases, it is just 'who sends them'.. standby redo's are filled, applied and then archived on the standby.

lgwr = real time, as it is generated
arch = after filled, as they fill up.

you would use arch for 2) doesn't make sense even for lgwr to have that job.

Thanks. It helps

Robert, August 05, 2004 - 2:09 pm UTC


standby_archive_dest

Sam, August 05, 2004 - 6:36 pm UTC

On the standby instance, can I use log_archive_dest_1=LOCATION to store archive files that are received from primary database instead of standby_archive_dest parameter? Thanks.

ORACLE_SID

Charlie, August 06, 2004 - 10:38 pm UTC

Hi Tom,

Can we use THE SAME ORACLE_SID on standby database as the one on primary database?

thanks,

Tom Kyte
August 07, 2004 - 10:07 am UTC

yes.

To Sam

A reader, August 07, 2004 - 1:01 pm UTC

<quote>
On the standby instance, can I use log_archive_dest_1=LOCATION to store archive files that are received from primary database instead of standby_archive_dest parameter? Thanks
<quote>

No

The way it works is the primary contacts the standby's listener, the listener responds with its standby_archive_dest and log_archive_format, these are concatenated and thats where the RFS process puts the logs on the standby.

The log_archive_dest_n on the standby would be used only if you switchover/failover to this standby and it starts to generate its own archive logs.

same service_names and oracle_sid

A reader, August 19, 2004 - 3:26 pm UTC

Hi Tom,

Is it a good idea to make service_names and oracle_sid SAME on both primary and standby databases? For example, when we have to failover, clients do not need to change their tnsnames.ora file.

thanks,

Tom Kyte
August 19, 2004 - 7:56 pm UTC

oracle_sid is not externally exposed when using services, so it doesn't matter.

the services would be OK to be the same.

What option do I have to implement standby solution for SE

Igor, September 02, 2004 - 7:10 am UTC

Tom,

we are using Oracle 10g SE. Data Guard is not availiable in SE. So what options do I have to implement standby?
I want to map a drive on standby and copy archives to standby using scheduled task. Then I want to apply changes on standby site starting recovery process as scheduled task as well.

Do Oracle support such configuration (or how whould you do this)? Sorry, but upgrade to EE is not possible for our budget.

Thanks in advance

Igor

Tom Kyte
September 02, 2004 - 9:12 am UTC

you would have to "do it yourself" (DIY)

it is funny, when you compare the cost of DIY vs "just buy it", you might be surprised...

but it would be similar to:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76995/standbyc.htm#29722 <code>

the "manual mode"

Oracle 9204

A reader, September 24, 2004 - 11:59 am UTC

Tom,

During dataguard creation process i created configuration
for primary site and then standby site and enabled configuration.

Then when i issue show resource verbose 'verbose_name' it displays all the properties, which file does the broker picks the value for settings of these properties for the first time for example
StandbyArchiveDest= '/u01/database/test/STANDBY'

Is it the spfile or some other file?

Thanks.


GUI for DataGuard

Pravesh Karthik from Chennai, October 10, 2004 - 1:02 pm UTC

Tom,

Welcome back,

where can i get the GUI for dataguard downloaded.
I checked, below url, but there isnt download for windows

</code> http://www.oracle.com/technology/software/deploy/availability/htdocs/othersoft.html <code>


Can you please let me know, if there is one for windows and if so, url please.

Thanks a lot,
Pravesh Karthik




Tom Kyte
October 10, 2004 - 6:30 pm UTC

it is just part of OEM -- enterprise manager. do you have that installed and running?

GUI for DataGuard

Pravesh Karthik from Chennai, October 10, 2004 - 1:12 pm UTC

Tom,

Welcome back,

where can i get the GUI for dataguard downloaded.
I checked, below url, but there isnt download for windows

</code> http://www.oracle.com/technology/software/deploy/availability/htdocs/othersoft.html <code>


Can you please let me know, if there is one for windows and if so, url please.

Thanks a lot,
Pravesh Karthik




Data Guard Manager - GUI

Pravesh Karthik from chennai, October 11, 2004 - 12:12 am UTC

Tom,

Yeah, i have installed OEM, its running too. But i do not see the menu as i see in the below link

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96629/gui.htm <code>

as in Figure 5-1 Starting Data Guard Manager

I dont have Managment Server installed - could that be the reason. I have installed Management Server in a PC, i will check out that too.

Thanks,
Pravesh Karthik

Does ODG support tables with XMLType and LOB columns?

Arun Gupta, October 11, 2004 - 10:55 am UTC

The client is considering implementing ODG and the application team wants to use XMLType data type (with CLOB storage) or CLOB data type (with data stored in the database) to store character strings more than 4000 characters. Are there issues using these data types with ODG? We are on 9ir2.
Thanks


Tom Kyte
October 11, 2004 - 1:36 pm UTC

If by ODG -- you mean Data Guard, the physical standby supports all types.

The logical standby has a documented list of restrictions:

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96653/create_ls.htm#1071517 <code>

user defined types (as xmltype is) would not be supportable in a logical standby.

Arun Gupta, October 11, 2004 - 2:10 pm UTC

Thanks Tom, this is exactly what I was looking for. ODG is what I use as shorthand for Oracle Data Guard. Don't remember where I picked it up from.

Multiple recovery processes

A reader, October 12, 2004 - 12:43 pm UTC

Is there a way to have multiple RECO processes to speed up the recovery on the physical standby database? My network latency combined with the high volume of logs on my primary is causing the standby to "fall behind".

Not a problem, just wondering if there is a way to speed it up? I know recovery is inherently a serial process, you cant apply log 10 before you apply log 9, but still?

Tom Kyte
October 12, 2004 - 2:04 pm UTC

reco is for distributed transaction recovery :)

but see:

</code> http://docs.oracle.com/docs/cd/A91202_01/901_doc/server.901/a88808/standbyreco.htm#31698 <code>

(don't know why it isn't in the 9ir2 online version -- it is on my copy locally -- but it is back in the 10g stuff)



Separate processes for local and remote archiving

A reader, October 21, 2004 - 10:05 am UTC

I have a physical standby database managed by Data Guard. Here are my relevant parameters

NAME TYPE VALUE
------------------------------------ ----------- --------------------------------------------------------------------------------
log_archive_dest_1 string location=/opt/oracle/admin/@/arch mandatory reopen=5
log_archive_dest_2 string service=my.standby optional reopen=60 max_failure=10
log_archive_dest_state_1 string enable
log_archive_dest_state_2 string ENABLE
log_archive_duplex_dest string
log_archive_format string arch_@_%S.rdl
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean TRUE
log_archive_trace integer 0
remote_archive_enable string true
standby_archive_dest string /opt/oracle/admin/@/arch

Since log_archive_max_processes is set to 2 (default), I see ARC0 and ARC1 messages in my alert log.

I have a slow network so I set my remote destination to optional.

Here is what I see in my alert log during periods of high activity

ARC0: Evaluating archive log 3 thread 1 sequence 420865
ARC0: Beginning to archive log 3 thread 1 sequence 420865
Creating archive destination LOG_ARCHIVE_DEST_2: 'my.standby'
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/admin/oracle/arch/arch_oracle_0000420865.rdl'

ARC1: Evaluating archive log 1 thread 1 sequence 420866
ARC1: Beginning to archive log 1 thread 1 sequence 420866
Creating archive destination LOG_ARCHIVE_DEST_2: 'my.standby'
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/admin/oracle/arch/arch_oracle_0000420866.rdl'

i.e. even though there are 2 archiver processes, only one of them works at a time and archives to both the local and remote destination.

As a result, when my network is not able to handle the volume, my local destination is also not able to be archived and my database slows down because of the dreaded error

ORACLE Instance oracle - Can not allocate log, archival required
Wed Oct 20 07:00:50 2004
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 420868
All online logs needed archiving

Question:

How can I make it such that out of the 2 archiver processes, one works exclusively on my remote destination and the other works on my local destination? The remote is marked optional anyway, so it is ok for that ARCn to "fall behind" a little. At least my primary database will not be affected by my slow network like it is now!

Help? Thanks

Tom Kyte
October 21, 2004 - 2:38 pm UTC

but optional doesn't mean "hey, you know if this guy cannot keep up -- just SKIP IT"

optional means "if we cannot do it, stop doing it"

even if you had one for one -- you would have the same exact ISSUE!!! we cannot overwrite online redo log "1" until both destinations finished -- optional doesn't mean "blow it off", optional is for a failure condition.

so, even if one worked local and one worked remote -- you would have the same same same exact issue -- the one working remote would prevent the redo from being overwritten!

Activating physical standby database

A reader, October 22, 2004 - 12:44 pm UTC

I was sending logs from my primary to a temporary physical standby database for some testing. I was testing Data Guard, network bandwidth, etc. I was able to open the physical standby database in READ ONLY mode fine.

Now my testing is over, I set the corresponding log_archive_dest_state_n on my primary to 'defer' and I want to start using my physical standby database independently of the primary. I dont care about the Data Guard stuff anymore, I just want to 'open resetlogs' this database and start using it read write.

How can I do this? None of the following seem to work

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01666: controlfile is for a standby database

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16003: standby database is restricted to read-only access

SQL> alter database recover standby database;
alter database recover standby database
*
ERROR at line 1:
ORA-00279: change 1429146774 generated at 10/15/2004 09:02:48 needed for thread
1
ORA-00289: suggestion :
/opt/oracle/product/9.2.0/dbs/archarch_oracle_0000414858.rdl
ORA-00280: change 1429146774 for thread 1 is in sequence #414858


SQL> alter database recover cancel;

Database altered.

All the switchover/failover scenarious in the docs assume some sort of involvement of my primary databse. I dont want that. Nothing I do on this standby should affect my primary.

Help? Thanks 

Tom Kyte
October 22, 2004 - 6:20 pm UTC

go through the method documented for "ok, so you want to fail over". </code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96653/role_management.htm#1024703 <code>

Managing archived logs

A reader, October 27, 2004 - 10:07 am UTC

Just curious about how people manage their archived logs. We are NOT using rman yet, only manual hot backups (using Unix shell scripts). log_archive_dest is set to a 'arch' directory which is a sym link pointing to say /disk01. There are 7 disks, one for each day of the week. Every day at 7am, a cron job switches the sym link from /disk01 to /disk02 and so on. It also does find /disk*/arch -mtime +7 | xargs rm -f to get rid of logs older than 7 days. 7am is usually a period of low activity, but if ARCH is actively writing a log at that exact time, I get errors when the sym link suddenly changes. Also, I am running Data Guard in physical standby mode and when the sym link changes, the standby database is unable to get the FAL archive because the primary now "cannot find it".

How is all this typically handled? What is the best practice? Ideally, I would like a mount point with infinite storage and I can just point log_archive_dest to it and have a cron job that does "-mtime +7 rm" and not worry about switching the symlink and stuff!

Help? Thanks

Tom Kyte
October 27, 2004 - 12:06 pm UTC

archives must be gotten off of that machine asap to some other device, some other machine.

they are there in the event you have a media failure, a system failure. If you don't get them off the system, onto some other safe, removed from this system storage -- they won't be as useful (when the building they are in burns down for example, or you lose two disks at about the same time)


but you should be able to have a mount point with "infinite" storage these days, talk to your SA - they should be able to set it up - logical volumes are able to get quite large.

if you move the goal post on us (change the directories), we will of course not be able to find our stuff.


You can have a job that periodically wakes up and moves the already processed archives to another place (tape, nfs mount, whatever)

A reader, November 12, 2004 - 8:03 am UTC

"but you should be able to have a mount point with "infinite" storage these days, talk to your SA - they should be able to set it up - logical volumes are able to get quite large"

That would be me! :( I know this is off-topic for this forum, but does someone out there know of any reliable, mature open source storage virtualization software (volume manager)? I have a few disks lying around and I want a mount point with the total storage, I dont want to be constrained by each physical disks size?

Thanks

Tom Kyte
November 12, 2004 - 8:08 am UTC



ASM -- free in 10g, not open source -- but so what?

A reader, November 12, 2004 - 9:36 am UTC

Well, ASM only applies to database files, right? I need a general purpose filesystem to store export dump files, compressed dbf files, export logfiles (exp log=...), etc.

Is ASM a general-purpose filesystem/LVM?

Tom Kyte
November 12, 2004 - 1:23 pm UTC

for database stuff, exports, backups (compressed or otherwise) -- yes. for file "files" -- ocfs exists if you want to use it for files as well.




A reader, November 12, 2004 - 2:05 pm UTC

1. What is "ocfs"?

2. Eh, how does it matter to ASM if it is a export.dmp file or abc.xyz file? You seem to be saying that ASM supports the former but not the latter?

Even so, how would this work? I would need to use the dbms_lob and bfile and stuff to read/write these export dumps via the database? I cant do ls/dir on them and stuff, can I?

3. Since I am still on 9iR2, ASM is not an option for me. Do you know of any robust open-source LVM-type software out there?

Thanks

Tom Kyte
November 12, 2004 - 3:36 pm UTC

1) oracle cluster file system (otn.oracle.com for more info)

2) asm supports database things (the expdb dump format). you don't need to "ls/dir" on these things.

3) nope -- and for something like "my disks, with my data for heavens sake", I'm not sure I would look really hard. open source does not mean free, it does not mean cheap, it does not mean something to run with immediately.

don't get me wrong, my browser -- open source. my email client -- open source. My OS, open source but far far from free (and really getting far from open source as far as I'm concerned, it comes from a company -- with regular releases -- and support -- and a qa team and only the features they are willing to support and so on)

A reader, November 15, 2004 - 1:41 pm UTC

Doh, since I am on Solaris 8, I can use the Solstice Disk Suite (rebranded as Solaris Volume Manager in Solaris 9) for this purpose. Its been around long enough and is supported by Sun.

Thanks

A reader

Edu, November 16, 2004 - 11:39 am UTC

Tom, I've configured a standby database. Primary database is sending archivers to DG database correctly. I mount DG database using the following commands:

SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

I think that all the ARC´s are applyed on DG database, but I have a question: Once ARC´s are applyied, what can I do with ARC´s? Can I erase it? Automatically disapear when they are aplyied?

Thanks & regards 

Tom Kyte
November 16, 2004 - 1:00 pm UTC

once applied, you can do with them what you like -- back them up for example.

Applied Archives

Nathan, November 17, 2004 - 7:18 am UTC

I wrote a shellscript to delete applied archives and scheduled it with cron , it would have been very useful to have a command where we can specify the logs to be deleted either based on sequence of hours of log to be kept. I now delete all but last 200 logs .

Is there any other option with oracle internally ... ? May be RMAN backup and delete input ?



Tom Kyte
November 17, 2004 - 10:52 am UTC

rman with its retention policies will do this.

you can tell rman "keep everything you need to make the last 4 backups you did 'restorable', delete everything else" or "keep everything you need to make all of the backups we've done in the last 3 weeks 'restorable', get rid of the rest"

change my DG physical standby database from read-only to managed recover

peter littlefield, November 23, 2004 - 2:02 pm UTC

I want to change my DG physical standby database from read-only to managed recover. Why must I shut it down and restart ? I'm doing as per Chapter 8.

I have a physical standby running in read-only mode . . . .
sys@stby> select open_mode, database_role,controlfile_type from v$database;
READ ONLY PHYSICAL STANDBY STANDBY

The archive logs are coming over from primary and piling up. . . . .
sys@stby> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
56 23-NOV-04 23-NOV-04 YES
57 23-NOV-04 23-NOV-04 YES
58 23-NOV-04 23-NOV-04 YES
59 23-NOV-04 23-NOV-04 YES
60 23-NOV-04 23-NOV-04 NO
61 23-NOV-04 23-NOV-04 NO
62 23-NOV-04 23-NOV-04 NO
63 23-NOV-04 23-NOV-04 NO
8 rows selected.

Thereis noone logged in but myself as sysdba . . . . .
sys@stby> select username from v$session where username is not null;
SYS
1 rows selected.

I start managed recovery as per Chapter 8 . . . . .
sys@stby> alter database recover managed standby database disconnect from session;
Database altered.

And I can't select ????
sys@stby> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01219: database not open: queries allowed on fixed tables/views only

But If I shutdown and startup . . .
sys@stby> alter database recover managed standby database cancel;
Database altered.

sys@stby> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
sys@stby> startup nomount
ORACLE instance started.
Total System Global Area 105979672 bytes
Fixed Size 454424 bytes
Variable Size 88080384 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
sys@stby> alter database mount standby database;
Database altered.

sys@stby> alter database recover managed standby database disconnect from session;
Database altered.

Confirm recover managed standby . . . . .
sys@stby> select open_mode, database_role,controlfile_type from v$database;
MOUNTED PHYSICAL STANDBY STANDBY

Now I can see the archive logs are coming over from primary and being applied. . . . .
sys@stby> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
56 23-NOV-04 23-NOV-04 YES
57 23-NOV-04 23-NOV-04 YES
58 23-NOV-04 23-NOV-04 YES
59 23-NOV-04 23-NOV-04 YES
60 23-NOV-04 23-NOV-04 YES
61 23-NOV-04 23-NOV-04 YES
62 23-NOV-04 23-NOV-04 YES
63 23-NOV-04 23-NOV-04 YES
8 rows selected.


Tom Kyte
November 23, 2004 - 2:52 pm UTC

why? because you have an opened read only database and to be in managed recovery mode you have to close a database but an Oracle instance (which is a set of processes and memory) cannot be used for much of anything after a "close" (except to be told to shutdown really)

the instance needs to be reset. not really a big deal in the long run.

Peter Littlefield, November 24, 2004 - 10:55 am UTC

OK, I think I understand.
I had hoped to switch the DG stby from managed recovery to read-only so I could run reports (off loading the primary). Then after the reports were run, switch back to managed recovery and let the stby 'catch-up'. I didn't think I would have to 'shutdown/startup' the stby to go from read-only to managed recovery. Chap 8 (sec 8.2.2) implies I should be able to do this ? I thought I could because I was able to 'select' while in managed recover mode (see above)? Are you saying that if going from read-only to managed recovery I will always need to shutdown/startup ?

Tom Kyte
November 24, 2004 - 11:56 am UTC

now that I look closer -- you are correct, you should be able to:

startup open read only;
<query>
and after kicking everyone off, issue:

alter database recover managed standby database disconnect from session;


what version are you running here?

9205

peter littlefield, November 27, 2004 - 1:55 pm UTC

9205
Tom Kyte
November 27, 2004 - 2:12 pm UTC

it should have worked, if it reproduces, please file a tar with support (and sorry for the confusion earlier)

Automation of Data Guard Failover

Richard Stroupe, November 30, 2004 - 2:11 pm UTC

Tom, does it make sense to automate the steps to initiate the failover steps for Data Guard via a script (i.e. have someone drive out to DR site and bring standby Db up in R/W)? Do you know anyone one that has done this? The instructor at Oracle University told us that oracle DG was not built to have these steps to be automated but it sure would work great for us if we could have these steps automated without manual intervention.

Tom Kyte
November 30, 2004 - 8:10 pm UTC

it would be OK to have a script on the standby that when a HUMAN ran it, caused the standby to become "primary" (to automate the N steps it takes to convert from standby the primary)

it would not be OK if you ask me to have an automagic piece of software that says "hmm, primary is dead, lets activate standby".

I don't know what you mean by "have someone drive...."

Followup via Oracle TAR CAN NOT SELECT AFTER CHANGING FROM READ-ONLY TO MANAGED RECOVER ORA-01219

peter littlefield, December 01, 2004 - 11:29 am UTC

Researching ...

<Bug:2871400>
Status: 11,Code Bug (Response/Resolution)
Abstract: ORA-604 ORA-1219 WHEN QUERYING V$ VIEWS IN MANAGED RECOVERY MODE

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Above bug was filed last year and the status indicates that developement is still working on the bug.
-- There are also few other bugs filed with these errors which basically say that we are trying to use CBO while performing the querries which in turn is trying to populate some dc_* tables which is not allowed when the database is not open.
-- Looks like this is happening only when we place the database in managed recovery mode from OPEN READ-ONLY mode. If we shutdown and restart the database, it does not happen.
-- There is an other <Bug:3092684> in 10g which is fixed. We check to see if the database is open or mounted and use RBO or CBO depending on it.
++++++++++++++++++++++++++++++++++++++++++++++++++++++

Peter,
Your issue matches the bug I mentioned above which is still being worked by the developement. Since the bug is not very serious and it is already fixed in 10g, I am not expecting it to get fixed in 9i very soon. Until then, you can use the workaround of SHUTDOWN, STARTUP (NOMOUNT & MOUNT STANDBY), MANAGED RECOVERY instead of directly placing the db in managed recovery from OPEN READONLY mode.

Thanks,
Srinivas


ORA-16089

A reader, December 01, 2004 - 9:51 pm UTC

Not a big deal, just a incomplete error message

When I do

alter database register logfile
1000 files;

If some of the files are already registered, I get

alter database register logfile
*
ERROR at line 1:
ORA-16089: archive log has already been registered

But the others are still properly registered.

The error message could say 'Unable to register %s logfiles out of %s supplied because ...'

Thanks

Tom Kyte
December 02, 2004 - 7:27 am UTC

the only way to get an enhancment request into the system is... to register an enhancment request in the system (eg: metalink)

Followup via Oracle TAR CAN NOT SELECT AFTER CHANGING FROM READ-ONLY TO MANAGED RECOVER ORA-01219

Peter Littlefield, December 09, 2004 - 10:27 am UTC

I changed optimizer_mode to CHOOSE (from First_rows) and was able to switch from Managed Recovery to Read-Only and back again without "ORA-01219: database not open: queries allowed on fixed tables/views only"

Tom Kyte
December 09, 2004 - 3:15 pm UTC

hmm, dynamic sampling? maybe that was kicking in -- interesting.

Standby controlfile

A reader, December 14, 2004 - 1:29 pm UTC

The steps to create a physical standby database are

1. alter database force logging;
2. put all tbs in hot backup mode
3. copy them to standby
4. end hot backup mode
5. alter database create standby controlfile
6. copy standby controlfile to standby

On standby
7. dbstart nomount
8. alter database mount standby database;
9. start managed recovery

I inadvertently did (5) when (3) was in progress i.e. when all the tablespaces were in hot backup mode

To my surprise, when I did (7) with this controlfile, select * from v$archived_log; returned 0 rows!

Why is this? What are the concepts at work here? Did the standby controlfile somehow get corrupted because all tablespaces were in hot backup mode?

Thanks

Tom Kyte
December 15, 2004 - 12:51 pm UTC

could be -- not even really worth looking into -- you did things in the wrong order, sort of like trying to recover before you've backed up.


documentation says:

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96653/create_ps.htm#58008 <code>

... The control file must also be created after the last time stamp for the backup datafiles......

Separate processes for local and remote archiving

A reader, December 16, 2004 - 4:55 pm UTC

A clarification on my earlier review with the same title...

So, how can I handle this situation? I have 3 log_archive_dest, one is local, second is on the same LAN subnet (machine failure DR), the third (site failure DR) is in a remote location across a WAN.

At times, the WAN bandwidth is not able to keep up with the primary log generation rate, so I get the dreaded 'Cannot allocate log, archival required' mesage and my primary database freezes momentarily.

Primary freezing is not acceptable, how can I workaround this?

Thanks

Tom Kyte
December 16, 2004 - 5:14 pm UTC

add more redo groups to the primary to let the WAN catch up (you only get the cannot allocate when you try to use a log that hasn't been archived yet, increase the cardinality of the logs, decrease the probability of getting this - assuming you have "bursts" which you must since you say "at times")

or write a custom job on lan (machine failure DR) to transfer logs recieved there to the true DR machine.

or use 9i data guard and have the redo stream continously over the wan instead of big bursts of archives all at once.

A reader, December 16, 2004 - 6:43 pm UTC

"add more redo groups to the primary to let the WAN catch up"

Thats a good idea. Right now, I have 3 log groups each with 2 5MB members. I can add 3 more groups so it will take twice as long to cycle back to each log.

"or write a custom job on lan (machine failure DR) to transfer logs recieved there to the true DR machine"

This would be like Data Guard's cascading redolog destinations, right? Data Guard already supports this, no need to write custom scripts?

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/cascade_appx.htm#635322 <code>

"or use 9i data guard and have the redo stream continously over the wan instead of big bursts of archives all at once"

I am using 9i Data Guard. Not sure what you mean by "have redo stream continuously over the wan"? I have set my DG protection mode to "maximum performance"

Thanks

Tom Kyte
December 16, 2004 - 9:13 pm UTC

if you are using data guard, that is right -- I didn't know if you were or not.


Instead of using archives -- you have data guard send the information straight from the database. If you are using "maximum performance" that would be actually not be using the archives?

A reader, December 16, 2004 - 7:16 pm UTC

Instead of adding more redolog groups, what if I increase the size of my current redolog members? Would that have the same effect? Any side-effects on overall system performance and recoverability that I should worry about?

Thanks

Tom Kyte
December 16, 2004 - 9:14 pm UTC

if you are using archives, it would potentially worsen the problem -- you would wait longer to transmit MORE stuff so when the burst came-- bam, you wait even longer.

You want a more continous flow.

Stream continuosly

A reader, December 16, 2004 - 9:37 pm UTC

I still dont understand what you mean by "stream continuosly".

The 3 protection modes DG has are

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/concepts.htm#1037159 <code>

Maximum performance simply means that ARCH transports the archives asynchronously to the standby site.

If you are referring to the other 2 modes (max protection or max availability), they are exactly what I dont want because my slow network might end up freezing the primary?

I guess I just dont understand what you mean by "stream continuosly"? What init.ora settings and/or commands are you referring to?

Thanks

Tom Kyte
December 17, 2004 - 7:45 am UTC

there are 4 ways data guard runs:

a) sync log transfer -- every COMMIT transfers redo from primary to standby redo logs

b) sync log transfer, but if standby is unavailable fall back to ARCH log shipping

c) async log transfer, if standby is unavailable fall back to ARCH log shipping (this is the mode you think you are in but apparently are not if you are doing ARCH log shipping)

d) ARCHive log shipping


max performance does not mean ARCH transports logs, it means C, we fire the redo over continously -- this is the mode you think you are in, but apparently if you are shipping archives -- you are *not*. Data Guard normally ships redo as it is generated, Data Guard does not normally wait for an archive redo log to fill up. If data guard did that -- you would always be behind by an archive redo log at least -- you would lose lots of data during a failover!

see
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96653/log_transport.htm#1069281 <code>
in particular -- first graphic depicting lgwr transmitting the redo to standby, NOT arch, data guard works with live redo, not archives


So, I think you have configured a "standby database" (what we did in 7.3, 8.0, 8i) and it is archive redo log based. Data Guard would rather be "online redo" based and would constantly stream the data from production to standby, rather than bunch up a ton of stuff in a single archive and try to stuff it over the WAN all at once.


Have you read through the entire data guard admin/concepts guide? the architecture is laid out there.

A reader, December 18, 2004 - 7:50 pm UTC

Yes, I understand what you are saying.

You are suggesting that I use the configuration in Figure 5-6

That would be something like

log_archive_dest_1='location=local_disk mandatory'
log_archive_dest_2='service=100mpbs_lan LGWR'
log_archive_dest_3='service=slow_wan LGWR ASYNC NOAFFIRM NET_TIMEOUT=20'

The docs say

"If a LGWR destination fails, the destination automatically reverts to using the archiver (ARCn) process until the error is corrected.

In maximum performance mode, the log writer process tries to reconnect for approximately 30 seconds

When you use the ASYNC attribute, there are several events that cause the network I/O to be initiated:

* If the LGWR request exceeds the currently available buffer space, the existing buffer is transmitted to the standby database. The LGWR process stalls until sufficient buffer space can be reclaimed.
* A primary database log switch forces any buffered redo logs to be transmitted to the standby database before the log switch operation completes.
* The primary database is shut down normally. An immediate shutdown of the primary database results in the buffered redo logs being discarded. A standby database shutdown also causes the buffered redo logs to be discarded.
* The primary database has no redo activity for a period of time. The duration of database inactivity is determined by the system and you cannot modified it.
* If the rate of redo generation exceeds the runtime network latency and sufficient space is available, then the LGWR request will be buffered. Otherwise, the existing buffer is transmitted to the standby database. The LGWR process stalls until sufficient buffer space can be reclaimed"

Sorry for pasting the docs, but I just want to make sure we are talking about the same thing.

OK from the above, it doesnt appear like ASYNC is really ASYNC i.e. it has the potential to stall my primary database if the network really goes slow on me. DG will resort to ARCH shipping only if the destination fails completely!

My main requirement is that the primary database performance should not be affected in the least.

For this, would you recommend using the above configuration? Or would your earlier suggestion work better for me (ARCH shipping with increased number of redolog groups to allow the standby/network to catch up)? The latter would have NO impact on the primary?

Thanks

Tom Kyte
December 19, 2004 - 10:38 am UTC

async is as async is -- not any different than when your redo log buffer (which is a way to doing async (buffered) IO with redo) fills up. until lgwr can clear space in it, you wait. No different.

async is async.

if you have a fast site and a slow site and you want slow site to not impact you, the idea of cascading pops into mind.

production -> fast site -> slow site

instead of

production -> fast site
|
v
slow site




A reader, December 19, 2004 - 5:01 pm UTC

Cascading is definitely a viable option, Production can stream as fast as it can over the 100Mbps LAN to a local site and that can take its time to cascade over my slow network to the WAN.

I have 3 5MB redolog groups now. My WAN bandwith is unable to keep up the rate of log generation on the primary, so regardless if ARCH does it or LGWR does it, the primary will suffer during periods of high redo generation.

Thats why your "add redolog groups" idea is also intriguing. That gives some more time for my WAN to catch up before the primary would need to re-use that online log?

In general, what are the performance implications on the primary (Data Guard or not) of adding more redolog groups?

Thanks

Tom Kyte
December 19, 2004 - 6:30 pm UTC

adding more redo log groups is not really a "performance thing" except for the fact that it can stop your database from "pausing" while waiting for ARCH to catch up of course :)

More redolog groups

A reader, December 19, 2004 - 6:37 pm UTC

:), ok so I guess you are saying that adding more redolog groups cannot hurt anything. And since it would seem to solve my problem with the slow WAN bandwidth by letting ARCH catch up, I will try that first before resorting to the other solutions we discussed here ( a) using LGWR instead of ARCH and b) using cascading redolog destinations).

Basically, as long as my WAN is able to copy one archived redolog in the time it takes for the primary to cycle thru N (where N=redolog groups), I am fine!

Thanks a lot, you have been very helpful.

A reader, December 24, 2004 - 11:46 am UTC

OK I added more redolog groups, it did help a little, but I still get "All online logs needed archiving" in my primary alert log during heavy activity

How can I get precise timing information on all this?

i.e. when the archived log started to be archived and when it was completed and such? v$archived_log has a completion_time, but no start time?

I want to see how long my archived logs are taking to be written to the various archive destinations and see where the bottleneck is

Thanks

Tom Kyte
December 24, 2004 - 1:51 pm UTC

add more redo -- look at how fast you generate redo at peak, and how fast you can archive them and how long your peak is. If you say generate 1 log per minute at peak, peak lasts 30 minutes and you can archive 1 log every 5 minutes you need:

30 logs -- would brute force it, arch wouldn't have to do a thing
25 logs -- would just about cut it -- in 30 minutes arch would have gotten 5 logs freed up for reuse....


the archival process starts at the log switch -- you should see everything you need in your alert log.

A reader, December 24, 2004 - 11:50 am UTC

Basically, I want to find out my log switch rate on the primary (say 10 logs in 5 seconds) and how long each of those logs took to get archived to the various archive destinations. This will tell me which archive dest is slow.

Thanks

Mining alert log

A reader, December 27, 2004 - 2:03 pm UTC

Right, I can get my log generation rate quite easily from v$log_history, slice it by day, minute, whatever upto the second.

It is the time that ARCH takes to archive my logs that I am having trouble with. I was hoping that the new v$archive_dest* views would track this accurately, but you are saying I have to "mine" my alert log! :-(

OK I have the following when I search for 518900

Mon Dec 27 11:34:35 2004
Beginning log switch checkpoint up to RBA [0x7eaf4.2.10], SCN: 0x0000.5e0653ad
Thread 1 advanced to log sequence 518900
Current log# 4 seq# 518900 mem# 0: /disk01/oradata/oracle/redo04b.log
Current log# 4 seq# 518900 mem# 1: /disk02/oradata/oracle/redo04a.log
Mon Dec 27 11:34:35 2004
ARC0: Evaluating archive log 5 thread 1 sequence 518899
ARC0: Beginning to archive log 5 thread 1 sequence 518899
Creating archive destination LOG_ARCHIVE_DEST_3: 'foo.bar'
Creating archive destination LOG_ARCHIVE_DEST_2: 'bar.foo'
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/admin/oracle/arch/arch_oracle_0000518899.rdl'
ARC0: Completed archiving log 5 thread 1 sequence 518899
Mon Dec 27 11:37:11 2004
Completed checkpoint up to RBA [0x7eaf4.2.10], SCN: 0x0000.5e0653ad
Mon Dec 27 11:37:48 2004
Beginning log switch checkpoint up to RBA [0x7eaf5.2.10], SCN: 0x0000.5e066718
Thread 1 advanced to log sequence 518901
Current log# 2 seq# 518901 mem# 0: /disk02/oradata/oracle/redo02a.log
Current log# 2 seq# 518901 mem# 1: /disk01/oradata/oracle/redo02b.log
Mon Dec 27 11:37:48 2004
ARC1: Evaluating archive log 4 thread 1 sequence 518900
ARC1: Beginning to archive log 4 thread 1 sequence 518900
Creating archive destination LOG_ARCHIVE_DEST_3: 'foo.bar'
Creating archive destination LOG_ARCHIVE_DEST_2: 'bar.foo'
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/admin/oracle/arch/arch_oracle_0000518900.rdl'
Mon Dec 27 11:38:00 2004
ARC1: Completed archiving log 4 thread 1 sequence 518900

1. How can I access the alert log as a external table to answer "how long did ARCH take to archive a specific sequence N?"

2. What is the average/min/max time for ARCH to do its thing?

3. Broken down by log_archive_dest?

I can see patterns in the alert log but the checkpoint messages generated by log_checkpoints_to_alert are cluttering up the output?

Thanks

Tom Kyte
December 27, 2004 - 4:28 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:28805759638277

use the same technique.  You are looking for lines

like '___ ___ __ __:__:__ 20__'
like '%: Beginning to archive %'
like '%: Completed archiving %'


from the beginning/completed, you would parse out log, thread, sequence, etc... 

like this:

ops$tkyte@ORA9IR2> create or replace directory data_dir as '/tmp/'
  2  /
 
Directory created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE alert_log
  2  (
  3  text_line varchar2(4000)
  4  )
  5  ORGANIZATION EXTERNAL
  6  (
  7      TYPE ORACLE_LOADER
  8      DEFAULT DIRECTORY data_dir
  9      ACCESS PARAMETERS
 10      (
 11          records delimited by newline
 12          fields
 13          REJECT ROWS WITH ALL NULL FIELDS
 14      )
 15      LOCATION
 16      (
 17          'test.dat'
 18      )
 19  )
 20  REJECT LIMIT unlimited
 21  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select log#, thread#, seq#,
  2         to_char(begin_dt,'dd-mon hh24:mi:ss')  || ' ' || to_char(end_dt,'hh24:mi:ss') times,
  3         trunc( mod( (end_dt-begin_dt)*24, 24 ) )  "Hr",
  4         trunc( mod( (end_dt-begin_dt)*24*60, 60 ) )  "Mi",
  5         trunc( mod( (end_dt-begin_dt)*24*60*60, 60 ) ) "Sec"
  6    from (
  7  select log#, thread#, seq#, dt end_dt, last_dt begin_dt
  8    from (
  9  select what,
 10         dt,
 11         ( substr( log#, 1, instr( log#, ' ') -1 )) log#,
 12         ( substr( thread#, 1, instr( thread#, ' ') -1 ))  thread#,
 13         ( substr( seq#, 1, instr( seq#, ' ') -1 ))  seq#,
 14         lag( dt ) over (order by r) last_dt
 15    from (
 16  select case when text_line like '% Beginning %' then 'Begin' else 'End' end what,
 17         to_date(last_line,'Dy Mon DD HH24:MI:SS YYYY') dt,
 18         substr(text_line, instr(text_line, ' log ')+5 ) log#,
 19         substr(text_line, instr(text_line, ' thread ')+8 ) thread#,
 20         substr(text_line, instr(text_line, ' sequence ')+10 ) || ' ' seq#,
 21         text_line,
 22         last_line,
 23         r
 24    from (
 25  select text_line, last_line, r
 26    from (
 27  select text_line, lag(text_line) over (order by r) last_line, r
 28    from (
 29  select rownum r, text_line
 30    from alert_log
 31   where text_line like '___ ___ __ __:__:__ 20__'
 32      or text_line like '%: Beginning to archive %'
 33      or text_line like '%: Completed archiving %'
 34         )
 35         )
 36   where text_line NOT like '___ ___ __ __:__:__ 20__'
 37         )
 38         )
 39         )
 40   where what = 'End'
 41         )
 42   where begin_dt > sysdate-3
 43  /
 
LOG#  THREA SEQ#  TIMES                            Hr         Mi        Sec
----- ----- ----- ------------------------ ---------- ---------- ----------
1     1     5685  24-dec 16:27:34 16:27:36          0          0          2
3     1     5686  24-dec 17:53:18 17:53:20          0          0          2
2     1     5687  24-dec 19:17:15 19:17:17          0          0          2
1     1     5688  24-dec 20:45:40 20:45:42          0          0          2
3     1     5689  24-dec 21:11:02 21:11:05          0          0          3
2     1     5690  24-dec 22:00:18 22:00:28          0          0         10
.....

 

Brilliant!

A reader, December 27, 2004 - 6:29 pm UTC

Brilliant, as usual. Thanks

Please Explain listener.ora and tnsnames.ora for data guard

N.Balasubramanian., January 03, 2005 - 7:24 am UTC

Hi Tom,
I have read the material on Data Guard several times but still I am not able to understand the entries in listener.ora and tnsnames.ora.

Assume the dbname as well instance name as prod (sid same on both the instances) and the primary and secondary servers as node1 and node2 and they are using the non-default port.

What are the entries required in listener.ora and tnsnames.ora (for client connections, dba activities and FAL activity)

Please give me a model listener.ora and tnsnames.ora

Thanks and Regards


Tom Kyte
January 03, 2005 - 8:59 am UTC

the configuration is not special, it is the same you would set up so that people can "connect" to the databases.

Data Guard

Jairo Ojeda, January 05, 2005 - 2:55 pm UTC

Tom, I have a cascade redo log destination with a remote Standby (9.2.0.6) and it was working fine, but now the IP address are NAT through the Firewall and it stop working. I use a second listener only for the redo traffic with port 1522 (Note: 175122.1), I check the Note: 124140.1 and I thing that I can use set the USE_SHARED_SOCKET=TRUE on both sites (local/remote standby).

1.Does Data Guard supports the use of NAT (Network Address Translator) with these configuration ?
2.Does Data Guard Broker supports cascade redo log destinations ?

Tom Kyte
January 05, 2005 - 7:44 pm UTC

draw us a simple picture of where the machines are what who is what. how would the guy on the outside know where to send the redo to -- do you have some sort of port forwarding setup?

can the guy with the redo actually connect to the guy who needs the redo.

What is the exact requirement for STANDBY REDO LOGS

N.Balasubramanian, January 31, 2005 - 3:08 am UTC

Hi Tom,
I have gone thru the Oracle manual but I don't understand the need for Standby Redo Logs. Even without that we are using LGWR to send the redo records. And they are applied only after it is archived. Even with Standby redo logs, first they are archived and then only applied (for recovery). Please explain the need for standby redo logs in the maximum protection and maximum availability modes.

Tom Kyte
January 31, 2005 - 8:16 am UTC

where else would the redo on the standby site go?



Then what happens in maximum performance mode ?

N.Balasubramanian, January 31, 2005 - 11:52 pm UTC

Then what is the difference between maximum protection mode (where standby redo logs is mandatory) and maximum performace mode. Please enlighten me.
Thanks

Tom Kyte
February 01, 2005 - 8:41 am UTC

async transfer of redo.


</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96653/concepts.htm#1037159 <code>

summarizes them

Data Guard

Jairo Ojeda, February 03, 2005 - 9:00 pm UTC

Tom, about the first question I wrote on January 05, 2005. I solved the NAT issue by set the parameter USE_SHARED_SOCKET=TRUE, on both sites (local and remote standby) and restarted the servers, now it is working well.

but about the second question, I don't fine any documentation, so, do you now if Data Guard Broker supports cascade redo log destinations ?

Tom Kyte
February 04, 2005 - 2:01 am UTC

what is "cascade redo log" destinations in your mind?

if you are asking "can a standby have a standby" the answer is absolutely.

Data Guard

Jairo Ojeda, February 04, 2005 - 10:27 am UTC

"cascade redo log" = "can a standby have a standby"
I have 2 local standby and 1 remote standby, the primary database send the redo logs to both local standby and one of the standby send the archive log to the remote one, I had configurated the Data Guard Broker for the local env. but I cann´t configurated the "cascade redo log" destination on the Data Guard Broker, so, my question is,
Does Data Guard Broker supports cascade redo log destinations ? any documentation?

Tom Kyte
February 04, 2005 - 12:01 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96653/cascade_appx.htm#635322 <code>

appendix entitled "Cascaded Redo Log Destinations"

Data Guard

Jairo Ojeda, February 04, 2005 - 1:55 pm UTC

Ok, if I tell to the primary to send archive logs to the remote standby I can configured the Data Guard Broker tool to administrate the remote standby without problems but I use a Cascaded Redo Log Destinations.

my Cascaded Redo Log Destinations works, but I have to administrate without the Data Guard Broker tool because I donÂ’t know How to configured the Data Guard Broker tool to administrate the remote standby database using Cascaded Redo Log Destinations.

It is the reason of my question Does Data Guard Broker tool supports it?

A reader, February 08, 2005 - 11:43 pm UTC

Tom,

After going through the following link

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/log_transport.htm#1071134 <code>

What is the advantage of using LGWR(ASYNC) for maximum performance over ARCH as wont it be same as using ARCH?

Thanks.

Tom Kyte
February 09, 2005 - 2:46 am UTC

with arch, you lag behind by AT LEAST an entire redo log file.

with async/lgwr, you lag behind by a couple of seconds at most.


there is a large difference in the amount of committed transactions you might lose between the two.

I have come back with my old question asked on 31st Jan 2005

N.Balasubramanian, February 09, 2005 - 7:29 am UTC

Hi Tom,
You have said

with arch, you lag behind by AT LEAST an entire redo log file.

with async/lgwr, you lag behind by a couple of seconds at most.

Ok. In maximum performance mode when we are using LGWR/ASYNC, where are the redo records written in the standby database. ( We are not using standby redo logs.)

I have gone thru the doc suggested by you and other docs also.

I am not understanding. Please help.




Tom Kyte
February 09, 2005 - 2:25 pm UTC

they are written to log files on the standby -- regardless. (archived redo logs that are applied or standby redo logs that are archived and applied)

to the previous question....

reader, February 09, 2005 - 1:27 pm UTC

my understanding is that RFS process on the standby would receive the redo from primary db and write it to standby archivelog. hope it helps. corrections welcome.

Tom Kyte
February 09, 2005 - 3:05 pm UTC

correct - thanks!

What is the extra benefit that we derive from standby redo logs

N.Balasubrmanian, February 09, 2005 - 10:42 pm UTC

Hi Tom,
Thanks to you and the other reader for providing the necessary clarification.

If the redo records can be written on the standby archive log, what is the extra benefit that we derive by using the standby redo logs in the maximum protection mode.

I understand about LGWR/SYNC/AFFIRM - The same can be achieved without standby redo logs also - can't it ?

Tom Kyte
February 10, 2005 - 1:04 am UTC

Because standby redo logs are preallocated files, they avoid the operating
system overhead of file system metadata updates common with sequential files.

Standby redo logs can reside on raw devices, thus providing I/O write
performance equal to that of the primary database.

Standby redo logs can be multiplexed using multiple members, improving
reliability over archived redo logs.

If primary database failure occurs, redo data written to standby redo logs can be
fully recovered.

Standby redo logs are required to implement the maximum protection level of
data protection (no-data-loss) disaster recovery solution with physical standby
databases.

Standby redo logs are required for cascading standby

Data Guard Mode vs DIY/Manual mode

Alain, February 10, 2005 - 9:37 am UTC

Tom,

In a previous review about standby on 10g/SE, you gave a OTN link to a 8.1.7 documentation.

Does it mean that there isn't any improvement or new fonctionality for 'manual' standby database since 8.1.7 ?

So guaranteed/instant and rapid modes are only relevant for EE ?

So, there is nothing more I can 'do it myself' in 9i/10g SE standby than in 8i standby ?



Tom Kyte
February 11, 2005 - 3:15 am UTC

correct.


in SE, you are using archive redo logs, you are not using the enterprise "real time" features.

physical standby database

Nadeesh, April 04, 2005 - 2:12 am UTC

Tom,

I am using dataguard on 9.2.0.1.0 on solaris 8(64 bit). Dataguard is with physical standby database.

Question1.)I am unable to create standby redo log groups on my standby database. When I try to create them it throws error: 

ORA-01156: recovery in progress may need access to files

Question2) Also "select * from v$logfile" on standby shows me the redo logs files but actually files doesn't exists. 

bash-2.03$ cd /export/home/oracle/u01/app/oracle/oradata/standby/
bash-2.03$ ls -al
total 1960128
drwxr-xr-x   6 oracle   dba         5632 Apr  1 10:24 .
drwxrwxr-x   6 oracle   dba         1536 Apr  1 10:33 ..
drwxr-xr-x   2 oracle   dba         2048 Apr  4 10:55 archivefromprim
drwxr-xr-x   2 oracle   dba          512 Apr  4 11:08 bdump
drwxr-xr-x   2 oracle   dba          512 Mar 28 11:23 cdump
-rw-r--r--   1 oracle   dba      20979712 Apr  4 11:08 cwmlite01.dbf
-rw-r--r--   1 oracle   dba      20979712 Apr  4 11:08 drsys01.dbf
-rw-r--r--   1 oracle   dba      155983872 Apr  4 11:08 example01.dbf
-rw-r--r--   1 oracle   dba      26222592 Apr  4 11:08 indx01.dbf
-rw-r--r--   1 oracle   dba         1359 Mar 31 12:03 initcellnext.ora
-rw-r--r--   1 oracle   dba      38281216 Apr  4 11:08 nadeesh01.dbf-bak
-rw-r--r--   1 oracle   dba      20979712 Apr  4 11:08 odm01.dbf
-rw-r--r--   1 oracle   dba      1056768 Apr  4 11:08 sales04.dbf
-rw-r--r--   1 oracle   dba      1056768 Apr  4 11:08 sales05.dbf
-rw-r--r--   1 oracle   dba      2875392 Apr  1 10:23 standby.ctl
-rw-r--r--   1 oracle   dba      2875392 Apr  4 11:10 standby01.ctl
-rw-r--r--   1 oracle   dba      2875392 Apr  4 11:10 standby02.ctl
-rw-r--r--   1 oracle   dba      2875392 Apr  4 11:10 standby03.ctl
-rw-r--r--   1 oracle   dba      419438592 Apr  4 11:08 system01.dbf
-rw-r--r--   1 oracle   dba      10493952 Apr  4 11:08 tools01.dbf
drwxr-xr-x   2 oracle   dba         1536 Apr  4 11:08 udump
-rw-r--r--   1 oracle   dba      209723392 Apr  4 11:08 undotbs01.dbf
-rw-r--r--   1 oracle   dba      26222592 Apr  4 11:08 users01.dbf
-rw-r--r--   1 oracle   dba      39985152 Apr  4 11:08 xdb01.dbf
bash-2.03$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 4 11:10:33 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         4         ONLINE
/export/home/oracle/u01/app/oracle/oradata/standby/redo04.log

         5         ONLINE
/export/home/oracle/u01/app/oracle/oradata/standby/redo05.log

         6         ONLINE
/export/home/oracle/u01/app/oracle/oradata/standby/redo06.log


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 1 '/export/home/oracle/u01/app/oracle/oradata/standby/redo01.log' size 3M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 1 '/export/home/oracle/u01/app/oracle/oradata/standby/redo01.log' size 3M
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files


SQL>
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/export/home/oracle/u01/app/oracle/oradata/standby/redo04.log' size 3M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/export/home/oracle/u01/app/oracle/oradata/standby/redo04.log' size 3M
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files


SQL>


My primary and standby database configurations are correct and working because when I do log switch at primary, then I can view results on standby using:

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Below are my control files:

[1] Data Guard Primary Control File

bash-2.03$ cat spfilecellnext.ora
Q!
zì*.aq_tm_processes=1
*.background_dump_dest='/OracleHome/oracle/u01/app/oracle/admin/global/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/OracleHome/oracle/u01/app/oracle/oradata/global/control01.ctl','/OracleHome/oracle/u01/app/oracle/oradata/global/control02.ctl','/OracleHome/oracle/u01/app/oracle/oradata/global/control03.ctl'
*.core_dump_dest='/OracleHome/oracle/u01/app/oracle/admin/global/cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='global'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cellnextXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='cellnext'
*.java_pool_size=117440512
*.job_queue_processes=10
*.large_pool_size=16777216
*.log_archive_dest_1='LOCATION=/oracle2/arch2
mandatory reopen'
*.log_archive_dest_2='LOCATION=/oracle3/arch3
mandatory reopen'
*.log_archive_dest_3='SERVICE=79'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=117440512
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/OracleHome/oracle/u01/app/oracle/admin/global/udump'
bash-2.03$


[2] Data Guard Standby Control File

bash-2.03$ cat spfilecellnext.ora
        !
*.aq_tm_processes=1
*.background_dump_dest='/export/home/oracle/u01/app/oracle/oradata/standby/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/export/home/oracle/u01/app/oracle/oradata/standby/standby01.ctl','/export/home/oracle/u01/app/oracle/oradata/standby/standby02.ctl','/export/home/oracle/u01/app/oracle/oradata/standby/standby03.ctl'
*.core_dump_dest='/export/home/oracle/u01/app/oracle/oradata/standby/cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='/OracleHome/oracle/u01/app/oracle/oradata/global/','/export/home/oracle/u01/app/oracle/oradata/standby/'
*.db_name='global'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cellnextXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='cellnext'
*.java_pool_size=117440512
*.job_queue_processes=10
*.large_pool_size=16777216
*.log_archive_dest_1='LOCATION=/export/home/oracle/u01/app/oracle/oradata/arch_log_switc mandatory reopen'
*.log_archive_dest_2='LOCATION=/export/home/oracle/u01/app/oracle/oradata/arch_log_switc1 mandatory reopen'
*.log_archive_start=TRUE
*.log_file_name_convert='/OracleHome/oracle/u01/app/oracle/oradata/global/','/export/home/oracle/u01/app/oracle/oradata/standby/'
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_archive_enable='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=117440512
*.sort_area_size=524288
*.standby_archive_dest='/export/home/oracle/u01/app/oracle/oradata/standby/archivefromprim/'
*.standby_file_management='AUTO'
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/export/home/oracle/u01/app/oracle/oradata/standby/udump'
bash-2.03$
 

Operation timed out

vivek, April 16, 2005 - 7:08 am UTC

Tom,

I am trying to set up a standby database (ver 9.2) on windows. I followed the directions available from manuals. I have a problem in service name configuration. i can ping the stanby server from primary but on tnsping it gaves me operation timed out. Whereas i can ping and tnsping the primary server from standby server.

My service name and sid name for primary and standby are identical.

Could you please direct me what to look for this error.
Thanks

Tom Kyte
April 16, 2005 - 9:00 am UTC

please contact support for installation and configuration assistance.

A reader, April 22, 2005 - 9:42 am UTC

Tom,

ALTER DATABASE FORCE LOGGING;

This statement can take a considerable amount of time to complete because it waits for all unlogged direct write I/O operations to finish


Does Oracle go through the datafiles, redologs or ?? can you please provide details on this.

Thanks.

Tom Kyte
April 22, 2005 - 10:48 am UTC

why would you be doing it in the middle of a big load?

basically, the database cannot be in "force logging mode" if NON-LOGGED operations are taking place, so it just waits for them to complete.

Seems to be a strange command to be running in the middle of a lot of long running non-logged operations?

A reader, April 22, 2005 - 11:27 am UTC

Tom,

Maybe I wasn't clear enough, what I actually wanted to know was the time, how long would it be... If I understand correctly, if there aren't any nologged operations then it should'nt take much time.

Thanks.

Tom Kyte
April 22, 2005 - 12:25 pm UTC

correct.

it is a setting, should not take long to set. it doesn't have to "look" at anything, it sets a flag that other things will look at in the future to see if they should really be done non-logging

Dataguard wait events

raul, May 17, 2005 - 2:52 pm UTC

After recently moving from good old standby to DG - max. performance (LGWR ASYNC), started noticing the DG related wait events in statspack.

On a quiet day, with load on the database being about the same, I did a compare on DG set up between 'LGWR ASYNC=20480' and 'ARCH'.

This is 9.2.0.6

for a 60 minute interval:

LGWR ASYNC=20480:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
LNS wait on SENDREQ                                 1,193       1,014    23.93
log file sync                                       2,178         935    22.08
LGWR wait on full LNS buffer                      148,838         731    17.26
log buffer space                                      801         645    15.22
CPU time                                                          357     8.43

ARCH:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
ARCH wait on SENDREQ                                  850         801    50.30
CPU time                                                          374    23.45
db file parallel write                                651          87     5.46
LNS wait on SENDREQ                                    87          70     4.39
log file sync                                       1,411          60     3.78


log file sync in both cases, for each wait

SQL> select 935/2178 from dual;

  935/2178
----------
.429292929

SQL> select 60/1411 from dual;

   60/1411
----------
.042523033

I am concerned with 'log file sync' being at the top when DG is set up to log ship using LGWR.

'log file sync' time per wait for LGWR is 10x ARCH. 'user commits' 'user rollbacks' are about the same for both cases.

I've seen tuning recommendations on 'log file sync' to look at faster drives for redo logs, get rid of redundant commits/rollbacks..

Given this info, what kind of optimization should I look at? or is this just that I am suffering from some kind of CTD :)

Thanks for your time. 

Tom Kyte
May 17, 2005 - 3:10 pm UTC

but 60 minutes is a very very long time.

is that 10x number something that actually is affecting your apparent end user response time? that is what is key. Don't look at the small number and the big number and say the big number is larger -- look at the number that you get when the system is running with the features you need enabled and ask "is that number too big for what I'm doing and what the end users demand as far as performance goes"

Analogy:

Adding an index will make an insert go lots slower.
Not adding the index makes it so that queries never actually complete.

cost benefit: the cost of the index far outweighs the cost of not having the index.

Now that said, 1/2 second does seem very large. do you have application level traces that show this as well? How big are the transactions here (not that many, less than 1 per second right?)

Dataguard wait events

raul, May 17, 2005 - 9:59 pm UTC

I was trying to compare the resource usage between LGWR and ARCH, though they are fundamentally different techniques. I totally agree with your response on big number vs small number.

From Statspack, transactions are .47 per second - I was checking this on a quiet day.

"Now that said, 1/2 second does seem very large. do you have application level traces that show this as well?" -> Are you referring to 10046 traces that have 'log file sync' events? Can you help me understand how I check this out further i.e do it on certain sessions?

Thank YOU!

Tom Kyte
May 18, 2005 - 8:44 am UTC

Best way -- have the applications have the ability to enable trace at the drop of a hat (eg: application should be able to do this)

Semi best way -- use dbms_system to enable trace in the session (yes, with waits)

A reader, May 25, 2005 - 3:37 pm UTC

Is ALTER DATABASE Skipped SQL Statement on a Physical Standby Database?

TIA.




Tom Kyte
May 25, 2005 - 7:42 pm UTC

nothing is skipped, the database is in recovery mode, there are no "statements" being applied, just redo.

A reader, May 25, 2005 - 8:46 pm UTC

<>
nothing is skipped, the database is in recovery mode, there are no "statements" being applied, just redo.
</>

Not clear yet does that mean it "alter database" is applied?..

Thanks.

Tom Kyte
May 25, 2005 - 9:06 pm UTC

ORA-2819

VA, July 06, 2005 - 10:25 am UTC

Just wanted to share a recent experience...

During a recent switchover of my physical standby to a primary role, I followed all the instructions and when I did the alter database commit to switchover to primary, I got

ORA-2819 signalled during: alter database commit to switchover to primary...
ORA-2819: Write failed.

Thats it, nothing else, no Unix error messages, nothing in the alert log, nothing.

Turns out that my init.ora had max_dump_file_size=1M so the trace file that this statement generates in udump was getting truncated and causing the command to fail.

Doing alter session set max_dump_file_size=unlimited before issuing the switchover worked fine.

Hope this helps someone, it was frustratingly hard to figure this out, Metalink is silent on it, the error message is cryptic...

LGWR

Sandeep, August 11, 2005 - 5:20 am UTC

Hi Tom,

Trying to get facts on DG configurations... so going through this thread. Regarding the Guaranteed mode, I see you have mentioned "when we cannot get the redo to the failover site when we commit -- STOP, FAIL, HALT"
".

Now (my "understanding"!) LGWR writes committed as well as uncommitted transactions to the online redo logs. So, in a guaranteed DG configuration, the LGWR will try to do the same, i.e write BOTH committed and "uncommitted" transactions to the standby redo log on the standby database. Just wondering why you stressed on "when we commit" ? Shouldn't it just be "when we cannot get the redo to the failover site..."?

Thanks,
Sandeep

Tom Kyte
August 11, 2005 - 9:58 am UTC

I used "when we commit" in a general sense. specifically:

when anyone commits
when log buffer 1/3 full
every 3 seconds
when log buffer is 1 mb full


are the triggers for lgwr to write to disk. "when we commit" is the 'most common' and what most people think of when thinking about lgwr.

DataGuard (DG)

Sandeep, August 11, 2005 - 12:14 pm UTC

Hi Tom,

Thanks! that cleared up the LGWR transfer somewhat.
Still struggling with the DG modes...

a) I saw a link on this thread regarding DataGuard:

"</code> http://www.oracle.com/technology/deploy/availability/htdocs/DataGuardRedoShipping.htm?_template=/ocom/technology/content/print" <code>

within which there is a statement:

"Step 4

Lets assume yet another successful outcome. Your evaluation of network performance is complete, and you decide to make LGWR SYNC the default redo shipping transport by upgrading the Data Guard protection mode from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY
"

Isn't that a *downgrade* in protection mode actually?


b) Consider a case where the Physical DG configuration is operating in protection mode - "MAXIMUM AVAILABILITY". In which case LGWR sends the redo to the RFS on the standby server. Now if there is an issue, say a temporary network failure, no logs can be shipped by LGWR on primary to the standby (RFS). Also, since its a *network failure*, even ARCH on the primary can't do anything. Then, the network guys fix the issue and everything is hunky dory. Next time when the LGWR is activated on the primary, its can now start doing work as usual. But before this, the "gapped" archive logs need to be transported to the standby. Who does this? FAL/client/server services from the standby? OR
the ARCH on the primary?
Confused - as MAXIMUM AVAILABILITY states that - "if there is an issue, don't stop, but fallback to ARCH" - not quite sure what that means?! (If there is no network connection, what can ARCH do?)

Regards,
Sandeep

Tom Kyte
August 12, 2005 - 8:01 am UTC

a) from "PERF" to "AVAIL"

from "faster" to "slightly slower but no data loss"

maximum availability is like "no data loss when all is well" and like "maximum performance" when something goes wrong (and hence, I haven't see really the need for it personally, pick one or the other)



Multi-Instance Primary Database with a Multi-Instance Standby Database

Sami, August 21, 2005 - 6:04 pm UTC

Dear Tom,

Oracle document,
</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/rac_support.htm#635227 <code>
says

"***Ideally****", each primary database instance should archive to a corresponding standby database instance.
However, this is not required.


Metalink Note:203326.1 says

We have a Primary Database with two Nodes and two Instances (A and B). We
equally have a Standby-RAC enviroments, also containing the Standby Database with
two Nodes and Instances (C and D)

Instance A:
LOG_ARCHIVE_DEST_1=(location=/u10/ARCHIVE)
LOG_ARCHIVE_DEST_2=(SERVICE=C LGWR ...)
LOG_ARCHIVE_FORMAT=arc_%s_%t

Instance B:
LOG_ARCHIVE_DEST_1=(location=/u10/ARCHIVE)
LOG_ARCHIVE_DEST_2=(SERVICE=D LGWR ...)
LOG_ARCHIVE_FORMAT=arc_%s_%t


Mu question is
WHY each primary database instance should archive to a corresponding standby database instance?





Tom Kyte
August 21, 2005 - 7:25 pm UTC

parity, one to one-ness, having a failover environment that is a mirror image of the production sight. That is the "ideal" situation.

If you take a 4 node rac cluster and set up a standby that is a single instance on another server -- and you fail over, you just lost "rac, 4 nodes, etc", you are different.

The ideal situation is to have a standby that looks just like production, if you fail over, you have failed over to something totally equivalent.

Why don't we ship both primary instances arch log into one standby (directly)

Sami, August 21, 2005 - 9:27 pm UTC

Dear Tom,

I think I did not put my question correctly. I did NOT ask about the capacity issue here when we failover.

For example we have 2 node Primary ( A and B) and 2 Node Standby (C and D).

Assume D would be my recovery instance on the standby.

Oracle Document & Metalink Note recommends to have the following Ssetup.

Instance A:
LOG_ARCHIVE_DEST_1=(location=/u10/ARCHIVE)
LOG_ARCHIVE_DEST_2=(SERVICE=C LGWR ...) <==========
LOG_ARCHIVE_FORMAT=arc_%s_%t

Instance B:
LOG_ARCHIVE_DEST_1=(location=/u10/ARCHIVE)
LOG_ARCHIVE_DEST_2=(SERVICE=D LGWR ...) <==========
LOG_ARCHIVE_FORMAT=arc_%s_%t

This way STANDBY instance "C" receives PRIMARY "A"'s archive log and tranfers to "D" which is recovery instance.


Why can't I directly ship BOTH A and B archive logs to STANBY "D" as below.

Instance A:
LOG_ARCHIVE_DEST_1=(location=/u10/ARCHIVE)
LOG_ARCHIVE_DEST_2=(SERVICE=D LGWR ...) <==========
LOG_ARCHIVE_FORMAT=arc_%s_%t

Instance B:
LOG_ARCHIVE_DEST_1=(location=/u10/ARCHIVE)
LOG_ARCHIVE_DEST_2=(SERVICE=D LGWR ...) <==========
LOG_ARCHIVE_FORMAT=arc_%s_%t



Tom Kyte
August 22, 2005 - 7:24 am UTC

you asked:

...
WHY each primary database instance should archive to a corresponding standby
database instance?
......

the answer above stands, you asked "why would the documentation say 'ideally'", the answer to that is above.

ideally is "ideally", meaning "not mandatory"

Standby DB problem

Deepak, August 22, 2005 - 9:22 am UTC

Hi Tom,

I created a standby DB on server.(Both promary & the standby share the same server). Everything went fine till I issued the following command...


SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'E:\ORACLE\ORA927\ORADATA\REPLSTBY\SYSTEM01.DBF'

What does the above message mean? Please help me in sorting out this issue. 
 

Tom Kyte
August 23, 2005 - 3:52 am UTC

[tkyte@xtkyte-pc tkyte]$ oerr ora 16004
16004, 00000, "backup database requires recovery"
// *Cause: The control file is for a backup database which requires recovery,
// and cannot be opened for read-only access by this instance.
// *Action: Perform the necessary recovery and re-open for read-only access.


It is basically saying "your system01.dbf isn't old enough according to this control file"

[tkyte@xtkyte-pc tkyte]$ oerr ora 1152
01152, 00000, "file %s was not restored from a sufficiently old backup "
// *Cause: An incomplete recovery session was started, but an insufficient
// number of logs were applied to make the database consistent. This
// file is still in the future of the last log applied. The most
// likely cause of this error is forgetting to restore the file
// from a backup before doing incomplete recovery.
// *Action: Either apply more logs until the database is consistent or
// restore the database file from an older backup and repeat recovery.


So, it would seem that probably everything was not fine, you didn't set something up right along the way. I'd suggest reviewing your setup steps and verifying that everything is "correct". be really careful doing this on the same machine, way too easy to accidently point to the wrong files.

logs not getting shipped to standby database

Parag Jayant Patankar, August 24, 2005 - 8:36 am UTC

Hi Tom,

I am trying to build logical standby database on same server running AIX 5.3 Oracle 9.2.

My Primary Database is "infod" and standby database is "standby". For both databases currently having pfile only ( initXXXXX.ora )

Primary Database important Configuration
LOG_ARCHIVE_START = TRUE
LOG_ARCHIVE_DEST_1 = 'LOCATION=/apps/oradbf/infod/archive MANDATORY'
LOG_ARCHIVE_DEST_STATE_1 = ENABLE
LOG_ARCHIVE_DEST_2 = 'SERVICE=standby LGWR ASYNC'
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
LOG_ARCHIVE_FORMAT = infod_log%s_%t.arc
LOG_PARALLELISM = 1
resource_manager_plan = system_plan

Standby Database configuration

LOG_ARCHIVE_START = TRUE
LOG_ARCHIVE_DEST_1 = 'LOCATION=/apps/oradbf/standby/archive MANDATORY'
LOG_ARCHIVE_DEST_STATE_1 = ENABLE
LOG_ARCHIVE_FORMAT = infod_log%s_%t.arc
LOG_PARALLELISM = 1
resource_manager_plan = system_plan
standby_archive_dest = '/apps/oradbf/standby/primary'
parallel_max_servers = 9
instance_name = standby
lock_name_space = standby

(TESTINFO-oracle)/apps/oracle/product/920/dbs > ls -ltr /apps/oradbf/standby/primary

It shows directory is present.

Listener Configuration

standby =
(DESCRIPTION =
(ADDRESS =
(COMMUNITY = TCP)
(PROTOCOL = TCP)
(Host = mumsa128200 )
(Port = 1521)
)
(CONNECT_DATA =
(SID = standby )
(GLOBAL_NAME = standby )
)
)

infod =
(DESCRIPTION =
(ADDRESS =
(COMMUNITY = TCP)
(PROTOCOL = TCP)
(Host = mumsa128200 )
(Port = 1521)
)
(CONNECT_DATA =
(SID = infod)
(GLOBAL_NAME = infod)
(SERVER = DEDICATED )
)
)

I am able to connect to databases by infod and standby connect string.

But my problem is when I am archiving on primary database i.e. infod log files are not automatically getting shipped to /apps/oradbf/standby/primary location.

Q1. Kindly tell me why it is not happening and what corrective action should be taken ?

Q2. As primary database logs are not shipped to standby site, Does I have to copy these missing logs manually and apply them by "alter database start logical standby apply;" command ?

regards & thanks
pjp








Tom Kyte
August 24, 2005 - 2:08 pm UTC

please utilize support for this.

sorry I missed small detail of my question -

Parag Jayant Patankar, August 24, 2005 - 9:03 am UTC

Hi Tom,

Sorry, I have missed out small part of details of my question in this thread. Query result from primary database ( infod ) is under

18:17:33 SQL> SELECT DEST_ID "ID",
18:17:36   2  STATUS "DB_status",
18:17:36   3  substr(DESTINATION,1,50) "Archive_dest",
18:17:37   4  substr(ERROR,1,50) "Error"
18:17:37   5  FROM V$ARCHIVE_DEST
18:17:37   6  /

        ID DB_status Archive_dest                                       Error
---------- --------- -------------------------------------------------- -------
         1 VALID     /apps/oradbf/infod/archive
         2 VALID     standby
         3 INACTIVE
         4 INACTIVE
         5 INACTIVE
         6 INACTIVE
         7 INACTIVE
         8 INACTIVE
         9 INACTIVE
        10 INACTIVE

regards & thanks
pjp 

backup controlfile ?

Parag Jayant Patankar, August 25, 2005 - 10:42 am UTC

Hi Tom,

I am creating Oracle 9i Logical Standby database on AIX 5 on same server ( for testing only ).

In Data Guard Manual ( PDF file page no : 86 ) it is said that 

Step 4 Create a backup copy of the control file for the standby database. On the primary database, create a backup copy of the control file for the standby database:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO
2> '/disk1/oracle/oradata/payroll/standby/payroll3.ctl';

My Questions regarding this are 

1. Does it missing "backup standby controlfile" word ? if not what is the use of this control file ? Because as per this manual section 4.2.2 step no 1 and 2 I have stopped the primary database and copied datafiles,logfiles and controlfiles as specified which are having consistent stage.

2. On primary database I am mirroring 2 controlfiles at two diffrent locations. On Standby Database I am going to keep same policy. Then on standby database do I have to copy this controlfile to two locations and rename datafiles or I have to user controlfiles which I have backed up along with datafiles ?

3. Suppose my primary database is "infod" and standby database is "standby". Then what should be value of log_file_format parameter of standby database ? On primary db I have defined "infod LOG_ARCHIVE_FORMAT=infod_log%s_%t.arc. Do I have to keep same or change it ? If on standby db I change the value do I require other parameters to be set ?

regards & thanks
pjp
 

Standby logfile - How it is used in Data Guard.

Sami, August 26, 2005 - 7:12 pm UTC

Dear Tom,

Thanks for all your help in the past.

Question 1)
============

After reading the Oracle document I know standby logfile is used in PHYSICAL standby database to provid Maximum Protection or High Availability. However it didn't give more explanation about how these standby logfiles are used
to provide the Maximum Protection or High Availability.

A) Could you please explain this with more explanation in detail?

B) Also please let me know whether standby logfiles are used only during switchover/failover OR used through out recover process on standby DB.





Question 2)
============
Release 9i R2.
I did setup & Switchover in between 2 node RAC Primary and 2 Node Physical Standby database

<Document Starts>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/role_management.htm#1033701

Step 5 Switch the physical standby database role to the primary role.

You can switch a physical standby database from the standby role to the primary role when the standby 
database instance is either mounted in managed recovery mode or open for read-only access. It must be 
mounted in one of these modes so that the primary database switchover operation request can be coordinated.

The SQL ALTER DATABASE statement used to perform the switchover automatically creates online redo logs if 
they do not already exist. This might significantly increase the time required to complete the COMMIT 
operation. Therefore, Oracle Corporation recommends that you always manually add online redo logs to the 
target standby database when you create it. Use one of the following methods to manually add the online 
redo logs if they do not already exist:

    * Copy the existing online redo logs from the initial primary database site to the target standby database site and define the LOG_FILE_NAME_CONVERT initialization parameter to correctly associate the standby site path names to the new online redo logs (see Section 3.2.6).
    * Drop any existing online redo logs at the target standby site and create new ones using the ALTER DATABASE ADD STANDBY LOGFILE statement.

After you manually add the online redo logs, use the following SQL statement on the physical standby database that you want to transition to the primary role:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; 

</Document Ends>

I added STANDBY LOGFILE like belwo in my STANDBY database.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/u01/oradata/primary/sredo11.log')  size 10M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('/u01/oradata/primary/sredo12.log')  size 10M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('/u01/oradata/primary/sredo13.log')  size 10M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 ('/u01/oradata/primary/sredo21.log')  size 10M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 22 ('/u01/oradata/primary/sredo22.log')  size 10M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 23 ('/u01/oradata/primary/sredo23.log')  size 10M;


Now i have both REDO LOG files and STANDBY REDO LOG files in my standby database.
However when I try to switchover from Standby to Primary, it says file exists.


SQL>  select switchover_status from v$database;
 
SWITCHOVER_STATUS
------------------
TO PRIMARY
 
 
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/u01/oradata/primary/sredo11.log'
ORA-17502: ksfdcre:4 Failed to create file /u01/oradata/primary/sredo11.log
ORA-17500: ODM err:ODM ERROR V-41-4-17 File exists
 
 
*******REMOVED ALL STANDBY LOGFILES MANUALLY**********


SQL> l
  1* alter database commit to switchover to primary
SQL> /
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/u01/oradata/primary/redo11.log'
ORA-17502: ksfdcre:4 Failed to create file /u01/oradata/primary/redo11.log
ORA-17500: ODM err:ODM ERROR V-41-4-17 File exists

*******REMOVED ALL REDO LOGFILES MANUALLY********** 
 
SQL> l
  1* alter database commit to switchover to primary
SQL> /
 
Database altered.


My questions is 

C) Why is it complaining about the existing STANDBY LOGFILEs? [I created these files as per Oracle recommendation to reduce the time required to complete the COMMIT  operation during switchove]


Tom, Again thanks for your valuable time & effort. 

Protection Mode and switch over

Sami, September 05, 2005 - 3:32 pm UTC

Dear Tom,

I have 2 Node RAC primary and 2 Node RAC Standby.
The above setup is running in "Maximum Availability Mode".

Before I did switch over I did shutdown the second instance so that both side only one instance was running.

From Standby Database:
=========================

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
------------------
SWITCHOVER PENDING

SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required

 
SQL> recover standby database;
ORA-00279: change 318016 generated at 09/04/2005 13:57:41 needed for thread 1
ORA-00289: suggestion : /u07/oradata/dgtest/0001S0000000056.ARC
ORA-00280: change 318016 for thread 1 is in sequence #56
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 318016 generated at 09/03/2005 21:06:49 needed for thread 2
ORA-00289: suggestion : /u07/oradata/dgtest/0002S0000000009.ARC
ORA-00280: change 318016 for thread 2 is in sequence #9
 
 
ORA-00328: archived log ends at change 317689, need later change 318016
ORA-00334: archived log: '/u07/oradata/dgtest/0002S0000000009.ARC'




Question:

It says "Media Recovery Required". Since I configured my Data Guard in Maximum Availability mode, Do I have to put it in "Maximum Performance Mode" before I do switchover?


Thanks Tom.
 

Tom Kyte
September 05, 2005 - 6:52 pm UTC

no, you just need to perform more recovery. data guard works by accumulating the redo at the standby site *and then* applying it after they (the redo logs) fill up.

Failover / resetlogs in 10G

Guy Lambregts, September 06, 2005 - 3:44 am UTC

Tom

From
"</code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10823/role_management.htm#1024703" <code>
...
During failover involving a physical standby database:

In all cases, after a failover, the original primary database can no longer participate in the Data Guard configuration.
...

I think to know -I might be wrong- a wonderfull 10G enhancement is the possible recovery through resetlogs (despite the new incarnation) which was not available in previous releases. I know in case of failover we open the standby with resetlogs (even in case of switchover I think).

Let' s suppose. We have at the primary site severe corruption off all the index datafiles on a given device ... the time to repair the hardware is ... hours ... we have a standby ... we decide to activate it = failover = resetlogs. Then the repair of the hardware is going on let 's say in two days everything is ok, let 's say we restore with rman the missing files ...

Q: Can,' t we use the datafiles from the previous primary site ... in order to have applied on them the missing redo (coming from the new primary(without standby) site) ... in order to renew the primary/standby relationship ... en then switch > as such the old (first) primary become the new primary again.

I should expect this is possible because the recovery through resetlogs. Offcourse I should always create a new standby controlfile (which is a piece of cake). Am I wrong ?

Regards
Guy

Tom Kyte
September 06, 2005 - 8:23 am UTC

I believe there are opportunities to counter that statement, yes, but I would neither

a) rely on them
b) hope for them

you have failed over, a disaster has struck, you are "dead" on the production site. Unless you were running in zero data loss (or were able to apply the failed sites online redo logs), you probably lost a transaction or two at the very end, the systems would not be in sync even if you could bring it back up.

In your scenario, sounds like a switchover might be possible.

Online and Standby Redlo Logs in a Data Guard Setup

Vivek Sharma, September 10, 2005 - 5:02 pm UTC

Hi Tom,

Thanks for all the valuable information on this site. The information contained in this forum really helped me understand the concepts of Data Guard.

In this forum and in Oracle Documentation as well, I read that we need to have either same or more number of Standby redo logs on the Physical Standby Database.

I have a 9204 instance. The primary instance has 3 redo logs of 100M each and I have configured the Standby with 3 Standby redo logs files as well of 100M each. But at any given point in time, whenever I query v$standby_log view, I can see that only 2 Standby log files are used whereas the 3rd is UNASSIGNED.

GROUP# THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANG
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------
1 1 121 157286400 0 YES ACTIVE 8814632 11-SEP-05 0
2 1 120 157286400 157285888 NO ACTIVE 8812169 11-SEP-05 8814632 11-SEP-05
3 0 0 157286400 1024 YES UNASSIGNED 0 0

I executed a big pl/sql block to insert 10M rows in a table which did 2-3 log switches, but used on 2 Standby redo logs on the Standby. What could be the reason ?

Regards
Vivek

2 different status on each instance (2 Node RAC Standby)

Sami, October 01, 2005 - 12:42 pm UTC

Dear Tom,

I am experiencing some weird behavior in standby database.

Environment:
===========
Sun OS 2.9, Oracle 9.2.0.6,Veritas 4.0

I did setup 2 Node Primary and 2 node Standby physical standby setup as below.

2 Node Primary (test1,test2) 
2 Node Standby (test1,test2)


Test Case:
==========
SQL> show parameter cluster (on both standby1 and standby2)
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string


1) Both standby instances are in READ-ONLY mode by using

SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.


2) From instance1 (TEST1), I issued the following command while instance2 (TEST2) is in READ-ONLY mode.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
 
Database altered.


3) From instance1 (TEST1), I did


SQL> select dbid,name,database_role,open_mode,SWITCHOVER_STATUS from v$database;
 
      DBID NAME      DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
---------- --------- ---------------- ---------- ------------------
1867751112 TEST      PHYSICAL STANDBY MOUNTED    RECOVERY NEEDED
 

4) From instance2 (TEST2), I did


SQL> select dbid,name,database_role,open_mode,SWITCHOVER_STATUS from v$database;
 
      DBID NAME      DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
---------- --------- ---------------- ---------- ------------------
1867751112 TEST      PHYSICAL STANDBY READ ONLY  SESSIONS ACTIVE


5) From instance1(TEST1), I did

SQL> select database_status,instance_role,status from gv$instance;
 
DATABASE_STATUS   INSTANCE_ROLE      STATUS
----------------- ------------------ ------------
ACTIVE            PRIMARY_INSTANCE   MOUNTED
 

6) From Instance2(TEST2), I did

SQL> select database_status,instance_role,status from gv$instance;
 
DATABASE_STATUS   INSTANCE_ROLE      STATUS
----------------- ------------------ ------------
ACTIVE            PRIMARY_INSTANCE   OPEN



Questions:
==========
Q1) How the cluster database is showing 2 different status in 2 difference instance

Q2) Why gv$instance is reporting only one instance event though both are either mounted or open for READ-ONLY
 

Tom Kyte
October 01, 2005 - 9:12 pm UTC

you are seeing instance information in these fields - you have them in two different modes. Why would you do this by the way?

the instances are in different modes in fact, you told them to be.

Approach

Vikas, October 18, 2005 - 7:34 am UTC

Hi Tom,

We have an OLTP 24*7 system running and want the data to be available to the datawarehouse in smallest amount of time. This OLTP system is being managed by some other team to which they are n't providing us the access to create a db link and get the data pulled out on a table to table basis using MERGE command.

Then we put forward the approach to create a standby database for us so that we can acess the same with a lag of one or two archived redo copies. However, the physical standy by database is constantly in a recover mode state in a MOUNT state, but can be opened for READ only purposes.

The data in that state can be pulled into the DW and then again put back to the RECOVER state so that the archive log copies can be applied to the standby database. Is a logical database creation a better approach, where the Data guard mode (RAPID) can be beneficial.

I think the logical standby database is opened in READ-WRITE mode and the redo streams are applied continuously to the standby database.

What do you suggest should be the best approach.


Thanks

Tom Kyte
October 18, 2005 - 9:14 am UTC

hard to say - if the oltp team is unwilling to work with you. there are other ways to capture this information - such as STREAMS, have you looked at that.

Fast-Start Failover

Thiru, October 25, 2005 - 3:49 pm UTC

Hi Tom,

I have read that this new feature Fast-Start Failover in 10G R2 is automatic.
a. So will this be considered a HA feature?
b. Will there by any downtime at all?
c. If there will be downtime, then what would be your recommendation for a 24 x 7 availability.
d. How do I go about estimating costs for a RAC setup if this is one of the options?

Thanks for the time.

Tom Kyte
October 26, 2005 - 11:28 am UTC

a) yes
b) yes, the failover has to be detected, and performed.
c) don't allow natural disasters, fires, explosions to ever occur?
d) rac is just part of it - rac would need a failover solution to protect from some of the things in c)

To estimate the costs from a software perspective all you need to know is the number of CPU's.

But then figure out what you want to do with your hardware ( lots of redundancy to make failover something that happens only if something in C happens...)...

And how much your people costs will be (24x7 isn't a lights out sort of situation typically...)



Wonderful answer as expected

Thiru, October 26, 2005 - 11:38 am UTC

Tom,

One more if you may.

a. I was looking at a Amazon case study of DG with the new feature. The auto fail over took less than 20 secs. In this case, am I right in saying that:

All active transactions will be rolled back.

All selects and open cursors will have to be re-executed.

The clients will know that there has been a failure.

Thanks Tom.

Tom Kyte
October 27, 2005 - 3:23 am UTC

Yes, the in-flight transactions will be cancelled (the primary host has "died")

Yes, all users will be switched over - whatever they were doing in the primary site is "over"

Yes, the clients will likely know that a failure happened - the application might "hide" this from the ultimate end user, but the application will "know"

understanding of standby redologs

A reader, November 29, 2005 - 10:24 am UTC

I am having some difficulties understanding standby redo log even after read the entire thread and the doc.

If I am using maximum protection and availability I understand I need the standby redolog since commit is run in both primary and standby site. i.e Oracle makes sure the transaction is written in the standby redolog so even primary database burns I have the transactions. The redo in these two cases are shipped sychronously. Corect?

If I am using maximum availability I dont need the standby redologs. The redo is shipped asynchronously, my doubt here is where are the redo stored in the standby site? Since there are no standby redologs?

I am a bit confused how the redo are applied in the standby. Do MRP process apply archived logs or it also applies standby logs. i.e Does it wait the log switch to archive the standby redo and then use the generated archive log to apply the redo or it applies the redo as it receives in real time?

THANKS

Tom Kyte
November 30, 2005 - 10:41 am UTC

in "no data loss mode", log data is shipped synchronously, yes.


If you do not use standby redologs, the redo is written to archive redo logs on the standby. Instead of putting them into standby redo logs (which are then turned into standby archive logs after they are full), it is put directly into the archive log on the standby.

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/standby.htm#sthref286 <code>

<quote>
2.5 Online Redo Logs, Archived Redo Logs, and Standby Redo Logs

The most crucial structures for Data Guard recovery operations are online redo logs, archived redo logs, and standby redo logs. Redo data transmitted from the primary database is received by the remote file server (RFS) process on the standby system where the RFS process writes the redo data to archived log files or standby redo log files. Redo data can be applied either after the redo is written to the archived redo log file or standby redo log file, or, if real-time apply is enabled, directly from the standby redo log file as it is being filled.
</quote>


Where/how the redo is applied depends on if you are using real time apply (mandates the use of standby redo logs - redo is applied as it arrives) or not - if not, it is applied from the archives after they are "filled" (or after some time delay if you have set up delayed apply)

understanding of standby redologs

A reader, November 30, 2005 - 11:07 am UTC

Hi

I am still confused how logs are applied, in one of your previous replies you said

"no, you just need to perform more recovery. data guard works by accumulating the redo at the standby site *and then* applying it after they (the redo logs) fill up."

IF we are using maximum protection and availability how the logs are applied? Are redo sent to standby redo logs applied on real time OR after the Standby redo are archived and then MRP process applies archived logs to the standby database?

What it confuses me is, are standby redo logs used by MRP process to apply the redo or MRP always uses archived logs :-?

THANX

Tom Kyte
November 30, 2005 - 3:03 pm UTC

you either configure real time apply using standby redo logs and the redo is applied as it arrives.

OR

you don't configure real time apply and the redo is applied from the archives on the standby.

max protection/availability is not the key to when it is applied, "real time apply or not" is.



how do you force real time apply?

A reader, November 30, 2005 - 5:41 pm UTC

hi

how do you force redo real time apply at standby site?

using current logfile to start real time apply

Charlie Zhu, November 30, 2005 - 6:05 pm UTC

To start real time apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile parallel 8 DISCONNECT;

It's easy to find it out by Oracle Documentataion
on otn.oracle.com

mine is 9i

A reader, December 01, 2005 - 2:10 am UTC

I forgot to mention, my data guard will be run on 9i. Seems in 9i there is no such thing called real time redo apply?!

Tom Kyte
December 01, 2005 - 12:27 pm UTC

use rman to create physical standby

A reader, December 02, 2005 - 8:29 am UTC

Hi

I am following Note:183570.1 from Metalink to create a Physical Standby.

I am trying to do all this with a cold backup instead of hot backup however whenever I run

duplicate target database for standby dorecover;

command it says

RMAN-05507: standby controlfile checkpoint (498158) is more recent than duplication point in time (472507)

How so? Is a hot backup compulsory?

that's a bug

A reader, December 05, 2005 - 4:12 pm UTC

it is a known bug that you cannot using rman cold backup + standby controlfile to create a standby database

Impact on hot backup from data guard

Fun to have data guard, December 07, 2005 - 3:02 pm UTC


Hi Tom,

I have been reading your forum for a long time. It has been very useful. And Now I have an ugent issue needed to be resolved regarding the data guard and hot backup.

We just implemented the data guard in our production environment over the weekend, and we found since then, the full database hotbackup is impacted. Before it took 1.30hours to finish the backup, but now it takes more than 10 hours!!! (unacceptable)

We expected the data guard would have slight impact on the hotbackup, but not this much! can you explain it? and what will be the approach to fix it? Thank you!

Urgently.


Tom Kyte
December 08, 2005 - 1:39 am UTC

and how exactly do you backup and what do you see different on your system (what is the bottleneck here)

DataGuard and backup

Charlie Zhu, December 07, 2005 - 6:37 pm UTC

I don't have this problem, same time used to do RMAN compressed backup as before deploy the DataGuard.

We're runing 4 node's RAC plus 2 physical stadby site,
2nd standby db is for reporting.

Oracle 10.1.0.4 + Patch p4074603_10104_LINUX.zip
(4074603:FAL DOES NOT COPY ARCHIVES FROM CROSS INSTANCE ARCHIVE DESTINATION)
Linux 2.4.21-32.0.1.ELhugemem #1 SMP i686 i386 GNU/Linux


Tom Kyte
December 08, 2005 - 1:55 am UTC

(yes, I think the two things are pretty much unrelated myself...)

Impact on hot backup from data guard

JD, December 08, 2005 - 10:13 am UTC

Hi Tom,

Sorry, I forgot to specify what hotbackup we are using.

we are backing up the database with the traditional way: take the tablespaces in backup mode; copy the files physically (one tablespace a time).

I have not actually seen any bottleneck, except the database creates 3 or 4 extra archived logs.

Do I have to implement RMAN to resolve this issue?

Thank you!

Tom Kyte
December 08, 2005 - 11:52 am UTC

no, you need to find out what the bottleneck is.


Unless you used to generate "almost no redo" and now you generate "hundreds of times the redo", I doubt that data guard and backup taking longer are *related* here.


How much MORE redo are you generating.

Find the bottleneck, don't make "I see X and I saw Y therefore X causes Y" leaps of conclusion so fast.

Some more info requested.

VG, December 11, 2005 - 4:21 am UTC

Tom, We have a server with 2 processors and we want to make a virtual server (Win2K) with in the same server and run Oracle 8.1.6 with common storage. In other words there will be 2 instances sharing common storage on the same box. This is to avoid the instance failure. Is this possible. What sort of raid is best preffered here. Where can I get the notes or help on this. R/ Vinod

Tom Kyte
December 11, 2005 - 4:35 am UTC

that would not be a very good idea. first of all, instance failure would be much less likely than hardware/OS failure. So, you are not protected.

Second, you only have 2 cpus, not a very large machine.

Third, you are running 8.1.6 which is so far out of support as to not even be something to consider deploying on.

Fourth, it would have been OPS (parallel server) way way back then.


No notes, recommendation: use a single instance, with current supported software.

A reader, January 03, 2006 - 5:12 pm UTC

Tom,

We are in the process of designing a 8TB database(RAC) 10G R2 and we will be having a DR Site(Physical Standby Databse).

I am in favour up dataguard but we've got recommendations if loads could be done in parallel on both servers then its preferrable over dataguard as its much performant. Can you please provide your valuable feedback?

Thanks.



Thanks.

Tom Kyte
January 03, 2006 - 6:07 pm UTC

depends, need more information. is this a "warehouse" with tons of readonly data?

remote_archive_enable=true

Aru, January 25, 2006 - 5:07 pm UTC

Hi Tom,
The only thing in the docs - chapter 3, I did not understand was that, do I have to put "remote_archive_enable=true"
in the primary database parameter file. It cleasly shows that it has to be put in the physical standby parameter file.
Regards,
Aru.

Tom Kyte
January 25, 2006 - 5:12 pm UTC

hard to say what "chapter 3" is - in 10g, this parameter for example is deprecated. but if you check out the Reference guide - you'll see it can be used to configure both sending a receiving (it is for either end of the situation)

Remote_archive_enable=true

Aru, January 25, 2006 - 5:20 pm UTC

Hi Tom,
Thanks for the reply.

The docs are 9i docs. Remote_archive_enable=true then does not have to be on the primary database??

Also will I be right in saying that :-
I have a 9i database I can setup a standby database using hot backup and putting all init.ora parameters and I do not have to restart my primary database even once.
I.E:- there are no parameters which need the database to restared.( All parameters on the primary side are dynamic ).

Thanks,
Regards,
Aru.

Tom Kyte
January 25, 2006 - 7:02 pm UTC

it is true by default:

ops$tkyte@ORA9IR2> select value, isdefault from v$parameter where name = 'remote_archive_enable';

VALUE
-------------------------------------------------------------------------------
ISDEFAULT
---------
true
TRUE



There are in general Oracle parameters that you may want to change that are not dynamic 

Vikas, February 25, 2006 - 12:34 pm UTC

Hi Tom,

Thanks for all the valuable information on this site. 

I configured DG (Maximum Performance Mode) using ARCH as the transporter,on two database boxes, with the db_unique_name being test_a and test_b, where a is the primary host and b is the standby host.

I have also created the standby Redo logs (5,6,7,8) on both the databases since it will help in Switchover in near future.

Primary database - test_a
SQL> Select * from V$standby_log;

GROUP# DBID   THREAD# SEQUENCE# USED ARC   STATUS    
5  UNASSIGNED 0       0         512  YES   UNASSIGNED      
6  UNASSIGNED 0       0         512  YES   UNASSIGNED      
7  UNASSIGNED 0       0         512  YES   UNASSIGNED      
8  UNASSIGNED 0       0         512  YES   UNASSIGNED      

Standby database - test_b
SQL> Select * from V$standby_log;

GROUP# DBID   THREAD# SEQUENCE# USED ARC   STATUS    
5  UNASSIGNED 0       0         512  YES   UNASSIGNED      
6  UNASSIGNED 0       0         512  YES   UNASSIGNED      
7  UNASSIGNED 0       0         512  YES   UNASSIGNED      
8  UNASSIGNED 0       0         512  YES   UNASSIGNED      

-- Cutting out the irrelevant information.

Questions:

1. What is the purpose of SRL's file(s) in general, since I felt during the whole process the ARCH will create the offline copy at the local destination, and another arch process async'ly will copy the file to Remote location over SQL*NET using SERVICE mentioned in the Log_archive_dest_n parameter. 

2. The DB_ID and the Status for the Standby log files is showing as 'UNASSIGNED'. Is it true becuase we are using the ARCH b'ground process to transport the redo.
 
3. Will they ever get utilized?

4. V$archived_log Contents in the test_a database are listed as follows:
SUBSTR(NAME,1,50)                                     DEST_ID  SEQUENCE# STA ARC APP
-------------------------------------------------- ---------- ---------- --- --- ---
/arch-01/databases/admw/redolog-40-1-583227512.arc          1         40 NO  YES NO
test_b                                                      2         40 YES YES YES
/arch-01/databases/admw/redolog-41-1-583227512.arc          1         41 NO  YES NO
test_b                                                      2         41 YES YES NO
/arch-01/databases/admw/redolog-42-1-583227512.arc          1         42 NO  YES NO
test_b                                                      2         42 YES YES NO
/arch-01/databases/admw/redolog-43-1-583227512.arc          1         43 NO  YES NO
test_b                                                      2         43 YES YES NO
/arch-01/databases/admw/redolog-44-1-583227512.arc          1         44 NO  YES NO
test_b                                                      2         44 YES YES NO
/arch-01/databases/admw/redolog-45-1-583227512.arc          1         45 NO  YES NO
test_b                                                      2         45 YES YES NO
/arch-01/databases/admw/redolog-46-1-583227512.arc          1         46 NO  YES NO
test_b                                                      2         46 YES YES NO

Questions: 
Why test_b as "Member"  being shown in the V$archived_log?
Destination_id = 2 and sequence# is repeated, and standby_destination = 'YES'

Some applied columns againt test_b are showing as 'YES' and all others are showing as 'NO'? why it is so?

Standby Server : V4archived_log

/arch-01/databases/admw/redolog-40-1-583227512.arc
         1         40 NO  YES YES

/arch-01/databases/admw/redolog-41-1-583227512.arc
         1         41 NO  YES NO

/arch-01/databases/admw/redolog-42-1-583227512.arc
         1         42 NO  YES NO

/arch-01/databases/admw/redolog-43-1-583227512.arc
         1         43 NO  YES NO

/arch-01/databases/admw/redolog-44-1-583227512.arc
         1         44 NO  YES NO

/arch-01/databases/admw/redolog-45-1-583227512.arc
         1         45 NO  YES NO

/arch-01/databases/admw/redolog-46-1-583227512.arc
         1         46 NO  YES NO

Why the Archived redo logs are still showing the Applied Column as NO for sequence no 42 - 46?

All the offline copies of redo logs (till seq 46) have been foung under the log_archive_dest_1 and _2 destinations ??

on checking whether there is some gap between Primary & standby it reported :

SQL> Select * from V$archive_gap;

no rows selected

Please help and guide, will appreciate that!
 

Tom Kyte
February 26, 2006 - 9:54 am UTC

SRL's? I assume you mean standby redo log files...

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/standby.htm#i72373 <code>

outlines their use and usefulness.

Data Guard=- related to SRL's

Vikas, February 26, 2006 - 10:12 am UTC

Hi Tom,

Thanks for the link, I got to understand the usefulness of SRL's.

Can you please answer the other questions listed in the previous forum by me.

Thanks


Tom Kyte
February 26, 2006 - 10:30 am UTC

this is actually a place for followup and review on the original question, not really "here is a new one for you".


the redo logs you have haven't been used yet #2, they just haven't been used.

#3 yes, when you open the standby for use in your case - since you really aren't using them in the mode you are running.


#4 you are archiving to where? over there - therefore the fact that it happened (or not) is recorded.

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1016.htm#sthref3455 <code>

explains the contents of that view and the meaning of yes and no.

Triggers on Logical standby

Vikas, February 28, 2006 - 11:42 am UTC

Hi Tom,

Thanks Tom in clarifying these questions.

This is some what complex and definitely need guidance and advice. We have an OLTP box for which a logical standby database has been created whih updates the data based on SQL streams via LGWR. Hence and updates to data as well as schema definition chages are rolled out to logical standby automatically.

Now we have another datawarehouse box on which a procedure is being executed which pulls the records from the Logical standby box on an hourly basis and updates the tables in the warehouse, using a db link and MERGE command.

Recently, some DDL changes were introduced to those tables in OLTP environment, which were never propogated to the datawarehouse environment, because the procedure got failed since the SQL statement is dynamically generated and that column information was missing in the datawarehouse schema table.

Is there some method, like DDL triggering event which can alter the structures on the datawarehouse schema automatically.

Please advise.

Tom Kyte
March 01, 2006 - 7:40 am UTC

what is an OLTP box?


but anyway - you really want something to automagically propagate schema DDL changes to your warehouse - hmm, interesting.

Not sure if I'd touch that one with a 1000 foot pole myself.

But if you use "replication" as you already are (from one system to whatever this OLTP box is), you know how this works.

There will be nothing that will automagically rewrite your custom code and merges however.

Trigger on Logical standby

Vikas, March 02, 2006 - 1:39 am UTC

Hi Tom,

OLTP box means a production database which is online 24*7.

Hmm, I thought of a solution but just need your advice if that's appropriate one.

Steps:
1. We will create a trigger "create or alter or drop on SCHEMA" and log the changes in a table on the datawarehouse schema using INSERT INTO log@warehouse where warehouse would be the defined database link.

2. There will be another trigger BEFORE INSERT on this table which will execute the DDL on the table concerned and the transaction gets committed.

Will this theory be converted into practical reality.

We are not using the replication but a job which executes the procedure on an hourly basis to get the updated/new records in the warehouse. We were just concerned if some one does a schema change on the production instance, this procedure will fail as the data gets into warehouse box using MERGE command.

Thanks


2.

Tom Kyte
March 02, 2006 - 12:12 pm UTC

...
OLTP box means a production database which is online 24*7....

huh, that is a new one on me. I thought it would be a transactional system - which could be 9-5x5, or whatever.

I am very skeptical of anything that tries to automate schema changes like this from a transactional system to a data warehouse. In fact, to me, it doesn't really seem sensible.



Oracle Data Guard 10g Release 2.

Dawar, March 24, 2006 - 8:15 am UTC

Tom,

We planned to do us Data Guard (DB version: 10 .2, rel 2) for our production.

We have made this decision in less than a one day. (Not a good practice, but I have not control on it).

This is my responsibility to implement this into production.

Could you pl. give me some advantages and disadvantages to use data guard?

Currently, we planned to used Two AIX Servers and one window box as a Data Guard Broker.

One AIX box will be our main primary production database, number two AIX box will be standby.

From the windows box we will monitor agents in both servers.

In case of failuer of primary box we will start running standby box.


Is it correct approach?

Do you have any general idea how long it takes to impliment Data Gurad compare to just a traditional Database?

Are there any good documentation available on ot?


Regards,
Dawar


Tom Kyte
March 24, 2006 - 10:00 am UTC

advantages as compared to WHAT exactly.

have you read the documentation itself, it is pretty good.

Standby Database Configuration in Data Guard architect

Dawar Naqvi, March 29, 2006 - 11:22 am UTC

Tom,

A Data Guard configuration consists of one production database and atleast one standby databases.

There are two types of standby DB.

** Physical standby database

A physical standby database is kept synchronized with the primary database, though Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.

** Logical standby database

The logical standby database is kept synchronized with the primary database though SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executing the SQL statements on the standby database.

Do you have any preference on any standby database configuration?

Which one is good in maintenance purposes for the 24 * 7 env?

Which one has more work in configuration? etc

Please also confirm.

We have two AIX boxes.
One should be primary and other be standby. (Same OS).

But our observer will be on windows 2003.
I think its ok but I want your views.

Regards,
Dawar

Regards,
Dawar

Tom Kyte
March 29, 2006 - 11:41 am UTC

... Do you have any preference on any standby database configuration? ..

yes, but like the wind, it blows in different directions depending on the weather.

I have a preference - but only after understanding the needs, goals. If one was always superior to the other - there would only be one.


.... Which one is good in maintenance purposes for the 24 * 7 env?...

both

.... Which one has more work in configuration? etc ...

likely the logical standby could be said to require more care and feeding since it is opened read only and typically doing other stuff.


"observer"? you mean the data guard monitor. Yes, the standby must be on the same platform as the primary - but the monitoring software (enterprise manager stuff) may be elsewhere.

few basic questions on dataguard,

A reader, March 29, 2006 - 3:08 pm UTC

Can we use dataguard on a non-archivelog database?
What is the difference between physical and logical standby database?
Can we use dataguard only on a set of tables?
The way the two databases (primary and standby) communicates
and the way the db link communicates, is both similar?
If we have a trigger to capture all DML activities and apply the same to another database
using a db link (I know this is not a good idea), which performs better (in speed wise)?

Tom Kyte
March 29, 2006 - 7:10 pm UTC

no, data guard requires archivelog mode.

If you care about availability, as you do when considering data guard, you care about your data. In NOarchivelog mode - all we can say is "you will LOSE YOUR DATA someday", period. It would not even begin to make sense.

Have you checked out the documentation on data guard? It covers lots of these topics. I'll let it cover the difference between logical and physical - that is a chapter.

data guard on a set of tables - with logical standby yes.

the way the databases communicate - very very different than using dblinks, we ship redo and apply redo or convert redo into sql and apply it. no database to database dblinks.

You cannot compare a trigger doing syncronous replication (which is not a failover solution) to a failover solution. You would not even begin to consider a trigger with syncronous replication for data guard.

Dave, March 29, 2006 - 5:30 pm UTC

Can we use dataguard on a non-archivelog database?
>>What is the difference between physical and logical standby database?

no - think about it


>> Can we use dataguard only on a set of tables?

yes, with a logical standby


>>The way the two databases (primary and standby) communicates
and the way the db link communicates, is both similar?

using sqlnet (either via lgwr or arch)

>> If we have a trigger to capture all DML activities and apply the same to another
database
using a db link (I know this is not a good idea), which performs better (in
speed wise)?


wow, sounds slow - why reinvent the wheel?

My answer to: Separate processes for local and remote archiving

Andras Gabor, May 18, 2006 - 6:36 am UTC

I have a useful follow up for this rather old question:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4111318776437#25919893449049

I would recommend the guy the metalink page:
Note:260040.1
http://metalink.oracle.com/metalink/plsql/f?p=130:14:12409093854223477474::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,260040.1,1,1,1,helvetica <code>

It was the solution for me to the same problem. (And I guess for many DBAs out there...)

use REOPEN

A reader, May 22, 2006 - 10:14 am UTC

Dear Tom.

If there is connection failed between primary and standby host, and if I have specified reopen = 300, it means that oracle will retry to connect to standby hos after 300 secs and transfer archivelogs which can not be copied before. Or something else must be done for this situation?

Thanks in advance

Testing Data Guard

Olemma, May 30, 2006 - 1:17 pm UTC

Hi Tom,
Thanks for all your contributions. You make DBA's lifes easier :)

Question. I just finished setting up a primary and physical standby DB configuration. Do you have any testing procedures (test escenarios) that I can use to practice and make sure my configuraion is working properly?

Many Thanks!!!

Tom Kyte
May 30, 2006 - 1:56 pm UTC

switch over and run your apps, switch back and run your apps.

reverse the role of each machine (in your test environment first of course)

How to delete applied archivelog on standby db automatically

Sean, July 06, 2006 - 10:47 am UTC

Hi Tom,

I have got a script from Note 260874.1 on metalink to delete applied archivelog of standby db, but the script tries to delete the archive which have been deleted. Do you have better ways to do it? Oracle version: 9207. Thanks for your help. -- Sean

Here is the script:

alter database recover managed standby database cancel;
alter database open read only;
spool arch_remove.sh
select 'rm '||al.name from v\$archived_log al, v\$log_history lh
where al.sequence# = lh.sequence#
and al.applied='YES' and lh.sequence# is not null;
spool off
alter database recover managed standby database disconnect from session;
exit
EOF
chmod 700 arch_remove.sh
arch_remove.sh



Tom Kyte
July 08, 2006 - 10:14 am UTC

is it a problem to re-remove a file?

sure, you could make this more complex than it is, but why bother? seems harmless enough as it is. (you could use a shell "if" statement to say if the file exists then....)

A reader, August 14, 2006 - 12:53 pm UTC

Tom,

I understand you don't recommend auto fail over but would like to know you feedback on auto switchover especially fast start failover of 10G R2.

Thanks.

Tom Kyte
August 14, 2006 - 12:56 pm UTC

I have reservations - failing over is something to be not taken lightly.

I sort of feel that if your system is so important that automatic failover is a desirable feature, you wouldn't need automatic failover since you would have a team dedicated to it in the first place.

A reader, August 14, 2006 - 1:17 pm UTC

Thanks Tom for your advice and thats what we recommend as well no automatic switchover/failover.

A reader, August 17, 2006 - 11:51 am UTC

Tom,

I was having discussion with my team and some of them favor autofailover can you please provide some points as to why you dont advocate autofailover so i can forward it to them.

Thanks.

Tom Kyte
August 17, 2006 - 12:49 pm UTC

let's do this the other way.

why do they believe automatic failover is a good positive thing?

(I've seen automatic failovers - not dataguard - whereby just because the machine became loaded, sluggish to respond, the automatic stuff said "holy cow, we are down, failover NOW". And what a mess that is - since you didn't really want to failover (guess what happened on the failover machine after failing over because the larger production machine was overloaded?)

get their ideas and let's address them.

dataguard - physical standby database - LGWR SYNC AFFIRM

parag jayant patankar, September 04, 2006 - 4:28 am UTC

Hi Tom,

I am trying to setup physical standby database with LGWR, SYNC and AFFIRM option on 2 Win NT boxes using 9.2 db using MAXIMUM AVAILABILITY database mode. Details are below

Production DB initfile :
------------------------
*.aq_tm_processes=1
*.background_dump_dest='E:\oracle\admin\prod\bdump'
*.compatible='9.2.0.0.0'
*.control_files='E:\oracle\oradata\prod\CONTROL01.CTL','E:\oracle\oradata\prod\CONTROL02.CTL','E:\oracle\oradata\prod\CONTROL03.CTL'
*.core_dump_dest='E:\oracle\admin\prod\cdump'
*.db_block_size=4096
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='prod'
*.fast_start_mttr_target=300
*.hash_join_enabled=FALSE
*.instance_name='prod'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='location=E:\oracle\ora92\admin\prod\arch MANDATORY'
*.log_archive_dest_2='SERVICE=stndby LGWR SYNC AFFIRM'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format=%t_%s.arc
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.query_rewrite_enabled='FALSE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\admin\prod\udump'


Production DB Alert file:
-------------------------
Mon Sep 04 12:34:07 2006
LGWR: Completed archiving log 1 thread 1 sequence 76
Creating archive destination LOG_ARCHIVE_DEST_2: 'stndby'
LGWR: Standby redo logfile selected to archive thread 1 sequence 77
LGWR: Beginning to archive log 3 thread 1 sequence 77
Thread 1 advanced to log sequence 77
Current log# 3 seq# 77 mem# 0: E:\ORACLE\ORADATA\PROD\REDO03.LOG
Mon Sep 04 12:34:09 2006
ARC1: Evaluating archive log 1 thread 1 sequence 76
ARC1: Archive destination LOG_ARCHIVE_DEST_2: Previously completed
ARC1: Beginning to archive log 1 thread 1 sequence 76
Creating archive destination LOG_ARCHIVE_DEST_1: 'E:\ORACLE\ORA92\ADMIN\PROD\ARCH\1_76.ARC'
ARC1: Completed archiving log 1 thread 1 sequence 76

Standby initfile
----------------
*.aq_tm_processes=1
*.background_dump_dest='c:\stndby\bdump'
*.compatible='9.2.0.0.0'
*.control_files='c:\stndby\ctl1\CONTROLFILE04.CTL','c:\stndby\ctl2\CONTROLFILE05.CTL',
*.core_dump_dest='c:\stdndby\cdump'
*.db_block_size=4096
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_file_name_convert=('e:\oracle\oradata\prod', 'c:\stndby')
*.db_name='prod'
*.fast_start_mttr_target=300
*.hash_join_enabled=FALSE
*.instance_name='stndby'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
#*.log_archive_dest_1='location=c:\stndby\switch\arch'
*.log_archive_dest_1='location=e:\stndbyarch'
*.log_archive_dest_state_1='enable'
*.log_archive_format='%%ORACLE_SID%%T%TS%S.arc'
*.log_archive_start=TRUE
*.log_file_name_convert=('e:\oracle\oradata\prod', 'c:\standby','e:\oracle\oradata\prod', 'c:\standby','e:\oracle\oradata\prod', 'c:\standby')
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_archive_enable=true
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.standby_archive_dest='e:\arch'
*.standby_file_management=auto
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='c:\stndby\udump'
*.fal_server=prod
*.fal_client=stndby

Standby alert file :
--------------------
Mon Sep 04 11:40:18 2006
ARC0: Evaluating archive log 4 thread 1 sequence 75
ARC0: Beginning to archive log 4 thread 1 sequence 75
Creating archive destination LOG_ARCHIVE_DEST_1: 'E:\STNDBYARCH\STNDBYT001S00075.ARC'
Mon Sep 04 11:40:19 2006
RFS: Successfully opened standby logfile 5: 'E:\STNDBYLOGS\STDNBYLOG02.LOG'
Mon Sep 04 11:40:36 2006
ARC0: Completed archiving log 4 thread 1 sequence 75

arc1_511 file on standby
-------------------------
*** 2006-08-30 15:20:21.000
Archiving standby database
Selected standby logfile
ORA-16014: log 4 sequence# 70 not archived, no available destinations
ORA-00312: online log 4 thread 1: 'E:\STNDBYLOGS\STDNBYLOG01.LOG'
*** 2006-08-31 21:06:15.000
Archiving standby database
Selected standby logfile
- Created archivelog as 'E:\STNDBYARCH\STNDBYT001S00072.ARC'
Archiving standby database
*** 2006-09-03 08:33:22.000
Archiving standby database
Selected standby logfile
- Created archivelog as 'E:\STNDBYARCH\STNDBYT001S00074.ARC'
Archiving standby database

Questions regarding this

1/ Pl tell me where I have gone wrong and correct ora-16014 this error ?

2/ If I do redo shipping by LGWR are changes on standby will apply immediately when I open standby database as read only or it will ship only logs ?

thanks & regards
PJP


Tom Kyte
September 04, 2006 - 9:06 am UTC

please utilize support for a setup/configuration sort of issu. this just isn't the right place.


dataguard one more query

Parag Jayant Patankar, September 07, 2006 - 3:13 am UTC

Hi Tom,

Thanks for your information. After again configuring standby database for maximum protection alerts and trace files showing me no error.

Now I am testing failover scenarios.

On Production DB ( Maximize protection with LGWR SYNC AFFIRM ) I have done changes in few tables owned by scott and committed but not done switch log file.

On Standby database ( with standby redo logs ), when I do

1/ recover managed standby database cancel;
2/ alter database open read only

then with scott/tiger I can not see changes ? Will you pl tell me what step I am missing.

thanks & regards
pjp


Time taken in applying the redologs in standby database

vijay, September 20, 2006 - 6:31 am UTC

Hi Tom,

How to measure the time taken in applying the changes in standby database (applying the changes which were done in primay database) . Assume there is one 1 primary database and 1 standby database and i have created a physical standby database.
is there a way i can get this information from any of the v$ views.
please let me know.

Vijay

is there anyway to measure this

Ajeet, September 26, 2006 - 3:54 am UTC

Hi Tom,

I have the similar requirement , i want to measure the time taken in applying the archive logs to standby database.
I saw in one of your answers that we can get it from the alert log - then use external table. but when i try to search for the lines you have suggested in my alert log

like '___ ___ __ __:__:__ 20__'
like '%: Beginning to archive %'
like '%: Completed archiving %'

i did not see any such lines.
do i need to set any parameter for this.

i have also opened a SR at metalink for this but could not get a response so far.

can you please describe a little bit.

regards


Tom Kyte
September 26, 2006 - 3:23 pm UTC

are you in archivelog mode? they just sort of happen


version?
operating system?

they are always useful bits of data...

Yes, Db is in Archivelog mode

Ajeet, September 27, 2006 - 1:07 am UTC

Hi Tom,

Yes my db is in Archive log mode.
Database version is 10.2.0.2.
Operation System is

HP-UX hpport4 B.11.23 U ia64 3887162164 .

Regards
Ajeet

Tom Kyte
September 27, 2006 - 5:00 am UTC

then it should be there as far as I know (I don't have an hpux box around). Maybe other hpux users can report what they see - and you can file an itar with metalink.

MAX PERF vs MAX AVAIL

A reader, October 08, 2006 - 2:36 am UTC

I've setup the log_archive_dest_2='SERVICE=rem LGWR SYNC AFFIRM'.

This is running in default "maximum performance" mode. My question is with above setting, what is the difference between running it in max performance mode vs running it in max availability mode.

Tom Kyte
October 08, 2006 - 7:38 am UTC

that'll have lgwr wait for the redo confirmation from the remote standby site.



MAX PERF vs MAX AVAIL

A reader, October 08, 2006 - 11:05 pm UTC

"that'll have lgwr wait for the redo confirmation from the remote standby site."

Does it mean that in MAX PERFORMANCE mode, lgwr will NOT wait for the redo cinfirmation from standby EVEN THOUGH I've setup LGWR SYNC AFFIRM.

Tom Kyte
October 09, 2006 - 8:24 am UTC

that parameter says "lgwr, do this and wait for the affirmation"

for maximum performance
</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#i1178539 <code>

...
The maximum performance mode enables you to either set the LGWR and ASYNC attributes, or set the ARCH attribute on the LOG_ARCHIVE_DEST_n parameter for the standby database destination

....

FAL meaning and usage. Can you explain us ?

Star Nirav, October 10, 2006 - 5:08 pm UTC

Hi TK,

Feeling Nice to contact you again. In the above questions and answers, nobody has configured FAL server and client, even you have also not recommended anywhere.

Why I am saying this ?

lets create one test scenario.

I am having 2 DB (92060) in RHE AS4, one is pdc and another one is DR.

Hostname for PDC is BANGLORE AND DR is AHMEDABAD. I am shipping my archives from BLR machine to AHM machine. Have configured lgwr, sync, register. Now want to ask you what would be my FAL_SERVER and FAL_CLIENT in BLR machine and do I need to configure same way in AHM machine ?

2) What if I keep remote_listener=false in PDC site ?
3) What would be standby_file_management parameter value and benefit ?
4) If I create tablespace (Permenant), (temporary) or Undo, will it shipped to standby also...?
5) If I do some changes in any parameter in PDC site, will it be applied in DR site also ?
6) If my DR site is in read-only mode, will it apply redolog automatically or i need to give command recover managed standby database
7) What is the difference between alter database recover standby database cancel/finish/disconnect ?

Hope your answer will give enough idea about the DR...

Pls. bear me for any silly questions asked... As I am new and very curious about the DR...

Regards,
Star Nirav

Any help on this please...

Satheesh Babu.S, October 11, 2006 - 1:30 pm UTC

Tom,
Is there any relation between primary redo log switch and standby redolog switch? I mean if i switch the redolog file for 5 times in primary, will there be five redo log files generated in standby. I doubt it.
If i am in performance mode and don't use standby redolog files, that case my changes will get applied to datafiles of standby directly from shipped archive log files. Assume 5 archived log files is getting applied to standby, since it is getting applied to datafiles directly, not much redo will be generated. Thus number of redolog file generated will be less than 5.
If i am using standby redolog files and switch 5 times in primary *might* generated 5 archive logs in standby. But again i don't think it will generate 5 redo log files.
I am not able to get these information any where, so came to you as last hope. Let me know if i need to ask this as new question.


Tom Kyte
October 11, 2006 - 4:04 pm UTC

why do you doubt it?



Satheesh Babu.S, October 11, 2006 - 4:19 pm UTC

I doubt it because archive log shipped from primary will get directly into datafiles of standby. So not much redo will get generated for switch to happen in standby.
Thus 5 switchs in primary means there may or maynot be any switch in standby. Am i right?

Regards,
Satheesh Babu.S

Tom Kyte
October 11, 2006 - 8:15 pm UTC

standby is not generally using archives first of all, lgwr is sending the information, to be recorded in the standby redo logs which will get applied as they fill up.

part of "switching log file" is writing to log file, we don't really about if there "is not much to write" we are obliged to write it.



Waiting for the reply... --- Gentle Reminder ---

Star Nirav, October 14, 2006 - 8:25 pm UTC

Hi TK,

Feeling Nice to contact you again. In the above questions and answers, nobody
has configured FAL server and client, even you have also not recommended
anywhere.

Why I am saying this ?

lets create one test scenario.

I am having 2 DB (92060) in RHE AS4, one is pdc and another one is DR.

Hostname for PDC is BANGLORE AND DR is AHMEDABAD. I am shipping my archives from
BLR machine to AHM machine. Have configured lgwr, sync, register. Now want to
ask you what would be my FAL_SERVER and FAL_CLIENT in BLR machine and do I need
to configure same way in AHM machine ?

2) What if I keep remote_listener=false in PDC site ?
3) What would be standby_file_management parameter value and benefit ?
4) If I create tablespace (Permenant), (temporary) or Undo, will it shipped to
standby also...?
5) If I do some changes in any parameter in PDC site, will it be applied in DR
site also ?
6) If my DR site is in read-only mode, will it apply redolog automatically or i
need to give command recover managed standby database
7) What is the difference between alter database recover standby database
cancel/finish/disconnect ?
8) What would be mine fal_server for Blr machine (Primary) ?

Hope your answers will give enough idea about the DR to all the people...


Regards,
Star Nirav

Tom Kyte
October 15, 2006 - 4:01 am UTC

"gentle reminder", how nice of you?

Have you read:

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#i1268316 <code>

It actually does seem to cover the "who, what, why, when and how" of this.

it is for 10g sir...

Star nirav, October 23, 2006 - 4:49 pm UTC

hi Tom,

can you share link/document for 9207 ? mine email address is nirav.khanderia@gmail.com


<prev. msg>
Hi TK,

Feeling Nice to contact you again. In the above questions and answers, nobody
has configured FAL server and client, even you have also not recommended
anywhere.

Why I am saying this ?

lets create one test scenario.

I am having 2 DB (92060) in RHE AS4, one is pdc and another one is DR.

Hostname for PDC is BANGLORE AND DR is AHMEDABAD. I am shipping my archives from

BLR machine to AHM machine. Have configured lgwr, sync, register. Now want to
ask you what would be my FAL_SERVER and FAL_CLIENT in BLR machine and do I need
to configure same way in AHM machine ?

2) What if I keep remote_listener=false in PDC site ?
3) What would be standby_file_management parameter value and benefit ?
4) If I create tablespace (Permenant), (temporary) or Undo, will it shipped to
standby also...?
5) If I do some changes in any parameter in PDC site, will it be applied in DR
site also ?
6) If my DR site is in read-only mode, will it apply redolog automatically or i
need to give command recover managed standby database
7) What is the difference between alter database recover standby database
cancel/finish/disconnect ?
8) What would be mine fal_server for Blr machine (Primary) ?

Hope your answers will give enough idea about the DR to all the people...


Regards,
Star Nirav


Followup:
"gentle reminder", how nice of you?

Have you read:


</code> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#i1268316 <code>

It actually does seem to cover the "who, what, why, when and how" of this.

</prev. msg>


Pls. respond.... gentle reminder 1

Star Nirav, October 26, 2006 - 4:26 pm UTC

it is for 10g sir... October 23, 2006
Reviewer: Star nirav from india

hi Tom,

can you share link/document for 9207 ? mine email address is
nirav.khanderia@gmail.com


<prev. msg>
Hi TK,

Feeling Nice to contact you again. In the above questions and answers, nobody
has configured FAL server and client, even you have also not recommended
anywhere.

Why I am saying this ?

lets create one test scenario.

I am having 2 DB (92060) in RHE AS4, one is pdc and another one is DR.

Hostname for PDC is BANGLORE AND DR is AHMEDABAD. I am shipping my archives from


BLR machine to AHM machine. Have configured lgwr, sync, register. Now want to
ask you what would be my FAL_SERVER and FAL_CLIENT in BLR machine and do I need
to configure same way in AHM machine ?

2) What if I keep remote_listener=false in PDC site ?
3) What would be standby_file_management parameter value and benefit ?
4) If I create tablespace (Permenant), (temporary) or Undo, will it shipped to
standby also...?
5) If I do some changes in any parameter in PDC site, will it be applied in DR
site also ?
6) If my DR site is in read-only mode, will it apply redolog automatically or i
need to give command recover managed standby database
7) What is the difference between alter database recover standby database
cancel/finish/disconnect ?
8) What would be mine fal_server for Blr machine (Primary) ?

Hope your answers will give enough idea about the DR to all the people...


Regards,
Star Nirav


Followup:
"gentle reminder", how nice of you?

Have you read:



</code> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#i1268316 <code>

It actually does seem to cover the "who, what, why, when and how" of this.

</prev. msg>
----------------

The site you have mentioned is for 10g, but I want to have information for 9i. And I asked you questions which can be consider as FAQ.

Many of DBAs dont know what is FAL and how it should be configured...?

Waiting sir...

Regards,
Star Nirav

Tom Kyte
October 27, 2006 - 7:23 am UTC

please utilize support for installation/configuration issues.

A reader, November 15, 2006 - 12:43 am UTC

Tom,

Lets say on 10.2.0.1 if dataguard is configured as Max Availability (haven't specified real time apply) with Standby redo logs and delay of 1 hour for log apply on the standby, will it be ignored or it will be applied after one hour.

Thanks.

Tom Kyte
November 15, 2006 - 7:04 am UTC

will what be ignored.

A reader, November 15, 2006 - 12:02 pm UTC

Will the logs be applied immediately or after the specified delay.

Tom Kyte
November 16, 2006 - 3:56 am UTC

if you say to delay, it will delay.

the redo is shipped immediately, but you can delay the application of it.

Alexander the ok, December 07, 2006 - 4:45 pm UTC

Tom,

Do you know the steps to be taken to remove a data guard configuration? The documentation just talks about removing some broker config, but states it does not remove the underlying data guard setup. Thanks.

MAX PERF with SYNC

A reader, December 12, 2006 - 10:21 am UTC

Followup:

that parameter says "lgwr, do this and wait for the affirmation"

for maximum performance

</code> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#i1178539 <code>

...
The maximum performance mode enables you to either set the LGWR and ASYNC
attributes, or set the ARCH attribute on the LOG_ARCHIVE_DEST_n parameter for
the standby database destination

....

----------------------------------------------
Actually we've our database DG setup running with LGWR=SYNC AFFIRM settings and under maximum performance level.

With whatever testing I could thinf of, I've done it for several days. This setup has behaved the same way had it been setup with LGWR=SYNC AFFIRM under max availability level.

My question is: Can I still have my dg running with LGWR=SYNC AFFIRM settings and still run under maximum performance. The reason is that this has adavantages - bcoz it does not need any outage. Also, if we need to do some bulk insert/update operations, we can temporarily set LGWR=ASYNC WITHOUT OUTAGE and after the bulk operation is done, we can set LGWR=SYNC again with NO OUTAGE.

My manager and the group is asking me why woudn't you setup the dataguard in this way which provides many adanavtegs rather than setting it under max availability mode that means OUTAGE.


Changing SYS password in dataguard

Luigi Sandon, December 18, 2006 - 12:30 pm UTC

What's the correct way to change the SYS password in a dataguard configuration? When we tried simply issuing a ALTER USER command on the primary DB (10.2.0.2 on Windows) it lead to some strange behaviours. A switchover could not be completed (via EM).
When I reset the old password, removed the dg configuration, changed the password again and tried to make the dg conf again (always via EM) I got an empty error message (or better, a single square character...).
Should I use orapwd on the standby database? Should I copy the password file manually?

Tom Kyte
December 18, 2006 - 2:55 pm UTC

you should not be able to log in as sys - not sure what "could not be compleleted" means here.

sys doesn't use the owapwd file - do you really mean "sysdba"

Changing SYS password in a dataguard configuration

Luigi D. Sandon, December 19, 2006 - 5:19 am UTC

We did the following:
1) Created the primary DB
2) Configured a physical standby on another machine using EM
3) Performed a switchover (and back) via EM, OK
4) Issued ALTER USER SYS IDENTIFIED BY...
5) Tried to perform another switchover (via EM) -> Error invalid user/password
6) Issued ALTER USER SYS to restore the old password, switchover ok
7) Removed the dataguard configuration
8) Issued ALTER USER SYS IDENTIFIED BY...
9) Tried to configure dataguard again -> Error, no meaningful error message, just a square character
10) Issued ALTER USER SYS to restore the old password, dataguard configuration could be created.

I read all the documentation about dataguard - found nothing about the proper way to change the password. It's the SYSDBA password, ok, should I change it in a different way? Thank you.

Tom Kyte
December 19, 2006 - 8:36 am UTC

the sysdba password (stored not in the database) must be the same in all locations

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#sthref522 <code>

orapwd

Going back to an earlier question

Tom Boyer, February 07, 2007 - 12:29 am UTC

Going back to an earlier point in this thread can I confirm my understanding and ask a follow on:

In Maximum Protection mode the redo data is written synchronously to both the primary and one or more standby sites. This ensures that all standby locations have all redo logs (including the one currently being written by the production database). If at least one DB isn't available then primary halts.

In Maximum Availability mode the redo data is written sychrounously as above but in this case if the standby db is not up the primary can carry on functioning.

In the default mode redo logs are shipped asynchronously as log switch occurs (no synch writing by LGWR/LNS)

Assuming this summary is correct then: In the first two modes when switch-over occurs the standby system will apply the currently open redo log (plus obviously any older outstanding logs) making sure that the standby can recover to the same point as the when the production db died.

In the third mode there is no guarantee that the standby db even got the logs and so recovery is simply up to the last log received. If this is the case does the data guard management tool look at the online and archived redo logs on the primary site machine (assuming it can still see it) and ship the missing ones over??


Tom Kyte
February 07, 2007 - 1:11 pm UTC

what is "default mode" - that is the old pre-9i standby mode, not really used very often now.

do you really mean switch over or fail over. In switch over, the two systems are always brought to a synchronized point and then we switch roles - standby becomes production and vice versa - no data is lost.

in a failover IF you can get to the online redo logs from the primary (failed) site, you can apply them - else if you are using async transfer of redo, you stand a chance of losing the last couple of committed transactions as they might not have been propagated to the standby yet.

data migration

A reader, February 07, 2007 - 3:54 pm UTC

I have a 9i database that has data on raw file systems. This is a single node database. We are migrating to 10g 2 node RAC with ASM.

The size of the database is 500 GB.

What is the best option of migrating to the new box considering data integrity and speed as top priority? I personally feel export and import.

I was asked whether we can have data guard setup between the 9i and one of the node on the 10g. Is there a possibility?


Tom Kyte
February 07, 2007 - 7:09 pm UTC

just restore? why export and import???

did not understand,

A reader, February 08, 2007 - 4:36 pm UTC

When you mean "just restore", you mean using RMAN (hot backup)?

On the new 10g box with ASM storage, can we restore directly onto the ASM or do we need any kind of staging area and later dump the files from the staging area to the ASM? If later is used, can RMAN dump the datafiles to the ASM?

Thanks,

Tom Kyte
February 08, 2007 - 9:13 pm UTC

just restore, yes. ASM is just "a file system"

thanks,

A reader, February 09, 2007 - 11:08 am UTC

We are planning to perform this on a production database to migrate to a new box next week.

If you can elaborate the steps of doing this restore process from 9i raw file system to 10g ASM, I appreciate that.

Thanks,

Tom Kyte
February 12, 2007 - 8:38 am UTC

ASM and Standby

Peter Verhoeven, February 11, 2007 - 7:04 pm UTC

If I'm upgrading from 9i to 10gR2, and at the same time go with ASM, what implications does this have for the physical standby? i.e. should the Standby also be using ASM?

Note: our Standby is currently managed manually, but I will probably go with Data Guard as part of the upgrade.

Note 2: I'm currrently doing weekly full cold backups off the Standby, plus weekly full exports off production. Both are done via "manual" scripts. I will probably go with RMAN for this as part of the upgrade.

Thanks
Tom Kyte
February 12, 2007 - 10:32 am UTC

I would say "homogeneous is definitely best for failover"

why? because you want the failover to be the same as production - so when you failover and are running around like a chicken with its head cut off - something seems familar, mistakes are not made because the environment is entirely different.

duplicating database,

A reader, February 12, 2007 - 11:17 am UTC

thanks for linking me to the document about duplicating the database to ASM storage.

What I understand is the "auxillary" database is my 10g database where the stuff are going to be migrated.

The RMAN run command have to be executed on my 9i database.

The 9i database can be UP and running during the execution of RMAN command.

After RMAN copies datafiles to DISKGROUP, I can start up the 10g database. After I start up I should not have any kind of links to 9i database and the data should be in sync between them.

Could you please clarify the above?

Thanks,

Tom Kyte
February 12, 2007 - 11:44 am UTC

well, you might not want the 9i database to be up and running at some point if you want to do a "cutover". You want a period of time when it is idle to do a cutover.

Dataguard "Creting a physical standby db"

Sanji, March 10, 2007 - 8:07 am UTC

Tom,
We are in the process of migrating our production database from EMC diskset to IBM diskset.
The current DR strategy is based on SRDF technology of EMC.
The environment is Oracle 9i R2, HP-UX 11i.
There are 2 IBM Disksets.
1 would host the migrated production db and the other would be shipped to the DR site.
The Database size is 600 Gb.

I am trying to configure Dataguard between the 2 IBM disksets as

1> Restore EMC version of database on IBM Diskset D1 through RMAN during cutover.
2> Replace D1 with EMC on the production server.
3> Establish Dataguard between D1 and IBM Diskset D2. D2 is to be shipped to the DR site and the transit time would be around 4 days.

The confusion is in step 3.

1> If the dataguard is configured between D1 and D2 and D2 is then disconnected for being shipped to the DR site, then how do we maintain this scenario ?

2> Would there be an impact on the production database since during these 4 days of D2 transit, the log transfer service would need to be deferred and how do we defer the log transport service .

Regards
Sanji

Tom Kyte
March 12, 2007 - 5:53 pm UTC

1) it is the same as if you "did not have network access for a while", ARCH archives the redo log files and when the systems are ready to be synchronized, the archives will be shipped.

you could do this faster than 4 days of course, you could just use your backups as well.

2) you would need to have the archives available on day 4.

Online log and Standby log files

Sanji, March 13, 2007 - 5:19 pm UTC

Tom,
Oracle 9i rel 2 on HP-UX 11i.
I am testing dataguard between 2 servers. This eventually would be the procedure for near immediate migration project.
The mode would be maximum performance and LGWR would be ASYNChronously transferring data to the standby server in the Dataguard.

I created the standby database through RMAN. This created redo log files on the standby server.

Since we are using LGWR to transfer redo data over the network (to be received by RFS and applied to standby redo log files, which would then be archived and applied to the standby database in managed recovery mode), the question is,
"Why do we need the redo log files on the standby site ?" : Since we are creating standby redo log files which ofcourse are different from the redo log files created on the standby database through RMAN.

The RFS is going to write redo data to the standby redo log files. Then why do we require the other set of redo log files ?

For instance

SQL >select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
4 ONLINE /pfin9/oradata/FRED/redo/redo_4.log
4 ONLINE /pfin6/oradata/FRED/redo/redo_3b.log
3 ONLINE /pfin9/oradata/FRED/redo/redo_3.log
3 ONLINE /pfin6/oradata/FRED/redo/redo_3b.log
2 ONLINE /pfin9/oradata/FRED/redo/redo_2.log
2 ONLINE /pfin6/oradata/FRED/redo/redo_2b.log
1 ONLINE /pfin9/oradata/FRED/redo/redo_1.log
1 ONLINE /pfin6/oradata/FRED/redo/redo_1b.log
5 STANDBY /pfin9/oradata/FRED/redo/redo_5.log
5 STANDBY /pfin9/oradata/FRED/redo/redo_5b.log
6 STANDBY /pfin9/oradata/FRED/redo/redo_6.log
6 STANDBY /pfin9/oradata/FRED/redo/redo_6b.log

SQL >select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 31 1073741824 2 YES CLEARING 8656566359 13-MAR-07
2 1 32 1073741824 2 YES CLEARING 8656622326 13-MAR-07
3 1 33 1073741824 2 YES CLEARING_CURRENT 8656625273 13-MAR-07
4 1 30 1073741824 2 YES CLEARING 8656495549 13-MAR-07

Elapsed: 00:00:00.00
SQL >select * from v$standby_log;

GROUP# THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
5 0 0 1073741824 2048 YES UNASSIGNED 0 0
6 1 33 1073741824 0 YES ACTIVE 8656625273 13-MAR-07 0

Elapsed: 00:00:00.00
SQL >

Feedback appreciated as usual.

Thanks
Sanji
Tom Kyte
March 13, 2007 - 8:56 pm UTC

... "Why do we need the redo log files on the standby site ?" ...

you do not, they are an option.

see
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/standby.htm#i72373
for when they are NOT an option and why you might want them when they are an option.

Sorry about the wrong information

Sanji, March 13, 2007 - 8:33 pm UTC

Tom,
I apologize for the incorrect information provided in my previous post.
I checked the directories for logfiles and redo log files actually did not get created by RMAN. The only contents of the log directories were the standby log files which were created later.

Thanks
Sanji

switchover/failover,

A reader, March 28, 2007 - 4:22 pm UTC

When will DBA's do unplanned switchover? What are the different issues the DBAs see after performing the switchover?

If the hardware of the primary database is not accessible or media failure than we do failover to standby database and the link between the current primary and the standby will be cut off until the current primary database is OPENED. Is that correct?

Thanks,


Tom Kyte
March 30, 2007 - 11:38 am UTC

you never do an "unplanned switchover"

You would do an unplanned failover whenever you - well - needed to (because production is gone and won't be around for a while)

Media failure might not cause a decision to failover, we might just do recovery - it all depends.

And when you failover, you failover - you would rebuild production afterwards from the standby and then switchover. You only failover after a catastrophic loss of production.

How to get archive log names on standby database

A reader, March 31, 2007 - 5:51 am UTC

Hi Thomas,

We are using 10gR2 on windows.
we have two nodes in a data guard configuration with maximum protection mode. Standby database is physical.

On a database when we query the NAME column on V$ARCHIVED_LOG view we get the name of the archivelogs.

Similarly i want to get the NAME on the standby database.
When i query this i get details like the service entry instead of the actual log file names.

Reason, i want to write a piece of code to delete the archive logs which are already applied on the standby.
Therefore i want the names, other way is to parse the string from the folder where actual archive logs are there and find match with the SEQUENCE# value manually.

Is there any automatic way to do that, i.e delete the archive logs after they are applied.

Thanks.

follow up on duplicate database,

A reader, April 04, 2007 - 4:30 pm UTC

The link you have given me earlier is for 10gR2 database.
http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb005.htm#sthref1298

Does this concept (copying datafile from 9i database to ASM storage on 10g database using RMAN) works in 10gR1 too?

Just to be clear again:

1.My old database is 9iR2 2 node RAC.
2.My new database is 10gR2 2 node RAC. ASM maintains storage.
3. Start up 10gR2 in mount stage.
4. Migrate 9i database to 10g using RMAN (for example:
RUN {
SET NEWNAME FOR DATAFILE 1 TO "+dgroup1";
SET NEWNAME FOR DATAFILE 2 TO "+dgroup2";
...
DUPLICATE TARGET DATABASE FOR STANDBY;
}
Here target database is 9i and auxillary database is 10g

4. After all the files are copied to auxillary instance, then open the 10g database.

Question: do we have to open with resetlogs option? If so from what point of time we need archive log files of the old database?

What about controlfile on 10g? How does control file on 10g knows the datafiles that were copied from 9i database?

Thanks,




Tom Kyte
April 04, 2007 - 6:06 pm UTC

why standby?

just restore the database, as if restoring to a new host - then you have to upgrade it.

Dataguard and RMAN Stby backup

Sanji, April 04, 2007 - 5:31 pm UTC

I have configure Dataguard in our environment as
(9.2.0.7, HP 11i)

Primary
Secondary 1 (SERVICE ... LGWR ASYNC) --- Different server but located in the same vicinity
Secondary 2 (SERVICE ... ARCH) --- Located 2000 miles from the primary site

I have this doubt.
How do we backup the archives and delete them only after the confirmation that they have been applied on the standby databases.
Does RMAN check whether the particular archive has been applied on the standby databases ?

I surely can do something like

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1' BACKED UP 1 TIMES TO DEVICE TYPE SBT;

but what, in case, we need to backup and delete archives every 2 hours.

Thanks
Sanji

A reader, April 05, 2007 - 12:15 am UTC

Set the RMAN policy for archivelogs as "APPLIED STANDBY"

But not sure if this exists with 9207

Configure Stand by database in Oracle 10g

K P Ratnaker, April 05, 2007 - 7:02 am UTC

hi tom,
How to configure standby database in Oracle 10g Please send me your suggestion.

Ratnaker

Tom Kyte
April 05, 2007 - 11:00 am UTC

follow up,

A reader, April 05, 2007 - 11:06 am UTC

Well, I don't have to use STANDBY. Thanks for pointing that.
You said, just restore and then upgrade it. You mean my new database should be in 9i and then after restoring upgrade to 10gR2?

In this case since 9i does not support ASM, so where I should I restore the data in the new database?
Tom Kyte
April 05, 2007 - 11:37 am UTC

you'll be using 10g rman to restore the backup on a new host.

follow up,

A reader, April 05, 2007 - 11:39 am UTC

Tom,

I am confused now. If I restore from 10g then when will the upgrade process happen?

At high level, can you describe the steps please?

thanks,

Tom Kyte
April 05, 2007 - 1:42 pm UTC

you upgrade databases.

so, you install 10g, you get a 9i database from somewhere, and you upgrade it (the database)

arhivelog names in standby database

A reader, April 05, 2007 - 11:41 am UTC

Hi Thomas,

We are using 10gR2 on windows.
we have two nodes in a data guard configuration with maximum protection mode. Standby database is physical.

On a database when we query the NAME column on V$ARCHIVED_LOG view we get the name of the archivelogs.

Similarly i want to get the NAME on the standby database.
When i query this i get details like the service entry instead of the actual log file names.

Reason, i want to write a piece of code to delete the archive logs which are already applied on the standby.
Therefore i want the names, other way is to parse the string from the folder where actual archive logs are there and find match with the SEQUENCE# value manually.

Is there any automatic way to do that, i.e delete the archive logs after they are applied.

Thanks.

follow up,

A reader, April 05, 2007 - 3:30 pm UTC

Just to confirm the steps from my end:

1.Source database is 9i on server X with raw device storage
2.Target database is 10gR2 on server Y with ASM storage
3.Using RMAN I get 9i database from server X to server Y. Here I assume the database is restored directly on ASM. RMAN is executed from server Y with database on server X being auxillary database.
4.Use STARTUP UPGRADE on server Y after restore is complete.

Question: Don't I require any archive log files to open the database as of NOW?


Please confirm my steps.

thanks,




Tom Kyte
April 05, 2007 - 3:40 pm UTC

answer: of course, if you have logs to apply to the backup.

Managing Archives in Dataguard environment

Sanji, April 05, 2007 - 4:22 pm UTC

Tom,

The Dataguard environment is 9.2.0.4, HP-UX 11i
Primary database on Server 1
Secondary database on Server 2

From Primary init file

log_archive_dest_1='LOCATION=.....
log_archive_dest_2='SERVICE=....LGWR...)

If i backup archived logs from Primary through RMAN like

RMAN> backup archivelog all delete all input;

This doesn't seem to delete archived logs from the standby server. Is this how it's supposed to be.

Does the "delete all input" clause work only on local destinations ?

Thanks
Sanji

relationship between LNS and RMAN

Momen, May 27, 2007 - 4:25 am UTC

Hi Tom,

I have been observing a strange relationship between the LNS and RMAN on couple of production databases. We have Data Guard (physical standby) configured on a database(Release 10.2.0.3.0). RMAN archivelog backup's run every 4 hours to backup the archivelog to tape.

1) "LNS1" background process runs on the Primary database and can be queried from V$SESSION view.
2) Everytime an RMAN backup is run, a new LNS1 process starts with a new session id and the old one disappears.
3) An Undocumented idle wait event "LNS ASYNC end of log" is always seen against the LNS1 session.

Could you please explain us the relationship between the LNS and RMAN and at the same time briefly describe the "LNS ASYNC end of log" wait event.

Thanks a lot.

Following is the RMAN log
=========================

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
RMAN TEST ARCHIVED_LOGS BACKUP Started:'Sun:27-May-2007-07.02.01'

Recovery Manager: Release 10.2.0.3.0 - Production on Sun May 27 07:02:02 2007


Following is the Query against V$SESSION
========================================
select program, status, last_call_et from v$session where sid=680;

PROGRAM STATUS LAST_CALL_ET
------------------------------------------------ -------- ------------
oracle@myserver (LNS1) ACTIVE 14655

SQL> select sysdate from dual;

SYSDATE
--------------------
27-may-2007 11:16:20


Wait Event
==========

select EVENT,SID,P1,P1RAW,P2,P2RAW,P3,P3RAW,SECONDS_IN_WAIT
FROM V$SESSION_WAIT
WHERE EVENT like 'LNS ASYNC%'
EVENT SID
---------------------------------------------------------------- ----------
P1 P1RAW P2 P2RAW P3
---------- ---------------- ---------- ---------------- ----------
P3RAW SECONDS_IN_WAIT
---------------- ---------------
LNS ASYNC end of log 680
0 00 0 00 0
00 0


Tom Kyte
May 27, 2007 - 9:31 am UTC

LNS1 is used by data guard in 10gr2 to transmit redo.

It is normal for it when a system is mostly idle to be waiting for redo - that is that wait.
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#i1267954


LNS is all about redo - not surprising that redo oriented (as opposed to RMAN) operations cause things to happen to it.

Citrus, May 27, 2007 - 10:57 am UTC


Thanks for your quick reply.

I am unable to understand the relationship between RMAN backups and the LNS process. When an implicit logswitch happens or I explicitly switch the logfile, the same LNS session continues but when I take a backup using RMAN why is that Oracle database starts a new LNS session (killing/exiting the old one).


Tom Kyte
May 27, 2007 - 4:33 pm UTC

undocumented and as long as all is working, not really that concerned about it. LNS is all about redo, anything affecting redo might cause it to do stuff.

DG Question

HiteshBajaj, July 13, 2007 - 1:34 pm UTC

Hi Tom,

We are focusing on changing DATA GUARD Protection mode from "MAX PERFOMANCE" to "MAX PROTECTION" for a case to have no loss of redo data in case of catastrophic failure on the primary machine.

Since we are a financial institute, we will be using LGWR SYNC mechanism to ship redo data to the physical standby and on the standby site, we will put the database under the recovery using the current logfile option.

Now we have a test case, where in at any point of time we should have 2 databases in sync with each other to take updates from the Web service.

We were thinking to have the configuration as one primary , one physical standby (redo transoport being LGWR SYNC) and the other physical standby (redo transport being ARCH).

Will this solve our problem? If our primary goes down DG broker will convert the most sync standby to primary database but still we are not at a stage where in we can accept transactions from customers, since the other Standby was still lagging from the primary by the current logfile to which the primary was writing to when the failure happened.

Questions:

1.How fast can the other standby catch up with primary, once we set the transport mode to be LGWR SYNC, rather than earlier set as ARCH which is ASYNC.
2.Do we need to have 3 standby's in place to match up this test case.
3. Using LGWR SYNC in MAX PROTECTION MODE with real time apply, do we need to specify "USING CURRENT LOGFILE" option along with the recovery command or it is by default.

Thanks


Tom Kyte
July 13, 2007 - 1:39 pm UTC

1) as fast as you want, use real time apply.
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/log_apply.htm#i1022881

2) you tell me. you will not have 2 databases in sync with each other to take updates however - that doesn't compute. You have two databases, one receiving transactions - the other applying them for a failover situation.

3) see the above link...

A reader, July 13, 2007 - 6:14 pm UTC

To HiteshBajaj from INDIA,

Think hard before opting for "MAX PROTECTION", you may need to take a look at "MAX AVAILABILITY" as well.

Just a thought.

Rahul.
Tom Kyte
July 13, 2007 - 7:52 pm UTC

financial institutions frequently have a legal requirement to have the transaction done in two geographically distinct places before saying "it happened".

You would not want your $1,000,000 transfer from checking to savings disappear after all :)

Data Guard Questions

hitesh bajaj, July 14, 2007 - 2:33 am UTC

Hi Tom,

I have already gone thru' this documentation but was having these doubts :

1.As a financial institute we just need to go with "MAX PROTECTION" mode and I have no doubts on that but given the legal situation we have to commit the transaction to two different geo locations at any point of time.

What my situation is that I have 3 machines in place for which I need to setup this configuration.

Is that possible? Test case :

Primary box
1st Physical Standby : LGWR SYNC
IInd Physical Standby : ARCH which is by default ASYMC

Now lets suppose the primary went down. DG broker will failover to Ist standby since the redo sync is already there and there is zero data loss.

Now I am in a fix, with this infrastructure able to support any further transactions. Now the IInd standby which was lagging behing by alleast the current log, which start recieving the redo data from the currently failed over primary database.

Will that be fast enough to take transactions because the IInd standby has to apply the last log changes before taking any other updates from the Primary.

Since, the transaction has to wait till the commit is ensured at two locations I think the wait time would be enormoous since we have 512M log files.

Is that infrastructure, supportive enough for financial institutes or more need

1 more machine for another standby configured OR
get the primary machine transmit redo data SYNC with LGWR to both the standby databases, which in fact I am worried about the performace of the primary.

Your suggestions would be appreciated.




Tom Kyte
July 17, 2007 - 10:24 am UTC

IInd is horribly confusing at first. took me a bit to figure out you meant "second"


512mb is pretty average to small - it would not take an enormous amount of time to apply (rather than hypothesize - you should SIMULATE these situations so you can speak from authority rather than gut feel)


You have a legal requirement to be committed in two locations for disaster purposes, not high availability. If you need also high availability - you'll have to take this all up a notch and do it in three locations under normal circumstances so that when one site disappears, you have two that are in lock step already.

real time apply

Samuel, October 13, 2007 - 1:50 pm UTC

I wanted to confim with you the following:

In 9i, real time APPLY is not possible regardless of the protection modes..in other words standby redolog must be archived before it can be applied..Is this correct? Thanks.
Tom Kyte
October 14, 2007 - 2:22 pm UTC

Data Guard

A reader, February 28, 2008 - 11:59 am UTC

Tom,

I want to add a service to both primary and standby dbs (10R1). I created a config with data guard broker and saw that broker added some name to service_name. So I don't want touch this by using alter system and use dbms_services.create_service/start_service - only on the primary site.

I thought that this will be replicated to standby per log switch, but this was not. But it seems it did not. As I checked service_name param on the standby I could not see a service I created. It was not registred by the listener as well (on standby site)
What I did wrong

How to resolve SWITCHOVER LATENT on Physical standby DB

David, February 29, 2008 - 6:10 pm UTC

Hi, Tom,

When I switched the Primary DB to standby and try to switch the physical Standby DB to Primary, I found this on standby:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 
SWITCHOVER_STATUS 
 ----------------- 
SWITCHOVER LATENT
1 row selected

If I still go ahead and try to do the switchover for standby db, I got this:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required

SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01666: controlfile is for a standby database

So my questions are:
1. why and how do I get the SWITCHOVER LATENT status on switch over?
2. How to solve the problem and make the standby DB switch over to Primary DB successfully?
Thanks a lot for your great help.


Tom Kyte
March 01, 2008 - 12:08 pm UTC

insufficient data to say - you don't tell us what you did.

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/role_management.htm#i1033702

did you do everything you were supposed to do.

Primary db recovery

Vinod, March 02, 2008 - 12:28 pm UTC

Hi Tom,

Which would be best method to adpot for sync of primary with standby ,if an incomplete recovery is done in Primary ?

Primary db recovery

A reader, March 02, 2008 - 8:27 pm UTC

Hi Tom,

Thanks for your valuable suggestions ..

Vinod

How to resolve SWITCHOVER LATENT on Physical standby DB

David, March 03, 2008 - 12:23 pm UTC

Ok, Tom, Here is what I did in detail:

I configured a primary DB(Prim1) and a Pyysical Standby DB(Standby1), I follow the instruction from the procedure from doc.180031.1 and oracle procedure from http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/role_management.htm

I checked the log shipping works fine on Standby1 when I did the switch log on Prim1
SQL> select status, error from v$archive_dest where dest_id=2;

 STATUS    ERROR
 --------- --------------------------------------
 VALID

Before I tried switchover, I checked Standby1:
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
------------------
TO PRIMARY

and on Prim1:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS 
 ----------------- 
 TO STANDBY 
 1 row selected 

Then I did the switchove on Prima1 side:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Database altered.

#3Shut down and restart prim1 as new standby (Standby2) with new standby init.ora file.
SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount pfile=C:\oracle\ora92\database\INITtest9i_2dr.ORA
ORACLE instance started.

Total System Global Area  126950220 bytes
Fixed Size                   453452 bytes
Variable Size             100663296 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> alter database mount standby database;

Database altered.

##At this point in the switchover process, both databases are configured as standby databases. 

## now work on the physical standby DB (Standby1)

##when I check the status on Stanby1 again, it should be on SWITCHOVER PENDING status after Prim1 is swichch over, but it gives me this:
SQL>  select switchover_status from v$database;

SWITCHOVER_STATUS
------------------
SWITCHOVER LATENT

##If I still go ahead and try to do the switchover for standby1, I got this:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required

SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01666: controlfile is for a standby database

So my questions are:
1. why and how do I get the SWITCHOVER LATENT status on switch over for standby DB at this stage?
2. How to solve the problem and make the standby DB switch over to Primary DB successfully?

Thanks a lot for your great help.


standby recovery

Vinod, March 03, 2008 - 10:30 pm UTC

Hi David ,

Sorry to interrupt !!You may have  recover standby database using the below mentioned method

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                  2071288 bytes
Variable Size             113247496 bytes
Database Buffers           50331648 bytes
Redo Buffers                6316032 bytes
SQL> alter database mount standby database;

Database altered.

SQL> recover standby database;
ORA-00279: change 1957724 generated at 02/24/2008 19:54:59 needed for thread 1
ORA-00289: suggestion : /u02/oradata/arch/1_289_642105719.dbf
ORA-00280: change 1957724 for thread 1 is in sequence #289


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO

and you could check the failover status from v$database and try for a switchover.

David, March 04, 2008 - 2:14 pm UTC

Vinod,

Thanks for your help. I can recover the physical standby DB to its standby status. But still how to solve the SWITCHOVER LATENT stauts is my question.
Hope Tom can give some great insight on how to get around it. TOM???

DataGuard Switchover/Failover and JDBC Connection Pools

A reader, April 02, 2008 - 12:48 am UTC

Tom,
We are looking to implement DataGuard. Once concern I had was when we do a Switchover or Failover, all of our web servers (and we have some) will attempt to connect to the new primary and attempt to create connection pools. I was told we had had issues with all web servers attempting to establish connections with the database at the same time and that it almost brought the database down. Have you heard of anything like this? Other than stagger the web servers, is there a way to prevent this from happening?
Tom Kyte
April 02, 2008 - 6:30 am UTC

it is called a logon storm. sure if you have 10 connection pools each of 100 connections attempt to simultaneously establish a connection, you'll have "an issue".

So, use much smaller lower bounds on your connection pools - let them grow as they need rather than start really large.

Or use TAF (transparent application failover) to have the connections fail over bit by bit as they are accessed.
http://docs.oracle.com/docs/cd/B19306_01/network.102/b14212/advcfg.htm#sthref1280


DataGuard Switchover/Failover and JDBC Connection Pools

A reader, April 02, 2008 - 9:57 am UTC

Thanks Tom, for your prompt response. Let me go read the documentation and see what I can use.

V$ARCHIVE_DEST_STATUS

Mahalingesh, April 15, 2008 - 4:08 am UTC

I am working on two standby database for prod 9i instance.
Both are physical standby databases. 

SQL> select DEST_NAME,TYPE,DESTINATION, RECOVERY_MODE from  V$ARCHIVE_DEST_STATUS;

DEST_NAME                TYPE           DESTINATION              RECOVER
------------------------ -------------- ------------------------ -------
LOG_ARCHIVE_DEST_1       LOCAL          /archive01/prod      IDLE
LOG_ARCHIVE_DEST_2       LOGICAL        prod_dr                 IDLE
LOG_ARCHIVE_DEST_3       LOGICAL        PROD_STNDBY              MANAGED
LOG_ARCHIVE_DEST_4       LOCAL                                   IDLE
LOG_ARCHIVE_DEST_5       LOCAL                                   IDLE
LOG_ARCHIVE_DEST_6       LOCAL                                   IDLE
LOG_ARCHIVE_DEST_7       LOCAL                                   IDLE
LOG_ARCHIVE_DEST_8       LOCAL                                   IDLE
LOG_ARCHIVE_DEST_9       LOCAL                                   IDLE
LOG_ARCHIVE_DEST_10      LOCAL                                   IDLE

10 rows selected.

SQL>

I am working on 9i physical standby database.
Any idea why I am getting values LOGICAL for column type? 

The possible values are 
CROSS-INSTANCE - An instance of the primary database 
LOCAL - Local to primary instance 
PHYSICAL - Physical standby database 


Tom Kyte
April 16, 2008 - 2:40 pm UTC

the type column is "wrong" in 9i, you have a physical standby - all is ok (this is very harmless). Reference bug 2377407.

A reader, April 19, 2008 - 4:12 pm UTC

Hello Tom,

Could you please look into my question? In primary standby database maximun performance scenario, when the primary database hardware gets destroyed completely while the archived redo log is being sent over the network to standby database, would there be any data loss because the archived log has not completely reached to standby database. Neither that archived log was backuped to some tape or disk in primary site. Assume that the primary site is completely gone due to a earthquake etc. Therefore for mission critical systems, is maximum protection with two phase commit necessary and not maximum availability? Thanks.

A reader, April 19, 2008 - 4:15 pm UTC

I know in maximum availability scenario there is primary and only one standby. How is the data backup at standby site ensured when the primary goes down and also something happens to the network while the archive log in it is being shipped to standby?
Tom Kyte
April 23, 2008 - 4:09 pm UTC

You are responsible for backups, therefore you ensure data backup at standby site.

If the network 'fails' during a log transfer, it'll be transferred later when network becomes available.

Bhaskar, April 23, 2008 - 4:15 am UTC

Hi Tom,

Does corruption in primary site will be replicated to standby site??. What are the options available to avoid disaster to standby.
Tom Kyte
April 28, 2008 - 9:14 am UTC

depends on how you define corruption.

physical corruption (disk does bad stuff) - not likely.

logical corruption (your program does bad stuff) - highly likely, probable in fact, unless you run with a long enough lag time to STOP the copy of data

Bhaskar, May 04, 2008 - 1:28 am UTC

Thanks Tom,

DR site with Standby(logical or physical) database is setup for any disaster.
If corruption is a disaster at DR site also, then what is point in investing
such huge amount for DR sites???.

What is justification for investment,How to over come this??

Thanks & Regards
Bhaskar
Tom Kyte
May 04, 2008 - 10:33 am UTC

I don't know what you mean here.

... If corruption is a disaster at DR site also ....

what do you mean by that

Bhaskar, May 05, 2008 - 12:16 am UTC

Hi Tom,

As you said earlier,IF logical corruption at primary is replicated standy database and standy database will also get corrupted.Lag time will delay our Real time
objective(RTO) commitment

How to over come this diaster??.
Tom Kyte
May 05, 2008 - 2:49 am UTC

Well - first and foremost by using the database to enforce integrity - as logical corruption pretty much is caused by applications attempting to enforce integrity outside the database.

Short of that, you have a lag time, if you catch the grievous error in that time - you can stop it from propagating to failover site.

if you do not catch it then, I really don't know what to say to you - it is not magic in place here - if you choose to enforce integrity outside of the database, you will have to deal with this.


This is not a shortcoming of dataguard or failover (it is however a shortcoming of your application design). dataguard protects you from hardware and software failures (crashes) and with a decent lag time, can help you protect you from yourself (the accidental truncate, the bad application and so on)

Dataguard..

A Reader, November 13, 2008 - 3:27 pm UTC

Hi Tom,

We have the Dataguard setup as follows ( DAtabase - 9.2.0.6):

* Site A production site and Site B Disaster Recovery site employing dataguard.

* We have another site C where database server is of same capacity as site A and site B.

* Due to some business reasons we want use Site B as the Production site and Site C as the Disaster Recovery site for Site B ( production).

I am listing the options available :

1. On the D-day , break the replication from A to B. Open the database in site B using resetlogs and then start using B as the production site.
1.1 Set up the DR site C using Hot Backup taken from Site B ( now Production) and build the Dataguard between B & C.

2. Set up dataguard in C site and let data replication happen to both B & C site from A ( production site). And then on D-day, stop replication to site B & C. Open B site database ( re-incarnation => log_sequence ZERO) and then use it for production.
2.1 Someone has suggested that in this scenario no need to build the data guard in Site C for site B ( now production). *sounds* like hooking up the DR database with new Production server. But i have opinion that it is NOT possible, because site C would be expecting log sequnce number n ( >0) and it will not accept the log sequence (0 from site B after incarnation of it).
2.2 Other option - Suppose we succeed in breaking the replication at the same time to Site B & Site C as mentioned in (2) above from site A. Then open both the databases in Site B & site C. then in such a scenario NO need to use the HOt/Cold backup to build the dataguard in site C. We say it is already there. So shutdown site C database. mount it using standby controlfile from site B and start recovery in site C from Site B.

Questions:
a) Tom how feasible is the option 2.2. My opinion is that if we are able to succeed splitting databases B & C at the same time from A. Then ONLY both database will have same state and then option 2.2 is feasible.?

b) Any other best method to migrate production to site B and use site C as the disaster recovery ?

Kind Regards




Tom Kyte
November 14, 2008 - 4:57 pm UTC

first of all - please do not call data guard replication - that just confuses stuff. Replication is not disaster recovery, it is replication (and a pain the $#@). Data guard is disaster recovery

You can cascade B to C right now, then when you want - just switchover from A to B and then cast off A. Almost no downtime (just the switchover time) no resetlogs, just a graceful switchover.

Dataguard..

A reader, November 18, 2008 - 10:45 pm UTC

Hi Tom,

sorry for mentioning data guard *replication*

further
...You can cascade B to C right now,....

cascade => setting up dataguard? if this is true then

it means we can setup dataguard of dataguard ( B which is dataguard of A needs to be cascaded to C ). can you please point me to any specific doc for such setup.



dataguard...

A reader, November 22, 2008 - 2:30 pm UTC

Hi Tom

Thanks for your time.

Can we cascade physical standby databases in Oracle 9.2.0 as we are running 9.2.0.6 in our box.

You have referred to 10g doc above.

Regards

Data Guard (10.2.0)

lalu, December 16, 2008 - 2:59 am UTC

Hi Tom,

I have a 2 node RAC (Soalris+raw devices+asm) and a single instance dataguard for the primary RAC.
They are on Max. avalability mode with fast start failover enabled.

miboss10-RAC node1(menadb1)
miboss1-RAC node2 (menadb2)
miboss5-standby single instance(menadbs)
The observer is running on menadb1.

For testing purposes, i made a shut abort(using srvctl) to the primary RAC and it was successful from all aspects.
DGMGRL> start observer;
Observer started

17:57:48.93 Saturday, December 16, 2000
Initiating fast-start failover to database "menadbs"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "menadbs"
17:59:02.06 Saturday, December 16, 2000

Then i mounted one of the RAC node(menadb2) and the observer output is:
18:00:12.79 Saturday, December 16, 2000
Initiating reinstatement for database "menadb"...
Reinstating database "menadb", please wait...
Operation requires shutdown of instance "MENADB2" on database "menadb"
Shutting down instance "MENADB2"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "MENADB2" on database "menadb"
Starting instance "MENADB2"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Manually i handled the above scenario.
Then i did a switchover to the RAC instance and i had some message like below:
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "MENADBS" on database "menadbs"
Starting instance "MENADBS"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


Am i missing any thing????
The listener entries are:
RAC node1:
LISTENER_MIBOSS10 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = miboss10-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.232.170.235)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_MIBOSS10 =
(SID_LIST =
(SID_DESC =
(SID_NAME = MENADB1)
(ORACLE_HOME = /IBOSS/oracle/product/db1)
(GLOBAL_DBNAME=MENADB_DGMGRL)
)
)


RAC node2:
LISTENER_MIBOSS1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = miboss1-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.232.170.226)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_MIBOSS1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = MENADB2)
(ORACLE_HOME = /IBOSS/oracle/product/db1)
(GLOBAL_DBNAME=MENADB_DGMGRL)
)
)


Single instance standby:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = miboss5)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = MENADBS)
(ORACLE_HOME = /IBOSS/oracle/product/db)
(GLOBAL_DBNAME=MENADBS_DGMGRL)
)
)


Thanks.
lalu.


Tom Kyte
December 29, 2008 - 11:05 am UTC

you need to use static registration - not dynamic - to remotely (over sqlnet) start up a database.



http://docs.oracle.com/docs/cd/B19306_01/network.102/b14213/listener.htm#sthref795

Dataguard(10.2..0)

lalu, December 16, 2008 - 8:16 am UTC

Hi Tom,

please ignore the above thread.
I stopped and started the listeners(all 3 machines) and looks running good now.

The failover/switcher happenes as expected.
It may that so many unnecessary services were registered to those listeners during my 1 week hit and trial.

Thanks.
lalu.

how to measure log shipping delay?

Jianhui, March 03, 2009 - 10:41 pm UTC

Hi Tom,
We are using 10gR2 physical standby using LGWR/ASYNC with standby redo logs files under max performance mode.

The question is that we want to know how much data loss we are exposed to should there be a disaster in the primary database location, this will help us to evaluate whether such protection mode meets our business requirement for data loss. (log application can be delayed for longer time as long as we have log data in standby location).

Is there any interface provided by Oracle to measure such delay? For example, in the primary, redo log#1 and SCN#n is most recent, how do we find out the most recent SCN# in standby redo logs and measure the difference, we need data loss to be less than a few minutes level, but need a proof instead of Oracle document saying it's shipped by LGWR immediately.

Best regards,

Tom Kyte
March 04, 2009 - 12:59 pm UTC

well, we don't say "but need a proof instead of Oracle
document saying it's shipped by LGWR immediately." - it is async, it is sent with a delay and that is for sure. It is shipped "really soon"

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1080.htm#REFRN30413

Thanks that's exactly what i needed

jianhui, March 04, 2009 - 2:19 pm UTC


Open physical standby read only

A reader, May 04, 2009 - 2:23 pm UTC

I am doing some testing with the following scenario - I have a primary database and a physical standby database. I want to do some patches for eg., CPU on the primary. I have opened my physical standby database read only after cancelling log apply. Before I opened the physical standby, I changed the LOG_ARCHIVE_DEST_STATE_n to DEFER which was configured to ship archive logs to the standby. After my patches were done, I had to startup the primary but I am still not ready for my connections to point to the primary. I thought since I DEFERed the destination and did a scope=spfile, when I restart the database it should still be deferred. But somehow the DEST_STATE was changed to enable and my standby automatically started in managed recovery mode from read only. Is there some other setting that I am missing to disable so that until I am ready the physical standby will be in read only and not switch to managed recovery mode automatically when I startup the primary ?


Tom Kyte
May 04, 2009 - 5:29 pm UTC

need a better blow by blow - can you reproduce this - any 'on startup triggers' or scripts you are overlooking?

Archive lag

Abdul Wahab, May 31, 2009 - 7:45 am UTC

Hello,I am reading 10g data guard concept guide. My major concern is to learn how to manually correct archive lag problems between primary and standby db. To find archive lag the guide says To determine if there are gaps in the archived redo log files on the target standby database, query the V$ARCHIVE_GAP view.

The V$ARCHIVE_GAP view contains the sequence numbers of the archived redo log files that are known to be missing for each thread. The data returned reflects the highest gap only.

For example:

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92

In this example the gap comprises archived redo log files with sequences 90, 91, and 92 for thread 1. If possible, copy all of the identified missing archived redo log files to the target standby database from the primary database and register them. This must be done for each thread.
My question is.. how does stand by database instance know the archive log numbers generating at primary site which are not applied here yet?

Tom Kyte
June 01, 2009 - 7:57 pm UTC

... how does stand by database instance know the archive log numbers generating at primary site which are not applied here yet? ..

have you observed they appear to be sequential?

Logical standby

A reader, June 22, 2009 - 3:36 pm UTC

Tom,
I want to create a logical standby database for reporting purpose. The primary database is 150GB in size but all the schemas are not required for reporting. Out of 35 schemas, I need only 10 schemas for reporting. This is about 40-50GB data.

As I understand, even to create a logical standby database, I will first have to create a physical standby and then convert it to logical standby. So, initially, I will have to copy the entire 150 GB primary database. Is this correct?

Second, after creating the logical standby, can I drop the unused schemas from logical standby tp free up space? I have read in documentation about creating skip handlers but I want to totally drop the unused schemas.

I do not want to use logical standby for failover/role reversal. It is just a way to get the load off the production database. Oracle version 10gr2.

Thanks...

Tom Kyte
June 22, 2009 - 4:03 pm UTC

why not just use streams and one way replication of the objects of interest then? Less limitations on versions and operations that way.

A reader, September 29, 2009 - 2:46 am UTC

Respected Sir;
I know that redologs are not being used in standby database.
I dont understand why their status is always changing (CLEARING_CURRENT,CLEARING) and I dont know how to interpret this clearing and clearing current. Can you please enlighten me?

Tom Kyte
October 02, 2009 - 8:24 am UTC

... I know that redologs are not being used in standby database. ...

you know incorrectly then - they absolutely are used.

lgwr ships the redo, the redo is written to standby online redo logs. If you use real time apply, then the standby online redo logs are applied right then, otherwise they are applied later after they are turned into archives.


they are used - that is precisely why their status is changing, just like the status changes in production.

log file sync waits

Rajesh N, October 07, 2009 - 12:07 am UTC

Hi Tom, we have maximize availabilty dataguard configuration with storage from Netapps. We are seeing log file sync waits of about 80 ms. Is there any way to dig out if there is any issue from storage OR network side to find out cause of high log file sync waits.

Thanks...Rajesh
Tom Kyte
October 08, 2009 - 7:20 am UTC

do you see correspondingly high "log file parallel write" waits?

your client waits for log file sync (time spent waiting for commit statement to be sent to server, plus wait for lgwr to respond, plus wait for IO by lgwr to complete, plus wait for confirmation of commit to be sent to client)

lgwr will post a 'Log file parallel write' wait event for the time is spends waiting for the IO to complete. If this wait is almost as large as the log file sync wait - then you know that most of the log file sync wait is waiting for IO to complete.

Data Guard control file and rman catalog not in sync

A reader, October 08, 2009 - 2:08 pm UTC

Tom --

I hope you will not consider it as a new question. It is related to Data Guard. We are on Oracle 10.2.0.4, RMAN with catalog, Data Guard (with broker, configured for MAXIMUM AVAILABILITY). I take hot backups on primary and cold backups on standby via RMAN and catalog. It seems that after several days control file gets out of sync with RMAN catalog as can be seen from the following:

1. On my primary database I am connecting to target and control file (NO catalog) and listing all archivelogs via list archivelg all - SHOWS logs from 106-1128:

RMAN>
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
6       1    106     A 28-SEP-09 /data/archive/PRD1/archivelog/2009_09_28/o1_mf_1_106_5d23tnj3_.arc
4       1    107     A 28-SEP-09 /data/archive/PRD1/archivelog/2009_09_28/o1_mf_1_107_5d23tnlp_.arc
3       1    108     A 28-SEP-09 /data/archive/PRD1/archivelog/2009_09_28/o1_mf_1_108_5d23tn9f_.arc
....
2023    1    1125    A 07-OCT-09 /data/archive/DPAPRD1/archivelog/2009_10_07/o1_mf_1_1125_5dtmphxq_.arc
2025    1    1126    A 07-OCT-09 /data/archive/DPAPRD1/archivelog/2009_10_07/o1_mf_1_1126_5dtmtwxq_.arc
2027    1    1127    A 07-OCT-09 /data/archive/DPAPRD1/archivelog/2009_10_07/o1_mf_1_1127_5dtnsfnb_.arc
2029    1    1128    A 07-OCT-09 /data/archive/DPAPRD1/archivelog/2009_10_07/o1_mf_1_1128_5dtomzdh_.arc


2. I then connect to my primary target WITH catalog and list archivelog all and see them listed for TODAY only:
List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
666421  1    1099    A 07-OCT-09 /data/archive/PRD2/archivelog/2009_10_07/o1_mf_1_1099_5dsyho8z_.arc
673868  1    1099    X 07-OCT-09 /data/archive/PRD1/archivelog/2009_10_07/o1_mf_1_1099_5dsyhof3_.arc
673869  1    1100    X 07-OCT-09 /data/archive/PRD1/archivelog/2009_10_07/o1_mf_1_1100_5dsz8n0n_.arc
666422  1    1100    A 07-OCT-09 /data/archive/PRD2/archivelog/2009_10_07/o1_mf_1_1100_5dsz8mst_.arc
666858  1    1101    A 07-OCT-09 /data/archive/PRD2/archivelog/2009_10_07/o1_mf_1_1101_5dt02cbq_.arc


Why do all of the primary archvielogs have X for status (its expired?). My CONFIGURE ARCHIVELOG settings are set to NONE on both primary and standy (because I take backups on both) :

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default


Is this a bug?

My RMAN hot backup script crosschecks archivelogs and then deletes logs that have been backed up twice, but keeps 7 days worth of logs on disk..

run
{
allocate channel c1 type disk;
crosscheck archivelog like '${FRA_DIR}%';
backup database format '${BACKUP_DIR}/datafiles__${BACKUP_TYPE}_${TIMESTAMP}%p_%s.rman';
backup archivelog all format '${BACKUP_DIR}/archivelogs_%d_%u_%s_%T' not backed up 2 times;
backup current controlfile format '${BACKUP_DIR}/${ORACLE_SID}_${TIMESTAMP}_CTL';
delete noprompt archivelog all backed up 2 times to DEVICE TYPE disk completed before 'sysdate-7';
delete noprompt expired archivelog like '${FRA_DIR}%';
release channel c1;
}



I expected to have 7 days of archivelogs in my catalog, not only control file. Please help! At this point I am considering taking backups on primary in NOCATALOG mode and taking backups on standby WITH catalog..

data guard

lzl, October 08, 2009 - 9:25 pm UTC

which difference of data guard and stream replication?

John Carew, October 10, 2009 - 3:09 am UTC

In maximum performance protection mode,

As far as I know,
After archivelog is generated in production, rfs gets the info from arc1 and creates the same archivelog in destination

why the archivelogs arent simply copied to standby database?




Private Information Retrieval

Vaibhav, October 13, 2009 - 12:00 am UTC

Hello Tom,

Its been almost a year that I am posting a question here.

Well my question has nothing to do with "Data Guard", it is based on Security.

I am sure you have heard of Private Information Retreival (PIR). Does Oracle have any implementation of PIR?

Books on PIR have algorithms that use Secure Processors to implement PIR.

I have to write an application that fakes the functionality of Coprocessor.

Any idea how should I go about?

Thanks

A reader, November 21, 2009 - 6:44 pm UTC

Hi Tom;


Suppose I am using maximum performace mode and no standby redologs.
In the primary database, the redolog is half filled.
I`ve decided to perform failover. I dont wanna lose data in this scenerio.
If I copy redologs from primay to standby,Does "finish recovery" command automatically apply the
half filled redologs?

Good

SAMI, July 27, 2010 - 1:06 am UTC

Hi Tom,

I want to know that how we can change the database unique name for Data Guard configuration. As the database name will be same for data guard configuration where as database unique name will different. Please guide me in this context.
Tom Kyte
July 27, 2010 - 12:13 pm UTC

have you read the step by step instructions for setting up a physical standby database yet? I'll bet every step you need to do is.... already written down.


http://docs.oracle.com/docs/cd/E11882_01/server.112/e10700/create_ps.htm#i63561

services and active dataguard,

A reader, October 21, 2010 - 9:42 am UTC

Hello,

I am trying to understand how does services play a role in failover (or switch over) to physical standby database that has an ACTIVE DATA GUARD?

Thanks,

Tom Kyte
October 25, 2010 - 3:22 pm UTC

databases register with listeners using a service name - clients connect to a listener and ask the listener to connect it to a database that provides a given service.

so, during the failover, the failover site would typically say "I am service X" where X was the service the production machine was saying it provided (before it failed)

services are simply a level of indirection - applications connect to SERVICES, databases provide services.

If an application connected to a DATABASE - failover would be hard since the database you were connecting to doesn't exist anymore, you'd have to change the way an application connects. Services allow you to change the database behind the service without changing the client.

services in ADG,

A reader, October 26, 2010 - 10:05 am UTC

Thanks for the explanation. Let me rephrase on what I have understood: I have primary database A and physical standby database B.

Users are connecting to databae A using services S1 and S2. If database A goes down and failsover to database B, services S1 and S2 will now point to database B. Is this automatic?

Other Question: Will there be any services on physical standby database set up with Active Data guard? I want users connecting to physical standby database for their reporting purposes. If this becomes primary, how will services from database A know to interact with services in database B?

Thanks,


Thanks,

Tom Kyte
October 26, 2010 - 8:11 pm UTC

... Is this automatic? ...

depends on how you configured it - it could be, sure. Connect time failover.
http://docs.oracle.com/docs/cd/E11882_01/network.112/e10836/concepts.htm#NETAG179

... Will there be any services on physical standby database set up
with Active Data guard? ...

you tell me, you configure that.

the "failover database" can be registered both as "service S1 and S2" and "service b".

Spfile changes in a data guard configuration

A reader, October 27, 2010 - 10:32 am UTC

I have a 10g production database with a data guard configuration - primary / physical standby. We recently changed the processes parameter on the primary database using

alter system set processes=200 scope=both;

How will any change like this affect the already running standby ? Consider this - currently my standby is running with a processes parameter set to 100. If for some reason, I have to switchover to my physical standby, then my current primary will be running with processes of 100 instead of what I changed (200). How can I maintain consistency between the init.ora parameters between the primary and physical standby, is the only way to achieve that is to make the alter system changes in both primary and standby ?
Tom Kyte
October 27, 2010 - 12:39 pm UTC

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ps.htm#i1022518

is a chapter on "what things you need to do if you do something to the production instance"

<quote>

if you: Change initialization parameters


then you: Dynamically change the standby parameters or shut down the standby database and update the initialization parameter file.
</quote>

You make the change (or not) on both.

Changing a table in Physical Standby in isolation

Anriban, January 31, 2011 - 2:11 am UTC

Hi Tom,

Consider Table TEST as below...
SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C                                                  VARCHAR2(10)
SQL> select * from test;
C
----------
Primary

How do I change the value of col C on Standby in isolation so that the val of col C is different on Primary and Standby?

I understand, I need to open the Standby in READ-WRITE mode as described in http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIABDH
can update the tables in Step 4, but, the change is not permanent after Step 5.

Is it possible in 10gR2? If yes, how? 

This is required for OWB metadata tables like wb_rt_service_nodes which contains hostnames, port and service names.

Tom Kyte
February 01, 2011 - 4:34 pm UTC

you do not. The entire purpose of the standby is to have every table from production that is in the standby be identical to the product instance.


You can only do this is you use a logical standby (always opened read/write) and if you do not copy the tables that need to have different data (logical standby doesn't have to get the entire database) but implement them each in each database.

RAC Primary to single instance standby

A reader, March 23, 2011 - 6:47 am UTC

Hi Tom,

We have Oracle 10.2.0.4 2 node RAC configured with single instance standby database. Today we were testing failover from RAC primary database to single instance standby database. We do not have dataguard broker. 

Following was done to test failover scenario. 
(1) Disabled archived log shipping from RAC primary to single standby database
(2) On standby database, performed below steps..



SQL> Recover managed standby database cancel;
SQL> alter database activate physical standby database;

Above steps worked fine. There were no errors reported in alert log file of single instance database. I was also able to open single instance standby database in READ-WRITE mode. The database is working fine but I noticed very strange thing in alert log file of single instance database that every time the database switches ONLINE REDO log file for Thread 1, Oracle also advances Thread 2 log sequence number and shows message archiver wakeup message. 

Below is abstract of alert log file of single instance database after opening it in READ-WRITE mode..

Wed Mar 23 16:51:47 2011
Thread 1 advanced to log sequence 54 (LGWR switch)
Current log# 10 seq# 54 mem# 0: /ora_logA_DR/NEWS2PS/redo_1_6a.log
Current log# 10 seq# 54 mem# 1: /ora_logB_DR/NEWS2PS/redo_1_6b.log
Wed Mar 23 16:51:47 2011
Thread 2 advanced to log sequence 46 (archiver wakeup)<-- This is what I mean
Wed Mar 23 16:59:15 2011
Thread 1 advanced to log sequence 55 (LGWR switch)
Current log# 13 seq# 55 mem# 0: /ora_logA_DR/NEWS2PS/redo_1_7a.log
Current log# 13 seq# 55 mem# 1: /ora_logB_DR/NEWS2PS/redo_1_7b.log
Wed Mar 23 16:59:15 2011
Thread 2 advanced to log sequence 47 (archiver wakeup)<---- Again same message for thread 2

My question is, why single instance database needs to generate archived logs for Thread 2? The database also generate physical archived logs for Thread 2. Please see below..

-> ls -ltr |tail -15 
-rw-r----- 1 oracle oinstall 1144832 Mar 23 16:37 arch_NEWS2PS_50_1_746538589.arc
-rw-r----- 1 oracle oinstall 1024 Mar 23 16:38 arch_NEWS2PS_43_2_746538589.arc
-rw-r----- 1 oracle oinstall 56320 Mar 23 16:38 arch_NEWS2PS_51_1_746538589.arc
-rw-r----- 1 oracle oinstall 1024 Mar 23 16:44 arch_NEWS2PS_44_2_746538589.arc
-rw-r----- 1 oracle oinstall 1976832 Mar 23 16:44 arch_NEWS2PS_52_1_746538589.arc
-rw-r----- 1 oracle oinstall 1234944 Mar 23 16:51 arch_NEWS2PS_53_1_746538589.arc
-rw-r----- 1 oracle oinstall 1024 Mar 23 16:51 arch_NEWS2PS_45_2_746538589.arc
-rw-r----- 1 oracle oinstall 1226240 Mar 23 16:59 arch_NEWS2PS_54_1_746538589.arc
-rw-r----- 1 oracle oinstall 1024 Mar 23 16:59 arch_NEWS2PS_46_2_746538589.arc
-rw-r----- 1 oracle oinstall 2017280 Mar 23 17:06 arch_NEWS2PS_55_1_746538589.arc
-rw-r----- 1 oracle oinstall 1024 Mar 23 17:06 arch_NEWS2PS_47_2_746538589.arc
-rw-r----- 1 oracle oinstall 1160704 Mar 23 17:14 arch_NEWS2PS_56_1_746538589.arc
-rw-r----- 1 oracle oinstall 1024 Mar 23 17:14 arch_NEWS2PS_48_2_746538589.arc
-rw-r----- 1 oracle oinstall 3641856 Mar 23 17:21 arch_NEWS2PS_57_1_746538589.arc
-rw-r----- 1 oracle oinstall 1024 Mar 23 17:21 arch_NEWS2PS_49_2_746538589.arc
As you can see, database generated physical archived log for Thread 2. Size of archived log for Thread 2 remains 1024 bytes only!

Do you see any problem? I need to reconfigure RAC standby database using backup of single instance database and I am not sure if this problem will prevent me for doing so..

Can you please shed some light on this behaviour? Is it normal? Please note that in standby spfile we have THREAD=1 configured. Not sure why single instance database is generating archived logs for thread 2!!

Thank you for your time..


Tom Kyte
March 23, 2011 - 8:38 am UTC

Your database still is a 'RAC-able' database. You can get rid of those if you want, but they were necessary when you were the standby. The redo log files are still registered in the database, still part of the database. You can clean them out if you don't want them - but you probably do.

Because this is your standby, a temporary place - you'll be fixing production and either switching back (if you did a graceful switchover) or re-instantiating from this site so you can do a graceful switchback to the original production environment.

RAC Primary to single instance standby

A reader, March 24, 2011 - 3:03 am UTC

Thank you Tom. I have already configured RAC standby database from the single instance primary database and have also performed switchover. All good and now I understand use of Thread 2 in single instance database as well!

Again, thanks for your time. You have been a great help.

Cheers

Restore primary

Antonio Cantillo, April 04, 2011 - 4:26 pm UTC

Hi,

What happen if i restore the primary, for example, the last sequence log is 1500 and i restore my database to 1000 log seq,

- what happen with my logical standby, if necesary create again the standby database?

- what happen with a phisical standby?

thank you and sorry for my bad english.

Tom Kyte
April 12, 2011 - 12:08 pm UTC

- depends
- depends

if you can flash them back using flashback database, they would be OK. If you cannot, you'll need to re-do them.


http://docs.oracle.com/docs/cd/E11882_01/server.112/e17022/scenarios.htm#i1049616

Last Contiguous SCN at the SRL

Rittick Gupta, April 16, 2011 - 11:00 am UTC

Is there a way to determine the last contiguous SCN in the SRL at the standby database. Assuming that gaps in SCN is a possibilty in the SRL logs, we would like to determine the SCN which is guaranteed to be available at the standby - in case the primary fails.
Tom Kyte
April 18, 2011 - 10:11 am UTC

what is a contiguous SCN? I mean - what is its relevance?

I hate acronyms - I assume by 'SRL' you mean standby redo logs (while we have a column in one table defined with the name SRL, it is not an acronym frequently used. It just makes it so much harder to figure out what people are talking about when they do not type things out)




http://docs.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_1012.htm#sthref3094

see the last column

Last SCN at the Standby REDO LOG

Rittick, April 18, 2011 - 7:38 pm UTC

Tom,
Thanks a lot for your response By SRL, I meant the "Standby REDO LOG". I was interested is the last SCN in the "Standby REDO LOG" which I can be sure of that it will be applied after a dual disaster i.e. failure of the primary and recovery of standby (before the SCN was applied).
Thanks again for your help.

Rittick

Speeding up network?

Loïc, April 21, 2011 - 8:23 am UTC

Hi Tom,
I've read that Data Guard redo transport works over IP network. Do you know if it would be feasible to use our SAN (fiber channel, using IPFC protocol) to transport the redo to the DR site?

Currently we are using OS mirroring for failover so switching to Data Guard would do the job *and* reduce the quantity of data being transfered but at the cost of overusing the IP network.

Apart testing it myself (with my workload) do you see any potential issue to do this? (IPFC not mature...?)

Also, could you make some comments if Exadata DB machines are now used to consolidate several databases (OLTP mainly) and using Data Guard?

Thanks in advance,
Loïc
Tom Kyte
April 25, 2011 - 8:14 am UTC

as long as it supports tcp/ip connections, you can use it.


Also, could you make some comments if Exadata DB machines are now used to
consolidate several databases (OLTP mainly) and using Data Guard?


not sure what sort of comment you are looking for? Yes, Exadata is being used to consolidate several databases - sometimes OLTP, sometimes DW, sometimes both.

Data Guard on Exadata

Loïc, April 26, 2011 - 9:35 am UTC

In fact, I meant if we consolidate, we'll add more redo activity. To face this growth, are there best practices using Data Guard on Exadata?

By the way, I found these two documents that helped me somewhat:

http://www.oracle.com/technetwork/database/features/availability/maa-wp-dr-dbm-130065.pdf

http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr1-activedataguard-1-128199.pdf
Tom Kyte
April 26, 2011 - 2:31 pm UTC

In fact, I meant if we consolidate, we'll add more redo activity.

you'll generate the same amount of redo - why do you think it would generate more redo overall?



Precision, precision, precision...

Loïc, April 27, 2011 - 8:04 am UTC

Ok, you are right regarding the quantity of redo that will not change; but with such powerful servers, the redo activity will be the same in a smaller time window, isn'it? (more peaks)


Tom Kyte
April 27, 2011 - 9:07 am UTC

It depends - but - so what if it is?

The hardware stack all of the way from the database server, to the network connecting it to the disk, to the storage cells are all balanced (at the same level of 'performance'). Meaning, they can handle it.

So, since the machine is all around "bigger and faster", you would expect it to be able to do the same amount of work a smaller machine would in less time.

It will only do what it can do - just like a smaller machine will only do what it can do.

data guard archiver process

A reader, July 17, 2011 - 7:55 am UTC

Dear Tom,

Regarding the notes on link http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#i1265621 , I have couple of queries:
1) I found the above document specifying that "On the remote destination, the remote file server process (RFS) will, in turn, write the redo data to an archived redo log file from a standby redo log file. Log apply services use Redo Apply or SQL Apply to apply the redo to the standby database." So why RFS is writing all the redo log entries into standby redo log files first and then ARCn process from that standby redo log to an archived log file? Why cann't RFS writing all the log entries into an archived redo log in standby site bypassing the standby redo log?
2) In the diagram there in the note (for ARCn Archival Processing), it is being shown that Redo or SQL apply happens from both standby redo log (real time apply) and archived log file? I am confused, because what I know was Redo apply and SQL apply both happens from archived redo log file only to the standby database. Can you please explain?
3) What is the role of standby redo log file here?

Data Guard

A reader, July 28, 2011 - 6:22 pm UTC


valid_for attribute

A reader, August 07, 2011 - 12:14 am UTC

Dear Tom,

I am little confused with Valid_for attribute in LOG_ARCHIVE_DEST_n parameter.
1) What is the exact meaning of LOG_ARCHIVE_DEST_2='service=standby lgwr async valid_for=(online_logfiles,primary_role)'? Why it is primary_role and online_logfiles when this destination is for standby side?
2) I have also observed that in a maximum performance mode, even if the redo apply services are not activated for physical standby the log switch in primary side, transfers the archived logs into standby side. So it is done by ARCn process I think. Then where does LGWR plays its part?

dataguard info questions

Doron, December 30, 2011 - 1:20 am UTC

Hi Tom,

Oracle Dataguard 10g Docs say:
"During a failover, Data Guard can recover and apply more redo data from standby redo log files than from the archived log files alone."

and probably related

what info the standby controlfile holds vs regular control file?
can you elaborate about the extra info in there for a better understanding on the internals?
Also, Are you familiar with a book on Dataguard internals that explain in logical and detail how things realy work like your books or Gopalakrishnan Books?

Thanks,
Doron
Tom Kyte
December 30, 2011 - 11:00 am UTC

? Not sure what you are trying to say/add/ask?

The book by Larry Carpenter and others

http://www.amazon.com/Oracle-Data-Guard-Handbook-Press/dp/0071621113/ref=sr_1_1?ie=UTF8&qid=1325264411&sr=8-1

is the best one going.

DataGuard Configuration Physical Standby on Windows

Rohit Dogra, January 05, 2012 - 9:04 pm UTC

Iam trying to setup the Dataguard at home on a single PC only, where my both Primary and Satndby Databse Reside on the same machine because i dont have an additional H/w at home.
I have configured the Intialization parameter file for the Primary database and have created the pfile and spfile for the same.
By creating the directory structure on the same machine for standby i just copy the primary datafile and redologs to standby location.
Next iam trying to open the database with Stanby pfile while my primary is shutdown iam getting the TNS Protocal adapter error.
For TNS Error i have configured the Netca and netmgr configuratioon properly and have also created 1 more listener for stanby database still when iam performing the test it doesnt get succeded

So please guide me where iam wrong...Thanks for reply

DB Migration using Data Guard

Kev, April 18, 2012 - 11:16 am UTC

Good evening Tom,

We have a 2.5TB 10gR2 database configured to use Data Guard with a physical stand-by. In the next couple of months, it is planned to migrate this database to an 11gR2 environment, also with Data Guard.

My question is this: Is it possible to create a Data Guard configuration between a 10gR2 Primary and 11gR2 Physical standby? The idea would then be to perform a switchover to complete the database migration before rebuilding the physical standby into the new 11g environment.

Of course, the following points would be observed during the process:

1. The compatibility level would be the same on both ends.
2. New parameters in 11g would not be implemented until after the migration.
3. The database to be migrated would naturally have been tested before the migration.

I would be very interested to hear your thoughts on this.

Many thanks and with kind regards.

Kev
Tom Kyte
April 19, 2012 - 4:39 am UTC

... Is it possible to create a Data Guard configuration
between a 10gR2 Primary and 11gR2 Physical standby? ...

no.


You can however, convert the physical into a transient logical standby and do an online upgrade.

http://docs.oracle.com/cd/E11882_01/server.112/e17157/planned.htm#HAOVW11871

apply latency,

A reader, June 21, 2012 - 11:05 am UTC

Hello,

I created a user on primary database (2-node RAC). The physical standby database is set to be on par with primary since with zero minute delay. Even after 15 minutes, I don't see the new user on the standby database. I tried doing alter system switch logfile but still not seen. The data guard is created with max availability option.

Why could it take so long? Any dictionary views that I can use to debug and tell where it got stuck?

Thanks,

Dataguard- Standby redolog file

Vimal, June 24, 2012 - 12:23 am UTC

Hi Tom,
I have a doubt in transforming the created standby redolog files from the primary to standby server during configuration.
In log_file_name_convert parameter in standby pfile, I have been giving the source location and destination location. It is OK for the online redo logfiles. But what happens to the standby redo file transformation? How it gets converted to the new location in the standby without any parameter to convert their location?

Kindly, guide me please.

Thanks.

Dataguard- Standby redolog file

Vimal, June 25, 2012 - 7:09 am UTC

Hi Tom, thanks for the document. However, it doesn't answer my question. My question was about the location of the Standby Redo Log file in both databases. But this document details about the destination of Archive Log files.

My question is that, by default if we create standby redo log files, they gets created in the flash recovery area and not inside the Oradata folder. While configuring data guard I usually create the standby redo log files in the primary and then copy them to the standby. I can successfully run data guard like this. But my doubt is that in standby database do we need to include the new location of the standby redo log files in the Log_file_name_convert parameter of the standby?

Thanks in advance.

Regards.
Tom Kyte
June 25, 2012 - 11:18 am UTC

if the files are in different locations on primary versus standby, yes, you'd have to convert them.

data guard

Jai, June 28, 2012 - 8:00 am UTC

Hi Tom,
1)Can we configure primary database and standby database in same oracle home?

2)Can we configure them in same host in different oracle homes each?

Thanks a lot!
Tom Kyte
June 29, 2012 - 12:00 am UTC

1) yes, but why??? it is for disaster recovery, that is something YOU DO NOT DO on a single machine, it would be silly

2) see #1

apply latency cont..

A reader, July 03, 2012 - 3:01 pm UTC

Hello,

We deliberately brought down standby for an hour and then we restarted the apply process. It did at the rate of 4-6 files per minute. Pretty good. However, when the apply is happening at the real time, I still see logs are lagging behind even though I am generating about 30-35 redo logs file hour (on primary).

Last week we spent some time with unix and storage team to determine any IO issue. They didn't see any significant reasons. Besides that, the apply rate was superfast when standby was catching up (archivelog files was already there in the destination).

As a Oracle DBA, how to determine whatever the issue here is? Would there be a delay in shipping redo log files?

Thanks,

Tom Kyte
July 03, 2012 - 5:44 pm UTC

what mode are you running in? are you really shipping redo logs? or are you shipping upon commit?

Following up on Jai's question

ChongYoh, July 03, 2012 - 6:54 pm UTC

Hi Tom,

Following up on Jai's question (of setting up primary and standby in the same box). What about having them in the same local site for instance same data center? Could there be licensing benefits? Easy of admin benefits? So instead of installing RAC and ASM and DG, just use DG across both local and global sites. Only thing that seems to be missing is RAC's scalability. Is that correct?

Cheers,
ChongYoh
Tom Kyte
July 03, 2012 - 7:26 pm UTC

You still have to license the standby server(s).

RAC one node might be what you are looking for for high availability in a data center - with data guard providing failover to a remote site.

Opinion on ACFS replication instead of Data Guard MAXPERF single primary/standby config.

Adrian Angelov, July 04, 2012 - 10:11 am UTC

Hi Tom,
what's your opinion on the replacement of a 11gR2 single instance one primary/one standby(the two servers are use same Linux x86_64 platform) Data Guard MAXPERF configuration with the following:

- removal of the standby database at the DR site.
- roll forward image copies backup(on the primary site) updated several times per day on ACFS and ACFS replication configured to send to the DR site.
- archived redo logs on ACFS volume that are replicated to the DR site using the ACFS replication.
- verification of the backup using ACFS snapshots on a regular basis.

(sort of replacement of Data Guard with storage replication)

What are the pros and cons from your point of view(i particular with the ACFS implementation)?

Thank You
Tom Kyte
July 05, 2012 - 7:30 am UTC

I would not drop data guard.


data guard will transmit the minimal amount of information to keep the failover up to date (just one copy of the redo stream). File system replication has to send a ton more (typically close to an order of magnitude or more data).

consider a simple insert into a table. You will modify an 8k database block. Suppose the table has three indexes, you will modify three more 8k blocks. You will generate at least one 8k undo block. You will generate redo for all of this which will typically be written to at least two redo logs. These redo logs in turn will be turned into archives.

All of that has to be sent ultimately using disk based replication. Only the original tiny redo stream has to be sent using data guard.

Also, data guard will be processing that redo in real time, looking for inconsistencies...

Also, data guard can be used to perform automatic block level recovery of either the production or standby site when it detects a problem.

also, also, also - data guard is for database failure, it has a lot of things it can do that you just cannot do with disk based approaches.


And if you do it the way you are talking about doing, all you have done is reinvent the Oracle 8i Release 3 implementation of data guard without any of the features using archives.


You would stand to lose a lot of data in a failover - at least as much as is contained in a redo log file.

I would not suggest this.

apply lag latency followup,

A reader, July 05, 2012 - 9:14 am UTC

Hello,

We are running in MaxAvailability mode.

The value for log_archive_dest_2 is:
service="prismz", LGWR SYNC AFFIRM delay=0 optional compress ion=disable max_failure=10 max _connections=1 reopen=15 db_un ique_name="PRISMZ" net_timeout =30, valid_for=(all_logfiles,p rimary_role)

Thanks,

Tom Kyte
July 05, 2012 - 11:41 am UTC

so, are you sure you are seeing a delay in REDO being transmitted? That is what you said was happening - the redo wasn't being transmitted.

http://docs.oracle.com/cd/E11882_01/server.112/e25608/log_transport.htm#SBYDB4756

you can use that to review how the redo is being transmitted (or not).

but basically- I don't think you are having a problem in sending the redo, the redo is getting sent.

are you using real time apply??

http://docs.oracle.com/cd/E11882_01/server.112/e25608/log_apply.htm#SBYDB0050


Your delay 0 will delay the application of a redo log until it is completely full and archived at the standby site - unless you have real time apply enabled.

http://docs.oracle.com/cd/E11882_01/server.112/e25608/log_apply.htm#SBYDB4762


apply lag followup,

A reader, July 06, 2012 - 1:31 pm UTC

Hello,

We just issued ALTER SYSTEM SET log_archive_trace=8192 SCOPE=BOTH SID='*'; on both primary and standby server.

According to the document, the trace file is written in the path specified in USER_DUMP_TEST. I don't see a new trace file although I could see bunch of *rfc*.trc on standby and also bunch of *lgwr*.trc files.

Which trace files would I be checking for?

Thanks,

apply lag followup

A reader, July 10, 2012 - 7:40 am UTC

Hi there,
you have to tune sometimes the network to avoid some lags.
Oracle recommends to set RECV_BUF_SIZE and SEND_BUF_SIZE.
Depending of the speed line and the latency you have between two nodes, you can calculate the value for these parameters.
Once it is done, you have to adapt the TCP stack (interesting note on MOS ID 1002174.1)
For example, for Solaris, you can adapt the settings for :
tcp_recv_hiwat, tcp_xmit_hiwat or tcp_max_buf.
You can check too the max generation redo on primary site and compare with the apply rate in v$recovery_progress.




does switch log effected by protection mode and standby database.

Khalid AlMansour, September 03, 2012 - 7:08 am UTC

Dear Tom,

I have setup standby database in 11gR2. I setup the size of the redo logs in the primary to be 512m. but I have noticed that redo logs not get filled till its maximum size some times switch log occurred and the files are in Kilo Byte or less than 30M.

Does the standby database effect the switch log of the primary database "force switch log while redo log still can be filled with redo entries"?

Regards,
Khalid.
Tom Kyte
September 10, 2012 - 7:05 pm UTC

what is archive_lag_target set to in your primary sites init.ora

Active Data Guard

Shambu, October 15, 2012 - 5:37 pm UTC

Hello Tom,

We have 2-node RAC Active Data guard environment. The apply instance is node2...(mrp is running on node2). the node1 is idle. When I query up gv$dataguard_stats view to check the delay, I see delay for node1 instance and no delay for node2. Why do we see this kind of information?  

SQL> SELECT inst_id,NAME, VALUE, TIME_COMPUTED FROM GV$DATAGUARD_STATS WHERE NAME='apply lag';

   INST_ID NAME                             VALUE                                         TIME_COMPUTED
---------- -------------------------------- --------------------------------------------- ------------------------------
         1 apply lag                        +36 23:55:50                                  10/15/2012 22:28:41
         2 apply lag                        +00 00:00:00                                  10/15/2012 22:28:42

Tom Kyte
October 16, 2012 - 9:27 am UTC

apply lag is computed based on data received from the primary database. if node1 isn't receiving anything (it is all going to node2...) .....

v$log vs v$standby_log

okan soyyilmaz, January 03, 2013 - 8:33 am UTC

Hi Tom

I have a 4 instance rac primary database and a single instance standby database. Each instance in primary database has 3 groups of online redo logs. According to dataguard administration document, I have created 4 groups of standby log for each instance, totally 16 standby logfiles on standby database. But, since I have created standby database by duplicating primary database (rac), there are 3 groups of online redo logs for 4 threads, totally 12 redo logfiles in my single instance standby database. Altough they are redo files and the standby database is opened read only access, the online redo logs on standby database are switched similar to primary database with same sequence#. Does it normal?

I mean whenever I query v$standby_log, I see 16 logfiles, only one group of each thread is active, which seems ok. But whenever I query v$log, I see 12 logfiles, only one group of each thread is current, as if this is a rac database like primary db. And the sequence# in both views are same for active ones. what should be the relation between online redo logs and standby redo logs in standby database?

I expect to have 3 groups of online redo logs and 16 groups of standby logs on standby database for this 4 instance primary rac database and 1 instance standby database configuration.

Thanks

2 node rac and a single standby node

A reader, July 09, 2013 - 10:50 am UTC

hi,tom :
all the time i have a question,in oracle 11g and oracle 12c ,i have test the 2 node rac and a single node standby ,
where rac became standby whether only one node can be "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;"
i mount the second node of rac,the infomation is

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

my test if right ?

2 node rac and a single standby

A reader, July 09, 2013 - 10:57 am UTC

all the time i have a question,in oracle 11g and oracle 12c ,i have test the 2 node rac and a 
single node standby ,
where rac became standby whether only one node can be "ALTER DATABASE RECOVER MANAGED STANDBY 
DATABASE DISCONNECT FROM SESSION;"
i mount the second node of rac,the infomation is 

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.0021E+10 bytes
Fixed Size                  3478336 bytes
Variable Size            2013269184 bytes
Database Buffers         7985954816 bytes
Redo Buffers               18677760 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


my test if right ?

S. Marsh, February 25, 2017 - 11:49 am UTC

I new to oracle database world.
I have little bit confusion , I have production database server where oracle database 11g install that is called prod_ora_db.
Now my question is if i want to apply a dataguard so in secondary or stand by database need install oracle database or only oracle database software only?

Connor McDonald
February 26, 2017 - 2:02 am UTC

You install the oracle software.

Then you instantiate the standby by (in effect) copying the production database from the production node to the standby node.

Then you initiate a process which transfers the redo logs (list of changes from the production) to the standby node which then is used to keep the standby (ie, the copy) database up to date.

There is a full set of instructions to set up a standby in the docs

http://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm#SBYDB00200