현재 backup 상태확인!!
SYS>
SYS> SELECT * FROM V$BACKUP;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- -------------------
1 NOT ACTIVE 550922 2012-05-27 14:14:30
2 NOT ACTIVE 550943 2012-05-27 14:14:49
3 NOT ACTIVE 550928 2012-05-27 14:14:33
4 NOT ACTIVE 550937 2012-05-27 14:14:46
5 NOT ACTIVE 550948 2012-05-27 14:14:52
6 NOT ACTIVE 0
6 rows selected.
Tablespace 이름 별로 begin~end 를 걸어주어야 함!!
SYS> alter tablespace system begin backup;
Tablespace altered.
SYS> alter tablespace sysaux begin backup;
Tablespace altered.
SYS> alter tablespace users begin backup;
Tablespace altered.
SYS> alter tablespace undotbs1 begin backup;
Tablespace altered.
SYS> alter tablespace example begin backup;
Tablespace altered.
SYS> alter tablespace TS_AFS begin backup;
Tablespace altered.
컨트롤 파일과 HOT backup(데이터파일 리두로그 파일 이동) -> 현재 ACTIVE 상태확인! -> 파일 이동이 끝니면 모두 end 해주어야함.
SYS>
SYS> alter database backup controlfile to '/home/oracle/control01.ctl';
Database altered.
SYS>
SYS> SELECT * FROM V$BACKUP;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- -------------------
1 ACTIVE 551993 2012-05-27 14:29:55
2 ACTIVE 552010 2012-05-27 14:30:04
3 ACTIVE 551999 2012-05-27 14:29:58
4 ACTIVE 552004 2012-05-27 14:30:01
5 ACTIVE 552016 2012-05-27 14:30:08
6 ACTIVE 552022 2012-05-27 14:30:11
6 rows selected.
파일 이동 /u01/oradata/test
ocp@orcl : /u01/oradata/orcl> ls
arch1 control03.ctl redo02.log system01.dbf undotbs01.dbf
control01.ctl example01.dbf redo03.log temp01.dbf users01.dbf
control02.ctl redo01.log sysaux01.dbf ts_afs.dbf
ocp@orcl : /u01/oradata/orcl>
ocp@orcl : /u01/oradata/orcl>
ocp@orcl : /u01/oradata/orcl> cp *.log /u01/oradata/test
ocp@orcl : /u01/oradata/orcl>
ocp@orcl : /u01/oradata/orcl> cp *.dbf /u01/oradata/test
backup creuser.sql orcl_start.sh
ocp@orcl : /home/oracle>
ocp@orcl : /home/oracle> mv control* /u01/oradata/test
ocp@orcl : /home/oracle>
ocp@orcl : /home/oracle> cd /u01/oradata/test
ocp@orcl : /u01/oradata/test> ls
control01.ctl redo02.log system01.dbf undotbs01.dbf
example01.dbf redo03.log temp01.dbf users01.dbf
redo01.log sysaux01.dbf ts_afs.dbf
ocp@orcl : /u01/oradata/test>
ocp@orcl : /u01/oradata/test>
Hot backup을 완료.
SYS> SELECT * FROM V$BACKUP;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- -------------------
1 ACTIVE 551993 2012-05-27 14:29:55
2 ACTIVE 552010 2012-05-27 14:30:04
3 ACTIVE 551999 2012-05-27 14:29:58
4 ACTIVE 552004 2012-05-27 14:30:01
5 ACTIVE 552016 2012-05-27 14:30:08
6 ACTIVE 552022 2012-05-27 14:30:11
6 rows selected.
SYS> alter tablespace system end backup;
Tablespace altered.
SYS> SELECT * FROM V$BACKUP;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- -------------------
1 NOT ACTIVE 551993 2012-05-27 14:29:55
2 ACTIVE 552010 2012-05-27 14:30:04
3 ACTIVE 551999 2012-05-27 14:29:58
4 ACTIVE 552004 2012-05-27 14:30:01
5 ACTIVE 552016 2012-05-27 14:30:08
6 ACTIVE 552022 2012-05-27 14:30:11
6 rows selected.
SYS>
SYS> alter tablespace sysaux end backup;
Tablespace altered.
SYS> alter tablespace users end backup;
Tablespace altered.
SYS> alter tablespace undotbs1 end backup;
Tablespace altered.
SYS> alter tablespace example end backup;
Tablespace altered.
SYS> alter tablespace TS_AFS end backup;
Tablespace altered.
SYS> SELECT * FROM V$BACKUP;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- -------------------
1 NOT ACTIVE 551993 2012-05-27 14:29:55
2 NOT ACTIVE 552010 2012-05-27 14:30:04
3 NOT ACTIVE 551999 2012-05-27 14:29:58
4 NOT ACTIVE 552004 2012-05-27 14:30:01
5 NOT ACTIVE 552016 2012-05-27 14:30:08
6 NOT ACTIVE 552022 2012-05-27 14:30:11
6 rows selected.
로그파일 및 컨트롤 파일, 데이터파일 위치 확인
SYS>
SYS> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
----------------------------------------------------------------------------------------------------
IS_
---
3 ONLINE
/u01/oradata/orcl/redo03.log
NO
2 ONLINE
/u01/oradata/orcl/redo02.log
NO
1 ONLINE
/u01/oradata/orcl/redo01.log
NO
SYS> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
/u01/oradata/orcl/redo03.log
/u01/oradata/orcl/redo02.log
/u01/oradata/orcl/redo01.log
SYS> select * from v$controlfile;
STATUS
-------
NAME
----------------------------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
/u01/oradata/orcl/control01.ctl
NO 16384 430
/u01/oradata/orcl/control02.ctl
NO 16384 430
/u01/oradata/orcl/control03.ctl
NO 16384 430
SYS>
SYS> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/u01/oradata/orcl/system01.dbf
/u01/oradata/orcl/undotbs01.dbf
/u01/oradata/orcl/sysaux01.dbf
/u01/oradata/orcl/users01.dbf
/u01/oradata/orcl/example01.dbf
/u01/oradata/orcl/ts_afs.dbf
6 rows selected.
Scott 유저의 현재 데이터 상태확인
SYS> conn scott/tiger
Connected.
SCOTT> SCOTT>
SCOTT> select table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
HOT backup 완료
InitSID.ora 컨트롤 파일 및 아카이브 위치 변경
<변경 후>
*.control_files='/u01/oradata/orcl/control01.ctl','/u01/oradata/orcl/control02.ctl','/u01/oradata/orcl/control03.ctl'
<변경 전>
*.control_files='/u01/oradata/test/control01.ctl','/u01/oradata/test/control02.ctl','/u01/oradata/test/control03.ctl'
<아카이브 위치도 맞쳐주기>
*.log_archive_format='orcl_%t_%s_%r.arc'
*.log_archive_dest_1='location=/home/oracle/backup/archive'
Startup -> 에러 해결
SYS> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 75499764 bytes
Database Buffers 88080384 bytes
Redo Buffers bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'
각 데이터파일의 위치를 재지정해줌.
SYS> ALTER DATABASE RENAME FILE '/u01/oradata/orcl/system01.dbf' to '/u01/oradataSYS> ALTER DATABASE RENAME FILE '/u01/oradata/orcl/system01.dbf' to '/u01/oradataALTER DATABASE RENAME FILE '/u01/oradata/orcl/system01.dbf' to '/u01/oradata/test/system01.dbf';
ALTER DATABASE RENAME FILE '/u01/oradata/orcl/sysaux01.dbf' to '/u01/oradata/testALTER DATABASE RENAME FILE '/u01/oradata/orcl/sysaux01.dbf' to '/u01/oradata/test/sysaux01.dbf';
ALTER DATABASE RENAME FILE '/u01/oradata/orcl/example01.dbf' to '/u01/oradata/tesALTER DATABASE RENAME FILE '/u01/oradata/orcl/example01.dbf' to '/u01/oradata/test/example01.dbf';
ALTER DATABASE RENAME FILE '/u01/oradata/orcl/temp01.dbf' to '/u01/oradata/test/tALTER DATABASE RENAME FILE '/u01/oradata/orcl/temp01.dbf' to '/u01/oradata/test/temp01.dbf';
ALTER DATABASE RENAME FILE '/u01/oradata/orcl/users01.dbf' to '/u01/oradata/test/ALTER DATABASE RENAME FILE '/u01/oradata/orcl/users01.dbf' to '/u01/oradata/test/users01.dbf';
ALTER DATABASE RENAME FILE '/u01/oradata/orcl/undotbs01.dbf' to '/u01/oradata/tesALTER DATABASE RENAME FILE '/u01/oradata/orcl/undotbs01.dbf' to '/u01/oradata/test/undotbs01.dbf';
ALTER DATABASE RENAME FILE '/u01/oradata/orcl/ts_afs.dbf' to '/u01/oradata/test/tALTER DATABASE RENAME FILE '/u01/oradata/orcl/ts_afs.dbf' to '/u01/oradata/test/ts_afs.dbf';
Database altered.
SYS> SYS>
ALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo01.log' to '/u01/oradata/test/rALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo01.log' to '/u01/oradata/test/redo01.log';
ALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo02.log' to '/u01/oradata/test/rALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo02.log' to '/u01/oradata/test/redo02.log';
ALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo03.log' to '/u01/oradata/test/rALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo03.log' to '/u01/oradata/test/redo03.log';
Database altered.
SYS> SYS>
Database altered.
SYS> SYS>
Database altered.
SYS> SYS>
Database altered.
SYS> SYS>
Database altered.
SYS> SYS>
Database altered.
SYS> SYS> SYS>
Database altered.
SYS> SYS>
Database altered.
SYS> SYS>
Database altered.
현재 상태확인 -> current 상태의 redolog 확인.
SYS> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- -------------------
1 ACTIVE 551993 2012-05-27 14:29:55
2 ACTIVE 552010 2012-05-27 14:30:04
3 ACTIVE 551999 2012-05-27 14:29:58
4 ACTIVE 552004 2012-05-27 14:30:01
5 ACTIVE 552016 2012-05-27 14:30:08
6 ACTIVE 552022 2012-05-27 14:30:11
6 rows selected.
SYS> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
FIRST_TIME
-------------------
1 1 17 10485760 1 YES INACTIVE 551980
2012-05-27 14:29:19
3 1 16 10485760 1 YES INACTIVE 551978
2012-05-27 14:29:18
2 1 18 10485760 1 NO CURRENT 551983
2012-05-27 14:29:25
SYS> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
----------------------------------------------------------------------------------------------------
IS_
---
3 ONLINE
/u01/oradata/test/redo03.log
NO
2 ONLINE
/u01/oradata/test/redo02.log
NO
1 ONLINE
/u01/oradata/test/redo01.log
NO
Recover database;
SYS> recover database using backup controlfile;
ORA-00279: change 551993 generated at 05/27/2012 14:29:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/backup/archive/orcl_1_18_643126719.arc
ORA-00280: change 551993 for thread 1 is in sequence #18
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oradata/test/redo02.log
Log applied.
Media recovery complete.
SYS> alter database open resetlogs;
Database altered.
SYS>
SYS>
SYS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 75499764 bytes
Database Buffers 88080384 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS>
Scott 유저 상태확인
SCOTT>
SCOTT> select table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
SCOTT>
SCOTT>
SCOTT>
결론 : HOT backup 상태의 데이터가 들어가 있음.
- 발생된 에러들..
- 해당 에러는 TEST 서버에서 아카이브를 먼저 생성해 놓지 않고 Hot backup을 받은 뒤에 recovery를 하려고 했더니 해당 에러가 발생됨.
HOT backup을 하기전에 아카이브를 생성한 뒤(alter system switch logfile;) 백업을 받고 -> 데이터 변경 이후에 아카이브를 생성(alter system switch logfile;) 후 리커버리 -> hot backup 된 데이터 상태로 돌아감.
