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
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.
