月归档：十二月 2012

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 条评论

EXADATA与非EXADATA搭建DATAGURAD关于EHCC特性测试

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

随着xd的越来越普及，不少的企业使用了xd，但是不少企业因为资金有限，只有一台xd，但是为了实现数据的容灾，可能会使用一台非xd的机器来通过dataguard来实现容灾,但是因为xd的ehcc新特性,官方宣传是只在xd中支持,如果dg的备库不是xd。那么会怎么样,这里通过测试得出如下一些结论：xd与非xd可以构造dg,ehcc功能在xd上无法高效使用。对于这样的环境条件下,使用ORACLE自带压缩效率更高.针对ehcc压缩效率很低,个人猜测,是因为xd检查到备库是非xd环境,直接对ehcc进行了降级压缩处理,从而出现了ehcc的压缩效率比oltp还低(牺牲了xd的性能,确保了数据的安全,看来xd的设计还是考虑的比较全面)
xd基本信息

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show parameter clu;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string

SQL> SELECT OPEN_MODE, DATABASE_ROLE ,NAME FROM V$DATABASE;

OPEN_MODE            DATABASE_ROLE    NAME
-------------------- ---------------- ---------
READ WRITE           PRIMARY          xxxxxx

SQL> !uname -a
Linux dm01db02 2.6.18-194.3.1.0.4.el5 #1 SMP Sat Feb 19 03:38:37 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

xd创建模拟表

SQL> create table t_FF_c compress as select  * from dba_objects;

Table created.

SQL> create table t_FF_c_o compress for oltp  as select  * from dba_objects;

Table created.

SQL> create table t_FF_q_l compress for query low as select  * from dba_objects;

Table created.

SQL> create table t_FF_q_h compress for query high  as select  * from dba_objects;

Table created.

SQL> create table t_FF_a_l compress for archive low  as select  * from dba_objects;

Table created.

SQL> create table t_FF_a_h compress for archive high as select * from dba_objects;

Table created.

SQL> create table t_ff as select * from dba_objects;

Table created.

xd查询模拟表

SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';  2    3  

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               10 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC

通过这里发现,带有非dg的xd使用ehcc压缩效率都低了很多

非xd备库基本信息

SQL> SELECT OPEN_MODE, DATABASE_ROLE ,NAME FROM V$DATABASE;

OPEN_MODE            DATABASE_ROLE    NAME
-------------------- ---------------- ---------
READ ONLY WITH APPLY PHYSICAL STANDBY xxxxxx

SQL> show parameter clu;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string

SQL> !uname -a
Linux oradg 2.6.18-238.el5xen #1 SMP Sun Dec 19 14:42:02 EST 2010 x86_64 x86_64 x86_64 GNU/Linux

查询非xd dg备库

SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%'; 

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               10 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC

SQL> SELECT COUNT(*) FROM T_FF_Q_L;

  COUNT(*)
----------
     94709

SQL> SELECT COUNT(*) FROM T_FF_Q_H;

  COUNT(*)
----------
     94710

SQL> SELECT COUNT(*) FROM T_FF_C_O;

  COUNT(*)
----------
     94708

SQL> SELECT COUNT(*) FROM T_FF_C;

  COUNT(*)
----------
     94707

SQL> SELECT COUNT(*) FROM T_FF_A_L;

  COUNT(*)
----------
     94711

SQL> SELECT COUNT(*) FROM T_FF_A_H;

  COUNT(*)
----------
     94712

SQL> select count(*) from t_FF;

  COUNT(*)
----------
    94713

通过这里测试证明,对于非xd dg库,可以正常的查询xd上的ehcc相关表,而且相关大小也相同(物理dg当然相同了)

测试xd与非xd dg测试ehcc的dml操作

--xd 主库
SQL> update t_ff_a_h set owner='www.xifenfei.com';

94712 rows updated.

SQL> commit;

Commit complete.

SQL>  select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';  2    3  

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC

7 rows selected.

SQL> alter system switch logfile;

