recover using backup controlfile

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/osrecov.htm#i1011129
--
08:43:53 155 4294967295 SYS@orcl> alter database backup controlfile to 'c:\project\ocp\new_controlfile.ctl';

Database altered.
--
RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 289406976 bytes

Fixed Size 1248576 bytes
Variable Size 109052608 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes

RMAN> restore controlfile from 'c:\project\ocp\new_controlfile.ctl';

Starting restore at 11-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 11-DEC-10

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1
--
08:48:21 156 4294967295 SYS@orcl> select controlfile_type from v$database;

CONTROL
-------
BACKUP

1 row selected.

Elapsed: 00:00:00.14
08:48:31 156 4294967295 SYS@orcl> recover database using backup controlfile;
ORA-00279: change 545737 generated at 12/11/2010 08:42:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_11\O1_MF_1_1_%U_.ARC
ORA-00280: change 545737 for thread 1 is in sequence #1


08:48:42 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log 'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_11\O1_MF_1_1_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) ═х єфрхЄё  эрщЄш єърчрээ√щ Їрщы.


08:48:53 156 4294967295 SYS@orcl> recover database using backup controlfile;
ORA-00279: change 545737 generated at 12/11/2010 08:42:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_11\O1_MF_1_1_%U_.ARC
ORA-00280: change 545737 for thread 1 is in sequence #1


08:48:55 Specify log: {=suggested | filename | AUTO | CANCEL}
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'


08:49:16 156 4294967295 SYS@orcl> recover database using backup controlfile;
ORA-00279: change 545737 generated at 12/11/2010 08:42:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_11\O1_MF_1_1_%U_.ARC
ORA-00280: change 545737 for thread 1 is in sequence #1


08:49:21 Specify log: {=suggested | filename | AUTO | CANCEL}
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'


08:49:28 156 4294967295 SYS@orcl> recover database using backup controlfile;
ORA-00279: change 545737 generated at 12/11/2010 08:42:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_11\O1_MF_1_1_%U_.ARC
ORA-00280: change 545737 for thread 1 is in sequence #1


08:49:32 Specify log: {=suggested | filename | AUTO | CANCEL}
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Log applied.
Media recovery complete.
--
08:50:08 156 4294967295 SYS@orcl> alter database open resetlogs;

Database altered.

re-create controlfile, readonly tablespaces

link

enq: TX - contention, alter tablespace read only

-- hang
RMAN> sql 'alter tablespace t_i read only';

sql statement: alter tablespace t_i read only
-- why
select ash.event
, ash.sql_id
, ash.session_id
, ash.blocking_session
, ash.SAMPLE_TIME
from v$active_session_history ash
where ash.event like '%contention%'
order by ash.sample_time
;

OSD-04006: ReadFile() failure, unable to read from file

-- After media failure
03:03:25 150 4294967295 SYS@orcl> select * from t_t where object_name = 'test';
select * from t_t where object_name = 'test'
*
ERROR at line 1:
ORA-01115: IO error reading block from file 15 (block # 13)
ORA-01110: data file 15: 'F:\OCP\T_I.DBF'
ORA-27091: unable to queue I/O
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
-- But file online
03:03:46 150 4294967295 SYS@orcl> select status, error from v$datafile_header where file# = 15;

STATUS | ERROR
----------- | -----------------------------------------------------------------
ONLINE | CANNOT READ HEADER
-- Put to offline
RMAN> sql 'alter database datafile 15 offline';

sql statement: alter database datafile 15 offline
-- Try back and cool! file just needs recovery
RMAN> sql 'alter database datafile 15 online';

sql statement: alter database datafile 15 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 12/11/2010 03:09:42
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 15 online
ORA-01113: file 15 needs media recovery
ORA-01110: data file 15: 'F:\OCP\T_I.DBF'

03:10:04 150 4294967295 SYS@orcl> select status, error from v$datafile_header where file# = 15;

STATUS | ERROR
----------- | -----------------------------------------------------------------
OFFLINE |

-- Recover
03:09:51 150 4294967295 SYS@orcl> recover datafile 15;
Media recovery complete.

-- And back to online
RMAN> sql 'alter database datafile 15 online';

sql statement: alter database datafile 15 online

-- Get working statement again :)
03:10:38 150 4294967295 SYS@orcl> select * from t_t where object_name = 'test';

no rows selected

Elapsed: 00:00:00.10

Execution Plan
----------------------------------------------------------
Plan hash value: 476242662

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_T | 1 | 177 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_I | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_NAME"='test')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
370 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

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;