Creating a DG Broker Configuration and Perform Swithover testing

This summary is not available. Please click here to view the post.

Manage Physical Standby Database - Add a Datafile

When a datafile is added to the primary database, what could happen at standby database? Today, I had some hands-on experiences about that.

Case 1 - STANDBY_FILE_MANAGEMENT set to MANUAL at standby database

In this case, an UNNAMED file will be created at $ORACLE_HOME/dbs, we need to issue 'ALTER DATABASE CREATE DATAFILE AS' to fix it.

(1) At primary:
alter tablespace dbaets_data add datafile '/db2/u01/oradata/ettdb/dbaets_data_02.dbf' size 500M;
 

(2) At standby, query v$datafile 
NAME                                                  SIZE_M
-------------------------------------------------    -------
/db2st2/u01/oradata/etsdb/system01.dbf                   500
/db2st2/u01/oradata/etsdb/sysaux01.dbf                  2048
/db2st2/u01/oradata/etsdb/undotbs01.dbf                  271
/db2st2/u01/oradata/etsdb/users01.dbf                    500
/db2st2/u01/oradata/etsdb/xdb01.dbf                     1024
/db2st2/u01/oradata/etsdb/dbaets_data_01.dbf             500
/apps/opt/oracle/product/11.2.0/dbs/UNNAMED00007           0
 

(3) At standby, issue 'ALTER DATABAE CREATE DATAFILE AS'

ALTER DATABASE CREATE DATAFILE '/apps/opt/oracle/product/11.2.0/dbs/UNNAMED00007'  AS '/db2st2/u01/oradata/etsdb/dbaets_data_02.dbf';

(4) At standby, query v$datafile again:

NAME                                                   SIZE_M
-------------------------------------------------- ----------
/db2st2/u01/oradata/etsdb/system01.dbf                    500
/db2st2/u01/oradata/etsdb/sysaux01.dbf                   2048
/db2st2/u01/oradata/etsdb/undotbs01.dbf                   271
/db2st2/u01/oradata/etsdb/users01.dbf                     500
/db2st2/u01/oradata/etsdb/xdb01.dbf                      1024
/db2st2/u01/oradata/etsdb/dbaets_data_01.dbf              500
/db2st2/u01/oradata/etsdb/dbaets_data_02.dbf              500

Case 2 - STANDBY_FILE_MANAGEMENT set to AUTO, but file cannot be created due to other error

At standby, initially I have DB_FILE_NAMEC_CONVERT set as folllow:

 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /db2st2/u01/oradata/etsdb, /db
                                                 2/u01/oradata/ettdb

This is not in the correct order as at standby site the path is '/db2st2/u01/oradata/etsdb' whereas '/db2/u01' does not exist.

At primary, I added another datafile:

ops$oracle@ETSDB> alter tablespace dbaets_data add datafile '/db2/u01/oradata/ettdb/dbaets_data_03.dbf' size 100M;
 
Tablespace altered.

In the alert log file of  the standby database, it can be seen the file is not created and managed recovery process (MRP0)  is terminated:

Media Recovery Log /db2st2/arch/1_1115_790089239.dbf
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Errors in file /apps/opt/oracle/admin/etsdb/diag/rdbms/etsdb/etsdb/trace/etsdb_pr00_8554.trc:
ORA-01119: error in creating database file '/db2/u01/oradata/ettdb/dbaets_data_03.dbf'
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
SVR4 Error: 13: Permission denied
File #8 added to control file as 'UNNAMED00008'.
Originally created as:
'/db2/u01/oradata/ettdb/dbaets_data_03.dbf'
Recovery was unable to create the file as:
'/db2/u01/oradata/ettdb/dbaets_data_03.dbf'
MRP0: Background Media Recovery terminated with error 1274
Errors in file /apps/opt/oracle/admin/etsdb/diag/rdbms/etsdb/etsdb/trace/etsdb_pr00_8554.trc:
ORA-01274: cannot add datafile '/db2/u01/oradata/ettdb/dbaets_data_03.dbf' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 12910838021570 but controlfile could be ahead of datafiles.

I reset the the db_file_name_covert to be '/db2/u01/oradata/ettdb','/db2st2/u01/oradata/etsdb' and restarted the MRP0, in the alert log I saw:

MRP0: Background Managed Standby Recovery process started (etsdb)
 started logmerger process
Tue Apr 08 16:43:35 2014
Managed Standby Recovery starting Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file /apps/opt/oracle/admin/etsdb/diag/rdbms/etsdb/etsdb/trace/etsdb_pr00_10330.trc:
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/apps/opt/oracle/product/11.2.0/dbs/UNNAMED00008'
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/apps/opt/oracle/product/11.2.0/dbs/UNNAMED00008'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (etsdb)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

So an UNNAMED file is created at $ORACLE_HOME/dbs  and MRP0 is stopped again. To fix:

SQL> alter system set standby_file_management=manual;
 
System altered.
 
SQL> alter database create datafile '/apps/opt/oracle/product/11.2.0/dbs/UNNAMED00008' as '/db2st2/u01/oradata/etsdb/dbaets_data_03.dbf';
 
Database altered.
 
SQL> alter system set standby_file_management=auto;
 
System altered.

Case 3 STANDBY_FILE_MANAGEMENT set to AUTO and DB_FILE_NAME_CONVERT  is correct

No issue as expected:
At primary:

ops$oracle@ETSDB> alter tablespace dbaets_data add datafile '/db2/u01/oradata/ettdb/dbaets_data_04.dbf' size 100M;
 
Tablespace altered.

At standby, the alert log shows the datafile is added with the converted file name:

Tue Apr 08 16:56:22 2014
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
Attempt to start background Managed Standby Recovery process (etsdb)
Tue Apr 08 16:56:22 2014
MRP0 started with pid=33, OS id=12229 
MRP0: Background Managed Standby Recovery process started (etsdb)
 started logmerger process
Tue Apr 08 16:56:27 2014
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /db2st2/arch/1_1115_790089239.dbf
Media Recovery Log /db2st2/arch/1_1116_790089239.dbf
Media Recovery Log /db2st2/arch/1_1117_790089239.dbf
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
Recovery created file /db2st2/u01/oradata/etsdb/dbaets_data_04.dbf
Successfully added datafile 9 to media recovery
Datafile #9: '/db2st2/u01/oradata/etsdb/dbaets_data_04.dbf'

Note: The testing dataguard environment is the same as the one that is described in the previous post. However, I did a switchover, so in today's testing, ettdb is primary and etsdb is standby.

Create a physical standby database through RMAN duplicate active data files

I have a very small database ( ~ 5GB) called etsdb, which hosts an Oracle APEX application called DBAETS for DBA team internal use.

In the past, as a better-than-nothing backup and disaster recovery strategy, I export it every night and ship the export dump file to a remote server. Now I decide to create a physical standby database at the same remote server for it.

The transaction volumn of this application is typically very low as at any given time there could be just two or three DBAs logged into it to update some info. So it is ideal to use RMAN active database duplication technique to create the physical standby. In this method, RMAN duplicate process copies the active data files of the primary database directly to the remote host through network. RMAN supports two basic types of duplication: active database duplication and backup-based duplication. Oracle online documentation has a section about the considerations  to choose duplication technique.

I describe the steps involved in the following:

1. Prepare the production database to be the primary database

a. Ensure that the database is in archivelog mode

b. Enable force logging

    SQL> ALTER DATABASE FORCE LOGGING;

c. Create standby redologs

     SQL> alter database add standby logfile '/db2st2/u01/oradata/etsdb/stby_redo01.rdo' size 100M;

d. Modify the primary initialization parameter for dataguard on primary,

         
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(etsdb,ettdb)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/db2st2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=etsdalter system set LOG_ARCHIVE_DEST_2='SERVICE=ettdb LGWR ASYNC VALID_FOR=(online_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=ettdb';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set FAL_SERVER=ettdb;
alter system set FAL_CLIENT=etsdb;
alter system set DB_FILE_NAME_CONVERT='/db2st2/u01/oradata/etsdb','/db2/u01/oradata/ettdb' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/db2st2/u01/oradata/etsdb','/db2/u01/oradata/ettdb', '/db2st2/u02/oradata/etsdb','/db2/u02/oradata/ettdb' scope=spfile;

2. Ensure that the sql*net connectivity is working fine.

Configure the listener in standby and put the following tns entries in tnsnames.ora:

 ettdb =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1523))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = ettdb )
     )
   )
  
 etsdb =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = etsdb )
     )
   )

Confirm that tnsping ettdb and tnsping etsdb work on both primary and standby sites

3. Create the standby database

a. Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.

The username is required to be SYS and the password needs to be the same on the Primary and Standby. The best practice for this is to copy the passwordfile as suggested. The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

orapwetsdb ----> orapwettdb

b. Create a initialization parameter with only one parameter DB_NAME.

  
 -- initettdb.ora 
 DB_NAME=etsdb
 DB_UNIQUE_NAME=ettdb
 DB_BLOCK_SIZE=8192

c. Create the necessary directories in the standby location to place the datafiles and the trace files in the $ADR_HOME.

