ArchiveMode 에서의 Backup & Recovery-RMAN02
recovery advisor : non system 테이블스페이스 장애후 advisor 이용한 복구
SYS@orcl2> CREATE TABLESPACE insa
DATAFILE '/u01/app/oracle/orcl2/insa01.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1141972
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1141972
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1141972
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1141972
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1141972
6 INSA /u01/app/oracle/orcl2/insa01.dbf ONLINE 1142094
6 rows selected.
SYS@orcl2> 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
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 19 16:43:53 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=986068470)
RMAN> report schema;
using target database control file instead of recovery catalog
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 510 SYSAUX *** /u01/app/oracle/orcl2/sysaux01.dbf
3 105 UNDOTBS1 *** /u01/app/oracle/orcl2/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/orcl2/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/orcl2/example01.dbf
6 10 INSA *** /u01/app/oracle/orcl2/insa01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2 20 TEMP 32767 /u01/app/oracle/orcl2/temp01.dbf
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
6 0 /u01/app/oracle/orcl2/insa01.dbf
RMAN> backup tablespace insa format '/home/oracle/backup/rman/%U_%T';
Starting backup at 19-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 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=/u01/app/oracle/orcl2/insa01.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-19
channel ORA_DISK_1: finished piece 1 at 19-APR-19
piece handle=/home/oracle/backup/rman/04tvd5kv_1_1_20190419 tag=TAG20190419T165559 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-19
Starting Control File and SPFILE Autobackup at 19-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190419-00 comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-19
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.04G DISK 00:00:27 19-APR-19
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20190419T165128
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_19/o1_mf_nnndf_TAG20190419T165128_gclzj13n_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1142992 19-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1142992 19-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1142992 19-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1142992 19-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1142992 19-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.67M DISK 00:00:01 19-APR-19
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190419T165128
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_19/o1_mf_ncsnf_TAG20190419T165128_gclzk57z_.bkp
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1143003 Ckp time: 19-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.03M DISK 00:00:00 19-APR-19
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20190419T165559
Piece Name: /home/oracle/backup/rman/04tvd5kv_1_1_20190419
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 1143484 19-APR-19 /u01/app/oracle/orcl2/insa01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 9.67M DISK 00:00:00 19-APR-19
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20190419T165600
Piece Name: /home/oracle/backup/rman/c-986068470-20190419-00
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1143491 Ckp time: 19-APR-19
SYS@orcl2> create table hr.insa_table tablespace insa as select * from hr.employees;
Table created.
SYS@orcl2> select count(*) from hr.insa_table;
COUNT(*)
----------
107
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS FIRST_CHANGE# MB
---------- ---------- ---------------- --- ---------- ------------- ----------
1 1 CURRENT NO 1 1140797 50
2 0 UNUSED YES 1 0 50
3 0 UNUSED YES 1 0 50
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 = 'INSA_TABLE'
AND e.owner = 'HR';
FILE_NAME
------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/insa01.dbf
SYS@orcl2> ! rm /u01/app/oracle/orcl2/insa01.dbf
SYS@orcl2> ! ls /u01/app/oracle/orcl2/insa01.dbf
ls: /u01/app/oracle/orcl2/insa01.dbf: No such file or directory
SYS@orcl2> conn / as sysdba
Connected.
SYS@orcl2> alter system flush buffer_cache;
System altered.
SYS@orcl2> select * from hr.insa_table;
select * from hr.insa_table
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/orcl2/insa01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SYS@orcl2> 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
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 19 16:59:19 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=986068470)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 11-APR-19 One or more non-system datafiles are missing
8 HIGH OPEN 10-APR-19 One or more non-system datafiles need media recovery
242 HIGH OPEN 16-APR-19 One or more non-system datafiles are offline
RMAN> list failure 42 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 11-APR-19 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 42
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1445 HIGH OPEN 19-APR-19 Datafile 6: '/u01/app/oracle/orcl2/insa01.dbf' is missing
Impact: Some objects in tablespace INSA might be unavailable
265 HIGH OPEN 16-APR-19 Datafile 6: '/u01/app/oracle/oradata/data01.dbf' is missing
Impact: Some objects in tablespace DATA01 might be unavailable
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 11-APR-19 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 42
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1445 HIGH OPEN 19-APR-19 Datafile 6: '/u01/app/oracle/orcl2/insa01.dbf' is missing
Impact: Some objects in tablespace INSA might be unavailable
265 HIGH OPEN 16-APR-19 Datafile 6: '/u01/app/oracle/oradata/data01.dbf' is missing
Impact: Some objects in tablespace DATA01 might be unavailable
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/data01.dbf was unintentionally renamed or moved, restore it
2. If file /u01/app/oracle/orcl2/insa01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 6
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm/reco_522096007.hm
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm/reco_522096007.hm
contents of repair script:
# restore and recover datafile
sql 'alter database datafile 6 offline';
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm/reco_522096007.hm
contents of repair script:
# restore and recover datafile
sql 'alter database datafile 6 offline';
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
sql statement: alter database datafile 6 offline
Starting restore at 19-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 00006 to /u01/app/oracle/orcl2/insa01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/rman/04tvd5kv_1_1_20190419
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/04tvd5kv_1_1_20190419 tag=TAG20190419T165559
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 19-APR-19
Starting recover at 19-APR-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-APR-19
sql statement: alter database datafile 6 online
repair failure complete
RMAN> exit
Recovery Manager complete.
[orcl2:~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 19 17:01:44 2019
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@orcl2> select count(*) from hr.insa_table;
COUNT(*)
----------
107
SYS@orcl2> drop tablespace insa including contents and datafiles;
Tablespace dropped.
-- offline recovery : system
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 19 17:02:55 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=986068470)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL2 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/rman/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/backup/rman/%U_%T';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl2.f'; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/rman/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
new RMAN configuration parameters are successfully stored
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL2 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/backup/rman/%U_%T';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl2.f'; # default
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.04G DISK 00:00:27 19-APR-19
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20190419T165128
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_19/o1_mf_nnndf_TAG20190419T165128_gclzj13n_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1142992 19-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1142992 19-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1142992 19-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1142992 19-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1142992 19-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.67M DISK 00:00:01 19-APR-19
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190419T165128
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_19/o1_mf_ncsnf_TAG20190419T165128_gclzk57z_.bkp
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1143003 Ckp time: 19-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.03M DISK 00:00:00 19-APR-19
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20190419T165559
Piece Name: /home/oracle/backup/rman/04tvd5kv_1_1_20190419
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 1143484 19-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 9.67M DISK 00:00:00 19-APR-19
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20190419T165600
Piece Name: /home/oracle/backup/rman/c-986068470-20190419-00
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1143491 Ckp time: 19-APR-19
RMAN> delete backupset;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_19/o1_mf_nnndf_TAG20190419T165128_gclzj13n_.bkp
2 2 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_19/o1_mf_ncsnf_TAG20190419T165128_gclzk57z_.bkp
3 3 1 1 AVAILABLE DISK /home/oracle/backup/rman/04tvd5kv_1_1_20190419
4 4 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190419-00
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_19/o1_mf_nnndf_TAG20190419T165128_gclzj13n_.bkp RECID=1 STAMP=1006015889
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_19/o1_mf_ncsnf_TAG20190419T165128_gclzk57z_.bkp RECID=2 STAMP=1006015925
deleted backup piece
backup piece handle=/home/oracle/backup/rman/04tvd5kv_1_1_20190419 RECID=3 STAMP=1006016159
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190419-00 RECID=4 STAMP=1006016160
Deleted 4 objects
RMAN> list backup;
specification does not match any backup in the repository
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /u01/app/oracle/orcl2/system01.dbf
2 0 /u01/app/oracle/orcl2/sysaux01.dbf
3 0 /u01/app/oracle/orcl2/undotbs01.dbf
4 0 /u01/app/oracle/orcl2/users01.dbf
5 0 /u01/app/oracle/orcl2/example01.dbf
RMAN> backup database;
Starting backup at 19-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=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 19-APR-19
channel ORA_DISK_1: finished piece 1 at 19-APR-19
piece handle=/home/oracle/backup/rman/06tvd64l_1_1_20190419 tag=TAG20190419T170421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 19-APR-19
Starting Control File and SPFILE Autobackup at 19-APR-19
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-01 comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-19
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.04G DISK 00:00:34 19-APR-19
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20190419T170421
Piece Name: /home/oracle/backup/rman/06tvd64l_1_1_20190419
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.67M DISK 00:00:00 19-APR-19
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190419T170456
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-01
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1144731 Ckp time: 19-APR-19
RMAN> exit
Recovery Manager complete.
[orcl2:~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 19 17:05:32 2019
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@orcl2> create table hr.loc_new tablespace users as select * from hr.locations;
Table created.
SYS@orcl2> select count(*) from hr.loc_new;
COUNT(*)
----------
23
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> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> ! rm /u01/app/oracle/orcl2/system01.dbf
SYS@orcl2> ! ls /u01/app/oracle/orcl2/system01.dbf
ls: /u01/app/oracle/orcl2/system01.dbf: No such file or directory
SYS@orcl2> @switch
System altered.
SYS@orcl2> select name from v$database;
NAME
---------
ORCL2
SYS@orcl2> conn hr/hr
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/orcl2/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE.
@> conn / as sysdba
Connected.
SYS@orcl2> shutdown abort
ORACLE instance shut down.
SYS@orcl2> 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
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 19 17:08:15 2019
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 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1569 CRITICAL OPEN 19-APR-19 System datafile 1: '/u01/app/oracle/orcl2/system01.dbf' is missing
242 HIGH OPEN 16-APR-19 One or more non-system datafiles are offline
RMAN> list failure 1569 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1569 CRITICAL OPEN 19-APR-19 System datafile 1: '/u01/app/oracle/orcl2/system01.dbf' is missing
Impact: Database cannot be opened
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.04G DISK 00:00:34 19-APR-19
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20190419T170421
Piece Name: /home/oracle/backup/rman/06tvd64l_1_1_20190419
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.67M DISK 00:00:00 19-APR-19
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190419T170456
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-01
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1144731 Ckp time: 19-APR-19
RMAN> restore tablespace system;
Starting restore at 19-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 00001 to /u01/app/oracle/orcl2/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/rman/06tvd64l_1_1_20190419
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/06tvd64l_1_1_20190419 tag=TAG20190419T170421
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 19-APR-19
RMAN> recover tablespace system;
Starting recover at 19-APR-19
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/arch1/arch_1_1_1006014226.arc
archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/arch1/arch_1_2_1006014226.arc
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/arch1/arch_1_3_1006014226.arc
archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/arch1/arch_1_4_1006014226.arc
archived log file name=/home/oracle/arch1/arch_1_1_1006014226.arc thread=1 sequence=1
archived log file name=/home/oracle/arch1/arch_1_2_1006014226.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-APR-19
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
[orcl2:~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 19 17:10:26 2019
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@orcl2> select status from v$instance;
STATUS
------------
OPEN
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 YES INACTIVE
3 6 /u01/app/oracle/orcl2/redo03.log 50 NO CURRENT
SYS@orcl2> select count(*) from hr.loc_new;
COUNT(*)
----------
23
-- online recovery : users
1. RMAN SESSION
RMAN> report schema;
using target database control file instead of recovery catalog
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 510 SYSAUX *** /u01/app/oracle/orcl2/sysaux01.dbf
3 105 UNDOTBS1 *** /u01/app/oracle/orcl2/undotbs01.dbf
4 5 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> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.04G DISK 00:00:34 19-APR-19
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20190419T170421
Piece Name: /home/oracle/backup/rman/06tvd64l_1_1_20190419
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.67M DISK 00:00:00 19-APR-19
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190419T170456
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-01
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1144731 Ckp time: 19-APR-19
2. DBA SESSION
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 YES INACTIVE
3 6 /u01/app/oracle/orcl2/redo03.log 50 NO CURRENT
SYS@orcl2> create table hr.rman_tab as select * from hr.employees;
Table created.
SYS@orcl2> select count(*) from hr.rman_tab;
COUNT(*)
----------
107
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 YES INACTIVE
3 6 /u01/app/oracle/orcl2/redo03.log 50 NO CURRENT
SYS@orcl2> @switch
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 YES INACTIVE
3 9 /u01/app/oracle/orcl2/redo03.log 50 NO CURRENT
SYS@orcl2> select sequence#, name from v$archived_log;
SEQUENCE# NAME
---------- --------------------------------------------------
9 /home/oracle/arch1/arch_1_9_1005837298.arc
9 /home/oracle/arch2/arch_1_9_1005837298.arc
10 /home/oracle/arch1/arch_1_10_1005837298.arc
10 /home/oracle/arch2/arch_1_10_1005837298.arc
11 /home/oracle/arch1/arch_1_11_1005837298.arc
11 /home/oracle/arch2/arch_1_11_1005837298.arc
13 /home/oracle/arch1/arch_1_13_1005837298.arc
13 /home/oracle/arch2/arch_1_13_1005837298.arc
14 /home/oracle/arch1/arch_1_14_1005837298.arc
14 /home/oracle/arch2/arch_1_14_1005837298.arc
15 /home/oracle/arch1/arch_1_15_1005837298.arc
15 /home/oracle/arch2/arch_1_15_1005837298.arc
1 /home/oracle/arch1/arch_1_1_1006014226.arc
1 /home/oracle/arch2/arch_1_1_1006014226.arc
2 /home/oracle/arch1/arch_1_2_1006014226.arc
2 /home/oracle/arch2/arch_1_2_1006014226.arc
3 /home/oracle/arch1/arch_1_3_1006014226.arc
3 /home/oracle/arch2/arch_1_3_1006014226.arc
4 /home/oracle/arch1/arch_1_4_1006014226.arc
4 /home/oracle/arch2/arch_1_4_1006014226.arc
5 /home/oracle/arch1/arch_1_5_1006014226.arc
5 /home/oracle/arch2/arch_1_5_1006014226.arc
6 /home/oracle/arch1/arch_1_6_1006014226.arc
6 /home/oracle/arch2/arch_1_6_1006014226.arc
7 /home/oracle/arch1/arch_1_7_1006014226.arc
7 /home/oracle/arch2/arch_1_7_1006014226.arc
8 /home/oracle/arch1/arch_1_8_1006014226.arc
8 /home/oracle/arch2/arch_1_8_1006014226.arc
28 rows selected.
SYS@orcl2> ! ls -lh /home/oracle/arch1
total 5.3M
-rw-r----- 1 oracle dba 5.1M Apr 19 17:06 arch_1_1_1006014226.arc
-rw-r----- 1 oracle dba 1.0K Apr 19 17:06 arch_1_2_1006014226.arc
-rw-r----- 1 oracle dba 3.0K Apr 19 17:06 arch_1_3_1006014226.arc
-rw-r----- 1 oracle dba 1.0K Apr 19 17:07 arch_1_4_1006014226.arc
-rw-r----- 1 oracle dba 5.5K Apr 19 17:09 arch_1_5_1006014226.arc
-rw-r----- 1 oracle dba 172K Apr 19 17:14 arch_1_6_1006014226.arc
-rw-r----- 1 oracle dba 1.0K Apr 19 17:14 arch_1_7_1006014226.arc
-rw-r----- 1 oracle dba 3.5K Apr 19 17:14 arch_1_8_1006014226.arc
3. RMAN SESSION
RMAN> list archivelog all;
.....
RMAN> crosscheck archivelog all;
....
RMAN> list expired archivelog all;
.....
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
....
Do you really want to delete the above objects (enter YES or NO)? yes
RMAN> list expired archivelog all;
specification does not match any archived log in the repository
RMAN> backup as compressed backupset archivelog all delete input tag='archivelog backup' ;
Starting backup at 19-APR-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=14 STAMP=1006016812
input archived log thread=1 sequence=2 RECID=15 STAMP=1006016813
input archived log thread=1 sequence=3 RECID=17 STAMP=1006016815
input archived log thread=1 sequence=4 RECID=19 STAMP=1006016847
input archived log thread=1 sequence=5 RECID=22 STAMP=1006016994
input archived log thread=1 sequence=6 RECID=24 STAMP=1006017255
input archived log thread=1 sequence=7 RECID=25 STAMP=1006017258
input archived log thread=1 sequence=8 RECID=27 STAMP=1006017261
input archived log thread=1 sequence=9 RECID=29 STAMP=1006017431
channel ORA_DISK_1: starting piece 1 at 19-APR-19
channel ORA_DISK_1: finished piece 1 at 19-APR-19
piece handle=/home/oracle/backup/rman/08tvd6so_1_1_20190419 tag=ARCHIVELOG BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/arch2/arch_1_1_1006014226.arc RECID=14 STAMP=1006016812
archived log file name=/home/oracle/arch1/arch_1_2_1006014226.arc RECID=15 STAMP=1006016813
archived log file name=/home/oracle/arch1/arch_1_3_1006014226.arc RECID=17 STAMP=1006016815
archived log file name=/home/oracle/arch1/arch_1_4_1006014226.arc RECID=19 STAMP=1006016847
archived log file name=/home/oracle/arch2/arch_1_5_1006014226.arc RECID=22 STAMP=1006016994
archived log file name=/home/oracle/arch2/arch_1_6_1006014226.arc RECID=24 STAMP=1006017255
archived log file name=/home/oracle/arch1/arch_1_7_1006014226.arc RECID=25 STAMP=1006017258
archived log file name=/home/oracle/arch1/arch_1_8_1006014226.arc RECID=27 STAMP=1006017261
archived log file name=/home/oracle/arch1/arch_1_9_1006014226.arc RECID=29 STAMP=1006017431
Finished backup at 19-APR-19
Starting Control File and SPFILE Autobackup at 19-APR-19
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-02 comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-19
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 2.16M DISK 00:00:00 19-APR-19
BP Key: 7 Status: AVAILABLE Compressed: YES Tag: ARCHIVELOG BACKUP
Piece Name: /home/oracle/backup/rman/08tvd6so_1_1_20190419
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 1140797 19-APR-19 1144860 19-APR-19
1 2 1144860 19-APR-19 1144863 19-APR-19
1 3 1144863 19-APR-19 1144867 19-APR-19
1 4 1144867 19-APR-19 1144882 19-APR-19
1 5 1144882 19-APR-19 1164895 19-APR-19
1 6 1164895 19-APR-19 1165377 19-APR-19
1 7 1165377 19-APR-19 1165380 19-APR-19
1 8 1165380 19-APR-19 1165385 19-APR-19
1 9 1165385 19-APR-19 1165519 19-APR-19
RMAN> exit
Recovery Manager complete.
[orcl2:~]$ ls -lh /home/oracle/arch1
total 5.3M
-rw-r----- 1 oracle dba 5.1M Apr 19 17:06 arch_1_1_1006014226.arc
-rw-r----- 1 oracle dba 5.5K Apr 19 17:09 arch_1_5_1006014226.arc
-rw-r----- 1 oracle dba 172K Apr 19 17:14 arch_1_6_1006014226.arc
4. DBA SESSION
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 = 'RMAN_TAB';
FILE_NAME
-----------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/users01.dbf
SYS@orcl2> ! rm /u01/app/oracle/orcl2/users01.dbf
SYS@orcl2> ! ls /u01/app/oracle/orcl2/users01.dbf
ls: /u01/app/oracle/orcl2/users01.dbf: No such file or directory
SYS@orcl2> create table hr.rman_dept as select * from hr.departments;
Table created.
SYS@orcl2> ! ls /u01/app/oracle/orcl2/users01.dbf
ls: /u01/app/oracle/orcl2/users01.dbf: No such file or directory
SYS@orcl2> select count(*) from hr.rman_dept;
COUNT(*)
----------
27
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1165385
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1165385
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1165385
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1165385
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1165385
SYS@orcl2> @check
System altered.
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1165753
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1165753
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1165753
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1165753
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1165753
5. RMAN SESSION
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 19 17:20:03 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=986068470)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 19-APR-19 One or more non-system datafiles are missing
242 HIGH OPEN 16-APR-19 One or more non-system datafiles are offline
RMAN> validate database;
Starting validate at 19-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
RMAN-06169: could not read file header for datafile 4 error reason 5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 04/19/2019 17:20:13
RMAN-06056: could not access datafile 4
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 19-APR-19 One or more non-system datafiles are missing
242 HIGH OPEN 16-APR-19 One or more non-system datafiles are offline
RMAN> list failure 42 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 19-APR-19 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 42
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1632 HIGH OPEN 19-APR-19 Datafile 4: '/u01/app/oracle/orcl2/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
RMAN> sql 'alter tablespace users offline immediate';
sql statement: alter tablespace users offline immediate
RMAN> restore tablespace users;
Starting restore at 19-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 /home/oracle/backup/rman/06tvd64l_1_1_20190419
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/06tvd64l_1_1_20190419 tag=TAG20190419T170421
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 19-APR-19
RMAN> recover tablespace users;
Starting recover at 19-APR-19
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/arch1/arch_1_1_1006014226.arc
archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/arch2/arch_1_2_1006014226.arc
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/arch2/arch_1_3_1006014226.arc
archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/arch2/arch_1_4_1006014226.arc
archived log for thread 1 with sequence 5 is already on disk as file /home/oracle/arch1/arch_1_5_1006014226.arc
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/arch1/arch_1_6_1006014226.arc
archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/arch2/arch_1_7_1006014226.arc
archived log for thread 1 with sequence 8 is already on disk as file /home/oracle/arch2/arch_1_8_1006014226.arc
archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/arch2/arch_1_9_1006014226.arc
archived log file name=/home/oracle/arch1/arch_1_1_1006014226.arc thread=1 sequence=1
archived log file name=/home/oracle/arch2/arch_1_2_1006014226.arc thread=1 sequence=2
archived log file name=/home/oracle/arch2/arch_1_3_1006014226.arc thread=1 sequence=3
archived log file name=/home/oracle/arch2/arch_1_4_1006014226.arc thread=1 sequence=4
archived log file name=/home/oracle/arch1/arch_1_5_1006014226.arc thread=1 sequence=5
archived log file name=/home/oracle/arch1/arch_1_6_1006014226.arc thread=1 sequence=6
archived log file name=/home/oracle/arch2/arch_1_7_1006014226.arc thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-APR-19
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online
6. DBA SESSION
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1165753
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1165753
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1165753
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1165913
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1165753
SYS@orcl2> select count(*) from hr.rman_tab;
COUNT(*)
----------
107
SYS@orcl2> select count(*) from hr.rman_dept;
COUNT(*)
----------
27
7. RMAN SESSION
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.04G DISK 00:00:34 19-APR-19
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20190419T170421
Piece Name: /home/oracle/backup/rman/06tvd64l_1_1_20190419
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.67M DISK 00:00:00 19-APR-19
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190419T170456
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-01
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1144731 Ckp time: 19-APR-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 2.16M DISK 00:00:00 19-APR-19
BP Key: 7 Status: AVAILABLE Compressed: YES Tag: ARCHIVELOG BACKUP
Piece Name: /home/oracle/backup/rman/08tvd6so_1_1_20190419
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 1140797 19-APR-19 1144860 19-APR-19
1 2 1144860 19-APR-19 1144863 19-APR-19
1 3 1144863 19-APR-19 1144867 19-APR-19
1 4 1144867 19-APR-19 1144882 19-APR-19
1 5 1144882 19-APR-19 1164895 19-APR-19
1 6 1164895 19-APR-19 1165377 19-APR-19
1 7 1165377 19-APR-19 1165380 19-APR-19
1 8 1165380 19-APR-19 1165385 19-APR-19
1 9 1165385 19-APR-19 1165519 19-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 9.67M DISK 00:00:00 19-APR-19
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20190419T171713
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-02
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1165530 Ckp time: 19-APR-19
8. NEW SESSION
[orcl2:rman]$ rm -f /home/oracle/backup/rman/06tvd64l_1_1_20190419
9. RMAN SESSION
RMAN> delete backupset 5;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
5 5 1 1 AVAILABLE DISK /home/oracle/backup/rman/06tvd64l_1_1_20190419
Do you really want to delete the above objects (enter YES or NO)? yes
RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece /home/oracle/backup/rman/06tvd64l_1_1_20190419
RMAN> crosscheck backupset;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/backup/rman/06tvd64l_1_1_20190419 RECID=5 STAMP=1006016661
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-01 RECID=6 STAMP=1006016696
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/backup/rman/08tvd6so_1_1_20190419 RECID=7 STAMP=1006017432
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-02 RECID=8 STAMP=1006017433
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-03 RECID=9 STAMP=1006018110
Crosschecked 5 objects
RMAN> list expired backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.04G DISK 00:00:34 19-APR-19
BP Key: 5 Status: EXPIRED Compressed: NO Tag: TAG20190419T170421
Piece Name: /home/oracle/backup/rman/06tvd64l_1_1_20190419
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1144706 19-APR-19 /u01/app/oracle/orcl2/example01.dbf
RMAN> delete expired backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
5 5 1 1 EXPIRED DISK /home/oracle/backup/rman/06tvd64l_1_1_20190419
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/backup/rman/06tvd64l_1_1_20190419 RECID=5 STAMP=1006016661
Deleted 1 EXPIRED objects
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.67M DISK 00:00:00 19-APR-19
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190419T170456
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-01
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1144731 Ckp time: 19-APR-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 2.16M DISK 00:00:00 19-APR-19
BP Key: 7 Status: AVAILABLE Compressed: YES Tag: ARCHIVELOG BACKUP
Piece Name: /home/oracle/backup/rman/08tvd6so_1_1_20190419
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 1140797 19-APR-19 1144860 19-APR-19
1 2 1144860 19-APR-19 1144863 19-APR-19
1 3 1144863 19-APR-19 1144867 19-APR-19
1 4 1144867 19-APR-19 1144882 19-APR-19
1 5 1144882 19-APR-19 1164895 19-APR-19
1 6 1164895 19-APR-19 1165377 19-APR-19
1 7 1165377 19-APR-19 1165380 19-APR-19
1 8 1165380 19-APR-19 1165385 19-APR-19
1 9 1165385 19-APR-19 1165519 19-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 9.67M DISK 00:00:00 19-APR-19
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20190419T171713
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-02
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1165530 Ckp time: 19-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 9.67M DISK 00:00:00 19-APR-19
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20190419T172830
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-03
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1166552 Ckp time: 19-APR-19
-- 데이타 파일을 다른 Disk로 복구 : set newname
<< 일반 테이블 스페이스 다른 disk로 복구 >>
1. DBA SESSION
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 10 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT
2 8 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 9 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
SYS@orcl2> CREATE TABLESPACE insa
DATAFILE '/u01/app/oracle/oradata/insa01.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1165753
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1165753
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1165753
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1165913
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1165753
6 INSA /u01/app/oracle/oradata/insa01.dbf ONLINE 1166703
6 rows selected.
SYS@orcl2> create table hr.all_obj tablespace insa as select * from all_objects;
Table created.
SYS@orcl2> select count(*) from hr.all_obj;
COUNT(*)
----------
71696
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 NO CURRENT
2 11 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 12 /u01/app/oracle/orcl2/redo03.log 50 YES ACTIVE
2. RMAN SESSION
RMAN> report need backup;
using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /u01/app/oracle/orcl2/system01.dbf
2 0 /u01/app/oracle/orcl2/sysaux01.dbf
3 0 /u01/app/oracle/orcl2/undotbs01.dbf
4 0 /u01/app/oracle/orcl2/users01.dbf
5 0 /u01/app/oracle/orcl2/example01.dbf
6 0 /u01/app/oracle/oradata/insa01.dbf
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 510 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
6 10 INSA *** /u01/app/oracle/oradata/insa01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2 20 TEMP 32767 /u01/app/oracle/orcl2/temp01.dbf
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL2 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/backup/rman/%U_%T';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl2.f'; # default
RMAN> backup database;
Starting backup at 19-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 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=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=00006 name=/u01/app/oracle/oradata/insa01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-19
channel ORA_DISK_1: finished piece 1 at 19-APR-19
piece handle=/home/oracle/backup/rman/0btvd84m_1_1_20190419 tag=TAG20190419T173830 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 19-APR-19
Starting Control File and SPFILE Autobackup at 19-APR-19
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190419-05 comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-19
RMAN> list backup of tablespace insa;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11 Full 1.05G DISK 00:00:18 19-APR-19
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20190419T173830
Piece Name: /home/oracle/backup/rman/0btvd84m_1_1_20190419
List of Datafiles in backup set 11
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 1167316 19-APR-19 /u01/app/oracle/oradata/insa01.dbf
3. DBA SESSION
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 NO CURRENT
2 11 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 12 /u01/app/oracle/orcl2/redo03.log 50 YES ACTIVE
SYS@orcl2> select count(*) from hr.all_obj;
COUNT(*)
----------
71696
SYS@orcl2> @switch
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 16 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT
2 14 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 15 /u01/app/oracle/orcl2/redo03.log 50 YES ACTIVE
SYS@orcl2> ! rm /u01/app/oracle/oradata/insa01.dbf
SYS@orcl2> ! ls /u01/app/oracle/oradata/insa01.dbf
ls: /u01/app/oracle/oradata/insa01.dbf: No such file or directory
4. RMAN SESSION
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
242 HIGH OPEN 16-APR-19 One or more non-system datafiles are offline
RMAN> validate database;
Starting validate at 19-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
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
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 04/19/2019 17:41:33
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/u01/app/oracle/oradata/insa01.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/insa01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 19-APR-19 One or more non-system datafiles are missing
242 HIGH OPEN 16-APR-19 One or more non-system datafiles are offline
RMAN> list failure 42 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 19-APR-19 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 42
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1772 HIGH OPEN 19-APR-19 Datafile 6: '/u01/app/oracle/oradata/insa01.dbf' is missing
Impact: Some objects in tablespace INSA might be unavailable
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 510 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
6 10 INSA *** /u01/app/oracle/oradata/insa01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2 20 TEMP 32767 /u01/app/oracle/orcl2/temp01.dbf
RMAN> run {
sql 'alter tablespace insa offline immediate';
set newname for datafile '/u01/app/oracle/oradata/insa01.dbf' to '/home/oracle/orcl2/insa01.dbf';
restore tablespace insa;
switch datafile 6;
recover tablespace insa;
sql 'alter tablespace insa online';
}
sql statement: alter tablespace insa offline immediate
executing command: SET NEWNAME
Starting restore at 19-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 00006 to /u01/app/oracle/orcl2/insa01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/rman/0btvd84m_1_1_20190419
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/0btvd84m_1_1_20190419 tag=TAG20190419T173830
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 19-APR-19
datafile 6 switched to datafile copy
input datafile copy RECID=2 STAMP=1006019001 file name=/u01/app/oracle/orcl2/insa01.dbf
Starting recover at 19-APR-19
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 13 is already on disk as file /home/oracle/arch1/arch_1_13_1006014226.arc
archived log for thread 1 with sequence 14 is already on disk as file /home/oracle/arch1/arch_1_14_1006014226.arc
archived log for thread 1 with sequence 15 is already on disk as file /home/oracle/arch1/arch_1_15_1006014226.arc
archived log file name=/home/oracle/arch1/arch_1_13_1006014226.arc thread=1 sequence=13
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-APR-19
sql statement: alter tablespace insa online
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 510 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
6 10 INSA *** /u01/app/oracle/orcl2/insa01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2 20 TEMP 32767 /u01/app/oracle/orcl2/temp01.dbf
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
242 HIGH OPEN 16-APR-19 One or more non-system datafiles are offline
5. DBA SESSION
SYS@orcl2> select count(*) from hr.all_obj;
COUNT(*)
----------
71696
SYS@orcl2> drop tablespace insa including contents and datafiles;
Tablespace dropped.
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1167560
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1167560
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1167560
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1167560
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1167560
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN03 (0) | 2019.04.22 |
|---|---|
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-TEMP,UNDO (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN01 (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-control file (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-logmnr (0) | 2019.04.22 |