Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置： 首页 » 技术文章 » Oracle 12c+

12c Backup/Restore PDB/CDB

时间: 2016-03-22 00:50:20  | 作者: ohsdba  | 
如非注明，本站文章皆为原创。欢迎转载，转载时请注明出处和作者信息。
RMAN supports backup and recovery of multitenant container databases (CDBs) and pluggable databases (PDBs), which are introduced in Oracle Database 12c. The support includes backing up CDBs and PDBs and performing both complete and point-in-time recovery of entire CDBs or individual PDBs.
You should have SYSBACKUP or SYSDBA privilege to backup any of the databases.


PDB常用打开关闭命令

    alter pluggable database all open;
    alter pluggable database all except pdb3 open;
    alter pluggable database pdb1, pdb2 open read only;
    alter pluggable database all close immediate;

  

备份PDB,CDB

备份Whole Container Database (CDB)
RMAN> BACKUP DATABASE plus ARCHIVELOG;

[oracle@db1 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Mar 21 17:03:16 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CDB1 (DBID=879831934)
RMAN> backup as compressed backupset database plus archivelog;

Starting backup at 21-MAR-16
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=1 STAMP=906224419
input archived log thread=1 sequence=29 RECID=2 STAMP=907088637
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/u01/oracle/product/121/dbs/03r124nu_1_1 tag=TAG20160321T170357 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 21-MAR-16
Starting backup at 21-MAR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/cdb1/system01.dbf
input datafile file number=00003 name=/oradata/cdb1/sysaux01.dbf
input datafile file number=00004 name=/oradata/cdb1/undotbs01.dbf
input datafile file number=00006 name=/oradata/cdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/u01/oracle/product/121/dbs/04r124o5_1_1 tag=TAG20160321T170405 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/oradata/cdb1/pdb11/sysaux01.dbf
input datafile file number=00008 name=/oradata/cdb1/pdb11/system01.dbf
input datafile file number=00010 name=/oradata/cdb1/pdb11/pdb11_users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/u01/oracle/product/121/dbs/05r124q6_1_1 tag=TAG20160321T170405 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/oradata/cdb1/pdb12/sysaux01.dbf
input datafile file number=00011 name=/oradata/cdb1/pdb12/system01.dbf
input datafile file number=00013 name=/oradata/cdb1/pdb12/pdb12_users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/u01/oracle/product/121/dbs/06r124ra_1_1 tag=TAG20160321T170405 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oradata/cdb1/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/oradata/cdb1/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/u01/oracle/product/121/dbs/07r124sd_1_1 tag=TAG20160321T170405 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 21-MAR-16
Starting backup at 21-MAR-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=3 STAMP=907088816
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/u01/oracle/product/121/dbs/08r124tg_1_1 tag=TAG20160321T170656 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-MAR-16

Starting Control File and SPFILE Autobackup at 21-MAR-16
piece handle=/u01/oracle/product/121/dbs/c-879831934-20160321-00 comment=NONE
Finished Control File and SPFILE Autobackup at 21-MAR-16

RMAN>
备份ROOT
RMAN> BACKUP DATABASE ROOT;
RMAN> BACKUP DATABASE ROOT;

Starting backup at 21-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=60 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=00001 name=/oradata/cdb1/system01.dbf
input datafile file number=00003 name=/oradata/cdb1/sysaux01.dbf
input datafile file number=00004 name=/oradata/cdb1/undotbs01.dbf
input datafile file number=00006 name=/oradata/cdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/u01/oracle/product/121/dbs/0dr12m02_1_1 tag=TAG20160321T215826 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 21-MAR-16

Starting Control File and SPFILE Autobackup at 21-MAR-16
piece handle=/u01/oracle/product/121/dbs/c-879831934-20160321-03 comment=NONE
Finished Control File and SPFILE Autobackup at 21-MAR-16

RMAN>
备份pluggable Database
RMAN> BACKUP PLUGGABLE DATABASE PDB1, PDB2;
RMAN> backup as compressed backupset pluggable database pdb11;

Starting backup at 21-MAR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/oradata/cdb1/pdb11/sysaux01.dbf
input datafile file number=00008 name=/oradata/cdb1/pdb11/system01.dbf
input datafile file number=00010 name=/oradata/cdb1/pdb11/pdb11_users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/u01/oracle/product/121/dbs/0ar12520_1_1 tag=TAG20160321T170920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 21-MAR-16

Starting Control File and SPFILE Autobackup at 21-MAR-16
piece handle=/u01/oracle/product/121/dbs/c-879831934-20160321-01 comment=NONE
Finished Control File and SPFILE Autobackup at 21-MAR-16

RMAN>

备份pluggable Database
$rman target sys@PDB1
RMAN> BACKUP DATABASE;
备份归档
归档备份只能通过CDB去备份，PDB用户无法备份归档和切换日志
RMAN> backup archivelog all;
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL>

CDB,PDB,ROOT完全恢复

还原Whole Container Database (CDB)
RMAN> RUN {
     startup mount;
     restore database;
     recover database;
     alter database open;}

Note:还原CDB，PDB也将会被还原

只还原ROOT Database

RMAN> RUN {
     startup mount;
     restore database root;
     recover database root;
     alter database open;
}
RMAN> restore database root;
Starting restore at 21-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 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 00001 to /oradata/cdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/cdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/cdb1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata/cdb1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/product/121/dbs/0dr12m02_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/product/121/dbs/0dr12m02_1_1 tag=TAG20160321T215826
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 21-MAR-16

RMAN> recover database root;

Starting recover at 21-MAR-16
using channel ORA_DISK_1

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

Finished recover at 21-MAR-16

RMAN> alter database open;
Statement processed
RMAN>       

我们有2种方式还原PDB

1.通过cdb还原pdb

RMAN > RUN {
     restore pluggable database pdb1, pdb2;
     recover pluggable database pdb1, pdb2;
     alter pluggable database pdb1, pdb2 open;
}

2.直接连接pdb，然后还原

$ rman target=rcadm@PDB1

     RMAN> run{
     restore database;
     recover database;
}
CDB,PDB,ROOT不完全恢复
还原CDB,PDB和正常的库一样，当你在CDB上执行不完全恢复时，PDB也将受到影响
当你在PDB上还原时，只影响单个PDB，不会影响全库
RMAN> run{
set until scn 1087645;
recover database;
}
RMAN> run{
set until sequence 120;
recover database;
}

PDB不完全恢复测试

还原时，需指定AUXILIARY DESTINATION

Specifies a location where auxiliary set data files, control files, and online redo logs are created during TSPITR if another location for an individual file is not explicitly specified.

If you do not specify AUXILIARY DESTINATION for a TSPITR, then you must specify the naming of individual auxiliary set data files, control files, and online redo logs before executing RECOVER TABLESPACE with theUNTIL clause. Otherwise, TSPITR fails.

While performing point-in-time recovery of PDBs, if no auxiliary destination is specified, then the fast recovery area is used as the auxiliary destination. When an auxiliary destination is not specified and a fast recovery area is not configured, the recovery of the PDB fails

1.创建表，并记录插入数据库前scn

SQL> create user ohsdba identified by oracle;
User created.
SQL> grant dba to ohsdba;
Grant succeeded.
SQL> conn ohsdba/oracle@10.0.0.120:1521/pdb11
Connected.
SQL> create table ohs(a number);   
Table created.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    2180622
SQL> insert into ohs values(1);
1 row created.
SQL> commit;
Commit complete.
SQL>
2.关闭并还原PDB11
[oracle@db1 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Mar 21 17:18:21 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CDB1 (DBID=879831934)
RMAN> alter pluggable database pdb11 close;
using target database control file instead of recovery catalog
Statement processed

RMAN> restore pluggable database pdb11;

Starting restore at 21-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 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 00008 to /oradata/cdb1/pdb11/system01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /oradata/cdb1/pdb11/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /oradata/cdb1/pdb11/pdb11_users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/product/121/dbs/0ar12520_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/product/121/dbs/0ar12520_1_1 tag=TAG20160321T170920
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 21-MAR-16

RMAN> run{
2> set until scn 2180622;
3> recover pluggable database pdb11;
4> }

executing command: SET until clause

Starting recover at 21-MAR-16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/21/2016 17:20:41
RMAN-05107: AUXILIARY DESTINATION option is not specified

RMAN> run{
2> set until scn 2180622;
3> recover pluggable database pdb11 AUXILIARY DESTINATION '/tmp';
4> }

executing command: SET until clause

Starting recover at 21-MAR-16
current log archived
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='ykfv'

initialization parameters used for automatic instance:
db_name=CDB1
db_unique_name=ykfv_pitr_pdb11_CDB1
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/oracle
_system_trig_enabled=FALSE
sga_target=760M
processes=200
db_create_file_dest=/tmp
log_archive_dest_1='location=/tmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance CDB1

Oracle instance started

Total System Global Area     796917760 bytes

Fixed Size                     2929448 bytes
Variable Size                222301400 bytes
Database Buffers             566231040 bytes
Redo Buffers                   5455872 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  scn 2180622;
# restore the controlfile
restore clone controlfile;
 
# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 21-MAR-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/oracle/product/121/dbs/c-879831934-20160321-01
channel ORA_AUX_DISK_1: piece handle=/u01/oracle/product/121/dbs/c-879831934-20160321-01 tag=TAG20160321T170955
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/CDB1/controlfile/o1_mf_cgzh9c8m_.ctl
Finished restore at 21-MAR-16

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  scn 2180622;
# switch to valid datafilecopies
switch clone datafile  8 to datafilecopy
 "/oradata/cdb1/pdb11/system01.dbf";
switch clone datafile  9 to datafilecopy
 "/oradata/cdb1/pdb11/sysaux01.dbf";
switch clone datafile  10 to datafilecopy
 "/oradata/cdb1/pdb11/pdb11_users01.dbf";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3, 6;
 
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

datafile 8 switched to datafile copy
input datafile copy RECID=3 STAMP=907089713 file name=/oradata/cdb1/pdb11/system01.dbf

datafile 9 switched to datafile copy
input datafile copy RECID=4 STAMP=907089713 file name=/oradata/cdb1/pdb11/sysaux01.dbf

datafile 10 switched to datafile copy
input datafile copy RECID=5 STAMP=907089713 file name=/oradata/cdb1/pdb11/pdb11_users01.dbf

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 21-MAR-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/CDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/CDB1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/CDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /tmp/CDB1/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/oracle/product/121/dbs/04r124o5_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/oracle/product/121/dbs/04r124o5_1_1 tag=TAG20160321T170405
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 21-MAR-16

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=907089788 file name=/tmp/CDB1/datafile/o1_mf_system_cgzh9kr2_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=907089788 file name=/tmp/CDB1/datafile/o1_mf_undotbs1_cgzh9kso_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=907089788 file name=/tmp/CDB1/datafile/o1_mf_sysaux_cgzh9ksg_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=907089788 file name=/tmp/CDB1/datafile/o1_mf_users_cgzh9ktp_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 2180622;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
sql clone 'PDB11' "alter database datafile
 8 online";
sql clone 'PDB11' "alter database datafile
 9 online";
sql clone 'PDB11' "alter database datafile
 10 online";
sql clone "alter database datafile  6 online";
# recover pdb
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS" pluggable database
 'PDB11'   delete archivelog;
sql clone 'alter database open read only';
plsql <<<begin
   add_dropped_ts;
end; >>>;
plsql <<<begin
   save_pdb_clean_scn;
end; >>>;
# shutdown clone before import
shutdown clone abort
plsql <<<begin
   pdbpitr_inspect(pdbname =>  'PDB11');
end; >>>;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  8 online

sql statement: alter database datafile  9 online

sql statement: alter database datafile  10 online

sql statement: alter database datafile  6 online

Starting recover at 21-MAR-16
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 30 is already on disk as file /u01/oracle/product/121/dbs/arch1_30_906114496.dbf
archived log for thread 1 with sequence 31 is already on disk as file /u01/oracle/product/121/dbs/arch1_31_906114496.dbf
archived log file name=/u01/oracle/product/121/dbs/arch1_30_906114496.dbf thread=1 sequence=30
archived log file name=/u01/oracle/product/121/dbs/arch1_31_906114496.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-MAR-16

sql statement: alter database open read only

Oracle instance shut down

Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/CDB1/datafile/o1_mf_sysaux_cgzh9ksg_.dbf deleted
auxiliary instance file /tmp/CDB1/controlfile/o1_mf_cgzh9c8m_.ctl deleted
Finished recover at 21-MAR-16

RMAN>
用ohsdba用户检验数据
SQL> show user
USER is "SYS"
SQL> alter session set container=pdb11;
Session altered.
SQL> select * from ohsdba.ohs;
no rows selected

SQL>
关键词:pdb 12c 

相关文章

关于max_string_size
在Oracle数据库19c中使用JSON
保障业务连续性的神器
Oracle事务卫士(Transaction Guard)和应用连续性(Application Continuity)
如何使用Autoupgrade升级RAC数据库
Oracle PDB升级/迁移实践
容器数据库（CDB）和可插拔数据库（PDB）概述
PDB Migration/Failover in Dataguard
Oracle Database 12c之后DataGuard环境中的PDB操作
在18c中通过ASM Flex DiskGroup克隆PDB
How to generate AWR on PDB and ADG(12.2 afterwards)
在12c上使用wm_concat

推荐文章

联系我们

Oracle Software Download Index

Oracle Database Upgrade

Oracle SQLMaria

Oracle Database Document

Oracle Cloud Document

Oracle Cloud中文链接

eDelivery

GoldenGate

My Oracle Support

Exadata Document

Oracle Linux

Oracle Fusion Middleware

MySQL Document

PostgreSQL

Python

Apache

Github

Sourceforge

CNCF

Ruby

Go

ipcheck

ipaddressguide

db-engines

Linux Kernel

Top