백업한 control file 내용과 현재 data file 정보가 틀릴 경우

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         7          1          1  104857600        512          1 NO
CURRENT                 837442 18-JAN-24   2.8147E+14

         8          1          0  104857600        512          1 YES
UNUSED                       0                      0

         9          1          0  104857600        512          1 YES
UNUSED                       0                      0

#) 테이블 생성 hr.emp_20

=> 기존에 있다면 지우고 생성해주자! 

SQL> drop table hr.emp_20 purge;

Table dropped.

SQL> create table hr.emp_20 as select * from hr.employees where department_id = 20;

Table created.

SQL> select count(*) from hr.emp_20;

  COUNT(*)
----------
         2

#) 로그 스위치 발생
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

# 확인 

select a.file#, a.name file_name, b.name file_name, b.name tbs_name, a.status, a.checkpoint_change# 
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM SYSTEM 850124
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX SYSAUX ONLINE 850124
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS USERS ONLINE 850124
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE EXAMPLE ONLINE 850124
9 /u01/app/oracle/oradata/ora11g/undo01.dbf UNDO1 UNDO1 ONLINE 850124

# 그룹 번호, redo log 확인 

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;
7 4 /u01/app/oracle/oradata/ora11g/redo07.log 100 NO CURRENT
8 2 /u01/app/oracle/oradata/ora11g/redo08.log 100 YES INACTIVE
9 3 /u01/app/oracle/oradata/ora11g/redo09.log 100 YES INACTIVE

# 아카이브 확인 
SQL> ! ls /home/oracle1/arch1/
arch_1_1_1158611832.arc  arch_1_2_1158611832.arc  arch_1_3_1158611832.arc
arch_1_1_1158617487.arc  arch_1_2_1158617487.arc  arch_1_3_1158617487.arc

# 테이블 스페이스 생성
SQL> create tablespace data_tbs 

datafile '/u01/app/oracle/oradata/ora11g/data01.dbf' size 5m 

extent management local uniform size 1m 

segment space management auto;

Tablespace created.

# 테이블 스페이스 확인
select a.file#, a.name file_name, b.name file_name, b.name tbs_name, a.status, a.checkpoint_change# 
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;

# redo log 정보 확인
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         7          1          4  104857600        512          1 NO
CURRENT                 850124 18-JAN-24   2.8147E+14

         8          1          2  104857600        512          1 YES
INACTIVE                850118 18-JAN-24       850121 18-JAN-24

         9          1          3  104857600        512          1 YES
INACTIVE                850121 18-JAN-24       850124 18-JAN-24

# 테이블 생성 hr.emp_50

=> 새로 tablespace 생성된 곳에 table 만들기

SQL> create table hr.emp_50 tablespace data_tbs as select * from hr.employees where department_id = 50;

Table created.

SQL> select count(*) from hr.emp_50;

  COUNT(*)
----------
        45

# 테이블 생성 및 테이블스페이스 확인
select f.tablespace_name, f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id
and e.segment_name in ('EMP_20', 'EMP_50')
and e.owner = 'HR';

# 어떤 작업때문에 DB 내리기
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

# 장애 유발 및 확인 #
SQL> ! rm /u01/app/oracle/oradata/ora11g/control01.ctl

SQL> ! ls /u01/app/oracle/oradata/ora11g/control01.ctl
ls: cannot access /u01/app/oracle/oradata/ora11g/control01.ctl: No such file or directory

# DB 올리기
SQL> startup
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1367004 bytes
Variable Size             440402980 bytes
Database Buffers          264241152 bytes
Redo Buffers                5419008 bytes
ORA-00205: error in identifying control file, check alert log for more info
=> alert_ora11g.log 에서 확인 
Thu Jan 18 23:59:17 2024
ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

# DB 상태 확인 
SQL> select status from v$instance;

STATUS
------------
STARTED

# DB 비정상적인 종료
SQL> shutdown abort
ORACLE instance shut down.

# restore 하기
SQL> ! ls /home/oracle1/backup/arch/cold_20240117/control01.ctl
/home/oracle1/backup/arch/cold_20240117/control01.ctl

SQL> ! cp -av /home/oracle1/backup/arch/cold_20240118/control01.ctl /u01/app/oracle/oradata/ora11g/

# DB mount 단계까지 올리기
SQL> startup mount
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1367004 bytes
Variable Size             440402980 bytes
Database Buffers          264241152 bytes
Redo Buffers                5419008 bytes
Database mounted.

 

====================== ====================== ====================== ======================

다시 진행해야 함

====================== ====================== ====================== ======================

# recover
SQL> recover database using backup controlfile
auto

# recover 재시도
SQL> recover database using backup controlfile
=> recovery 하지 못함.

SQL> alter database backup controlfile to trace as '/home/oracle1/20240119.sql';

# OS 나와서 vi 편집기로 열기
SQL> !
vi 20240119.sql
startup nomount 복사
:q! 나오기

# tablespace 확인
SQL> 
select a.file#, a.name file_name, b.name file_name, b.name tbs_name, a.status, a.checkpoint_change# 
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;

# 이름 rename 해주기
SQL> alter database renmae file '이전 data file to '/u01/app/oracle/oradata/ora11g/data01.dbf';

# 수정되었는지 확인 
SQL> 
select a.file#, a.name file_name, b.name file_name, b.name tbs_name, a.status, a.checkpoint_change# 
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;

# recover 재시도 
(+ redo log file 적용)
recover database using backup controlfile

# resetlogs 로 오픈
alter database open resetlogs;

# 'EMP_20', 'EMP_50' 확인
select f.tablespace_name, f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id
and e.segment_name in ('EMP_20', 'EMP_50')
and e.owner = 'HR';