oracle 19c数据库联机备份与恢复

文章oracle 19c数据库联机备份与恢复分享给大家，欢迎收藏Java资料网，专注分享技术知识

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               MOUNTED

SQL> 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@pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 10:39:42 2024
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

SQL> 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 it


SQL> 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
----------
     2

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               0            3

SQL> 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        3

SQL> 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.dbf
   544 -rw-r-----. 1 oracle oinstall  37756928 Jan 17 14:03 temp01.dbf
107528 -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 -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            3
    10 NOT ACTIVE               0            3
    11 NOT ACTIVE               0            3
    12 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.dbf
   544 -rw-r-----. 1 oracle oinstall  37756928 Jan 17 14:03 temp01.dbf
107528 -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 privileges


SQL> 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.dbf
   544 -rw-r-----. 1 oracle oinstall  37756928 Jan 17 14:03 temp01.dbf
107528 -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@pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 10:54:31 2024
Version 19.3.0.0.0

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

Last Successful login time: Wed Mar 06 2024 10:39:42 +08:00

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

SQL> show user
USER is "JIE"
SQL> select * from test5;

    ID
----------
     2

文章来源:https://blog.csdn.net/m0_70247753/article/details/136483656
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！