d. Set the environment variable ORACLE_SID to the standby service and start the standby-instance.

 % export ORACLE_SID=ettdb
 % sqlplus "/ as sysdba"
 SQL> startup nomount pfile=$ORACLE_HOME/dbs/initettdb.ora

e. Verify if the connection 'AS SYSDBA' is working
  sqlplus sys@ettdb as sysdba
  sqlplus sys@etsdb as sysdba

f. On the primary system invoke the RMAN executable and connect to the primary and the auxiliary database ( i.e., the standby)

$ rman target sys/xxx@etsdb auxiliary sys/xxx@ettdb cmdfile=dup.rmn

content of dup.rmn :

duplicate target database for standby from active database
spfile
parameter_value_convert 'etsdb','ettdb'
set db_unique_name='ettdb'
set db_file_name_convert='/db2st2/u01/oradata/etsdb','/db2/u01/oradata/ettdb','/db2st2','/db2'
set log_file_name_convert='/db2st2/u01/oradata/etsdb','/db2/u01/oradata/ettdb', '/db2st2/u02/oradata/etsdb','/db2/u02/oradata/ettdb'
set control_files='/db2/u01/oradata/ettdb/control01.ctl'
set log_archive_dest_1 =  'LOCATION=/db2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ettdb' 
set log_archive_max_processes='5'
set fal_client='ettdb'
set fal_server='etsdb'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(etsdb,ettdb)'
set log_archive_dest_2='service=etsdb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=etsdb'
;

4. Connect to standby using SQL*Plus and start the MRP ( Managed Recovery Process)

-- mount db first if required

alter database recover managed standby database disconnect from session;

5. If licensed and want to use Active Dataguard (ADG), than open the Standby Database in READ ONLY and start the revoer

Enable Active Data Guard using SQL Plus :

SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> alter database recover managed standby database disconnect using current logfile;

-- verify
SQL> select name, db_unique_name, database_role, open_mode from v$database;
 
NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ETSDB     ettdb                          PHYSICAL STANDBY READ ONLY WITH APPLY

testing to confirm that standby db can be read and meanwhile redo logs are being applied:

in standby

create table dbaets.t as select * from all_objects
                                       *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
 


in primary

  1* select thread#, max(sequence#) from v$log_history group by thread#
SQL> /
 
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1           1057
 
SQL> create table dbaets.t as select * from all_objects;
 
Table created.
 
SQL> alter system archive log current;
 
System altered.
 
SQL> select thread#, max(sequence#) from v$log_history group by thread#
  2  ;
 
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1           1058


in standby:



SQL> select thread#, max(sequence#) from v$log_history group by thread#;
 
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1           1058


SQL> select count(*) from dbaets.t;
 
  COUNT(*)
----------
     17855

References:

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)

RMAN duplicate: http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV298

Clone an online database - applying standby feature

In a previous post, I described how to clone an online database by transferring data files through network. Since the cloned database should not be opened immediately after the copy, instead, it needed to be opened one week later. This was to minimize the maintenance window needed to configure the cloned database. In this scenario, we need to ship the archived logs from source to target, and also apply them in the target during the whole week in order to keep the source and target database in sync. For that purpose, I wrote a shell script and put it in the cron to automatically transfer the archived logs and then manually applying the logs.

Inspired by a fellow DBA recently, I have found that we can take advantage of Oracle standby technology to achieve the log transportation and apply purpose automatically. I thus did a test on a testing 10g database to demonstrate the concept, which consisted of the following steps:

1. Copied selected tablespaces from source to target, using the same method as in the previous post
    Tablespaces: SYSTEM, SYSAUX, USERS and UNDOTBS1

2. Created standby control file in source and copied them to the target.e.g
     a. At source, alter database create standby controlfile as '/tmp/stby.ctl';
     b. scp from source to target server
     c. At target, issue:
        cp /tmp/stby.ctl /db1/u01/oradata/orasid1/control01.ctl
        cp /tmp/stby.ctl /db1/u02/oradata/orasid1/control02.ctl
        cp /tmp/stby.ctl /db1/u03/oradata/orasid1/control03.ctl

3. Added in the init.ora a parameter: standby_file_management = manual
    Note: this is to take into account that data file directory may be different between source and targer server.

4. Mount the target database with the standby controlfile.
     I then did Offline drop all those data files that are not copied
   e.g.  alter database datafile 'filename' offline drop;

5. In mount mode, issue:
    SQL> RECOVER STANDBY DATABASE UNTIL CANCEL;
    Note: this is to apply all available archived logs upto the current time manually.

6. Started the managed recovery of the standby database at target server
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

