_覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 豕覲糾規襴れ願屋覦覯
Data Analysis(R)
Data Analysis(Python)
Statistics
Data Warehouse
Database System
SQL Server
Trino
Hive
Hadoop
Regular Expressions
Tools
Money
Working Smart
Blog
Booklog
Philosophy
Link
Miscellaneous
__ edit




Describe 豕覲糾規襴れ願屋覦覯 here

豕 覲糾規 襴れ  願屋覦覯(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.
鏤
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

覓伎瑚 螻 苦 覦覯 谿場願 覓願 蠍 蟲れ 谿場碁. (朱 )