Oracle Database Discussions

Hi All,

I have Physical Standby Database on Sun Solaris with Oracle 10g (10.2.0.4) & on which i got the Status in standby Box for the online Redo log is CLEARING_CURRENT ,CLEARING
_----------------------------

STANDby Box

SQL> select group#,status from v$log;

GROUP# STATUS
----------------
1 CLEARING
2 CLEARING
3 CLEARING_CURRENT
4 CLEARING
5 CLEARING
########################
Select group#,member from v$log; ( Physically not available on standby Box)

GROUP#----------MEMBER
1 /bmcpdslog/BMCPDS/logs/redo1b.log
1 /bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/redo1a.log
2 /bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/redo2a.log
2 /bmcpdslog/BMCPDS/logs/redo2b.log
3 /bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/redo3a.log
3 /bmcpdslog/BMCPDS/logs/redo3b.log
4 /bmcpdslog/BMCPDS/logs/redo4b.log
4 /bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/redo4a.log
5 /bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/redo5a.log
5 /bmcpdslog/BMCPDS/logs/redo5b.log

10 rows selected.

############################


and on PROD Box all the redo log files are available & in good status .

SQL> select group#,status from v$log;

GROUP# STATUS
----------------
1 ACTIVE
2 CURRENT
3 ACTIVE
4 ACTIVE
5 ACTIVE
##############################

Select group#,member from v$log;

GROUP#----------MEMBER
1 /bmcpdslog/BMCPDS/logs/redo1b.log
1 /bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/redo1a.log
2 /bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/redo2a.log
2 /bmcpdslog/BMCPDS/logs/redo2b.log
3 /bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/redo3a.log
3 /bmcpdslog/BMCPDS/logs/redo3b.log
4 /bmcpdslog/BMCPDS/logs/redo4b.log
4 /bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/redo4a.log
5 /bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/redo5a.log
5 /bmcpdslog/BMCPDS/logs/redo5b.log

10 rows selected.

################################

Now the Issue on Standby Box.

i have no online Redo log file available in the require Location & when i am trying to open the database in READ Write mode its require the Redo logs & when i try to rebuild the redos at the require location its giving me the error.

Please let me know what is suitable step for clearing the status to Active & how to rebuild the Redo logs in the Standby with the Same Name .

i have followed the Metalink Doc ID 740675.1 (part 3) with that i have recreated the New standby controlfile & aftert that below is the scenarios.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
###########
SQL> startup nomount
ORACLE instance started.

Total System Global Area 629145600 bytes
Fixed Size 2030752 bytes
Variable Size 163578720 bytes
Database Buffers 427819008 bytes
Redo Buffers 35717120 bytes
SQL> create spfile from pfile='/apps/Datastore/ora10g/db_1/dbs/initBMCPDSSTB.ora';

File created.
################3
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
################
SQL> startup nomount
ORACLE instance started.

Total System Global Area 629145600 bytes
Fixed Size 2030752 bytes
Variable Size 163578720 bytes
Database Buffers 427819008 bytes
Redo Buffers 35717120 bytes
SQL> alter database mount ;

Database altered.

SQL> select group#,status from v$log;

GROUP# STATUS
----------------
1 CLEARING
2 CLEARING_CURRENT
5 ACTIVE
4 ACTIVE
3 ACTIVE

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1:
'/bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/redo1a.log'
SQL> !
SecondaryDB>cd /bmcpds/Datastore/ora10g/oradata/BMCPDS/logs/
SecondaryDB>ls -lrt
total 0
SecondaryDB>exit

SQL> alter database add standby logfile group 1 '/bmcpdslog/BMCPDSSTB/logs/Stand1.log' size 300m
2 ;
alter database add standby logfile group 1 '/bmcpdslog/BMCPDSSTB/logs/Stand1.log' size 300m
*
ERROR at line 1:
ORA-01184: logfile group 1 already exists
SQL> alter database add standby logfile group 1 '/bmcpdslog/BMCPDSSTB/logs/Stand1.log' size 300m reuse;
alter database add standby logfile group 1 '/bmcpdslog/BMCPDSSTB/logs/Stand1.log' size 300m reuse
*
ERROR at line 1:
ORA-01184: logfile group 1 already exists


After few mins group 5 is also come under the Clearing state Why ?

SQL> select group#,status from v$log;

GROUP# STATUS
----------------
1 CLEARING
2 CLEARING
5 CLEARING_CURRENT
4 ACTIVE
3 ACTIVE
Now please let me know why the Group1,2 is in Clearing State & how can i resolve this issue .


#############


1) . Now i have created the standby redo log with this command .

alter database add standby logfile '/bmcpdslog/BMCPDSSTB/logs/standby03.log' size 300m;
alter database add standby logfile '/bmcpdslog/BMCPDSSTB/logs/standby04.log' size 300m;
alter database add standby logfile '/bmcpdslog/BMCPDSSTB/logs/standby05.log' size 300m;
alter database add standby logfile '/bmcpdslog/BMCPDSSTB/logs/standby06.log' size 300m;


But could you please clear my confusion ,why ie was giving me error when i trying with this command ?
SQL> alter database add standby logfile group 1 '/bmcpdslog/BMCPDSSTB/logs/Stand1.log' size 300m
2 ;
alter database add standby logfile group 1 '/bmcpdslog/BMCPDSSTB/logs/Stand1.log' size 300m
*
ERROR at line 1:
ORA-01184: logfile group 1 already exists
SQL> alter database add standby logfile group 1 '/bmcpdslog/BMCPDSSTB/logs/Stand1.log' size 300m reuse;
alter database add standby logfile group 1 '/bmcpdslog/BMCPDSSTB/logs/Stand1.log' size 300m reuse
*
ERROR at line 1:
ORA-01184: logfile group 1 already exists

And In the scenario of READ WRITE if my Primary Box is Shutdown & customer want Standby Box in READ WRITE mode then what about online redo logs . because on that time we have only standby redo logs . Then how can we proceed further in that scenario.
2) . i checked v$standby_log;

SQL> select group#,status from v$standby_log;

GROUP# STATUS
----------
6 ACTIVE
7 ACTIVE
8 UNASSIGNED
9 UNASSIGNED
10 UNASSIGNED
11 UNASSIGNED

6 rows selected.

Why its started with 6 series , where are the others starting from 1 to 5 ? How can i get the starting series of standby redo ? And these standby redo are mandatory only on standby Box or its also need to be created on PROD Box ?