oracle 12c pdb 恢复

转载

lych528 2018-08-25 22:18:06 博主文章分类：Oracle 日常管理

文章标签 pdb 恢复 文章分类 Oracle 数据库

备份数据库

[oracle@12cr2 ~]$ rman target /

RMAN> backup database;

RMAN> list backup summary;

数据库open状态，系统表空间损坏 -----故障模拟

[oracle@12cr2 ~]$ sqlplus / as sysdba

SQL> alter session set container=PDB4;

SQL> select file_name from dba_data_files;

FILE_NAME

/u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf

/u01/app/oracle/oradata/zylong/pdb4/pdb4/sysaux01.dbf

/u01/app/oracle/oradata/zylong/pdb4/pdb4/undotbs01.dbf

[oracle@12cr2 ~]$ rm /u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf

[oracle@12cr2 ~]$ rm /u01/app/oracle/oradata/zylong/pdb4/pdb4/sysaux01.dbf

[oracle@12cr2 ~]$ sqlplus / as sysdba

ERROR:

ORA-09817: Write to audit file failed.

ORA-01075: you are currently logged on

故障现象

[root@12cr2 opt]# su - oracle

[oracle@12cr2 ~]$ sqlplus / as sysdba

SQL> alter session set container=PDB4;

SQL> select file_name from dba_data_files;

FILE_NAME

/u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf

/u01/app/oracle/oradata/zylong/pdb4/pdb4/sysaux01.dbf

/u01/app/oracle/oradata/zylong/pdb4/pdb4/undotbs01.dbf

SQL> create user test identified by oracle;

create user test identified by oracle

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01116: error in opening database file 38

ORA-01110: data file 38:

'/u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf'

ORA-27041: unable to open file

SQL> conn / as sysdba

SQL> alter pluggable database pdb4 close;

Pluggable database altered.

SQL> alter pluggable database pdb4 open;

alter pluggable database pdb4 open

ERROR at line 1:

ORA-01113: file 40 needs media recovery

ORA-01110: data file 40:

'/u01/app/oracle/oradata/zylong/pdb4/pdb4/undotbs01.dbf'

恢复

[oracle@12cr2 ~]$ rman target /

----可以在下一层指定PDB来进行相关操作，也可以在当前指定PDB进行连接：rman target sys/password@hostname:1521/pdb_name,此时恢复跟11G没多大区别，直接可以restore datafile n,recover datafile n,restore tablespace n,recover tablepace n等等操作。

RMAN> restore tablespace pdb4:system;

RMAN> restore tablespace pdb4:sysaux;

RMAN> recover tablespace pdb4:system;

RMAN> recover tablespace pdb4:sysaux;

RMAN> alter pluggable database pdb4 open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of sql statement command at 06/11/2017 21:00:12

ORA-01113: file 40 needs media recovery

ORA-01110: data file 40: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/undotbs01.dbf'

RMAN> recover tablespace pdb4:UNDOTBS1;

RMAN> alter pluggable database pdb4 open;

数据库关闭状态，普通表空间损坏模拟故障

SQL> alter pluggable database pdb4 close;

[oracle@12cr2 ~]$ rm /u01/app/oracle/oradata/zylong/pdb4/pdb4/users01.dbf

SQL> alter pluggable database pdb4 open;

alter pluggable database pdb4 open

ERROR at line 1:

ORA-01157: cannot identify/lock data file 41 - see DBWR trace file

ORA-01110: data file 41: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/users01.dbf'

恢复

[oracle@12cr2 ~]$ rman target /

RMAN> restore tablespace pdb4:users;

RMAN> alter pluggable database pdb4 open;

RMAN> recover tablespace pdb4:users;

RMAN> alter pluggable database pdb4 open;

数据库open状态，未备份的数据文件恢复

模拟故障

SQL> create tablespace users datafile '/u01/app/oracle/oradata/zylong/pdb4/pdb4/users01.dbf' size 20M;

SQL> create user test identified by oracle default tablespace users;

SQL> grant dba to test;

SQL> conn test/oracle@192.168.16.81:1521/pdb4

SQL> create table test as select * from all_objects where rownum<100;

SQL> commit;

SQL> !rm /u01/app/oracle/oradata/zylong/pdb4/pdb4/users01.dbf

故障后的现象

SQL> select count(*) from test;

SQL> insert into test select * from test;

SQL> commit;

SQL> insert into test select * from test;

SQL> commit;

SQL> alter system checkpoint;

ERROR at line 1:

ORA-03113: end-of-file on communication channel

SQL> insert into test select * from test;

ORA-03114: not connected to ORACLE

SQL> conn test/oracle@192.168.16.81:1521/pdb4

ORA-01033: ORACLE initialization or shutdown in progress

SQL> conn / as sysdba

SQL> show pdbs;

SQL> alter pluggable database PDB4 open;

ORA-01157: cannot identify/lock data file 41 - see DBWR trace file

ORA-01110: data file 41: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/users01.dbf'

恢复

[oracle@12cr2 ~]$ rman target /

RMAN> restore tablespace pdb4:users;

RMAN> recover tablespace pdb4:users;

RMAN> alter pluggable database pdb4 open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of sql statement command at 06/11/2017 21:35:30

ORA-01113: file 40 needs media recovery

ORA-01110: data file 40: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/undotbs01.dbf'

RMAN> recover tablespace pdb4:UNDOTBS1;

RMAN> alter pluggable database pdb4 open;

ORA-01113: file 39 needs media recovery

ORA-01110: data file 39: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/sysaux01.dbf'

RMAN> recover tablespace pdb4:sysaux;

RMAN> alter pluggable database pdb4 open;

ORA-01113: file 38 needs media recovery

ORA-01110: data file 38: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf'

RMAN> recover tablespace pdb4:system;

如果PDB4没有关闭，可以restore tablespace pdb4:users可能会报错，可以尝试执行以下命令在restore：

SQL > alter tablespace users offline immediate;

recover之后online

SQL > alter tablespace users online;

4 单个pdb 数据文件修复

[oracle@12c pdb01]$ rman target sys/password@hostname:1521/pdb_name RMAN> recover datafile 12; RMAN-06094: datafile 12 must be restored RMAN> alter database datafile 12 offline; RMAN> restore datafile 12; RMAN> recover datafile 12; RMAN> alter database datafile 12 online;

5 单个PDB数据库恢复

-----set until time "TO_DATE('2018-06-03 15:00:00','yyyy-mm-dd hh24:mi:ss')"; -----set until SCN = 180000; RMAN>restore pluggable database pdb01; RMAN>recover pluggable database pdb01; [oracle@12c01 ~]$ sqlplus / as sysdba SQL> alter pluggable database pdb01 open;