clone DB 생성 후 운영 중인 table 삭제했을 경우

■ clone DB 생성 
#) redo log 정보 

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status, b.first_time, b.first_change#, b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;

 

#) data file 확인 

SELECT a.file#, a.name AS file_name, b.name AS tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;

<rman session>
#) 스키마 확인
RMAN> report schema;

RMAN> list backup;

#) crosscheck backup
RMAN> crosscheck backup;

#) expired backup 확인
RMAN> list expired backup;

<ora11g session>

#) 불필요한 tablespace 삭제

drop tablespace data_tbs including contents and datafiles;

 

#) 불필요한 tablespace 삭제 후 확인

SELECT a.file#, a.name AS file_name, b.name AS tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;

#) 용량 체크하기!

! df -h

 <rman session>
#) backup 다시 받기
RMAN> list backup;

#) tablespace 지웠으니 backup 지우기 

RMAN> delete backup;

#) bakcup 다시 받기 

RMAN> backup database;

 

#) backup file 확인 

RMAN> list backup;

#) 스키마 확인 

RMAN> report schema;

#) 정책상 필요없는 파일(아카이브) 확인
RMAN> report obsolete;

#) 정책상 필요없는 파일(아카이브) 삭제
RMAN> delete obsolete;

#) 정책상 필요없는 파일 다시 확인
RMAN> report obsolete;

#) 아카이브 파일 지워졌는지 확인

! ls /home/oracle1/arch1

#) redo log 정보 확인

=> redo scn(current한 그룹, 2) 와 backup scn(data file:2340117 / control file:2340147) 비교해보기

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status, b.first_time, b.first_change#, b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;

 

#) 로그 스위치 발생 

alter system switch logfile;

#####
Fri Jan 26 10:02:27 2024
Beginning log switch checkpoint up to RBA [0x3.2.10], SCN: 2340736
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 9 seq# 3 mem# 0: /u01/app/oracle/oradata/ora11g/redo09.log
Fri Jan 26 10:02:27 2024
Archived Log entry 51 added for thread 1 sequence 2 ID 0xf85af81 dest 1:
#####

#) 샘플 테이블 생성 후 확인

create table hr.emp_temp as select * from hr.employees;
select count(*) from hr.emp_temp;

 

#) 시간 체크

select systimestamp from dual;

#) table 확인

select count(*) from hr.emp_temp;

#) 운영 테이블 삭제함! (장애 유발)

drop table hr.emp_temp purge;

#) 로그 스위치 발생*3

alter system switch logfile;

#) redo 정보 확인

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status, b.first_time, b.first_change#, b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;

#) 아카이브 체크 

! ls /home/oracle1/arch1

#) 장애 발생!!

select count(*) from hr.emp_temp;

# 해결 방법, 복구 #
#1) 복제할 초기파라미터 파일 확인!
[oracle1@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle1@oracle dbs]$ ls

[oracle1@oracle dbs]$ vi initclone.ora

compatible = '11.2.0.4.0'
*.control_files='/home/oracle1/clone/control01.ctl'
*.db_name='clone'
*.log_archive_dest_1='location=/home/oracle1/clone mandatory'
*.log_archive_format='arch_%t_%s_%r.arc'
*.undo_tablespace='UNDO1'

#2-1) clone 기존 파일 삭제
[oracle1@oracle ~]$ cd clone/
[oracle1@oracle clone]$ rm -r *.*
[oracle1@oracle clone]$ ls
[oracle1@oracle clone]$ pwd
/home/oracle1/clone

#2-2) clone 아카이브 파일 확인
[oracle1@oracle arch1]$ ls

