Fixing issues related with Online Redologs

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:ORACLE1ORADATAORCL3REDO04.LOG’, ‘C:ORACLE1ORADATAORCL3REDO04B.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:ORACLE1ORADATAORCL3REDO03.LOG NO
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
Note: The files in the group has to be dropped manually.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s