http://cafe.naver.com/ocmkorea/4490 이현룡 교수님 카페 참조
=================================================================
RMAN 완전 복구 실습 1 (system file lost)
=================================================================
RMAN> backup database plus archivelog;
Starting backup at 12-OCT-15
RMAN> exit
Recovery Manager complete.
[oracle@lnx04 ~]$ rm -rf cd /u01/app/oracle/oradata/orcl/system01.dbf
[oracle@lnx04 ~]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 12 15:33:01 2015
Copyright (c) 1982, 2009, Oracle. 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
SYS@orcl>shutdown abort
ORACLE instance shut down.
SYS@orcl>!ls $ORACLE_BASE/oradata/orcl/
control01.ctl redo01_b.log redo02.log rman_tbs.dbf undotbs01.dbf
control02.ctl redo01.log redo03_b.log sysaux01.dbf users01.dbf
example01.dbf redo02_b.log redo03.log temp01.dbf
SYS@orcl>rtc
SP2-0042: unknown command "rtc" - rest of line ignored.
SYS@orcl>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx04 ~]$ rtc
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 12 15:33:42 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
[oracle@lnx04 ~]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 12 15:33:50 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SYS@orcl>startup mount
ORACLE instance started.
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1006635364 bytes
Database Buffers 687865856 bytes
Redo Buffers 11608064 bytes
Database mounted.
SYS@orcl>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx04 ~]$ rtc
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 12 15:34:02 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1420439357, not open)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database: ORA-01033: ORACLE initialization or shutdown in progress
[oracle@lnx04 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 12 15:34:08 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1420439357, not open)
RMAN> restore datafile 1;
Starting restore at 12-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: 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/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_49_1
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_49_1 tag=TAG20151012T152708
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_49_2
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_49_2 tag=TAG20151012T152708
channel ORA_DISK_1: restored backup piece 2
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_49_3
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_49_3 tag=TAG20151012T152708
channel ORA_DISK_1: restored backup piece 3
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_49_4
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_49_4 tag=TAG20151012T152708
channel ORA_DISK_1: restored backup piece 4
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_49_5
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_49_5 tag=TAG20151012T152708
channel ORA_DISK_1: restored backup piece 5
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_49_6
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_49_6 tag=TAG20151012T152708
channel ORA_DISK_1: restored backup piece 6
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_49_7
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_49_7 tag=TAG20151012T152708
channel ORA_DISK_1: restored backup piece 7
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 12-OCT-15
RMAN> recover datafile 1;
Starting recover at 12-OCT-15
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 12-OCT-15
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@lnx04 ~]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 12 15:35:05 2015
Copyright (c) 1982, 2009, Oracle. 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
SYS@orcl>!ls $ORACLE_BASE/oradata/orcl/
control01.ctl redo01_b.log redo02.log rman_tbs.dbf temp01.dbf
control02.ctl redo01.log redo03_b.log sysaux01.dbf undotbs01.dbf
example01.dbf redo02_b.log redo03.log system01.dbf users01.dbf
======================================================================
RMAN 완전 복구 실습 2 system tablespace 새로운 경로에 복구(H/W lost)
======================================================================
SYS@orcl>startup
ORACLE instance started.
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1006635364 bytes
Database Buffers 687865856 bytes
Redo Buffers 11608064 bytes
Database mounted.
Database opened.
SYS@orcl>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx04 ~]$ rm -rf /u01/app/oracle/oradata/orcl/system01.dbf
[oracle@lnx04 ~]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 12 15:50:46 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected.
SYS@orcl>shutdown abort;
ORACLE instance shut down.
SYS@orcl>exit
Disconnected
[oracle@lnx04 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 12 15:50:55 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1006635364 bytes
Database Buffers 687865856 bytes
Redo Buffers 11608064 bytes
RMAN> run {
2> set newname for datafile 1 to '/home/oracle/system01.dbf';
3> restore datafile 1;
4> switch datafile all;
5> recover datafile 1;
6> alter database open; }
executing command: SET NEWNAME
Starting restore at 12-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: 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 /home/oracle/system01.dbf
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_58_1
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_58_1 tag=TAG20151012T153823
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_58_2
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_58_2 tag=TAG20151012T153823
channel ORA_DISK_1: restored backup piece 2
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_58_3
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_58_3 tag=TAG20151012T153823
channel ORA_DISK_1: restored backup piece 3
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_58_4
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_58_4 tag=TAG20151012T153823
channel ORA_DISK_1: restored backup piece 4
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_58_5
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_58_5 tag=TAG20151012T153823
channel ORA_DISK_1: restored backup piece 5
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_58_6
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_58_6 tag=TAG20151012T153823
channel ORA_DISK_1: restored backup piece 6
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_58_7
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_58_7 tag=TAG20151012T153823
channel ORA_DISK_1: restored backup piece 7
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 12-OCT-15
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=892914819 file name=/home/oracle/system01.dbf
Starting recover at 12-OCT-15
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-OCT-15
database opened
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** /home/oracle/system01.dbf
2 510 SYSAUX *** /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 105 UNDOTBS1 *** /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/oradata/orcl/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/orcl/example01.dbf
6 100 RMAN_TBS *** /u01/app/oracle/oradata/orcl/rman_tbs.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf
SYS@orcl>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl>!cp /home/oracle/system01.dbf $ORACLE_BASE/oradata/orcl/system01.dbf
SYS@orcl>startup mount
ORACLE instance started.
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1006635364 bytes
Database Buffers 687865856 bytes
Redo Buffers 11608064 bytes
Database mounted.
SYS@orcl>alter database rename
2 file '/home/oracle/system01.dbf'
3 to '/u01/app/oracle/oradata/orcl/system01.dbf';
Database altered.
SYS@orcl>alter database open;
Database altered.
SYS@orcl>!rm /home/oracle/system01.dbf
======================================================================
RMAN 완전 복구 실습 3 일반 Tablespace ( data file lost )
======================================================================
[oracle@lnx04 ~]$ rm -rf/u01/app/oracle/oradata/orcl/users01.dbf
[oracle@lnx04 ~]$ rtc
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 12 15:59:50 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1420439357)
connected to recovery catalog database
RMAN> run {
2> sql "alter tablespace users offline immediate";
3> restore tablespace users;
4> recover tablespace users;
5> sql "alter tablespace users online";
6> }
sql statement: alter tablespace users offline immediate
Starting restore at 12-OCT-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=17 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/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_66_1
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_66_1 tag=TAG20151012T155900
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 12-OCT-15
Starting recover at 12-OCT-15
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 12-OCT-15
sql statement: alter tablespace users online
RMAN> exit
Recovery Manager complete.
[oracle@lnx04 ~]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 12 16:01:26 2015
Copyright (c) 1982, 2009, Oracle. 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
SYS@orcl>@dd.sql
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
--------------- -------------------------------------------------- ---------------
USERS /u01/app/oracle/oradata/orcl/users01.dbf 5
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 105
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 510
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 700
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf 100
RMAN_TBS /u01/app/oracle/oradata/orcl/rman_tbs.dbf 100
6 rows selected.
======================================================================
RMAN 완전 복구 실습 4 일반 Tablespace 다른 경로에 복구
======================================================================
SYS@orcl>!rm $ORACLE_BASE/oradata/orcl/example01.dbf
SYS@orcl>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx04 ~]$ rtc
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 12 16:08:10 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1420439357)
connected to recovery catalog database
RMAN> run {
2> sql "alter tablespace example offline immediate";
3> set newname for datafile 6 to '/home/oracle/example01.dbf';
4> restore(tablespace example);
5> switch datafile all;
6> recover tablespace example;
7> sql "alter tablespace example online";
8> }
sql statement: alter tablespace example offline immediate
executing command: SET NEWNAME
Starting restore at 12-OCT-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=22 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 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_65_1
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_65_1 tag=TAG20151012T155900
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_65_2
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_65_2 tag=TAG20151012T155900
channel ORA_DISK_1: restored backup piece 2
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 12-OCT-15
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch command at 10/12/2015 16:09:28
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20230: datafile copy not found in the repository
RMAN-06015: error while looking up datafile copy name: /home/oracle/example01.dbf
run {
sql "alter tablespace example offline immediate";
set newname for datafile 5 to '/home/oracle/example01.dbf';
restore(tablespace example);
switch datafile all;
recover tablespace example;
sql "alter tablespace example online";
8> }
sql statement: alter tablespace example offline immediate
executing command: SET NEWNAME
Starting restore at 2015-10-12:20:04:42
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=143 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=22 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 00005 to /home/oracle/example01.dbf
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_19_1
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_19_1 tag=TAG20151012T194526
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: reading from backup piece /BACKUP/rman/ORCL_19_2
channel ORA_DISK_1: piece handle=/BACKUP/rman/ORCL_19_2 tag=TAG20151012T194526
channel ORA_DISK_1: restored backup piece 2
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 2015-10-12:20:04:45
datafile 5 switched to datafile copy
input datafile copy RECID=2 STAMP=892929885 file name=/home/oracle/example01.dbf
starting full resync of recovery catalog
full resync complete
Starting recover at 2015-10-12:20:04:47
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /ARC/ORCL_892921262_1_9.arc
archived log for thread 1 with sequence 10 is already on disk as file /ARC/ORCL_892921262_1_10.arc
archived log for thread 1 with sequence 11 is already on disk as file /ARC/ORCL_892921262_1_11.arc
archived log file name=/ARC/ORCL_892921262_1_9.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 2015-10-12:20:04:47
sql statement: alter tablespace example online
conn hr/hr
Connected.
HR@orcl>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
8 rows selected.
SYS@orcl>alter tablespace example offline;
Tablespace altered.
SYS@orcl>!cp /home/oracle/example01.dbf $ORACLE_BASE/oradata/orcl/example01.dbf
SYS@orcl>alter tablespace example
2 rename datafile '/home/oracle/example01.dbf'
3 to '/u01/app/oracle/oradata/orcl/example01.dbf';
Tablespace altered.
SYS@orcl>alter tablespace example online;
Tablespace altered.
'스터디북' 카테고리의 다른 글
| [10/13] 정리 (0) | 2015.10.13 |
|---|---|
| [10/12] RMAN 복구 실습 - 4 (0) | 2015.10.12 |
| [10/12] 실습 - 2 (0) | 2015.10.12 |
| [10/12] 정리 그리고 실습 - 1 (0) | 2015.10.12 |
| [10/11] 정리 (0) | 2015.10.11 |