버전 : ORACLE 11.2.0.1
| racdb(원본 db_name) to newdb(복구 db_name) - 복구 순서 1. pfile 수정 (원본 db_name으로 설정) 2. nomount 상태에서 controlfile 복구 3. mount 후 restore database 4. pfile 수정 (변경 후 db_name으로 설정) 및 control file 재생성 5. recover database (원하는 시점까지) 6. database open resetlogs |
BACKUP
데이터 생성 및 full 백업
-- 테이블 생성
SQL> create table sk.t1 (c1 varchar(10), c2 varchar(10)) ;
Table created.
SQL> exit
-- FULL BACKUP 진행
[oracle@rac1 backup]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 28 23:05:36 2022
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1039423392)
RMAN> backup format '/home/oracle/backup/%d_backup_%s_%p_%T' database ;
Starting backup at 22/05/28
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 instance=racdb1 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=00006 name=+DATA/racdb/datafile/ts01.306.1057870055
input datafile file number=00001 name=+DATA/racdb/system01.dbf
input datafile file number=00002 name=+DATA/racdb/sysaux01.dbf
input datafile file number=00004 name=+DATA/racdb/users01.dbf
input datafile file number=00003 name=+DATA/racdb/undotbs01.dbf
input datafile file number=00005 name=+DATA/racdb/undotbs02.dbf
channel ORA_DISK_1: starting piece 1 at 22/05/28
channel ORA_DISK_1: finished piece 1 at 22/05/28
piece handle=/home/oracle/backup/RACDB_backup_11_1_20220528 tag=TAG20220528T230540 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 22/05/28
channel ORA_DISK_1: finished piece 1 at 22/05/28
piece handle=/home/oracle/backup/RACDB_backup_12_1_20220528 tag=TAG20220528T230540 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22/05/28
RMAN> exit
Recovery Manager complete.
-- 데이터 INSERT
[oracle@rac1 backup]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 28 23:06:04 2022
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> insert into sk.t1 values (1,'a');
1 row created.
SQL> commit ;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into sk.t1 values (2,'a');
1 row created.
SQL> commit ;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into sk.t1 values (3,'a');
1 row created.
SQL> commit ;
Commit complete.
-- 모든 노드 log switch 실행
SQL> alter system archive log current;
System altered.
SQL> exit
아카이브 로그 및 컨트롤파일 백업
[oracle@rac1 backup]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 28 23:16:23 2022
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1039423392)
-- 복원을 위한 최소 SCN 확인(1973281)
RMAN> list backup ;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.72G DISK 00:00:08 22/05/28
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20220528T230540
Piece Name: /home/oracle/backup/RACDB_backup_11_1_20220528
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------- ----
1 Full 1973281 22/05/28 +DATA/racdb/system01.dbf
2 Full 1973281 22/05/28 +DATA/racdb/sysaux01.dbf
3 Full 1973281 22/05/28 +DATA/racdb/undotbs01.dbf
4 Full 1973281 22/05/28 +DATA/racdb/users01.dbf
5 Full 1973281 22/05/28 +DATA/racdb/undotbs02.dbf
6 Full 1973281 22/05/28 +DATA/racdb/datafile/ts01.306.1057870055
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 17.80M DISK 00:00:02 22/05/28
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20220528T230540
Piece Name: /home/oracle/backup/RACDB_backup_12_1_20220528
SPFILE Included: Modification time: 22/05/28
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 1973313 Ckp time: 22/05/28
-- 위에서 확인한 SCN 이후 아카이브로그 백업
RMAN> backup archivelog from scn 1973281 format '/home/oracle/backup/%d_arch_%s_%p_%T' ;
Starting backup at 22/05/28
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=182 RECID=351 STAMP=1105916884
input archived log thread=2 sequence=195 RECID=354 STAMP=1105916958
input archived log thread=1 sequence=183 RECID=352 STAMP=1105916940
input archived log thread=1 sequence=184 RECID=353 STAMP=1105916956
input archived log thread=1 sequence=185 RECID=356 STAMP=1105917631
input archived log thread=2 sequence=196 RECID=355 STAMP=1105917630
channel ORA_DISK_1: starting piece 1 at 22/05/28
channel ORA_DISK_1: finished piece 1 at 22/05/28
piece handle=/home/oracle/backup/RACDB_arch_13_1_20220528 tag=TAG20220528T232039 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22/05/28
-- 컨트롤파일 백업
RMAN> backup format '/home/oracle/backup/%d_ctl_%s_%p_%T' current controlfile ;
Starting backup at 22/05/28
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 22/05/28
channel ORA_DISK_1: finished piece 1 at 22/05/28
piece handle=/home/oracle/backup/RACDB_ctl_14_1_20220528 tag=TAG20220528T232303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22/05/28
RMAN>
RESTORE
pfile 편집, startup nomount, 컨트롤파일 restore
restore 진행 시 원본 db_name 과 동일해야 함
*.db_name='racdb'
-- nomount에 사용할 pfile 적당히 편집(db_name은 원본과 동일하게 설정)
[oracle@svr1 backup]$ cat pfile.ora
*.compatible='11.2.0.0.0'
*.control_files='/data/backup/tmp_ctrl.ctl'
#*.control_files='/u01/app/oracle/oradata/newdb/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='racdb'
#*.db_name='newdb'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest=''
*.log_archive_dest_1='location=/home/oracle/archive_log'
*.log_archive_format='newdb_%t_%s_%r.arc'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=838860800
*.sga_target=838860800
*.undo_tablespace='UNDOTBS1'
-- startup nomount
SQL> startup nomount pfile='/data/backup/pfile.ora';
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 230688800 bytes
Database Buffers 595591168 bytes
Redo Buffers 6606848 bytes
SQL> exit
[oracle@svr1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 29 12:02:07 2022
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (not mounted)
-- 임시경로에 controlfile restore
RMAN> restore controlfile to '/data/backup/tmp_ctrl.ctl' from '/data/backup/RACDB_ctl_14_1_20220528' ;
Starting restore at 22/05/29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 22/05/29
datafile restore
경로 재지정 restore 명령 후 switch datafile 꼭 해줄 것
백업 경로가 기존과 다를 시 현재 백업 이미지가 있는 경로 추가
SYSTEM, UNDO 테이블스페이스는 필수 복구
-- alter database mount
RMAN> sql "alter database mount" ;
sql statement: alter database mount
released channel: ORA_DISK_1
-- 컨트롤파일에 저장되어있는 데이터파일 확인
RMAN> report schema ;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** +DATA/racdb/system01.dbf
2 0 SYSAUX *** +DATA/racdb/sysaux01.dbf
3 0 UNDOTBS1 *** +DATA/racdb/undotbs01.dbf
4 0 USERS *** +DATA/racdb/users01.dbf
5 0 UNDOTBS2 *** +DATA/racdb/undotbs02.dbf
6 0 TS01 *** +DATA/racdb/datafile/ts01.306.1057870055
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 0 TEMP 32767 +DATA/racdb/temp01.dbf
-- datafile restore(불필요한 테이블스페이스 TS01은 제외)
RMAN> run{
set newname for datafile 1 to '/u01/app/oracle/oradata/newdb/system01.dbf' ;
set newname for datafile 2 to '/u01/app/oracle/oradata/newdb/sysaux01.dbf' ;
set newname for datafile 3 to '/u01/app/oracle/oradata/newdb/undotbs01.dbf' ;
set newname for datafile 4 to '/u01/app/oracle/oradata/newdb/users01.dbf' ;
set newname for datafile 5 to '/u01/app/oracle/oradata/newdb/undotbs02.dbf' ;
restore datafile 1,2,3,4,5;
switch datafile all;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 22/05/29
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 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/oradata/newdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/newdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/newdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/newdb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/newdb/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/RACDB_backup_11_1_20220528
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /home/oracle/backup/RACDB_backup_11_1_20220528
ORA-19505: failed to identify file "/home/oracle/backup/RACDB_backup_11_1_20220528"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
failover to previous backup
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/oradata/newdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/newdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/newdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/newdb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/newdb/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/RACDB_backup_3_1_20220528
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /home/oracle/backup/RACDB_backup_3_1_20220528
ORA-19505: failed to identify file "/home/oracle/backup/RACDB_backup_3_1_20220528"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/29/2022 12:02:38
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
-- 백업 정보 확인(백업이미지를 업로드한 경로 /data/backup/ 의 정보가 없다)
RMAN> list backup ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.72G DISK 00:00:08 22/05/28
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20220528T230540
Piece Name: /home/oracle/backup/RACDB_backup_11_1_20220528
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------- ----
1 Full 1973281 22/05/28 +DATA/racdb/system01.dbf
2 Full 1973281 22/05/28 +DATA/racdb/sysaux01.dbf
3 Full 1973281 22/05/28 +DATA/racdb/undotbs01.dbf
4 Full 1973281 22/05/28 +DATA/racdb/users01.dbf
5 Full 1973281 22/05/28 +DATA/racdb/undotbs02.dbf
6 Full 1973281 22/05/28 +DATA/racdb/datafile/ts01.306.1057870055
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 17.80M DISK 00:00:02 22/05/28
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20220528T230540
Piece Name: /home/oracle/backup/RACDB_backup_12_1_20220528
SPFILE Included: Modification time: 22/05/28
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 1973313 Ckp time: 22/05/28
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 5.64M DISK 00:00:00 22/05/28
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20220528T232039
Piece Name: /home/oracle/backup/RACDB_arch_13_1_20220528
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------- ---------- ---------
1 182 1967364 22/05/28 1973417 22/05/28
1 183 1973417 22/05/28 1973888 22/05/28
1 184 1973888 22/05/28 1973932 22/05/28
1 185 1973932 22/05/28 1976154 22/05/28
2 195 1972751 22/05/28 1973936 22/05/28
2 196 1973936 22/05/28 1976150 22/05/28
-- 현재 백업 이미지 경로 등록
RMAN> catalog start with '/data/backup';
searching for all files that match the pattern /data/backup
List of Files Unknown to the Database
=====================================
File Name: /data/backup/RACDB_arch_13_1_20220528
File Name: /data/backup/RACDB_backup_12_1_20220528
File Name: /data/backup/RACDB_ctl_14_1_20220528
File Name: /data/backup/eleena
File Name: /data/backup/pfile.ora
File Name: /data/backup/RACDB_backup_11_1_20220528
File Name: /data/backup/soyul
File Name: /data/backup/soyul2
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /data/backup/RACDB_arch_13_1_20220528
File Name: /data/backup/RACDB_backup_12_1_20220528
File Name: /data/backup/RACDB_ctl_14_1_20220528
File Name: /data/backup/RACDB_backup_11_1_20220528
List of Files Which Where Not Cataloged
=======================================
File Name: /data/backup/eleena
RMAN-07517: Reason: The file header is corrupted
File Name: /data/backup/pfile.ora
RMAN-07517: Reason: The file header is corrupted
File Name: /data/backup/soyul
RMAN-07517: Reason: The file header is corrupted
File Name: /data/backup/soyul2
RMAN-07517: Reason: The file header is corrupted
-- 신규 백업 경로 확인(/data/backup/)
RMAN> list backup tag TAG20220528T230540 ;
List of Backup Sets
===================
BS Key Type LV Size
------- ---- -- ----------
5 Full 1.72G
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------- ----
1 Full 1973281 22/05/28 +DATA/racdb/system01.dbf
2 Full 1973281 22/05/28 +DATA/racdb/sysaux01.dbf
3 Full 1973281 22/05/28 +DATA/racdb/undotbs01.dbf
4 Full 1973281 22/05/28 +DATA/racdb/users01.dbf
5 Full 1973281 22/05/28 +DATA/racdb/undotbs02.dbf
6 Full 1973281 22/05/28 +DATA/racdb/datafile/ts01.306.1057870055
Backup Set Copy #1 of backup set 5
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:08 22/05/28 NO TAG20220528T230540
List of Backup Pieces for backup set 5 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
5 1 AVAILABLE /home/oracle/backup/RACDB_backup_11_1_20220528
Backup Set Copy #2 of backup set 5
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:08 22/05/29 NO TAG20220528T230540
List of Backup Pieces for backup set 5 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
11 1 AVAILABLE /data/backup/RACDB_backup_11_1_20220528
BS Key Type LV Size
------- ---- -- ----------
6 Full 17.80M
SPFILE Included: Modification time: 22/05/28
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 1973313 Ckp time: 22/05/28
Backup Set Copy #1 of backup set 6
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:02 22/05/28 NO TAG20220528T230540
List of Backup Pieces for backup set 6 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
6 1 AVAILABLE /home/oracle/backup/RACDB_backup_12_1_20220528
Backup Set Copy #2 of backup set 6
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:02 22/05/29 NO TAG20220528T230540
List of Backup Pieces for backup set 6 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
9 1 AVAILABLE /data/backup/RACDB_backup_12_1_20220528
-- datafile restore(불필요한 테이블스페이스 TS01은 제외)
RMAN> run{
set newname for datafile 1 to '/u01/app/oracle/oradata/newdb/system01.dbf' ;
set newname for datafile 2 to '/u01/app/oracle/oradata/newdb/sysaux01.dbf' ;
set newname for datafile 3 to '/u01/app/oracle/oradata/newdb/undotbs01.dbf' ;
set newname for datafile 4 to '/u01/app/oracle/oradata/newdb/users01.dbf' ;
set newname for datafile 5 to '/u01/app/oracle/oradata/newdb/undotbs02.dbf' ;
restore datafile 1,2,3,4,5;
switch datafile all;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 22/05/29
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 00001 to /u01/app/oracle/oradata/newdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/newdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/newdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/newdb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/newdb/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/RACDB_backup_11_1_20220528
channel ORA_DISK_1: errors found reading piece handle=/home/oracle/backup/RACDB_backup_11_1_20220528
channel ORA_DISK_1: failover to piece handle=/data/backup/RACDB_backup_11_1_20220528 tag=TAG20220528T230540
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 22/05/29
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1105963415 file name=/u01/app/oracle/oradata/newdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=1105963415 file name=/u01/app/oracle/oradata/newdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=1105963415 file name=/u01/app/oracle/oradata/newdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=1105963415 file name=/u01/app/oracle/oradata/newdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=1105963415 file name=/u01/app/oracle/oradata/newdb/undotbs02.dbf
RMAN> exit
Recovery Manager complete.
pfile 수정 및 컨트롤 파일 재생성
db_name, 컨트롤 파일 경로 변경 후 startup nomount
컨트롤 파일 생성 시 SET 옵션
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- pfile 수정 db_name 및 컨트롤파일 경로 변경
[oracle@svr1 backup]$ cat pfile.ora
*.compatible='11.2.0.0.0'
#*.control_files='/data/backup/tmp_ctrl.ctl'
*.control_files='/u01/app/oracle/oradata/newdb/control01.ctl'
*.db_block_size=8192
*.db_domain=''
#*.db_name='racdb'
*.db_name='newdb'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest=''
*.log_archive_dest_1='location=/home/oracle/archive_log'
*.log_archive_format='newdb_%t_%s_%r.arc'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=838860800
*.sga_target=838860800
*.undo_tablespace='UNDOTBS1'
-- 수정한 pfile 사용하여 startup nomount
SQL> startup nomount pfile='/data/backup/pfile.ora';
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 230688800 bytes
Database Buffers 595591168 bytes
Redo Buffers 6606848 bytes
-- 변경한 pfile 사용하여 spfile 저장
SQL> create spfile from pfile='/data/backup/pfile.ora' ;
File created.
-- 컨트롤 파일 재생성, redo 및 데이터 파일 경로 설정
SQL> CREATE CONTROLFILE set DATABASE "NEWDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/newdb/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/newdb/redo02.log' SIZE 200M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/newdb/system01.dbf',
'/u01/app/oracle/oradata/newdb/sysaux01.dbf',
'/u01/app/oracle/oradata/newdb/undotbs01.dbf',
'/u01/app/oracle/oradata/newdb/users01.dbf',
'/u01/app/oracle/oradata/newdb/undotbs02.dbf'
;
Control file created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
recover database
백업 경로가 기존과 다를 시 현재 백업 이미지가 있는 경로 추가
[oracle@svr1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 29 12:05:34 2022
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: NEWDB (DBID=1039423392, not open)
RMAN> list backup ;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> recover database ;
Starting recover at 22/05/29
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
starting media recovery
unable to find archived log
archived log thread=1 sequence=182
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/29/2022 12:06:11
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 182 and starting SCN of 1973281
-- 현재 백업 이미지 경로 등록
RMAN> catalog start with '/data/backup';
searching for all files that match the pattern /data/backup
List of Files Unknown to the Database
=====================================
File Name: /data/backup/RACDB_arch_13_1_20220528
File Name: /data/backup/RACDB_backup_12_1_20220528
File Name: /data/backup/RACDB_ctl_14_1_20220528
File Name: /data/backup/eleena
File Name: /data/backup/tmp_ctrl.ctl
File Name: /data/backup/pfile.ora
File Name: /data/backup/RACDB_backup_11_1_20220528
File Name: /data/backup/soyul
File Name: /data/backup/soyul2
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /data/backup/RACDB_arch_13_1_20220528
File Name: /data/backup/RACDB_backup_12_1_20220528
File Name: /data/backup/RACDB_ctl_14_1_20220528
File Name: /data/backup/tmp_ctrl.ctl
File Name: /data/backup/RACDB_backup_11_1_20220528
List of Files Which Where Not Cataloged
=======================================
File Name: /data/backup/eleena
RMAN-07517: Reason: The file header is corrupted
File Name: /data/backup/pfile.ora
RMAN-07517: Reason: The file header is corrupted
File Name: /data/backup/soyul
RMAN-07517: Reason: The file header is corrupted
File Name: /data/backup/soyul2
RMAN-07517: Reason: The file header is corrupted
-- recover database(아카이브 로그 반영)
RMAN> recover database ;
Starting recover at 22/05/29
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=182
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=195
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=183
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=184
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=185
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=196
channel ORA_DISK_1: reading from backup piece /data/backup/RACDB_arch_13_1_20220528
channel ORA_DISK_1: piece handle=/data/backup/RACDB_arch_13_1_20220528 tag=TAG20220528T232039
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/home/oracle/archive_log/newdb_1_182_1041806630.arc thread=1 sequence=182
archived log file name=/home/oracle/archive_log/newdb_2_195_1041806630.arc thread=2 sequence=195
archived log file name=/home/oracle/archive_log/newdb_1_183_1041806630.arc thread=1 sequence=183
archived log file name=/home/oracle/archive_log/newdb_1_184_1041806630.arc thread=1 sequence=184
archived log file name=/home/oracle/archive_log/newdb_1_185_1041806630.arc thread=1 sequence=185
archived log file name=/home/oracle/archive_log/newdb_2_196_1041806630.arc thread=2 sequence=196
unable to find archived log
archived log thread=2 sequence=197
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/29/2022 12:06:40
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 197 and starting SCN of 1976150
RMAN> exit
Recovery Manager complete.
database open resetlogs
불완전복구 이므로 resetlogs 옵션 필수
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled 에러 발생 시(버그) "_no_recovery_through_resetlogs"=true 히든 파라미터 설정 후 재시도
[oracle@svr1 ~]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 29 12:06:56 2022
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- open resetlogs 시 에러 발생(버그라고 함)
SQL> alter database open resetlogs ;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
-- 아래 히든파라미터 변경 후 재시도
SQL> alter system set "_no_recovery_through_resetlogs"=true;
System altered.
-- open resetlogs
SQL> alter database open resetlogs;
Database altered.
-- 테이블 조회(정상)
SQL> select * from sk.t1 ;
C1 C2
------------------------------ ------------------------------
1 a
2 a
3 a
-- 데이터 파일 조회
SQL> select * from dba_data_files ;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTEN MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------------------------- ------------ --------- ---------- ---------- ------------ ---------- ----------- ---------------------
/u01/app/oracle/oradata/newdb/undotbs02.dbf 5 UNDOTBS2 75497472 9216 AVAILABLE 5 YES 3.4360E+10 4194302 1 7444889 9088 ONLINE
/u01/app/oracle/oradata/newdb/users01.dbf 4 USERS 423362560 51680 AVAILABLE 4 YES 3.4360E+10 4194302 160 42231398 51552 ONLINE
/u01/app/oracle/oradata/newdb/undotbs01.dbf 3 UNDOTBS1 78643200 9600 AVAILABLE 3 YES 3.4360E+10 4194302 640 7759462 9472 ONLINE
/u01/app/oracle/oradata/newdb/sysaux01.dbf 2 SYSAUX 576716800 70400 AVAILABLE 2 YES 3.4360E+10 4194302 1280 57566822 70272 ONLINE
/u01/app/oracle/oradata/newdb/system01.dbf 1 SYSTEM 744488960 90880 AVAILABLE 1 YES 3.4360E+10 4194302 1280 74344038 90752 SYSTEM
/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING000 6 TS01 AVAILABLE 6 RECOVER
06
6 rows selected.
-- temp file 조회
SQL> select * from dba_temp_files ;
no rows selected
-- temp file 생성
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/newdb/temp01.dbf' size 20M ;
Tablespace altered.
SQL> select * from dba_temp_files ;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTEN MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------------------- ------------ --------- ---------- ---------- ------------ ---------- -----------
/u01/app/oracle/oradata/newdb/temp01.dbf 1 TEMP 20971520 2560 ONLINE 1 NO 0 0 0 19922944 2432
SQL>
'DBMS > ORACLE' 카테고리의 다른 글
| AFD disk 추가/삭제 (0) | 2022.06.09 |
|---|---|
| ASMLIB to AFD(ASM Filter Driver) 마이그레이션 (ASM SINGLE) (0) | 2022.06.08 |
| RMAN 불완전 복구 (0) | 2022.05.27 |
| 선분이력 테이블 (0) | 2022.05.25 |
| RAC relink 작업 (0) | 2022.05.17 |
댓글