lost logfile ORA-00362, STALE status for logfile

--alert.log
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'
--
22:20:58 150 4294967295 SYS@orcl> alter database drop logfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG';
alter database drop logfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'
*
ERROR at line 1:
ORA-01514: error in log specification: no such log
ORA-01517: log member: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'
--
22:26:18 150 4294967295 SYS@orcl> select group#, member, status from v$logfile;

GROUP# | MEMBER


| STATUS
------------------------------------ | ----------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------ | -----------
3.0 | C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG


| STALE
2.0 | C:\PROJECT\OCP\REDO2.LOG


|
1.0 | C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG


| INVALID
1.0 | C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01_1.LOG


| STALE
3.0 | C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01_3.LOG


| STALE
4.0 | C:\PROJECT\OCP\DB_CREATE_ONLINE_DEST\ORCL\ONLINELOG\O1_MF_4_6HLR64SC_.LOG


|

6 rows selected.
--
alter system switch logfile;
--
22:28:39 150 4294967295 SYS@orcl> alter database drop logfile member 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG';

Database altered.
22:33:44 150 4294967295 SYS@orcl> alter database add logfile member 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG' to group 1;

Database altered.

Elapsed: 00:00:03.75

resetlogs

1) archives the current online redo logs
2) resets the log sequence number to 1
3) gives the online redo logs a new time stamp and SCN

ORA-19809: limit exceeded for recovery files

In such situation we need to deal with the error fast as possilbe as database in freeze state and could potentially abort if no action has taken. Bellow I will show 2 ways to do it in fast manner as follows:

1) If we need archived logs (production)
Backup all archivelogs (fra or not) by 1 archive log (delete) to temporary destination
rman target /
backup archivelog all to destination "C:\Project\testdb\bkp\fra" filesperset 1 delete input;


2) If we do not need archived logs to be backed up (test/dev system)
os: delete old archivelogs
rman:
crosscheck archivelog all;
delete expired archivelog all;

cpu

cpu

Current session timezone

-- Get
select sessiontimezone from dual;
--Db timezone
select dbtimezone from dual;
-- Set
set ora_sdtz=db_tz
or
alter session set time_zone=dbtimezone;
--change db time_zone
16:51:23 SQL> alter database set time_zone='+01:00';

Database altered.
16:55:11 SQL> create table t_lt (id timestamp with local time zone);

Table created.

Elapsed: 00:00:00.64
16:55:29 SQL> alter database set time_zone='+01:00';

Database altered.

Elapsed: 00:00:00.06
16:55:31 SQL> alter database set time_zone='+02:00';

Database altered.
16:56:16 SQL> insert into t_lt select systimestamp from dual;

1 row created.

Elapsed: 00:00:00.07
16:56:33 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
16:56:35 SQL> alter database set time_zone='+01:00';
alter database set time_zone='+01:00'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

dbms_scheduler using as dbms_job.submit

exec dbms_scheduler.create_job(job_name => DBMS_SCHEDULER.GENERATE_JOB_NAME, job_type => 'plsql_block', job_action => 'begin raise_application_error(-20000, ''error_in_alert''); end;', start_date => sysdate, enabled => true);

ora-04031

orafaq

set lines 300
select to_char(to_number(v.KSPPSTVL), '999999999') min_to_go_to_reserved, to_char(s.request_failures, '99999999') ora04031
, to_char(s.last_failure_size, '999999999999') failure_size, trunc(s.free_space/1024/1024) free_reserved_m, max_free_size largest
, to_char(stat.bytes, '999999999') shs_pool_free
from x$ksppi n,
x$ksppsv v,
v$shared_pool_reserved s
, v$sgastat stat
where n.indx = v.indx
and n.ksppinm = '_shared_pool_reserved_min_alloc'
and stat.pool = 'shared pool'
and stat.name = 'free memory'
;

Issue if REQUEST_FAILURES > 0:
Shared pool - fragmentation? or low memory
if LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
else Shared pool reserved -
fragmentation?
if max_free_size < failure_size and free_space > failure_size
or low memory

-- Free shared memory hist
select t.BEGIN_INTERVAL_TIME, s.bytes
from DBA_HIST_SGASTAT s,
dba_hist_snapshot t
where s.pool = 'shared pool' and s.name = 'free memory'
and t.snap_id = s.snap_id
order by 1;