7. In the source db issue:
    SQL> ALTER SYSTEM SET log_archive_dest_2='SERVICE=ORASID1_STBY LGWR ASYNC';

8. Verify archived log are shiped and applied automatically.
    for examples:
     SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

9. Opened the standby database as a regular operational database
    a. At source issue: ALTER SYSTEM SET log_archive_dest_2=''

    b. At target, cancel Redo Apply:
        ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    c. At target issue: alter database activate physical standby database
    d. ALTER DATABASE OPEN;
    e. Drop those tablespace not copied and needed in the target server.

Standby log gap issue solved

We had a physical standby database that is behind the primary database for several days. We found that there were gaps. So we restored the archived logs from tape by RMAN to their original destination. Oracle is smart enough to automatically fetch those archived logs to the destination. And we don't need to register them at all on the standby ( our environment is 10g).

However, we found the MRP0 process status is always WAIT_FOR_LOG when issue the following sql:

SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM
V$MANAGED_STANDBY

Later I found out that in the alert log, there is a line showing:

Media Recovery Delayed for 479 minute(s) (thread 1 sequence 52361)

I reallized we have used the DELAY=480 attribute of the LOG_ARCHIVE_DEST_2 initialization parameter on the primary database to delay applying archived redo log files to the standby database.

So I tryied the following command which I found in the documentaton on the standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

Oracle started the redo apply immediately. By this way we are able to catch up and solve this log missing and apply lag issue.

Troubleshooting ORA-16191 : Primary log shipping client not logged on standby

I recieved an error ORA-16191 from email alert tonight when I was on-call. I searched the internet immediately, found that:

---
ORA-16191: Primary log shipping client not logged on standby
Cause: An attempt to ship redo to standby without logging on to standby or with invalid user credentials.
Action: Check that primary and standby are using password files and that both primary and standby have the same SYS password. Restart primary and/or standby after ensuring that password file is accessible and REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to SHARED or EXCLUSIVE
---

I checked the alert log file of the primary database, found that the alert starting from 1:18

Tue Sep 23 01:18:15 2008
Error 1017 received logging on to the standby
------------------------------------------------------------
RA-16191: Primary log shipping client not logged on standby
PING[ARC3]: Heartbeat failed to connect to standby 'PS4008A.world'. Error is 16191.
Tue Sep 23 01:23:16 2008
Error 1031 received logging on to the standby
Tue Sep 23 01:23:16 2008
Errors in file /logs/ORACLE/MYDBNAME/bdump/pphi08a_arc3_2846.trc:
ORA-01031: insufficient privileges
PING[ARC3]: Heartbeat failed to connect to standby 'PS4008A.world'. Error is 1031.

Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------

I also checked the alert log file of the standby database, find that the someone probably was doing shutdown/start the standby db

-----
Tue Sep 23 02:00:33 2008
Physical Standby Database mounted.
Completed: alter database mount standby database
Tue Sep 23 02:00:33 2008
Physical Standby Database mounted.
Completed: alter database mount standby database
Tue Sep 23 02:01:26 2008
alter database recover managed standby database parallel 16 disconnect
Tue Sep 23 02:01:26 2008
alter database recover managed standby database parallel 16 disconnect
---

The timestamp of the pmon process also indicated it was just started tonight.

$ ps -ef  grep pmon
   oracle 23059     1  0 01:59:26 ?         0:00 ora_pmon_PS4008A

Another team member explained to me that we change the SYS password every 2 or 3 months, however,
when we change for the primary database, we don't change for the standby database. So after standby db down and up, Oracle trying to connect the standby using the new password, but the password file in the standby db site still contains old password. This is the reason that I saw the error.

Based on what he said and the problem observed, it looks to me that somehow there is a "connection" from primary db to the standby db using the password in the password files and the two password files have to be in sync to enable such a connection. When we changed the sys password in the primary only, the password files are out of sync. So when standby is bounced, this "connection" need to be re-established but it can not because the out of sync situation.

I thus used the "orapwd" command to create a new password file on the standby site. Problem resolved. No such alerts recieved again.

Log Apply Service

??> What this means: Archived redo data is not available for log apply services until a log switch occurs on the primary database.

DS: suppose the current archived redo sequence is 75, the last applied archived redo seq# maybe 74 (not 75), only next time the log swtich occurs on the primary db, the 75 will be available for being applied

Task List: Configuring Log Apply Services for Physcial Standy Databases
1. Start the standby instance and mount the standby database.
SQL> startup nomount;
SQL> alter database mount standby database;

2. Enable managed recovery or read-only operations
- to start a foreground session:
SQL> alter database recover managed standby database;

