分类目录归档：rman备份/恢复

ORACLE 12C备份与恢复测试

发表于 2012 年 12 月 13 日由惜分飞

12C引进了pdb的概念,使得rman的恢复相对来说复杂了一些,这里对pdb的常规备份和恢复进行了简单测试,供大家参考
cdb启动和pdb关系测试

[oracle@xifenfei tmp]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.0.2 Beta on Wed Dec 12 23:48:02 2012

Copyright (c) 1982, 2012, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> startup
ORACLE instance started.

Total System Global Area  939495424 bytes
Fixed Size                  2267128 bytes
Variable Size             662702088 bytes
Database Buffers          268435456 bytes
Redo Buffers                6090752 bytes
Database mounted.
Database opened.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4043918109 PDB$SEED                       READ ONLY
         3 2346805300 LX1                            MOUNTED
         4 2385557792 LX2                            MOUNTED
         5 1565384817 FF                             MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4043918109 PDB$SEED                       READ ONLY
         3 2346805300 LX1                            READ WRITE
         4 2385557792 LX2                            READ WRITE
         5 1565384817 FF                             READ WRITE

证明直接startup cdb里面的pdb不会自动open,需要手工进行open

rman使用cdb备份数据库

[oracle@xifenfei ~]$ rman target /

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 21:36:08 2012

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

connected to target database: xifenfei (DBID=2412861330)

RMAN> backup filesperset = 5 as compressed backupset database format '/tmp/full_db_%U';

Starting backup at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
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=/u01/app/oracle/oradata/xifenfei/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/xifenfei/LX2/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/xifenfei/LX2/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_06nsn3uq_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00019 name=/u01/app/oracle/oradata/xifenfei/xffexample01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/xifenfei/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
input datafile file number=00018 name=/u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_07nsn407_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/xifenfei/pdbseed/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/xifenfei/pdbseed/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_08nsn41l_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/xifenfei/sysaux01.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/xifenfei/users01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/xifenfei/LX2/LX2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_09nsn440_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_0ansn45d_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-DEC-12

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213250
2       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
3       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
4       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
5       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
6       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626

RMAN> report schema;

Report of database schema for database with db_unique_name xifenfei

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               ***     /u01/app/oracle/oradata/xifenfei/system01.dbf
2    210      PDB$SEED:SYSTEM      ***     /u01/app/oracle/oradata/xifenfei/pdbseed/system01.dbf
3    550      SYSAUX               ***     /u01/app/oracle/oradata/xifenfei/sysaux01.dbf
4    165      PDB$SEED:SYSAUX      ***     /u01/app/oracle/oradata/xifenfei/pdbseed/sysaux01.dbf
5    310      UNDOTBS1             ***     /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
6    5        USERS                ***     /u01/app/oracle/oradata/xifenfei/users01.dbf
7    210      LX1:SYSTEM           ***     /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
8    165      LX1:SYSAUX           ***     /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
9    5        LX1:USERS            ***     /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
10   210      LX2:SYSTEM           ***     /u01/app/oracle/oradata/xifenfei/LX2/system01.dbf
11   165      LX2:SYSAUX           ***     /u01/app/oracle/oradata/xifenfei/LX2/sysaux01.dbf
12   5        LX2:USERS            ***     /u01/app/oracle/oradata/xifenfei/LX2/LX2_users01.dbf
16   270      FF:SYSTEM            ***     /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
17   570      FF:SYSAUX            ***     /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
18   5        FF:USERS             ***     /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
19   341      FF:EXAMPLE           ***     /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    530      TEMP                 32767       /u01/app/oracle/oradata/xifenfei/temp01.dbf
2    20       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/xifenfei/pdbseed/temp01.dbf
3    20       LX1:TEMP             32767       /u01/app/oracle/oradata/xifenfei/LX1/temp01.dbf
4    20       LX2:TEMP             32767       /u01/app/oracle/oradata/xifenfei/LX2/temp01.dbf
5    20       FF:TEMP              32767       /u01/app/oracle/oradata/xifenfei/xfftemp01.dbf

