1.在CDB$ROOT中将数据库修改为归档模式

SQL> show pdbs;CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED              READ ONLY  NO3 PDB1               READ WRITE NO4 PDB2               MOUNTEDSQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/app/oracle/product/19.2.0/db_home1/dbs/arch
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9

 

2.连接到pdb1创建一张测试表

[oracle@19c ~]$ sqlplus jie/123@pdb1SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 10:39:42 2024
Version 19.3.0.0.0Copyright (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.0SQL> create table test5 (id number);Table created.SQL> insert into test5 values(2);
insert into test5 values(2)*
ERROR at line 1:
ORA-01647: tablespace 'USERS' is read-only, cannot allocate space in itSQL> alter tablespace users read write;Tablespace altered.SQL> insert into test5 values(2);1 row created.SQL> commit;Commit complete.SQL> select * from test5;ID
----------2SQL> select * from v$backup;FILE# STATUS         CHANGE# TIME           CON_ID
---------- ------------------ ---------- --------- ----------9 NOT ACTIVE               0            310 NOT ACTIVE               0            311 NOT ACTIVE               0            312 NOT ACTIVE               0            3SQL> alter tablespace users begin backup;-------将表空间修改为备份状态Tablespace altered.SQL> select * from v$backup;FILE# STATUS         CHANGE# TIME           CON_ID
---------- ------------------ ---------- --------- ----------9 NOT ACTIVE               0            310 NOT ACTIVE               0            311 NOT ACTIVE               0            312 ACTIVE         2387606 06-MAR-24        3SQL> select name from v$datafile;NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/pdb1/system01.dbf
/u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb1/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdb1/users01.dbf

3.执行cp命令复制文件备份

[oracle@19c pdb1]$ ll -ls
total 748096
358408 -rw-r-----. 1 oracle oinstall 367009792 Mar  6 10:43 sysaux01.dbf
276488 -rw-r-----. 1 oracle oinstall 283123712 Mar  6 10:45 system01.dbf544 -rw-r-----. 1 oracle oinstall  37756928 Jan 17 14:03 temp01.dbf
107528 -rw-r-----. 1 oracle oinstall 110108672 Mar  6 10:45 undotbs01.dbf5128 -rw-r-----. 1 oracle oinstall   5251072 Mar  6 10:44 users01.dbf
[oracle@19c pdb1]$ cp users01.dbf /home/oracle/backup
[oracle@19c pdb1]$ cd /home/oracle/backup
[oracle@19c backup]$ ll -ls
total 5128
5128 -rw-r----- 1 oracle oinstall 5251072 Mar  6 10:46 users01.dbf

 

3.结束表空间的备份状态

SQL> alter tablespace users end backup;Tablespace altered.SQL> select * from v$backup;FILE# STATUS         CHANGE# TIME           CON_ID
---------- ------------------ ---------- --------- ----------9 NOT ACTIVE               0            310 NOT ACTIVE               0            311 NOT ACTIVE               0            312 NOT ACTIVE         2387606 06-MAR-24        3

4.故意删除数据文件造成异常

[oracle@19c pdb1]$ ll -ls
total 748096
358408 -rw-r-----. 1 oracle oinstall 367009792 Mar  6 10:46 sysaux01.dbf
276488 -rw-r-----. 1 oracle oinstall 283123712 Mar  6 10:46 system01.dbf544 -rw-r-----. 1 oracle oinstall  37756928 Jan 17 14:03 temp01.dbf
107528 -rw-r-----. 1 oracle oinstall 110108672 Mar  6 10:46 undotbs01.dbf5128 -rw-r-----. 1 oracle oinstall   5251072 Mar  6 10:47 users01.dbf
[oracle@19c pdb1]$ rm -rf users01.dbf
[oracle@19c pdb1]$ cd /home/oracle/backup

SQL> shutdown immediate;
ORA-01031: insufficient privilegesSQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/u01/app/oracle/oradata/CDB/pdb1/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.

5.执行cp命令恢复数据文件

[oracle@19c backup]$ pwd
/home/oracle/backup
[oracle@19c backup]$ cd /u01/app/oracle/oradata/CDB/pdb1/
[oracle@19c pdb1]$ ll -ls
total 748096
358408 -rw-r-----. 1 oracle oinstall 367009792 Mar  6 10:48 sysaux01.dbf
276488 -rw-r-----. 1 oracle oinstall 283123712 Mar  6 10:48 system01.dbf544 -rw-r-----. 1 oracle oinstall  37756928 Jan 17 14:03 temp01.dbf
107528 -rw-r-----. 1 oracle oinstall 110108672 Mar  6 10:48 undotbs01.dbf5128 -rw-r-----  1 oracle oinstall   5251072 Mar  6 10:51 users01.dbf

6.启动数据库查看table数据是否存在

[oracle@19c ~]$ sqlplus jie/123@pdb1SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 10:54:31 2024
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Last Successful login time: Wed Mar 06 2024 10:39:42 +08:00Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0SQL> show user
USER is "JIE"
SQL> select * from test5;ID
----------2