ArchiveMode 에서의 Backup & Recovery-RMAN04
서버 파라미터 파일 손실 시 Recovery
- FROM MEMORY
- 컨트롤 파일 자동 백업으로부터 복원
- alert.log에서 긁어오기
--spfile recovery
/* 11gWS2 교재 7-17
1. create pfile from memory 명령을 이용 (11g 버전 부터 사용가능)
2. alert.log에 startup 시 파라메터의 값이 기록되어 있으므로 복사해서 사용(11g 버전 부터 사용 가능)
3. contolfile autobackup on 설정시 같이 백업되어 있으므로 restore 해서 사용한다.
*/
/* 사례1. create pfile from memory 명령 사용
DB가 아직 종료되지 않은 상태에서 사용 가능
*/
SYS@orcl2> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SYS@orcl2> select status from v$instance;
STATUS
------------
OPEN
SYS@orcl2> !ls $ORACLE_HOME/dbs
hc_DBUA0.dat init.ora lkORCL orapworcl peshm__0 spfilePROD.ora
hc_PROD.dat initPROD.ora lkORCL2 orapworcl2 peshm_orcl2_0 spfileorcl2.bak
hc_orcl.dat initorcl.ora lkPROD orapworcl2.bak peshm_orcl_0 spfileorcl2.ora
hc_orcl2.dat initorcl2.ora orapwPROD peshm_DBUA0_0 snapcf_orcl2.f
SYS@orcl2> !ls $ORACLE_HOME/dbs
hc_DBUA0.dat init.ora lkORCL orapworcl peshm__0 spfilePROD.ora
hc_PROD.dat initPROD.ora lkORCL2 orapworcl2 peshm_orcl2_0 spfileorcl2.bak
hc_orcl.dat initorcl.ora lkPROD orapworcl2.bak peshm_orcl_0 spfileorcl2.ora
hc_orcl2.dat initorcl2.ora orapwPROD peshm_DBUA0_0 snapcf_orcl2.f
SYS@orcl2> !mv spfileorcl2.ora spfileorcl2.org
mv: cannot stat `spfileorcl2.ora': No such file or directory
SYS@orcl2> !mv $ORACLE_HOME/dbs/spfileorcl2.ora $ORACLE_HOME/dbs/spfileorcl2.org
SYS@orcl2> !ls $ORACLE_HOME/dbs/*.ora
init.ora spfilePROD.ora
initPROD.ora initorcl.ora spfileorcl2.org initorcl2.ora
SYS@orcl2> create pfile from memory;
File created.
SYS@orcl2> !ls $ORACLE_HOME/dbs/*.ora
init.ora spfilePROD.ora
initPROD.ora initorcl.ora spfileorcl2.org initorcl2.ora
-- 따로 파일이 생성되지는 않는다.
SYS@orcl2> !vi $ORACLE_HOME/dbs/initorcl2.ora
# Oracle init.ora parameter file generated by instance orcl2 on 04/23/2019 10:51:28
__db_cache_size=264M
__java_pool_size=4M
__large_pool_size=4M
__oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment
__pga_aggregate_target=140M
__sga_target=412M
__shared_io_pool_size=0
__shared_pool_size=128M
__streams_pool_size=4M
_aggregation_optimization_settings=0
_always_anti_join='CHOOSE'
_always_semi_join='CHOOSE'
_and_pruning_enabled=TRUE
_b_tree_bitmap_plans=TRUE
_bloom_filter_enabled=TRUE
_bloom_folding_enabled=TRUE
_bloom_pruning_enabled=TRUE
_complex_view_merging=TRUE
_compression_compatibility='11.2.0.0.0'
_connect_by_use_union_all='TRUE'
_convert_set_to_join=FALSE
_cost_equality_semi_join=TRUE
_cpu_to_io=0
_dimension_skip_null=TRUE
"/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora" 167L, 5745C
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 138414408 bytes
Database Buffers 285212672 bytes
Redo Buffers 6074368 bytes
Database mounted.
Database opened.
SYS@orcl2> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
-- vlaue가 null 이므로, pfile(initorcl2.ora)를 사용하고 있다
-- pfile : text parameter file : init{SID}.ora
-- spfile : server parameter file(binary parameter file) : spfile{SID}.ora
alter system set {parametr} = {value} scope = {both(dynamic prameter, default) / spfile(static parameter } / memory}
SYS@orcl2> !ls $ORACLE_HOME/dbs/spfileorcl2.ora
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl2.ora
SYS@orcl2> shutdown immedaite
SP2-0717: illegal SHUTDOWN option
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 138414408 bytes
Database Buffers 285212672 bytes
Redo Buffers 6074368 bytes
Database mounted.
Database opened.
SYS@orcl2> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileorcl2.ora
-- spfile로 기동된 상태라는 것을 알 수 있다.
/* 사례2. alert.log 화일의 내용을 이용하여 pfile 작성*/
[orcl2:~]$ mv $ORACLE_HOME/dbs/spfileorcl2.ora $ORACLE_HOME/dbs/spfileorcl2.bak
[orcl2:~]$ rm $ORACLE_HOME/dbs/initorcl2.ora
SYS@orcl2> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora'
====================================
alert.log
processes = 150
sga_target = 412M
control_files = "/u01/app/oracle/orcl2/control01.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_dest_1 = "location=/home/oracle/arch1 mandatory"
log_archive_dest_2 = "location=/home/oracle/arch2 optional"
log_archive_format = "arch_%t_%s_%r.arc"
log_buffer = 5873664
db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 3852M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = "example.com"
dispatchers = "(PROTOCOL=TCP) (SERVICE=orcl2XDB)"
utl_file_dir = "/home/oracle/utl_dir"
plsql_warnings = "DISABLE:ALL"
result_cache_max_size = 2112K
core_dump_dest = "/u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump"
audit_file_dest = "/u01/app/oracle/admin/orcl2/adump"
audit_trail = "DB"
db_name = "orcl2"
open_cursors = 300
optimizer_mode = "ALL_ROWS"
query_rewrite_enabled = "TRUE"
pga_aggregate_target = 137M
optimizer_dynamic_sampling= 2
skip_unusable_indexes = TRUE
diagnostic_dest = "/u01/app/oracle"
====================================
[orcl2:~]$ cd $ORACLE_HOME/dbs
[orcl2:dbs]$ vi initorcl2.ora
processes = 150
sga_target = 412M
control_files = "/u01/app/oracle/orcl2/control01.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_dest_1 = "location=/home/oracle/arch1 mandatory"
log_archive_dest_2 = "location=/home/oracle/arch2 optional"
log_archive_format = "arch_%t_%s_%r.arc"
log_buffer = 5873664
db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 3852M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = "example.com"
dispatchers = "(PROTOCOL=TCP) (SERVICE=orcl2XDB)"
utl_file_dir = "/home/oracle/utl_dir"
plsql_warnings = "DISABLE:ALL"
result_cache_max_size = 2112K
core_dump_dest = "/u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump"
audit_file_dest = "/u01/app/oracle/admin/orcl2/adump"
audit_trail = "DB"
db_name = "orcl2"
open_cursors = 300
optimizer_mode = "ALL_ROWS"
query_rewrite_enabled = "TRUE"
pga_aggregate_target = 137M
optimizer_dynamic_sampling= 2
skip_unusable_indexes = TRUE
diagnostic_dest = "/u01/app/oracle"
SYS@orcl2> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora'
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 138414408 bytes
Database Buffers 285212672 bytes
Redo Buffers 6074368 bytes
Database mounted.
Database opened.
/* 사례3. alert.log의 기록이 없을때autobackup에서 복구 */
[orcl2:trace]$ rm /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL2 are:
...............
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
...............
RMAN> backup as compressed backupset database;
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
58 Full 281.09M DISK 00:00:36 23-APR-19
BP Key: 63 Status: AVAILABLE Compressed: YES Tag: TAG20190423T112607
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_23/o1_mf_nnndf_TAG20190423T112607_gcwxxzo6_.bkp
List of Datafiles in backup set 58
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1343822 23-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1343822 23-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1343822 23-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1343822 23-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1343822 23-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
59 Full 9.70M DISK 00:00:00 23-APR-19
BP Key: 64 Status: AVAILABLE Compressed: NO Tag: TAG20190423T112652
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_23/o1_mf_n_1006342012_gcwxzdsn_.bkp
Control File Included: Ckp SCN: 1343902 Ckp time: 23-APR-19
[orcl2:dbs]$ rm $ORACLE_HOME/dbs/initorcl2.ora
SYS@orcl2> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora'
RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1335192 bytes
Variable Size 75497576 bytes
Database Buffers 75497472 bytes
Redo Buffers 6688768 bytes
RMAN> restore spfile from autobackup;
Starting restore at 23-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/23/2019 11:32:01
RMAN-06495: must explicitly specify DBID with SET DBID command
RMAN> SET DBID 986068470
executing command: SET DBID
-- 미리 나의 DBID를 알고 있어야한다.
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup
connected to target database (not started)
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/23/2019 11:33:23
ORA-00205: error in identifying control file, check alert log for more info
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1335192 bytes
Variable Size 75497576 bytes
Database Buffers 75497472 bytes
Redo Buffers 6688768 bytes
====================================================================================================
autobackup이 off일 경우
RMAN>
run{
set dbid 986068470
restore spfile to '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl2.ora' from autobackup
recovery area = '/u01/app/oracle/flash_recovery_area'
db_name='orcl2';
}
====================================================================================================
RMAN>
run{
set dbid 986068470
restore spfile from autobackup
recovery area = '/u01/app/oracle/flash_recovery_area'
db_name='orcl2';
}
executing command: SET DBID
Starting restore at 23-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 device type=DISK
recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL2
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006277283_gctyrn0c_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190423
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190422
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006277283_gctyrn0c_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 23-APR-19
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
SYS@orcl2> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileorcl2.ora
READ ONLY TABLESPACE
- SQL>alter tablespace tablespace_name read only;
- 변경하고자 하는 tablespace가 online이어야 한다.
- 이 tablespace가 active rollback segment를 포함하지 않아야 한다.
- undo 만 존재한다.
- begin backup mode 상태가 아니어야 한다.
- initSID.ora file 내의 COMPATIBLE parameter가 7.1.0 이상이어야 한다.
- 해당 tablespace에 active transaction이 있어서는 안 된다.
- control file내에 해당 datafile을 read-only 상태로 설정하고 stop SCN 기록
- 해당 datafile의 header에도 stop SCN 기록
- sys.TS$ 에 stop SCN값 기록.
- 이렇게 tablespace를 read-only상태로 유지하게 되면 먼저 한번만 write후 읽기만 가능한 장비등에 저장, 사용할 수 있다.
- 그외에 실제적인 장점은 backup/recovery시에 도움이 된다는 것이다.
- read-only로 변경이 일어나면 바로 백업을 받아야한다.
- read-only상태에서 한번 backup을 받으면 이후 read-write가 되기 전까지 추가적인 backup이 필요없으며, 일정 기간만 read-only상태로 유지하더라도 recovery시 해당 기간 동안은 archive file을 아예 비교하지도 않고 skip하게 되어 recovery성능에 도움이 된다.
- read-write tablespace의 경우는 변경이 이루어지지 않았다 하더라도 recovery시 archive file을 계속 확인해야 하는 작업이 필요하다.
-- read-only tablespace에 대한 정보는 controlfile내에 저장되기 때문에,recovery시 controlfile을 현재것을 사용하느냐, 과거것을 사용하느냐에 따라 recovery 절차도 달라지게 된다.
-- 현재의 controlfile을 사용하는 경우 datafile header의 stop SCN과 controlfile내의 stop SCN이 같으면 recovery가 필요없게 되어, 간단히 작업이 끝나게 되므로 tablespace가 read-only상태가 되면 backup을 한번 받아두는것이 중요하다.
SYS@orcl2> alter tablespace users read only;
Tablespace altered.
SYS@orcl2> select a.name, a.status, a.checkpoint_change# con_ckpt, a.last_change# stop, a.offline_change# off, a.online_change# onn, b.checkpoint_change# h_ckpt
from v$datafile a, v$datafile_header b
where a.file#=b.file#;
NAME STATUS CON_CKPT STOP OFF ONN H_CKPT
----------------------------------- ------- ---------- ---------- ---------- ---------- ----------
/u01/app/oracle/orcl2/system01.dbf SYSTEM 1389493 1298641 1298642 1389493
/u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1389493 1298641 1298642 1389493
/u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1389493 1298641 1298642 1389493
/u01/app/oracle/orcl2/users01.dbf ONLINE 1389493 1298641 1298642 1389493
/u01/app/oracle/orcl2/example01.dbf ONLINE 1389493 1298641 1298642 1389493
SYS@orcl2> save dfile
Created file dfile.sql
SYS@orcl2> @dfile
NAME STATUS CON_CKPT STOP OFF ONN H_CKPT
----------------------------------- ------- ---------- ---------- ---------- ---------- ----------
/u01/app/oracle/orcl2/system01.dbf SYSTEM 1389493 1298641 1298642 1389493
/u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1389493 1298641 1298642 1389493
/u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1389493 1298641 1298642 1389493
/u01/app/oracle/orcl2/users01.dbf ONLINE 1390956 1390956 1298641 1298642 1390956
/u01/app/oracle/orcl2/example01.dbf ONLINE 1389493 1298641 1298642 1389493
-- 현재 cold되어있는 상황이다.
SYS@orcl2> @check
System altered.
SYS@orcl2> @dfile
NAME STATUS CON_CKPT STOP OFF ONN H_CKPT
----------------------------------- ------- ---------- ---------- ---------- ---------- ----------
/u01/app/oracle/orcl2/system01.dbf SYSTEM 1390994 1298641 1298642 1390994
/u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1390994 1298641 1298642 1390994
/u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1390994 1298641 1298642 1390994
/u01/app/oracle/orcl2/users01.dbf ONLINE 1390956 1390956 1298641 1298642 1390956
/u01/app/oracle/orcl2/example01.dbf ONLINE 1390994 1298641 1298642 1390994
-- 체크포인트를 진행해도, 체크포인트 넘버의 변화는 없다.
SYS@orcl2> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE ONLINE
6 rows selected.
SYS@orcl2> alter tablespace users read write;
Tablespace altered.
SYS@orcl2> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
6 rows selected.
SYS@orcl2> @check
System altered.
SYS@orcl2> @dfile
NAME STATUS CON_CKPT STOP OFF ONN H_CKPT
----------------------------------- ------- ---------- ---------- ---------- ---------- ----------
/u01/app/oracle/orcl2/system01.dbf SYSTEM 1391168 1298641 1298642 1391168
/u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1391168 1298641 1298642 1391168
/u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1391168 1298641 1298642 1391168
/u01/app/oracle/orcl2/users01.dbf ONLINE 1391168 1390956 1391155 1391168
/u01/app/oracle/orcl2/example01.dbf ONLINE 1391168 1298641 1298642 1391168
--read only 테이블스페이스 복구
SYS@orcl2> select table_name, tablespace_name
from dba_tables
where owner='HR'
and table_name='CP_EMP';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CP_EMP USERS
SYS@orcl2> select * from hr.cp_emp
where rownum < 5;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------------- --------- ---------- ---------- -------------- ---------- -------------
198 Donald OConnell DOCONNEL
650.507.9833 21-JUN-07 SH_CLERK 2600 124 50
199 Douglas Grant DGRANT
650.507.9844 13-JAN-08 SH_CLERK 2600 124 50
200 Jennifer Whalen JWHALEN
515.123.4444 17-SEP-03 AD_ASST 4400 101 10
201 Michael Hartstein MHARTSTE
515.123.5555 17-FEB-04 MK_MAN 13000 100 20
SYS@orcl2> delete hr.cp_emp
where employee_id=198;
1 row deleted.
SYS@orcl2> rollback;
Rollback complete.
SYS@orcl2> @dfile
NAME STATUS CON_CKPT STOP OFF ONN H_CKPT
----------------------------------- ------- ---------- ---------- ---------- ---------- ----------
/u01/app/oracle/orcl2/system01.dbf SYSTEM 1391168 1298641 1298642 1391168
/u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1391168 1298641 1298642 1391168
/u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1391168 1298641 1298642 1391168
/u01/app/oracle/orcl2/users01.dbf ONLINE 1391168 1390956 1391155 1391168
/u01/app/oracle/orcl2/example01.dbf ONLINE 1391168 1298641 1298642 1391168
SYS@orcl2> alter tablespace users read only;
Tablespace altered.
SYS@orcl2> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE ONLINE
6 rows selected.
SYS@orcl2> @dfile
NAME STATUS CON_CKPT STOP OFF ONN H_CKPT
----------------------------------- ------- ---------- ---------- ---------- ---------- ----------
/u01/app/oracle/orcl2/system01.dbf SYSTEM 1391168 1298641 1298642 1391168
/u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1391168 1298641 1298642 1391168
/u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1391168 1298641 1298642 1391168
/u01/app/oracle/orcl2/users01.dbf ONLINE 1392487 1392487 1390956 1391155 1392487
/u01/app/oracle/orcl2/example01.dbf ONLINE 1391168 1298641 1298642 1391168
RAED ONLY 로 변경되었으니 백업을 받아둔다.
RMAN> backup tablespace users tag='ro';
Starting backup at 23-APR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-APR-19
channel ORA_DISK_1: finished piece 1 at 23-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_23/o1_mf_nnndf_RO_gcx9ch86_.bkp tag=RO comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-APR-19
Starting Control File and SPFILE Autobackup at 23-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_23/o1_mf_s_1006353664_gcx9cjcy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-APR-19
SYS@orcl2> delete hr.cp_emp
where employee_id=198;
delete hr.cp_emp
*
ERROR at line 1:
ORA-00372: file 4 cannot be modified at this time
ORA-01110: data file 4: '/u01/app/oracle/orcl2/users01.dbf'
SYS@orcl2> @check
System altered.
SYS@orcl2> @dfile
NAME STATUS CON_CKPT STOP OFF ONN H_CKPT
----------------------------------- ------- ---------- ---------- ---------- ---------- ----------
/u01/app/oracle/orcl2/system01.dbf SYSTEM 1392569 1298641 1298642 1392569
/u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1392569 1298641 1298642 1392569
/u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1392569 1298641 1298642 1392569
/u01/app/oracle/orcl2/users01.dbf ONLINE 1392487 1392487 1390956 1391155 1392487
/u01/app/oracle/orcl2/example01.dbf ONLINE 1392569 1298641 1298642 1392569
****************************************************************************************************
사례1) Current Controlfile(RO) + RO TS Backup이 있다면
: 복구과정이 필요하지 않으며 백업을 restore만해서 사용한다.
****************************************************************************************************
SYS@orcl2> !rm /u01/app/oracle/orcl2/users01.dbf
RMAN> list failure detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
8 HIGH OPEN 23-APR-19 One or more non-system datafiles need media recovery
Impact: See impact for individual child failures
List of child failures for parent failure ID 8
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2976 HIGH OPEN 23-APR-19 Datafile 4: '/u01/app/oracle/orcl2/users01.dbf' needs media recovery
Impact: Some objects in tablespace USERS might be unavailable
RMAN> sql 'alter tablespace users offline';
sql statement: alter tablespace users offline
RMAN> restore tablespace users from tag='ro';
Starting restore at 23-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_23/o1_mf_nnndf_RO_gcx9ch86_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_23/o1_mf_nnndf_RO_gcx9ch86_.bkp tag=RO
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 23-APR-19
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online
SYS@orcl2> @dfile
NAME STATUS CON_CKPT STOP OFF ONN H_CKPT
----------------------------------- ------- ---------- ---------- ---------- ---------- ----------
/u01/app/oracle/orcl2/system01.dbf SYSTEM 1392569 1298641 1298642 1392569
/u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1392569 1298641 1298642 1392569
/u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1392569 1298641 1298642 1392569
/u01/app/oracle/orcl2/users01.dbf ONLINE 1392487 1392487 1390956 1391155 1392487
/u01/app/oracle/orcl2/example01.dbf ONLINE 1392569 1298641 1298642 1392569
****************************************************************************************************
사례2) Current Controlfile(RO) + RW TS Backup
: 복구과정이 필요
RW 백업이후의 복구가 필요
****************************************************************************************************
users 테이블스페이스를 read write 로 변경후 백업받는다.(RW)
SYS@orcl2> alter tablespace users read write;
Tablespace altered.
SYS@orcl2> insert into scott.dept values(50,'before','ddd');
1 row created.
SYS@orcl2> commit;
Commit complete.
RMAN> backup tablespace users tag='rw';
Starting backup at 23-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-APR-19
channel ORA_DISK_1: finished piece 1 at 23-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_23/o1_mf_nnndf_RW_gcx9l658_.bkp tag=RW comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-APR-19
Starting Control File and SPFILE Autobackup at 23-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_23/o1_mf_s_1006353879_gcx9l798_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-APR-19
SYS@orcl2> insert into scott.dept values(60,'after','dddd');
1 row created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> alter tablespace users read only;
Tablespace altered.
[orcl2:~]$ rm /u01/app/oracle/orcl2/users01.dbf
--READ ONLY 상태의 백업이 없는 상태에서 장애발생
RMAN> list failure detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
8 HIGH OPEN 23-APR-19 One or more non-system datafiles need media recovery
Impact: See impact for individual child failures
List of child failures for parent failure ID 8
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2976 HIGH OPEN 23-APR-19 Datafile 4: '/u01/app/oracle/orcl2/users01.dbf' needs media recovery
Impact: Some objects in tablespace USERS might be unavailable
RMAN> sql 'alter tablespace users offline';
sql statement: alter tablespace users offline
-- rw 에서 ro로 변경될 때까지의 복구과정이 필요하다.
RMAN> restore tablespace users from tag='rw';
Starting restore at 23-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_23/o1_mf_nnndf_RW_gcx9l658_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_23/o1_mf_nnndf_RW_gcx9l658_.bkp tag=RW
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 23-APR-19
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 04/23/2019 14:47:04
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace users online
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/orcl2/users01.dbf'
RMAN> recover tablespace users;
Starting recover at 23-APR-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-APR-19
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online
SYS@orcl2> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 before ddd
60 after dddd
6 rows selected.
SYS@orcl2> @dfile
NAME STATUS CON_CKPT STOP OFF ONN H_CKPT
----------------------------------- ------- ---------- ---------- ---------- ---------- ----------
/u01/app/oracle/orcl2/system01.dbf SYSTEM 1392569 1298641 1298642 1392569
/u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1392569 1298641 1298642 1392569
/u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1392569 1298641 1298642 1392569
/u01/app/oracle/orcl2/users01.dbf ONLINE 1392785 1392785 1392487 1392688 1392785
/u01/app/oracle/orcl2/example01.dbf ONLINE 1392569 1298641 1298642 1392569
SYS@orcl2> alter tablespace users read write;
Tablespace altered.
추가정보
@con
SYS@orcl2> select name from v$controlfile;
@log
select group#, sequence#, status, archived, members, first_change#, bytes/1024/1024 mb
from v$log;
@logfile
col status for a10
col member for a35
SELECT a.group# ,b.sequence#,a.member , b.bytes/1024/1024 MB , b.archived , b.status, b.first_change#
FROM v$logfile a , v$log b
WHERE a.group#=b.group#
ORDER BY 1;
@data
col name for a35
select name, status from v$datafile;
@datafile
col name1 for a15
col name2 for a35
select a.file#,b.name name1,a.name name2,a.status,a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
@switch
alter system switch logfile;
@check
alter system checkpoint;
@logmnr
col filename for a35
select db_name, filename from v$logmnr_logs;
@db_list2
set echo off
set pages 0
set head off
set feed off
create pfile from spfile;
spool orcl2_backup.sh
select 'cp -av ' || name||' /home/oracle/backup/arch/cold'
from v$controlfile
union all
select 'cp -av ' || name||' /home/oracle/backup/arch/cold'
from v$datafile
union all
select 'cp -av ' || member ||' /home/oracle/backup/arch/cold'
from v$logfile
union all
select 'cp -av $ORACLE_HOME/dbs/initorcl2.ora /home/oracle/backup/arch/cold' from dual;
spool off
set feedback on
set head on
set pages 100
set echo on
shutdown immediate
! chmod 755 orcl2_backup.sh
! . orcl2_backup.sh
startup
-- 불완전 복구 - scn
SYS@orcl2> @db_list2
RMAN> backup as compressed backupset format '/home/oracle/backup/rman/%U_%T' database;
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
69 Full 278.13M DISK 00:00:37 23-APR-19
BP Key: 74 Status: AVAILABLE Compressed: YES Tag: TAG20190423T152554
Piece Name: /home/oracle/backup/rman/2otvnhs2_1_1_20190423
List of Datafiles in backup set 69
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1395523 23-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1395523 23-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1395523 23-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1395523 23-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1395523 23-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
70 Full 9.73M DISK 00:00:00 23-APR-19
BP Key: 75 Status: AVAILABLE Compressed: NO Tag: TAG20190423T152639
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_23/o1_mf_s_1006356399_gcxd0zmm_.bkp
SPFILE Included: Modification time: 23-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1395551 Ckp time: 23-APR-19
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 4 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE
2 5 /u01/app/oracle/orcl2/redo02.log 50 NO CURRENT
3 3 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
3 rows selected.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @check
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 7 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE
2 8 /u01/app/oracle/orcl2/redo02.log 50 NO CURRENT
3 6 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
SYS@orcl2> create table hr.emp_arch as select * from hr.employees;
Table created.
-- 8번 sequence(current)에 기록된다.
SYS@orcl2> select count(*) from hr.emp_arch;
COUNT(*)
----------
107
SYS@orcl2> @switch
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 10 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE
2 11 /u01/app/oracle/orcl2/redo02.log 50 NO CURRENT
3 9 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
-- current 11
-- 11번을 지울 예정
SYS@orcl2> create table hr.dept_arch as select * from hr.departments;
Table created.
-- 11번 sequence(current)에 기록된다.
SYS@orcl2> select count(*) from hr.dept_arch;
COUNT(*)
----------
27
SYS@orcl2> @switch
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 13 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE
2 14 /u01/app/oracle/orcl2/redo02.log 50 NO CURRENT
3 12 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
-- 11번 없게 하자.
-- 있으면 온라인 로그를 통해 완전복구가 가능하다.
SYS@orcl2> !ls arch1 arch2
arch1:
arch_1_10_1006336720.arc arch_1_1_1006276794.arc arch_1_4_1006276794.arc arch_1_7_1006336720.arc
arch_1_11_1006336720.arc arch_1_2_1006276794.arc arch_1_5_1006276794.arc arch_1_8_1006336720.arc
arch_1_12_1006336720.arc arch_1_3_1006276794.arc arch_1_5_1006336720.arc arch_1_9_1006336720.arc
arch_1_13_1006336720.arc arch_1_4_1006274601.arc arch_1_6_1006336720.arc
arch2:
arch_1_10_1006336720.arc arch_1_13_1006336720.arc arch_1_7_1006336720.arc
arch_1_11_1006336720.arc arch_1_5_1006336720.arc arch_1_8_1006336720.arc
arch_1_12_1006336720.arc arch_1_6_1006336720.arc arch_1_9_1006336720.arc
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1395982
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1395982
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1395982
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1395982
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1395982
SYS@orcl2> !rm /u01/app/oracle/orcl2/users01.dbf
SYS@orcl2> !ls /home/oracle/arch1/arch_1_11*
/home/oracle/arch1/arch_1_11_1006336720.arc
SYS@orcl2> !ls /home/oracle/arch2/arch_1_11*
/home/oracle/arch2/arch_1_11_1006336720.arc
SYS@orcl2> !rm /home/oracle/arch2/arch_1_11*
SYS@orcl2> !rm /home/oracle/arch1/arch_1_11*
SYS@orcl2> startup force
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/orcl2/users01.dbf'
SYS@orcl2> select * from dba_data_files where file_id = 4;
FILE_NAME
----------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT
---------- ------------------------------ ---------- ---------- --------- ------------ ---
MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------- ---------- ------------ ---------- ----------- -------
/u01/app/oracle/orcl2/users01.dbf
4 USERS 6553600 800 AVAILABLE 4 YES
3.4360E+10 4194302 160 5505024 672 ONLINE
RMAN> sql 'alter tablespace users offline immediate';
sql statement: alter tablespace users offline immediate
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 04/23/2019 15:50:32
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace users offline immediate
ORA-01109: database not open
RMAN> restore tablespace users;
Starting restore at 23-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/rman/2otvnhs2_1_1_20190423
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/2otvnhs2_1_1_20190423 tag=TAG20190423T152554
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 23-APR-19
RMAN> recover tablespace users;
Starting recover at 23-APR-19
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 5 is already on disk as file /home/oracle/arch1/arch_1_5_1006336720.arc
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/arch1/arch_1_6_1006336720.arc
archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/arch1/arch_1_7_1006336720.arc
archived log for thread 1 with sequence 8 is already on disk as file /home/oracle/arch1/arch_1_8_1006336720.arc
archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/arch1/arch_1_9_1006336720.arc
archived log for thread 1 with sequence 10 is already on disk as file /home/oracle/arch1/arch_1_10_1006336720.arc
archived log for thread 1 with sequence 12 is already on disk as file /home/oracle/arch1/arch_1_12_1006336720.arc
archived log for thread 1 with sequence 13 is already on disk as file /home/oracle/arch1/arch_1_13_1006336720.arc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/23/2019 15:51:41
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 11 and starting SCN of 1395797 found to restore
-- 완전 복구 실패
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
RMAN> restore database until scn 1395797;
Starting restore at 23-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
skipping datafile 4; already restored to file /u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/orcl2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/orcl2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/orcl2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/orcl2/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/rman/2otvnhs2_1_1_20190423
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/2otvnhs2_1_1_20190423 tag=TAG20190423T152554
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-APR-19
RMAN> recover database until scn 1395797;
Starting recover at 23-APR-19
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 5 is already on disk as file /home/oracle/arch1/arch_1_5_1006336720.arc
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/arch1/arch_1_6_1006336720.arc
archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/arch1/arch_1_7_1006336720.arc
archived log for thread 1 with sequence 8 is already on disk as file /home/oracle/arch1/arch_1_8_1006336720.arc
archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/arch1/arch_1_9_1006336720.arc
archived log for thread 1 with sequence 10 is already on disk as file /home/oracle/arch1/arch_1_10_1006336720.arc
archived log file name=/home/oracle/arch1/arch_1_5_1006336720.arc thread=1 sequence=5
archived log file name=/home/oracle/arch1/arch_1_6_1006336720.arc thread=1 sequence=6
archived log file name=/home/oracle/arch1/arch_1_7_1006336720.arc thread=1 sequence=7
archived log file name=/home/oracle/arch1/arch_1_8_1006336720.arc thread=1 sequence=8
archived log file name=/home/oracle/arch1/arch_1_9_1006336720.arc thread=1 sequence=9
archived log file name=/home/oracle/arch1/arch_1_10_1006336720.arc thread=1 sequence=10
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-APR-19
-- 안되면 recover database ; 또는 recover database until cancel; 해보기 ~
RMAN> alter database open resetlogs;
database opened
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 1 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT
2 0 /u01/app/oracle/orcl2/redo02.log 50 YES UNUSED
3 0 /u01/app/oracle/orcl2/redo03.log 50 YES UNUSED
SYS@orcl2> select count(*) from hr.emp_arch;
COUNT(*)
----------
107
이후 반드시 백업
RMAN> backup as compressed backupset database;
--불완전 복구
-- orcl2 DB에서 테이블을 truncate하여 영구적으로 삭제된 상황
-- temp DB를 만들어서 truncate 한 시점까지 불완전 복구하여 export한다음
-- orcl2 DB 로 import하여 해결한다.
-- DB 정보 확인, RMAN backup list 확인
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 4 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT
2 2 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 3 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1397529
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1397529
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1397529
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1397529
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1397529
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/rman/%F';
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /u01/app/oracle/orcl2/system01.dbf
2 520 SYSAUX *** /u01/app/oracle/orcl2/sysaux01.dbf
3 105 UNDOTBS1 *** /u01/app/oracle/orcl2/undotbs01.dbf
4 6 USERS *** /u01/app/oracle/orcl2/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/orcl2/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2 20 TEMP 32767 /u01/app/oracle/orcl2/temp01.dbf
RMAN> backup as compressed backupset format '/home/oracle/backup/rman/%d_%U_%T' database;
Starting backup at 23-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-APR-19
channel ORA_DISK_1: finished piece 1 at 23-APR-19
piece handle=/home/oracle/backup/rman/ORCL2_2ttvnkvt_1_1_20190423 tag=TAG20190423T161909 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 23-APR-19
Starting Control File and SPFILE Autobackup at 23-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_23/o1_mf_s_1006359594_gcxh4tbm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-APR-19
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
74 Full 278.60M DISK 00:00:35 23-APR-19
BP Key: 79 Status: AVAILABLE Compressed: YES Tag: TAG20190423T161909
Piece Name: /home/oracle/backup/rman/ORCL2_2ttvnkvt_1_1_20190423
List of Datafiles in backup set 74
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1397652 23-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1397652 23-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1397652 23-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1397652 23-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1397652 23-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
75 Full 9.73M DISK 00:00:00 23-APR-19
BP Key: 80 Status: AVAILABLE Compressed: NO Tag: TAG20190423T161954
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_23/o1_mf_s_1006359594_gcxh4tbm_.bkp
SPFILE Included: Modification time: 23-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1397680 Ckp time: 23-APR-19
-- DBA SESSION 에서 DB 작업하다가 테이블 truncate 발생
SYS@orcl2> create table hr.new_emp tablespace users as select * from hr.employees;
Table created.
-- current sequnce(4번)에 생성된다.
6
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 4 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT
2 2 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 3 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
SYS@orcl2> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
23-APR-19 04.21.40.522724 PM +09:00
SYS@orcl2> truncate table hr.new_emp;
Table truncated.
SYS@orcl2> select count(*) from hr.new_emp;
COUNT(*)
----------
0
-- logminer 분석해서 truncate 시간 알아내자
SYS@orcl2> begin
dbms_logmnr.add_logfile (
logfilename => '/u01/app/oracle/orcl2/redo01.log' , options => dbms_logmnr.new);
dbms_logmnr.add_logfile (
logfilename => '/u01/app/oracle/orcl2/redo02.log' , options => dbms_logmnr.addfile);
dbms_logmnr.add_logfile (
logfilename => '/u01/app/oracle/orcl2/redo03.log' , options => dbms_logmnr.addfile);
end;
/
PL/SQL procedure successfully completed.
SYS@orcl2> select db_name, filename from v$logmnr_logs;
DB_NAME FILENAME
-------- -----------------------------------
ORCL2 /u01/app/oracle/orcl2/redo02.log
ORCL2 /u01/app/oracle/orcl2/redo03.log
ORCL2 /u01/app/oracle/orcl2/redo01.log
SYS@orcl2> begin
dbms_logmnr.start_logmnr
(options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);
end;
/
PL/SQL procedure successfully completed.
SYS@orcl2> select to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss'), operation, sql_redo
from v$logmnr_contents
where seg_name = 'NEW_EMP';
TO_CHAR(TIMESTAMP,' OPERATION
------------------- --------------------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
2019-04-23 16:20:53 DDL
create table hr.new_emp tablespace users as select * from hr.employees;
2019-04-23 16:21:57 DDL
truncate table hr.new_emp;
SYS@orcl2> begin
dbms_logmnr.end_logmnr;
end;
/
PL/SQL procedure successfully completed.
SYS@orcl2> create pfile = '$ORACLE_HOME/dbs/initorcl_temp.ora' from spfile;
File created.
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
-- TEMP DB로 복제해서 불완전 복구하여 truncate 된 테이블을 복구하기로 결정
[orcl2:~]$ mkdir -p /home/oracle/temp
현재 컨트롤 파일과 log 파일을 temp DB 디렉토리로 복사한다.
[orcl2:~]$ cp -av /u01/app/oracle/orcl2/*.ctl /home/oracle/temp
`/u01/app/oracle/orcl2/control01.ctl' -> `/home/oracle/temp/control01.ctl'
[orcl2:~]$ cp -av /u01/app/oracle/orcl2/*.log /home/oracle/temp
`/u01/app/oracle/orcl2/redo01.log' -> `/home/oracle/temp/redo01.log'
`/u01/app/oracle/orcl2/redo02.log' -> `/home/oracle/temp/redo02.log'
`/u01/app/oracle/orcl2/redo03.log' -> `/home/oracle/temp/redo03.log'
[orcl2:~]$ ls /home/oracle/temp
control01.ctl redo01.log redo02.log redo03.log
[orcl2:~]$ vi $ORACLE_HOME/dbs/initorcl_temp.ora
...................
#*.control_files='/u01/app/oracle/orcl2/control01.ctl'#Restore Controlfile
*.control_files='/home/oracle/temp/control01.ctl'
...................
-- 수정한 파라미터 파일로 nomount하고 복사한 컨트롤 파일로 mount 모드까지 가서 데이타 파일의 경로명을 바꾼다.
< TEMP DB >
[orcl2:~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 23 16:29:04 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SYS@orcl2> startup pfile=$ORACLE_HOME/dbs/initorcl_temp.ora mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> select name from v$controlfile;
NAME
----------------------------------------------
/home/oracle/temp/control01.ctl
SYS@orcl2> select name from v$datafile;
NAME
----------------------------------------------
/u01/app/oracle/orcl2/system01.dbf
/u01/app/oracle/orcl2/sysaux01.dbf
/u01/app/oracle/orcl2/undotbs01.dbf
/u01/app/oracle/orcl2/users01.dbf
/u01/app/oracle/orcl2/example01.dbf
SYS@orcl2> select member from v$logfile;
MEMBER
----------------------------------------------
/u01/app/oracle/orcl2/redo02.log
/u01/app/oracle/orcl2/redo01.log
/u01/app/oracle/orcl2/redo03.log
-- <TEMP DB >: RMAN SESSION -- set newname과 switch 수행해서 temp DB 인식하도록 수정한 후 복구한다.
RMAN>
run {
sql "alter database rename file ''/u01/app/oracle/orcl2/redo01.log'' to ''/home/oracle/temp/redo01.log''";
sql "alter database rename file ''/u01/app/oracle/orcl2/redo02.log'' to ''/home/oracle/temp/redo02.log''";
sql "alter database rename file ''/u01/app/oracle/orcl2/redo03.log'' to ''/home/oracle/temp/redo03.log''";
set newname for datafile 1 to '/home/oracle/temp/system01.dbf';
set newname for datafile 2 to '/home/oracle/temp/sysaux01.dbf';
set newname for datafile 3 to '/home/oracle/temp/undotbs01.dbf';
set newname for datafile 4 to '/home/oracle/temp/users01.dbf';
sql 'alter session set nls_date_format = "yyyy-mm-dd hh24:mi:ss"';
set until time='2019-04-23 16:21:00';
restore database skip tablespace example;
switch datafile all;
recover database skip tablespace example;
sql 'alter database datafile 5 offline drop';
alter database open resetlogs;
}
sql statement: alter database rename file ''/u01/app/oracle/orcl2/redo01.log'' to ''/home/oracle/temp/redo01.log''
sql statement: alter database rename file ''/u01/app/oracle/orcl2/redo02.log'' to ''/home/oracle/temp/redo02.log''
sql statement: alter database rename file ''/u01/app/oracle/orcl2/redo03.log'' to ''/home/oracle/temp/redo03.log''
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
sql statement: alter session set nls_date_format = "yyyy-mm-dd:hh24:mi:ss"
executing command: SET until clause
Starting restore at 23-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/orcl2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/orcl2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/orcl2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/rman/ORCL2_2ttvnkvt_1_1_20190423
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/ORCL2_2ttvnkvt_1_1_20190423 tag=TAG20190423T161909
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-APR-19
Starting recover at 23-APR-19
using channel ORA_DISK_1
Executing: alter database datafile 5 offline
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-APR-19
sql statement: alter database datafile 5 offline drop
database opened
-- <TEMP DB> DBA SESSION에서 불완전 복구 성공했는지 확인하고 export 받는다.
SYS@orcl2> select count(*) from hr.new_emp;
COUNT(*)
----------
107
SYS@orcl2> select f.file_name from dba_extents e, dba_data_files f where e.file_id = f.file_id and e.segment_name = 'NEW_EMP';
FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/users01.dbf
SYS@orcl2> !
[orcl2:~]$ exp hr/hr tables=new_emp file=new_emp.dmp
Export: Release 11.2.0.1.0 - Production on Tue Apr 23 16:35:22 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table NEW_EMP 107 rows exported
Export terminated successfully without warnings.
[orcl2:~]$ ls -al new_emp.dmp
-rw-r--r-- 1 oracle oinstall 24576 Apr 23 16:35 new_emp.dmp
[orcl2:~]$ exit
-- <ORCL2 DB> export 받은 파일을 import하여 유실했던 데이타를 복구한다.
[orcl2:~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 23 17:35:07 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
Database opened.
SYS@orcl2> select count(*) from hr.new_emp;
COUNT(*)
----------
0
SYS@orcl2> !
[orcl2:~]$ imp hr/hr file=new_emp.dmp tables=new_emp data_only=y
Import: Release 11.2.0.1.0 - Production on Tue Apr 23 17:36:25 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into HR
. . importing table "NEW_EMP" 107 rows imported
Import terminated successfully without warnings.
[orcl2:~]$ exit
exit
SYS@orcl2> select count(*) from hr.new_emp;
COUNT(*)
----------
107
-- 정리
SYS@orcl2> !rm -rf temp
SYS@orcl2> !rm $ORACLE_HOME/dbs/initorcl_temp.ora
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]Flashback 기술01 (0) | 2019.05.03 |
|---|---|
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-Recovery Catalog (0) | 2019.05.03 |
| [Oracle DBA]Clone DB (0) | 2019.05.03 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN03 (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-TEMP,UNDO (0) | 2019.04.22 |