最近单位在整合oracle 10g数据库，将许多不常用的小库合并到一个大库中(oracle10g)。这里的大小指的是数据少，并发低的数据库。 整合方案有两种，一种是在一套10gRAC中创建多个实例，每个实例对应整合前的数据库。另一种方案是在RAC中之创建一个实例，将整合进来的小库转换成同名的表空间。这两种做法各有优缺点，给我的感觉第二种做法更像是12C的多租户模式。 可能有反对的意见，认为第一种方式才更接近于多租户的方式，因为每个整合过来的数据库都是一个独立的instance，独立的instance具备独立的系统表空间，redo日志.... 这个没错。 我们可以通过一个实验，看看12c自己是怎么说的，看看它(CDB)自己是怎样看待PDB的。
实验步骤如下：
1. 创建pdb1的rman备份
2. rm pdb1的数据文件
3. 重启pdb1数据库
4. 使用oracle advise方式自我诊断，自我修复。 advise就是oracle的语言，看看它怎么说。
Let’s get started.
(1)使用sysdba权限登录rman
[oracle@snow ~]$rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Mon May 4 07:35:00 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB (DBID=1995215983)
(2)12c中支持RMAN中直接写SQL语句，在此出查询当前的pdb状况
RMAN>select name,total_size/1024/1024 mb,open_mode from v$pdbs;
using target database control file instead of recovery catalog
NAME MB OPEN_MODE
------------------------------ ---------- ----------
PDB$SEED 270 READ ONLY
PDB1 385 READ WRITE
PDB2 275 READ WRITE
(3)采用传统的方式备份可插拔库pdb1
RMAN>backup pluggable database pdb1 include current controlfile plus archivelog;
(4)人为删除pdb1在磁盘上的物理文件，破坏该数据库
[oracle@snow cdb]$ cd pdb1
[oracle@snow pdb1]$ ll
total 1874840
-rw-r----- 1 oracle oinstall 374874112 May 4 07:35 example01.dbf
-rw-r----- 1 oracle oinstall 558899200 May 3 09:00 pdb1_temp01.dbf
-rw-r----- 1 oracle oinstall 5251072 May 4 07:35 SAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle oinstall 734011392 May 4 07:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 283123712 May 4 07:35 system01.dbf
[oracle@snow pdb1]$rm -rf *
(5)关闭可插拔数据库pdb1
RMAN>alter pluggable database pdb1 close;
Statement processed
(6)重新打开可插拔数据库pdb1，报错。 在这里并没有像以往的数据库提示system01.dbf 如何如何，而是提示这个非重要表空间。
RMAN> alter pluggable database pdb1 open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 05/04/2015 07:41:10
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/home/oracle/dbfile/cdb/pdb1/example01.dbf'
(7)使用oracle的advise方式检查错误
RMAN>list failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3082 CRITICAL OPEN 04-MAY-15 System datafile 8: '/home/oracle/dbfile/cdb/pdb1/system01.dbf' is missing
8 HIGH OPEN 04-MAY-15 One or more non-system datafiles are missing
(8)继续使用oracle的advise来给出恢复建议
RMAN>advise failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3082 CRITICAL OPEN 04-MAY-15 System datafile 8: '/home/oracle/dbfile/cdb/pdb1/system01.dbf' is missing
8 HIGH OPEN 04-MAY-15 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /home/oracle/dbfile/cdb/pdb1/system01.dbf was unintentionally renamed or moved, restore it
2. Automatic repairs may be available if you shutdown the database and restart it in mount mode
3. If file /home/oracle/dbfile/cdb/pdb1/sysaux01.dbf was unintentionally renamed or moved, restore it
4. If file /home/oracle/dbfile/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf was unintentionally renamed or moved, restore it
5. If file /home/oracle/dbfile/cdb/pdb1/example01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 8; Restore and recover datafile 9; Restore and recover datafile 10; ...
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/cdb/cdb/hm/reco_383443975.hm
(9)根据提示，查看一下恢复脚本。发现并没有看到我预期的语法，如：
restore pluggable database pdb1
recover pluggable database pdb1
alter pluggable database pdb1 open
而是基于数据文件号来恢复的。从这个角度看，pdb更像是表空间了，而不是数据库。如果在oracle 11g中使用advise是不会出现下面的datafile 8,9,..offline的。这就是我开篇提到的那种感觉
[oracle@snow pdb1]$cat /u01/app/oracle/diag/rdbms/cdb/cdb/hm/reco_383443975.hm
# restore and recover datafile
sql 'PDB1' 'alter database datafile 8, 9, 10, 11 offline';
restore ( datafile 8, 9, 10, 11 );
recover datafile 8, 9, 10, 11;
sql 'PDB1' 'alter database datafile 8, 9, 10, 11 online';
(10)执行oracle的advise恢复脚本
RMAN>repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/cdb/cdb/hm/reco_383443975.hm
contents of repair script:
# restore and recover datafile
sql 'PDB1' 'alter database datafile 8, 9, 10, 11 offline';
restore ( datafile 8, 9, 10, 11 );
recover datafile 8, 9, 10, 11;
sql 'PDB1' 'alter database datafile 8, 9, 10, 11 online';
Do you really want to execute the above repair (enter YES or NO)?yes
executing repair script
sql statement: alter database datafile 8, 9, 10, 11 offline
Starting restore at 04-MAY-15
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 00008 to /home/oracle/dbfile/cdb/pdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/dbfile/cdb/pdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /home/oracle/dbfile/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/dbfile/cdb/pdb1/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.1.0.1/db_1/dbs/3fq62sqs_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.1.0.1/db_1/dbs/3fq62sqs_1_1 tag=TAG20150504T073556
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 04-MAY-15
Starting recover at 04-MAY-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-MAY-15
sql statement: alter database datafile 8, 9, 10, 11 online
repair failure complete
(11)恢复完成后CDB并不会将PDB置为open状态，需要我们手工open
RMAN> list failure;
Database Role: PRIMARY
no failures found that match specification
RMAN> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDB2 READ WRITE
RMAN>alter pluggable database pdb1 open;
Statement processed
全文完
625
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
