Home » RDBMS Server » Backup & Recovery » Standby in R/W mode with RMAN (Oracle 10g (10.2.0.3) Windows 2000 Server (Standard Edition SP4))
icon5.gif  Standby in R/W mode with RMAN [message #316577] Fri, 25 April 2008 18:12 Go to next message
DrNeko
Messages: 17
Registered: January 2007
Location: NJ
Junior Member
Hello,
I have a test environment that I have a physical non-managed standby database created in a virtual machine. I would like to use a physical standby database for read/write testing and reporting. I've been implementing the Data Guard Scenarios and tried to see if I can revert the activated database back to a physical standby database. However, I keep receiving the following error when I try to open the standby in read only mode:

ORA-16004: backup database requires recovery
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'


Note: The standby db starts up as follows:
startup nomount pfile=C:\oracle\product\10.2.0\db_2\database\initORCLstdby.ora;
alter database mount standby database;


Once every x minutes, a batch job ships the logs from the primary to the secondary and runs the following:

connect / as sysdba
spool c:\scripts\logapply.log
alter database recover automatic standby database until cancel;
alter database recover cancel;


Here's the steps I've done:

1. On the standby database, query the V$DATABASE view and record the current SCN of the standby database:

SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
     1804721


2. This step didn't work/isn't necessary:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active


3. Connect to the standby database as the RMAN target and create an incremental backup from the current SCN of the standby database that was recorded in step 1:

RMAN> connect target "system/password"

connected to target database: ORCL (DBID=xxxxx, not open)

RMAN> BACKUP INCREMENTAL FROM SCN 1804721 DATABASE FORMAT 'C:\tmp\ForStandby_%U' tag 'FOR STANDBY';


4. Connect to the standby database as the RMAN target, and catalog all incremental backup pieces:

RMAN> CATALOG START WITH 'c:\tmp\ForStandby_';


5. On the primary database, switch logs so the SCN of the restore point (created in step 1) will be archived on the physical standby database and ship it to the standby database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;



6. Activate the physical standby database:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> STARTUP MOUNT FORCE;
SQL> ALTER DATABASE OPEN;


Make any write changes on the activated standby database.

7. Revert the activated database back to a physical standby database.

SQL> STARTUP MOUNT FORCE;


Connect to the standby database as the RMAN target and apply incremental backups

Open another session for RMAN:
RMAN> RECOVER DATABASE NOREDO;


Back to SQL session
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;


I then run a batch file that automatically start the standby database and recover from the shipped archive logs:
startup nomount pfile=C:\oracle\product\10.2.0\db_2\database\initORCLstdby.ora;
alter database mount standby database;

SQL> alter database recover automatic standby database until cancel;
alter database recover automatic standby database until cancel
*
ERROR at line 1:
ORA-00279: change 1804724 generated at 04/25/2008 18:14:16 needed for thread 1
ORA-00289: suggestion : C:\ARCHIVELOGS\NODE2\ARCH_1_1_652990391.ARC 
ORA-00280: change 1804724 for thread 1 is in sequence #1 
ORA-00278: log file 'C:\ARCHIVELOGS\NODE2\ARCH_1_1_652990391.ARC' no longer 
needed for this recovery 
ORA-00308: cannot open archived log 
'C:\ARCHIVELOGS\NODE2\ARCH_1_1_652990391.ARC' 
ORA-27041: unable to open file 
OSD-04002: unable to open file 
O/S-Error: (OS 2) The system cannot find the file specified. 


SQL> alter database recover cancel;
alter database recover cancel
*
ERROR at line 1:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below 
ORA-01194: file 1 needs more recovery to be consistent 
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' 


I've tried another approach to step 7:
SQL> STARTUP MOUNT FORCE;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> STARTUP MOUNT FORCE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;


I get an error if I try this:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCO
NNECT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without stand


When I try to open the standby database in read only mode, I get this error:
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'


I ran SELECT SEQUENCE#, FIRST_TIME FROM V$LOG_HISTORY; and found that the standby sequence# is only one sequence behind the test primary database.

I'm stuck and would appreciate any help. Thank you.
Re: Standby in R/W mode with RMAN [message #317436 is a reply to message #316577] Thu, 01 May 2008 04:23 Go to previous messageGo to next message
prashant.pawar
Messages: 6
Registered: May 2008
Junior Member
in first reading.

1. in step 6, never activate the database using this command
oracle says "Do not use the ALTER DATABASE ACTIVATE STANDBY
DATABASE to perform a failover, because this statement may cause
data loss." check the doc.

2. If i remember correctly to switch back the to the standby mode after opening database in R/w mode. you need flashback database enabled.

3. i didn't get why you are taking incremental backup on standby. how is it going to help to switch back. May be i didn't get your question.

-Prashant
Re: Standby in R/W mode with RMAN [message #317438 is a reply to message #317436] Thu, 01 May 2008 05:40 Go to previous message
prashant.pawar
Messages: 6
Registered: May 2008
Junior Member
complete solution :
http://static7.userland.com/oracle/gems/alejandroVargas/PhysicalStandbyActivatedRead.pdf
Previous Topic: RMAN deleting ASM directory
Next Topic: Unable to connect to the Target from catalog
Goto Forum:
  


Current Time: Mon Dec 05 21:02:59 CST 2016

Total time taken to generate the page: 0.08467 seconds