试验证明:通过rman通过cdb库的备份,可以实现对对应的cdb和所包含的pdb进行备份

配置pdb访问tns

[oracle@xifenfei ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.0.2        on 12-DEC-2012 22:33:27

Copyright (c) 1991, 2012, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.0.2       
Start Date                12-DEC-2012 22:31:55
Uptime                    0 days 0 hr. 1 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/xifenfei/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ff" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx1" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx2" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xffXDB" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@xifenfei admin]$ vi tnsnames.ora 
lx1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lx1)
    )
  )
ff =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ff)
    )
  )

sqlplus访问pdb

[oracle@xifenfei admin]$ sqlplus sys@lx1 as sysdba

SQL*Plus: Release 12.1.0.0.2 Beta on Wed Dec 12 22:35:07 2012

Copyright (c) 1982, 2012, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show con_name;

CON_NAME
------------------------------
LX1

SQL> create user xff identified by xifenfei;

User created.

SQL> GRANT SYSDBA TO XFF;

Grant succeeded.

rman备份pdb数据库

[oracle@xifenfei admin]$ rman target xff/xifenfei@lx1

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 22:44:46 2012

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

connected to target database: xifenfei (DBID=2412861330)

RMAN> backup filesperset = 5 as compressed backupset database format '/tmp/lx1_db_%U';

Starting backup at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=256 device type=DISK
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=/u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/lx1_db_0bnsn80f_1_1 tag=TAG20121212T224534 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 12-DEC-12

RMAN> report schema;

Report of database schema for database with db_unique_name xifenfei

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
7    210      LX1:SYSTEM           ***     /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
8    165      LX1:SYSAUX           ***     /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
9    5        LX1:USERS            ***     /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
3    20       LX1:TEMP             32767       /u01/app/oracle/oradata/xifenfei/LX1/temp01.dbf

rman通过cdb备份pdb

[oracle@xifenfei admin]$ rman target /

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:02:07 2012

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

connected to target database: xifenfei (DBID=2412861330)

RMAN> backup filesperset = 5 as compressed backupset pluggable database FF format '/tmp/ff_db_%U';

Starting backup at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=262 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
input datafile file number=00019 name=/u01/app/oracle/oradata/xifenfei/xffexample01.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
input datafile file number=00018 name=/u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/ff_db_0cnsn8vm_1_1 tag=TAG20121212T230214 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
Finished backup at 12-DEC-12

模拟pdb库全库恢复

SQL> conn xff/xifenfei@lx1 as sysdba
Connected.

SQL> create table t_xifenfei as select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     19121

--在pdb中不能切换日志(因为日志是全局的)
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

SQL> shutdown immediate;
Pluggable Database closed.

