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

来源：互联网发布：htpc 用什么软件编辑：程序博客网时间：2024/06/14 21:01

1. 备份数据库

数据库打开归档模式

SQL> archive log list;Database log mode              No Archive ModeAutomatic archival             DisabledArchive destination            /u01/app/oracle/product/12.2.0/db_1/dbs/archOldest online log sequence     27Current log sequence           29SQL> alter system set log_archive_dest_1="location=/archive";System altered.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 1207959552 bytesFixed Size                  8792152 bytesVariable Size             436209576 bytesDatabase Buffers          754974720 bytesRedo Buffers                7983104 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            /archiveOldest online log sequence     27Next log sequence to archive   29Current log sequence           29SQL> 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 2017Copyright (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-17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=78 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00022 name=/u01/app/oracle/oradata/zylong/noncdb_pdp/undotbs01.dbfinput datafile file number=00020 name=/u01/app/oracle/oradata/zylong/noncdb_pdp/system01.dbfinput datafile file number=00021 name=/u01/app/oracle/oradata/zylong/noncdb_pdp/sysaux01.dbfinput datafile file number=00023 name=/u01/app/oracle/oradata/zylong/noncdb_pdp/users01.dbfchannel ORA_DISK_1: starting piece 1 at 11-JUN-17channel ORA_DISK_1: starting piece 1 at 11-JUN-17channel ORA_DISK_1: finished piece 1 at 11-JUN-17piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1 tag=TAG20170611T202849 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00006 name=/u01/app/oracle/oradata/zylong/pdbseed/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/zylong/pdbseed/system01.dbfinput datafile file number=00008 name=/u01/app/oracle/oradata/zylong/pdbseed/undotbs01.dbfchannel ORA_DISK_1: starting piece 1 at 11-JUN-17channel ORA_DISK_1: finished piece 1 at 11-JUN-17piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/07s6i7io_1_1 tag=TAG20170611T202849 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:27Finished backup at 11-JUN-17Starting Control File and SPFILE Autobackup at 11-JUN-17piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/c-4281269484-20170611-00 comment=NONEFinished Control File and SPFILE Autobackup at 11-JUN-17RMAN> 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         TAG20170611T2028492       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T2028493       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T2028494       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T2028495       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T2028496       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T2028497       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T2028498       B  F  A DISK        11-JUN-17       1       1       NO         TAG20170611T203252

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

故障模拟

[oracle@12cr2 ~]$ sqlplus / as sysdbaSQL> 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 -hFilesystem      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 sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 11 20:48:43 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.ERROR:ORA-09817: Write to audit file failed.Linux-x86_64 Error: 28: No space left on deviceAdditional information: 12ORA-01075: you are currently logged on[root@12cr2 opt]# rm -rf linuxx64_12201_database.zip [root@12cr2 opt]# df -hFilesystem      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 sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 11 20:50:14 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> 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.dbfSQL> create user test identified by oracle;create user test identified by oracle*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-01116: error in opening database file 38ORA-01110: data file 38:'/u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> conn / as sysdbaSQL> 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 recoveryORA-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 2017Copyright (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-17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=52 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00038 to /u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1 tag=TAG20170611T202849channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:07Finished restore at 11-JUN-17RMAN> restore tablespace pdb4:sysaux;Starting restore at 11-JUN-17using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00039 to /u01/app/oracle/oradata/zylong/pdb4/pdb4/sysaux01.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1 tag=TAG20170611T202849channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:07Finished restore at 11-JUN-17RMAN> recover tablespace pdb4:system;Starting recover at 11-JUN-17using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 11-JUN-17RMAN> recover tablespace pdb4:sysaux;Starting recover at 11-JUN-17using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 11-JUN-17RMAN> 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:12ORA-01113: file 40 needs media recoveryORA-01110: data file 40: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/undotbs01.dbf'RMAN> recover tablespace pdb4:UNDOTBS1;Starting recover at 11-JUN-17using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 11-JUN-17RMAN>  alter pluggable database pdb4 open;Statement processed

恢复后现象

[oracle@12cr2 ~]$ sqlplus / as sysdbaSQL>  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.dbfSQL> 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 fileORA-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 2017Copyright (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-17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=52 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00038 to /u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/06s6i7hv_1_1 tag=TAG20170611T202849channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:03Finished restore at 11-JUN-17RMAN> 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:32ORA-01113: file 38 needs media recoveryORA-01110: data file 38: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf'RMAN> recover tablespace pdb4:system;Starting recover at 11-JUN-17using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:01Finished recover at 11-JUN-17RMAN> 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.dbfSQL> 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 fileORA-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 2017Copyright (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-17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=82 device type=DISKcreating datafile file number=41 name=/u01/app/oracle/oradata/zylong/pdb4/pdb4/users01.dbfrestore not done; all files read only, offline, excluded, or already restoredFinished restore at 11-JUN-17RMAN> 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:22ORA-01113: file 41 needs media recoveryORA-01110: data file 41: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/users01.dbf'RMAN> recover tablespace pdb4:users;Starting recover at 11-JUN-17using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 11-JUN-17RMAN> 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/pdb4Connected.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(*)----------        99SQL> 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 channelProcess ID: 5970Session ID: 52 Serial number: 5849SQL> insert into test select * from test;ERROR:ORA-03114: not connected to ORACLESQL> conn test/oracle@192.168.16.81:1521/pdb4ERROR:ORA-01033: ORACLE initialization or shutdown in progressProcess ID: 0Session ID: 0 Serial number: 0SQL> conn / as sysdbaConnected.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                           MOUNTEDSQL> 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 fileORA-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 2017Copyright (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-17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=77 device type=DISKcreating datafile file number=41 name=/u01/app/oracle/oradata/zylong/pdb4/pdb4/users01.dbfrestore not done; all files read only, offline, excluded, or already restoredFinished restore at 11-JUN-17RMAN> recover tablespace pdb4:users;     Starting recover at 11-JUN-17using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 11-JUN-17RMAN> alter pluggable database pdb4 open;using target database control file instead of recovery catalogRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of sql statement command at 06/11/2017 21:35:30ORA-01113: file 40 needs media recoveryORA-01110: data file 40: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/undotbs01.dbf'RMAN> recover tablespace pdb4:UNDOTBS1;Starting recover at 11-JUN-17allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=71 device type=DISKstarting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 11-JUN-17RMAN> 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:09ORA-01113: file 39 needs media recoveryORA-01110: data file 39: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/sysaux01.dbf'RMAN> recover tablespace pdb4:sysaux;Starting recover at 11-JUN-17using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 11-JUN-17RMAN> 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:43ORA-01113: file 38 needs media recoveryORA-01110: data file 38: '/u01/app/oracle/oradata/zylong/pdb4/pdb4/system01.dbf'RMAN> recover tablespace pdb4:system; Starting recover at 11-JUN-17using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 11-JUN-17RMAN> alter pluggable database pdb4 open;Statement processed

恢复后的现象

SQL> conn test/oracle@192.168.16.81:1521/pdb4Connected.SQL> select count(*) from test;  COUNT(*)----------       396

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

SQL > alter tablespace users offline immediate;recover之后onlineSQL > alter tablespace users online;

阅读全文

0 0