[oracle1@oracle ~]$ cp -av /home/oracle1/arch1/*.* /home/oracle1/clone

[oracle1@oracle ~]$ cd clone/
[oracle1@oracle clone]$ ls

<rman session>
#3-1) backup 확인
RMAN> list backup;

data file : 
/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_nnndf_TAG20240126T095228_lv60lf70_.bkp

 

control file :
/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_26/o1_mf_s_1159264395_lv60mvqg_.bkp

#3-2) data file, control file을 clone 디렉터리로 이동
[oracle1@oracle ~]$ cp -av /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_nnndf_TAG20240126T095228_lv60lf70_.bkp /home/oracle1/clone/
[oracle1@oracle ~]$ cp -av /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_26/o1_mf_s_1159264395_lv60mvqg_.bkp /home/oracle1/clone/

#3-3) 백업본 확인

[oracle1@oracle ~]$ cd clone/
[oracle1@oracle clone]$ ls

#4) $ORACLE_HOME 위치 확인
[oracle1@oracle ~]$ echo $ORACLE_HOME

#5) clone 접속
[oracle1@oracle ~]$ . oraenv
ORACLE_SID = [ora11g] ? clone
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0.4/db_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle1@oracle ~]$ sqlplus / as sysdba

#6) nomount 단계까지 DB 올리기
=> control file
SYS@clone> startup pfile=$ORACLE_HOME/dbs/initclone.ora nomount

 

tip) 만약 clone에서 nomout단계까지 올라오지 않았다면?
[oracle1@oracle ~]$ ps -ef | grep clone
oracle1   5968     1  0 10:49 ?        00:00:00 ora_pmon_clone
oracle1   5970     1  0 10:49 ?        00:00:00 ora_psp0_clone
oracle1   5972     1  1 10:49 ?        00:00:02 ora_vktm_clone
oracle1   5976     1  0 10:49 ?        00:00:00 ora_gen0_clone
oracle1   5978     1  0 10:49 ?        00:00:00 ora_diag_clone
oracle1   5980     1  0 10:49 ?        00:00:00 ora_dbrm_clone
oracle1   5982     1  0 10:49 ?        00:00:00 ora_dia0_clone
oracle1   5984     1  0 10:49 ?        00:00:00 ora_mman_clone
oracle1   5986     1  0 10:49 ?        00:00:00 ora_dbw0_clone
oracle1   5988     1  0 10:49 ?        00:00:00 ora_lgwr_clone
oracle1   5990     1  0 10:49 ?        00:00:00 ora_ckpt_clone
oracle1   5992     1  0 10:49 ?        00:00:00 ora_smon_clone
oracle1   5994     1  0 10:49 ?        00:00:00 ora_reco_clone
oracle1   5996     1  0 10:49 ?        00:00:00 ora_mmon_clone
oracle1   5998     1  0 10:49 ?        00:00:00 ora_mmnl_clone
oracle1   6072  5312  0 10:52 pts/2    00:00:00 grep --color=auto clone

=> kill -9 (pmon 번호)

#7) OS로 나오기
SYS@clone> exit

#8) rman 접속
=> rman을 통해서 복제 DB 생성
[oracle1@oracle ~]$ rman auxiliary /

#9) run{} 작업형 스크립트를 이용

=> 입력 및 실행

 

RMAN> RUN {
  set newname for datafile 1 to '/home/oracle1/clone/system01.dbf';
  set newname for datafile 2 to '/home/oracle1/clone/sysaux01.dbf';
  set newname for datafile 4 to '/home/oracle1/clone/users01.dbf';
  set newname for datafile 5 to '/home/oracle1/clone/example01';
  set newname for datafile 9 to '/home/oracle1/clone/undo01.dbf';
  DUPLICATE TARGET DATABASE TO 'clone'
  pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initclone.ora'
  nofilenamecheck
  backup location '/home/oracle1/clone'
  until time "to_date('2024-01-26 10.04.30','yyyy-mm-dd hh24:mi:ss')"
  LOGFILE
    '/home/oracle1/clone/redo07.log' SIZE 100M,
    '/home/oracle1/clone/redo08.log' SIZE 100M,
    '/home/oracle1/clone/redo09.log' SIZE 100M;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16>

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 26-JAN-24

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     167407616 bytes

Fixed Size                     1363300 bytes
Variable Size                109052572 bytes
Database Buffers              50331648 bytes
Redo Buffers                   6660096 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORA11G'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''CLONE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/home/oracle1/clone/o1_mf_s_1159264395_lv60mvqg_.bkp';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ORA11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CLONE'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     167407616 bytes

Fixed Size                     1363300 bytes
Variable Size                109052572 bytes
Database Buffers              50331648 bytes
Redo Buffers                   6660096 bytes

Starting restore at 26-JAN-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=170 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle1/clone/control01.ctl
Finished restore at 26-JAN-24

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=170 device type=DISK

contents of Memory Script:
{
   set until scn  2340117;
   set newname for datafile  1 to
 "/home/oracle1/clone/system01.dbf";
   set newname for datafile  2 to
 "/home/oracle1/clone/sysaux01.dbf";
   set newname for datafile  4 to
 "/home/oracle1/clone/users01.dbf";
   set newname for datafile  5 to
 "/home/oracle1/clone/example01";
   set newname for datafile  9 to
 "/home/oracle1/clone/undo01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 26-JAN-24
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle1/clone/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /home/oracle1/clone/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle1/clone/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /home/oracle1/clone/example01
channel ORA_AUX_DISK_1: restoring datafile 00009 to /home/oracle1/clone/undo01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle1/clone/o1_mf_nnndf_TAG20240126T095228_lv60lf70_.bkp
channel ORA_AUX_DISK_1: piece handle=/home/oracle1/clone/o1_mf_nnndf_TAG20240126T095228_lv60lf70_.bkp tag=TAG20240126T095228
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 26-JAN-24

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1159268889 file name=/home/oracle1/clone/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=1159268889 file name=/home/oracle1/clone/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1159268889 file name=/home/oracle1/clone/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1159268889 file name=/home/oracle1/clone/example01
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=1159268889 file name=/home/oracle1/clone/undo01.dbf
Oracle instance started

Total System Global Area     167407616 bytes

Fixed Size                     1363300 bytes
Variable Size                109052572 bytes
Database Buffers              50331648 bytes
Redo Buffers                   6660096 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 '/home/oracle1/clone/redo07.log' SIZE 100 M ,
  GROUP   2 '/home/oracle1/clone/redo08.log' SIZE 100 M ,
  GROUP   3 '/home/oracle1/clone/redo09.log' SIZE 100 M
 DATAFILE
  '/home/oracle1/clone/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/ora11g/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/home/oracle1/clone/sysaux01.dbf",
 "/home/oracle1/clone/users01.dbf",
 "/home/oracle1/clone/example01",
 "/home/oracle1/clone/undo01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/ora11g/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/home/oracle1/clone/sysaux01.dbf RECID=1 STAMP=1159268891
cataloged datafile copy
datafile copy file name=/home/oracle1/clone/users01.dbf RECID=2 STAMP=1159268891
cataloged datafile copy
datafile copy file name=/home/oracle1/clone/example01 RECID=3 STAMP=1159268891
cataloged datafile copy
datafile copy file name=/home/oracle1/clone/undo01.dbf RECID=4 STAMP=1159268891

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1159268891 file name=/home/oracle1/clone/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1159268891 file name=/home/oracle1/clone/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=1159268891 file name=/home/oracle1/clone/example01
datafile 9 switched to datafile copy
input datafile copy RECID=4 STAMP=1159268891 file name=/home/oracle1/clone/undo01.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 26-JAN-24

=> 완료!

#) OS 나와서 확인
[oracle1@oracle ~]$ cd clone/
[oracle1@oracle clone]$ ls

#) clone으로 접속 후 DB 이름, 상태 확인 
[oracle1@oracle ~]$ sqlplus / as sysdba

select name from v$database;
select status from v$instance;

 
#) os 나와서 export하기
[oracle1@oracle ~]$ exp system/oracle tables=hr.emp_temp file=emp_tmp.dmp

#) 복제 DB나와서 운영중인 DB로 접속 
[oracle1@oracle ~]$ . oraenv
ORACLE_SID = [clone] ? ora11g
The Oracle base remains unchanged with value /u01/app/oracle

[oracle1@oracle ~]$ sqlplus / as sysdba

select name from v$database;

#) 테이블 확인
=> 당연히 없다.

select count(*) from hr.emp_temp;

select count(*) from hr.emp_temp
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

=> import 해줘야 함

#) import 작업해야 한다. 
[oracle1@oracle ~]$ imp system/oracle tables=emp_temp file=emp_temp.dmp fromuser=hr