--删除数据文件
[oracle@xifenfei admin]$ rm /u01/app/oracle/oradata/xifenfei/LX1/*

--rman基于cdb恢复pdb库
[oracle@xifenfei ~]$ rman target /

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:11:22 2012

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

connected to target database: xifenfei (DBID=2412861330)

RMAN> restore pluggable database lx1;

Starting restore at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 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 00007 to /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/lx1_db_0bnsn80f_1_1
channel ORA_DISK_1: piece handle=/tmp/lx1_db_0bnsn80f_1_1 tag=TAG20121212T224534
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 12-DEC-12

RMAN> recover pluggable database lx1;

Starting recover at 12-DEC-12
using channel ORA_DISK_1

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

Finished recover at 12-DEC-12

RMAN> alter pluggable database lx1 open;

Statement processed

--验证恢复结果
SQL> conn xff/xifenfei@lx1 as sysdba
Connected.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     19121

试验证明:对于pdb库的备份,使用rman可以在cdb级别进行还原和恢复

模拟数据文件恢复

SQL> create table t_xifenfei tablespace example
  2   as
  3    select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     89604

SQL> col name for a60 
SQL> set lines 134
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------
         5 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
        16 /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
        17 /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
        18 /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
        19 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

--离线含测试数据的数据文件
SQL> alter database datafile 19 offline;

Database altered.

--删除数据文件
SQL> !rm /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

SQL> !ls -l /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
ls: /u01/app/oracle/oradata/xifenfei/xffexample01.dbf: No such file or directory


--尝试pdb级别恢复
[oracle@xifenfei ~]$ rman target sys/xifenfei@ff

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:29:03 2012

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

connected to target database: xifenfei (DBID=2412861330)

RMAN> restore datafile 19;

Starting restore at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/12/2012 23:29:15
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 19 found to restore

--pdb级别不能识别对应数据文件(一种可能是我在cdb级别备份FF库,另一种可能bug)
RMAN> list backup of datafile 19;

specification does not match any backup in the repository

--在cdb级别还原
[oracle@xifenfei tmp]$ rman target /

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:44:21 2012

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

connected to target database: xifenfei (DBID=2412861330)

RMAN> list backup of datafile 19;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    76.81M     DISK        00:00:44     12-DEC-12      
        BP Key: 3   Status: AVAILABLE  Compressed: YES  Tag: TAG20121212T213626
        Piece Name: /tmp/full_db_07nsn407_1_1
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  19      Full 1860043    12-DEC-12 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    189.52M    DISK        00:01:25     12-DEC-12      
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20121212T230214
        Piece Name: /tmp/ff_db_0cnsn8vm_1_1
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  19      Full 1860043    12-DEC-12 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

RMAN> restore datafile 19;

Starting restore at 12-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 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 00019 to /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ff_db_0cnsn8vm_1_1
channel ORA_DISK_1: piece handle=/tmp/ff_db_0cnsn8vm_1_1 tag=TAG20121212T230214
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 12-DEC-12

--cdb级别恢复数据文件
RMAN> recover datafile 19;

Starting recover at 12-DEC-12
using channel ORA_DISK_1

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

Finished recover at 12-DEC-12

--cdb级别不能直接online
RMAN> alter database datafile 19 online;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 12/12/2012 23:46:15
ORA-01516: nonexistent log file, data file, or temporary file "19"

RMAN> alter pluggable database ff datafile 19 online;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 12/12/2012 23:47:53
ORA-65046: operation not allowed from outside a pluggable database

--进入pdb库进行online
SQL> alter database datafile 19 online;               

Database altered.

--验证数据
SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     89604

试验证明:rman通过cdb级别操作,还是一步步恢复了pdb中离线异常的数据文件

总结说明
1.rman可以比较好的操作cdb和pdb备份
2.pdb的备份和恢复可以通过cdb来完成

发表在 ORACLE 12C, rman备份/恢复 | 标签为 ORACLE 12C | 3 条评论

通过odu验证rman backup对于truncate对象备份处理

发表于 2012 年 8 月 27 日由惜分飞

rman backup 对于truncate和drop等相关操作的extent到底是怎么处理的,这里通过rman backup 结合odu证明出来,在较新版本的rman中,rman backup 并未完全的备份这些被认为不需要的extent.
创建模拟环境

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create tablespace xifenfei datafile '/u01/oracle/oradata/XFF/xifenfei01.dbf' 
   2 size 10m autoextend on maxsize 10g;

Tablespace created.

SQL> conn chf/xifenfei
Connected.

SQL> create table t_xifenfei tablespace xifenfei
  2  as
  3  select * from dba_objects;

Table created.

SQL> insert into t_xifenfei
  2  select * from dba_objects;

50055 rows created.

SQL> commit;

Commit complete.

SQL> select BYTES from dba_free_space where TABLESPACE_NAME='XIFENFEI';

     BYTES
----------
    983040

SQL> select BYTES from v$datafile where name='/u01/oracle/oradata/XFF/xifenfei01.dbf';

     BYTES
----------
  12582912

SQL> select 12582912-983040 from dual;

12582912-983040
---------------
       11599872

SQL> select object_id,data_object_id from dba_objects where object_name='T_XIFENFEI';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     51833          51833

--这里我们得到信息有:
--1.dataobj#=51833
--2.使用数据文件空间为:11599872

rman备份no truncate table 数据文件

[oracle@xifenfei ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Dec 15 06:00:05 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: XFF (DBID=3440302261)

RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/no_truncate_xifenfei';

Starting backup at 15-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/oracle/oradata/XFF/xifenfei01.dbf
channel ORA_DISK_1: starting piece 1 at 15-DEC-11
channel ORA_DISK_1: finished piece 1 at 15-DEC-11
piece handle=/u01/oracle/oradata/tmp/no_truncate_xifenfei tag=TAG20111215T060343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-DEC-11

truncate table 操作

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 15 06:03:58 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> truncate table chf.t_xifenfei;

Table truncated.

rman备份truncate table 数据文件

RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/truncate_xifenfei';

Starting backup at 15-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/oracle/oradata/XFF/xifenfei01.dbf
channel ORA_DISK_1: starting piece 1 at 15-DEC-11
channel ORA_DISK_1: finished piece 1 at 15-DEC-11
piece handle=/u01/oracle/oradata/tmp/truncate_xifenfei tag=TAG20111215T060445 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-DEC-11

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/*_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 06:03 /u01/oracle/oradata/tmp/no_truncate_xifenfei
-rw-r----- 1 oracle oinstall   630784 Dec 15 06:04 /u01/oracle/oradata/tmp/truncate_xifenfei

odu挖rman备份前数据文件

ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 89
TABLE OBJ$ file_no: 1 block_no: 121
CLUSTER C_OBJ# file_no: 1 block_no: 25
CLUSTER C_OBJ# file_no: 1 block_no: 25
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found TABPART$'s obj# 266
found TABPART$'s dataobj#:266,ts#:0,file#:1,block#:2121,tab#:0
found INDPART$'s obj# 271
found INDPART$'s dataobj#:271,ts#:0,file#:1,block#:2161,tab#:0
found TABSUBPART$'s obj# 278
found TABSUBPART$'s dataobj#:278,ts#:0,file#:1,block#:2217,tab#:0
found INDSUBPART$'s obj# 283
found INDSUBPART$'s dataobj#:283,ts#:0,file#:1,block#:2257,tab#:0
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found LOB$'s obj# 151
found LOB$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:6
found LOBFRAG$'s obj# 299
found LOBFRAG$'s dataobj#:299,ts#:0,file#:1,block#:2393,tab#:0

ODU> scan extent tablespace 6

scan extent start: 2011-12-15 06:12:28
scanning extent...
scanning extent finished.
scan extent completed: 2011-12-15 06:12:28

ODU> unload table chf.t_xifenfei object 51833 

Unloading table: T_XIFENFEI,object ID: 51833
Unloading segment,storage(Obj#=51833 DataObj#=51833 TS#=6 File#=5 Block#=11 Cluster=0)
100110 rows unloaded
--这里可以看到odu全部找到被truncate掉的记录条数

使用rman 备份后数据文件

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@xifenfei odu]$ rm /u01/oracle/oradata/XFF/xifenfei01.dbf
[oracle@xifenfei odu]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Dec 15 06:14:00 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     318767104 bytes

Fixed Size                     1267236 bytes
Variable Size                104860124 bytes
Database Buffers             205520896 bytes
Redo Buffers                   7118848 bytes

RMAN> restore datafile 5;

Starting restore at 15-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u01/oracle/oradata/XFF/xifenfei01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/truncate_xifenfei
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/oradata/tmp/truncate_xifenfei tag=TAG20111215T060445
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-DEC-11

odu挖rman还原后数据文件

ODU> scan extent tablespace 6

scan extent start: 2011-12-15 06:14:43
scanning extent...
scanning extent finished.
scan extent completed: 2011-12-15 06:14:43

ODU>  unload table chf.t_xifenfei object 51833 

Unloading table: T_XIFENFEI,object ID: 51833
Unloading segment,storage(Obj#=51833 DataObj#=51833 TS#=6 File#=5 Block#=11 Cluster=0)
4774 rows unloaded
--odu只找到极少数数据4774/100110

通过odu挖rman备份前和备份后的数据文件,得知rman backup备份的过程,对绝大多数truncate的表的原始数据未正常备份(为什么是绝大多数,我无法给出解释),这里也可以看出rman backup并非是真正意义上的完全物理上复制(和rman copy还是有区别,copy不能完全被取代)

发表在 rman备份/恢复 | 2 条评论

rman备份对各种数据块操作

发表于 2012 年 8 月 26 日由惜分飞

有不少人对于rman的backup功能,到底备份数据文件的什么级别,一直有着不明确的说法,我这里以10.2.0.4版本的rman backup 测试,进行一个简单的说明.这里提供的是一种思路.如果你在实际工作中,遇到一些rman到底会不会备份相关数据块的时候,可以通过类此的试验来证明你的版本的rman的功能.
模拟环境

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create tablespace xifenfei datafile '/u01/oracle/oradata/XFF/xifenfei01.dbf' 
   2 size 10m autoextend on next 10m maxsize 30g;

Tablespace created.

备份空数据文件

SQL> select BYTES from v$datafile where name='/u01/oracle/oradata/XFF/xifenfei01.dbf';

     BYTES
----------
  10485760

SQL> select BYTES from dba_free_space where TABLESPACE_NAME='XIFENFEI';

     BYTES
----------
  10420224

SQL> SELECT 10485760-10420224 FROM DUAL;

10485760-10420224
-----------------
            65536

RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/no_table_xifenfei';

[root@xifenfei tmp]# ls -l no_table_xifenfei
-rw-r----- 1 oracle oinstall 106496 Dec 15 01:03 no_table_xifenfei

从这里可以看出来rman备份的时候,数据文件中未格式化的块并没有备份(数据文件10m,备份集只有106k左右,比文件实际使用的65536b稍微大点)

备份create表数据文件

SQL> create table t_rman tablespace xifenfei
  2  as
  3  select * from chf.t_xifenfei1;

Table created.

SQL> select BYTES from dba_free_space where TABLESPACE_NAME='XIFENFEI';

     BYTES
----------
   9371648

SQL> select BYTES from v$datafile where name='/u01/oracle/oradata/XFF/xifenfei01.dbf';

     BYTES
----------
  20971520

SQL> select 20971520-9371648 from dual;

20971520-9371648
----------------
        11599872

RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/crt_table_xifenfei';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/crt_table_xifenfei
-rw-r----- 1 oracle oinstall 11608064 Dec 15 01:29 /u01/oracle/oradata/tmp/crt_table_xifenfei

这里可以得出结论,rman的备份集大小可以从一定程度上近似等于数据文件使用空间大小

备份truncate表数据文件

SQL> truncate table t_rman;

Table truncated.

SQL> SELECT 20840448-9371648 from dual;

20840448-9371648
----------------
        11468800

SQL> select 20971520-20840448 from dual;

20971520-20840448
-----------------
           131072

RMAN>  backup tablespace xifenfei format '/u01/oracle/oradata/tmp/truncate_table_xifenfei';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/truncate_table_xifenfei
-rw-r----- 1 oracle oinstall 630784 Dec 15 01:30 /u01/oracle/oradata/tmp/truncate_table_xifenfei

通过这里可以看出来,truncate 对象后,数据文件释放了对象空间,rman备份集也同样未备份这部分空间

备份insert表数据文件

SQL> insert into t_rman  select * from chf.t_xifenfei1;

100062 rows created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select BYTES from v$datafile where name='/u01/oracle/oradata/XFF/xifenfei01.dbf';

     BYTES
----------
  20971520

SQL> select BYTES from dba_free_space where TABLESPACE_NAME='XIFENFEI';

     BYTES
----------
   9371648

SQL>  select 20971520 - 9371648 from dual;

20971520-9371648
----------------
        11599872

RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/insert_table_xifenfei';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/insert_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:19 /u01/oracle/oradata/tmp/insert_table_xifenfei

和直接创建表的出来结论相似

备份delete表数据文件

SQL> delete from t_rman;

100062 rows deleted.

SQL> commit;

Commit complete.

SQL>  alter system checkpoint;

System altered.

SQL> select BYTES from v$datafile where name='/u01/oracle/oradata/XFF/xifenfei01.dbf';

     BYTES
----------
  20971520

SQL>  select BYTES from dba_free_space where TABLESPACE_NAME='XIFENFEI';

     BYTES
----------
   9371648

SQL> select 20971520 - 9371648 from dual;

20971520-9371648
----------------
        11599872

RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/delete_table_xifenfei';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/delete_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:45 /u01/oracle/oradata/tmp/delete_table_xifenfei

这里是直接delete数据,产生了明显的高水位现象(高水位之下部分无数据),但是rman备份,还是会备份高水位之下的所有数据

备份drop表数据文件

SQL> drop table t_rman;

Table dropped.

SQL> select BYTES from v$datafile where name='/u01/oracle/oradata/XFF/xifenfei01.dbf';

     BYTES
----------
  20971520

SQL> select BYTES from v$datafile where name='/u01/oracle/oradata/XFF/xifenfei01.dbf';

     BYTES
----------
  20971520
SQL> select sum(bytes) from  dba_free_space where TABLESPACE_NAME='XIFENFEI';

SUM(BYTES)
----------
  20905984

SQL> select 20971520-20905984 from dual;

20971520-20905984
-----------------
            65536

RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/drop_table_xifenfei';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/drop_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:51 /u01/oracle/oradata/tmp/drop_table_xifenfei

在10g中,因为默认使用回收站功能,对象还存在回收站中,rman为了使得还原出来的数据库可以继续使用回收站中相应的表的闪回功能,所以也会备份回收站中数据

备份purge表数据文件

SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$tBHa31bTe3jgQKjACgEImw==$0 T_RMAN

SQL> purge table "BIN$tBHa31bTe3jgQKjACgEImw==$0";

Table purged.

RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/PURGE_table_xifenfei';

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/PURGE_table_xifenfei
-rw-r----- 1 oracle oinstall 106496 Dec 15 03:08 /u01/oracle/oradata/tmp/PURGE_table_xifenfei

可以看到purge表之后,其实效果类此truncate(当然truncate做的工作更多),rman备份集大小和无数据对象时相同,结合drop和purge也可以知道在删除大对象或者比较多对象而且又确定不再需要,且有rman备份,这个时候建议直接加上purge.

各个备份集汇总

[root@xifenfei tmp]# ll *table_xifenfei
-rw-r----- 1 oracle oinstall 11608064 Dec 15 01:29 crt_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:45 delete_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:51 drop_table_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 02:19 insert_table_xifenfei
-rw-r----- 1 oracle oinstall   106496 Dec 15 01:03 no_table_xifenfei
-rw-r----- 1 oracle oinstall   106496 Dec 15 03:08 PURGE_table_xifenfei
-rw-r----- 1 oracle oinstall   630784 Dec 15 01:30 truncate_table_xifenfei

rman的备份功能本身就是在不断的增强,不同的版本会有不同的结果,最明显的就是在9i版本会备份truncate的数据.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> create tablespace xifenfei datafile
  2  '/u01/oracle/oradata/xifenfei/xifenfei01.dbf' size 10m autoextend on next 10m maxsize 10240m;

Tablespace created.

SQL> create table t_xifenfei tablespace xifenfei
  2  as
  3  select * from dba_objects;

Table created.

SQL> insert into t_xifenfei
  2  select * from dba_objects;

30803 rows created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

RMAN> backup tablespace xifenfei format '/tmp/no_truncate_xifenfei';

SQL> truncate table t_xifenfei;

Table truncated.

[oracle@xifenfei ~]$ ls -l /tmp/*truncate_xifenfei
-rw-r-----  1 oracle oinstall 7004160 Aug 26 22:52 /tmp/no_truncate_xifenfei
-rw-r-----  1 oracle oinstall 7004160 Aug 26 22:53 /tmp/truncate_xifenfei

发表在 rman备份/恢复 | 评论关闭

17813235971

QQ 咨询

分类目录归档：rman备份/恢复

ORACLE 12C备份与恢复测试

通过odu验证rman backup对于truncate对象备份处理

rman备份对各种数据块操作

文章分类

最近发表

分类目录归档：rman备份/恢复

ORACLE 12C备份与恢复测试

通过odu验证rman backup对于truncate对象备份处理

rman备份对各种数据块操作

标签云

文章分类

最近发表