12C的PDB使用RMAN的4种完全恢复场景

于 2017-06-28 09:25:12 发布
阅读量4.9k 收藏 3
点赞数
分类专栏： oracle12c
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010692693/article/details/73826007
版权


1. 备份数据库

数据库打开归档模式

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence     27
Current log sequence           29

SQL> alter system set log_archive_dest_1="location=/archive";
System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size                  8792152 bytes
Variable Size             436209576 bytes
Database Buffers          754974720 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archive
Oldest online log sequence     27
Next log sequence to archive   29
Current log sequence           29
SQL> alter database open;
Database altered.

备份数据库 

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SEED_PDB                       MOUNTED
         4 NONCDB_PDP                     MOUNTED
         5 CLON_PDB                       MOUNTED
         6 TEST_PDB                       READ WRITE NO
         7 PDB4                           READ WRITE NO


[oracle@12cr2 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 11 20:28:36 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ZYLONG (DBID=4281269484)

RMAN> backup database;

Starting backup at 11-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=78 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00022 name=/u01/app/oracle/oradata/zylong/noncdb_pdp/undotbs01.dbf
input datafile file number=00020 name=/u01/app/oracle/oradata/zylong/noncdb_pdp/system01.dbf
input datafile file number=00021 name=/u01/app/oracle/oradata/zylong/noncdb_pdp/sysaux01.dbf
input datafile file number=00023 name=/u01/app/oracle/oradata/zylong/noncdb_pdp/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-JUN-17


channel ORA_DISK_1: starting piece 1 at 11-JUN-17
channel ORA_DISK_1: finished piece 1 at 11-JUN-17
piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1 tag=TAG20170611T202849 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/zylong/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/zylong/pdbseed/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/zylong/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 11-JUN-17
channel ORA_DISK_1: finished piece 1 at 11-JUN-17
piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/07s6i7io_1_1 tag=TAG20170611T202849 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:27
Finished backup at 11-JUN-17

Starting Control File and SPFILE Autobackup at 11-JUN-17
piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/c-4281269484-20170611-00 comment=NONE
Finished Control File and SPFILE Autobackup at 11-JUN-17

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T202849
2       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T202849
3       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T202849
4       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T202849
5       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T202849
6       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T202849
7       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T202849
8       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T203252

2. 数据库open状态，普通表空间损坏

故障模拟

[oracle@12cr2 ~]$ sqlplus / as sysdba
SQL> alter session set container=PDB4;
Session altered.

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 ~]$ cp /u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf .
cp: writing `./system01.dbf': No space left on device
[oracle@12cr2 ~]$ cp /u01/app/oracle/oradata/zylong/pdb4/pdb4/sysaux01.dbf .
cp: writing `./sysaux01.dbf': No space left on device
[oracle@12cr2 ~]$ rm /u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf
[oracle@12cr2 ~]$ rm /u01/app/oracle/oradata/zylong/pdb4/pdb4/sysaux01.dbf
上面cp报错是因为空间不足 
[oracle@12cr2 ~]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        46G   43G   17M 100% /
tmpfs           1.9G   72K  1.9G   1% /dev/shm

