CentOS Linux release 7.9.2009 (Core)
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Oracle Database - ORA11G, TESTDEV
- 개발DB 최신화 - 기존에 사용중이던 개발DB(TESTDEV)를 지우고, 운영DB(ORA11g)에서 개발DB로 복사
- 백업 및 복구(HOT BACKUP) -- 핫백업은 아카이브 모드에서 가능
- 특정 사용자 삭제(TEST01) -- 고객 요청사항
- 특정 테이블스페이스 삭제(TS_TEST) -- 고객 요청사항
- 노아카이브 모드 -- 개발DB는 아카이브파일 필요없음
1. 인스턴스 중지(개발DB)
export ORACLE_SID=TESTDEV
echo $ORACLE_SID
TESTDEV
sqlplus / as sysdba
select NAME from v$database;
NAME
---------
TESTDEV
shutdown immediate
// 작업을 할때는 운영인지 개발인지 확인이 중요!
2. 데이터파일, 컨트롤파일, 리두로그파일, 파라미터 파일 삭제(개발DB)
$ ls /oracle/app/oracle/oradata/TESTDEV
control01.ctl control02.ctl redo01.log redo02.log redo03.log
sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
rm $ORACLE_BASE/oradata/TESTDEV/*.ctl
rm $ORACLE_BASE/oradata/TESTDEV/*.log
rm $ORACLE_BASE/oradata/TESTDEV/*.dbf
rm $ORACLE_HOME/dbs/spfileTESTDEV.ora
3. 데이터파일, 컨트롤파일, 리두로그파일, 테이블스페이스 확인(운영DB)
ls $ORACLE_BASE/oradata/ORA11g
drwxr-xr-x. 2 oracle dba 6 Aug 5 15:18 arch
-rw-r-----. 1 oracle dba 9748480 Aug 6 09:22 control01.ctl
-rw-r-----. 1 oracle dba 9748480 Aug 6 09:22 control02.ctl
-rw-r-----. 1 oracle dba 52429312 Aug 6 09:01 redo01.log
-rw-r-----. 1 oracle dba 52429312 Aug 6 09:22 redo02.log
-rw-r-----. 1 oracle dba 52429312 Aug 6 09:01 redo03.log
-rw-r-----. 1 oracle dba 629153792 Aug 6 09:21 sysaux01.dbf
-rw-r-----. 1 oracle dba 734011392 Aug 6 09:21 system01.dbf
-rw-r-----. 1 oracle dba 38805504 Aug 5 16:15 temp01.dbf
-rw-r-----. 1 oracle dba 10493952 Aug 6 09:14 ts_test01.dbf
-rw-r-----. 1 oracle dba 346038272 Aug 6 09:21 undotbs01.dbf
-rw-r-----. 1 oracle dba 5251072 Aug 6 09:01 users01.dbf
export ORACLE_SID=ORA11g
echo $ORACLE_SID
ORA11g
sqlplus / as sysdba
select file_id,tablespace_name,file_name from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ --------------------------------------------------
1 SYSTEM /oracle/app/oracle/oradata/ORA11g/system01.dbf
2 SYSAUX /oracle/app/oracle/oradata/ORA11g/sysaux01.dbf
3 UNDOTBS1 /oracle/app/oracle/oradata/ORA11g/undotbs01.dbf
4 USERS /oracle/app/oracle/oradata/ORA11g/users01.dbf
5 TS_TEST /oracle/app/oracle/oradata/ORA11g/ts_test01.dbf
// 개발DB와 운영DB의 테이블스페이스 & 데이터파일 구성이 다를 수 있다.
4. 핫 백업(운영DB)
alter system switch logfile; -- 테스트용 로그스위치
alter tablespace SYSTEM begin backup;
!cp /oracle/app/oracle/oradata/ORA11g/system01.dbf /backup
alter tablespace SYSTEM end backup;
alter tablespace SYSAUX begin backup;
!cp /oracle/app/oracle/oradata/ORA11g/sysaux01.dbf /backup
alter tablespace SYSAUX end backup;
alter system switch logfile; -- 테스트용 로그스위치
alter tablespace UNDOTBS1 begin backup;
!cp /oracle/app/oracle/oradata/ORA11g/undotbs01.dbf /backup
alter tablespace UNDOTBS1 end backup;
alter tablespace USERS begin backup;
!cp /oracle/app/oracle/oradata/ORA11g/users01.dbf /backup
alter tablespace USERS end backup;
alter system switch logfile; -- 테스트용 로그스위치
alter tablespace TS_TEST begin backup;
!cp /oracle/app/oracle/oradata/ORA11g/ts_test01.dbf /backup
alter tablespace TS_TEST end backup;
alter system switch logfile; -- 테스트용 로그스위치
// 백업중에도 로그스위치가 발생할 수 있기때문에 비슷한 환경을 임의로 만들어줌
5. 복원(백업 복사)
cp /backup/system01.dbf /oracle/app/oracle/oradata/TESTDEV/
cp /backup/sysaux01.dbf /oracle/app/oracle/oradata/TESTDEV/
cp /backup/undotbs01.dbf /oracle/app/oracle/oradata/TESTDEV/
cp /backup/users01.dbf /oracle/app/oracle/oradata/TESTDEV/
cp /backup/ts_test01.dbf /oracle/app/oracle/oradata/TESTDEV/
// 백업본을 직접사용하기 보다 백업본의 카피본을 사용
6. 파라미터 파일 생성(개발DB)
-- 운영DB의 Spfile을 Pfile로 생성
ls $ORACLE_HOME/dbs/
...
spfileORA11g.ora
export ORACLE_SID=ORA11g
echo $ORACLE_SID
ORA11g
sqlplus / as sysdba
create pfile from spfile;
-- Pfile 이름변경 및 수정
ls $ORACLE_HOME/dbs/
...
spfileORA11g.ora
initORA11g.ora
cd $ORACLE_HOME/dbs/
mv initORA11g.ora initTESTDEV.ora
vi initTESTDEV.ora
1. __ (언더스코프) 파라미터 항목 지우기
2. 경로 수정 및 서비스명 변경
:%s;ORA11g;TESTDEV;
// 사용 환경에 맞게 편집
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/app/oracle/oradata/TESTDEV/control01.ctl','/oracle/app/oracle/oradata/TESTDEV/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='TESTDEV'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDEVXDB)'
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_tablespace='UNDOTBS1'
7. 컨트롤 파일 생성 스크립트
export ORACLE_SID=ORA11g
echo $ORACLE_SID
ORA11g
sqlplus / as sysdba
alter database backup controlfile to trace as '/oracle/recon.sql';
vi /oracle/recon.sql
-- 주석 삭제
-- 리셋로그
...
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS ARCHIVELOG
...
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
// 리셋로그랑 노리셋로그가 있는데 리셋로그만 남김
-- 데이터베이스 이름 및 경로 변경
-- REUSE를 SET 으로 변경
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "TESTDEV" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/app/oracle/oradata/TESTDEV/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/oracle/app/oracle/oradata/TESTDEV/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/oracle/app/oracle/oradata/TESTDEV/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/oracle/app/oracle/oradata/TESTDEV/system01.dbf',
'/oracle/app/oracle/oradata/TESTDEV/sysaux01.dbf',
'/oracle/app/oracle/oradata/TESTDEV/undotbs01.dbf',
'/oracle/app/oracle/oradata/TESTDEV/users01.dbf',
'/oracle/app/oracle/oradata/TESTDEV/ts_test01.dbf'
CHARACTER SET AL32UTF8
;
8. 파라미터 파일 생성(개발DB)
export ORACLE_SID=TESTDEV
echo $ORACLE_SID
TESTDEV
sqlplus / as sysdba
create spfile from pfile;
File created.
9. 컨트롤 파일 생성(개발DB)
@/oracle/recon.sql
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Control file created.
select status from v$instance;
STATUS
------------
MOUNTED
10. 데이터 정합성
정상적으로 오픈하기 위해서는 다음 파일의 SCN이 같아야한다.
데이터파일헤더 / 컨트롤파일 / 리두로그파일
SELECT CONTROLFILE_CHANGE# FROM V$DATABASE;
CONTROLFILE_CHANGE#
-------------------
0
// 컨트롤 파일은 새로 만들었기 때문에 SCN 존재하지 않음
// REDO로그는 삭제했기 때문에 존재하지 않음
// 데이터 정합성을 맞출 수 없음 => 리셋로그 진행
SELECT NAME, CHECKPOINT_CHANGE# FROM V$DATAFILE_HEADER;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/oracle/app/oracle/oradata/TESTDEV/system01.dbf 572336
/oracle/app/oracle/oradata/TESTDEV/sysaux01.dbf 572347
/oracle/app/oracle/oradata/TESTDEV/undotbs01.dbf 572381
/oracle/app/oracle/oradata/TESTDEV/users01.dbf 572393
/oracle/app/oracle/oradata/TESTDEV/ts_test01.dbf 572408
// 운영중에 핫백업을 했기때문에 데이터파일의 SCN도 같지 않음
// 리커버리를 통해서 데이터파일의 SCN을 맞춰줘야 리셋로그 가능
11. 리커버
-- alert 로그 확인
vi $ORACLE_BASE/diag/rdbms/ora11g/ORA11g/trace/alert_ORA11g.log
Tue Aug 06 10:54:52 2024
Thread 1 advanced to log sequence 48 (LGWR switch)
Current log# 3 seq# 48 mem# 0: /oracle/app/oracle/oradata/ORA11g/redo03.log
Tue Aug 06 10:54:52 2024
Archived Log entry 13 added for thread 1 sequence 47 ID 0x10560fee dest 1:
Tue Aug 06 10:54:56 2024
alter tablespace SYSTEM begin backup
...
Completed: alter tablespace TS_TEST end backup
Thread 1 cannot allocate new log, sequence 51
Checkpoint not complete
Current log# 2 seq# 50 mem# 0: /oracle/app/oracle/oradata/ORA11g/redo02.log
Thread 1 advanced to log sequence 51 (LGWR switch)
Current log# 3 seq# 51 mem# 0: /oracle/app/oracle/oradata/ORA11g/redo03.log
Tue Aug 06 10:55:31 2024
// 백업 시작전에 Current Log (#48)
// 백업 끝난 이후 Current Log (#50)
-- 리커버
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 572336 generated at 08/06/2024 10:54:56 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/product/11.2.0.4/dbs/arch1_48_1172837934.dbf
ORA-00280: change 572336 for thread 1 is in sequence #48
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/app/oracle/product/11.2.0.4/dbs/arch1_48_1172837934.dbf
ORA-00279: change 572380 generated at 08/06/2024 10:55:07 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/product/11.2.0.4/dbs/arch1_49_1172837934.dbf
ORA-00280: change 572380 for thread 1 is in sequence #49
ORA-00278: log file
'/oracle/app/oracle/product/11.2.0.4/dbs/arch1_48_1172837934.dbf' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/app/oracle/product/11.2.0.4/dbs/arch1_49_1172837934.dbf
ORA-00279: change 572406 generated at 08/06/2024 10:55:21 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/product/11.2.0.4/dbs/arch1_50_1172837934.dbf
ORA-00280: change 572406 for thread 1 is in sequence #50
ORA-00278: log file
'/oracle/app/oracle/product/11.2.0.4/dbs/arch1_49_1172837934.dbf' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/app/oracle/product/11.2.0.4/dbs/arch1_50_1172837934.dbf
ORA-00279: change 572423 generated at 08/06/2024 10:55:31 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/product/11.2.0.4/dbs/arch1_51_1172837934.dbf
ORA-00280: change 572423 for thread 1 is in sequence #51
ORA-00278: log file
'/oracle/app/oracle/product/11.2.0.4/dbs/arch1_50_1172837934.dbf' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SELECT NAME, CHECKPOINT_CHANGE# FROM V$DATAFILE_HEADER;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/oracle/app/oracle/oradata/TESTDEV/system01.dbf 572423
/oracle/app/oracle/oradata/TESTDEV/sysaux01.dbf 572423
/oracle/app/oracle/oradata/TESTDEV/undotbs01.dbf 572423
/oracle/app/oracle/oradata/TESTDEV/users01.dbf 572423
/oracle/app/oracle/oradata/TESTDEV/ts_test01.dbf 572423
12. 리셋로그로 오픈 및 나머지 작업
ALTER DATABASE OPEN RESETLOGS;
Database altered.
-- TEMP 파일 추가
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/app/oracle/oradata/TESTDEV/temp01.dbf'
SIZE 38797312 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP
-- 노아카이브 모드
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;
archive log list;
archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/app/oracle/product/11.2.0.4/dbs/arch
Oldest online log sequence 1
Current log sequence 1
-- 유저 삭제
SELECT USERNAME FROM V$SESSION WHERE USERNAME = 'TEST01';
no rows selected
// 세션 붙어있는지 확인
DROP USER TEST01 CASCADE;
User dropped.
-- 테이블 스페이스 삭제
DROP TABLESPACE TS_TEST INCLUDING CONTENTS AND DATAFILES;
ls $ORACLE_BASE/oradata/TESTDEV
control01.ctl control02.ctl redo01.log redo02.log redo03.log
sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf