recover system datafile lost
Recover from a loss of the SYSTEM tablespace datafile
Error on alert log
ORA-01110: data file 1: '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'
ORA-01565: error in identifying file '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1' ------<<<<<<<
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
We should have valid backup to recover system datafile ...
export NLS_DATE_FORMAT='dd-mm-yyyy hh:mi:ss'
rman target / | tee rman.log
run {
allocate channel ch1 device type DISK;
allocate channel ch2 device type DISK;
backup incremental level=0 tag "RMANFULL" format '/u01/app/oracle/RmanBackup/%d_T%T_db_s%s_p%p_t%t' database filesperset 4 plus archivelog tag "RMANFULL";
sql "alter system archive log current";
backup format '/u01/app/oracle/RmanBackup/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";
backup format '/u01/app/oracle/RmanBackup/%d_T%T_cf%s_p%p_t%t' current controlfile tag "RMANFULL";
sql "alter system archive log current";
restore database preview;
}
Simulate the failure - by moving the system datafile:
[oracle@wcp12cr2 Datafile]$ mv data_D-ORCL_TS-SYSTEM_FNO-1 data_D-ORCL_TS-SYSTEM_FNO-1-ORIG
SQL> shutdown immediate ;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
from alert log
2022-12-29T13:34:41.592711-08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_10344.trc:
ORA-01110: data file 1: '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'
ORA-01565: error in identifying file '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1' ------<<<<<<<
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 1 new persistent data failures
2022-12-29T13:35:25.549197-08:00
OS process OFSD (ospid 3088) idle for 30 seconds, exiting
--Check the status of datafiles
set pagesize 300 linesize 200
col NAME for a70
col recover for a12
col , error for a20
select file#,NAME,checkpoint_change#, status, recover, error from v$datafile_header;
SQL> SQL> SQL>
FILE# NAME CHECKPOINT_CHANGE# STATUS REC
---------- ---------------------------------------------------------------------- ------------------ ------- ---
1 0 ONLINE <<<<< lost file
2 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2 3361521 ONLINE NO
3 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3 3361521 ONLINE NO
4 /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4 3361521 ONLINE NO
5 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5 3361521 ONLINE NO
6 /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6 3361521 ONLINE NO
7 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7 3361521 ONLINE NO
8 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8 3361521 ONLINE NO
9 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9 3361521 ONLINE NO
10 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10 3361521 ONLINE NO
11 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11 3361521 ONLINE NO
12 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12 3361521 ONLINE NO
13 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13 3361521 ONLINE NO
14 /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14 3361521 ONLINE NO
14 rows selected.
Restore and Recover the system datafile using backups
RMAN> restore datafile 1;
Starting restore at 29-12-2022 01:42:42
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/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_T20221229_db_s19_p1_t1124716704
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221229_db_s19_p1_t1124716704 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 29-12-2022 01:43:08
SQL> SQL> /
FILE# NAME CHECKPOINT_CHANGE# STATUS REC
---------- ---------------------------------------------------------------------- ------------------ ------- ---
1 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1 0 ONLINE
2 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2 3361521 ONLINE NO
3 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3 3361521 ONLINE NO
4 /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4 3361521 ONLINE NO
5 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5 3361521 ONLINE NO
6 /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6 3361521 ONLINE NO
7 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7 3361521 ONLINE NO
8 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8 3361521 ONLINE NO
9 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9 3361521 ONLINE NO
10 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10 3361521 ONLINE NO
11 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11 3361521 ONLINE NO
12 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12 3361521 ONLINE NO
13 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13 3361521 ONLINE NO
14 /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14 3361521 ONLINE NO
14 rows selected.
RMAN> recover datafile 1;
Starting recover at 29-12-2022 01:44:29
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_3_ktw1221o_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_4_ktw124o0_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_5_ktw124sc_.arc
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_6_ktw12fc8_.arc
archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_3_ktw1221o_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_4_ktw124o0_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 29-12-2022 01:44:31
RMAN>
FILE# NAME CHECKPOINT_CHANGE# STATUS REC
---------- ---------------------------------------------------------------------- ------------------ ------- ---
1 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1 3382895 ONLINE NO <<<<<<<----- 10="" 11="" 12="" 13="" 14="" 2="" 3361521="" 3="" 4="" 5="" 6="" 7="" 8="" 9="" app="" atafile="" data_d-orcl_ts-dbfs_ts_fno-14="" data_d-orcl_ts-sysaux_fno-3="" data_d-orcl_ts-undotbs1_fno-4="" data_d-orcl_ts-users_fno-6="" data_d-orcl_ts-wcpvm_ias_activity_fno-13="" data_d-orcl_ts-wcpvm_ias_discuss_fno-8="" data_d-orcl_ts-wcpvm_ias_opss_fno-2="" data_d-orcl_ts-wcpvm_ias_portlet_fno-5="" data_d-orcl_ts-wcpvm_ias_webcenter_fno-7="" data_d-orcl_ts-wcpvm_iau_fno-10="" data_d-orcl_ts-wcpvm_mds_fno-11="" data_d-orcl_ts-wcpvm_ocs_fno-12="" data_d-orcl_ts-wcpvm_stb_fno-9="" no="" online="" oracle="" rman="" rows="" selected.="" u01=""> alter database open ;
Statement processed
RMAN>
====================
now all the scn no are same
SQL> r
1* select file#,NAME,checkpoint_change#, status, recover from v$datafile_header
FILE# NAME CHECKPOINT_CHANGE# STATUS RECOVER
---------- ---------------------------------------------------------------------- ------------------ ------- ------------
1 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1 3402898 ONLINE NO
2 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2 3402898 ONLINE NO
3 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3 3402898 ONLINE NO
4 /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4 3402898 ONLINE NO
5 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5 3402898 ONLINE NO
6 /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6 3402898 ONLINE NO
7 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7 3402898 ONLINE NO
8 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8 3402898 ONLINE NO
9 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9 3402898 ONLINE NO
10 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10 3402898 ONLINE NO
11 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11 3402898 ONLINE NO
12 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12 3402898 ONLINE NO
13 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13 3402898 ONLINE NO
14 /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14 3402898 ONLINE NO
14 rows selected.
set pages 200 lines 200
col FILE_NAME for a70
select FILE_ID,TABLESPACE_NAME,FILE_NAME,STATUS from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME STATUS
---------- ------------------------------ ---------------------------------------------------------------------- ---------
1 SYSTEM /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1 AVAILABLE
3 SYSAUX /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3 AVAILABLE
4 UNDOTBS1 /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4 AVAILABLE
6 USERS /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6 AVAILABLE
2 WCPVM_IAS_OPSS /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2 AVAILABLE
5 WCPVM_IAS_PORTLET /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5 AVAILABLE
7 WCPVM_IAS_WEBCENTER /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7 AVAILABLE
8 WCPVM_IAS_DISCUSS /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8 AVAILABLE
9 WCPVM_STB /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9 AVAILABLE
10 WCPVM_IAU /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10 AVAILABLE
11 WCPVM_MDS /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11 AVAILABLE
12 WCPVM_OCS /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12 AVAILABLE
13 WCPVM_IAS_ACTIVITY /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13 AVAILABLE
14 DBFS_TS /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14 AVAILABLE
14 rows selected.
=====
SET LINES 200 pagesize 300
COL name FORM a80
COL status FORM A12
COL file# FORM 9999
COL control_file_SCN FORM 999999999999999
COL datafile_SCN FORM 999999999999999
--
SELECT
a.name
,a.status
,a.file#
,a.checkpoint_change# control_file_SCN
,b.checkpoint_change# datafile_SCN
,CASE
WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'
WHEN ((b.checkpoint_change#) = 0) THEN 'File Missing?'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Rec. Req.'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'
ELSE 'what the ?'
END datafile_status
FROM v$datafile a -- control file SCN for datafile
,v$datafile_header b -- datafile header SCN
WHERE a.file# = b.file#
ORDER BY a.file#;
NAME STATUS FILE# CONTROL_FILE_SCN DATAFILE_SCN DATAFILE_STATUS
-------------------------------------------------------------------------------- ------------ ----- ---------------- ---------------- ----------------
/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1 SYSTEM 1 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2 ONLINE 2 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3 ONLINE 3 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4 ONLINE 4 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5 ONLINE 5 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6 ONLINE 6 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7 ONLINE 7 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8 ONLINE 8 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9 ONLINE 9 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10 ONLINE 10 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11 ONLINE 11 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12 ONLINE 12 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13 ONLINE 13 3405085 3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14 ONLINE 14 3405085 3405085 Startup Normal
14 rows selected.
Friday, 30 December 2022
Oracle Recover from a loss of the SYSTEM tablespace datafile
Subscribe to:
Post Comments (Atom)
1 comment:
https://anuj-singh.blogspot.com/2017/01/recover-tablespace.html
Post a Comment