行业知识
oracle 19c数据库联机备份与恢复
2026-11-15 04:45:43
来源：互联网转载
1.在CDB$ROOT中将数据库修改为归档模式
（）SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 MOUNTEDSQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/app/oracle/product/19.2.0/db_home1/dbs/archOldest online log sequence 7Next log sequence to archive 9Current 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 2024Version 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 - ProductionVersion 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 3 10 NOT ACTIVE 0 3 11 NOT ACTIVE 0 3 12 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 3 10 NOT ACTIVE 0 3 11 NOT ACTIVE 0 3 12 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 -lstotal 748096358408 -rw-r-----. 1 oracle oinstall 367009792 Mar 6 10:43 sysaux01.dbf276488 -rw-r-----. 1 oracle oinstall 283123712 Mar 6 10:45 system01.dbf 544 -rw-r-----. 1 oracle oinstall 37756928 Jan 17 14:03 temp01.dbf107528 -rw-r-----. 1 oracle oinstall 110108672 Mar 6 10:45 undotbs01.dbf 5128 -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 -lstotal 51285128 -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 3 10 NOT ACTIVE 0 3 11 NOT ACTIVE 0 3 12 NOT ACTIVE 2387606 06-MAR-24 3
4.故意删除数据文件造成异常
[oracle@19c pdb1]$ ll -lstotal 748096358408 -rw-r-----. 1 oracle oinstall 367009792 Mar 6 10:46 sysaux01.dbf276488 -rw-r-----. 1 oracle oinstall 283123712 Mar 6 10:46 system01.dbf 544 -rw-r-----. 1 oracle oinstall 37756928 Jan 17 14:03 temp01.dbf107528 -rw-r-----. 1 oracle oinstall 110108672 Mar 6 10:46 undotbs01.dbf 5128 -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 sysdbaConnected.SQL> shutdown immediate;ORA-01116: error in opening database file 12ORA-01110: data file 12: '/u01/app/oracle/oradata/CDB/pdb1/users01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> 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 -lstotal 748096358408 -rw-r-----. 1 oracle oinstall 367009792 Mar 6 10:48 sysaux01.dbf276488 -rw-r-----. 1 oracle oinstall 283123712 Mar 6 10:48 system01.dbf 544 -rw-r-----. 1 oracle oinstall 37756928 Jan 17 14:03 temp01.dbf107528 -rw-r-----. 1 oracle oinstall 110108672 Mar 6 10:48 undotbs01.dbf 5128 -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 2024Version 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 - ProductionVersion 19.3.0.0.0SQL> show userUSER is "JIE"SQL> select * from test5; ID---------- 2