System altered.

--非xd 备库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';  

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC

证明对于xd与非xd构成的dg环境,可以执行dml操作.

测试xd与非xd dg的ehcc的append操作

--xd准备三张测试空表
SQL> create table t_FF_a_l_1 compress for archive low as select * from t_FF  where 1=0;

Table created.

SQL> create table t_FF_a_h_1 compress for archive high  as select * from t_FF  where 1=0;

Table created.

SQL>truncate table t_FF;

Table truncated.

--插入数据(每个表执行5次)
SQL> insert /*+ APPEND */ into t_FF_a_l_1 select * from dba_objects; 

94714 rows created.

SQL> commit;

Commit complete.


SQL> insert /*+ APPEND */ into t_FF_h_l_1 select * from dba_objects; 

94714 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into t_FF select * from dba_objects; 

94714 rows created.

SQL> commit;

Commit complete.

--查看相关表数据量
SQL> select count(*) from t_FF_a_l_1;

  COUNT(*)
----------
    473570

SQL> select count(*) from t_FF_a_h_1;

  COUNT(*)
----------
    473570

SQL> select count(*) from t_FF;
  COUNT(*)
----------
    473570

--查看xd主库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%'; 

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   52
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC
SYS                            T_FF_A_H_1                             47 ARCHIVE HIGH
SYS                            T_FF_A_L_1                             47 ARCHIVE LOW

--查看非xd备库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%'; 

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   52
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC
SYS                            T_FF_A_H_1                             47 ARCHIVE HIGH
SYS                            T_FF_A_L_1                             47 ARCHIVE LOW

试验整体结论
1.xd可以与非xd机器构建dg容灾环境(不会因为非dg不支持ehcc而导致无法搭建他们之间的dg)
2.xd与非xd的dg,ehcc功能大大缩水,基本上和非压缩状态差不多,比OLTP低很多
3.xd与非xd的dg在备库中支持select,dml,hint append等操作,这些操作是因为ehcc表在xd端就进行了ehcc降级导致

发表在 Data Guard, EXADATA | 一条评论

ORACLE 12C PDB部分功能测试

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

ORACLE 12C中提出来CDB和PDB的概念,对于ORACLE的数据库来说,确实是一个新东西,他们可以分别理解为容器和插件(PDB插入在CDB中),CDB的管理和传统数据库区别不大,本篇文章对PDB的部分操作进行了简单说明(创建PDB,OPEN PDB,DROP PDB,Plug PDB,Unplugging PDB)
CREATE PDB

SQL> SELECT NAME,CDB FROM  V$DATABASE;

NAME      CDB
--------- ---
XIFENFEI  YES

SQL> select pdb_id,pdb_name,dbid,STATUS,CREATION_SCN  from dba_pdbs;

    PDB_ID PDB_NAME                             DBID STATUS        CREATION_SCN
---------- ------------------------------ ---------- ------------- ------------
         2 PDB$SEED                       4044122081 NORMAL             1661281
         3 FF                             1565322182 NORMAL             1720654


SQL> CREATE PLUGGABLE DATABASE xff_db ADMIN USER xff IDENTIFIED BY xifenfei
  2  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  3  DEFAULT TABLESPACE xifenfei
  4  DATAFILE '/u01/app/oracle/oradata/xifenfei/xff/xifenfei01.dbf' SIZE 25M AUTOEXTEND ON
  5  PATH_PREFIX = '/u01/app/oracle/oradata/xifenfei/xff/'
  6  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/xifenfei/pdbseed/', 
  7  '/u01/app/oracle/oradata/xifenfei/xff/');

Pluggable database created.

SQL> select pdb_id,pdb_name,dbid,STATUS,CREATION_SCN  from dba_pdbs;

    PDB_ID PDB_NAME                             DBID STATUS        CREATION_SCN
---------- ------------------------------ ---------- ------------- ------------
         2 PDB$SEED                       4044122081 NORMAL             1661281
         3 FF                             1565322182 NORMAL             1720654
         4 XFF_DB                         2272981748 NEW                1771028

