Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 28 May 2012

Fixing issues related with Online Redologs

In the light of my earlier post "Redolog performance Tuning" to fix more redolog generation and slow disk I/O i am continuing here some more concept related to redolog. May it will help you. To check that post click on the link: http://shahiddba.blogspot.com/2012/05/redolog-performance-tuning.html
Which session generating more redologs:
SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc;
SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5,6 desc;
How to fix issue when the online redo logs are missing?
If for some reason the online redo log were lost or when cloning the copy of shutdown database you don’t have the online redo logs, you would need to open the database with “RESETLOGS” option which will create the online redo logs.

If you not performed incomplete recovery and trying to start oracle with "Resetlog" oracle generate the following error.
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
-or-
After performing “RECOVER DATABASE UNTIL CANCEL” before opening the database you must start with "Resetlogs" if you start withuot resetlog Oracle will generate the following
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open










More on fixing online Redologs visit the link: http://shahiddba.blogspot.com/2012/07/how-to-deal-online-redo-log-files.html


How to add or drop online redo logs?
you can add either the same or different size groups. Here in the below example i am adding same size
SQL> SELECT GROUP#, ARCHIVED, STATUS, BYTES FROM V$LOG;
GROUP# ARC STATUS          BYTES
--------- ------- ------------      -------------
1             NO    ACTIVE   104857600
2             NO    CURRENT       104857600
3             NO    INACTIVE       104857600
Add a new log group with the same size and query v$log to show the activity status of the new log member:
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('C:\ORACLE1\ORADATA\ORCL3\REDO04.LOG', 'C:\ORACLE1\ORADATA\ORCL3\REDO04B.LOG') SIZE 100M;
Database altered.

SQL> select group#, archived, status, bytes from v$log;
GROUP# ARC STATUS ‎ BYTES
‎----- ---- -------      -------------
‎1 ‎ NO CURRENT ‎ ‎104857600‎
‎2 ‎ NO INACTIVE ‎ ‎104857600‎
‎3 ‎ NO INACTIVE ‎ ‎104857600
‎4 ‎ YES UNUSED ‎ ‎104857600‎

Droping online redo logs, if the redo log is Status is CURRENT or ACTIVE, the on-line redo log cannot be dropped. When trying to drop CURRENT online redo log, Oracle will return "ORA-01623: log 1 is current log for instance orcl3 (thread 1) – cannot drop" error message. When trying to drop online redo log in ACTIVE status, Oracle will return "ORA-01624: log 1 needed for crash recovery of instance orcl3 (thread 1)"
To drop the online redo log group:
SQL> select * from v$logfile where group# = 3;
GROUP# STATUS TYPE MEMBER IS
---------- --- ---------------- ----------
3 ONLINE C:\ORACLE1\ORADATA\ORCL3\REDO03.LOG NO
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
Note: The files in the group has to be dropped manually.

0 comments:

Post a Comment