豕 覲糾規 襴れ 願屋覦覯(SCN 覈 襴)
語. OCM 豺危譽 覩殊壱企.
http://cafe.daum.net/oraclemanager
SCN 語 蟆曙一 覲糾規 覦覯碁一.
襷 蠍 覲糾規 レ海 蟆曙一 蟆讌.
朱 覲糾規 螳 ..
願碓讌襦 企至 覲糾規伎 れ 蠍
覦. 蠎 襷 伎殊.
朱 覲糾規 覃 .
.. 覲糾規 危 蠎 伎 蟆..
DB燕 export import 蟆 蟠ル給.
蠏碁Μ螻 tempfile 豢螳 蟆 讌 襷蟲.
undo 蟾襴螻 襦 燕 .
豕 覲糾規襴れ.. 蠏碁 伎 蟆.. system tablespace 覓伎＾蟇 覦煙企伎 . system tablespace螳 蟾讌覃 'dul' 語 牛伎 觚襦 曙朱 覲糾規 覿螳ロり れ. system tablespace bootstrap 蟾讌覃 覓 る 覦煙企朱 object襯 燕 dictionary 覲願 朱 轟壱 覈詞郁讌..
襷讌襷 襷 k4 朱 企 一危磯
企 dictionary 覲伎 蠍一危 殊 蟆讌.
system 襾殊 cp 覦煙螻. 企襷り datafile 覦煙る伎..
_corrupted_rollback_segments=true => rollback segment螳 蟾譟, commit り る狩伎 , _offline_rollback_segments 螳襦 rollback segment襯 offline (_corrupted~, _offline~ 螳伎
_allow_resetlogs_corruption=true =>redo log 螻覯 伎 一危壱殊 SCN 伎 蟇磯 recover archive襯 谿場 覈詩伎 fail 蟆曙, 讀 current redo log襯 牛伎 覲糾規螳 覿螳ロ 蟆曙, 旧襦 current redo log襯 reset log り open 朱誤
ORA- 10501 event襯 牛伎 adjust scn 螳 覯 蟾譟
蟆曙 覯り覃伎 覲糾規 覦覯.
10501, 00000, "periodically check selected heap"
// *Cause:
// *Action:
// Level: 0x01 PGA
// 0x02 SGA
// 0x04 UGA
// 0x08 current call
// 0x10 user call
// 0x20 large allocation pool
begin backup 讌 螻 cp襦 覲旧伎 覦煙 蟆曙 覲糾規 覦覯 , archive 企 SCN 覈 襴
1. 碁 cp襦 覦煙
vi kkk.sql
create table k1 tablespace data02 as select * from dba_segments;
insert into k1 select * from k1;
commit;
create table k2 tablespace data01 as select * from dba_objects;
insert into k2 select * from k2;
insert into k1 select * from k1;
commit;
create table k3 tablespace users as select * from dba_extents;
insert into k3 select * from k3;
commit;
vi kkk2.sql
insert into k1 select * from k1;
insert into k2 select * from k2;
insert into k3 select * from k3;
commit;
SQL> @kkk
@kkk2
@kkk2
@kkk2
@kkk2
@kkk2
@kkk2
@kkk2
SQL> !cp /data1/oradata/PROD/*.dbf PROD_CP
SQL> alter database backup controlfile to '/data1/oradata/PROD_CP/control01.ctl';
SQL> !cp /data1/oradata/PROD/system01.dbf PROD_CP
SQL> create table k4 tablespace users as select * from dba_extents;
- archive
mv archive archive3
2. cp襦 覦煙 蟆 restore(datafile, controlfile襷 譟伎, redo log )
mv PROD PROD_CP_ORG
mv PROD_CP PROD
3. recovery status
SQL> startup
ORACLE instance started.
Total System Global Area 101799240 bytes
Fixed Size 456008 bytes
Variable Size 50331648 bytes
Database Buffers 50331648 bytes
Redo Buffers 679936 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
4. 覲糾規
archive 螳 朱襦 controlfile trace RESETLOGS ARCHIVELOG => RESETLOGS NOARCHIVELOG襦 覲蟆
(1) 朱 覲糾規 朱 archive螳
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 5531180 generated at 11/11/2005 00:20:07 needed for thread 1
ORA-00289: suggestion : /data1/oradata/archive/1_37.arc
ORA-00280: change 5531180 for thread 1 is in sequence #37
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/data1/oradata/archive/1_37.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf'
(2) resetlog open 覿螳ロ
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf'
(3) hidden parameter
initPROD.ora 殊 襯 豢螳
_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3 $,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
_corrupted_rollback_segments=true
_allow_resetlogs_corruption=true
(4) controlfile
SQL> alter database backup controlfile to trace;
SQL> show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /data1/oradata/admin/PROD/udump
- 碁伎れ RESETLOGS ARCHIVELOG => RESETLOGS NOARCHIVELOG 襦 覲蟆
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 3
MAXLOGHISTORY 337
LOGFILE
GROUP 1 '/data1/oradata/PROD/log01a.log' SIZE 50M,
GROUP 2 '/data1/oradata/PROD/log02a.log' SIZE 50M,
GROUP 3 '/data1/oradata/PROD/log03b.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/data1/oradata/PROD/system01.dbf',
'/data1/oradata/PROD/undotbs.dbf',
'/data1/oradata/PROD/users01.dbf',
'/data1/oradata/PROD/data01_02.dbf',
'/data1/oradata/PROD/data02_02.dbf',
'/data1/oradata/PROD/data01_03.dbf',
'/data1/oradata/PROD/data01_04.dbf',
'/data1/oradata/PROD/data01_01.dbf',
'/data1/oradata/PROD/data02_01.dbf',
'/data1/oradata/PROD/users02.dbf'
CHARACTER SET KO16KSC5601;
(5) DB蠍磯 controlfile
SQL> startup nomount
ORACLE instance started.
Total System Global Area 101799240 bytes
Fixed Size 456008 bytes
Variable Size 50331648 bytes
Database Buffers 50331648 bytes
Redo Buffers 679936 bytes
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 16
4 MAXLOGMEMBERS 2
5 MAXDATAFILES 30
6 MAXINSTANCES 3
7 MAXLOGHISTORY 337
8 LOGFILE
9 GROUP 1 '/data1/oradata/PROD/log01a.log' SIZE 50M,
10 GROUP 2 '/data1/oradata/PROD/log02a.log' SIZE 50M,
11 GROUP 3 '/data1/oradata/PROD/log03b.log' SIZE 50M
12 -- STANDBY LOGFILE
13 DATAFILE
14 '/data1/oradata/PROD/system01.dbf',
15 '/data1/oradata/PROD/undotbs.dbf',
16 '/data1/oradata/PROD/users01.dbf',
17 '/data1/oradata/PROD/data01_02.dbf',
18 '/data1/oradata/PROD/data02_02.dbf',
19 '/data1/oradata/PROD/data01_03.dbf',
20 '/data1/oradata/PROD/data01_04.dbf',
21 '/data1/oradata/PROD/data01_01.dbf',
22 '/data1/oradata/PROD/data02_01.dbf',
23 '/data1/oradata/PROD/users02.dbf'
24 CHARACTER SET KO16KSC5601;
Control file created.
(6) db襯 resetlogs openろ DB螳 DOWN
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 5531180 generated at 11/11/2005 00:20:07 needed for thread 1
ORA-00289: suggestion : /data1/oradata/archive/1_37.arc
ORA-00280: change 5531180 for thread 1 is in sequence #37
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
(7) alert log 覿
- system tablespace 覃覈襴襯 蟯襴 る狩 企朱 bootstrap 蟾讌
- 600 [4000] 螳 覦, hidden parameter rollback semgent襯 offline , corrupt
貅郁鍵 覓語 覦 蟆朱
ARC1: Media recovery disabled
Fri Nov 11 00:48:00 2005
Errors in file /data1/oradata/admin/PROD/udump/prod_ora_12450.trc:
ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], []
Fri Nov 11 00:48:04 2005
Errors in file /data1/oradata/admin/PROD/udump/prod_ora_12450.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], []
Fri Nov 11 00:48:04 2005
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 12450
ORA-1092 signalled during: alter database open resetlogs...
(8) DB蠍磯 覦 controlfile , redo襯 覈 讌, controlfile
SQL> startup nomount
ORACLE instance started.
Total System Global Area 101799240 bytes
Fixed Size 456008 bytes
Variable Size 50331648 bytes
Database Buffers 50331648 bytes
Redo Buffers 679936 bytes
SQL> !rm /data1/oradata/PROD/*.log
SQL> !rm /data1/oradata/PROD/*.ctl
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 16
4 MAXLOGMEMBERS 2
5 MAXDATAFILES 30
6 MAXINSTANCES 3
7 MAXLOGHISTORY 337
8 LOGFILE
9 GROUP 1 '/data1/oradata/PROD/log01a.log' SIZE 50M,
10 GROUP 2 '/data1/oradata/PROD/log02a.log' SIZE 50M,
11 GROUP 3 '/data1/oradata/PROD/log03b.log' SIZE 50M
12 -- STANDBY LOGFILE
13 DATAFILE
14 '/data1/oradata/PROD/system01.dbf',
15 '/data1/oradata/PROD/undotbs.dbf',
16 '/data1/oradata/PROD/users01.dbf',
17 '/data1/oradata/PROD/data01_02.dbf',
18 '/data1/oradata/PROD/data02_02.dbf',
19 '/data1/oradata/PROD/data01_03.dbf',
20 '/data1/oradata/PROD/data01_04.dbf',
21 '/data1/oradata/PROD/data01_01.dbf',
22 '/data1/oradata/PROD/data02_01.dbf',
23 '/data1/oradata/PROD/users02.dbf'
24 CHARACTER SET KO16KSC5601;
Control file created.
SQL>
SQL> alter session set events '10015 trace name adjust_scn level 1';
Session altered.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 5531188 generated at 11/11/2005 00:58:07 needed for thread 1
ORA-00289: suggestion : /data1/oradata/archive/1_1.arc
ORA-00280: change 5531188 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
(9) adjust_scn level り覃伎
QL> startup mount
ORACLE instance started.
Total System Global Area 101799240 bytes
Fixed Size 456008 bytes
Variable Size 50331648 bytes
Database Buffers 50331648 bytes
Redo Buffers 679936 bytes
Database mounted.
SQL>
SQL> alter session set events '10015 trace name adjust_scn level 2';
Session altered.
SQL> recover database;
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> alter database open;
Database altered.