[oracle@12cr2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 11 20:48:43 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
ORA-01075: you are currently logged on

[root@12cr2 opt]# rm -rf linuxx64_12201_database.zip 
[root@12cr2 opt]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        46G   40G  3.3G  93% /
tmpfs           1.9G   72K  1.9G   1% /dev/shm

故障现象 

[root@12cr2 opt]# su - oracle
[oracle@12cr2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 11 20:50:14 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=PDB4;
Session altered.

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
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

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 /
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 11 20:57:18 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ZYLONG (DBID=4281269484)
RMAN> restore tablespace pdb4:system;

Starting restore at 11-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00038 to /u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1 tag=TAG20170611T202849
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 11-JUN-17

RMAN> restore tablespace pdb4:sysaux;

Starting restore at 11-JUN-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00039 to /u01/app/oracle/oradata/zylong/pdb4/pdb4/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1 tag=TAG20170611T202849
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 11-JUN-17

RMAN> recover tablespace pdb4:system;

Starting recover at 11-JUN-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-JUN-17

RMAN> recover tablespace pdb4:sysaux;

Starting recover at 11-JUN-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-JUN-17

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;

Starting recover at 11-JUN-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-JUN-17

RMAN>  alter pluggable database pdb4 open;

Statement processed

恢复后现象 

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

SQL>  alter session set container=PDB4;
Session altered.

SQL> select username from dba_users where username='TEST';
no rows selected

3. 数据库关闭状态，系统表空间损坏

模拟现象

SQL> alter pluggable database pdb4 close;
Pluggable database altered.

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

SQL> alter pluggable database pdb4 open;
alter pluggable database pdb4 open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 38 - see DBWR trace file
ORA-01110: data file 38:
'/u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf'

恢复 

[oracle@12cr2 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 11 22:00:41 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ZYLONG (DBID=4281269484)

RMAN> restore tablespace pdb4:system;    

Starting restore at 11-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00038 to /u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1 tag=TAG20170611T202849
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 11-JUN-17

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 22:01:32
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;

Starting recover at 11-JUN-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 11-JUN-17

RMAN> alter pluggable database pdb4 open;

Statement processed

4. 数据库关闭状态，普通表空间损坏

模拟故障

SQL> alter pluggable database pdb4 close;
Pluggable database altered.

[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 /
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 11 22:05:53 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ZYLONG (DBID=4281269484)

RMAN> restore tablespace pdb4:users;

Starting restore at 11-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=82 device type=DISK

creating datafile file number=41 name=/u01/app/oracle/oradata/zylong/pdb4/pdb4/users01.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 11-JUN-17

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 22:06:22
ORA-01113: file 41 needs media recovery
ORA-01110: data file 41: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/users01.dbf'

RMAN> recover tablespace pdb4:users;

Starting recover at 11-JUN-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-JUN-17

RMAN> alter pluggable database pdb4 open;

Statement processed

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

模拟故障

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

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

SQL> grant dba to test;
Grant succeeded.

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

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

SQL> commit;
Commit complete.

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

故障后的现象 

SQL> select count(*) from test;
  COUNT(*)
----------
        99

SQL> insert into test select * from test;
99 rows created.

SQL> commit;
Commit complete.

SQL> insert into test select * from test;
198 rows created.

SQL> commit;
Commit complete.

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5970
Session ID: 52 Serial number: 5849

SQL> insert into test select * from test;
ERROR:
ORA-03114: not connected to ORACLE

SQL> conn test/oracle@192.168.16.81:1521/pdb4
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

SQL> conn / as sysdba
Connected.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SEED_PDB                       MOUNTED
         4 NONCDB_PDP                     MOUNTED
         5 CLON_PDB                       MOUNTED
         6 TEST_PDB                       READ WRITE NO
         7 PDB4                           MOUNTED
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 /
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 11 21:28:14 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ZYLONG (DBID=4281269484)

RMAN>  restore tablespace pdb4:users;     

Starting restore at 11-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=77 device type=DISK

creating datafile file number=41 name=/u01/app/oracle/oradata/zylong/pdb4/pdb4/users01.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 11-JUN-17

RMAN> recover tablespace pdb4:users;     

Starting recover at 11-JUN-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-JUN-17

RMAN> alter pluggable database pdb4 open;

using target database control file instead of recovery catalog
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;

Starting recover at 11-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-JUN-17

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:37:09
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;

Starting recover at 11-JUN-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-JUN-17

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:37:43
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; 

Starting recover at 11-JUN-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-JUN-17

RMAN> alter pluggable database pdb4 open;

Statement processed

恢复后的现象 

SQL> conn test/oracle@192.168.16.81:1521/pdb4
Connected.
SQL> select count(*) from test;
  COUNT(*)
----------
       396
如果PDB4没有关闭，可以restore tablespace pdb4:users可能会报错，可以尝试执行以下命令在restore： 
SQL > alter tablespace users offline immediate;
recover之后online
SQL > alter tablespace users online;

张玉龙
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值