resumable session

08:21:50 159 67 TEST@orcl> alter session enable resumable timeout 60;
ORA-01031: insufficient privileges
--
08:29:00 159 4294967295 SYS@orcl> grant resumable to test;
Grant succeeded.
--
08:29:22 159 69 TEST@orcl> alter session enable resumable timeout 60;
Session altered.

online validate structure

-- error will raise if something wrong
analyze table test validate structure online;

scn controlfile/datafile

set lines 300
column name format a51
select d.name, d.checkpoint_change# control_file_change, h.checkpoint_change# file_change
from v$datafile d, v$datafile_header h
where h.file# = d.file#
order by 1
/

1) control_file_change = file_change => database can be opened
2) control_file_change > file_change => recovery is needed
3) control_file_change < file_change => recovery using backup controlfile needed

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