- to start a background process:
SQL> alter database recover managed standby database disconnect;

- stop log apply services
SQL> alter database recover managed standby database cancel;

3. If performing managed recovery operations, set initilization parameters to automatically resolve archive gaps.

4. Monitor log apply services.

(1) verify log apply services are initiated correctly
SQL> select process, status, thread#, sequence#, block#, blocks
2> from v$managed_standby;

(2) v$archive_dest_status


An archive gap is a range of archived redo logs created whenever the standby system is unable to receive the next archived redo log generated by the primary database

e.g network becomes unavailable

Every minute, the primary database polls its standby databases to see if there is a gap in the sequence of archived redo logs. The polling between the primary and standby databases is sometimes referred to as a heartbeat. The primary database polls the standby databases serially.

To determine if there is an archive gap on the physical standby database:
SQL> select * from v$archive_gap;

6.5 Monitoring Log Apply Services for Physical Standby Databases
7/20/06 10:37 AM practice:

1. accessing the v$managed_standby fixed view to monitor log apply and log transport activity at standby site

3 Accessing the V$ARCHIVED_LOG Fixed View
to show all the archived redo logs received from the primary database.

SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,
2> NEXT_CHANGE# FROM V$ARCHIVED_LOG;

??> under creator column, I see 'ARCH' and ' FGRD', what the FGRD stands for

OraFAQ Forum Reading Notes - Data Guard

OraFAQ Forum Reading Notes - Data Guard
( http://www.orafaq.com/forum/f/12/0/ )

Re: How to disable transfers of LOGS to standby database

Try:
SQL> ALTER SYSTEM SET log_archive_dest_state_2 = DEFER;
System altered.
DEFER = Specifies that valid destination information and attributes are preserved, but the destination is excluded from archiving operations until re-enabled

SUBJECT: log_sequence column in v$archive_dest

In my dataguard setup, in which 2 primary RAC instances are sending archive logs to a Physical standby database, the log_Sequence value in V$ARCHIVE_DEST view is not getting updated.

1 select ads.dest_id,max(sequence#) "Current Sequence",
2 max(log_sequence) "Last Archived"
3 from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
4 where ad.dest_id=al.dest_id
5 and al.dest_id=ads.dest_id
6* group by ads.dest_id

DEST_ID Current Sequence Last Archived
---------- ---------------- -------------
1 38743 38743 2 38743 0 3 38726 0
There is no problem with the dataguard, and it applies the archive logs correctly and is in sync with PROD.
Our dataguard monitoring script checks the archive log gap based on the values in this column,and METALINK also says the same thing.
From Metalink: (Note: 241374.1)Quote:
-- The following query will determine the current sequence number
-- and the last sequence archived.

If remotely archiving using the-- ARCH process then the archived sequence should be equal to the-- current sequence. The applied sequence information is updated at-- log switch time.

select ads.dest_id,max(sequence#) "Current Sequence",max(log_sequence) "Last Archived"from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads where ad.dest_id=al.dest_id and al.dest_id=ads.dest_id group by ads.dest_id;
Would you please throw some light, as why this is not getting updated.

// -----------------------------------------------------------------------
Q: we are planning a switchover making the standby as the primary.We will not be making the old primary db to standby. Application testers want old primary back as primary and test the application with both old primary and new primary.In this case, How can I make the old primary back to a normal PROD database?? After running, "Alter database commit to switchover to physical standby", it will create the standby control file, right?? So is the control file overwritten in this case?? Do I need to recreate the control file ??

A:Try to do a fail-over instead of a switch-over. After a fail-over you should have 2 primary databases with no data guard.
( messeage read till 16-Jun-06)

--------------------------
SUBJECT: 2x standby db - 1 using redo apply & 1 using sql apply

HiWe are wanting to setup 2 standby databases - 1 for DR/failover using redo apply and 1 for reporting using SQL apply.The first standby db is strictly for failover as the Primary db is a 24x7 system. The second db will allow us to add Summary tables & Materialized views around the core data and remove all reports processing away from the Primary.Oracle documentation says you can have up to 9 standby databases, but doesn't say they can be populated by the 2 different methods (redo & SQL apply). I'm assuming we can, but would like to know if anyone else has successfully setup a similar scenario before investing large amounts of time.thanks

Ans:
As a short answer - yes.We have successfully tested setting up both a physical and logical standby on the same primary - basically for the same reasons you are looking to do it. We are working toward rolling this out in our production environment soon.Our environment is a 10GR1 4 node RAC cluster with physical replication to a single node DR site and soon logical standby to a single node reporting environment.