Oracle cold backup ---- Delete non-system tablespace

1. Backup File

Define fil =/home/Oracle/cdbk. SQL
Define log =/home/oracle/cdbk. log
Spool & fil
Prompt connet sys/sysdba @ asmdemo as sysdba;
Prompt shutdown immediate;
Select 'host cp' | file_name | '/u01/asmdemo' | substr (file_name, instr (file_name, '/',-1, 1) from dba_data_fi
Les;
Prompt startup;
Spool off
Spool & log
@ & Fil;
Spool off

2. Create a user

Create user test identified by test default tablespace ts_test;

Create table t1 ase select * from dba_objects where rownum = 1

3. Delete non-system tablespace

Select file_name, file_id from dba_data_files;

/U01/app/oracle/oradata/demo/users01.dbf 1

/U01/app/oracle/oradata/demo/sysaux01.dbf 2
/U01/app/oracle/oradata/demo/undotbs01.dbf 3

/U01/app/oracle/oradata/demo/system01.dbf 4
/U01/app/oracle/oradata/demo/ts_test01.dbf 5

Rm-f/u01/app/oracle/oradata/demo/ts_test01.dbf

4. An error is returned when querying table t1.

Select * from t1

5. Restore data files online

Alter tablespace ts_test online

6. Restore data files

Recover datafile 5