오라클/Backup & Recovery
Oracle 장애 유형과 문제해결
※ SCENARIO 0 : Tablespace의 조작
① 새로운 Tablespace Create
[/DBA3/DBA/dba숫자]svrmgrl
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> create tablespace test
2> datafile '/DBA3/DBA/dba숫자/u01/test_01.dbf'
3> size 10k;
SVRMGR> select tablespace_name, bytes, file_name from dba_data_files;
② Table Create
SVRMGR> create table test(name char(30))
2> tablespace test
3> storage(initial 4k);
③ Table에 Row들을 Insert
SVRMGR> @?/labs/test100
Statement processed.
SVRMGR> @?/labs/test100
ORA-01653: unable to extend table SYS.TEST by 5 in tablespace TEST
ORA-06512: at line 6
④ Tablespace를 늘인다
SVRMGR> alter tablespace test
2> add datafile '/DBA3/DBA/dba숫자/u01/test_02.dbf' size 30k;
Statement processed.
SVRMGR> @?/labs/test100
Statement processed.
SVRMGR> select tablespace_name, bytes, file_name from dba_data_files;
SVRMGR> !ls -la $ORACLE_HOME/u01 --> "test_02.dbf" file 크기 확인
total 22563
drwxrwxr-x 2 dbamgr dba 512 Feb 24 13:08 .
drwxrwxr-x 43 dba숫자 dba 1024 Feb 20 23:21 ..
-rw-rw---- 1 dba숫자 dba 514048 Feb 24 13:05 index_01.dbf
-rw-rw---- 1 dba숫자 dba 155648 Feb 24 13:05 log1a.rdo
-rw-rw---- 1 dba숫자 dba 155648 Feb 24 13:09 log2a.rdo
-rw-rw---- 1 dba숫자 dba 155648 Feb 24 12:55 log3a.rdo
-rw-rw---- 1 dba숫자 dba 10487808 Feb 24 13:09 system.dbf
-rw-rw---- 1 dba숫자 dba 12288 Feb 24 13:09 test_01.dbf
-rw-rw---- 1 dba숫자 dba 32768 Feb 24 13:09 test_02.dbf
⑤ Datafile의 크기를 늘인다
SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u01/test_02.dbf' resize 50k;
Statement processed.
SVRMGR> select tablespace_name, bytes, file_name from dba_data_files;
SVRMGR> !ls -la $ORACLE_HOME/u01
⑥ Test가 끝나면 Drop
SVRMGR> drop tablespace test;
drop tablespace test
*
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
SVRMGR> drop tablespace test including contents;
Statement processed.
SVRMGR> select tablespace_name, bytes, file_name from dba_data_files;
SVRMGR> !ls -la $ORACLE_HOME/u01
total 22611
drwxrwxr-x 2 dbamgr dba 512 Feb 24 13:08 .
drwxrwxr-x 43 dba35 dba 1024 Feb 20 23:21 ..
-rw-rw---- 1 dba35 dba 514048 Feb 24 13:05 index_01.dbf
-rw-rw---- 1 dba35 dba 155648 Feb 24 13:05 log1a.rdo
-rw-rw---- 1 dba35 dba 155648 Feb 24 13:18 log2a.rdo
-rw-rw---- 1 dba35 dba 155648 Feb 24 12:55 log3a.rdo
-rw-rw---- 1 dba35 dba 10487808 Feb 24 13:18 system.dbf
-rw-rw---- 1 dba35 dba 12288 Feb 24 13:18 test_01.dbf
-rw-rw---- 1 dba35 dba 53248 Feb 24 13:18 test_02.dbf
⑦ Datafile도 삭제
SVRMGR> !rm $ORACLE_HOME/u01/test_0*
SVRMGR> !ls -la $ORACLE_HOME/u01
SVRMGR> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
※ SCENARIO 1 : Online Redo Log의 Mirroring
SVRMGR> select * from v$log;
SVRMGR> select * from v$logfile;
SVRMGR> alter database add logfile member
'/DBA3/DBA/dba숫자/u02//log그룹번호b.rdo’to 그룹번호;
-> 각 그룹마다 멤버의 수를 갖게 미러링한다.
SVRMGR> select * from v$logfile;
※ SCENARIO 2 : Full Offline Backup 수행
① database를 shutdown한 상태에서 init/control/data file을 backup 폴더에 copy
① Database를 Startup
[/DBA3/DBA/dba숫자]svrmgrl
SVRMGR> connect internal
SVRMGR> startup
② Tablespace 정보를 확인(DBA_DATA_FILES, V$DATAFILE)
SVRMGR> select TABLESPACE_NAME, FILE_NAME
2> from dba_data_files; --> memo
③ Log File 정보를 확인(V$LOGFILE)
SVRMGR> select GROUP#, MEMBER
2> from v$logfile; --> memo
④ Control File의 정보를 확인
(V$CONTROLFILE, V$PARAMETER, init<SID>.ora, SHOW PARAMETER command)
⑤ Control File의 이름은 $ORACLE_HOME/dbs에서 Parameter File로 확인
SVRMGR> host more $ORACLE_HOME/dbs/initDBA숫자.ora --> memo
⑥ System이 정상인지 확인 (Row들을 Insert)
SVRMGR> ! more $ORACLE_HOME/labs/more_emp.sql
SVRMGR> @?/labs/more_emp
* Full Offline Backup 수행
⑦ Database Shutdown
SVRMGR> shutdown immediate
SVRMGR> exit
⑧ File들을 Backup
[/DBA3/DBA/dba숫자] cp -rp u0* backup
[/DBA3/DBA/dba숫자] cp dbs/initDBA*.ora backup
[/DBA3/DBA/dba숫자] cp dbs/cntrlDBA*.ctl backup
⑨ backup에 가서 확인
[/DBA3/DBA/dba숫자] cd backup
[/DBA3/DBA/dba숫자] ls -la
※ SCENARIO 3 : Recovery - Temporary Tablespace의 유실
1) 정상적인 업무를 수행
- Database를 기동
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> select tablespace_name, file_name from dba_data_files;
--> Temporary Tablespace의 Data File 경로명을 확인
SVRMGR> !ls -la /DBA3/DBA/dba숫자/u04/temp_01.dbf --> 크기 확인
2) Failure를 만든다.
SVRMGR> shutdown abort --> Failure를 상상
SVRMGR> exit
[/DBA3/DBA/dba숫자]cd $ORACLE_HOME/u04
[/DBA3/DBA/dba숫자/u04]mv temp_01.dbf temp_01.org --> Temporary Tablespace의 유실
SVRMGR> connect internal
SVRMGR> startup mount
SVRMGR> alter database open;
alter database open
*
ORA-01157: cannot identify data file 4 - file not found
ORA-01110: data file 4: '/DBA3/DBA/dba숫자/u04/temp_01.dbf'
3) Recovery 수행
- Temporary Tablespace라면 Drop하고 새로 만들면 될껄?
SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u04/temp_01.dbf' offline drop;
Statement processed. --> Temporary Tablespace를 포기하고 Startup
SVRMGR> alter database open;
Stateent processed.
SVRMGR> select * from dba_tablespaces;
--> "Temp" Tablespace는 그래도 존재
--> 왜냐면 Temp Tablespace의 구성 File 중 하나를 Drop한 것 뿐이니까.
(여기선 우연히 하나였다)
SVRMGR> drop tablespace temp including contents; --> 이젠 새로 만들면 끝
SVRMGR> create tablespace temp
2> datafile '/DBA3/DBA/dba숫자/u04/temp_01.dbf' size 1M;
SVRMGR> shutdown immediate --> Shutdown과 Startup을 해봐서 잘 되는지 확인
SVRMGR> startup
SVRMGR> shutdown immediate
SVRMGR> exit
[/DBA3/DBA/dba숫자/u04]rm temp_01.org --> 필요 없는 File을 삭제
[/DBA3/DBA/dba숫자/u04]cd
※ SCENARIO 4 : Noarchive Log Mode Recovery - Disk의 유실
1) Failure를 가정하고 Recovery 수행
- 모든 File들을 Restore하려는 데, "users_01.dbf"를 원래 위치에 Restore 할 수가 없다.
따라서, 할 수 없이 $ORACLE_HOME에 Restore 한다.
[/DBA3/DBA/dba숫자] cd backup
[/DBA3/DBA/dba숫자/backup] ls
cntrlDBA숫자.ctl u01 u03
initDBA숫자.ora u02 u04
[/DBA3/DBA/dba숫자/backup] cp -rp u0* $ORACLE_HOME
[/DBA3/DBA/dba숫자/backup] cp initDBA숫자.ora $ORACLE_HOME/dbs
[/DBA3/DBA/dba숫자/backup] cp cntrlDBA숫자.ctl $ORACLE_HOME/dbs
[/DBA3/DBA/dba숫자/backup] cd $ORACLE_HOME/u03
[/DBA3/DBA/dba숫자/u03]ls
query_01.dbf rbs_01.dbf users_01.dbf
[/DBA3/DBA/dba숫자/u03] mv users_01.dbf $ORACLE_HOME
--> users_01.dbf 이 다른 곳으로 이사 갔다.
[/DBA3/DBA/dba숫자/u03] cd
[/DBA3/DBA/dba숫자] ls -la users*
2) Startup 시도
SVRMGR> connect internal
SVRMGR> startup mount
SVRMGR> alter database open;
alter database open
*
ORA-01157: cannot identify data file 3 - file not found
ORA-01110: data file 3: '/DBA3/DBA/dba숫자/u03/users_01.dbf'
SVRMGR> select name from v$datafile;
NAME
----------------------------------------------------------
/DBA3/DBA/dba숫자/u01/system.dbf
/DBA3/DBA/dba숫자/u03/rbs_01.dbf
/DBA3/DBA/dba숫자/u03/users_01.dbf
/DBA3/DBA/dba숫자/u04/temp_01.dbf
/DBA3/DBA/dba숫자/u03/query_01.dbf
/DBA3/DBA/dba숫자/u01/index_01.dbf
6 rows selected.
--> Oracle Server는 File이 다른 곳($ORACLE_HOME)에 있다는 것을 모르네.
3) 그럼 내가 가르쳐 주지.
SVRMGR> alter database rename file '/DBA3/DBA/dba숫자/u03/users_01.dbf'
2> to '/DBA3/DBA/dba숫자/users_01.dbf';
4) 다시 Open 시도
SVRMGR> alter database open; --> 성공!!!
SVRMGR> shutdown immediate
SVRMGR> startup --> 한번 더 확인
5) 원래 상태로 만들자.
SVRMGR> shutdown immediate
SVRMGR> exit
[/DBA3/DBA/dba숫자]rm users_01.dbf
[/DBA3/DBA/dba숫자]cd backup
[/DBA3/DBA/dba숫자/backup]cp -rp u0* $ORACLE_HOME
[/DBA3/DBA/dba숫자/backup]cp initDBA숫자.ora $ORACLE_HOME/dbs
[/DBA3/DBA/dba숫자/backup]cp cntrlDBA숫자.ctl $ORACLE_HOME/dbs
[/DBA3/DBA/dba숫자/backup]cd
[/DBA3/DBA/dba숫자]svrmgrl
SVRMGR> connect internal
SVRMGR> startup --> 괜히 확인
SVRMGR> shutdown
SVRMGR> exit
※ SCENARIO 5 : Read Only Tablespace의 Backup & Recovery
1) 정상적인 업무를 수행
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp
2) Read Only Tablespace의 Backup
- Online/Offline 상태, 즉 DB가 사용중이던지 사용중이 아니던지 관계없이 Copy
SVRMGR> !cp u03/query_01.dbf $ORACLE_HOME
3) Failure를 만든다
- 업무 수행 중에 query_01.dbf File이 삭제되었다
SVRMGR> @?/labs/more_emp
SVRMGR> !rm u03/query_01.dbf
SVRMGR> select * from scott.new_dept;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/DBA3/DBA/dba숫자/u03/query_01.dbf'
ORA-07368: sfofi: open error, unable to open database file.
SVR4 Error: 2: No such file or directory
4) Recovery 시작
- Online/Offline 상태, 즉 DB가 사용중이던지 사용중이 아니던지 관계없이 Copy
SVRMGR> !cp $ORACLE_HOME/query_01.dbf u03
SVRMGR> select * from scott.new_dept; --> 이게 Recovery 전부...
SVRMGR> shutdown
SVRMGR> exit
※ SCENARIO 6 : DATABASE의 BACKUP - Control File Mirroring & Archive log mode
1) Control File을 Mirroring하여 Database를 StartUp
- parameter File을 보고 현재의 "control_files=?????"를 확인
[/DBA3/DBA/dba숫자] more dbs/initDBA숫자.ora
- Control File을 복사
[/DBA3/DBA/dba숫자] cp dbs/cntrlDBA숫자.ctl u01
[/DBA3/DBA/dba숫자] cp dbs/cntrlDBA숫자.ctl u02
- 추가된 Control File들을 init<SID>.ora File에 등록
[/DBA3/DBA/dba숫자] vi dbs/initDBA숫자.ora
(수정) control_files=($ORACLE_HOME/dbs/cntrlDBA숫자.ctl,
$ORACLE_HOME/u01/cntrlDBA숫자.ctl,
$ORACLE_HOME/u02/cntrlDBA숫자.ctl)
:wq
- Database를 Startup
SVRMGR> connect internal
SVRMGR> startup
2) Database를 Archive Log Mode로 운영
- 현재 Archive Log Mode를 확인
SVRMGR> select * from v$logfile; --> On-Line Redo Log File들 확인
SVRMGR> archive log list --> No Archive Mode 확인
- Archive Log Mode로 전환 & Parameter 수정
SVRMGR>shutdown immediate
SVRMGR>host
[/DBA3/DBA/dba숫자]vi dbs/initDBA숫자.ora
(수정) log_archive_start = true
log_archive_dest = $ORACLE_HOME/arch
log_archive_format = _%s.arc
:wq
[/DBA3/DBA/dba숫자]exit
SVRMGR>startup mount --> 반드시 Mount로 StartUp 해야 함
SVRMGR>alter database archivelog; --> Mode 변경
SVRMGR>archive log list --> Archive Mode 확인,
--> Current Log 번호 기억
SVRMGR>alter database open; --> 현재 Mount이므로
3) Documentation을 위한 정보 탐색
- Tablespace 정보
SVRMGR> select TABLESPACE_NAME, FILE_NAME, v$datafile.STATUS, ENABLED
2> from dba_data_files, v$datafile
3> where FILE_ID = FILE#;
- Log File 정보
SVRMGR> select v$logfile.MEMBER, v$logfile.GROUP#, v$log.STATUS, BYTES
2> from v$logfile, v$log
3> where v$logfile.GROUP# = v$log.GROUP#;
- Control File 정보
SVRMGR> select * from v$controlfile;
- 각종 Parameter 정보
SVRMGR> show parameter log
SVRMGR> show parameter db_block
SVRMGR> show parameter dump
4) System이 정상인지 확인
- Row들을 Insert
SVRMGR> host more $ORACLE_HOME/labs/more_emp.sql
SVRMGR> @?/labs/more_emp
- Archived Log File이 만들어 지는 지 확인
SVRMGR> host ls -la $ORACLE_HOME/*.arc --> Log File 존재 확인
SVRMGR> archive log list --> Current Log 번호 증가 확인
5) Full Offline Backup 수행
- Database Shutdown
SVRMGR> shutdown immediate
SVRMGR> exit
- File들을 Backup (만일을 위해서 두 번 Backup)
[/DBA3/DBA/dba숫자] cp -rp u0* dontouch
[/DBA3/DBA/dba숫자] cp dbs/init*.ora dontouch
[/DBA3/DBA/dba숫자] cp dbs/cntrl*.ctl dontouch
[/DBA3/DBA/dba숫자] cp -rp u0* backup
[/DBA3/DBA/dba숫자] cp dbs/init*.ora backup
[/DBA3/DBA/dba숫자] cp dbs/cntrl*.ctl backup
- 확인
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> shutdown immediate
SVRMGR> exit
※ SCENARIO 7 : Complete Recovery(Archive) - User의 DATA FILE 유실
1) 정상적인 업무를 수행
- Database를 기동
SVRMGR> connect internal
SVRMGR> startup
- 업무 수행 & Archived Log File 확인
SVRMGR> !ls -la *.arc --> 현재의 Archived Log File 확인
SVRMGR> !more labs/more_emp.sql --> "scott" user의 "s_emp" table에 Row를 Insert/Update 하는 Script
SVRMGR> @?/labs/more_emp
SVRMGR> exit
[/DBA3/DBA/dba숫자] ls -la *.arc --> Archived Log File 생성 확인
2) Failure를 만든다.
[/DBA3/DBA/dba숫자] ls u03 --> "USERS" Tablespace를 구성하는 File 확인
[/DBA3/DBA/dba숫자] rm u03/users_01.dbf --> FIle 삭제
[/DBA3/DBA/dba숫자] ls u03
SVRMGR> connect internal
SVRMGR> shutdown immediate --> Error 발생 & 실패
SVRMGR> shutdown abort
SVRMGR> exit
3) Recovery 시작
① 예전에 받은 Full Backup으로부터 손상된 Data File을 Restore
[/DBA3/DBA/dba숫자] cd backup/u03
[/DBA3/DBA/dba숫자/backup/u03] ls -la
[/DBA3/DBA/dba숫자/backup/u03] cp users_01.dbf $ORACLE_HOME/u03
- Recovery를 수행
② SVRMGR> startup
--> Error와 함께 Mount까지만 수행
--> Recovery를 위해선 " Startup Mount " 하는게 정상
Database mounted.
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/DBA3/DBA/dba숫자/u03/users_01.dbf'
③ SVRMGR> recover database
ORA-00279: Change 7474 generated at 04/24/97 22:52:31 needed for thread 1
ORA-00289: Suggestion : /DBA3/DBA/dba27/arch_256.arc
ORA-00280: Change 7474 for thread 1 is in sequence #256
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} --> 여러번 "Enter"를 눌러야 함
Media recovery complete.
④ SVRMGR> alter database open;
--> 현재가 "Mount" 상태이므로
4) System이 정상적으로 복구 되었는지 확인
SVRMGR> select count(*) from scott.s_emp; --> 정상적으로 수행 됨
SVRMGR> shutdown immediate --> 정상적으로 수행 됨
SVRMGR> exit
※ SCENARIO 8 : Complete Recovery(ARchive) - Tablespace Recovery
1) 정상적인 업무를 수행
- Database를 기동
SVRMGR> connect internal
SVRMGR> startup
- 업무 수행 & Archived Log File 확인
SVRMGR> !ls -la *.arc --> 현재의 Archived Log File 확인
SVRMGL> @?/labs/more_emp
SVRMGR> exit
[/DBA3/DBA/dba숫자]ls -la *.arc --> Archived Log File 생성 확인
2) Failure를 만든다.
[/DBA3/DBA/dba숫자] ls $ORACLE_HOME/u03 --> "USERS" Tablespace를 구성하는 File 확인
[/DBA3/DBA/dba숫자] rm $ORACLE_HOME/u03/users_01.dbf --> FIle 삭제
[/DBA3/DBA/dba숫자] ls $ORACLE_HOME/u03
SVRMGR> connect internal
SVRMGR> shutdown immediate --> Error 발생 & 실패
SVRMGR> shutdown abort
SVRMGR> exit
3) Recovery 시작
- 예전에 받은 Full Backup으로부터 손상된 Data File을 Restore
[/DBA3/DBA/dba숫자] cd backup/u03
[/DBA3/DBA/dba숫자/backup/u03] ls -la
[/DBA3/DBA/dba숫자/backup/u03] cp users_01.dbf $ORACLE_HOME/u03
- Recovery를 수행
SVRMGR> connect internal
SVRMGR> startup mount
SVRMGR> alter database open; --> Error
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/DBA3/DBA/dba숫자/u03/users_01.dbf'
SVRMGR> select FILE#, STATUS, NAME from v$datafile;
SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u03/users_01.dbf' offline;
SVRMGR> select FILE#, STATUS, NAME from v$datafile;
SVRMGR> alter database open;
SVRMGR> select TABLESPACE_NAME, STATUS from dba_tablespaces;
SVRMGR> alter tablespace user_data offline immediate;
SVRMGR> select TABLESPACE_NAME, STATUS from dba_tablespaces;
SVRMGR> recover tablespace user_data
ORA-00279: Change 7220 generated at 02/24/97 23:51:30 needed for thread 1
ORA-00289: Suggestion : /DBA3/DBA/dba숫자/arch_219.arc
ORA-00280: Change 7220 for thread 1 is in sequence #219
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto - --> 입력하자
SVRMGR> alter tablespace user_data online;
SVRMGR> select count(*) from scott.s_emp; --> 정상적으로 수행 됨
SVRMGR> shutdown immediate
SVRMGR> exit
※ SCENARIO 9 : Complete Recovery(ARchive) - Datafile Recovery
1) 정상적인 업무를 수행
- Database를 기동
SVRMGR> connect internal
SVRMGR> startup
- 업무 수행 & Archived Log File 확인
SVRMGR> !ls -la *.arc --> 현재의 Archived Log File 확인
SVRMGR> @?/labs/more_emp
SVRMGR> exit
[/DBA3/DBA/dba숫자]ls -la *.arc --> Archived Log File 생성 확인
2) Failure를 만든다.
[/DBA3/DBA/dba숫자] ls $ORACLE_HOME/u03 --> "USERS" Tablespace를 구성하는 File 확인
[/DBA3/DBA/dba숫자] rm $ORACLE_HOME/u03/users_01.dbf --> FIle 삭제
[/DBA3/DBA/dba숫자] ls $ORACLE_HOME/u03
SVRMGR> connect internal
SVRMGR> shutdown immediate --> Error 발생 & 실패
SVRMGR> shutdown abort
SVRMGR> exit
3) Recovery 시작
① 예전에 받은 Full Backup으로부터 손상된 Data File을 Restore
[/DBA3/DBA/dba숫자] cd backup/u03
[/DBA3/DBA/dba숫자/backup/u03] ls -la
[/DBA3/DBA/dba숫자/backup/u03] cp users_01.dbf $ORACLE_HOME/u03
- Recovery를 수행
SVRMGR> connect internal
② startup mount
③ SVRMGR> alter database open;
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/DBA3/DBA/dba숫자/u03/users_01.dbf'
④ SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u03/users_01.dbf' offline;
⑤ SVRMGR> alter database open;
⑥ SVRMGR> recover datafile '/DBA3/DBA/dba숫자/u03/users_01.dbf'
ORA-00279: Change 7220 generated at 02/24/97 23:51:30 needed for thread 1
ORA-00289: Suggestion : /DBA3/DBA/dba숫자/arch_219.arc
ORA-00280: Change 7220 for thread 1 is in sequence #219
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
"Return key"를 여러번 누르거나, "auto"를 입력하자
⑦ SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u03/users_01.dbf' online;
SVRMGR> select count(*) from scott.s_emp; --> 정상적으로 수행 됨
SVRMGR> shutdown immediate
SVRMGR> exit
※ SCENARIO 10 : Parallel Recovery(Archive)
1) Parallel 환경 setup
- parameter file을 수정하여 parallel 환경을 만든다.
Parallel_min_servers = 2
Parallel_max_servers = 4
Recovery_parallelism = 4
- DB를 다시 기동한 후 background process들 (p000, p001) 을 확인한다.
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> host ps -ef | grep <SID>
SVRMGR> select count(*) from scott.s_emp;
SVRMGR> shutdown immediate
2) user_data tablespace를 backup받고 DB 기동후, 정상적인 업무를 수행
[/DBA3/DBA/dba숫자]cp u03/users_01.dbf u03/users_01.bak
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp -----> row들을 insert
3) Failure를 만든다.
SVRMGR> shutdown immediate
SVRMGR> host rm $ORACLE_HOME/u03/users_01.dbf
4) DB 복구 작업
- backup 받은 file을 restore 시킨 후, Database를 mount 시킨다.
SVRMGR> !mv u03/users_01.bak u03/users_01.dbf
SVRMGR> startup mount
SVRMGR> alter database open
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/DBA3/DBA/dba숫자/u03/users_01.dbf'
- DB parallel recovery 후 DB open
SVRMGR> set autorecovery on
SVRMGR> recover database parallel (degree 4)
......
Media recovery complete.
SVRMGR> alter database open
5) System이 정상인지 확인
SVRMGR> select count(*) from scott.s_emp;
SVRMGR> !ps -ef | grep <SID> ----> p002, p003 확인 (<- degree 4)
- 5분 이상 경과 후, p002, p003이 존재하는지 확인한다.
SVRMGR> !ps -ef | grep <SID>
SVRMGR> shutdown immediate
SVRMGR> exit
※ SCENARIO 11 : Complete Recovery - Shutdown 하지 않고 Data File만 Recovery
1) 정상적인 업무를 수행
- Database를 기동
SVRMGR> connect internal
SVRMGR> startup
- 업무 수행 & Archived Log File 확인
SVRMGR> !ls -la *.arc --> 현재의 Archived Log File 확인
SVRMGR> @?/labs/more_emp
SVRMGR> exit
[/DBA3/DBA/dba숫자] ls -la *.arc --> Archived Log File 생성 확인
2) Failure를 만든다.
[/DBA3/DBA/dba숫자] ls $ORACLE_HOME/u03 --> "USERS" Tablespace를 구성하는 File 확인
[/DBA3/DBA/dba숫자] rm $ORACLE_HOME/u03/users_01.dbf --> FIle 삭제
[/DBA3/DBA/dba숫자] ls $ORACLE_HOME/u03
SVRMGR> connect internal
SVRMGR> select * from scott.s_dept;
ID NAME REGION_ID
---------- ------------------------- ----------
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/DBA3/DBA/dba숫자/u03/users_01.dbf'
ORA-07368: sfofi: open error, unable to open database file.
SVR4 Error: 2: No such file or directory
3) Recovery 시작
① 예전에 받은 Full Backup으로부터 손상된 Data File을 Restore
[/DBA3/DBA/dba숫자] cd backup/u03
[/DBA3/DBA/dba숫자/backup/u03] ls -la
[/DBA3/DBA/dba숫자/backup/u03] cp users_01.dbf $ORACLE_HOME/u03
- Recovery를 수행
SVRMGR> connect internal
SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u03/users_01.dbf' offline;
SVRMGR> recover datafile '/DBA3/DBA/dba숫자/u03/users_01.dbf'
ORA-00279: Change 7220 generated at 02/24/97 23:51:30 needed for thread 1
ORA-00289: Suggestion : /DBA3/DBA/dba숫자/arch_219.arc
ORA-00280: Change 7220 for thread 1 is in sequence #219
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
"Return key"를 여러번 누르거나, "auto"를 입력하자
SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u03/users_01.dbf' online;
SVRMGR> select count(*) from scott.s_emp; --> 정상적으로 수행 됨
SVRMGR> select * from scott.s_dept; --> 정상적으로 수행 됨
SVRMGR> shutdown immediate
SVRMGR> exit
※ SCENARIO 12 : Online Backup (Hot Backup)
1) 정상적인 업무를 수행
- Database를 기동
SVRMGR> connect internal
SVRMGR> startup
- 업무 수행 & Archived Log File 확인
SVRMGR> !ls -la *.arc --> 현재의 Archived Log File 확인
SVRMGR> @?/labs/more_emp
SVRMGR> !ls -la *.arc --> Archived Log File 생성 확인
2) Online Backup
① Data File들의 Online Backup : datafile별로 backup받는다.
SVRMGR> select tablespace_name, file_name from dba_data_files;
SVRMGR> select status, enabled, name from v$datafile;
--> enabled가 "READ ONLY" Tablespace는 Online Backup시에 제외
SVRMGR> !mkdir $ORACLE_HOME/online_backup
SVRMGR> alter tablespace system begin backup;
SVRMGR> !cp u01/system.dbf online_backup
SVRMGR> alter tablespace system end backup;
SVRMGR> alter tablespace rbs begin backup;
SVRMGR> !cp u03/rbs_01.dbf online_backup
SVRMGR> alter tablespace rbs end backup;
SVRMGR> alter tablespace user_data begin backup;
SVRMGR> !cp u03/users_01.dbf online_backup
SVRMGR> alter tablespace user_data end backup;
SVRMGR> alter tablespace temp begin backup;
SVRMGR> !cp u04/temp_01.dbf online_backup
SVRMGR> alter tablespace temp end backup;
SVRMGR> alter tablespace user_index begin backup;
SVRMGR> !cp u01/index_01.dbf online_backup
SVRMGR> alter tablespace user_index end backup;
SVRMGR> alter system switch logfile;
② Read-only Tablespace Backup
Read-only Tablespace는 예전 Backup에 이미 Copy되어 있으므로 다시 수행할 필요가 없다.
그래도 꼭 하겠다면 "alter tablespace ...begin/end backup" 없이 수행
에이, 말 나온 김에 한번 해보자......
SVRMGR> !cp u03/query_01.dbf online_backup
③ Control File의 Online Backup
SVRMGR> alter database backup controlfile to
'$ORACLE_HOME/online_backup/backup_control.ctl' reuse;
SVRMGR> alter database backup controlfile to trace;
④ Parameter File의 Backup
SVRMGR> !cp dbs/initDBA숫자.ora online_backup
SVRMGR> shutdown immediate
SVRMGR> exit
※ SCENARIO 13 : Online Backup 실패 후 Recovery - Online Backup 도중에 정전
1) 정상적인 업무를 수행
- Database를 기동 & 업무 수행
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp
2) Online Backup
- Data File들의 Online Backup
SVRMGR> alter tablespace user_data begin backup;
SVRMGR> !cp u03/users_01.dbf online_backup
SVRMGR> shutdown abort --> 정전 사태 발생
SVRMGR> exit
[/DBA3/DBA/dba숫자] svrmgrl --> 다시 전원이 들어와서 DB를 살리려고 시도
SVRMGR> connect internal
SVRMGR> startup
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/DBA3/DBA/dba숫자/u03/users_01.dbf'
--> 어? 이상하다....아하! 이것쯤이야..
SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u03/users_01.dbf' end backup;
SVRMGR> alter database open;
SVRMGR> --> Online Backup을 다시 받으면 된다
SVRMGR> shutdown immediate
SVRMGR> exit
※ SCENARIO 14 : Incomplete Recovery(Noarchive) - 실수로 Drop한 Table의 복구
1) 정상적인 업무를 수행
- Database를 기동
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp
2) Failure를 만든다.
SVRMGR> !date --> 현재의 시간을 기억해야 함
SVRMGR> drop table scott.s_emp cascade constraints; --> 실수로 Drop 하였다고 가정
SVRMGR> select * from scott.s_emp;
select * from scott.s_emp
*
ERROR at line 1:
ORA-00942: table or view does not exist --> 이제와서 후회
3) Recovery 수행
- 예전에 받은 Full Backup으로부터 Data File들을 Restore
SVRMGR> shutdown immediate
SVRMGR> exit
[/DBA3/DBA/dba숫자] cd backup
[/DBA3/DBA/dba숫자/backup] ls
[/DBA3/DBA/dba숫자/backup] cp u01/*.dbf $ORACLE_HOME/u01
[/DBA3/DBA/dba숫자/backup] cp u03/*.dbf $ORACLE_HOME/u03
[/DBA3/DBA/dba숫자/backup] cp u04/*.dbf $ORACLE_HOME/u04
- Incomplete Recovery 수행
SVRMGR> connect internal
SVRMGR> startup mount
① SVRMGR> set autorecovery on
② SVRMGR> recover database until time '1997-01-23:16:44:47'
--> 앞에서 기억한 시간이어야 함
③ SVRMGR> alter database open resetlogs; --> Incomplete Recovery 이니까 "resetlogs"로 Open
④ SVRMGR> archive log list --> Log Sequence 번호가 Reset되었음
SVRMGR> select * from scott.s_emp; --> Drop 되었던 "s_emp" Table이 다시 살아났다.
SVRMGR> shutdown immediate
SVRMGR> exit
⑤ Log Sequence 번호가 Reset 되었으니까 Off-Line Full Backup 수행
[/DBA3/DBA/dba숫자/backup] cd $ORACLE_HOME
[/DBA3/DBA/dba숫자] cp -rp u0* backup
[/DBA3/DBA/dba숫자] cp dbs/cntrl*.ctl backup
[/DBA3/DBA/dba숫자] cp dbs/init*.ora backup
- 더 이상 필요 없는 File들을 삭제
[/DBA3/DBA/dba숫자] rm *.arc
[/DBA3/DBA/dba숫자] ls
※ SCENARIO 15 : Inactive Online Redo Log Group의 유실
1) 정상적인 업무를 수행
SVRMGR> connect / as sysdba --> connect internal과 같음
SVRMGR> startup
SVRMGR> @?/labs/more_emp
2) Failure를 만든다.
- Inactive Online Redo Log Group을 유실
SVRMGR> select v$logfile.member from v$logfile where group# =
( select min(v$log.group#) from v$log where status = 'INACTIVE');
MEMBER
------------------------------------------------------------
/DBA3/DBA/dba숫자/u01/log2a.rdo --> 예를 들어서...라면
/DBA3/DBA/dba숫자/u02/log2b.rdo
2 rows selected.
SVRMGR> !ls u01 u02
SVRMGR> !rm /DBA3/DBA/dba숫자/u01/log2a.rdo
SVRMGR> !rm /DBA3/DBA/dba숫자/u02/log2b.rdo
SVRMGR> !ls u01 u02
- Database가 비정상적으로 수행됨을 확인
SVRMGR> connect / as sysdba
SVRMGR> @more_emp --> Online Redo Log FIle의 유실로 인해 Error 발생
SVRMGR> shutdown immediate --> shutdown 실패 (Server Process가 죽었다)
SVRMGR> exit --> exit 했다가 다시 들어가자.
3) Recovery 시작
SVRMGR> connect / as sysdba
SVRMGR> shutdown abort
SVRMGR> startup --> Mount까지만 수행됨
SVRMGR> select * from v$logfile; --> Log FIle의 유실이 반영되지 않았음을 확인
SVRMGR> alter database backup controlfile to trace; --> 그냥 습관적으로
SVRMGR> alter database drop logfile group 그룹번호; --> Log FIle의 유실을 반영
SVRMGR> select * from v$logfile; --> Log FIle의 유실이 반영되었음을 확인
SVRMGR> alter database add logfile group 그룹번호
2> '/DBA3/DBA/dba숫자/u01/log그룹번호a.rdo' size 150k;
--> 유실된 Online Redo Log Group의 첫번째 Member를 생성
SVRMGR> alter database add logfile member '/DBA3/DBA/dba숫자/u02/log그룹번호b.rdo' to group 그룹번호;
--> 복구된 Online Redo Log Group의 두번째 Member를 생성
SVRMGR> select * from v$logfile; --> Log File들이 생성되었는지 확인
--> Invalid는 나중에 없어지니까 놀라지 마세요.
SVRMGR> alter database open; --> Database를 Open
4) System이 정상적으로 복구 되었는지 확인
SVRMGR> @more_emp
SVRMGR> select * from v$logfile; --> 음, Invalid가 없어졌구나.
SVRMGR> shutdown immediate
SVRMGR> startup --> startup도 제대로 되는구나.
SVRMGR> shutdown immediate
SVRMGR> exit
[/DBA3/DBA/dba26/labs] cd
※ SCENARIO 16 : Current Online Redo Log Group의 유실
1) 정상적인 업무를 수행
- Database를 기동
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp
SVRMGR> !ls -la *.arc
2) Failure를 만든다.
- Current Online Redo Log Group을 유실
SVRMGR> select v$logfile.member from v$logfile where group# =
( select min(v$log.group#) from v$log where status = 'CURRENT');
MEMBER
----------------------------------------------------------------
/DBA3/DBA/dba숫자/u01/log1a.rdo --> 예를 들어 ...라면
/DBA3/DBA/dba숫자/u02/log1b.rdo
2 rows selected.
SVRMGR> !ls u01 u02
SVRMGR> !rm /DBA3/DBA/dba숫자/u01/log1a.rdo
SVRMGR> !rm /DBA3/DBA/dba숫자/u02/log1b.rdo
SVRMGR> !ls u01 u02
- Database가 비정상적으로 수행됨을 확인
SVRMGR> @?/labs/more_emp --> Online Redo Log FIle의 유실로 인해 Error 발생
--> 무한정 대기하게 된다. Why?
--> "Ctrl-C"를 두번 눌러서 강제 종료
SVRMGR> shutdown immediate
3) Recovery 시작
- 유실된 Redo Log Group을 제거하고 재생성함으로써 해결할려고 시도
SVRMGR> startup --> Redo Log Group이 유실 되었음을 알리며 Error 발생
--> Log Group 번호 확인할 것
--> Mount까지만 수행된다
SVRMGR> alter database drop logfile group 그룹번호;
--> 유실된 Redo Log Group을 제거 시도
--> Archive되지 않은 Current Log이므로 Error와 함께 실패
--> (참고) ORA-00350: log 그룹번호 of thread 1 needs to be archived
SVRMGR> shutdown immediate
SVRMGR> exit
- Alert File, Trace File 확인
[/DBA3/DBA/dba숫자/labs] cd $ORACLE_HOME/trace
[/DBA3/DBA/dba숫자/trace] ls
[/DBA3/DBA/dba숫자/trace] vi alert_DBA숫자.log
--> Archiving을 실패한 기록과 Sequence 번호 확인
[/DBA3/DBA/dba숫자/trace] more arch_번호.trc
--> 기록되지 않은 Log File의 Sequence 번호 확인
--> (예)ORA-00255: error archiving log 1 of thread 1, sequence # 15
--> Incomplete Recovery 방법으로 복구
--> 예를 들어 sequence # 15번이라면 Incomplete Recovery시
15번 에서 "Cancel" 을 입력할거다.
- 예전에 받은 Full Backup으로부터 Data File들을 Restore
[/DBA3/DBA/dba숫자/trace]cd $ORACLE_HOME/backup
[/DBA3/DBA/dba숫자/backup]cp u01/*.dbf $ORACLE_HOME/u01
[/DBA3/DBA/dba숫자/backup]cp u03/*.dbf $ORACLE_HOME/u03
[/DBA3/DBA/dba숫자/backup]cp u04/*.dbf $ORACLE_HOME/u04
- Incomplete Recovery 수행
SVRMGR> connect internal
SVRMGR> startup mount
SVRMGR> recover database until cancel
"cancel" 입력
--> 계속 "Enter"를 누르다가 15번 에서 "Cancel" 을 입력
SVRMGR> alter database open resetlogs;
--> Incomplete Recovery 이니까 "resetlogs"로 Open
--> 이때 유실된 Log File이 자동으로 만들어 진다
SVRMGR> archive log list --> Log Sequence 번호가 Reset되었음
SVRMGR> shutdown immediate
SVRMGR> exit
- Log Sequence 번호가 Reset 되었으니까 Off-Line Full Backup 수행
[/DBA3/DBA/dba숫자/backup]cd $ORACLE_HOME
[/DBA3/DBA/dba숫자] cp -rp u0* backup
[/DBA3/DBA/dba숫자] cp dbs/cntrl*.ctl backup
[/DBA3/DBA/dba숫자] cp dbs/init*.ora backup
- 더 이상 필요 없는 File들을 삭제
[/DBA3/DBA/dba숫자] cd trace
[/DBA3/DBA/dba숫자/trace] rm *.trc --> Trace File들 삭제
[/DBA3/DBA/dba숫자/trace]rm alert*.log --> Alert Log File 삭제
[/DBA3/DBA/dba숫자/trace]cd $ORACLE_HOME
[/DBA3/DBA/dba숫자]rm *.arc --> Archived Redo Log File들 삭제
[/DBA3/DBA/dba숫자]ls -la *.arc --> File들 삭제 확인
4) System이 정상적으로 복구 되었는지 확인
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp --> 음, 잘 되는 군
SVRMGR> !ls -la *.arc --> 새로운 Archived Redo Log File들이 만들어 지는 지 확인
SVRMGR> shutdown immediate
SVRMGR> exit
※ SCENARIO 17 : 모든 Online Redo Log Group의 유실
1) 정상적인 업무를 수행
- Database를 기동
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp
SVRMGR> !ls -la *.arc
2) Failure를 만든다.
- 모든 Online Redo Log Group을 유실
SVRMGR> !ls u01 u02
SVRMGR> !rm /DBA3/DBA/dba숫자/u01/log*.rdo
SVRMGR> !rm /DBA3/DBA/dba숫자/u02/log*.rdo
- Database가 비정상적으로 수행됨을 확인
SVRMGR> @?/labs/more_emp --> Online Redo Log FIle의 유실로 인해 Error 발생
SVRMGR> !ps -ef|grep dba숫자|sort|more --> Background Precess들과 Server Process들이 죽었다
3) Recovery 시작
- 앞에서 배운 꽁수로 해결해 보자
SVRMGR> shutdown immediate --> shutdown 실패 (Process들이 죽었으니까)
SVRMGR> shutdown abort --> shutdown 실패 (Process들이 죽었으니까)
SVRMGR> exit --> exit 했다가 다시 들어가자.
SVRMGR> connect internal
SVRMGR> shutdown immediate
ORA-01012: not logged on
SVRMGR> shutdown abort
ORACLE instance shut down.
SVRMGR> startup
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/DBA3/DBA/dba숫자/u02/log3a.rdo'
ORA-07360: sfifi: stat error, unable to obtain information about file.
.............
SVRMGR> recover database until cancel;
ORA-00279: Change 8064 generated at 01/20/98 13:02:09 needed for thread 1
ORA-00289: Suggestion : /DBA3/DBA/dba숫자/arch_9.arc
ORA-00280: Change 8064 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
"cancel" 입력
Media recovery cancelled.
SVRMGR> alter database open resetlogs;
alter database open resetlogs
*
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/DBA3/DBA/dba숫자/u01/system.dbf'
--> 앗! 어더레케 된거야? .....꽁수가 안 통하잖아?
??? 할 수 없다. 정식으로 한번 해 보자.
SVRMGR> exit
- 정상적인 Incomplete Recovery 수행
[/DBA3/DBA/dba숫자] cd $ORACLE_HOME/backup
[/DBA3/DBA/dba숫자/backup] cp u01/*.dbf $ORACLE_HOME/u01
[/DBA3/DBA/dba숫자/backup] cp u03/*.dbf $ORACLE_HOME/u03
[/DBA3/DBA/dba숫자/backup] cp u04/*.dbf $ORACLE_HOME/u04
SVRMGR> connect internal
SVRMGR> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DBA3/DBA/dba숫자/arch
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5
--> 예를 들어 "Next log sequence to archive 5" 번이라면
Incomplete Recovery 시 5번 에서 "Cancel" 을 입력할거다.
SVRMGR> recover database until cancel
"cancel" 입력 --> 계속 "Enter"를 누르다가 5번 에서 "Cancel" 을 입력
SVRMGR> alter database open resetlogs;
SVRMGR> archive log list
- Log Sequence 번호가 Reset 되었으니까 Offline Full Backup 수행
SVRMGR> shutdown immediate
SVRMGR> exit
[/DBA3/DBA/dba숫자/backup] cd $ORACLE_HOME
[/DBA3/DBA/dba숫자] cp -rp u0* backup
[/DBA3/DBA/dba숫자] cp -p dbs/cntrl*.ctl backup
[/DBA3/DBA/dba숫자] cp -p dbs/init*.ora backup
- 더 이상 필요 없는 File들을 삭제
[/DBA3/DBA/dba숫자] rm *.arc --> Archived Redo Log File들 삭제
4) System이 정상적으로 복구 되었는지 확인
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp --> 음, 잘 되는 군
SVRMGR> !ls -la *.arc --> 새로운 Archived Redo Log File들이 만들어 지는 지 확인
SVRMGR> shutdown immediate
SVRMGR> exit
※ SCENARIO 18 : 모든 Redo Log & Data File 유실
1) 정상적인 업무를 수행
- Database를 기동
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp
SVRMGR> !ls -la *.arc
2) Failure를 만든다.
- 모든 Online Redo Log Group을 유실
SVRMGR> !ls u01 u02
SVRMGR> !rm /DBA3/DBA/dba숫자/u01/log*.rdo
SVRMGR> !rm /DBA3/DBA/dba숫자/u02/log*.rdo
- datafile을 유실
SVRMGR> !rm /DBA3/DBA/dba숫자/u01/system.dbf
SVRMGR> !rm /DBA3/DBA/dba숫자/u01/index_01.dbf
- 정전까지 되었다고 가정
SVRMGR> shutdown abort
3) Recovery 시작
- Incomplete Recovery 수행
SVRMGR> exit
[/DBA3/DBA/dba숫자] cd $ORACLE_HOME/backup
[/DBA3/DBA/dba숫자/backup] cp u01/*.dbf $ORACLE_HOME/u01
[/DBA3/DBA/dba숫자/backup] cp u03/*.dbf $ORACLE_HOME/u03
[/DBA3/DBA/dba숫자/backup] cp u04/*.dbf $ORACLE_HOME/u04
SVRMGR> connect internal
SVRMGR> startup mount
SVRMGR> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DBA3/DBA/dba숫자/arch
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
--> 예를 들어 "Next log sequence to archive 5" 번이라면
Incomplete Recovery 시 5번 에서 "Cancel" 을 입력할거다.
SVRMGR> recover database until cancel
"cancel" 입력 --> 계속 "Enter"를 누르다가 5번 에서 "Cancel" 을 입력
SVRMGR> alter database open resetlogs;
- Log Sequence 번호가 Reset 되었으니까 Offline Full Backup 수행
SVRMGR> shutdown immediate
SVRMGR> exit
[/DBA3/DBA/dba숫자/backup] cd $ORACLE_HOME
[/DBA3/DBA/dba숫자] cp -rp u0* backup
[/DBA3/DBA/dba숫자] cp -p dbs/cntrl*.ctl backup
[/DBA3/DBA/dba숫자] cp -p dbs/init*.ora backup
- 더 이상 필요 없는 File들을 삭제
[/DBA3/DBA/dba숫자] rm *.arc --> Archived Redo Log File들 삭제
4) System이 정상적으로 복구 되었는지 확인
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp --> 음, 잘 되는 군
SVRMGR> !ls -la *.arc --> 새로운 Archived Redo Log File들이 만들어 지는 지 확인
SVRMGR> shutdown immediate
SVRMGR> exit
※ SCENARIO 19 : Control File Recreate
1) 정상적인 업무 중 Control File Creation Script를 생성
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> !ps -ef|grep dba숫자|sort --> 현재 Server Process의 번호 확인
SVRMGR> alter database backup controlfile to trace;
SVRMGR> !
$ cd $ORACLE_HOME/trace
$ ls
$ cp ora_프로세서번호.trc control.sql
2) Control File들을 모두 삭제
$ rm $ORACLE_HOME/dbs/cntrlDBA숫자.ctl
$ rm $ORACLE_HOME/u01/cntrlDBA숫자.ctl
$ rm $ORACLE_HOME/u02/cntrlDBA숫자.ctl
$ exit
SVRMGR> shutdown immediate
SVRMGR> startup
ORA-00205: error identifying controlfile '$ORACLE_HOME/dbs/cntrlDBA숫자.ctl'
ORA-07360: sfifi: stat error, unable to obtain information about file.
SVR4 Error: 2: No such file or directory
SVRMGR> shutdown
SVRMGR> exit
3) Control File을 새로 생성
[/DBA3/DBA/dba숫자]cd trace
[/DBA3/DBA/dba숫자/trace]ls
[/DBA3/DBA/dba숫자/trace]vi control.sql --> "STARTUP NOMOUNT" 앞까지 모두 삭제
--> "RECOVER DATABASE" 삭제
SVRMGR> connect internal
SVRMGR> @control.sql
SVRMGR> !ls $ORACLE_HOME/dbs
SVRMGR> !ls $ORACLE_HOME/u01
SVRMGR> !ls $ORACLE_HOME/u02
4) 정상인지 확인
SVRMGR> @?/labs/more_emp
SVRMGR> !ls $ORACLE_HOME
SVRMGR> shutdown immediate
SVRMGR> exit
[/DBA3/DBA/dba숫자/trace] cd
[/DBA3/DBA/dba숫자]
※ SCENARIO 20 : 모든 Control File 유실
* 이번의 시나리오는 Database의 Mode(Archive/Noarchive)에 관계 없이 모두 가능
1) 정상적인 업무를 수행
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp
SVRMGR> !ls *.arc
2) Failure를 만든다.
- Control File을 모두 삭제
SVRMGR> !rm dbs/*.ctl u01/*.ctl u02/*.ctl
SVRMGR> shutdown abort --> 꽥! (사망하시는 소리)
SVRMGR> exit
3) Recovery 시작
[/DBA3/DBA/dba숫자]cp backup/cntrlDBA숫자.ctl dbs
[/DBA3/DBA/dba숫자]cp backup/cntrlDBA숫자.ctl u01
[/DBA3/DBA/dba숫자]cp backup/cntrlDBA숫자.ctl u02
SVRMGR> connect internal
SVRMGR> startup mount
SVRMGR> recover database using backup controlfile
ORA-00283: Recovery session canceled due to errors
ORA-01233: file 5 is read only - cannot recover using backup controlfile
ORA-01110: data file 5: '/DBA3/DBA/dba숫자/u03/query_01.dbf'
--> read only File이 있으면 반드시 offline시켜야 한다
SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u03/query_01.dbf' offline;
SVRMGR> recover database using backup controlfile
ORA-00279: Change 8050 generated at 01/20/98 15:22:26 needed for thread 1
ORA-00289: Suggestion : /DBA3/DBA/dba숫자/arch_5.arc
ORA-00280: Change 8050 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Online Redo Log File 명을 Full Path로 입력
(예) /DBA3/DBA/dba숫자/u01/log1a.rdo --> 입력
그런데, 특별히 운이 좋지 않다면, 다음의 에러가 난다
(에러 메시지)
ORA-00310: archived log contains sequence 4; sequence 5 required
ORA-00334: archived log: '/DBA3/DBA/dba숫자/u01/log1a.rdo'
그렇다면, recover와 File명 입력을 다시 시도
(예)
SVRMGR> recover database using backup controlfile --> 다시 수행
/DBA3/DBA/dba숫자/u01/log2a.rdo --> 다른 Redo Log File 명 입력
다음의 메시지를 볼 때까지 다른 Redo Log File에도 수행
(보여야 하는 메시지)
Log applied.
Media recovery complete. --> 이 메시지가 보이면 성공한 것임
SVRMGR> alter database open resetlogs;
SVRMGR> select count(*) from scott.s_emp; --> 성공이다
SVRMGR> select * from scott.new_emp;
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/DBA3/DBA/dba숫자/u03/query_01.dbf'
--> 얼라리오? 이상하다? 아하! query_01.dbf이 Offline이지!
SVRMGR> select * from v$datafile;
--> 역시 "/DBA3/DBA/dba숫자/u03/query_01.dbf"이 Offline이다.
SVRMGR> alter tablespace query_data online;
SVRMGR> select * from v$datafile;
SVRMGR> select * from scott.new_emp;
SVRMGR> shutdown
SVRMGR> exit
3) 반드시 Full Backup 수행
[/DBA3/DBA/dba숫자]cp -rp u0* backup
[/DBA3/DBA/dba숫자]cp -p dbs/cntrlDBA숫자.ctl backup
[/DBA3/DBA/dba숫자]cp -p dbs/initDBA숫자.ora backup
[/DBA3/DBA/dba숫자]rm *.arc
※ SCENARIO 21 : Control File과 Data File 동시에 유실
1) 정상적인 업무를 수행
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp
SVRMGR> !ls *.arc --> 마지막 File의 번호를 기억
SVRMGR> exit
2) Failure를 만든다.
- Control File을 모두 삭제
[/DBA3/DBA/dba숫자] rm dbs/*.ctl u01/*.ctl u02/*.ctl
- Data File을 삭제
[/DBA3/DBA/dba숫자] rm u03/users_01.dbf
[/DBA3/DBA/dba숫자] svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate
ORA-00210: cannot open control file '/DBA3/DBA/dba숫자/dbs/cntrlDBA숫자.ctl'
SVRMGR> shutdown abort
SVRMGR> exit
3) Recovery 시작
[/DBA3/DBA/dba숫자] cp backup/cntrlDBA숫자.ctl dbs
[/DBA3/DBA/dba숫자] cp backup/cntrlDBA숫자.ctl u01
[/DBA3/DBA/dba숫자] cp backup/cntrlDBA숫자.ctl u02
[/DBA3/DBA/dba숫자] cp backup/u03/users_01.dbf u03
[/DBA3/DBA/dba숫자] svrmgrl
SVRMGR> connect internal
SVRMGR> startup mount
SVRMGR> recover database using backup controlfile
ORA-00283: Recovery session canceled due to errors
ORA-01233: file 5 is read only - cannot recover using backup controlfile
ORA-01110: data file 5: '/DBA3/DBA/dba숫자/u03/query_01.dbf'
--> read only File이 있으면 반드시 offline시켜야 한다.
SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u03/query_01.dbf' offline;
SVRMGR> recover database using backup controlfile
ORA-00279: Change 8050 generated at 01/20/98 15:22:26 needed for thread 1
ORA-00289: Suggestion : /DBA3/DBA/dba숫자/arch_5.arc
ORA-00280: Change 8050 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
앞에서 기억한 마지막 번호까지 "Return" Key를 누르고,
Online Redo Log File 명을 Full Path로 입력
(예) /DBA3/DBA/dba숫자/u01/log1a.rdo --> 입력
그런데, 특별히 운이 좋지 않다면, 다음의 에러가 난다
(에러 메세지)
ORA-00310: archived log contains sequence 4; sequence 5 required
ORA-00334: archived log: '/DBA3/DBA/dba숫자/u01/log1a.rdo'
그렇다면, recover와 File명 입력을 다시 시도
(예)
SVRMGR> recover database using backup controlfile --> 다시 수행
/DBA3/DBA/dba숫자/u01/log2a.rdo --> 다른 offline File 명 입력
다음의 메시지를 볼 때까지 다른 offline File에도 수행
(보여야 하는 메시지)
Log applied.
Media recovery complete. --> 이 메시지가 보이면 성공한 것임
SVRMGR> alter database open resetlogs;
SVRMGR> select count(*) from scott.s_emp;
SVRMGR> select * from v$datafile; --> /DBA3/DBA/dba숫자/u03/query_01.dbf이 Offline이다.
SVRMGR> alter tablespace query_data online;
SVRMGR> select * from v$datafile;
SVRMGR> shutdown
SVRMGR> startup
SVRMGR> shutdown
SVRMGR> exit
3) 반드시 Full Backup 수행
[/DBA3/DBA/dba숫자] cp -rp u0* backup
[/DBA3/DBA/dba숫자] cp -p dbs/cntrlDBA숫자.ctl backup
[/DBA3/DBA/dba숫자] cp -p dbs/initDBA숫자.ora backup
[/DBA3/DBA/dba숫자] rm *.arc
>
※ SCENARIO 22 : Read Only Tablespace의 상태변경에 따른 Recovery -1
control file은 그대로 있고 R/O 가 R/W로 변경되고 그 때 데이터의 변동은 archiving되었다.
1) 정상적인 업무를 수행
[/DBA3/DBA/dba숫자] svrmgrl
SVRMGR> connect internal
SVRMGR> startup
2) Read Only tablespace query_data를 Read Write로 바꾸고 scott가 data를 입력함.
SVRMGR> alter tablespace query_data read write;
SVRMGR> select tablespace_name, status from dba_tablespaces;
SVRMGR> alter user scott quota 1 m on query_data;
SVRMGR> connect scott/tiger;
SVRMGR> create table query (id number) tablespace query_data;
SVRMGR> insert into query select id from s_emp;
SVRMGR> commit;
SVRMGR> connect internal;
SVRMGR> shutdown immediate
3) Failure를 만든다
- 업무 수행 중에 query_01.dbf File이 삭제되었다.
[/DBA3/DBA/dba숫자] rm /DBA3/DBA/dba숫자/u03/query_01.dbf
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> select * from scott.new_dept;
select * from scott.query
*
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/ DBA3/DBA/dba숫자 /u03/query_01.dbf'
ORA-07368: sfofi: open error, unable to open database file.
SVR4 Error: 2: No such file or directory
SVRMGR> shutdown abort;
SVRMGR> exit
4) Recovery 시작
[/DBA3/DBA/dba숫자] cp backup/u03/query_01.dbf u03 --> restore backup file
[/DBA3/DBA/dba숫자] svrmgrl
SVRMGR> connect internal
SVRMGR> startup
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/ DBA3/DBA/dba숫자 /u03/query_01.dbf'
SVRMGR> set autorecovery on
SVRMGR> recover database;
--> control file에 query_data tablespace가 read write로 되어 있어서
예전에 read only였던 사실은 중요하지 않다.
SVRMGR> alter database open;
SVRMGR> select tablespace_name, status from dba_tablespaces; --> read only 가 아니고 online
SVRMGR> select * from scott.query;
SVRMGR> shutdown
SVRMGR> exit
※ SCENARIO 23 : Read Only Tablespace의 상태변경에 따른 Recovery -2
control file은 그대로 있고 R/O 가 R/W로 변경되고 그 때 데이터의 변동은 archiving되었다.
또한 그 중간에 test라는 tablespace를 추가하였다.
Control file이 깨졌는데 R/W로 변화를 가한 후 backup을 받지 않아서 옛날 R/O시절의 control
file을 restore한다면?
1) 정상적인 업무를 수행
[/DBA3/DBA/dba숫자] svrmgrl
SVRMGR> connect internal
SVRMGR> startup
2) 새로운 tablespace를 생성하고 data를 입력한다.
SVRMGR> create tablespace test datafile '/tmp/t숫자.dbf' size 3 m; --> test tablespace 생성
SVRMGR> alter user scott quota 1 m on test;
SVRMGR> connect scott/tiger;
SVRMGR> create table test (id number) tablespace test;
SVRMGR> insert into test select id from s_emp;
SVRMGR> insert into s_emp select * from s_emp; --> 기존에 있었던 tablespace에 작업을 한다.
SVRMGR> commit;
SVRMGR> connect internal;
SVRMGR> select tablespace_name, status from dba_tablespaces;
3) Read Only tablespace query_data를 Read Write로 바꾸고 scott가 data를 입력함.
SVRMGR> alter tablespace query_data read write;
SVRMGR> select tablespace_name, status from dba_tablespaces;
SVRMGR> alter user scott quota 1 m on query_data;
SVRMGR> connect scott/tiger;
SVRMGR> create table query (id number) tablespace query_data;
SVRMGR> insert into query select id from s_emp;
SVRMGR> commit;
SVRMGR> connect internal;
SVRMGR> shutdown immediate
3) Failure를 만든다
- 업무 수행 중에 query_01.dbf File이 삭제되었다.
[/DBA3/DBA/dba숫자] rm /DBA3/DBA/dba숫자/u03/query_01.dbf
SVRMGR> connect internal
SVRMGR> startup
ORA-00205: error in identifying control file '$ORACLE_HOME/dbs/cntrlNDBA15.ctl'
ORA-07360: sfifi: stat error, unable to obtain information about file.
SVR4 Error: 2: No such file or directory
SVRMGR> shutdown abort;
SVRMGR> exit
4) Recovery 시작
[/DBA3/DBA/dba숫자]cp backup/dbs/cntrlNDBA숫자.ctl dbs
[/DBA3/DBA/dba숫자]cp backup /dbs/cntrlNDBA숫자.ctl u01
[/DBA3/DBA/dba숫자]cp backup /dbs/cntrlNDBA숫자.ctl u02
[/DBA3/DBA/dba숫자]cp backup /u03/query_01.dbf u03
[/DBA3/DBA/dba숫자] svrmgrl
SVRMGR> startup mount
SVRMGR> recover database using backup controlfile;
ORA-00283: Recovery session canceled due to errors
ORA-01233: file 6 is read only - cannot recover using backup controlfile
ORA-01110: data file 6: '/DBA3/DBA/dba숫자 /u03/query_01.dbf'
SVRMGR> alter database datafile ‘/DBA3/DBA/dba숫자 /u03/query_01.dbf' offline;
SVRMGR> recover database using backup controlfile
ORA-00279: Change 7479 generated at 06/03/98 16:52:00 needed for thread 1
ORA-00289: Suggestion : /base6/NDBA/ndba15/arch_252.arc
ORA-00280: Change 7479 for thread 1 is in sequence #252
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/base6/NDBA/ndba15/u01/log3a.rdo
Log applied. --> 이 message가 보일 때까지 계속 recovery 수행.
Media recovery complete
SVRMGR> alter database open resetlogs;
SVRMGR> select * from v$datafile; --> MISSING000x 라는 file이 있다. (t.dbf) --> 왜 이름을 모를까?
SVRMGR> alter database rename file 'MISSING0008' to '/tmp/t.dbf';
SVRMGR> alter tablespace test online;
alter tablespace test online
*
ORA-01190: control file or data file 8 is from before the last RESETLOGS
ORA-01110: data file 8: '/tmp/t.dbf'
SVRMGR> alter tablespace query_data online; --> 안되는 이유는?
alter tablespace query_data online
*
ORA-01190: control file or data file 6 is from before the last RESETLOGS
ORA-01110: data file 6: '/base6/NDBA/ndba15/u03/query_01.dbf'
SVRMGR> select count(*) from scott.s_emp;
SVRMGR> shutdown
SVRMGR> exit
※ SCENARIO 24 : Read Only Tablespace의 상태변경에 따른 Recovery -3
control file은 그대로 있고 R/O 가 R/W로 변경되고 그 때 데이터의 변동은 archiving되었다.
또한 그 중간에 test라는 tablespace를 추가하였다. Control file이 깨졌는데 R/W로 변화를
가한 후 backup을 이용하면 복구가 가능하다. 항상 control file을 backup.
1) 정상적인 업무를 수행
[/DBA3/DBA/dba숫자] svrmgrl
SVRMGR> connect internal
SVRMGR> startup
2) 새로운 tablespace를 생성하고 data를 입력한다.
SVRMGR> create tablespace test datafile '/tmp/t숫자.dbf' size 3 m; --> test tablespace 생성
SVRMGR> alter user scott quota 1 m on test;
SVRMGR> connect scott/tiger;
SVRMGR> create table test (id number) tablespace test;
SVRMGR> insert into test select id from s_emp;
SVRMGR> insert into s_emp select * from s_emp; --> 기존에 있었던 tablespace에 작업을 한다.
SVRMGR> commit;
SVRMGR> connect internal;
SVRMGR> select tablespace_name, status from dba_tablespaces;
3) Read Only tablespace query_data를 Read Write로 바꾸고 scott가 data를 입력함.
SVRMGR> alter tablespace query_data read write;
SVRMGR> select tablespace_name, status from dba_tablespaces;
SVRMGR> alter user scott quota 1 m on query_data;
SVRMGR> connect scott/tiger;
SVRMGR> create table query (id number) tablespace query_data;
SVRMGR> insert into query select id from s_emp;
SVRMGR> commit;
SVRMGR> connect internal;
SVRMGR> shutdown immediate
SVRMGR> !ps -ef | grep NDBA15
ndba15 25748 25747 0 11:38:49 ? 0:03 oracleNDBA15
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
ndba15 25766 25765 2 11:39:26 pts/40 0:00 grep NDBA15
ndba15 25102 1 6 11:20:43 ? 5:37 oracleNDBA15
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
SVRMGR> !ls $ORACLE_HOME/trace
alert_NDBA15.log ora_25748.trc
SVRMGR> !mv $ORACLE_HOME/trace/ ora_25748.trc /tmp/c.sql
SVRMGR> !vi /tmp/c.sql
SVRMGR
3) Failure를 만든다
- 업무 수행 중에 control File이 삭제되었다.
[/DBA3/DBA/dba숫자] rm */cntrl*.ctl
[/DBA3/DBA/dba숫자] svrmgrl
SVRMGR> connect internal
SVRMGR> startup
ORA-00205: error in identifying control file '$ORACLE_HOME/dbs/cntrlNDBA15.ctl'
ORA-07360: sfifi: stat error, unable to obtain information about file.
SVR4 Error: 2: No such file or directory
SVRMGR> shutdown abort;
SVRMGR> shutdown abort;
4) Recovery 시작
SVRMGR> @/tmp/c.sql
SVRMGR> select count(*) from scott.s_emp;
SVRMGR> shutdown
SVRMGR> exit
※ SCENARIO 25 : Recovery from Online Backup - Data File, Control File 유실
1) 정상적인 업무를 수행
- Database를 기동
[/DBA3/DBA/dba숫자] svrmgrl
SVRMGR> connect internal
SVRMGR> startup
- 업무 수행 & Archived Log File 확인
SVRMGR> @?/labs/more_emp
SVRMGR> !ls -la *.arc --> 마지막 번호 기억
2) Failure를 만든다.
- Data file, Control file 삭제
SVRMGR> shutdown abort
SVRMGR> exit
[/DBA3/DBA/dba숫자] rm u01/system.dbf u03/rbs_01.dbf
[/DBA3/DBA/dba숫자] rm dbs/*.ctl u01/*.ctl u02/*.ctl
3) Recovery 시작
- Data file, Control file Restore
[/DBA3/DBA/dba숫자] cd online_backup
[/DBA3/DBA/dba숫자/online_backup] ls
[/DBA3/DBA/dba숫자/online_backup] cp system.dbf $ORACLE_HOME/u01
[/DBA3/DBA/dba숫자/online_backup] cp rbs_01.dbf $ORACLE_HOME/u03
[/DBA3/DBA/dba숫자/online_backup] cp backup_control.ctl $ORACLE_HOME/dbs/cntrlDBA숫자.ctl
[/DBA3/DBA/dba숫자/online_backup] cp backup_control.ctl $ORACLE_HOME/u01/cntrlDBA숫자.ctl
[/DBA3/DBA/dba숫자/online_backup] cp backup_control.ctl $ORACLE_HOME/u02/cntrlDBA숫자.ctl
[/DBA3/DBA/dba숫자/online_backup] cd
SVRMGR> connect internal
SVRMGR> startup mount
SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u03/query_01.dbf' offline;
SVRMGR> recover database using backup controlfile
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
앞에서 기억한 마지막 번호까지 "Return" Key를 누르고,
Online Redo Log File 명을 Full Path로 입력
(예) /DBA3/DBA/dba숫자/u01/log1a.rdo --> 입력
Error가 나면, recover와 다른 Online Redo Log File명을 다시 입력
Media recovery complete. --> 이 메세지가 보이면 성공한 것임
SVRMGR> alter database open resetlogs;
SVRMGR> alter tablespace query_data online;
SVRMGR> select count(*) from scott.s_emp;
SVRMGR> shutdown
SVRMGR> exit
4) 반드시 Full Backup 해야 함.
[/DBA3/DBA/dba숫자] cp -rp u0* backup
[/DBA3/DBA/dba숫자] cp -p dbs/cntrlDBA숫자.ctl backup
[/DBA3/DBA/dba숫자] cp -p dbs/initDBA숫자.ora backup
[/DBA3/DBA/dba숫자] rm *.arc
※ SCENARIO 26 : Recovery from Online Backup - File들 모두가 사라졌다.
게다가, Archived Redo Log File의 일부가 없고,
Data File Backup도 일부 없다.
0) 바로 앞의 실습(SCENARIO 15번)을 하였다면 Redo Log가 Reset 되었으므로
Online Backup을 다시 받고 나서 아래의 과정으로 실습하여야 한다.
Online Backup은 <시나리오12>의 실습 참고.
1) 정상적인 업무를 수행
- Database를 기동
SVRMGR> connect internal
SVRMGR> startup
- 업무 수행 & Archived Log File 확인
SVRMGR> !ls -la *.arc --> 현재의 Archived Log File 확인
SVRMGR> @?/labs/more_emp
2) Failure를 만든다.
- Database의 모든 Data file들, 모든 Control file들, 모든 Online Redo Log file들,
Parameter file 즉, 몽조리 사라졌다. 난리 났다.
SVRMGR> shutdown abort
SVRMGR> exit
[/DBA3/DBA/dba숫자] rm u01/* u02/* u03/* u04/* dbs/*.ctl
3) Recovery 시작
- 게다가 Archived Redo Log File 마지막 2개도 사라졌다.
[/DBA3/DBA/dba숫자] ls *.arc
[/DBA3/DBA/dba숫자] rm -i *.arc --> 알아서 마지막 2개 삭제
[/DBA3/DBA/dba숫자] ls *.arc --> 존재하는 마지막 File의 번호를 기억
- File들을 Restore한다. 그런데 index_01.dbf File의 Backup이 사라졌다. 기절하시겠다.
[/DBA3/DBA/dba숫자] cd online_backup
[/DBA3/DBA/dba숫자/online_backup] ls
[/DBA3/DBA/dba숫자/online_backup] cp initDBA숫자.ora $ORACLE_HOME/dbs
[/DBA3/DBA/dba숫자/online_backup] cp backup_control.ctl $ORACLE_HOME/dbs/cntrlDBA숫자.ctl
[/DBA3/DBA/dba숫자/online_backup] cp backup_control.ctl $ORACLE_HOME/u01/cntrlDBA숫자.ctl
[/DBA3/DBA/dba숫자/online_backup] cp backup_control.ctl $ORACLE_HOME/u02/cntrlDBA숫자.ctl
[/DBA3/DBA/dba숫자/online_backup] cp system.dbf $ORACLE_HOME/u01
[/DBA3/DBA/dba숫자/online_backup] cp query_01.dbf rbs_01.dbf users_01.dbf $ORACLE_HOME/u03
[/DBA3/DBA/dba숫자/online_backup] cp temp_01.dbf $ORACLE_HOME/u04
[/DBA3/DBA/dba숫자/online_backup] cd
- Online Redo Log file이 없으니까 Incomplete Recovery 수행
[/DBA3/DBA/dba숫자]svrmgrl
SVRMGR> connect internal
SVRMGR> startup mount
SVRMGR> recover database using backup controlfile until cancel
ORA-00283: Recovery session canceled due to errors
ORA-01233: file 5 is read only - cannot recover using backup controlfile
ORA-01110: data file 5: '/DBA3/DBA/dba숫자/u03/query_01.dbf'
SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u03/query_01.dbf' offline;
SVRMGR> recover database using backup controlfile until cancel
ORA-00283: Recovery session canceled due to errors
ORA-01157: cannot identify data file 6 - file not found
ORA-01110: data file 6: '/DBA3/DBA/dba숫자/u01/index_01.dbf'
SVRMGR> alter database datafile '/DBA3/DBA/dba숫자/u01/index_01.dbf' offline;
SVRMGR> recover database using backup controlfile until cancel
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel --> 마지막 Log 번호까지 처리하고 나서 "cancel" 입력
Media recovery cancelled.
SVRMGR> alter database open resetlogs;
SVRMGR> select * from v$datafile;
SVRMGR> alter tablespace query_data online;
SVRMGR> select * from dba_tablespaces;
SVRMGR> drop tablespace user_index; --> Backup 자체가 존재하지 않으므로 포기하는 수 밖에 없다.
SVRMGR> create tablespace user_index
datafile '/DBA3/DBA/dba숫자/u01/index_01.dbf' size 500k;
SVRMGR> select count(*) from scott.s_emp;
SVRMGR> shutdown
SVRMGR> exit
4) 반드시 Full Backup 해야 함.
[/DBA3/DBA/dba숫자]cp -rp u0* backup
[/DBA3/DBA/dba숫자]cp -p dbs/cntrlDBA숫자.ctl backup
[/DBA3/DBA/dba숫자]cp -p dbs/initDBA숫자.ora backup
[/DBA3/DBA/dba숫자]rm *.arc
※ SCENARIO 27 : Recover with No Backup
1) 정상적인 업무
[/DBA3/DBA/dba숫자]svrmgrl
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> @?/labs/more_emp
SVRMGR> !ls *.arc
SVRMGR> create tablespace new_data
datafile '$ORACLE_HOME/u04/new_data.dbf' size 500k reuse ;
SVRMGR> create table scott.new_data tablespace new_data
as select * from scott.s_emp;
SVRMGR> @?/labs/more_emp
2) Failure
SVRMGR> shutdown abort
SVRMGR> exit
[/DBA3/DBA/dba숫자] ls -la $ORACLE_HOME/u04
[/DBA3/DBA/dba숫자] rm $ORACLE_HOME/u04/new_data.dbf
[/DBA3/DBA/dba숫자] ls -la $ORACLE_HOME/u04
3) Recovery
[/DBA3/DBA/dba숫자] svrmgrl
SVRMGR> connect internal
SVRMGR> startup
ORA-01157: cannot identify data file 7 - file not found
ORA-01110: data file 7: '/DBA3/DBA/dbapjw/u04/new_data.dbf'
SVRMGR> alter database
create datafile '/DBA3/DBA/dba숫자/u04/new_data.dbf';
SVRMGR> !ls -la $ORACLE_HOME/u04
SVRMGR> recover datafile '/DBA3/DBA/dba숫자/u04/new_data.dbf'
auto 입력
SVRMGR> alter database open;
4) 확인
SVRMGR> select count(*) from scott.new_data;
5) 원상 복구
SVRMGR> drop tablespace new_data including contents;
SVRMGR> !rm /DBA3/DBA/dba숫자/u04/new_data.dbf
SVRMGR> shutdown
SVRMGR> exit
※ SCENARIO 28 : Incremental export 와 direct path
$ sqlplus scott/tiger
SQL> SELECT COUNT(1) FROM s_emp;
COUNT(1)
----------
44
SQL> exit
$ exp userid=sys/change_on_install full=y file=Comp001.dmp inctype=complete
$ sqlplus system/manager
SQL> @?/labs/more_emp
SQL> exit
$exp userid=sys/change_on_install full=y file=Inc002.dmp inctype=incremental
$ sqlplus system/manager
SQL> @?/labs/more_emp
$ exp userid=sys/change_on_install full=y file=Inc003.dmp inctype=incremental
$ sqlplus system/manager
SQL> @?/labs/more_emp
$ exp userid=sys/change_on_install full=y file=Cum004.dmp inctype=cumulative
$ sqlplus system/manager
SQL> @?/labs/more_emp
$ exp userid=sys/change_on_install full=y file=Inc005.dmp inctype=incremental
$ sqlplus scott/tiger
SQL> select count(1) from s_emp;
SQL> drop table s_emp;
$ imp userid=sys/change_on_install full=y file=Comp001.dmp ignore=y
$ imp userid=sys/change_on_install full=y file=Cum004.dmp ignore=y
$ imp userid=sys/change_on_install full=y file=Inc005.dmp ignore=y
$ sqlplus scott/tiger
SQL> select count(1) from s_emp;
$
$
*********************************************************
** Direct mode Export Test **
*********************************************************
$
$
$vi direct.sh
date > Dstart
exp userid=scott/tiger table=s_emp file=direct.dmp direct=y
date > Dend
$vi conv.sh
date > Cstart
exp userid=scott/tiger table=s_emp file=conv.dmp direct=n
date > Cend
※ SCENARIO 29 : standby database 생성
PRIMARY DB part
Script started on Tue Jan 20 19:53:46 1998
$ pwd
/disk2/inst/parkjy/oracle
$ set | grep ORACLE
ORACLE_HOME=/disk2/inst/parkjy/oracle
ORACLE_SID=KELLOGG
$ svrmgrl
Oracle Server Manager Release 2.3.2.0.0 - Production
Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.
O racle7 Server Release 7.3.2.1.0 - Production Release
With the distributed and parallel query options
PL/SQL Release 2.3.2.0.0 - Production
SVRMGR> connect internal
Connected.
SVRMGR> @/tmp/more_emp;
Statement processed.
Statement processed.
4 rows processed.
Statement processed.
Statement processed.
4 rows processed.
Statement processed.
Statement processed.
4 rows processed.
Statement processed.
Statement processed.
Statement processed.
44 rows processed.
Statement processed.
Statement processed.
COUNT(*)
----------
44
1 row selected.
SVRMGR> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
$ pwd
/disk2/inst/parkjy/oracle
$ tar cvf dbf.tar u0?/*
seek = 0K a u01/system.dbf 10242K
seek = 10243K a u02/log1a.rdo 152K
seek = 10395K a u02/log1c.rdo 152K
seek = 10548K a u02/log1d.rdo 152K
seek = 10700K a u02/log2a.rdo 152K
seek = 10853K a u02/log3a.rdo 152K
seek = 11005K a u03/log1b.rdo 152K
seek = 11158K a u03/log2b.rdo 152K
seek = 11310K a u03/log3b.rdo 152K
seek = 11463K a u04/rbs_01.dbf 1026K
seek = 12489K a u05/users_01.dbf 5122K
seek = 17612K a u06/index_01.dbf 502K
seek = 18114K a u07/temp_01.dbf 1026K
seek = 19141K a u08/query_01.dbf 502K
$ ftp krnile3
Connected to krnile3.kr.oracle.com.
220 krnile3 FTP server (UNIX(r) System V Release 4.0) ready.
Name (krnile3:parkjy):
331 Password required for parkjy.
Password:
230 User parkjy logged in.
ftp> bin
200 Type set to I.
ftp> put dbf.tar
200 PORT command successful.
150 Binary data connection for dbf.tar (152.69.16.52,1842).
226 Transfer complete.
local: dbf.tar remote: dbf.tar
20116480 bytes sent in 20 seconds (9.6e+02 Kbytes/s)
ftp> pwd
257 "/disk2/inst/parkjy" is current directory.
ftp> quit
221 Goodbye.
$ svrmgrl
Oracle Server Manager Release 2.3.2.0.0 - Production
Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.
Oracle7 Server Release 7.3.2.1.0 - Production Release
With the distributed and parallel query options
PL/SQL Release 2.3.2.0.0 - Production
SVRMGR> connect internal
Connected to an idle instance.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 2113588 bytes
Fixed Size 40436 bytes
Variable Size 1860160 bytes
Database Buffers 204800 bytes
Redo Buffers 8192 bytes
Database mounted.
Database opened.
SVRMGR> alter database create standby controlfile as '/tmp/stnb.ctl';
Statement processed.
SVRMGR> alter system archive log current;
Statement processed.
SVRMGR> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /disk2/inst/parkjy/oracle/arch/arc
Oldest online log sequence 35
Next log sequence to archive 37
Current log sequence 37
SVRMGR> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
$ cd /tmp
$ ftp krnile3
Connected to krnile3.kr.oracle.com.
220 krnile3 FTP server (UNIX(r) System V Release 4.0) ready.
Name (krnile3:parkjy):
331 Password required for parkjy.
Password:
230 User parkjy logged in.
ftp> bin
200 Type set to I.
ftp> put stnb.ctl
200 PORT command successful.
150 Binary data connection for stnb.ctl (152.69.16.52,1851).
226 Transfer complete.
local: stnb.ctl remote: stnb.ctl
145408 bytes sent in 0.1 seconds (1.4e+03 Kbytes/s)
ftp> pwd
257 "/disk2/inst/parkjy" is current directory.
ftp> quit
221 Goodbye.
$ cd
$ cd oracle/arch
$ ls
arc_1.arc arc_16.arc arc_22.arc arc_29.arc arc_35.arc arc_6.arc
arc_10.arc arc_17.arc arc_23.arc arc_3.arc arc_36.arc arc_7.arc
arc_11.arc arc_18.arc arc_24.arc arc_30.arc arc_4.arc arc_8.arc
arc_12.arc arc_19.arc arc_25.arc arc_31.arc arc_5.arc arc_9.arc
arc_13.arc arc_2.arc arc_26.arc arc_32.arc arc_514.arc
arc_14.arc arc_20.arc arc_27.arc arc_33.arc arc_515.arc
arc_15.arc arc_21.arc arc_28.arc arc_34.arc arc_516.arc
$ r ftp krnile3
Connected to krnile3.kr.oracle.com.
220 krnile3 FTP server (UNIX(r) System V Release 4.0) ready.
Name (krnile3:parkjy):
331 Password required for parkjy.
Password:
230 User parkjy logged in.
ftp> bin
200 Type set to I.
ftp> put arc_36.arc
200 PORT command successful.
150 Binary data connection for arc_36.arc (152.69.16.52,1854).
226 Transfer complete.
local: arc_36.arc remote: arc_36.arc
14336 bytes sent in 0 seconds (14 Kbytes/s)
ftp> quit
221 Goodbye.
STANDBY DB Part
Script started on Tue Jan 20 18:56:25 1998
$ pwd
/disk2/inst/parkjy
$ set | grep ORACLE
ORACLE_HOME=/disk2/inst/parkjy/oracle
ORACLE_SID=KELLOGG
$ ls
C++ TEST arc_36.arc dbf.tar oracle work
PROC WEB arch dbs_standby stnb.ctl
$ mv arc_36.* orace le/arch
$ mv stnb.ctl oracle/dbs/cntrlKELLOGG.ctl
$ ls oracle/dbs
cntrlKELLOG.ctl create_db.sql log1KELLOGG.dbf s2.ctl
cntrlKELLOGG.bak dbs1KELLOGG.dbf log2KELLOGG.dbf sql.bsq
cntrlKELLOGG.ctl destroydb mkdb standby.ctl
create_db.sh initKELLOGG.ora s.ctl
$ mv dbf.tar oracle
$ cd oracle
$ tar xvf dbf.. tar
x u01/system.dbf, 10487808 bytes, 10242K
x u02/log1a.rdo, 155648 bytes, 152K
x u02/log1c.rdo, 155648 bytes, 152K
x u02/log1d.rdo, 155648 bytes, 152K
x u02/log2a.rdo, 155648 bytes, 152K
x u02/log3a.rdo, 155648 bytes, 152K
x u03/log1b.rdo, 155648 bytes, 152K
x u03/log2b.rdo, 155648 bytes, 152K
x u03/log3b.rdo, 155648 bytes, 152K
x u04/rbs_01.dbf, 1050624 bytes, 1026K
x u05/users_01.dbf, 5244928 bytes, 5122K
x u06/index_01.dbf, 514048 bytes, 502K
x u07/temp_01.dbf, 1050624 bytes, 1026K
x u08/query_01.dbf, 514048 bytes, 502K
$ svrmgrl
Oracle Server Manager Release 2.1.4.0.0 - Production
Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.
Oracle7 Server Release 7.3.2.1.0 - Production Release
With the distributed and parallel query options
PL/SQL Release 2.3.2.0.0 - Production
SVRMGR> connect internal
Connected to an idle instance.
SVRMGR> startup nomount
ORACLE instance started.
Total System Global Area 2113588 bytes
Fixed Size 40436 bytes
Variable Size 1860160 bytes
Database Buffers 204800 bytes
Redo Buffers 8192 bytes
SVRMGR> alter database mount standby database;
Statement processed.
SVRMGR> recover standby database;
ORA-00279: Change 9735 generated at 01/20/98 19:54:43 needed for thread 1
ORA-00289: Suggestion : /disk2/inst/parkjy/oracle/arch/arc_36.arc
ORA-00280: Change 9735 for thread 1 is in sequence #36
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
ORA-00279: Change 9743 generated at 01/20/98 19:57:41 needed for thread 1
ORA-00289: Suggestion : /disk2/inst/parkjy/oracle/arch/arc_37.arc
ORA-00280: Change 9743 for thread 1 is in sequence #37
ORA-00278: Logfile '/disk2/inst/parkjy/oracle/arch/arc_36.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel;
Media recovery cancelled.
SVRMGR> alter database activate standby database;
Statement processed.
SVRMGR> shutdown;
sORA-01507: database not mounted
Database dismounted.
ORACLE instance shut down.
SVGMGR> startup
SVRMGR> ORACLE instance started.
Total System Global Area 2113588 bytes
Fixed Size 40436 bytes
Variable Size 1860160 bytes
Database Buffers 204800 bytes
Redo Buffers 8192 bytes
Database mounted.
Database opened.
SVRMGR> select count(*)
2> from d s_emp.scott scott.s_emp;
COUNT(*)
----------
44
1 row selected.
SVRMGR> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete
※ SCENARIO 30 : Catalog DB를 이용한 복구 Oracle8
Part I.
1. Try starting the rman program.
What happens and why?
$ rman
Recovery Manager: Release 8.0.2.0.0 - Beta
RMAN-06005: connected to target database: DBA15
RMAN-06009: using target database controlfile instead of recovery catalog
RMAN>
Recovery manager connects to your database expecting no recovery
catalog. All information is retrieved from the controlfile.
2. Disconnect from the recovery manager.
Connect to the recovery catalog using dbaXX/dbaXX@DBA16 as connect string.
You should connect to your targeted database as internal/admin@DBAXX.
XX is your account number.
Do not specify a log file that way all messages will be written to
your screen.
$ rman target=\"internal/admin@DBA15\" rcvcat=\"dba15/dba15@DBA16\"
Recovery Manager: Release 8.0.2.0.0 - Beta
RMAN-06005: connected to target database: DBA15
RMAN-06008: connected to recovery catalog database
RMAN>
3. Register your database with the recovery catalog.
RMAN> register database;
RMAN-08006: database registered in recovery catalog
RMAN-08002: starting full resync of recovery catalog
RMAN-08029: snapshot controlfile name set to default value: ?/dbs/snapcf_@.f
RMAN-08004: full resync complete
RMAN>
4. List the incarnation of the database.
RMAN> list incarnation of database;
RMAN-06240: List of Database Incarnations
RMAN-06241: DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
RMAN-06242: ------- ------- -------- ---------------- --- ---------- -------
RMAN-06243: 1 2 DBA15 4045670789 YES 1 18-FEB-97
RMAN>
5. Try to do a resync of the recovery catalog.
RMAN> resync catalog;
RMAN-08002: starting full resync of recovery catalog
RMAN-08004: full resync complete
RMAN>
6. Exit the recovery manager and reconnect directing your output to a logfile.
rman target=\"internal/admin@DBA15\" rcvcat=\"dba15/dba15@DBA16\" \
> msglog=\"rmanDBA15.log\"
7. Try to do a resync one more time.
RMAN> resync catalog;
RMAN>
8. Exit and investigate your logfile.
RMAN> exit
$ cat rmanDBA15.log
Recovery Manager: Release 8.0.2.0.0 - Beta
RMAN-06005: connected to target database: DBA15
RMAN-06008: connected to recovery catalog database
RMAN> resync catalog;
RMAN-08002: starting full resync of recovery catalog
RMAN-08004: full resync complete
RMAN> exit
Recovery Manager complete.
Part II.
1. Investigate the script cre_back.rman
Change "YOUR_PATH" to contain your HOME directory
(Can be displayed using pwd from the unix prompt)
$ cat cre_back.rman
create script back_db_full {
allocate channel d1 type disk;
backup full filesperset 4
(database include current controlfile
format "/YOUR_PATH/BACK/back_DBA15_full.%s.%p");
release channel d1;}
2. Connect to the recovery catalog database and use your database
as the targeted database.
Create and run the backup script
What happens and why ?
Note that Recovery Manager does not accept the @script as svrmgr or
sqlplus, so you either have to cut and paste or type it in.
$ rman target=\"internal/admin@DBA15\" rcvcat=\"dba15/dba15@DBA16\"
Recovery Manager: Release 8.0.2.0.0 - Beta
RMAN-06005: connected to target database: DBA15
RMAN-06008: connected to recovery catalog database
RMAN> create script back_db_full {
2> allocate channel d1 type disk;
3> backup full filesperset 4
4> (database include current controlfile
5> format "/users/dba15/BACK/back_DBA15_full.%s.%p");
6> release channel d1;}
RMAN-08085: created script back_db_full
RMAN> run { execute script back_db_full;}
RMAN-08030: allocated channel: d1
RMAN-08500: channel d1: sid=9 devtype=DISK
RMAN-08008: channel d1: started datafile backupset
RMAN-08502: set_count=17 set_stamp=296089196
RMAN-03007: exception occurred during execution, error is retryable
RMAN-07004: unhandled exception during command execution on channel d1
RMAN-10032: unhandled exception during execution of job step 1: ORA-06512: at line 57
RMAN-10035: exception raised in RPC: ORA-19624: operation failed, retry possible
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 312
RMAN-10031: ORA-19624 occurred during call to X$DBMS_BACKUP_RESTORE.BACKUPDATAFILE
This happens because your database is running in NOARCHIVELOG mode and a backup
from recovery manager can only be performed with the database in mounted state.
3. Shutdown your database and restart it in mount mode.
Rerun your script.
RMAN> exit;
Recovery Manager complete.
$ svrmgrl
Oracle Server Manager Release 3.0.2.0.0 - Beta
Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.
Oracle8 Server Release 8.0.2.0.0 - Beta
With the distributed, heterogeneous, replication, objects,
parallel query and Spatial Data options
PL/SQL Release 3.0.2.0.0 - Beta
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup mount pfile=initDBA15.ora
ORACLE instance started.
Total System Global Area 4635056 bytes
Fixed Size 43724 bytes
Variable Size 4116196 bytes
Database Buffers 409600 bytes
Redo Buffers 65536 bytes
Database mounted.
SVRMGR> exit
Server Manager complete.
$ rman target=\"internal/admin@DBA15\" rcvcat=\"dba15/dba15@DBA16\"
Recovery Manager: Release 8.0.2.0.0 - Beta
RMAN-06005: connected to target database: DBA15
RMAN-06008: connected to recovery catalog database
RMAN> run { execute script back_db_full;}
RMAN-08030: allocated channel: d1
RMAN-08500: channel d1: sid=8 devtype=DISK
RMAN-08008: channel d1: started datafile backupset
RMAN-08502: set_count=18 set_stamp=296089658
RMAN-08010: channel d1: including datafile number 1 in backupset
RMAN-08010: channel d1: including datafile number 2 in backupset
RMAN-08010: channel d1: including datafile number 3 in backupset
RMAN-08010: channel d1: including datafile number 4 in backupset
RMAN-08013: channel d1: piece 1 created
RMAN-08503: piece handle=/users/dba15/BACK/back_DBA15_full.18.1 comment=NONE
RMAN-08008: channel d1: started datafile backupset
RMAN-08502: set_count=19 set_stamp=296089694
RMAN-08010: channel d1: including datafile number 5 in backupset
RMAN-08010: channel d1: including datafile number 6 in backupset
RMAN-08010: channel d1: including datafile number 7 in backupset
RMAN-08010: channel d1: including datafile number 8 in backupset
RMAN-08013: channel d1: piece 1 created
RMAN-08503: piece handle=/users/dba15/BACK/back_DBA15_full.19.1 comment=NONE
RMAN-08008: channel d1: started datafile backupset
RMAN-08502: set_count=20 set_stamp=296089706
RMAN-08010: channel d1: including datafile number 9 in backupset
RMAN-08010: channel d1: including datafile number 10 in backupset
RMAN-08010: channel d1: including datafile number 11 in backupset
RMAN-08010: channel d1: including datafile number 12 in backupset
RMAN-08013: channel d1: piece 1 created
RMAN-08503: piece handle=/users/dba15/BACK/back_DBA15_full.20.1 comment=NONE
RMAN-08008: channel d1: started datafile backupset
RMAN-08502: set_count=21 set_stamp=296089717
RMAN-08010: channel d1: including datafile number 13 in backupset
RMAN-08010: channel d1: including datafile number 14 in backupset
RMAN-08010: channel d1: including datafile number 15 in backupset
RMAN-08010: channel d1: including datafile number 16 in backupset
RMAN-08013: channel d1: piece 1 created
RMAN-08503: piece handle=/users/dba15/BACK/back_DBA15_full.21.1 comment=NONE
RMAN-08008: channel d1: started datafile backupset
RMAN-08502: set_count=22 set_stamp=296089723
RMAN-08010: channel d1: including datafile number 17 in backupset
RMAN-08010: channel d1: including datafile number 18 in backupset
RMAN-08010: channel d1: including datafile number 19 in backupset
RMAN-08011: channel d1: including current controlfile in backupset
RMAN-08013: channel d1: piece 1 created
RMAN-08503: piece handle=/users/dba15/BACK/back_DBA15_full.22.1 comment=NONE
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-08031: released channel: d1
RMAN>
4. Startup the database and force some log switches using
the alter system switch logfile command.
svrmgrl
Oracle Server Manager Release 3.0.2.0.0 - Beta
Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.
Oracle8 Server Release 8.0.2.0.0 - Beta
With the distributed, heterogeneous, replication, objects,
parallel query and Spatial Data options
PL/SQL Release 3.0.2.0.0 - Beta
SVRMGR> connect internal
Connected.
SVRMGR> alter database open;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR>
5. Shutdown your database and remove the datafiles, all the logfiles and
all the controlfiles.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
$ rm *.dbf *.ctl *.log
6. Investigate the script cre_rec.rman
Change "YOUR_PATH" to contain your HOME directory
(Can be displayed using pwd from the unix prompt)
$ cat cre_rec.rman
create script rec_db_full{
allocate channel d1 type disk;
restore controlfile to "/YOUR_PATH/control1.ctl";
restore database;
release channel d1;}
7. Startup your instance.
$ svrmgrl
Oracle Server Manager Release 3.0.2.0.0 - Beta
Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.
Oracle8 Server Release 8.0.2.0.0 - Beta
With the distributed, heterogeneous, replication, objects,
parallel query and Spatial Data options
PL/SQL Release 3.0.2.0.0 - Beta
SVRMGR> connect internal
Connected.
SVRMGR> startup nomount pfile=initDBA15.ora
ORACLE instance started.
Total System Global Area 4635056 bytes
Fixed Size 43724 bytes
Variable Size 4116196 bytes
Database Buffers 409600 bytes
Redo Buffers 65536 bytes
SVRMGR> exit
Server Manager complete.
8. Connect to the recovery catalog and your instance.
Run the content of cre_rec.rman.
$ rman target=\"internal/admin@DBA15\" rcvcat=\"dba15/dba15@DBA16\"
Recovery Manager: Release 8.0.2.0.0 - Beta
RMAN-06006: connected to target database: DBA15 (not mounted)
RMAN-06008: connected to recovery catalog database
RMAN> create script rec_db_full{
2> allocate channel d1 type disk;
3> restore controlfile to "/users/dba15/control1.ctl";
4> restore database;
5> sql "alter database mount";
6> release channel d1;}
RMAN-08085: created script rec_db_full
9. Run the script rec_db_full.
RMAN> run {execute script "rec_db_full";}
MAN-08030: allocated channel: d1
RMAN-08500: channel d1: sid=6 devtype=DISK
RMAN-08016: channel d1: started datafile restore
RMAN-08021: channel d1: restoring controlfile
RMAN-08505: output filename=/users/dba15/control1.ctl
RMAN-08023: channel d1: restored backup piece 1
RMAN-08511: piece handle=/users/dba15/BACK/back_DBA15_full.22.1 params=NULL
RMAN-08024: channel d1: restore complete
RMAN-08016: channel d1: started datafile restore
RMAN-08019: channel d1: restoring datafile number 1
RMAN-08509: destination for restored datafile number=1 filename=/users/dba15/systemDBA15.dbf
RMAN-08019: channel d1: restoring datafile number 2
RMAN-08509: destination for restored datafile number=2 filename=/users/dba15/rbsDBA15.dbf
RMAN-08019: channel d1: restoring datafile number 3
RMAN-08509: destination for restored datafile number=3 filename=/users/dba15/tempDBA15.dbf
RMAN-08019: channel d1: restoring datafile number 4
RMAN-08509: destination for restored datafile number=4 filename=/users/dba15/data01DBA15_1.dbf
RMAN-08023: channel d1: restored backup piece 1
RMAN-08511: piece handle=/users/dba15/BACK/back_DBA15_full.18.1 params=NULL
RMAN-08024: channel d1: restore complete
RMAN-08016: channel d1: started datafile restore
RMAN-08019: channel d1: restoring datafile number 5
RMAN-08509: destination for restored datafile number=5 filename=/users/dba15/data01DBA15_2.dbf
RMAN-08019: channel d1: restoring datafile number 6
RMAN-08509: destination for restored datafile number=6 filename=/users/dba15/data02DBA15_1.dbf
RMAN-08019: channel d1: restoring datafile number 7
RMAN-08509: destination for restored datafile number=7 filename=/users/dba15/data02DBA15_2.dbf
RMAN-08019: channel d1: restoring datafile number 8
RMAN-08509: destination for restored datafile number=8 filename=/users/dba15/data03DBA15_1.dbf
RMAN-08023: channel d1: restored backup piece 1
RMAN-08511: piece handle=/users/dba15/BACK/back_DBA15_full.19.1 params=NULL
RMAN-08024: channel d1: restore complete
RMAN-08016: channel d1: started datafile restore
RMAN-08019: channel d1: restoring datafile number 9
RMAN-08509: destination for restored datafile number=9 filename=/users/dba15/data03DBA15_2.dbf
RMAN-08019: channel d1: restoring datafile number 10
RMAN-08509: destination for restored datafile number=10 filename=/users/dba15/data04DBA15_1.dbf
RMAN-08019: channel d1: restoring datafile number 11
RMAN-08509: destination for restored datafile number=11 filename=/users/dba15/data04DBA15_2.dbf
RMAN-08019: channel d1: restoring datafile number 12
RMAN-08509: destination for restored datafile number=12 filename=/users/dba15/index01DBA15_1.dbf
RMAN-08023: channel d1: restored backup piece 1
RMAN-08511: piece handle=/users/dba15/BACK/back_DBA15_full.20.1 params=NULL
RMAN-08024: channel d1: restore complete
RMAN-08016: channel d1: started datafile restore
RMAN-08019: channel d1: restoring datafile number 13
RMAN-08509: destination for restored datafile number=13 filename=/users/dba15/index01DBA15_2.dbf
RMAN-08019: channel d1: restoring datafile number 14
RMAN-08509: destination for restored datafile number=14 filename=/users/dba15/index02DBA15_1.dbf
RMAN-08019: channel d1: restoring datafile number 15
RMAN-08509: destination for restored datafile number=15 filename=/users/dba15/index02DBA15_2.dbf
RMAN-08019: channel d1: restoring datafile number 16
RMAN-08509: destination for restored datafile number=16 filename=/users/dba15/index03DBA15_1.dbf
RMAN-08023: channel d1: restored backup piece 1
RMAN-08511: piece handle=/users/dba15/BACK/back_DBA15_full.21.1 params=NULL
RMAN-08024: channel d1: restore complete
RMAN-08016: channel d1: started datafile restore
RMAN-08019: channel d1: restoring datafile number 17
RMAN-08509: destination for restored datafile number=17 filename=/users/dba15/index03DBA15_2.dbf
RMAN-08019: channel d1: restoring datafile number 18
RMAN-08509: destination for restored datafile number=18 filename=/users/dba15/index04DBA15_1.dbf
RMAN-08019: channel d1: restoring datafile number 19
RMAN-08509: destination for restored datafile number=19 filename=/users/dba15/index04DBA15_2.dbf
RMAN-08023: channel d1: restored backup piece 1
RMAN-08511: piece handle=/users/dba15/BACK/back_DBA15_full.22.1 params=NULL
RMAN-08024: channel d1: restore complete
RMAN-08031: released channel: d1
RMAN>
10. Exit recovery manager.
Enter server manager and do a "fake" recovery using :
recover database until cancel using backup controlfile;
RMAN> exit
Recovery Manager complete.
$ svrmgrl
Oracle Server Manager Release 3.0.2.0.0 - Beta
Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.
Oracle8 Server Release 8.0.2.0.0 - Beta
With the distributed, heterogeneous, replication, objects,
parallel query and Spatial Data options
PL/SQL Release 3.0.2.0.0 - Beta
SVRMGR> connect internal
Connected.
SVRMGR> recover database until cancel using backup controlfile;
ORA-00279: change 134717 generated at 03/17/97 23:05:27 needed for thread 1
ORA-00289: suggestion : /oracle/app/oracle/product/8.0.2/dbs/arch1_623.dbf
ORA-00280: change 134717 for thread 1 is in sequence #623
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SVRMGR>
11. Open the database with the resetlog option.
SVRMGR> alter database open resetlogs;
Statement processed.
SVRMGR>
12. Reset the database from recovery manager.
(Needs to be done after an incomplete recovery)
$ rman target=\"internal/admin@DBA15\" rcvcat=\"dba15/dba15@DBA16\"
Recovery Manager: Release 8.0.2.0.0 - Beta
RMAN-06005: connected to target database: DBA15
RMAN-06008: connected to recovery catalog database
RMAN> reset database;
RMAN-08006: database registered in recovery catalog
RMAN-08002: starting full resync of recovery catalog
RMAN-08029: snapshot controlfile name set to default value: ?/dbs/snapcf_@.f
RMAN-08004: full resync complete
RMAN>
13. Check how many incarnations of your database you have now.
RMAN> list incarnation of database "DBA15";
RMAN-06240: List of Database Incarnations
RMAN-06241: DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
RMAN-06242: ------- ------- -------- ---------------- --- ---------- -------
RMAN-06243: 1 2 DBA15 4045670789 NO 1 18-FEB-97
RMAN-06243: 1 2172 DBA15 4045670789 YES 134718 18-MAR-97
출처 : http://myweb.bcpark.net/~truedog/sub1/theme4.htm#SCENARIO
'오라클 > Backup & Recovery' 카테고리의 다른 글
| Flashback (0) | 2010.07.16 |
|---|