Re: 오늘의 마지막 문제1. @datafile 에서 data file의 위치를 확인하고 그리고 shutdown....

[21기_이풍혁]

1.데이터 파일 복사

alter tablespace USERS begin backup;
alter tablespace TS01 begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace TS02 begin backup;
alter tablespace SYSTEM begin backup;

cd /u01/app/oracle/oradata/ORCL19/

cp *.dbf ~/onelinebackup/

alter tablespace USERS end backup;
alter tablespace TS01 end backup;
alter tablespace UNDOTBS1 end backup;
alter tablespace SYSAUX end backup;
alter tablespace TS02 end backup;
alter tablespace SYSTEM end backup;

[oracle@localhost onelinebackup]$ cd /u01/app/oracle/oradata/ORCL19/
[oracle@localhost ORCL19]$ ll *.dbf
-rw-r-----. 1 oracle oinstall 576724992 Sep 23 16:42 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 943726592 Sep 23 16:42 system01.dbf
-rw-r-----. 1 oracle oinstall 134225920 Sep 23 16:35 temp01.dbf
-rw-r-----. 1 oracle oinstall  52436992 Sep 23 16:42 ts01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Sep 23 16:42 ts02.dbf
-rw-r-----. 1 oracle oinstall 356524032 Sep 23 16:42 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Sep 23 16:42 users01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Sep 23 16:42 users02.dbf
[oracle@localhost ORCL19]$ cp *.dbf ~/onelinebackup/

2. 비정상 종료 및 데이터파일 삭제


[oracle@localhost ~]$ sqlplus / as sysdba


SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 23 16:45:15 2025
Version 19.3.0.0.0


Copyright (c) 1982, 2019, Oracle.  All rights reserved.



Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


SYS@orcl19> shutdown abort;
ORACLE instance shut down.
SYS@orcl19> exit;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
sd[oracle@localhost ~]$ cd /u01/app/oracle/oradata/ORCL19/
[oracle@localhost ORCL19]$ ll *.dbf
-rw-r-----. 1 oracle oinstall 576724992 Sep 23 16:44 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 943726592 Sep 23 16:44 system01.dbf
-rw-r-----. 1 oracle oinstall 134225920 Sep 23 16:35 temp01.dbf
-rw-r-----. 1 oracle oinstall  52436992 Sep 23 16:44 ts01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Sep 23 16:44 ts02.dbf
-rw-r-----. 1 oracle oinstall 356524032 Sep 23 16:44 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Sep 23 16:44 users01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Sep 23 16:44 users02.dbf
[oracle@localhost ORCL19]$ rm *.dbf
[oracle@localhost ORCL19]$ sqlplus / as sysdba


SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 23 16:45:50 2025
Version 19.3.0.0.0


Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to an idle instance.


SYS@orcl19> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.


Total System Global Area 2415917880 bytes
Fixed Size                  8899384 bytes
Variable Size             520093696 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL19/system01.dbf'



SYS@orcl19> exit;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

3. 복구
[oracle@localhost ORCL19]$ cd ~/onelinebackup/
[oracle@localhost onelinebackup]$ ll *.dbf
-rw-r-----. 1 oracle oinstall 576724992 Sep 23 16:43 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 943726592 Sep 23 16:43 system01.dbf
-rw-r-----. 1 oracle oinstall 134225920 Sep 23 16:43 temp01.dbf
-rw-r-----. 1 oracle oinstall  52436992 Sep 23 16:43 ts01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Sep 23 16:43 ts02.dbf
-rw-r-----. 1 oracle oinstall 356524032 Sep 23 16:43 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Sep 23 16:43 users01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Sep 23 16:43 users02.dbf
[oracle@localhost onelinebackup]$ cp *.dbf /u01/app/oracle/oradata/ORCL19/
[oracle@localhost onelinebackup]$ sqlplus / as sysdba


SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 23 16:46:35 2025
Version 19.3.0.0.0


Copyright (c) 1982, 2019, Oracle.  All rights reserved.



Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


SYS@orcl19> recover datafile 1;
Media recovery complete.
SYS@orcl19> recover datafile 2;
Media recovery complete.
SYS@orcl19> rocover datafile 3;
SP2-0734: unknown command beginning "rocover da..." - rest of line ignored.
SYS@orcl19> recover datafile 3;
Media recovery complete.
SYS@orcl19> recover datafile 4;
Media recovery complete.
SYS@orcl19> recover datafile 5;
Media recovery complete.
SYS@orcl19> recover datafile 6;
ORA-01179: file 6 does not exist


SYS@orcl19> recover datafile 7;
Media recovery complete.
SYS@orcl19> recover datafile 8;
Media recovery complete.



SYS@orcl19> alter database open;


Database altered.

4. 접속 및 확인
SYS@orcl19> select tablespace_name,file_name, STATUS from dba_data_files;


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
USERS
/u01/app/oracle/oradata/ORCL19/users02.dbf
AVAILABLE


USERS
/u01/app/oracle/oradata/ORCL19/users01.dbf
AVAILABLE


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------


TS01
/u01/app/oracle/oradata/ORCL19/ts01.dbf
AVAILABLE


UNDOTBS1
/u01/app/oracle/oradata/ORCL19/undotbs01.dbf


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
AVAILABLE


SYSAUX
/u01/app/oracle/oradata/ORCL19/sysaux01.dbf
AVAILABLE


TS02


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
/u01/app/oracle/oradata/ORCL19/ts02.dbf
AVAILABLE


SYSTEM
/u01/app/oracle/oradata/ORCL19/system01.dbf
AVAILABLE


7 rows selected.