Wednesday, April 15, 2009

Renaming/Dropping and recreating online redo logs and Standby logfiles

We are in process of testing Dataguard with Physical standby. After we created Primary and Standby DB's we realize what we haven't put file names as per our standard. So I got the task to rename both, online redo logs and standby logs. This what I did :

1. Following is the script to identify all log files we have
SELECT GROUP#, BYTES, 'ONLINE' AS TYPE FROM V$LOG
UNION
SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG
ORDER BY 1;
2. Make sure protection mode is MAXPERFORMANCE:
SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE

First we start working on Standby database
3. STOP redo apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
4. Set STANDBY_FILE_MANAGEMENT to MANUAL.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
5. Gather current status of all member.
SQL> select member from v$logfile;
6. Prepare command to drop standby redo logfiles.
select 'alter database drop standby logfile group ' group# ';' from v$standby_log;
7. Prepare command to drop online redo logfiles.
select 'alter database drop logfile group ' group# ';' from v$log;
8. Check the status of online redolog group.
SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CLEARING_CURRENT
3 CLEARING
2 CLEARING
We can not drop CLEARING_CURRENT status logs, for that you have sync with Primary (Easy way is, leave them for a while and move ahead with droping Primary Database redo logs, and come back to Standby and you will be able to do it).
9. Clear the log group first before drop.
ALTER DATABASE CLEAR LOGFILE GROUP 1;
10. Now drop online redo log.
alter database drop logfile group 1;
Follow the same process for rest of the groups.
11. Now drop standby redo logs, leave the one which refuses to drop.
alter database drop standby logfile group 5;
12. Now create new online redo logs.
ALTER DATABASE ADD LOGFILE GROUP 1 '/path/redo_01.dbf' SIZE 10M;
13. Now we will crate standby redo logs.
ALTER DATABASE ADD STANDBY LOGFILE '/path/redo_std_by_05.dbf' SIZE 10M REUSE;
14. Again check the current status of member.
select member from v$logfile;

Now you will see latest updated names for all files. The one which left we can do them after We are done with Primary DB.

Now on Primary Database:
15. Follow step 5,6,7 to know current status and prepare commands.
16. Drop Standby redo logs and recreate them
alter database drop standby logfile group 5;
ALTER DATABASE ADD STANDBY LOGFILE '/path/redo_std_by_05.dbf' SIZE 10M REUSE;
17. Drop online Redo logs, recrate, switch and drop next inactive one.
alter database drop logfile group 1;
ALTER DATABASE ADD LOGFILE GROUP 1 '/path/redo_01.dbf' SIZE 10M;
ALTER SYSTEM SWITCH LOGFILE;
SELECT GROUP#, STATUS FROM V$LOG;
18. Check the new status.
select member from v$logfile;

Now come back to Standby Database back and drop recreate left over online and standby redo logs.
19. Set STANDBY_FILE_MANAGEMENT to AUTO.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
20. start redo apply.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
21. Check the status of applied log again.
select sequence#, applied from v$archived_log;

Happy Troubleshooting !!!

9 comments:

Rajesh said...

Nice Work Satish. Got trapped into similar situation and this blog helped a lot :-).
Keep up the Good work.

John Goodhue said...

This really is one of the most helpful blog entries I have seen. Helped me through a very complicated situation.

Pascal said...

Hello

Why did u issue
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO'; ?

Sundeep Dubey said...

Hi Pascal,
This is for reinstate earlier situation. I mean to say to put in older state of auto management of redo logs.

Thanks
Sundeep

Pascal said...

Thanks.

Assume I have dataguard configuration.

When I add a standby redolog to production, Does oracle apply the same statement(alter database create standby redolog..) to standby???

(Since this ddl is recorded in redolog file and eventually applied to standby)

Sachin said...

REally Nice, i was able to follow note very easily

sandeep said...

Hi Sundeep,

Really a nice article .
I have a similar issue.
Primary - orc1 (RAC 2 node on ASM)
Physical Standby - lsd

I wanted to recreate my physical standby database and encountered with errors:

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

so i needed to drop and recreate my standby redo logs

Now I have doubts and need more clarification as I have no prior
experience on ASM.

1>
How many standby redologs do i have to create on my standby database?
As my primary is using 10 groups , do i have to create (10+1)* number
of threads= 22 log files?


2>
should the path of primary and standby redologs be same ?

the path of primary is " +DATA/orc1/redo*.log" "+UNDO/orc1/redo*.log"
and "+IDX/orc1/redo*.log"
or shall i create the standby as +DATA/lsd/redo*.log"
"+UNDO/lsd/redo*.log" and "+IDX/lsd/redo*.log"

When should i set my parameter log_file_name_convert ?

Sally UK said...

I've twice found this document when looking for a solution to problems with Standby logs and twice it helped me out (guess I should have remembered the first time around!). Thanks for your help. Appreciated.

Anonymous said...

One minor issue: the syntax to create the drop statements isn't quite correct. Here are the corrected ones:

select 'alter database drop logfile group ' || group# || ';' from v$log;

select 'alter database drop standby logfile group ' || group# || ';' from v$standby_log;


Also, I could not drop the CURRENT redo, even after clearing it. alter system switch logfile on the primary worked once, but the other two I had to clear and then rename. Oracle recreated them, but I was never able to drop them.