OPEN PDB

SQL> alter pluggable database xff_db open;

Pluggable database altered.

SQL> select pdb_id,pdb_name,dbid,STATUS,CREATION_SCN  from dba_pdbs;

    PDB_ID PDB_NAME                             DBID STATUS        CREATION_SCN
---------- ------------------------------ ---------- ------------- ------------
         2 PDB$SEED                       4044122081 NORMAL             1661281
         3 FF                             1565322182 NORMAL             1720654
         4 XFF_DB                         2272981748 NORMAL             1771028

SQL>  alter pluggable database all close immediate;

Pluggable database altered.

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

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4044122081 PDB$SEED                       READ ONLY
         3 1565322182 FF                             MOUNTED
         4 2272981748 XFF_DB                         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 4044122081 PDB$SEED                       READ ONLY
         3 1565322182 FF                             READ WRITE
         4 2272981748 XFF_DB                         READ WRITE

Unplugging a PDB from a CDB

SQL> alter pluggable database FF close immediate;

Pluggable database altered.

SQL> alter  pluggable database ff UNPLUG into '/tmp/ff.xml';

Pluggable database altered.

DROP PDB

SQL> DROP PLUGGABLE DATABASE xff_db INCLUDING DATAFILES;

Pluggable database dropped.

SQL> DROP PLUGGABLE DATABASE ff INCLUDING DATAFILES;

Pluggable database dropped.

Plug Unplugged PDB into CDB

SQL> SELECT NAME,CDB FROM  V$DATABASE;

NAME      CDB
--------- ---
XFF_L     YES

SQL> create pluggable database ff using '/tmp/ff.xml'
  2  copy file_name_convert=('/u01/app/oracle/oradata/xifenfei/FF/','/u01/app/oracle/oradata/xff_l/xff');

Pluggable database created.

SQL> exec DBMS_PDB.SYNC_PDB();

PL/SQL procedure successfully completed.

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

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/xff_l/system01.dbf
/u01/app/oracle/oradata/xff_l/pdbseed/system01.dbf
/u01/app/oracle/oradata/xff_l/sysaux01.dbf
/u01/app/oracle/oradata/xff_l/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/xff_l/undotbs01.dbf
/u01/app/oracle/oradata/xff_l/users01.dbf
/u01/app/oracle/oradata/xff_l/LX1/system01.dbf
/u01/app/oracle/oradata/xff_l/LX1/sysaux01.dbf
/u01/app/oracle/oradata/xff_l/LX1/LX1_users01.dbf
/u01/app/oracle/oradata/xff_l/LX2/system01.dbf
/u01/app/oracle/oradata/xff_l/LX2/sysaux01.dbf
/u01/app/oracle/oradata/xff_l/LX2/LX2_users01.dbf
/u01/app/oracle/oradata/xff_l/xffsystem01.dbf
/u01/app/oracle/oradata/xff_l/xffsysaux01.dbf
/u01/app/oracle/oradata/xff_l/xffSAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/xff_l/xffexample01.dbf

16 rows selected.

补充说明:本部分内容比较多,本篇blog,只是对其中的很小一部分进行了测试,确实证明可以对pdb实现在不同的cdb中实现迁移,对于该项操作,可以在dbca和gc中实现相同操作.由于12c数据库尚未正式发布,该部分功能只是出于个人测试目的.

发表在 ORACLE 12C | 标签为 ORACLE 12C | 一条评论

17813235971

QQ 咨询

月归档：十二月 2012

ORACLE 12C备份与恢复测试

EXADATA与非EXADATA搭建DATAGURAD关于EHCC特性测试

ORACLE 12C PDB部分功能测试

文章分类

最近发表

月归档：十二月 2012

ORACLE 12C备份与恢复测试

EXADATA与非EXADATA搭建DATAGURAD关于EHCC特性测试

ORACLE 12C PDB部分功能测试

标签云

文章分类

最近发表